Importing BUS100 Budgets via WebServices Part II

Ok, so I posted some code about submitting importing budgets last week. And now we have some improvements.

Since last week I have had some time to think about the script and some of the improvements. I didn’t really like the fact that the username and password were stored in the spreadsheet, so I’ve added code which will create a WPF Window which will prompt the user for a username and password.

Over and above this I now have a method that will determine the number of rows that we need to go down to rather than using a keyword such as End.

And last but not least we will write an OK or an exception per line that we submit in volume V on the spreadsheet so the user can easily determine which submissions haven’t worked correctly.

So to start with…

        // construct and display a window to ask a user for a username and password
        // which we will use for the web services
        private function passwordPrompt() : boolean 
        {
            var bResult : boolean = false;
            wndPasswordPrompt = new Window();

            if(null != wndPasswordPrompt)
            {
                var spVertical : StackPanel = new StackPanel();
                var spHorizontal1 : StackPanel = new StackPanel();
                var spHorizontal2 : StackPanel = new StackPanel();
                var spHorizontal3 : StackPanel = new StackPanel();
                var lbUsername : Label = new Label();
                var lbPassword : Label = new Label();
                var tbUsername : TextBox = new TextBox();
                var pbPassword : PasswordBox = new PasswordBox();
                var btnOk : Button = new Button();
                var btnCancel : Button = new Button();

                wndPasswordPrompt.Title = "Please enter you username and password";

                wndPasswordPrompt.Width = 280;
                wndPasswordPrompt.Height = 150;

                lbUsername.Content = "Username:";
                lbPassword.Content = "Password:";

                lbUsername.Width = 80;
                lbPassword.Width = 80;

                tbUsername.Width = 150;
                pbPassword.Width = 150;

                tbUsername.Margin = new Thickness(2);
                pbPassword.Margin = new Thickness(2);

                tbUsername.Name = "tbUsername";
                pbPassword.Name = "pbPassword";

                btnOk.Content = "Ok";
                btnCancel.Content = "Cancel";

                btnOk.IsDefault = true;
                btnOk.Margin = new Thickness(10);

                btnCancel.IsCancel = true;
                btnCancel.Margin = new Thickness(10);

                spHorizontal1.Children.Add(lbUsername);
                spHorizontal1.Children.Add(tbUsername);
                spHorizontal2.Children.Add(lbPassword);
                spHorizontal2.Children.Add(pbPassword);

                spHorizontal3.Children.Add(btnOk);
                spHorizontal3.Children.Add(btnCancel);
                spHorizontal3.HorizontalAlignment = System.Windows.HorizontalAlignment.Stretch;

                spVertical.Margin = new Thickness(5);
                spVertical.Orientation = System.Windows.Controls.Orientation.Vertical;
                spHorizontal1.Orientation = System.Windows.Controls.Orientation.Horizontal;
                spHorizontal2.Orientation = System.Windows.Controls.Orientation.Horizontal;
                spHorizontal3.Orientation = System.Windows.Controls.Orientation.Horizontal;

                wndPasswordPrompt.Content = spVertical;
                spVertical.Children.Add(spHorizontal1);
                spVertical.Children.Add(spHorizontal2);
                spVertical.Children.Add(spHorizontal3);
                
                btnOk.add_Click(OnbtnDialogOKClick);
                btnCancel.add_Click(OnbtnDialogCancelClick);

                if(true == (bResult = wndPasswordPrompt.ShowDialog()))
                {
                    gstrUsername = tbUsername.Text;
                    gstrPassword = pbPassword.Password;
                }
                else
                {
                    gstrUsername = null;
                    gstrPassword = null;
                }

                // remove the OK and cancel events
                btnOk.remove_Click(OnbtnDialogOKClick);
                btnCancel.remove_Click(OnbtnDialogCancelClick);

                wndPasswordPrompt = null;
            }
            return(bResult);
        }

        public function OnbtnDialogOKClick(sender: Object, e: RoutedEventArgs)
        {
            wndPasswordPrompt.DialogResult = true;
            wndPasswordPrompt.Close();
        }

        public function OnbtnDialogCancelClick(sender: Object, e: RoutedEventArgs)
        {
            wndPasswordPrompt.Close();
        }

The passwordPrompt() method will create a Window, in that window we add a vertical StackPanel which has three horizontal StackPanels, we then have a label and TextBox, then a label and PasswordBox before finally adding an OK and Cancel button. We need to also had the events for the OK and Cancel.

Then we display the window.

Determining the last row of the spreadsheet is the next enhancement.

giMaxRow = gwbWorkbook.ActiveSheet.Cells(gwbWorkbook.ActiveSheet.Rows.Count,1).End(-4162).Row;

This will basically go to the bottom most cell in column A and then go up until it encounters a non-empty cell and we’ll record that bottom cell. We will then only scan down to that cell. We use this method rather than using the End keyword.

Anyways, the code…

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_002
    {
		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;        // the row we will start from

        var gstrCompany : String = null;
        var gstrDivision : String = null;
        var gstrBudgetNumber : String = null;
        var gstrBudgetVersion : String = null;

        var gstrUsername : String = null;           // the username that we will use to log in to the WebServices
        var gstrPassword : String = null;           // the password that we will use to log in to the WebServices

        var btnImportBudget : Button = null;

        var wndPasswordPrompt : Window = null;      // this is the window we will display requesting the username and password

        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");    // description
            var wetx15 : String = retrieveFromActiveSheet("B4");    // name
            var webspr : String = retrieveFromActiveSheet("D4");    // start period
            // var wecrtp : String = "1"; //retrieveFromActiveSheet("B3");
            var wecrtp : String = retrieveFromActiveSheet("K4");    // exchange rate tp
            var wenpam : String = retrieveFromActiveSheet("G4");    // number of periods
            // var weupdb : String = retrieveFromActiveSheet("B3");
            var weupdb : String = retrieveFromActiveSheet("I4");    // should we update the budget
            var strUpdate : String = "1";

            // check to ensure a value for the update budget
            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

                    var sbSOAPString : StringBuilder = new StringBuilder(5000);     // we use a string builder which we will add to as we process our data
                    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++;        // increment our spreadsheet position
            }
        }

		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
				{
                    if(true == passwordPrompt())
                    {
					    gwbWorkbook = gexaApplication.Workbooks.Open(strFilename);  // open the spreadsheet
					    if(null != gwbWorkbook)
					    {
						    gwbWorkbook.Saved = true;               // get rid of those annoying save messages

                            giMaxRow = gwbWorkbook.ActiveSheet.Cells(gwbWorkbook.ActiveSheet.Rows.Count,1).End(-4162).Row;      // get the end row
                            
                            handleHeader();                         // kick off the creation of the header, this in turn will create the lines
                            gwbWorkbook.Save();                     // save the spreadsheet

                            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;
                                        gwbWorkbook.ActiveSheet.Range("V" + giCurrentRowBUS101_B1).Value = "OK";        // write an ok to the spreadsheet
                                    }
                                }
                            }
                            else
                            {
                                gdebug.WriteLine("No Response was returned");
                            }
                        }
                        catch(e)
                        {
                            gdebug.WriteLine("Exception: " + e.message);
                            gwbWorkbook.ActiveSheet.Range("V" + giCurrentRowBUS101_B1).Value = "Exception - " + e.message;  // write an exception to the spreadsheet
                        }
                    }
                }
            }
            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);
		}

        // construct and display a window to ask a user for a username and password
        // which we will use for the web services
        private function passwordPrompt() : boolean 
        {
            var bResult : boolean = false;
            wndPasswordPrompt = new Window();

            if(null != wndPasswordPrompt)
            {
                var spVertical : StackPanel = new StackPanel();
                var spHorizontal1 : StackPanel = new StackPanel();
                var spHorizontal2 : StackPanel = new StackPanel();
                var spHorizontal3 : StackPanel = new StackPanel();
                var lbUsername : Label = new Label();
                var lbPassword : Label = new Label();
                var tbUsername : TextBox = new TextBox();
                var pbPassword : PasswordBox = new PasswordBox();
                var btnOk : Button = new Button();
                var btnCancel : Button = new Button();

                wndPasswordPrompt.Title = "Please enter you username and password";

                wndPasswordPrompt.Width = 280;
                wndPasswordPrompt.Height = 150;

                lbUsername.Content = "Username:";
                lbPassword.Content = "Password:";

                lbUsername.Width = 80;
                lbPassword.Width = 80;

                tbUsername.Width = 150;
                pbPassword.Width = 150;

                tbUsername.Margin = new Thickness(2);
                pbPassword.Margin = new Thickness(2);

                tbUsername.Name = "tbUsername";
                pbPassword.Name = "pbPassword";

                btnOk.Content = "Ok";
                btnCancel.Content = "Cancel";

                btnOk.IsDefault = true;
                btnOk.Margin = new Thickness(10);

                btnCancel.IsCancel = true;
                btnCancel.Margin = new Thickness(10);

                spHorizontal1.Children.Add(lbUsername);
                spHorizontal1.Children.Add(tbUsername);
                spHorizontal2.Children.Add(lbPassword);
                spHorizontal2.Children.Add(pbPassword);

                spHorizontal3.Children.Add(btnOk);
                spHorizontal3.Children.Add(btnCancel);
                spHorizontal3.HorizontalAlignment = System.Windows.HorizontalAlignment.Stretch;

                spVertical.Margin = new Thickness(5);
                spVertical.Orientation = System.Windows.Controls.Orientation.Vertical;
                spHorizontal1.Orientation = System.Windows.Controls.Orientation.Horizontal;
                spHorizontal2.Orientation = System.Windows.Controls.Orientation.Horizontal;
                spHorizontal3.Orientation = System.Windows.Controls.Orientation.Horizontal;

                wndPasswordPrompt.Content = spVertical;
                spVertical.Children.Add(spHorizontal1);
                spVertical.Children.Add(spHorizontal2);
                spVertical.Children.Add(spHorizontal3);
                
                btnOk.add_Click(OnbtnDialogOKClick);
                btnCancel.add_Click(OnbtnDialogCancelClick);

                if(true == (bResult = wndPasswordPrompt.ShowDialog()))
                {
                    gstrUsername = tbUsername.Text;
                    gstrPassword = pbPassword.Password;
                }
                else
                {
                    gstrUsername = null;
                    gstrPassword = null;
                }

                // remove the OK and cancel events
                btnOk.remove_Click(OnbtnDialogOKClick);
                btnCancel.remove_Click(OnbtnDialogCancelClick);

                wndPasswordPrompt = null;
            }
            return(bResult);
        }

        public function OnbtnDialogOKClick(sender: Object, e: RoutedEventArgs)
        {
            wndPasswordPrompt.DialogResult = true;
            wndPasswordPrompt.Close();
        }

        public function OnbtnDialogCancelClick(sender: Object, e: RoutedEventArgs)
        {
            wndPasswordPrompt.Close();
        }
    }
}

This entry was posted in M3 / MoveX, Webservices and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s