public SpentCategoryModel(string pattern, string category, PaymentKind kind)
{
_entity = new SpendCategoryEntity
{
Id = Guid.NewGuid(),
Pattern = pattern,
Category = category,
Kind = (int)kind
};
}
public SpendCategory ToModel(SpendCategoryEntity spendCategoryEntity)
{
return(new SpendCategory()
{
Id = spendCategoryEntity.Id,
SpendMasterCategoryId = spendCategoryEntity.SpendMasterCategoryId,
Name = spendCategoryEntity.Name,
CatCode = spendCategoryEntity.CatCode,
Active = spendCategoryEntity.Active,
});
}
public SpendCategoryEntity Update(SpendCategoryEntity spendCategoryEntity)
{
string sql = @"UPDATE [dbo].[spend_category] SET
[spend_master_category_id] = @spendmastercategoryid
,[name] = @name
,[cat_code] = @catcode
WHERE [id] = @id";
string sqlDelRoles = @"DELETE FROM [dbo].[spend_category_roles] WHERE [spend_category_id] = @spendcategoryid";
string sqlInsRoles = @"INSERT INTO [dbo].[spend_category_roles]
([role_id]
,[spend_category_id])
VALUES
(@roleid
,@spendcategoryid)";
DynamicParameters dp = new DynamicParameters();
dp.Add("spendmastercategoryid", spendCategoryEntity.SpendMasterCategoryId, DbType.Int32, ParameterDirection.Input);
dp.Add("name", spendCategoryEntity.Name.Trim(), DbType.String, ParameterDirection.Input, 100);
dp.Add("catcode", spendCategoryEntity.CatCode, DbType.String, ParameterDirection.Input, 60);
dp.Add("id", spendCategoryEntity.Id, DbType.Int32, ParameterDirection.Input);
using (IDbConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (var tran = conn.BeginTransaction())
{
// update spend category table
int spendCateId = spendCategoryEntity.Id;
var affRows0 = conn.Execute(sql, dp, transaction: tran);
// delete all roles irrespective and create new roles if any
DynamicParameters dp2 = new DynamicParameters();
dp2.Add("spendcategoryid", spendCateId, DbType.Int32, ParameterDirection.Input);
var affRows1 = conn.Execute(sqlDelRoles, dp2, transaction: tran);
if (spendCategoryEntity.SpendCategoryRoleEntities.Any())
{
spendCategoryEntity.SpendCategoryRoleEntities.ForEach(catRole =>
{
DynamicParameters dp3 = new DynamicParameters();
dp3.Add("roleid", catRole.RoleId, DbType.Int32, ParameterDirection.Input);
dp3.Add("spendcategoryid", spendCateId, DbType.Int32, ParameterDirection.Input);
var affRows2 = conn.Execute(sqlInsRoles, dp3, transaction: tran);
});
}
tran.Commit();
}
}
return(spendCategoryEntity);
}
public SpentCategoryModel(string pattern, string category, PaymentKind kind)
{
Id = Guid.NewGuid();
_entity = new SpendCategoryEntity
{
Pattern = pattern,
Category = category,
RowKey = Id.ToString(),
PartitionKey = nameof(SpendCategoryEntity),
Kind = (int)kind
};
}
public SpendCategoryEntity Insert(SpendCategoryEntity spendCategoryEntity)
{
string sql = @"INSERT INTO [dbo].[spend_category]
([spend_master_category_id]
,[name]
,[cat_code])
VALUES
(@spendmastercategoryid
,@name
,@catcode);
SELECT CAST(SCOPE_IDENTITY() as int)";
string sqlInsRoles = @"INSERT INTO [dbo].[spend_category_roles]
([role_id]
,[spend_category_id])
VALUES
(@roleid
,@spendcategoryid)";
DynamicParameters dp = new DynamicParameters();
dp.Add("spendmastercategoryid", spendCategoryEntity.SpendMasterCategoryId, DbType.Int32, ParameterDirection.Input);
dp.Add("name", spendCategoryEntity.Name.Trim(), DbType.String, ParameterDirection.Input, 100);
dp.Add("catcode", spendCategoryEntity.CatCode, DbType.String, ParameterDirection.Input, 60);
using (IDbConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (var tran = conn.BeginTransaction())
{
var spendCateId = conn.QuerySingle <int>(sql, dp, commandType: CommandType.Text, transaction: tran);
if (spendCategoryEntity.SpendCategoryRoleEntities.Any())
{
DynamicParameters dp2 = new DynamicParameters();
spendCategoryEntity.SpendCategoryRoleEntities.ForEach(catRole =>
{
dp2.Add("roleid", catRole.RoleId, DbType.Int32, ParameterDirection.Input);
dp2.Add("spendcategoryid", spendCateId, DbType.Int32, ParameterDirection.Input);
var affRowsX = conn.Execute(sqlInsRoles, dp2, transaction: tran);
});
}
tran.Commit();
}
}
return(spendCategoryEntity);
}
public SpentCategoryModel(SpendCategoryEntity entity)
{
_entity = entity;
}
public SpentCategoryModel(SpendCategoryEntity entity)
{
_entity = entity;
Id = Guid.Parse(_entity.RowKey);
}
