/// <summary>
/// Add a new picture in the database.<br/>
/// If pName is null or empty, an automatic name will be generated.
/// </summary>
/// <param name="pGroup">Picture group</param>
/// <param name="pId">Picture Id</param>
/// <param name="pictureSubId">Picture subId</param>
/// <param name="pPicture">PNG picture binary data</param>
/// <param name="pThumbnail">PNG thumbnail picture binary data</param>
/// <param name="pName">Picture name</param>
/// <returns></returns>
public int AddPicture(string pGroup, int pId, int pictureSubId, byte[] pPicture, byte[] pThumbnail, string pName)
{
// Find the first available subid
List<int> subIds = GetPictureSubIds(pGroup, pId);
int foundPlace = subIds.Count;
for (int i = 0; i < subIds.Count; i++)
{
if (subIds[i] != i)
{
foundPlace = i;
break;
}
}
// Add row
string q =
@"INSERT INTO Pictures ([group], [id] ,[subid] ,[picture] ,[thumbnail] ,[name])
VALUES (@group ,@id ,@subid ,@picture ,@thumbnail ,@name)";
using (OpenCbsCommand c = new OpenCbsCommand(q, AttachmentsConnection))
{
c.AddParam("group", pGroup);
c.AddParam("id", pId);
c.AddParam("subid", pictureSubId);
c.AddParam("picture", pPicture);
c.AddParam("thumbnail", pThumbnail);
if (pName.Length < 50)
c.AddParam("name", pName);
else
c.AddParam("name", pName.Substring(0, 49));
c.ExecuteNonQuery();
}
return foundPlace;
}
/// <summary>
/// Method to add a package into database. We use the NullableTypes to make the correspondance between
/// nullable int, decimal and double types in database and our own objects
/// </summary>
/// <param name="colProduct">Package Object</param>
/// <returns>The id of the package which has been added</returns>
public int AddCollateralProduct(CollateralProduct colProduct)
{
string sqlText = @"INSERT INTO [CollateralProducts]
(
[name]
,[desc]
,[deleted]
)
VALUES
(
@name
,@desc
,@deleted
)
SELECT CONVERT(int, SCOPE_IDENTITY())";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, connection))
{
cmd.AddParam("@name", colProduct.Name);
cmd.AddParam("@desc", colProduct.Description);
cmd.AddParam("@deleted", colProduct.Delete);
colProduct.Id = int.Parse(cmd.ExecuteScalar().ToString());
}
foreach (CollateralProperty collateralProperty in colProduct.Properties)
AddCollateralProperty(colProduct.Id, collateralProperty);
return colProduct.Id;
}
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 int AddDistrict(string pName, int pProvinceId)
{
const string q = "INSERT INTO [Districts] ([name],[province_id],[deleted]) VALUES( @name, @province,0) SELECT SCOPE_IDENTITY()";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@name", pName);
c.AddParam("@province", pProvinceId);
c.AddParam("@deleted", false);
return int.Parse(c.ExecuteScalar().ToString());
}
}
public int AddCity(City pCity)
{
const string q = "INSERT INTO [City] ([name], [district_id],[deleted]) VALUES (@name,@district_id,0) SELECT SCOPE_IDENTITY()";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@name", pCity.Name);
c.AddParam("@district_id", pCity.DistrictId);
c.AddParam("@deleted", pCity.Deleted);
return int.Parse(c.ExecuteScalar().ToString());
}
}
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 bool CodeExists(int id, string code)
{
const string q =
@"select count(*)
from dbo.Branches
where code = @code and id <> @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@id", id);
c.AddParam("@code", code);
return Convert.ToBoolean(c.ExecuteScalar());
}
}
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 int AddUser(User pUser)
{
const string sqlText = @"INSERT INTO [Users] (
[deleted],
[role_code],
[user_name],
[user_pass],
[first_name],
[last_name],
[mail],
[sex],
[phone])
VALUES(
@deleted,
@roleCode,
@username,
@userpass,
@firstname,
@lastname,
@mail,
@sex,
@phone)
SELECT SCOPE_IDENTITY()";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand sqlCommand = new OpenCbsCommand(sqlText, conn))
{
sqlCommand.AddParam("@deleted", false);
SetUser(sqlCommand, pUser);
pUser.Id = int.Parse(sqlCommand.ExecuteScalar().ToString());
SaveUsersRole(pUser.Id, pUser.UserRole.Id);
}
return pUser.Id;
}
public Branch Add(Branch branch, SqlTransaction t)
{
const string q = @"INSERT INTO dbo.Branches
(name, code, address, description)
VALUES (@name, @code, @address, @description)
SELECT SCOPE_IDENTITY()";
using (OpenCbsCommand c = new OpenCbsCommand(q, t.Connection, t))
{
c.AddParam("@name", branch.Name);
c.AddParam("@code", branch.Code);
c.AddParam("@address", branch.Address);
c.AddParam("@description", branch.Description);
branch.Id = Convert.ToInt32(c.ExecuteScalar());
return branch;
}
}
/// <summary>
/// Add an economic activity in database
/// </summary>
/// <param name="pEconomicActivity">the economic activity object to add</param>
/// <returns>the id of the economic activity added</returns>
public int AddEconomicActivity(EconomicActivity pEconomicActivity)
{
const string sqlText = @"INSERT INTO EconomicActivities ([name] , [parent_id] , [deleted])
VALUES (@name,@parentId,@deleted) SELECT SCOPE_IDENTITY()";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, connection))
{
insert.AddParam("@name", pEconomicActivity.Name);
insert.AddParam("@deleted", pEconomicActivity.Deleted);
if (pEconomicActivity.Parent != null)
insert.AddParam("@parentId", pEconomicActivity.Parent.Id);
else
insert.AddParam("@parentId", null);
return int.Parse(insert.ExecuteScalar().ToString());
}
}
public Teller Add(Teller teller, SqlTransaction t)
{
const string sqlText =
@"INSERT INTO dbo.Tellers (name, [desc], account_id, branch_id, user_id, currency_id,
amount_min, amount_max, deposit_amount_min, deposit_amount_max, withdrawal_amount_min, withdrawal_amount_max)
VALUES (@name, @desc, @account_id, @branch_id, @user_id, @currency_id,
@amount_min, @amount_max, @deposit_amount_min, @deposit_amount_max, @withdrawal_amount_min, @withdrawal_amount_max)
SELECT SCOPE_IDENTITY()";
using (var c = new OpenCbsCommand(sqlText, t.Connection, t))
{
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);
teller.Id = Convert.ToInt32(c.ExecuteScalar());
}
return teller;
}
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;
}
private void UpdateAllowedMenuItem(int pRoleId, int pMenuId, bool pAllowed)
{
const string q = @"UPDATE [AllowedRoleMenus]
SET allowed = @allowed
WHERE [menu_item_id] = @menuId AND
role_id = @roleId";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@allowed", pAllowed);
c.AddParam("@menuId", pMenuId);
c.AddParam("@roleId", pRoleId);
c.ExecuteNonQuery();
}
}
}
public void UpdateInstallmentComment(string pComment, int pContractId, int pNumber)
{
const string q = @"UPDATE Installments
SET comment = @comment
WHERE contract_id = @contractId
AND number = @number";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
//primary key = contractId + number
c.AddParam("@contractId", pContractId);
c.AddParam("@number", pNumber);
c.AddParam("@comment", pComment);
c.ExecuteNonQuery();
}
}
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 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 List <Teller> SelectAllOfUser(int userId)
{
var tellers = new List <Teller>();
const string q = @"SELECT id
, name
, description
, account_id
, deleted
, branch_id
, user_id
, currency_id
, amount_min
, amount_max
, deposit_amount_min
, deposit_amount_max
, withdrawal_amount_min
, withdrawal_amount_max
FROM dbo.Tellers
WHERE user_id = @user_id AND deleted = 0";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@user_id", userId);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r.Empty)
{
return(tellers);
}
while (r.Read())
{
var teller = new Teller();
teller.Id = r.GetInt("id");
teller.Name = r.GetString("name");
teller.Description = r.GetString("description");
teller.Deleted = r.GetBool("deleted");
teller.Account = accountManager.Select(r.GetInt("account_id"));
teller.Branch = branchManager.Select(r.GetInt("branch_id"));
int uId = r.GetInt("user_id");
teller.User = uId == 0 ? new User {
Id = 0
} : userManager.SelectUser(uId, false);
teller.Currency = currencyManager.SelectCurrencyById(r.GetInt("currency_id"));
teller.MinAmountTeller = r.GetMoney("amount_min");
teller.MaxAmountTeller = r.GetMoney("amount_max");
teller.MinAmountDeposit = r.GetMoney("deposit_amount_min");
teller.MaxAmountDeposit = r.GetMoney("deposit_amount_max");
teller.MinAmountWithdrawal = r.GetMoney("withdrawal_amount_min");
teller.MaxAmountWithdrawal = r.GetMoney("withdrawal_amount_max");
tellers.Add(teller);
}
}
}
return(tellers);
}
private static void SetUser(OpenCbsCommand sqlCommand, User pUser)
{
sqlCommand.AddParam("@username", pUser.UserName);
sqlCommand.AddParam("@userpass", pUser.Password);
sqlCommand.AddParam("@roleCode", pUser.UserRole.ToString());
sqlCommand.AddParam("@firstname", pUser.FirstName);
sqlCommand.AddParam("@lastname", pUser.LastName);
sqlCommand.AddParam("@mail", pUser.Mail);
sqlCommand.AddParam("@sex", pUser.Sex);
sqlCommand.AddParam("@phone", pUser.Phone);
}
public byte[] GetPicture(string clientType, int clientId, bool thumbnail, int photoSubId)
{
string q;
if (thumbnail)
{
q = string.Format(
@"SELECT TOP(1) [Pictures].[thumbnail]
FROM [Pictures]
WHERE [Pictures].[group]=@client_type
AND [Pictures].[id]=@client_id
AND [Pictures].[subid]=@photo_sub_id
");
}
else
{
q =
string.Format(
@"SELECT TOP(1) Pictures.picture
FROM Pictures
WHERE [Pictures].[group]=@client_type
AND [Pictures].[id]=@client_id
AND [Pictures].[subid]=@photo_sub_id
");
}
using (OpenCbsCommand c = new OpenCbsCommand(q, AttachmentsConnection))
{
c.AddParam("client_type", clientType);
c.AddParam("client_id", clientId);
c.AddParam("photo_sub_id", photoSubId);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r.Read())
{
return(r.GetBytes(0));
}
else
{
return(null);
}
}
}
}
public void Update(Branch branch, SqlTransaction t)
{
const string q = @"UPDATE dbo.Branches
SET name = @name
, code = @code
, description = @description
, address = @address
WHERE id = @id";
using (OpenCbsCommand c = new OpenCbsCommand(q, t.Connection, t))
{
c.AddParam("@id", branch.Id);
c.AddParam("@name", branch.Name);
c.AddParam("@code", branch.Code);
c.AddParam("@description", branch.Description);
c.AddParam("@address", branch.Address);
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 bool UpdateMFI(MFI pMFI)
{
if (SelectMFI().Login != null)
{
string sqlText = @"UPDATE [MFI] SET [name]=@name, [login]=@login, [password]=@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 OCurrency GetTellerBalance(Teller teller)
{
using (SqlConnection conn = GetConnection())
{
using (var c = new OpenCbsCommand("GetTellerBalance", conn).AsStoredProcedure())
{
c.AddParam("@teller_id", teller.Id);
return(decimal.Parse(c.ExecuteScalar().ToString()));
}
}
}
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 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 bool IsDistrictUsed(int districtId)
{
const string query = "SELECT TOP 1 district_id FROM Tiers WHERE district_id = @districtId OR secondary_district_id = @districtId";
using (var connection = GetConnection())
using (var cmd = new OpenCbsCommand(query, connection))
{
cmd.AddParam("districtId", districtId);
return(cmd.ExecuteScalar() != null);
}
}
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 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 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 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 bool PorductExists(string productName)
{
const string sqlText = @"SELECT 1 FROM [CollateralProducts] WHERE [name] = @name";
using (var connection = GetConnection())
using (var cmd = new OpenCbsCommand(sqlText, connection))
{
cmd.AddParam("name", productName);
return(cmd.ExecuteScalar() != null);
}
}
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 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();
}
}
/// <summary>
/// Add an economic activity in database
/// </summary>
/// <param name="pEconomicActivity">the economic activity object to add</param>
/// <returns>the id of the economic activity added</returns>
public int AddEconomicActivity(EconomicActivity pEconomicActivity)
{
const string sqlText = @"INSERT INTO EconomicActivities ([name] , [parent_id] , [deleted])
VALUES (@name,@parentId,@deleted) SELECT SCOPE_IDENTITY()";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, connection))
{
insert.AddParam("@name", pEconomicActivity.Name);
insert.AddParam("@deleted", pEconomicActivity.Deleted);
if (pEconomicActivity.Parent != null)
{
insert.AddParam("@parentId", pEconomicActivity.Parent.Id);
}
else
{
insert.AddParam("@parentId", null);
}
return(int.Parse(insert.ExecuteScalar().ToString()));
}
}
public CollateralProduct SelectCollateralProductByPropertyId(int propertyId)
{
int productId;
const string sqlProductIdText = @"SELECT product_id
FROM [CollateralProperties]
WHERE id = @id ";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlProductIdText, connection))
{
cmd.AddParam("@id", propertyId);
using (OpenCbsReader reader = cmd.ExecuteReader())
{
if (reader.Empty)
{
return(null); // nothing is coming... (c)
}
reader.Read();
productId = reader.GetInt("product_id");
}
}
const string sqlText = @"SELECT
[name]
,[desc]
,[deleted]
FROM CollateralProducts
WHERE id = @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand selectProduct = new OpenCbsCommand(sqlText, conn))
{
selectProduct.AddParam("@id", productId);
using (OpenCbsReader reader = selectProduct.ExecuteReader())
{
if (reader.Empty)
{
return(null);
}
reader.Read();
CollateralProduct colProduct = new CollateralProduct
{
Id = productId,
Name = reader.GetString("name"),
Description = reader.GetString("desc"),
Delete = reader.GetBool("deleted")
};
return(colProduct);
}
}
}
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 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 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();
}
}
/// <summary>
/// Add Role in database
/// </summary>
/// <param name="pRole"></param>
/// <returns>Role id</returns>
public int AddRole(Role pRole)
{
const string q = @"INSERT INTO [Roles]
([deleted], [code], [description], [role_of_loan], [role_of_saving], [role_of_teller])
VALUES(@deleted, @code, @description, @role_of_loan, @role_of_saving, @role_of_teller)
SELECT SCOPE_IDENTITY()";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@deleted", false);
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);
pRole.Id = int.Parse(c.ExecuteScalar().ToString());
SaveRoleMenu(pRole);
SaveAllowedActionsForRole(pRole);
}
}
return pRole.Id;
}
/// <summary>
/// Add Role in database
/// </summary>
/// <param name="pRole"></param>
/// <returns>Role id</returns>
public int AddRole(Role pRole)
{
const string q = @"INSERT INTO [Roles]
([deleted], [code], [description], [role_of_loan], [role_of_saving], [role_of_teller])
VALUES(@deleted, @code, @description, @role_of_loan, @role_of_saving, @role_of_teller)
SELECT SCOPE_IDENTITY()";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@deleted", false);
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);
pRole.Id = int.Parse(c.ExecuteScalar().ToString());
SaveRoleMenu(pRole);
SaveAllowedActionsForRole(pRole);
}
}
return(pRole.Id);
}
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 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();
}
}
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();
}
}
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();
}
}
/// <summary>
/// Add Role in database
/// </summary>
/// <param name="pRole"></param>
/// <returns>Role id</returns>
public int AddRole(Role pRole)
{
const string q = @"INSERT INTO [Roles]
([deleted], [code], [description], [default_start_view])
VALUES(@deleted, @code, @description, @start_view)
SELECT SCOPE_IDENTITY()";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@deleted", false);
c.AddParam("@code", pRole.RoleName);
c.AddParam("@description", pRole.Description);
c.AddParam("@start_view", pRole.DefaultStartPage.ToString());
pRole.Id = int.Parse(c.ExecuteScalar().ToString());
SaveRoleMenu(pRole);
SaveAllowedActionsForRole(pRole);
}
}
return pRole.Id;
}
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 MenuObject AddNewMenu(string name)
{
MenuObject newMenu = new MenuObject();
const string q = @"INSERT INTO [MenuItems]([component_name])
VALUES (@menu)
SELECT SCOPE_IDENTITY()";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@menu", name.Trim());
newMenu.Id = int.Parse(c.ExecuteScalar().ToString());
newMenu.NotSavedInDBYet = false;
newMenu.Name = name;
}
}
return newMenu;
}
public bool CustomizableFieldsExistFor(OCustomizableFieldEntities entity)
{
string sqlText = @"SELECT COUNT(*) AS [number]
FROM dbo.AdvancedFields
WHERE [entity_id] = @entity_id ";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand selectCmd = new OpenCbsCommand(sqlText, conn))
{
selectCmd.AddParam("@entity_id",
(int) Enum.Parse(typeof (OCustomizableFieldEntities), entity.ToString()));
using (OpenCbsReader reader = selectCmd.ExecuteReader())
{
if (reader == null || reader.Empty) return false;
reader.Read();
if (reader.GetInt("number") > 0) return true;
}
}
return false;
}
private List<EconomicActivity> SelectChildren(int pParentId)
{
List<EconomicActivity> doaList = new List<EconomicActivity>();
const string sqlText = "SELECT id, name, deleted FROM EconomicActivities WHERE parent_id = @id AND deleted = 0";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand sqlCommand = new OpenCbsCommand(sqlText, connection))
{
sqlCommand.AddParam("@id", pParentId);
using (OpenCbsReader reader = sqlCommand.ExecuteReader())
{
while (reader.Read())
{
EconomicActivity domain = new EconomicActivity
{
Id = reader.GetInt("id"),
Name = reader.GetString("name"),
Deleted = reader.GetBool("deleted")
};
doaList.Add(domain);
}
}
}
for (int i = 0; i < doaList.Count; i++)
doaList[i].Childrens = SelectChildren(doaList[i].Id);
return doaList;
}
/// <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 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 bool ThisActivityAlreadyExist(string pName, int pParentId)
{
int id = 0;
const string sqlText = @"SELECT id, name, deleted FROM EconomicActivities WHERE parent_id = @id
AND name = @name AND deleted = 0";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand sqlCommand = new OpenCbsCommand(sqlText, connection))
{
sqlCommand.AddParam("@name", pName);
sqlCommand.AddParam("@id", pParentId);
using (OpenCbsReader reader = sqlCommand.ExecuteReader())
{
if (!reader.Empty)
{
reader.Read();
id = reader.GetInt("id");
}
}
}
return id != 0;
}
public EconomicActivity SelectEconomicActivity(string pName)
{
EconomicActivity doa;
const string sqlText = "SELECT id, name, deleted FROM EconomicActivities WHERE name = @name";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand selectById = new OpenCbsCommand(sqlText, connection))
{
selectById.AddParam("@name", pName);
using (OpenCbsReader reader = selectById.ExecuteReader())
{
doa = GetEconomicActivity(reader);
}
}
doa.Childrens = SelectChildren(doa.Id);
return doa;
}
/// <summary>
/// This methods allows us to find a economic activity.
/// We use recursive method to find parent
/// </summary>
/// <param name="id">the id searched</param>
/// <returns>DomainOfApplication object</returns>
public EconomicActivity SelectEconomicActivity(int pId)
{
EconomicActivity doa;
const string sqlText = @"SELECT [id], [name], [deleted]
FROM EconomicActivities
WHERE id = @id";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand selectById = new OpenCbsCommand(sqlText, connection))
{
selectById.AddParam("@id", pId);
using (OpenCbsReader reader = selectById.ExecuteReader())
{
doa = GetEconomicActivity(reader);
}
}
doa.Childrens = SelectChildren(doa.Id);
return doa;
}
public int AddGenericTiersIntoDatabase(string type)
{
int districtId = AddDistrictIntoDatabase().Id;
OpenCbsCommand command = new OpenCbsCommand("INSERT INTO Tiers (creation_date,client_type_code,active,bad_client,district_id,city,loan_cycle) VALUES ('1/1/2007','G',1,0,@districtId,'Tress',1) SELECT SCOPE_IDENTITY()", _connection);
command.AddParam("@districtId", districtId);
int tiersId = int.Parse(command.ExecuteScalar().ToString());
OpenCbsCommand insert = null;
if (type.Equals("group"))
insert = new OpenCbsCommand("INSERT INTO Groups (id,name) VALUES (" + tiersId + ",'SCG')", _connection);
else if (type.Equals("person"))
insert = new OpenCbsCommand("INSERT INTO Persons (id,first_name,sex,identification_data,last_name,household_head) VALUES (" + tiersId + ",'Nicolas','M','123KH','BARON',1)", _connection);
if (insert != null) insert.ExecuteNonQuery();
return tiersId;
}
public PaymentMethod SelectPaymentMethodById(int paymentMethodId)
{
string q = @"SELECT pm.[id]
,[name]
,[description]
,[pending]
, 0 AS account_id
FROM [dbo].[PaymentMethods] pm
WHERE pm.id = @id";
PaymentMethod pm = new PaymentMethod();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@id", paymentMethodId);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r != null && !r.Empty)
{
r.Read();
pm = GetPaymentMethodFromReader(r);
}
}
}
return pm;
}
public bool EconomicActivityLoanHistoryDeleted(int contractId, int personId, SqlTransaction sqlTransaction)
{
int id = 0;
const string sqlText = @"SELECT contract_id, person_id, group_id, economic_activity_id, deleted
FROM EconomicActivityLoanHistory
WHERE contract_id = @contract_id AND person_id = @person_id AND deleted = 1";
using (OpenCbsCommand sqlCommand = new OpenCbsCommand(sqlText, sqlTransaction.Connection, sqlTransaction))
{
sqlCommand.AddParam("@contract_id", contractId);
sqlCommand.AddParam("@person_id", personId);
using (OpenCbsReader reader = sqlCommand.ExecuteReader())
{
if (!reader.Empty)
{
reader.Read();
id = reader.GetInt("contract_id");
}
}
}
return id != 0;
}
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;
}