public decimal getClosingBalance(string strCashBoxId, out decimal nQty)
{
decimal nRet = 0;
nQty = 0;
clsSqlFactory hSql = new clsSqlFactory();
hSql.NewCommand("select top 1 isnull(b.AMOUNT,0),a.UPDATED_DATE, isnull(b.QTY,0) from " + objUtil.getTable("CASHREG") + " a," +
objUtil.getTable("CASHREGROW") + " b where a.ROWID = b.CASHREGROWID and a.CASHBOXID = ? and a.EVENT = 'O' " +
" order by a.UPDATED_DATE desc");
hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
hSql.ExecuteReader();
hSql.Read();
DateTime dtLastOpen = hSql.Reader.GetDateTime(1);
decimal nOpeningBalance = hSql.Reader.GetDecimal(0);
decimal nOpeningQty = hSql.Reader.GetDecimal(2);
hSql.NewCommand("select isnull(sum(b.AMOUNT),0) as TOTAMOUNT, isnull(sum(b.QTY),0) from " + objUtil.getTable("CASHTRANSH") + " a, " + objUtil.getTable("CASHTRANSR") + " b" +
" where a.CASHBOXID =? and a.RECEIPTNO = b.RECEIPTNO and a.UPDATED_DATE >=? ");
hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
hSql.Com.Parameters.AddWithValue("LASTOPEN", dtLastOpen);
hSql.ExecuteReader();
hSql.Read();
nQty = hSql.Reader.GetDecimal(1) + nOpeningQty;
nRet = hSql.Reader.GetDecimal(0) + nOpeningBalance;
return(nRet);
}
private void syncAllContractsToolStripMenuItem_Click(object sender, EventArgs e)
{
clsSqlFactory hSql = new clsSqlFactory();
String strSql = "exec ZSC_SP_SyncContractToAM '" + NWDBName + "', null";
hSql.NewCommand(strSql);
hSql.ExecuteNonQuery();
hSql.Commit();
hSql.Close();
}
private void cmbCashBox_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
clsBaseListItem selectedListItem = (clsBaseListItem)cmbCashBox.SelectedItem;
String strCashBoxId = selectedListItem.strValue1;//cmbCashBox.Text.Substring(0, cmbCashBox.Text.IndexOf("="));
if (strCashBoxId != "")
{
int CashBoxInd = CashBox.getIndexByCashBoxId(strCashBoxId);
String strCurCd = CashBox.CashBoxes.Rows[CashBoxInd]["CURCD"].ToString();
decimal nClosingQty = 0;
dfClosingBalance.Text = string.Format("{0:#,0.00}", CashBox.getClosingBalance(strCashBoxId, out nClosingQty));
dfClosingQty.Text = string.Format("{0:#,0.00}", nClosingQty);
this.gridDenomination.Rows.Clear();
//Fill denomination
if (selectedListItem.nValue1 == 1) //Need denomination
{
gridDenomination.Enabled = true;
clsSqlFactory hSql = new clsSqlFactory();
hSql.NewCommand("select V1,C2,C4 from " + objUtil.getTable("CORW") + " a where a.CODAID ='CASHDENOM' and a.C3 = ? ");
hSql.Com.Parameters.AddWithValue("CURCD", strCurCd);
hSql.ExecuteReader();
int i = 0;
while (hSql.Read())
{
gridDenomination.Rows.Add();
gridDenomination.Rows[i].Cells["colIsCoin"].Value = hSql.Reader.GetInt32(0);
gridDenomination.Rows[i].Cells["colCaption"].Value = hSql.Reader.GetString(1);
gridDenomination.Rows[i].Cells["colFaceValue"].Value = hSql.Reader.GetString(2);
gridDenomination.Rows[i].Cells["colCount"].Value = string.Format("{0:#,0}", Int32.Parse("0"));
gridDenomination.Rows[i].Cells["colTotal"].Value = string.Format("{0:#,0.00}", Decimal.Parse("0"));
i++;
}
gridDenomination.CurrentCell = gridDenomination.Rows[0].Cells["colCount"];
gridDenomination.BeginEdit(true);
}
else
{
gridDenomination.Enabled = false;
}
//En_disable OK button
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
MessageBox.Show(objUtil.Localization.getMsgString("CASHREG_03"));//Select Cashbox !
}
finally
{
disEnableButtons();
}
}
private void syncSelectedContractToolStripMenuItem_Click(object sender, EventArgs e)
{
foreach (DataGridViewRow r in this.gridContract.SelectedRows)
{
ContractOid = (int)r.Cells["colCcontractOID"].Value;
}
if (ContractOid > 0)
{
clsSqlFactory hSql = new clsSqlFactory();
String strSql = "exec ZSC_SP_SyncContractToAM '" + NWDBName + "'," + ContractOid.ToString();
hSql.NewCommand(strSql);
hSql.ExecuteNonQuery();
hSql.Commit();
hSql.Close();
}
}
public int getReceiptNUSE(clsSqlFactory hSql, int nNuseId)
{
_log.Debug("getReceiptNUSE >> nUseId = " + nNuseId.ToString());
int nRet = 0;
hSql.NewCommand("update " + objUtil.getTable("NUSE") + " set RECNO=RECNO+1 where NUSEID = ?");
hSql.Com.Parameters.AddWithValue("NUSEID", nNuseId);
hSql.ExecuteNonQuery();
hSql.NewCommand("select RECNO from " + objUtil.getTable("NUSE") + " where NUSEID = ?");
hSql.Com.Parameters.AddWithValue("NUSEID", nNuseId);
hSql.ExecuteReader();
if (hSql.Read())
{
nRet = hSql.Reader.GetInt32(0);
}
_log.Debug("getReceiptNUSE << ReceipNo = " + nRet.ToString());
return(nRet);
}
public decimal getPreviousBalance(string strCashBoxId, out decimal nQty)
{
decimal nRet = 0;
nQty = 0;
clsSqlFactory hSql = new clsSqlFactory();
hSql.NewCommand("select top 1 isnull(b.AMOUNT,0), isnull(b.QTY,0) from " + objUtil.getTable("CASHREG") + " a left join " + objUtil.getTable("CASHREGROW") + " b on a.ROWID = b.CASHREGROWID " +
" and b.CASHFORM in ('CASH','CURRENCY') where a.EVENT = 'C' and a.CASHBOXID =? order by a.UPDATED_DATE desc");
hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
hSql.ExecuteReader();
if (hSql.Read())
{
nRet = hSql.Reader.GetDecimal(0);
nQty = hSql.Reader.GetDecimal(1);
}
return(nRet);
}
public void openSPInvoice(String SiteId, int SRECNO)
{
String AddParam = "-com:11 -param1:$OPENINVOICE$1 -param2:$START$1 -param3:$CLOSE$1 -param4:$APPLICATIONID$4 -param5:$INVOICENUMBER$" + SRECNO.ToString();
clsSqlFactory hSql = new clsSqlFactory();
hSql.NewCommand("select a.BTYPE,a.BILLD,b.CUSTID from ALL_SBIL a, CUST b where a.SRECNO=? and a._UNITID=? and a.CUSTNO=b.CUSTNO ");
hSql.Com.Parameters.Add("SRECNO", SRECNO);
hSql.Com.Parameters.Add("UNITID", SiteId);
hSql.ExecuteReader();
if (hSql.Read())
{
AddParam += " -param6:$INVOICECATEGORY$" + hSql.Reader.GetString(0);
TimeSpan ts = hSql.Reader.GetDateTime(1) - new DateTime(1900, 1, 1);
AddParam += " -param7:$INVOICEDATE$" + ts.TotalDays.ToString();
AddParam += " -param8:$CUSTOMERID$" + hSql.Reader.GetInt32(2).ToString();
launchCOM(SiteId, AddParam);
}
hSql.Close();
}
private void pbPrintClosingReport_Click(object sender, EventArgs e)
{
_log.Debug("pbPrintClosingReport_Click >>");
clsSqlFactory hSql = new clsSqlFactory();
try
{
String strLanguageCode = "ENG";
hSql.NewCommand("select C1 from " + objUtil.getTable("CORW") + " where CODAID ='KIELIKOODI' and C8 = ?");
hSql.Com.Parameters.AddWithValue("C8", objGlobal.CultureInfo);
hSql.ExecuteReader();
if (hSql.Read())
{
strLanguageCode = hSql.Reader.GetString(0);
}
else
if (ConfigurationManager.AppSettings["LangCodeAM"] != null && ConfigurationManager.AppSettings["LangCodeAM"] != "")
{
strLanguageCode = ConfigurationManager.AppSettings["LangCodeAM"];
}
String strPrintClosing_bat = ConfigurationSettings.AppSettings["PrintClosing_bat"];
if (objAppConfig.getStringParam("CASHREG", "P_CLOSING", "C3", "") != "")
{
strPrintClosing_bat = objAppConfig.getStringParam("CASHREG", "P_CLOSING", "C3", "");
}
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo.FileName = strPrintClosing_bat;
process.StartInfo.Arguments = strLanguageCode;
process.Start();
}
catch (Exception ex)
{
_log.Error(ex.ToString());
throw ex;
}
finally
{
hSql.Close();
}
_log.Debug("pbPrintClosingReport_Click <<");
}
public AMComClient()
{
AMComClientFileName = ConfigurationManager.AppSettings["AMComClient"].ToString();
clsSqlFactory hSql = new clsSqlFactory();
try
{
SiteIds = new String[0];
Users = new String[0];
Passwords = new String[0];
hSql.NewCommand("select UnitId, UserId, Password from Z_BASE_EXTUNIT order by UnitId ");
if (hSql.ExecuteReader())
{
int i = 0;
while (hSql.Read())
{
i = resizeArray();
SiteIds[i] = hSql.Reader.GetString(0);
Users[i] = hSql.Reader.GetString(1);
Passwords[i] = hSql.Reader.GetString(2);
}
}
hSql.NewCommand("select isnull(LANGID,'ENG') from EUSR where USRSID=? ");
hSql.Com.Parameters.Add("USRSID", objGlobal.DMSFirstUserName);
hSql.ExecuteReader();
if (hSql.Read())
{
LangId = hSql.Reader.GetString(0);
}
}
catch (Exception ex)
{
hSql.Rollback();
_log.Error(ex.Message);
throw ex;
}
finally
{
hSql.Close();
}
}
public bool loadRows()
{
Rows.Clear();
if ((ReceiptNo > 0) && (Paid == true))
{
clsSqlFactory hSql = new clsSqlFactory();
hSql.NewCommand("select CASHFORM,CASHTYPE,isnull(QTY,0),isnull(AMOUNT,0),SALETYPE,ROWNO, isnull(b.C2,'') as CASHFORM_NAME,"
+ " isnull(c.C2,'') as CASHTYPE_NAME, isnull(FEE,0) as FEE, isnull(CC_TERMINALID,'') as CARDTYPE, isnull(d.C2,'') as CARDNAME from " + objUtil.getTable("CASHTRANSR")
+ " left join " + objUtil.getTable("CORW") + " b on b.CODAID = 'CASHFORM' and b.C1=CASHFORM "
+ " left join " + objUtil.getTable("CORW") + " c on c.CODAID = 'CASHTYPE' and c.C1=CASHTYPE "
+ " left join " + objUtil.getTable("CORW") + " d on d.CODAID = 'CASHCCTYPE' and d.C1=CC_TERMINALID "
+ " where RECEIPTNO=?");
hSql.Com.Parameters.AddWithValue("RECEIPTNO", ReceiptNo);
hSql.ExecuteReader();
while (hSql.Read())
{
InvoiceRow aRow = new InvoiceRow();
aRow.CashBoxId = strCashBoxId;
aRow.Receiptno = ReceiptNo;
aRow.PaymentForm.Code = hSql.Reader.GetString(0);
aRow.PaymentType.Code = hSql.Reader.GetString(1);
aRow.Quantity = hSql.Reader.GetDecimal(2);
aRow.Amount = hSql.Reader.GetDecimal(3);
aRow.AmountPaid = aRow.Amount;
aRow.AmountChange = 0;
aRow.SaleType = hSql.Reader.GetString(4);
aRow.Rowno = hSql.Reader.GetInt32(5);
aRow.PaymentForm.Name = aRow.PaymentForm.Code + "=" + hSql.Reader.GetString(6);
aRow.PaymentType.Name = aRow.PaymentType.Code + "=" + hSql.Reader.GetString(7);
aRow.AmountFee = hSql.Reader.GetDecimal(hSql.Reader.GetOrdinal("FEE"));
aRow.CardType.Code = hSql.Reader.GetString(hSql.Reader.GetOrdinal("CARDTYPE"));
aRow.CardType.Name = aRow.CardType.Code + "=" + hSql.Reader.GetString(hSql.Reader.GetOrdinal("CARDNAME"));
Rows.Add(aRow);
}
hSql.Close();
}
return(true);
}
public bool invoiceContract(Contract objContract, bool bDraft, bool bManual)
{
bool bRet = true;
int InvoiceSeqNr = 0;
clsSqlFactory hSql = new clsSqlFactory();
hSql.NewCommand("select isnull(max(InvoiceNo),0) from ZSC_ContractInvoice where ContractOID=? ");
hSql.Com.Parameters.Add("ContractOID", objContract.ContractOID);
hSql.ExecuteReader();
if (hSql.Read())
{
InvoiceSeqNr = hSql.Reader.GetInt32(0);
InvoiceSeqNr += 1;
}
List <String> Payers = new List <String>();
hSql.NewCommand("select distinct PartialPayer from ZSC_ContractOption where ContractOid = ? and PartialPayer is not null and PartialPayer != '' and SelPr!=0 ");
hSql.Com.Parameters.Add("ContractOID", objContract.ContractOID);
hSql.ExecuteReader();
while (hSql.Read())
{
Payers.Add(hSql.Reader.GetString(0));
}
if (objContract.ContractPaymentData.PaymentIsInBlock == false)
{
if (objContract.LastInvoiceDate <= objContract.ContractDateData.InvoiceEndDate)
{
bRet = invoiceContract(objContract, bDraft, bManual, "", InvoiceSeqNr, false);
foreach (String Payer in Payers)
{
bRet = invoiceContract(objContract, bDraft, bManual, Payer, InvoiceSeqNr, false);
}
if ((objContract.ContractCapitalData.CapitalMonthAmount != 0) && (objContract.ContractCapitalData.CapitalMonthPayer.strValue1 != ""))
{
bRet = bRet && invoiceContract(objContract, bDraft, bManual, objContract.ContractCapitalData.CapitalMonthPayer.strValue1, InvoiceSeqNr, true);
}
}
}
else
{
objContract.calcPaymentBlock(true);
DateTime dtNextBlockEnd = objContract.ContractPaymentData.PaymentNextBlockEnd;
if (objContract.NextInvoiceDate == DateTime.MinValue)
{
objContract.NextInvoiceDate = DateTime.Now;
}
while ((objContract.NextInvoiceDate <= dtNextBlockEnd) && (objContract.LastInvoiceDate <= objContract.ContractDateData.InvoiceEndDate))
{
bRet = invoiceContract(objContract, bDraft, bManual, "", InvoiceSeqNr, false);
foreach (String Payer in Payers)
{
bRet = invoiceContract(objContract, bDraft, bManual, Payer, InvoiceSeqNr, false);
}
if ((objContract.ContractCapitalData.CapitalMonthAmount != 0) && (objContract.ContractCapitalData.CapitalMonthPayer.strValue1 != ""))
{
bRet = bRet && invoiceContract(objContract, bDraft, bManual, objContract.ContractCapitalData.CapitalMonthPayer.strValue1, InvoiceSeqNr, true);
}
objContract = SCBase.searchContracts(objContract.ContractOID);
InvoiceSeqNr++;
}
objContract.calcPaymentBlock(true);
bRet = bRet && hSql.NewCommand("update ZSC_Contract set PaymentNextBlockStart = ?,PaymentNextBlockEnd=? where OID=?");
hSql.Com.Parameters.AddWithValue("PaymentNextBlockStart", objContract.ContractPaymentData.PaymentNextBlockStart);
hSql.Com.Parameters.AddWithValue("PaymentNextBlockEnd", objContract.ContractPaymentData.PaymentNextBlockEnd);
hSql.Com.Parameters.AddWithValue("ContractOID", objContract.ContractOID);
bRet = bRet && hSql.ExecuteNonQuery();
if (bRet)
{
hSql.Commit();
objContract = SCBase.searchContracts(objContract.ContractOID);
}
else
{
hSql.Rollback();
}
}
hSql.Close();
return(bRet);
}
public static List <SCInvoiceItem> getInvoiceDetail(Int32 invoiceID)
{
if (invoiceID < 0)
{
return(new List <SCInvoiceItem>());
}
clsSqlFactory hSql = new clsSqlFactory();
List <SCInvoiceItem> Result = new List <SCInvoiceItem>();
try
{
clsGlobalVariable objGlobal = new clsGlobalVariable();
String strSql = "select isnull(c.ITEM,'') as ITEMNO, isnull(c.NAME,'') as ITEMNAME,isnull(c.VATCD,'') as VATCD,isnull(c.NUM,0) as NUM,isnull(c.RSUM,0) as RSUM,isnull(c.NOTE,'') as NOTE,c.RTYPE as RTYPE " +
", isnull(c.RSUM,0) / isnull(dbo.fn_AMVATValue(c._UNITID,c.VATCD),1) as RSUM0 " +
" FROM ZSC_ContractInvoice a, all_sbil b, ALL_SROW c " +
" WHERE a.OID=? and a.SSALID = b.SSALID and a.UnitId = b._UNITID and b._unitid = c._unitid and b.srecno = c.srecno and b.ssalid = c.ssalid";
hSql.NewCommand(strSql);
hSql.Com.Parameters.AddWithValue("OID", invoiceID);
hSql.ExecuteReader();
int colId;
while (hSql.Read())
{
SCInvoiceItem item = new SCInvoiceItem();
colId = hSql.Reader.GetOrdinal("ITEMNO");
if (!hSql.Reader.IsDBNull(colId))
{
item.ITEMNO = hSql.Reader.GetString(colId);
}
colId = hSql.Reader.GetOrdinal("ITEMNAME");
if (!hSql.Reader.IsDBNull(colId))
{
item.NAME = hSql.Reader.GetString(colId);
}
colId = hSql.Reader.GetOrdinal("VATCD");
if (!hSql.Reader.IsDBNull(colId))
{
item.VATCD = hSql.Reader.GetString(colId);
}
colId = hSql.Reader.GetOrdinal("NUM");
if (!hSql.Reader.IsDBNull(colId))
{
item.NUM = hSql.Reader.GetDecimal(colId);
}
colId = hSql.Reader.GetOrdinal("RSUM");
if (!hSql.Reader.IsDBNull(colId))
{
item.RSUM = hSql.Reader.GetDecimal(colId);
}
colId = hSql.Reader.GetOrdinal("RSUM0");
if (!hSql.Reader.IsDBNull(colId))
{
item.RSUM0 = hSql.Reader.GetDecimal(colId);
}
colId = hSql.Reader.GetOrdinal("NOTE");
if (!hSql.Reader.IsDBNull(colId))
{
item.NOTE = hSql.Reader.GetString(colId);
}
colId = hSql.Reader.GetOrdinal("RTYPE");
if (!hSql.Reader.IsDBNull(colId))
{
item.RTYPE = hSql.Reader.GetInt16(colId);
}
Result.Add(item);
}
}
catch (Exception ex)
{
_log.Error("ERROR getInvoiceDetail " + invoiceID + ": ", ex);
throw ex;
}
finally
{
hSql.Close();
}
return(Result);
}
public static List <SCContractRemark> getRemark(int ContractOID)
{
clsSqlFactory hSql = new clsSqlFactory();
List <SCContractRemark> Result = new List <SCContractRemark>();
try
{
clsGlobalVariable objGlobal = new clsGlobalVariable();
String strSql = "select a.OID as OID, a.ContractOID as ContractOID , a.Created as Created,a.UserId as UserId,a.RemarkType as RemarkType,a.Info as Info " +
"FROM ZSC_ContractRemark a " +
"WHERE a.ContractOID = ? ORDER BY a.Created DESC ";
hSql.NewCommand(strSql);
hSql.Com.Parameters.AddWithValue("ContractOID", ContractOID);
hSql.ExecuteReader();
int colId;
while (hSql.Read())
{
SCContractRemark item = new SCContractRemark();
colId = hSql.Reader.GetOrdinal("OID");
if (!hSql.Reader.IsDBNull(colId))
{
item.OID = hSql.Reader.GetInt32(colId);
}
colId = hSql.Reader.GetOrdinal("ContractOID");
if (!hSql.Reader.IsDBNull(colId))
{
item.ContractOID = hSql.Reader.GetInt32(colId);
}
colId = hSql.Reader.GetOrdinal("Created");
if (!hSql.Reader.IsDBNull(colId))
{
item.Created = hSql.Reader.GetDateTime(colId);
}
colId = hSql.Reader.GetOrdinal("UserId");
if (!hSql.Reader.IsDBNull(colId))
{
item.UserId = hSql.Reader.GetString(colId);
}
colId = hSql.Reader.GetOrdinal("RemarkType");
if (!hSql.Reader.IsDBNull(colId))
{
item.RemarkType = hSql.Reader.GetInt16(colId);
}
colId = hSql.Reader.GetOrdinal("Info");
if (!hSql.Reader.IsDBNull(colId))
{
item.Info = hSql.Reader.GetString(colId);
}
Result.Add(item);
}
}
catch (Exception ex)
{
_log.Error("ERROR getRemark " + ContractOID + ": ", ex);
throw ex;
}
finally
{
hSql.Close();
}
return(Result);
}
private void addInvoiceRows(Contract objContract, ref SCInvoice objInv, bool bCapital)
{
clsSqlFactory hSql = new clsSqlFactory();
int RowId = objInv.InvItems.Count;
Decimal nInvoiceSum = 0;
Decimal nInvoiceOrigSum = 0;
Decimal nInvoiceOrigBuyPr = 0;
int nPayPeriod = 1;
clsTaxHandling objTax = new clsTaxHandling();
objTax.Init(2);
String RowVatCd = getVatCode(objTax, objInv.BTYPE);
SCInvoiceItem objRow;
if (objContract.IsInvoiceDetail == false)
{
RowId++;
objRow = new SCInvoiceItem();
objRow.SROWID = RowId;
objRow.NAME = "~1";
objRow.RTYPE = 8;
objRow.NOTE = "CONTRACT NO " + objContract.ContractNo.ToString();
objRow.EXIDNO = objContract.ContractNo;
objInv.InvItems.Add(objRow);
}
switch (objContract.ContractCostData.CostBasis.strValue1)
{
case CostBasisType.Monthly:
nInvoiceSum = objContract.ContractCostData.CostBasedOnService - objContract.ContractCapitalData.CapitalMonthAmount;
break;
case CostBasisType.KmOrHour:
break;
case CostBasisType.KmOrHourWithLump:
nInvoiceSum = objContract.ContractCostData.CostMonthBasis - objContract.ContractCapitalData.CapitalMonthAmount;
break;
default:
break;
}
switch (objContract.ContractPaymentData.PaymentPeriod.strValue1)
{
case PaymentPeriodType.Quarterly:
nPayPeriod = 3;
break;
case PaymentPeriodType.HalfYear:
nPayPeriod = 6;
break;
case PaymentPeriodType.Yearly:
nPayPeriod = 12;
break;
default:
break;
}
if (bCapital)
{
nInvoiceSum = objContract.ContractCapitalData.CapitalMonthAmount * nPayPeriod;
nInvoiceSum = (Decimal)objTax.Add(nInvoiceSum, RowVatCd, objInv.BILLD);
}
else
{
if (objInv.Payer != "")
{
nInvoiceSum = 0;
}
nInvoiceSum = (Decimal)objTax.Add(nInvoiceSum, RowVatCd, objInv.BILLD);
nInvoiceSum = nInvoiceSum * nPayPeriod;
}
foreach (ContractOption objCat in objContract.listContractOptions)
{
if ((objCat.PartNr != null) && (objCat.PartNr != "") &&
(((objCat.PartialPayer == objInv.Payer) && (bCapital == false)) || ((objCat.PartialPayer == "") && (bCapital == true))
)
)
{
RowId++;
objRow = new SCInvoiceItem();
objRow.ITEMNO = objCat.PartNr;
if (objInv.PartPostFix != "")
{
hSql.NewCommand("select 1 from ITEM where ITEMNO=? and SUPLNO=? ");
hSql.Com.Parameters.Add("ITEMNO", objRow.ITEMNO + objInv.PartPostFix);
hSql.Com.Parameters.Add("SUPLNO", objCat.PartSuplNo);
hSql.ExecuteReader();
if (hSql.Read())
{
objRow.ITEMNO += objInv.PartPostFix;
}
}
objRow.SUPLNO = objCat.PartSuplNo;
objRow.NAME = objCat.Name;
objRow.RTYPE = 2;
objRow.SROWID = RowId;
objRow.BUYPR = (Decimal)objTax.Add(objCat.PurchasePr, RowVatCd, objInv.BILLD);
objRow.DISCPC = 0;
objRow.NUM = 1;
objRow.NUM = objRow.NUM * nPayPeriod;
objRow.UNITPR = (Decimal)objTax.Add(objCat.SalePr, RowVatCd, objInv.BILLD);
objRow.RSUM = (decimal)objRow.NUM * objRow.UNITPR;
objRow.IGROUPID = -1;
objRow.VATCD = RowVatCd;
nInvoiceOrigSum += objRow.RSUM;
nInvoiceOrigBuyPr += objRow.BUYPR;
objRow.EXIDNO = objContract.ContractNo;
objInv.InvItems.Add(objRow);
if ((objInv.Payer != "") && (bCapital == false))
{
nInvoiceSum += objRow.RSUM;
}
}
}
if (objContract.IsInvoiceDetail == false)
{
RowId++;
objRow = new SCInvoiceItem();
objRow.SROWID = RowId;
objRow.NAME = "~1";
objRow.RTYPE = 8;
objRow.NOTE = "-----------------------";
objRow.EXIDNO = objContract.ContractNo;
objInv.InvItems.Add(objRow);
}
//adapt row sum
if ((nInvoiceOrigSum != 0) && (objInv.Payer == "") || (bCapital == true))
{
for (int i = 0; i < objInv.InvItems.Count; i++)
{
if (objInv.InvItems[i].RTYPE != 8)
{
Decimal nOrigRSUM = objInv.InvItems[i].RSUM;
objInv.InvItems[i].RSUM = nOrigRSUM * nInvoiceSum / nInvoiceOrigSum;
objInv.InvItems[i].BUYPR = objInv.InvItems[i].BUYPR * nInvoiceSum / nInvoiceOrigSum;
if (nInvoiceOrigSum > nInvoiceSum)
{
if (nOrigRSUM != 0)
{
objInv.InvItems[i].DISCPC = (nOrigRSUM - objInv.InvItems[i].RSUM) / nOrigRSUM;
}
}
else
{
objInv.InvItems[i].UNITPR = objInv.InvItems[i].RSUM / objInv.InvItems[i].NUM;
}
}
}
}
hSql.Close();
}
public bool closeCashBox(string strCashBoxId)
{
_log.Debug("closeCashBox >> " + strCashBoxId);
bool bRet = true;
clsSqlFactory hSql = new clsSqlFactory();
clsAppConfig objAppConfig = new clsAppConfig();
try
{
int NUSEID = objAppConfig.getNumberParam("CASHREG", "DEPRECNO", "V1", "");
DataRow cashbox = CashBoxes.Select(("CASHBOXID = '" + strCashBoxId + "'"))[0];
int EOD_NUSEID = Int32.Parse(cashbox["EODNUSEID"].ToString());
hSql.NewCommand("select ISOPEN from " + objUtil.getTable("CASHBOX") + " where CASHBOXID=? and ISOPEN=1");
hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
bRet = bRet && hSql.ExecuteReader();
if (hSql.Read())
{
hSql.NewCommand("update " + objUtil.getTable("NUSE") + " set RECNO=RECNO+1 where NUSEID=?");
hSql.Com.Parameters.AddWithValue("NUSEID", NUSEID);
bRet = bRet && hSql.ExecuteNonQuery();
hSql.NewCommand("select RECNO from " + objUtil.getTable("NUSE") + " where NUSEID=?");
hSql.Com.Parameters.AddWithValue("NUSEID", NUSEID);
bRet = bRet && hSql.ExecuteReader();
if (hSql.Read())
{
int RECEIPTNO = hSql.Reader.GetInt32(0);
//EOD_NUSEID
hSql.NewCommand("update " + objUtil.getTable("NUSE") + " set RECNO=RECNO+1 where NUSEID=?");
hSql.Com.Parameters.AddWithValue("NUSEID", EOD_NUSEID);
hSql.ExecuteNonQuery();
hSql.NewCommand("select RECNO from " + objUtil.getTable("NUSE") + " where NUSEID=?");
hSql.Com.Parameters.AddWithValue("NUSEID", EOD_NUSEID);
bRet = bRet && hSql.ExecuteReader();
if (hSql.Read())
{
int EOD_RECEIPTNO = hSql.Reader.GetInt32(0);
//
hSql.NewCommand("update " + objUtil.getTable("CASHBOX") + " set ISOPEN=0 where CASHBOXID=?");
hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
hSql.ExecuteNonQuery();
hSql.NewCommand("insert into " + objUtil.getTable("CASHREG") + "(CASHBOXID,SMANID,EVENT,UPDATED_DATE,UPDATED_BY,RECEIPTNO,EOD_RECEIPTNO) values(?,?,'C',getdate(),?,?,?)");
hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
hSql.Com.Parameters.AddWithValue("SMANID", objGlobal.DefaultSManID);
hSql.Com.Parameters.AddWithValue("UPDATED_BY", objGlobal.DMSFirstUserName);
hSql.Com.Parameters.AddWithValue("RECEIPTNO", RECEIPTNO);
hSql.Com.Parameters.AddWithValue("EOD_RECEIPTNO", EOD_RECEIPTNO);
hSql.ExecuteNonQuery();
hSql.Com.Parameters.Clear();
hSql.NewCommand("SELECT @@IDENTITY");
bRet = bRet && hSql.ExecuteReader();
hSql.Read();
int nCashRegRowId = hSql.Reader.GetInt32(0);
hSql.NewCommand("select top 1 isnull(b.AMOUNT,0),a.UPDATED_DATE, isnull(b.QTY,0) from " + objUtil.getTable("CASHREG") + " a," +
" CASHREGROW b where a.ROWID = b.CASHREGROWID and a.CASHBOXID = ? and a.EVENT = 'O' " +
" order by a.UPDATED_DATE desc");
hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
bRet = bRet && hSql.ExecuteReader();
hSql.Read();
DateTime dtLastOpen = hSql.Reader.GetDateTime(1);
decimal nOpeningBalance = hSql.Reader.GetDecimal(0);
decimal nOpeningQty = hSql.Reader.GetDecimal(2);
hSql.NewCommand("insert into " + objUtil.getTable("CASHREGROW") + "(CASHREGROWID,ROWNO,CASHFORM,CASHTYPE,AMOUNT,QTY,UPDATED_DATE,UPDATED_BY) " +
" select ?, row_number() over (partition by getdate() order by getdate()),CASHFORM,CASHTYPE,isnull(TOTAMOUNT,0),isnull(TOTQTY,0),getdate(),? from " +
"(select b.CASHFORM,case b.CASHFORM when 'CASH' then '" + ConfigurationSettings.AppSettings["DefaultCashType"] + "' else b.CASHTYPE end as CASHTYPE,sum(b.AMOUNT) as TOTAMOUNT,sum(b.QTY) as TOTQTY from " + objUtil.getTable("CASHTRANSH") + " a, " + objUtil.getTable("CASHTRANSR") + " b" +
" where a.CASHBOXID =? and a.RECEIPTNO = b.RECEIPTNO and a.UPDATED_DATE >=? " +
" group by b.CASHFORM,case b.CASHFORM when 'CASH' then '" + ConfigurationSettings.AppSettings["DefaultCashType"] + "' else b.CASHTYPE end ) x");
hSql.Com.Parameters.AddWithValue("CASHREGROWID", nCashRegRowId);
hSql.Com.Parameters.AddWithValue("UPDATED_BY", objGlobal.DMSFirstUserName);
hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
hSql.Com.Parameters.AddWithValue("LASTOPEN", dtLastOpen);
bRet = bRet && hSql.ExecuteNonQuery();
hSql.NewCommand("select 1 from " + objUtil.getTable("CASHREGROW") + " where CASHREGROWID=? and CASHFORM in ('CASH','CURRENCY')");
hSql.Com.Parameters.AddWithValue("CASHREGROWID", nCashRegRowId);
bRet = bRet && hSql.ExecuteReader();
if (hSql.Read())
{
hSql.NewCommand("update " + objUtil.getTable("CASHREGROW") + " set AMOUNT=AMOUNT+?, QTY=QTY+? where CASHREGROWID=? and CASHFORM in ('CASH','CURRENCY') ");
hSql.Com.Parameters.AddWithValue("OPENBALANCE", nOpeningBalance);
hSql.Com.Parameters.AddWithValue("OPENQTY", nOpeningQty);
hSql.Com.Parameters.AddWithValue("CASHREGROWID", nCashRegRowId);
bRet = bRet && hSql.ExecuteNonQuery();
}
else
{
hSql.NewCommand("insert into " + objUtil.getTable("CASHREGROW") + "(CASHREGROWID,ROWNO,CASHFORM,CASHTYPE,AMOUNT,QTY,UPDATED_DATE,UPDATED_BY) values(" +
" ?, (select isnull(max(ROWNO),0)+1 from CASHREGROW where CASHREGROWID=? ),'CASH',?,?,?,getdate(),? )");
hSql.Com.Parameters.AddWithValue("CASHREGROWID", nCashRegRowId);
hSql.Com.Parameters.AddWithValue("CASHREGROWID2", nCashRegRowId);
hSql.Com.Parameters.AddWithValue("CASHTYPE", ConfigurationSettings.AppSettings["DefaultCashType"]);
hSql.Com.Parameters.AddWithValue("AMOUNT", nOpeningBalance);
hSql.Com.Parameters.AddWithValue("QTY", nOpeningQty);
hSql.Com.Parameters.AddWithValue("UPDATED_BY", objGlobal.DMSFirstUserName);
hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
bRet = bRet && hSql.ExecuteNonQuery();
}
//Update CASHTRANSH.EOD_RECEIPTNO
hSql.NewCommand("update " + objUtil.getTable("CASHTRANSH") + " set EOD_RECEIPTNO =? where CASHBOXID =? and EOD_RECEIPTNO is null and UPDATED_DATE >=?");
hSql.Com.Parameters.AddWithValue("EOD_RECEIPTNO", EOD_RECEIPTNO);
hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
hSql.Com.Parameters.AddWithValue("LASTOPEN", dtLastOpen);
bRet = bRet && hSql.ExecuteNonQuery();
//
hSql.NewCommand("delete from " + objUtil.getTable("CASHBOXPC") + " where CASHBOXID = ?");
hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
bRet = bRet && hSql.ExecuteNonQuery();
}
else
{
bRet = false;
throw new Exception("Invalid EODNUSEID (CASHBOXID.V3) ): " + NUSEID);
}
}
else
{
bRet = false;
throw new Exception("Invalid NUSEID (CASHREG.DEPRECNO): " + NUSEID);
}
}
else
{
bRet = false;
}
if (bRet)
{
hSql.Commit();
}
else
{
hSql.Rollback();
}
}
catch (Exception ex)
{
_log.Error(ex.ToString());
hSql.Rollback();
bRet = false;
throw ex;
}
finally
{
hSql.Close();
}
_log.Debug("closeCashBox >> " + bRet.ToString());
return(bRet);
}
public void loadVehicleData()
{
clearData();
//Load Data from DB
clsSqlFactory hSql = new clsSqlFactory();
contractVehicle.loadDynFields(hSql);
contractVehicle.loadMileages(hSql);
//Set Text
txtVin.Text = contractVehicle.VIN;
txtMake.Text = contractVehicle.Make;
//Set Dynamic
fillDynField();
//Load data grid
gridMileage.DataSource = LoadGridMileage(contractVehicle);
if (addCol)
{
addColumn();
addCol = false;
}
//Load Deviation
int diffMileage = 0;
int diffDate = 0;
VehicleMileage Mileage1 = null;
VehicleMileage Mileage2 = null;
List <VehicleMileage> Mileages = contractVehicle.Mileages;
if (Mileages != null && Mileages.Count > 1)
{
Mileage1 = Mileages[0];
Mileage2 = Mileages[1];
System.Diagnostics.Debug.WriteLine("---------------------Mileage1: " + Mileage1.MileageDate);
System.Diagnostics.Debug.WriteLine("---------------------Mileage2: " + Mileage2.MileageDate);
//T0 is the first, Tn is the last update to date mileage time stamp. The time stamps are read from V_ZSC_MileageReg.Created
TimeSpan difference = (DateTime.Compare(Mileage1.MileageDate, Mileage2.MileageDate) > 0) ? Mileage1.MileageDate - Mileage2.MileageDate : Mileage2.MileageDate - Mileage1.MileageDate;
diffDate = difference.Minutes;
//Mn are the mileages (at above time stamp), from V_ZSC_MileageReg.Mileage
diffMileage = Math.Abs(Mileage1.Mileage - Mileage2.Mileage);
System.Diagnostics.Debug.WriteLine("---------------------diffDate: " + diffDate);
System.Diagnostics.Debug.WriteLine("---------------------diffMileage: " + diffMileage);
}
if (diffMileage > 0)
{
float periodY = contract.ContractPeriodKm;
float periodM = contract.ContractPeriodKm / 12;
float avgDay = diffDate / diffMileage;
float avgMonth = avgDay * 30;
float devMonth = 100 * Math.Abs((avgMonth - periodM)) / periodM;
System.Diagnostics.Debug.WriteLine("---------------------avgMonth: " + avgMonth.ToString());
//Set data
txtMonAvg.Text = avgMonth.ToString();
txtMonDev.Text = devMonth.ToString();
txtQuarterAvg.Text = "";
txtQuarterDev.Text = "";
txtYearAvg.Text = "";
}
}
public bool openCashBox(string strCashBoxId, decimal nOpeningBalance, decimal nQty)
{
bool bRet = true;
_log.Debug("openCashBox >> SmanId = " + objGlobal.DefaultSManID + ", UserName = "******"select ISOPEN from " + objUtil.getTable("CASHBOX") + " where CASHBOXID=?");
hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
hSql.ExecuteReader();
if (hSql.Read())
{
if (hSql.Reader.GetInt16(0) == 1)
{
bRet = false;
}
else
{
hSql.NewCommand("update " + objUtil.getTable("CASHBOX") + " set ISOPEN=1 where CASHBOXID=?");
hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
bRet = hSql.ExecuteNonQuery();
}
}
else
{
hSql.NewCommand("insert into " + objUtil.getTable("CASHBOX") + "(CASHBOXID,ISOPEN) values(?,1)");
hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
bRet = hSql.ExecuteNonQuery();
}
string strComputerName = getClientPCName();
hSql.NewCommand("select 1 from " + objUtil.getTable("CASHBOXPC") + " where CASHBOXID=? and PC=?");
hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
hSql.Com.Parameters.AddWithValue("PC", strComputerName);
bRet = bRet && hSql.ExecuteReader();
//clsGlobalVariable objGlobal = new clsGlobalVariable();
if (bRet)
{
if (hSql.Read())
{
hSql.NewCommand("update " + objUtil.getTable("CASHBOXPC") + " set UPDATED_DATE=getdate(),UPDATED_BY=? where CASHBOXID=? and PC=?");
hSql.Com.Parameters.AddWithValue("UPDATED_BY", objGlobal.DMSFirstUserName);
hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
hSql.Com.Parameters.AddWithValue("PC", strComputerName);
bRet = bRet && hSql.ExecuteNonQuery();
}
else
{
hSql.NewCommand("insert into " + objUtil.getTable("CASHBOXPC") + "(CASHBOXID,PC,UPDATED_DATE,UPDATED_BY) values(?,?,getdate(),?)");
hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
hSql.Com.Parameters.AddWithValue("PC", strComputerName);
hSql.Com.Parameters.AddWithValue("UPDATED_BY", objGlobal.DMSFirstUserName);
bRet = bRet && hSql.ExecuteNonQuery();
}
}
if (bRet)
{
hSql.NewCommand("insert into " + objUtil.getTable("CASHREG") + "(CASHBOXID,SMANID,EVENT,UPDATED_DATE,UPDATED_BY) values(?,?,'O',getdate(),?)");
hSql.Com.Parameters.AddWithValue("CASHBOXID", strCashBoxId);
hSql.Com.Parameters.AddWithValue("SMANID", objGlobal.DefaultSManID);
hSql.Com.Parameters.AddWithValue("UPDATED_BY", objGlobal.DMSFirstUserName);
hSql.ExecuteNonQuery();
hSql.Com.Parameters.Clear();
hSql.NewCommand("SELECT @@IDENTITY");
hSql.ExecuteReader();
hSql.Read();
int nCashRegRowId = hSql.Reader.GetInt32(0);
hSql.NewCommand("insert into " + objUtil.getTable("CASHREGROW") + "(CASHREGROWID,ROWNO,CASHFORM,CASHTYPE,AMOUNT,UPDATED_DATE,UPDATED_BY,QTY) values(?,1,'CASH',?,?,getdate(),?,?)");
hSql.Com.Parameters.AddWithValue("CASHREGROWID", nCashRegRowId);
hSql.Com.Parameters.AddWithValue("CASHTYPE", ConfigurationSettings.AppSettings["DefaultCashType"]);
hSql.Com.Parameters.AddWithValue("AMOUNT", nOpeningBalance);
hSql.Com.Parameters.AddWithValue("UPDATED_BY", objGlobal.DMSFirstUserName);
hSql.Com.Parameters.AddWithValue("QTY", nQty);
hSql.ExecuteNonQuery();
}
if (bRet)
{
hSql.Commit();
}
else
{
hSql.Rollback();
}
}
catch (Exception ex)
{
hSql.Rollback();
_log.Error(ex.ToString());
bRet = false;
}
finally
{
hSql.Close();
}
return(bRet);
}
public static List <Invoice> searchInvoices(string namephrase, bool isPaid, bool isIncludeCredit, int SearchFlags)
{
List <Invoice> Results = new List <Invoice>();
clsBaseUtility locObjUtil = new clsBaseUtility();
clsGlobalVariable locObjGlobal = new clsGlobalVariable();
clsSqlFactory hSql = new clsSqlFactory();
try
{
string searchString = namephrase;
RegexOptions options = RegexOptions.None;
Regex regex = new Regex(@"[ ]{2,}", options);
searchString = regex.Replace(searchString, @" ");
string[] words = searchString.Split(new char[] { ' ' });
int i = 0;
string strOrder = "";
string strSql = "";
string strCUST = locObjUtil.getTable("CUST");
string strASVIEW_CAREG_UNPAID_INVOICE = strCUST == "CUST" ? "ASVIEW_CAREG_UNPAID_INVOICE" : "ASVIEW_CAREG_UNPAID_INVOICE" + locObjGlobal.CurrentSiteId;
if (SearchFlags > 0)
{
strOrder = "";
strSql = "select top " + ConfigurationManager.AppSettings["SearchResultNumber"].ToString() + " a.ORDNO,isnull(a.CUSTNO,''),isnull(a.LNAME,''),isnull(a.FNAME,''),a.BILLD,isnull(a.RECNO,''),a.INVSUM,a.APPAREA, isnull(a.DEPT,'') as DEPT,isnull(a.PAYSUM,0) from " + strASVIEW_CAREG_UNPAID_INVOICE + " a ";
while (i < words.Length)
{
if (words[i] != "")
{
strSql += " inner join containstable(" + strASVIEW_CAREG_UNPAID_INVOICE + ", *, '\"" + words[i] + "*\"' ) as T" + i.ToString() + " on a.INVID = T" + i.ToString() + ".[KEY] ";
if (i > 0)
{
strOrder += "+";
}
strOrder += "T" + i.ToString() + ".RANK";
}
i++;
}
//if (strOrder!="") strOrder = " order by " + strOrder + " desc, a.BILLD desc, a.RECNO desc ";
strOrder = " order by a.BILLD desc, a.RECNO desc ";
strSql = strSql + " where a.UNITID =? and (1=0 ";
if ((SearchFlags & InvoiceFlags.VehicleSalesFlag) == InvoiceFlags.VehicleSalesFlag)
{
strSql = strSql + " or APPAREA = 'C' ";
}
if ((SearchFlags & InvoiceFlags.SparePartSalesFlag) == InvoiceFlags.SparePartSalesFlag)
{
strSql = strSql + " or APPAREA = 'S' ";
}
if ((SearchFlags & InvoiceFlags.WorkshopFlag) == InvoiceFlags.WorkshopFlag)
{
strSql = strSql + " or APPAREA = 'G' ";
}
// strSql += ") and not exists( select 1 from TEMPINV x where x.UNITID = a.UNITID and a.RECNO = x.CRERECNO) " + strOrder;
strSql += ") and ( exists (select 1 from ALL_CBIL bil where bil.CRECNO = a.RECNO ) or exists (select 1 from ALL_GBIL bil where bil.GRECNO = a.RECNO ) or exists (select 1 from ALL_SBIL bil where bil.SRECNO = a.RECNO ) ) ";
if (!isIncludeCredit)
{
//Exclude credited
strSql += " and not exists( select 1 from TEMPINV x where x.UNITID = a.UNITID and a.RECNO = x.CRERECNO) ";
//Exclude credit
strSql += " and not exists( select 1 from TEMPINV x where x.UNITID = a.UNITID and a.RECNO = x.RECNO and x.CREDIT = 1 ) ";
}
//
strSql += strOrder;
_log.Debug(strSql);
hSql.NewCommand(strSql);
hSql.Com.Parameters.AddWithValue("UNITID", locObjGlobal.CurrentSiteId);
if (hSql.ExecuteReader())
{
while (hSql.Read())
{
Invoice Inv = new Invoice();
Inv.strOrderNo = hSql.GetString(0).ToString();
Inv.strCustNo = hSql.GetString(1).ToString();
Inv.strCustomerName = hSql.GetString(2).ToString() + " " + hSql.GetString(3).ToString();
Inv.strStatus = locObjUtil.Localization.getMsgString("CASHREG_10"); //Unpaid
Inv.strInvoiceNo = hSql.GetString(5);
Inv.dtInvoiceDate = hSql.GetDateTime(4); //.ToString("yyyy.MM.dd"));
Inv.decInvoiceSum = hSql.Reader.GetDecimal(6) - hSql.Reader.GetDecimal(9); //.ToString("N6").TrimEnd('0').Trim(',');
Inv.Paid = false;
Inv.strDeptId = hSql.GetString(8);
Inv.decInvoiceSumOrig = hSql.Reader.GetDecimal(6);
switch (hSql.GetString(7))
{
case "C":
{
Inv.InvoiceFlag = InvoiceFlags.VehicleSalesFlag;
Inv.strType = locObjUtil.Localization.getMsgString("CASHREG_07");//"Vehicle sales"
Inv.strInvoiceModule = "VA";
break;
}
case "G":
{
Inv.InvoiceFlag = InvoiceFlags.WorkshopFlag;
Inv.strType = locObjUtil.Localization.getMsgString("CASHREG_09");//"Workshop"
Inv.strInvoiceModule = "WO";
break;
}
case "S":
{
Inv.InvoiceFlag = InvoiceFlags.SparePartSalesFlag;
Inv.strType = locObjUtil.Localization.getMsgString("CASHREG_06");//"Spare part sales"
Inv.strInvoiceModule = "SP";
break;
}
}
Results.Add(Inv);
}
}
}
//--------------------------------------------------------------------Paid part------------------------------------------------
if (isPaid == true)
{
// 0 1 2 3 4 5 6 7 8 9 10
strSql = "select top " + ConfigurationManager.AppSettings["SearchResultNumber"].ToString() + " a.ORDNO,isnull(a.CUSTNO,''),isnull(a.LNAME,'') as LNAME,isnull(a.FNAME,''),a.BILLD,isnull(a.RECNO,''),isnull(a.AMOUNTTOPAY,0),isnull(a.TOTPAID,0),isnull(a.RECEIPTNO,''),a.PAYDATE " +
" , a.CASHBOXID,a.MODULE,a.DEPARTMENT,b.TEXT,isnull(a.LICNO,'') as LICNO,isnull(b.CREDITNEWNO,0) as CREDITNEWNO,ISNULL(b.CREDITNOTE,0) as CREDITNOTE, ISNULL(b.CREDITOFNO,0) as CREDITOFNO from " + locObjUtil.getTable("ASVIEW_CAREG_PAID_INVOICE") + " a inner join " + locObjUtil.getTable("CASHTRANSH") + " b on a._OID=b._OID ";
i = 0;
strOrder = "";
while (i < words.Length)
{
if (words[i] != "")
{
strSql += " inner join containstable(ASVIEW_CAREG_PAID_INVOICE" + locObjGlobal.CurrentSiteId + ", *, '\"" + words[i] + "*\"' ) as T" + i.ToString() + " on a._OID = T" + i.ToString() + ".[KEY] ";
if (i > 0)
{
strOrder += "+";
}
strOrder += "T" + i.ToString() + ".RANK";
}
i++;
}
//if (strOrder!="") strOrder = " order by " + strOrder + " desc, a.PAYDATE desc";
strOrder = " order by a.BILLD desc, a.RECNO desc,a.PAYDATE desc ";
strSql = strSql + " where (1=0 ";
if ((SearchFlags & InvoiceFlags.VehicleSalesFlag) == InvoiceFlags.VehicleSalesFlag)
{
strSql = strSql + " or a.MODULE = 'VA' ";
}
if ((SearchFlags & InvoiceFlags.SparePartSalesFlag) == InvoiceFlags.SparePartSalesFlag)
{
strSql = strSql + " or a.MODULE = 'SP' ";
}
if ((SearchFlags & InvoiceFlags.WorkshopFlag) == InvoiceFlags.WorkshopFlag)
{
strSql = strSql + " or a.MODULE = 'WO' ";
}
if ((SearchFlags & InvoiceFlags.CashRegisterFlag) == InvoiceFlags.CashRegisterFlag)
{
strSql = strSql + " or a.MODULE in ('CU','SU','CR') ";
}
strSql += ") " + strOrder;
_log.Debug(strSql);
hSql.NewCommand(strSql);
if (hSql.ExecuteReader())
{
while (hSql.Read())
{
//try
{
Invoice Inv = new Invoice();
Inv.strOrderNo = hSql.GetString(0).ToString();
Inv.strCustNo = hSql.GetString(1).ToString();
Inv.strCustomerName = hSql.GetString(2).ToString() + " " + hSql.GetString(3).ToString();
Inv.strStatus = locObjUtil.Localization.getMsgString("CASHREG_11");//Paid
Inv.strInvoiceNo = hSql.GetString(5);
Inv.ReceiptNo = hSql.Reader.GetInt32(8);
Inv.dtInvoiceDate = hSql.GetDateTime(4); //.ToString("yyyy.MM.dd"));
Inv.dtPaymentDate = hSql.GetDateTime(9); //.ToString("yyyy.MM.dd"));
Inv.decInvoiceSumOrig = hSql.Reader.GetDecimal(6); //.ToString("N6").TrimEnd('0').Trim(',');
Inv.decInvoiceSum = 0;
Inv.decPaymentSum = hSql.Reader.GetDecimal(7); //.ToString("N6").TrimEnd('0').Trim(',');
Inv.strCashBoxId = hSql.Reader.GetString(10);
Inv.strInvoiceModule = hSql.GetString(11);
Inv.strDeptId = hSql.GetString(12);
Inv.strRemark = hSql.GetString(13);
Inv.strLicno = hSql.GetString(14);
Inv.nCreditNewNo = hSql.Reader.GetInt32(15);
Inv.nCreditNote = hSql.Reader.GetInt32(16);
Inv.nCrediOfNo = hSql.Reader.GetInt32(17);
switch (Inv.strInvoiceModule)
{
case "VA":
{
Inv.InvoiceFlag = InvoiceFlags.VehicleSalesFlag;
Inv.strType = locObjUtil.Localization.getMsgString("CASHREG_11"); break; //"Vehicle sales"
}
case "WO":
{
Inv.InvoiceFlag = InvoiceFlags.WorkshopFlag;
Inv.strType = locObjUtil.Localization.getMsgString("CASHREG_09"); break; //"Workshop"
}
case "SP":
{
Inv.InvoiceFlag = InvoiceFlags.SparePartSalesFlag;
Inv.strType = locObjUtil.Localization.getMsgString("CASHREG_06"); break; //"Spare part sales"
}
default:
{
Inv.InvoiceFlag = InvoiceFlags.CashRegisterFlag;
Inv.strType = locObjUtil.Localization.getMsgString("CASHREG_08"); break; //"Cash Register"
}
}
Inv.Paid = true;
Results.Add(Inv);
}
}
}
}
}
catch (Exception ex)
{
_log.Error(ex.Message);
throw ex;
}
finally
{
hSql.Close();
}
return(Results);
}
private bool invoiceContract(Contract objContract, bool bDraft, bool bManual, String Payer, int InvoiceSeqNr, bool bCapital)
{
bool bRet = true;
clsAppConfig objAppConfig = new clsAppConfig();
clsGlobalVariable objGlobal = new clsGlobalVariable();
SCInvoice objInv = new SCInvoice();
clsSqlFactory hSql = new clsSqlFactory();
objInv.InvoiceSeqNr = InvoiceSeqNr;
if (Payer == "")
{
if (objContract.InvoiceCustId != null)
{
objInv.CustNo = objContract.InvoiceCustId.CustNr;
}
else
{
objInv.CustNo = objContract.ContractCustId.CustNr;
}
}
else
{
objInv.CustNo = objAppConfig.getNumberParam("ZSCCAPPAYE", Payer, "V1", "");
}
objInv.DCustNo = objContract.ContractCustId.CustNr;
objInv.UnitId = objContract.InvoiceSiteId.strValue1;
objInv.ContractOID = objContract.ContractOID;
objInv.ExtOrderId = objContract.ExtContractNo;
if (objInv.ExtOrderId.Length > 10)
{
objInv.ExtOrderId = objInv.ExtOrderId.Substring(0, 10);
}
objInv.SalesType = objAppConfig.getStringParam("ZSCSETTING", "TRTYPE", "C3", "");
objInv.SmanId = objContract.RespSmanId.SmanId;
objInv.TPCODE = objContract.ContractPaymentData.PaymentTerm.strValue1;
objInv.TPTIME = objAppConfig.getNumberParam("MAKSUEHDOT", objInv.TPCODE, "V1", "");
if (objContract.CostCenter.strValue1 != "")
{
hSql.NewCommand("select isnull(a.C4,'') from CORW a where a.CODAID='ZSCCOSTCC' and a.C3=? and a.C5=? ");
hSql.Com.Parameters.Add("UNITID", objContract.SiteId.strValue1);
hSql.Com.Parameters.Add("COSTCENTER", objContract.CostCenter.strValue1);
hSql.ExecuteReader();
if (hSql.Read())
{
objInv.DeptId = hSql.Reader.GetString(0);
}
}
objInv.Payer = Payer;
objInv.OUserId = objGlobal.DMSFirstUserName;
objInv.VehiId = objContract.VehiId.VehiId;
objInv.Note = objContract.ContractNo.ToString() + "/" + objContract.VersionNo.ToString() + "/" + objInv.InvoiceSeqNr.ToString();
hSql.NewCommand("select isnull(a.C4,''), isnull(a.C5,'') from CORW a, VEHI b where a.CODAID='ZSCCONV' and a.C2='VEHICLASS2BTYPE' and a.C3=b.CLASS and b.VEHIID=? ");
hSql.Com.Parameters.Add("VEHIID", objContract.VehiId.VehiId);
hSql.ExecuteReader();
if (hSql.Read())
{
objInv.BTYPE = hSql.Reader.GetString(0);
objInv.PartPostFix = hSql.Reader.GetString(1);
}
if (objContract.ContractPaymentData.PaymentGroupingLevel == PaymentGroupingType.Customer)
{
objInv.BTYPE = "0";
}
else
{
if (objContract.PaymentCollecType == PaymentCollectionType.Transfer)
{
objInv.BTYPE = objAppConfig.getStringParam("ZSCSETTING", "INVCAT4", "C3", "");
}
else if (objContract.PaymentCollecType == PaymentCollectionType.Debit)
{
objInv.BTYPE = objAppConfig.getStringParam("ZSCSETTING", "INVCAT5", "C3", "");
}
else if (objContract.PaymentCollecType == PaymentCollectionType.Plain)
{
objInv.BTYPE = objAppConfig.getStringParam("ZSCSETTING", "INVCAT6", "C3", "");
}
else
{
if (bManual == true)
{
objInv.BTYPE = objAppConfig.getStringParam("ZSCSETTING", "INVCAT2", "C3", "");
}
else
{
objInv.BTYPE = objAppConfig.getStringParam("ZSCSETTING", "INVCAT1", "C3", "");
}
}
}
if (Payer != "")
{
objInv.BTYPE = objAppConfig.getStringParam("ZSCCAPPAYE", Payer, "C3", "");
}
if (objContract.NextInvoiceDate == DateTime.MinValue)
{
objInv.BILLD = DateTime.Now;
}
else
{
objInv.BILLD = objContract.NextInvoiceDate;
}
int nInvoiceDay = objContract.ContractPaymentData.InvoiceDate;
if (nInvoiceDay <= 0)
{
nInvoiceDay = objAppConfig.getNumberParam("ZSCSETTING", "INVDATE", "V1", "");
}
if (objInv.BILLD.Day > nInvoiceDay)
{
objInv.BILLD = new DateTime(objInv.BILLD.Year, objInv.BILLD.Month, nInvoiceDay);
objInv.BILLD = objInv.BILLD.AddMonths(1);
}
else if (objInv.BILLD.Day < nInvoiceDay)
{
objInv.BILLD = new DateTime(objInv.BILLD.Year, objInv.BILLD.Month, nInvoiceDay);
}
objInv.CBILLD = new DateTime(objInv.BILLD.Year, objInv.BILLD.Month, objInv.BILLD.Day);
switch (objContract.ContractPaymentData.PaymentPeriod.strValue1)
{
case PaymentPeriodType.Quarterly:
objInv.NBILLD = objInv.CBILLD.AddMonths(3);
break;
case PaymentPeriodType.HalfYear:
objInv.NBILLD = objInv.CBILLD.AddMonths(6);
break;
case PaymentPeriodType.Yearly:
objInv.NBILLD = objInv.CBILLD.AddMonths(12);
break;
default:
objInv.NBILLD = objInv.CBILLD.AddMonths(1);
break;
}
objInv.DELD = objInv.BILLD;
addInvoiceText(hSql, objContract, ref objInv);
objContract.listContractOptions = ContractOption.getContractOption(objContract.ContractOID);
//objContract.loadDetail();
addInvoiceRows(objContract, ref objInv, bCapital);
hSql.Close();
bRet = objInv.saveOrder(bDraft);
if ((bRet == true) && (bManual == true))
{
if (bDraft == true)
{
openInvoice(objInv.UnitId, objInv.SSALID, objInv.SRECNO);
}
else
{
openInvoicePDF(objInv.UnitId, objInv.SRECNO);
}
}
return(bRet);
}
public static List <SCInvoice> getContractInvoice(int ContractOID, List <Int32> lstInvoiceType, bool creditInvoice)
{
if (lstInvoiceType == null || lstInvoiceType.Count <= 0)
{
return(new List <SCInvoice>());
}
clsSqlFactory hSql = new clsSqlFactory();
List <SCInvoice> Result = new List <SCInvoice>();
try
{
String strSql = "select a.OID as OID, b.SRECNO , b.BILLD,b.DELD,b.PAIDDATE,b.PAIDSUM,b.CRERECNO,b.CUSTNO,c.LNAME,d.EXPL, b.SSALID,b._UNITID " +
",b.BTYPE,e.SMANID as SMANID ,e.SORDNO as SORDNO,e.DEPT as DEPT, a.InvoiceNo, x.INVSUM,x.INVSUM0 " +
"FROM ZSC_ContractInvoice a, all_sbil b LEFT JOIN cust c on b.custno = c.CUSTNO, unit d, all_SSAL e," +
" (select _UNITID,SRECNO,SSALID,sum(isnull(RSUM,0)) as INVSUM,sum(isnull(RSUM/isnull(dbo.fn_AMVATValue(_UNITID,VATCD),1),0)) as INVSUM0 from all_SROW group by _UNITID,SRECNO,SSALID ) x " +
"WHERE a.ContractOID = ? and a.SSALID = b.SSALID and a.UnitId = b._UNITID and a.UnitId = d.UnitId and b.SSALID=e.SSALID and b._UNITID=e._UNITID and b._UNITID=x._UNITID and b.SSALID=x.SSALID and b.SRECNO=x.SRECNO ";
if (creditInvoice == false)
{
strSql += " AND b.CRERECNO IS NULL ";
}
if (lstInvoiceType.Count == 1)
{
strSql += " AND a.InvoiceType = ?";
strSql += " order by a.InvoiceNo desc ";
hSql.NewCommand(strSql);
hSql.Com.Parameters.AddWithValue("ContractOID", ContractOID);
hSql.Com.Parameters.AddWithValue("InvoiceType", lstInvoiceType[0]);
}
else
{
strSql = MyUtils.BuildWhereInClause(strSql, "InvoiceType", lstInvoiceType);
strSql += " order by a.OID desc ";
hSql.NewCommand(strSql);
hSql.Com.Parameters.AddWithValue("ContractOID", ContractOID);
int i = 0;
foreach (Int32 InvoiceType in lstInvoiceType)
{
hSql.Com.Parameters.AddWithValue("InvoiceType" + i++, InvoiceType);
}
}
hSql.ExecuteReader();
int colId;
while (hSql.Read())
{
SCInvoice item = new SCInvoice();
colId = hSql.Reader.GetOrdinal("OID");
if (!hSql.Reader.IsDBNull(colId))
{
item.OID = hSql.Reader.GetInt32(colId);
}
colId = hSql.Reader.GetOrdinal("SRECNO");
if (!hSql.Reader.IsDBNull(colId))
{
item.SRECNO = hSql.Reader.GetInt32(colId);
}
colId = hSql.Reader.GetOrdinal("BILLD");
if (!hSql.Reader.IsDBNull(colId))
{
item.BILLD = hSql.Reader.GetDateTime(colId);
}
colId = hSql.Reader.GetOrdinal("DELD");
if (!hSql.Reader.IsDBNull(colId))
{
item.DELD = hSql.Reader.GetDateTime(colId);
}
colId = hSql.Reader.GetOrdinal("PAIDDATE");
if (!hSql.Reader.IsDBNull(colId))
{
item.PAIDDATE = hSql.Reader.GetDateTime(colId);
}
colId = hSql.Reader.GetOrdinal("PAIDSUM");
if (!hSql.Reader.IsDBNull(colId))
{
item.PAIDSUM = hSql.Reader.GetInt32(colId);
}
colId = hSql.Reader.GetOrdinal("CRERECNO");
if (!hSql.Reader.IsDBNull(colId))
{
item.SRECNO = hSql.Reader.GetInt32(colId);
}
colId = hSql.Reader.GetOrdinal("CUSTNO");
if (!hSql.Reader.IsDBNull(colId))
{
item.CustNo = hSql.Reader.GetInt32(colId);
}
colId = hSql.Reader.GetOrdinal("LNAME");
if (!hSql.Reader.IsDBNull(colId))
{
item.LNAME = hSql.Reader.GetString(colId);
}
colId = hSql.Reader.GetOrdinal("EXPL");
if (!hSql.Reader.IsDBNull(colId))
{
item.EXPL = hSql.Reader.GetString(colId);
}
colId = hSql.Reader.GetOrdinal("SSALID");
if (!hSql.Reader.IsDBNull(colId))
{
item.SSALID = hSql.Reader.GetInt32(colId);
}
colId = hSql.Reader.GetOrdinal("_UNITID");
if (!hSql.Reader.IsDBNull(colId))
{
item.UnitId = hSql.Reader.GetString(colId);
}
colId = hSql.Reader.GetOrdinal("BTYPE");
if (!hSql.Reader.IsDBNull(colId))
{
item.BTYPE = hSql.Reader.GetString(colId);
}
colId = hSql.Reader.GetOrdinal("SMANID");
if (!hSql.Reader.IsDBNull(colId))
{
item.SmanId = hSql.Reader.GetString(colId);
}
colId = hSql.Reader.GetOrdinal("SORDNO");
if (!hSql.Reader.IsDBNull(colId))
{
item.SORDNO = (Int32)hSql.Reader.GetDecimal(colId);
}
colId = hSql.Reader.GetOrdinal("DEPT");
if (!hSql.Reader.IsDBNull(colId))
{
item.DeptId = hSql.Reader.GetString(colId);
}
colId = hSql.Reader.GetOrdinal("InvoiceNo");
if (!hSql.Reader.IsDBNull(colId))
{
item.InvoiceSeqNr = hSql.Reader.GetInt32(colId);
}
colId = hSql.Reader.GetOrdinal("INVSUM");
if (!hSql.Reader.IsDBNull(colId))
{
item.INVSUM = hSql.Reader.GetDecimal(colId);
}
colId = hSql.Reader.GetOrdinal("INVSUM0");
if (!hSql.Reader.IsDBNull(colId))
{
item.INVSUM0 = hSql.Reader.GetDecimal(colId);
}
Result.Add(item);
}
}
catch (Exception ex)
{
_log.Error("ERROR getContractInvoice " + ContractOID + ": ", ex);
throw ex;
}
finally
{
hSql.Close();
}
return(Result);
}
private void addInvoiceText(clsSqlFactory hSql, Contract objContract, ref SCInvoice objInv)
{
int RowId = objInv.InvItems.Count;
List <String> strTexts = new List <String>();
List <String> strPrintingFlags = new List <String>();
List <String> strFieldCodes = new List <String>();
String strSql = "select a.C6,b.C3 from CORW a left join CORW b on b.CODAID='INFOTEXT' and a.C4=b.C1 where a.CODAID='ZSCINVTEXT' and a.V1=1 and a.C3 like '%N%' order by a.V2 ";
hSql.NewCommand(strSql);
hSql.ExecuteReader();
while (hSql.Read())
{
String strTmp = hSql.Reader.GetString(0);
strPrintingFlags.Add(hSql.Reader.GetString(1));
getFieldCodesFromString(strTmp, ref strFieldCodes);
strTexts.Add(strTmp);
}
hSql.NewCommand("exec ZSC_SP_PrintContract " + objContract.ContractOID.ToString());
hSql.ExecuteReader();
if (hSql.Read())
{
int colId = -1;
foreach (String strFieldCode in strFieldCodes)
{
colId = hSql.Reader.GetOrdinal(strFieldCode);
if (colId >= 0)
{
String strDataType = hSql.Reader.GetDataTypeName(colId).ToUpper();
String strValue = "";
if (!hSql.Reader.IsDBNull(colId))
{
switch (strDataType)
{
case "DATETIME":
strValue = hSql.Reader.GetDateTime(colId).ToShortDateString();
break;
case "INT":
strValue = hSql.Reader.GetInt32(colId).ToString();
break;
default:
strValue = hSql.Reader.GetString(colId);
break;
}
for (int i = 0; i < strTexts.Count; i++)
{
strTexts[i] = strTexts[i].Replace("$" + strFieldCode + "$", strValue);
}
}
}
}
for (int i = 0; i < strTexts.Count; i++)
{
RowId++;
SCInvoiceItem objRow = new SCInvoiceItem();
objRow.SROWID = RowId;
objRow.NAME = "";
objRow.RTYPE = 8;
objRow.NOTE = strTexts[i];
objRow.RINFO = strPrintingFlags[i];
objRow.EXIDNO = objContract.ContractNo;
objInv.InvItems.Add(objRow);
}
}
}
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);
}
public bool saveOrder(bool bDraft)
{
bool bRet = true;
clsSqlFactory hSql = new clsSqlFactory();
clsBaseUtility objUtil = new clsBaseUtility();
clsCurrExchange objCurr = new clsCurrExchange();
objCurr.Init();
String strSql = "";
try
{
if (bDraft == false)
{
strSql = "select V1 from " + objUtil.getTable("CORW", UnitId) + " where CODAID='VLASKUTYY' and C1=? ";
bRet = bRet && hSql.NewCommand(strSql);
hSql.Com.Parameters.AddWithValue("BTYPE", BTYPE);
hSql.ExecuteReader();
if (hSql.Read())
{
int NuseId = hSql.Reader.GetInt32(0);
strSql = "update " + objUtil.getTable("NUSE", UnitId) + " set RECNO=RECNO+1 where NUSEID=? ";
bRet = bRet && hSql.NewCommand(strSql);
hSql.Com.Parameters.AddWithValue("NUSEID", NuseId);
hSql.ExecuteNonQuery();
strSql = "select RECNO from " + objUtil.getTable("NUSE", UnitId) + " where NUSEID=? ";
bRet = bRet && hSql.NewCommand(strSql);
hSql.Com.Parameters.AddWithValue("NUSEID", NuseId);
hSql.ExecuteReader();
if (hSql.Read())
{
SRECNO = hSql.Reader.GetInt32(0);
}
else
{
bDraft = true;
}
}
else
{
bDraft = true;
}
}
strSql = "select top 1 isnull(SSALID,0), isnull(SORDNO,0) from " + objUtil.getTable("SSALSEED", UnitId);
bRet = bRet && hSql.ExecuteReader(strSql);
if (bRet && hSql.Read())
{
SSALID = hSql.Reader.GetInt32(0) + 1;
SORDNO = hSql.Reader.GetInt32(1) + 1;
strSql = "update " + objUtil.getTable("SSALSEED", UnitId) + " set SSALID = SSALID + 1, SORDNO = SORDNO + 1 ";
bRet = bRet && hSql.ExecuteNonQuery(strSql);
}
strSql = "insert into " + objUtil.getTable("SSAL", UnitId) +
"(CREATED,SSALID, CUSTNO, SMANID, DEPT, STATUS,RECTYPE, RECORDID, SORDNO, STYPE, " +
" DIVISION, ISDIVIDED, BOPRIOR, OUSRSID, CDTRAN,NOTE,RDATE,EXIDNO,VEHIID)" +
" values (getdate(),?, ?, ?,?, ?, ?, 0, ?, ?, 0, 0, 0, ?, 0, ?,?,?,?) ";
bRet = bRet && hSql.NewCommand(strSql);
hSql.Com.Parameters.AddWithValue("SSALID", SSALID);
hSql.Com.Parameters.AddWithValue("CUSTNO", CustNo);
hSql.Com.Parameters.AddWithValue("SMANID", SmanId);
if (DeptId != null)
{
hSql.Com.Parameters.AddWithValue("DEPT", DeptId);
}
else
{
hSql.Com.Parameters.AddWithValue("DEPT", DBNull.Value);
}
if (bDraft == true)
{
hSql.Com.Parameters.AddWithValue("STATUS", 'A');
hSql.Com.Parameters.AddWithValue("RECTYPE", DBNull.Value);
}
else
{
hSql.Com.Parameters.AddWithValue("STATUS", 'S');
if (BTYPE == "0")
{
hSql.Com.Parameters.AddWithValue("RECTYPE", 'E');
}
else
{
hSql.Com.Parameters.AddWithValue("RECTYPE", 'N');
}
}
hSql.Com.Parameters.AddWithValue("SORDNO", SORDNO);
hSql.Com.Parameters.AddWithValue("STYPE", SalesType);
hSql.Com.Parameters.AddWithValue("OUSRSID", OUserId);
hSql.Com.Parameters.AddWithValue("NOTE", Note);
hSql.Com.Parameters.AddWithValue("RDATE", DELD.ToString("yyyy-MM-dd"));
hSql.Com.Parameters.AddWithValue("EXIDNO", ExtOrderId);
hSql.Com.Parameters.AddWithValue("VEHIID", VehiId);
bRet = bRet && hSql.ExecuteNonQuery();
strSql = "insert into " + objUtil.getTable("SBIL", UnitId) +
"(SSALID, SRECNO, CUSTNO, LCUSTNO, DCUSTNO, " +
" REFE, DDEL, DWP, PDEL," +
" CURRCD, BTYPE,BILLD,BILLD_WITH_TIME,HSMANID,DELD,TPCODE,TPTIME)" +
" values (?, ?, ?, ?, ?," +
" ?, 0, 0, 1, " +
"?,?,?,?,?,?,?,?) ";
bRet = bRet && hSql.NewCommand(strSql);
hSql.Com.Parameters.AddWithValue("SSALID", SSALID);
hSql.Com.Parameters.AddWithValue("SRECNO", SRECNO);
hSql.Com.Parameters.AddWithValue("CUSTNO", CustNo);
hSql.Com.Parameters.AddWithValue("LCUSTNO", CustNo);
hSql.Com.Parameters.AddWithValue("DCUSTNO", DCustNo);
hSql.Com.Parameters.AddWithValue("REFE", Note.Length > 45 ? Note.Substring(0, 45) : Note);
hSql.Com.Parameters.AddWithValue("CURRCD", objCurr.BaseCurrency);
hSql.Com.Parameters.AddWithValue("BTYPE", BTYPE);
if (bDraft == true)
{
hSql.Com.Parameters.AddWithValue("BILLD", DBNull.Value);
hSql.Com.Parameters.AddWithValue("BILLD_WITH_TIME", DBNull.Value);
hSql.Com.Parameters.AddWithValue("HSMANID", DBNull.Value);
hSql.Com.Parameters.AddWithValue("DELD", DBNull.Value);
}
else
{
hSql.Com.Parameters.AddWithValue("BILLD", new DateTime(BILLD.Year, BILLD.Month, BILLD.Day));
hSql.Com.Parameters.AddWithValue("BILLD_WITH_TIME", DateTime.Now);
hSql.Com.Parameters.AddWithValue("HSMANID", SmanId);
hSql.Com.Parameters.AddWithValue("DELD", new DateTime(DELD.Year, DELD.Month, DELD.Day));
}
hSql.Com.Parameters.AddWithValue("TPCODE", TPCODE);
hSql.Com.Parameters.AddWithValue("TPTIME", TPTIME);
bRet = bRet && hSql.ExecuteNonQuery();
strSql = "update a set a.AGRP = b.AGRP, a.CUSTNAME = ltrim(isnull(b.FNAME,'')+' ' +isnull(b.LNAME,'')) from " +
objUtil.getTable("SBIL", UnitId) + " a, CUST b where a.SSALID=? and a.CUSTNO = b.CUSTNO ";
bRet = bRet && hSql.NewCommand(strSql);
hSql.Com.Parameters.AddWithValue("SSALID", SSALID);
bRet = bRet && hSql.ExecuteNonQuery();
strSql = "update a set a.DADDR1 = b.ADDR1, a.DADDR2 = b.ADDR2,a.DCTRYCD=b.CTRYCD,a.DCOUNTRY=b.COUNTRY," +
" a.DNAME= ltrim(isnull(b.FNAME,'')+' ' +isnull(b.LNAME,'')), a.DPO=b.PO,a.DPOSTCD=b.POSTCD,a.DADDR2E=b.ADDR2E from " +
objUtil.getTable("SBIL", UnitId) + " a, CUST b where a.SSALID=? and a.DCUSTNO = b.CUSTNO ";
bRet = bRet && hSql.NewCommand(strSql);
hSql.Com.Parameters.AddWithValue("SSALID", SSALID);
bRet = bRet && hSql.ExecuteNonQuery();
strSql = "insert into ZSC_ContractInvoice(ContractOID,InvoiceNo,SSALID,UnitId,InvoiceType,Created,Modified) select " +
"?,?,SSALID,?,0,getdate(),getdate() from " + objUtil.getTable("SBIL", UnitId) + " where SSALID=?";
bRet = bRet && hSql.NewCommand(strSql);
hSql.Com.Parameters.AddWithValue("ContractOID", ContractOID);
hSql.Com.Parameters.AddWithValue("InvoiceNo", InvoiceSeqNr);
hSql.Com.Parameters.AddWithValue("UnitId", UnitId);
hSql.Com.Parameters.AddWithValue("SSALID", SSALID);
bRet = bRet && hSql.ExecuteNonQuery();
strSql = "update ZSC_Contract set LastInvoiceDate = ?,NextInvoiceDate=? where OID=?";
bRet = bRet && hSql.NewCommand(strSql);
hSql.Com.Parameters.AddWithValue("CBILLD", CBILLD);
hSql.Com.Parameters.AddWithValue("NBILLD", NBILLD);
hSql.Com.Parameters.AddWithValue("ContractOID", ContractOID);
bRet = bRet && hSql.ExecuteNonQuery();
clsTaxHandling objTax = new clsTaxHandling();
objTax.Init(6);
foreach (SCInvoiceItem objRow in InvItems)
{
strSql = "insert into " + objUtil.getTable("SROW", UnitId) + "(CREATED,SSALID, SROWID, SRECNO, SMANID, ITEMNO, SUPLNO, BUYPR, " +
" DISCPC, ITEM, SUPL, NAME, NUM, RNO, RTYPE, RSUM, UNITPR, VATCD, " +
" ONDEDNUM, ORDNUM, IGROUPID,NOTE,RINFO,EXIDNO) values (getdate(),?,?,?,?,?,?,?," +
" ?,?,?,?,?,?,?,?,?,?," +
"0,?,?,?,?,?)";
bRet = bRet && hSql.NewCommand(strSql);
hSql.Com.Parameters.AddWithValue("SSALID", SSALID);
hSql.Com.Parameters.AddWithValue("SROWID", DBNull.Value);
hSql.Com.Parameters.AddWithValue("SRECNO", SRECNO);
hSql.Com.Parameters.AddWithValue("SMANID", SmanId);
hSql.Com.Parameters.AddWithValue("ITEMNO", DBNull.Value);
hSql.Com.Parameters.AddWithValue("SUPLNO", DBNull.Value);
hSql.Com.Parameters.AddWithValue("BUYPR", DBNull.Value);
hSql.Com.Parameters.AddWithValue("DISCPC", DBNull.Value);
hSql.Com.Parameters.AddWithValue("ITEM", DBNull.Value);
hSql.Com.Parameters.AddWithValue("SUPL", DBNull.Value);
hSql.Com.Parameters.AddWithValue("NAME", DBNull.Value);
hSql.Com.Parameters.AddWithValue("NUM", DBNull.Value);
hSql.Com.Parameters.AddWithValue("RNO", DBNull.Value);
hSql.Com.Parameters.AddWithValue("RTYPE", DBNull.Value);
hSql.Com.Parameters.AddWithValue("RSUM", DBNull.Value);
hSql.Com.Parameters.AddWithValue("UNITPR", DBNull.Value);
hSql.Com.Parameters.AddWithValue("VATCD", DBNull.Value);
hSql.Com.Parameters.AddWithValue("ORDNUM", DBNull.Value);
hSql.Com.Parameters.AddWithValue("IGROUPID", DBNull.Value);
hSql.Com.Parameters.AddWithValue("NOTE", DBNull.Value);
hSql.Com.Parameters.AddWithValue("RINFO", DBNull.Value);
hSql.Com.Parameters.AddWithValue("EXIDNO", DBNull.Value);
if (objRow.RTYPE != 8)
{
hSql.Com.Parameters["SROWID"].Value = objRow.SROWID;
hSql.Com.Parameters["ITEMNO"].Value = objRow.ITEMNO;
hSql.Com.Parameters["SUPLNO"].Value = objRow.SUPLNO;
hSql.Com.Parameters["BUYPR"].Value = objRow.BUYPR;
hSql.Com.Parameters["DISCPC"].Value = objRow.DISCPC;
hSql.Com.Parameters["ITEM"].Value = objRow.ITEMNO;
hSql.Com.Parameters["SUPL"].Value = objRow.SUPLNO;
hSql.Com.Parameters["NAME"].Value = objRow.NAME;
hSql.Com.Parameters["NUM"].Value = objRow.NUM;
hSql.Com.Parameters["RNO"].Value = objRow.SROWID;
hSql.Com.Parameters["RTYPE"].Value = objRow.RTYPE;
hSql.Com.Parameters["RSUM"].Value = objRow.RSUM;
hSql.Com.Parameters["UNITPR"].Value = objRow.UNITPR;
hSql.Com.Parameters["VATCD"].Value = objRow.VATCD;
hSql.Com.Parameters["ORDNUM"].Value = objRow.NUM;
if (objRow.IGROUPID > 0)
{
hSql.Com.Parameters["IGROUPID"].Value = objRow.IGROUPID;
}
hSql.Com.Parameters["EXIDNO"].Value = objRow.EXIDNO;
}
else
{
hSql.Com.Parameters["SROWID"].Value = objRow.SROWID;
hSql.Com.Parameters["NAME"].Value = objRow.NAME;
hSql.Com.Parameters["RNO"].Value = objRow.SROWID;
hSql.Com.Parameters["RTYPE"].Value = objRow.RTYPE;
hSql.Com.Parameters["NOTE"].Value = objRow.NOTE;
if ((objRow.RINFO != null) && (objRow.RINFO != ""))
{
hSql.Com.Parameters["RINFO"].Value = objRow.RINFO;
}
hSql.Com.Parameters["EXIDNO"].Value = objRow.EXIDNO;
}
hSql.ExecuteNonQuery();
}
hSql.Commit();
}
catch (Exception ex)
{
hSql.Rollback();
throw ex;
}
finally
{
hSql.Close();
}
return(bRet);
}
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 static DataTable searchCustomers(string namephrase)
{
_log.Debug("searchCustomers >> " + namephrase);
List <CashRegCust> Results = new List <CashRegCust>();
clsBaseUtility locObjUtil = new clsBaseUtility();
clsSqlFactory hSql = new clsSqlFactory();
DataTable retDataTable = new DataTable();
bool bRet = true;
try
{
string searchString = namephrase;
RegexOptions options = RegexOptions.None;
Regex regex = new Regex(@"[ ]{2,}", options);
searchString = regex.Replace(searchString, @" ");
string[] words = searchString.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
int i = 0;
string strSql = "";
string strOrder = "";
string strCUST = locObjUtil.getTable("CUST");
string strFTVIEW_CUST = (strCUST == "CUST")?"FTVIEW_CUST":locObjUtil.getTable("FTVIEW_CUST");
strSql = "select top " + ConfigurationManager.AppSettings["SearchResultNumber"].ToString() + " isnull(a.CUSTNO,'') as CUSTNO,isnull(a.LNAME,'') as LNAME,isnull(a.FNAME,'') as FNAME,isnull(a.SPINNR,'') as SPINNR, " +
" isnull(replace(a.WTEL,'/',''),'') +'/'+ isnull(replace(a.HTEL,'/',''),'') +'/'+ isnull(replace(a.Tel3,'/',''),'') as PHONES, " +
" isnull(a.EMAIL,'') as EMAIL, " +
" isnull(a.PO,'')+' '+ isnull(a.POSTCD,'')+' '+isnull(a.ADDR2,'')+' '+isnull(a.ADDR2E,'')+' '+isnull(a.ADDR1,'') as ADDRESS " +
" from " + strCUST + " a ";
while (i < words.Length)
{
if (words[i] != "")
{
strSql += " inner join containstable(" + strFTVIEW_CUST + ", *, '\"" + words[i] + "*\"' ) as T" + i.ToString() + " on a._OID = T" + i.ToString() + ".[KEY] ";
if (i > 0)
{
strOrder += "+";
}
strOrder += "T" + i.ToString() + ".RANK";
}
i++;
}
strSql = strSql + " where a.CUSTNO >0 ";
if (strOrder != "")
{
strOrder = " order by " + strOrder + " desc ";
}
if (words.Length > 0)
{
strSql += strOrder;
}
_log.Debug(strSql);
bRet = hSql.NewCommand(strSql);
using (OdbcConnection connection =
new OdbcConnection(new clsGlobalVariable().getConnectionString()))
{
OdbcDataAdapter adapter =
new OdbcDataAdapter(strSql, connection);
// Open the connection and fill the DataSet.
try
{
connection.Open();
adapter.Fill(retDataTable);
}
catch (Exception ex)
{
_log.Error(ex.ToString());
throw ex;
}
}
}
catch (Exception ex)
{
_log.Error(ex.ToString());
throw ex;
}
finally
{
hSql.Close();
}
_log.Debug("searchCustomers <<");
return(retDataTable);
}
public static bool saveRemark(int ContractOID, List <SCContractRemark> lstData, clsSqlFactory hSql)
{
if (lstData == null)
{
return(true);
}
bool bRet = true;
try
{
clsGlobalVariable objGlobal = new clsGlobalVariable();
foreach (SCContractRemark data in lstData)
{
if (data.OID > 0)
{
if (data.isMarkDeleted == true)
{
bRet = hSql.NewCommand("delete from ZSC_ContractRemark where OID=?");
hSql.Com.Parameters.AddWithValue("OID", data.OID);
bRet = bRet && hSql.ExecuteNonQuery();
}
else
{
bRet = hSql.NewCommand("update ZSC_ContractRemark set UserId=?,RemarkType=?,Info=?, Modified=getdate() where OID=?");
hSql.Com.Parameters.AddWithValue("UserId", objGlobal.DMSFirstUserName);
hSql.Com.Parameters.AddWithValue("RemarkType", data.RemarkType);
hSql.Com.Parameters.AddWithValue("Info", data.Info);
hSql.Com.Parameters.AddWithValue("OID", data.OID);
bRet = bRet && hSql.ExecuteNonQuery();
}
}
else
{
bRet = hSql.NewCommand("INSERT INTO ZSC_ContractRemark(ContractOID,Created,UserId,RemarkType,Info,Modified) VALUES(?,getdate(),?,?,?,getdate())");
hSql.Com.Parameters.AddWithValue("ContractOID", ContractOID);
hSql.Com.Parameters.AddWithValue("UserId", objGlobal.DMSFirstUserName);
hSql.Com.Parameters.AddWithValue("RemarkType", data.RemarkType);
hSql.Com.Parameters.AddWithValue("Info", data.Info);
bRet = bRet && hSql.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
_log.Error("ERROR saveRemark " + ContractOID + ": ", ex);
throw ex;
}
return(bRet);
}
