Пример #1
0
        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);
        }