Ejemplo n.º 1
0
		/// <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);
		}      
Ejemplo n.º 2
0
		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;				
			}
		}