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