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