SPServices Stories #4 – Using SPServices to Process Files from a Non-Microsoft Source

Introduction

SPServices Stories #4 comes to us from Michael Broschat. Michael claims he’s just a “regular old contractor, nothing special”. He lives in the DC metro area (Northern Virginia, specifically), although he’s originally a West Coast boy.

Using SPServices to Process Files from a Non-Microsoft Source

Our system involves email messages sent to a SharePoint library by a non-Microsoft forms application. Although this particular application normally saves its results as XML files, it cannot _send_ those results anywhere (say, the SharePoint library). It can send the results as delimited strings, however, without field names.

I developed a scheme whereby I provide the field structure in an array (arFields) then match it with the field values derived from the delimited-string email. I then write same to a list.

So, three steps in a four-step process have email looking something like this:

[email protected]*BP*11/01/2012*11/19/2012*18*1 - Sent to Supervisor

The final step contains much more information, for a total of 25 fields. The endpoint list includes all 25 fields, of course. Typically, a user entry will comprise four entries but accommodation must also be made for changes made while the four-step process operates. Once a step 4 has been received, any further attempts to augment this process for that user are ignored.

This JavaScript code (with SPServices, jQuery) runs via an enabling button on the list display page. But even before the button appears, the first routine has run. This routine looks in the library for any entries that have not yet been exported. If any, the button is enabled and its label includes the number of records to read and create in an associated list.

$(document).ready(function() {
  jQuery.support.cors = true;
  $().SPServices({
    operation:  "GetListItems",
    listName: "{D3795486-9926-424E-8F14-59BE5DB65BA8}",	//dev  GFSSEForm
    CAMLViewFields: "<ViewFields><FieldRef Name='LinkFilename'/><FieldRef Name='Exported' /></ViewFields>",
    completefunc: function (xData, Status) {
      $(xData.responseXML).SPFilterNode('z:row').each(function() {
        var thisFSObjType = parseInt($(this).attr("ows_FSObjType").split(";#")[1]);
        if(thisFSObjType === 0) {
          if(parseInt($(this).attr("ows_Exported"))===0) iRecordsToExport++;
        }  //if this is a file name
      });  //each()
      if(iRecordsToExport > 0) {
        $("#btnInitiate").html('There are ' + iRecordsToExport + ' records to export...');
        $("#btnInitiate").prop('disabled', false);
      } else {
        $("#btnInitiate").html('No records to export...'); //evidently, once we're off IE7
        $("#btnInitiate").prop('disabled', true);
      }
    }  //completefunc
  });  //SPServices
});  //document.ready

When the Records to Export button is launched, more or less the same code runs again, but this time I want to see only those email entries that have not already been dealt with (ie, Exported = false). This is done via CAML. I collect the library IDs of all items I’m writing in this batch (by the way, there is an interesting distinction between dealing with a batch and dealing with the set of records already written to the list), along with a couple other values, and create an array of IDs. The rest of the processing in this application is from that array.

return {
  //the only public method; handler for the web page button (enabled when at least one library item has not been exported)
  processRecord: function () {
    //this filter restricts the selection just to items that haven't been exported;
    var myQueryOptions = "<QueryOptions />";
    var myQuery = "<Query><OrderBy><FieldRef Name='Created_x0020_Date' /></OrderBy><Where><Eq><FieldRef Name='Exported' /><Value Type='Integer'>0</Value></Eq></Where></Query>";
    // now sorts by Created, ascending.
    $().SPServices({
      operation:  "GetListItems",
      async: false,
      listName: "{D3795486-9926-424E-8F14-59BE5DB65BA8}",	//dev  GFSSEForm
      CAMLViewFields: "<ViewFields><FieldRef Name='LinkFilename'/><FieldRef Name='Exported' /></ViewFields>",
      CAMLQuery: myQuery,
      CAMLQueryOptions: myQueryOptions,
      completefunc: function (xData, Status) {
        $(xData.responseXML).SPFilterNode('z:row').each(function() {
          //pick up some metadata from the SharePoint library
          //gather ows_ID='14', ows_FSObjType='14;#0', ows_Created_x0020_Date='14;#2012-11-20 06:55:13'
          var thisFSObjType = parseInt($(this).attr("ows_FSObjType").split(";#")[1]);
          idCurrent = $(this).attr("ows_ID");	//available globally
          dateCurrentCreated = $(this).attr("ows_Created_x0020_Date").split(";#")[1];	//available globally
          var formFilename = $(this).attr("ows_LinkFilename");
          if(thisFSObjType == 0) {
            arIDs.push([idCurrent, dateCurrentCreated, formFilename]);
          }  //if this is a file name
        });  //each(); arIDs is an array built from looking at all non-Exported items in the library
        //actually, you need to know the contents before you can decide whether there are any duplicate entries.
        //	ordering by Created, we're processing all entries in order of their entry into the library
      }  //completefunc
    });  //SPServices
    //here, do the Ajax call against the array of arrays; async:false is no longer used with $.ajax, deferred
    //	taking over that function; 2 Jan took the following routine out from completefunc; seems to save a stack level
    $.each(arIDs, function(index, value) {
      var promise = $.ajax({
        type:"GET",
        url:"GFSSEForm/" + arIDs[index][2],		//for SP2010 and above, full path is needed for the library name
        dataType:"text"
      });
      //the promise code executes when the file has been opened by AJAX
      promise.done(doStuff);	//magically passes the data along, too
      promise.fail(function() {alert("failed to open this eform: " + arIDs[index][2]);});
    });  //each
  }
};	// public method processRecord() [return]

By this point, the email file in the SharePoint library has been opened and its contents are ready to process.

There are two ways for email to get into the library: sent by the non-Microsoft forms application and sent directly via a client’s Outlook. The former way gets encoded (binary64), whereas the latter way does not. Both must be parsed for usable content but the encoded email must first be decoded. I use the following routine to handle both:

//	Sets the global variable: arValues; deals with both base64-encoded email and also non-encoded email
//	CRLF = \r\n
function decodeEmail(block) {
  var iBase64 = block.indexOf("base64");
  if(iBase64 > -1) {
    var startBlock = iBase64 + 6;
    var endBlock = block.indexOf("\n--", startBlock);
    var emailBlock = $.trim(block.substring(startBlock, endBlock));
    var strEmail = emailBlock.replace(new RegExp("\\n", "g"), "");
    var strDecoded = Base64.decode(strEmail);
    strDecoded = stripHTML(strDecoded);
    var iLong = strDecoded.indexOf("\r\n\r\n");	//intended for non-SMTP messages
    if(iLong > -1) {
      //take up to first \r\n
      strDecoded = strDecoded.substring(0,iLong+2);
    }
    arValues = strDecoded.split("*");
  } else {
    //	charset="us-ascii"; charset=utf-8; charset="gb2312"
    //here if there was no "base64" in the message; perhaps you should look for [charset="]us-ascii["]
    // this routine greatly strengthened 24 Jan 2012
    var iTrueStart = block.indexOf("quoted-printable");	//24 Jan fine; whole routine looks good
    var iTrueStart2 = iTrueStart + 16;
    var endBlock = block.indexOf("\n--", iTrueStart);
    var strBlock2 = $.trim(block.substring(iTrueStart2, endBlock));
    var newBlock = strBlock2.replace("=\r\n", "");		//kill all CRLFs
    var newBlock2 = newBlock.replace(/\<.*\>/g, "");  //a weird <mailto...> string in one message
    //you could have just called your own stripHTML()!
    var newBlock3 = newBlock2.replace(/=\r\n/g, "");	//one last holdout: =CRLF
    arValues = newBlock3.split("*");

  }
}

In my experience, getting values from functions does not always work within this environment (JavaScript within SharePoint). I have had to rely upon global variables (which have created their own problems at times). When that email decoding code runs, it places the parsed values into a global array: arValues, which is then used by the various routines that follow.

doStuff runs when the promise has been fulfilled. In other words, it only runs when data from the email is in hand. It sends the record off to writeNew or, if the number of field values does not match the current field template, stops the record from being processed.

function doStuff(data) {
  decodeEmail(data);	//sets global arValues, regardless of email type
  arFields = [];	//to ensure correct value within the batch loop
  if(arValues.length == 7) arFields = arFields100;
  if(arValues.length == 25) arFields = arFields200;
  if(arFields.length > 0) {
    boolIgnore = false;	//ensures correct starting point for each email
    iGlobal++;
    arValues[2] = dateFormat(arValues[2]);	//watch for changes in this default field order
    arValues[3] = dateFormat(arValues[3]);
    if(arValues.length === arFields.length) {
      var strArguments = arValues.join();
      writeNew(strArguments);	//wait until this routine before handling a dupe;
    } else alert("Number of values differed from number of form fields; not written." + arFields.length + " fields, " + arValues.length + " values (" + arValues + ")");
  } // was arFields set? If not, just return and let it pass
}  // doStuff()

writeNew simply uses SPServices to write the email contents to the list. It does this by preparing an array value to contain the values in the proper manner for UpdateListItems::New. After writing the record, I call setUpdated to modify the library entry, passing the library ID, which setUpdated uses to access the library metadata.

// strArguments is arValues rendered as string
function writeNew(strArguments) {
  $("#divId").ajaxError( function(event, request, settings, exception) {
    $(this).append("Error here: " + settings.url + ", exception: " + exception);
  });
  var iFields = arFields.length;
  var i = 0;
  var strTest = "";
  var strField = "";
  var vpairs = [];
  var strPairs = "";
  var arValues2 = strArguments.split(',');
  for(i=0; i<iFields; i++) {
    strTest = String(arValues2[i]);
    if(strTest.length > 255) strTest = strTest.substring(0,255);
    strField = arFields[i];
    vpairs.push([strField,strTest]);
  }
  //check to see whether this email address is in HoldMe; if so, processing stops, but run setUpdated(arIDs[idIndex][0]) and advance the index
  notInHoldMe(vpairs[0][1]);	//sets global value regarding presence in HoldMe list
  if(!inHoldMe) {
    var jsDate = getJSDate();		//picks up date values from arValues
    vpairs[4][1]=jsDate;
    $().SPServices({
      operation: "UpdateListItems",
      batchCmd: "New",
      async: false,
      listName: "{2D9F4CDB-A5F0-4EED-8996-C26FB2D08294}",  //development list GFSSVerified
      valuepairs: vpairs,
      completefunc: function(xData, Status) {
        if(Status == 'success') {
          //'success' is a relative term; you must also examine any error text, to see whether an error occurred
          var strError = $(xData.responseXML).SPFilterNode('ErrorText').text();
          if(strError != "") {
            $("#showErrors").append("<p>Error adding: " + $(xData.responseXML).SPFilterNode('z:row').attr("ows_Title") + " " + strError + "</p>");
          } else setUpdated(arIDs[idIndex][0]);  //possibly delete the row at this point
          idIndex++;
          if(vpairs[6][1].substring(0,1) == "4") setLocked(vpairs[0][1]);	// ie, after writing Step 4
          //record has been written; now find out whether it was a duplicate
          else findExisting(vpairs[0][1], vpairs[6][1].substring(0,1));
        } else alert("error: " + xData.responseText);
      }	//completefunc
    }); //SPServices
  }	//if not locked
  else {
    alert("The record for " + vpairs[0][1] + " is locked...");
    setUpdated(arIDs[idIndex][0]);
    idIndex++;
  }
}  // writeNew()

All records are to be written to the list, but it will happen that some records are duplicates (because a later action changes the previous action). In that case, the earlier record needs to be marked as ‘orphan’. The original idea was to simply delete the record but someone wanted to keep it. Therefore, I need to filter orphans from various stages of processing. The routine called findExisting deals with this issue. I use CAML to filter orphans.

function findExisting(user, action) {
  $("#divId").ajaxError( function(event, request, settings, exception) {
    $(this).append("Error in findExisting: " + settings.url + ", exception: " + exception + "<br>");
  });
  var queryOptions = "<QueryOptions />";
  var query = "<Query><Where><And><And><Eq><FieldRef Name='Title' /><Value Type='Text'>" + user + "</Value></Eq><BeginsWith><FieldRef Name='col07x' /><Value Type='Text'>" + action + "</Value></BeginsWith></And><Neq><FieldRef Name='col26x' /><Value Type='Integer'>1</Value></Neq></And></Where></Query>";  //col26x is 'Orphan'
  //CAML looks for existing items having same name and action, ignoring any that have already been marked as orphans
  $().SPServices({
    operation:  "GetListItems",
    async: false,		//required!!!!!
    listName: "{2D9F4CDB-A5F0-4EED-8996-C26FB2D08294}",  //development list GFSSVerified
    CAMLViewFields: "<ViewFields><FieldRef Name='Title'/></ViewFields>",
    CAMLQuery: query,
    CAMLQueryOptions: queryOptions,
    completefunc: function (xData, Status) {
      var iCount = parseInt($(xData.responseXML).SPFilterNode("rs:data").attr("ItemCount"));
      if(iCount > 1) {	//you're here because this value _at least_ was written
        //within this batch, there are multiples of this user/action; pass the multiple IDs
        var arDupIDs = [];
        var iDupID = 0;
        // routine examines each entry in arDupIDs, and replaces any value with lesser; ends up with earliest entry, which is then orphaned
        // limitation here is that it only--practically speaking--handles two instances; three or more would lose all but one
        $(xData.responseXML).SPFilterNode('z:row').each(function() {
          iDupID = parseInt($(this).attr("ows_ID"));
          arDupIDs.push(iDupID);
          if(arDupIDs.length > 1) {
            if(iDupID < arDupIDs[0]) arDupIDs[0] = iDupID;
          }
        });
        orphanGFSSRow(arDupIDs[0]);
      }	// if at least one
    }	//completefunc
  });  //SPServices
}	//findExisting()

The completefunc routine looks only at items that have duplicates. It determines the earliest item, then sends off its ID for marking as orphan.

SPServices is also used to lock an account (by placing the email address in a separate list), by checking for existence of the email address currently being processed in the Locked list. One function sets the lock, while notInHoldMe() queries the lock list.