/// <summary> /// get all Loyalty customers /// </summary> /// <param name="additionalCriteria"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public List <Customer> GetLoyaltyCustomers(string additionalCriteria, int pageIndex = 1, int pageSize = 100) { var dateStart = DateTime.Now; _performancelog.Debug($"Start,CustomerService,GetLoyaltyCustomers,{string.Empty},{DateTime.Now:hh.mm.ss.ffffff}"); var sqlStmt = "SELECT CLIENT.CL_CODE AS [Code]," + "Client.LO_Num as [Loyalty]," + " CLIENT.CL_NAME AS [Name]," + " CLIENT.CL_PHONE AS [Phone]," + "client.Lo_Points as [Points]" + " FROM CLIENT" + " WHERE (CL_STATUS is null OR CL_Status IN (\'A\',\'F\',\'\')) " + additionalCriteria + " ORDER BY CLIENT.CL_CODE"; var dt = GetPagedRecords(sqlStmt, DataSource.CSCMaster, pageIndex, pageSize); var customers = new List <Customer>(); if (dt != null) { customers.AddRange(from DataRow fields in dt.Rows select new Customer { Name = CommonUtility.GetStringValue(fields["Name"]), Code = CommonUtility.GetStringValue(fields["Code"]), Phone = CommonUtility.GetStringValue(fields["Phone"]), Loyalty_Points = CommonUtility.GetDoubleValue(fields["Points"]), Loyalty_Code = CommonUtility.GetStringValue(fields["Loyalty"]) }); } _performancelog.Debug($"End,CustomerService,GetLoyaltyCustomers,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}"); return(customers); }
/// <summary> /// Get Line Kits /// </summary> /// <param name="saleNumber"></param> /// <param name="lineNumber"></param> /// <param name="dataSource"></param> /// <returns></returns> private Line_Kits GetLineKits(int saleNumber, int lineNumber, DataSource dataSource) { var dateStart = DateTime.Now; _performancelog.Debug($"Start,ReturnSaleService,GetLineKits,{string.Empty},{dateStart:hh.mm.ss.ffffff}"); Line_Kits lineKits = new Line_Kits(); // Get the kit items in the line var rsLineKit = GetRecords("Select * FROM SaleKit WHERE SaleKit.Sale_No = " + CommonUtility.GetStringValue(saleNumber) + " AND SaleKit.Line_No = " + CommonUtility.GetStringValue(lineNumber) + " ", dataSource); foreach (DataRow rsLineKitFields in rsLineKit.Rows) { // Charges on Kit items var rsLineKitChg = GetRecords("Select * From SaleKitChg Where SaleKitChg.Sale_No = " + CommonUtility.GetStringValue(saleNumber) + " AND SaleKitChg.Line_No = " + CommonUtility.GetStringValue(lineNumber) + " AND SaleKitChg.Kit_Item = \'" + CommonUtility.GetStringValue(rsLineKitFields["Kit_Item"]) + "\' ", dataSource); var lkc = new K_Charges(); foreach (DataRow rsLineKitChgFields in rsLineKitChg.Rows) { // Taxes on Charges on Kit items var rsCgt = GetRecords("Select * From SaleKitChgTax Where SaleKitChgTax.Sale_No = " + CommonUtility.GetStringValue(saleNumber) + " AND SaleKitChgTax.Line_No = " + CommonUtility.GetStringValue(lineNumber) + " AND SaleKitChgTax.Kit_Item = \'" + CommonUtility.GetStringValue(rsLineKitFields["Kit_Item"]) + "\' AND SaleKitChgTax.As_Code = \'" + CommonUtility.GetStringValue(rsLineKitChgFields["As_Code"]) + "\' ", dataSource); var cgt = new Charge_Taxes(); foreach (DataRow rsCgtFields in rsCgt.Rows) { cgt.Add(CommonUtility.GetStringValue(rsCgtFields["Tax_Name"]), CommonUtility.GetStringValue(rsCgtFields["Tax_Code"]), CommonUtility.GetFloatValue(rsCgtFields["Tax_Rate"]), CommonUtility.GetBooleanValue(rsCgtFields["Tax_Included"]), ""); } lkc.Add(CommonUtility.GetDoubleValue(rsLineKitChgFields["price"]), CommonUtility.GetStringValue(rsLineKitChgFields["Description"]), CommonUtility.GetStringValue(rsLineKitChgFields["As_Code"]), cgt, ""); } lineKits.Add(CommonUtility.GetStringValue(rsLineKitFields["Kit_Item"]), CommonUtility.GetStringValue(rsLineKitFields["Descript"]), CommonUtility.GetFloatValue(rsLineKitFields["Quantity"]), CommonUtility.GetFloatValue(rsLineKitFields["Base"]), CommonUtility.GetFloatValue(rsLineKitFields["Fraction"]), CommonUtility.GetFloatValue(rsLineKitFields["Alloc"]), CommonUtility.GetStringValue(rsLineKitFields["Serial"]), lkc, ""); } _performancelog.Debug($"End,ReturnSaleService,GetLineKits,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}"); return(lineKits); }
/// <summary> /// Search Customers /// </summary> /// <param name="searchCriteria"></param> /// <param name="totalrecords"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public List <Customer> SearchArCustomer(string searchCriteria, out int totalrecords, int pageIndex = 1, int pageSize = 100) { var dateStart = DateTime.Now; _performancelog.Debug($"Start,CustomerService,SearchArCustomer,{string.Empty},{DateTime.Now:hh.mm.ss.ffffff}"); var customers = new List <Customer>(); var query = "Select * from Client " + " WHERE CL_ArCust = 1" + " AND (CL_NAME like \'%" + searchCriteria + "%\' OR " + " CL_CODE like \'%" + searchCriteria + "%\' OR " + " CL_PHONE like \'%" + searchCriteria + "%\' )" + "ORDER BY CLIENT.CL_CODE, CLIENT.CL_NAME, CLIENT.CL_PHONE"; var dt = GetPagedRecords(query, DataSource.CSCMaster, pageIndex, pageSize); totalrecords = 0; if (dt != null) { totalrecords = dt.Rows.Count; customers.AddRange(from DataRow fields in dt.Rows select new Customer { Code = CommonUtility.GetStringValue(fields["CL_CODE"]), Name = CommonUtility.GetStringValue(fields["CL_NAME"]), Phone = CommonUtility.GetStringValue(fields["CL_PHONE"]), Current_Balance = CommonUtility.GetDoubleValue(fields["CL_CURBAL"]), Credit_Limit = CommonUtility.GetDoubleValue(fields["CL_LIMIT"]) }); } _performancelog.Debug($"End,CustomerService,SearchArCustomer,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}"); return(customers); }
/// <summary> /// Method to get promos for today /// </summary> /// <returns>List of promos</returns> public List <Promo> GetPromosForToday() { var dateStart = DateTime.Now; _performancelog.Debug($"Start,PromoService,GetPromosForToday,{string.Empty},{DateTime.Now:hh.mm.ss.ffffff}"); // August 21, 2008 took out the quantity validation to implement mix and match promotions var strSql = "SELECT DISTINCT [PromoHeader].[PromoID] AS PromoID, [PromoHeader].[Day] AS Day,[PromoDetail].[Qty] AS Qty, [PromoDetail].[Link] AS Link, [PromoDetail].[Amount] AS Amount,[PromoDetail].[Stock_Code],[PromoDetail].[Dept],[PromoDetail].[Sub_Dept],[PromoDetail].[SubDetail] FROM PromoHeader LEFT JOIN PromoDetail ON [PromoHeader].[PromoID] =[PromoDetail].[PromoID] WHERE (Day IS NULL OR Day=0 OR Day=" + Convert.ToString(DateAndTime.Weekday(DateAndTime.Today)) + ") AND [PromoHeader].[EndDate]>= \'" + DateTime.Now.ToString("yyyyMMdd") + "\' AND [PromoHeader].[StartDate]<= \'" + DateTime.Now.ToString("yyyyMMdd") + "\'"; var rsPromo = GetRecords(strSql, DataSource.CSCMaster); var promos = (from DataRow fields in rsPromo.Rows select new Promo { StockCode = CommonUtility.GetStringValue(fields["Stock_Code"]), Dept = CommonUtility.GetStringValue(fields["Dept"]), SubDept = CommonUtility.GetStringValue(fields["Sub_Dept"]), SubDetail = CommonUtility.GetStringValue(fields["SubDetail"]), PromoID = CommonUtility.GetStringValue(fields["PromoID"]), Day = CommonUtility.GetByteValue(fields["Day"]), TotalQty = CommonUtility.GetShortValue(fields["Qty"]), MaxLink = CommonUtility.GetShortValue(fields["Link"]), Amount = CommonUtility.GetDoubleValue(fields["Amount"]) }).ToList(); _performancelog.Debug($"End,PromoService,GetPromosForToday,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}"); return(promos); }
/// <summary> /// Get AR Customers /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public List <Customer> GetArCustomers(int pageIndex = 1, int pageSize = 100) { var dateStart = DateTime.Now; _performancelog.Debug($"Start,CustomerService,GetArCustomers,{string.Empty},{DateTime.Now:hh.mm.ss.ffffff}"); string sqlStmt = "SELECT CL_Code AS Code, CL_Name AS Name, CL_Phone AS Phone,CL_CURBAL,CL_LIMIT FROM Client WHERE CL_ARCust = 1 ORDER BY Cl_Code "; var dt = GetPagedRecords(sqlStmt, DataSource.CSCMaster, pageIndex, pageSize); var customers = new List <Customer>(); if (dt != null) { customers.AddRange(from DataRow fields in dt.Rows select new Customer { Name = CommonUtility.GetStringValue(fields["Name"]), Code = CommonUtility.GetStringValue(fields["Code"]), Phone = CommonUtility.GetStringValue(fields["Phone"]), Current_Balance = CommonUtility.GetDoubleValue(fields["CL_CURBAL"]), Credit_Limit = CommonUtility.GetDoubleValue(fields["CL_LIMIT"]) }); } _performancelog.Debug($"End,CustomerService,GetArCustomers,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}"); return(customers); }
/// <summary> /// Get Cigarette Equivalent Units /// </summary> /// <param name="productType">Product type</param> /// <returns>Cigratte equivalent units</returns> public double GetCigaretteEquivalentUnits(string productType) { var sSql = "SELECT Value FROM Admin WHERE Name=" + "'" + productType + "'"; var oRecs = GetRecords(sSql, DataSource.CSCAdmin); var returnValue = CommonUtility.GetDoubleValue(oRecs.Rows[0]["Value"]); return(returnValue); }
/// <summary> /// Get Quota /// </summary> /// <param name="fieldName">Field name</param> /// <param name="treatyNumber">Treaty number</param> /// <returns>Quota</returns> public double GetQuota(string fieldName, string treatyNumber) { var sSql = "SELECT " + fieldName + " As Quota " + " FROM TreatyNo WHERE TreatyNo=\'" + treatyNumber + "\'"; var oRecs = GetRecords(sSql, DataSource.CSCMaster); var dOutQuota = oRecs.Rows.Count == 0 ? 0 : CommonUtility.GetDoubleValue(oRecs.Rows[0]["Quota"]); return(dOutQuota); }
/// <summary> /// Method to get line kit /// </summary> /// <param name="db">Data source</param> /// <param name="sn">Sale number</param> /// <param name="ln">Line number</param> /// <returns>Line kits</returns> public Line_Kits Get_Line_Kit(DataSource db, int sn, int ln) { Line_Kits lk = new Line_Kits(); // Get the kit items in the line var rsLineKit = GetRecords("Select * FROM SaleKit WHERE SaleKit.Sale_No = " + Convert.ToString(sn) + " AND " + " SaleKit.Line_No = " + Convert.ToString(ln) + " ", db); foreach (DataRow lineKit in rsLineKit.Rows) { // Charges on Kit items var rsLineKitChg = GetRecords("Select * FROM SaleKitChg WHERE SaleKitChg.Sale_No = " + Convert.ToString(sn) + " AND " + " SaleKitChg.Line_No = " + Convert.ToString(ln) + " AND " + " SaleKitChg.Kit_Item = \'" + CommonUtility.GetStringValue(lineKit["Kit_Item"]) + "\' ", db); var lkc = new K_Charges(); foreach (DataRow kitCharge in rsLineKitChg.Rows) { // Taxes on Charges on Kit items var rsCgt = GetRecords("Select * FROM SaleKitChgTax WHERE SaleKitChgTax.Sale_No = " + Convert.ToString(sn) + " AND " + " SaleKitChgTax.Line_No = " + Convert.ToString(ln) + " AND " + " SaleKitChgTax.Kit_Item = \'" + CommonUtility.GetStringValue(lineKit["Kit_Item"]) + "\' AND " + " SaleKitChgTax.As_Code = \'" + CommonUtility.GetStringValue(kitCharge["As_Code"]) + "\' ", db); var cgt = new Charge_Taxes(); foreach (DataRow tax in rsCgt.Rows) { cgt.Add(CommonUtility.GetStringValue(tax["Tax_Name"]), CommonUtility.GetStringValue(tax["Tax_Code"]), CommonUtility.GetFloatValue(tax["Tax_Rate"]), CommonUtility.GetBooleanValue(tax["Tax_Included"]), ""); } lkc.Add(CommonUtility.GetDoubleValue(kitCharge["price"]), CommonUtility.GetStringValue(kitCharge["Description"]), CommonUtility.GetStringValue(kitCharge["As_Code"]), cgt, ""); } lk.Add(CommonUtility.GetStringValue(lineKit["Kit_Item"]), CommonUtility.GetStringValue(lineKit["Descript"]), CommonUtility.GetFloatValue(lineKit["Quantity"]), CommonUtility.GetFloatValue(lineKit["Base"]), CommonUtility.GetFloatValue(lineKit["Fraction"]), CommonUtility.GetFloatValue(lineKit["Alloc"]), CommonUtility.GetStringValue(lineKit["Serial"]), lkc, ""); } var returnValue = lk; return(returnValue); }
///// <summary> ///// Method to get max link ///// </summary> ///// <param name="promoId"></param> ///// <returns>Max link</returns> //public short GetMaxLink(string promoId) //{ // var dateStart = DateTime.Now; // _performancelog.Debug($"Start,PromoService,GetMaxLink,{string.Empty},{DateTime.Now:hh.mm.ss.ffffff}"); // var rsTemp = GetRecords("SELECT MAX(Link) AS MaxLink FROM PromoDetail WHERE PromoID=\'" + promoId + "\'", DataSource.CSCMaster); // if (rsTemp.Rows.Count != 0) // { // var fields = rsTemp.Rows[0]; // _performancelog.Debug($"End,PromoService,GetMaxLink,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}"); // return CommonUtility.GetShortValue(fields["MaxLink"]); // } // _performancelog.Debug($"End,PromoService,GetMaxLink,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}"); // return 0; //} ///// <summary> ///// Method to get number of links ///// </summary> ///// <param name="promoId">Promo id</param> ///// <returns>Number of links</returns> //public List<int> GetNumberOfLinks(string promoId) //{ // var dateStart = DateTime.Now; // _performancelog.Debug($"Start,PromoService,GetNumberOfLinks,{string.Empty},{DateTime.Now:hh.mm.ss.ffffff}"); // var rsTemp = GetRecords("SELECT SUM(Link)/Link AS NrOfLinks, Link FROM PromoDetail WHERE PromoID=\'" + promoId + "\' GROUP BY Link ORDER BY Link", DataSource.CSCMaster); // var links = (from DataRow fields in rsTemp.Rows select CommonUtility.GetShortValue(fields["NrOfLinks"])).Select(dummy => (int)dummy).ToList(); // _performancelog.Debug($"End,PromoService,GetNumberOfLinks,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}"); // return links; //} /// <summary> /// Method to get all promos for today /// </summary> /// <param name="optPromoId">Promo id</param> /// <returns>Promo headers</returns> public List <Promo> GetPromoHeadersForToday(string optPromoId) { var dateStart = DateTime.Now; _performancelog.Debug($"Start,PromoService,GetPromoHeadersForToday,{string.Empty},{DateTime.Now:hh.mm.ss.ffffff}"); var promos = new List <Promo>(); _connection = new SqlConnection(GetConnectionString(DataSource.CSCMaster)); if (_connection.State == ConnectionState.Closed) { _connection.Open(); } SqlCommand cmd = new SqlCommand("GetPromoHeaders", _connection) { CommandType = CommandType.StoredProcedure }; cmd.Parameters.Add(new SqlParameter { ParameterName = "@optPromoId", Value = optPromoId }); cmd.Parameters.Add(new SqlParameter { ParameterName = "@dt", Value = DateTime.Now.ToString("yyyyMMdd") }); var rdr = cmd.ExecuteReader(); if (!rdr.HasRows) { return(promos); } var stockInfo = new Sale_Line(); while (rdr.Read()) { Promo promo = new Promo(); promo.PromoID = CommonUtility.GetStringValue(rdr["PromoID"]); promo.Description = CommonUtility.GetStringValue(rdr["Description"]); promo.DiscType = CommonUtility.GetStringValue(rdr["DiscType"]); promo.Amount = CommonUtility.GetDoubleValue(rdr["price"]); promo.PrType = CommonUtility.GetStringValue(rdr["PrType"]); promo.MaxLink = CommonUtility.GetShortValue(rdr["MaxLink"]); promo.MultiLink = CommonUtility.GetIntergerValue(rdr["NrOfLinks"]) > 1 ? true : false; promos.Add(promo); } _performancelog.Debug($"End,PromoService,GetPromoHeadersForToday,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}"); return(promos); }
/// <summary> /// Method to get orginal saleLines /// </summary> /// <param name="invoiceId">Sale number</param> /// <param name="tillNumber">Till number</param> /// <param name="dataSource">Data source</param> /// <returns>List of sale lines</returns> public List <Sale_Line> GetOrginalSaleLines(int invoiceId, int tillNumber, DataSource dataSource) { var saleLines = new List <Sale_Line>(); var rsDetail = GetRecords(" SELECT * FROM SaleLine " + " WHERE SaleLine.Sale_No = " + Convert.ToString(invoiceId) + " " + " and TILL_NUM=" + tillNumber + " ORDER BY SaleLine.Line_Num ", dataSource); foreach (DataRow row in rsDetail.Rows) { saleLines.Add(new Sale_Line { Dept = CommonUtility.GetStringValue(row["Dept"]), Sub_Dept = CommonUtility.GetStringValue(row["Sub_Dept"]), Sub_Detail = CommonUtility.GetStringValue(row["Sub_Detail"]), Stock_Code = CommonUtility.GetStringValue(row["Stock_Code"]), PLU_Code = CommonUtility.GetStringValue(row["PLU_Code"]), Line_Num = CommonUtility.GetShortValue(row["Line_Num"]), Price_Type = CommonUtility.GetCharValue(row["Price_Type"]), Quantity = CommonUtility.GetFloatValue(row["Quantity"]), Amount = CommonUtility.GetDecimalValue(row["Amount"]), Discount_Adjust = CommonUtility.GetDoubleValue(row["Disc_adj"]), Line_Discount = CommonUtility.GetDoubleValue(row["Discount"]), Discount_Type = CommonUtility.GetStringValue(row["Disc_Type"]), Discount_Code = CommonUtility.GetStringValue(row["Disc_Code"]), Discount_Rate = CommonUtility.GetFloatValue(row["Disc_Rate"]), DiscountName = CommonUtility.GetStringValue(row["DiscountName"]), Associate_Amount = CommonUtility.GetDecimalValue(row["Assoc_Amt"]), User = CommonUtility.GetStringValue(row["User"]), Description = CommonUtility.GetStringValue(row["Descript"]), Loyalty_Save = CommonUtility.GetFloatValue(row["Loyl_Save"]), Units = CommonUtility.GetStringValue(row["Units"]), Serial_No = CommonUtility.GetStringValue(row["Serial_No"]), Prepay = Convert.ToBoolean(row["Prepay"]), pumpID = CommonUtility.GetByteValue(row["pumpID"]), GradeID = CommonUtility.GetByteValue(row["GradeID"]), PositionID = CommonUtility.GetByteValue(row["PositionID"]), IsTaxExemptItem = CommonUtility.GetBooleanValue(row["TaxExempt"]), Total_Amount = CommonUtility.GetDecimalValue(row["Total_Amt"]), Regular_Price = CommonUtility.GetDoubleValue(row["Reg_Price"]), price = CommonUtility.GetDoubleValue(row["price"]), FuelRebateEligible = CommonUtility.GetBooleanValue(row["FuelRebateUsed"]), FuelRebate = CommonUtility.GetDecimalValue(row["RebateDiscount"]), PromoID = CommonUtility.GetStringValue(row["PromoID"]) }); } return(saleLines); }
/// <summary> /// Save DipInputs /// </summary> /// <param name="dipInputs">List of dip inputs</param> public void SaveDipInputs(List <DipInput> dipInputs) { foreach (DipInput dipInput in dipInputs) { var addNew = false; var strSql = "select * from DipInput where TankID=" + Convert.ToString(dipInput.TankId); _connection = new SqlConnection(GetConnectionString(DataSource.CSCTrans)); if (_connection.State == ConnectionState.Closed) { _connection.Open(); } _dataTable = new DataTable(); _adapter = new SqlDataAdapter(strSql, _connection); _adapter.Fill(_dataTable); DataRow fields; if (_dataTable.Rows.Count == 0) { fields = _dataTable.NewRow(); fields["TankID"] = CommonUtility.GetDoubleValue(dipInput.TankId); addNew = true; } else { fields = _dataTable.Rows[0]; } fields["grade"] = CommonUtility.GetDoubleValue(dipInput.GradeId); fields["DipValue"] = CommonUtility.GetDoubleValue(dipInput.DipValue); fields["dipdate"] = DateTime.Now; if (addNew) { _dataTable.Rows.Add(fields); SqlCommandBuilder builder = new SqlCommandBuilder(_adapter); _adapter.InsertCommand = builder.GetInsertCommand(); _adapter.Update(_dataTable); } else { SqlCommandBuilder builder = new SqlCommandBuilder(_adapter); _adapter.UpdateCommand = builder.GetUpdateCommand(); _adapter.Update(_dataTable); } _connection.Close(); _adapter?.Dispose(); } }
/// <summary> /// Search Loyalty customers /// </summary> /// <param name="searchTerm"></param> /// <param name="totalrecords"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public IList <Customer> SearchLoyaltyCustomer(string searchTerm, out int totalrecords, int pageIndex = 1, int pageSize = 100) { var dateStart = DateTime.Now; _performancelog.Debug($"Start,CustomerService,SearchLoyaltyCustomer,{string.Empty},{DateTime.Now:hh.mm.ss.ffffff}"); var customers = new List <Customer>(); var query = "SELECT CLIENT.CL_CODE AS[Code]," + "CLIENT.LO_Num as [Loyalty]," + " CLIENT.CL_NAME AS [Name]," + " CLIENT.CL_PHONE AS [Phone]," + "client.Lo_Points as [Points]" + " FROM CLIENT left join clientcard on CLIENT.CL_CODE = clientcard.CL_CODE" + " WHERE (CLIENT.CL_STATUS is null OR CLIENT.CL_Status IN (\'A\',\'F\',\'\')) " + " AND (CLIENT.CL_CODE like \'%" + searchTerm + "%\' OR " + " CLIENT.LO_Num like \'%" + searchTerm + "%\' OR " + " CLIENT.CL_NAME like \'%" + searchTerm + "%\' OR " + " CLIENT.CL_PHONE like \'%" + searchTerm + "%\' OR " + " CLIENT.Lo_Points like \'%" + searchTerm + "%\' )" + " ORDER BY CLIENT.CL_CODE, CLIENT.LO_Num, CLIENT.CL_NAME, CLIENT.CL_PHONE"; var dt = GetPagedRecords(query, DataSource.CSCMaster, pageIndex, pageSize); totalrecords = 0; if (dt != null) { totalrecords = dt.Rows.Count; foreach (DataRow fields in dt.Rows) { var customer = new Customer { Name = CommonUtility.GetStringValue(fields["Name"]), Code = CommonUtility.GetStringValue(fields["Code"]), Phone = CommonUtility.GetStringValue(fields["Phone"]), Loyalty_Points = CommonUtility.GetDoubleValue(fields["Points"]), Loyalty_Code = CommonUtility.GetStringValue(fields["Loyalty"]) }; customers.Add(customer); } } _performancelog.Debug($"End,CustomerService,SearchLoyaltyCustomer,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}"); return(customers); }
/// <summary> /// Search Customers /// </summary> /// <param name="searchCriteria"></param> /// <param name="totalrecords"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public List <Customer> Search(string searchCriteria, out int totalrecords, int pageIndex = 1, int pageSize = 100) { var dateStart = DateTime.Now; _performancelog.Debug($"Start,CustomerService,Search,{string.Empty},{DateTime.Now:hh.mm.ss.ffffff}"); totalrecords = 0; var customers = new List <Customer>(); var query = "Select * from Client " + " WHERE (Client.CL_Status IS NULL " + " OR Client.CL_Status IN (\'A\',\'F\',\'\')) " + " AND (CL_NAME like \'%" + searchCriteria + "%\' OR " + " CL_CODE like \'%" + searchCriteria + "%\' OR " + " CL_PHONE like \'%" + searchCriteria + "%\' )" + "ORDER BY CLIENT.CL_CODE, CLIENT.CL_NAME, CLIENT.CL_PHONE"; var dt = GetPagedRecords(query, DataSource.CSCMaster, pageIndex, pageSize); if (dt != null) { totalrecords = dt.Rows.Count; customers.AddRange(from DataRow fields in dt.Rows select new Customer { Code = CommonUtility.GetStringValue(fields["CL_CODE"]), Name = CommonUtility.GetStringValue(fields["CL_NAME"]), Category = CommonUtility.GetStringValue(fields["CL_CATEG"]), Address_1 = CommonUtility.GetStringValue(fields["CL_ADD1"]), Address_2 = CommonUtility.GetStringValue(fields["CL_ADD2"]), City = CommonUtility.GetStringValue(fields["CL_CITY"]), Province = CommonUtility.GetStringValue(fields["CL_PROV"]), Country = CommonUtility.GetStringValue(fields["CL_COUNTRY"]), Postal_Code = CommonUtility.GetStringValue(fields["CL_POSTAL"]), Area_Code = CommonUtility.GetStringValue(fields["CL_ACODE"]), Phone = CommonUtility.GetStringValue(fields["CL_PHONE"]), Cell_Phone = CommonUtility.GetStringValue(fields["CL_CELL"]), Fax = CommonUtility.GetStringValue(fields["CL_FAX"]), Toll_Free = CommonUtility.GetStringValue(fields["CL_TOLL"]), E_Mail = CommonUtility.GetStringValue(fields["CL_EMAIL"]), Contact_1 = CommonUtility.GetStringValue(fields["CL_CONT1"]), Contact_2 = CommonUtility.GetStringValue(fields["CL_CONT2"]), Loyalty_Code = CommonUtility.GetStringValue(fields["LO_NUM"]), Loyalty_Points = CommonUtility.GetDoubleValue(fields["LO_POINTS"]), AR_Customer = CommonUtility.GetBooleanValue(fields["CL_ARCUST"]), Customer_Type = CommonUtility.GetStringValue(fields["CL_TYPE"]), Terms = CommonUtility.GetShortValue(fields["TERMS"]), Credit_Limit = CommonUtility.GetDoubleValue(fields["CL_LIMIT"]), Current_Balance = CommonUtility.GetDoubleValue(fields["CL_CURBAL"]), CL_Status = CommonUtility.GetStringValue(fields["CL_STATUS"]), UsePO = CommonUtility.GetBooleanValue(fields["UsePO"]), GroupID = CommonUtility.GetStringValue(fields["GroupID"]), UseFuelRebate = CommonUtility.GetBooleanValue(fields["UseFuelRebate"]), UseFuelRebateDiscount = CommonUtility.GetBooleanValue(fields["UseFuelRebateDiscount"]), CL_Note = CommonUtility.GetStringValue(fields["CL_Note"]), TaxExempt = CommonUtility.GetBooleanValue(fields["TaxExempt"]), PlateNumber = CommonUtility.GetStringValue(fields["PlateNumber"]), MultiUse_PO = CommonUtility.GetBooleanValue(fields["MultiUse_PO"]), TECardNumber = CommonUtility.GetStringValue(fields["TECardNumber"]), Discount_Code = CommonUtility.GetByteValue(fields["CUST_DISC"]) }); } _performancelog.Debug($"End,CustomerService,Search,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}"); return(customers); }
/// <summary> /// Get Customer By Client Code /// </summary> /// <param name="clientCode">code</param> /// <returns>Customer</returns> public Customer GetClientByClientCode(string clientCode) { var dateStart = DateTime.Now; _performancelog.Debug($"Start,CustomerService,GetClientByClientCode,{string.Empty},{DateTime.Now:hh.mm.ss.ffffff}"); Customer customer = null; var dt = GetRecords(" SELECT * FROM Client WHERE Client.CL_Code = \'" + clientCode + "\'", DataSource.CSCMaster); if (dt != null && dt.Rows.Count > 0) { _performancelog.Debug($"End,CustomerService,GetClientByClientCode,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}"); customer = new Customer { Code = CommonUtility.GetStringValue(dt.Rows[0]["CL_CODE"]), Name = CommonUtility.GetStringValue(dt.Rows[0]["CL_NAME"]), Category = CommonUtility.GetStringValue(dt.Rows[0]["CL_CATEG"]), Address_1 = CommonUtility.GetStringValue(dt.Rows[0]["CL_ADD1"]), Address_2 = CommonUtility.GetStringValue(dt.Rows[0]["CL_ADD2"]), City = CommonUtility.GetStringValue(dt.Rows[0]["CL_CITY"]), Province = CommonUtility.GetStringValue(dt.Rows[0]["CL_PROV"]), Country = CommonUtility.GetStringValue(dt.Rows[0]["CL_COUNTRY"]), Postal_Code = CommonUtility.GetStringValue(dt.Rows[0]["CL_POSTAL"]), Area_Code = CommonUtility.GetStringValue(dt.Rows[0]["CL_ACODE"]), Phone = CommonUtility.GetStringValue(dt.Rows[0]["CL_PHONE"]), Cell_Phone = CommonUtility.GetStringValue(dt.Rows[0]["CL_CELL"]), Fax = CommonUtility.GetStringValue(dt.Rows[0]["CL_FAX"]), Toll_Free = CommonUtility.GetStringValue(dt.Rows[0]["CL_TOLL"]), E_Mail = CommonUtility.GetStringValue(dt.Rows[0]["CL_EMAIL"]), Contact_1 = CommonUtility.GetStringValue(dt.Rows[0]["CL_CONT1"]), Contact_2 = CommonUtility.GetStringValue(dt.Rows[0]["CL_CONT2"]), Loyalty_Code = CommonUtility.GetStringValue(dt.Rows[0]["LO_NUM"]), Loyalty_Points = CommonUtility.GetDoubleValue(dt.Rows[0]["LO_POINTS"]), AR_Customer = CommonUtility.GetBooleanValue(dt.Rows[0]["CL_ARCUST"]), Customer_Type = CommonUtility.GetStringValue(dt.Rows[0]["CL_TYPE"]), Terms = CommonUtility.GetShortValue(dt.Rows[0]["TERMS"]), Credit_Limit = CommonUtility.GetDoubleValue(dt.Rows[0]["CL_LIMIT"]), Current_Balance = CommonUtility.GetDoubleValue(dt.Rows[0]["CL_CURBAL"]), CL_Status = CommonUtility.GetStringValue(dt.Rows[0]["CL_STATUS"]), UsePO = CommonUtility.GetBooleanValue(dt.Rows[0]["UsePO"]), GroupID = CommonUtility.GetStringValue(dt.Rows[0]["GroupID"]), UseFuelRebate = CommonUtility.GetBooleanValue(dt.Rows[0]["UseFuelRebate"]), UseFuelRebateDiscount = CommonUtility.GetBooleanValue(dt.Rows[0]["UseFuelRebateDiscount"]), CL_Note = CommonUtility.GetStringValue(dt.Rows[0]["CL_Note"]), TaxExempt = CommonUtility.GetBooleanValue(dt.Rows[0]["TaxExempt"]), PlateNumber = CommonUtility.GetStringValue(dt.Rows[0]["PlateNumber"]), TECardNumber = CommonUtility.GetStringValue(dt.Rows[0]["TECardNumber"]), Discount_Code = CommonUtility.GetByteValue(dt.Rows[0]["CUST_DISC"]), Price_Code = CommonUtility.GetByteValue(dt.Rows[0]["Price_Code"]), MultiUse_PO = CommonUtility.GetBooleanValue(dt.Rows[0]["UsePO"]) && CommonUtility.GetBooleanValue(dt.Rows[0]["MultiUse_PO"]), }; if (!string.IsNullOrEmpty(customer.GroupID)) { var clientDt = GetRecords("select * from ClientGroup where GroupID=\'" + customer.GroupID + "\'", DataSource.CSCMaster); if (clientDt == null || clientDt.Rows.Count == 0) { customer.GroupID = ""; } else { customer.GroupName = CommonUtility.GetStringValue(clientDt.Rows[0]["GroupName"]); customer.DiscountType = CommonUtility.GetStringValue(clientDt.Rows[0]["DiscountType"]); customer.DiscountRate = CommonUtility.GetFloatValue(clientDt.Rows[0]["DiscountRate"]); customer.Footer = CommonUtility.GetStringValue(clientDt.Rows[0]["LoyaltyFooter"]); customer.DiscountName = CommonUtility.GetStringValue(clientDt.Rows[0]["DiscountName"]); } } // var clientCard = GetClientCardForCustomer() } _performancelog.Debug($"End,CustomerService,GetClientByClientCode,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}"); return(customer); }
/// <summary> /// Method to get suspended sale /// </summary> /// <param name="tillNumber">Till number</param> /// <param name="saleNumber">Sale number</param> /// <param name="shareSusp">Share suspended sale</param> /// <returns>Sale</returns> public Sale GetSuspendedSale(int tillNumber, int saleNumber, bool shareSusp) { var dateStart = DateTime.Now; _performancelog.Debug($"Start,SuspendedSaleService,GetSuspendedSale,{string.Empty},{dateStart:hh.mm.ss.ffffff}"); var sale = new Sale(); DataTable uHead; DataTable uLine; if (shareSusp) { uHead = GetRecords("select * from SusHead WHERE SALE_NO=" + Convert.ToString(saleNumber), DataSource.CSCTills); uLine = GetRecords("select * from SusLine WHERE SALE_NO=" + Convert.ToString(saleNumber) + " ORDER BY SusLine.Line_Num ", DataSource.CSCTills); } else { uHead = GetRecords("select * from SusHead where TILL=" + tillNumber + " AND SALE_NO=" + Convert.ToString(saleNumber), DataSource.CSCTills); uLine = GetRecords("select * from SusLine where TILL_NUM=" + tillNumber + " AND SALE_NO=" + Convert.ToString(saleNumber) + " ORDER BY SusLine.Line_Num ", DataSource.CSCTills); } if (uHead.Rows.Count > 0) { sale.Sale_Num = CommonUtility.GetIntergerValue(uHead.Rows[0]["SALE_NO"]); sale.TillNumber = (byte)tillNumber; sale.Register = CommonUtility.GetByteValue(uHead.Rows[0]["Regist"]); sale.Customer.Code = CommonUtility.GetStringValue(uHead.Rows[0]["Client"]); sale.Sale_Type = CommonUtility.GetStringValue(uHead.Rows[0]["T_type"]); sale.Return_Reason.Reason = CommonUtility.GetStringValue(uHead.Rows[0]["Reason"]); sale.Return_Reason.RType = CommonUtility.GetStringValue(uHead.Rows[0]["Reason_Type"]); sale.Sale_Totals.Invoice_Discount_Type = CommonUtility.GetStringValue(uHead.Rows[0]["Disc_Type"]); sale.Sale_Totals.Invoice_Discount = CommonUtility.GetDecimalValue(uHead.Rows[0]["Invc_Disc"]); sale.Sale_Totals.Discount_Percent = CommonUtility.GetFloatValue(uHead.Rows[0]["Discount_Percent"]); sale.Void_Num = CommonUtility.GetIntergerValue(uHead.Rows[0]["Void_No"]); sale.Customer.LoyaltyCard = CommonUtility.GetStringValue(uHead.Rows[0]["LoyaltyCard"]); sale.Customer.LoyaltyExpDate = CommonUtility.GetStringValue(uHead.Rows[0]["LoyaltyExpDate"]); sale.CouponID = CommonUtility.GetStringValue(uHead.Rows[0]["CouponID"]); sale.Upsell = CommonUtility.GetBooleanValue(uHead.Rows[0]["Upsell"]); var ln = (short)0; foreach (DataRow row in uLine.Rows) { ln++; var sl = new Sale_Line { Sale_Num = CommonUtility.GetStringValue(row["sale_no"]), Till_Num = sale.TillNumber }; sale.Sale_Num = CommonUtility.GetIntergerValue(row["sale_no"]); sl.Line_Num = ln; sl.PLU_Code = CommonUtility.GetStringValue(row["PLU_Code"]); sl.Quantity = CommonUtility.GetFloatValue(row["Quantity"]); sl.price = CommonUtility.GetDoubleValue(row["price"]); sl.Amount = CommonUtility.GetDecimalValue(row["Amount"]); sl.Line_Discount = CommonUtility.GetDoubleValue(row["Discount"]); sl.Discount_Type = CommonUtility.GetStringValue(row["Disc_Type"]); sl.Discount_Adjust = CommonUtility.GetDoubleValue(row["Disc_Adj"]); sl.Discount_Code = CommonUtility.GetStringValue(row["Disc_Code"]); sl.Discount_Rate = CommonUtility.GetFloatValue(row["Disc_Rate"]); sl.DiscountName = CommonUtility.GetStringValue(row["DiscountName"]); sl.Price_Type = CommonUtility.GetCharValue(row["Price_Type"]); sl.Loyalty_Save = CommonUtility.GetFloatValue(row["Loyl_Save"]); sl.Units = CommonUtility.GetStringValue(row["Units"]); sl.Serial_No = CommonUtility.GetStringValue(row["Serial_No"]); sl.User = CommonUtility.GetStringValue(row["User"]); sl.pumpID = CommonUtility.GetByteValue(row["pumpID"]); sl.PositionID = CommonUtility.GetByteValue(row["PositionID"]); sl.GradeID = CommonUtility.GetByteValue(row["GradeID"]); sl.Gift_Certificate = CommonUtility.GetBooleanValue(row["Gift_Cert"]); sl.Gift_Num = CommonUtility.GetStringValue(row["GC_Num"]); sale.TillNumber = (byte)tillNumber; sl.ManualFuel = CommonUtility.GetBooleanValue(row["ManualFuel"]); sl.IsTaxExemptItem = CommonUtility.GetBooleanValue(row["TaxExempt"]); var strPromo = CommonUtility.GetStringValue(row["PromoID"]); if (!string.IsNullOrEmpty(strPromo)) { sl.PromoID = strPromo; } sl.Upsell = CommonUtility.GetBooleanValue(row["Upsell"]); sl.ScalableItem = CommonUtility.GetBooleanValue(row["ScalableItem"]); DataTable uReason; if (shareSusp) { uReason = GetRecords( "select * from SusReas WHERE SALE_NO=" + Convert.ToString(saleNumber) + " AND LINE_Num = " + ln + " ORDER BY SusReas.Line_Num", DataSource.CSCTills); } else { uReason = GetRecords( "select * from SusReas where TILL_NUM=" + tillNumber + " AND SALE_NO=" + Convert.ToString(saleNumber) + " AND LINE_Num = " + ln + " ORDER BY SusReas.Line_Num", DataSource.CSCTills); } var returnReason = new Return_Reason(); foreach (DataRow dataRow in uReason.Rows) { if (CommonUtility.GetStringValue(dataRow["Reason"]) != "" & CommonUtility.GetStringValue(dataRow["Reason_Type"]) != "") { returnReason.Reason = CommonUtility.GetStringValue(dataRow["Reason"]); returnReason.RType = CommonUtility.GetStringValue(dataRow["Reason_Type"]); } sl.Return_Reasons.Add(returnReason.Reason, returnReason.RType, ln.ToString()); } sale.Sale_Lines.AddLine(ln, sl, ""); } _performancelog.Debug($"End,SuspendedSaleService,GetSuspendedSale,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}"); return(sale); } _performancelog.Debug($"End,SuspendedSaleService,GetSuspendedSale,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}"); return(new Sale()); }
/// <summary> /// Method to load service /// </summary> /// <returns></returns> public Service LoadService() { var service = new Service(); _connection = new SqlConnection(GetConnectionString(DataSource.CSCMaster)); if (_connection.State == ConnectionState.Closed) { _connection.Open(); } _dataTable = new DataTable(); _adapter = new SqlDataAdapter("select * from Variables where Name=\'PostPayEnabled\'", _connection); _adapter.Fill(_dataTable); if (_dataTable.Rows.Count != 0) { if (!DBNull.Value.Equals(_dataTable.Rows[0]["Value"])) { service.PostPayEnabled = CommonUtility.GetDoubleValue(_dataTable.Rows[0]["Value"]) == 1; } else { service.PostPayEnabled = true; _dataTable.Rows[0]["Value"] = "1"; SqlCommandBuilder builder = new SqlCommandBuilder(_adapter); _adapter.UpdateCommand = builder.GetUpdateCommand(); } } else { var rsTmp = GetRecords("select Max(VarIndex) as MaxIndex from Variables", DataSource.CSCMaster); DataRow rsfields = _dataTable.NewRow(); rsfields["VarIndex"] = DBNull.Value.Equals(rsTmp.Rows[0]["MaxIndex"]) ? 0 : CommonUtility.GetIntergerValue(rsTmp.Rows[0]["MaxIndex"]) + 1; rsfields["Name"] = "PostPayEnabled"; rsfields["Value"] = "1"; rsfields["Description"] = "To keep the current PostPay status, 1-On/0-Off."; _dataTable.Rows.Add(rsfields); service.PostPayEnabled = true; SqlCommandBuilder builder = new SqlCommandBuilder(_adapter); _adapter.InsertCommand = builder.GetInsertCommand(); } _adapter.Update(_dataTable); _dataTable = new DataTable(); _adapter = new SqlDataAdapter("select * from Variables where Name=\'PayPumpEnabled\'", _connection); _adapter.Fill(_dataTable); if (_dataTable.Rows.Count != 0) { if (!DBNull.Value.Equals(_dataTable.Rows[0]["Value"])) { service.PayPumpEnabled = CommonUtility.GetDoubleValue(_dataTable.Rows[0]["Value"]) == 1; } else { service.PayPumpEnabled = true; _dataTable.Rows[0]["Value"] = "1"; SqlCommandBuilder builder = new SqlCommandBuilder(_adapter); _adapter.UpdateCommand = builder.GetUpdateCommand(); } } else { var rsTmp = GetRecords("select Max(VarIndex) as MaxIndex from Variables", DataSource.CSCMaster); DataRow rsfields = _dataTable.NewRow(); rsfields["VarIndex"] = DBNull.Value.Equals(rsTmp.Rows[0]["MaxIndex"]) ? 0 : CommonUtility.GetIntergerValue(rsTmp.Rows[0]["MaxIndex"]) + 1; rsfields["Name"] = "PayPumpEnabled"; rsfields["Value"] = "1"; rsfields["Description"] = "To keep the current PayPump status, 1-On/0-Off."; _dataTable.Rows.Add(rsfields); service.PayPumpEnabled = true; SqlCommandBuilder builder = new SqlCommandBuilder(_adapter); _adapter.InsertCommand = builder.GetInsertCommand(); } _adapter.Update(_dataTable); _dataTable = new DataTable(); _adapter = new SqlDataAdapter("select * from Variables where Name=\'PrepayEnabled\'", _connection); _adapter.Fill(_dataTable); if (_dataTable.Rows.Count != 0) { if (!DBNull.Value.Equals(_dataTable.Rows[0]["Value"])) { service.PrepayEnabled = CommonUtility.GetDoubleValue(_dataTable.Rows[0]["Value"]) == 1; } else { service.PrepayEnabled = true; _dataTable.Rows[0]["Value"] = "1"; SqlCommandBuilder builder = new SqlCommandBuilder(_adapter); _adapter.UpdateCommand = builder.GetUpdateCommand(); } } else { var rsTmp = GetRecords("select Max(VarIndex) as MaxIndex from Variables", DataSource.CSCMaster); DataRow rsfields = _dataTable.NewRow(); rsfields["VarIndex"] = DBNull.Value.Equals(rsTmp.Rows[0]["MaxIndex"]) ? 0 : CommonUtility.GetIntergerValue(rsTmp.Rows[0]["MaxIndex"]) + 1; rsfields["Name"] = "PrepayEnabled"; rsfields["Value"] = "1"; rsfields["Description"] = "To keep the current Prepay status, 1-On/0-Off."; _dataTable.Rows.Add(rsfields); service.PrepayEnabled = true; SqlCommandBuilder builder = new SqlCommandBuilder(_adapter); _adapter.InsertCommand = builder.GetInsertCommand(); } _adapter.Update(_dataTable); _connection.Close(); _adapter?.Dispose(); return(service); }
/// <summary> /// Gets the List of Sale Lines /// </summary> /// <param name="saleNumber">Sale number</param> /// <param name="tillNumber">Till number</param> /// <param name="saleDate">Sale date</param> /// <param name="discountType">Discount type</param> /// <param name="teType">Tax exempt type</param> /// <param name="taxExempt">Tax exempt name</param> /// <returns>List of sale lines</returns> public List <Sale_Line> GetSaleLineBySaleNumber(int saleNumber, int tillNumber, DateTime saleDate, string discountType, string teType, bool taxExempt) { var dateStart = DateTime.Now; _performancelog.Debug($"Start,ReturnSaleService,GetSaleLineBySaleNumber,{string.Empty},{dateStart:hh.mm.ss.ffffff}"); List <Sale_Line> saleLines = new List <Sale_Line>(); DataSource db = DataSource.CSCTrans; var strSqlHead = " SELECT * FROM SaleHead Where SaleHead.Sale_No = " + CommonUtility.GetStringValue(saleNumber) + " AND SaleHead.TILL = " + CommonUtility.GetStringValue(tillNumber) + " AND SaleHead.T_Type IN ( \'SALE\' ,\'REFUND\', \'RUNAWAY\',\'PUMPTEST\' ) and SaleHead.SALE_AMT <> 0 "; var strSqlLine = " SELECT * FROM SaleLine Where SaleLine.Sale_No = " + CommonUtility.GetStringValue(saleNumber) + " AND SaleLine.TILL_NUM = " + CommonUtility.GetStringValue(tillNumber) + "ORDER BY SaleLine.Line_Num "; var rsHead = GetRecords(strSqlHead, DataSource.CSCTrans); var rsLine = GetRecords(strSqlLine, DataSource.CSCTrans); // If SaleNo is not found in Trans.mdb database, then // look for this SaleNo in all active Tills if (rsHead == null || rsHead.Rows.Count == 0) { rsHead = GetRecords(strSqlHead, DataSource.CSCTills); rsLine = GetRecords(strSqlLine, DataSource.CSCTills); // If sale number is found exit for do and use this recordset if (rsHead != null && rsHead.Rows.Count != 0) { db = DataSource.CSCTills; } } else { db = DataSource.CSCTrans; } foreach (DataRow rsLineFields in rsLine.Rows) { var saleLine = new Sale_Line { No_Loading = true, Stock_Code = CommonUtility.GetStringValue(rsLineFields["Stock_Code"]), PLU_Code = CommonUtility.GetStringValue(rsLineFields["PLU_Code"]), Line_Num = CommonUtility.GetShortValue(rsLineFields["Line_Num"]), Price_Type = CommonUtility.GetCharValue(rsLineFields["Price_Type"]), Dept = CommonUtility.GetStringValue(rsLineFields["Dept"]), Sub_Detail = CommonUtility.GetStringValue(rsLineFields["Sub_Detail"]), Sub_Dept = CommonUtility.GetStringValue(rsLineFields["Sub_Dept"]), Quantity = CommonUtility.GetFloatValue(rsLineFields["Quantity"]) * -1, price = CommonUtility.GetDoubleValue(rsLineFields["price"]), Regular_Price = CommonUtility.GetDoubleValue(rsLineFields["Reg_Price"]), Cost = CommonUtility.GetDoubleValue(rsLineFields["Cost"]), Amount = CommonUtility.GetDecimalValue(rsLineFields["Amount"]) * -1, Total_Amount = CommonUtility.GetDecimalValue(rsLineFields["Total_Amt"]) * -1 }; if (!(teType == "QITE" && discountType == "D")) { saleLine.Line_Discount = CommonUtility.GetDoubleValue(rsLineFields["Discount"]) * -1; } saleLine.Discount_Type = CommonUtility.GetStringValue(rsLineFields["Disc_Type"]); saleLine.Discount_Code = CommonUtility.GetStringValue(rsLineFields["Disc_Code"]); if (!(teType == "QITE" && discountType == "D")) { SetDiscountRate(ref saleLine, CommonUtility.GetFloatValue(rsLineFields["Disc_Rate"])); } saleLine.Associate_Amount = CommonUtility.GetDecimalValue(rsLineFields["Assoc_Amt"]); saleLine.Description = CommonUtility.GetStringValue(rsLineFields["Descript"]); saleLine.Loyalty_Save = CommonUtility.GetFloatValue(rsLineFields["Loyl_Save"]); saleLine.Units = CommonUtility.GetStringValue(rsLineFields["Units"]); saleLine.Serial_No = CommonUtility.GetStringValue(rsLineFields["Serial_No"]); saleLine.Stock_Code = CommonUtility.GetStringValue(rsLineFields["Stock_Code"]); saleLine.pumpID = CommonUtility.GetByteValue(rsLineFields["pumpID"]); saleLine.PositionID = CommonUtility.GetByteValue(rsLineFields["PositionID"]); saleLine.GradeID = CommonUtility.GetByteValue(rsLineFields["GradeID"]); saleLine.Prepay = CommonUtility.GetBooleanValue(rsLineFields["Prepay"]); saleLine.ManualFuel = CommonUtility.GetBooleanValue(rsLineFields["ManualFuel"]); saleLine.IsTaxExemptItem = CommonUtility.GetBooleanValue(rsLineFields["TaxExempt"]); saleLine.Gift_Num = CommonUtility.GetStringValue(rsLineFields["GC_Num"]); saleLine.FuelRebateEligible = CommonUtility.GetBooleanValue(rsLineFields["FuelRebateUsed"]); saleLine.FuelRebate = CommonUtility.GetDecimalValue(rsLineFields["RebateDiscount"]); saleLine.EligibleTaxEx = CommonUtility.GetBooleanValue(rsLineFields["ElgTaxExemption"]); saleLine.CarwashCode = CommonUtility.GetStringValue(rsLineFields["CarwashCode"]); if (saleLine.CarwashCode != "") { saleLine.IsCarwashProduct = true; } if (saleLine.IsTaxExemptItem) { if (teType == "SITE") { var rsPurchaseItem = GetRecords( "select * from PurchaseItem Where Sale_No=" + CommonUtility.GetStringValue(saleNumber) + " AND Line_No=" + CommonUtility.GetStringValue(saleLine.Line_Num), db); if (rsPurchaseItem != null && rsPurchaseItem.Rows.Count != 0) { var rsPurchaseItemFields = rsPurchaseItem.Rows[0]; saleLine.OriginalPrice = CommonUtility.GetFloatValue(rsPurchaseItemFields["OriginalPrice"]); saleLine.TaxInclPrice = CommonUtility.GetFloatValue(rsPurchaseItemFields["Amount"]); } else { saleLine.OriginalPrice = (float)saleLine.price; saleLine.TaxInclPrice = (float)(-1 * saleLine.Amount); } } else { var rsTeSaleLine = GetRecords( "select * from TaxExemptSaleLine Where SALE_NO=" + CommonUtility.GetStringValue(saleNumber) + " AND LINE_NUM=" + CommonUtility.GetStringValue(saleLine.Line_Num), db); if (rsTeSaleLine != null && rsTeSaleLine.Rows.Count != 0) { var rsTeSaleLineFields = rsTeSaleLine.Rows[0]; saleLine.OriginalPrice = CommonUtility.GetFloatValue(rsTeSaleLineFields["OriginalPrice"]); saleLine.TaxInclPrice = CommonUtility.GetFloatValue(-1 * CommonUtility.GetIntergerValue( rsTeSaleLineFields["TaxIncludedAmount"])); } else { saleLine.OriginalPrice = (float)saleLine.price; saleLine.TaxInclPrice = (float)(-1 * saleLine.Amount); } saleLine.IsTaxExemptItem = false; } } string strPromo = CommonUtility.GetStringValue(rsLineFields["PromoID"]); if (strPromo.Length != 0) { saleLine.PromoID = strPromo; } saleLine.Line_Taxes = null; var rsLineTax = GetRecords( "Select * From S_LineTax WHERE S_LineTax.Sale_No = " + CommonUtility.GetStringValue(saleNumber) + " AND S_LineTax.Line_No = " + CommonUtility.GetStringValue(rsLineFields["Line_Num"]) + " " + "Order By S_LineTax.Tax_Name ", db); if (rsLineTax == null || rsLineTax.Rows.Count == 0) { if (taxExempt && (teType == "AITE" || teType == "QITE")) { rsLineTax = GetRecords( "Select * From TaxCreditLine WHERE TaxCreditLine.Sale_No = " + CommonUtility.GetStringValue(saleNumber) + " AND TaxCreditLine.Line_No = " + CommonUtility.GetStringValue(rsLineFields["Line_Num"]) + " " + "Order By TaxCreditLine.Tax_Name ", db); if (!saleLine.IsTaxExemptItem && rsLineTax.Rows.Count != 0) { } } } foreach (DataRow rsLineTaxFields in rsLineTax.Rows) { saleLine.Line_Taxes.Add(CommonUtility.GetStringValue(rsLineTaxFields["Tax_Name"]), CommonUtility.GetStringValue(rsLineTaxFields["Tax_Code"]), CommonUtility.GetFloatValue(rsLineTaxFields["Tax_Rate"]), CommonUtility.GetBooleanValue(rsLineTaxFields["Tax_Included"]), CommonUtility.GetFloatValue(rsLineTaxFields["Tax_Rebate_Rate"]), CommonUtility.GetDecimalValue(rsLineTaxFields["Tax_Rebate"]), ""); } // Similarly, pick up the charges associated with the line. saleLine.Charges = null; var rsLineChg = GetRecords( "Select * FROM SaleChg WHERE SaleChg.Sale_No = " + CommonUtility.GetStringValue(saleNumber) + " AND SaleChg.Line_No = " + CommonUtility.GetStringValue(rsLineFields["Line_Num"]) + " Order By SaleChg.As_Code ", db); foreach (DataRow linChrField in rsLineChg.Rows) { var rsLcTax = GetRecords( "Select * FROM ChargeTax WHERE ChargeTax.Sale_No = " + CommonUtility.GetStringValue(saleNumber) + " AND ChargeTax.Line_No = " + CommonUtility.GetStringValue(rsLineFields["Line_Num"]) + " AND ChargeTax.As_Code = \'" + CommonUtility.GetStringValue(linChrField["As_Code"]) + "\' ", db); // Find any taxes that applied to those charges. var lct = new Charge_Taxes(); foreach (DataRow rsLcTaxFields in rsLcTax.Rows) { lct.Add(CommonUtility.GetStringValue(rsLcTaxFields["Tax_Name"]), CommonUtility.GetStringValue(rsLcTaxFields["Tax_Code"]), CommonUtility.GetFloatValue(rsLcTaxFields["Tax_Rate"]), CommonUtility.GetBooleanValue(rsLcTaxFields["Tax_Included"]), ""); } saleLine.Charges.Add(CommonUtility.GetStringValue(linChrField["As_Code"]), CommonUtility.GetStringValue(linChrField["Description"]), CommonUtility.GetFloatValue(linChrField["price"]), lct, ""); } saleLine.Line_Kits = GetLineKits(saleNumber, CommonUtility.GetIntergerValue(rsLineFields["Line_Num"]), db); saleLines.Add(saleLine); } _performancelog.Debug($"End,ReturnSaleService,GetSaleLineBySaleNumber,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}"); return(saleLines); }