In my last post I was talking about importing Budgets for BUS100 from a spreadsheet by purely using jscripts, and it was a miserable failure; well, not quite miserable, but sadly didn’t work in a way suitable to deploy to users.
It would probably be a better idea to break down the script and submit a CASE to Lawson but meh! Let us do something fun and have another play with WebServices.
I won’t go through detailing the creation of our web services, if you are unsure, then take a look at my previous post https://potatoit.wordpress.com/2011/04/18/calling-webservices-from-jscript-in-lso-%E2%80%93-a-rough-cut/
So, I created a couple of methods for BUS100, AddBudgetHeader and AddBudgetLines which correspond to the BUS100MI AddBudgetHeader and AddBudgetLines APIs
APIs from MRS001
And from the Lawson WebService Designer
Thankfully most of the work on this project has been done.
There are a few interesting things that I came across – the WebServices will produce an exception when if we pass an element without a value, so we dynamically build the request and the WebServices seem pretty reasonable from a performance perspective from limited testing.
There is definite room for improvement and optimisation. For example, we can submit multiple lines in one request and we can look at removing things like the password from the spreadsheet after we have completed submission.
It’s also worth noting that having the keyword End in column A will terminate the scanning of the spreadsheet (quicker than iterating through all the blank rows in Excel).
But enough of this jibber-jabber, we want to see code! 🙂
Spreadsheet Template Below
import System;
import System.IO;
import System.Net;
import System.Windows;
import System.Windows.Controls;
import MForms;
import System.Web.Services.Protocols;
import System.Xml;
import System.Text;
import Excel;
import System.Reflection;
package MForms.JScript
{
class BUS100_BudgetImport_001
{
var giicInstanceController : IInstanceController = null; // this is where we will store the IInstanceController to make it available to the rest of the class
var ggrdContentGrid : Grid = null; // this is the Grid that we get passed by the Init()
var gexaApplication = null; // Excel.Application object
var gwbWorkbook = null; // here we will store the Workbook object
var giStartRow : int = 7; // the starting row in the Spreadsheet
var giMaxRow : int = 10000; // the end row in the Spreadsheet
var giCurrentRowBUS101_B1 : int = 7;
var gstrCompany : String = null;
var gstrDivision : String = null;
var gstrBudgetNumber : String = null;
var gstrBudgetVersion : String = null;
var gstrUsername : String = null;
var gstrPassword : String = null;
var btnImportBudget : Button = null;
var gdebug;
public function Init(element: Object, args: Object, controller : Object, debug : Object)
{
gdebug = debug;
var content : Object = controller.RenderEngine.Content;
// add a button to retrieve the free caps of an item
btnImportBudget = new Button();
// the button will display "FreeCap"
btnImportBudget.Content = "Import";
// set the position of the button
Grid.SetColumnSpan(btnImportBudget, 10);
Grid.SetColumn(btnImportBudget, 11);
Grid.SetRow(btnImportBudget, 0);
// actually add the button to the panel
content.Children.Add(btnImportBudget);
// we want to know about the click and unloaded events, so we register our interest here
btnImportBudget.add_Click(OnbtnImportBudgetClick);
btnImportBudget.add_Unloaded(OnbtnImportBudgetClickUnloaded);
}
public function OnbtnImportBudgetClickUnloaded(sender: Object, e: RoutedEventArgs)
{
// remove the events that we are subscribed to
btnImportBudget.remove_Click(OnbtnImportBudgetClick);
btnImportBudget.remove_Unloaded(OnbtnImportBudgetClickUnloaded);
}
// this will submit the header to the webservice
// we statically set the Company & Division in our case
private function handleHeader()
{
// "WETX40" - Description
// "WETX15" - Name
// "WEBSPR" - Start Period
// "WECRTP" - Exchange Rate
// "WENPAM" - Number of Periods
// "WEUPDB" - Update Balance File (Checkbox)
gstrUsername = retrieveFromActiveSheet("B1"); // retrieve the username from the spreadsheet
gstrPassword = retrieveFromActiveSheet("B2"); // retrieve the password from the spreadsheet
var w1buno : String = retrieveFromActiveSheet("B3"); // budget number
var w1bver : String = retrieveFromActiveSheet("D3"); // version number
gstrCompany = "100"; // statically set our company for us
gstrDivision = "IFL"; // statically set out division
gstrBudgetNumber = w1buno;
gstrBudgetVersion = w1bver;
var wetx40 : String = retrieveFromActiveSheet("B4");
var wetx15 : String = retrieveFromActiveSheet("B4");
var webspr : String = retrieveFromActiveSheet("D4");
// var wecrtp : String = "1"; //retrieveFromActiveSheet("B3");
var wecrtp : String = retrieveFromActiveSheet("K4");
var wenpam : String = retrieveFromActiveSheet("G4");
// var weupdb : String = retrieveFromActiveSheet("B3");
var weupdb : String = retrieveFromActiveSheet("I4");
var strUpdate : String = "1";
if(true == doWeHaveAValueFromSpreadsheet(weupdb))
{
if(0 == String.Compare(weupdb, "true", true))
{
strUpdate = "1";
}
else if(0 == String.Compare(weupdb, "yes", true))
{
strUpdate = "0";
}
}
doRequest(addBudgetHeader(wetx40, wetx15, webspr, wecrtp, wenpam, strUpdate)); // send the request
handleLines();
}
// this function will go through the spreadsheet looking for values to submit
// to the SOAP interface
private function handleLines()
{
while(giCurrentRowBUS101_B1 <= giMaxRow) // we will loop through until we hit MaxRows
{
var w1ait1 : String = retrieveFromActiveSheet("A" + giCurrentRowBUS101_B1); // retrieve the first dimension from the spreadsheet
if(true == doWeHaveAValueFromSpreadsheet(w1ait1)) // if the dimension doesn't exist then there isn't any point retrieving any other dimensions
{
if(0 == String.Compare("End", w1ait1, true)) // if we come across End we should break from our line loop
{
break;
}
var w1ait2 : String = retrieveFromActiveSheet("B" + giCurrentRowBUS101_B1); // dimension 1
var w1ait3 : String = retrieveFromActiveSheet("C" + giCurrentRowBUS101_B1); // dimension 2
var w1ait4 : String = retrieveFromActiveSheet("D" + giCurrentRowBUS101_B1); // dimension 4
var w1ait5 : String = retrieveFromActiveSheet("E" + giCurrentRowBUS101_B1); // dimension 5
var w1ait6 : String = retrieveFromActiveSheet("F" + giCurrentRowBUS101_B1); // dimension 6
var w1ait7 : String = retrieveFromActiveSheet("G" + giCurrentRowBUS101_B1); // dimension 7
var w1cucd : String = retrieveFromActiveSheet("H" + giCurrentRowBUS101_B1); //
var w1amtn : String = retrieveFromActiveSheet("I" + giCurrentRowBUS101_B1); //
var strPeriod1 : String = retrieveFromActiveSheet("J" + giCurrentRowBUS101_B1); // value for period 1
var strPeriod2 : String = retrieveFromActiveSheet("K" + giCurrentRowBUS101_B1); // value for period 2
var strPeriod3 : String = retrieveFromActiveSheet("L" + giCurrentRowBUS101_B1); // value for period 3
var strPeriod4 : String = retrieveFromActiveSheet("M" + giCurrentRowBUS101_B1); // value for period 4
var strPeriod5 : String = retrieveFromActiveSheet("N" + giCurrentRowBUS101_B1); // value for period 5
var strPeriod6 : String = retrieveFromActiveSheet("O" + giCurrentRowBUS101_B1); // value for period 6
var strPeriod7 : String = retrieveFromActiveSheet("P" + giCurrentRowBUS101_B1); // value for period 7
var strPeriod8 : String = retrieveFromActiveSheet("Q" + giCurrentRowBUS101_B1); // value for period 8
var strPeriod9 : String = retrieveFromActiveSheet("R" + giCurrentRowBUS101_B1); // value for period 9
var strPeriod10 : String = retrieveFromActiveSheet("S" + giCurrentRowBUS101_B1); // value for period 10
var strPeriod11 : String = retrieveFromActiveSheet("T" + giCurrentRowBUS101_B1); // value for period 11
var strPeriod12 : String = retrieveFromActiveSheet("U" + giCurrentRowBUS101_B1); // value for period 12
// if(true == doWeHaveAValueFromSpreadsheet(w1ait1))
// {
var sbSOAPString : StringBuilder = new StringBuilder();
if(null != sbSOAPString)
{
sbSOAPString.Append('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:mws2="http://mws.intentia.net/mws2" xmlns:add="http://www.indfish.co.nz/BUS100/AddBudgetLines"><soapenv:Header><mws2:mws><mws2:user>' + gstrUsername + '</mws2:user><mws2:password>' + gstrPassword + '</mws2:password><mws2:company>' + gstrCompany + '</mws2:company><mws2:division>' + gstrDivision + '</mws2:division></mws2:mws></soapenv:Header><soapenv:Body><add:AddBudgetLines maxRecords="100"><!--Zero or more repetitions:--><add:AddBudgetLinesItem>');
buildSOAP(sbSOAPString, "add:Company", gstrCompany);
buildSOAP(sbSOAPString, "add:Division", gstrDivision);
buildSOAP(sbSOAPString, "add:BudgetNumber", gstrBudgetNumber);
buildSOAP(sbSOAPString, "add:BudgetVersion", gstrBudgetVersion);
buildSOAP(sbSOAPString, "add:AccountingDimension1", w1ait1);
buildSOAP(sbSOAPString, "add:AccountingDimension2", w1ait2);
buildSOAP(sbSOAPString, "add:AccountingDimension3", w1ait3);
buildSOAP(sbSOAPString, "add:AccountingDimension4", w1ait4);
buildSOAP(sbSOAPString, "add:AccountingDimension5", w1ait5);
buildSOAP(sbSOAPString, "add:AccountingDimension6", w1ait6);
buildSOAP(sbSOAPString, "add:AccountingDimension7", w1ait7);
buildSOAP(sbSOAPString, "add:Currency", w1cucd);
buildSOAP(sbSOAPString, "add:Curve", null);
buildSOAP(sbSOAPString, "add:TotalForeignCurrencyAmountBudget", null);
buildSOAP(sbSOAPString, "add:TotalBudgetQuantity", null);
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget1", convertTo2DecimalPlaces(strPeriod1));
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget2", convertTo2DecimalPlaces(strPeriod2));
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget3", convertTo2DecimalPlaces(strPeriod3));
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget4", convertTo2DecimalPlaces(strPeriod4));
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget5", convertTo2DecimalPlaces(strPeriod5));
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget6", convertTo2DecimalPlaces(strPeriod6));
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget7", convertTo2DecimalPlaces(strPeriod7));
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget8", convertTo2DecimalPlaces(strPeriod8));
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget9", convertTo2DecimalPlaces(strPeriod9));
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget10", convertTo2DecimalPlaces(strPeriod10));
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget11", convertTo2DecimalPlaces(strPeriod11));
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget12", convertTo2DecimalPlaces(strPeriod12));
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget13", null);
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget14", null);
buildSOAP(sbSOAPString, "add:ForeignCurrencyAmountBudget15", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity1", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity2", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity3", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity4", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity5", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity6", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity7", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity8", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity9", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity10", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity11", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity12", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity13", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity14", null);
buildSOAP(sbSOAPString, "add:BudgetQuantity15", null);
sbSOAPString.Append('</add:AddBudgetLinesItem></add:AddBudgetLines></soapenv:Body></soapenv:Envelope>');
doRequest(sbSOAPString.ToString());
}
else
{
return(null);
}
}
giCurrentRowBUS101_B1++;
}
}
public function OnbtnImportBudgetClick(sender: Object, e: RoutedEventArgs)
{
try
{
// here we do some initialisation of Excel
InitialiseExcel();
var strFilename : String = retrieveImportFile(); // retrieve the filename of the Excel spreadsheet to open
if((null != strFilename) && (null != gexaApplication)) // ensure that not only do we have a filename, but we also managed to initialise Excel
{
gwbWorkbook = gexaApplication.Workbooks.Open(strFilename); // open the spreadsheet
if(null != gwbWorkbook)
{
gwbWorkbook.Saved = true; // get rid of those annoying save messages
handleHeader(); // kick off the creation of the header, this in turn will create the lines
MessageBox.Show("Finished Import"); // provide some feedback that we are finished
}
else MessageBox.Show("Failed to Open Workbook");
}
else MessageBox.Show("Filename or Excel doesn't exist: " + strFilename);
}
catch(exException)
{
MessageBox.Show("Error: " + exException.description);
}
CleanUp();
}
// send the WebService Request
public function doRequest(astrXMLRequest : String)
{
// we are going to use the HttpWebRequest object
// http://msdn.microsoft.com/en-us/library/system.net.httpwebrequest.aspx
// and we want to connect to the ItemFreeCaps2 service
var hwrRequest : HttpWebRequest = WebRequest.Create("http://wlmx02.indfish.co.nz:12000/lwstest/services/BUS100");
// ensure we actually managed to create something
if(null != hwrRequest)
{
// here we're defining our actions and content types
hwrRequest.Headers.Add("SOAPAction","\"\"");
hwrRequest.ContentType = "text/xml;charset=\"utf-8\"";
hwrRequest.Method = "POST";
hwrRequest.Accept = "text/xml";
hwrRequest.Proxy = GlobalProxySelection.GetEmptyWebProxy();
// we are going to use a stream to write out our request (and also read it later)
var strmStream : Stream = hwrRequest.GetRequestStream();
if(null != strmStream)
{
// SOAP is basically just xml, so we are going to use the XML framework
// to make our lives easier.
// Create an XML Document
var xmdDocument : XmlDocument = new XmlDocument();
if(null != xmdDocument)
{
// we then add the String to our XML document
xmdDocument.LoadXml(astrXMLRequest);
// the save of the document to our stream actually sends the request
// to the server
xmdDocument.Save(strmStream);
// close our stream
strmStream.Close();
// this section is wrapped in a try .. catch()
// block because I had a lot of problems getting
// this running initially.
try
{
// now we want to get a response
var wresponse : WebResponse = hwrRequest.GetResponse();
if(null != wresponse)
{
// we like using streams, so get the stream
// connection
strmStream = wresponse.GetResponseStream();
if(null != strmStream)
{
// create a streamreader to retrieve the data
var srStreamReader : StreamReader = new StreamReader(strmStream);
if(null != srStreamReader)
{
// and finally we read the data
var strXML : String = srStreamReader.ReadToEnd();
// close the response
wresponse.Close();
// close the stream reader
srStreamReader.Close();
// lets load the xml we read
xmdDocument.LoadXml(strXML);
var xel = xmdDocument.DocumentElement;
}
}
}
else
{
gdebug.WriteLine("No Response was returned");
}
}
catch(e)
{
gdebug.WriteLine("Exception: " + e.message);
}
}
}
}
else
{
gdebug.WriteLine("doRequest() unable to create");
}
}
// findNode will recurse through the XmlNodes until if finds
// and Element with astrElementName as a name, it will
// then return that node.
public function findNode(axmnNode : XmlNode, astrElementName : String)
{
var result : XmlNode = null;
if(null != axmnNode)
{
if(String.Compare(astrElementName, axmnNode.Name, true) == 0)
{
result = axmnNode;
}
else
{
if(true == axmnNode.HasChildNodes)
{
result = findNode(axmnNode.FirstChild, astrElementName);
}
if(null != axmnNode.NextSibling)
{
result = findNode(axmnNode.NextSibling, astrElementName);
}
}
}
return result;
}
// here we will create the budget header SOAP request
private function addBudgetHeader(astrDescription : String, astrName : String, astrStartPeriod : String, astrExchangeRate : String, astrNumberPeriods : String, astrUpdateBudget : String) : String
{
// because we have to build the request on the fly we will use a StringBuilder
// it helps prevent memory fragmentation
var sbSOAPString : StringBuilder = new StringBuilder(5000);
if(null != sbSOAPString)
{
sbSOAPString.Append('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:mws2="http://mws.intentia.net/mws2" xmlns:add="http://www.indfish.co.nz/BUS100/AddBudgetHeader"><soapenv:Header><mws2:mws><mws2:user>' + gstrUsername + '</mws2:user><mws2:password>' + gstrPassword + '</mws2:password><mws2:company>' + gstrCompany + '</mws2:company><mws2:division>' + gstrDivision + '</mws2:division></mws2:mws></soapenv:Header><soapenv:Body><add:AddBudgetHeader maxRecords="100"><add:AddBudgetHeaderItem>');
buildSOAP(sbSOAPString, "add:Company", gstrCompany);
buildSOAP(sbSOAPString, "add:Division", gstrDivision);
buildSOAP(sbSOAPString, "add:BudgetNumber", gstrBudgetNumber);
buildSOAP(sbSOAPString, "add:BudgetVersion", gstrBudgetVersion);
buildSOAP(sbSOAPString, "add:Description", "Test");
buildSOAP(sbSOAPString, "add:Name", "Test");
buildSOAP(sbSOAPString, "add:StartPeriodBudget", astrStartPeriod);
buildSOAP(sbSOAPString, "add:ExchangeRateType", astrExchangeRate);
buildSOAP(sbSOAPString, "add:NumberOfPeriods", astrNumberPeriods);
buildSOAP(sbSOAPString, "add:RoundingoffCategory", null);
buildSOAP(sbSOAPString, "add:UpdateBalanceFile", astrUpdateBudget);
buildSOAP(sbSOAPString, "add:AllocationTemplate", null);
buildSOAP(sbSOAPString, "add:ObjectAccessGroup", null);
sbSOAPString.Append('</add:AddBudgetHeaderItem></add:AddBudgetHeader></soapenv:Body></soapenv:Envelope>');
return(sbSOAPString.ToString());
}
else
{
return(null);
}
}
// this is a little function to encapsulate a value within an XML element
// if we don't have a value then the element isn't added - if we add the element with the
// empty value we will get an exception back from the webservice
private function buildSOAP(atbFinalString : StringBuilder, astrElement : String, astrValue : String)
{
if(false == String.IsNullOrEmpty(astrValue))
{
atbFinalString.Append("<" + astrElement + ">" + astrValue + "</" + astrElement + ">");
}
}
// retrieve some data from the active spreadsheet
// at a specific location
private function retrieveFromActiveSheet(astrPosition : String)
{
var strValue : String = gwbWorkbook.ActiveSheet.Range(astrPosition).Value;
if(true == String.IsNullOrEmpty(strValue))
{
strValue = "";
}
else if(0 == String.Compare(strValue, "undefined"))
{
strValue = "";
}
return(strValue);
}
// display an OpenFileDialog box
// and extract the result
private function retrieveImportFile()
{
var result : String = null;
var ofdFile = new System.Windows.Forms.OpenFileDialog(); // we have to use the forms OpenFileDialog unfortunately
if(null != ofdFile)
{
ofdFile.Multiselect = false;
ofdFile.Filter = "Excel Files (*.xls;*.xlsx)|*.xls;*.xlsx|All Files (*.*)|*.*"; // filter on xls or xlsx files only
if(true == ofdFile.ShowDialog())
{
result = ofdFile.FileName;
}
}
return(result);
}
// our central cleanup function
private function CleanUp()
{
CleanUpExcel();
}
// our Import button is being unloaded, now's a good time to clean
// everything up
private function OnImportFromExcelUnloaded(sender : Object, e : RoutedEventArgs)
{
if(null != btnImportBudget)
{
btnImportBudget.remove_Click(OnbtnImportBudgetClick);
btnImportBudget.remove_Unloaded(OnbtnImportBudgetClickUnloaded);
}
}
private function convertTo2DecimalPlaces(astrValue : String)
{
var strResult : String = "";
if(false == String.IsNullOrEmpty(astrValue))
{
var dblTemp : double = astrValue;
strResult = dblTemp.ToString("#.##");
}
return(strResult);
}
// check to ensure that we have a value when we extract from the
// spreadsheet
private function doWeHaveAValueFromSpreadsheet(astrValue : String)
{
var bResult : boolean = false;
if(false == String.IsNullOrEmpty(astrValue))
{
if(0 != String.Compare(astrValue, "undefined"))
{
bResult = true;
}
}
return(bResult);
}
// close Excel and any workbooks
private function CleanUpExcel()
{
// check to ensure we have a Workbook object
// before we attempt to close the workbook
if(null != gwbWorkbook)
{
gwbWorkbook.Close();
gwbWorkbook = null;
}
// make sure we have actually created
// the Excel Application object before
// we Quit
if(null != gexaApplication)
{
gexaApplication.Quit();
gexaApplication = null;
}
}
// Initialise Excel, essentially start the Excel Application and set it to visible
private function InitialiseExcel()
{
var result = null;
try
{
gexaApplication = new ActiveXObject("Excel.Application");
gexaApplication.Visible = true;
result = gexaApplication;
}
catch(exException)
{
MessageBox.Show("Error: " + exException.Message + Environment.NewLine + exException.StackTrace);
}
return(result);
}
}
}
Have fun! 🙂
Potato IT 