/// <summary>
/// Method to add cash bonus draw
/// </summary>
/// <param name="cashBonusDraw">Cash draw</param>
public void AddCashBonusDraw(CashBonusDraw cashBonusDraw)
{
var _connection = new SqlConnection(GetConnectionString(DataSource.CSCTills));
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
var _dataTable = new DataTable();
var _adapter = new SqlDataAdapter("select * from CashDraw where TILL=" + cashBonusDraw.TillNumber, _connection);
_adapter.Fill(_dataTable);
var fields = _dataTable.NewRow();
fields["Draw_Date"] = cashBonusDraw.DrawDate;
fields["User"] = cashBonusDraw.User;
fields["Till"] = cashBonusDraw.TillNumber;
fields["Reason"] = cashBonusDraw.Reason;
fields["CashBonus"] = cashBonusDraw.CashBonus;
_dataTable.Rows.Add(fields);
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.InsertCommand = builder.GetInsertCommand();
_adapter.Update(_dataTable);
_connection.Close();
_adapter?.Dispose();
}
/// <summary>
/// Method to save pay pump enabled
/// </summary>
/// <param name="vData">Data</param>
public void SavePayPumpEnabled(bool vData)
{
string query = "select * from Variables where Name=\'PayPumpEnabled\'";
_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)
{
_dataTable.Rows[0]["Value"] = vData ? "1" : "0";
}
else
{
query = "select Max(VarIndex) as MaxIndex from Variables";
var rsTemp = GetRecords(query, DataSource.CSCMaster);
DataRow rsfields = _dataTable.NewRow();
rsfields["VarIndex"] = DBNull.Value.Equals(rsTemp.Rows[0]["MaxIndex"]) ? 0 : CommonUtility.GetIntergerValue(rsTemp.Rows[0]["MaxIndex"]) + 1;
rsfields["Name"] = "PayPumpEnabled";
rsfields["Value"] = vData ? "1" : "0";
rsfields["Description"] = "To keep the current PayPump status, 1-On/0-Off.";
_dataTable.Rows.Add(rsfields);
}
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.UpdateCommand = builder.GetUpdateCommand();
_adapter.Update(_dataTable);
_connection.Close();
_adapter?.Dispose();
}
/// <summary>
/// Method to save post pay set manually
/// </summary>
/// <param name="vData">Data</param>
public void Save_PostPaySetManually(bool vData)
{
var query = "SELECT * FROM Variables WHERE Name=\'PostPayManually\'";
_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)
{
_dataTable.Rows[0]["Value"] = vData ? "1" : "0";
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"] = "PostPayManually";
rsfields["Value"] = vData ? "1" : "0";
rsfields["Description"] = "PostPay status manually set from POS, 1-Yes/0-No";
_dataTable.Rows.Add(rsfields);
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.InsertCommand = builder.GetInsertCommand();
}
_adapter.Update(_dataTable);
_connection.Close();
_adapter?.Dispose();
}
/// <summary>
/// Update tax exempt card holder
/// </summary>
/// <param name="taxExemptCardHolder">tax exempt card holder</param>
public void UpdateTaxExemptCardHolder(teCardholder taxExemptCardHolder)
{
var dateStart = DateTime.Now;
_performancelog.Debug($"Start,TaxService,UpdateTaxExemptCardHolder,{string.Empty},{dateStart:hh.mm.ss.ffffff}");
var rsCardHolder = new DataTable();
_connection = new SqlConnection(GetConnectionString(DataSource.CSCMaster));
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
_adapter = new SqlDataAdapter("select * from TaxExemptCardRegistry Where CardholderID= \'" + taxExemptCardHolder.CardholderID + "\'", _connection);
_adapter.Fill(rsCardHolder);
if (rsCardHolder.Rows.Count > 0)
{
rsCardHolder.Rows[0]["TobaccoQuota"] = taxExemptCardHolder.TobaccoQuota;
rsCardHolder.Rows[0]["GasQuota"] = taxExemptCardHolder.GasQuota;
rsCardHolder.Rows[0]["PropaneQuota"] = taxExemptCardHolder.PropaneQuota;
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.UpdateCommand = builder.GetUpdateCommand();
_adapter.Update(rsCardHolder);
_connection.Close();
_adapter?.Dispose();
}
_performancelog.Debug($"End,TaxService,UpdateTaxExemptCardHolder,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}");
}
/// <summary>
/// Method to lock prepay
/// </summary>
/// <param name="pumpId">Pump id</param>
/// <returns>True or false</returns>
public bool LockPrepay(short pumpId)
{
var returnValue = false;
var sSql = "SELECT * FROM PrepayGlobal WHERE pumpID = " + Convert.ToString(pumpId);
_connection = new SqlConnection(GetConnectionString(DataSource.CSCTrans));
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
_dataTable = new DataTable();
_adapter = new SqlDataAdapter(sSql, _connection);
_adapter.Fill(_dataTable);
if (_dataTable.Rows.Count > 0)
{
_dataTable.Rows[0]["locked"] = true;
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.UpdateCommand = builder.GetUpdateCommand();
_adapter.Update(_dataTable);
returnValue = true;
}
_adapter?.Dispose();
_connection.Close();
return(returnValue);
}
protected override void Dispose(bool disposing)
{
SelectCommand?.Dispose();
Adapter?.Dispose();
GC.Collect();
base.Dispose(disposing);
}
/// <summary>
/// Method to update cash in till
/// </summary>
/// <param name="tillNumber">Till number</param>
/// <param name="amount">Amount</param>
/// <returns>Updated cash</returns>
public decimal UpdateCash(int tillNumber, decimal amount)
{
decimal updatedCash = 0;
var query = "Select * From Tills Where Tills.Till_Num = " + tillNumber;
_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)
{
updatedCash = CommonUtility.GetDecimalValue(_dataTable.Rows[0]["Cash"]) + amount;
_dataTable.Rows[0]["Cash"] = updatedCash;
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.UpdateCommand = builder.GetUpdateCommand();
_adapter.Update(_dataTable);
}
_connection.Close();
_adapter?.Dispose();
return(updatedCash);
}
/// <summary>
/// Update customer
/// </summary>
/// <param name="customer"></param>
public void UpdateCustomer(Customer customer)
{
var dateStart = DateTime.Now;
_performancelog.Debug($"Start,CustomerService,UpdateCustomer,{string.Empty},{DateTime.Now:hh.mm.ss.ffffff}");
_connection = new SqlConnection(GetConnectionString(DataSource.CSCMaster));
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
_dataTable = new DataTable();
_adapter = new SqlDataAdapter("SELECT * FROM Client " +
" WHERE Client.CL_Code = \'" + customer.Code +
"\'", _connection);
_adapter.Fill(_dataTable);
if (_dataTable.Rows.Count != 0)
{
_dataTable.Rows[0]["CL_CURBAL"] = customer.Current_Balance;
_dataTable.Rows[0]["LO_POINTS"] = customer.Loyalty_Points;
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.UpdateCommand = builder.GetUpdateCommand();
_adapter.Update(_dataTable);
_connection.Close();
_adapter?.Dispose();
}
_performancelog.Debug($"End,CustomerService,UpdateCustomer,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}");
}
/// <summary>
/// Method to add sale line kit
/// </summary>
/// <param name="lineKit"></param>
public void AddSaleLineKit(Line_Kit lineKit)
{
var dateStart = DateTime.Now;
_performancelog.Debug($"Start,SaleLineService,AddSaleLineKit,{string.Empty},{DateTime.Now:hh.mm.ss.ffffff}");
_connection = new SqlConnection(GetConnectionString(DataSource.CSCTills));
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
_dataTable = new DataTable();
_adapter = new SqlDataAdapter("SELECT * from SaleKit where TILL_NUM= " + lineKit.TillNumber + " and sale_no = " + lineKit.SaleNumber + " and Line_No = " + lineKit.LineNumber, _connection);
_adapter.Fill(_dataTable);
if (_dataTable.Rows.Count == 0)
{
var fields = _dataTable.NewRow();
fields["Till_Num"] = lineKit.TillNumber;
fields["sale_no"] = lineKit.SaleNumber;
fields["Line_No"] = lineKit.LineNumber;
fields["Kit_Item"] = lineKit.Kit_Item;
fields["Descript"] = lineKit.Kit_Item_Desc;
fields["Quantity"] = lineKit.Kit_Item_Qty;
fields["Base"] = lineKit.Kit_Item_Base;
fields["Fraction"] = lineKit.Kit_Item_Fraction;
fields["Alloc"] = lineKit.Kit_Item_Allocate;
fields["Serial"] = lineKit.Kit_Item_Serial;
_dataTable.Rows.Add(fields);
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.InsertCommand = builder.GetInsertCommand();
_adapter.Update(_dataTable);
_connection.Close();
_adapter?.Dispose();
_performancelog.Debug($"End,SaleLineService,AddSaleLineKit,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}");
}
}
/// <summary>
/// Method to add drop header
/// </summary>
/// <param name="dropHeader">Drop header</param>
public void AddDropHeader(DropHeader dropHeader)
{
_connection = new SqlConnection(GetConnectionString(DataSource.CSCTills));
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
_dataTable = new DataTable();
_adapter = new SqlDataAdapter("select * from DropHeader", _connection);
_adapter.Fill(_dataTable);
var fields = _dataTable.NewRow();
fields["DropDate"] = dropHeader.DropDate;
fields["User"] = dropHeader.UserCode;
fields["Till_Num"] = dropHeader.TillNumber;
fields["DropCount"] = dropHeader.DropCount;
fields["shiftid"] = dropHeader.ShiftId;
fields["ShiftDate"] = dropHeader.ShiftDate;
if (!string.IsNullOrEmpty(dropHeader.EnvelopeNo))
{
fields["EnvelopeNo"] = dropHeader.EnvelopeNo;
}
fields["ReasonCode"] = dropHeader.ReasonCode;
fields["DropID"] = dropHeader.DropId;
_dataTable.Rows.Add(fields);
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.InsertCommand = builder.GetInsertCommand();
_adapter.Update(_dataTable);
_connection.Close();
_adapter?.Dispose();
}
/// <summary>
/// 异步的执行T-SQL命令，并返回一个数据表。如果执行失败返回null
/// </summary>
/// <param name="cmdText">T-SQL命令</param>
/// <param name="tableName">数据表名称</param>
/// <param name="parameters">T-SQL命令参数</param>
public static async Task <DataTable> GetDataTableAsync(string cmdText, string tableName, SqlParameter[] parameters = null)
{
return(await Task.Run <DataTable>(() =>
{
SqlConnection sqlConnection = null;
SqlCommand sqlCommand = null;
SqlDataAdapter sqlDataAdapter = null;
try
{
sqlConnection = new SqlConnection(SQLServerString);
sqlCommand = new SqlCommand(cmdText, sqlConnection);
sqlDataAdapter = new SqlDataAdapter(sqlCommand);
if (parameters != null)
{
sqlCommand.Parameters.AddRange(parameters);
}
DataTable dataTable = new DataTable(tableName);
sqlDataAdapter.Fill(dataTable);
return dataTable;
}
catch (Exception ex)
{
WriteErrorInfo(ex);
return null;
}
finally
{
sqlConnection?.Dispose();
sqlCommand?.Dispose();
sqlDataAdapter?.Dispose();
}
}));
}
/// <summary>
/// Method to add sale vendor coupon line
/// </summary>
/// <param name="oLine">Sale vendor coupon line</param>
/// <param name="saleNumber">Sale number</param>
/// <param name="tillNumber">Till number</param>
public void AddSaleVendorCouponLine(SaleVendorCouponLine oLine, int saleNumber, int tillNumber)
{
_connection = new SqlConnection(GetConnectionString(DataSource.CSCCurSale));
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
_dataTable = new DataTable();
_adapter = new SqlDataAdapter("select * from SaleVendorCoupon where TILL_NUM=" + tillNumber
+ " AND SALE_NO=" + Convert.ToString(saleNumber) + " AND LINE_NUM="
+ Convert.ToString(oLine.Line_Num) + "AND SeqNumber=" + Convert.ToString(oLine.SeqNum)
, _connection);
_adapter.Fill(_dataTable);
if (_dataTable.Rows.Count == 0)
{
var fields = _dataTable.NewRow();
fields["Till_Num"] = tillNumber;
fields["Sale_No"] = saleNumber;
fields["Line_Num"] = oLine.Line_Num;
fields["CouponCode"] = oLine.CouponCode;
fields["CouponName"] = oLine.CouponName;
fields["UnitValue"] = oLine.UnitValue;
fields["Quantity"] = oLine.Quantity;
fields["TotalValue"] = oLine.TotalValue;
fields["SerialNumber"] = oLine.SerialNumber;
fields["SeqNumber"] = oLine.SeqNum;
fields["TendDesc"] = oLine.TendDesc;
_dataTable.Rows.Add(fields);
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.InsertCommand = builder.GetInsertCommand();
_adapter.Update(_dataTable);
_connection.Close();
_adapter?.Dispose();
}
}
/// <summary>
/// Save Signature
/// </summary>
/// <param name="tillNumber">Till number</param>
/// <param name="saleNumber">Sale number</param>
/// <param name="imageFilePath">Image file path</param>
/// <returns>True or false</returns>
public bool SaveSignature(int tillNumber, int saleNumber, string imageFilePath)
{
bool returnValue = false;
_connection = new SqlConnection(GetConnectionString(DataSource.CSCTills));
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
_dataTable = new DataTable();
_adapter = new SqlDataAdapter("Select * from Signature where Sale_No = " + Convert.ToString(saleNumber) + " and Till_num = " + Convert.ToString(tillNumber), _connection);
_adapter.Fill(_dataTable);
if (_dataTable.Rows.Count == 0)
{
var rsFields = _dataTable.NewRow();
if (SavePictureToDb(rsFields, imageFilePath))
{
rsFields["Sale_No"] = saleNumber;
rsFields["Till_Num"] = tillNumber;
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_dataTable.Rows.Add(rsFields);
_adapter.UpdateCommand = builder.GetUpdateCommand();
_adapter.Update(_dataTable);
_connection.Close();
_adapter?.Dispose();
returnValue = true;
}
}
return(returnValue);
}
/// <summary>
/// Method to set close batch number
/// </summary>
/// <param name="cc">Credit card</param>
public void SetCloseBatchNumber(Credit_Card cc)
{
//Save to the CloseBatch table
_connection = new SqlConnection(GetConnectionString(DataSource.CSCTrans));
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
_dataTable = new DataTable();
_adapter = new SqlDataAdapter("select * from CloseBatch", _connection);
_adapter.Fill(_dataTable);
var fields = _dataTable.NewRow();
fields["BatchNumber"] = cc.Sequence_Number;
fields["TerminalID"] = cc.TerminalID;
fields["BatchDate"] = cc.Trans_Date;
fields["BatchTime"] = cc.Trans_Time;
fields["Report"] = cc.Report;
_dataTable.Rows.Add(fields);
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.InsertCommand = builder.GetInsertCommand();
_adapter.Update(_dataTable);
_connection.Close();
_adapter?.Dispose();
//Update all the Tills and Trans for this TerminalID
UpdateTables(cc, DataSource.CSCTills);
UpdateTables(cc, DataSource.CSCTrans);
}
/// <summary>
/// Method to add a sale line reason
/// </summary>
/// <param name="reason">Sale line reason</param>
public void AddSaleLineReason(SaleLineReason reason)
{
var dateStart = DateTime.Now;
_performancelog.Debug($"Start,SaleLineService,AddSaleLineReason,{string.Empty},{DateTime.Now:hh.mm.ss.ffffff}");
_connection = new SqlConnection(GetConnectionString(DataSource.CSCTills));
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
_dataTable = new DataTable();
_adapter = new SqlDataAdapter("SELECT * from SLineReason where TILL_NUM= " + reason.TillNumber +
" and sale_no = " + reason.SaleNumber +
" and Line_Num = " + reason.LineNumber +
" and Reason_Type = " + reason.ReasonType, _connection);
_adapter.Fill(_dataTable);
if (_dataTable.Rows.Count == 0)
{
var fields = _dataTable.NewRow();
fields["Till_Num"] = reason.TillNumber;
fields["sale_no"] = reason.SaleNumber;
fields["Line_Num"] = reason.LineNumber;
fields["Reason"] = reason.Reason;
fields["Reason_Type"] = reason.ReasonType;
_dataTable.Rows.Add(fields);
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.InsertCommand = builder.GetInsertCommand();
_adapter.Update(_dataTable);
_connection.Close();
_adapter?.Dispose();
_performancelog.Debug($"End,SaleLineService,AddSaleLineReason,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}");
}
}
/// <summary>
/// Method to add drop line
/// </summary>
/// <param name="dropLine">Drop line</param>
public void AddDropLine(DropLine dropLine)
{
_connection = new SqlConnection(GetConnectionString(DataSource.CSCTills));
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
_dataTable = new DataTable();
_adapter = new SqlDataAdapter("select * from DropLines", _connection);
_adapter.Fill(_dataTable);
var fields = _dataTable.NewRow();
fields["Till_Num"] = dropLine.TillNumber;
fields["DropDate"] = dropLine.DropDate;
fields["Tender_Name"] = dropLine.TenderName;
fields["Exchange_Rate"] = dropLine.ExchangeRate;
fields["Amount"] = dropLine.Amount;
fields["Conv_Amount"] = dropLine.ConvAmount;
fields["DropID"] = dropLine.DropID;
_dataTable.Rows.Add(fields);
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.InsertCommand = builder.GetInsertCommand();
_adapter.Update(_dataTable);
_connection.Close();
_adapter?.Dispose();
}
/// <summary>
/// Global execute query.
/// </summary>
/// <param name="passKey">The pass key.</param>
/// <param name="username">The username.</param>
/// <param name="procName">Name of the proc.</param>
/// <param name="paramList">The parameter list.</param>
/// <returns>DataSet.</returns>
public DataSet ws_GlobalExecuteQuery(string passKey, string username, string procName, SPParam[] paramList)
{
if (!ValidationAndEncryptDecrypt.ValidateKey(passKey))
{
return(null);
}
DataSet dsCheck = SpParamHelpers.CheckSpParams(procName, "", paramList);
if (dsCheck != null)
{
return(dsCheck);
}
SqlConnection dbConn = null;
SqlCommand sqlCmd = null;
SqlDataAdapter sqlAdapter = null;
DataSet ds = null;
try
{
dbConn = GetOpenSqlConnection("");
sqlCmd = new SqlCommand(procName, dbConn)
{
CommandType = CommandType.StoredProcedure, CommandTimeout = 0
};
foreach (SPParam p in paramList)
{
SqlParameter param = sqlCmd.Parameters.AddWithValue(p.Name, p.Value);
if (p.Value == null)
{
param.IsNullable = true;
param.Value = DBNull.Value;
}
}
ds = new DataSet();
sqlAdapter = new SqlDataAdapter {
SelectCommand = sqlCmd
};
sqlAdapter.Fill(ds);
return(ds);
//}
}
catch (Exception e)
{
WriteEventLogEntry(e, procName, paramList);
return(HandleExceptionHelper.HandleSqlException(e, procName, paramList));
}
finally
{
ds?.Dispose();
sqlAdapter?.Dispose();
sqlCmd?.Dispose();
CloseConnection(dbConn);
}
}
protected override void Dispose(bool disposing)
{
if (disposing)
{
_connection?.Dispose();
_adapter?.Dispose();
}
}
/// <summary>
/// Method to save sale vendor coupon service
/// </summary>
/// <param name="sv">Sale vendor coupon</param>
/// <param name="tillNumber">Till number</param>
/// <param name="dataSource">Data source</param>
public void SaveSaleVendorCoupon(SaleVendorCoupon sv, int tillNumber, DataSource dataSource)
{
_connection = new SqlConnection(GetConnectionString(dataSource));
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
foreach (SaleVendorCouponLine tempLoopVarSvc in sv.SVC_Lines)
{
var svc = tempLoopVarSvc;
bool addNew = false;
_dataTable = new DataTable();
_adapter = new SqlDataAdapter("select * from SaleVendorCoupon where TILL_NUM="
+ tillNumber + " AND SALE_NO=" + Convert.ToString(sv.Sale_Num)
+ " AND LINE_NUM=" + Convert.ToString(svc.Line_Num) + " AND SeqNumber="
+ Convert.ToString(svc.SeqNum), _connection);
_adapter.Fill(_dataTable);
DataRow fields;
if (_dataTable.Rows.Count != 0)
{
addNew = true;
fields = _dataTable.NewRow();
fields["Till_Num"] = tillNumber;
fields["Sale_No"] = sv.Sale_Num;
fields["Line_Num"] = svc.Line_Num;
fields["SeqNumber"] = svc.SeqNum;
}
else
{
fields = _dataTable.Rows[0];
}
fields["CouponCode"] = svc.CouponCode;
fields["CouponName"] = svc.CouponName;
fields["UnitValue"] = svc.UnitValue;
fields["Quantity"] = svc.Quantity;
fields["TotalValue"] = svc.TotalValue;
fields["SerialNumber"] = svc.SerialNumber;
fields["TendDesc"] = svc.TendDesc;
if (addNew)
{
_dataTable.Rows.Add(fields);
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.InsertCommand = builder.GetInsertCommand();
}
else
{
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.UpdateCommand = builder.GetUpdateCommand();
}
_adapter.Update(_dataTable);
_connection.Close();
_adapter?.Dispose();
}
}
/// <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 add a sale line to current sale
/// </summary>
/// <param name="saleLine">Sale line</param>
public void AddSaleLineToDbTill(Sale_Line saleLine)
{
var dateStart = DateTime.Now;
_performancelog.Debug($"Start,SaleLineService,AddSaleLineToDbTill,{string.Empty},{DateTime.Now:hh.mm.ss.ffffff}");
_connection = new SqlConnection(GetConnectionString(DataSource.CSCTills));
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
_dataTable = new DataTable();
_adapter = new SqlDataAdapter("SELECT * from SALELINE where TILL_NUM=" + saleLine.Till_Num +
" and SALE_NO = " + saleLine.Sale_Num +
" and Line_Num = " + saleLine.Line_Num, _connection);
_adapter.Fill(_dataTable);
if (_dataTable.Rows.Count == 0)
{
var fields = _dataTable.NewRow();
fields["PLU_Code"] = saleLine.PLU_Code;
fields["price"] = saleLine.price;
fields["SALE_NO"] = saleLine.Sale_Num;
fields["TILL_NUM"] = saleLine.Till_Num;
fields["Quantity"] = saleLine.Quantity;
fields["Line_Num"] = saleLine.Line_Num;
fields["Stock_Code"] = saleLine.Stock_Code;
fields["DiscountName"] = saleLine.DiscountName;
fields["Disc_Type"] = saleLine.Discount_Type;
fields["Disc_Adj"] = saleLine.Discount_Adjust;
fields["Disc_Code"] = saleLine.Discount_Code;
fields["Disc_Rate"] = saleLine.Discount_Rate;
fields["Amount"] = saleLine.Amount;
fields["PumpID"] = saleLine.pumpID;
fields["PositionID"] = saleLine.PositionID;
fields["GradeID"] = saleLine.GradeID;
fields["Gift_Cert"] = saleLine.Gift_Certificate;
fields["PromoID"] = saleLine.PromoID;
fields["Prepay"] = saleLine.Prepay;
fields["SERIAL_NO"] = saleLine.Serial_No;
fields["PaidByCard"] = saleLine.PaidByCard;
fields["Upsell"] = saleLine.Upsell;
fields["TaxExempt"] = saleLine.TaxForTaxExempt;
fields["TOTAL_AMT"] = saleLine.Total_Amount;
fields["Rebate"] = saleLine.Rebate;
fields["ScalableItem"] = saleLine.ScalableItem;
fields["CardProfileID"] = saleLine.CardProfileID;
_dataTable.Rows.Add(fields);
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.InsertCommand = builder.GetInsertCommand();
_adapter.Update(_dataTable);
_connection.Close();
_adapter?.Dispose();
}
_performancelog.Debug($"End,SaleLineService,AddSaleLineToDbTill,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}");
}
/// <summary>
/// Get Records
/// </summary>
/// <param name="query">Query</param>
/// <param name="source">Source</param>
/// <returns>DataTable</returns>
public DataTable GetRecords(string query, DataSource source)
{
_connection = new SqlConnection(GetConnectionString(source));
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
_dataTable = new DataTable();
_adapter = new SqlDataAdapter(query, _connection);
_adapter.Fill(_dataTable);
_connection.Close();
_adapter?.Dispose();
return(_dataTable);
}
/// <summary>
/// Set up Taxes
/// </summary>
/// <param name="stockCode">Stock code</param>
/// <param name="taxNames">Tax name</param>
/// <param name="selectedTaxes">Selected taxes</param>
/// <returns>True or false</returns>
public bool SetupTaxes(string stockCode, List <string> taxNames, List <string> selectedTaxes)
{
var dateStart = DateTime.Now;
_performancelog.Debug($"Start,TaxService,SetupTaxes,{string.Empty},{dateStart:hh.mm.ss.ffffff}");
short i;
var returnValue = true;
var taxes = GetAllActiveTaxes();
for (i = 0; i < taxNames.Count; i++)
{
if (selectedTaxes.Contains(taxNames[i]))
{
var taxName = taxes[i].Name;
var taxCode = taxes[i].Code;
_connection = new SqlConnection(GetConnectionString(DataSource.CSCMaster));
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
_dataTable = new DataTable();
_adapter = new SqlDataAdapter("SELECT * FROM StockTax where Stock_Code = \'" + stockCode + "\'and Tax_name = \'" + taxName + "\'", _connection);
_adapter.Fill(_dataTable);
if (_dataTable.Rows.Count > 0)
{
returnValue = false;
}
else
{
DataRow row = _dataTable.NewRow();
row["Stock_Code"] = stockCode;
row["Tax_Name"] = taxName;
row["Tax_Code"] = taxCode;
_dataTable.Rows.Add(row);
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.InsertCommand = builder.GetInsertCommand();
_adapter.Update(_dataTable);
_connection.Close();
_adapter?.Dispose();
}
}
}
_performancelog.Debug($"End,TaxService,SetupTaxes,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}");
return(returnValue);
}
/// <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 Paged Records
/// </summary>
/// <param name="query">Query</param>
/// <param name="source">Data source</param>
/// <param name="pageIndex">Page index</param>
/// <param name="pageSize">Page size</param>
/// <returns>Data table</returns>
public DataTable GetPagedRecords(string query,
DataSource source,
int pageIndex,
int pageSize = 100)
{
_connection = new SqlConnection(GetConnectionString(source));
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
_dataTable = new DataTable();
_adapter = new SqlDataAdapter(query, _connection);
_adapter.Fill((pageIndex - 1) * pageSize, pageSize, _dataTable);
_connection.Close();
_adapter?.Dispose();
return(_dataTable);
}
/// <summary>
/// Method to save password in database
/// </summary>
/// <param name="strPassword"></param>
public void SavePassword(string strPassword)
{
string strEncryptPassword = "";
var query = "select * from Variables where Name=\'EncryptPassword\'";
bool addNew = false;
DataRow fields;
_connection = new SqlConnection(GetConnectionString(DataSource.CSCTills));
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
_dataTable = new DataTable();
_adapter = new SqlDataAdapter(query, _connection);
_adapter.Fill(_dataTable);
if (_dataTable.Rows.Count == 0)
{
fields = _dataTable.NewRow();
addNew = true;
var rsTmp = GetRecords("select Max(VarIndex) as MaxIndex from Variables", DataSource.CSCMaster);
fields["VarIndex"] = Convert.ToInt32(DBNull.Value.Equals(rsTmp.Rows[0]["MaxIndex"]) ? 0 : rsTmp.Rows[0]["MaxIndex"]) + 1;
fields["Name"] = "EncryptPassword";
fields["Description"] = "To keep Encrypted Password.";
}
else
{
fields = _dataTable.Rows[0];
}
fields["Value"] = strEncryptPassword;
if (addNew)
{
_dataTable.Rows.Add(fields);
var builder = new SqlCommandBuilder(_adapter);
_adapter.InsertCommand = builder.GetInsertCommand();
_adapter.Update(_dataTable);
}
else
{
var builder = new SqlCommandBuilder(_adapter);
_adapter.UpdateCommand = builder.GetUpdateCommand();
_adapter.Update(_dataTable);
}
_connection.Close();
_adapter?.Dispose();
}
/// <summary>
/// Add or Update Treaty Name
/// </summary>
/// <param name="treatyNumber">Treaty number</param>
/// <param name="treatyName">Treaty name</param>
/// <returns>Treaty name</returns>
public string AddorUpdateTreatyName(string treatyNumber, string treatyName)
{
if (!string.IsNullOrEmpty(treatyNumber))
{
return(null);
}
var returnValue = treatyName;
var query = "SELECT * FROM TreatyNo WHERE TreatyNo=\'" + treatyNumber + "\'";
_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]["TreatyName"]))
{
if (CommonUtility.GetStringValue(_dataTable.Rows[0]["TreatyName"]).Length <= 0)
{
return(returnValue);
}
var trtyName = CommonUtility.GetStringValue(_dataTable.Rows[0]["TreatyName"]);
if (treatyName.Trim().Length == 0)
{
returnValue = Convert.ToString(_dataTable.Rows[0]["TreatyName"]);
}
else if (!treatyName.Equals(trtyName))
{
_dataTable.Rows[0]["TreatyName"] = treatyName.Trim();
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.UpdateCommand = builder.GetUpdateCommand();
_adapter.Update(_dataTable);
_connection.Close();
_adapter?.Dispose();
}
}
}
return(returnValue);
}
/// <summary>
/// Method to set prepay from POS
/// </summary>
/// <param name="invoiceId">Invoice id</param>
/// <param name="pumpId">Pump Id</param>
/// <param name="amount">Amount</param>
/// <param name="mop">MOP</param>
/// <param name="positionId">Position Id</param>
/// <param name="tillNumber">Till number</param>
/// <returns>True or false</returns>
public bool SetPrepaymentFromPos(int invoiceId, short pumpId, float amount,
byte mop, byte positionId, int tillNumber)
{
var sSql = "SELECT * FROM PrepayGlobal WHERE pumpID = " + Convert.ToString(pumpId);
_connection = new SqlConnection(GetConnectionString(DataSource.CSCTrans));
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
_dataTable = new DataTable();
_adapter = new SqlDataAdapter(sSql, _connection);
_adapter.Fill(_dataTable);
if (_dataTable.Rows.Count == 0)
{
DataRow dataRow = _dataTable.NewRow();
dataRow["pumpID"] = pumpId;
dataRow["InvoiceID"] = invoiceId;
dataRow["MOP"] = mop;
dataRow["TillID"] = tillNumber;
dataRow["Amount"] = amount.ToString("###0.00");
dataRow["locked"] = false;
dataRow["PositionID"] = positionId;
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.InsertCommand = builder.GetInsertCommand();
}
else
{
_dataTable.Rows[0]["InvoiceID"] = invoiceId;
_dataTable.Rows[0]["MOP"] = mop;
_dataTable.Rows[0]["TillID"] = tillNumber;
_dataTable.Rows[0]["Amount"] = amount.ToString("###0.00");
_dataTable.Rows[0]["locked"] = false;
_dataTable.Rows[0]["PositionID"] = positionId;
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.UpdateCommand = builder.GetUpdateCommand();
}
_adapter.Update(_dataTable);
_adapter?.Dispose();
_connection.Close();
return(true);
}
/// <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>
/// Clear the non active Tills
/// </summary>
/// <param name="active">Active</param>
/// <param name="tillNumber">Till number</param>
public void ClearNonActiveTill(int active, string tillNumber)
{
var dateStart = DateTime.Now;
_performancelog.Debug($"Start,TillService,ClearNonActiveTill,{string.Empty},{DateTime.Now:hh.mm.ss.ffffff}");
var query = "select * from tills where active = " + active + " and Till_Num <> " + tillNumber + " ";
_connection = new SqlConnection(GetConnectionString(DataSource.CSCMaster));
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
_dataTable = new DataTable();
_adapter = new SqlDataAdapter(query, _connection);
_adapter.Fill(_dataTable);
foreach (DataRow row in _dataTable.Rows)
{
row["Float"] = 0;
row["CashBonusFloat"] = 0;
row["Cash"] = 0;
row["Date_Open"] = DBNull.Value;
row["Time_Open"] = DBNull.Value;
row["ShiftDate"] = DBNull.Value;
row["ShiftNumber"] = DBNull.Value;
row["posID"] = DBNull.Value;
row["UserLoggedOn"] = DBNull.Value;
}
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.UpdateCommand = builder.GetUpdateCommand();
_adapter.Update(_dataTable);
_connection.Close();
_adapter?.Dispose();
_performancelog.Debug($"End,TillService,ClearNonActiveTill,{DateTime.Now.Subtract(dateStart).TotalMilliseconds},{DateTime.Now:hh.mm.ss.ffffff}");
}
