Exemplo n.º 1
0
        private static User _GetUser(OpenCbsReader pReader)
        {
            User user = new User
            {
                Id            = pReader.GetInt("user_id"),
                UserName      = pReader.GetString("user_name"),
                Password      = pReader.GetString("user_pass"),
                FirstName     = pReader.GetString("first_name"),
                LastName      = pReader.GetString("last_name"),
                Mail          = pReader.GetString("mail"),
                IsDeleted     = pReader.GetBool("deleted"),
                HasContract   = (pReader.GetInt("contract_count") != 0),
                Sex           = pReader.GetChar("sex"),
                Phone         = pReader.GetString("phone"),
                TimedOut      = pReader.GetNullBool("timed_out"),
                IsExpired     = pReader.GetNullBool("is_expired"),
                IsReset       = pReader.GetNullBool("is_reset"),
                LastUpdated   = pReader.GetNullDateTime("last_updated"),
                LoginAttempt  = pReader.GetNullInt("login_attempt"),
                FrapidLoginId = pReader.GetNullInt("frapid_login_id"),
            };

            user.Secret.Question = pReader.GetString("user_sq");
            user.SetRole(pReader.GetString("role_code"));

            user.UserRole = new Role
            {
                RoleName = pReader.GetString("role_name"),
                Id       = pReader.GetInt("role_id")
            };

            return(user);
        }
Exemplo n.º 2
0
        public List <User> SellectAllWithoutTellerOfBranch(Branch branch, User user)
        {
            /*const string q = @"SELECT
             *                  u.id,
             *                  u.deleted,
             *                  u.user_name,
             *                  u.first_name,
             *                  u.last_name,
             *                  u.user_pass,
             *                  u.mail,
             *                  u.sex,
             *                  u.phone,
             *                  (SELECT COUNT(*)
             *                  FROM dbo.Credit
             *                  WHERE loanofficer_id = u.id) AS num_contracts
             *                  FROM dbo.Users AS u
             *                  INNER JOIN dbo.UsersBranches ub ON ub.user_id = u.id
             *                  INNER JOIN UserRole ur ON ur.user_id = u .id
             *                  INNER JOIN Roles r ON r.id = ur.role_id
             *                  WHERE u.deleted = 0 AND r.role_of_teller = 1
             *                  AND (u.id NOT IN (SELECT user_id FROM Tellers WHERE deleted = 0) OR u.id = @user_id)
             *                  AND ub.branch_id = @branch_id AND u.id IN (SELECT @boss_id
             *                                                                                             UNION ALL
             *                                                                                             SELECT subordinate_id
             *                                                                                             FROM dbo.UsersSubordinates
             *                                                                                             WHERE user_id = @boss_id)";
             */

            const string q     = @"SELECT 
                                u.id, 
                                u.deleted, 
                                u.last_updated,
                                u.timed_out,
                                u.is_expired,
                                u.is_reset,
                                u.login_attempt,

                                u.user_name, 
                                u.first_name,
                                u.last_name, 
                                u.user_pass,
                                u.user_sq,
                                u.user_sqa,
                                u.mail, 
                                u.sex,
                                u.phone,
                                (SELECT COUNT(*)
                                FROM dbo.Credit 
                                WHERE loanofficer_id = u.id) AS num_contracts
                                FROM dbo.Users AS u
                                INNER JOIN dbo.UsersBranches ub ON ub.user_id = u.id
                                INNER JOIN UserRole ur ON ur.user_id = u .id
                                INNER JOIN Roles r ON r.id = ur.role_id
                                WHERE u.deleted = 0
                                AND (u.id NOT IN (SELECT user_id FROM Tellers WHERE deleted = 0) OR u.id = @user_id)
                                AND ub.branch_id = @branch_id AND u.id IN (SELECT @boss_id
								                                           UNION ALL
								                                           SELECT subordinate_id
								                                           FROM dbo.UsersSubordinates
								                                           WHERE user_id = @boss_id)"                                ;
            List <User>  users = new List <User>();

            using (SqlConnection conn = GetConnection())
                using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
                {
                    c.AddParam("@branch_id", branch.Id);
                    c.AddParam("@boss_id", User.CurrentUser.Id);
                    c.AddParam("@user_id", user == null ? 0 : user.Id);
                    using (OpenCbsReader r = c.ExecuteReader())
                    {
                        if (r.Empty)
                        {
                            return(users);
                        }

                        while (r.Read())
                        {
                            User u = new User
                            {
                                Id           = r.GetInt("id"),
                                FirstName    = r.GetString("first_name"),
                                LastName     = r.GetString("last_name"),
                                IsDeleted    = r.GetBool("deleted"),
                                TimedOut     = r.GetNullBool("timed_out"),
                                IsExpired    = r.GetNullBool("is_expired"),
                                LoginAttempt = r.GetNullInt("login_attempt"),
                                LastUpdated  = r.GetNullDateTime("last_updated"),
                                UserName     = r.GetString("user_name"),
                                Password     = r.GetString("user_pass"),
                                Mail         = r.GetString("mail"),
                                Sex          = r.GetChar("sex"),
                                HasContract  = r.GetInt("num_contracts") > 0
                            };
                            u.Secret.Question = r.GetString("user_sq");
                            u.Secret.Answer   = r.GetString("user_sqa");

                            users.Add(u);
                        }
                    }
                }
            return(users);
        }
Exemplo n.º 3
0
        public List <User> SelectAll()
        {
            const string q = @"SELECT 
                                 id, 
                                 deleted, 
                                 last_updated,
                                 timed_out,
                                 is_expired,
                                 is_reset,
                                 login_attempt,
                                 frapid_login_id,

                                 user_name, 
                                 first_name,
                                 last_name, 
                                 user_pass,
                                 user_sq,
                                 user_sqa,
                                 mail, 
                                 sex,
                                 phone, 
                                (SELECT COUNT(*)
                                 FROM dbo.Credit 
                                 WHERE loanofficer_id = u.id) AS num_contracts
                             FROM dbo.Users AS u";

            List <User> users = new List <User>();

            using (SqlConnection conn = GetConnection())
                using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
                    using (OpenCbsReader r = c.ExecuteReader())
                    {
                        if (r.Empty)
                        {
                            return(users);
                        }

                        while (r.Read())
                        {
                            User u = new User
                            {
                                Id            = r.GetInt("id"),
                                FirstName     = r.GetString("first_name"),
                                LastName      = r.GetString("last_name"),
                                IsDeleted     = r.GetBool("deleted"),
                                TimedOut      = r.GetNullBool("timed_out"),
                                IsExpired     = r.GetNullBool("is_expired"),
                                IsReset       = r.GetNullBool("is_reset"),
                                LastUpdated   = r.GetNullDateTime("last_updated"),
                                LoginAttempt  = r.GetNullInt("login_attempt"),
                                FrapidLoginId = r.GetNullInt("frapid_login_id"),
                                UserName      = r.GetString("user_name"),
                                Password      = r.GetString("user_pass"),
                                Mail          = r.GetString("mail"),
                                Sex           = r.GetChar("sex"),
                                HasContract   = r.GetInt("num_contracts") > 0
                            };
                            u.Secret.Question = r.GetString("user_sq");
                            u.Secret.Answer   = r.GetString("user_sqa");

                            users.Add(u);
                        }
                    }
            return(users);
        }