private string CreateDailySales(DateTime pvtProcessDate, Data.TerminalReportDetails pvtTerminalReportDetails) { string strRetValue = ""; try { string stDailyTableName = mclsAyalaDetails.TenantCode.Substring(0, 3) + pvtProcessDate.ToString("MMdd"); string stDailyFileName = mclsAyalaDetails.OutputDirectory + "\\" + stDailyTableName + ".dbf"; if (File.Exists(stDailyFileName)) { File.Delete(stDailyFileName); } CreateDailySalesTable(stDailyTableName); InsertDailySales(stDailyTableName, pvtTerminalReportDetails); strRetValue = stDailyFileName; } catch (Exception ex) { throw ex; } return(strRetValue); }
public void CreateAndTransferFile(int BranchID, string TerminalNo, DateTime pvtDateInitialized) { DateTime dteDateToprocess = pvtDateInitialized; Event clsEvent = new Event(); try { clsEvent.AddEventLn("Starting Ayala FILE CREATOR.", true); clsEvent.AddEventLn("==================================================", true); clsEvent.AddEventLn("= Ayala FILE CREATOR =", true); clsEvent.AddEventLn("==================================================", true); /*********************************************************************** * Check the destination dir if existing. * ********************************************************************/ clsEvent.AddEventLn("Checking directory settings.", true); string dir = mclsAyalaDetails.OutputDirectory; dir = dir.Replace("{YYYY}", DateTime.Now.ToString("yyyy")); dir = dir.Replace("{MM}", DateTime.Now.ToString("MM")); dir = dir.Replace("{MMM}", DateTime.Now.ToString("MMM")); dir = dir.Replace("{MMMM}", DateTime.Now.ToString("MMMM")); if (!System.IO.Directory.Exists(dir)) { clsEvent.AddEventLn("Directory [" + dir + "] does not exist.", true); System.IO.Directory.CreateDirectory(dir); } else { clsEvent.AddEventLn("Directory [" + dir + "] exist.", true); } mclsAyalaDetails.OutputDirectory = dir; /*********************************************************************** * GET The report of Current Terminal using Specified InitializationDate * ********************************************************************/ Data.TerminalReportHistory clsTerminalReportHistory = new Data.TerminalReportHistory(); Data.TerminalReportDetails clsTerminalReportDetail = clsTerminalReportHistory.Details(BranchID, TerminalNo, dteDateToprocess); string stDailyTableName = CreateDailySales(dteDateToprocess, clsTerminalReportDetail); string stHourlyTableName = CreateHourlySales(BranchID, TerminalNo, dteDateToprocess); clsTerminalReportHistory.UpdateTerminalReportBatchCounter(TerminalNo, dteDateToprocess); clsTerminalReportHistory.CommitAndDispose(); clsEvent.AddEventLn("Record for [" + dteDateToprocess.ToString("yyyy-MM-dd HH:mm:ss") + "] BacthCounter:" + clsTerminalReportDetail.BatchCounter.ToString() + " has been created for Ayala.", true); TransferFile(stDailyTableName); TransferFile(stHourlyTableName); } catch (Exception ex) { clsEvent.AddErrorEventLn(ex); throw ex; } clsEvent.AddEventLn("Ayala FILE CREATOR exited.", true); }
private string CreateDailySales(DateTime pvtProcessDate, Data.TerminalReportDetails pvtTerminalReportDetails, decimal decSeniorCitizenDiscount, long lngSeniorCitizenDiscountCount) { string strRetValue = ""; try { string stDailyTableName = mclsFSIDetails.OutputDirectory + "\\S" + mclsFSIDetails.TenantName.Substring(0, 4) + pvtTerminalReportDetails.TerminalNo + pvtTerminalReportDetails.BatchCounter.ToString() + "." + pvtTerminalReportDetails.DateLastInitializedToDisplay.ToString("MM").Replace("10", "A").Replace("11", "B").Replace("12", "C").Replace("0", "") + pvtTerminalReportDetails.DateLastInitializedToDisplay.ToString("dd"); if (File.Exists(stDailyTableName)) { File.Delete(stDailyTableName); } long lngDiscountCountNetOfSeniorCitizen = pvtTerminalReportDetails.NoOfDiscountedTransactions - lngSeniorCitizenDiscountCount; decimal decDiscountNetOfSeniorCitizen = pvtTerminalReportDetails.TotalDiscount - decSeniorCitizenDiscount; writer = File.AppendText(stDailyTableName); writer.WriteLine("01{0}", mclsFSIDetails.TenantCode); writer.WriteLine("02{0}", pvtTerminalReportDetails.TerminalNo); writer.WriteLine("03{0}", pvtTerminalReportDetails.DateLastInitializedToDisplay.ToString("MMddyyyy")); writer.WriteLine("04{0}", pvtTerminalReportDetails.OldGrandTotal.ToString("####.#0").Replace(".", "")); writer.WriteLine("05{0}", pvtTerminalReportDetails.NewGrandTotal.ToString("####.#0").Replace(".", "")); writer.WriteLine("06{0}", Convert.ToDecimal(pvtTerminalReportDetails.DailySales + pvtTerminalReportDetails.VAT).ToString("####.#0").Replace(".", "")); writer.WriteLine("07{0}", pvtTerminalReportDetails.NonVATableAmount.ToString("####.#0").Replace(".", "")); writer.WriteLine("08{0}", decSeniorCitizenDiscount.ToString("####.#0").Replace(".", "")); writer.WriteLine("09{0}", "0"); writer.WriteLine("10{0}", decDiscountNetOfSeniorCitizen.ToString("####.#0").Replace(".", "")); writer.WriteLine("11{0}", pvtTerminalReportDetails.RefundSales.ToString("####.#0").Replace(".", "")); writer.WriteLine("12{0}", pvtTerminalReportDetails.VoidSales.ToString("####.#0").Replace(".", "")); writer.WriteLine("13{0}", pvtTerminalReportDetails.ZReadCount.ToString("####.#0").Replace(".", "")); writer.WriteLine("14{0}", pvtTerminalReportDetails.NoOfClosedTransactions.ToString("####")); writer.WriteLine("15{0}", pvtTerminalReportDetails.NoOfTotalTransactions.ToString("####")); writer.WriteLine("16{0}", mclsFSIDetails.SalesTypeCode); writer.WriteLine("17{0}", pvtTerminalReportDetails.DailySales.ToString("####.#0").Replace(".", "")); decimal decVAT = pvtTerminalReportDetails.DailySales * decimal.Parse("0.12"); writer.WriteLine("18{0}", decVAT.ToString("####.#0").Replace(".", "")); writer.WriteLine("19{0}", pvtTerminalReportDetails.TotalCharge.ToString("####.#0").Replace(".", "")); writer.WriteLine("20{0}", "0"); //adjustment writer.Flush(); writer.Close(); strRetValue = stDailyTableName; } catch (Exception ex) { throw ex; } return(strRetValue); }
//public delegate void UpdateTerminalReportDelegate(TransactionStatus TransStatus, decimal SubTotal, decimal Discount, decimal Charge, decimal VAT, decimal VATableAmount, decimal ZeroRatedSales, decimal NonVATableAmount, decimal EVAT, decimal EVATableAmount, decimal NonEVATableAmount, decimal LocalTax, decimal CashPayment, decimal ChequePayment, decimal CreditCardPayment, decimal CreditPayment, decimal DebitPayment, PaymentTypes PaymentType); public void UpdateTerminalReport(TransactionStatus TransStatus, decimal ItemSold, decimal QuantitySold, decimal SubTotal, decimal Discount, decimal ItemsDiscount, decimal SNRItemsDiscount, decimal PWDItemsDiscount, decimal OtherItemsDiscount, decimal Charge, decimal VAT, decimal VATableAmount, decimal ZeroRatedSales, decimal NonVATableAmount, decimal VATExempt, decimal EVAT, decimal EVATableAmount, decimal NonEVATableAmount, decimal LocalTax, decimal CashPayment, decimal ChequePayment, decimal CreditCardPayment, decimal CreditPayment, decimal DebitPayment, decimal RewardPointsPayment, decimal RewardConvertedPayment, PaymentTypes PaymentType) { Int32 intNoOfCashTransactions = 0; Int32 intNoOfChequeTransactions = 0; Int32 intNoOfCreditCardTransactions = 0; Int32 intNoOfCreditTransactions = 0; Int32 intNoOfDebitTransactions = 0; Int32 intNoOfCombinationPaymentTransactions = 0; Int32 intNoOfDiscountedTransactions = 0; Int32 intNoOfRewardPointsPayment = 0; decimal decPromotionalItems = 0; foreach (System.Data.DataRow dr in ItemDataTable.Rows) { //decimal ItemQuantity = decimal.TryParse(dr["Quantity"].ToString().Replace("RETURN", "").Trim(), out ItemQuantity) ? ItemQuantity : 0; decPromotionalItems += Convert.ToDecimal(dr["PromoApplied"]); DiscountTypes ItemDiscountType = (DiscountTypes)Enum.Parse(typeof(DiscountTypes), dr["ItemDiscountType"].ToString().ToString()); } switch (PaymentType) { case PaymentTypes.Cash: { intNoOfCashTransactions = 1; break; } case PaymentTypes.Cheque: { intNoOfChequeTransactions = 1; break; } case PaymentTypes.CreditCard: { intNoOfCreditCardTransactions = 1; break; } case PaymentTypes.Credit: { intNoOfCreditTransactions = 1; break; } case PaymentTypes.Debit: { intNoOfDebitTransactions = 1; break; } case PaymentTypes.RewardPoints: { intNoOfRewardPointsPayment = 1; break; } case PaymentTypes.Combination: { intNoOfCombinationPaymentTransactions = 1; break; } default: { intNoOfCashTransactions = 1; break; } } Data.TerminalReportDetails clsTerminalReportDetails = new Data.TerminalReportDetails(); if (TransStatus == TransactionStatus.Closed) { clsTerminalReportDetails.ItemSold = ItemSold; clsTerminalReportDetails.QuantitySold = QuantitySold; clsTerminalReportDetails.NoOfCashTransactions = intNoOfCashTransactions; clsTerminalReportDetails.NoOfChequeTransactions = intNoOfChequeTransactions; clsTerminalReportDetails.NoOfCreditCardTransactions = intNoOfCreditCardTransactions; clsTerminalReportDetails.NoOfCreditTransactions = intNoOfCreditTransactions; clsTerminalReportDetails.NoOfDebitPaymentTransactions = intNoOfDebitTransactions; clsTerminalReportDetails.NoOfCombinationPaymentTransactions = intNoOfCombinationPaymentTransactions; clsTerminalReportDetails.NoOfRewardPointsPayment = intNoOfRewardPointsPayment; clsTerminalReportDetails.NoOfClosedTransactions = 1; // march 19, 2009 clsTerminalReportDetails.NoOfDiscountedTransactions = intNoOfDiscountedTransactions; clsTerminalReportDetails.CreditSalesTax = clsTerminalReportDetails.CreditPayment * (mclsTerminalDetails.VAT / 100); clsTerminalReportDetails.PromotionalItems = decPromotionalItems; clsTerminalReportDetails.NoOfTotalTransactions = 1; } else if (TransStatus == TransactionStatus.Void) { clsTerminalReportDetails.ItemSold = 0; clsTerminalReportDetails.QuantitySold = 0; clsTerminalReportDetails.NoOfVoidTransactions = 1; clsTerminalReportDetails.NoOfTotalTransactions = 1; } else if (TransStatus == TransactionStatus.Refund) { clsTerminalReportDetails.ItemSold = -ItemSold; clsTerminalReportDetails.QuantitySold = -QuantitySold; clsTerminalReportDetails.NoOfRefundTransactions = 1; clsTerminalReportDetails.NoOfTotalTransactions = 1; // march 19, 2009 clsTerminalReportDetails.CreditSalesTax = -(clsTerminalReportDetails.CreditPayment * (mclsTerminalDetails.VAT / 100)); } else if (TransStatus == TransactionStatus.CreditPayment) { clsTerminalReportDetails.NoOfCreditPaymentTransactions = 1; clsTerminalReportDetails.NoOfTotalTransactions = 1; // march 19, 2009 // Apr 12, 2014 change to 0 coz its already taxed when goods are sold clsTerminalReportDetails.CreditSalesTax = 0; // clsTerminalReportDetails.CreditPayment * (mclsTerminalDetails.VAT / 100); clsTerminalReportDetails.PromotionalItems = decPromotionalItems; } else if (TransStatus == TransactionStatus.ClosedWalkIn) { clsTerminalReportDetails.NoOfWalkInTransactions = 1; } else if (TransStatus == TransactionStatus.ClosedOutOfStock) { clsTerminalReportDetails.NoOfOutOfStockTransactions = 1; } else if (TransStatus == TransactionStatus.Consignment) { clsTerminalReportDetails.NoOfConsignmentTransactions = 1; } else if (TransStatus == TransactionStatus.ClosedWalkInRefund) { clsTerminalReportDetails.NoOfWalkInRefundTransactions = 1; } else if (TransStatus == TransactionStatus.ClosedOutOfStockRefund) { clsTerminalReportDetails.NoOfOutOfStockRefundTransactions = 1; } else if (TransStatus == TransactionStatus.ConsignmentRefund) { clsTerminalReportDetails.NoOfConsignmentRefundTransactions = 1; } clsTerminalReportDetails.TerminalNo = mclsTerminalDetails.TerminalNo; clsTerminalReportDetails.BranchID = mclsTerminalDetails.BranchID; Data.TerminalReport clsTerminalReport = new Data.TerminalReport(mConnection, mTransaction); mConnection = clsTerminalReport.Connection; mTransaction = clsTerminalReport.Transaction; clsTerminalReport.UpdateTransactionSales(clsTerminalReportDetails); }
private string CreateDailySales(DateTime pvtProcessDate, Data.TerminalReportDetails pvtTerminalReportDetails, decimal decSeniorCitizenDiscount, long lngSeniorCitizenDiscountCount, decimal decPWDDiscount, long lngPWDDiscountCount) { string strRetValue = ""; try { string stDailyTableName = mclsRLCDetails.OutputDirectory + "\\" + mclsRLCDetails.TenantCode.Substring(mclsRLCDetails.TenantCode.Length - 4) + pvtTerminalReportDetails.DateLastInitializedToDisplay.ToString("MMdd") + "." + pvtTerminalReportDetails.TerminalNo + pvtTerminalReportDetails.BatchCounter.ToString(); if (File.Exists(stDailyTableName)) { File.Delete(stDailyTableName); } // remove the + pvtTerminalReportDetails.TotalCharge in decGrossSales decimal decGrossSales = pvtTerminalReportDetails.DailySales + pvtTerminalReportDetails.VAT + pvtTerminalReportDetails.LocalTax + pvtTerminalReportDetails.TotalDiscount; //+ pvtTerminalReportDetails.TotalCharge //decimal decVAT = (pvtTerminalReportDetails.DailySales + pvtTerminalReportDetails.VAT - pvtTerminalReportDetails.NonVaTableAmount) / Convert.ToDecimal(1.12) * Convert.ToDecimal(0.12); //decGrossSales long lngDiscountCountNetOfSeniorCitizen = pvtTerminalReportDetails.NoOfDiscountedTransactions - lngSeniorCitizenDiscountCount - lngPWDDiscountCount; decimal decDiscountNetOfSeniorCitizen = pvtTerminalReportDetails.TotalDiscount - decSeniorCitizenDiscount - decPWDDiscount; decimal deCreditCardSalesTax = pvtTerminalReportDetails.CreditCardSales / Convert.ToDecimal(1.12) * Convert.ToDecimal(0.12); decimal decVAT = (decGrossSales - decSeniorCitizenDiscount - pvtTerminalReportDetails.NonVATableAmount - decPWDDiscount) / Convert.ToDecimal(1.12) * Convert.ToDecimal(0.12); //decGrossSales writer = File.AppendText(stDailyTableName); writer.WriteLine("01{0}", mclsRLCDetails.TenantCode.PadLeft(16, '0')); writer.WriteLine("02{0}", pvtTerminalReportDetails.TerminalNo.PadLeft(16, '0')); writer.WriteLine("03{0}", decGrossSales.ToString("####.#0").PadLeft(16, '0')); //gross of VAT, regular discount, sr citizen, local tax, pwd, / net of void ,refund and service charge writer.WriteLine("04{0}", decVAT.ToString("####.#0").PadLeft(16, '0')); // Line 3 less Line 11, Line 24, Line 27 & Line 28 / 1.12 x 12% writer.WriteLine("05{0}", pvtTerminalReportDetails.VoidSales.ToString("####.#0").PadLeft(16, '0')); writer.WriteLine("06{0}", pvtTerminalReportDetails.NoOfVoidTransactions.ToString("####").PadLeft(16, '0')); writer.WriteLine("07{0}", decDiscountNetOfSeniorCitizen.ToString("####.#0").PadLeft(16, '0')); writer.WriteLine("08{0}", lngDiscountCountNetOfSeniorCitizen.ToString("####").PadLeft(16, '0')); writer.WriteLine("09{0}", Convert.ToDecimal(pvtTerminalReportDetails.RefundSales * Convert.ToDecimal(1.12)).ToString("####.#0").PadLeft(16, '0')); // with VAT writer.WriteLine("10{0}", pvtTerminalReportDetails.NoOfRefundTransactions.ToString("####").PadLeft(16, '0')); writer.WriteLine("11{0}", decSeniorCitizenDiscount.ToString("####.#0").PadLeft(16, '0')); writer.WriteLine("12{0}", lngSeniorCitizenDiscountCount.ToString("####").PadLeft(16, '0')); writer.WriteLine("13{0}", pvtTerminalReportDetails.TotalCharge.ToString("####.#0").PadLeft(16, '0')); if (pvtTerminalReportDetails.ZReadCount == 0) { writer.WriteLine("14{0}", "0".PadLeft(16, '0')); } else { writer.WriteLine("14{0}", Convert.ToInt64(pvtTerminalReportDetails.ZReadCount - 1).ToString("####").PadLeft(16, '0')); } writer.WriteLine("15{0}", pvtTerminalReportDetails.OldGrandTotal.ToString("####.#0").PadLeft(16, '0')); // line 15 writer.WriteLine("16{0}", pvtTerminalReportDetails.ZReadCount.ToString("####").PadLeft(16, '0')); writer.WriteLine("17{0}", pvtTerminalReportDetails.NewGrandTotal.ToString("####.#0").PadLeft(16, '0')); // line 3 - line 7 - line 11 - line 27 + Line 15 writer.WriteLine("18{0}", pvtTerminalReportDetails.DateLastInitializedToDisplay.ToString("MM/dd/yyyy").PadLeft(16, '0')); writer.WriteLine("19{0}", pvtTerminalReportDetails.PromotionalItems.ToString("####.#0").PadLeft(16, '0')); writer.WriteLine("20{0}", "0.00".PadLeft(16, '0')); writer.WriteLine("21{0}", pvtTerminalReportDetails.LocalTax.ToString("####.#0").PadLeft(16, '0')); writer.WriteLine("22{0}", pvtTerminalReportDetails.CreditCardSales.ToString("####.#0").PadLeft(16, '0')); writer.WriteLine("23{0}", deCreditCardSalesTax.ToString("####.#0").PadLeft(16, '0')); writer.WriteLine("24{0}", pvtTerminalReportDetails.NonVATableAmount.ToString("####.#0").PadLeft(16, '0')); //08Jan2014 added as per new requirement writer.WriteLine("25{0}", "0.00".PadLeft(16, '0')); // Pharma Sales writer.WriteLine("26{0}", "0.00".PadLeft(16, '0')); // Non Pharma Sales writer.WriteLine("27{0}", decPWDDiscount.ToString("####.#0").PadLeft(16, '0')); // PWD - Persons with Disability Discount writer.WriteLine("28{0}", "0.00".PadLeft(16, '0')); // SALES NOT SUBJECT TO PERCENTAGE RENT (ex. sales from fixed rent kiosks) writer.WriteLine("29{0}", pvtTerminalReportDetails.TotalReprintedTransaction.ToString("####.#0").PadLeft(16, '0')); // Total Sales of Reprinted Transaction writer.WriteLine("30{0}", pvtTerminalReportDetails.NoOfReprintedTransaction.ToString("####").PadLeft(16, '0')); // No. of Reprinted Transaction writer.Flush(); writer.Close(); strRetValue = stDailyTableName; } catch (Exception ex) { throw ex; } return(strRetValue); }
public bool CreateAndTransferFile(Int32 BranchID, string TerminalNo, DateTime pvtDateInitialized) { bool bolRetValue = false; DateTime dteDateToprocess = pvtDateInitialized; Event clsEvent = new Event(); try { clsEvent.AddEventLn("Starting RLC FILE CREATOR.", true); clsEvent.AddEventLn("==================================================", true); clsEvent.AddEventLn("= RLC FILE CREATOR =", true); clsEvent.AddEventLn("==================================================", true); /*********************************************************************** * Check the destination dir if existing. * ********************************************************************/ clsEvent.AddEventLn("Checking directory settings.", true); string dir = mclsRLCDetails.OutputDirectory; dir = dir.Replace("{YYYY}", DateTime.Now.ToString("yyyy")); dir = dir.Replace("{MM}", DateTime.Now.ToString("MM")); dir = dir.Replace("{MMM}", DateTime.Now.ToString("MMM")); dir = dir.Replace("{MMMM}", DateTime.Now.ToString("MMMM")); if (!System.IO.Directory.Exists(dir)) { clsEvent.AddEventLn("Directory [" + dir + "] does not exist.", true); System.IO.Directory.CreateDirectory(dir); } else { clsEvent.AddEventLn("Directory [" + dir + "] exist.", true); } mclsRLCDetails.OutputDirectory = dir; /*********************************************************************** * GET The report of Current Terminal using Specified InitializationDate * ********************************************************************/ Data.TerminalReportHistory clsTerminalReportHistory = new Data.TerminalReportHistory(); Data.TerminalReportDetails clsTerminalReportDetail = clsTerminalReportHistory.Details(BranchID, TerminalNo, dteDateToprocess); Data.SalesTransactions clsSalesTransactions = new Data.SalesTransactions(clsTerminalReportHistory.Connection, clsTerminalReportHistory.Transaction); long lngSeniorCitizenDiscountCount = 0; decimal decSeniorCitizenDiscount = clsSalesTransactions.SeniorCitizenDiscounts(clsTerminalReportDetail.BranchID, clsTerminalReportDetail.TerminalNo, clsTerminalReportDetail.BeginningTransactionNo, clsTerminalReportDetail.EndingTransactionNo, out lngSeniorCitizenDiscountCount); long lngPWDDiscountCount = 0; decimal decPWDDiscount = clsSalesTransactions.PersonWithDisabilityDiscounts(clsTerminalReportDetail.BranchID, clsTerminalReportDetail.TerminalNo, clsTerminalReportDetail.BeginningTransactionNo, clsTerminalReportDetail.EndingTransactionNo, out lngPWDDiscountCount); string stDailyTableName = CreateDailySales(dteDateToprocess, clsTerminalReportDetail, decSeniorCitizenDiscount, lngSeniorCitizenDiscountCount, decPWDDiscount, lngPWDDiscountCount); clsTerminalReportHistory.CommitAndDispose(); bool bolTransferFile = TransferFile(stDailyTableName); if (bolTransferFile) { clsTerminalReportHistory = new Data.TerminalReportHistory(); clsTerminalReportHistory.UpdateTerminalReportBatchCounter(clsTerminalReportDetail.TerminalNo, dteDateToprocess); clsTerminalReportHistory.UpdateTerminalReportIsMallFileUploadComplete(clsTerminalReportDetail.TerminalNo, dteDateToprocess, true); if (clsTerminalReportDetail.BatchCounter == 1) { clsTerminalReportHistory.UpdateTerminalReportMallForwarderFileName(clsTerminalReportDetail.TerminalNo, dteDateToprocess, stDailyTableName); } clsTerminalReportHistory.CommitAndDispose(); bolRetValue = true; } clsEvent.AddEventLn("Record for [" + dteDateToprocess.ToString("yyyy-MM-dd HH:mm:ss") + "] BacthCounter:" + clsTerminalReportDetail.BatchCounter.ToString() + " has been created for RLC.", true); } catch (Exception ex) { clsEvent.AddErrorEventLn(ex); throw ex; } clsEvent.AddEventLn("RLC FILE CREATOR exited.", true); return(bolRetValue); }
private void InsertDailySales(string pvtDailyTableName, Data.TerminalReportDetails pvtTerminalReportDetails) { try { string SQL = "INSERT INTO " + pvtDailyTableName + "(" + "TRANDATE," + "OLDGT," + "NEWGT," + "DLYSALE," + "TOTDISC," + "TOTREF," + "TOTCAN," + "VAT," + "TENTNAME," + "BEGININV," + "ENDIND," + "BEGOR," + "ENDOR," + "TRANCNT," + "LOCALTAX," + "SERVCHARGE," + "NOTAXSALE," + "RAWGROSS," + "DLYLOCTAX," + "OTHERS," + "TERMNUM" + ") VALUES (" + "@TRANDATE," + "@OLDGT," + "@NEWGT," + "@DLYSALE," + "@TOTDISC," + "@TOTREF," + "@TOTCAN," + "@VAT," + "@TENTNAME," + "@BEGININV," + "@ENDIND," + "@BEGOR," + "@ENDOR," + "@TRANCNT," + "@LOCALTAX," + "@SERVCHARGE," + "@NOTAXSALE," + "@RAWGROSS," + "@DLYLOCTAX," + "@OTHERS," + "@TERMNUM" + ");"; OleDbConnection cn = GetConnection(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = cn; cmd.Transaction = mTransaction; cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = SQL; OleDbParameter prmTRANDATE = new OleDbParameter("@TRANDATE", System.Data.OleDb.OleDbType.Date, 8); prmTRANDATE.Value = pvtTerminalReportDetails.DateLastInitialized.ToString("MM/dd/yyyy"); cmd.Parameters.Add(prmTRANDATE); OleDbParameter prmOLDGT = new OleDbParameter("@OLDGT", System.Data.OleDb.OleDbType.Numeric, 17); prmOLDGT.Value = pvtTerminalReportDetails.OldGrandTotal; cmd.Parameters.Add(prmOLDGT); OleDbParameter prmNEWGT = new OleDbParameter("@NEWGT", System.Data.OleDb.OleDbType.Numeric, 17); prmNEWGT.Value = pvtTerminalReportDetails.NewGrandTotal; cmd.Parameters.Add(prmNEWGT); OleDbParameter prmDLYSALE = new OleDbParameter("@DLYSALE", System.Data.OleDb.OleDbType.Numeric, 13); prmDLYSALE.Value = pvtTerminalReportDetails.DailySales; cmd.Parameters.Add(prmDLYSALE); OleDbParameter prmTOTDISC = new OleDbParameter("@TOTDISC", System.Data.OleDb.OleDbType.Numeric, 13); prmTOTDISC.Value = pvtTerminalReportDetails.TotalDiscount; cmd.Parameters.Add(prmTOTDISC); OleDbParameter prmTOTREF = new OleDbParameter("@TOTREF", System.Data.OleDb.OleDbType.Numeric, 13); prmTOTREF.Value = pvtTerminalReportDetails.RefundSales; cmd.Parameters.Add(prmTOTREF); OleDbParameter prmTOTCAN = new OleDbParameter("@TOTCAN", System.Data.OleDb.OleDbType.Numeric, 13); prmTOTCAN.Value = pvtTerminalReportDetails.VoidSales; cmd.Parameters.Add(prmTOTCAN); OleDbParameter prmVAT = new OleDbParameter("@VAT", System.Data.OleDb.OleDbType.Numeric, 13); prmVAT.Value = pvtTerminalReportDetails.VAT; cmd.Parameters.Add(prmVAT); OleDbParameter prmTENTNAME = new OleDbParameter("@TENTNAME", System.Data.OleDb.OleDbType.VarChar, 10); prmTENTNAME.Value = mclsAyalaDetails.TenantName; cmd.Parameters.Add(prmTENTNAME); OleDbParameter prmBEGININV = new OleDbParameter("@BEGININV", System.Data.OleDb.OleDbType.VarChar, 6); prmBEGININV.Value = Convert.ToInt64(pvtTerminalReportDetails.BeginningTransactionNo).ToString(); cmd.Parameters.Add(prmBEGININV); OleDbParameter prmENDIND = new OleDbParameter("@ENDIND", System.Data.OleDb.OleDbType.VarChar, 6); prmENDIND.Value = Convert.ToInt64(pvtTerminalReportDetails.EndingTransactionNo).ToString(); cmd.Parameters.Add(prmENDIND); OleDbParameter prmBEGOR = new OleDbParameter("@BEGOR", System.Data.OleDb.OleDbType.VarChar, 6); prmBEGOR.Value = ""; cmd.Parameters.Add(prmBEGOR); OleDbParameter prmENDOR = new OleDbParameter("@ENDOR", System.Data.OleDb.OleDbType.VarChar, 6); prmENDOR.Value = ""; cmd.Parameters.Add(prmENDOR); OleDbParameter prmTRANCNT = new OleDbParameter("@TRANCNT", System.Data.OleDb.OleDbType.Numeric, 9); prmTRANCNT.Value = pvtTerminalReportDetails.NoOfTotalTransactions; cmd.Parameters.Add(prmTRANCNT); OleDbParameter prmLOCALTAX = new OleDbParameter("@LOCALTAX", System.Data.OleDb.OleDbType.Numeric, 13); prmLOCALTAX.Value = pvtTerminalReportDetails.LocalTax; cmd.Parameters.Add(prmLOCALTAX); OleDbParameter prmSERVCHARGE = new OleDbParameter("@SERVCHARGE", System.Data.OleDb.OleDbType.Numeric, 13); prmSERVCHARGE.Value = 0; cmd.Parameters.Add(prmSERVCHARGE); OleDbParameter prmNOTAXSALE = new OleDbParameter("@NOTAXSALE", System.Data.OleDb.OleDbType.Numeric, 13); prmNOTAXSALE.Value = pvtTerminalReportDetails.NonVATableAmount; cmd.Parameters.Add(prmNOTAXSALE); OleDbParameter prmRAWGROSS = new OleDbParameter("@RAWGROSS", System.Data.OleDb.OleDbType.Numeric, 13); prmRAWGROSS.Value = pvtTerminalReportDetails.GrossSales; cmd.Parameters.Add(prmRAWGROSS); OleDbParameter prmDLYLOCTAX = new OleDbParameter("@DLYLOCTAX", System.Data.OleDb.OleDbType.Numeric, 13); prmDLYLOCTAX.Value = pvtTerminalReportDetails.LocalTax; //pvtTerminalReportDetails.DailySales - pvtTerminalReportDetails.LocalTax; cmd.Parameters.Add(prmDLYLOCTAX); OleDbParameter prmOTHERS = new OleDbParameter("@OTHERS", System.Data.OleDb.OleDbType.Numeric); prmOTHERS.Value = 0; cmd.Parameters.Add(prmOTHERS); OleDbParameter prmTERMNUM = new OleDbParameter("@TERMNUM", System.Data.OleDb.OleDbType.VarChar, 3); prmTERMNUM.Value = pvtTerminalReportDetails.TerminalNo; cmd.Parameters.Add(prmTERMNUM); cmd.ExecuteNonQuery(); } catch (Exception ex) { TransactionFailed = true; if (IsInTransaction) { mTransaction.Rollback(); mConnection.Close(); mConnection.Dispose(); } throw ex; } }
private string CreateHourlySales(Int32 BranchID, string TerminalNo, DateTime ProcessDate, Data.TerminalReportDetails TerminalReportDetails) { string strRetValue = ""; DateTime dteDateFrom = ProcessDate; Data.TerminalReportHistory clsTerminalReportHistory = new Data.TerminalReportHistory(); DateTime dteDateTo = DateTime.MinValue; try { dteDateTo = clsTerminalReportHistory.NEXTDateLastInitialized(BranchID, TerminalNo, ProcessDate); } catch { } if (dteDateTo == DateTime.MinValue) { Event clsEvent = new Event(); clsEvent.AddEventLn("HourlySales: Did not found MAXDateLastInitialized from Terminal Report History. Using the MAXDateLastInitialized from terminal report", true); Data.TerminalReport clsTerminalReport = new Data.TerminalReport(clsTerminalReportHistory.Connection, clsTerminalReportHistory.Transaction); dteDateTo = clsTerminalReport.MAXDateLastInitialized(BranchID, TerminalNo, ProcessDate); } System.Data.DataTable dtHourlyReport = clsTerminalReportHistory.HourlyReport(BranchID, TerminalNo, dteDateFrom, dteDateTo); clsTerminalReportHistory.CommitAndDispose(); string stHourlyTableName = mclsFSIDetails.OutputDirectory + "\\H" + mclsFSIDetails.TenantName.Substring(0, 4) + TerminalReportDetails.TerminalNo + TerminalReportDetails.BatchCounter.ToString() + "." + TerminalReportDetails.DateLastInitializedToDisplay.ToString("MM").Replace("10", "A").Replace("11", "B").Replace("12", "C").Replace("0", "") + TerminalReportDetails.DateLastInitializedToDisplay.ToString("dd"); if (File.Exists(stHourlyTableName)) { File.Delete(stHourlyTableName); } writer = File.AppendText(stHourlyTableName); writer.WriteLine("01{0}", mclsFSIDetails.TenantCode); writer.WriteLine("02{0}", TerminalNo); DateTime dtePreviousTransactionDate = DateTime.MinValue; foreach (System.Data.DataRow dr in dtHourlyReport.Rows) { if (dtePreviousTransactionDate != Convert.ToDateTime(dr["TransactionDate"])) { writer.WriteLine("03{0}", Convert.ToDateTime(dr["TransactionDate"]).ToString("MMddyyyy")); dtePreviousTransactionDate = Convert.ToDateTime(dr["TransactionDate"]); } writer.WriteLine("04{0}", dr["Time"].ToString().Substring(0, 2)); writer.WriteLine("05{0}", Convert.ToDecimal(Convert.ToDecimal(dr["Amount"]) - Convert.ToDecimal(dr["VAT"])).ToString("####.#0").Replace(".", "")); writer.WriteLine("06{0}", Convert.ToInt64(dr["TranCount"]).ToString("####")); } writer.WriteLine("07{0}", TerminalReportDetails.DailySales.ToString("####.#0").Replace(".", "")); writer.WriteLine("08{0}", TerminalReportDetails.NoOfClosedTransactions.ToString("####")); writer.Flush(); writer.Close(); strRetValue = stHourlyTableName; return(strRetValue); }