public Booking SelectStandardBookingById(int id)
        {
            const string sqlText = @"SELECT Id, [Name], debit_account_id, credit_account_id 
                                    FROM StandardBookings
                                    WHERE Id = @Id";
            Booking standardBooking = new Booking();
            using (SqlConnection conn = GetConnection())
            {
                using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
                {
                    select.AddParam("@Id", id);

                    using (OpenCbsReader reader = select.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            standardBooking.Id = reader.GetInt("Id");
                            standardBooking.Name = reader.GetString("Name");

                            Account account =
                                _chartOfAccounts.GetAccountById(reader.GetInt("debit_account_id"));
                            standardBooking.DebitAccount = account;

                            account =
                                _chartOfAccounts.GetAccountById(reader.GetInt("credit_account_id"));
                            standardBooking.CreditAccount = account;
                        }
                    }
                }
            }
            return standardBooking;
        }
        public List<Booking> SelectAllStandardBookings()
        {
            const string sqlText = @"SELECT Id, [Name], debit_account_id, credit_account_id FROM StandardBookings";
            List<Booking> list = new List<Booking>();
            using (SqlConnection conn = GetConnection())
            {
                using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
                {
                    using (OpenCbsReader reader = select.ExecuteReader())
                    {
                        if (reader.Empty) return list;
                        while (reader.Read())
                        {
                            Booking standardBooking = new Booking();
                            standardBooking.Id = reader.GetInt("Id");
                            standardBooking.Name = reader.GetString("Name");

                            Account account =
                                _chartOfAccounts.GetAccountById(reader.GetInt("debit_account_id"));
                            standardBooking.DebitAccount = account;

                            account =
                                _chartOfAccounts.GetAccountById(reader.GetInt("credit_account_id"));
                            standardBooking.CreditAccount = account;

                            list.Add(standardBooking);
                        }
                    }
                }
                return list;
            }
        }
        private FundingLineAccountingRule _getFundingLineAccountingRule(OpenCbsReader reader)
        {
            FundingLineAccountingRule rule = new FundingLineAccountingRule();

            rule.Id           = reader.GetInt("id");
            rule.DebitAccount = new Account {
                Id = reader.GetInt("debit_account_number_id")
            };
            rule.CreditAccount = new Account {
                Id = reader.GetInt("credit_account_number_id")
            };
            rule.BookingDirection = (OBookingDirections)reader.GetSmallInt("booking_direction");

            int?fundingLineId = reader.GetInt("funding_line_id");

            if (fundingLineId.HasValue)
            {
                rule.FundingLine = new FundingLine {
                    Id = fundingLineId.Value
                }
            }
            ;

            return(rule);
        }
    }
示例#4
0
 private static LoanScaleRate GetLoanScale(OpenCbsReader pReader)
 {
     return(new LoanScaleRate
     {
         Number = pReader.GetInt("id"),
         ScaleMin = pReader.GetInt("ScaleMin"),
         ScaleMax = pReader.GetInt("ScaleMax")
     });
 }
 private static ProvisioningRate GetProvisionningRate(OpenCbsReader reader)
 {
     return(new ProvisioningRate
     {
         Number = reader.GetInt("id"),
         NbOfDaysMin = reader.GetInt("number_of_days_min"),
         NbOfDaysMax = reader.GetInt("number_of_days_max"),
         Rate = reader.GetDouble("provisioning_value")
     });
 }
        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);
        }
示例#7
0
        public bool SelectAccountingRuleByChartOfAccountsId(Account account)
        {
            const string sqlText = @"SELECT TOP 1 id
                                    FROM AccountingRules
                                    WHERE debit_account_number_id = @id OR credit_account_number_id = @id";

            using (SqlConnection conn = GetConnection())
            {
                using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
                {
                    select.AddParam("@id", account.Id);

                    int?inUse;
                    using (OpenCbsReader reader = select.ExecuteReader())
                    {
                        if (reader == null || reader.Empty)
                        {
                            return(false);
                        }

                        reader.Read();
                        inUse = reader.GetInt("id");
                    }
                    return(inUse != null);
                }
            }
        }
示例#8
0
        public Currency SelectCurrencyByName(string name)
        {
            const string q = @"SELECT id, code, is_pivot, is_swapped, use_cents FROM [Currencies] WHERE name = @name";

            using (SqlConnection conn = GetConnection())
                using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
                {
                    c.AddParam("@name", name);
                    using (OpenCbsReader r = c.ExecuteReader())
                    {
                        if (r == null || r.Empty)
                        {
                            return(null);
                        }

                        r.Read();
                        return(new Currency
                        {
                            Id = r.GetInt("id"),
                            Code = r.GetString("code"),
                            Name = name,
                            IsPivot = r.GetBool("is_pivot"),
                            IsSwapped = r.GetBool("is_swapped"),
                            UseCents = r.GetBool("use_cents")
                        });
                    }
                }
        }
示例#9
0
        public List <Currency> SelectAllCurrencies(SqlTransaction t)
        {
            List <Currency> currencies = new List <Currency>();
            const string    q          = @"SELECT * FROM Currencies ORDER BY is_pivot DESC";

            using (OpenCbsCommand c = new OpenCbsCommand(q, t.Connection, t))
                using (OpenCbsReader r = c.ExecuteReader())
                {
                    if (r == null || r.Empty)
                    {
                        return(currencies);
                    }
                    while (r.Read())
                    {
                        currencies.Add(new Currency
                        {
                            Id        = r.GetInt("id"),
                            Name      = r.GetString("name"),
                            Code      = r.GetString("code"),
                            IsPivot   = r.GetBool("is_pivot"),
                            IsSwapped = r.GetBool("is_swapped"),
                            UseCents  = r.GetBool("use_cents")
                        });
                    }
                }

            return(currencies);
        }
示例#10
0
        public Currency GetPivot()
        {
            Currency     pivot = null;
            const string q     = "SELECT * FROM Currencies where is_pivot = 1";

            using (SqlConnection conn = GetConnection())
                using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
                    using (OpenCbsReader r = c.ExecuteReader())
                    {
                        if (r == null || r.Empty)
                        {
                            return(pivot);
                        }
                        while (r.Read())
                        {
                            pivot = new Currency
                            {
                                Id        = r.GetInt("id"),
                                Name      = r.GetString("name"),
                                Code      = r.GetString("code"),
                                IsPivot   = r.GetBool("is_pivot"),
                                IsSwapped = r.GetBool("is_swapped"),
                                UseCents  = r.GetBool("use_cents")
                            };
                        }
                        return(pivot);
                    }
        }
示例#11
0
 private static Account GetAccount(OpenCbsReader pReader)
 {
     return(new Account
     {
         Id = pReader.GetInt("id"),
         Number = pReader.GetString("account_number"),
         Label = pReader.GetString("label"),
         DebitPlus = pReader.GetBool("debit_plus"),
         TypeCode = pReader.GetString("type_code"),
         AccountCategory = ((OAccountCategories)pReader.GetSmallInt("account_category_id")),
         Type = pReader.GetBool("type"),
         ParentAccountId = pReader.GetNullInt("parent_account_id"),
         Left = pReader.GetInt("lft"),
         Right = pReader.GetInt("rgt")
     });
 }
        public MessageTemplate SelectByName(string name)
        {
            string q =
                @"SELECT * FROM dbo.MessageTemplates WHERE Name LIKE '%{0}%' AND Deleted <> 1";

            q = string.Format(q, name);

            using (SqlConnection conn = GetConnection())
                using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
                    using (OpenCbsReader r = c.ExecuteReader())
                    {
                        if (r.Empty)
                        {
                            return(null);
                        }
                        if (!r.Read())
                        {
                            return(null);
                        }
                        var messageTemplate = new MessageTemplate
                        {
                            Id   = r.GetInt("Id"),
                            Name = r.GetString("Name"),
                            BccEmailAddresses = r.GetString("BccEmailAddresses"),
                            Subject           = r.GetString("Subject"),
                            Body           = r.GetString("Body"),
                            EmailBody      = r.GetString("EmailBody"),
                            SendEmail      = r.GetNullBool("SendEmail"),
                            SendSMS        = r.GetNullBool("SendSMS"),
                            IsActive       = r.GetBool("IsActive"),
                            EmailAccountId = r.GetInt("EmailAccountId"),
                            IsDefault      = r.GetBool("IsDefault"),
                            Deleted        = r.GetBool("Deleted"),
                        };

                        if (messageTemplate.EmailAccountId > 0)
                        {
                            EmailAccountManager _emailAccountManager = new EmailAccountManager(user);
                            messageTemplate.EmailAccount = _emailAccountManager.SelectById(messageTemplate.EmailAccountId);
                        }

                        return(messageTemplate);
                    }
        }
        /// <summary>
        /// Select all events for selected Funding Line
        /// </summary>
        /// <param name="fundingLine">funding line </param>
        /// <returns>list of Funding Line events</returns>
        public List <FundingLineEvent> SelectFundingLineEvents(FundingLine fundingLine)
        {
            List <FundingLineEvent> list = new List <FundingLineEvent>();

            const string sqlText =
                @"SELECT 
                        [id],
                        [code],
                        [amount],
                        [direction],
                        [fundingline_id],
                        [deleted],
                        [creation_date],
                        [type] 
                  FROM [FundingLineEvents] 
                  WHERE fundingline_id = @fundingline_id 
                  ORDER BY creation_date DESC, id DESC";

            using (SqlConnection conn = GetConnection())
                using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, conn))
                {
                    cmd.AddParam("@fundingline_id", fundingLine.Id);

                    using (OpenCbsReader reader = cmd.ExecuteReader())
                    {
                        if (reader == null || reader.Empty)
                        {
                            return(list);
                        }
                        {
                            while (reader.Read())
                            {
                                FundingLineEvent fundingLineEvent = new FundingLineEvent
                                {
                                    Id       = reader.GetInt("id"),
                                    Code     = reader.GetString("code"),
                                    Amount   = reader.GetMoney("amount"),
                                    Movement =
                                        ((OBookingDirections)
                                         reader.GetSmallInt("direction")),
                                    IsDeleted =
                                        reader.GetBool("deleted"),
                                    CreationDate =
                                        reader.GetDateTime("creation_date"),
                                    Type =
                                        ((OFundingLineEventTypes)
                                         reader.GetSmallInt("type")),
                                    FundingLine = fundingLine
                                };
                                list.Add(fundingLineEvent);
                            }
                        }
                    }
                    return(list);
                }
        }
        public List <MessageTemplate> SelectAll()
        {
            List <MessageTemplate> messageTemplates = new List <MessageTemplate>();
            const string           q =
                @"SELECT * FROM dbo.MessageTemplates where Deleted <> 1";

            using (SqlConnection conn = GetConnection())
                using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
                    using (OpenCbsReader r = c.ExecuteReader())
                    {
                        if (r.Empty)
                        {
                            return(messageTemplates);
                        }

                        while (r.Read())
                        {
                            var messageTemplate = new MessageTemplate
                            {
                                Id   = r.GetInt("Id"),
                                Name = r.GetString("Name"),
                                BccEmailAddresses = r.GetString("BccEmailAddresses"),
                                Subject           = r.GetString("Subject"),
                                Body           = r.GetString("Body"),
                                EmailBody      = r.GetString("EmailBody"),
                                SendEmail      = r.GetNullBool("SendEmail"),
                                SendSMS        = r.GetNullBool("SendSMS"),
                                IsActive       = r.GetBool("IsActive"),
                                EmailAccountId = r.GetInt("EmailAccountId"),
                                IsDefault      = r.GetBool("IsDefault"),
                                Deleted        = r.GetBool("Deleted"),
                            };

                            if (messageTemplate.EmailAccountId > 0)
                            {
                                EmailAccountManager _emailAccountManager = new EmailAccountManager(user);
                                messageTemplate.EmailAccount = _emailAccountManager.SelectById(messageTemplate.EmailAccountId);
                            }
                            messageTemplates.Add(messageTemplate);
                        }
                    }
            return(messageTemplates);
        }
        public AccountingRuleCollection SelectAllByEventType(string type)
        {
            const string sqlText = @"SELECT id, rule_type
                                     FROM [AccountingRules] 
                                     WHERE deleted = 0
                                       AND (event_type = @event_type OR @event_type = '')";

            AccountingRuleCollection rules = new AccountingRuleCollection();

            using (SqlConnection conn = GetConnection())
            {
                using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
                {
                    select.AddParam("@event_type", type);

                    using (OpenCbsReader reader = select.ExecuteReader())
                    {
                        if (reader.Empty)
                        {
                            return(rules);
                        }

                        while (reader.Read())
                        {
                            if (reader.GetChar("rule_type") == 'C')
                            {
                                rules.Add(new ContractAccountingRule {
                                    Id = reader.GetInt("id")
                                });
                            }
                        }
                    }
                }
            }

            List <Account> accounts = _accountManager.SelectAllAccounts();

            for (int i = 0; i < rules.Count; i++)
            {
                if (rules[i] is ContractAccountingRule)
                {
                    rules[i] = SelectContractAccountingRule(rules[i].Id);
                }
                else
                {
                    rules[i] = SelectFundingLineAccountingRule(rules[i].Id);
                }

                rules[i].DebitAccount  = accounts.FirstOrDefault(item => item.Id == rules[i].DebitAccount.Id);
                rules[i].CreditAccount = accounts.FirstOrDefault(item => item.Id == rules[i].CreditAccount.Id);
            }

            return(rules);
        }
        public EmailAccount SelectById(int Id)
        {
            const string q =
                @"SELECT * FROM dbo.EmailAccounts where Id = @Id AND Deleted <> 1";


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

                    using (OpenCbsReader r = c.ExecuteReader())
                    {
                        if (r.Empty)
                        {
                            return(null);
                        }
                        if (!r.Read())
                        {
                            return(null);
                        }

                        return(new EmailAccount
                        {
                            Id = r.GetInt("Id"),
                            Email = r.GetString("Email"),
                            DisplayName = r.GetString("DisplayName"),
                            Host = r.GetString("Host"),
                            Port = r.GetInt("Port"),
                            Username = r.GetString("Username"),
                            Password = r.GetString("Password"),
                            EnableSsl = r.GetBool("EnableSsl"),
                            UseDefaultCredentials = r.GetBool("UseDefaultCredentials"),
                            IsDefaultEmailAccount = r.GetBool("IsDefaultEmailAccount"),
                            Deleted = r.GetBool("Deleted"),
                        });
                    }
                }
        }
        public List <ExchangeRate> SelectRatesByDate(DateTime beginDate, DateTime endDate)
        {
            const string q =
                @"SELECT 
                     exchange_date, 
                     exchange_rate, 
                     currency_id,
                    is_pivot,
                    is_swapped,
                    name,
                    code
                  FROM ExchangeRates 
                  INNER JOIN Currencies ON ExchangeRates.currency_id = Currencies.id 
                  WHERE exchange_date BETWEEN @beginDate AND @endDate";

            List <ExchangeRate> rates;

            using (SqlConnection conn = GetConnection())
                using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
                {
                    c.AddParam("@beginDate", beginDate.Date);
                    c.AddParam("@endDate", endDate.Date);
                    using (OpenCbsReader r = c.ExecuteReader())
                    {
                        if (r == null || r.Empty)
                        {
                            return(null);
                        }

                        rates = new List <ExchangeRate>();
                        while (r.Read())
                        {
                            ExchangeRate newRate = new ExchangeRate
                            {
                                Date     = r.GetDateTime("exchange_date"),
                                Rate     = r.GetDouble("exchange_rate"),
                                Currency = new Currency
                                {
                                    Id        = r.GetInt("currency_id"),
                                    IsPivot   = r.GetBool("is_pivot"),
                                    IsSwapped = r.GetBool("is_swapped"),
                                    Name      = r.GetString("name"),
                                    Code      = r.GetString("code")
                                }
                            };
                            rates.Add(newRate);
                        }
                    }
                }
            return(rates);
        }
示例#18
0
        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);
        }
        public List <EmailAccount> SelectAll()
        {
            List <EmailAccount> emailAccounts = new List <EmailAccount>();
            const string        q             =
                @"SELECT * FROM dbo.EmailAccounts where Deleted <> 1";

            using (SqlConnection conn = GetConnection())
                using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
                    using (OpenCbsReader r = c.ExecuteReader())
                    {
                        if (r.Empty)
                        {
                            return(emailAccounts);
                        }

                        while (r.Read())
                        {
                            var b = new EmailAccount
                            {
                                Id                    = r.GetInt("Id"),
                                Email                 = r.GetString("Email"),
                                DisplayName           = r.GetString("DisplayName"),
                                Host                  = r.GetString("Host"),
                                Port                  = r.GetInt("Port"),
                                Username              = r.GetString("Username"),
                                Password              = r.GetString("Password"),
                                EnableSsl             = r.GetBool("EnableSsl"),
                                UseDefaultCredentials = r.GetBool("UseDefaultCredentials"),
                                IsDefaultEmailAccount = r.GetBool("IsDefaultEmailAccount"),
                                Deleted               = r.GetBool("Deleted"),
                            };
                            emailAccounts.Add(b);
                        }
                    }
            return(emailAccounts);
        }
        public int SelectFundingLineEventId(FundingLineEvent pFundingLineEvent, SqlTransaction sqlTransac,
                                            bool includeDeleted)
        {
            int id = -1;

            string sqlText =
                @"SELECT [id] 
                          FROM [FundingLineEvents] 
                          WHERE [code] = @code 
                                 AND [amount] = @amount
                                 AND [direction] = @direction
                                 AND [type] = @type
                                 AND [fundingline_id] = @fundinglineid";

            if (!includeDeleted)
            {
                sqlText += " and deleted = @deleted";
            }

            OpenCbsCommand cmd = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac);

            cmd.AddParam("@code", pFundingLineEvent.Code);
            cmd.AddParam("@amount", pFundingLineEvent.Amount);
            cmd.AddParam("@direction", (int)pFundingLineEvent.Movement);
            cmd.AddParam("@type", (int)pFundingLineEvent.Type);
            cmd.AddParam("@fundinglineid", pFundingLineEvent.FundingLine.Id);
            if (!includeDeleted)
            {
                cmd.AddParam("@deleted", pFundingLineEvent.IsDeleted);
            }

            using (OpenCbsReader reader = cmd.ExecuteReader())
            {
                if (reader != null)
                {
                    if (!reader.Empty)
                    {
                        reader.Read();
                        id = reader.GetInt("id");
                    }
                }
            }
            return(id);
        }
示例#21
0
        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);
        }
示例#22
0
        private static ContractAccountingRule GetContractAccountingRule(OpenCbsReader reader)
        {
            ContractAccountingRule rule = new ContractAccountingRule();

            rule.Id = reader.GetInt("id");
            rule.EventType = new EventType
                                 {
                                     EventCode = reader.GetString("event_type"),
                                     Description = reader.GetString("event_description")
                                 };
            rule.EventAttribute = new EventAttribute
                                      {
                                          Id = reader.GetInt("event_attribute_id"),
                                          Name = reader.GetString("attribute_name")
                                      };
            rule.DebitAccount = new Account { Id = reader.GetInt("debit_account_number_id") };
            rule.CreditAccount = new Account { Id = reader.GetInt("credit_account_number_id") };
            rule.BookingDirection = (OBookingDirections)reader.GetSmallInt("booking_direction");
            rule.Order = reader.GetInt("order");
            rule.Description = reader.GetString("rule_description");

            rule.ProductType = (OProductTypes)reader.GetSmallInt("product_type");

            int? loanProductId = reader.GetNullInt("loan_product_id");
            if (loanProductId.HasValue)
                rule.LoanProduct = new LoanProduct { Id = loanProductId.Value };

            int? currencyId = reader.GetNullInt("currency_id");
            if (currencyId.HasValue)
                rule.Currency = new Currency { Id = currencyId.Value };

            int? savingsProductId = reader.GetNullInt("savings_product_id");
            if (savingsProductId.HasValue)
                rule.SavingProduct = new SavingsBookProduct { Id = savingsProductId.Value };

            rule.ClientType = reader.GetChar("client_type").ConvertToClientType();

            int? activityId = reader.GetNullInt("activity_id");

            if (activityId.HasValue)
                rule.EconomicActivity = new EconomicActivity { Id = activityId.Value };

            return rule;
        }
示例#23
0
 private static Account GetAccount(OpenCbsReader pReader)
 {
     return new Account
     {
         Id = pReader.GetInt("id"),
         Number = pReader.GetString("account_number"),
         Label = pReader.GetString("label"),
         DebitPlus = pReader.GetBool("debit_plus"),
         TypeCode = pReader.GetString("type_code"),
         AccountCategory = ((OAccountCategories)pReader.GetSmallInt("account_category_id")),
         Type = pReader.GetBool("type"),
         ParentAccountId = pReader.GetNullInt("parent_account_id"),
         Left = pReader.GetInt("lft"),
         Right = pReader.GetInt("rgt")
     };
 }
示例#24
0
        public List <Installment> SelectInstallment(DateTime pStartDate, DateTime pEndDate)
        {
            List <Installment> rows = new List <Installment>();

            const string sqlText = @"SELECT [credit].[id] AS contract_id 
                                           ,[expected_date] AS installment_date
	                                       ,[contract_code]
	                                       ,[number] AS installment_number
                                           ,[interest_repayment] + [capital_repayment] - [paid_capital] - [paid_interest] AS installment_amount
	                                       ,PersonalBank.[name] AS personal_bank_name
	                                       ,PersonalBank.[BIC] AS personal_bank_bic
                                           ,PersonalBank.[IBAN1] AS personal_bank_iban_1
                                           ,PersonalBank.[IBAN2] AS personal_bank_iban_2
                                           ,BusinessBank.[name] AS business_bank_name
                                           ,BusinessBank.[BIC] AS business_bank_bic
                                           ,BusinessBank.[IBAN1] AS business_bank_iban_1
                                           ,BusinessBank.[IBAN2] AS business_bank_iban_2
                                           ,Packages.code AS product_code
                                           ,Packages.Name AS product_name
                                           ,[Persons].[id] AS client_id
                                           ,[Persons].[first_name] + ' ' + [Persons].[last_name] AS client_name
                                     FROM [Installments] i
                                     INNER JOIN Contracts ON Contracts.id = contract_id
                                     INNER JOIN Credit ON Credit.id = contract_id
                                     INNER JOIN Projects ON Projects.id = project_id
                                     INNER JOIN Persons ON Persons.id = Projects.tiers_id
                                     INNER JOIN Banks AS PersonalBank ON PersonalBank.id = Persons.personalBank_id
                                     INNER JOIN Banks AS BusinessBank ON BusinessBank.id = Persons.businessBank_id
                                     INNER JOIN Packages ON Packages.id = Credit.package_id
                                     WHERE paid_date IS NULL
                                     AND ([interest_repayment] + [capital_repayment] - [paid_capital] - [paid_interest]) != 0
                                     AND Credit.disbursed = 1
                                     AND expected_date BETWEEN @startDate AND @endDate
                                     AND (SELECT COUNT(i2.number) 
                                          FROM Installments i2
	                                      WHERE i2.contract_id = i.contract_id
	                                      AND i2.number < i.number
	                                      AND ((i2.paid_date IS NULL AND (i2.interest_repayment + i2.capital_repayment - i2.paid_capital - paid_capital) > 0)
                                           OR (i2.paid_date IS NOT NULL AND i2.pending = 1))) = 0";

            using (SqlConnection connection = GetConnection())
                using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, connection))
                {
                    cmd.AddParam("@startDate", pStartDate);
                    cmd.AddParam("@endDate", pEndDate);

                    using (OpenCbsReader reader = cmd.ExecuteReader())
                    {
                        if (reader == null || reader.Empty)
                        {
                            return(rows);
                        }

                        while (reader.Read())
                        {
                            Installment row = new Installment
                            {
                                InstallmentDate   = reader.GetDateTime("installment_date"),
                                ContractId        = reader.GetInt("contract_id"),
                                ClientId          = reader.GetInt("client_id"),
                                ContractCode      = reader.GetString("contract_code"),
                                InstallmentNumber = reader.GetInt("installment_number"),
                                InstallmentAmount = reader.GetMoney("installment_amount"),
                                PersonalBankName  = reader.GetString("personal_bank_name"),
                                PersonalBankBic   = reader.GetString("personal_bank_bic"),
                                PersonalBankIban1 = reader.GetString("personal_bank_iban_1"),
                                PersonalBankIban2 = reader.GetString("personal_bank_iban_2"),
                                BusinessBankName  = reader.GetString("business_bank_name"),
                                BusinessBankBic   = reader.GetString("business_bank_bic"),
                                BusinessBankIban1 = reader.GetString("business_bank_iban_1"),
                                BusinessBankIban2 = reader.GetString("business_bank_iban_2"),
                                ProductCode       = reader.GetString("product_code"),
                                ProductName       = reader.GetString("product_name"),
                                ClientName        = reader.GetString("client_name")
                            };

                            rows.Add(row);
                        }
                    }
                }

            return(rows);
        }
示例#25
0
 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"),
                };
 }
示例#26
0
        private Event ReadEvent(OpenCbsReader r)
        {
            Event e;

            if (r.GetNullInt("lde_id").HasValue)
            {
                e = GetLoanDisbursmentEvent(r);
            }
            else if (r.GetNullInt("woe_id").HasValue)
            {
                e = GetWriteOffEvent(r);
            }
            else if (r.GetNullInt("rle_id").HasValue)
            {
                e = GetReschedulingLoanEvent(r);
            }
            else if (r.GetNullInt("rpe_id").HasValue)
            {
                e = GetRepaymentEvent(r);
            }
            else if (r.GetNullInt("tranche_id").HasValue)
            {
                e = GetTrancheLoanEvent(r);
            }
            else if (r.GetNullInt("liae_id").HasValue)
            {
                e = GetLoanInterestAccruingEvent(r);
            }
            else if (r.GetNullInt("ov_id").HasValue)
            {
                e = GetOverdueEvent(r);
            }
            else if (r.GetNullInt("pe_id").HasValue)
            {
                e = GetProvisionEvent(r);
            }
            else if (r.GetNullInt("ef_id").HasValue)
            {
                e = GetEntryFeeEvent(r);
            }
            else if (r.GetNullInt("cie_id").HasValue)
            {
                e = GetCreditInsuranceEvent(r);
            }
            else if (r.GetString("code").StartsWith("S"))
            {
                e = GetSavingEvent(r);
            }
            else
            {
                if(r.GetString("code").Equals("LOVE"))
                    e = new LoanValidationEvent{Id = r.GetInt("event_id")};
                else if (r.GetString("code").Equals("LOCE"))
                    e = new LoanCloseEvent{Id = r.GetInt("event_id")};
                else
                    e = new RegEvent {Id = r.GetInt("event_id")};
            }

            GetEvent(r, e);

            return e;
        }
示例#27
0
 private LoanDisbursmentEvent GetLoanDisbursmentEvent(OpenCbsReader r)
 {
     return new LoanDisbursmentEvent
                {
                    Id = r.GetInt("lde_id"),
                    Amount = r.GetMoney("lde_amount"),
                    Fee = r.GetMoney("lde_fees"),
                    PaymentMethodId = r.GetNullInt("lde_pm"),
                    PaymentMethod = r.GetNullInt("lde_pm") == null
                                        ? null
                                        : _paymentMethodManager.SelectPaymentMethodById(
                                            r.GetNullInt("lde_pm").Value)
                };
 }
示例#28
0
        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;
        }
示例#29
0
        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;
        }
示例#30
0
 private static EntryFee GetEntryFee(OpenCbsReader r)
 {
     EntryFee entryFee = new EntryFee
                        {
                            Id = r.GetInt("id"),
                            Name = r.GetString("name_of_fee"),
                            Min = r.GetNullDecimal("min"),
                            Max = r.GetNullDecimal("max"),
                            Value = r.GetNullDecimal("value"),
                            IsRate = r.GetNullBool("rate") ?? false,
                            Index = r.GetInt("fee_index"),
                            CycleId = r.GetNullInt("cycle_id")
                        };
     return entryFee;
 }
示例#31
0
        private static LoanProduct GetProduct(OpenCbsReader r)
        {
            LoanProduct package = new LoanProduct();
            package.Id = r.GetInt("id");
            package.Delete = r.GetBool("deleted");
            package.Name = r.GetString("name");
            package.Code = r.GetString("code");
            package.ClientType = r.GetChar("client_type");
            package.LoanType = (OLoanTypes)r.GetSmallInt("loan_type");
            package.RoundingType = (ORoundingType)r.GetSmallInt("rounding_type");
            package.Amount = r.GetMoney("amount");
            package.AmountMin = r.GetMoney("amount_min");
            package.AmountMax = r.GetMoney("amount_max");
            package.InterestRate = r.GetNullDecimal("interest_rate");
            package.InterestRateMin = r.GetNullDecimal("interest_rate_min");
            package.InterestRateMax = r.GetNullDecimal("interest_rate_max");
            package.GracePeriod = r.GetNullInt("grace_period");
            package.GracePeriodMin = r.GetNullInt("grace_period_min");
            package.GracePeriodMax = r.GetNullInt("grace_period_max");
            package.GracePeriodOfLateFees = r.GetNullInt("grace_period_of_latefees");
            package.NbOfInstallments = r.GetNullInt("number_of_installments");
            package.NbOfInstallmentsMin = r.GetNullInt("number_of_installments_min");
            package.NbOfInstallmentsMax = r.GetNullInt("number_of_installments_max");

            package.AnticipatedTotalRepaymentPenalties = r.GetNullDouble("anticipated_total_repayment_penalties");
            package.AnticipatedTotalRepaymentPenaltiesMin = r.GetNullDouble("anticipated_total_repayment_penalties_min");
            package.AnticipatedTotalRepaymentPenaltiesMax = r.GetNullDouble("anticipated_total_repayment_penalties_max");

            package.AnticipatedPartialRepaymentPenalties = r.GetNullDouble("anticipated_partial_repayment_penalties");
            package.AnticipatedPartialRepaymentPenaltiesMin = r.GetNullDouble("anticipated_partial_repayment_penalties_min");
            package.AnticipatedPartialRepaymentPenaltiesMax = r.GetNullDouble("anticipated_partial_repayment_penalties_max");

            package.ChargeInterestWithinGracePeriod = r.GetBool("charge_interest_within_grace_period");
            package.KeepExpectedInstallment = r.GetBool("keep_expected_installment");

            package.AnticipatedTotalRepaymentPenaltiesBase = (OAnticipatedRepaymentPenaltiesBases)r.GetSmallInt("anticipated_total_repayment_base");
            package.AnticipatedPartialRepaymentPenaltiesBase = (OAnticipatedRepaymentPenaltiesBases)r.GetSmallInt("anticipated_partial_repayment_base");

            package.NonRepaymentPenalties.InitialAmount = r.GetNullDouble("non_repayment_penalties_based_on_initial_amount");
            package.NonRepaymentPenalties.OLB = r.GetNullDouble("non_repayment_penalties_based_on_olb");
            package.NonRepaymentPenalties.OverDueInterest = r.GetNullDouble("non_repayment_penalties_based_on_overdue_interest");
            package.NonRepaymentPenalties.OverDuePrincipal = r.GetNullDouble("non_repayment_penalties_based_on_overdue_principal");

            package.NonRepaymentPenaltiesMin.InitialAmount = r.GetNullDouble("non_repayment_penalties_based_on_initial_amount_min");
            package.NonRepaymentPenaltiesMin.OLB = r.GetNullDouble("non_repayment_penalties_based_on_olb_min");
            package.NonRepaymentPenaltiesMin.OverDuePrincipal = r.GetNullDouble("non_repayment_penalties_based_on_overdue_principal_min");
            package.NonRepaymentPenaltiesMin.OverDueInterest = r.GetNullDouble("non_repayment_penalties_based_on_overdue_interest_min");

            package.NonRepaymentPenaltiesMax.InitialAmount = r.GetNullDouble("non_repayment_penalties_based_on_initial_amount_max");
            package.NonRepaymentPenaltiesMax.OLB = r.GetNullDouble("non_repayment_penalties_based_on_olb_max");
            package.NonRepaymentPenaltiesMax.OverDueInterest = r.GetNullDouble("non_repayment_penalties_based_on_overdue_interest_max");
            package.NonRepaymentPenaltiesMax.OverDuePrincipal = r.GetNullDouble("non_repayment_penalties_based_on_overdue_principal_max");
            package.AllowFlexibleSchedule = r.GetBool("allow_flexible_schedule");

            package.UseGuarantorCollateral = r.GetBool("use_guarantor_collateral");
            package.SetSeparateGuarantorCollateral = r.GetBool("set_separate_guarantor_collateral");

            package.PercentageTotalGuarantorCollateral = r.GetInt("percentage_total_guarantor_collateral");
            package.PercentageSeparateGuarantour = r.GetInt("percentage_separate_guarantor");
            package.PercentageSeparateCollateral = r.GetInt("percentage_separate_collateral");

            package.UseCompulsorySavings = r.GetBool("use_compulsory_savings");
            package.CompulsoryAmount = r.GetNullInt("compulsory_amount");
            package.CompulsoryAmountMin = r.GetNullInt("compulsory_amount_min");
            package.CompulsoryAmountMax = r.GetNullInt("compulsory_amount_max");
            package.UseEntryFeesCycles = r.GetBool("use_entry_fees_cycles");

            //if (DatabaseHelper.GetNullAuthorizedInt32("fundingLine_id", pReader).HasValue)
            //{
            //    package.FundingLine = new FundingLine { Id = r.GetNullInt("fundingLine_id").Value };
            //    package.FundingLine.Name = r.GetString("funding_line_name");
            //    package.FundingLine.Currency = new Currency { Id = r.GetInt("funding_line_currency_id") };
            //}
            if (r.GetNullInt("currency_id").HasValue)
            {
                package.Currency = new Currency
                                       {
                                           Id = r.GetInt("currency_id"),
                                           Code = r.GetString("currency_code"),
                                           Name = r.GetString("currency_name"),
                                           IsPivot = r.GetBool("currency_is_pivot"),
                                           IsSwapped = r.GetBool("currency_is_swapped"),
                                           UseCents = r.GetBool("currency_use_cents")
                                       };
            }

            /* Line of credit */
            package.DrawingsNumber = r.GetNullInt("number_of_drawings_loc");

            package.AmountUnderLoc = r.GetMoney("amount_under_loc");
            package.AmountUnderLocMin = r.GetMoney("amount_under_loc_min");
            package.AmountUnderLocMax = r.GetMoney("amount_under_loc_max");

            package.MaturityLoc = r.GetNullInt("maturity_loc");
            package.MaturityLocMin = r.GetNullInt("maturity_loc_min");
            package.MaturityLocMax = r.GetNullInt("maturity_loc_max");
            package.ActivatedLOC = r.GetBool("activated_loc");
            package.CycleId = r.GetNullInt("cycle_id");
            package.CreditInsuranceMin = r.GetDecimal("insurance_min");
            package.CreditInsuranceMax = r.GetDecimal("insurance_max");
            package.InterestScheme = (OInterestScheme)r.GetInt("interest_scheme");
            return package;
        }
示例#32
0
        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")
            };
        }
示例#33
0
        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 ContractAccountingRule GetContractAccountingRule(OpenCbsReader reader)
        {
            ContractAccountingRule rule = new ContractAccountingRule();

            rule.Id        = reader.GetInt("id");
            rule.EventType = new EventType
            {
                EventCode   = reader.GetString("event_type"),
                Description = reader.GetString("event_description")
            };
            rule.EventAttribute = new EventAttribute
            {
                Id   = reader.GetInt("event_attribute_id"),
                Name = reader.GetString("attribute_name")
            };
            rule.DebitAccount = new Account {
                Id = reader.GetInt("debit_account_number_id")
            };
            rule.CreditAccount = new Account {
                Id = reader.GetInt("credit_account_number_id")
            };
            rule.BookingDirection = (OBookingDirections)reader.GetSmallInt("booking_direction");
            rule.Order            = reader.GetInt("order");
            rule.Description      = reader.GetString("rule_description");

            rule.ProductType = (OProductTypes)reader.GetSmallInt("product_type");

            int?loanProductId = reader.GetNullInt("loan_product_id");

            if (loanProductId.HasValue)
            {
                rule.LoanProduct = new LoanProduct {
                    Id = loanProductId.Value
                }
            }
            ;

            int?currencyId = reader.GetNullInt("currency_id");

            if (currencyId.HasValue)
            {
                rule.Currency = new Currency {
                    Id = currencyId.Value
                }
            }
            ;

            int?savingsProductId = reader.GetNullInt("savings_product_id");

            if (savingsProductId.HasValue)
            {
                rule.SavingProduct = new SavingsBookProduct {
                    Id = savingsProductId.Value
                }
            }
            ;

            rule.ClientType = reader.GetChar("client_type").ConvertToClientType();

            int?activityId = reader.GetNullInt("activity_id");

            if (activityId.HasValue)
            {
                rule.EconomicActivity = new EconomicActivity {
                    Id = activityId.Value
                }
            }
            ;

            return(rule);
        }
示例#35
0
 private static ExoticInstallmentsTable _GetExoticProduct(OpenCbsReader r)
 {
     return new ExoticInstallmentsTable
                {
                    Id = r.GetInt("id"),
                    Name = r.GetString("name")
                };
 }
示例#36
0
 private static LoanEntryFeeEvent GetEntryFeeEvent(OpenCbsReader r)
 {
     return new LoanEntryFeeEvent
     {
         Id = r.GetInt("ef_id"),
         Fee = r.GetMoney("ef_fee"),
         Cancelable = true,
         DisbursementEventId = r.GetInt("disbursement_event_id")
     };
 }
示例#37
0
        private RepaymentEvent GetRepaymentEvent(OpenCbsReader r)
        {
            RepaymentEvent e = new RepaymentEvent {Id = r.GetInt("rpe_id")};
            switch (r.GetString("event_type"))
            {
                case "RBLE":
                    {
                        e = new BadLoanRepaymentEvent {Id = r.GetInt("rpe_id")};
                        break;
                    }
                case "RRLE":
                    {
                        e = new RescheduledLoanRepaymentEvent {Id = r.GetInt("rpe_id")};
                        break;
                    }
                case "ROWO":
                    {
                        e = new RepaymentOverWriteOffEvent { Id = r.GetInt("rpe_id") };
                        break;
                    }
                case "PRLR":
                    {
                        e = new PendingRepaymentEvent(r.GetString("event_type"))
                                {Id = r.GetInt("rpe_id")};
                        break;
                    }
                case "PBLR":
                    {
                        e = new PendingRepaymentEvent(r.GetString("event_type"))
                                {Id = r.GetInt("rpe_id")};
                        break;
                    }
                case "PRWO":
                    {
                        e = new PendingRepaymentEvent(r.GetString("event_type"))
                                {Id = r.GetInt("rpe_id")};
                        break;
                    }
                case "PERE":
                    {
                        e = new PendingRepaymentEvent(r.GetString("event_type"))
                                {Id = r.GetInt("rpe_id")};
                        break;
                    }
            }

            e.Principal = r.GetMoney("rpe_principal");
            e.Interests = r.GetMoney("rpe_interests");
            e.Penalties = r.GetMoney("rpe_penalties");
            e.Commissions = r.GetMoney("rpe_commissions");
            e.PastDueDays = r.GetInt("rpe_past_due_days");
            e.InstallmentNumber = r.GetInt("rpe_installment_number");
            e.PaymentMethodId = r.GetNullInt("rpe_pm");
            e.PaymentMethod = e.PaymentMethodId == null ? null :
                _paymentMethodManager.SelectPaymentMethodById(e.PaymentMethodId.Value);

            e.CalculatedPenalties = r.GetMoney("rpe_calculated_penalties");
            e.WrittenOffPenalties = r.GetMoney("rpe_written_off_penalties");
            e.UnpaidPenalties = r.GetMoney("rpe_unpaid_penalties");

            e.Code = r.GetString("event_type");

            if (e.Code != "RBLE")
                e.RepaymentType = OPaymentType.StandardPayment;

            // set type of payment
            switch (r.GetString("event_type").Trim())
            {
                case "ATR":
                    {
                        e.RepaymentType = OPaymentType.TotalPayment;
                        break;
                    }
                case "APR":
                    {
                        e.RepaymentType = OPaymentType.PartialPayment;
                        break;
                    }
                case "APTR":
                    {
                        e.RepaymentType = OPaymentType.PersonTotalPayment;
                        break;
                    }
            }

            return e;
        }
示例#38
0
        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");
        }
示例#39
0
 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;
 }
示例#40
0
 private static AccruedInterestEvent GetLoanInterestAccruingEvent(OpenCbsReader r)
 {
     return new AccruedInterestEvent{
                    Id = r.GetInt("liae_id"),
                    AccruedInterest = r.GetMoney("liae_accruedInterest"),
                    Interest = r.GetMoney("liae_interestPrepayment"),
                    Rescheduled = r.GetBool("liae_rescheduled"),
                    InstallmentNumber = r.GetInt("liae_installmentNumber")
                };
 }
示例#41
0
 private static TrancheEvent GetTrancheLoanEvent(OpenCbsReader r)
 {
     return new TrancheEvent{
         Id = r.GetInt("tranche_id"),
         Amount = r.GetMoney("tranche_amount"),
         InterestRate = r.GetMoney("tranche_interest_rate").Value,
         Maturity = r.GetInt("tranche_maturity"),
         StartDate = r.GetDateTime("tranche_start_date"),
         GracePeriod = r.GetInt("tranche_grace_period"),
         FirstRepaymentDate = r.GetDateTime("tranche_first_repayment_date"),
     };
 }
示例#42
0
 private static OverdueEvent GetOverdueEvent(OpenCbsReader r)
 {
     return new OverdueEvent{
         Id = r.GetInt("ov_id"),
         OLB = r.GetMoney("ov_olb"),
         OverdueDays = r.GetInt("ov_overdue_days"),
         OverduePrincipal = r.GetMoney("ov_overdue_principal")
     };
 }
        private static Booking GetBooking(OpenCbsReader reader)
        {
            return new Booking
                       {
                           Id = reader.GetInt("id"),
                           Amount = reader.GetMoney("amount"),
                           IsExported = reader.GetBool("is_exported"),
                           DebitAccount =
                               new Account
                                   {
                                       Id = reader.GetInt("debit_account_number_id")
                                   },
                           CreditAccount =
                               new Account
                                   {
                                       Id = reader.GetInt("credit_account_number_id")
                                   },
                           EventId = reader.GetInt("event_id"),
                           ContractId = reader.GetInt("contract_id"),

                           Date = reader.GetDateTime("transaction_date"),
                           Currency =
                               new Currency
                                   {Id = reader.GetInt("currency_id")},
                           ExchangeRate = reader.GetDouble("exchange_rate"),
                           Description = reader.GetString("description"),
                           Branch = new Branch {Id = reader.GetInt("branch_id")}

                       };
        }
示例#44
0
 private static ProvisionEvent GetProvisionEvent(OpenCbsReader r)
 {
     return new ProvisionEvent{
         Id = r.GetInt("pe_id"),
         Amount = r.GetMoney("pe_amount"),
         OverdueDays = r.GetInt("pe_overdue_days")
     };
 }
示例#45
0
        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") };
        }
示例#46
0
 private static RescheduleLoanEvent GetReschedulingLoanEvent(OpenCbsReader r)
 {
     return new RescheduleLoanEvent{
         Id = r.GetInt("rle_id"),
         Amount = r.GetMoney("rle_amount"),
         NbOfMaturity = r.GetInt("rle_maturity"),
         DateOffset = r.GetInt("rle_date_offset")
     };
 }
示例#47
0
        private FundingLineAccountingRule _getFundingLineAccountingRule(OpenCbsReader reader)
        {
            FundingLineAccountingRule rule = new FundingLineAccountingRule();

            rule.Id = reader.GetInt("id");
            rule.DebitAccount = new Account { Id = reader.GetInt("debit_account_number_id") };
            rule.CreditAccount = new Account { Id = reader.GetInt("credit_account_number_id") };
            rule.BookingDirection = (OBookingDirections)reader.GetSmallInt("booking_direction");

            int? fundingLineId = reader.GetInt("funding_line_id");

            if (fundingLineId.HasValue)
                rule.FundingLine = new FundingLine { Id = fundingLineId.Value };

            return rule;
        }
示例#48
0
 private static TrancheEvent GetTrancheLoanEvent(OpenCbsReader r)
 {
     return new TrancheEvent{
         Id = r.GetInt("tranche_id"),
         Amount = r.GetMoney("tranche_amount"),
         InterestRate = r.GetMoney("tranche_interest_rate").Value,
         Maturity = r.GetInt("tranche_maturity"),
         StartDate = r.GetDateTime("tranche_start_date")
     };
 }
示例#49
0
 private static TrancheEvent GetTransh(OpenCbsReader r)
 {
     return new TrancheEvent
     {
         Number = r.GetInt("Number"),
         StartDate = r.GetDateTime("start_date"),
         Amount = r.GetMoney("amount"),
         Maturity = r.GetInt("countOfInstallments"),
         ApplyNewInterest = r.GetBool("ApplyNewInterest"),
         InterestRate = r.GetDecimal("interest_rate"),
         StartedFromInstallment = r.GetInt("started_from_installment"),
         Deleted = r.GetBool("is_deleted"),
         Id = r.GetInt("event_id")
     };
 }
示例#50
0
 private static Event GetWriteOffEvent(OpenCbsReader r)
 {
     return new WriteOffEvent
                           {
                               Id = r.GetInt("woe_id"),
                               OLB = r.GetMoney("woe_olb"),
                               AccruedInterests = r.GetMoney("woe_accrued_interests"),
                               AccruedPenalties = r.GetMoney("woe_accrued_penalties"),
                               PastDueDays = r.GetInt("woe_past_due_days"),
                               OverduePrincipal = r.GetMoney("woe_overdue_principal")
                           };
 }
示例#51
0
 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")};
 }
示例#52
0
 private static ExoticInstallment _GetExoticInstallment(OpenCbsReader r)
 {
     return new ExoticInstallment
                {
                    Number = r.GetInt("number"),
                    PrincipalCoeff = r.GetDouble("principal_coeff"),
                    InterestCoeff =r.GetNullDouble("interest_coeff")
                };
 }
 private static ProvisioningRate GetProvisionningRate(OpenCbsReader reader)
 {
     return new ProvisioningRate
                {
                    Number = reader.GetInt("id"),
                    NbOfDaysMin = reader.GetInt("number_of_days_min"),
                    NbOfDaysMax = reader.GetInt("number_of_days_max"),
                    ProvisioningValue = reader.GetDouble("provisioning_value"),
                    ProvisioningInterest = reader.GetDouble("provisioning_interest"),
                    ProvisioningPenalty = reader.GetDouble("provisioning_penalty")
                };
 }
示例#54
0
 private static Event GetCreditInsuranceEvent(OpenCbsReader r)
 {
     CreditInsuranceEvent cie = new CreditInsuranceEvent();
     cie.Id = r.GetInt("cie_id");
     cie.Commission = r.GetDecimal("cie_commission");
     cie.Principal = r.GetDecimal("cie_principal");
     return cie;
 }