private StockinProductSearchData GetData() { StockinProductSearchData data = new StockinProductSearchData(); data.PRODUCETYPE = Request["producetype"]; data.CODEFROM = this.txtCodeFrom.Text.Trim(); data.CODETO = this.txtCodeTo.Text.Trim(); data.DATEFROM = this.ctlDateFrom.DateValue; data.DATETO = this.ctlDateTo.DateValue; data.PRODUCTNAME = this.txtProductName.Text.Trim(); data.LOTNOFROM = this.txtLotNoFrom.Text.Trim(); data.LOTNOTO = this.txtLotNoTo.Text.Trim(); data.CREATEONFROM = this.ctlProduceDateFrom.DateValue; data.CREATEONTO = this.ctlProduceDateTo.DateValue; data.WAREHOUSE = Authz.CurrentUserInfo.Warehouse; return data; }
public DataTable GetStockInListFG(StockinProductSearchData data) { string whereString = " RECEIVER = " + data.WAREHOUSE + " AND DOCTYPE=13 "; if (data.CODEFROM.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "CODE >= '" + OracleDB.QRText(data.CODEFROM.Trim()) + "' "; if (data.CODETO.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "CODE <= '" + OracleDB.QRText(data.CODETO.Trim()) + "' "; if (data.DATEFROM.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "CREATEON >= " + OracleDB.QRDate(data.DATEFROM) + " "; if (data.DATETO.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "CREATEON <= " + OracleDB.QRDate(data.DATETO) + " "; if (data.PRODUCTNAME.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "PRODUCTNAME = '" + OracleDB.QRText(data.PRODUCTNAME.Trim()) + "' "; if (data.LOTNOFROM.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "LOTNO >= '" + OracleDB.QRText(data.LOTNOFROM.Trim()) + "' "; if (data.LOTNOTO.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "LOTNO <= '" + OracleDB.QRText(data.LOTNOTO.Trim()) + "' "; if (data.CREATEONFROM.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "CREATEON >= " + OracleDB.QRDate(data.CREATEONFROM) + " "; if (data.CREATEONTO.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "CREATEON <= " + OracleDB.QRDate(data.CREATEONTO) + " "; if (data.PRODUCETYPE.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "PRODUCETYPE = '" + OracleDB.QRText(data.PRODUCETYPE.Trim()) + "' "; string sql = "select ROWNUM NO, a.* from (SELECT DISTINCT ST.LOID LOID,ST.CODE CODE,ST.CREATEON CREATEON,ST.RECEIVER,PPD.PRODUCETYPE,PPD.LOTNO,PD.NAME PRODUCTNAME,ST.DOCTYPE, "; sql += "CASE ST.STATUS WHEN '" + Constz.Requisition.Status.Waiting.Code + "' THEN '" + Constz.Requisition.Status.Waiting.Name + "' "; sql += "WHEN '" + Constz.Requisition.Status.Finish.Code + "' THEN '" + Constz.Requisition.Status.Finish.Name + "' "; sql += "ELSE '' END AS STATUSNAME, "; sql += "CASE ST.STATUS WHEN '" + Constz.Requisition.Status.Waiting.Code + "' THEN '" + Constz.Requisition.Status.Waiting.Rank + "' "; sql += "WHEN '" + Constz.Requisition.Status.Finish.Code + "' THEN '" + Constz.Requisition.Status.Finish.Rank + "' "; sql += "ELSE '' END AS RANK "; sql += "FROM STOCKIN ST INNER JOIN STOCKINITEM SI ON SI.STOCKIN=ST.LOID "; sql += "INNER JOIN PDPRODUCT PPD ON PPD.LOID=SI.REFLOID AND SI.REFTABLE ='PDPRODUCT' "; sql += "INNER JOIN PRODUCT PD ON PPD.PRODUCT=PD.LOID) a "; sql += (whereString == "" ? "" : "WHERE " + whereString); DataTable dt = OracleDB.ExecListCmd(sql); return dt; }