public void TestConstructSQLByCriteres()
{
string search = "ASSDER,DER,1";
string SELECT_FROM_PROJET_ = @" SELECT TOP 100 percent Contracts.id , Contracts.contract_code, Contracts.start_date, Contracts.close_date,Persons.identification_data as identification_data,
Credit.amount, Credit.loanofficer_id,Tiers.client_type_code, ISNULL(Users.first_name + SPACE(1) + Users.last_name,
Users.user_name) AS user_name ,ISNULL(Groups.name,Persons.first_name + SPACE(1) + Persons.last_name) AS client_name ,
ISNULL((SELECT TOP 1 Persons.first_name + SPACE(1) + Persons.last_name FROM Persons WHERE Persons.id=Credit.loanofficer_id),'-') as loanofficer_name
FROM Contracts INNER JOIN Credit ON Contracts.id = Credit.id
INNER JOIN Projects ON Contracts.project_id = Projects.id
INNER JOIN Tiers ON Projects.tiers_id = Tiers.id
INNER JOIN Users ON Users.id = Credit.loanofficer_id LEFT OUTER JOIN Persons ON Tiers.id = Persons.id LEFT OUTER JOIN
Groups ON Tiers.id = Groups.id ) maTable ";
string CloseWhere = @" WHERE (contract_code LIKE %@contractCode% OR client_name LIKE %@clientName% OR user_name LIKE %@userName% OR identification_data LIKE %@numberPasseport% OR loanofficer_name LIKE %@loanofficerName% )) maTable";
QueryEntity q = new QueryEntity(search, SELECT_FROM_PROJET_, CloseWhere);
string resultat = q.ConstructSQLEntityByCriteresProxy(15, 0);
Assert.IsTrue(q.DynamiqParameters().Count > 0);
}
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 int GetNumberProject(string pQuery)
{
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.ConstructSQLEntityNumberProxy();
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())
{
reader.Read();
return reader.GetInt(0);
}
}
}
public List<SavingSearchResult> SearchSavingContractByCritere(int pPageNumber, string pQuery, bool all, bool activeContractOnly)
{
List<SavingSearchResult> list = new List<SavingSearchResult>();
string sql = @"SELECT
SavingContracts.id,
SavingContracts.code AS contract_code,
SavingContracts.status AS contract_status,
SavingContracts.creation_date as start_date,
SavingContracts.closed_date as end_date,
SavingProducts.product_type as product_type,
Persons.identification_data as identification_data,
Tiers.client_type_code,
Tiers.id AS client_id,
ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS user_name,
ISNULL(Persons.first_name + SPACE(1) + Persons.last_name, ISNULL(Groups.name, Corporates.name)) AS client_name,
ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS loanofficer_name,
SavingContracts.user_id AS loan_officer_id,
SavingProducts.currency_id
FROM SavingContracts
INNER JOIN SavingProducts ON SavingContracts.product_id = SavingProducts.id
INNER JOIN Users ON SavingContracts.user_id = Users.id
INNER JOIN Tiers ON SavingContracts.tiers_id = Tiers.id
LEFT OUTER JOIN Persons ON Tiers.id = Persons.id
LEFT OUTER JOIN Groups ON Tiers.id = Groups.id
LEFT OUTER JOIN Corporates ON Tiers.id = Corporates.id";
sql += " WHERE 1=1 ";
if (activeContractOnly)
sql += " AND SavingContracts.status=1 ";
if (!all)
{
sql += @" AND Tiers.branch_id in (
select branch_id
from dbo.UsersBranches
WHERE user_id = @user_id
)";
}
sql += ") maTable";
const string closeWhere = @" WHERE ( contract_code LIKE @contractCode
OR client_name LIKE @clientName
OR user_name LIKE @UserName
OR identification_data LIKE @numberPassport
OR loanofficer_name LIKE @loanofficerName )) maTable";
QueryEntity q = new QueryEntity(pQuery, sql, closeWhere);
string pSqlText = q.ConstructSQLEntityByCriteresProxy(20, (pPageNumber - 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));
}
select.AddParam("@user_id", User.CurrentUser.Id);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (!reader.Empty)
{
while (reader.Read())
{
SavingSearchResult result =
new SavingSearchResult
{
Id = reader.GetInt("id"),
ClientId = reader.GetInt("client_id"),
ContractCode = reader.GetString("contract_code"),
Status = (OSavingsStatus)reader.GetSmallInt("contract_status"),
ClientTypeCode = reader.GetString("client_type_code")
};
switch (result.ClientTypeCode)
{
case "I": result.ClientType = OClientTypes.Person; break;
case "G": result.ClientType = OClientTypes.Group; break;
case "V": result.ClientType = OClientTypes.Village; break;
case "C": result.ClientType = OClientTypes.Corporate; break;
default: result.ClientType = OClientTypes.Person; break;
}
result.ClientName = reader.GetString("client_name");
result.ContractStartDate = reader.GetDateTime("start_date");
result.ContractEndDate = reader.GetNullDateTime("end_date");
result.ContractType = reader.GetString("product_type");
result.LoanOfficer = new User { Id = reader.GetInt("loan_officer_id") };
result.CurrencyId = reader.GetInt("currency_id");
list.Add(result);
}
}
}
return list;
}
}
public int GetNumberSavingContract(string pQuery, bool all, bool activeContractsOnly)
{
string sql =@"SELECT TOP 100 percent
SavingContracts.id,
SavingContracts.code AS contract_code,
SavingContracts.creation_date as start_date,
Persons.identification_data as identification_data,
Tiers.client_type_code,
ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS user_name,
ISNULL(Persons.first_name + SPACE(1) + Persons.last_name, ISNULL(Groups.name, Corporates.name)) AS client_name,
ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS loanofficer_name
FROM SavingContracts
INNER JOIN Users ON SavingContracts.user_id = Users.id
INNER JOIN Tiers ON SavingContracts.tiers_id = Tiers.id
LEFT OUTER JOIN Persons ON Tiers.id = Persons.id
LEFT OUTER JOIN Groups ON Tiers.id = Groups.id
LEFT OUTER JOIN Corporates ON Tiers.id = Corporates.id";
sql += " WHERE 1=1 ";
if (activeContractsOnly)
sql += " AND SavingContracts.[status]=1 ";
if (!all)
{
sql += @" AND Tiers.branch_id in
(
select
branch_id
from
dbo.UsersBranches
WHERE user_id = @user_id
)";
}
sql += @") maTable";
const string closeWhere = @" WHERE (contract_code LIKE @contractCode
OR client_name LIKE @clientName
OR user_name LIKE @userName
OR identification_data LIKE @numberPassport
OR loanofficer_name LIKE @loanofficerName )) maTable";
QueryEntity q = new QueryEntity(pQuery, sql, closeWhere);
string sqlText = q.ConstructSQLEntityNumberProxy();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
foreach (var item in q.DynamiqParameters())
{
select.AddParam(item.Key, string.Format("%{0}%", item.Value));
}
select.AddParam("@user_id", User.CurrentUser.Id);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader.Empty) return 0;
reader.Read();
return reader.GetInt(0);
}
}
}
public int GetNumberVillages(string pQuery)
{
string SELECT_FROM_PROJET_ = @"SELECT TOP 100 percent Villages.id,Tiers.client_type_code AS type,Villages.name,Tiers.active,Tiers.loan_cycle AS loanCycle,
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 )) maTable";
QueryEntity q = new QueryEntity(pQuery, SELECT_FROM_PROJET_, CloseWhere);
string pSqlText = q.ConstructSQLEntityNumberProxy();
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));
}
return (int)c.ExecuteScalar();
}
}
public int GetNumberPerson(string pQuery, int isActive)
{
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.ConstructSQLEntityNumberProxy();
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));
}
return (int)c.ExecuteScalar();
}
}
public int GetNumberInactivePersons(string query)
{
const string sql = @"SELECT TOP 100 PERCENT Persons.id,
Persons.first_name + ' ' + Persons.last_name AS name,
Districts.name AS district,
Tiers.city,
Tiers.active
FROM Persons
LEFT JOIN Tiers ON Persons.id = Tiers.id
LEFT JOIN Districts ON Tiers.district_id = Districts.id WHERE active = 0
where Tiers.branch_id in (
select branch_id
from dbo.UsersBranches
where user_id = @user_id
)
) maTable";
const string where =
@" WHERE active = 0 AND ( name LIKE @name OR district LIKE @district OR city LIKE @city )) maTable";
QueryEntity q = new QueryEntity(query, sql, where);
string sqlText = q.ConstructSQLEntityNumberProxy();
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);
return (int)c.ExecuteScalar();
}
}
public int GetNumberCorporate(string pQuery)
{
string SELECT_FROM_PROJET_ = @"SELECT TOP 100 percent
Corporates.id,
Corporates.name,
Corporates.siret,
Tiers.active,
Districts.name AS district,
Tiers.city AS city
FROM Corporates
INNER JOIN Tiers ON Corporates.id = Tiers.id
INNER JOIN Districts ON Districts.id = Tiers.district_id
ORDER BY Corporates.id ) maTable";
string CloseWhere = @" WHERE ( siret LIKE @siret 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.ConstructSQLEntityNumberProxy();
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));
}
return (int)c.ExecuteScalar();
}
}
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;
}