/// <summary> /// For the generation the whole INVOICE, no to process lines provided, all received from database /// </summary> /// <param name="DocumnetNumber">The Document number from which this document will be created</param> /// <param name="iInstruction">1 = Invoice and convert Future Sales Order to Active order, 2 = Invoice and convert Active order to standing order, 3 = Invoice and close order (remove all lines)</param> /// <param name="dtInvoiceUpto">for lease items the last day of invoice: invoice the lease item up-to date</param> /// <param name="dtInvoiceDate">The actual invocie date, this will put the invoice in a specific financial period</param> /// <param name="aItemTypeAction">0 - Don't Invocie, 1 - Invoice; [0] = Invoice Lease Items, [1] = Invoice Returnable consumables, [2] = Invoice Consumables</param> /// <param name="frmMonth">The scripting form</param> /// <returns></returns> public string CreateCustomerDocument(string DocumnetNumber, int iInstruction, DateTime dtInvoiceUpto, DateTime dtInvoiceDate, string[] aItemTypeAction, Solsage_Process_Management_System.Forms.MonthEndProcessings frmMonth, string sMonthEndType) { return CreateDocumentLines(DocumnetNumber, iInstruction, dtInvoiceUpto, dtInvoiceDate, aItemTypeAction, frmMonth, sMonthEndType); }
private string CreateDocumentLines(string DocumnetNumber, int iInstruction, DateTime dtInvoiceUpto, DateTime dtInvoiceDate, string[] aItemTypeAction, Solsage_Process_Management_System.Forms.MonthEndProcessings frmMonth, string sMonthEndType) { bZeroLine = false; string sSql = ""; using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sPastelConnStr)) { //Get Current Period end and start dates string[] aPeriodStartEnd = GetPeriodStartEnd(); DateTime dtPeriodStart = new DateTime(Convert.ToInt32(aPeriodStartEnd[0].Substring(6, 4)), Convert.ToInt32(aPeriodStartEnd[0].Substring(0, 2)), Convert.ToInt32(aPeriodStartEnd[0].Substring(3, 2)), 0, 0, 0); DateTime dtPeriodEnd = new DateTime(Convert.ToInt32(aPeriodStartEnd[1].Substring(6, 4)), Convert.ToInt32(aPeriodStartEnd[1].Substring(0, 2)), Convert.ToInt32(aPeriodStartEnd[1].Substring(3, 2)), 0, 0, 0); // Needed to get Multiplier for Downtime string sSalesOrderNumber = DocumnetNumber; //Create Header string[] aHeader = new string[2]; aHeader = buildHeader(DocumnetNumber, dtInvoiceDate, "", "", ""); string sHeader = aHeader[0]; string sCustomer = aHeader[1]; #region GET rounding information from Companysetup using (PsqlConnection oConnCS = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr)) { oConnCS.Open(); sSql = "Select RoundingAccount, RoundingAccountName, RoundingMethod, RoundingToNearest from SOLCS"; PsqlDataReader rdCSReader = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConnCS).ExecuteReader(); while (rdCSReader.Read()) { sRoundingType = rdCSReader["RoundingMethod"].ToString().Trim(); sRoundingAccount = rdCSReader["RoundingAccount"].ToString().Trim(); sRoundingAccountName = rdCSReader["RoundingAccountName"].ToString().Trim(); iRoundingTo = Convert.ToInt32(rdCSReader["RoundingToNearest"].ToString().Trim()); } rdCSReader.Close(); oConnCS.Dispose(); } #endregion //Creat Document Lines string sLine = ""; string[] aSalesLine = new string[0]; string[] aBuildSalesLine = new string[13]; string[] aSalesLineMessage = new string[0]; string[] aSOLHLupdate = new string[0]; string[] aSOLHLupdateNeedsConfrim = new string[0]; string sLineMessage = ""; string[] aSalesLineNeedsConfirm = new string[0]; string sResultString = ""; string sLineNeedsConfirm = ""; double iLineQuantity = 0; string sDescription = "No item lines to process"; string sErrorNumber = "100"; dDocumentTotal = 0; string sSiteName = ""; string sMarketer = ""; string sCategory = ""; string sCommissionType = ""; oConn.Open(); // get SiteName for specific Salesorder using (PsqlConnection oConnHH = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr)) { sSql = "Select SiteName, Marketer, MarketingCategory, CommissionType from SOLHH where DocNumber = '" + DocumnetNumber + "'"; PsqlDataReader rdReaderHH = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConnHH).ExecuteReader(); while (rdReaderHH.Read()) { sSiteName = rdReaderHH["SiteName"].ToString().Trim(); sMarketer = rdReaderHH[1].ToString().Trim(); sCategory = rdReaderHH[2].ToString().Trim(); sCommissionType = rdReaderHH[3].ToString().Trim(); } rdReaderHH.Dispose(); oConnHH.Dispose(); } //LL KH 11/03/2010 sSql = "select CostPrice,Qty,Unitprice,InclusivePrice,UnitUsed, TaxType,HistoryLines.DiscountType HLDiscountType,DiscountPercentage,HistoryLines.ItemCode HLItemCode,HistoryLines.Description HLDescription,SearchType,MultiStore,CostCode, UserDefNum01, LinkNum, CONCAT(LTRIM(RTRIM(HistoryLines.ItemCode)),LTRIM(RTRIM(LinkNum))) NumberCode from HistoryLines "; sSql += "left join Inventory on Inventory.ItemCode = HistoryLines.ItemCode "; sSql += "where DocumentNumber = '" + DocumnetNumber + "' and HistoryLines.DocumentType in (102,2) "; DataSet dsItemsToInvoice = Solsage_Process_Management_System.Classes.Connect.getDataSet(sSql, "ItemsToInvoice", oConn); foreach (DataRow drRow in dsItemsToInvoice.Tables["ItemsToInvoice"].Rows) { string sItemCode = drRow["HLItemCode"].ToString().Trim(); string sNumberCode = drRow["NumberCode"].ToString().Trim(); string sUserDefNum01 = drRow["UserDefNum01"].ToString(); double dLineQuantity = Convert.ToDouble(drRow["Qty"].ToString()); string sLineDescription = drRow["HLDescription"].ToString().Trim() + "~" + drRow["LinkNum"].ToString().Trim();//add original link num in for syncing wiht SOLIL string sSearchType = drRow["SearchType"].ToString().Trim(); string sLineLinkNum = drRow["LinkNum"].ToString().Trim(); double dMultiplier = 0; ////Build Base sales line aBuildSalesLine[0] = drRow["CostPrice"].ToString().Trim(); //Cost Price aBuildSalesLine[1] = "0"; //Line Quantity aBuildSalesLine[2] = drRow["UnitPrice"].ToString().Trim(); //Exclusive Price Per Unit aBuildSalesLine[3] = drRow["InclusivePrice"].ToString().Trim(); //Inclusive Price Per Unit aBuildSalesLine[4] = drRow["UnitUsed"].ToString().Trim(); //Unit aBuildSalesLine[5] = Convert.ToInt32(drRow["TaxType"]).ToString("00").Trim(); //Tax Type aBuildSalesLine[6] = drRow["HLDiscountType"].ToString().Trim(); //Discount Type aBuildSalesLine[7] = drRow["DiscountPercentage"].ToString().Trim(); //Discount % aBuildSalesLine[8] = drRow["HLItemCode"].ToString().Trim(); //Code aBuildSalesLine[9] = sLineDescription; //Description aBuildSalesLine[10] = drRow["SearchType"].ToString().Trim(); //Line Type aBuildSalesLine[11] = drRow["MultiStore"].ToString().Trim(); //Store aBuildSalesLine[12] = drRow["CostCode"].ToString().Trim(); //CostCode iLineQuantity = 0; sCheckIfInvoicedBefore = ""; #region IS THIS AN ASSET //Test if item is an Asset if (sUserDefNum01 == "1") { //Get line item detail from SOLHL string[] aLineDateData = getLineDateData(DocumnetNumber, sItemCode, sNumberCode); if ((aItemTypeAction[0] == "1" && "0" == aLineDateData[3]) || aLineDateData[3] == "1") //Invoice Lease Items? { //ERROR HANDLING -- MESSAGE THAT THERE WAS AN ERROR -- AJD PHALABORWA 30 September 2009 if (aLineDateData[5] == null)//Line Item not found -- Can't invoice this order - Return Error Message -- Probable cause LinkNumbers not in sync { return ("Error: Cannot invoice order " + DocumnetNumber); } dMultiplier = Convert.ToDouble(aLineDateData[5].ToString()); if ((aItemTypeAction[0] == "1" && "0" == aLineDateData[3]) || aLineDateData[3] == "1") { //Calculate amount of days that should be invoiced if (aLineDateData[0].Length < 6)//not a correct date { return ("Error: Cannot invoice order " + DocumnetNumber); } DateTime theStartDate = new DateTime(Convert.ToInt32(aLineDateData[0].ToString().Substring(6, 4)), Convert.ToInt32(aLineDateData[0].ToString().Substring(3, 2)), Convert.ToInt32(aLineDateData[0].ToString().Substring(0, 2))); DateTime theReturnDate = new DateTime(Convert.ToInt32(aLineDateData[1].ToString().Substring(6, 4)), Convert.ToInt32(aLineDateData[1].ToString().Substring(3, 2)), Convert.ToInt32(aLineDateData[1].ToString().Substring(0, 2))); bool bAlreadyAdded = false; //for remove lines so it wont be added twice sCheckIfInvoicedBefore = aLineDateData[2].ToString(); if (aLineDateData[2].ToString() != "") //Last invoice date { DateTime theLastInvoicedDate = Convert.ToDateTime(aLineDateData[2].ToString()); DateTime dtInvoiceToDate = new DateTime(); #region LAST INVOICE DATE IS GREATER THAN INVOICE UPTO DATE string sMessage = ""; if (aLineDateData[3] != "1") { dtInvoiceToDate = dtInvoiceUpto; sMessage = "Last invoice date is greater than invoice upto date"; } else if (aLineDateData[3] == "1") { dtInvoiceToDate = theReturnDate; sMessage = "Last invoice date is greater than the returned Items return date"; } if (theLastInvoicedDate >= dtInvoiceToDate) { sDescription = sMessage; sErrorNumber = "101"; //MessageBox.Show("geen invoice vir die lyn"); if (iInstruction == 3) //close order { Array.Resize<string>(ref aSalesLineLineRemove, aSalesLineLineRemove.Length + 1); string sSOLHLDelQury = BuildDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); string sPastelLineRemove = BuildHistLineDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); aSalesLineLineRemove[aSalesLineLineRemove.Length - 1] = sSOLHLDelQury + "^" + sPastelLineRemove + "^" + sItemCode; } } #endregion #region WAS LINE INVOICED THIS FINANCIAL PERIOD //Was sales line invoiced in this month (Financial Period) else if (theLastInvoicedDate >= dtPeriodStart && theLastInvoicedDate <= dtPeriodEnd) { if (aLineDateData[3] == "1" || sMonthEndType == "ValuesFromSalesOrder") //Use Values from Sales order NO Calculation { iLineQuantity = Convert.ToDouble(aLineDateData[6]) * dMultiplier; } else { TimeSpan tsBetween = new TimeSpan(); //MessageBox.Show("Line has been invoiced in current Month"); tsBetween = dtInvoiceToDate - theLastInvoicedDate; int iDaysBetween = tsBetween.Days; int iSaturdays = 0; int iSundays = 0; if (sSaturday == "1") { iSaturdays = CountDays(DayOfWeek.Saturday, theStartDate, dtInvoiceToDate); } if (sSunday == "1") { iSundays = CountDays(DayOfWeek.Sunday, theStartDate, dtInvoiceToDate); } //Check if calculation rule must be used if (aLineDateData[7].ToString().Trim() == "") { iLineQuantity = Convert.ToDouble((iDaysBetween - iSaturdays - iSundays) * dMultiplier); } else { int iDateStartDay = Convert.ToInt16(theStartDate.Day); int iDateEndDay = Convert.ToInt16(dtInvoiceToDate.Day); int iReturnQty = iDaysBetween; string sCalculationQty = iReturnQty.ToString(); bool bLineReturned = false; if (aLineDateData[3] == "1") bLineReturned = true; string sReturnQty = Functions.CalculateQty_UnitRule(sCalculationQty, aLineDateData[7].ToString().Trim(), bLineReturned, theStartDate, dtInvoiceToDate); double dReturnQty = Convert.ToDouble(sReturnQty); iLineQuantity = Convert.ToDouble(dReturnQty * dMultiplier); } } if (iLineQuantity > 0 || Global.bGenerateZeroInvoice) // make sure quantity is greater than 0 { //build array with lines items that has been invoiced during this month sLineMessage = sItemCode + "|"; sLineMessage += aLineDateData[2].ToString() + "|"; Array.Resize<string>(ref aSalesLineMessage, aSalesLineMessage.Length + 1); aSalesLineMessage[aSalesLineMessage.Length - 1] = sLineMessage; //Biuld the invoice line in case user wants to invoice these lines sLineNeedsConfirm = buildaSalesLine(aBuildSalesLine, iLineQuantity, 0); string[] aReturn = processDowntimeQuery(oConn, DocumnetNumber, sItemCode, "Confirm", theLastInvoicedDate, dtInvoiceToDate, sLineNeedsConfirm, iLineQuantity, sNumberCode, aLineDateData[3]); if (aReturn.Length > 0) { for (int i = 0; i < aReturn.Length; i++) { Array.Resize<string>(ref aSalesLineNeedsConfirm, aSalesLineNeedsConfirm.Length + 1); aSalesLineNeedsConfirm[aSalesLineNeedsConfirm.Length - 1] = aReturn[i].ToString(); } } else { Array.Resize<string>(ref aSalesLineNeedsConfirm, aSalesLineNeedsConfirm.Length + 1); aSalesLineNeedsConfirm[aSalesLineNeedsConfirm.Length - 1] = sLineNeedsConfirm; //Create Text line with date from and to invoiced //string sNoteDescription = buildLeaseItemDescriptionNote(dtInvoiceToDate, theLastInvoicedDate, iLineQuantity); //sLineNeedsConfirm = buildaSalesLineNote(sItemCode, sNoteDescription); //Array.Resize<string>(ref aSalesLineNeedsConfirm, aSalesLineNeedsConfirm.Length + 1); //aSalesLineNeedsConfirm[aSalesLineNeedsConfirm.Length - 1] = sLineNeedsConfirm; } if (iInstruction != 3) { Array.Resize<string>(ref aSOLHLupdateNeedsConfrim, aSOLHLupdateNeedsConfrim.Length + 1); aSOLHLupdateNeedsConfrim[aSOLHLupdateNeedsConfrim.Length - 1] = buildaSalesLine(aBuildSalesLine, iLineQuantity, 0); } if (aLineDateData[3] == "1") //Remove returned Items { Array.Resize<string>(ref aSalesLineLineRemoveNeedsConfrim, aSalesLineLineRemoveNeedsConfrim.Length + 1); string sSOLHLDelQury = BuildDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); string sPastelLineRemove = BuildHistLineDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); aSalesLineLineRemoveNeedsConfrim[aSalesLineLineRemoveNeedsConfrim.Length - 1] = sSOLHLDelQury + "^" + sPastelLineRemove + "^" + sItemCode; bAlreadyAdded = true; } } else if (!Global.bGenerateZeroInvoice) { bZeroLine = true; //Build Sales line sLine = buildaSalesLine(aBuildSalesLine, iLineQuantity, 0); Array.Resize<string>(ref aSalesLine, aSalesLine.Length + 1); aSalesLine[aSalesLine.Length - 1] = sLine; if (iInstruction != 3) { Array.Resize<string>(ref aSOLHLupdateNeedsConfrim, aSOLHLupdateNeedsConfrim.Length + 1); aSOLHLupdateNeedsConfrim[aSOLHLupdateNeedsConfrim.Length - 1] = buildaSalesLine(aBuildSalesLine, iLineQuantity, 0); } if (aLineDateData[3] == "1") //Remove returned Items { Array.Resize<string>(ref aSalesLineLineRemoveNeedsConfrim, aSalesLineLineRemoveNeedsConfrim.Length + 1); string sSOLHLDelQury = BuildDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); string sPastelLineRemove = BuildHistLineDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); aSalesLineLineRemoveNeedsConfrim[aSalesLineLineRemoveNeedsConfrim.Length - 1] = sSOLHLDelQury + "^" + sPastelLineRemove + "^" + sItemCode; bAlreadyAdded = true; } } if (iInstruction == 3 && !bAlreadyAdded) //close order { Array.Resize<string>(ref aSalesLineLineRemoveNeedsConfrim, aSalesLineLineRemoveNeedsConfrim.Length + 1); string sSOLHLDelQury = BuildDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); string sPastelLineRemove = BuildHistLineDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); aSalesLineLineRemoveNeedsConfrim[aSalesLineLineRemoveNeedsConfrim.Length - 1] = sSOLHLDelQury + "^" + sPastelLineRemove + "^" + sItemCode; } else if (aLineDateData[3] == "1" && !bAlreadyAdded) //Remove returned Items { Array.Resize<string>(ref aSalesLineLineRemoveNeedsConfrim, aSalesLineLineRemoveNeedsConfrim.Length + 1); string sSOLHLDelQury = BuildDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); string sPastelLineRemove = BuildHistLineDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); aSalesLineLineRemoveNeedsConfrim[aSalesLineLineRemoveNeedsConfrim.Length - 1] = sSOLHLDelQury + "^" + sPastelLineRemove + "^" + sItemCode; } //SOLIL Build insert Query string sInsertLine = Build_SOLIL_Insert(aLineDateData, theLastInvoicedDate.AddDays(1), dtInvoiceToDate, iLineQuantity, DocumnetNumber, sItemCode, sLineDescription,"") ; Array.Resize<string>(ref aSOLIL_InsertQuery_confirm, aSOLIL_InsertQuery_confirm.Length + 1); aSOLIL_InsertQuery_confirm[aSOLIL_InsertQuery_confirm.Length - 1] = sInsertLine; } #endregion #region HAS LAST INVOICE DATE BUT MUST BE INVOICED FOR BALANCE else //Has a LastInvoicedDate but must be invoiced for the balance { if (aLineDateData[3] == "1" || sMonthEndType == "ValuesFromSalesOrder") { iLineQuantity = Convert.ToDouble(aLineDateData[6]) * dMultiplier; } else { TimeSpan tsBetween = new TimeSpan(); tsBetween = dtInvoiceToDate - theLastInvoicedDate; int iDaysBetween = tsBetween.Days; //MessageBox.Show(iDaysBetween.ToString()); int iSaturdays = 0; int iSundays = 0; if (sSaturday == "1") { iSaturdays = CountDays(DayOfWeek.Saturday, theStartDate, dtInvoiceToDate); } if (sSunday == "1") { iSundays = CountDays(DayOfWeek.Sunday, theStartDate, dtInvoiceToDate); } //Check if calculation rule must be used if (aLineDateData[7].ToString().Trim() == "") { iLineQuantity = Convert.ToDouble((iDaysBetween - iSaturdays - iSundays) * dMultiplier); } else { int iDateStartDay = Convert.ToInt16(theStartDate.Day); int iDateEndDay = Convert.ToInt16(dtInvoiceToDate.Day); int iReturnQty = iDaysBetween; string sCalculationQty = iReturnQty.ToString(); bool bLineReturned = false; if (aLineDateData[3] == "1") bLineReturned = true; string sReturnQty = Functions.CalculateQty_UnitRule(sCalculationQty, aLineDateData[7].ToString().Trim(), bLineReturned, theStartDate, dtInvoiceToDate); double dReturnQty = Convert.ToDouble(sReturnQty); iLineQuantity = Convert.ToDouble(dReturnQty * dMultiplier); } } if (iLineQuantity > 0 || Global.bGenerateZeroInvoice) // make sure quantity is greater than 0 { //Build Sales line sLine = buildaSalesLine(aBuildSalesLine, iLineQuantity, 0); string[] aReturn = processDowntimeQuery(oConn, DocumnetNumber, sItemCode, "Line", theLastInvoicedDate, dtInvoiceToDate, sLine, iLineQuantity, sNumberCode, aLineDateData[3]); if (aReturn.Length > 0) { for (int i = 0; i < aReturn.Length; i++) { Array.Resize<string>(ref aSalesLine, aSalesLine.Length + 1); aSalesLine[aSalesLine.Length - 1] = aReturn[i].ToString(); } } else { Array.Resize<string>(ref aSalesLine, aSalesLine.Length + 1); aSalesLine[aSalesLine.Length - 1] = sLine; //Create Text line with date from and to invoiced //string sNoteDescription = buildLeaseItemDescriptionNote(dtInvoiceToDate, theLastInvoicedDate, iLineQuantity); //sLine = buildaSalesLineNote(sItemCode, sNoteDescription); //Array.Resize<string>(ref aSalesLine, aSalesLine.Length + 1); //aSalesLine[aSalesLine.Length - 1] = sLine; } if (iInstruction != 3) { Array.Resize<string>(ref aSOLHLupdate, aSOLHLupdate.Length + 1); aSOLHLupdate[aSOLHLupdate.Length - 1] = sItemCode; } if (aLineDateData[3] == "1") //Remove returned Items { Array.Resize<string>(ref aSalesLineLineRemove, aSalesLineLineRemove.Length + 1); string sSOLHLDelQury = BuildDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); string sPastelLineRemove = BuildHistLineDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); aSalesLineLineRemove[aSalesLineLineRemove.Length - 1] = sSOLHLDelQury + "^" + sPastelLineRemove + "^" + sItemCode; bAlreadyAdded = true; } } else if (!Global.bGenerateZeroInvoice) { bZeroLine = true; //Build Sales line sLine = buildaSalesLine(aBuildSalesLine, iLineQuantity, 0); Array.Resize<string>(ref aSalesLine, aSalesLine.Length + 1); aSalesLine[aSalesLine.Length - 1] = sLine; if (iInstruction != 3) { Array.Resize<string>(ref aSOLHLupdate, aSOLHLupdate.Length + 1); aSOLHLupdate[aSOLHLupdate.Length - 1] = sItemCode; } if (aLineDateData[3] == "1") //Remove returned Items { Array.Resize<string>(ref aSalesLineLineRemove, aSalesLineLineRemove.Length + 1); string sSOLHLDelQury = BuildDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); string sPastelLineRemove = BuildHistLineDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); aSalesLineLineRemove[aSalesLineLineRemove.Length - 1] = sSOLHLDelQury + "^" + sPastelLineRemove + "^" + sItemCode; bAlreadyAdded = true; } } if (iInstruction == 3 && !bAlreadyAdded) //close order { Array.Resize<string>(ref aSalesLineLineRemove, aSalesLineLineRemove.Length + 1); string sSOLHLDelQury = BuildDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); string sPastelLineRemove = BuildHistLineDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); aSalesLineLineRemove[aSalesLineLineRemove.Length - 1] = sSOLHLDelQury + "^" + sPastelLineRemove + "^" + sItemCode; } else if (aLineDateData[3] == "1" && !bAlreadyAdded) //Remove returned Items { Array.Resize<string>(ref aSalesLineLineRemove, aSalesLineLineRemove.Length + 1); string sSOLHLDelQury = BuildDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); string sPastelLineRemove = BuildHistLineDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); aSalesLineLineRemove[aSalesLineLineRemove.Length - 1] = sSOLHLDelQury + "^" + sPastelLineRemove + "^" + sItemCode; } //SOLIL Build insert Query string sInsertLine = Build_SOLIL_Insert(aLineDateData, theLastInvoicedDate.AddDays(1), dtInvoiceToDate, iLineQuantity, DocumnetNumber, sItemCode, sLineDescription,""); Array.Resize<string>(ref aSOLIL_InsertQuery, aSOLIL_InsertQuery.Length + 1); aSOLIL_InsertQuery[aSOLIL_InsertQuery.Length - 1] = sInsertLine; } #endregion } else //Line Item hasn't been invoiced before { #region LINE HASN'T BEEN INVOICED BEFORE DateTime dtInvoiceToDate = new DateTime(); if (aLineDateData[3] != "1") { dtInvoiceToDate = dtInvoiceUpto; } else if (aLineDateData[3] == "1") { dtInvoiceToDate = theReturnDate; } if ((dtInvoiceUpto >= theStartDate && aLineDateData[3] != "1") || ((dtInvoiceUpto >= theStartDate && aLineDateData[3] == "1"))) { if (aLineDateData[3] == "1" || sMonthEndType == "ValuesFromSalesOrder") { iLineQuantity = Convert.ToDouble(aLineDateData[6]) * dMultiplier; } else { TimeSpan tsBetween = new TimeSpan(); tsBetween = dtInvoiceToDate - theStartDate; int iDaysBetween = tsBetween.Days + 1; //Add start date to invoice //MessageBox.Show(iDaysBetween.ToString()); int iSaturdays = 0; int iSundays = 0; if (sSaturday == "1") { iSaturdays = CountDays(DayOfWeek.Saturday, theStartDate, dtInvoiceToDate); } if (sSunday == "1") { iSundays = CountDays(DayOfWeek.Sunday, theStartDate, dtInvoiceToDate); } //Check if calculation rule must be used if (aLineDateData[7].ToString().Trim() == "") { iLineQuantity = Convert.ToDouble((iDaysBetween - iSaturdays - iSundays) * dMultiplier); } else { int iDateStartDay = Convert.ToInt16(theStartDate.Day); int iDateEndDay = Convert.ToInt16(dtInvoiceToDate.Day); int iReturnQty = iDaysBetween; string sCalculationQty = iReturnQty.ToString(); bool bLineReturned = false; if (aLineDateData[3] == "1") bLineReturned = true; string sReturnQty = Functions.CalculateQty_UnitRule(sCalculationQty, aLineDateData[7].ToString().Trim(), bLineReturned, theStartDate, dtInvoiceToDate); double dReturnQty = Convert.ToDouble(sReturnQty); iLineQuantity = Convert.ToDouble(dReturnQty * dMultiplier); } } //if (iLineQuantity > 0 && Convert.ToDecimal(aBuildSalesLine[3].ToString()) > 0) // make sure quantity is greater than 0 if (iLineQuantity > 0 || Global.bGenerateZeroInvoice) // make sure quantity is greater than 0 { //Build Sales line sLine = buildaSalesLine(aBuildSalesLine, iLineQuantity, 0); string[] aReturn = processDowntimeQuery(oConn, DocumnetNumber, sItemCode, "Line", theStartDate, dtInvoiceToDate, sLine, iLineQuantity, sNumberCode, aLineDateData[3]); if (aReturn.Length > 0) { for (int i = 0; i < aReturn.Length; i++) { Array.Resize<string>(ref aSalesLine, aSalesLine.Length + 1); aSalesLine[aSalesLine.Length - 1] = aReturn[i].ToString(); } } else { Array.Resize<string>(ref aSalesLine, aSalesLine.Length + 1); aSalesLine[aSalesLine.Length - 1] = sLine; //Create Text line with date from and to invoiced //string sNoteDescription = buildLeaseItemDescriptionNote(dtInvoiceToDate, theStartDate, iLineQuantity); //sLine = buildaSalesLineNote(sItemCode, sNoteDescription); //Array.Resize<string>(ref aSalesLine, aSalesLine.Length + 1); //aSalesLine[aSalesLine.Length - 1] = sLine; } if (iInstruction != 3) { Array.Resize<string>(ref aSOLHLupdate, aSOLHLupdate.Length + 1); aSOLHLupdate[aSOLHLupdate.Length - 1] = sItemCode; } if (aLineDateData[3] == "1") //Remove returned Items { Array.Resize<string>(ref aSalesLineLineRemove, aSalesLineLineRemove.Length + 1); string sSOLHLDelQury = BuildDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); string sPastelLineRemove = BuildHistLineDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); aSalesLineLineRemove[aSalesLineLineRemove.Length - 1] = sSOLHLDelQury + "^" + sPastelLineRemove + "^" + sItemCode; bAlreadyAdded = true; } } else if (!Global.bGenerateZeroInvoice) { bZeroLine = true; //Build Sales line sLine = buildaSalesLine(aBuildSalesLine, iLineQuantity, 0); Array.Resize<string>(ref aSalesLine, aSalesLine.Length + 1); aSalesLine[aSalesLine.Length - 1] = sLine; if (iInstruction != 3) { Array.Resize<string>(ref aSOLHLupdate, aSOLHLupdate.Length + 1); aSOLHLupdate[aSOLHLupdate.Length - 1] = sItemCode; } if (aLineDateData[3] == "1") //Remove returned Items { Array.Resize<string>(ref aSalesLineLineRemove, aSalesLineLineRemove.Length + 1); string sSOLHLDelQury = BuildDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); string sPastelLineRemove = BuildHistLineDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); aSalesLineLineRemove[aSalesLineLineRemove.Length - 1] = sSOLHLDelQury + "^" + sPastelLineRemove + "^" + sItemCode; bAlreadyAdded = true; } } if (iInstruction == 3 && !bAlreadyAdded) //close order { Array.Resize<string>(ref aSalesLineLineRemove, aSalesLineLineRemove.Length + 1); string sSOLHLDelQury = BuildDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); string sPastelLineRemove = BuildHistLineDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); aSalesLineLineRemove[aSalesLineLineRemove.Length - 1] = sSOLHLDelQury + "^" + sPastelLineRemove + "^" + sItemCode; } else if (aLineDateData[3] == "1" && !bAlreadyAdded) //Remove returned Items { Array.Resize<string>(ref aSalesLineLineRemove, aSalesLineLineRemove.Length + 1); string sSOLHLDelQury = BuildDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); string sPastelLineRemove = BuildHistLineDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); aSalesLineLineRemove[aSalesLineLineRemove.Length - 1] = sSOLHLDelQury + "^" + sPastelLineRemove + "^" + sItemCode; } } else { sDescription = "Delivery date is greater than invoice upto date"; sErrorNumber = "101"; } #endregion string sInsertLine = Build_SOLIL_Insert(aLineDateData, theStartDate, dtInvoiceToDate, iLineQuantity, DocumnetNumber, sItemCode, sLineDescription,sSiteName); Array.Resize<string>(ref aSOLIL_InsertQuery, aSOLIL_InsertQuery.Length + 1); aSOLIL_InsertQuery[aSOLIL_InsertQuery.Length - 1] = sInsertLine; } } } } #endregion #region IS THIS A RETURNABLE CONSUMABLE else if (sUserDefNum01 == "2") { string[] aLineDateData = getLineDateData(DocumnetNumber, sItemCode, sNumberCode); dMultiplier = Convert.ToInt32(Convert.ToDecimal(aLineDateData[5].ToString())); //Invoice Returnable consumable? if ((aItemTypeAction[1] == "1" && "0" == aLineDateData[3]) || aLineDateData[3] == "1") { if (sItemCode != "'") { iLineQuantity = dLineQuantity; //Build Sales line sLine = buildaSalesLine(aBuildSalesLine, iLineQuantity, 0); Array.Resize<string>(ref aSalesLine, aSalesLine.Length + 1); aSalesLine[aSalesLine.Length - 1] = sLine; //Lines to Remove Array.Resize<string>(ref aSalesLineLineRemove, aSalesLineLineRemove.Length + 1); string sSOLHLDelQury = BuildDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); string sPastelLineRemove = BuildHistLineDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); aSalesLineLineRemove[aSalesLineLineRemove.Length - 1] = sSOLHLDelQury + "^" + sPastelLineRemove + "^" + sItemCode; string sInsertLine = Build_SOLIL_Insert(aLineDateData, Convert.ToDateTime("1900/01/01"), Convert.ToDateTime("1900/01/01"), iLineQuantity, DocumnetNumber, sItemCode, sLineDescription,""); Array.Resize<string>(ref aSOLIL_InsertQuery, aSOLIL_InsertQuery.Length + 1); aSOLIL_InsertQuery[aSOLIL_InsertQuery.Length - 1] = sInsertLine; } } } #endregion #region IS THIS A CONSUMABLE else if (sUserDefNum01 == "0") { string[] aLineDateData = getLineDateData(DocumnetNumber, sItemCode, sNumberCode); //ERROR HANDLING - MESSAGE THAT THERE WAS AN ERROR -- AJD PHALABORWA 30 September 2009 if (aLineDateData[5] == null)//Line Item not found -- Can't invoice this order - Return Error Message -- Probable cause LinkNumbers not in sync { return ("Error: Cannot invoice order " + DocumnetNumber); } dMultiplier = Convert.ToDouble(aLineDateData[5].ToString()); //Invoice Consumable? if ((aItemTypeAction[2] == "1" && "0" == aLineDateData[3]) || aLineDateData[3] == "1") { if (sItemCode != "'") { iLineQuantity = dLineQuantity; //Build Sales line sLine = buildaSalesLine(aBuildSalesLine, iLineQuantity, 0); Array.Resize<string>(ref aSalesLine, aSalesLine.Length + 1); aSalesLine[aSalesLine.Length - 1] = sLine; //Lines to Remove Array.Resize<string>(ref aSalesLineLineRemove, aSalesLineLineRemove.Length + 1); string sSOLHLDelQury = BuildDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); string sPastelLineRemove = BuildHistLineDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); aSalesLineLineRemove[aSalesLineLineRemove.Length - 1] = sSOLHLDelQury + "^" + sPastelLineRemove + "^" + sItemCode; string sInsertLine = Build_SOLIL_Insert(aLineDateData, Convert.ToDateTime("1900/01/01"), Convert.ToDateTime("1900/01/01"), iLineQuantity, DocumnetNumber, sItemCode, sLineDescription,""); Array.Resize<string>(ref aSOLIL_InsertQuery, aSOLIL_InsertQuery.Length + 1); aSOLIL_InsertQuery[aSOLIL_InsertQuery.Length - 1] = sInsertLine; } } } #endregion #region IS THIS A TEXT LINE else if (sUserDefNum01 == "" || sSearchType == "7") //Text Line { if (sItemCode == "'" && !sLineDescription.StartsWith("*D")) { sLine = buildaSalesLineNote(sItemCode, sLineDescription); Array.Resize<string>(ref aSalesLine, aSalesLine.Length + 1); aSalesLine[aSalesLine.Length - 1] = sLine; //LL Don's remove text lines when they are putn on invoice //string sSOLHLDelQury = BuildDeleteStringQuery(DocumnetNumber, sItemCode, sLineLinkNum); //string sPastelLineRemove = sLine; //Array.Resize<string>(ref aSalesLineLineRemove, aSalesLineLineRemove.Length + 1); //aSalesLineLineRemove[aSalesLineLineRemove.Length - 1] = sSOLHLDelQury + "^" + sPastelLineRemove + "^" + sItemCode; } } #endregion } //End of while ///////////////////////////////////////// //Items Invoiced During the month confirm ///////////////////////////////////////// #region Items Invoiced During the month confirm if (aSalesLineMessage.Length > 0) { string sMessage = "The following Item(s) for Sales Order " + DocumnetNumber + ", customer " + sCustomer + ", has been invoiced during this month:\r\n\r\n"; for (int i = 0; i < aSalesLineMessage.Length; i++) { string[] aMessageDetail = aSalesLineMessage[i].Split("|".ToCharArray()); sMessage += "Item Code: " + aMessageDetail[0].ToString() + " Invoiced until: " + aMessageDetail[1].ToString().Substring(0, 10) + "\r\n\r\n"; } sMessage += "Do you want to invoice these line items up to: " + dtInvoiceUpto.ToString().Substring(0, 10) + " ?"; if (MessageBox.Show(sMessage, "Items already invoiced in this month: " + dtPeriodStart.ToString().Substring(0, 10) + " - " + dtPeriodEnd.ToString().Substring(0, 10), MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { for (int i = 0; i < aSalesLineNeedsConfirm.Length; i++) { Array.Resize<string>(ref aSalesLine, aSalesLine.Length + 1); aSalesLine[aSalesLine.Length - 1] = aSalesLineNeedsConfirm[i]; } for (int i = 0; i < aSOLHLupdateNeedsConfrim.Length; i++) { Array.Resize<string>(ref aSOLHLupdate, aSOLHLupdate.Length + 1); aSOLHLupdate[aSOLHLupdate.Length - 1] = aSOLHLupdateNeedsConfrim[i]; } for (int i = 0; i < aUpdateDownTimeDescriptionNeedsConfirm.Length; i++) { Array.Resize<string>(ref aUpdateDownTimeDescription, aUpdateDownTimeDescription.Length + 1); aUpdateDownTimeDescription[aUpdateDownTimeDescription.Length - 1] = aUpdateDownTimeDescriptionNeedsConfirm[i]; } for (int i = 0; i < aSalesLineLineRemoveNeedsConfrim.Length; i++) { Array.Resize<string>(ref aSalesLineLineRemove, aSalesLineLineRemove.Length + 1); aSalesLineLineRemove[aSalesLineLineRemove.Length - 1] = aSalesLineLineRemoveNeedsConfrim[i]; } for (int i = 0; i < aSOLIL_InsertQuery_confirm.Length; i++) { Array.Resize<string>(ref aSOLIL_InsertQuery, aSOLIL_InsertQuery.Length + 1); aSOLIL_InsertQuery[aSOLIL_InsertQuery.Length - 1] = aSOLIL_InsertQuery_confirm[i]; } aSOLIL_InsertQuery_confirm = new string[0]; } } #endregion string sInvUpTo = dtInvoiceUpto.Year + "-" + dtInvoiceUpto.Month.ToString("00") + "-" + dtInvoiceUpto.Day.ToString("00"); ////////////////// //Generate Invoice ////////////////// if (((aSalesLine.Length > 0 && dDocumentTotal > 0) || (aSalesLine.Length > 0 && Global.bGenerateZeroInvoice))) { string[] aReturn = new string[0]; //if (MessageBox.Show("This will Send Invoice instruction to Pastel, Please confirm", "Pastel Confirmation", MessageBoxButtons.OKCancel, MessageBoxIcon.Information) == DialogResult.OK) //{ aReturn = clsSDK.CreatePastelDocument(sHeader, aSalesLine, 103, Global.sDataPath, Global.iPastelSdkUser, Global.bLogCreateDocument, oConn, 4).Split("|".ToCharArray()); //} //else //{ //Array.Resize<string>(ref aReturn, aReturn.Length + 1); //aReturn[aReturn.Length - 1] = "Dubbel Invoice"; //Array.Resize<string>(ref aReturn, aReturn.Length + 1); //aReturn[aReturn.Length - 1] = "Dubbel Invoice"; //} if (aReturn[0] == "0") { frmMonth.WriteStatus(aReturn[0], aReturn[1], DocumnetNumber, "Completed"); //build Return string of result sResultString = "Order " + DocumnetNumber + " To Invoice " + aReturn[1].ToString(); if (sRoundingType != "None" && (bCashAccount || !Global.bRoundingCOD)) { string sRoundingLine = autorounding(aReturn[1].ToString()); if (sRoundingLine.Trim() != "") { string[] aEditDownReturn = clsSDK.EditPastelDocument(sRoundingLine, 103, aReturn[1].ToString(), "I", Global.sDataPath).Split("|".ToCharArray()); } } //////////////////////////////////////////////////////////////// #region DELETE AND UPDATE LINES IN SOLHL, SOLHH AND HISTORYLINES + SOLIL //////////////////////////////////////////////////////////////// string sDeleteString = ""; using (PsqlConnection oConnSolHL = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr)) { oConnSolHL.Open(); //Update SOLHL with last invoice date if (aSOLHLupdate.Length > 0) { for (int i = 0; i < aSOLHLupdate.Length; i++) { sSql = "update SOLHL set LastInvoiceDate = '" + sInvUpTo + "', DeliveryDate = '" + Convert.ToDateTime(sInvUpTo).AddDays(1).ToString("dd-MM-yyyy") + "' "; sSql += "where ItemCode = '" + aSOLHLupdate[i].ToString() + "' and Header = '" + DocumnetNumber + "'"; int iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConnSolHL).ExecuteNonQuery(); } } //Insert into SOLIL with invoice detail if (aSOLIL_InsertQuery.Length > 0) { for (int i = 0; i < aSOLIL_InsertQuery.Length; i++) { sSql = aSOLIL_InsertQuery[i].ToString().Replace("|", aReturn[1].ToString()); int iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConnSolHL).ExecuteNonQuery(); } aSOLIL_InsertQuery = new string[0]; } using (PsqlConnection oPasInsert = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sPastelConnStr)) { using (PsqlConnection oPasConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sPastelConnStr)) { //LL KH 11/03/2010 //Sync SOLIL with HistoryLines and set Sitename in HistoryHeader sSql = "select DocumentNumber, ItemCode, Description, LinkNum from HistoryLines where DocumentNumber = '" + aReturn[1].Trim() + "' and HistoryLines.DocumentType in (103,3)"; int iReturn; using (PsqlDataReader rdReader = Connect.getDataCommand(sSql, oPasConn).ExecuteReader()) { while (rdReader.Read()) { string[] aDescription = rdReader["Description"].ToString().Split('~'); if (aDescription.Length > 1) { sSql = "Update SOLIL SET LinkNum = " + rdReader["LinkNum"].ToString(); sSql += " where DocumentNumber = '" + aReturn[1].Trim() + "'"; sSql += " and LinkNum = " + aDescription[1]; iReturn = Connect.getDataCommand(sSql, oConnSolHL).ExecuteNonQuery(); sSql = "Update HistoryLines SET Description = '" + aDescription[0].Replace("'","") + "'"; sSql += " where DocumentNumber = '" + aReturn[1].Trim() + "'"; sSql += " and LinkNum = " + rdReader["LinkNum"].ToString(); //LL KH 11/03/2010 sSql += " and HistoryLines.DocumentType in (103,3)"; iReturn = Connect.getDataCommand(sSql, oPasInsert).ExecuteNonQuery(); } } rdReader.Close(); string sSql2 = "Update HistoryHeader set Message03 = '" + sSiteName + "' where DocumentNumber = '" + aReturn[1].Trim() + "'"; int iRet2 = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql2, oPasConn).ExecuteNonQuery(); } } } //Update SOLHH with type string sSqlHH = ""; if (iInstruction == 1) //convert Future Sales Order to Active order { sSqlHH = "update SOLHH set Type = 0, Status = 2 where DocNumber = '" + DocumnetNumber + "'"; int iReturnHH = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSqlHH, oConnSolHL).ExecuteNonQuery(); } else if (iInstruction == 2) //convert Active order to standing order { sSqlHH = "update SOLHH set Type = 1, Status = 2 where DocNumber = '" + DocumnetNumber + "'"; int iReturnHH = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSqlHH, oConnSolHL).ExecuteNonQuery(); } else if (iInstruction == 3) //Close { //update SOLHH's Status and type sSqlHH = "update SOLHH set Type = 3, Status = 3 where DocNumber = '" + DocumnetNumber + "'"; int iReturnHH = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSqlHH, oConnSolHL).ExecuteNonQuery(); //Clear Inventory's UserDefText Fields using (PsqlConnection oConnHistLine = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sPastelConnStr)) { oConnHistLine.Open(); sSql = "update Inventory set UserDefText01 = '', UserDefText02 = '', UserDefText03 = '' where UserDefText02 = '" + DocumnetNumber + "'"; int iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConnHistLine).ExecuteNonQuery(); oConnHistLine.Dispose(); } } else { sSqlHH = "update SOLHH set Status = 2 where DocNumber = '" + DocumnetNumber + "'"; int iReturnHH = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSqlHH, oConnSolHL).ExecuteNonQuery(); } // Update Downtime string if upto date fell besteen downtime dates if (aUpdateDownTimeDescription.Length > 0) { using (PsqlConnection oConnHistLine = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sPastelConnStr)) { oConnHistLine.Open(); for (int i = 0; i < aUpdateDownTimeDescription.Length; i++) { string[] aDownTimeUpdateDetail = aUpdateDownTimeDescription[i].Split("|".ToCharArray()); //LL KH 11/03/2010 sSql = "Update HistoryLines set Description = '" + aDownTimeUpdateDetail[0].ToString() + "' where DocumentNumber = '" + DocumnetNumber + "' and CONCAT(LTRIM(RTRIM(HistoryLines.ItemCode)),LTRIM(RTRIM(LinkNum))) = ''" + aDownTimeUpdateDetail[1].ToString().Trim() + "' and HistoryLines.DocumentType in (102,2)"; int iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConnHistLine).ExecuteNonQuery(); } oConnHistLine.Dispose(); } } string[] aDeleteDowntimeString = new string[0]; ///////////////////////////////////////////////////////////////////// //Remove sales lines from SOLHL and HistoryLines and renumber linkNum ///////////////////////////////////////////////////////////////////// if (aSalesLineLineRemove.Length > 0) { sDeleteString = "("; for (int i = 0; i < aSalesLineLineRemove.Length; i++) { string[] aSalesLineLineRemoveDetail = aSalesLineLineRemove[i].Split("^".ToCharArray()); if (aSalesLineLineRemoveDetail[2].ToString().Trim() != "'") { sDeleteString += "'" + aSalesLineLineRemoveDetail[2].ToString() + "',"; } ////////////////////////////// //Delete sales Line from SOLHL ////////////////////////////// if (aSalesLineLineRemoveDetail[0].ToString() != "" && aSalesLineLineRemoveDetail[2].ToString().Trim() != "'") { sSql = aSalesLineLineRemoveDetail[0].ToString(); int iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConnSolHL).ExecuteNonQuery(); } else if (aSalesLineLineRemoveDetail[0].ToString() != "" && aSalesLineLineRemoveDetail[2].ToString().Trim() == "'") { Array.Resize<string>(ref aDeleteDowntimeString, aDeleteDowntimeString.Length + 1); aDeleteDowntimeString[aDeleteDowntimeString.Length - 1] = aSalesLineLineRemoveDetail[0].ToString(); } //////////////////////////////////////////// //Delete sales Line from Pastel HistoryLines //////////////////////////////////////////// string sMyLine = aSalesLineLineRemoveDetail[1].ToString(); if (sMyLine != null && sMyLine.Trim() != "") { using (PsqlConnection oConnHistLine = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sPastelConnStr)) { //aReturn = clsSDK.EditPastelDocument(sMyLine, 102, DocumnetNumber, "D", Global.sDataPath).Split("|".ToCharArray()); int iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sMyLine, oConnHistLine).ExecuteNonQuery(); } } } sDeleteString = sDeleteString.Substring(0, sDeleteString.Length - 1); if (sDeleteString != "") sDeleteString += ")"; } //////////////////////////////////////////////////////////////////// //Update Inventory User fields and delete Downtime from HistoryLines //////////////////////////////////////////////////////////////////// using (PsqlConnection oConnHistLine = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sPastelConnStr)) { oConnHistLine.Open(); sSql = ""; int iReturn = 0; if (sDeleteString != "") { sSql = "update Inventory set UserDefText01 = '', UserDefText02 = '', UserDefText03 = '' where ItemCode in " + sDeleteString; iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConnHistLine).ExecuteNonQuery(); } //for (int i = 0; i < aDeleteDowntimeString.Length; i++) //{ // sSql = aDeleteDowntimeString[i].ToString(); // iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConnHistLine).ExecuteNonQuery(); //} oConnHistLine.Dispose(); } //////////////////////////////////////////////// //Renumber link Number in HistoryLines and SOLHL //////////////////////////////////////////////// int iRemainingRows = Functions.reOrderPastelLinkNums(DocumnetNumber); /////////////////////////////// //Renumber link Number in SOLHL /////////////////////////////// //int iRemainingRows = Functions.reOrderLiquidSalesLines(DocumnetNumber); if (iRemainingRows == 0) { sSql = "UPDATE SOLHH SET "; sSql += " Status = 3"; sSql += " WHERE DocNumber = '" + DocumnetNumber + "' "; int iRet = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConnSolHL).ExecuteNonQuery(); } //insert marketing details //get total invoice amount Decimal dTotalInvoiceAmount = 0; using (PsqlConnection oPasInsert = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sPastelConnStr)) { string sGetAmount = "Select Total, TotalTax From HistoryHeader where DocumentNumber = '" + aReturn[1].ToString().Trim() + "'"; using (PsqlDataReader rdReader = Connect.getDataCommand(sGetAmount, oPasInsert).ExecuteReader()) { while (rdReader.Read()) { Decimal dTotal = Convert.ToDecimal(rdReader[0].ToString()); //Decimal dTax = Convert.ToDecimal(rdReader[1].ToString()); dTotalInvoiceAmount = dTotal; } rdReader.Close(); } oPasInsert.Dispose(); } doMarketing(sCustomer, dTotalInvoiceAmount, dtInvoiceUpto, aReturn[1]); oConnSolHL.Dispose(); } #endregion } else //Error with generation of invioce { frmMonth.WriteStatus(aReturn[0], "", DocumnetNumber, aReturn[1]); } } else //if there is no sales lines to invoice update invoice { frmMonth.WriteStatus(sErrorNumber, "", DocumnetNumber, sDescription); using (PsqlConnection oConnSolHL = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr)) { oConnSolHL.Open(); //Update SOLHL with last invoice date if (aSOLHLupdate.Length > 0) { for (int i = 0; i < aSOLHLupdate.Length; i++) { sSql = "update SOLHL set LastInvoiceDate = '" + sInvUpTo + "', DeliveryDate = '" + Convert.ToDateTime(sInvUpTo).AddDays(1).ToString("dd-MM-yyyy") + "' "; sSql += "where ItemCode = '" + aSOLHLupdate[i].ToString() + "' and Header = '" + DocumnetNumber + "'"; int iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConnSolHL).ExecuteNonQuery(); } } //Update SOLHH with type string sSqlHH = ""; if (iInstruction == 1) //convert Future Sales Order to Active order { sSqlHH = "update SOLHH set Type = 0 where DocNumber = '" + DocumnetNumber + "'"; int iReturnHH = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSqlHH, oConnSolHL).ExecuteNonQuery(); } else if (iInstruction == 2) //convert Active order to standing order { sSqlHH = "update SOLHH set Type = 1 where DocNumber = '" + DocumnetNumber + "'"; int iReturnHH = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSqlHH, oConnSolHL).ExecuteNonQuery(); } else if (iInstruction == 3) //Close { sSqlHH = "update SOLHH set Type = 3, Status = 3 where DocNumber = '" + DocumnetNumber + "'"; int iReturnHH = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSqlHH, oConnSolHL).ExecuteNonQuery(); //Clear Inventory's UserDefText Fields using (PsqlConnection oConnHistLine = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sPastelConnStr)) { oConnHistLine.Open(); sSql = "update Inventory set UserDefText01 = '', UserDefText02 = '', UserDefText03 = '' where UserDefText02 = '" + DocumnetNumber + "'"; int iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConnHistLine).ExecuteNonQuery(); oConnHistLine.Dispose(); } } oConnSolHL.Dispose(); } } return sResultString; } }