private StockinProductSearchData GetData()
{
StockinProductSearchData data = new StockinProductSearchData();
data.PRODUCETYPE = Request["producetype"];
data.CODEFROM = this.txtCodeFrom.Text.Trim();
data.CODETO = this.txtCodeTo.Text.Trim();
data.DATEFROM = this.ctlDateFrom.DateValue;
data.DATETO = this.ctlDateTo.DateValue;
data.PRODUCTNAME = this.txtProductName.Text.Trim();
data.LOTNOFROM = this.txtLotNoFrom.Text.Trim();
data.LOTNOTO = this.txtLotNoTo.Text.Trim();
data.CREATEONFROM = this.ctlProduceDateFrom.DateValue;
data.CREATEONTO = this.ctlProduceDateTo.DateValue;
data.WAREHOUSE = Authz.CurrentUserInfo.Warehouse;
return data;
}
public DataTable GetStockInListFG(StockinProductSearchData data)
{
string whereString = " RECEIVER = " + data.WAREHOUSE + " AND DOCTYPE=13 ";
if (data.CODEFROM.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "CODE >= '" + OracleDB.QRText(data.CODEFROM.Trim()) + "' ";
if (data.CODETO.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "CODE <= '" + OracleDB.QRText(data.CODETO.Trim()) + "' ";
if (data.DATEFROM.Year != 1)
whereString += (whereString == "" ? "" : "AND ") + "CREATEON >= " + OracleDB.QRDate(data.DATEFROM) + " ";
if (data.DATETO.Year != 1)
whereString += (whereString == "" ? "" : "AND ") + "CREATEON <= " + OracleDB.QRDate(data.DATETO) + " ";
if (data.PRODUCTNAME.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "PRODUCTNAME = '" + OracleDB.QRText(data.PRODUCTNAME.Trim()) + "' ";
if (data.LOTNOFROM.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "LOTNO >= '" + OracleDB.QRText(data.LOTNOFROM.Trim()) + "' ";
if (data.LOTNOTO.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "LOTNO <= '" + OracleDB.QRText(data.LOTNOTO.Trim()) + "' ";
if (data.CREATEONFROM.Year != 1)
whereString += (whereString == "" ? "" : "AND ") + "CREATEON >= " + OracleDB.QRDate(data.CREATEONFROM) + " ";
if (data.CREATEONTO.Year != 1)
whereString += (whereString == "" ? "" : "AND ") + "CREATEON <= " + OracleDB.QRDate(data.CREATEONTO) + " ";
if (data.PRODUCETYPE.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "PRODUCETYPE = '" + OracleDB.QRText(data.PRODUCETYPE.Trim()) + "' ";
string sql = "select ROWNUM NO, a.* from (SELECT DISTINCT ST.LOID LOID,ST.CODE CODE,ST.CREATEON CREATEON,ST.RECEIVER,PPD.PRODUCETYPE,PPD.LOTNO,PD.NAME PRODUCTNAME,ST.DOCTYPE, ";
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 STOCKINITEM SI ON SI.STOCKIN=ST.LOID ";
sql += "INNER JOIN PDPRODUCT PPD ON PPD.LOID=SI.REFLOID AND SI.REFTABLE ='PDPRODUCT' ";
sql += "INNER JOIN PRODUCT PD ON PPD.PRODUCT=PD.LOID) a ";
sql += (whereString == "" ? "" : "WHERE " + whereString);
DataTable dt = OracleDB.ExecListCmd(sql);
return dt;
}