private PurchaseRequestSearchData GetData()
{
PurchaseRequestSearchData data = new PurchaseRequestSearchData();
data.CODEFROM = this.txtCodeFrom.Text.Trim();
data.CODETO = this.txtCodeTo.Text.Trim();
data.DATEFROM = this.ctlDateFrom.DateValue;
data.DATETO = this.ctlDateTo.DateValue;
data.PURCHASETYPE = Convert.ToDouble(this.cmbPurchaseType.SelectedItem.Value);
data.PRODUCTNAME = this.txtProductName.Text.Trim();
data.DIVISION = Authz.CurrentUserInfo.DivisionID;
data.STATUSFROM = this.cmbStatusFrom.SelectedItem.Value;
data.STATUSTO = this.cmbStatusTo.SelectedItem.Value;
data.STATUSPRFROM = this.cmbStatusPRFrom.SelectedItem.Value;
data.STATUSPRTO = this.cmbStatusPRTo.SelectedItem.Value;
data.STATUSPOFROM = this.cmbStatusPOFrom.SelectedItem.Value;
data.STATUSPOTO = this.cmbStatusPOTo.SelectedItem.Value;
return data;
}
public DataTable GetPDRequestList(PurchaseRequestSearchData data)
{
string whereString = "";
string wherePrStatusPc = "";
string wherePrStatusOt = "";
if (data.DIVISION != Constz.AdminDepartment.LOID && data.DIVISION != Constz.PurchaseDepartment.LOID)
whereString += (whereString == "" ? "" : "AND ") + "DIVISION = " + data.DIVISION.ToString() + " ";
if (data.CODEFROM.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "UPPER(PRCODE) >= '" + OracleDB.QRText(data.CODEFROM.Trim()).ToUpper() + "' ";
if (data.CODETO.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "UPPER(PRCODE) <= '" + OracleDB.QRText(data.CODETO.Trim()).ToUpper() + "' ";
if (data.DATEFROM.Year != 1)
whereString += (whereString == "" ? "" : "AND ") + "REQUESTDATE >= " + OracleDB.QRDate(data.DATEFROM) + " ";
if (data.DATETO.Year != 1)
whereString += (whereString == "" ? "" : "AND ") + "REQUESTDATE <= " + OracleDB.QRDate(data.DATETO) + " ";
if (data.PURCHASETYPE != 0)
whereString += (whereString == "" ? "" : "AND ") + "PURCHASETYPE = " + data.PURCHASETYPE.ToString() + " ";
if (data.PRODUCTNAME.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "UPPER(PRODUCTNAME) LIKE '%" + data.PRODUCTNAME.Trim().ToUpper() + "%' ";
if (data.STATUSFROM.Trim() != "0")
whereString += (whereString == "" ? "" : "AND ") + "STSTATUSRANK >= " + OracleDB.QRText(data.STATUSFROM.Trim()) + " ";
if (data.STATUSTO.Trim() != "0")
whereString += (whereString == "" ? "" : "AND ") + "STSTATUSRANK <= " + OracleDB.QRText(data.STATUSTO.Trim()) + " ";
if (data.STATUSPRFROM.Trim() != "0")
wherePrStatusOt += (wherePrStatusOt == "" ? "" : "AND ") + "PRSTATUSRANK >= " + OracleDB.QRText(data.STATUSPRFROM.Trim()) + " ";
if (data.STATUSPRTO.Trim() != "0")
wherePrStatusOt += (wherePrStatusOt == "" ? "" : "AND ") + "PRSTATUSRANK <= " + OracleDB.QRText(data.STATUSPRTO.Trim()) + " ";
//¡Ã³Õ½èÒ¨Ѵ«×éÍ ÊÒÁÒö¤é¹ËÒ㺠PR ʶҹÐÊè§ãËé¨Ñ´«×éÍ, ͹ØÁѵÔ, ¡àÅÔ¡ ä´é·Ø¡á¼¹¡ áµèʶҹ¡ÓÅѧ´Óà¹Ô¹¡Òäé¹ËÒä´é੾ÒТͧ½èÒ¨Ѵ«×éÍà·èÒ¹Ñé¹
if (data.DIVISION == Constz.PurchaseDepartment.LOID)
{
if (data.STATUSPRFROM.Trim() != "0")
wherePrStatusPc += (wherePrStatusPc == "" ? "" : "AND ") + "PRSTATUSRANK >= " + OracleDB.QRText(data.STATUSPRFROM.Trim()) + " ";
if (data.STATUSPRTO.Trim() != "0")
wherePrStatusPc += (wherePrStatusPc == "" ? "" : "AND ") + "PRSTATUSRANK <= " + OracleDB.QRText(data.STATUSPRTO.Trim()) + " ";
wherePrStatusOt += (wherePrStatusOt == "" ? "" : "AND ") + "PRSTATUSRANK >= " + Constz.Requisition.Status.SP.Rank + " ";
wherePrStatusOt += (wherePrStatusOt == "" ? "" : "AND ") + "PRSTATUSRANK <= " + Constz.Requisition.Status.Void.Rank + " ";
}
if (data.STATUSPOFROM.Trim() != "0")
whereString += (whereString == "" ? "" : "AND ") + "POSTATUSRANK >= " + OracleDB.QRText(data.STATUSPOFROM.Trim()) + " ";
if (data.STATUSPOTO.Trim() != "0")
whereString += (whereString == "" ? "" : "AND ") + "POSTATUSRANK <= " + OracleDB.QRText(data.STATUSPOTO.Trim()) + " ";
string sql = "SELECT ROWNUM NO, PRLOID, PRCODE, REQUESTDATE, PRODUCTNAME, PRQTY, UNITNAME, REQUESTBYNAME, ";
sql += "PURCHASETYPENAME, PRSTATUS, PRSTATUSNAME, POCODE, POQTY, NVL(POSTATUS,'WA') POSTATUS, POSTATUSNAME, STCODE, STQTY, LOTNO, STSTATUSNAME, PRILOID, SENDPODATE, ";
sql += "case when sysdate-REQUESTDATE >= (select configvalue from sysconfig where loid = 6) then 'Y' else 'N' end as REDWA, ";
sql += "case when sysdate-PRAPPROVEDATE >= (select configvalue from sysconfig where loid = 7) then 'Y' else 'N' end as REDAP ";
sql += "FROM V_PRODUCT_PR_SEARCH ";
string sqlPc = sql;
sql += " WHERE DIVISION<>3 " + (whereString == "" ? "" : "AND " + whereString) + " " + (wherePrStatusOt == "" ? "" : "AND " + wherePrStatusOt);
if (data.DIVISION == Constz.PurchaseDepartment.LOID)
{
sql += "UNION " + sqlPc + " WHERE DIVISION=3 " + (wherePrStatusPc == "" ? "" : "AND " + wherePrStatusPc) + " " + (whereString == "" ? "" : "AND " + whereString);
}
sql += "ORDER BY PRCODE DESC, PRODUCTNAME ";
DataTable dt = OracleDB.ExecListCmd(sql);
for (int i = 0; i < dt.Rows.Count; ++i)
{
dt.Rows[i]["NO"] = i + 1;
}
//string sql = "SELECT ROWNUM NO, PRLOID, PRCODE, REQUESTDATE, PRODUCTNAME, PRQTY, UNITNAME, REQUESTBYNAME, ";
//sql += "PURCHASETYPENAME, PRSTATUS, PRSTATUSNAME, POCODE, POQTY, NVL(POSTATUS,'WA') POSTATUS, POSTATUSNAME, STCODE, STQTY, LOTNO, STSTATUSNAME, PRILOID, SENDPODATE, ";
//sql += "case when sysdate-REQUESTDATE >= (select configvalue from sysconfig where loid = 6) then 'Y' else 'N' end as REDWA, ";
//sql += "case when sysdate-PRAPPROVEDATE >= (select configvalue from sysconfig where loid = 7) then 'Y' else 'N' end as REDAP ";
//sql += "FROM V_PRODUCT_PR_SEARCH ";
//sql += (whereString == "" ? "" : "WHERE " + whereString);
//sql = "SELECT PR.RANK, B.* FROM (SELECT ROWNUM RANK, PRLOID FROM (SELECT DISTINCT A.PRLOID, A.PRCODE FROM (" + " SELECT PRLOID, PRCODE FROM V_PRODUCT_PR_SEARCH " + (whereString == "" ? "" : "WHERE " + whereString) + ") A ORDER BY A.PRCODE DESC) ) PR INNER JOIN (" + sql + ") B ON PR.PRLOID = B.PRLOID ";
//sql += "ORDER BY B.PRCODE DESC, B.PRODUCTNAME ";
return dt;
}