A quick connection to Salesforce with Pentaho

I have been tasked with create a quick way to load orders into sales force by my employer.  They have set up the custom order type in sales force and provided me a login and csv file.  This is the limit of my knowledge of sales force, so I am starting from scratch.  I was able to look around their developer site long enough to find a wsdl and was well on my way to creating a custom c# app to attach to it and simply pars the csv file, but decided to dual purpose this experience and try to do it with the new tool in the shop, Pentaho. 

Pentaho, if you're not familiar, is a ETL / DTL tool that runs on java and creates files called kettle files.  http://www.pentaho.com/  These files script data transformations and movement as transformations and jobs and can be run on a server on a scheduled basis.  The UI editor for configure it is called spoon and is basically a 100 click tool (100 clicks for every line of code I could write).  Its basically an attempt to make the standard process of file reading, writing and data conversions all take place in this tool.  Its a nice idea as who likes writing file readers and parsers?  Well me too sort of, but you get my drift.

Anyways, I crack open Spoon (Pentaho front end), create a new csv file import type and configure it as below.

Basically, I browse to the file I want to load, make sure its comma delimited and quote enclosed then press get fields, it did a nice job in this case and I was able to simply press ok.

Next, I was looking for web server endpoint to tie the data too, but instead found the SalesForce endpoints sitting there for me.  So I grabbed the upsert one (as the files I'm getting weekly are total refreshes so upsert is the way to go).  I configure it like below. 

Basically, all I had to do was enter a username and password as well as the select the custom order object we had created in SalesForce and then edit mappings.  This is where you tie file fields to the sales force service fields.  This isn't too hard and not worth picturing, you can see a few in the picture above.  Then I was done with the transform and loading data to Salesforce.  I will talk more about finishing the Pentaho job off in another post.

BlogEngine.Net 2.9 Syntax Highlighting

So, I just started this blog and part of it is going to be programming, so syntax highlighting is definitely going to be needed.  According to the internet, BlogEngine has built in syntax highlighting and looking through the code, I agree.  However, I can't seem to get it to work.  After about an hour of trying to fix this or understand why, I opted to use a copy paste engine.  The one I use is http://tohtml.com/.  I'm happy with it for today, perhaps I will look further into the BlogEngine issue another day.

Inserting Images with Aspose Word

So I the requirement for a project was to have documents to be mailed to customers show to render from a mvc website so a user could print them and mail them out.  We contemplated connecting to the printer from the web server and forcing the print, but we decided to mail merge the document together and simply dump it to the end user to print out through their own means.  Which down the road will save us time.  Because there could be hundreds of these a day, we decided to do all of them in the same word document so only one download and one print would be needed.  So I had to do a multi merge, not too hard.  But then we needed to put labels in there document.  Inserting images, isn't straight forward I quickly learned.

To accomplish this, I first created a controller function for it looked like the following:

public ActionResult Print(string id, string carrier)
            var ctx = new ContextManaged();
            var returns = get data;
            //do printing
            var doc = new Document(HttpContext.Server.MapPath(Config.GetString("PrintTemplateFile")));
            doc.MailMerge.FieldMergingCallback = new HandleMergeImageFieldFromBlob(HttpContext);
            var ds = new DataSet();
            foreach (var ret in returns)
                var dr = ds.Tables["Returns"].NewRow();
                dr["CustomerName"] = ret.CustomerName;
                dr["AddressLine1"] = ret.CustomerAddressLine1;
                dr["AddressLine2"] = ret.CustomerAddressLine2;
                dr["City"] = ret.CustomerCity;
                dr["State"] = ret.CustomerState;
                dr["Zip"] = ret.CustomerZip;
                dr["OrderNumber"] = ret.OrderNumber;
                dr["Date"] = DateTime.Now.ToString("MMMM d, yyyy");
                dr["CompanyName"] = ret.tbl_Company.CompanyName;
                if (ret.Image != null)
                        dr["Image"] = ret.Id;
                    catch (Exception ex)
                        dr["Image"] = "";
                    dr["Image"] = "";

            var ms = new System.IO.MemoryStream();
            doc.Save(ms, SaveFormat.Doc);
            ms.Position = 0;
            var fileStreamResult = new FileStreamResult(ms, "application/ms-word");
            fileStreamResult.FileDownloadName = "PrintReturns.doc";
            return fileStreamResult;

Then I created functions that overrides the Image Field merging and puts them into the word document as a stream.  This was a bit tricky, but it works.  I also had a function to resize the image as my images were too large.

/// <summary>
        /// This is called when mail merge engine encounters Image:XXX merge field in the document.
        /// You have a chance to return an Image object, file name or a stream that contains the image.
        /// </summary>
        void IFieldMergingCallback.ImageFieldMerging(ImageFieldMergingArgs e)
            if (e.FieldValue.ToString() == String.Empty)
                e.ImageStream = new FileStream(context.Server.MapPath(Config.GetString("NoImage")), FileMode.Open);
                    //get image
                    var ctx = new PPSHP_ShippingServiceContextManaged();
                    var returns = (from ret in ctx.tbl_Returns where ret.Id == Convert.ToInt32(e.FieldValue) select ret.Image).FirstOrDefault();
                    //resize image
                    var bit = ResizeBitmap(new System.Drawing.Bitmap(new MemoryStream(returns.ToArray())), 700, 500);
                    // The field value is a byte array, just cast it and create a stream on it.
                    var ms = new MemoryStream();
                    bit.Save(ms, System.Drawing.Imaging.ImageFormat.Png);
                    MemoryStream imageStream = ms;
                    // Now the mail merge engine will retrieve the image from the stream.
                    e.ImageStream = imageStream;
                catch (Exception ex)
                    e.ImageStream = new FileStream(context.Server.MapPath(Config.GetString("BadImage")), FileMode.Open);

        Bitmap ResizeBitmap(Bitmap b, int nWidth, int nHeight)
            Bitmap result = new Bitmap(nWidth, nHeight);
            using (Graphics g = Graphics.FromImage((Image)result))
                g.DrawImage(b, 0, 0, nWidth, nHeight);
                //draw border
                g.DrawLine(new Pen(Brushes.Black, 2), new Point(0, 0), new Point(0, nHeight));
                g.DrawLine(new Pen(Brushes.Black, 2), new Point(0, nHeight), new Point(nWidth, nHeight));
                g.DrawLine(new Pen(Brushes.Black, 2), new Point(nWidth, nHeight), new Point(nWidth, 0));
                g.DrawLine(new Pen(Brushes.Black, 2), new Point(nWidth, 0), new Point(0, 0));
            return result;

Starting a blog with BlogEngine . Net

I decided to create a blog that covers the various projects I do from day to day.  I am an Engineer by trade but am always busy building something.  In order to leave more time for building things, I decided to use the BlogEngine.Net because, it works with my winhost account, I understand the code, it has good support and theme options, and its free.  

Half the reason I created this blog is simply to remember the things I do, almost like a timeline of projects.  So its really for me, but if anyone else finds it useful, good for you.