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);
}