public void TestConstructSQL()
{
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.ConstructSQLEntityNumberProxy();
Assert.IsTrue(q.DynamiqParameters().Count > 0);
}
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 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();
}
}