public List<ClientSearchResult> SearchAllByCriteres(string pQuery, int activeOnly, int currentPage, int includePersons, int includeGroups, int includeVillages) { List<ClientSearchResult> list = new List<ClientSearchResult>(); using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = GenerateSearchQuery(false, pQuery, activeOnly, currentPage, includePersons, includeGroups, includeVillages, conn)) using (OpenCbsReader r = c.ExecuteReader()) { if (!r.Empty) { while (r.Read()) { ClientSearchResult result = new ClientSearchResult(); result.Id = r.GetInt("id"); switch (r.GetChar("type")) { case 'I': result.Type = OClientTypes.Person; break; case 'G': result.Type = OClientTypes.Group; break; case 'V': result.Type = OClientTypes.Village; break; case 'C': result.Type = OClientTypes.Corporate; break; default: result.Type = OClientTypes.Person; break; } result.Name = r.GetString("name"); result.Active = r.GetBool("active"); result.LoanCycle = r.GetInt("loan_cycle"); result.District = r.GetString("district"); result.City = r.GetString("city"); result.BadClient = r.GetBool("bad_client"); result.PassportNumber = r.GetString("passport_number"); result.MemberOf = r.GetString("group_name"); list.Add(result); } } } return list; }
protected virtual void HandleTierSelect(ClientSearchResult pClient) { try { _client = ServicesProvider.GetInstance().GetClientServices().FindTiers(pClient.Id, pClient.Type); /* * This code is for loading compulsory savings. Compulsory savings are being * loaded here because in LoanManager class SavingsManager trigers problems. * Ruslan Kazakov */ if (_client.Projects != null) foreach (Project project in _client.Projects) if (project.Credits != null) foreach (Loan loan in project.Credits) loan.CompulsorySavings = ServicesProvider.GetInstance().GetSavingServices().GetSavingForLoan(loan.Id, true); if (test != 2) { var mainForm = (MainView) Application.OpenForms[0]; if (pClient.Type == OClientTypes.Person) mainForm.InitializePersonForm((Person)_client, null); if (pClient.Type == OClientTypes.Group) mainForm.InitializeGroupForm((Group)_client, null); if (pClient.Type == OClientTypes.Corporate) mainForm.InitializeCorporateForm((Corporate)_client, null); if (OClientTypes.Village == pClient.Type) mainForm.InitializeVillageForm((Village)_client); } else DialogResult = DialogResult.OK; Close(); } catch (Exception ex) { new frmShowError(CustomExceptionHandler.ShowExceptionText(ex)).ShowDialog(); } }
public List<ClientSearchResult> SetClientSearchResult(string q, string pQuery, OClientTypes pClientType) { List<ClientSearchResult> list = new List<ClientSearchResult>(); using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@name", string.Format("%{0}%", pQuery)); c.AddParam("@district", string.Format("%{0}%", pQuery)); c.AddParam("@city", string.Format("%{0}%", pQuery)); if (pClientType == OClientTypes.Person) { c.AddParam("@passportNumber", string.Format("%{0}%", pQuery)); } using (OpenCbsReader r = c.ExecuteReader()) { if (!r.Empty) { while (r.Read()) { ClientSearchResult result = new ClientSearchResult(); result.Id = r.GetInt("id"); result.Type = r.GetChar("type") == 'I' ? OClientTypes.Person : OClientTypes.Group; result.Name = r.GetString("name"); result.Active = r.GetBool("active"); result.LoanCycle = pClientType == OClientTypes.Group ? 0 : r.GetInt("loan_cycle"); result.District = r.GetString("district"); result.City = r.GetString("city"); result.BadClient = r.GetBool("bad_client"); result.PassportNumber = r.GetString("passport_number"); result.MemberOf = r.GetString("group_name"); list.Add(result); } } } } return list; }
public List<ClientSearchResult> SetCorporatesSearchResult(string q, string pQuery, OClientTypes pClientType) { List<ClientSearchResult> list = new List<ClientSearchResult>(); using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@name", string.Format("%{0}%", pQuery)); c.AddParam("@district", string.Format("%{0}%", pQuery)); c.AddParam("@city", string.Format("%{0}%", pQuery)); c.AddParam("@siret", string.Format("%{0}%", pQuery)); using (OpenCbsReader r = c.ExecuteReader()) { if (!r.Empty) { while (r.Read()) { ClientSearchResult result = new ClientSearchResult(); result.Id = r.GetInt("id"); result.Name = r.GetString("name"); result.Active = r.GetBool("active"); result.District = r.GetString("district"); result.City = r.GetString("city"); result.Siret = r.GetString("siret"); result.Type = OClientTypes.Corporate; list.Add(result); } } } } return list; }
public List<ClientSearchResult> SearchVillagesByCriteres(int pageNumber, string pQuery, int isActive) { List<ClientSearchResult> list = new List<ClientSearchResult>(); string SELECT_FROM_PROJET_ = @" SELECT TOP 100 percent Villages.id,Tiers.client_type_code AS type,Villages.name,Tiers.active,Tiers.loan_cycle AS loan_cycle, Districts.name AS district, Tiers.city AS city, Tiers.bad_client AS bad_client, '-' AS passport_number, '-' AS village_name FROM Villages INNER JOIN Tiers ON Villages.id = Tiers.id INNER JOIN Districts ON Districts.id = Tiers.district_id ORDER BY Villages.id ) maTable"; string CloseWhere = @" WHERE (name LIKE @name OR district LIKE @district OR city LIKE @city ) and active = @isActive) maTable"; QueryEntity q = new QueryEntity(pQuery, SELECT_FROM_PROJET_, CloseWhere); string pSqlText = q.ConstructSQLEntityByCriteresProxy(20, (pageNumber - 1) * 20); using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(pSqlText, conn)) { foreach (var item in q.DynamiqParameters()) { c.AddParam(item.Key, string.Format("%{0}%", item.Value)); } using (OpenCbsReader r = c.ExecuteReader()) { if (!r.Empty) { while (r.Read()) { ClientSearchResult result = new ClientSearchResult(); result.Id = r.GetInt("id"); result.Type = OClientTypes.Village; result.Name = r.GetString("name"); result.Active = r.GetBool("active"); result.LoanCycle = r.GetInt("loan_cycle"); result.District = r.GetString("district"); result.City = r.GetString("city"); result.BadClient = r.GetBool("bad_client"); result.PassportNumber = r.GetString("passport_number"); result.MemberOf = r.GetString("village_name"); list.Add(result); } } } } return list; }
public List<ClientSearchResult> SearchPersonByCriteres(int pageNumber, string pQuery) { List<ClientSearchResult> list = new List<ClientSearchResult>(); string SELECT_FROM_PROJET_ = @" SELECT TOP 100 percent pers.id, Tiers.client_type_code AS type,pers.first_name + SPACE(1) + pers.last_name AS name,Tiers.active,Tiers.loan_cycle AS loan_cycle, dis.name AS district, Tiers.city AS city,Tiers.bad_client AS bad_client, pers.identification_data AS passport_number,ISNULL((SELECT TOP 1 Groups.name from Groups INNER JOIN PersonGroupBelonging on Groups.id=PersonGroupBelonging.group_id WHERE PersonGroupBelonging.person_id=pers.id),'-') AS group_name FROM Persons pers INNER JOIN Tiers ON pers.id = Tiers.id INNER JOIN Districts dis ON dis.id = Tiers.district_id ORDER BY pers.id ) maTable"; string CloseWhere = @" WHERE ( passport_number LIKE @passeportNumber OR name LIKE @name OR district LIKE @district OR city LIKE @city )) maTable"; QueryEntity q = new QueryEntity(pQuery, SELECT_FROM_PROJET_, CloseWhere); string pSqlText = q.ConstructSQLEntityByCriteresProxy(20, (pageNumber - 1) * 20); using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(pSqlText, conn)) { foreach (var item in q.DynamiqParameters()) { c.AddParam(item.Key, string.Format("%{0}%", item.Value)); } OClientTypes pClientType = OClientTypes.Person; using (OpenCbsReader r = c.ExecuteReader()) { if (!r.Empty) { while (r.Read()) { ClientSearchResult result = new ClientSearchResult(); result.Id = r.GetInt("id"); result.Type = r.GetChar("type") == 'I' ? OClientTypes.Person : OClientTypes.Group; result.Name = r.GetString("name"); result.Active = r.GetBool("active"); result.LoanCycle = pClientType == OClientTypes.Group ? 0 : r.GetInt("loan_cycle"); result.District = r.GetString("district"); result.City = r.GetString("city"); result.BadClient = r.GetBool("bad_client"); result.PassportNumber = r.GetString("passport_number"); result.MemberOf = r.GetString("group_name"); list.Add(result); } } } } return list; }
public List<ClientSearchResult> SearchInactivePersonsByCriteres(int pageNumber, string query) { List<ClientSearchResult> list = new List<ClientSearchResult>(); const string sql = @"SELECT TOP 100 PERCENT p.id, t.client_type_code AS type, p.first_name + SPACE(1) + p.last_name AS name, t.active, t.loan_cycle, d.name AS district, t.city, t.bad_client, p.identification_data AS passport_number FROM Persons AS p LEFT JOIN Tiers AS t ON p.id = t.id LEFT JOIN Districts AS d ON t.district_id = d.id WHERE active = 0 and Tiers.branch_id in ( select branch_id from dbo.UsersBranches where user_id = @user_id ) ) maTable"; const string where = @" WHERE active = 0 AND ( passport_number LIKE @passportNumber OR name LIKE @name OR district LIKE @district OR city LIKE @city )) maTable"; QueryEntity q = new QueryEntity(query, sql, where); string sqlText = q.ConstructSQLEntityByCriteresProxy(20, (pageNumber - 1) * 20); using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(sqlText, conn)) { foreach (var item in q.DynamiqParameters()) { c.AddParam(item.Key, string.Format("%{0}%", item.Value)); } c.AddParam("@user_id", User.CurrentUser.Id); using (OpenCbsReader r = c.ExecuteReader()) { if (null == r || r.Empty) return list; while (r.Read()) { ClientSearchResult result = new ClientSearchResult(); result.Id = r.GetInt("id"); result.Type = OClientTypes.Person; result.Name = r.GetString("name"); result.Active = r.GetBool("active"); result.Active = r.GetBool("active"); result.LoanCycle = r.GetInt("loan_cycle"); result.District = r.GetString("district"); result.City = r.GetString("city"); result.BadClient = r.GetBool("bad_client"); result.PassportNumber = r.GetString("passport_number"); result.MemberOf = "--"; list.Add(result); } } } return list; }
public List<ClientSearchResult> SearchCorporateByCriteres(int onlyActive, int pageNumber, string pQuery) { string sign; if (onlyActive == 0 || onlyActive == 1) { sign = "="; } // active or not active clients else if (onlyActive == 2) { sign = "<"; } // everyone else { sign = ">"; } // no one List<ClientSearchResult> list = new List<ClientSearchResult>(); string SELECT_FROM_PROJET_ = String.Format(@"SELECT TOP 100 percent Corporates.id, Corporates.name, Corporates.siret, Tiers.active, Districts.name AS district, Tiers.loan_cycle AS loan_cycle, Tiers.city AS city FROM Corporates INNER JOIN Tiers ON Corporates.id = Tiers.id INNER JOIN Districts ON Districts.id = Tiers.district_id WHERE active {0} {1} ORDER BY Corporates.name ) maTable", sign, onlyActive); string CloseWhere = @" WHERE ( siret LIKE @siret OR name LIKE @name OR district LIKE @district OR city LIKE @city )) maTable"; var q = new QueryEntity(pQuery, SELECT_FROM_PROJET_, CloseWhere); string pSqlText = q.ConstructSQLEntityByCriteresProxy(20, (pageNumber - 1) * 20); using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(pSqlText, conn)) { foreach (var item in q.DynamiqParameters()) c.AddParam(item.Key, string.Format("%{0}%", item.Value)); using (OpenCbsReader r = c.ExecuteReader()) { if (!r.Empty) { while (r.Read()) { var result = new ClientSearchResult { Id = r.GetInt("id"), Name = r.GetString("name"), Active = r.GetBool("active"), District = r.GetString("district"), City = r.GetString("city"), Siret = r.GetString("siret"), Type = OClientTypes.Corporate, LoanCycle = r.GetInt("loan_cycle") }; list.Add(result); } } } } return list; }
protected override void HandleTierSelect(ClientSearchResult pClient) { var clientServices = ServicesProvider.GetInstance().GetClientServices(); var person = clientServices.FindPersonById(pClient.Id); Value = new CustomClientField(person); }