public bool saveReceipt(clsCashBox CashBox)
{
bool bRet = true;
clsSqlFactory hSql = new clsSqlFactory();
clsGlobalVariable objGlobal = new clsGlobalVariable();
try
{
_log.Debug("saveReceipt >> SmanId = " + objGlobal.DefaultSManID);
List <int> nVoucherIds = new List <int>();
List <int> nReceiptNos = new List <int>();
List <decimal> nReceiptTotals = new List <decimal>();
int i = 0;
int nNuseId = 0;
int nReceiptNo = 0;
bool bNeedRounding = false;
decPaymentSum = getRowsTotal();
while (i < Rows.Count)
{
DataRow rowPaymentType = CashBox.CashTypes.Select("TYPE = '" + Rows[i].PaymentType.Code + "'")[0];
if (rowPaymentType != null)
{
if (CashBox.getSpecRound(rowPaymentType["TYPE"].ToString()) > 0)
{
bNeedRounding = true;
}
nNuseId = CashBox.getVoucherIdOfType(Rows[i].PaymentType.Code, Rows[0].Amount > 0 ? false : true);
if (nVoucherIds.IndexOf(nNuseId) < 0)
{
nVoucherIds.Add(nNuseId);
nReceiptNo = getReceiptNUSE(hSql, nNuseId);
nReceiptNos.Add(nReceiptNo);
nReceiptTotals.Add(0);
// insert header
_log.Debug("receiptno: " + nReceiptNo.ToString());
hSql.NewCommand("insert into " + objUtil.getTable("CASHTRANSH") + "(RECEIPTNO,CASHBOXID,DEPARTMENT,SMANID,UNIT,MODULE," +
"INVOICECATEGORY,CUSTNO,LICNO,AMOUNTTOPAY,TOTPAID,CASHRETURNED," +
"TEXT,INSERTPC,UPDATED_DATE,UPDATED_BY,EXT_BILLD,EXT_ORDERNO,EXT_INVOICENO,INVOICENO,ORDERNO) " +
" values(?,?,?,?,?,?," +
"'1',?,?,?,?,0," +
"?,?,getdate(),?,?,?,?,?,?)");
hSql.Com.Parameters.AddWithValue("RECEIPTNO", nReceiptNo);
hSql.Com.Parameters.AddWithValue("CASHBOXID", Rows[i].CashBoxId);
if (strDeptId != "")
{
_log.Debug("Department = " + strDeptId);
hSql.Com.Parameters.AddWithValue("DEPARTMENT", strDeptId);
}
else
{
String strDefDeptId = objConfig.getStringParam("CASHREG", "DEFDEPTID", "C3", "").Trim();
if (strDefDeptId == "")
{
strDefDeptId = ConfigurationManager.AppSettings["DefDeptId"].ToString();
}
hSql.Com.Parameters.AddWithValue("DEPARTMENT", strDefDeptId);
_log.Debug("Nor department found from invoices, use default department " + strDeptId);
}
hSql.Com.Parameters.AddWithValue("SMANID", objGlobal.DefaultSManID);
hSql.Com.Parameters.AddWithValue("UNITID", objGlobal.CurrentSiteId);
hSql.Com.Parameters.AddWithValue("MODULE", strInvoiceModule);
hSql.Com.Parameters.AddWithValue("CUSTNO", strCustNo);
hSql.Com.Parameters.AddWithValue("LICNO", strLicno);
hSql.Com.Parameters.AddWithValue("AMOUNTTOPAY", decInvoiceSum);
hSql.Com.Parameters.AddWithValue("TOTPAID", 0);
hSql.Com.Parameters.AddWithValue("TEXT", strRemark);
hSql.Com.Parameters.AddWithValue("INSERTEDPC", CashBox.getClientPCName());
hSql.Com.Parameters.AddWithValue("UPDATED_BY", objGlobal.DMSFirstUserName);
hSql.Com.Parameters.AddWithValue("EXT_BILLD", dtInvoiceDate);
hSql.Com.Parameters.AddWithValue("EXT_ORDNO", strOrderNo);
hSql.Com.Parameters.AddWithValue("EXT_INVOICENO", strInvoiceNo);
if (InvoiceFlag != InvoiceFlags.CashRegisterFlag)
{
hSql.Com.Parameters.AddWithValue("INVOICENO", strInvoiceNo);
hSql.Com.Parameters.AddWithValue("ORDERNO", strOrderNo);
}
else
{
hSql.Com.Parameters.AddWithValue("INVOICENO", DBNull.Value);
hSql.Com.Parameters.AddWithValue("ORDERNO", DBNull.Value);
}
hSql.ExecuteNonQuery();
}
else
{
nReceiptNo = nReceiptNos[nVoucherIds.IndexOf(nNuseId)];
}
if (nReceiptNo > 0)
{
hSql.NewCommand("insert into " + objUtil.getTable("CASHTRANSR") + "(RECEIPTNO,ROWNO,CASHFORM,CASHTYPE,QTY,AMOUNT,SALETYPE,FEE,CC_TERMINALID) " +
" values(?,?,?,?,?,?,?,?,?)");
hSql.Com.Parameters.AddWithValue("RECEIPTNO", nReceiptNo);
hSql.Com.Parameters.AddWithValue("ROWNO", i + 1);
hSql.Com.Parameters.AddWithValue("CASHFORM", Rows[i].PaymentForm.Code);
hSql.Com.Parameters.AddWithValue("CASHTYPE", Rows[i].PaymentType.Code);
hSql.Com.Parameters.AddWithValue("QTY", Rows[i].Quantity);
hSql.Com.Parameters.AddWithValue("AMOUNT", Rows[i].Amount);
hSql.Com.Parameters.AddWithValue("SALETYPE", Rows[i].SaleType);
hSql.Com.Parameters.AddWithValue("FEE", Rows[i].AmountFee);
hSql.Com.Parameters.AddWithValue("CC_TERMINALID", Rows[i].CardType.Code);
hSql.ExecuteNonQuery();
Rows[i].Receiptno = nReceiptNo;
nReceiptTotals[nReceiptNos.IndexOf(nReceiptNo)] += Rows[i].Amount;
}
else
{
_log.Error("Fail to generate receipt number");
Exception ex = new Exception("Fail to generate receipt number, check the voucher id settings !");
throw ex;
}
}
else
{
_log.Error("PaymentType " + Rows[i].PaymentType.Code + " not found in the valid CashTypes !");
}
i++;
}
i = 0;
while (i < nReceiptNos.Count)
{
hSql.NewCommand("update " + objUtil.getTable("CASHTRANSH") + " set TOTPAID =? where RECEIPTNO=? ");
hSql.Com.Parameters.AddWithValue("TOTPAID", nReceiptTotals[i]);
hSql.Com.Parameters.AddWithValue("RECEIPTNO", nReceiptNos[i]);
hSql.ExecuteNonQuery();
i++;
}
_log.Debug(strInvoiceModule);
if ((strInvoiceModule == "VA") || (strInvoiceModule == "SP") || (strInvoiceModule == "WO"))
{
hSql.NewCommand("update TEMPINV set PAYSUM=isnull(PAYSUM,0) + ? where RECNO=? and UNITID = ?");
hSql.Com.Parameters.AddWithValue("PAYSUM", decPaymentSum);
hSql.Com.Parameters.AddWithValue("RECNO", strInvoiceNo);
hSql.Com.Parameters.AddWithValue("UNITID", objGlobal.CurrentSiteId);
hSql.ExecuteNonQuery();
//
if (bNeedRounding)
{
hSql.NewCommand("update TEMPINV set PAYDATE=getdate() where RECNO=? and PAYDATE is null and abs(PAYSUM-INVSUM) < 3 and UNITID = ?");
}
else
{
hSql.NewCommand("update TEMPINV set PAYDATE=getdate() where RECNO=? and PAYDATE is null and abs(PAYSUM-INVSUM) = 0 and UNITID = ?");
}
hSql.Com.Parameters.AddWithValue("RECNO", strInvoiceNo);
hSql.Com.Parameters.AddWithValue("UNITID", objGlobal.CurrentSiteId);
hSql.ExecuteNonQuery();
string strSql = "";
if (strInvoiceModule == "VA")
{
strSql = "update a set a.PAYD=b.PAYDATE, a.PAIDSUM=b.PAYSUM from " + objUtil.getTable("CBIL") + " a, TEMPINV b where a.CRECNO=b.RECNO " +
//" and a.PAYD is null and b.PAYDATE is not null "+
" and b.RECNO=? and b.UNITID = ?";
}
if (strInvoiceModule == "SP")
{
strSql = "update a set a.PAIDDATE=b.PAYDATE,a.PAIDSUM=b.PAYSUM from " + objUtil.getTable("SBIL") + " a, TEMPINV b where a.SRECNO=b.RECNO " +
//"and a.PAIDDATE is null and b.PAYDATE is not null "+
" and b.RECNO=? and b.UNITID = ?";
}
if (strInvoiceModule == "WO")
{
strSql = "update a set a.PAIDDATE=b.PAYDATE,a.PAIDSUM=b.PAYSUM from " + objUtil.getTable("GBIL") + " a, TEMPINV b where a.GRECNO=b.RECNO " +
//" and a.PAIDDATE is null and b.PAYDATE is not null"+
" and b.RECNO=? and b.UNITID = ?";
}
hSql.NewCommand(strSql);
hSql.Com.Parameters.AddWithValue("RECNO", strInvoiceNo);
hSql.Com.Parameters.AddWithValue("UNITID", objGlobal.CurrentSiteId);
hSql.ExecuteNonQuery();
}
hSql.Commit();
}
catch (Exception ex)
{
_log.Error(ex.ToString());
bRet = false;
hSql.Rollback();
throw ex;
}
finally {
hSql.Close();
}
return(bRet);
}
public bool undoPayment(clsCashBox CashBox)
{
_log.Debug("undoPayment >> " + ReceiptNo.ToString());
bool bRet = true;
clsSqlFactory hSql = new clsSqlFactory();
clsGlobalVariable objGlobal = new clsGlobalVariable();
try
{
DataRow invoiceCashbox = CashBox.CashBoxes.Select(("CASHBOXID = '" + strCashBoxId + "'"))[0];
if (invoiceCashbox["ISOPEN"].ToString() == "1" && invoiceCashbox["PC"].ToString() == CashBox.getClientPCName() && nCreditNewNo == 0 && nCreditNote == 0 && nCrediOfNo == 0 && Rows.Count > 0)
{
//Get new Receiptno
int nNuseId = CashBox.getVoucherIdOfType(Rows[0].PaymentType.Code, Rows[0].Amount > 0 ? true : false);
//Insert new TRANSH row
if (nNuseId > 0)
{
int nNewReceiptNo = getReceiptNUSE(hSql, nNuseId);
_log.Debug("New credit receiptno: " + nNewReceiptNo.ToString());
hSql.NewCommand("insert into " + objUtil.getTable("CASHTRANSH") + "(CASHBOXID,DEPARTMENT,SMANID,UNIT,MODULE," +
"INVOICECATEGORY,CUSTNO,AMOUNTTOPAY,TOTPAID,CASHRETURNED," +
"EXT_BILLD,EXT_ORDERNO,EXT_INVOICENO,INVOICENO,ORDERNO,LICNO, " +
"UPDATED_DATE,TEXT,INSERTPC,UPDATED_BY, RECEIPTNO,CREDITNOTE,CREDITOFNO )" +
" select a.CASHBOXID,a.DEPARTMENT,a.SMANID,a.UNIT,a.MODULE," +
"a.INVOICECATEGORY,a.CUSTNO,(-1)*a.AMOUNTTOPAY,(-1)*a.TOTPAID,(-1)*a.CASHRETURNED," +
"a.EXT_BILLD,a.EXT_ORDERNO,a.EXT_INVOICENO,a.INVOICENO,a.ORDERNO,a.LICNO," +
"getdate(),?,?,?,?,?,? " +
" from " + objUtil.getTable("CASHTRANSH") + " a where a.RECEIPTNO = ?");
hSql.Com.Parameters.AddWithValue("TEXT", "");
hSql.Com.Parameters.AddWithValue("INSERTEDPC", CashBox.getClientPCName());
hSql.Com.Parameters.AddWithValue("UPDATED_BY", objGlobal.DMSFirstUserName);
hSql.Com.Parameters.AddWithValue("RECEIPTNO", nNewReceiptNo);
hSql.Com.Parameters.AddWithValue("CREDITNOTE", 1);
hSql.Com.Parameters.AddWithValue("CREDITOFNO", ReceiptNo);
hSql.Com.Parameters.AddWithValue("RECEIPTNO", ReceiptNo);
bRet = bRet && hSql.ExecuteNonQuery();
//Update current TRANSH row
hSql.NewCommand("update " + objUtil.getTable("CASHTRANSH") + " set CREDITNEWNO = ? where RECEIPTNO =? ");
hSql.Com.Parameters.AddWithValue("CREDITNEWNO", nNewReceiptNo);
hSql.Com.Parameters.AddWithValue("RECEIPTNO", ReceiptNo);
bRet = bRet && hSql.ExecuteNonQuery();
nCreditNewNo = nNewReceiptNo;
//Get the credited amount
Decimal nCreditedPaidAmount = 0;
hSql.NewCommand("select a.TOTPAID from " + objUtil.getTable("CASHTRANSH") + " a where a.RECEIPTNO = ?");
hSql.Com.Parameters.AddWithValue("RECEIPTNO", nNewReceiptNo);
hSql.ExecuteReader();
if (hSql.Read())
{
nCreditedPaidAmount = hSql.Reader.GetDecimal(0);
}
//Insert new TRANSR row
int i = 0;
bool bNeedRounding = false;
while (i < Rows.Count)
{
hSql.NewCommand("insert into " + objUtil.getTable("CASHTRANSR") + "(RECEIPTNO,ROWNO,CASHFORM,CASHTYPE,QTY,AMOUNT,SALETYPE,FEE, CC_TERMINALID) " +
" values(?,?,?,?,?,?,?,?,?)");
hSql.Com.Parameters.AddWithValue("RECEIPTNO", nNewReceiptNo);
hSql.Com.Parameters.AddWithValue("ROWNO", Rows[i].Rowno);
hSql.Com.Parameters.AddWithValue("CASHFORM", Rows[i].PaymentForm.Code);
hSql.Com.Parameters.AddWithValue("CASHTYPE", Rows[i].PaymentType.Code);
hSql.Com.Parameters.AddWithValue("QTY", Rows[i].Quantity * (-1));
hSql.Com.Parameters.AddWithValue("AMOUNT", Rows[i].Amount * (-1));
hSql.Com.Parameters.AddWithValue("SALETYPE", Rows[i].SaleType);
hSql.Com.Parameters.AddWithValue("FEE", Rows[i].AmountFee * (-1));
hSql.Com.Parameters.AddWithValue("CC_TERMINALID", Rows[i].CardType.Code);
bRet = bRet && hSql.ExecuteNonQuery();
Rows[i].CreditNewNo = nNewReceiptNo;
i++;
}
_log.Debug(strInvoiceModule);
if ((strInvoiceModule == "VA") || (strInvoiceModule == "SP") || (strInvoiceModule == "WO"))
{
hSql.NewCommand("update TEMPINV set PAYSUM=isnull(PAYSUM,0) + ? where RECNO=? and UNITID = ?");
//hSql.NewCommand("update TEMPINV set PAYSUM= ? where RECNO=? and UNITID = ?"); //and PAYDATE is not null
hSql.Com.Parameters.AddWithValue("PAYSUM", nCreditedPaidAmount);
hSql.Com.Parameters.AddWithValue("RECNO", strInvoiceNo);
hSql.Com.Parameters.AddWithValue("UNITID", objGlobal.CurrentSiteId);
hSql.ExecuteNonQuery();
hSql.NewCommand("update TEMPINV set PAYDATE=null where RECNO=? and PAYDATE is not null and UNITID = ?");
hSql.Com.Parameters.AddWithValue("RECNO", strInvoiceNo);
hSql.Com.Parameters.AddWithValue("UNITID", objGlobal.CurrentSiteId);
hSql.ExecuteNonQuery();
string strSql = "";
if (strInvoiceModule == "VA")
{
strSql = "update a set a.PAYD=b.PAYDATE,a.PAIDSUM=b.PAYSUM from " + objUtil.getTable("CBIL") + " a, TEMPINV b where a.CRECNO=b.RECNO and a.PAYD is not null and b.PAYDATE is null and b.RECNO=? and b.UNITID = ?";
}
if (strInvoiceModule == "SP")
{
strSql = "update a set a.PAIDDATE=b.PAYDATE,a.PAIDSUM=b.PAYSUM from " + objUtil.getTable("SBIL") + " a, TEMPINV b where a.SRECNO=b.RECNO and a.PAIDDATE is not null and b.PAYDATE is null and b.RECNO=? and b.UNITID = ?";
}
if (strInvoiceModule == "WO")
{
strSql = "update a set a.PAIDDATE=b.PAYDATE,a.PAIDSUM=b.PAYSUM from " + objUtil.getTable("GBIL") + " a, TEMPINV b where a.GRECNO=b.RECNO and a.PAIDDATE is not null and b.PAYDATE is null and b.RECNO=? and b.UNITID = ?";
}
hSql.NewCommand(strSql);
hSql.Com.Parameters.AddWithValue("RECNO", strInvoiceNo);
hSql.Com.Parameters.AddWithValue("UNITID", objGlobal.CurrentSiteId);
hSql.ExecuteNonQuery();
}
}
else
{
_log.Error("Invalid NUSEID assigned to the CASHTYPE : " + Rows[0].PaymentType);
}
//
if (bRet)
{
hSql.Commit();
}
else
{
hSql.Rollback();
}
}
}
catch (Exception ex)
{
_log.Error(ex.ToString());
bRet = false;
hSql.Rollback();
throw ex;
}
finally
{
hSql.Close();
}
_log.Debug("undoPayment << ");
return(bRet);
}