public void CreateStandardBooking(Booking booking)
{
const string sqlText = @"INSERT INTO StandardBookings([Name], debit_account_id, credit_account_id)
VALUES (@name, @debit_account_id, @credit_account_id)";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand insertIntoTable = new OpenCbsCommand(sqlText, conn))
{
insertIntoTable.AddParam("@name", booking.Name);
insertIntoTable.AddParam("@debit_account_id", booking.DebitAccount.Id);
insertIntoTable.AddParam("@credit_account_id", booking.CreditAccount.Id);
insertIntoTable.ExecuteNonQuery();
}
}
public int Add(Currency pCurrency, SqlTransaction t)
{
const string q = @"INSERT INTO [Currencies] ([name], [code], [is_pivot], [is_swapped],use_cents)
VALUES(@name, @code, @is_pivot, @is_swapped,@use_cents) SELECT SCOPE_IDENTITY()";
using (OpenCbsCommand c = new OpenCbsCommand(q, t.Connection, t))
{
c.AddParam("@name", pCurrency.Name);
c.AddParam("@code", pCurrency.Code);
c.AddParam("@is_pivot", pCurrency.IsPivot);
c.AddParam("@is_swapped", pCurrency.IsSwapped);
c.AddParam("@use_cents", pCurrency.UseCents);
return int.Parse(c.ExecuteScalar().ToString());
}
}
public double GetMostRecentlyRate(DateTime pDate, Currency pCurrency)
{
const string q = @"SELECT TOP 1 exchange_rate
FROM ExchangeRates
WHERE exchange_date <= @date AND currency_id = @currency
ORDER BY exchange_date DESC";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@currency",pCurrency.Id);
c.AddParam("@date", pDate);
return Convert.ToDouble(c.ExecuteScalar());
}
}
public static void AddDatabaseToAccounts(string pAccountName, string pDatabaseName, string pLogin, string pPassword, SqlConnection pSqlConnection)
{
const string sqlText = @"INSERT INTO [Accounts].[dbo].[SqlAccounts]
([account_name],[database_name],[user_name],[password],[active])
VALUES (@accountName, @databaseName, @login, @password,1)";
using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, pSqlConnection))
{
insert.AddParam("@accountName", pAccountName);
insert.AddParam("@databaseName", pDatabaseName);
insert.AddParam("@login", pLogin);
insert.AddParam("@password", pPassword);
insert.ExecuteNonQuery();
}
}
/// <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 void UpdateStandardBooking(Booking booking)
{
const string sqlText = @"UPDATE StandardBookings
SET [Name] = @name,
debit_account_id = @debit_account_id,
credit_account_id = @credit_account_id
WHERE Id = @Id";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand insertIntoTable = new OpenCbsCommand(sqlText, conn))
{
insertIntoTable.AddParam("@name", booking.Name);
insertIntoTable.AddParam("@debit_account_id", booking.DebitAccount.Id);
insertIntoTable.AddParam("@credit_account_id", booking.CreditAccount.Id);
insertIntoTable.AddParam("@Id", booking.Id);
insertIntoTable.ExecuteNonQuery();
}
}
}
private static void SetAccount(OpenCbsCommand cmd, Account pAccount)
{
cmd.AddParam("@number", pAccount.Number);
cmd.AddParam("@label", pAccount.Label);
cmd.AddParam("@debitPlus", pAccount.DebitPlus);
cmd.AddParam("@type", pAccount.Type);
cmd.AddParam("@typeCode", pAccount.TypeCode);
cmd.AddParam("@account_category_id", (int)pAccount.AccountCategory);
if (pAccount.ParentAccountId == 0 || pAccount.ParentAccountId == null)
{
cmd.AddParam("parentAccountId", null);
}
else
{
cmd.AddParam("@parentAccountId", pAccount.ParentAccountId);
}
cmd.AddParam("@lft", pAccount.Left);
cmd.AddParam("@rgt", pAccount.Right);
}
public int AddGenericFundingLine4()
{
OpenCbsCommand delete = new OpenCbsCommand(@"DELETE FROM FundingLineEvents
where fundingline_id = (SELECT id FROM FundingLines where name = 'AFD_TEST')", SqlConnection);
delete.ExecuteNonQuery();
OpenCbsCommand delete2 = new OpenCbsCommand(@"DELETE FROM FundingLines WHERE name = 'AFD_TEST'", SqlConnection);
delete2.ExecuteNonQuery();
OpenCbsCommand insert = new OpenCbsCommand(@"INSERT INTO FundingLines (name,begin_date,end_date,amount,purpose,deleted, currency_id)
VALUES ('AFD_TEST',@startDate, @endDate,1000,'TEST',0,1) SELECT SCOPE_IDENTITY()", SqlConnection);
insert.AddParam("@startDate", DateTime.Now);
insert.AddParam("@endDate", DateTime.Now);
return(Convert.ToInt32(insert.ExecuteScalar()));
}
public int AddFundingLineEvent(FundingLineEvent pFundingLineEvent, SqlTransaction pTransac)
{
const string sqlText =
@"INSERT INTO [FundingLineEvents]
([code],
[amount],
[direction],
[fundingline_id],
[deleted],
[creation_date],
[type],
user_id,
contract_event_id)
VALUES
(@code,
@amount,
@direction,
@fundingLineId,
@deleted,
@creationDate,
@type,
@user_id,
@contract_event_id
)
SELECT SCOPE_IDENTITY()";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand cmd = pTransac == null
? new OpenCbsCommand(sqlText, conn)
: new OpenCbsCommand(sqlText, pTransac.Connection, pTransac))
{
cmd.AddParam("@code", pFundingLineEvent.Code);
cmd.AddParam("@amount", pFundingLineEvent.Amount);
cmd.AddParam("@direction", (int)pFundingLineEvent.Movement);
cmd.AddParam("@fundingLineId", pFundingLineEvent.FundingLine.Id);
// pFundingLineId);
cmd.AddParam("@deleted", false);
cmd.AddParam("@creationDate", pFundingLineEvent.CreationDate);
cmd.AddParam("@type", (int)pFundingLineEvent.Type);
cmd.AddParam("@user_id", _user == null? (object)null:_user.Id);
cmd.AddParam("contract_event_id", pFundingLineEvent.AttachTo == null ?
(object)null: pFundingLineEvent.AttachTo.Id);
pFundingLineEvent.Id = Convert.ToInt32(cmd.ExecuteScalar());
return(pFundingLineEvent.Id);
}
}
public void SetBuildNumber(string buildNum)
{
string query = "UPDATE [TechnicalParameters] SET [value]=@value WHERE [name]='BuildNumber'";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(query, conn))
{
c.AddParam("@value", buildNum);
c.ExecuteNonQuery();
}
}
public int InsertAccountCategory(AccountCategory accountCategory, SqlTransaction pSqlTransac)
{
const string sqlText = @"INSERT INTO AccountsCategory (name) VALUES (@name) SELECT SCOPE_IDENTITY()";
using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, pSqlTransac.Connection, pSqlTransac))
{
insert.AddParam("@name", accountCategory.Name);
return(int.Parse(insert.ExecuteScalar().ToString()));
}
}
public void SetGuid(Guid guid)
{
string query = "INSERT INTO [TechnicalParameters] ([name], [value]) VALUES ('GUID', @value)";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(query, conn))
{
c.AddParam("@value", guid.ToString());
c.ExecuteNonQuery();
}
}
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 void DeleteSelectedParameter(string key)
{
string sqlText = "DELETE FROM GeneralParameters WHERE upper([key]) = @name";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(sqlText, conn))
{
c.AddParam("@name", key);
c.ExecuteNonQuery();
}
}
public Branch AddBranchIntoDatabase()
{
Branch branch = new Branch();
branch.Name = "Default";
OpenCbsCommand command = new OpenCbsCommand("INSERT INTO Branches (name) VALUES ('@name') SELECT SCOPE_IDENTITY()", SqlConnection);
command.AddParam("@name", branch.Name);
command.ExecuteScalar().ToString();
return(branch);
}
public void AddParameter(DictionaryEntry entry)
{
ApplicationSettings.GetInstance(_user.Md5).AddParameter(entry.Key.ToString(), entry.Value);
string sqlText = "INSERT INTO [GeneralParameters]([key], [value])" +
" VALUES(@name,@value)";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(sqlText, conn))
{
c.AddParam("@name", entry.Key.ToString());
if (entry.Value != null)
c.AddParam("@value", entry.Value.ToString());
else
c.AddParam("@value", null);
c.ExecuteNonQuery();
}
}
public void DeleteSavingsEventByLoanEventId(int loanEventId, SqlTransaction sqlTransaction)
{
const string q =
@"UPDATE [dbo].[SavingEvents] SET [deleted]=1, [is_exported]=0
WHERE loan_event_id = @loan_event_id";
using (OpenCbsCommand c = new OpenCbsCommand(q, sqlTransaction.Connection, sqlTransaction))
{
c.AddParam("@loan_event_id", loanEventId);
c.ExecuteNonQuery();
}
}
//public Currency SelectCurrencyByName(string pName)
//{
// const string q = @"SELECT id, name, code, is_pivot, is_swapped FROM [Currencies] where name = @name";
// using (SqlConnection conn = GetConnection())
// using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
// {
// c.AddParam("@name", pName);
// using (OpenCbsReader r = c.ExecuteReader())
// {
// if (r == null || r.Empty) return null;
// r.Read();
// return new Currency
// {
// Id = r.GetInt("id", reader),
// Code = r.GetString("code", reader),
// Name = r.GetString("name", reader),
// IsPivot = r.GetBool("is_pivot", reader),
// IsSwapped = r.GetBool("is_swapped", reader)
// };
// }
// }
//}
public bool IsThisCurrencyAlreadyExist(string pCode, string pName)
{
const string q = @"SELECT * FROM [Currencies] where name = @name AND code = @code";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@code", pCode);
c.AddParam("@name", pName);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r == null || r.Empty)
{
return(false);
}
return(true);
}
}
}
public void DeleteStandardBooking(int id)
{
const string sqlText = @"DELETE FROM StandardBookings WHERE Id = @Id";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand insertIntoTable = new OpenCbsCommand(sqlText, conn))
{
insertIntoTable.AddParam("@Id", id);
insertIntoTable.ExecuteNonQuery();
}
}
}
public void Delete(MessageTemplate messageTemplate)
{
const string q = @"UPDATE dbo.MessageTemplates
SET Deleted = 1 WHERE Id = @Id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@Id", messageTemplate.Id);
c.ExecuteNonQuery();
}
}
public int UpdateSelectedParameter(string pName, bool pNewValue)
{
ApplicationSettings.GetInstance(_user.Md5).UpdateParameter(pName, pNewValue);
string sql = "UPDATE GeneralParameters SET [value] = @value WHERE upper([key]) = @key";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(sql, conn))
{
if (pNewValue)
{
c.AddParam("@value", "1");
}
else
{
c.AddParam("@value", "0");
}
c.AddParam("@key", pName);
return(c.ExecuteNonQuery());
}
}
public void DeleteFiscalYear(SqlTransaction sqlTransaction, FiscalYear fiscalYear)
{
const string sqlText =
@"DELETE FROM dbo.FiscalYear
WHERE id = @id";
using (OpenCbsCommand command = new OpenCbsCommand(sqlText, sqlTransaction.Connection, sqlTransaction))
{
command.AddParam("@id", fiscalYear.Id);
command.ExecuteNonQuery();
}
}
public void DeleteStandardBooking(int id)
{
const string sqlText = @"DELETE FROM StandardBookings WHERE Id = @Id";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand insertIntoTable = new OpenCbsCommand(sqlText, conn))
{
insertIntoTable.AddParam("@Id", id);
insertIntoTable.ExecuteNonQuery();
}
}
}
public void Delete(EmailAccount emailAccount)
{
const string q = @"UPDATE dbo.EmailAccounts
SET Deleted = 1 WHERE Id = @Id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@Id", emailAccount.Id);
c.ExecuteNonQuery();
}
}
public void Delete(QueuedSMS queuedSMS)
{
const string q = @"UPDATE dbo.QueuedSMS
SET Deleted = 1 WHERE Id = @Id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@Id", queuedSMS.Id);
c.ExecuteNonQuery();
}
}
public void UpdateFiscalYear(SqlTransaction sqlTransaction, FiscalYear fiscalYear)
{
const string sqlText =
@"UPDATE FiscalYear
SET close_date = @close_date,
open_date = @open_date,
name = @name
WHERE id = @id";
using (OpenCbsCommand command = new OpenCbsCommand(sqlText, sqlTransaction.Connection, sqlTransaction))
{
if (fiscalYear.OpenDate != null)
{
command.AddParam("@open_date", ((DateTime)fiscalYear.OpenDate).Date);
}
else
{
command.AddParam("@open_date", null);
}
if (fiscalYear.CloseDate != null)
{
command.AddParam("@close_date", ((DateTime)fiscalYear.CloseDate).Date);
}
else
{
command.AddParam("@close_date", null);
}
command.AddParam("@name", fiscalYear.Name);
command.AddParam("@id", fiscalYear.Id);
command.ExecuteNonQuery();
}
}
public void DeleteAccountCategory(AccountCategory accountCategory)
{
const string sqlText = @"DELETE FROM AccountsCategory WHERE id = @id";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand delete = new OpenCbsCommand(sqlText, conn))
{
delete.AddParam("@id", accountCategory.Id);
delete.ExecuteNonQuery();
}
}
}
public District AddDistrictIntoDatabase()
{
Province province = AddProvinceIntoDatabase();
District district = new District();
district.Province = province;
district.Name = "District";
OpenCbsCommand command = new OpenCbsCommand("INSERT INTO Districts (name,province_id,deleted) VALUES ('" + district.Name + "',@provinceId,0) SELECT SCOPE_IDENTITY()", SqlConnection);
command.AddParam("@provinceId", province.Id);
district.Id = int.Parse(command.ExecuteScalar().ToString());
return(district);
}
public int Add(QueuedSMS queuedSMS)
{
const string q = @"INSERT INTO dbo.QueuedSMS
([From], [Recipient], [RecipientId], [ContractId], [Charged], [Message], CreatedOnUtc, SentOnUtc, SentTries, Deleted)
VALUES (@From, @Recipient, @RecipientId, @ContractId, @Charged, @Message, @CreatedOnUtc, @SentOnUtc, @SentTries, @Deleted)
SELECT SCOPE_IDENTITY()";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@From", queuedSMS.From);
c.AddParam("@Recipient", queuedSMS.Recipient);
c.AddParam("@RecipientId", queuedSMS.RecipientId);
c.AddParam("@ContractId", queuedSMS.ContractId);
c.AddParam("@Charged", queuedSMS.Charged);
c.AddParam("@Message", queuedSMS.Message);
c.AddParam("@CreatedOnUtc", queuedSMS.CreatedOnUtc);
c.AddParam("@SentOnUtc", queuedSMS.SentOnUtc);
c.AddParam("@SentTries", queuedSMS.SentTries);
c.AddParam("@Deleted", false);
return(int.Parse(c.ExecuteScalar().ToString()));
}
}
public int Add(EmailAccount emailAccount)
{
const string q = @"INSERT INTO dbo.EmailAccounts
(Email, DisplayName, Host, Port, Username, Password, EnableSsl, UseDefaultCredentials, IsDefaultEmailAccount, Deleted)
VALUES (@email, @displayName, @host, @port, @userName, @password, @enableSSL, @useDefaultCred, @isDefault, @deleted)
SELECT SCOPE_IDENTITY()";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@email", emailAccount.Email);
c.AddParam("@displayName", emailAccount.DisplayName);
c.AddParam("@host", emailAccount.Host);
c.AddParam("@port", emailAccount.Port);
c.AddParam("@userName", emailAccount.Username);
c.AddParam("@password", emailAccount.Password);
c.AddParam("@enableSSL", emailAccount.EnableSsl);
c.AddParam("@useDefaultCred", emailAccount.UseDefaultCredentials);
c.AddParam("@isDefault", emailAccount.IsDefaultEmailAccount);
c.AddParam("@deleted", false);
return(int.Parse(c.ExecuteScalar().ToString()));
}
}
public void InsertFiscalYear(SqlTransaction sqlTransaction, FiscalYear fiscalYear)
{
const string sqlText =
@"INSERT INTO dbo.FiscalYear
( name ,
open_date ,
close_date
)
VALUES ( @name ,
@open_date ,
@close_date
)";
using (OpenCbsCommand command = new OpenCbsCommand(sqlText, sqlTransaction.Connection, sqlTransaction))
{
command.AddParam("@name", fiscalYear.Name);
if (fiscalYear.OpenDate != null)
{
command.AddParam("@open_date", ((DateTime)fiscalYear.OpenDate).Date);
}
else
{
command.AddParam("@open_date", null);
}
if (fiscalYear.CloseDate != null)
{
command.AddParam("@close_date", ((DateTime)fiscalYear.CloseDate).Date);
}
else
{
command.AddParam("@close_date", null);
}
command.ExecuteNonQuery();
}
}
private static void SetProvisioningRate(OpenCbsCommand octCommand, ProvisioningRate pProvisionningRate)
{
octCommand.AddParam("@number", pProvisionningRate.Number);
octCommand.AddParam("@numberOfDaysMin", pProvisionningRate.NbOfDaysMin);
octCommand.AddParam("@numberOfDaysMax", pProvisionningRate.NbOfDaysMax);
octCommand.AddParam("@ProvisioningOlb", pProvisionningRate.ProvisioningValue);
octCommand.AddParam("@ProvisioningInterest", pProvisionningRate.ProvisioningInterest);
octCommand.AddParam("@ProvisioningPenalty", pProvisionningRate.ProvisioningPenalty);
}
private static void SetTrancheEvent(OpenCbsCommand cmd, TrancheEvent trancheEvent)
{
cmd.AddParam("@Id", trancheEvent.Id);
cmd.AddParam("@InterestRate", trancheEvent.InterestRate);
cmd.AddParam("@Amount", trancheEvent.Amount);
cmd.AddParam("@Maturity", trancheEvent.Maturity);
cmd.AddParam("@StartDate", trancheEvent.StartDate);
cmd.AddParam("@applied_new_interest", trancheEvent.StartDate);
}
public void DeleteAccountingRule(IAccountingRule pRule)
{
const string sqlText = @"UPDATE [AccountingRules]
SET [deleted] = 1
WHERE id = @id";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand delete = new OpenCbsCommand(sqlText, conn))
{
delete.AddParam("@id", pRule.Id);
delete.ExecuteNonQuery();
}
}
}
public void AddParameter(DictionaryEntry entry)
{
ApplicationSettings.GetInstance(_user.Md5).AddParameter(entry.Key.ToString(), entry.Value);
string sqlText = "INSERT INTO [GeneralParameters]([key], [value])" +
" VALUES(@name,@value)";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(sqlText, conn))
{
c.AddParam("@name", entry.Key.ToString());
if (entry.Value != null)
{
c.AddParam("@value", entry.Value.ToString());
}
else
{
c.AddParam("@value", null);
}
c.ExecuteNonQuery();
}
}
public int GetContractQuantityByCurrencyId(int currencyId)
{
const string q = @"
SELECT COUNT(Credit.id) AS contract_quantity
FROM [dbo].[Currencies] AS Cur
INNER JOIN Packages AS Pack ON Pack.currency_id=Cur.id
INNER JOIN Credit ON Credit.package_id=Pack.id
WHERE Cur.id=@currency_id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@currency_id", currencyId);
return (int)c.ExecuteScalar();
}
}
public int GetContractQuantityByCurrencyId(int currencyId)
{
const string q = @"
SELECT COUNT(Credit.id) AS contract_quantity
FROM [dbo].[Currencies] AS Cur
INNER JOIN Packages AS Pack ON Pack.currency_id=Cur.id
INNER JOIN Credit ON Credit.package_id=Pack.id
WHERE Cur.id=@currency_id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@currency_id", currencyId);
return((int)c.ExecuteScalar());
}
}
public void UpdateInitialData(int pSavingId, OCurrency initialAmount, OCurrency entryFees)
{
const string sqlText = @"UPDATE SavingContracts SET initial_amount = @initial_amount, entry_fees = @entry_fees WHERE id = @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand update = new OpenCbsCommand(sqlText, conn))
{
update.AddParam("@id", pSavingId);
update.AddParam("@initial_amount", initialAmount);
update.AddParam("@entry_fees", entryFees);
update.ExecuteNonQuery();
}
}
public void UpdateNextMaturityForSavingBook(int savingId, DateTime? nextMaturity)
{
const string sqlText =
@"UPDATE SavingBookContracts
SET next_maturity = @nextMaturity
WHERE id = @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, conn))
{
cmd.AddParam("@nextMaturity", nextMaturity);
cmd.AddParam("@id", savingId);
cmd.ExecuteNonQuery();
}
}
public void SelectSavingsBookContract(SavingBookContract saving)
{
const string sqlText = @"SELECT
sbc.flat_withdraw_fees,
sbc.rate_withdraw_fees,
sbc.flat_transfer_fees,
sbc.rate_transfer_fees,
sbc.flat_deposit_fees,
sbc.flat_close_fees,
sbc.flat_management_fees,
sbc.flat_overdraft_fees,
sbc.in_overdraft,
sbc.rate_agio_fees,
sbc.cheque_deposit_fees,
sbc.flat_reopen_fees,
sbc.flat_ibt_fee,
sbc.rate_ibt_fee,
sbc.use_term_deposit,
sbc.term_deposit_period,
sbc.term_deposit_period_min,
sbc.term_deposit_period_max,
sbc.transfer_account,
sbc.rollover,
t.branch_id,
sbc.next_maturity
FROM dbo.SavingBookContracts sbc
INNER JOIN dbo.SavingContracts sc on sc.id = sbc.id
INNER JOIN dbo.Tiers t on t.id = sc.tiers_id
WHERE sbc.id = @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@id", saving.Id);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (!reader.Read()) return;
GetSavingBookFromReader(saving, reader);
}
//It needs to launch a new reader, that's why next code is here
saving.TransferAccount = Select(saving.TransferAccount.Code);
}
OnSavingSelected(saving);
}
public SavingBookContract SelectSavingsByLoanId(int pLoanId, bool loadLoans)
{
SavingBookContract saving;
const string sqlText = @"SELECT
SavingContracts.*,
u2.first_name AS so_first_name,
u2.last_name AS so_last_name,
SavingProducts.product_type,
Users.id AS [user_id],
Users.deleted,
Users.user_name,
Users.user_pass,
Users.role_code,
Users.first_name,
Users.last_name
FROM SavingContracts
INNER JOIN SavingProducts ON SavingContracts.product_id = SavingProducts.id
INNER JOIN LoansLinkSavingsBook ON LoansLinkSavingsBook.savings_id = SavingContracts.id
INNER JOIN Users ON SavingContracts.[user_id] = Users.id
INNER JOIN Users AS u2 ON u2.id = SavingContracts.savings_officer_id
WHERE LoansLinkSavingsBook.loan_id = @loanId";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@loanId", pLoanId);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader.Empty) return null;
reader.Read();
saving = (SavingBookContract)GetSavingFromReader(reader);
}
}
if (saving != null)
{
saving.Product = (SavingsBookProduct)_savingProductManager.SelectSavingProduct(saving.Product.Id);
saving.Events.AddRange(_savingEventManager.SelectEvents(saving.Id, saving.Product));
if (loadLoans)
saving.Loans = SelectLoansBySavingsId(saving.Id);
}
return saving;
}
public List<Loan> SelectLoansBySavingsId(int savingsId)
{
List<Loan> listLoans = new List<Loan>();
const string sqlText = @"SELECT loan_id
FROM LoansLinkSavingsBook
WHERE savings_id = @savingsId";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@savingsId", savingsId);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (!reader.Empty)
{
while (reader.Read())
{
listLoans.Add(_loanManager.SelectLoan(reader.GetInt("loan_id"), true, false, false));
}
}
}
}
return listLoans;
}
public List<ISavingsContract> SelectSavings(int pClientId)
{
const string sqlText = @"SELECT SavingContracts.[id]
,SavingContracts.[product_id]
,SavingContracts.[user_id]
,SavingContracts.[code]
,SavingContracts.[tiers_id]
,SavingContracts.[creation_date]
,SavingContracts.[interest_rate]
,SavingContracts.[status]
,SavingContracts.[closed_date]
,SavingContracts.[savings_officer_id]
,SavingContracts.[initial_amount]
,SavingContracts.[entry_fees]
,SavingContracts.[nsg_id]
,u2.first_name AS so_first_name
,u2.last_name AS so_last_name
,SavingProducts.product_type
,Users.id AS user_id
,Users.deleted
,Users.user_name
,Users.user_pass
,Users.role_code
,Users.first_name
,Users.last_name
FROM SavingContracts
INNER JOIN SavingProducts ON SavingContracts.product_id = SavingProducts.id
INNER JOIN Users ON SavingContracts.user_id = Users.id
INNER JOIN Users AS u2 ON u2.id = SavingContracts.savings_officer_id
WHERE SavingContracts.tiers_id = @tiers_id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
List<ISavingsContract> savings = new List<ISavingsContract>();
select.AddParam("@tiers_id", pClientId);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader.Empty) return new List<ISavingsContract>();
while (reader.Read())
{
savings.Add(GetSavingFromReader(reader));
}
}
foreach (ISavingsContract saving in savings)
{
SelectSavingsBookContract((SavingBookContract)saving);
saving.Product = _savingProductManager.SelectSavingProduct(saving.Product.Id);
saving.Events.AddRange(_savingEventManager.SelectEvents(saving.Id, saving.Product));
((SavingBookContract)saving).Loans = SelectLoansBySavingsId(saving.Id);
}
return savings;
}
}
public List<ISavingsContract> SelectAll(string pSavingCode)
{
List<ISavingsContract> listSaving = new List<ISavingsContract>();
const string sqlText = @"SELECT SavingContracts.*,
u2.first_name AS so_first_name,
u2.last_name AS so_last_name,
SavingProducts.product_type,
Users.id AS user_id,
Users.deleted,
Users.user_name,
Users.user_pass,
Users.role_code,
Users.first_name,
Users.last_name
FROM SavingContracts
INNER JOIN SavingProducts ON SavingContracts.product_id = SavingProducts.id
INNER JOIN Users ON SavingContracts.user_id = Users.id
INNER JOIN Users AS u2 ON u2.id = SavingContracts.savings_officer_id
WHERE SavingContracts.Code LIKE @code";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@code", string.Format("%{0}%", pSavingCode));
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader.Empty) return new List<ISavingsContract>();
while (reader.Read())
{
ISavingsContract saving = GetSavingFromReader(reader);
listSaving.Add(saving);
}
}
}
foreach (ISavingsContract saving in listSaving)
{
SelectSavingsBookContract((SavingBookContract)saving);
saving.Product = _savingProductManager.SelectSavingProduct(saving.Product.Id);
saving.Events.AddRange(_savingEventManager.SelectEvents(saving.Id, saving.Product));
}
return listSaving;
}
public KeyValuePair<int, string>[] SelectClientSavingBookCodes(int tiersId, int? currencyId)
{
const string sqlText = @"SELECT sc.id, sc.code
FROM SavingContracts sc
INNER JOIN SavingProducts sp
ON sc.product_id = sp.id AND (@currency_id IS NULL OR sp.currency_id = @currency_id)
WHERE tiers_id = @tiersId AND closed_date IS NULL";
using (SqlConnection conn = GetConnection())
using (var command = new OpenCbsCommand(sqlText, conn))
{
command.AddParam("tiersId", tiersId);
command.AddParam("currency_id", currencyId);
var result = new List<KeyValuePair<int, string>>();
using (var reader = command.ExecuteReader())
{
while(reader.Read())
{
var savingId = reader.GetInt("id");
var savingCode = reader.GetString("code");
result.Add(
new KeyValuePair<int, string>(savingId, savingCode)
);
}
return result.ToArray();
}
}
}
public SavingBookContract Select(int pSavingId)
{
SavingBookContract saving;
const string sqlText = @"SELECT SavingContracts.*,
u2.first_name AS so_first_name,
u2.last_name AS so_last_name,
SavingProducts.product_type,
Users.id AS user_id,
Users.deleted,
Users.user_name,
Users.user_pass,
Users.role_code,
Users.first_name,
Users.last_name
FROM SavingContracts
INNER JOIN SavingProducts ON SavingContracts.product_id = SavingProducts.id
INNER JOIN Users ON SavingContracts.user_id = Users.id
INNER JOIN Users AS u2 ON u2.id = SavingContracts.savings_officer_id
WHERE SavingContracts.id = @id";
using (SqlConnection conn = GetConnection())
using(var cmd = new OpenCbsCommand(sqlText, conn))
{
cmd.AddParam("@id", pSavingId);
using (OpenCbsReader reader = cmd.ExecuteReader())
{
if (reader.Empty) return null;
reader.Read();
saving = GetSavingFromReader(reader);
}
}
SelectSavingsBookContract(saving);
saving.Product = _savingProductManager.SelectSavingProduct(saving.Product.Id);
saving.Events.AddRange(_savingEventManager.SelectEvents(saving.Id, saving.Product));
return saving;
}
public void UpdateOverdraftStatus(int savingId, bool inOverdraft)
{
const string sqlText = @"UPDATE SavingBookContracts SET in_overdraft = @inOverdraft WHERE id = @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand update = new OpenCbsCommand(sqlText, conn))
{
update.AddParam("@id", savingId);
update.AddParam("@inOverdraft", inOverdraft);
update.ExecuteNonQuery();
}
}
public void UpdateStatus(int savingId, OSavingsStatus status, DateTime? closedDate)
{
const string sqlText = @"UPDATE SavingContracts SET status = @status, closed_date = @closedDate WHERE id = @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand update = new OpenCbsCommand(sqlText, conn))
{
update.AddParam("@status", (int)status);
update.AddParam("@closedDate", closedDate);
update.AddParam("@id", savingId);
update.ExecuteNonQuery();
}
}
public static string GetObjectDropScript(string db, string name, SqlConnection conn)
{
string q = @"SELECT xtype
FROM {0}..sysobjects
WHERE name = @name";
q = string.Format(q, db);
OpenCbsCommand c = new OpenCbsCommand(q, conn);
c.AddParam("@name", name);
string xtype = c.ExecuteScalar().ToString().Trim();
string xtypeObject = string.Empty;
switch (xtype)
{
case "FN":
case "IF":
case "TF":
xtypeObject = "FUNCTION";
break;
case "P":
xtypeObject = "PROCEDURE";
break;
case "V":
xtypeObject = "VIEW";
break;
default:
Debug.Fail("Cannot be here.");
break;
}
const string retval = @"IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.{0}') AND type = N'{1}'
)
DROP {2} [dbo].[{0}]";
return string.Format(retval, name, xtype, xtypeObject);
}
public static string GetDatabaseNameForAccount(string pAccountName, SqlConnection pSqlConnection)
{
const string sqlText = @"SELECT database_name
FROM Accounts.dbo.SqlAccounts
WHERE account_name = @account";
if (pSqlConnection.State == ConnectionState.Closed)
pSqlConnection.Open();
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection))
{
select.AddParam("@account", pAccountName);
return (string)select.ExecuteScalar();
}
}
public void UpdateSavingContractCode(int savingsId, string code, SqlTransaction pSqlTransac)
{
const string sqlText = @"UPDATE SavingContracts SET code = @code WHERE id = @id";
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, pSqlTransac.Connection, pSqlTransac))
{
cmd.AddParam("@code", code);
cmd.AddParam("@id", savingsId);
cmd.ExecuteNonQuery();
}
}
public int Add(ISavingsContract savings, Client client, SqlTransaction sqlTransac)
{
const string sqlText = @"INSERT INTO [SavingContracts]
(
[product_id],
[user_id],
[code],
[status],
[tiers_id],
[creation_date],
[interest_rate],
[closed_date],
[savings_officer_id],
[initial_amount],
[entry_fees],
[nsg_id]
)
VALUES
(
@product_id,
@user_id,
@code,
@status,
@tiers_id,
@creation_date,
@interest_rate,
@closedDate,
@savings_officer_id,
@initial_amount,
@entry_fees,
@nsg_id
)
SELECT CONVERT(int, SCOPE_IDENTITY())";
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac))
{
cmd.AddParam("@product_id", savings.Product.Id);
cmd.AddParam("@user_id", savings.User.Id);
cmd.AddParam("@code", savings.Code);
cmd.AddParam("@status", (int)savings.Status);
cmd.AddParam("@tiers_id", client.Id);
cmd.AddParam("@creation_date", savings.CreationDate);
cmd.AddParam("@interest_rate", savings.InterestRate);
cmd.AddParam("@closedDate", savings.ClosedDate);
cmd.AddParam("@savings_officer_id", savings.SavingsOfficer.Id);
cmd.AddParam("@initial_amount", savings.InitialAmount);
cmd.AddParam("@entry_fees", savings.EntryFees);
cmd.AddParam("@nsg_id", savings.NsgID);
savings.Id = int.Parse(cmd.ExecuteScalar().ToString());
}
AddSavingsBookContract((SavingBookContract)savings, sqlTransac);
return savings.Id;
}
private static void SetProvisioningRate(OpenCbsCommand octCommand, ProvisioningRate pProvisionningRate)
{
octCommand.AddParam("@number", pProvisionningRate.Number);
octCommand.AddParam("@numberOfDaysMin", pProvisionningRate.NbOfDaysMin);
octCommand.AddParam("@numberOfDaysMax", pProvisionningRate.NbOfDaysMax);
octCommand.AddParam("@ProvisioningOlb", pProvisionningRate.ProvisioningValue);
octCommand.AddParam("@ProvisioningInterest", pProvisionningRate.ProvisioningInterest);
octCommand.AddParam("@ProvisioningPenalty", pProvisionningRate.ProvisioningPenalty);
}
public void AddSavingsBookContract(SavingBookContract pSaving, SqlTransaction pSqlTransac)
{
const string sqlText = @"INSERT INTO [SavingBookContracts]
(
[id],
[flat_withdraw_fees],
[rate_withdraw_fees],
[flat_transfer_fees],
[rate_transfer_fees],
[flat_deposit_fees],
[flat_close_fees],
[flat_management_fees],
[flat_overdraft_fees],
[in_overdraft],
[rate_agio_fees],
[cheque_deposit_fees],
[flat_reopen_fees],
[flat_ibt_fee],
[rate_ibt_fee],
[use_term_deposit],
[term_deposit_period],
[term_deposit_period_min],
[term_deposit_period_max],
[transfer_account],
[rollover],
[next_maturity]
)
VALUES
(
@id,
@flatWithdrawFees,
@rateWithdrawFees,
@flatTransferFees,
@rateTransferFees,
@flatDepositFees,
@flatCloseFees,
@flatManagementFees,
@flatOverdraftFees,
@inOverdraft,
@rateAgioFees,
@chequeDepositFees,
@flatReopenFees,
@flat_ibt_fee,
@rate_ibt_fee,
@use_term_deposit,
@term_deposit_period,
@term_deposit_period_min,
@term_deposit_period_max,
@transfer_account,
@rollover,
@next_maturity
)";
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, pSqlTransac.Connection, pSqlTransac))
{
cmd.AddParam("@id", pSaving.Id);
cmd.AddParam("@flatWithdrawFees", pSaving.Product.WithdrawFeesType == OSavingsFeesType.Flat ? pSaving.FlatWithdrawFees : null);
cmd.AddParam("@rateWithdrawFees", pSaving.Product.WithdrawFeesType == OSavingsFeesType.Rate ? pSaving.RateWithdrawFees : null);
cmd.AddParam("@flatTransferFees", pSaving.Product.TransferFeesType == OSavingsFeesType.Flat ? pSaving.FlatTransferFees : null);
cmd.AddParam("@rateTransferFees", pSaving.Product.TransferFeesType == OSavingsFeesType.Rate ? pSaving.RateTransferFees : null);
OCurrency flat = null;
double? rate = null;
if (pSaving.Product.InterBranchTransferFee.IsFlat)
{
flat = pSaving.FlatInterBranchTransferFee;
}
else
{
rate = pSaving.RateInterBranchTransferFee;
}
cmd.AddParam("@flat_ibt_fee", flat);
cmd.AddParam("@rate_ibt_fee", rate);
cmd.AddParam("@flatDepositFees", pSaving.DepositFees);
cmd.AddParam("@flatCloseFees", pSaving.CloseFees);
cmd.AddParam("@flatManagementFees", pSaving.ManagementFees);
cmd.AddParam("@flatOverdraftFees", pSaving.OverdraftFees);
cmd.AddParam("@inOverdraft", pSaving.InOverdraft);
cmd.AddParam("@rateAgioFees", pSaving.AgioFees);
cmd.AddParam("@chequeDepositFees", pSaving.ChequeDepositFees);
cmd.AddParam("@flatReopenFees", pSaving.ReopenFees);
cmd.AddParam("@use_term_deposit", pSaving.UseTermDeposit);
cmd.AddParam("@term_deposit_period", pSaving.NumberOfPeriods);
// if (pSaving.UseTermDeposit)
// {
cmd.AddParam("@term_deposit_period_min", pSaving.TermDepositPeriodMin);
cmd.AddParam("@term_deposit_period_max", pSaving.TermDepositPeriodMax);
cmd.AddParam("@transfer_account",
pSaving.TransferAccount != null ? pSaving.TransferAccount.Code : null);
cmd.AddParam("@rollover", (int)pSaving.Rollover);
// }
// else
// {
// cmd.AddParam("@term_deposit_period_min", null);
// cmd.AddParam("@term_deposit_period_max", null);
// cmd.AddParam("@transfer_account", null);
// cmd.AddParam("@rollover", null);
// }
cmd.AddParam("@next_maturity", pSaving.NextMaturity);
cmd.ExecuteNonQuery();
}
}
public static string GetObjectCreateScript(string db, string name, SqlConnection conn)
{
string q = string.Format("{0}..sp_helptext", db);
OpenCbsCommand cmd = new OpenCbsCommand(q, conn).AsStoredProcedure();
cmd.AddParam("@objname", name);
var buffer = new StringBuilder(2048);
using (OpenCbsReader reader = cmd.ExecuteReader())
{
if (null == reader) return string.Empty;
while (reader.Read())
{
buffer.Append(reader.GetString("Text"));
}
}
return buffer.ToString();
}
public int GetNumberOfSavings(int clientId)
{
const string sqlText = @"SELECT COUNT(id) FROM SavingContracts WHERE SavingContracts.tiers_id = @tiers_id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@tiers_id", clientId);
return (int)select.ExecuteScalar();
}
}
public static int UpdateAccountActive(string pAccountName, bool pActive, SqlConnection pSqlConnection)
{
const string sqlText = @"UPDATE [Accounts].[dbo].[SqlAccounts]
SET active = @active
WHERE [account_name] = @accountName";
using (OpenCbsCommand update = new OpenCbsCommand(sqlText, pSqlConnection))
{
update.AddParam("@active", pActive);
update.AddParam("@accountName", pAccountName);
return update.ExecuteNonQuery();
}
}
public int GetNumberSavingContract(string pQuery, bool all, bool activeContractsOnly)
{
string sql =@"SELECT TOP 100 percent
SavingContracts.id,
SavingContracts.code AS contract_code,
SavingContracts.creation_date as start_date,
Persons.identification_data as identification_data,
Tiers.client_type_code,
ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS user_name,
ISNULL(Persons.first_name + SPACE(1) + Persons.last_name, ISNULL(Groups.name, Corporates.name)) AS client_name,
ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS loanofficer_name
FROM SavingContracts
INNER JOIN Users ON SavingContracts.user_id = Users.id
INNER JOIN Tiers ON SavingContracts.tiers_id = Tiers.id
LEFT OUTER JOIN Persons ON Tiers.id = Persons.id
LEFT OUTER JOIN Groups ON Tiers.id = Groups.id
LEFT OUTER JOIN Corporates ON Tiers.id = Corporates.id";
sql += " WHERE 1=1 ";
if (activeContractsOnly)
sql += " AND SavingContracts.[status]=1 ";
if (!all)
{
sql += @" AND Tiers.branch_id in
(
select
branch_id
from
dbo.UsersBranches
WHERE user_id = @user_id
)";
}
sql += @") maTable";
const string closeWhere = @" WHERE (contract_code LIKE @contractCode
OR client_name LIKE @clientName
OR user_name LIKE @userName
OR identification_data LIKE @numberPassport
OR loanofficer_name LIKE @loanofficerName )) maTable";
QueryEntity q = new QueryEntity(pQuery, sql, closeWhere);
string sqlText = q.ConstructSQLEntityNumberProxy();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
foreach (var item in q.DynamiqParameters())
{
select.AddParam(item.Key, string.Format("%{0}%", item.Value));
}
select.AddParam("@user_id", User.CurrentUser.Id);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader.Empty) return 0;
reader.Read();
return reader.GetInt(0);
}
}
}
public static int ChangeAdminPasswordForAccount(string pAccountName, string pPassword, SqlConnection pSqlConnection)
{
const string sqlText = @"DECLARE @dbName NVARCHAR(50),
@query NVARCHAR(200)
SELECT @dbName = database_name
FROM Accounts.dbo.SqlAccounts
WHERE account_name = @account
SET @query = 'UPDATE ' + @dbName + '.dbo.Users SET user_pass = ''' + @password + ''' WHERE [user_name] = ''admin'''
EXEC (@query)";
if (pSqlConnection.State == ConnectionState.Closed)
pSqlConnection.Open();
using (OpenCbsCommand update = new OpenCbsCommand(sqlText, pSqlConnection))
{
update.AddParam("@account", pAccountName);
update.AddParam("@password", pPassword);
return update.ExecuteNonQuery();
}
}
public List<SavingSearchResult> SearchSavingContractByCritere(int pPageNumber, string pQuery, bool all, bool activeContractOnly)
{
List<SavingSearchResult> list = new List<SavingSearchResult>();
string sql = @"SELECT
SavingContracts.id,
SavingContracts.code AS contract_code,
SavingContracts.status AS contract_status,
SavingContracts.creation_date as start_date,
SavingContracts.closed_date as end_date,
SavingProducts.product_type as product_type,
Persons.identification_data as identification_data,
Tiers.client_type_code,
Tiers.id AS client_id,
ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS user_name,
ISNULL(Persons.first_name + SPACE(1) + Persons.last_name, ISNULL(Groups.name, Corporates.name)) AS client_name,
ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS loanofficer_name,
SavingContracts.user_id AS loan_officer_id,
SavingProducts.currency_id
FROM SavingContracts
INNER JOIN SavingProducts ON SavingContracts.product_id = SavingProducts.id
INNER JOIN Users ON SavingContracts.user_id = Users.id
INNER JOIN Tiers ON SavingContracts.tiers_id = Tiers.id
LEFT OUTER JOIN Persons ON Tiers.id = Persons.id
LEFT OUTER JOIN Groups ON Tiers.id = Groups.id
LEFT OUTER JOIN Corporates ON Tiers.id = Corporates.id";
sql += " WHERE 1=1 ";
if (activeContractOnly)
sql += " AND SavingContracts.status=1 ";
if (!all)
{
sql += @" AND Tiers.branch_id in (
select branch_id
from dbo.UsersBranches
WHERE user_id = @user_id
)";
}
sql += ") maTable";
const string closeWhere = @" WHERE ( contract_code LIKE @contractCode
OR client_name LIKE @clientName
OR user_name LIKE @UserName
OR identification_data LIKE @numberPassport
OR loanofficer_name LIKE @loanofficerName )) maTable";
QueryEntity q = new QueryEntity(pQuery, sql, closeWhere);
string pSqlText = q.ConstructSQLEntityByCriteresProxy(20, (pPageNumber - 1) * 20);
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(pSqlText, conn))
{
foreach (var item in q.DynamiqParameters())
{
select.AddParam(item.Key, string.Format("%{0}%", item.Value));
}
select.AddParam("@user_id", User.CurrentUser.Id);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (!reader.Empty)
{
while (reader.Read())
{
SavingSearchResult result =
new SavingSearchResult
{
Id = reader.GetInt("id"),
ClientId = reader.GetInt("client_id"),
ContractCode = reader.GetString("contract_code"),
Status = (OSavingsStatus)reader.GetSmallInt("contract_status"),
ClientTypeCode = reader.GetString("client_type_code")
};
switch (result.ClientTypeCode)
{
case "I": result.ClientType = OClientTypes.Person; break;
case "G": result.ClientType = OClientTypes.Group; break;
case "V": result.ClientType = OClientTypes.Village; break;
case "C": result.ClientType = OClientTypes.Corporate; break;
default: result.ClientType = OClientTypes.Person; break;
}
result.ClientName = reader.GetString("client_name");
result.ContractStartDate = reader.GetDateTime("start_date");
result.ContractEndDate = reader.GetNullDateTime("end_date");
result.ContractType = reader.GetString("product_type");
result.LoanOfficer = new User { Id = reader.GetInt("loan_officer_id") };
result.CurrencyId = reader.GetInt("currency_id");
list.Add(result);
}
}
}
return list;
}
}