private SearchSaleData GetData()
{
SearchSaleData data = new SearchSaleData();
data.REQDATEFROM = dtpDateFrom.Value;
data.REQDATETO = dtpDateTo.Value;
data.CODEFROM = this.txtCodeFrom.Text.Trim();
data.CODETO = this.txtCodeTo.Text.Trim();
data.CUSTOMER = Convert.ToDouble(this.cmbCustomer.SelectedValue);
data.PRODUCT = Convert.ToDouble(this.cmbProduct.SelectedValue);
return data;
}
private SearchSaleData GetData()
{
SearchSaleData data = new SearchSaleData();
data.REQDATEFROM = dtpDateFrom.Value;
data.REQDATETO = dtpDateTo.Value;
data.CODEFROM = this.txtCodeFrom.Text.Trim();
data.CODETO = this.txtCodeTo.Text.Trim();
data.CUSTOMERNAME = this.txtCustomerName.Text.Trim();
data.PRODUCT = Convert.ToDouble(this.cmbProduct.SelectedValue);
data.STATUS = Constz.Requisition.Status.Void.Code;
return data;
}
public void SetData(SearchSaleData data)
{
if (data.REQDATEFROM.Year != 1)
this.dtpDateFrom.Value = data.REQDATEFROM;
else
this.dtpDateFrom.Value = DateTime.Now.Date;
if (data.REQDATETO.Year != 1)
this.dtpDateTo.Value = data.REQDATETO;
else
this.dtpDateTo.Value = DateTime.Now.Date;
this.txtCodeFrom.Text = data.CODEFROM.Trim();
this.txtCodeTo.Text = data.CODETO.Trim();
this.txtCustomerName.Text = data.CUSTOMERNAME.Trim();
this.cmbProduct.SelectedValue = data.PRODUCT;
}
public static DataTable GetInvList(SearchSaleData data)
{
string where = "";
if (data.CUSTOMERNAME.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(C.NAME || ' ' || C.LASTNAME) LIKE '%" + OracleDB.QRText(data.CUSTOMERNAME) + "%' ";
if (data.PRODUCT != 0)
where += (where == "" ? "" : "AND ") + "RQI.PRODUCT = " + data.PRODUCT.ToString() + " ";
if (data.CUSTOMER != 0)
where += (where == "" ? "" : "AND ") + "RQ.CUSTOMER = " + data.CUSTOMER.ToString() + " ";
if (data.INVCODE.Trim() != "")
where += (where == "" ? "" : "AND ") + "RQ.INVCODE = '" + data.INVCODE.ToString() + " ' ";
string sql = "SELECT RQ.LOID, RQ.REQDATE, RQ.CODE, TITLE.NAME || RQ.CNAME || ' ' || RQ.CLASTNAME AS CUSTOMERNAME, ";
sql += "RQ.TOTDIS, RQ.TOTAL, RQ.TOTVAT, RQ.GRANDTOT, RQ.INVCODE, C.CODE AS CUSTOMERCODE ";
sql += "FROM REQUISITION RQ INNER JOIN CUSTOMER C ON RQ.CUSTOMER = C.LOID ";
sql += "INNER JOIN REQUISITIONITEM RQI INNER JOIN PRODUCT P ON RQI.PRODUCT = P.LOID ";
sql += "ON RQ.LOID = RQI.REQUISITION LEFT JOIN TITLE ON TITLE.LOID = C.TITLE ";
sql += (where == "" ? "" : "WHERE " + where);
sql += "ORDER BY RQ.INVCODE ";
return OracleDB.ExecListCmd(sql);
}
public static DataTable GetSaleList2(SearchSaleData data)
{
string where = "RQ.REQUISITIONTYPE = " + Constz.Requisition.RequisitionType.REQ13.ToString() + " ";
if (data.REQDATEFROM.Year != 1)
where += (where == "" ? "" : "AND ") + "TO_CHAR(REQDATE, 'YYYYMMDD') >= '" + data.REQDATEFROM.Year.ToString() + data.REQDATEFROM.ToString("MMdd") + "' ";
if (data.REQDATETO.Year != 1)
where += (where == "" ? "" : "AND ") + "TO_CHAR(REQDATE, 'YYYYMMDD') <= '" + data.REQDATETO.Year.ToString() + data.REQDATETO.ToString("MMdd") + "' ";
if (data.CODEFROM.Trim() != "")
where += (where == "" ? "" : "AND ") + "RQ.CODE = '" + OracleDB.QRText(data.CODEFROM) + "' ";
if (data.CODETO.Trim() != "")
where += (where == "" ? "" : "AND ") + "RQ.CODE <= '" + OracleDB.QRText(data.CODETO) + "' ";
if (data.CUSTOMERNAME.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(C.NAME || ' ' || C.LASTNAME) LIKE '%" + OracleDB.QRText(data.CUSTOMERNAME) + "%' ";
if (data.PRODUCT != 0)
where += (where == "" ? "" : "AND ") + "RQI.PRODUCT = " + data.PRODUCT.ToString() + " ";
if (data.CUSTOMER != 0)
where += (where == "" ? "" : "AND ") + "RQ.CUSTOMER = " + data.CUSTOMER.ToString() + " ";
if (data.INVCODE.Trim() != "")
where += (where == "" ? "" : "AND ") + "RQ.INVCODE = " + data.INVCODE.ToString() + " ";
if (data.STATUS.Trim() != "")
where += (where == "" ? "" : "AND ") + "RQ.STATUS = '" + data.STATUS.Trim() + "' ";
string sql = "SELECT DISTINCT RQ.LOID, RQ.REQDATE, RQ.CODE, TITLE.NAME || C.NAME || ' ' || C.LASTNAME AS CUSTOMERNAME, RQ.REFNO, RQ.CUSTOMER, ";
sql += "RQ.TOTDIS, RQ.TOTAL, RQ.TOTVAT, RQ.GRANDTOT, RQ.INVCODE, C.CODE AS CUSTOMERCODE ";
sql += "FROM REQUISITION RQ INNER JOIN CUSTOMER C ON RQ.CUSTOMER = C.LOID ";
sql += "INNER JOIN REQUISITIONITEM RQI ON RQ.LOID = RQI.REQUISITION ";
sql += "LEFT JOIN TITLE ON TITLE.LOID = C.TITLE ";
sql += (where == "" ? "" : "WHERE " + where);
sql += "ORDER BY RQ.REQDATE, RQ.CODE, TITLE.NAME || C.NAME || ' ' || C.LASTNAME ";
return OracleDB.ExecListCmd(sql);
}
public DataTable GetInvList(SearchSaleData data)
{
return SearchDAL.GetInvList(data);
}
public DataTable GetSaleList2(SearchSaleData data)
{
return SearchDAL.GetSaleList2(data);
}