private static Installment GetInstallment(OpenCbsReader r)
{
var installment = new Installment
{
Number = r.GetInt("number"),
ExpectedDate = r.GetDateTime("expected_date"),
InterestsRepayment = r.GetMoney("interest_repayment"),
CapitalRepayment = r.GetMoney("capital_repayment"),
PaidDate = r.GetNullDateTime("paid_date"),
PaidCapital = r.GetMoney("paid_capital"),
FeesUnpaid = r.GetMoney("fees_unpaid"),
PaidInterests = r.GetMoney("paid_interest"),
PaidFees = r.GetMoney("paid_fees"),
Comment = r.GetString("comment"),
IsPending = r.GetBool("pending"),
StartDate = r.GetDateTime("start_date"),
OLB = r.GetMoney("olb"),
Commission = r.GetMoney("commission"),
PaidCommissions = r.GetMoney("paid_commission"),
LastInterestAccrualDate = r.GetDateTime("last_interest_accrual_date"),
ExtraAmount1 = r.GetMoney("extra_amount_1"),
ExtraAmount2 = r.GetMoney("extra_amount_2")
};
return(installment);
}
public FundingLine SelectFundingLineByName(string name)
{
FundingLine fundingLine = null;
string q =
@"SELECT FundingLines.[id],
FundingLines.[name],
FundingLines.[deleted],
FundingLines.[amount],
FundingLines.[begin_date],
FundingLines.[end_date],
FundingLines.[purpose],
FundingLines.[currency_id],
Currencies.[name] as currency_name,
Currencies.[code] as currency_code,
Currencies.[is_pivot] as currency_is_pivot,
Currencies.[is_swapped] as currency_is_swapped
FROM [FundingLines]
LEFT JOIN Currencies ON FundingLines.currency_id = Currencies.id
WHERE FundingLines.[name]=@name AND [deleted]=0";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@name", name);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r != null)
{
if (!r.Empty)
{
r.Read();
fundingLine = new FundingLine();
fundingLine.Id = r.GetInt("id");
fundingLine.Name = r.GetString("name");
fundingLine.Deleted = r.GetBool("deleted");
fundingLine.StartDate = r.GetDateTime("begin_date");
fundingLine.Purpose = r.GetString("purpose");
fundingLine.EndDate = r.GetDateTime("end_date");
fundingLine.Amount = r.GetMoney("amount");
fundingLine.Currency = new Currency
{
Id = r.GetInt("currency_id"),
Name = r.GetString("currency_name"),
Code = r.GetString("currency_code"),
IsPivot = r.GetBool("currency_is_pivot"),
IsSwapped = r.GetBool("currency_is_swapped")
};
}
}
}
}
SetLazyLoader(fundingLine);
return(fundingLine);
}
public List <FundingLine> SelectFundingLines()
{
List <FundingLine> list = new List <FundingLine>();
string q =
@"SELECT FundingLines.[id],
FundingLines.[name],
[begin_date],
[end_date],
[amount],
[purpose],
[deleted],
[currency_id],
Currencies.[name] as currency_name,
Currencies.[code] as currency_code,
Currencies.[is_pivot] as currency_is_pivot,
Currencies.[is_swapped] as currency_is_swapped
FROM [FundingLines]
LEFT JOIN Currencies ON FundingLines.currency_id = Currencies.id
WHERE [deleted] = 0";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
using (OpenCbsReader r = c.ExecuteReader())
{
while (r.Read())
{
FundingLine fundingLine = new FundingLine();
fundingLine.Id = r.GetInt("id");
fundingLine.Name = r.GetString("name");
fundingLine.StartDate = r.GetDateTime("begin_date");
fundingLine.EndDate = r.GetDateTime("end_date");
fundingLine.Amount = r.GetMoney("amount");
fundingLine.Amount = r.GetMoney("amount");
fundingLine.Purpose = r.GetString("purpose");
fundingLine.Deleted = r.GetBool("deleted");
fundingLine.Currency = new Currency
{
Id = r.GetInt("currency_id"),
Name = r.GetString("currency_name"),
Code = r.GetString("currency_code"),
IsPivot = r.GetBool("currency_is_pivot"),
IsSwapped = r.GetBool("currency_is_swapped")
};
SetLazyLoader(fundingLine);
list.Add(fundingLine);
}
}
return(list);
}
private static Installment GetInstallmentHistoryFromReader(OpenCbsReader r)
{
var i = new Installment
{
Number = r.GetInt("number"),
ExpectedDate = r.GetDateTime("expected_date"),
StartDate = r.GetDateTime("start_date"),
CapitalRepayment = r.GetMoney("capital_repayment"),
InterestsRepayment = r.GetMoney("interest_repayment"),
PaidInterests = r.GetMoney("paid_interest"),
PaidCapital = r.GetMoney("paid_capital"),
PaidFees = r.GetMoney("paid_fees"),
FeesUnpaid = r.GetMoney("fees_unpaid"),
PaidDate = r.GetNullDateTime("paid_date"),
Comment = r.GetString("comment"),
OLB = r.GetMoney("olb"),
IsPending = r.GetBool("pending")
};
return(i);
}
private Project GetProject(OpenCbsReader reader)
{
Project project = new Project();
project.Id = reader.GetInt("id");
project.ProjectStatus = (OProjectStatus)reader.GetSmallInt("status");
project.Code = reader.GetString("code");
project.Name = reader.GetString("name");
project.Aim = reader.GetString("aim");
project.BeginDate = reader.GetDateTime("begin_date");
project.Abilities = reader.GetString("abilities");
project.Experience = reader.GetString("experience");
project.Market = reader.GetString("market");
project.Concurrence = reader.GetString("concurrence");
project.Purpose = reader.GetString("purpose");
project.CorporateName = reader.GetString("corporate_name");
project.CorporateJuridicStatus = reader.GetString("corporate_juridicStatus");
project.CorporateFiscalStatus = reader.GetString("corporate_FiscalStatus");
project.CorporateSIRET = reader.GetString("corporate_siret");
project.CorporateRegistre = reader.GetString("corporate_registre");
project.CorporateCA = reader.GetMoney("corporate_CA");
project.CorporateNbOfJobs = reader.GetNullInt("corporate_nbOfJobs");
project.CorporateFinancialPlanType = reader.GetString("corporate_financialPlanType");
project.CorporateFinancialPlanAmount = reader.GetMoney("corporateFinancialPlanAmount");
project.CorporateFinancialPlanTotalAmount = reader.GetMoney("corporate_financialPlanTotalAmount");
project.Address = reader.GetString("address");
project.City = reader.GetString("city");
project.ZipCode = reader.GetString("zipCode");
int?districtId = reader.GetNullInt("district_id");
if (districtId.HasValue)
{
project.District = new District {
Id = districtId.Value
}
}
;
project.HomePhone = reader.GetString("home_phone");
project.PersonalPhone = reader.GetString("personalPhone");
project.Email = reader.GetString("Email");
project.HomeType = reader.GetString("hometype");
return(project);
}
private void SelectAllPublicHolidays()
{
const string sqlText = @"SELECT name, date
FROM PublicHolidays
ORDER BY date";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, connection))
using (OpenCbsReader reader = select.ExecuteReader())
{
while (reader.Read())
{
nonWorkingDateHelper.PublicHolidays.Add(reader.GetDateTime("date"),
reader.GetString("name"));
}
}
}
public List <PaymentMethod> GetPaymentMethodOfBranch()
{
string q = @"SELECT [lbpm].[payment_method_id],
[lbpm].[id],
[pm].[name],
[pm].[description],
[pm].[pending],
[lbpm].[branch_id],
[lbpm].[date],
[lbpm].[account_id]
FROM PaymentMethods pm
INNER JOIN LinkBranchesPaymentMethods lbpm ON lbpm.payment_method_id = pm.id
WHERE [lbpm].[deleted] = 0";
List <PaymentMethod> paymentMethods = new List <PaymentMethod>();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
//c.AddParam("@id", branchId);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r.Empty)
{
return(paymentMethods);
}
while (r.Read())
{
PaymentMethod paymentMethod = new PaymentMethod
{
Id = r.GetInt("payment_method_id"),
Name = r.GetString("name"),
Description = r.GetString("description"),
IsPending = r.GetBool("pending"),
LinkId = r.GetInt("id"),
Branch = _branchManager.Select(r.GetInt("branch_id")),
Date = r.GetDateTime("date"),
Account = _accountManager.Select(r.GetInt("account_id"))
};
paymentMethods.Add(paymentMethod);
}
}
}
return(paymentMethods);
}
public List <FundingLineEvent> GetEvents(FundingLine fl)
{
List <FundingLineEvent> list = new List <FundingLineEvent>();
const string q = @"SELECT id,
code,
amount,
direction,
deleted,
creation_date,
type
FROM dbo.FundingLineEvents
WITH (READUNCOMMITTED)
WHERE fundingline_id = @id
ORDER BY creation_date DESC, id DESC";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@id", fl.Id);
using (OpenCbsReader r = c.ExecuteReader())
{
if (null == r || r.Empty)
{
return(list);
}
while (r.Read())
{
FundingLineEvent e = new FundingLineEvent();
e.Id = r.GetInt("id");
e.Code = r.GetString("code");
e.Amount = r.GetMoney("amount");
e.Movement = (OBookingDirections)r.GetSmallInt("direction");
e.IsDeleted = r.GetBool("deleted");
e.CreationDate = r.GetDateTime("creation_date");
e.Type = (OFundingLineEventTypes)r.GetSmallInt("type");
e.FundingLine = fl;
list.Add(e);
}
}
}
return(list);
}
private Project GetProject(OpenCbsReader reader)
{
Project project = new Project();
project.Id = reader.GetInt("id");
project.ProjectStatus = (OProjectStatus)reader.GetSmallInt("status");
project.Code = reader.GetString("code");
project.Name = reader.GetString("name");
project.Aim = reader.GetString("aim");
project.BeginDate = reader.GetDateTime("begin_date");
project.Abilities = reader.GetString("abilities");
project.Experience = reader.GetString("experience");
project.Market = reader.GetString("market");
project.Concurrence = reader.GetString("concurrence");
project.Purpose = reader.GetString("purpose");
project.CorporateName = reader.GetString("corporate_name");
project.CorporateJuridicStatus = reader.GetString("corporate_juridicStatus");
project.CorporateFiscalStatus = reader.GetString("corporate_FiscalStatus");
project.CorporateSIRET = reader.GetString("corporate_siret");
project.CorporateRegistre = reader.GetString("corporate_registre");
project.CorporateCA = reader.GetMoney("corporate_CA");
project.CorporateNbOfJobs = reader.GetNullInt("corporate_nbOfJobs");
project.CorporateFinancialPlanType = reader.GetString("corporate_financialPlanType");
project.CorporateFinancialPlanAmount = reader.GetMoney("corporateFinancialPlanAmount");
project.CorporateFinancialPlanTotalAmount = reader.GetMoney("corporate_financialPlanTotalAmount");
project.Address = reader.GetString("address");
project.City = reader.GetString("city");
project.ZipCode = reader.GetString("zipCode");
int? districtId = reader.GetNullInt("district_id");
if (districtId.HasValue)
project.District = new District { Id = districtId.Value };
project.HomePhone = reader.GetString("home_phone");
project.PersonalPhone = reader.GetString("personalPhone");
project.Email = reader.GetString("Email");
project.HomeType = reader.GetString("hometype");
return project;
}
private static Installment GetInstallmentHistoryFromReader(OpenCbsReader r)
{
var i = new Installment
{
Number = r.GetInt("number"),
ExpectedDate = r.GetDateTime("expected_date"),
StartDate = r.GetDateTime("start_date"),
CapitalRepayment = r.GetMoney("capital_repayment"),
InterestsRepayment = r.GetMoney("interest_repayment"),
PaidInterests = r.GetMoney("paid_interest"),
PaidCapital = r.GetMoney("paid_capital"),
PaidFees = r.GetMoney("paid_fees"),
FeesUnpaid = r.GetMoney("fees_unpaid"),
PaidDate = r.GetNullDateTime("paid_date"),
Comment = r.GetString("comment"),
OLB = r.GetMoney("olb"),
IsPending = r.GetBool("pending")
};
return i;
}