public override IQueryable <DimAccountGroup> GetListQuery(FinancialStatementContext db, DimAccountGroupFilter filter)
{
filter.AccountGroupDesc = filter.AccountGroupDesc?.Trim();
var accountGroupNameFilterEmpty = string.IsNullOrEmpty(filter.AccountGroupDesc);
var query = db.DimAccountGroups.Where(dag => accountGroupNameFilterEmpty || dag.AccountGroupDesc != null && dag.AccountGroupDesc.Contains(filter.AccountGroupDesc));
if (filter.SortMode != null)
{
switch (filter.SortMode.SortType)
{
case DimAccountGroupSortTypes.Id:
query = filter.SortMode.Ascending ? query.OrderBy(p => p.AccountGroupID) : query.OrderByDescending(p => p.AccountGroupID);
break;
case DimAccountGroupSortTypes.AccountGroupDesc:
query = filter.SortMode.Ascending ? query.OrderBy(p => p.AccountGroupDesc) : query.OrderByDescending(p => p.AccountGroupDesc);
break;
default:
query = query.OrderByDescending(p => p.AccountGroupID);
break;
}
}
else
{
query = query.OrderByDescending(p => p.AccountGroupID);
}
return(query);
}
public async Task <IList <DimOrgStructure> > GetAllOrgStructures()
{
using (var db = new FinancialStatementContext())
{
return(await _cache.GetItem(OrgStructuresCacheKey, _cacheExpiration, async() => await db.DimOrgStructures.ToListAsync()));
}
}
private async Task <int> ExecuteStoredProcedure(string storedProcedureName, string errorMessage)
{
using (var db = new FinancialStatementContext())
{
try
{
using (SqlConnection connection = new SqlConnection(db.Database.Connection.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(storedProcedureName, connection))
{
cmd.CommandType = CommandType.StoredProcedure;
var returnParameter = cmd.Parameters.Add("@ReturnVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
connection.Open();
await cmd.ExecuteNonQueryAsync();
return((int)returnParameter.Value);
}
}
}
catch (Exception ex)
{
throw new PublicException(errorMessage, ex);
}
}
}
protected override IQueryable <AccountMappingViewModel> GetSingleModelQuery(FinancialStatementContext db, long rowId)
{
return(from am in db.AccountMappings
from dimDept in db.DimDepts.Where(p => p.DeptID == am.DeptID).DefaultIfEmpty()
from dimItem in db.DimItems.Where(p => p.ItemID == am.ItemID).DefaultIfEmpty()
from dimAccGroup in db.DimAccountGroups.Where(p => p.AccountGroupID == am.AccountGroupID).DefaultIfEmpty()
from dimAcc in db.DimAccounts.Where(p => p.AccountID == am.AccountID).DefaultIfEmpty()
from dimProduct in db.DimProducts.Where(p => p.ProductID == am.ProductID).DefaultIfEmpty()
from dimChannel in db.DimChannels.Where(p => p.ChannelID == am.ChannelID).DefaultIfEmpty()
where am.RowId == rowId
select new AccountMappingViewModel
{
AccountCode = dimAcc.AccountCode,
AccountGroupID = am.AccountGroupID,
AccountGroupDesc = dimAccGroup.AccountGroupDesc,
AccountID = am.AccountID,
ChannelCode = dimChannel.ChannelCode,
ChannelID = am.ChannelID,
CreateDate = am.CreateDate,
Dept = dimDept.Dept,
DeptID = am.DeptID,
ItemID = am.ItemID,
ItemUAN = dimItem.UAN,
PrintDigital = am.PrintDigital,
ProductCode = dimProduct.ProductCode,
ProductID = am.ProductID,
RowId = am.RowId,
SignMapping = am.SignMapping,
SignPL = am.SignPL
});
}
public async Task <IList <DimAccountGroup> > GetAllAccountGroups()
{
using (var db = new FinancialStatementContext())
{
return(await _cache.GetItem(AccountGroupsCacheKey, _cacheExpiration, async() => await db.DimAccountGroups.ToListAsync()));
}
}
protected override IList <LogParams> EditFunc(FinancialStatementContext db, DimPLKind existingItem, DimPLKindViewModel existingModel, DimPLKindViewModel model)
{
var logEntries = new List <LogParams>();
if (existingItem.PLGroupID != model.PLGroupID)
{
AddPropertyChangeLogEntry(
logEntries,
model,
"(PLGroupID) PLGroupName",
existingModel.PLGroupID,
existingModel.PLGroupName,
model.PLGroupID,
model.PLGroupName);
existingItem.PLGroupID = model.PLGroupID;
}
if (existingItem.PLKindName != model.PLKindName)
{
if (string.IsNullOrEmpty(model.PLKindName))
{
throw new PublicException("PLKindName can not be null or empty");
}
AddPropertyChangeLogEntry(logEntries, model, "PLKindName", existingItem.PLKindName, model.PLKindName);
existingItem.PLKindName = model.PLKindName;
}
return(logEntries);
}
protected override void AddMappingFunc(FinancialStatementContext db, CostCenterMappingViewModel model)
{
var newMapping = TinyMapper.Map <CostCenterMapping>(model);
newMapping.CreateDate = DateTime.Now;
db.CostCenterMappings.Add(newMapping);
}
public override IQueryable <DimPLGroup> GetListQuery(FinancialStatementContext db, DimPLGroupFilter filter)
{
filter.PLGroupName = filter.PLGroupName?.Trim();
var plGroupNameFilterEmpty = string.IsNullOrEmpty(filter.PLGroupName);
var query = db.DimPLGroups.Where(dplg => plGroupNameFilterEmpty || dplg.PLGroupName != null && dplg.PLGroupName.Contains(filter.PLGroupName));
if (filter.SortMode != null)
{
switch (filter.SortMode.SortType)
{
case DimPLGroupSortTypes.Id:
query = filter.SortMode.Ascending ? query.OrderBy(p => p.PLGroupID) : query.OrderByDescending(p => p.PLGroupID);
break;
case DimPLGroupSortTypes.PLGroupName:
query = filter.SortMode.Ascending ? query.OrderBy(p => p.PLGroupName) : query.OrderByDescending(p => p.PLGroupName);
break;
default:
query = query.OrderByDescending(p => p.PLGroupID);
break;
}
}
else
{
query = query.OrderByDescending(p => p.PLGroupID);
}
return(query);
}
public async Task Remove(long id, LogParams logParams)//TODO: transaction?
{
using (var db = new FinancialStatementContext())
{
var model = await GetSingleModel(db, id);
var existingItem = await GetSingle(db, id);
if (existingItem != null)
{
try
{
RemoveFunc(db, existingItem);
await db.SaveChangesAsync();
}
catch (Exception ex)
{
throw new PublicException(RemovingErrorText, ex);
}
logParams.ActionType = RemovingActionType;
logParams.Details = GetDetailsForLog(model);
await SaveLog(db, logParams);
}
}
}
public async Task <IList <DimChannel> > GetAllChannels()
{
using (var db = new FinancialStatementContext())
{
return(await _cache.GetItem(ChannelsCacheKey, _cacheExpiration, async() => await db.DimChannels.ToListAsync()));
}
}
public async Task <IList <TRestriction> > GetRestrictions()
{
using (var db = new FinancialStatementContext())
{
return(await GetRestrictions(db));
}
}
private async Task Edit(FinancialStatementContext db, TReference existingItem, TReference item, LogParams logParams)
{
var logEntries = new List <LogParams>();
try
{
logEntries.AddRange(EditFunc(db, existingItem, item));
await db.SaveChangesAsync();
}
catch (Exception ex)
{
if (SqlExceptionHelper.IsUniqueConstraintError(ex))
{
throw new PublicException(UniqueConstraintErrorText, ex);
}
throw new PublicException(EditingErrorText, ex);
}
foreach (var logEntry in logEntries)
{
logEntry.ActionType = EditingActionType;
logEntry.AdUsername = logParams.AdUsername;
logEntry.RemoteAddress = logParams.RemoteAddress;
await SaveLog(db, logEntry);
}
}
public override async Task <IList <TReference> > GetList(TFilter filter)
{
using (var db = new FinancialStatementContext())
{
var query = GetListQuery(db, filter);
if (filter.Skip.HasValue)
{
query = query.Skip(filter.Skip.Value);
}
if (filter.Take.HasValue)
{
query = query.Take(filter.Take.Value);
}
try
{
return(await query.ToListAsync());
}
catch (Exception ex)
{
throw new PublicException(GetListErrorText, ex);
}
}
}
public async Task <IList <PerimeterEntity> > GetAllViewPerimeters()
{
using (var db = new FinancialStatementContext())
{
return(await _cache.GetItem(ViewPerimetersCacheKey, _cacheExpiration, async() => await db.Perimeters.ToListAsync()));
}
}
public async Task <IList <DimPerimeterLaw> > GetAllPerimeterLaws()
{
using (var db = new FinancialStatementContext())
{
return(await _cache.GetItem(PerimeterLawsCacheKey, _cacheExpiration, async() => await db.DimPerimeterLaws.ToListAsync()));
}
}
protected override IList <LogParams> EditFunc(FinancialStatementContext db, DimScenario existingItem, DimScenario item)
{
var logEntries = new List <LogParams>();
if (existingItem.ScenarioCode != item.ScenarioCode)
{
AddPropertyChangeLogEntry(logEntries, item, "ScenarioCode", existingItem.ScenarioCode, item.ScenarioCode);
existingItem.ScenarioCode = item.ScenarioCode;
}
if (existingItem.ScenarioDesc != item.ScenarioDesc)
{
AddPropertyChangeLogEntry(logEntries, item, "ScenarioDesc", existingItem.ScenarioDesc, item.ScenarioDesc);
existingItem.ScenarioDesc = item.ScenarioDesc;
}
if (existingItem.MonthNum != item.MonthNum)
{
AddPropertyChangeLogEntry(logEntries, item, "MonthNum", existingItem.MonthNum + "", item.MonthNum + "");
existingItem.MonthNum = item.MonthNum;
}
if (existingItem.InputCode != item.InputCode)
{
AddPropertyChangeLogEntry(logEntries, item, "InputCode", existingItem.InputCode, item.InputCode);
existingItem.InputCode = item.InputCode;
}
return(logEntries);
}
protected override void AddFunc(FinancialStatementContext db, OrgSubordinationByDateViewModel model)
{
var newItem = TinyMapper.Map <OrgSubordinationByDate>(model);
newItem.CreateDate = DateTime.Now;
db.OrgSubordinationByDates.Add(newItem);
}
public async Task Remove(long rowId, LogParams logParams)//TODO: transaction?
{
using (var db = new FinancialStatementContext())
{
var model = await GetSingleModel(db, rowId);
var existingMapping = await GetSingle(db, rowId);
if (model != null && existingMapping != null)
{
var checkErrors = await CheckRestrictionsOnRemove(db, model);
if (checkErrors.Any())
{
throw new PublicException("Mapping remove failed due to locks: " + JoinErrors(checkErrors));
}
try
{
RemoveMappingFunc(db, existingMapping);
await db.SaveChangesAsync();
}
catch (Exception ex)
{
throw new PublicException(RemovingErrorText, ex);
}
logParams.ActionType = RemovingActionType;
logParams.Details = GetDetailsForLog(model);
await SaveLog(db, logParams);
}
}
}
protected override IList <LogParams> EditMappingFunc(FinancialStatementContext db, EntityMapping existingMapping, EntityMappingViewModel existingModel, EntityMappingViewModel model)
{
var logEntries = new List <LogParams>();
if (existingMapping.EntityID != model.EntityID)
{
AddPropertyChangeLogEntry(
logEntries,
model,
"(EntityID) Dept",
existingModel.EntityID,
existingModel.EntityCode,
model.EntityID,
model.EntityCode);
existingMapping.EntityID = model.EntityID;
}
if (existingMapping.PerimeterID != model.PerimeterID)
{
AddPropertyChangeLogEntry(
logEntries,
model,
"(PerimeterID) CostCenterDesc",
existingModel.PerimeterID,
existingModel.PerimeterCode,
model.PerimeterID,
model.PerimeterCode);
existingMapping.PerimeterID = model.PerimeterID;
}
return(logEntries);
}
protected override IList <LogParams> EditMappingFunc(FinancialStatementContext db, BrandMapping existingMapping, BrandMappingViewModel existingModel, BrandMappingViewModel model)
{
var logEntries = new List <LogParams>();
if (existingMapping.ProjectID != model.ProjectID)
{
AddPropertyChangeLogEntry(
logEntries,
model,
"(ProjectID) ProjectCode",
existingModel.ProjectID,
existingModel.ProjectCode,
model.ProjectID,
model.ProjectCode);
existingMapping.ProjectID = model.ProjectID;
}
if (existingMapping.LOBDetailID != model.LOBDetailID)
{
AddPropertyChangeLogEntry(
logEntries,
model,
"(LOBDetailID) BrandCode",
existingModel.LOBDetailID,
existingModel.LOBDetailCode,
model.LOBDetailID,
model.LOBDetailCode);
existingMapping.LOBDetailID = model.LOBDetailID;
}
return(logEntries);
}
protected override void AddFunc(FinancialStatementContext db, DimYear item)
{
if (string.IsNullOrEmpty(item.YearCode))
{
throw new PublicException("YearCode can not be null or empty");
}
db.DimYears.Add(item);
}
private async Task <IList <string> > CheckRestrictionsOnRemove(FinancialStatementContext db, TViewModel existingModel)
{
return(await CheckRestrictions(
db,
existingModel,
null,
(restriction, existingModelParam, newModelParam, errorList) => errorList.Add($"mapping with RowId = {existingModelParam.RowId} is locked by fields: {restriction.ToString()}")));
}
private async Task <IList <string> > CheckRestrictionsOnEdit(FinancialStatementContext db, TViewModel existingModel, TViewModel model)
{
return(await CheckRestrictions(
db,
existingModel,
model,
CheckRestrictionsOnEditFunc));
}
protected override void AddFunc(FinancialStatementContext db, DimItem item)
{
if (string.IsNullOrEmpty(item.UAN))
{
throw new PublicException("UAN can not be null or empty");
}
item.CreateDate = DateTime.Now;
db.DimItems.Add(item);
}
protected override void AddFunc(FinancialStatementContext db, DimPLKindViewModel model)
{
if (string.IsNullOrEmpty(model.PLKindName))
{
throw new PublicException("PLKindName can not be null or empty");
}
var newItem = TinyMapper.Map <DimPLKind>(model);
db.DimPLKinds.Add(newItem);
}
protected async Task SaveLog(FinancialStatementContext db, LogParams logParams)
{
try
{
logParams.CreatedOn = DateTime.Now;
var sqlParams = new[]
{
new SqlParameter {
ParameterName = "@createdOn", Value = logParams.CreatedOn, Direction = ParameterDirection.Input, SqlDbType = SqlDbType.DateTime, Size = 8
},
new SqlParameter {
ParameterName = "@actionType", Value = logParams.ActionType, Direction = ParameterDirection.Input, SqlDbType = SqlDbType.NVarChar, Size = 100
},
new SqlParameter {
ParameterName = "@adUsername", Value = logParams.AdUsername, Direction = ParameterDirection.Input, SqlDbType = SqlDbType.NVarChar, Size = 100
},
new SqlParameter {
ParameterName = "@remoteAddress", Value = logParams.RemoteAddress, Direction = ParameterDirection.Input, SqlDbType = SqlDbType.NVarChar, Size = 50
},
new SqlParameter {
ParameterName = "@oldData", Value = logParams.OldData, Direction = ParameterDirection.Input, SqlDbType = SqlDbType.NVarChar, Size = 1000
},
new SqlParameter {
ParameterName = "@newData", Value = logParams.NewData, Direction = ParameterDirection.Input, SqlDbType = SqlDbType.NVarChar, Size = 1000
},
new SqlParameter {
ParameterName = "@details", Value = logParams.Details, Direction = ParameterDirection.Input, SqlDbType = SqlDbType.NVarChar, Size = -1
},
};
CheckParamValues(sqlParams);
try
{
await db.Database.ExecuteSqlCommandAsync(ExecuteLogMappingProcedureQuery, sqlParams);
}
catch (Exception ex)
{
var nl = Environment.NewLine;
var message = $"CreatedOn: {logParams.CreatedOn}; " + nl +
$"ActionType: {logParams.ActionType}; " + nl +
$"AdUsername: {logParams.AdUsername}; " + nl +
$"RemoteAddress: {logParams.RemoteAddress}; " + nl +
$"OldData: {logParams.OldData}; " + nl +
$"NewData: {logParams.NewData}; " + nl +
$"Details: {logParams.Details} ";
throw new LogException($"Failed to write to log: {nl + message}", ex);
}
}
catch (Exception ex)
{
throw new PublicException("An error occured while writing to log", ex);
}
}
public override IQueryable <DimPerimeter> GetListQuery(FinancialStatementContext db, DimPerimeterFilter filter)
{
filter.PerimeterDesc = filter.PerimeterDesc?.Trim();
filter.PerimeterCode = filter.PerimeterCode?.Trim();
filter.PerimeterCurrency = filter.PerimeterCurrency?.Trim();
var createDateFromFilterEmpty = !filter.CreateDateFrom.HasValue;
var createDateToFilterEmpty = !filter.CreateDateTo.HasValue;
var perimeterDescFilterEmpty = string.IsNullOrEmpty(filter.PerimeterDesc);
var perimeterCodeFilterEmpty = string.IsNullOrEmpty(filter.PerimeterCode);
var perimeterCurrencyFilterEmpty = string.IsNullOrEmpty(filter.PerimeterCurrency);
var query = db.DimPerimeters.Where(p =>
(createDateFromFilterEmpty || p.CreateDate.HasValue && p.CreateDate.Value >= filter.CreateDateFrom.Value) &&
(createDateToFilterEmpty || p.CreateDate.HasValue && p.CreateDate.Value <= filter.CreateDateTo.Value) &&
(perimeterDescFilterEmpty || p.PerimeterDesc != null && p.PerimeterDesc.Contains(filter.PerimeterDesc)) &&
(perimeterCodeFilterEmpty || p.PerimeterCode != null && p.PerimeterCode.Contains(filter.PerimeterCode)) &&
(perimeterCurrencyFilterEmpty || p.PerimeterCurrency != null && p.PerimeterCurrency.Contains(filter.PerimeterCurrency)));
if (filter.SortMode != null)
{
switch (filter.SortMode.SortType)
{
case DimPerimeterSortTypes.PerimeterID:
query = filter.SortMode.Ascending ? query.OrderBy(p => p.PerimeterID) : query.OrderByDescending(p => p.PerimeterID);
break;
case DimPerimeterSortTypes.PerimeterDesc:
query = filter.SortMode.Ascending ? query.OrderBy(p => p.PerimeterDesc) : query.OrderByDescending(p => p.PerimeterDesc);
break;
case DimPerimeterSortTypes.PerimeterCode:
query = filter.SortMode.Ascending ? query.OrderBy(p => p.PerimeterCode) : query.OrderByDescending(p => p.PerimeterCode);
break;
case DimPerimeterSortTypes.PerimeterCurrency:
query = filter.SortMode.Ascending ? query.OrderBy(p => p.PerimeterCurrency) : query.OrderByDescending(p => p.PerimeterCurrency);
break;
case DimPerimeterSortTypes.CreateDate:
query = filter.SortMode.Ascending ? query.OrderBy(p => p.CreateDate) : query.OrderByDescending(p => p.CreateDate);
break;
default:
query = query.OrderByDescending(p => p.CreateDate);
break;
}
}
else
{
query = query.OrderByDescending(p => p.CreateDate);
}
return(query);
}
protected override IList <LogParams> EditFunc(FinancialStatementContext db, DimAccountGroup existingItem, DimAccountGroup item)
{
var logEntries = new List <LogParams>();
if (existingItem.AccountGroupDesc != item.AccountGroupDesc)
{
AddPropertyChangeLogEntry(logEntries, item, "AccountGroupDesc", existingItem.AccountGroupDesc, item.AccountGroupDesc);
existingItem.AccountGroupDesc = item.AccountGroupDesc;
}
return(logEntries);
}
protected override IList <LogParams> EditFunc(FinancialStatementContext db, DimPLGroup existingItem, DimPLGroup item)
{
var logEntries = new List <LogParams>();
if (existingItem.PLGroupName != item.PLGroupName)
{
AddPropertyChangeLogEntry(logEntries, item, "PLGroupName", existingItem.PLGroupName, item.PLGroupName);
existingItem.PLGroupName = item.PLGroupName;
}
return(logEntries);
}
protected override IList <LogParams> EditFunc(FinancialStatementContext db, DimAllOrgStructure existingItem, DimAllOrgStructure item)
{
var logEntries = new List <LogParams>();
if (existingItem.AllOrgStructure != item.AllOrgStructure)
{
AddPropertyChangeLogEntry(logEntries, item, "AllOrgStructure", existingItem.AllOrgStructure, item.AllOrgStructure);
existingItem.AllOrgStructure = item.AllOrgStructure;
}
return(logEntries);
}