public List <customer> getCustomers(string userList, int opt)
{
customer cust;
List <customer> Customers = new List <customer>();
try
{
SqlConnection conn = new SqlConnection(Login.connString);
string query1 = "select a.rowID,a.customerID, a.Name,a.status,a.DocumentStatus," +
"ISNULL(a.CreateUser, ' ') AS CreateUser, ISNULL(a.ForwardUser, ' ') AS ForwardUser," +
" ISNULL(a.ApproveUser, ' ') AS ApproveUser,a.ForwarderList,a.LedgerType, " +
"ISNULL(b.Name, ' ') as Creator,ISNULL(c.Name, ' ') as Forwarder,ISNULL(d.Name, ' ') as Approver " +
"from Customer as a LEFT OUTER JOIN " +
"ViewUserEmployeeList as b on a.CreateUser = b.UserID LEFT OUTER JOIN " +
"ViewUserEmployeeList as c on a.ForwardUser = c.UserID LEFT OUTER JOIN " +
"ViewUserEmployeeList as d on a.ApproveUser = d.UserID " +
" where ((a.ForwardUser='******' and a.DocumentStatus between 2 and 98) " +
" or (a.CreateUser='******' and a.DocumentStatus=1) ) order by a.Name";
string query2 = "select a.rowID,a.customerID, a.Name,a.status,a.DocumentStatus," +
"ISNULL(a.CreateUser, ' ') AS CreateUser, ISNULL(a.ForwardUser, ' ') AS ForwardUser," +
" ISNULL(a.ApproveUser, ' ') AS ApproveUser,a.ForwarderList,a.LedgerType, " +
"ISNULL(b.Name, ' ') as Creator,ISNULL(c.Name, ' ') as Forwarder,ISNULL(d.Name, ' ') as Approver " +
"from Customer as a LEFT OUTER JOIN " +
"ViewUserEmployeeList as b on a.CreateUser = b.UserID LEFT OUTER JOIN " +
"ViewUserEmployeeList as c on a.ForwardUser = c.UserID LEFT OUTER JOIN " +
"ViewUserEmployeeList as d on a.ApproveUser = d.UserID " +
" where ((a.createuser='******' and a.DocumentStatus between 2 and 98 ) " +
" or (a.ForwarderList like '%" + userList + "%' and a.DocumentStatus between 2 and 98 and a.ForwardUser <> '" + Login.userLoggedIn + "')) order by a.Name";
string query3 = "select a.rowID,a.customerID, a.Name,a.status,a.DocumentStatus," +
"ISNULL(a.CreateUser, ' ') AS CreateUser, ISNULL(a.ForwardUser, ' ') AS ForwardUser," +
" ISNULL(a.ApproveUser, ' ') AS ApproveUser,a.ForwarderList,a.LedgerType, " +
"ISNULL(b.Name, ' ') as Creator,ISNULL(c.Name, ' ') as Forwarder,ISNULL(d.Name, ' ') as Approver " +
"from Customer as a LEFT OUTER JOIN " +
"ViewUserEmployeeList as b on a.CreateUser = b.UserID LEFT OUTER JOIN " +
"ViewUserEmployeeList as c on a.ForwardUser = c.UserID LEFT OUTER JOIN " +
"ViewUserEmployeeList as d on a.ApproveUser = d.UserID " +
" where ((a.createuser='******'" +
" or a.ForwarderList like '%" + userList + "%'" +
" or a.approveUser='******')" +
" and a.DocumentStatus = 99) order by a.Name";
string query6 = "select a.rowID,a.customerID, a.Name,a.status,a.DocumentStatus," +
"ISNULL(a.CreateUser, ' ') AS CreateUser, ISNULL(a.ForwardUser, ' ') AS ForwardUser," +
" ISNULL(a.ApproveUser, ' ') AS ApproveUser,a.ForwarderList,a.LedgerType, " +
"ISNULL(b.Name, ' ') as Creator,ISNULL(c.Name, ' ') as Forwarder,ISNULL(d.Name, ' ') as Approver " +
"from Customer as a LEFT OUTER JOIN " +
"ViewUserEmployeeList as b on a.CreateUser = b.UserID LEFT OUTER JOIN " +
"ViewUserEmployeeList as c on a.ForwardUser = c.UserID LEFT OUTER JOIN " +
"ViewUserEmployeeList as d on a.ApproveUser = d.UserID " +
" where a.DocumentStatus = 99 order by a.Name";
string query7 = "select a.rowID,a.customerID, a.Name,a.status,a.DocumentStatus," +
"ISNULL(a.CreateUser, ' ') AS CreateUser, ISNULL(a.ForwardUser, ' ') AS ForwardUser," +
" ISNULL(a.ApproveUser, ' ') AS ApproveUser,a.ForwarderList,a.LedgerType, " +
"ISNULL(b.Name, ' ') as Creator,ISNULL(c.Name, ' ') as Forwarder,ISNULL(d.Name, ' ') as Approver " +
"from Customer as a LEFT OUTER JOIN " +
"ViewUserEmployeeList as b on a.CreateUser = b.UserID LEFT OUTER JOIN " +
"ViewUserEmployeeList as c on a.ForwardUser = c.UserID LEFT OUTER JOIN " +
"ViewUserEmployeeList as d on a.ApproveUser = d.UserID " +
" where a.DocumentStatus = 99 order by a.customerID";
string query = "";
switch (opt)
{
case 1:
query = query1;
break;
case 2:
query = query2;
break;
case 3:
query = query3;
break;;
case 6:
query = query6;
break;
case 7:
query = query7;
break;
default:
query = "";
break;
}
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
cust = new customer();
cust.rowID = reader.GetInt32(0);
cust.CustomerID = reader.GetString(1);
cust.name = reader.GetString(2);
cust.status = reader.GetInt32(3);
cust.DocumentStatus = reader.GetInt32(4);
cust.CreateUser = reader.GetString(5);
cust.ForwardUser = reader.IsDBNull(6) ? "" : reader.GetString(6);
cust.ApproveUser = reader.IsDBNull(7) ? "" : reader.GetString(7);
cust.ForwarderList = reader.IsDBNull(8) ? "" : reader.GetString(8);
cust.LedgerType = reader.IsDBNull(9) ? "" : reader.GetString(9);
cust.Creator = reader.IsDBNull(10) ? "" : reader.GetString(10);
cust.Forwarder = reader.IsDBNull(11) ? "" : reader.GetString(11);
cust.Approver = reader.IsDBNull(12) ? "" : reader.GetString(12);
Customers.Add(cust);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
}
return(Customers);
}