Example #1
0
        public List<Person> FindAllPersons()
        {
            List<Person> result = new List<Person>();

            Person person = null;
            int? districtId = null;
            int? secondaryDistrictId = null;
            int? activityId = null;

            string q = @"SELECT
                                 Tiers.id AS tiers_id,
                                 Tiers.client_type_code,
                                 Tiers.scoring,
                                 Tiers.loan_cycle,
                                 Tiers.active,
                                 Tiers.bad_client,
                                 Tiers.other_org_name,
                                 Tiers.other_org_amount,
                                 Tiers.other_org_debts,
                                 Tiers.district_id,
                                 Tiers.city,
                                 Tiers.address,
                                 Tiers.secondary_district_id,
                                 Tiers.secondary_city,
                                 Tiers.secondary_address,
                                 Tiers.cash_input_voucher_number,
                                 Tiers.cash_output_voucher_number,
                                 Tiers.status,
                                 Tiers.home_phone,
                                 Tiers.personal_phone,
                                 Tiers.secondary_home_phone,
                                 Tiers.secondary_personal_phone,
                                 Tiers.e_mail,
                                 Tiers.secondary_e_mail,
                                 Tiers.home_type,
                                 Tiers.zipCode,
                                 Tiers.secondary_zipCode,
                                 Tiers.secondary_homeType,
                                 Tiers.other_org_comment,
                                 Tiers.sponsor1,Tiers.sponsor2,
                                 Tiers.sponsor1_comment,Tiers.sponsor2_comment,
                                 Tiers.follow_up_comment,
                                 Tiers.branch_id,
                                 Persons.first_name,
                                 Persons.sex,
                                 Persons.identification_data,
                                 Persons.last_name,
                                 Persons.birth_date,
                                 Persons.activity_id,
                                 Persons.nb_of_people,
                                 Persons.image_path,
                                 Persons.father_name,
                                 Persons.birth_place,
                                 Persons.nationality,
                                 Persons.loan_officer_id
                            FROM Tiers
                            INNER JOIN Persons ON Tiers.id = Persons.id";

            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
            {
                using (OpenCbsReader r = c.ExecuteReader())
                {
                    if (!r.Empty)
                    {

                        while (r.Read())
                        {
                            person = GetPersonFromReader(r);
                            secondaryDistrictId = r.GetNullInt("secondary_district_id");
                            activityId = r.GetNullInt("activity_id");
                            districtId = r.GetNullInt("district_id");
                            if (person != null)
                            {
                                UserManager userManager = new UserManager(User.CurrentUser);
                                if (person.FavouriteLoanOfficerId.HasValue)
                                    person.FavouriteLoanOfficer = userManager.SelectUser((int)person.FavouriteLoanOfficerId,
                                                                                         true);

                                if (activityId.HasValue)
                                    person.Activity = _doam.SelectEconomicActivity(activityId.Value);

                                if (districtId.HasValue)
                                    person.District = _locations.SelectDistrictById(districtId.Value);

                                if (secondaryDistrictId.HasValue)
                                    person.SecondaryDistrict = _locations.SelectDistrictById(secondaryDistrictId.Value);

                                if (person != null)
                                {
                                    if (_projectManager != null)
                                        person.AddProjects(_projectManager.SelectProjectsByClientId(person.Id));
                                    if (_savingManager != null)
                                        person.AddSavings(_savingManager.SelectSavings(person.Id));
                                }

                                OnClientSelected(person);
                            }
                            result.Add(person);
                        }
                    }
                }
            }
            return result;
        }
Example #2
0
        public Corporate SelectBodyCorporateById(int id)
        {
            int? activityId = null;
            int? districtId = null;
            int? secondaryDistrictId = null;

            Corporate body = null;
            const string q = @" SELECT * FROM Tiers
            INNER JOIN Corporates ON Tiers.id = Corporates.id
            WHERE [deleted] = 0 AND [email protected]";

            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
            {
                c.AddParam("@id", id);

                using (OpenCbsReader r = c.ExecuteReader())
                {
                    if (r != null && !r.Empty)
                    {
                        r.Read();
                        body = new Corporate
                                   {
                                       Id = r.GetInt("id"),
                                       Name = r.GetString("name"),
                                       Status = (OClientStatus)r.GetSmallInt("status"),
                                       IsDeleted = r.GetBool("deleted"),
                                       CashReceiptIn = r.GetNullInt("cash_input_voucher_number"),
                                       CashReceiptOut =
                                           r.GetNullInt("cash_output_voucher_number"),
                                       Type = r.GetChar("client_type_code") == 'I'
                                                  ? OClientTypes.Person
                                                  : r.GetChar("client_type_code") == 'G'
                                                        ? OClientTypes.Group
                                                        : OClientTypes.Corporate,
                                       Scoring = r.GetNullDouble("scoring"),
                                       LoanCycle = r.GetInt("loan_cycle"),
                                       Active = r.GetBool("active"),
                                       BadClient = r.GetBool("bad_client"),
                                       OtherOrgName = r.GetString("other_org_name"),
                                       OtherOrgAmount = r.GetMoney("other_org_amount"),
                                       OtherOrgDebts = r.GetMoney("other_org_debts"),
                                       City = r.GetString("city"),
                                       Address = r.GetString("address"),
                                       SecondaryCity = r.GetString("secondary_city"),
                                       SecondaryAddress = r.GetString("secondary_address"),
                                       HomePhone = r.GetString("home_phone"),
                                       PersonalPhone = r.GetString("personal_phone"),
                                       SecondaryHomePhone = r.GetString("secondary_home_phone"),
                                       SecondaryPersonalPhone = r.GetString("secondary_personal_phone"),
                                       VolunteerCount = r.GetNullInt("volunteer_count"),
                                       EmployeeCount = r.GetNullInt("employee_count"),
                                       Sigle = r.GetString("sigle"),
                                       Siret = r.GetString("siret"),
                                       SmallName = r.GetString("small_name"),
                                       CreationDate = r.GetDateTime("creation_date"),
                                       AgrementDate = r.GetNullDateTime("agrement_date"),
                                       FollowUpComment = r.GetString("follow_up_comment"),
                                       HomeType = r.GetString("home_type"),
                                       SecondaryHomeType = r.GetString("secondary_hometype"),
                                       ZipCode = r.GetString("zipCode"),
                                       SecondaryZipCode = r.GetString("secondary_zipCode"),
                                       Sponsor1 = r.GetString("sponsor1"),
                                       Sponsor2 = r.GetString("sponsor2"),
                                       Sponsor1Comment = r.GetString("sponsor1_Comment"),
                                       Sponsor2Comment = r.GetString("sponsor2_comment"),
                                       FiscalStatus = r.GetString("fiscal_status"),
                                       Registre = r.GetString("registre"),
                                       LegalForm = r.GetString("legalForm"),
                                       InsertionType = r.GetString("insertionType"),
                                       Email = r.GetString("e_mail"),
                                       FavouriteLoanOfficerId = r.GetNullInt("loan_officer_id"),
                                       Branch = new Branch { Id = r.GetInt("branch_id") },
                                       RegistrationDate = r.GetNullDateTime("date_create") ?? TimeProvider.Today
                                   };

                        districtId = r.GetNullInt("district_id");
                        secondaryDistrictId = r.GetNullInt("secondary_district_id");
                        activityId = r.GetNullInt("activity_id");
                    }
                }
            }
            if (body != null)
            {
                if (body.FavouriteLoanOfficerId != null)
                {
                    UserManager userManager = new UserManager(User.CurrentUser);
                    body.FavouriteLoanOfficer = userManager.SelectUser((int)body.FavouriteLoanOfficerId, true);
                }
            }

            if (districtId.HasValue) body.District = _locations.SelectDistrictById(districtId.Value);

            if (secondaryDistrictId.HasValue) body.SecondaryDistrict = _locations.SelectDistrictById(secondaryDistrictId.Value);

            if (activityId.HasValue) body.Activity = _doam.SelectEconomicActivity(activityId.Value);

            if (body != null)
            {
                if (_projectManager != null)
                    body.AddProjects(_projectManager.SelectProjectsByClientId(body.Id));
                if (_savingManager != null)
                    body.AddSavings(_savingManager.SelectSavings(body.Id));
            }

            // Selecting Corporate contacts - Not very logical, but asked by Julien M.
            const string sqlContactText = @"
                                 SELECT Persons.id,
                                        Persons.first_name,
                                        Persons.last_name,
                                        Tiers.home_phone,
                                        Tiers.personal_phone,
                                        CorporatePersonBelonging.position
                                 FROM Tiers
                                 INNER JOIN CorporatePersonBelonging ON Tiers.id = CorporatePersonBelonging.person_id
                                 INNER JOIN Persons ON Tiers.id = Persons.id
                                 WHERE CorporatePersonBelonging.corporate_id = @id";

            Person person = null;
            Contact contact = null;
            List<Contact> contacts = new List<Contact>();

            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand c = new OpenCbsCommand(sqlContactText, conn))
            {
                c.AddParam("@id", id);

                using (OpenCbsReader r = c.ExecuteReader())
                {
                    if (r != null && !r.Empty)
                    {
                        while (r.Read())
                        {
                            person = new Person()
                                         {
                                             Id = r.GetInt("id"),
                                             FirstName = r.GetString("first_name"),
                                             LastName = r.GetString("last_name"),
                                             PersonalPhone = r.GetString("personal_phone"),
                                             HomePhone = r.GetString("home_phone")
                                         };

                            contact = new Contact()
                                          {
                                              Tiers = person,
                                              Position = r.GetString("position")
                                          };
                            contacts.Add(contact);
                        }
                    }
                }
            }

            body.Contacts = contacts;

            OnClientSelected(body);

            return body;
        }
Example #3
0
        private Group SelectGroup(int pGroupId)
        {
            Group group = null;
            int? districtId = null;
            int? secondaryDistrictId = null;
            int? activityId = null;

            const string q = @"SELECT
                Tiers.id AS tiers_id,
                Tiers.client_type_code,
                Tiers.scoring,
                Tiers.loan_cycle,
                Tiers.active,
                Tiers.bad_client,
                Tiers.other_org_name,
                Tiers.other_org_amount,
                Tiers.other_org_debts,
                Tiers.district_id,
                Tiers.city,
                Tiers.address,
                Tiers.secondary_district_id,
                Tiers.secondary_city,
                Tiers.secondary_address,
                Tiers.status,
                Tiers.home_phone,
                Tiers.personal_phone,
                Tiers.secondary_home_phone,
                Tiers.secondary_personal_phone,
                Tiers.cash_input_voucher_number,
                Tiers.cash_output_voucher_number,
                Tiers.e_mail,
                Tiers.secondary_e_mail,
                Tiers.home_type,
                Tiers.zipCode,
                Tiers.secondary_zipCode,
                Tiers.secondary_homeType,
                Tiers.other_org_comment,
                Tiers.sponsor1,
                Tiers.sponsor2,
                Tiers.sponsor1_comment,
                Tiers.sponsor2_comment,
                Tiers.follow_up_comment,
                Tiers.branch_id,
                Groups.name,
                Groups.establishment_date,
                Groups.comments,
                Groups.meeting_day,
                eah.economic_activity_id,
                Groups.loan_officer_id
                FROM Tiers
                INNER JOIN Groups ON Tiers.id = Groups.id
                LEFT JOIN EconomicActivityLoanHistory eah ON eah.group_id = Groups.id
                WHERE Groups.id = @id";

            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
            {

                c.AddParam("@id", pGroupId);

                using (OpenCbsReader r = c.ExecuteReader())
                {
                    if (r != null && !r.Empty)
                    {
                        r.Read();
                        group = new Group
                                    {
                                        Id = r.GetInt("tiers_id"),
                                        HomeType = r.GetString("home_type"),
                                        SecondaryHomeType = r.GetString("secondary_hometype"),
                                        ZipCode = r.GetString("zipCode"),
                                        SecondaryZipCode = r.GetString("secondary_zipCode"),
                                        HomePhone = r.GetString("home_phone"),
                                        PersonalPhone = r.GetString("personal_phone"),
                                        SecondaryHomePhone = r.GetString("secondary_home_phone"),
                                        SecondaryPersonalPhone = r.GetString("secondary_personal_phone"),
                                        Status = ((OClientStatus)r.GetSmallInt("status")),
                                        CashReceiptIn = r.GetNullInt("cash_input_voucher_number"),
                                        CashReceiptOut = r.GetNullInt("cash_output_voucher_number"),
                                        Type = r.GetChar("client_type_code") == 'I'
                                                   ? OClientTypes.Person
                                                   : r.GetChar("client_type_code") ==
                                                     'G'
                                                         ? OClientTypes.Group
                                                         : OClientTypes.Corporate,
                                        Scoring = r.GetNullDouble("scoring"),
                                        LoanCycle = r.GetInt("loan_cycle"),
                                        Active = r.GetBool("active"),
                                        BadClient = r.GetBool("bad_client"),
                                        OtherOrgName = r.GetString("other_org_name"),
                                        OtherOrgAmount = r.GetMoney("other_org_amount"),
                                        OtherOrgDebts = r.GetMoney("other_org_debts"),
                                        OtherOrgComment = r.GetString("other_org_comment"),
                                        Comments = r.GetString("comments"),
                                        Email = r.GetString("e_mail"),
                                        SecondaryEmail = r.GetString("secondary_e_mail"),
                                        MeetingDay = (DayOfWeek?)r.GetNullInt("meeting_day"),
                                        Branch = new Branch { Id = r.GetInt("branch_id") },
                                        FavouriteLoanOfficerId = r.GetNullInt("loan_officer_id")
                                    };

                        districtId = r.GetNullInt("district_id");

                        group.City = r.GetString("city");
                        group.Address = r.GetString("address");
                        secondaryDistrictId = r.GetNullInt("secondary_district_id");
                        group.SecondaryCity = r.GetString("secondary_city");
                        group.SecondaryAddress = r.GetString("secondary_address");
                        group.Name = r.GetString("name");
                        group.EstablishmentDate = r.GetNullDateTime("establishment_date");
                        group.FollowUpComment = r.GetString("follow_up_comment");
                        group.Sponsor1 = r.GetString("sponsor1");
                        group.Sponsor2 = r.GetString("sponsor2");
                        group.Sponsor1Comment = r.GetString("sponsor1_comment");
                        group.Sponsor2Comment = r.GetString("sponsor2_comment");

                        activityId = r.GetNullInt("economic_activity_id");

                        UserManager userManager = new UserManager(User.CurrentUser);
                        if (group.FavouriteLoanOfficerId.HasValue)
                            group.FavouriteLoanOfficer = userManager.SelectUser((int)group.FavouriteLoanOfficerId,
                                                                                true);
                    }
                }
            }

            if (districtId.HasValue)
                group.District = _locations.SelectDistrictById(districtId.Value);

            if (secondaryDistrictId.HasValue)
                group.SecondaryDistrict = _locations.SelectDistrictById(secondaryDistrictId.Value);

            if (group != null)
            {
                if (_projectManager != null)
                    group.AddProjects(_projectManager.SelectProjectsByClientId(group.Id));
                List<Member> idsList = SelectPersonIdsByGroupId(group.Id, true);

                if (_savingManager != null)
                    group.AddSavings(_savingManager.SelectSavings(group.Id));

                foreach (Member member in idsList)
                {
                    member.Tiers = SelectPersonById(member.Tiers.Id);
                    if (IsLeader(member.Tiers.Id, group.Id))
                        group.Leader = member;

                    else
                        group.AddMember(member);
                }

                if (activityId.HasValue)
                    group.Activity = _doam.SelectEconomicActivity((int)activityId);
            }

            OnClientSelected(group);

            return group;
        }
Example #4
0
        public List<Person> FindAllPersons()
        {
            List<Person> result = new List<Person>();

            Person person = null;
            int? districtId = null;
            int? secondaryDistrictId = null;
            int? activityId = null;
            int? personalBankId = null;
            int? businessBankId = null;

            string q = @"SELECT
                                 Tiers.id AS tiers_id,
                                 Tiers.client_type_code,
                                 Tiers.scoring,
                                 Tiers.loan_cycle,
                                 Tiers.active,
                                 Tiers.bad_client,
                                 Tiers.other_org_name,
                                 Tiers.other_org_amount,
                                 Tiers.other_org_debts,
                                 Tiers.district_id,
                                 Tiers.city,
                                 Tiers.address,
                                 Tiers.secondary_district_id,
                                 Tiers.secondary_city,
                                 Tiers.secondary_address,
                                 Tiers.cash_input_voucher_number,
                                 Tiers.cash_output_voucher_number,
                                 Tiers.status,
                                 Tiers.home_phone,
                                 Tiers.personal_phone,
                                 Tiers.secondary_home_phone,
                                 Tiers.secondary_personal_phone,
                                 Tiers.e_mail,
                                 Tiers.secondary_e_mail,
                                 Tiers.home_type,
                                 Tiers.zipCode,
                                 Tiers.secondary_zipCode,
                                 Tiers.secondary_homeType,
                                 Tiers.other_org_comment,
                                 Tiers.sponsor1,Tiers.sponsor2,
                                 Tiers.sponsor1_comment,Tiers.sponsor2_comment,
                                 Tiers.follow_up_comment,
                                 Tiers.branch_id,
                                 Persons.first_name,
                                 Persons.sex,
                                 Persons.identification_data,
                                 Persons.last_name,
                                 Persons.birth_date,
                                 Persons.household_head,
                                 Persons.nb_of_dependents,
                                 Persons.nb_of_children,
                                 Persons.children_basic_education,
                                 Persons.livestock_number,
                                 Persons.livestock_type,
                                 Persons.landplot_size,
                                 Persons.home_time_living_in,
                                 Persons.home_size,
                                 Persons.capital_other_equipments,
                                 Persons.activity_id,
                                 Persons.experience,
                                 Persons.nb_of_people,
                                 Persons.mother_name,
                                 Persons.image_path,
                                 Persons.father_name,
                                 Persons.birth_place,
                                 Persons.nationality,
                                 Persons.unemployment_months,
                                 Persons.personalBank_id,
                                 Persons.businessBank_id,
                                 Persons.study_level,
                                 Persons.SS,
                                 Persons.CAF,
                                 Persons.housing_situation,
                                 Persons.handicapped,
                                 Persons.professional_situation,
                                 Persons.professional_experience,
                                 Persons.first_contact,
                                 Persons.first_appointment,
                                 Persons.family_situation,
                                 Persons.povertylevel_childreneducation,
                                 Persons.povertylevel_economiceducation,
                                 Persons.povertylevel_socialparticipation,
                                 Persons.povertylevel_healthsituation,
                                 Persons.loan_officer_id
                            FROM Tiers
                            INNER JOIN Persons ON Tiers.id = Persons.id";

            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
            {
                using (OpenCbsReader r = c.ExecuteReader())
                {
                    if (!r.Empty)
                    {

                        while (r.Read())
                        {
                            person = GetPersonFromReader(r);
                            personalBankId = r.GetNullInt("personalBank_id");
                            businessBankId = r.GetNullInt("businessBank_id");
                            secondaryDistrictId = r.GetNullInt("secondary_district_id");
                            activityId = r.GetNullInt("activity_id");
                            districtId = r.GetNullInt("district_id");
                            if (person != null)
                            {
                                UserManager userManager = new UserManager(User.CurrentUser);
                                if (person.FavouriteLoanOfficerId.HasValue)
                                    person.FavouriteLoanOfficer = userManager.SelectUser((int)person.FavouriteLoanOfficerId,
                                                                                         true);

                                if (activityId.HasValue)
                                    person.Activity = _doam.SelectEconomicActivity(activityId.Value);

                                if (districtId.HasValue)
                                    person.District = _locations.SelectDistrictById(districtId.Value);

                                if (secondaryDistrictId.HasValue)
                                    person.SecondaryDistrict = _locations.SelectDistrictById(secondaryDistrictId.Value);

                                if (person != null)
                                {
                                    if (_projectManager != null)
                                        person.AddProjects(_projectManager.SelectProjectsByClientId(person.Id));
                                    if (_savingManager != null)
                                        person.AddSavings(_savingManager.SelectSavings(person.Id));
                                }

                                OnClientSelected(person);
                            }
                           result.Add(person);
                        }
                     }
                }
            }
               return result;
        }