public DataTable GetStockOutList(StockOutFGSearchData data)
{
DataTable dt = StockSearchDAL.GetStockOutList(data);
int i = 1;
foreach (DataRow dRow in dt.Rows)
{
dRow["NO"] = i;
i += 1;
}
return dt;
}
private StockOutFGSearchData GetData()
{
StockOutFGSearchData data = new StockOutFGSearchData();
data.REQUISITIONCODE = this.txtReqCode.Text.Trim();
data.STOCKOUTCODE = this.txtStockCode.Text.Trim();
data.CUSTOMER = Convert.ToDouble(this.cmbCustomer.SelectedItem.Value);
data.CREATEFROM = this.ctlApproveDateFrom.DateValue;
data.CREATETO = this.ctlApproveDateTo.DateValue;
data.RESERVEDATEFROM = this.ctlRequestDateFrom.DateValue;
data.RESERVEDATETO = this.ctlRequestDateTo.DateValue;
data.DOCTYPE = Convert.ToDouble(this.cmbDocType.SelectedItem.Value);
data.STATUSFROM = this.cmbStatusFrom.SelectedItem.Value;
data.STATUSTO = this.cmbStatusTo.SelectedItem.Value;
data.CREATEBY = this.txtCreateby.Text;
return data;
}
public DataTable GetStockOutList(StockOutFGSearchData whereData)
{
string whereString = "";
if (whereData.DOCTYPE != 0)
whereString += (whereString == "" ? "" : "AND ") + "DOCLOID = '" + whereData.DOCTYPE.ToString() + "' ";
if (whereData.STOCKOUTCODE.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "UPPER(STOCKCODE) = '" + OracleDB.QRText(whereData.STOCKOUTCODE.Trim()).ToUpper() + "' ";
if (whereData.RESERVEDATEFROM.Year != 1)
whereString += (whereString == "" ? "" : "AND ") + "RESERVEDATE >= " + OracleDB.QRDate(whereData.RESERVEDATEFROM) + " ";
if (whereData.RESERVEDATETO.Year != 1)
whereString += (whereString == "" ? "" : "AND ") + "RESERVEDATE <= " + OracleDB.QRDate(whereData.RESERVEDATETO) + " ";
if (whereData.REQUISITIONCODE.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "UPPER(REQCODE) = '" + OracleDB.QRText(whereData.REQUISITIONCODE.Trim()).ToUpper() + "' ";
if (whereData.CREATEFROM.Year != 1)
whereString += (whereString == "" ? "" : "AND ") + "CREATEON >= " + OracleDB.QRDate(whereData.CREATEFROM) + " ";
if (whereData.CREATEBY.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "UPPER(CREATEBY) = '" + OracleDB.QRText(whereData.CREATEBY.Trim().ToUpper()) + "' ";
if (whereData.CREATETO.Year != 1)
whereString += (whereString == "" ? "" : "AND ") + "CREATEON <= " + OracleDB.QRDate(whereData.CREATETO) + " ";
if (whereData.CUSTOMER != 0)
whereString += (whereString == "" ? "" : "AND ") + "CUSTOMER = " + whereData.CUSTOMER.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,RQ.CODE REQCODE,RQ.REQDATE,ST.CREATEBY ,ST.CREATEON, RQ.RESERVEDATE, ";
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,CU.LOID CUSTOMER, CU.NAME || ' ' || CU.LASTNAME AS CUSTOMERNAME, ST.INVCODE, RQ.LOID REQUISITION ";
sql += "FROM STOCKOUT ST INNER JOIN DOCTYPE DT ON ST.DOCTYPE = DT.LOID ";
sql += "INNER JOIN CUSTOMER CU ON ST.RECEIVER = CU.LOID INNER JOIN REQUISITION RQ ON ST.REFTABLE = 'REQUISITION' AND ST.REFLOID = RQ.LOID WHERE ST.DOCTYPE IN ( ";
sql += Constz.DocType.Reserve.LOID.ToString() + "," + Constz.DocType.ReqOrgSupport.LOID.ToString() + "," + Constz.DocType.ReqFair.LOID.ToString() + "," + Constz.DocType.ReqDistribute.LOID.ToString() + "," + Constz.DocType.ReqSupport.LOID.ToString() + ") UNION ";
sql += "SELECT ST.LOID LOID,ST.DOCTYPE DOCLOID,DT.DOCNAME DOCTYPE,RQ.CODE REQCODE,RQ.REQDATE,ST.CREATEBY ,ST.CREATEON, RQ.RESERVEDATE, ";
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,W.LOID CUSTOMER, W.NAME AS CUSTOMERNAME, ST.INVCODE, RQ.LOID REQUISITION ";
sql += "FROM STOCKOUT ST INNER JOIN DOCTYPE DT ON ST.DOCTYPE = DT.LOID ";
sql += "INNER JOIN WAREHOUSE W ON ST.RECEIVER = W.LOID INNER JOIN REQUISITION RQ ON ST.REFTABLE = 'REQUISITION' AND ST.REFLOID = RQ.LOID WHERE ST.DOCTYPE = " + Constz.DocType.ReqProduct.LOID.ToString() + ") A ";
sql += (whereString == "" ? "" : "WHERE " + whereString);
sql += "ORDER BY STOCKCODE DESC ";
return OracleDB.ExecListCmd(sql);
}
