/// <summary>
/// Get All Sales
/// </summary>
/// <param name="saleDate">Sale date</param>
/// <param name="timeFormat">Time format</param>
/// <param name="pageIndex">Page index</param>
/// <param name="pageSize">Page size</param>
/// <returns>List of sale head</returns>
public List <SaleHead> GetAllSales(DateTime saleDate, string timeFormat, int pageIndex, int pageSize)
{
var dateStart = DateTime.Now;
_performancelog.Debug($"Start,ReturnSaleService,GetAllSales,{string.Empty},{dateStart:hh.mm.ss.ffffff}");
var salesHeads = new List <SaleHead>();
string strSql;
if (timeFormat == "24 HOURS") //to format the time according to the policy
{
strSql = "(SELECT Till, Sale_No AS [Sale No], Sale_Date AS [Date], convert(varchar,Sale_time, 108) AS [Time] , Sale_Amt AS [Amount] FROM SaleHead A WHERE A.T_Type IN ( \'SALE\' ,\'REFUND\', \'RUNAWAY\', \'PUMPTEST\' ) and A.SALE_AMT <> 0 and A.Sale_Date >= \'" + saleDate.ToString("yyyyMMdd") + "\' UNION ALL SELECT Till, Sale_No AS [Sale No], Sale_Date AS [Date], convert(varchar,Sale_time, 108) AS [Time] , Sale_Amt AS [Amount] FROM CSCTRANS.dbo.SaleHead A WHERE A.T_Type IN ( \'SALE\' ,\'REFUND\', \'RUNAWAY\' , \'PUMPTEST\') and A.SALE_AMT <> 0 and A.Sale_Date >= \'" + saleDate.ToString("yyyyMMdd") + "\') ORDER BY Sale_No DESC";
}
else
{
strSql = "(SELECT Till, Sale_No AS [Sale No], Sale_Date AS [Date], RIGHT(CONVERT(CHAR(19),sale_time,100),7) AS [Time] , Sale_Amt AS [Amount] FROM SaleHead A WHERE A.T_Type IN ( \'SALE\' ,\'REFUND\' , \'RUNAWAY\', \'PUMPTEST\') and A.SALE_AMT <> 0 and A.Sale_Date >= \'" + saleDate.ToString("yyyyMMdd") + "\' UNION ALL SELECT Till, Sale_No AS [Sale No], Sale_Date AS [Date], RIGHT(CONVERT(CHAR(19),sale_time,100),7) AS [Time] , Sale_Amt AS [Amount] FROM CSCTRANS.dbo.SaleHead A WHERE A.T_Type IN ( \'SALE\' ,\'REFUND\', \'RUNAWAY\' , \'PUMPTEST\') and A.SALE_AMT <> 0 and A.Sale_Date >= \'" + saleDate.ToString("yyyyMMdd") + "\') ORDER BY Sale_No DESC";
}
var rsSales = GetPagedRecords(strSql, DataSource.CSCTills, pageIndex, pageSize);
foreach (DataRow fields in rsSales.Rows)
{
salesHeads.Add(new SaleHead
{
TillNumber = CommonUtility.GetIntergerValue(fields["Till"]),
SaleNumber = CommonUtility.GetIntergerValue(fields["Sale No"]),
SaleDate = CommonUtility.GetDateTimeValue(fields["Date"]),
SaleTime = CommonUtility.GetDateTimeValue(fields["Time"]),
SaleAmount = CommonUtility.GetDecimalValue(fields["Amount"])
});
}
_performancelog.Debug($"End,ReturnSaleService,GetAllSales,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}");
return(salesHeads);
}
/// <summary>
///
/// </summary>
/// <param name="cardNumber"></param>
/// <returns></returns>
public ClientCard GetClientCardByCardNumber(string cardNumber)
{
var dateStart = DateTime.Now;
_performancelog.Debug($"Start,CustomerService,GetClientCardByCardNumber,{string.Empty},{DateTime.Now:hh.mm.ss.ffffff}");
var dt = GetRecords("select * from ClientCard where CardNum=\'" + cardNumber.Trim() + "\'", DataSource.CSCMaster);
if (dt != null && dt.Rows.Count > 0)
{
_performancelog.Debug($"End,CustomerService,GetCustomers,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}");
return(new ClientCard
{
CardNumber = CommonUtility.GetStringValue(dt.Rows[0]["CardNum"]),
ClientCode = CommonUtility.GetStringValue(dt.Rows[0]["CL_Code"]),
CardName = CommonUtility.GetStringValue(dt.Rows[0]["CardName"]),
ExpirationDate = CommonUtility.GetDateTimeValue(dt.Rows[0]["ExpDate"]),
Pin = CommonUtility.GetStringValue(dt.Rows[0]["PIN"]),
CardStatus = Convert.ToChar(dt.Rows[0]["CardStatus"]),
CreditLimiit = Convert.ToDecimal(dt.Rows[0]["CreditLimiit"]),
Balance = Convert.ToDecimal(dt.Rows[0]["Balance"]),
AllowRedemption = CommonUtility.GetBooleanValue(dt.Rows[0]["AllowRedemption"]),
TaxExemptedCardNumber = CommonUtility.GetStringValue(dt.Rows[0]["TECardNumber"]),
ProfileID = CommonUtility.GetStringValue(dt.Rows[0]["ProfileID"])
});
}
_performancelog.Debug($"End,CustomerService,GetClientCardByCardNumber,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}");
return(null);
}
/// <summary>
/// Get the active tills for a given user
/// </summary>
/// <param name="active">Active</param>
/// <param name="payAtPumpTill">Pay at pump till</param>
/// <param name="userLoggedOn">User logged on</param>
/// <returns>Tills</returns>
public List <Till> GetTillForUser(int active, int payAtPumpTill, string userLoggedOn)
{
var dateStart = DateTime.Now;
_performancelog.Debug($"Start,TillService,GetTillForUser,{string.Empty},{DateTime.Now:hh.mm.ss.ffffff}");
var strSql = "Select * From Tills Where Tills.Active = " + active + " AND Tills.Till_Num <> " + payAtPumpTill + " and Tills.UserLoggedON=\'" + userLoggedOn + "\'";
var sTills = GetRecords(strSql, DataSource.CSCMaster);
var tills = (from DataRow row in sTills.Rows
select new Till
{
Number = CommonUtility.GetShortValue(row["TILL_NUM"]),
Active = CommonUtility.GetBooleanValue(row["ACTIVE"]),
Processing = CommonUtility.GetBooleanValue(row["PROCESS"]),
Float = CommonUtility.GetDecimalValue(row["FLOAT"]),
BonusFloat = CommonUtility.GetDecimalValue(row["CashBonusFloat"]),
Cash = CommonUtility.GetDecimalValue(row["CASH"]),
Date_Open = CommonUtility.GetDateTimeValue(row["DATE_OPEN"]),
Time_Open = CommonUtility.GetDateTimeValue(row["TIME_OPEN"]),
ShiftDate = CommonUtility.GetDateTimeValue(row["ShiftDate"]),
Shift = CommonUtility.GetShortValue(row["ShiftNumber"]),
UserLoggedOn = CommonUtility.GetStringValue(row["UserLoggedOn"]),
POSId = CommonUtility.GetIntergerValue(row["POSID"]),
CashBonus = CommonUtility.GetDecimalValue(row["CashBonus"])
}).ToList();
_performancelog.Debug($"End,TillService,GetTillForUser,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}");
return(tills);
}
/// <summary>
/// Get the tills which are not pay at pump
/// </summary>
/// <param name="payAtPumpTill">aPay at pump till</param>
/// <returns>Tills</returns>
public List <Till> GetnotPayAtPumpTill(int payAtPumpTill)
{
var dateStart = DateTime.Now;
_performancelog.Debug($"Start,TillService,GetnotPayAtPumpTill,{string.Empty},{DateTime.Now:hh.mm.ss.ffffff}");
var strSql = "Select * FROM Tills WHERE Tills.Till_Num <> " + payAtPumpTill + "";
var sTills = GetRecords(strSql, DataSource.CSCMaster);
var tills = new List <Till>();
foreach (DataRow row in sTills.Rows)
{
var till = new Till
{
Number = CommonUtility.GetShortValue(row["TILL_NUM"]),
Active = CommonUtility.GetBooleanValue(row["ACTIVE"]),
Processing = CommonUtility.GetBooleanValue(row["PROCESS"]),
Float = CommonUtility.GetDecimalValue(row["FLOAT"]),
BonusFloat = CommonUtility.GetDecimalValue(row["CashBonusFloat"]),
Cash = CommonUtility.GetDecimalValue(row["CASH"]),
Date_Open = CommonUtility.GetDateTimeValue(row["DATE_OPEN"]),
Time_Open = CommonUtility.GetDateTimeValue(row["TIME_OPEN"]),
ShiftDate = CommonUtility.GetDateTimeValue(row["ShiftDate"]),
Shift = CommonUtility.GetShortValue(row["ShiftNumber"]),
UserLoggedOn = CommonUtility.GetStringValue(row["UserLoggedOn"]),
POSId = CommonUtility.GetIntergerValue(row["POSID"]),
CashBonus = CommonUtility.GetDecimalValue(row["CashBonus"])
};
tills.Add(till);
}
_performancelog.Debug($"End,TillService,GetnotPayAtPumpTill,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}");
return(tills);
}
/// <summary>
/// Get the Till by Till Number
/// </summary>
/// <param name="tillNumber">Till number</param>
/// <returns>Till</returns>
public Till GetTill(int tillNumber)
{
var sTill = GetRecords("Select * From Tills where till_num=" + tillNumber, DataSource.CSCMaster);
if (sTill.Rows.Count > 0)
{
var row = sTill.Rows[0];
var till = new Till
{
Number = CommonUtility.GetShortValue(row["TILL_NUM"]),
Active = CommonUtility.GetBooleanValue(row["ACTIVE"]),
Processing = CommonUtility.GetBooleanValue(row["PROCESS"]),
Float = CommonUtility.GetDecimalValue(row["FLOAT"]),
BonusFloat = CommonUtility.GetDecimalValue(row["CashBonusFloat"]),
Cash = CommonUtility.GetDecimalValue(row["CASH"]),
Date_Open = CommonUtility.GetDateTimeValue(row["DATE_OPEN"]),
Time_Open = CommonUtility.GetDateTimeValue(row["TIME_OPEN"]),
ShiftDate = CommonUtility.GetDateTimeValue(row["ShiftDate"]),
Shift = CommonUtility.GetShortValue(row["ShiftNumber"]),
UserLoggedOn = CommonUtility.GetStringValue(row["UserLoggedOn"]),
POSId = CommonUtility.GetIntergerValue(row["POSID"]),
CashBonus = CommonUtility.GetDecimalValue(row["CashBonus"])
};
return(till);
}
return(null);
}
/// <summary>
/// Method to get list of prices
/// </summary>
/// <param name="stockCode">Stock code</param>
/// <returns>List of prices</returns>
public List <PriceL> GetPriceL(string stockCode)
{
var rs = GetRecords("SELECT * FROM Pricel WHERE Pricel.Stock_code = \'" + stockCode + "\' ORDER BY Pricel.Pr_F_Qty ", DataSource.CSCMaster);
return((from DataRow dr in rs.Rows
select new PriceL
{
FQuantity = CommonUtility.GetFloatValue(dr["Pr_F_Qty"]),
TQuantity = CommonUtility.GetFloatValue(dr["Pr_T_Qty"]),
Price = CommonUtility.GetFloatValue(dr["Price"]),
StartDate = CommonUtility.GetDateTimeValue(dr["StartDate"]),
EndDate = CommonUtility.GetDateTimeValue(dr["EndDate"])
}).ToList());
}
/// <summary>
/// Checks If Sale Exists
/// </summary>
/// <param name="saleNumber">Sale number</param>
/// <param name="saleDate">Sale date</param>
/// <param name="isSaleFound">Sale found</param>
/// <param name="isReturnable">Sale returnable or not</param>
public void IsSaleExist(int saleNumber, DateTime saleDate, out bool isSaleFound, out bool isReturnable)
{
var dateStart = DateTime.Now;
_performancelog.Debug($"Start,ReturnSaleService,IsSaleExist,{string.Empty},{dateStart:hh.mm.ss.ffffff}");
var saleFound = false;
isSaleFound = true;
isReturnable = true;
var strSqlHead = " SELECT * FROM SaleHead Where SaleHead.Sale_No = " + CommonUtility.GetStringValue(saleNumber) + " AND SaleHead.T_Type IN ( \'SALE\' ,\'REFUND\', \'RUNAWAY\',\'PUMPTEST\' ) and SaleHead.SALE_AMT <> 0 ";
var rsHead = GetRecords(strSqlHead, 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);
// If sale number is found exit for do and use this recordset
if (rsHead != null && rsHead.Rows.Count != 0)
{
saleFound = true;
}
}
else
{
saleFound = true;
}
if (!saleFound)
{
isSaleFound = false;
isReturnable = false;
return;
}
var rsHeadFields = rsHead.Rows[0];
if (saleDate != new DateTime() && CommonUtility.GetDateTimeValue(rsHeadFields["Sale_Date"]) < saleDate)
{
isReturnable = false;
}
_performancelog.Debug($"End,ReturnSaleService,IsSaleExist,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}");
}
/// <summary>
/// Get the Shift by Shift Number
/// </summary>
/// <param name="shiftNumber"></param>
/// <param name="active"></param>
/// <returns>Shift</returns>
public ShiftStore GetShift(int shiftNumber, int active)
{
var sShift = GetRecords("SELECT * from ShiftStore A where A.ShiftNumber > " + shiftNumber + " and A.Active =" + active, DataSource.CSCMaster);
if (sShift == null || sShift.Rows.Count == 0)
{
return(null);
}
var fields = sShift.Rows[0];
var shift = new ShiftStore
{
ShiftNumber = CommonUtility.GetIntergerValue(fields["ShiftNumber"]),
CurrentDay = CommonUtility.GetByteValue(fields["CurrentDay"]),
Active = CommonUtility.GetByteValue(fields["Active"]),
StartTime = CommonUtility.GetDateTimeValue(fields["StartTime"])
};
return(shift);
}
/// <summary>
/// Get the next Shift
/// </summary>
/// <param name="shiftNumber"></param>
/// <param name="active"></param>
/// <param name="tillNumber"></param>
/// <returns>Shifts</returns>
public List <ShiftStore> GetNextShift(int shiftNumber, int active, int tillNumber)
{
var innerQuery = "Select ShiftNumber from Tills where Active = " + active + " and Till_Num = " + tillNumber;
var sqlQuery = "SELECT * from ShiftStore A where A.ShiftNumber > " + shiftNumber + " and A.Active =" + active + " and ShiftNumber Not in ( " + innerQuery + ") ORDER BY A.ShiftNumber";
var sShifts = GetRecords(sqlQuery, DataSource.CSCMaster);
var shifts = new List <ShiftStore>();
foreach (DataRow fields in sShifts.Rows)
{
var shift = new ShiftStore
{
ShiftNumber = CommonUtility.GetIntergerValue(fields["ShiftNumber"]),
CurrentDay = CommonUtility.GetByteValue(fields["CurrentDay"]),
Active = CommonUtility.GetByteValue(fields["Active"]),
StartTime = CommonUtility.GetDateTimeValue(fields["StartTime"])
};
shifts.Add(shift);
}
return(shifts);
}
/// <summary>
/// Get Shifts
/// </summary>
/// <param name="active"></param>
/// <returns>Shifts</returns>
public List <ShiftStore> GetShifts(byte?active)
{
var sShifts = active.HasValue ?
GetRecords("select * from shiftstore where active =" + active, DataSource.CSCMaster)
: GetRecords("select * from shiftstore order by ShiftNumber", DataSource.CSCMaster);
var shifts = new List <ShiftStore>();
foreach (DataRow fields in sShifts.Rows)
{
var shift = new ShiftStore
{
ShiftNumber = CommonUtility.GetIntergerValue(fields["ShiftNumber"]),
CurrentDay = CommonUtility.GetByteValue(fields["CurrentDay"]),
Active = CommonUtility.GetByteValue(fields["Active"]),
StartTime = CommonUtility.GetDateTimeValue(fields["StartTime"])
};
shifts.Add(shift);
}
return(shifts);
}
/// <summary>
/// Get Givex Report details
/// </summary>
/// <param name="reportDate"></param>
/// <param name="timeFormat"></param>
/// <returns></returns>
public List <GivexDetails> GetGivexReportDetails(DateTime reportDate, string timeFormat)
{
var result = new List <GivexDetails>();
var rs = GetRecords("Select ID, CashOutID,BatchDate,BatchTime,Report From GiveXClose Where BatchDate=\'" + reportDate.ToString("yyyyMMdd") + "\' ORDER BY ID DESC ", DataSource.CSCTrans);
if (rs != null && rs.Rows.Count > 0)
{
foreach (DataRow dr in rs.Rows)
{
GivexDetails record = new GivexDetails();
record.Id = CommonUtility.GetIntergerValue(dr[0]);
record.CashOut = CommonUtility.GetStringValue(dr[1]);
record.BatchDate = CommonUtility.GetDateTimeValue(dr[2]).ToString("MM/dd/yyyy");
record.BatchTime = CommonUtility.GetDateTimeValue(dr[3]).ToString(timeFormat);
record.Report = CommonUtility.GetStringValue(dr[4]);
result.Add(record);
}
}
return(result);
}
/// <summary>
/// Method to load tax exempt
/// </summary>
/// <param name="teType">Tax exemption type</param>
/// <param name="sn">Sale number</param>
/// <param name="tillId">Till Number</param>
/// <param name="db">Data base</param>
/// <param name="checkQuota">Check Quota or not</param>
/// <returns>Tax exempt sale</returns>
public TaxExemptSale LoadTaxExempt(string teType, int sn, byte tillId, DataSource db,
bool checkQuota = true)
{
var taxExemptSale = new TaxExemptSale();
if (LoadGstExempt(ref taxExemptSale, sn, tillId, db))
{
taxExemptSale.Sale_Num = sn;
taxExemptSale.TillNumber = tillId;
}
var rsHead = GetRecords("select * from TaxExemptSaleHead " + " where SALE_NO=" + Convert.ToString(sn) + " AND TILL_NUM=" + Convert.ToString(tillId), db);
var rsLine = GetRecords("select * from TaxExemptSaleLine " + " where SALE_NO=" + Convert.ToString(sn) + " AND TILL_NUM=" + Convert.ToString(tillId), db);
if (teType == "QITE")
{
if (rsHead.Rows.Count == 0)
{
return(null);
}
}
else
{
if (rsHead.Rows.Count == 0 || rsLine.Rows.Count == 0)
{
return(null);
}
}
var rsHeadField = rsHead.Rows[0];
taxExemptSale.Sale_Num = sn;
taxExemptSale.TillNumber = tillId;
foreach (DataRow fields in rsLine.Rows)
{
var mTeLine = new TaxExemptSaleLine
{
Quantity = CommonUtility.GetFloatValue(fields["Quantity"]),
UnitsPerPkg = CommonUtility.GetFloatValue(fields["UnitQuantity"]),
EquvQuantity = CommonUtility.GetFloatValue(fields["EquvQuantity"]),
OriginalPrice = CommonUtility.GetFloatValue(fields["OriginalPrice"]),
TaxFreePrice = CommonUtility.GetFloatValue(fields["price"]),
Line_Num = CommonUtility.GetShortValue(fields["Line_Num"]),
StockCode = CommonUtility.GetStringValue(fields["Stock_Code"]),
TaxInclPrice = CommonUtility.GetFloatValue(fields["TaxIncludedAmount"]),
Amount = CommonUtility.GetFloatValue(fields["Amount"]),
ExemptedTax = CommonUtility.GetFloatValue(fields["ExemptedTax"]),
Description = CommonUtility.GetStringValue(fields["Description"]),
ProductCode = CommonUtility.GetStringValue(fields["ProductCode"])
};
mPrivateGlobals.teProductEnum productType;
Enum.TryParse(CommonUtility.GetStringValue(fields["ProductType"]), out productType);
mTeLine.ProductType = productType;
mTeLine.RunningQuota = CommonUtility.GetFloatValue(fields["RunningQuota"]);
mTeLine.OverLimit = CommonUtility.GetBooleanValue(fields["OverLimit"]);
mTeLine.TaxExemptRate = CommonUtility.GetFloatValue(fields["TaxExemptRate"]);
var tempCheckOverLimit = false;
taxExemptSale.Add_a_Line(mTeLine, ref tempCheckOverLimit, ref checkQuota);
if (!mTeLine.OverLimit)
{
continue;
}
switch (mTeLine.ProductType)
{
case mPrivateGlobals.teProductEnum.eCigarette:
case mPrivateGlobals.teProductEnum.eCigar:
case mPrivateGlobals.teProductEnum.eLooseTobacco:
taxExemptSale.TobaccoOverLimit = true;
break;
case mPrivateGlobals.teProductEnum.eGasoline:
case mPrivateGlobals.teProductEnum.eDiesel:
case mPrivateGlobals.teProductEnum.emarkedGas:
case mPrivateGlobals.teProductEnum.emarkedDiesel:
taxExemptSale.GasOverLimit = true;
break;
case mPrivateGlobals.teProductEnum.ePropane:
taxExemptSale.PropaneOverLimit = true;
break;
case mPrivateGlobals.teProductEnum.eNone:
break;
default:
throw new ArgumentOutOfRangeException();
}
}
taxExemptSale.Amount = CommonUtility.GetFloatValue(rsHeadField["SaleAmount"]);
taxExemptSale.ShiftDate = CommonUtility.GetDateTimeValue(rsHeadField["ShiftDate"]);
taxExemptSale.Sale_Time = CommonUtility.GetDateTimeValue(rsHeadField["SaleTime"]);
taxExemptSale.GasReason = CommonUtility.GetStringValue(rsHeadField["GasReason"]);
taxExemptSale.GasReasonDesp = CommonUtility.GetStringValue(rsHeadField["GasReasonDesp"]);
taxExemptSale.GasReasonDetail = CommonUtility.GetStringValue(rsHeadField["GasReasonDetail"]);
taxExemptSale.PropaneReason = CommonUtility.GetStringValue(rsHeadField["PropaneReason"]);
taxExemptSale.PropaneReasonDesp = CommonUtility.GetStringValue(rsHeadField["PropaneReasonDesp"]);
taxExemptSale.PropaneReasonDetail = CommonUtility.GetStringValue(rsHeadField["PropaneReasonDetail"]);
taxExemptSale.TobaccoReason = CommonUtility.GetStringValue(rsHeadField["TobaccoReason"]);
taxExemptSale.TobaccoReasonDesp = CommonUtility.GetStringValue(rsHeadField["TobaccoReasonDesp"]);
taxExemptSale.TobaccoReasonDetail = CommonUtility.GetStringValue(rsHeadField["TobaccoReasonDetail"]);
taxExemptSale.Shift = CommonUtility.GetShortValue(rsHeadField["Shift"]);
taxExemptSale.UserCode = CommonUtility.GetStringValue(rsHeadField["User"]);
taxExemptSale.teCardholder.CardholderID = CommonUtility.GetStringValue(rsHeadField["CardholderID"]);
taxExemptSale.teCardholder.Barcode = CommonUtility.GetStringValue(rsHeadField["Barcode"]);
taxExemptSale.teCardholder.CardNumber = CommonUtility.GetStringValue(rsHeadField["cardnumber"]);
if (teType == "QITE")
{
var rsCardHolder = GetRecords("select * from CLIENT " + " where CL_CODE=\'" + taxExemptSale.teCardholder.CardholderID + "\'", DataSource.CSCMaster);
if (rsCardHolder.Rows.Count > 0)
{
taxExemptSale.teCardholder.Name = CommonUtility.GetStringValue(rsCardHolder.Rows[0]["Cl_Name"]);
taxExemptSale.teCardholder.Address = CommonUtility.GetStringValue(rsCardHolder.Rows[0]["CL_Add1"]);
taxExemptSale.teCardholder.City = CommonUtility.GetStringValue(rsCardHolder.Rows[0]["CL_City"]);
taxExemptSale.teCardholder.PlateNumber = CommonUtility.GetStringValue(rsCardHolder.Rows[0]["PlateNumber"]);
taxExemptSale.teCardholder.PostalCode = CommonUtility.GetStringValue(rsCardHolder.Rows[0]["CL_Postal"]);
}
}
else // For AITE
{
var rsCardHolder = GetRecords("select * from TaxExemptCardRegistry " + " where CardholderID=\'" + taxExemptSale.teCardholder.CardholderID + "\'", DataSource.CSCMaster);
if (rsCardHolder.Rows.Count > 0)
{
taxExemptSale.teCardholder.Name = CommonUtility.GetStringValue(rsCardHolder.Rows[0]["Name"]);
}
}
return(taxExemptSale);
}
/// <summary>
/// Method to get card holder
/// </summary>
/// <param name="isBarCode">Is bar code or not</param>
/// <param name="strNumber">Number</param>
/// <param name="matchCount">Match count</param>
/// <param name="ageRestrict">Age restriction</param>
/// <returns>Tax exempt card holder</returns>
public teCardholder GetCardHolder(bool isBarCode, string strNumber, out short matchCount, int ageRestrict)
{
var dateStart = DateTime.Now;
_performancelog.Debug($"Start,CustomerService,GetCardHolder,{string.Empty},{DateTime.Now:hh.mm.ss.ffffff}");
string strSql;
teCardholder cardHolder = new teCardholder {
IsValidCardHolder = false
};
if (isBarCode)
{
strSql = "Select * from TaxExemptCardRegistry where BarCode=\'" + strNumber + "\' OR AltBarCode=\'" + strNumber + "\'";
}
else
{
strSql = "Select * from TaxExemptCardRegistry where CardNumber=\'" + strNumber + "\' OR AltCardNumber=\'" + strNumber + "\'";
}
var dt = GetRecords(strSql, DataSource.CSCMaster);
matchCount = 0;
if (dt != null && dt.Rows.Count > 0)
{
matchCount = Convert.ToInt16(dt.Rows.Count);
if (matchCount > 1)
{
cardHolder.IsValidCardHolder = false;
return(cardHolder);
}
DateTime mBirthDate;
if (CommonUtility.GetBooleanValue(dt.Rows[0]["Birthdate"]))
{
mBirthDate = DateAndTime.DateAdd(DateInterval.Year, Convert.ToDouble(-1 * (ageRestrict + 1)), DateAndTime.Today);
}
else
{
mBirthDate = CommonUtility.GetDateTimeValue(dt.Rows[0]["Birthdate"]);
}
cardHolder.Birthdate = mBirthDate;
cardHolder.Name = CommonUtility.GetStringValue(dt.Rows[0]["Name"]);
cardHolder.CardholderID = CommonUtility.GetStringValue(dt.Rows[0]["CardholderID"]);
cardHolder.IsValidCardHolder = false;
if (isBarCode)
{
if (CommonUtility.GetStringValue(dt.Rows[0]["Barcode"]) == strNumber)
{
cardHolder.ValidateMode = 1;
cardHolder.CardNumber = CommonUtility.GetStringValue(dt.Rows[0]["CardNumber"]);
}
else
{
cardHolder.ValidateMode = 2;
cardHolder.CardNumber = CommonUtility.GetStringValue(dt.Rows[0]["AltCardNumber"]);
}
cardHolder.Barcode = strNumber;
}
else
{
if (CommonUtility.GetStringValue(dt.Rows[0]["CardNumber"]) == strNumber)
{
cardHolder.ValidateMode = 3;
cardHolder.Barcode = CommonUtility.GetStringValue(dt.Rows[0]["Barcode"]);
}
else
{
cardHolder.ValidateMode = 4;
cardHolder.Barcode = CommonUtility.GetStringValue(dt.Rows[0]["AltBarCode"]);
}
cardHolder.CardNumber = strNumber;
}
cardHolder.GasQuota = CommonUtility.GetFloatValue(dt.Rows[0]["GasQuota"]);
cardHolder.PropaneQuota = CommonUtility.GetFloatValue(dt.Rows[0]["PropaneQuota"]);
cardHolder.TobaccoQuota = CommonUtility.GetFloatValue(dt.Rows[0]["TobaccoQuota"]);
cardHolder.IsValidCardHolder = true;
}
_performancelog.Debug($"End,CustomerService,GetCardHolder,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}");
return(cardHolder);
}
/// <summary>
/// Get SaleBy SaleNumber
/// </summary>
/// <param name="saleNumber">Sale number</param>
/// <param name="tillNumber">Till number</param>
/// <param name="saleDate">Sale date</param>
/// <param name="defaultCustCode">Default customer code</param>
/// <param name="isSaleFound">Sale found or not</param>
/// <param name="isReturnable">Sale returned or not</param>
/// <param name="teType">Taxe exempt type</param>
/// <param name="teGetName">Tax exempt name</param>
/// <param name="taxExemptGa">Tax exempt ga</param>
/// <param name="pDefaultCustomer">Deafult customer</param>
/// <returns>Sale</returns>
public Sale GetSaleBySaleNumber(int saleNumber, int tillNumber, DateTime saleDate, string teType, bool teGetName, bool taxExemptGa, bool pDefaultCustomer, string defaultCustCode, out bool isSaleFound, out bool isReturnable)
{
var dateStart = DateTime.Now;
_performancelog.Debug($"Start,ReturnSaleService,GetSaleBySaleNumber,{string.Empty},{dateStart:hh.mm.ss.ffffff}");
DataSource db = DataSource.CSCTrans;
bool saleFound = false;
Sale sale = new Sale();
isSaleFound = true;
isReturnable = true;
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 rsHead = GetRecords(strSqlHead, 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);
// If sale number is found exit for do and use this recordset
if (rsHead != null && rsHead.Rows.Count != 0)
{
saleFound = true;
db = DataSource.CSCTills;
}
}
else
{
saleFound = true;
db = DataSource.CSCTrans;
}
if (!saleFound)
{
isSaleFound = false;
isReturnable = false;
return(sale);
}
var rsHeadFields = rsHead.Rows[0];
if (saleDate != new DateTime() && CommonUtility.GetDateTimeValue(rsHeadFields["Sale_Date"]) < saleDate)
{
isReturnable = false;
return(sale);
}
sale.LoadingTemp = true;
sale.TillNumber = CommonUtility.GetByteValue(tillNumber);
sale.Sale_Num = saleNumber;
sale.Sale_Amount = CommonUtility.GetDecimalValue(rsHeadFields["SALE_AMT"]);
sale.Sale_Invc_Disc = CommonUtility.GetDecimalValue(rsHeadFields["INVC_DISC"]);
sale.TotalTaxSaved = CommonUtility.GetFloatValue(CommonUtility.GetDecimalValue(rsHeadFields["SALE_AMT"]) - CommonUtility.GetDecimalValue(rsHeadFields["TEND_AMT"]));
sale.Customer = LoadCustomer(CommonUtility.GetStringValue(rsHeadFields["Client"]), pDefaultCustomer, defaultCustCode);
if ((CommonUtility.GetStringValue(rsHeadFields["T_type"]) == "RUNAWAY" || CommonUtility.GetStringValue(rsHeadFields["T_type"]) == "PUMPTEST") && CommonUtility.GetDecimalValue(rsHeadFields["Sale_amt"]) < 0)
{
sale.Sale_Type = "SALE";
}
else
{
sale.Sale_Type = CommonUtility.GetStringValue(rsHeadFields["T_type"]);
}
sale.Void_Num = saleNumber;
sale.Sale_Deposit = CommonUtility.GetDecimalValue(rsHeadFields["Deposit"]);
sale.TreatyNumber = CommonUtility.GetStringValue(rsHeadFields["TreatyNumber"]);
if (teType != "AITE" && teType != "QITE" && teGetName)
{
sale.TreatyName = GetTreatyName(CommonUtility.GetStringValue(rsHeadFields["TreatyNumber"]));
}
sale.ReferenceNumber = CommonUtility.GetStringValue(rsHeadFields["ReferenceNum"]);
if (sale.ReferenceNumber != "" && taxExemptGa)
{
sale.EligibleTaxEx = true;
}
//Added to load the original Loyalty Card
var rsDiscountTender = GetRecords("select * from DiscountTender where SALE_NO=" + CommonUtility.GetStringValue(saleNumber) + " AND TILL_NUM=" + tillNumber, db);
if (rsDiscountTender != null && rsDiscountTender.Rows.Count != 0)
{
var tenderFields = rsDiscountTender.Rows[0];
sale.Customer.LoyaltyCard = CommonUtility.GetStringValue(tenderFields["CardNum"]);
sale.CouponID = CommonUtility.GetStringValue(tenderFields["CouponID"]);
}
sale.ForCorrection = false;
sale.Sale_Totals.Invoice_Discount_Type = Convert.ToString(rsHeadFields["Disc_Type"]);
sale.Sale_Totals.Invoice_Discount = Convert.ToDecimal(Convert.ToInt32(rsHeadFields["Invc_Disc"]) * -1);
sale.Sale_Totals.Penny_Adj = Convert.ToDecimal(-1 * Convert.ToInt32(DBNull.Value.Equals(rsHeadFields["PENNY_ADJ"]) ? 0 : rsHeadFields["PENNY_ADJ"])); //
sale.ReverseRunaway = (string)rsHeadFields["T_type"] == "RUNAWAY" && Convert.ToInt32(rsHeadFields["Sale_amt"]) > 0; //
sale.ReversePumpTest = (string)rsHeadFields["T_type"] == "PUMPTEST" && Convert.ToInt32(rsHeadFields["Sale_amt"]) > 0; //
_performancelog.Debug($"End,ReturnSaleService,GetSaleBySaleNumber,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}");
return(sale);
}
/// <summary>
/// Get the list of tills
/// </summary>
/// <param name="posId">Pos id</param>
/// <param name="tillNumber">Till number</param>
/// <param name="shiftDate">Shift date</param>
/// <param name="userCode">User code</param>
/// <param name="active">Is active or not</param>
/// <param name="process">Is processing or not</param>
/// <returns>Tills</returns>
public List <Till> GetTills(int?posId, int?tillNumber, DateTime?shiftDate,
string userCode, int?active, int?process)
{
var dateStart = DateTime.Now;
_performancelog.Debug($"Start,TillService,GetTills,{string.Empty},{DateTime.Now:hh.mm.ss.ffffff}");
var whereClause = new StringBuilder();
string query;
if (posId.HasValue)
{
whereClause.Append($" AND POSID={posId.Value}");
}
if (tillNumber.HasValue)
{
whereClause.Append($" AND TILL_NUM={tillNumber.Value} ");
}
if (shiftDate.HasValue)
{
whereClause.Append($" AND ShiftDate={shiftDate.Value}");
}
if (!string.IsNullOrEmpty(userCode))
{
whereClause.Append($" AND UserLoggedOn='{userCode}'");
}
if (active.HasValue)
{
whereClause.Append($" AND ACTIVE={active.Value}");
}
if (process.HasValue)
{
whereClause.Append($" AND PROCESS={process.Value}");
}
if (whereClause.Length != 0)
{
var condition = whereClause.ToString().Substring(5, whereClause.Length - 5);
query = $"Select * from Tills where {condition}";
}
else
{
query = "Select * from Tills";
}
var sTills = GetRecords(query, DataSource.CSCMaster);
var tills = new List <Till>();
foreach (DataRow row in sTills.Rows)
{
var till = new Till
{
Number = CommonUtility.GetShortValue(row["TILL_NUM"]),
Active = CommonUtility.GetBooleanValue(row["ACTIVE"]),
Processing = CommonUtility.GetBooleanValue(row["PROCESS"]),
Float = CommonUtility.GetDecimalValue(row["FLOAT"]),
BonusFloat = CommonUtility.GetDecimalValue(row["CashBonusFloat"]),
Cash = CommonUtility.GetDecimalValue(row["CASH"]),
Date_Open = CommonUtility.GetDateTimeValue(row["DATE_OPEN"]),
Time_Open = CommonUtility.GetDateTimeValue(row["TIME_OPEN"]),
ShiftDate = CommonUtility.GetDateTimeValue(row["ShiftDate"]),
Shift = CommonUtility.GetShortValue(row["ShiftNumber"]),
UserLoggedOn = CommonUtility.GetStringValue(row["UserLoggedOn"]),
POSId = CommonUtility.GetIntergerValue(row["POSID"]),
CashBonus = CommonUtility.GetDecimalValue(row["CashBonus"])
};
tills.Add(till);
}
_performancelog.Debug($"End,TillService,GetTills,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}");
return(tills);
}