private StockinReturnSearchData GetData() { StockinReturnSearchData data = new StockinReturnSearchData(); data.CODE = this.txtCode.Text.Trim(); data.DATEFROM = this.ctlDateFrom.DateValue; data.DATETO = this.ctlDateTo.DateValue; //data.PRODUCT = Convert.ToDouble(this.cmbProduct.SelectedItem.Value); data.PRODUCTNAME = this.txtProduct.Text.Trim(); data.RQCODE = this.txtRQCode.Text.Trim(); data.STATUSFROM = this.cmbStatusFrom.SelectedItem.Value; data.STATUSTO = this.cmbStatusTo.SelectedItem.Value; return data; }
private StockinReturnSearchData GetData() { StockinReturnSearchData data = new StockinReturnSearchData(); data.CODE = this.txtCode.Text.Trim(); data.DATEFROM = this.ctlDateFrom.DateValue; data.DATETO = this.ctlDateTo.DateValue; data.DOCTYPE = Convert.ToDouble(this.cmbDocType.SelectedItem.Value); data.CUSTOMER = Convert.ToDouble(this.cmbCustomer.SelectedItem.Value); data.NAME = this.txtCName.Text.Trim(); data.LNAME = this.txtCLName.Text.Trim(); data.STATUSFROM = this.cmbStatusFrom.SelectedItem.Value; data.STATUSTO = this.cmbStatusTo.SelectedItem.Value; return data; }
public DataTable GetItemList(StockinReturnSearchData data) { string whereString = ""; if (data.DOCTYPE != 0) whereString += (whereString == "" ? "" : "AND ") + "DOCTYPE = " + data.DOCTYPE.ToString() + " "; if (data.CODE.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "UPPER(CODE) LIKE '%" + data.CODE.Trim().ToUpper() + "%' "; if (data.DATEFROM.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "RECEIVEDATE >= " + OracleDB.QRDate(data.DATEFROM) + " "; if (data.DATETO.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "RECEIVEDATE <= " + OracleDB.QRDate(data.DATETO) + " "; //if (data.CUSTOMER != 0) // whereString += (whereString == "" ? "" : "AND ") + "SENDER = " + data.CUSTOMER.ToString() + " "; if (data.NAME.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "UPPER(CNAME) LIKE '%" + data.NAME.Trim().ToUpper() + "%' "; if (data.LNAME.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "UPPER(CLASTNAME) LIKE '%" + data.LNAME.Trim().ToUpper() + "%' "; if (data.STATUSFROM.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "RANK >= '" + OracleDB.QRText(data.STATUSFROM.Trim()) + "' "; if (data.STATUSTO.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "RANK <= '" + OracleDB.QRText(data.STATUSTO.Trim()) + "' "; string sql = "select * from ( SELECT ROWNUM NO, ST.LOID, ST.DOCTYPE, VF.DOCNAME, ST.CODE, ST.RECEIVEDATE, ST.CREATEBY, ST.SENDER, VC.CUSTOMERNAME, ST.GRANDTOT,VC.CNAME,VC.CLASTNAME, "; sql += "CASE ST.STATUS WHEN '" + Constz.StockinReturn.Status.Waiting.Code + "' THEN '" + Constz.StockinReturn.Status.Waiting.Name + "' "; sql += "WHEN '" + Constz.StockinReturn.Status.Approved.Code + "' THEN '" + Constz.StockinReturn.Status.Approved.Name + "' "; sql += "WHEN '" + Constz.StockinReturn.Status.Void.Code + "' THEN '" + Constz.StockinReturn.Status.Void.Name + "' "; sql += "ELSE '' END AS STATUSNAME, "; sql += "CASE ST.STATUS WHEN '" + Constz.StockinReturn.Status.Waiting.Code + "' THEN '" + Constz.StockinReturn.Status.Waiting.Rank + "' "; sql += "WHEN '" + Constz.StockinReturn.Status.Approved.Code + "' THEN '" + Constz.StockinReturn.Status.Approved.Rank + "' "; sql += "WHEN '" + Constz.StockinReturn.Status.Void.Code + "' THEN '" + Constz.StockinReturn.Status.Void.Rank + "' "; sql += "ELSE '' END AS RANK "; sql += "FROM STOCKIN ST INNER JOIN V_RETURNTYPE_FG VF ON ST.DOCTYPE = VF.DOCTYPE "; sql += "INNER JOIN V_CUSTOMER VC ON ST.SENDER = VC.LOID) "; sql += (whereString == "" ? "" : "WHERE " + whereString); sql += "ORDER BY CODE "; DataTable dt = OracleDB.ExecListCmd(sql); for (int i = 0; i < dt.Rows.Count; ++i) { dt.Rows[i]["NO"] = i + 1; } return dt; }
public DataTable GetReturnWHList(StockinReturnSearchData data) { string whereString = ""; if (data.PRODUCTNAME.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "UPPER(PRODUCTNAME) LIKE '%" + data.PRODUCTNAME.Trim().ToUpper() + "%' "; if (data.CODE.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "UPPER(CODE) =" + data.CODE.Trim().ToUpper() + " "; if (data.RQCODE.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "UPPER(REQCODE) =" + data.RQCODE.Trim().ToUpper() + " "; if (data.DATEFROM.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "RECEIVEDATE >= " + OracleDB.QRDate(data.DATEFROM) + " "; if (data.DATETO.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "RECEIVEDATE <= " + OracleDB.QRDate(data.DATETO) + " "; if (data.STATUSFROM.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "RANK >= '" + OracleDB.QRText(data.STATUSFROM.Trim()) + "' "; if (data.STATUSTO.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "RANK <= '" + OracleDB.QRText(data.STATUSTO.Trim()) + "' "; string sql = "select * from ( SELECT ROWNUM NO,ST.LOID, ST.CODE, ST.RECEIVEDATE, VM.PDLOID, VM.PRODUCTNAME, VM.PDQTY, VM.UNITNAME,VM.LOTNO, VM.REQCODE, "; 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 V_MATERIAL_RETURN_POPUP_LIST VM ON ST.REFTABLE = 'REQUISITION' AND ST.REFLOID = VM.LOID) A "; sql += (whereString == "" ? "" : "WHERE " + whereString); sql += "ORDER BY RECEIVEDATE "; DataTable dt = OracleDB.ExecListCmd(sql); for (int i = 0; i < dt.Rows.Count; ++i) { dt.Rows[i]["NO"] = i + 1; } return dt; }