public void setupDBTests()
        {
            //create sessions for each type of stored proc
            login = new AquariumLogin();

            eventSession = new Datalayer.Xero.Interresolve.AquariumEventManagement.SessionDetails();
            userSession = new Datalayer.Xero.Interresolve.AquariumUserManagement.SessionDetails();
            customSession = new Datalayer.Xero.Interresolve.AquariumCustomProcessing.SessionDetails();
        }
        /// <summary>
        /// pulls all inbound invoice numbers from Aq and assigns to 
        /// </summary>
        public int Get()
        {
            try{

                int noOfInvoicesPulled = 0;
                //gets specified invoices -- everything in this case

                AquariumLogin login = new AquariumLogin();
                login.LoginToAquarium();

                LoggedOnUserResult theUserResult = login.GetLoggedOnUserResult();

                Datalayer.Xero.Interresolve.AquariumUserManagement.SessionDetails sessionDetails = new Datalayer.Xero.Interresolve.AquariumUserManagement.SessionDetails(); //set this from the logon

                //set the USER sesson
                sessionDetails.SessionKey = theUserResult.SessionKey;
                sessionDetails.Username = theUserResult.Username;
                sessionDetails.ThirdPartySystemId = 29;

                //map a custom session
                Datalayer.Xero.Interresolve.AquariumCustomProcessing.SessionDetails customSessionDetails = new Datalayer.Xero.Interresolve.AquariumCustomProcessing.SessionDetails();
                customSessionDetails.SessionKey = sessionDetails.SessionKey;
                customSessionDetails.ThirdPartySystemId = 29;
                customSessionDetails.Username = theUserResult.Username;

                CustomProcessingSoapClient customSdk = new CustomProcessingSoapClient();

                DataTable InvoiceResults = new DataTable();
                InvoiceResults = InvoiceHelper.QueryInvoices(customSdk, customSessionDetails, InvoiceHelper.AquariumInvoiceTypeToQuery.InboundInvoice);

                InboundInvoices = (from data in InvoiceResults.AsEnumerable()
                                   select new InboundInvoice
                                   {
                                       TableRowID = Convert.ToInt64(data.Field<string>("TableRowID")),
                                       LeadID = Convert.ToInt32(data.Field<string>("LeadID")),
                                       ProvidersInvoiceNumber = Convert.ToInt32(data.Field<string>("Invoice Number")),
                                       InvoiceDate = Convert.ToDateTime(data.Field<string>("Invoice Date")),
                                   }).ToList();

                noOfInvoicesPulled = InboundInvoices.Count;
                return noOfInvoicesPulled;

            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public void GetProviderInvoiceNumbersTest()
        {
            setupDBTests();

            login.LoginToAquarium();

            customSession = login.SetUpCustomSessionForSDKAction();

            CustomProcessingSoapClient customSdk = new CustomProcessingSoapClient();

            ReportResult theResults = customSdk.RunCustomProc(customSession, "_C74_Xero_GetInboundInvoice_ProvidersInvoiceNumberAndLeadID", new string[0]);

            int Count = Convert.ToInt32(theResults.Data.Rows.Count());

            Assert.Greater(Count, 1);
        }
        static void Main(string[] args)
        {
            AquariumLogin login = new AquariumLogin();
            login.LoginToAquarium();

            LoggedOnUserResult theUserResult = login.GetLoggedOnUserResult();

            Datalayer.Xero.Interresolve.AquariumUserManagement.SessionDetails sessionDetails = new Datalayer.Xero.Interresolve.AquariumUserManagement.SessionDetails(); //set this from the logon

            //set the USER sesson
            sessionDetails.SessionKey = theUserResult.SessionKey;
            sessionDetails.Username = theUserResult.Username;
            sessionDetails.ThirdPartySystemId = 29;

            //map a custom session
            Datalayer.Xero.Interresolve.AquariumCustomProcessing.SessionDetails customSessionDetails = new Datalayer.Xero.Interresolve.AquariumCustomProcessing.SessionDetails();
            customSessionDetails.SessionKey = sessionDetails.SessionKey;
            customSessionDetails.ThirdPartySystemId = 29;
            customSessionDetails.Username = theUserResult.Username;

            //set the session details here
            CustomProcessingSoapClient customSdk = new CustomProcessingSoapClient();

            //now call the 'QueryInvoices Method
            OutboundInvoiceLineItemList theOutboundInvoices = new OutboundInvoiceLineItemList();

            //gets all invoices
            theOutboundInvoices.Get();

            //do something with them

            List<DataTable> InvoiceOptions = OutboundInvoiceLineItemList.GetInvoiceOptions();
            /*
            //string InvoiceType = "Sales Invoice";

            //map to xml

            var InvoicesThisMonth = (from invoice in theResults.AsEnumerable()
                                     .Where(x => x.Field<string>("WhenCreated").ToString().Substring(3, 5) == DateTime.Now.ToString("dd/MM/yy").Substring(3, 5)) //this month
                                     .Where(x => x.Field<string>("Invoice Type").ToString() = InvoiceType) //sales invoices
                                     .Where(x => x.Field<string>("Imported Into Sage").ToString().ToLower() == "false") //not been imported
                                     .Where( (x => x.Field<string>("Imported Into Sage").ToString() == "Claimant's InterResolve Scheme Fee"))
                                     select invoice
                                     );
            //post
               */
        }
        public static int Aquarium_GetLeadTypeIDFromLeadID(int LeadID)
        {
            try
            {
                int LeadTypeID = 0;

                //connect to sdk and run proc
                AquariumLogin login = new AquariumLogin();
                login.LoginToAquarium();

                LoggedOnUserResult theUserResult = login.GetLoggedOnUserResult();

                Datalayer.Xero.Interresolve.AquariumUserManagement.SessionDetails sessionDetails = new Datalayer.Xero.Interresolve.AquariumUserManagement.SessionDetails(); //set this from the logon

                //set the USER sesson
                sessionDetails.SessionKey = theUserResult.SessionKey;
                sessionDetails.Username = theUserResult.Username;
                sessionDetails.ThirdPartySystemId = 29;

                //map a custom session
                Datalayer.Xero.Interresolve.AquariumCustomProcessing.SessionDetails customSessionDetails = new Datalayer.Xero.Interresolve.AquariumCustomProcessing.SessionDetails();
                customSessionDetails.SessionKey = sessionDetails.SessionKey;
                customSessionDetails.ThirdPartySystemId = 29;
                customSessionDetails.Username = theUserResult.Username;

                CustomProcessingSoapClient customSdk = new CustomProcessingSoapClient();

                string[] ProcParam = new string[1];
                ProcParam[0] =  LeadID.ToString();

                MultiReportResult theResults = customSdk.RunCustomProcMultiResult(customSessionDetails, "_C74__GetLeadTypeIDByLeadID", ProcParam);

                //WHERE IS THE LEADTYPEID???
                DataTable TempHolder = SDKHelper.GetDataTableFromSdkResult(theResults.Data[0].Columns, theResults.Data[0].Rows);

                LeadTypeID = Convert.ToInt32(TempHolder.Rows[0].ItemArray[0]);

                return LeadTypeID;
            }

            catch (Exception ex)
            {
                throw ex;

            }
        }
        //methods to assist with the Xero importer
        public static int GetMatterIDByLeadID(int LeadID)
        {
            try
            {
                int MatterID = 0;

                AquariumLogin login = new AquariumLogin();
                login.LoginToAquarium();

                LoggedOnUserResult theUserResult = login.GetLoggedOnUserResult();

                Datalayer.Xero.Interresolve.AquariumUserManagement.SessionDetails sessionDetails = new Datalayer.Xero.Interresolve.AquariumUserManagement.SessionDetails(); //set this from the logon
                //set the USER sesson
                sessionDetails.SessionKey = theUserResult.SessionKey;
                sessionDetails.Username = theUserResult.Username;
                sessionDetails.ThirdPartySystemId = 29;

                //map a custom session
                Datalayer.Xero.Interresolve.AquariumCustomProcessing.SessionDetails customSessionDetails = new Datalayer.Xero.Interresolve.AquariumCustomProcessing.SessionDetails();
                customSessionDetails.SessionKey = sessionDetails.SessionKey;
                customSessionDetails.ThirdPartySystemId = 29;
                customSessionDetails.Username = theUserResult.Username;

                CustomProcessingSoapClient customSdk = new CustomProcessingSoapClient();

                //now put this invoice's fields into the proc and push the proc to Aquarium
                string ProcName = System.Configuration.ConfigurationManager.AppSettings["_C74__GetMatterIDByLeadID"].ToString();

                string[] ProcParam = new string[1];
                ProcParam[0] = LeadID.ToString();

                MultiReportResult theResults = customSdk.RunCustomProcMultiResult(customSessionDetails, ProcName, ProcParam);

                DataTable TempHolder = SDKHelper.GetDataTableFromSdkResult(theResults.Data[0].Columns, theResults.Data[0].Rows);

                MatterID = Convert.ToInt32(TempHolder.Rows[0].ItemArray[0]);

                return MatterID;
            }

            catch (Exception ex)
            {
                throw ex;

            }
        }
        public static bool XeroImportSuccessful_UpdateAquariumTableRow(Int64 TableRowID)
        {
            try
            {
                AquariumLogin login = new AquariumLogin();
                login.LoginToAquarium();

                LoggedOnUserResult theUserResult = login.GetLoggedOnUserResult();

                Datalayer.Xero.Interresolve.AquariumUserManagement.SessionDetails sessionDetails = new Datalayer.Xero.Interresolve.AquariumUserManagement.SessionDetails(); //set this from the logon

                //set the USER sesson
                sessionDetails.SessionKey = theUserResult.SessionKey;
                sessionDetails.Username = theUserResult.Username;
                sessionDetails.ThirdPartySystemId = 29;

                //map a custom session
                Datalayer.Xero.Interresolve.AquariumCustomProcessing.SessionDetails customSessionDetails = new Datalayer.Xero.Interresolve.AquariumCustomProcessing.SessionDetails();
                customSessionDetails.SessionKey = sessionDetails.SessionKey;
                customSessionDetails.ThirdPartySystemId = 29;
                customSessionDetails.Username = theUserResult.Username;

                CustomProcessingSoapClient customSdk = new CustomProcessingSoapClient();

                string[] ProcParam = new string[1];
                ProcParam[0] = TableRowID.ToString();

                MultiReportResult theResults = customSdk.RunCustomProcMultiResult(customSessionDetails, "_C74_Xero_UpdateSentToSage_XeroField", ProcParam);

                if (theResults.ResultInfo.ReturnCode == AquariumCustomProcessing.BasicCode.OK)
                { return true; }

                else { return false; }
            }

            catch (Exception ex)
            { throw ex; }
        }
        /// <summary>
        /// calls the stored proc to put this invoice into aquarium 
        /// </summary>
        /// <returns></returns>
        public bool ApplyInvoiceToAquarium()
        {
            try
            {

                //  bool result = false;
                //gets specified invoices -- everything in this case

                AquariumLogin login = new AquariumLogin();
                login.LoginToAquarium();

                LoggedOnUserResult theUserResult = login.GetLoggedOnUserResult();

                Datalayer.Xero.Interresolve.AquariumUserManagement.SessionDetails sessionDetails = new Datalayer.Xero.Interresolve.AquariumUserManagement.SessionDetails(); //set this from the logon
                //set the USER sesson
                sessionDetails.SessionKey = theUserResult.SessionKey;
                sessionDetails.Username = theUserResult.Username;
                sessionDetails.ThirdPartySystemId = 29;

                //map a custom session
                Datalayer.Xero.Interresolve.AquariumCustomProcessing.SessionDetails customSessionDetails = new Datalayer.Xero.Interresolve.AquariumCustomProcessing.SessionDetails();
                customSessionDetails.SessionKey = sessionDetails.SessionKey;
                customSessionDetails.ThirdPartySystemId = 29;
                customSessionDetails.Username = theUserResult.Username;

                CustomProcessingSoapClient customSdk = new CustomProcessingSoapClient();

                //now put this invoice's fields into the proc and push the proc to Aquarium
                string ProcName = System.Configuration.ConfigurationManager.AppSettings["_C74_Insert_Inbound_Invoice_Table_Record"].ToString();

                string[] ProcParm = new string[21];

                //map the elements of this invoice to the string array

                ProcParm[0] = this.LeadID.ToString();
                ProcParm[1] = this.MatterID.ToString();
                ProcParm[2] = this.LeadTypeID.ToString();
                ProcParm[3] = this.InvoiceDate.ToString();
                ProcParm[4] = this.InvoiceType.ToString();
                ProcParm[5] = this.ProvidersInvoiceNumber.ToString();

                ProcParm[6] = this.LineItem1Desc.ToString();
                ProcParm[7] = this.LineItem1Qty.ToString();
                ProcParm[8] = this.LineItem1Cost.ToString();

                ProcParm[9] = this.LineItem2Desc.ToString();
                ProcParm[10] = this.LineItem2Qty.ToString();
                ProcParm[11] = this.LineItem2Cost.ToString();

                ProcParm[12] = this.LineItem3Desc.ToString();
                ProcParm[13] = this.LineItem3Qty.ToString();
                ProcParm[14] = this.LineItem3Cost.ToString();

                ProcParm[15] = this.LineItem4Desc.ToString();
                ProcParm[16] = this.LineItem4Qty.ToString();
                ProcParm[17] = this.LineItem4Cost.ToString();

                ProcParm[18] = this.InvoiceVATAmount.ToString();
                ProcParm[19] = this.InvoiceSubtotal.ToString();
                ProcParm[20] = this.InvoiceTotalCost.ToString();

                //  DataTable InvoiceResults = new DataTable();
                //  InvoiceResults = InvoiceHelper.QueryInvoices(customSdk, customSessionDetails, InvoiceHelper.AquariumInvoiceTypeToQuery.OutboundInvoice);
                ReportResult result = new ReportResult();

                result = customSdk.RunCustomProc(customSessionDetails, ProcName, ProcParm);

                //has it worked?
                if (result.ResultInfo.ReturnCode == AquariumCustomProcessing.BasicCode.OK)
                {

                    //it's worked
                }
                else
                {
                    //it ain't
                }

            }
            catch (Exception ex)
            { throw ex; }

            return true;
        }
        //Get method - no params - will pull all invoices
        public int Get()
        {
            try
            {
                int noOfInvoicesPulled = 0;
                //gets specified invoices -- everything in this case

                AquariumLogin login = new AquariumLogin();
                login.LoginToAquarium();

                LoggedOnUserResult theUserResult = login.GetLoggedOnUserResult();

                Datalayer.Xero.Interresolve.AquariumUserManagement.SessionDetails sessionDetails = new Datalayer.Xero.Interresolve.AquariumUserManagement.SessionDetails(); //set this from the logon

                //set the USER sesson
                sessionDetails.SessionKey = theUserResult.SessionKey;
                sessionDetails.Username = theUserResult.Username;
                sessionDetails.ThirdPartySystemId = 29;

                //map a custom session
                Datalayer.Xero.Interresolve.AquariumCustomProcessing.SessionDetails customSessionDetails = new Datalayer.Xero.Interresolve.AquariumCustomProcessing.SessionDetails();
                customSessionDetails.SessionKey = sessionDetails.SessionKey;
                customSessionDetails.ThirdPartySystemId = 29;
                customSessionDetails.Username = theUserResult.Username;

                CustomProcessingSoapClient customSdk = new CustomProcessingSoapClient();

                DataTable InvoiceResults = new DataTable();
                InvoiceResults = InvoiceHelper.QueryInvoices(customSdk, customSessionDetails, InvoiceHelper.AquariumInvoiceTypeToQuery.OutboundInvoice);

                OutboundInvoiceLineItems = (from data in InvoiceResults.AsEnumerable()
                                    select new OutboundInvoiceLineItem
                                    {
                                        TableRowID = Convert.ToInt64(data.Field<string>("TableRowID")),
                                        InvoiceNumber = Convert.ToInt32(data.Field<string>("Invoice Number")),
                                        InvoiceDate = Convert.ToDateTime(data.Field<string>("Invoice Date")),
                                        InvoiceType = data.Field<string>("Invoice Type"),
                                        OriginalPurchaseInvoiceAmount = Convert.ToDecimal(data.Field<string>("Invoice Amount (ex VAT)")),
                                        OriginalPurchaseInvoiceVAT = Convert.ToDecimal(data.Field<string>("Invoice VAT")),
                                        OriginalPurchaseInvoiceTotal = Convert.ToDecimal(data.Field<string>("Invoice Total")),
                                        TransactionType = data.Field<string>("Transaction Type"),
                                        NominalAccount = data.Field<string>("Nominal Account"),
                                        NominalCostCentre = data.Field<string>("Nominal Cost Centre"),
                                        NominalDepartment = data.Field<string>("Nominal Dept"),
                                        SageCompanyName = data.Field<string>("Sage Comp Name"),
                                        TaxCode = Convert.ToInt32(data.Field<string>("Tax Code")),
                                        XeroSecRef = data.Field<string>("Sage 2nd Ref"),
                                        SageNarrative = data.Field<string>("Sage Narrative"),
                                        SentToSage = Convert.ToDateTime(data.Field<string>("Import Date")),
                                        CompanyRef = data.Field<string>("Company Ref"),
                                        Result = data.Field<string>("Result"),
                                        Message = data.Field<string>("Message"),
                                        NetInvoiceTotal = Convert.ToDecimal(data.Field<string>("Net Invoice Total")),
                                        YourRef = data.Field<string>("YourRef"),
                                        SetForImport = false

                                    }).ToList();

                noOfInvoicesPulled = OutboundInvoiceLineItems.Count;
                return noOfInvoicesPulled;

            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        //returns two 'out' parameters = the custom session details and the user session details, which can be used with
        //both SDKs
        public static bool LoginToAquariumAndCreateUserAndCustomSDKHelpersInSession(out Datalayer.Xero.Interresolve.AquariumUserManagement.SessionDetails userSession, Datalayer.Xero.Interresolve.AquariumCustomProcessing.SessionDetails customSession )
        {
            try
            {

                AquariumLogin login = new AquariumLogin();
                login.LoginToAquarium();

                LoggedOnUserResult theUserResult = login.GetLoggedOnUserResult();

                Datalayer.Xero.Interresolve.AquariumUserManagement.SessionDetails sessionDetails = new Datalayer.Xero.Interresolve.AquariumUserManagement.SessionDetails(); //set this from the logon

                //set the USER sesson
                sessionDetails.SessionKey = theUserResult.SessionKey;
                sessionDetails.Username = theUserResult.Username;
                sessionDetails.ThirdPartySystemId = 29;

                userSession = sessionDetails; //assign out param

                //map a custom session
                Datalayer.Xero.Interresolve.AquariumCustomProcessing.SessionDetails customSessionDetails = new Datalayer.Xero.Interresolve.AquariumCustomProcessing.SessionDetails();
                customSessionDetails.SessionKey = sessionDetails.SessionKey;
                customSessionDetails.ThirdPartySystemId = 29;
                customSessionDetails.Username = theUserResult.Username;

                customSession = customSessionDetails; //assign out param

                return true;

            } catch (Exception ex)
            {
                throw ex;
            }
        }
        //gets invoice options to be displayed to the user - returns as a list
        public static List<DataTable> GetInvoiceOptions()
        {
            List<DataTable> returnedResults = new List<DataTable>();

            try
            {
                AquariumLogin login = new AquariumLogin();
                login.LoginToAquarium();

                LoggedOnUserResult theUserResult = login.GetLoggedOnUserResult();

                Datalayer.Xero.Interresolve.AquariumUserManagement.SessionDetails sessionDetails = new Datalayer.Xero.Interresolve.AquariumUserManagement.SessionDetails(); //set this from the logon

                //set the USER sesson
                sessionDetails.SessionKey = theUserResult.SessionKey;
                sessionDetails.Username = theUserResult.Username;
                sessionDetails.ThirdPartySystemId = 29;

                //map a custom session
                Datalayer.Xero.Interresolve.AquariumCustomProcessing.SessionDetails customSessionDetails = new Datalayer.Xero.Interresolve.AquariumCustomProcessing.SessionDetails();
                customSessionDetails.SessionKey = sessionDetails.SessionKey;
                customSessionDetails.ThirdPartySystemId = 29;
                customSessionDetails.Username = theUserResult.Username;

                CustomProcessingSoapClient customSdk = new CustomProcessingSoapClient();

                MultiReportResult theResults = customSdk.RunCustomProcMultiResult(customSessionDetails, "_C74_Xero_GetAllSalesInvoiceSelectables", new string[0]);

                //now convert to a list

                for (int i = 0; i < theResults.Data.Count(); i++)
                {

                    returnedResults.Add(
                       SDKHelper.GetDataTableFromSdkResult(theResults.Data.ElementAt(i).Columns, theResults.Data.ElementAt(i).Rows)
                    );

                }

                ///convert into dropdownlistoptions

                return returnedResults;// = SDKHelper.GetDataTableFromSdkResult(theResults.Data.Columns, theResults.Data.Rows);

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }