public List <ABSARetailPoolingLGDSegment> GetLGDPerPDGroupData(ABSARetailPoolingLGDSegmentParam param)
{
var result = new List <ABSARetailPoolingLGDSegment>();
var sql = $"[dbo].[ABSA_LGDPERPDGROUPDATA] {param.PdRowLebelIs}, '{param.UniqueSegment}', {param.SegmentRowId}, {param.ClusterId}, " +
$"'{param.RetailPoolingFileType.ToString()}', '{param.RowId}'";
switch (param.DbContextType)
{
case DbContextType.SOURCEDATACONTEXT:
result = _dbContextSourceData.Database.SqlQuery <ABSARetailPoolingLGDSegment>(sql).ToList();
break;
case DbContextType.RFSTAGINGCONTEXT:
result = _dbContextRFStaging.Database.SqlQuery <ABSARetailPoolingLGDSegment>(sql).ToList();
break;
default:
throw new NotImplementedException();
}
return(result);
}
private bool GenerateLGDRatioInitialRank(RetailPoolingFileType productName, DbContextType dbContextType, Guid rowId)
{
var lgdPerPdGroupings = _iDbContextAccess.GetLGDPERPDGroup(productName, dbContextType, rowId);
foreach (var grouping in lgdPerPdGroupings)
{
var param = new ABSARetailPoolingLGDSegmentParam()
{
DbContextType = dbContextType, PdRowLebelIs = grouping.PDRowLabelId, UniqueSegment = grouping.UniqueSegment, SegmentRowId = grouping.SegmentRowId,
ClusterId = grouping.ClusterRowId, RetailPoolingFileType = productName, RowId = rowId
};
var lgdPerPDGroupData = _iDbContextAccess.GetLGDPerPDGroupData(param);
var numberOfTransactionsInFile = _iDbContextAccess.GetSegmentLGDCount(grouping.UniqueSegment, dbContextType);
var pdLgdTonorLookup = GetLookupData(productName, numberOfTransactionsInFile, dbContextType);
var numberOfLGPool = pdLgdTonorLookup.LGD;
var total = lgdPerPDGroupData.Sum(x => x.LGDCount);
var avgPerPool = ((double)total / (double)numberOfLGPool).NearestTenthWithRoundupAlways();
var originalAvgTransactionsInPool = ((double)total / (double)numberOfLGPool);
foreach (var item in lgdPerPDGroupData)
{
var ratio = (double)item.CumulativeSum / avgPerPool;
item.Ratio = ratio;
item.InitialRank = Math.Min((numberOfLGPool - 1), (int)ratio);
item.AveragePerPool = avgPerPool;
item.OriginalAveragePerPool = originalAvgTransactionsInPool;
}
//DB update and Clear
var update = _iDbContextAccess.UpdateLGDRatioInitialRankFinalPool(lgdPerPDGroupData, dbContextType, rowId);
lgdPerPDGroupData.Clear();
}
lgdPerPdGroupings.Clear();
return(true);
}
private bool GenerateLGDFinalPool(RetailPoolingFileType productName, DbContextType dbContextType, Guid rowId)
{
var lgdPerPdGroupings = _iDbContextAccess.GetLGDPERPDGroup(productName, dbContextType, rowId);
foreach (var grouping in lgdPerPdGroupings)
{
var param = new ABSARetailPoolingLGDSegmentParam()
{
DbContextType = dbContextType, PdRowLebelIs = grouping.PDRowLabelId, UniqueSegment = grouping.UniqueSegment, SegmentRowId = grouping.SegmentRowId,
ClusterId = grouping.ClusterRowId, RetailPoolingFileType = productName, RowId = rowId
};
var lgdPerPDGroupData = _iDbContextAccess.GetLGDPerPDGroupData(param);
var numberOfTransactionsInFile = _iDbContextAccess.GetSegmentLGDCount(grouping.UniqueSegment, dbContextType);
var pdLgdTonorLookup = GetLookupData(productName, numberOfTransactionsInFile, dbContextType);
var numberOfLGPool = pdLgdTonorLookup.LGD;
var toleranceLevel = pdLgdTonorLookup.LGDTolerance;
var total = lgdPerPDGroupData.Sum(x => x.LGDCount);
var avgPerPool = ((double)total / (double)numberOfLGPool).NearestTenthWithRoundupAlways();
foreach (var item in lgdPerPDGroupData)
{
if (item.Count == item.CumulativeSum)
{
item.FinalPool = 0;
}
else
{
var previousItem = lgdPerPDGroupData.FirstOrDefault(x => x.Id == (item.Id - 1));
if (item.UniqueLGDCountPerPDPool <= numberOfLGPool)
{
item.FinalPool = previousItem.FinalPool + 1;
}
else
{
if (previousItem.FinalPool == item.InitialRank)
{
item.FinalPool = previousItem.InitialRank;
}
else
{
var tempContents = lgdPerPDGroupData.Where(x => x.Id >= 1).Where(x => x.Id <= (item.Id - 1)).ToList();
var sumValue = tempContents.Where(x => x.FinalPool == previousItem.FinalPool).Sum(x => x.Count);
var sliceSize = item.AveragePerPool * toleranceLevel;
if (sumValue < sliceSize)
{
item.FinalPool = previousItem.FinalPool;
}
else
{
item.FinalPool = previousItem.FinalPool + 1;
}
}
}
}
}
//DB update and Clear
var update = _iDbContextAccess.UpdateLGDRatioInitialRankFinalPool(lgdPerPDGroupData, dbContextType, rowId);
lgdPerPDGroupData.Clear();
}
lgdPerPdGroupings.Clear();
return(true);
}
