private static User _GetUser(OpenCbsReader pReader) { User user = new User { Id = pReader.GetInt("user_id"), UserName = pReader.GetString("user_name"), 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") }; user.SetRole(pReader.GetString("role_code")); user.UserRole = new Role { RoleName = pReader.GetString("role_name"), Id = pReader.GetInt("role_id") }; return(user); }
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); }
public List <User> SelectAll() { const string q = @"SELECT id, deleted, user_name, first_name, last_name, user_pass, 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"), UserName = r.GetString("user_name"), Password = r.GetString("user_pass"), Mail = r.GetString("mail"), Sex = r.GetChar("sex"), HasContract = r.GetInt("num_contracts") > 0 }; users.Add(u); } } return(users); }
public List <ProjetSearchResult> SelectProjectByCriteres(int pageNumber, string pQuery) { List <ProjetSearchResult> list = new List <ProjetSearchResult>(); string SELECT_FROM_PROJET_ = @" SELECT DISTINCT pro.id,pro.code,pro.name as name_project,pro.aim,pers.first_name, pers.last_name,tie.client_type_code,tie.id as tiers_id,corp.name as companyName FROM (Projects as pro INNER JOIN Tiers tie on pro.tiers_id=tie.id ) LEFT JOIN Corporates corp on corp.id=tie.id LEFT JOIN Persons pers on pers.id=tie.id ) maTable" ; string CloseWhere = @" WHERE ( companyName LIKE @companyName OR code LIKE @code OR name_project LIKE @nameProject OR aim LIKE @aim OR last_name LIKE @lastName OR first_name LIKE @firtName )) maTable"; QueryEntity q = new QueryEntity(pQuery, SELECT_FROM_PROJET_, CloseWhere); string pSqlText = q.ConstructSQLEntityByCriteresProxy(20, (pageNumber - 1) * 20); using (SqlConnection conn = GetConnection()) using (OpenCbsCommand select = new OpenCbsCommand(pSqlText, conn)) { foreach (var item in q.DynamiqParameters()) { select.AddParam(item.Key, string.Format("%{0}%", item.Value)); } using (OpenCbsReader reader = select.ExecuteReader()) { while (reader.Read()) { ProjetSearchResult resultat = new ProjetSearchResult(); resultat.Id = reader.GetInt("id"); resultat.Code = reader.GetString("code"); resultat.ProjectName = reader.GetString("name_project"); resultat.CompanyName = reader.GetString("companyName"); resultat.Aim = reader.GetString("aim"); resultat.TiersId = reader.GetInt("tiers_id"); resultat.Status = reader.GetChar("client_type_code"); resultat.LastName = reader.GetString("last_name"); resultat.FirstName = reader.GetString("first_name"); //resultat.ContractCode = reader.GetString("contract_code"); list.Add(resultat); } } } return(list); }
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)" ; 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"), UserName = r.GetString("user_name"), Password = r.GetString("user_pass"), Mail = r.GetString("mail"), Sex = r.GetChar("sex"), HasContract = r.GetInt("num_contracts") > 0 }; users.Add(u); } } } return(users); }
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); }
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); }
private static User _GetUser(OpenCbsReader pReader) { User user = new User { Id = pReader.GetInt("user_id"), UserName = pReader.GetString("user_name"), 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") }; user.SetRole(pReader.GetString("role_code")); user.UserRole = new Role { RoleName = pReader.GetString("role_name"), Id = pReader.GetInt("role_id") }; return user; }