/// <summary>
/// Method to get all message buttons
/// </summary>
/// <returns>Message buttons list</returns>
public List <MessageButton> GetAllMessageButtons()
{
var dateStart = DateTime.Now;
_performancelog.Debug($"Start,UtilityService,GetAllMessageButtons,{string.Empty},{DateTime.Now:hh.mm.ss.ffffff}");
var messages = new List <MessageButton>();
//var dt = GetRecords("SELECT * FROM MessageButtons WHERE Button >= "
// + Convert.ToString(pageIndex) + " AND " + " Button <= "
// + Convert.ToString(pageSize) + " " + "ORDER BY Button ", DataSource.CSCMaster);
var dt = GetRecords("SELECT * FROM MessageButtons ORDER BY Button ", DataSource.CSCMaster);
foreach (DataRow row in dt.Rows)
{
messages.Add(new MessageButton
{
Index = CommonUtility.GetIntergerValue(row["Button"]),
Caption = CommonUtility.GetStringValue(row["Caption"]),
Message = CommonUtility.GetStringValue(row["Message"]),
});
}
_performancelog.Debug($"End,UtilityService,GetAllMessageButtons,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}");
return(messages);
}
/// <summary>
/// Method to get message by button id
/// </summary>
/// <param name="buttonId">Button id</param>
/// <returns>Message button</returns>
public MessageButton GetMessageByButtonId(int buttonId)
{
var dateStart = DateTime.Now;
_performancelog.Debug($"Start,UtilityService,GetMessageByButtonId,{string.Empty},{DateTime.Now:hh.mm.ss.ffffff}");
var dt = GetRecords("SELECT * FROM MessageButtons WHERE Button = "
+ Convert.ToString(buttonId), DataSource.CSCMaster);
_performancelog.Debug($"End,UtilityService,GetMessageByButtonId,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}");
if (dt.Rows.Count == 0)
{
return(null);
}
else
{
return new MessageButton
{
Index = CommonUtility.GetIntergerValue(dt.Rows[0]["Button"]),
Caption = CommonUtility.GetStringValue(dt.Rows[0]["Caption"]),
Message = CommonUtility.GetStringValue(dt.Rows[0]["Message"]),
}
};
}
}
/// <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 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 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 tax exempt sale line
/// </summary>
/// <param name="saleNumber">Sale number</param>
/// <param name="tillNumber">Till number</param>
/// <returns>Tax exempt sale lines</returns>
public List <TaxExemptSaleLine> GetTaxExemptSaleLines(int saleNumber, int tillNumber)
{
var dateStart = DateTime.Now;
_performancelog.Debug($"Start,TaxService,GetTaxExemptSaleLines,{string.Empty},{dateStart:hh.mm.ss.ffffff}");
var taxExemptSalelines = new List <TaxExemptSaleLine>();
var rsSite =
GetRecords(
"select * from TaxExemptSaleLine a inner join TaxExemptSaleHead b " +
" On a.sale_no = b.sale_No and a.till_num = b.till_num Where a.sale_no =" +
Convert.ToString(saleNumber) + " and a.Till_num =" + Convert.ToString(tillNumber),
DataSource.CSCTills);
foreach (DataRow dataRow in rsSite.Rows)
{
taxExemptSalelines.Add(new TaxExemptSaleLine
{
ExemptedTax = CommonUtility.GetIntergerValue(dataRow["ExemptedTax"]),
ProductType = (mPrivateGlobals.teProductEnum)Enum.Parse(typeof(mPrivateGlobals.teProductEnum), CommonUtility.GetStringValue(dataRow["ProductType"]), true),
CardHolderID = CommonUtility.GetStringValue(dataRow["CardholderID"])
});
}
_performancelog.Debug($"End,TaxService,GetTaxExemptSaleLines,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}");
return(taxExemptSalelines);
}
/// <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>
/// Method to get original sale head
/// </summary>
/// <param name="invoiceId">Sale number</param>
/// <param name="tillNumber">Till number</param>
/// <param name="db"></param>
/// <returns>Sale head</returns>
public SaleHead GetOriginalSaleHead(int invoiceId, int tillNumber, out DataSource db)
{
db = DataSource.CSCTills;
var rsHead = GetRecords(" SELECT * FROM SaleHead " + " WHERE SaleHead.Sale_No = " + Convert.ToString(invoiceId) + " and TILL=" + tillNumber, db); //, adLockReadOnly)
if (rsHead.Rows.Count == 0)
{
db = DataSource.CSCTrans;
rsHead = GetRecords(" SELECT * FROM SaleHead " + " WHERE SaleHead.Sale_No = " + Convert.ToString(invoiceId) + " and TILL=" + tillNumber, db); //, adLockReadOnly)
}
if (rsHead.Rows.Count == 0)
{
return(null);
}
return(new SaleHead
{
SaleNumber = CommonUtility.GetIntergerValue(rsHead.Rows[0]["sale_no"]),
TillNumber = CommonUtility.GetIntergerValue(rsHead.Rows[0]["Till"]),
Client = CommonUtility.GetStringValue(rsHead.Rows[0]["Client"]),
TType = CommonUtility.GetStringValue(rsHead.Rows[0]["T_type"]),
Deposit = CommonUtility.GetDecimalValue(rsHead.Rows[0]["Deposit"]),
TreatyNumber = CommonUtility.GetStringValue(rsHead.Rows[0]["TreatyNumber"]),
Change = CommonUtility.GetDecimalValue(rsHead.Rows[0]["Change"]),
PennyAdjust = CommonUtility.GetDecimalValue(rsHead.Rows[0]["Penny_Adj"]),
DiscountType = CommonUtility.GetStringValue(rsHead.Rows[0]["Disc_Type"]),
INVCDiscount = CommonUtility.GetDecimalValue(rsHead.Rows[0]["Invc_Disc"])
});
}
/// <summary>
/// Method to get prepay global
/// </summary>
/// <param name="pumpId">Pump id</param>
/// <returns>Prepay</returns>
public recPump GetPrepayGlobal(int pumpId)
{
var rec = new recPump();
var dRecset = GetRecords("SELECT * FROM PrepayGlobal WHERE pumpID = " + Convert.ToString(pumpId), DataSource.CSCTrans);
if (dRecset.Rows.Count == 0)
{
rec.IsPrepay = false;
rec.IsPrepayLocked = false;
if (!(rec.IsHoldPrepay && rec.PrepayAmount > 0))
{
rec.PrepayAmount = 0;
rec.PrepayInvoiceID = 0;
rec.PrepayPosition = 0;
}
}
else
{
rec.PrepayAmount = CommonUtility.GetFloatValue(dRecset.Rows[0]["Amount"]);
rec.IsPrepayLocked = CommonUtility.GetBooleanValue(dRecset.Rows[0]["Locked"]);
rec.PrepayInvoiceID = CommonUtility.GetIntergerValue(dRecset.Rows[0]["InvoiceID"]);
rec.PrepayPosition = CommonUtility.GetShortValue(dRecset.Rows[0]["PositionID"]);
rec.IsPrepay = true;
}
return(rec);
}
/// <summary>
/// Method to load prepay
/// </summary>
/// <param name="pumpId">Pump Id</param>
/// <returns>Prepay</returns>
public recPump LoadPrepay(int pumpId)
{
recPump rec;
var dRecset = GetRecords("SELECT * FROM PrepayGlobal WHERE pumpID = " + Convert.ToString(pumpId), DataSource.CSCTrans);
if (dRecset.Rows.Count == 0)
{
rec = new recPump
{
IsHoldPrepay = false,
IsPrepay = false,
PrepayAmount = 0,
IsPrepayLocked = false,
PrepayInvoiceID = 0,
PrepayPosition = 0
};
}
else
{
rec = new recPump
{
IsHoldPrepay = true,
IsPrepay = true,
PrepayAmount = CommonUtility.GetFloatValue(dRecset.Rows[0]["Amount"]),
IsPrepayLocked = CommonUtility.GetBooleanValue(dRecset.Rows[0]["Locked"]),
PrepayInvoiceID = CommonUtility.GetIntergerValue(dRecset.Rows[0]["InvoiceID"]),
PrepayPosition = CommonUtility.GetShortValue(dRecset.Rows[0]["PositionID"])
};
}
return(rec);
}
/// <summary>
/// Get the maximum Shift Number
/// </summary>
/// <returns></returns>
public int GetMaximumShiftNumber()
{
var sShift = GetRecords("Select Max(ShiftStore.ShiftNumber) as [MaxShift] FROM ShiftStore ", DataSource.CSCMaster);
var fields = sShift.Rows[0];
return(CommonUtility.GetIntergerValue(fields["MaxShift"]));
}
/// <summary>
/// Method to get max drop id
/// <param name="dataSource">Data Source</param>
/// </summary>
/// <returns></returns>
public int GetMaxDropId(DataSource dataSource)
{
var dt = GetRecords("Select max(DropID) as DropID from DropHeader", dataSource);
if (dt == null || dt.Rows.Count == 0)
{
return(0);
}
return(DBNull.Value.Equals(dt.Rows[0]["DropID"]) ? 0 : CommonUtility.GetIntergerValue(dt.Rows[0]["DropID"]) + 1);
}
/// <summary>
/// Method to load device
/// </summary>
/// <returns>Device</returns>
public Device LoadDevice()
{
Device device = null;
var query = "select max(DeviceID) as MaxID from Device";
var records = GetRecords(query, DataSource.CSCAdmin);
if (records.Rows.Count > 0)
{
var maxId = CommonUtility.GetIntergerValue(records.Rows[0]["MAXID"]);
if (maxId > 0)
{
device = new Device {
DeviceName = new string[maxId + 1]
};
var rsDevice = GetRecords("select * from Device", DataSource.CSCAdmin);
foreach (DataRow row in rsDevice.Rows)
{
device.DeviceName[CommonUtility.GetIntergerValue(row["DeviceId"])] = CommonUtility.GetStringValue(row["DeviceName"]);
string deviceName = CommonUtility.GetStringValue(row["DeviceName"]);
switch (Strings.UCase(deviceName))
{
case "SCANNER":
device.ScannerID = CommonUtility.GetByteValue(row["DeviceID"]);
break;
case "RECEIPTPRINTER":
device.ReceiptPrinterID = CommonUtility.GetByteValue(row["DeviceID"]);
break;
case "MSR":
device.MSRID = CommonUtility.GetByteValue(row["DeviceID"]);
break;
case "CASHDRAWER":
device.CashDrawerID = CommonUtility.GetByteValue(row["DeviceID"]);
break;
case "CUSTOMERDISPLAY":
device.CustomerDisplayID = CommonUtility.GetByteValue(row["DeviceID"]);
break;
case "REPORTPRINTER":
device.ReportPrinterID = CommonUtility.GetByteValue(row["DeviceID"]);
break;
case "SCALE":
device.ScaleID = CommonUtility.GetByteValue(row["DeviceID"]);
break;
}
}
}
}
return(device);
}
/// <summary>
/// Method to initialise data
/// </summary>
/// <returns></returns>
public bool ClassInitialize()
{
bool myEncryptEnabled;
string query = "select * from Variables where Name=\'EncryptEnabled\'";
_connection = new SqlConnection(GetConnectionString(DataSource.CSCMaster));
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
_dataTable = new DataTable();
_adapter = new SqlDataAdapter(query, _connection);
_adapter.Fill(_dataTable);
if (_dataTable.Rows.Count != 0)
{
if (!DBNull.Value.Equals(_dataTable.Rows[0]["Value"]))
{
myEncryptEnabled = CommonUtility.GetIntergerValue(_dataTable.Rows[0]["Value"]) == 1;
}
else
{
myEncryptEnabled = true;
_dataTable.Rows[0]["Value"] = "1";
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.UpdateCommand = builder.GetUpdateCommand();
_adapter.Update(_dataTable);
}
}
else
{
query = "select Max(VarIndex) as MaxIndex from Variables";
_connection = new SqlConnection(GetConnectionString(DataSource.CSCMaster));
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
_dataTable = new DataTable();
_adapter = new SqlDataAdapter(query, _connection);
_adapter.Fill(_dataTable);
DataRow rsTmpfields = _dataTable.NewRow();
rsTmpfields["VarIndex"] = Convert.ToInt32(DBNull.Value.Equals(rsTmpfields["MaxIndex"]) ? 0 : rsTmpfields["MaxIndex"]) + 1;
rsTmpfields["Name"] = "EncryptEnabled";
rsTmpfields["Value"] = "1";
rsTmpfields["Description"] = "To Encrypt Card Number or not, 1-Yes/0-No.";
_dataTable.Rows.Add(rsTmpfields);
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.InsertCommand = builder.GetInsertCommand();
_adapter.Update(_dataTable);
myEncryptEnabled = true;
}
_connection.Close();
_adapter?.Dispose();
return(myEncryptEnabled);
}
/// <summary>
/// Method to get total departments
/// </summary>
/// <returns>Total departments</returns>
public int GetTotalDepartments()
{
var dateStart = DateTime.Now;
_performancelog.Debug($"Start,UtilityService,GetTotalDepartments,{string.Empty},{DateTime.Now:hh.mm.ss.ffffff}");
var rs = GetRecords("SELECT COUNT(*) AS Tot FROM Dept WHERE Cnt_Detail>0", DataSource.CSCMaster);
_performancelog.Debug($"End,UtilityService,GetTotalDepartments,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}");
return(CommonUtility.GetIntergerValue(rs.Rows[0]["Tot"]));
}
/// <summary>
/// Method to get all tills
/// </summary>
/// <returns>List of till numbers</returns>
public List <int> GetAllTills()
{
var tills = new List <int>();
var payAtPumpTill = "100";
var rs = GetRecords("SELECT Till_Num FROM Tills WHERE Till_Num<>" + payAtPumpTill, DataSource.CSCMaster);
foreach (DataRow row in rs.Rows)
{
tills.Add(CommonUtility.GetIntergerValue(row["Till_Num"]));
}
return(tills);
}
/// <summary>
/// Check Logged in user of POS
/// </summary>
/// <param name="userName">User name</param>
/// <param name="posId">Pos id</param>
/// <returns>True or false</returns>
public bool CheckLoggedinUserPos(string userName, int posId)
{
var rsUser = GetRecords("select Till_num, POSID from tills where userloggedon=\'" + userName + "\' and Process=1", DataSource.CSCMaster);
if (rsUser != null && rsUser.Rows.Count > 0)
{
if (CommonUtility.GetIntergerValue(rsUser.Rows[0]["posID"]) != posId)
{
return(true);
}
}
return(false);
}
/// <summary>
/// Method to get list of treaty numbers
/// </summary>
/// <param name="query">Query</param>
/// <returns>List of treaty numbers</returns>
public List <TreatyNo> GetTreatyNumbers(string query)
{
var oRecs = GetRecords(query, DataSource.CSCMaster);
return((from DataRow treaty in oRecs.Rows
select new TreatyNo
{
TreatyNumber = CommonUtility.GetStringValue(treaty["TreatyNo"]),
AltTreatyNo = CommonUtility.GetShortValue(treaty["AltTreatyNo"]),
TobaccoQuota = CommonUtility.GetIntergerValue(treaty["TobaccoQuota"]),
GasQuota = CommonUtility.GetFloatValue(treaty["GasQuota"]),
TreatyName = CommonUtility.GetStringValue(treaty["TreatyName"])
}).ToList());
}
///// <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 all coins
/// </summary>
/// <returns></returns>
public List <CashBonus> GetCashBonusCoins()
{
var dt = GetRecords("Select * FROM CBonusDrawTypes", DataSource.CSCMaster);
var coins = new List <CashBonus>();
foreach (DataRow dr in dt.Rows)
{
coins.Add(new CashBonus
{
CurrencyName = CommonUtility.GetStringValue(dr["Currency_Name"]),
Value = CommonUtility.GetDecimalValue(CommonUtility.GetDecimalValue(dr["Value"]).ToString("0.00")),
ButtonNumber = CommonUtility.GetIntergerValue(dr["Button_Number"])
});
}
return(coins);
}
/// <summary>
/// Method to get tax free price
/// </summary>
/// <param name="blForFuel">For fule price or not</param>
/// <param name="sProductKey">Product key</param>
/// <param name="stockcode">Stock code</param>
/// <param name="quantityPerPkg">Quantity per kg</param>
/// <param name="baseUnitQty">Base unit quantity</param>
/// <param name="taxExemptTaxCode">tax code</param>
/// <param name="productCode">Product code</param>
/// <param name="eOutCategory">Category</param>
/// <param name="dOutUnitsPerPkg">Units per kg</param>
/// <param name="sOutUpcCode">Out up code</param>
/// <param name="isError">Is error or not</param>
/// <returns>True or false</returns>
public bool TeGetTaxFreePriceByRate(bool blForFuel, ref string sProductKey, string stockcode,
ref short quantityPerPkg, ref short baseUnitQty,
ref short taxExemptTaxCode, ref string productCode,
ref mPrivateGlobals.teProductEnum eOutCategory,
ref float dOutUnitsPerPkg, ref string sOutUpcCode,
out bool isError)
{
var dateStart = DateTime.Now;
_performancelog.Debug($"Start,TaxService,TeGetTaxFreePriceByRate,{string.Empty},{dateStart:hh.mm.ss.ffffff}");
DataTable oRecs;
if (blForFuel)
{
oRecs = GetRecords("SELECT CategoryFK,ProductCode,TAX_CODE, QuantityPerPkg, BaseUnitQty, 1 As UnitsPerPkg, \'\' As UpcCode from ProductTaxExempt Where ProductKey =\'" + stockcode + "\' ", DataSource.CSCMaster);
}
else
{
oRecs = GetRecords("SELECT CategoryFK, ProductCode,UnitsPerPkg, TAX_CODE, QuantityPerPkg, BaseUnitQty, UpcCode FROM ProductTaxExempt Where ProductKey=\'" + sProductKey + "\'", DataSource.CSCMaster);
}
if (oRecs.Rows.Count == 0)
{
isError = false;
return(false);
}
taxExemptTaxCode = CommonUtility.GetShortValue(oRecs.Rows[0]["Tax_Code"]);
productCode = CommonUtility.GetStringValue(oRecs.Rows[0]["ProductCode"]);
if (CommonUtility.GetBooleanValue(oRecs.Rows[0]["CategoryFK"]))
{
eOutCategory = (mPrivateGlobals.teProductEnum)CommonUtility.GetIntergerValue(oRecs.Rows[0]["CategoryFK"]);
}
else
{
isError = true;
return(true);
}
isError = false;
dOutUnitsPerPkg = CommonUtility.GetFloatValue(oRecs.Rows[0]["UnitsPerPkg"]);
sOutUpcCode = CommonUtility.GetStringValue(oRecs.Rows[0]["UpcCode"]);
quantityPerPkg = CommonUtility.GetShortValue(oRecs.Rows[0]["QuantityPerPkg"]);
baseUnitQty = CommonUtility.GetShortValue(oRecs.Rows[0]["BaseUnitQty"]);
_performancelog.Debug($"End,TaxService,TeGetTaxFreePriceByRate,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}");
return(true);
}
/// <summary>
/// Save Close Batch
/// </summary>
/// <param name="cashoutId">CashoutId</param>
/// <param name="reports">Reports</param>
public void SaveCloseBatch(string cashoutId, string reports)
{
var dateStart = DateTime.Now;
_performancelog.Debug($"Start,GivexService,SaveCloseBatch,{string.Empty},{DateTime.Now:hh.mm.ss.ffffff}");
int closeId;
var dt = GetRecords("select MAX(ID) as MaxID from GiveXClose", DataSource.CSCTrans);
if (dt == null || dt.Rows.Count == 0)
{
closeId = 1;
}
else if (DBNull.Value.Equals(dt.Rows[0]["MAXID"]))
{
closeId = 1;
}
else
{
closeId = CommonUtility.GetIntergerValue(dt.Rows[0]["MAXID"]) + 1;
}
_connection = new SqlConnection(GetConnectionString(DataSource.CSCTrans));
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
_dataTable = new DataTable();
_adapter = new SqlDataAdapter("select * from GiveXClose where ID = " + closeId, _connection);
_adapter.Fill(_dataTable);
if (_dataTable.Rows.Count == 0)
{
DataRow rsfields = _dataTable.NewRow();
rsfields["ID"] = closeId;
rsfields["CashoutId"] = cashoutId.Trim();
rsfields["BatchDate"] = DateTime.Now.ToShortDateString();
rsfields["BatchTime"] = DateTime.Now.ToShortTimeString();
rsfields["Report"] = reports.Trim();
_dataTable.Rows.Add(rsfields);
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.InsertCommand = builder.GetInsertCommand();
_adapter.Update(_dataTable);
}
_connection.Close();
_adapter?.Dispose();
_performancelog.Debug($"End,GivexService,SaveCloseBatch,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}");
}
/// <summary>
/// Get the Pos Id by Ip Address
/// </summary>
/// <param name="ipAddress">Ip address</param>
/// <returns>Pos id</returns>
public int GetPosId(string ipAddress)
{
var dateStart = DateTime.Now;
_performancelog.Debug($"Start,UtilityService,GetPosId,{string.Empty},{DateTime.Now:hh.mm.ss.ffffff}");
var posId = 0;
var query = $"Select * From POS_IP_Address where IP_Address='{ipAddress}'";
var sPosId = GetRecords(query, DataSource.CSCAdmin);
if (sPosId.Rows.Count > 0)
{
posId = CommonUtility.GetIntergerValue(sPosId.Rows[0]["ID"]);
}
_performancelog.Debug($"End,UtilityService,GetPosId,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}");
return(posId);
}
/// <summary>
/// Method to get lsit of sounds
/// </summary>
/// <returns>List of sounds</returns>
public List <Sound> GetAllSounds()
{
var sounds = new List <Sound>();
var rs = GetRecords("SELECT * FROM Sounds", DataSource.CSCMaster);
foreach (DataRow row in rs.Rows)
{
sounds.Add(new Sound
{
ID = CommonUtility.GetIntergerValue(row["ID"]),
Active = CommonUtility.GetBooleanValue(row["Active"]),
SoundName = CommonUtility.GetStringValue(row["soundname"]),
SoundType = CommonUtility.GetStringValue(row["soundType"])
});
}
return(sounds);
}
/// <summary>
/// Method to get tax exempt tax free price by price
/// </summary>
/// <param name="sSql">Query</param>
/// <param name="dOutPrice">Price</param>
/// <param name="originalPrice">Original Price</param>
/// <param name="eOutCategory">Category</param>
/// <param name="dOutUnitsPerPkg">Units per kg</param>
/// <param name="sOutUpcCode">UPC code</param>
/// <param name="bFound">Found or not</param>
/// <param name="isError">Error or not</param>
/// <returns>True or false</returns>
public bool TeGetTaxFreePriceByPrice(string sSql, ref float dOutPrice, double originalPrice,
ref mPrivateGlobals.teProductEnum eOutCategory, ref float dOutUnitsPerPkg,
ref string sOutUpcCode, out bool bFound,
out bool isError)
{
var dateStart = DateTime.Now;
_performancelog.Debug($"Start,TaxService,TeGetTaxFreePriceByPrice,{string.Empty},{dateStart:hh.mm.ss.ffffff}");
var oRecs = GetRecords(sSql, DataSource.CSCPump);
//eCategory and bFound are already set.
if (oRecs.Rows.Count > 0)
{
if (CommonUtility.GetIntergerValue(oRecs.Rows[0]["TaxFreePrice"]) != 0)
{
dOutPrice = CommonUtility.GetFloatValue(oRecs.Rows[0]["TaxFreePrice"]);
}
else
{
dOutPrice = (float)originalPrice;
}
if (CommonUtility.GetBooleanValue(oRecs.Rows[0]["CategoryFK"]))
{
eOutCategory = (mPrivateGlobals.teProductEnum)CommonUtility.GetIntergerValue(oRecs.Rows[0]["CategoryFK"]);
}
else
{
isError = true;
bFound = false;
return(false);
}
dOutUnitsPerPkg = CommonUtility.GetFloatValue(oRecs.Rows[0]["UnitsPerPkg"]);
sOutUpcCode = CommonUtility.GetStringValue(oRecs.Rows[0]["UpcCode"]);
bFound = true;
isError = false;
return(true);
}
bFound = false;
isError = false;
_performancelog.Debug($"End,TaxService,TeGetTaxFreePriceByPrice,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}");
return(false);
}
/// <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 Till by Till Number
/// </summary>
/// <param name="tillNumber">Till number</param>
/// <returns>Till</returns>
public bool IsTillExists(int tillNumber)
{
_connection = new SqlConnection(GetConnectionString(DataSource.CSCMaster));
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
_dataTable = new DataTable();
var query = "Select COUNT(*) From Tills where till_num=" + tillNumber;
SqlCommand cmd = new SqlCommand(query, _connection);
var count = CommonUtility.GetIntergerValue(cmd.ExecuteScalar());
if (count > 0)
{
return(true);
}
return(false);
}
/// <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 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>
/// Method to get all departments
/// </summary>
/// <returns>List of departments</returns>
public List <Department> GetAllDepartments()
{
var dateStart = DateTime.Now;
_performancelog.Debug($"Start,UtilityService,GetAllDepartments,{string.Empty},{DateTime.Now:hh.mm.ss.ffffff}");
var rs = GetRecords("SELECT Dept, Dept_Name, Cnt_Detail FROM Dept Where Cnt_Detail>0 AND Cnt_Detail<5 ORDER BY Dept", DataSource.CSCMaster);
var departments = new List <Department>();
foreach (DataRow row in rs.Rows)
{
departments.Add(new Department
{
Dept = CommonUtility.GetStringValue(row["Dept"]),
DeptName = CommonUtility.GetStringValue(row["Dept_Name"]),
CountDetail = CommonUtility.GetIntergerValue(row["Cnt_Detail"])
});
}
_performancelog.Debug($"End,UtilityService,GetAllDepartments,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}");
return(departments);
}
