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