private StockMovementSearchData GetData()
{
StockMovementSearchData data = new StockMovementSearchData();
data.DATEFROM = this.ctlDateFrom.DateValue;
data.DATETO = this.ctlDateTo.DateValue;
data.PRODUCTTYPE = Convert.ToDouble(this.cmbProductType.SelectedItem.Value);
data.PRODUCTGROUP = Convert.ToDouble(this.cmbProductGroup.SelectedItem.Value);
data.PRODUCTNAME = this.txtProductName.Text.Trim();
data.ZONE = Convert.ToDouble(this.cmbWarehouse.SelectedItem.Value);
data.ZONEFROM = Convert.ToDouble(this.cmbWarehouseFrom.SelectedItem.Value);
data.ZONETO = Convert.ToDouble(this.cmbWarehouseTo.SelectedItem.Value);
return data;
}
public DataTable GetStockMovementItemList(StockMovementSearchData data)
{
string whereString = "";
if (data.DATEFROM.Year != 1)
whereString += (whereString == "" ? "" : "AND ") + "TO_DATE(CDATE,'YYYY-MM-DD') >= " + OracleDB.QRDate(data.DATEFROM) + " ";
if (data.DATETO.Year != 1)
whereString += (whereString == "" ? "" : "AND ") + "TO_DATE(CDATE,'YYYY-MM-DD') <= " + OracleDB.QRDate(data.DATETO) + " ";
if (data.PRODUCTTYPE != 0)
whereString += (whereString == "" ? "" : "AND ") + "PTLOID = " + data.PRODUCTTYPE.ToString() + " ";
if (data.PRODUCTGROUP != 0)
whereString += (whereString == "" ? "" : "AND ") + "PGLOID = " + data.PRODUCTGROUP.ToString() + " ";
if (data.PRODUCTNAME.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "UPPER(PRODUCTNAME) LIKE '%" + data.PRODUCTNAME.Trim().ToUpper() + "%' ";
if (data.ZONE != 0)
whereString += (whereString == "" ? "" : "AND ") + "(FROMWAREHOUSE = " + data.ZONE.ToString() + " OR TOWAREHOUSE = " + data.ZONE.ToString() + ") ";
if (data.ZONEFROM != 0)
whereString += (whereString == "" ? "" : "AND ") + "FROMZONE = " + data.ZONEFROM.ToString() + " ";
if (data.ZONETO != 0)
whereString += (whereString == "" ? "" : "AND ") + "TOZONE = " + data.ZONETO.ToString() + " ";
string sql = "SELECT ROWNUM NO, CREATEON, FROMWAREHOUSENAME, FROMZONENAME, TOWAREHOUSENAME, TOZONENAME, PRODUCTNAME, QTY, ";
sql += "UNITNAME, DOCCODE, TYPENAME ";
sql += "FROM V_STOCKMOVEMENT_SEARCH ";
sql += (whereString == "" ? "" : "WHERE " + whereString);
DataTable dt = OracleDB.ExecListCmd(sql);
return dt;
}
