public DataTable GetReserveList(ProductReserveSearchData whereData) { string whereString = ""; if (whereData.CODEFROM.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "UPPER(CODE) >= '" + OracleDB.QRText(whereData.CODEFROM.Trim()).ToUpper() + "' "; if (whereData.CODETO.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "UPPER(CODE) <= '" + OracleDB.QRText(whereData.CODETO.Trim()).ToUpper() + "' "; if (whereData.DATEFROM.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "REQDATE >= " + OracleDB.QRDate(whereData.DATEFROM) + " "; if (whereData.DATETO.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "REQDATE <= " + OracleDB.QRDate(whereData.DATETO) + " "; if (whereData.CUSTOMERNAME.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "UPPER(CUSTOMERNAME) LIKE '%" + OracleDB.QRText(whereData.CUSTOMERNAME.Trim()).ToUpper() + "%' "; if (whereData.STATUSFROM.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "RANK >= " + OracleDB.QRText(whereData.STATUSFROM.Trim()) + " "; if (whereData.STATUSTO.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "RANK <= " + OracleDB.QRText(whereData.STATUSTO.Trim()) + " "; string sql = "SELECT ROWNUM NO, A.* FROM (SELECT RQ.CODE, RQ.LOID, RQ.REQUISITIONTYPE, RQ.REQDATE, RQ.RESERVEDATE, RQ.DUEDATE, CU.NAME || ' ' || CU.LASTNAME AS CUSTOMERNAME, "; sql += "CASE RQ.STATUS WHEN '" + Constz.Requisition.Status.Waiting.Code + "' THEN '" + Constz.Requisition.Status.Waiting.Name + "' "; sql += "WHEN '" + Constz.Requisition.Status.ApproveWH.Code + "' THEN '" + Constz.Requisition.Status.ApproveWH.Name + "' "; sql += "WHEN '" + Constz.Requisition.Status.Finish.Code + "' THEN '" + Constz.Requisition.Status.Finish.Name + "' "; sql += "ELSE '' END AS STATUSNAME, "; sql += "CASE RQ.STATUS WHEN '" + Constz.Requisition.Status.Waiting.Code + "' THEN '" + Constz.Requisition.Status.Waiting.Rank + "' "; sql += "WHEN '" + Constz.Requisition.Status.ApproveWH.Code + "' THEN '" + Constz.Requisition.Status.ApproveWH.Rank + "' "; sql += "WHEN '" + Constz.Requisition.Status.Finish.Code + "' THEN '" + Constz.Requisition.Status.Finish.Rank + "' "; sql += "ELSE '' END AS RANK, RQ.CREATEBY "; sql += "FROM REQUISITION RQ "; sql += "INNER JOIN CUSTOMER CU ON RQ.CUSTOMER = CU.LOID AND RQ.REQUISITIONTYPE = '12') A "; sql += (whereString == "" ? "" : "WHERE " + whereString); sql += "ORDER BY REQDATE, CODE "; return OracleDB.ExecListCmd(sql); }
public DataTable GetRequisitionList(ProductReserveSearchData data) { DataTable dt = SearchDAL.GetReserveList(data); for (int i = 0; i < dt.Rows.Count; ++i) { dt.Rows[i]["NO"] = i + 1; } return dt; }
public DataTable GetProductionOtherList(ProductReserveSearchData data) { DataTable dt = SearchDAL.GetProductionOtherList(data); int i = 1; foreach (DataRow dRow in dt.Rows) { dRow["NO"] = i; i += 1; } return dt; }
private ProductReserveSearchData GetData() { ProductReserveSearchData data = new ProductReserveSearchData(); data.CODEFROM = this.txtCodeFrom.Text.Trim(); data.CODETO = this.txtCodeTo.Text.Trim(); data.CUSTOMERNAME = this.txtName.Text.Trim(); data.RESERVEFROM = this.ctlReserveFrom.DateValue; data.RESERVETO = this.ctlReserveTo.DateValue; data.DATEFROM = this.ctlReqFrom.DateValue; data.DATETO = this.ctlReqTo.DateValue; data.REQUISITIONTYPE = Convert.ToDouble(this.cmbRequisitionType.SelectedItem.Value); data.STATUSFROM = this.cmbStatusFrom.SelectedItem.Value; data.STATUSTO = this.cmbStatusTo.SelectedItem.Value; return data; }
private bool VeridateData(ProductReserveSearchData data) { bool ret = true; if (data.INVCODE.Trim() == "") { ret = false; _error = "¡ÃسÒÃкØàÅ¢·ÕèãºàÊÃç¨"; } else if (data.CODE.Trim() == "") { ret = false; _error = "¡ÃسÒÃкØÃËÑÊÊÁÒªÔ¡"; } return ret; }
public DataTable GetReserveList(ProductReserveSearchData whereData, string sortField) { string whereString = ""; if (whereData.REQUISITIONTYPE != 0) whereString += (whereString == "" ? "" : "AND ") + "REQUISITIONTYPE = " + whereData.REQUISITIONTYPE.ToString() + " "; if (whereData.CODEFROM.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "UPPER(CODE) >= '" + OracleDB.QRText(whereData.CODEFROM.Trim()).ToUpper() + "' "; if (whereData.CODETO.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "UPPER(CODE) <= '" + OracleDB.QRText(whereData.CODETO.Trim()).ToUpper() + "' "; if (whereData.DATEFROM.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "RESERVEDATE >= " + OracleDB.QRDate(whereData.DATEFROM) + " "; if (whereData.DATETO.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "RESERVEDATE <= " + OracleDB.QRDate(whereData.DATETO) + " "; if (whereData.CUSTOMERNAME.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "UPPER(CUSTOMERNAME) LIKE '%" + OracleDB.QRText(whereData.CUSTOMERNAME.Trim()).ToUpper() + "%' "; if (whereData.STATUSFROM.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "RANK >= " + OracleDB.QRText(whereData.STATUSFROM.Trim()) + " "; if (whereData.STATUSTO.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "RANK <= " + OracleDB.QRText(whereData.STATUSTO.Trim()) + " "; if (whereData.WAREHOUSE != 0) whereString += (whereString == "" ? "" : "AND ") + "WAREHOUSE = " + whereData.WAREHOUSE.ToString() + " "; if (sortField == "") sortField = "REQUISITIONTYPENAME, CODE "; string sql = "SELECT ROWNUM NO, A.* FROM (SELECT RT.NAME REQUISITIONTYPENAME, RQ.CODE, RQ.LOID, RQ.REQUISITIONTYPE, RQ.REQDATE, RQ.RESERVEDATE, RQ.DUEDATE, CU.NAME || ' ' || CU.LASTNAME AS CUSTOMERNAME, "; sql += "RQ.WAREHOUSE, CASE RQ.STATUS WHEN '" + Constz.Requisition.Status.Waiting.Code + "' THEN '" + Constz.Requisition.Status.Waiting.Name + "' "; sql += "WHEN '" + Constz.Requisition.Status.Approved.Code + "' THEN '" + Constz.Requisition.Status.Approved.Name + "' "; sql += "WHEN '" + Constz.Requisition.Status.Void.Code + "' THEN '" + Constz.Requisition.Status.Void.Name + "' "; sql += "WHEN '" + Constz.Requisition.Status.QC.Code + "' THEN '" + Constz.Requisition.Status.QC.Name + "' "; sql += "WHEN '" + Constz.Requisition.Status.Finish.Code + "' THEN '" + Constz.Requisition.Status.Finish.Name + "' "; sql += "WHEN '" + Constz.Requisition.Status.Reserve.Code + "' THEN '" + Constz.Requisition.Status.Reserve.Name + "' "; sql += "ELSE '' END AS STATUSNAME, "; sql += "CASE RQ.STATUS WHEN '" + Constz.Requisition.Status.Waiting.Code + "' THEN '" + Constz.Requisition.Status.Waiting.Rank + "' "; sql += "WHEN '" + Constz.Requisition.Status.Approved.Code + "' THEN '" + Constz.Requisition.Status.Approved.Rank + "' "; sql += "WHEN '" + Constz.Requisition.Status.Void.Code + "' THEN '" + Constz.Requisition.Status.Void.Rank + "' "; sql += "WHEN '" + Constz.Requisition.Status.QC.Code + "' THEN '" + Constz.Requisition.Status.QC.Rank + "' "; sql += "WHEN '" + Constz.Requisition.Status.Finish.Code + "' THEN '" + Constz.Requisition.Status.Finish.Rank + "' "; sql += "WHEN '" + Constz.Requisition.Status.Reserve.Code + "' THEN '" + Constz.Requisition.Status.Reserve.Rank + "' "; sql += "ELSE '' END AS RANK, RQ.CREATEBY, "; sql += "(SELECT COUNT(*) FROM STOCKOUT WHERE REFTABLE = 'REQUISITION' AND REFLOID = RQ.LOID AND STATUS <> '" + Constz.Requisition.Status.Void.Code + "' ) CNT "; sql += "FROM REQUISITION RQ INNER JOIN V_REQTYPE_RESERVE RT ON RQ.REQUISITIONTYPE = RT.LOID "; sql += "LEFT JOIN CUSTOMER CU ON RQ.CUSTOMER = CU.LOID ) A "; sql += (whereString == "" ? "" : "WHERE " + whereString); sql += (sortField == "" ? "" : "ORDER BY " + sortField); return OracleDB.ExecListCmd(sql); }
private ProductReserveSearchData GetData() { ProductReserveSearchData data = new ProductReserveSearchData(); //data.REQCODE = this.txtReqCode.Text.Trim(); data.CODE = this.txtStockCode.Text.Trim(); data.CREATEFROM = this.ctlApproveDateFrom.DateValue; data.CREATETO = this.ctlApproveDateTo.DateValue.AddDays(1); //data.DATEFROM = this.ctlRequestDateFrom.DateValue; //data.DATETO = this.ctlRequestDateTo.DateValue; //data.REQUISITIONTYPE = Convert.ToDouble(this.cmbRequisitionType.SelectedItem.Value); //data.PRODUCT = Convert.ToDouble(this.cmbProduct.SelectedItem.Value); data.STATUSFROM = this.cmbStatusFrom.SelectedItem.Value; data.STATUSTO = this.cmbStatusTo.SelectedItem.Value; data.CUSTOMERNAME = this.txtCreateby.Text.Trim(); data.DIVISION = Convert.ToDouble(this.cmbDivision.SelectedItem.Value); return data; }
public DataTable GetProductionList(ProductReserveSearchData whereData) { string whereString = "DOCLOID IN (" + Constz.DocType.ReqRawPD.LOID.ToString() + "," + Constz.DocType.ReqRawPO.LOID.ToString() + ") "; if (whereData.REQUISITIONTYPE != 0) whereString += (whereString == "" ? "" : "AND ") + "DOCLOID = '" + whereData.REQUISITIONTYPE.ToString() + "' "; if (whereData.CODE.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "UPPER(STOCKCODE) = '" + OracleDB.QRText(whereData.CODE.Trim()).ToUpper() + "' "; if (whereData.REQCODE.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "UPPER(REQCODE) = '" + OracleDB.QRText(whereData.REQCODE.Trim()).ToUpper() + "' "; if (whereData.DATEFROM.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "REQDATE >= " + OracleDB.QRDate(whereData.DATEFROM) + " "; if (whereData.DATETO.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "REQDATE <= " + OracleDB.QRDate(whereData.DATETO) + " "; if (whereData.CREATEFROM.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "CREATEON >= " + OracleDB.QRDate(whereData.CREATEFROM) + " "; if (whereData.CREATETO.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "CREATEON <= " + OracleDB.QRDate(whereData.CREATETO) + " "; if (whereData.PRODUCT != 0) whereString += (whereString == "" ? "" : "AND ") + "PRODUCT = " + whereData.PRODUCT.ToString() + " "; if (whereData.STATUSFROM.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "RANK >= '" + OracleDB.QRText(whereData.STATUSFROM.Trim()) + "' "; if (whereData.STATUSTO.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "RANK <= '" + OracleDB.QRText(whereData.STATUSTO.Trim()) + "' "; string sql = "SELECT ROWNUM NO, A.* FROM (SELECT ST.LOID LOID,ST.DOCTYPE DOCLOID,DT.DOCNAME DOCTYPE,NVL(VPL.POCODE,VRP.RQCODE) REQCODE,NVL(VPL.ORDERDATE,VRP.REQDATE) REQDATE,ST.CREATEBY ,ST.CREATEON, "; sql += "CASE ST.STATUS WHEN '" + Constz.Requisition.Status.Waiting.Code + "' THEN '" + Constz.Requisition.Status.Waiting.Name + "' "; sql += "WHEN '" + Constz.Requisition.Status.Approved.Code + "' THEN '" + Constz.Requisition.Status.Approved.Name + "' "; sql += "WHEN '" + Constz.Requisition.Status.Void.Code + "' THEN '" + Constz.Requisition.Status.Void.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.Approved.Code + "' THEN '" + Constz.Requisition.Status.Approved.Rank + "' "; sql += "WHEN '" + Constz.Requisition.Status.Void.Code + "' THEN '" + Constz.Requisition.Status.Void.Rank + "' "; sql += "ELSE '' END AS RANK, ST.CODE STOCKCODE,NVL(VPL.PD_LOID,VRP.PDLOID) PRODUCT,S.SUPPLIERNAME AS CUSTOMERNAME "; sql += "FROM STOCKOUT ST LEFT JOIN DOCTYPE DT ON ST.DOCTYPE = DT.LOID "; sql += "LEFT JOIN V_PDORDER_LIST VPL ON ST.REFTABLE = 'PDORDER' AND VPL.PO_LOID = ST.REFLOID AND ST.PRODUCTREF = 'POITEM' AND ST.PRODUCTLOID = VPL.POI_LOID "; sql += "LEFT JOIN V_REQUISITION_PROD_LIST VRP ON ST.REFTABLE = 'REQUISITION' AND VRP.RQLOID = ST.REFLOID "; sql += "LEFT JOIN SUPPLIER S ON S.LOID = ST.RECEIVER ) A "; sql += (whereString == "" ? "" : "WHERE " + whereString); sql += "ORDER BY NO "; return OracleDB.ExecListCmd(sql); }
private void SearchData() { this.txtRefNo.Text = ""; SearchFlow flow = new SearchFlow(); ProductReserveSearchData data = new ProductReserveSearchData(); data.REQUISITIONTYPE = Convert.ToDouble(this.cmbRequisitionType.SelectedValue); if (this.cmbCustomer.SelectedValue != "") data.CUSTOMER = Convert.ToDouble(this.cmbCustomer.SelectedItem.Value == "" ? "0" : this.cmbCustomer.SelectedItem.Value); data.CODE = this.txtPopup.Text; this.grvReserve.DataSource = flow.GetReserveList(data); this.grvReserve.DataBind(); if (this.grvReserve.SelectedValue == null) this.txtRefNo.Text = ""; else this.txtRefNo.Text = this.grvReserve.SelectedValue.ToString(); this.btnSelect.Visible = (this.grvReserve.Rows.Count > 0); }
public ArrayList GetSearchUnit(ProductReserveSearchData uSearch) { //string str = ""; ArrayList arrResult = new ArrayList(); //str = " SELECT * FROM UNIT "; //str += " WHERE NAME = '" + name + "'"; //str += " AND E = " + Ename; //if (Barcode != "") //{ // str += " AND BARCODE = '" + Barcode + "'"; //} //if (PName != "") //{ // str += " AND PNAME LIKE '%" + PName + "%'"; //} //try //{ // OracleDataReader zRd = OracleDB.ExecQueryCmd(str); // arrResult.Clear(); // int i = 1; // while (zRd.Read()) // { // V_Product_List_RequisitionData irData = new V_Product_List_RequisitionData(); // irData.ORDERNO = i; // irData.BARCODE = zRd["BARCODE"].ToString(); // irData.PNAME = zRd["PNAME"].ToString(); // arrResult.Add(irData); // i = i + 1; // } //} //catch (Exception ex) //{ // throw ex; //} return arrResult; }
public DataTable GetInvoiceList(ProductReserveSearchData whereData) { string whereString = ""; if (whereData.REQUISITIONTYPE != 0) whereString += (whereString == "" ? "" : "AND ") + "REFTYPELOID = " + whereData.REQUISITIONTYPE.ToString() + " "; if (whereData.CODEFROM.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "UPPER(INVCODE) >= '" + OracleDB.QRText(whereData.CODEFROM.Trim()).ToUpper() + "' "; if (whereData.CODETO.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "UPPER(INVCODE) <= '" + OracleDB.QRText(whereData.CODETO.Trim()).ToUpper() + "' "; if (whereData.DATEFROM.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "REQDATE >= " + OracleDB.QRDate(whereData.DATEFROM) + " "; if (whereData.DATETO.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "REQDATE <= " + OracleDB.QRDate(whereData.DATETO) + " "; if (whereData.CUSTOMERNAME.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "UPPER(CUSTOMERNAME) LIKE '%" + OracleDB.QRText(whereData.CUSTOMERNAME.Trim()).ToUpper() + "%' "; if (whereData.STATUSFROM.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "RANK >= " + OracleDB.QRText(whereData.STATUSFROM.Trim()) + " "; if (whereData.STATUSTO.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "RANK <= " + OracleDB.QRText(whereData.STATUSTO.Trim()) + " "; string sql = "SELECT ROWNUM NO, A.* FROM (SELECT RQ.INVCODE, RQ.LOID, RQ.CODE, RQ.REQUISITIONTYPE, RQ.REFTYPELOID, RT.NAME REQUISITIONTYPENAME, RQ.REQDATE, RQ.DUEDATE, CU.NAME || ' ' || CU.LASTNAME AS CUSTOMERNAME, "; sql += "CASE RQ.STATUS WHEN '" + Constz.Requisition.Status.Waiting.Code + "' THEN '" + Constz.Requisition.Status.Waiting.Name + "' "; sql += "WHEN '" + Constz.Requisition.Status.Reserve.Code + "' THEN '" + Constz.Requisition.Status.Reserve.Name + "' "; sql += "WHEN '" + Constz.Requisition.Status.Approved.Code + "' THEN '" + Constz.Requisition.Status.Approved.Name + "' "; sql += "WHEN '" + Constz.Requisition.Status.Void.Code + "' THEN '" + Constz.Requisition.Status.Void.Name + "' "; sql += "ELSE '' END AS STATUSNAME, "; sql += "CASE RQ.STATUS WHEN '" + Constz.Requisition.Status.Waiting.Code + "' THEN '" + Constz.Requisition.Status.Waiting.Rank + "' "; sql += "WHEN '" + Constz.Requisition.Status.Reserve.Code + "' THEN '" + Constz.Requisition.Status.Reserve.Rank + "' "; sql += "WHEN '" + Constz.Requisition.Status.Approved.Code + "' THEN '" + Constz.Requisition.Status.Approved.Rank + "' "; sql += "WHEN '" + Constz.Requisition.Status.Void.Code + "' THEN '" + Constz.Requisition.Status.Void.Rank + "' "; sql += "ELSE '' END AS RANK, RQ.CREATEBY, RQ.RESERVEDATE, RQ.GRANDTOT, RQ.RESERVEDATE+14 AS SENDDATE "; sql += "FROM REQUISITION RQ INNER JOIN REQUISITIONTYPE RT ON RQ.REQUISITIONTYPE = RT.LOID "; sql += "AND (RQ.REQUISITIONTYPE = " + Constz.Requisition.RequisitionType.REQ11.ToString() + " OR (RQ.REQUISITIONTYPE = " + Constz.Requisition.RequisitionType.REQ01.ToString() + " AND RQ.STATUS <> '" + Constz.Requisition.Status.Waiting.Code + "')) LEFT JOIN CUSTOMER CU ON RQ.CUSTOMER = CU.LOID ) A "; sql += (whereString == "" ? "" : "WHERE " + whereString); sql += "ORDER BY NO "; return OracleDB.ExecListCmd(sql); }
public DataTable GetRequestList(ProductReserveSearchData whereData) { //TESTSETSETSET string whereString = ""; if (whereData.REQUISITIONTYPE != 0) whereString += (whereString == "" ? "" : "AND ") + "REQUISITIONTYPE = " + whereData.REQUISITIONTYPE.ToString() + " "; if (whereData.CODEFROM.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "UPPER(CODE) >= '" + OracleDB.QRText(whereData.CODEFROM.Trim()).ToUpper() + "' "; if (whereData.CODETO.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "UPPER(CODE) <= '" + OracleDB.QRText(whereData.CODETO.Trim()).ToUpper() + "' "; if (whereData.DATEFROM.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "REQDATE >= " + OracleDB.QRDate(whereData.DATEFROM) + " "; if (whereData.DATETO.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "REQDATE <= " + OracleDB.QRDate(whereData.DATETO) + " "; if (whereData.CUSTOMERNAME.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "UPPER(CUSTOMERNAME) LIKE '%" + OracleDB.QRText(whereData.CUSTOMERNAME.Trim()).ToUpper() + "%' "; if (whereData.STATUSFROM.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "RANK >= " + OracleDB.QRText(whereData.STATUSFROM.Trim()) + " "; if (whereData.STATUSTO.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "RANK <= " + OracleDB.QRText(whereData.STATUSTO.Trim()) + " "; //string sql = "SELECT ROWNUM NO, A.* FROM (SELECT RQ.LOID, RQ.CODE, RQ.REQUISITIONTYPE, RT.NAME REQUISITIONTYPENAME, RQ.RESERVEDATE, RQ.DUEDATE, CU.NAME || ' ' || CU.LASTNAME AS CUSTOMERNAME, "; string sql = "SELECT ROWNUM NO, A.* FROM (SELECT RQ.LOID, RQ.CODE, RQ.REQUISITIONTYPE, RT.NAME REQUISITIONTYPENAME, RQ.RESERVEDATE, RQO.INVCODE, RQ.GRANDTOT, RQ.REQDATE, RQ.DUEDATE, CU.NAME || ' ' || CU.LASTNAME AS CUSTOMERNAME, "; sql += "RQ.REFLOID, CASE RQ.STATUS WHEN '" + Constz.Requisition.Status.Waiting.Code + "' THEN '" + Constz.Requisition.Status.Waiting.Name + "' "; sql += "WHEN '" + Constz.Requisition.Status.ApproveWH.Code + "' THEN '" + Constz.Requisition.Status.ApproveWH.Name + "' "; sql += "WHEN '" + Constz.Requisition.Status.Void.Code + "' THEN '" + Constz.Requisition.Status.Void.Name + "' "; sql += "ELSE '' END AS STATUSNAME, "; sql += "CASE RQ.STATUS WHEN '" + Constz.Requisition.Status.Waiting.Code + "' THEN '" + Constz.Requisition.Status.Waiting.Rank + "' "; sql += "WHEN '" + Constz.Requisition.Status.ApproveWH.Code + "' THEN '" + Constz.Requisition.Status.ApproveWH.Rank + "' "; sql += "WHEN '" + Constz.Requisition.Status.Void.Code + "' THEN '" + Constz.Requisition.Status.Void.Rank + "' "; sql += "ELSE '' END AS RANK, RQ.CREATEBY "; sql += "FROM REQUISITION RQ INNER JOIN REQUISITION RQO ON RQO.LOID = RQ.REFLOID AND RQ.REFTABLE = 'REQUISITION' "; sql += "INNER JOIN REQUISITIONTYPE RT ON RQ.REQUISITIONTYPE = RT.LOID "; sql += "LEFT JOIN CUSTOMER CU ON RQ.CUSTOMER = CU.LOID ) A "; sql += (whereString == "" ? "" : "WHERE " + whereString); return OracleDB.ExecListCmd(sql); }
private ProductReserveSearchData GetData() { ProductReserveSearchData data = new ProductReserveSearchData(); data.CODEFROM = this.txtCodeFrom.Text.Trim(); data.CODETO = this.txtCodeTo.Text.Trim(); data.CUSTOMERNAME = this.txtName.Text.Trim(); data.DATEFROM = this.ctlDateFrom.DateValue; data.DATETO = this.ctlDateTo.DateValue; data.REQUISITIONTYPE = Convert.ToDouble(this.cmbRequisitionType.SelectedItem.Value); data.STATUSFROM = this.cmbStatusFrom.SelectedItem.Value; data.STATUSTO = this.cmbStatusTo.SelectedItem.Value; //data.WAREHOUSE = Authz.CurrentUserInfo.Warehouse; return data; }
public DataTable GetProductionList(ProductReserveSearchData data) { return SearchDAL.GetProductionList(data); }
public DataTable GetRequisitionList(ProductReserveSearchData data) { return SearchObj.GetRequestList(data); }
public DataTable GetProductionOtherList(ProductReserveSearchData whereData) { string whereString = "DOCTYPE = " + Constz.DocType.RetSOther.LOID + " "; if (whereData.DIVISION != 0) whereString += (whereString == "" ? "" : "AND ") + "DIVISION = " + whereData.DIVISION.ToString() + " "; if (whereData.CUSTOMERNAME.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "CREATEBY = '" + OracleDB.QRText(whereData.CUSTOMERNAME.Trim()) + "' "; if (whereData.CODE.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "UPPER(STOCKOUTCODE) = '" + OracleDB.QRText(whereData.CODE.Trim()).ToUpper() + "' "; if (whereData.CREATEFROM.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "CREATEON >= " + OracleDB.QRDate(whereData.CREATEFROM) + " "; if (whereData.CREATETO.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "CREATEON <= " + OracleDB.QRDate(whereData.CREATETO)+ " "; if (whereData.STATUSFROM.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "RANK >= " + OracleDB.QRText(whereData.STATUSFROM.Trim()) + " "; if (whereData.STATUSTO.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "RANK <= " + OracleDB.QRText(whereData.STATUSTO.Trim()) + " "; string sql = "SELECT ROWNUM NO, A.* FROM (SELECT ST.LOID LOID,ST.DOCTYPE,ST.CODE STOCKOUTCODE,ST.CREATEON REQDATE,ST.CREATEBY ,ST.CREATEON, DV.TNAME DIVISIONNAME, ST.SUPPORTREFCODE,DV.LOID DIVISION, "; sql += "CASE ST.STATUS WHEN '" + Constz.Requisition.Status.Waiting.Code + "' THEN '" + Constz.Requisition.Status.Waiting.Name + "' "; sql += "WHEN '" + Constz.Requisition.Status.Approved.Code + "' THEN '" + Constz.Requisition.Status.Approved.Name + "' "; sql += "WHEN '" + Constz.Requisition.Status.Void.Code + "' THEN '" + Constz.Requisition.Status.Void.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.Approved.Code + "' THEN '" + Constz.Requisition.Status.Approved.Rank + "' "; sql += "WHEN '" + Constz.Requisition.Status.Void.Code + "' THEN '" + Constz.Requisition.Status.Void.Rank + "' "; sql += "ELSE '' END AS RANK "; sql += "FROM STOCKOUT ST "; sql += "INNER JOIN DOCTYPE DT ON ST.DOCTYPE = DT.LOID "; sql += "INNER JOIN DIVISION DV ON DV.LOID=ST.DIVISION) A "; sql += (whereString == "" ? "" : "WHERE " + whereString); sql += "ORDER BY STOCKOUTCODE DESC"; return OracleDB.ExecListCmd(sql); }
public static DataTable GetReserveList(ProductReserveSearchData data) { string where = "QTY > 0"; if (data.REQUISITIONTYPE != 0) where += (where == "" ? "" : "AND ") + "REFTYPELOID = " + data.REQUISITIONTYPE.ToString() + " "; if (data.CUSTOMER != 0) where += (where == "" ? "" : "AND ") + "CULOID = " + data.CUSTOMER.ToString() + " "; if (data.CODE != "") where += (where == "" ? "" : "AND ") + "PDLOID NOT IN (" + data.CODE.ToString() + ") "; string sql = "SELECT * FROM V_PRODUCT_INVOICE "; sql += (where == "" ? "" : "WHERE " + where); sql += "ORDER BY PRODUCTGROUPNAME, PRODUCTNAME "; return OracleDB.ExecListCmd(sql); }
public DataTable GetRequisitionList(ProductReserveSearchData data) { return SearchDAL.GetInvoiceList(data); }
public ProductReserveSearchData GetData(double loid) { ProductReserveSearchData data = new ProductReserveSearchData(); if (DALObj.GetDataByLOID(loid, null)) { data.CUSTOMERNAME = DALObj.CUSTOMERNAME; //data.ACTIVE = DALObj.ACTIVE; data.CODE = DALObj.CODE; data.LOID = DALObj.LOID; data.INVCODE = DALObj.INVCODE; } return data; }
public bool UpdateData(string userID, ProductReserveSearchData data) { bool ret = true; if (VeridateData(data)) { OracleDBObj obj = new OracleDBObj(); obj.CreateConnection(); obj.CreateTransaction(); try { DALObj.GetDataByLOID(data.LOID, obj.zTrans); DALObj.LOID = data.LOID; //DALObj.NAME = data.NAME.Trim(); //DALObj.ENAME = data.ENAME.Trim(); //DALObj.TYPE = data.TYPE.Trim(); DALObj.CODE = data.CODE.Trim(); //DALObj.ACTIVE = data.ACTIVE.Trim(); if (DALObj.OnDB) ret = DALObj.UpdateCurrentData(userID, obj.zTrans); else ret = DALObj.InsertCurrentData(userID, obj.zTrans); if (ret) { obj.zTrans.Commit(); obj.CloseConnection(); } else { throw new ApplicationException(DALObj.ErrorMessage); } } catch (Exception ex) { obj.zTrans.Rollback(); obj.CloseConnection(); ret = false; _error = ex.Message; } } else ret = false; return ret; }