public static DataTable GetCustRetProductList(SearchCustomerData data)
{
string where = "";
if (data.CODE.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(V.CUCODE) LIKE '%" + data.CODE.Trim().ToUpper() + "%' ";
if (data.FULLNAME.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(V.CUSTOMERNAME) LIKE '%" + data.FULLNAME.Trim().ToUpper() + "%' ";
if (data.CUSTOMERTYPE.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(V.CUSTOMERTYPE) = '" + data.CUSTOMERTYPE.Trim().ToUpper() + "' ";
if (data.MEMBERTYPE != 0)
where += (where == "" ? "" : "AND ") + "V.MEMBERTYPE = " + data.MEMBERTYPE.ToString() + " ";
string sql = "SELECT DISTINCT V.CULOID LOID, V.CUCODE CODE, V.CUSTOMERNAME, MT.NAME AS MEMBERTYPE, ";
sql += "CASE V.CUSTOMERTYPE WHEN '" + Constz.CustomerType.Company.Code + "' THEN '" + Constz.CustomerType.Company.Name + "' ";
sql += "WHEN '" + Constz.CustomerType.Government.Code + "' THEN '" + Constz.CustomerType.Government.Name + "' ";
sql += "WHEN '" + Constz.CustomerType.Personal.Code + "' THEN '" + Constz.CustomerType.Personal.Name + "' ";
sql += "ELSE '' END CUSTOMERTYPE ";
sql += "FROM V_PRODUCT_RETURNREQUEST V INNER JOIN MEMBERTYPE MT ON V.MEMBERTYPE = MT.LOID ";
sql += (where == "" ? "" : "WHERE " + where);
sql += "ORDER BY V.CUSTOMERNAME ";
return OracleDB.ExecListCmd(sql);
}
public static DataTable GetCustomerList(SearchCustomerData data)
{
string where = "C.ACTIVE = '" + Constz.ActiveStatus.Active + "' ";
if (data.CODE.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(C.CODE) LIKE '%" + data.CODE.Trim().ToUpper() + "%' ";
//if (data.FULLNAME.Trim() != "")
// where += (where == "" ? "" : "AND ") + "UPPER(C.NAME || ' ' || C.LASTNAME) LIKE '%" + data.FULLNAME.Trim().ToUpper() + "%' ";
if (data.NAME.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(C.NAME) LIKE '%" + data.NAME.Trim().ToUpper() + "%' ";
if (data.LASTNAME.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(C.LASTNAME) LIKE '%" + data.LASTNAME.Trim().ToUpper() + "%' ";
if (data.CUSTOMERTYPE.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(C.CUSTOMERTYPE) = '" + data.CUSTOMERTYPE.Trim().ToUpper() + "' ";
if (data.MEMBERTYPE != 0)
where += (where == "" ? "" : "AND ") + "C.MEMBERTYPE = " + data.MEMBERTYPE.ToString() + " ";
string sql = "SELECT C.LOID, C.CODE, TITLE.NAME || C.NAME || ' ' || C.LASTNAME AS CUSTOMERNAME, MT.NAME AS MEMBERTYPE, ";
sql += "CASE C.CUSTOMERTYPE WHEN '" + Constz.CustomerType.Company.Code + "' THEN '" + Constz.CustomerType.Company.Name + "' ";
sql += "WHEN '" + Constz.CustomerType.Government.Code + "' THEN '" + Constz.CustomerType.Government.Name + "' ";
sql += "WHEN '" + Constz.CustomerType.Personal.Code + "' THEN '" + Constz.CustomerType.Personal.Name + "' ";
sql += "ELSE '' END CUSTOMERTYPE ";
sql += "FROM CUSTOMER C INNER JOIN MEMBERTYPE MT ON C.MEMBERTYPE = MT.LOID AND C.EPDATE > SYSDATE AND MT.ACTIVE = '" + Constz.ActiveStatus.Active + "' ";
sql += "LEFT JOIN TITLE ON TITLE.LOID = C.TITLE ";
sql += (where == "" ? "" : "WHERE " + where);
sql += "ORDER BY TITLE.NAME || C.NAME || ' ' || C.LASTNAME ";
return OracleDB.ExecListCmd(sql);
}
public void SetData(SearchCustomerData data)
{
this.txtCode.Text = data.CODE;
this.txtName.Text = data.NAME;
this.txtLName.Text = data.LASTNAME;
this.cmbCustomerType.SelectedValue = data.CUSTOMERTYPE;
this.cmbMemberType.SelectedValue = data.MEMBERTYPE.ToString();
}
private SearchCustomerData GetData()
{
SearchCustomerData data = new SearchCustomerData();
data.CODE = this.txtCode.Text.Trim();
//data.FULLNAME = this.txtName.Text.Trim();
data.NAME = this.txtName.Text.Trim();
data.LASTNAME = this.txtLName.Text.Trim();
data.CUSTOMERTYPE = this.cmbCustomerType.SelectedValue.ToString();
data.MEMBERTYPE = Convert.ToDouble(this.cmbMemberType.SelectedValue);
return data;
}
private void SearchData()
{
this.txtCustomer.Text = "";
SearchFlow flow = new SearchFlow();
SearchCustomerData data = new SearchCustomerData();
data.CODE = this.txtCode.Text.Trim();
data.FULLNAME = this.txtFullName.Text.Trim();
this.grvCustomer.DataSource = flow.GetSupplierList(data);
this.grvCustomer.DataBind();
if (this.grvCustomer.SelectedValue == null)
this.txtCustomer.Text = "";
else
this.txtCustomer.Text = this.grvCustomer.SelectedValue.ToString();
}
private void SearchData()
{
this.txtCustomer.Text = "";
SearchFlow flow = new SearchFlow();
SearchCustomerData data = new SearchCustomerData();
data.CODE = this.txtCode.Text.Trim();
data.CUSTOMERTYPE = this.cmbCustomerType.SelectedItem.Value;
data.FULLNAME = this.txtFullName.Text.Trim();
data.MEMBERTYPE = Convert.ToDouble(this.cmbMemberType.SelectedItem.Value);
this.grvCustomer.DataSource = flow.GetCustomerList(data);
this.grvCustomer.DataBind();
if (this.grvCustomer.SelectedValue == null)
this.txtCustomer.Text = "";
else
this.txtCustomer.Text = this.grvCustomer.SelectedValue.ToString();
}
public static DataTable GetSupplierList(SearchCustomerData data)
{
string where = "ACTIVE = '" + Constz.ActiveStatus.Active + "' ";
if (data.CODE.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(CODE) LIKE '%" + data.CODE.Trim().ToUpper() + "%' ";
if (data.FULLNAME.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(SUPPLIERNAME) LIKE '%" + data.FULLNAME.Trim().ToUpper() + "%' ";
string sql = "SELECT * FROM SUPPLIER ";
sql += (where == "" ? "" : "WHERE " + where);
sql += "ORDER BY CODE ";
return OracleDB.ExecListCmd(sql);
}
public DataTable GetSupplierList(SearchCustomerData data)
{
return SearchDAL.GetSupplierList(data);
}
public DataTable GetCustRetProductList(SearchCustomerData data)
{
return SearchDAL.GetCustRetProductList(data);
}
