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