/// <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);
}
