public DataTable GetStockInShopList(StockInShopSearchData data)
{
string where = "STI.DOCTYPE = " + Constz.DocType.RecShop.LOID.ToString() + " ";
if (data.STOCKINCODE.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(STI.CODE) LIKE '%" + data.STOCKINCODE.Trim().ToUpper() + "%' ";
if (data.REQUISITIONCODE.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(STI.REFCODE) LIKE '%" + data.REQUISITIONCODE.Trim().ToUpper() + "%' ";
if (data.DATEFROM.Year != 1)
where += (where == "" ? "" : "AND ") + "TO_CHAR(STI.RECEIVEDATE, 'YYYYMMDD') >= TO_CHAR(" + OracleDB.QRDate(data.DATEFROM) + ", 'YYYYMMDD') ";
if (data.DATETO.Year != 1)
where += (where == "" ? "" : "AND ") + "TO_CHAR(STI.RECEIVEDATE, 'YYYMMDD') <= TO_CHAR(" + OracleDB.QRDate(data.DATETO) + ", 'YYYYMMDD') ";
//string sql = "SELECT 0 ORDERNO, STI.LOID, STI.CODE STOCKINCODE, STI.RECEIVEDATE, RQ.CODE REQUISITIONCODE, RQ.RESERVEDATE, STI.GRANDTOT, ";
//sql += "CASE STI.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 + "' END STATUSNAME ";
//sql += "FROM STOCKIN STI LEFT JOIN STOCKOUT" + Constz.ABBSERV + " STO ON STO.LOID = STI.REFLOID AND STI.REFTABLE = 'STOCKOUT' AND STO.DOCTYPE = " + Constz.DocType.ReqProduct.LOID.ToString() + " ";
//sql += "LEFT JOIN REQUISITION" + Constz.ABBSERV + " RQ ON RQ.LOID = STO.REFLOID AND STO.REFTABLE = 'REQUISITION' ";
string sql = "SELECT 0 ORDERNO, STI.LOID, STI.CODE STOCKINCODE, STI.RECEIVEDATE, STI.REFCODE REQUISITIONCODE, STI.GRANDTOT, ";
sql += "CASE STI.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 + "' END STATUSNAME ";
sql += "FROM STOCKIN STI ";
sql += (where == "" ? "" : "WHERE ") + where;
sql += "ORDER BY STI.CODE ";
return OracleDB.ExecListCmd(sql);
}
private StockInShopSearchData GetSearchData()
{
StockInShopSearchData data = new StockInShopSearchData();
data.DATEFROM = this.dtpDateFrom.Value.Date;
data.DATETO = this.dtpDateTo.Value.Date;
data.REQUISITIONCODE = this.txtRequisitionCode.Text.Trim();
data.STOCKINCODE = this.txtStockInCode.Text.Trim();
return data;
}
public DataTable GetStockInShopList(StockInShopSearchData data)
{
DataTable dt = SearchObj.GetStockInShopList(data);
int i = 1;
foreach (DataRow dRow in dt.Rows)
{
dRow["ORDERNO"] = i;
i += 1;
}
return dt;
}