private void SearchData()
{
this.txtRefNo.Text = "";
SearchFlow flow = new SearchFlow();
StockoutSearchData data = new StockoutSearchData();
data.REQCODETO = this.txtCodeTo.Text.Trim();
data.REQCODEFROM = this.txtCodeFrom.Text.Trim();
//data.REQUISITIONTYPE = Convert.ToDouble(this.cmbRequisitionType.SelectedValue);
data.REQUESTDATEFROM = this.ctlDateFrom.DateValue;
data.REQUESTDATETO = this.ctlDateTo.DateValue;
data.PRODUCT = Convert.ToDouble(this.cmbProduct.SelectedValue);
string sType = Request["type"];
if (sType == "12")
{
this.grvReserve.DataSource = flow.GetProductionList(data);
}
else
{
this.grvReserve.DataSource = flow.GetReqProductionList(data);
}
this.grvReserve.DataBind();
// this.btnSelect.Visible = (this.grvReserve.Rows.Count > 0);
if (this.grvReserve.SelectedValue == null)
this.txtRefNo.Text = "";
else
this.txtRefNo.Text = this.grvReserve.SelectedValue.ToString();
}
private StockoutSearchData GetData()
{
StockoutSearchData data = new StockoutSearchData();
data.STOCKCODE = this.txtCode.Text.Trim();
data.SUPPLIER = Convert.ToDouble(this.cmbSupplier.SelectedItem.Value);
data.DATEFROM = this.ctlDateFrom.DateValue;
data.DATETO = this.ctlDateTo.DateValue;
data.STATUSFROM = this.cmbStatusFrom.SelectedItem.Value;
data.STATUSTO = this.cmbStatusTo.SelectedItem.Value;
return data;
}
private void SearchData()
{
this.txtRefNo.Text = "";
SearchFlow flow = new SearchFlow();
StockoutSearchData data = new StockoutSearchData();
data.REQCODETO = this.txtCodeTo.Text.Trim();
data.REQCODEFROM = this.txtCodeFrom.Text.Trim();
//data.REQUISITIONTYPE = Convert.ToDouble(this.cmbRequisitionType.SelectedValue);
data.CUSTOMERNAME = this.txtCustName.Text.Trim();
data.CUSTOMERCODE = this.txtCustCode.Text.Trim();
data.REQUESTDATEFROM = this.ctlDateFrom.DateValue;
data.REQUESTDATETO = this.ctlDateTo.DateValue;
this.grvReserve.DataSource = flow.GetRequisitionList(data);
this.grvReserve.DataBind();
//this.btnSelect.Visible = (this.grvReserve.Rows.Count > 0);
if (this.grvReserve.SelectedValue == null)
this.txtRefNo.Text = "";
else
this.txtRefNo.Text = this.grvReserve.SelectedValue.ToString();
}
public DataTable GetReturnWHList(StockoutSearchData whereData)
{
string whereString = "DOCLOID = " + Constz.DocType.RetRaw.LOID + " ";
if (whereData.STOCKCODE.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "UPPER(CODE) = '" + OracleDB.QRText(whereData.STOCKCODE.Trim()).ToUpper() + "' ";
if (whereData.DATEFROM.Year != 1)
whereString += (whereString == "" ? "" : "AND ") + "CREATEON >= " + OracleDB.QRDate(whereData.DATEFROM) + " ";
if (whereData.DATETO.Year != 1)
whereString += (whereString == "" ? "" : "AND ") + "CREATEON <= " + OracleDB.QRDate(whereData.DATETO) + " ";
if (whereData.SUPPLIER != 0)
whereString += (whereString == "" ? "" : "AND ") + "SUPPLIER = " + whereData.SUPPLIER.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,ST.CODE,ST.DOCTYPE DOCLOID,ST.CREATEBY,ST.CREATEON,SP.LOID SUPPLIER, SP.SUPPLIERNAME, ";
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.REASON FROM STOCKOUT ST LEFT JOIN DOCTYPE DT ON ST.DOCTYPE = DT.LOID ";
sql += "INNER JOIN SUPPLIER SP ON ST.RECEIVER = SP.LOID) A ";
sql += (whereString == "" ? "" : "WHERE " + whereString);
sql += "ORDER BY NO ";
return OracleDB.ExecListCmd(sql);
}
public static DataTable GetReqProductionList(StockoutSearchData data)
{
string where = " RQLOID NOT IN (SELECT REFLOID FROM STOCKOUT WHERE REFTABLE = 'REQUISITION') ";
if (data.PRODUCT != 0)
where += (where == "" ? "" : "AND ") + "PDLOID = " + data.PRODUCT.ToString() + " ";
if (data.REQCODEFROM.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(RQCODE) >= '" + OracleDB.QRText(data.REQCODEFROM.Trim()).ToUpper() + "' ";
if (data.REQCODETO.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(RQCODE) <= '" + OracleDB.QRText(data.REQCODETO.Trim()).ToUpper() + "' ";
if (data.REQUESTDATEFROM.Year != 1)
where += (where == "" ? "" : "AND ") + "TO_DATE(REQDATE, 'DDMMYYYY') >= " + OracleDB.QRDate(data.REQUESTDATEFROM) + " ";
if (data.REQUESTDATETO.Year != 1)
where += (where == "" ? "" : "AND ") + "TO_DATE(REQDATE, 'DDMMYYYY') <= " + OracleDB.QRDate(data.REQUESTDATETO) + " ";
string sql = "SELECT RQLOID REFLOID,POLOID LOID,RQCODE CODE,REQDATE," + Constz.ProductionDepartment.LOID.ToString() + " AS S_LOID,'' AS S_CODE, ";
sql += " '" + Constz.ProductionDepartment.Name + "' AS NAME,PDLOID, PDNAME FROM V_REQUISITION_PROD_LIST ";
sql += (where == "" ? "" : "WHERE " + where);
sql += "ORDER BY CODE ";
return OracleDB.ExecListCmd(sql);
}
public static DataTable GetProductionList(StockoutSearchData data)
{
string where = " ISMATERIAL = 'Y' AND POI_LOID NOT IN (SELECT REFLOID FROM STOCKINITEM WHERE REFTABLE = 'POITEM') ";
if (data.PRODUCT != 0)
where += (where == "" ? "" : "AND ") + "PD_LOID = " + data.PRODUCT.ToString() + " ";
if (data.REQCODEFROM.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(POCODE) >= '" + OracleDB.QRText(data.REQCODEFROM.Trim()).ToUpper() + "' ";
if (data.REQCODETO.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(POCODE) <= '" + OracleDB.QRText(data.REQCODETO.Trim()).ToUpper() + "' ";
if (data.REQUESTDATEFROM.Year != 1)
where += (where == "" ? "" : "AND ") + "TO_DATE(ORDERDATE, 'DDMMYYYY') >= " + OracleDB.QRDate(data.REQUESTDATEFROM) + " ";
if (data.REQUESTDATETO.Year != 1)
where += (where == "" ? "" : "AND ") + "TO_DATE(ORDERDATE, 'DDMMYYYY') <= " + OracleDB.QRDate(data.REQUESTDATETO) + " ";
string sql = "SELECT PO_LOID LOID,POI_LOID REFLOID,POCODE CODE,ORDERDATE REQDATE,REFTABLE,S_LOID,S_CODE,SUPPLIERNAME NAME,PD_LOID PDLOID, PDNAME FROM V_PDORDER_LIST ";
sql += (where == "" ? "" : "WHERE " + where);
sql += "ORDER BY CODE ";
return OracleDB.ExecListCmd(sql);
}
/// <summary>
/// áÊ´§ãºÊÑ觫×éÍ/ÊÑ觨ͧ 㺢ÍàºÔ¡ÊÔ¹¤éÒ½Ò¡¢Ò 㺢ÍàºÔ¡¢Ò¹͡ʶҹ·Õè áÅР㺢ÍàºÔ¡Ë¹èǧҹʹѺʹع ·ÕèÂѧäÁèÁÕãºàºÔ¡ÍÍ¡¨Ò¡¤Åѧ (â´ÂäÁèÃÇÁãºàºÔ¡·Õè¡àÅÔ¡)
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public static DataTable GetRequisitionList(StockoutSearchData data)
{
string where = "STATUS = '" + Constz.Requisition.Status.Approved.Code + "' AND RQ_LOID NOT IN (SELECT REFLOID FROM STOCKOUT WHERE REFTABLE = 'REQUISITION') ";
if (data.CUSTOMERNAME.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(NAME) LIKE '%" + OracleDB.QRText(data.CUSTOMERNAME.Trim()).ToUpper() + "%' ";
if (data.CUSTOMERCODE.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(C_CODE) LIKE '%" + OracleDB.QRText(data.CUSTOMERCODE.Trim()).ToUpper() + "%' ";
if (data.REQCODEFROM.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(CODE) >= '" + OracleDB.QRText(data.REQCODEFROM.Trim()).ToUpper() + "' ";
if (data.REQCODETO.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(CODE) <= '" + OracleDB.QRText(data.REQCODETO.Trim()).ToUpper() + "' ";
if (data.REQUESTDATEFROM.Year != 1)
where += (where == "" ? "" : "AND ") + "TO_DATE(REQDATE, 'DDMMYYYY') >= " + OracleDB.QRDate(data.REQUESTDATEFROM) + " ";
if (data.REQUESTDATETO.Year != 1)
where += (where == "" ? "" : "AND ") + "TO_DATE(REQDATE, 'DDMMYYYY') <= " + OracleDB.QRDate(data.REQUESTDATETO) + " ";
string sql = "SELECT RQ_LOID LOID, CODE, REQDATE, C_LOID, C_CODE, NAME, REQUISITIONTYPE, REQUISITIONTYPENAME RTNAME, WAREHOUSE, STATUS, REFTABLE, REFLOID FROM V_REQUISITION_LIST ";
sql += (where == "" ? "" : "WHERE " + where);
sql += "ORDER BY REQUISITIONTYPE, LOID ";
return OracleDB.ExecListCmd(sql);
}
public DataTable GetReqProductionList(StockoutSearchData data)
{
return SearchDAL.GetReqProductionList(data);
}
public DataTable GetReturnWHList(StockoutSearchData data)
{
return StockSearchDAL.GetReturnWHList(data);
}
