private StockinReturnSearchData GetData()
{
StockinReturnSearchData data = new StockinReturnSearchData();
data.CODE = this.txtCode.Text.Trim();
data.DATEFROM = this.ctlDateFrom.DateValue;
data.DATETO = this.ctlDateTo.DateValue;
//data.PRODUCT = Convert.ToDouble(this.cmbProduct.SelectedItem.Value);
data.PRODUCTNAME = this.txtProduct.Text.Trim();
data.RQCODE = this.txtRQCode.Text.Trim();
data.STATUSFROM = this.cmbStatusFrom.SelectedItem.Value;
data.STATUSTO = this.cmbStatusTo.SelectedItem.Value;
return data;
}
private StockinReturnSearchData GetData()
{
StockinReturnSearchData data = new StockinReturnSearchData();
data.CODE = this.txtCode.Text.Trim();
data.DATEFROM = this.ctlDateFrom.DateValue;
data.DATETO = this.ctlDateTo.DateValue;
data.DOCTYPE = Convert.ToDouble(this.cmbDocType.SelectedItem.Value);
data.CUSTOMER = Convert.ToDouble(this.cmbCustomer.SelectedItem.Value);
data.NAME = this.txtCName.Text.Trim();
data.LNAME = this.txtCLName.Text.Trim();
data.STATUSFROM = this.cmbStatusFrom.SelectedItem.Value;
data.STATUSTO = this.cmbStatusTo.SelectedItem.Value;
return data;
}
public DataTable GetItemList(StockinReturnSearchData data)
{
string whereString = "";
if (data.DOCTYPE != 0)
whereString += (whereString == "" ? "" : "AND ") + "DOCTYPE = " + data.DOCTYPE.ToString() + " ";
if (data.CODE.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "UPPER(CODE) LIKE '%" + data.CODE.Trim().ToUpper() + "%' ";
if (data.DATEFROM.Year != 1)
whereString += (whereString == "" ? "" : "AND ") + "RECEIVEDATE >= " + OracleDB.QRDate(data.DATEFROM) + " ";
if (data.DATETO.Year != 1)
whereString += (whereString == "" ? "" : "AND ") + "RECEIVEDATE <= " + OracleDB.QRDate(data.DATETO) + " ";
//if (data.CUSTOMER != 0)
// whereString += (whereString == "" ? "" : "AND ") + "SENDER = " + data.CUSTOMER.ToString() + " ";
if (data.NAME.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "UPPER(CNAME) LIKE '%" + data.NAME.Trim().ToUpper() + "%' ";
if (data.LNAME.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "UPPER(CLASTNAME) LIKE '%" + data.LNAME.Trim().ToUpper() + "%' ";
if (data.STATUSFROM.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "RANK >= '" + OracleDB.QRText(data.STATUSFROM.Trim()) + "' ";
if (data.STATUSTO.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "RANK <= '" + OracleDB.QRText(data.STATUSTO.Trim()) + "' ";
string sql = "select * from ( SELECT ROWNUM NO, ST.LOID, ST.DOCTYPE, VF.DOCNAME, ST.CODE, ST.RECEIVEDATE, ST.CREATEBY, ST.SENDER, VC.CUSTOMERNAME, ST.GRANDTOT,VC.CNAME,VC.CLASTNAME, ";
sql += "CASE ST.STATUS WHEN '" + Constz.StockinReturn.Status.Waiting.Code + "' THEN '" + Constz.StockinReturn.Status.Waiting.Name + "' ";
sql += "WHEN '" + Constz.StockinReturn.Status.Approved.Code + "' THEN '" + Constz.StockinReturn.Status.Approved.Name + "' ";
sql += "WHEN '" + Constz.StockinReturn.Status.Void.Code + "' THEN '" + Constz.StockinReturn.Status.Void.Name + "' ";
sql += "ELSE '' END AS STATUSNAME, ";
sql += "CASE ST.STATUS WHEN '" + Constz.StockinReturn.Status.Waiting.Code + "' THEN '" + Constz.StockinReturn.Status.Waiting.Rank + "' ";
sql += "WHEN '" + Constz.StockinReturn.Status.Approved.Code + "' THEN '" + Constz.StockinReturn.Status.Approved.Rank + "' ";
sql += "WHEN '" + Constz.StockinReturn.Status.Void.Code + "' THEN '" + Constz.StockinReturn.Status.Void.Rank + "' ";
sql += "ELSE '' END AS RANK ";
sql += "FROM STOCKIN ST INNER JOIN V_RETURNTYPE_FG VF ON ST.DOCTYPE = VF.DOCTYPE ";
sql += "INNER JOIN V_CUSTOMER VC ON ST.SENDER = VC.LOID) ";
sql += (whereString == "" ? "" : "WHERE " + whereString);
sql += "ORDER BY CODE ";
DataTable dt = OracleDB.ExecListCmd(sql);
for (int i = 0; i < dt.Rows.Count; ++i)
{
dt.Rows[i]["NO"] = i + 1;
}
return dt;
}
public DataTable GetReturnWHList(StockinReturnSearchData data)
{
string whereString = "";
if (data.PRODUCTNAME.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "UPPER(PRODUCTNAME) LIKE '%" + data.PRODUCTNAME.Trim().ToUpper() + "%' ";
if (data.CODE.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "UPPER(CODE) =" + data.CODE.Trim().ToUpper() + " ";
if (data.RQCODE.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "UPPER(REQCODE) =" + data.RQCODE.Trim().ToUpper() + " ";
if (data.DATEFROM.Year != 1)
whereString += (whereString == "" ? "" : "AND ") + "RECEIVEDATE >= " + OracleDB.QRDate(data.DATEFROM) + " ";
if (data.DATETO.Year != 1)
whereString += (whereString == "" ? "" : "AND ") + "RECEIVEDATE <= " + OracleDB.QRDate(data.DATETO) + " ";
if (data.STATUSFROM.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "RANK >= '" + OracleDB.QRText(data.STATUSFROM.Trim()) + "' ";
if (data.STATUSTO.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "RANK <= '" + OracleDB.QRText(data.STATUSTO.Trim()) + "' ";
string sql = "select * from ( SELECT ROWNUM NO,ST.LOID, ST.CODE, ST.RECEIVEDATE, VM.PDLOID, VM.PRODUCTNAME, VM.PDQTY, VM.UNITNAME,VM.LOTNO, VM.REQCODE, ";
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 V_MATERIAL_RETURN_POPUP_LIST VM ON ST.REFTABLE = 'REQUISITION' AND ST.REFLOID = VM.LOID) A ";
sql += (whereString == "" ? "" : "WHERE " + whereString);
sql += "ORDER BY RECEIVEDATE ";
DataTable dt = OracleDB.ExecListCmd(sql);
for (int i = 0; i < dt.Rows.Count; ++i)
{
dt.Rows[i]["NO"] = i + 1;
}
return dt;
}
