public List <FiscalYear> SelectFiscalYears() { const string sqlText = @"SELECT id, name, open_date, close_date FROM dbo.FiscalYear"; List <FiscalYear> fiscalYears = new List <FiscalYear>(); using (SqlConnection conn = GetConnection()) { using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn)) { using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) { return(null); } while (reader.Read()) { fiscalYears.Add(new FiscalYear { Id = reader.GetInt("id"), Name = reader.GetString("name"), CloseDate = reader.GetNullDateTime("close_date"), OpenDate = reader.GetNullDateTime("open_date") }); } } } } return(fiscalYears); }
public List <QueuedEmail> SelectAll() { List <QueuedEmail> qeuedEmails = new List <QueuedEmail>(); const string q = @"SELECT * FROM dbo.QueuedEmails where Deleted <> 1"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) using (OpenCbsReader r = c.ExecuteReader()) { if (r.Empty) { return(qeuedEmails); } while (r.Read()) { var queuedEmail = new QueuedEmail { Id = r.GetInt("Id"), Priority = r.GetInt("Priority"), From = r.GetString("From"), FromName = r.GetString("FromName"), To = r.GetString("To"), ToName = r.GetString("ToName"), ReplyTo = r.GetString("ReplyTo"), ReplyToName = r.GetString("ReplyToName"), CC = r.GetString("CC"), Bcc = r.GetString("Bcc"), Body = r.GetString("Body"), CreatedOnUtc = r.GetDateTime("CreatedOnUtc"), SentOnUtc = r.GetNullDateTime("SentOnUtc"), EmailAccountId = r.GetInt("EmailAccountId"), SentTries = r.GetInt("SentTries"), Subject = r.GetString("Subject"), Deleted = r.GetBool("Deleted"), }; if (queuedEmail.EmailAccountId > 0) { EmailAccountManager _emailAccountManager = new EmailAccountManager(user); queuedEmail.EmailAccount = _emailAccountManager.SelectById(queuedEmail.EmailAccountId); } qeuedEmails.Add(queuedEmail); } } return(qeuedEmails); }
public List <QueuedSMS> SelectAll() { List <QueuedSMS> qeuedSMSs = new List <QueuedSMS>(); const string q = @"SELECT * FROM dbo.QueuedSMS where Deleted <> 1"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) using (OpenCbsReader r = c.ExecuteReader()) { if (r.Empty) { return(qeuedSMSs); } while (r.Read()) { var queuedSMS = new QueuedSMS { Id = r.GetInt("Id"), From = r.GetString("From"), Recipient = r.GetString("Recipient"), RecipientId = r.GetNullInt("RecipientId"), ContractId = r.GetNullInt("ContractId"), Charged = r.GetNullBool("Charged"), Message = r.GetString("Message"), CreatedOnUtc = r.GetDateTime("CreatedOnUtc"), SentOnUtc = r.GetNullDateTime("SentOnUtc"), SentTries = r.GetInt("SentTries"), Response = r.GetString("Response"), Deleted = r.GetBool("Deleted"), }; qeuedSMSs.Add(queuedSMS); } } return(qeuedSMSs); }
private SavingBookContract GetSavingFromReader(OpenCbsReader pReader) { var savingContract = new SavingBookContract( ApplicationSettings.GetInstance(_user.Md5), _user); savingContract.Product = new SavingsBookProduct { Id = pReader.GetInt("product_id") }; savingContract.Id = pReader.GetInt("id"); savingContract.Code = pReader.GetString("code"); savingContract.Status = (OSavingsStatus)pReader.GetSmallInt("status"); savingContract.CreationDate = pReader.GetDateTime("creation_date"); savingContract.ClosedDate = pReader.GetNullDateTime("closed_date"); savingContract.InterestRate = pReader.GetDouble("interest_rate"); savingContract.SavingsOfficer = new User { Id = pReader.GetInt("savings_officer_id") , FirstName = pReader.GetString("so_first_name") , LastName = pReader.GetString("so_last_name") }; savingContract.InitialAmount = pReader.GetMoney("initial_amount"); savingContract.EntryFees = pReader.GetMoney("entry_fees"); savingContract.NsgID = pReader.GetNullInt("nsg_id"); return savingContract; }
private void GetSavingBookFromReader(SavingBookContract saving, OpenCbsReader reader) { saving.FlatWithdrawFees = reader.GetNullDecimal("flat_withdraw_fees"); saving.RateWithdrawFees = reader.GetNullDouble("rate_withdraw_fees"); saving.FlatTransferFees = reader.GetNullDecimal("flat_transfer_fees"); saving.RateTransferFees = reader.GetNullDouble("rate_transfer_fees"); saving.DepositFees = reader.GetNullDecimal("flat_deposit_fees"); saving.ChequeDepositFees = reader.GetNullDecimal("cheque_deposit_fees"); saving.CloseFees = reader.GetNullDecimal("flat_close_fees"); saving.ManagementFees = reader.GetNullDecimal("flat_management_fees"); saving.OverdraftFees = reader.GetNullDecimal("flat_overdraft_fees"); saving.InOverdraft = reader.GetBool("in_overdraft"); saving.AgioFees = reader.GetNullDouble("rate_agio_fees"); saving.ReopenFees = reader.GetNullDecimal("flat_reopen_fees"); saving.FlatInterBranchTransferFee = reader.GetNullDecimal("flat_ibt_fee"); saving.RateInterBranchTransferFee =reader.GetNullDouble("rate_ibt_fee"); saving.UseTermDeposit = reader.GetBool("use_term_deposit"); saving.NumberOfPeriods = reader.GetInt("term_deposit_period"); saving.TermDepositPeriodMin = reader.GetNullInt("term_deposit_period_min"); saving.TermDepositPeriodMax = reader.GetNullInt("term_deposit_period_max"); saving.TransferAccount = new SavingBookContract(ApplicationSettings.GetInstance(_user.Md5), _user) { Code = reader.GetString("transfer_account") }; saving.NextMaturity = reader.GetNullDateTime("next_maturity"); if (saving.UseTermDeposit) saving.Rollover = (OSavingsRollover) reader.GetInt("rollover"); // This is the bozo's way of fetching branch information. // Ideally it should be available through the saving's client object. // But that object is initialized in many places so that modifying // it does not look feasible. // Instead, we constract a new Branch object *partially* and then // through the OnSavingSelected below call back into the Service layer // to finalize the construction. saving.Branch = new Branch {Id = reader.GetInt("branch_id")}; }
private Loan _GetLoan(OpenCbsReader r) { return new Loan(_user, ApplicationSettings.GetInstance(_user.Md5), NonWorkingDateSingleton.GetInstance(_user.Md5), ProvisionTable.GetInstance(_user), ChartOfAccounts.GetInstance(_user)) { Id = r.GetInt("credit_id"), ClientType = r.GetChar("client_type_code") == 'I' ? OClientTypes.Person : r.GetChar("client_type_code") == 'G' ? OClientTypes.Group : OClientTypes.Corporate, ContractStatus = (OContractStatus) r.GetSmallInt("status"), CreditCommiteeDate = r.GetNullDateTime("credit_commitee_date"), CreditCommiteeComment = r.GetString("credit_commitee_comment"), CreditCommitteeCode = r.GetString("credit_commitee_code"), Amount = r.GetMoney("amount"), InterestRate = r.GetDecimal("interest_rate"), NbOfInstallments = r.GetInt("nb_of_installment"), NonRepaymentPenalties = new NonRepaymentPenalties { InitialAmount = r.GetDouble("non_repayment_penalties_based_on_initial_amount"), OLB = r.GetDouble("non_repayment_penalties_based_on_olb"), OverDueInterest = r.GetDouble("non_repayment_penalties_based_on_overdue_interest"), OverDuePrincipal = r.GetDouble("non_repayment_penalties_based_on_overdue_principal") }, AnticipatedTotalRepaymentPenalties = r.GetDouble("anticipated_total_repayment_penalties"), AnticipatedPartialRepaymentPenalties = r.GetDouble("anticipated_partial_repayment_penalties"), AnticipatedPartialRepaymentPenaltiesBase = (OAnticipatedRepaymentPenaltiesBases) r.GetSmallInt("anticipated_partial_repayment_base"), AnticipatedTotalRepaymentPenaltiesBase =(OAnticipatedRepaymentPenaltiesBases) r.GetSmallInt("anticipated_total_repayment_base"), Disbursed = r.GetBool("disbursed"), GracePeriod = r.GetNullInt("grace_period"), GracePeriodOfLateFees = r.GetNullInt("grace_period_of_latefees"), WrittenOff = r.GetBool("written_off"), Rescheduled = r.GetBool("rescheduled"), Code = r.GetString("contract_code"), BranchCode = r.GetString("branch_code"), CreationDate = r.GetDateTime("creation_date"), StartDate = r.GetDateTime("start_date"), AlignDisbursementDate = r.GetDateTime("align_disbursed_date"), CloseDate = r.GetDateTime("close_date"), Closed = r.GetBool("closed"), BadLoan = r.GetBool("bad_loan"), Synchronize = r.GetBool("synchronize"), ScheduleChangedManually = r.GetBool("schedule_changed"), AmountUnderLoc = r.GetMoney("amount_under_loc"), CompulsorySavingsPercentage = r.GetNullInt("loan_percentage"), LoanPurpose = r.GetString("loan_purpose"), Comments = r.GetString("comments"), AmountMin = r.GetMoney("amount_min"), AmountMax = r.GetMoney("amount_max"), InterestRateMin = r.GetNullDecimal("ir_min"), InterestRateMax = r.GetNullDecimal("ir_max"), NmbOfInstallmentsMin = r.GetNullInt("nmb_of_inst_min"), NmbOfInstallmentsMax = r.GetNullInt("nmb_of_inst_max"), LoanCycle = r.GetNullInt("loan_cycle"), Insurance = r.GetDecimal("insurance"), NsgID = r.GetNullInt("nsg_id"), EconomicActivityId = r.GetInt("activity_id"), FirstInstallmentDate = r.GetDateTime("preferred_first_installment_date"), }; }
private static void SetSavingsEvent(OpenCbsReader r, SavingEvent e, ISavingProduct pProduct) { e.Id = r.GetInt("id"); e.ContracId = r.GetInt("contract_id"); e.Code = r.GetString("code"); e.Amount = r.GetMoney("amount"); e.Description = r.GetString("description"); e.Deleted = r.GetBool("deleted"); e.Date = r.GetDateTime("creation_date"); e.Cancelable = r.GetBool("cancelable"); e.IsFired = r.GetBool("is_fired"); e.CancelDate = r.GetNullDateTime("cancel_date"); if(pProduct != null) e.ProductType = pProduct.GetType(); if (r.GetNullSmallInt("savings_method").HasValue) e.SavingsMethod = (OSavingsMethods)r.GetNullSmallInt("savings_method").Value; e.IsPending = r.GetBool("pending"); e.PendingEventId = r.GetNullInt("pending_event_id"); e.TellerId = r.GetNullInt("teller_id"); e.LoanEventId = r.GetNullInt("loan_event_id"); if (pProduct != null) { e.ProductType = pProduct.GetType(); } if (e is SavingTransferEvent) { ((SavingTransferEvent)e).RelatedContractCode = r.GetString("related_contract_code"); } if (e is ISavingsFees) { ((ISavingsFees) e).Fee = r.GetMoney("fees"); } e.User = new User { Id = r.GetInt("user_id"), UserName = r.GetString("user_name"), Password = r.GetString("user_pass"), LastName = r.GetString("last_name"), FirstName = r.GetString("first_name") }; e.User.SetRole(r.GetString("role_code")); e.ClientType = OClientTypes.All; switch (r.GetString("client_type_code")) { case "I": e.ClientType = OClientTypes.Person; break; case "C": e.ClientType = OClientTypes.Corporate; break; case "G": e.ClientType = OClientTypes.Group; break; case "V": e.ClientType = OClientTypes.Village; break; } e.Branch = new Branch { Id = r.GetInt("branch_id") }; e.Currency = new Currency { Id = r.GetInt("currency_id"), Code = r.GetString("currency_code"), IsPivot = r.GetBool("is_pivot"), IsSwapped = r.GetBool("is_swapped") }; e.SavingProduct = new SavingsBookProduct { Id = r.GetInt("product_id") }; }
private static void GetEvent(OpenCbsReader r, Event pEvent) { //abstract class Event attributes string eventType = r.GetString("event_type"); pEvent.Code = eventType; pEvent.ContracId = r.GetInt("contract_id"); pEvent.Date = r.GetDateTime("event_date"); pEvent.EntryDate = r.GetDateTime("entry_date"); pEvent.Deleted = r.GetBool("event_deleted"); pEvent.IsFired = true; pEvent.Cancelable = true; pEvent.ExportedDate = DateTime.MinValue; pEvent.Comment = r.GetString("comment"); pEvent.TellerId = r.GetNullInt("teller_id"); pEvent.ParentId = r.GetNullInt("parent_id"); pEvent.CancelDate = r.GetNullDateTime("cancel_date"); pEvent.ClientType = OClientTypes.All; switch (r.GetString("client_type_code")) { case "I": pEvent.ClientType = OClientTypes.Person; break; case "C": pEvent.ClientType = OClientTypes.Corporate; break; case "G": pEvent.ClientType = OClientTypes.Group; break; case "V": pEvent.ClientType = OClientTypes.Village; break; } //User associated to the event pEvent.User = new User { Id = r.GetInt("user_id"), UserName = r.GetString("user_username"), Password = r.GetString("user_password"), LastName = r.GetString("user_lastname"), FirstName = r.GetString("user_firstname") }; pEvent.Currency = new Currency { Id = r.GetInt("currency_id"), Code = r.GetString("currency_code"), IsPivot = r.GetBool("is_pivot"), IsSwapped = r.GetBool("is_swapped") }; pEvent.Branch = new Branch { Id = r.GetInt("branch_id") }; pEvent.LoanProduct = new LoanProduct { Id = r.GetInt("product_id") }; pEvent.User.SetRole(r.GetString("user_role")); if ( eventType.Equals("ULIE") || eventType.Equals("ULOE") ) return; if (r.HasColumn("contract_code")) pEvent.Description = r.GetString("contract_code"); }
private static void SetSavingsEvent(OpenCbsReader r, SavingEvent e, ISavingProduct pProduct) { e.Id = r.GetInt("id"); e.ContracId = r.GetInt("contract_id"); e.Code = r.GetString("code"); e.Amount = r.GetMoney("amount"); e.Description = r.GetString("description"); e.Deleted = r.GetBool("deleted"); e.Date = r.GetDateTime("creation_date"); e.Cancelable = r.GetBool("cancelable"); e.IsFired = r.GetBool("is_fired"); e.CancelDate = r.GetNullDateTime("cancel_date"); if (pProduct != null) { e.ProductType = pProduct.GetType(); } if (r.GetNullSmallInt("savings_method").HasValue) { e.SavingsMethod = (OSavingsMethods)r.GetNullSmallInt("savings_method").Value; } e.IsPending = r.GetBool("pending"); e.PendingEventId = r.GetNullInt("pending_event_id"); e.TellerId = r.GetNullInt("teller_id"); e.LoanEventId = r.GetNullInt("loan_event_id"); if (pProduct != null) { e.ProductType = pProduct.GetType(); } if (e is SavingTransferEvent) { ((SavingTransferEvent)e).RelatedContractCode = r.GetString("related_contract_code"); } if (e is ISavingsFees) { ((ISavingsFees)e).Fee = r.GetMoney("fees"); } e.User = new User { Id = r.GetInt("user_id"), UserName = r.GetString("user_name"), Password = r.GetString("user_pass"), LastName = r.GetString("last_name"), FirstName = r.GetString("first_name") }; e.User.SetRole(r.GetString("role_code")); e.ClientType = OClientTypes.All; switch (r.GetString("client_type_code")) { case "I": e.ClientType = OClientTypes.Person; break; case "C": e.ClientType = OClientTypes.Corporate; break; case "G": e.ClientType = OClientTypes.Group; break; case "V": e.ClientType = OClientTypes.Village; break; } e.Branch = new Branch { Id = r.GetInt("branch_id") }; e.Currency = new Currency { Id = r.GetInt("currency_id"), Code = r.GetString("currency_code"), IsPivot = r.GetBool("is_pivot"), IsSwapped = r.GetBool("is_swapped") }; e.SavingProduct = new SavingsBookProduct { Id = r.GetInt("product_id") }; }
private Village GetVillageFromReader(OpenCbsReader r) { Village village; village = new Village { Id = r.GetInt("tiers_id"), ZipCode = r.GetString("zipCode"), Status = ((OClientStatus)r.GetSmallInt("status")), 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") }; village.MeetingDay = (DayOfWeek?)r.GetNullInt("meeting_day"); village.City = r.GetString("city"); village.Address = r.GetString("address"); village.Name = r.GetString("name"); village.EstablishmentDate = r.GetNullDateTime("establishment_date"); village.Branch = new Branch { Id = r.GetInt("branch_id") }; return village; }
private Person GetPersonFromReader(OpenCbsReader r) { Person person; person = new Person { Id = r.GetInt("tiers_id"), HomePhone = r.GetString("home_phone"), Email = r.GetString("e_mail"), Status = (OClientStatus)r.GetSmallInt("status"), SecondaryEmail = r.GetString("secondary_e_mail"), HomeType = r.GetString("home_type"), SecondaryHomeType = r.GetString("secondary_hometype"), ZipCode = r.GetString("zipCode"), SecondaryZipCode = r.GetString("secondary_zipCode"), OtherOrgComment = r.GetString("other_org_comment"), PersonalPhone = r.GetString("personal_phone"), SecondaryHomePhone = r.GetString("secondary_home_phone"), SecondaryPersonalPhone = r.GetString("secondary_personal_phone"), 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"), FirstName = r.GetString("first_name"), Sex = r.GetChar("sex"), IdentificationData = r.GetString("identification_data"), DateOfBirth = r.GetNullDateTime("birth_date"), LastName = r.GetString("last_name"), FatherName = r.GetString("father_name"), Image = r.GetString("image_path"), BirthPlace = r.GetString("birth_place"), Nationality = r.GetString("nationality"), FollowUpComment = r.GetString("follow_up_comment"), Sponsor1 = r.GetString("sponsor1"), Sponsor2 = r.GetString("sponsor2"), Sponsor1Comment = r.GetString("sponsor1_comment"), Sponsor2Comment = r.GetString("sponsor2_comment"), FavouriteLoanOfficerId = r.GetNullInt("loan_officer_id"), Branch = new Branch { Id = r.GetInt("branch_id") } }; return person; }
private VillageMember GetMemberFromReader(OpenCbsReader r) { return new VillageMember { Tiers = { Id = r.GetInt("person_id") }, JoinedDate = r.GetDateTime("joined_date"), LeftDate = r.GetNullDateTime("left_date"), IsLeader = r.GetBool("is_leader"), CurrentlyIn = r.GetBool("currently_in"), }; }
private Person GetPersonFromReader(OpenCbsReader r) { Person person; person = new Person { Id = r.GetInt("tiers_id"), HomePhone = r.GetString("home_phone"), FirstContact = r.GetNullDateTime("first_contact"), FirstAppointment = r.GetNullDateTime("first_appointment"), ProfessionalSituation = r.GetString("professional_situation"), ProfessionalExperience = r.GetString("professional_experience"), FamilySituation = r.GetString("family_situation"), Handicapped = r.GetBool("handicapped"), Email = r.GetString("e_mail"), Status = (OClientStatus) r.GetSmallInt("status"), SecondaryEmail = r.GetString("secondary_e_mail"), HomeType = r.GetString("home_type"), SecondaryHomeType = r.GetString("secondary_hometype"), ZipCode = r.GetString("zipCode"), SecondaryZipCode = r.GetString("secondary_zipCode"), OtherOrgComment = r.GetString("other_org_comment"), PersonalPhone = r.GetString("personal_phone"), SecondaryHomePhone = r.GetString("secondary_home_phone"), SecondaryPersonalPhone = r.GetString("secondary_personal_phone"), 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"), FirstName = r.GetString("first_name"), Sex = r.GetChar("sex"), IdentificationData = r.GetString("identification_data"), DateOfBirth = r.GetNullDateTime("birth_date"), LastName = r.GetString("last_name"), HouseHoldHead = r.GetBool("household_head"), NbOfDependents = r.GetNullInt("nb_of_dependents"), NbOfChildren = r.GetNullInt("nb_of_children"), ChildrenBasicEducation = r.GetNullInt("children_basic_education"), LivestockNumber = r.GetNullInt("livestock_number"), LivestockType = r.GetString("livestock_type"), LandplotSize = r.GetNullDouble("landplot_size"), HomeTimeLivingIn = r.GetNullInt("home_time_living_in"), HomeSize = r.GetNullDouble("home_size"), CapitalOthersEquipments = r.GetString("capital_other_equipments"), Experience = r.GetNullInt("experience"), NbOfPeople = r.GetNullInt("nb_of_people"), FatherName = r.GetString("father_name"), MotherName = r.GetString("mother_name"), Image = r.GetString("image_path"), StudyLevel = r.GetString("study_level"), BirthPlace = r.GetString("birth_place"), Nationality = r.GetString("nationality"), UnemploymentMonths = r.GetNullInt("unemployment_months"), SSNumber = r.GetString("SS"), CAFNumber = r.GetString("CAF"), HousingSituation = r.GetString("housing_situation"), FollowUpComment = r.GetString("follow_up_comment"), Sponsor1 = r.GetString("sponsor1"), Sponsor2 = r.GetString("sponsor2"), Sponsor1Comment = r.GetString("sponsor1_comment"), Sponsor2Comment = r.GetString("sponsor2_comment"), FavouriteLoanOfficerId = r.GetNullInt("loan_officer_id"), Branch = new Branch {Id = r.GetInt("branch_id")}, PovertyLevelIndicators = { ChildrenEducation = r.GetInt("povertylevel_childreneducation"), EconomicEducation = r.GetInt("povertylevel_economiceducation"), HealthSituation = r.GetInt("povertylevel_socialparticipation"), SocialParticipation = r.GetInt("povertylevel_healthsituation") } }; return person; }