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 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 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);
}
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);
}
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);
}