Ejemplo n.º 1
0
        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);
        }
Ejemplo n.º 2
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;
        }
Ejemplo n.º 3
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);
                }
            }
        }
Ejemplo n.º 4
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;
            }
        }
Ejemplo n.º 5
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);
                }
            }
        }
Ejemplo n.º 6
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();
            }
        }
Ejemplo n.º 7
0
        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();
            }
        }
Ejemplo n.º 8
0
        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();
            }
        }
Ejemplo n.º 9
0
        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();
            }
        }
Ejemplo n.º 10
0
        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;
        }
Ejemplo n.º 11
0
        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;
        }
Ejemplo n.º 12
0
        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;
        }
Ejemplo n.º 13
0
        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;
        }