private void SaveBranches(User user)
{
const string query = @"DELETE FROM dbo.UsersBranches
WHERE user_id = @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(query, conn))
{
c.AddParam("@id", user.Id);
c.ExecuteNonQuery();
if (0 == user.BranchCount)
{
return;
}
List <string> ids = new List <string>();
foreach (Branch b in user.Branches)
{
ids.Add(b.Id.ToString());
}
c.CommandText = @"INSERT INTO dbo.UsersBranches
(user_id, branch_id)
SELECT @id, number
FROM dbo.IntListToTable(@list)";
c.AddParam("@list", string.Join(",", ids.ToArray()));
c.ExecuteNonQuery();
}
}
public void UpdateInstallment(DateTime date, int id, int number)
{
// Update installement in database
const string q = @"UPDATE Installments
SET expected_date = @expectedDate
WHERE contract_id = @contractId
AND number = @number";
using (var conn = GetConnection())
using (var c = new OpenCbsCommand(q, conn))
{
//primary key = contractId + number
c.AddParam("@contractId", id);
c.AddParam("@number", number);
c.AddParam("@expectedDate", date);
c.ExecuteNonQuery();
c.ResetParams();
c.CommandText = @"UPDATE dbo.Installments
SET start_date = @start_date
WHERE contract_id = @contractId
AND number = @number";
c.AddParam("@contractId", id);
c.AddParam("@number", number + 1);
c.AddParam("@start_date", date);
c.ExecuteNonQuery();
}
}
public void RunSQL(string sqlText, SqlTransaction tran)
{
using (OpenCbsCommand sqlCommand = new OpenCbsCommand(sqlText, tran.Connection, tran))
{
sqlCommand.ExecuteNonQuery();
}
}
private void SaveCustomizableFieldValue(CustomizableFieldValue fieldValue, int groupId, bool isInsert)
{
string sqlText = @"
INSERT INTO [AdvancedFieldsValues]
([entity_field_id]
,[field_id]
,[value])
VALUES
(@entity_field_id
,@field_id
,@value)";
if (!isInsert)
{
sqlText = @"UPDATE [AdvancedFieldsValues]
SET [value] = @value
WHERE [entity_field_id] = @entity_field_id
AND [field_id] = @field_id";
}
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand insertValue = new OpenCbsCommand(sqlText, conn))
{
insertValue.AddParam("@entity_field_id", groupId);
insertValue.AddParam("@field_id", fieldValue.Field.Id);
insertValue.AddParam("@value", fieldValue.Value);
insertValue.ExecuteNonQuery();
}
}
public void RunSQL(string sqlText, SqlTransaction tran)
{
using (OpenCbsCommand sqlCommand = new OpenCbsCommand(sqlText, tran.Connection, tran))
{
sqlCommand.ExecuteNonQuery();
}
}
public void SavePicture(byte[] picture, byte[] thumbnail, int person_id, string filename, int subId)
{
int pictureId;
string q = string.Format(@"INSERT INTO [dbo].[Pictures]
([group], [id] ,[subid] ,[picture] ,[thumbnail] ,[name])
VALUES (@group ,@person_id ,@subid ,@picture ,@thumbnail ,@name)
SELECT CONVERT(int, SCOPE_IDENTITY())");
using (OpenCbsCommand c = new OpenCbsCommand(q, AttachmentsConnection))
{
c.AddParam("group", "SECOND_PICTURE");
c.AddParam("picture", picture);
c.AddParam("subid", subId);
c.AddParam("name", filename);
c.AddParam("thumbnail", thumbnail);
c.AddParam("person_id", person_id);
pictureId = int.Parse(c.ExecuteScalar().ToString());
}
q =
string.Format(
@"INSERT INTO PersonsPhotos ([person_id], [picture_id])
VALUES (@person_id, @picture_id)");
using (OpenCbsCommand c = new OpenCbsCommand(q, AttachmentsConnection))
{
c.AddParam("person_id", person_id);
c.AddParam("picture_id", pictureId);
c.ExecuteNonQuery();
}
}
/// <summary>
/// Update selected Role in database
/// </summary>
/// <param name="pRole"></param>
public void UpdateRole(Role pRole)
{
const string q = @"UPDATE [Roles]
SET [code]=@code,
[deleted]=@deleted,
[description]=@description,
[default_start_view] = @start_view
WHERE [id] = @RoleId";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@RoleId", pRole.Id);
c.AddParam("@deleted", pRole.IsDeleted);
c.AddParam("@code", pRole.RoleName);
c.AddParam("@description", pRole.Description);
c.AddParam("start_view", pRole.DefaultStartPage.ToString());
c.ExecuteNonQuery();
SaveRoleMenu(pRole);
SaveAllowedActionsForRole(pRole);
}
}
}
/// <summary>
/// Update selected Role in database
/// </summary>
/// <param name="pRole"></param>
public void UpdateRole(Role pRole)
{
const string q = @"UPDATE [Roles]
SET [code]=@code,
[deleted]=@deleted,
[description]=@description,
[role_of_loan] = @role_of_loan,
[role_of_saving] = @role_of_saving,
[role_of_teller] = @role_of_teller
WHERE [id] = @RoleId";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@RoleId", pRole.Id);
c.AddParam("@deleted", pRole.IsDeleted);
c.AddParam("@code", pRole.RoleName);
c.AddParam("@description", pRole.Description);
c.AddParam("@role_of_loan", pRole.IsRoleForLoan);
c.AddParam("@role_of_saving", pRole.IsRoleForSaving);
c.AddParam("@role_of_teller", pRole.IsRoleForTeller);
c.ExecuteNonQuery();
SaveRoleMenu(pRole);
SaveAllowedActionsForRole(pRole);
}
}
}
public void DeleteItem(MenuObject menu)
{
const string q = @"DELETE FROM dbo.AllowedRoleMenus
WHERE menu_item_id = @menu_item_id";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@menu_item_id", menu.Id);
c.ExecuteNonQuery();
}
}
const string q1 = @"DELETE FROM dbo.MenuItems
WHERE id = @id";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand c = new OpenCbsCommand(q1, conn))
{
c.AddParam("@id", menu.Id);
c.ExecuteNonQuery();
}
}
}
public void AddEconomicActivityLoanHistory(EconomicActivityLoanHistory activityLoanHistory,
SqlTransaction sqlTransaction)
{
const string sqlText = @"INSERT INTO EconomicActivityLoanHistory
([contract_id],[person_id],[group_id],[economic_activity_id],[deleted])
VALUES (@contract_id, @person_id, @group_id, @economic_activity_id, @deleted)";
using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, sqlTransaction.Connection, sqlTransaction))
{
insert.AddParam("@contract_id", activityLoanHistory.Contract.Id);
insert.AddParam("@person_id", activityLoanHistory.Person.Id);
if (activityLoanHistory.Group != null)
{
insert.AddParam("@group_id", activityLoanHistory.Group.Id);
}
else
{
insert.AddParam("@group_id", null);
}
insert.AddParam("@economic_activity_id", activityLoanHistory.EconomicActivity.Id);
insert.AddParam("@deleted", activityLoanHistory.Deleted);
insert.ExecuteNonQuery();
RefreshCache();
}
}
protected void DeleteDatasFromTable(string tableName, SqlTransaction transaction)
{
const string deleteSql = "DELETE FROM {0}";
string query = string.Format(deleteSql, tableName);
using (OpenCbsCommand command = new OpenCbsCommand(query, transaction.Connection, transaction))
command.ExecuteNonQuery();
}
public void DeleteAllPublicHolidays()
{
const string sqlText = "DELETE PublicHolidays";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, connection))
cmd.ExecuteNonQuery();
}
public void DeleteAll(SqlTransaction transaction)
{
const string q = @"DELETE FROM dbo.Tellers";
using (var c = new OpenCbsCommand(q, transaction.Connection, transaction))
{
c.ExecuteNonQuery();
}
}
public void DeleteInstallments(int pLoanId, SqlTransaction pSqlTransac)
{
const string q = @"DELETE FROM Installments WHERE contract_id = @contractId";
using (var c = new OpenCbsCommand(q, pSqlTransac.Connection, pSqlTransac))
{
c.AddParam("@contractId", pLoanId);
c.ExecuteNonQuery();
}
}
public void DeleteMFI()
{
if (SelectMFI() != null)
{
string sqlText = "DELETE FROM [MFI]";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, connection))
cmd.ExecuteNonQuery();
}
}
public void ArchiveInstallment(Installment i, int contractId, Event e, SqlTransaction transaction)
{
const string q = @"INSERT INTO dbo.InstallmentHistory
(contract_id,
event_id,
number,
expected_date,
capital_repayment,
interest_repayment,
paid_interest,
paid_capital,
paid_fees,
fees_unpaid,
paid_date,
comment,
pending,
start_date,
olb)
VALUES (
@contract_id,
@event_id,
@number,
@expected_date,
@capital_repayment,
@interest_repayment,
@paid_interest,
@paid_capital,
@paid_fees,
@fees_unpaid,
@paid_date,
@comment,
@pending,
@start_date,
@olb)";
using (OpenCbsCommand c = new OpenCbsCommand(q, transaction.Connection, transaction))
{
c.AddParam("@contract_id", contractId);
c.AddParam("@event_id", e.Id);
c.AddParam("@number", i.Number);
c.AddParam("@expected_date", i.ExpectedDate);
c.AddParam("@capital_repayment", i.CapitalRepayment.Value);
c.AddParam("@interest_repayment", i.InterestsRepayment.Value);
c.AddParam("@paid_interest", i.PaidInterests.Value);
c.AddParam("@paid_capital", i.PaidCapital.Value);
c.AddParam("@paid_fees", i.PaidFees.Value);
c.AddParam("@fees_unpaid", i.FeesUnpaid.Value);
c.AddParam("@paid_date", i.PaidDate);
c.AddParam("@comment", i.Comment);
c.AddParam("@pending", i.IsPending);
c.AddParam("@start_date", i.StartDate);
c.AddParam("@olb", i.OLB);
c.ExecuteNonQuery();
}
}
/// <summary>
/// this method allows us to update an installment
/// </summary>
/// <param name="pInstallment">the installment modified</param>
/// <param name="pContractId"></param>
/// <param name="pEventId">Event linked to this installment update</param>
/// <param name="pSqlTransac"></param>
/// <param name="pRescheduling">Is it a rescheduled installment</param>
public void UpdateInstallment(IInstallment pInstallment, int pContractId, int?pEventId, SqlTransaction pSqlTransac, bool pRescheduling)
{
// Update installement in database
const string q = @"UPDATE Installments
SET expected_date = @expectedDate,
interest_repayment = @interestRepayment,
capital_repayment = @capitalRepayment,
contract_id = @contractId,
number = @number,
paid_interest = @paidInterest,
paid_capital = @paidCapital,
fees_unpaid = @feesUnpaid,
paid_date = @paidDate,
paid_fees = @paidFees,
comment = @comment,
pending = @pending,
start_date = @start_date,
olb = @olb,
commission = @commission,
paid_commission = @paidCommission
WHERE contract_id = @contractId
AND number = @number";
using (var c = new OpenCbsCommand(q, pSqlTransac.Connection, pSqlTransac))
{
//primary key = contractId + number
c.AddParam("@contractId", pContractId);
c.AddParam("@number", pInstallment.Number);
c.AddParam("@expectedDate", pInstallment.ExpectedDate);
c.AddParam("@interestRepayment", pInstallment.InterestsRepayment.Value);
c.AddParam("@capitalRepayment", pInstallment.CapitalRepayment.Value);
c.AddParam("@paidInterest", pInstallment.PaidInterests.Value);
c.AddParam("@paidCapital", pInstallment.PaidCapital.Value);
c.AddParam("@paidDate", pInstallment.PaidDate);
c.AddParam("@paidFees", pInstallment.PaidFees.Value);
c.AddParam("@comment", pInstallment.Comment);
c.AddParam("@pending", pInstallment.IsPending);
c.AddParam("@start_date", pInstallment.StartDate);
c.AddParam("@olb", pInstallment.OLB);
c.AddParam("@commission", pInstallment.Commission);
c.AddParam("@paidCommission", pInstallment.PaidCommissions);
if (pInstallment is Installment)
{
Installment installment = (Installment)pInstallment;
c.AddParam("@feesUnpaid", installment.FeesUnpaid);
}
else
{
c.AddParam("@feesUnpaid", 0);
}
c.ExecuteNonQuery();
}
}
public void DeleteUser(User pUser)
{
const string sqlText = "UPDATE [Users] SET deleted = 1 WHERE [id] = @userId";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand sqlCommand = new OpenCbsCommand(sqlText, conn))
{
sqlCommand.AddParam("@userId", pUser.Id);
sqlCommand.ExecuteNonQuery();
}
}
public void DeletePublicHoliday(DictionaryEntry entry)
{
const string sqlText = "DELETE FROM [PublicHolidays] WHERE [date] = @date";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, connection))
{
cmd.AddParam("@date", Convert.ToDateTime(entry.Key));
cmd.ExecuteNonQuery();
}
}
public void UpdateProjectStatut(Project pProject, SqlTransaction pTransaction)
{
const string q = "UPDATE [Projects] SET [status] = @status WHERE id = @id";
using (OpenCbsCommand c = new OpenCbsCommand(q, pTransaction.Connection, pTransaction))
{
c.AddParam("@id", pProject.Id);
c.AddParam("@status", (int)pProject.ProjectStatus);
c.ExecuteNonQuery();
}
}
public void DeleteDistrictById(int districtID)
{
const string q = "UPDATE [Districts] SET [deleted]=1 WHERE id=@id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@id", districtID);
c.ExecuteNonQuery();
}
}
public void DeleteInstallmentType(InstallmentType installmentType)
{
const string q = @"DELETE FROM [InstallmentTypes] WHERE id = @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@id", installmentType.Id);
c.ExecuteNonQuery();
}
}
public void AddPublicHoliday(DictionaryEntry entry)
{
const string sqlText = "INSERT INTO [PublicHolidays]([date], [name]) VALUES(@date, @name)";
using(SqlConnection connection = GetConnection())
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, connection))
{
cmd.AddParam("@date",Convert.ToDateTime(entry.Key));
cmd.AddParam("@name", entry.Value.ToString());
cmd.ExecuteNonQuery();
}
}
public void DeleteCityById(int pCityId)
{
const string q = "UPDATE [City] SET [deleted]=1 WHERE id=@id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@id", pCityId);
c.ExecuteNonQuery();
}
}
public void DeleteInstallmentType(InstallmentType installmentType)
{
const string q = @"DELETE FROM [InstallmentTypes] WHERE id = @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@id", installmentType.Id);
c.ExecuteNonQuery();
}
}
public void DeleteCollateralProduct(int colProductId)
{
const string sqlText = @"UPDATE CollateralProducts SET deleted = 1
WHERE [id] = @id";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand command = new OpenCbsCommand(sqlText, connection))
{
command.AddParam("@id", colProductId);
command.ExecuteNonQuery();
}
}
public void AddInstallments(List <Installment> pInstallments, int pLoanId, SqlTransaction pSqlTransac)
{
const string q = @"INSERT INTO Installments(
expected_date,
interest_repayment,
capital_repayment,
contract_id,
number,
paid_interest,
paid_capital,
fees_unpaid,
paid_date,
paid_fees,
comment,
pending,
start_date,
olb,
commission,
paid_commission,
last_interest_accrual_date,
extra_amount_1,
extra_amount_2)
VALUES (
@expectedDate,
@interestsRepayment,
@capitalRepayment,
@contractId,
@number,
@paidInterests,
@paidCapital,
@feesUnpaid,
@paidDate,
@paid_fees,
@comment,
@pending,
@start_date,
@olb,
@commission,
@paidCommission,
@lastInterestAccrualDate,
@extra_amount_1,
@extra_amount_2)";
using (var c = new OpenCbsCommand(q, pSqlTransac.Connection, pSqlTransac))
{
foreach (var installment in pInstallments)
{
SetInstallment(installment, pLoanId, c);
c.ExecuteNonQuery();
c.ResetParams();
}
}
}
public void DeleteProvinceById(int pProvinceId)
{
const string q = "UPDATE [Provinces] SET [deleted]=1 WHERE id=@id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@id", pProvinceId);
RefreshCache();
c.ExecuteNonQuery();
}
}
public void UpdateSentField(bool isSent)
{
string sql = @"UPDATE [dbo].[Questionnaire]
SET [is_sent] = @isSent";
using (SqlConnection conn = GetConnection())
using (var cmd = new OpenCbsCommand(sql, conn))
{
cmd.AddParam("@isSent", isSent);
cmd.ExecuteNonQuery();
}
}
public void DeleteCollection(int fieldId)
{
string sqlListText = @"DELETE FROM [AdvancedFieldsCollections]
WHERE [field_id] = @field_id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand insertCmd = new OpenCbsCommand(sqlListText, conn))
{
insertCmd.AddParam("@field_id", fieldId);
insertCmd.ExecuteNonQuery();
}
}
public void UpdatePublicHoliday(DictionaryEntry entry)
{
const string sqlText = "UPDATE [PublicHolidays] SET [name] = @name WHERE [date]= @date";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, connection))
{
cmd.AddParam("@date", Convert.ToDateTime(entry.Key));
cmd.AddParam("@name", entry.Value.ToString());
cmd.ExecuteNonQuery();
}
}
public void AddPublicHoliday(DictionaryEntry entry)
{
const string sqlText = "INSERT INTO [PublicHolidays]([date], [name]) VALUES(@date, @name)";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, connection))
{
cmd.AddParam("@date", Convert.ToDateTime(entry.Key));
cmd.AddParam("@name", entry.Value.ToString());
cmd.ExecuteNonQuery();
}
}
public void Delete(int?id)
{
const string q = @"UPDATE dbo.Tellers SET deleted = 1
WHERE id = @id";
using (SqlConnection conn = GetConnection())
using (var c = new OpenCbsCommand(q, conn))
{
c.AddParam("@id", id);
c.ExecuteNonQuery();
}
}
public void AddCollectionItem(int fieldId, string colItem)
{
string sqlListText = @"INSERT INTO [AdvancedFieldsCollections]
([field_id], [value])
VALUES (@field_id, @col_item)";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand insertCmd = new OpenCbsCommand(sqlListText, conn))
{
insertCmd.AddParam("@field_id", fieldId);
insertCmd.AddParam("@col_item", colItem);
insertCmd.ExecuteNonQuery();
}
}
public void AddPaymentMethodToBranch(PaymentMethod paymentMethod)
{
const string q =
@"INSERT INTO LinkBranchesPaymentMethods (branch_id, payment_method_id, account_id)
VALUES (@branch_id, @payment_method_id, @account_id)";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@branch_id", paymentMethod.Branch.Id);
c.AddParam("@payment_method_id", paymentMethod.Id);
c.AddParam("@account_id", paymentMethod.Account.Id);
c.ExecuteNonQuery();
}
}
public void AddAllowedItem(int pRoleId, int pActionId, bool pAllowed)
{
const string q = @"INSERT INTO AllowedRoleActions (action_item_id, role_id, allowed)
VALUES (@actionId, @roleId, @allowed)";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@allowed", pAllowed);
c.AddParam("@actionId", pActionId);
c.AddParam("@roleId", pRoleId);
c.ExecuteNonQuery();
}
}
}
public bool CreateMFI(MFI pMFI)
{
if (SelectMFI().Login == null)
{
string sqlText = "INSERT INTO [MFI] ([name], [login], [password]) VALUES(@name,@login,@password)";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, connection))
{
cmd.AddParam("@name", pMFI.Name);
cmd.AddParam("@login", pMFI.Login);
cmd.AddParam("@password", pMFI.Password);
cmd.ExecuteNonQuery();
return true;
}
}
return false;
}
public void DeletePaymentMethodFromBranach(PaymentMethod paymentMethod)
{
const string q =
@"UPDATE LinkBranchesPaymentMethods SET deleted = 1
WHERE id = @link_id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@link_id", paymentMethod.LinkId);
c.ExecuteNonQuery();
}
}
public void UpdateCollateralProduct(int productId, string name, string description)
{
const string sqlText = @"UPDATE [CollateralProducts]
SET [name] = @name,
[desc] = @desc
WHERE id = @product_id";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand updateProduct = new OpenCbsCommand(sqlText, connection))
{
updateProduct.AddParam("@product_id", productId);
updateProduct.AddParam("@name", name);
updateProduct.AddParam("@desc", description);
updateProduct.ExecuteNonQuery();
}
}
/// <summary>
/// Update economic activity name and delete
/// </summary>
/// <param name="pEconomicActivity">EconomicActivity object</param>
public void UpdateEconomicActivity(EconomicActivity pEconomicActivity)
{
const string sqlText = "UPDATE EconomicActivities SET name = @name,deleted = @wasDeleted WHERE id = @id";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand update = new OpenCbsCommand(sqlText, connection))
{
update.AddParam("@id", pEconomicActivity.Id);
update.AddParam("@name", pEconomicActivity.Name);
update.AddParam("@wasDeleted", pEconomicActivity.Deleted);
update.ExecuteNonQuery();
}
}
public void DeleteTiers()
{
OpenCbsCommand deleteClientBranchHistory = new OpenCbsCommand("DELETE ClientBranchHistory", _connection);
deleteClientBranchHistory.ExecuteNonQuery();
OpenCbsCommand deletePersonGroupBelonging = new OpenCbsCommand("DELETE PersonGroupBelonging", _connection);
deletePersonGroupBelonging.ExecuteNonQuery();
OpenCbsCommand deletePersons = new OpenCbsCommand("DELETE Persons", _connection);
deletePersons.ExecuteNonQuery();
OpenCbsCommand deleteGroups = new OpenCbsCommand("DELETE Groups", _connection);
deleteGroups.ExecuteNonQuery();
OpenCbsCommand deleteProject = new OpenCbsCommand("DELETE Projects", _connection);
deleteProject.ExecuteNonQuery();
OpenCbsCommand deleteSavingEvents = new OpenCbsCommand("DELETE SavingEvents", _connection);
deleteSavingEvents.ExecuteNonQuery();
OpenCbsCommand deleteSavingBookContracts = new OpenCbsCommand("DELETE FROM SavingBookContracts", _connection);
deleteSavingBookContracts.ExecuteNonQuery();
OpenCbsCommand deleteSavingDepositContracts = new OpenCbsCommand("DELETE FROM SavingDepositContracts", _connection);
deleteSavingDepositContracts.ExecuteNonQuery();
OpenCbsCommand deleteSavingContracts = new OpenCbsCommand("DELETE SavingContracts", _connection);
deleteSavingContracts.ExecuteNonQuery();
OpenCbsCommand deleteTiers = new OpenCbsCommand("DELETE Tiers", _connection);
deleteTiers.ExecuteNonQuery();
}
public void DeletePackage()
{
OpenCbsCommand delete = new OpenCbsCommand("DELETE Packages", _connection);
delete.ExecuteNonQuery();
}
public void DeleteCorporatePerson()
{
OpenCbsCommand deleteCorporatePerson = new OpenCbsCommand("DELETE CorporatePersonBelonging", _connection);
deleteCorporatePerson.ExecuteNonQuery();
}
public void UpdateProject(Project pProject, SqlTransaction pSqlTransaction)
{
const string q = @"UPDATE Projects SET status = @status,name = @name,code = @code,aim = @aim,
begin_date = @beginDate, abilities = @abilities, experience = @experience, market = @market, concurrence = @concurrence,
purpose = @purpose ,[corporate_name] = @corporateName, [corporate_juridicStatus] = @corporateJuridicStatus,
[corporate_FiscalStatus] = @corporateFiscalStatus,[corporate_siret] = @corporateSiret,[corporate_CA] = @corporateCA,
[corporate_nbOfJobs] = @corporateNbOfJobs,[corporate_financialPlanType] = @corporateFinancialPlanType,
[corporateFinancialPlanAmount] = @corporateFinancialPlanAmount,[corporate_financialPlanTotalAmount] = @corporateFinancialPlanTotalAmount,
[address] = @address,[city] = @city, [zipCode] = @zipCode, [district_id] = @districtId, [home_phone] = @homePhone,
[personalPhone] = @personalPhone, [Email] = @Email, [hometype] = @hometype,
[corporate_registre] = @corporateRegistre WHERE id = @id";
using (OpenCbsCommand c = new OpenCbsCommand(q, pSqlTransaction.Connection, pSqlTransaction))
{
c.AddParam("@status", (int) pProject.ProjectStatus);
c.AddParam("@id", pProject.Id);
c.AddParam("@name", pProject.Name);
c.AddParam("@code", pProject.Code);
c.AddParam("@aim", pProject.Aim);
c.AddParam("@beginDate", pProject.BeginDate);
c.AddParam("@abilities", pProject.Abilities);
c.AddParam("@experience", pProject.Experience);
c.AddParam("@market", pProject.Market);
c.AddParam("@concurrence", pProject.Concurrence);
c.AddParam("@purpose", pProject.Purpose);
c.AddParam("@corporateName", pProject.CorporateName);
c.AddParam("@corporateJuridicStatus", pProject.CorporateJuridicStatus);
c.AddParam("@corporateFiscalStatus", pProject.CorporateFiscalStatus);
c.AddParam("@corporateRegistre", pProject.CorporateRegistre);
c.AddParam("@corporateSiret", pProject.CorporateSIRET);
c.AddParam("@corporateCA", pProject.CorporateCA);
c.AddParam("@corporateNbOfJobs", pProject.CorporateNbOfJobs);
c.AddParam("@corporateFinancialPlanType", pProject.CorporateFinancialPlanType);
c.AddParam("@corporateFinancialPlanAmount", pProject.CorporateFinancialPlanAmount);
c.AddParam("@corporateFinancialPlanTotalAmount", pProject.CorporateFinancialPlanTotalAmount);
c.AddParam("@address", pProject.Address);
c.AddParam("@city", pProject.City);
c.AddParam("@zipCode", pProject.ZipCode);
if (pProject.District != null)
c.AddParam("@districtId", pProject.District.Id);
else
c.AddParam("@districtId", null);
c.AddParam("@homePhone", pProject.HomePhone);
c.AddParam("@personalPhone", pProject.PersonalPhone);
c.AddParam("@Email", pProject.Email);
c.AddParam("@hometype", pProject.HomeType);
c.ExecuteNonQuery();
}
foreach (FollowUp up in pProject.FollowUps)
{
if (up.Id == 0)
_AddFollowUp(up, pProject.Id, pSqlTransaction);
else
_UpdateFollowUp(up, pSqlTransaction);
}
}
public void Update(Teller teller, SqlTransaction t)
{
const string sqlText = @"UPDATE dbo.Tellers
SET name = @name,
[desc] = @desc,
account_id = @account_id,
branch_id = @branch_id,
user_id = @user_id,
currency_id = @currency_id,
amount_min = @amount_min,
amount_max = @amount_max,
deposit_amount_min = @deposit_amount_min,
deposit_amount_max = @deposit_amount_max,
withdrawal_amount_min = @withdrawal_amount_min,
withdrawal_amount_max = @withdrawal_amount_max
WHERE id = @id";
using (var c = new OpenCbsCommand(sqlText, t.Connection, t))
{
c.AddParam("@id", teller.Id);
c.AddParam("@name", teller.Name);
c.AddParam("@desc", teller.Description);
if (teller.Branch != null)
c.AddParam("@branch_id", teller.Branch.Id);
else
c.AddParam("@branch_id", null);
if (teller.Account != null)
c.AddParam("@account_id", teller.Account.Id);
else
c.AddParam("@account_id", null);
if (teller.User != null)
c.AddParam("@user_id", teller.User.Id);
else
c.AddParam("@user_id", null);
if (teller.Currency != null)
c.AddParam("@currency_id", teller.Currency.Id);
else
c.AddParam("@currency_id", null);
c.AddParam("@amount_min", teller.MinAmountTeller);
c.AddParam("@amount_max", teller.MaxAmountTeller);
c.AddParam("@deposit_amount_min", teller.MinAmountDeposit);
c.AddParam("@deposit_amount_max", teller.MaxAmountDeposit);
c.AddParam("@withdrawal_amount_min", teller.MinAmountWithdrawal);
c.AddParam("@withdrawal_amount_max", teller.MaxAmountWithdrawal);
c.ExecuteNonQuery();
}
}
public void EditInstallmentType(InstallmentType installmentType)
{
const string q = @"UPDATE [InstallmentTypes] SET [name] = @name, [nb_of_days] = @days, [nb_of_months] = @months
WHERE id = @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
SetInstallmentType(c, installmentType);
c.AddParam("@id", installmentType.Id);
c.ExecuteNonQuery();
}
}
public void AddCollateralPropertyCollectionItem(int collateralPropertyId, string colItem)
{
string sqlListText = @"INSERT INTO [CollateralPropertyCollections]
([property_id]
,[value])
VALUES
(@property_id
, @col_item)";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlListText, connection))
{
cmd.AddParam("@property_id", collateralPropertyId);
cmd.AddParam("@col_item", colItem);
cmd.ExecuteNonQuery();
}
}
public void DeleteUser(User pUser)
{
const string sqlText = "UPDATE [Users] SET deleted = 1 WHERE [id] = @userId";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand sqlCommand = new OpenCbsCommand(sqlText, conn))
{
sqlCommand.AddParam("@userId", pUser.Id);
sqlCommand.ExecuteNonQuery();
}
}
private void SaveSubordinates(User user)
{
const string query = @"DELETE FROM dbo.UsersSubordinates
WHERE user_id = @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(query, conn))
{
c.AddParam("id", user.Id);
c.ExecuteNonQuery();
if (0 == user.SubordinateCount) return;
List<string> subIds = new List<string>();
foreach (User sub in user.Subordinates)
{
subIds.Add(sub.Id.ToString());
}
c.CommandText =
@"INSERT INTO dbo.UsersSubordinates
(user_id, subordinate_id)
SELECT @id, number
FROM dbo.IntListToTable(@list)";
c.AddParam("@list", string.Join(",", subIds.ToArray()));
c.ExecuteNonQuery();
}
}
public void UpdateUser(User pUser)
{
const string sqlText = @"UPDATE [Users]
SET [user_name] = @username,
[user_pass] = @userpass,
[role_code] = @roleCode,
[first_name] = @firstname,
[last_name] = @lastname,
[mail] = @mail,
[sex] = @sex,
[phone] = @phone
WHERE [id] = @userId";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand sqlCommand = new OpenCbsCommand(sqlText, conn))
{
sqlCommand.AddParam("@userId", pUser.Id);
SetUser(sqlCommand, pUser);
sqlCommand.ExecuteNonQuery();
_UpdateUsersRole(pUser.Id, pUser.UserRole.Id);
}
}
public int AddGenericCreditContractIntoDatabase()
{
DeleteCreditContract();
int tiersId = AddGenericTiersIntoDatabase("group");
OpenCbsCommand insertProject = new OpenCbsCommand(@"INSERT INTO Projects([tiers_id], [status], [name], [code], [aim], [begin_date]) VALUES
(" + tiersId + " , 2,'NOT SET','NOT SET','NOT SET','10/10/2005') SELECT SCOPE_IDENTITY()", _connection);
int projectId = int.Parse(insertProject.ExecuteScalar().ToString());
OpenCbsCommand insertContract = new OpenCbsCommand("INSERT INTO [Contracts]([contract_code], [branch_code], [creation_date], [start_date], [close_date], [closed], [project_id], [activity_id],[preferred_first_installment_date]) VALUES " +
"('KH/130','SU','10/10/2004','10/10/2005','10/10/2006',0," + projectId + ", 1,'11/10/2005') SELECT SCOPE_IDENTITY()", _connection);
int contractId = int.Parse(insertContract.ExecuteScalar().ToString());
int packageId = AddGenericPackage();
int fundingline_id = AddGenericFundingLine();
string sqlText =
string.Format(
@"INSERT INTO [Credit]
( [id]
,[package_id]
,[amount]
,[interest_rate]
,[installment_type]
,[nb_of_installment]
,[non_repayment_penalties_based_on_initial_amount]
,[non_repayment_penalties_based_on_olb]
,[non_repayment_penalties_based_on_overdue_interest]
,[non_repayment_penalties_based_on_overdue_principal]
,[anticipated_total_repayment_penalties]
,[disbursed]
,[loanofficer_id]
,[fundingLine_id]
,[grace_period]
,[written_off]
,[rescheduled]
,[bad_loan])
VALUES(@id, @packageId, 1000, 2, 1, 2, 2 ,2 ,2 ,3 ,2 ,0 ,1 ,{0} ,6 ,0 ,0 ,0)
", fundingline_id);
OpenCbsCommand insertCredit = new OpenCbsCommand(sqlText, _connection);
insertCredit.AddParam("@id", contractId);
insertCredit.AddParam("@packageId", packageId);
insertCredit.ExecuteNonQuery();
OpenCbsCommand insertInstallment = new OpenCbsCommand(@"INSERT INTO [Installments]([expected_date],[interest_repayment],[capital_repayment],[contract_id],
[number],[paid_interest],[paid_capital],[fees_unpaid])
VALUES (01/01/2007,100,200,@contractId,1,0,0,0)
INSERT INTO [Installments]([expected_date],[interest_repayment],[capital_repayment],[contract_id],
[number],[paid_interest],[paid_capital],[fees_unpaid])
VALUES (01/02/2007,100,200,@contractId,2,0,0,0)", _connection);
insertInstallment.AddParam("@contractId", contractId);
insertInstallment.ExecuteNonQuery();
return contractId;
}
public void UpdateProjectStatut(Project pProject, SqlTransaction pTransaction)
{
const string q = "UPDATE [Projects] SET [status] = @status WHERE id = @id";
using (OpenCbsCommand c = new OpenCbsCommand(q, pTransaction.Connection, pTransaction))
{
c.AddParam("@id", pProject.Id);
c.AddParam("@status", (int) pProject.ProjectStatus);
c.ExecuteNonQuery();
}
}
public void DeleteCreditContract()
{
OpenCbsCommand deleteLinkSavings = new OpenCbsCommand("DELETE FROM [LoansLinkSavingsBook]", _connection);
deleteLinkSavings.ExecuteNonQuery();
OpenCbsCommand deleteLinkGuarantor3 = new OpenCbsCommand("DELETE LoanDisbursmentEvents", _connection);
deleteLinkGuarantor3.ExecuteNonQuery();
OpenCbsCommand deleteInstallmentHistory = new OpenCbsCommand("DELETE InstallmentHistory", _connection);
deleteInstallmentHistory.ExecuteNonQuery();
OpenCbsCommand deleteLinkGuarantor2 = new OpenCbsCommand("DELETE ContractEvents", _connection);
deleteLinkGuarantor2.ExecuteNonQuery();
OpenCbsCommand deleteLinkGuarantor = new OpenCbsCommand("DELETE LinkGuarantorCredit", _connection);
deleteLinkGuarantor.ExecuteNonQuery();
OpenCbsCommand deleteCredit = new OpenCbsCommand("DELETE Credit", _connection);
deleteCredit.ExecuteNonQuery();
DeletePackage();
DeleteFundingLine();
OpenCbsCommand deleteContract = new OpenCbsCommand("DELETE Contracts", _connection);
deleteContract.ExecuteNonQuery();
DeleteTiers();
DeleteCity();
DeleteDistrict();
DeleteProvince();
}
private void _UpdateFollowUp(FollowUp pUp, SqlTransaction pTransaction)
{
const string q = @"UPDATE [FollowUp] SET [year] = @year,[CA] = @CA,[Jobs1] = @Jobs1 ,[Jobs2] = @Jobs2
,[PersonalSituation] = @PersonalSituation ,[activity] = @activity ,[comment] = @comment WHERE id = @id";
using (OpenCbsCommand c = new OpenCbsCommand(q, pTransaction.Connection, pTransaction))
{
c.AddParam("@id", pUp.Id);
c.AddParam("@year", pUp.Year);
c.AddParam("@CA", pUp.CA);
c.AddParam("@jobs1", pUp.Jobs1);
c.AddParam("@jobs2", pUp.Jobs2);
c.AddParam("@personalSituation", pUp.PersonalSituation);
c.AddParam("@activity", pUp.Activity);
c.AddParam("@comment", pUp.Comment);
c.ExecuteNonQuery();
}
}
public void DeleteSaving()
{
OpenCbsCommand deleteSVE = new OpenCbsCommand("DELETE SavingEvents", _connection);
deleteSVE.ExecuteNonQuery();
OpenCbsCommand deleteSBC = new OpenCbsCommand("DELETE FROM SavingBookContracts", _connection);
deleteSBC.ExecuteNonQuery();
OpenCbsCommand deleteSDC = new OpenCbsCommand("DELETE FROM SavingDepositContracts", _connection);
deleteSDC.ExecuteNonQuery();
OpenCbsCommand deleteSVC = new OpenCbsCommand("DELETE SavingContracts", _connection);
deleteSVC.ExecuteNonQuery();
OpenCbsCommand deleteSVP = new OpenCbsCommand("DELETE SavingBookProducts", _connection);
deleteSVP.ExecuteNonQuery();
OpenCbsCommand deleteSBP = new OpenCbsCommand("DELETE SavingProducts", _connection);
deleteSVP.ExecuteNonQuery();
}
public void DeleteAll(SqlTransaction transaction)
{
const string q = @"DELETE FROM dbo.Tellers";
using (var c = new OpenCbsCommand(q, transaction.Connection, transaction))
{
c.ExecuteNonQuery();
}
}
public void UpdateDeletedEconomicActivityLoanHistory(int contractId, int personId, int economicActivityId,
SqlTransaction sqlTransaction, bool deleted)
{
const string sqlText = @"UPDATE EconomicActivityLoanHistory
SET deleted = @deleted, economic_activity_id = @economic_activity_id
WHERE contract_id = @contract_id AND person_id = @person_id";
using (OpenCbsCommand update = new OpenCbsCommand(sqlText, sqlTransaction.Connection, sqlTransaction))
{
update.AddParam("@contract_id", contractId);
update.AddParam("@person_id", personId);
update.AddParam("@economic_activity_id", economicActivityId);
update.AddParam("@deleted", deleted);
update.ExecuteNonQuery();
}
}
public void UpdatePaymentMethodFromBranch(PaymentMethod paymentMethod)
{
const string q =
@"UPDATE LinkBranchesPaymentMethods SET account_id = @account_id, payment_method_id = @payment_method_id
WHERE id = @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@account_id", paymentMethod.Account.Id);
c.AddParam("@payment_method_id", paymentMethod.Id);
c.AddParam("@id", paymentMethod.LinkId);
c.ExecuteNonQuery();
}
}
public void AddEconomicActivityLoanHistory(EconomicActivityLoanHistory activityLoanHistory, SqlTransaction sqlTransaction)
{
const string sqlText = @"INSERT INTO EconomicActivityLoanHistory
([contract_id],[person_id],[group_id],[economic_activity_id],[deleted])
VALUES (@contract_id, @person_id, @group_id, @economic_activity_id, @deleted)";
using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, sqlTransaction.Connection, sqlTransaction))
{
insert.AddParam("@contract_id", activityLoanHistory.Contract.Id);
insert.AddParam("@person_id", activityLoanHistory.Person.Id);
if (activityLoanHistory.Group != null)
insert.AddParam("@group_id", activityLoanHistory.Group.Id);
else
insert.AddParam("@group_id", null);
insert.AddParam("@economic_activity_id", activityLoanHistory.EconomicActivity.Id);
insert.AddParam("@deleted", activityLoanHistory.Deleted);
insert.ExecuteNonQuery();
}
}
public void DeleteCollateralProduct(int colProductId)
{
const string sqlText = @"UPDATE CollateralProducts SET deleted = 1
WHERE [id] = @id";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand command = new OpenCbsCommand(sqlText, connection))
{
command.AddParam("@id", colProductId);
command.ExecuteNonQuery();
}
}
