private bool GeneratePDRatioInitialRank(RetailPoolingFileType productName, DbContextType dbContextType, Guid rowId)
{
var clusterSegments = GetClusterSegments(productName, dbContextType, rowId);
foreach (var segment in clusterSegments)
{
var segmentContents = GetSegmentContents(dbContextType, segment.ClusterRowId, segment.SegmentRowId, segment.Segment, productName, rowId);
var numberOfTransactionsInFile = segmentContents.Sum(x => x.CountOfPd);
var pdLgdTonorLookup = GetLookupData(productName, numberOfTransactionsInFile, dbContextType);
var numberOfPDPool = pdLgdTonorLookup.PD;
var avgNoOfTransactionInPool = ((double)numberOfTransactionsInFile / (double)numberOfPDPool).NearestTenthWithRoundupAlways();
var originalAvgTransactionsInPool = ((double)numberOfTransactionsInFile / (double)numberOfPDPool);
foreach (var PDSegment in segmentContents)
{
var ratio = (double)PDSegment.CumulativeSum / avgNoOfTransactionInPool;
PDSegment.Ratio = ratio;
PDSegment.InitialRank = Math.Min((numberOfPDPool - 1), (int)ratio);
PDSegment.AveragePerPool = avgNoOfTransactionInPool;
PDSegment.OriginalAveragePerPool = originalAvgTransactionsInPool;
}
//DB update and Clear
var update = _iDbContextAccess.UpdateDRatioInitialRankFinalPool(segmentContents, dbContextType, rowId);
segmentContents.Clear();
}
clusterSegments.Clear();
return(true);
}
private bool GenerateTMRatioInitialRank(RetailPoolingFileType productName, DbContextType dbContextType, Guid rowId)
{
var tmPerPdLgdGroupings = _iDbContextAccess.GetTMPERPDLGDGroup(productName, dbContextType, rowId);
foreach (var grouping in tmPerPdLgdGroupings)
{
var param = new ABSARetailPolingTMSegmentParam()
{
DbContextType = dbContextType, ClusterId = grouping.ClusterRowId, LgdRowLabelId = grouping.LGDRowLabelId, PdRowLebelId = grouping.PDRowLabelId,
RetailPoolingFileType = productName, UniqueSegmentPDLGD = grouping.UniqueSegmentPDLGD, RowId = rowId, SegmentRowId = grouping.SegmentRowId
};
var tmPerPDLGDGroupData = _iDbContextAccess.GetTMPerPDLGDGroupData(param);
var numberOfTransactionsInFile = _iDbContextAccess.GetSegmentTMCount(grouping.UniqueSegmentPDLGD, dbContextType);
var pdLgdTonorLookup = GetLookupData(productName, numberOfTransactionsInFile, dbContextType);
var numberOfTMPool = pdLgdTonorLookup.Tenor;
var total = tmPerPDLGDGroupData.Sum(x => x.TMCount);
var avgPerPool = ((double)total / (double)numberOfTMPool).NearestTenthWithRoundupAlways();
var originalAvgPerPool = ((double)total / (double)numberOfTMPool);
foreach (var item in tmPerPDLGDGroupData)
{
var ratio = (double)item.CumulativeSum / avgPerPool;
item.Ratio = ratio;
item.InitialRank = Math.Min((numberOfTMPool - 1), (int)ratio);
item.AveragePerPool = avgPerPool;
item.OriginalAveragePerPool = originalAvgPerPool;
}
//DB update and Clear
var update = _iDbContextAccess.UpdateTMRatioInitialRankFinalPool(tmPerPDLGDGroupData, dbContextType, rowId);
tmPerPDLGDGroupData.Clear();
}
tmPerPdLgdGroupings.Clear();
return(true);
}
private bool GenerateTMFinalPool(RetailPoolingFileType productName, DbContextType dbContextType, Guid rowId)
{
var tmPerPdLgdGroupings = _iDbContextAccess.GetTMPERPDLGDGroup(productName, dbContextType, rowId);
foreach (var grouping in tmPerPdLgdGroupings)
{
var param = new ABSARetailPolingTMSegmentParam()
{
DbContextType = dbContextType, ClusterId = grouping.ClusterRowId, LgdRowLabelId = grouping.LGDRowLabelId, PdRowLebelId = grouping.PDRowLabelId,
RetailPoolingFileType = productName, UniqueSegmentPDLGD = grouping.UniqueSegmentPDLGD, RowId = rowId, SegmentRowId = grouping.SegmentRowId
};
var tmPerPDLGDGroupData = _iDbContextAccess.GetTMPerPDLGDGroupData(param);
var numberOfTransactionsInFile = _iDbContextAccess.GetSegmentTMCount(grouping.UniqueSegmentPDLGD, dbContextType);
var pdLgdTonorLookup = GetLookupData(productName, numberOfTransactionsInFile, dbContextType);
var numberOfLGPool = pdLgdTonorLookup.Tenor;
var toleranceLevel = pdLgdTonorLookup.TenorTolerance;
var total = tmPerPDLGDGroupData.Sum(x => x.TMCount);
var avgPerPool = ((double)total / (double)numberOfLGPool).NearestTenthWithRoundupAlways();
foreach (var item in tmPerPDLGDGroupData)
{
if (item.TMCount == item.CumulativeSum)
{
item.FinalPool = 0;
}
else
{
var previousItem = tmPerPDLGDGroupData.FirstOrDefault(x => x.Id == (item.Id - 1));
if (previousItem.FinalPool == item.InitialRank)
{
item.FinalPool = previousItem.InitialRank;
}
else
{
var tempContents = tmPerPDLGDGroupData.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.TMCount);
var sliceSize = item.AveragePerPool * toleranceLevel;
if (sumValue < sliceSize)
{
item.FinalPool = previousItem.FinalPool;
}
else
{
item.FinalPool = previousItem.FinalPool + 1;
}
tempContents.Clear();
}
}
}
//DB update and Clear
var update = _iDbContextAccess.UpdateTMRatioInitialRankFinalPool(tmPerPDLGDGroupData, dbContextType, rowId);
tmPerPDLGDGroupData.Clear();
}
tmPerPdLgdGroupings.Clear();
return(true);
}
private Guid CreateRetailPoolingProgress(DbContextType dbContextType, RetailPoolingFileType productName)
{
return(_iDbContextAccess.CreateRetailProcessingProgress(dbContextType, new ABSARetailProcessingProgress()
{
ProductName = productName.ToString(),
DateCreated = DateTime.Now
}));
}
private bool GeneratePDFinalPool(RetailPoolingFileType productName, DbContextType dbContextType, Guid rowId)
{
var clusterSegments = GetClusterSegments(productName, dbContextType, rowId);
foreach (var segment in clusterSegments)
{
var segmentContents = GetSegmentContents(dbContextType, segment.ClusterRowId, segment.SegmentRowId, segment.Segment, productName, rowId);
var numberOfTransactionsInFile = segmentContents.Sum(x => x.CountOfPd);
var pdLgdTonorLookup = GetLookupData(productName, numberOfTransactionsInFile, dbContextType);
var numberOfPDPool = pdLgdTonorLookup.PD;
var constTolerance = pdLgdTonorLookup.PDTolerance;
var avgNoOfTransactionInPool = ((double)numberOfTransactionsInFile / (double)numberOfPDPool).NearestTenthWithRoundupAlways();
foreach (var item in segmentContents)
{
if (item.Count == item.CumulativeSum)
{
item.FinalPool = 0;
}
else
{
var previousItem = segmentContents.FirstOrDefault(x => x.Id == (item.Id - 1));
if (previousItem.InitialRank == item.InitialRank)
{
item.FinalPool = previousItem.InitialRank;
}
else
{
if (previousItem.InitialRank != previousItem.FinalPool)
{
item.FinalPool = item.InitialRank;
}
else
{
var tempContents = segmentContents.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 = avgNoOfTransactionInPool * constTolerance;
if (sumValue < sliceSize)
{
item.FinalPool = previousItem.FinalPool;
}
else
{
item.FinalPool = previousItem.FinalPool + 1;
}
tempContents.Clear();
}
}
}
}
//DB update and Clear
var update = _iDbContextAccess.UpdateDRatioInitialRankFinalPool(segmentContents, dbContextType, rowId);
segmentContents.Clear();
}
clusterSegments.Clear();
return(true);
}
//PD
public bool TMRank(RetailPoolingFileType productName, DbContextType dbContextType, Guid rowId)
{
var updateProgressStart = _iDbContextAccess.CreateRetailProcessingProgressDetails(dbContextType, rowId, "TM Processing", ProgressStatus.Started);
var initialTMLogic = _iDbContextAccess.InitialTMLogic(productName, dbContextType, rowId);
var generateTMRatioInitialRank = GenerateTMRatioInitialRank(productName, dbContextType, rowId);
var generateTMFinalPool = GenerateTMFinalPool(productName, dbContextType, rowId);
var updateInputFileFinalTMRank = _iDbContextAccess.UpdateInputFileFinalTMRank(productName, dbContextType, rowId);
var updateProgressEnd = _iDbContextAccess.CreateRetailProcessingProgressDetails(dbContextType, rowId, "TM Processing", ProgressStatus.Completed);
return(updateInputFileFinalTMRank);
}
public bool ProcessSingleProductRetailPoolingRank(RetailPoolingFileType productType, DbContextType dbContextType)
{
var retailPoolingConfigs = _iDbContextAccess.GetRetailPoolingConfigs(dbContextType);
var result = false;
var inputFilesbaseDirectory = retailPoolingConfigs.FirstOrDefault(x => string.Compare(x.Type, ABSARetailPoolingEnumConfigType.SharedBaseDirectory.ToString(), true) == 0).Value;
var fileContentDelimeter = retailPoolingConfigs.FirstOrDefault(x => string.Compare(x.Type, ABSARetailPoolingEnumConfigType.InputFileContentDelimeter.ToString(), true) == 0).Value;
//var expectedTotalFile = retailPoolingConfigs.FirstOrDefault(x => string.Compare(x.Type, ABSARetailPoolingEnumConfigType.TotalRequiredFiles.ToString(), true) == 0).Value;
var outputFilesBaseDirectory = retailPoolingConfigs.FirstOrDefault(x => string.Compare(x.Type, ABSARetailPoolingEnumConfigType.OutputBaseDirectory.ToString(), true) == 0).Value;
var outputFolderName = string.Format("Output_{0}_{1}", DateTime.Now.ToString("yyyy_MM_dd_hh_mm_ss"), DateTime.Now.Ticks.ToString("x"));
var fileName = string.Format("_{0}_", productType.ToString());
var baseDirectoryLastFolder = fileManager.GetLastFolder(inputFilesbaseDirectory);
var filePath = fileManager.SearchFiles(baseDirectoryLastFolder).FirstOrDefault(x => x.ToLower().Contains(fileName.ToLower()));
if (!string.IsNullOrEmpty(filePath))
{
var inputParam = new RetailInputFileParam()
{
ContentDelimeter = fileContentDelimeter,
ProductName = productType,
DbContextType = dbContextType,
ContentLine = fileContentDelimeter,
FilePath = filePath
};
var rowId = ReadRetailPoolingFiles(inputParam);
var pd = PDRank(productType, dbContextType, rowId);
var lgd = LGDRank(productType, dbContextType, rowId);
var tm = TMRank(productType, dbContextType, rowId);
var fileContent = _iDbContextAccess.GetFileContents(productType, dbContextType, rowId);
var outputFileName = string.Format("{0}_RANKING_PROCESSING_{1}_{2}", productType.ToString().ToUpper(), DateTime.Now.ToString("yyyyMMddmmss"), DateTime.Now.Ticks.ToString("x"));
result = fileManager.CreatePoolingOutputFiles($"{outputFileName}_Input", outputFilesBaseDirectory, fileContent, outputFolderName);
var pdContents = _iDbContextAccess.GetFileContentsPD(dbContextType, rowId);
result = fileManager.CreatePoolingOutputFilePD($"{outputFileName}_PDLOGIC", outputFilesBaseDirectory, pdContents, outputFolderName);
var lgdContents = _iDbContextAccess.GetFileContentsLGD(dbContextType, rowId);
result = fileManager.CreatePoolingOutputFileLGD($"{outputFileName}_LGDLOGIC", outputFilesBaseDirectory, lgdContents, outputFolderName);
var tmContents = _iDbContextAccess.GetFileContentsTM(dbContextType, rowId);
result = fileManager.CreatePoolingOutputFileTM($"{outputFileName}_TMLOGIC", outputFilesBaseDirectory, tmContents, outputFolderName);
var processingDone = _iDbContextAccess.UpdateRetailProcessingProgress(dbContextType, rowId);
var updateProgressStart = _iDbContextAccess.CreateRetailProcessingProgressDetails(dbContextType, rowId, "Clearing Temp Data", ProgressStatus.Started);
var cleanTable = _iDbContextAccess.TruncateRetailPoolingInputTable(dbContextType);
var updateProgressEnd = _iDbContextAccess.CreateRetailProcessingProgressDetails(dbContextType, rowId, "Clearing Temp Data", ProgressStatus.Completed);
fileContent.Clear();
}
return(result);
}
public ABSAPoolingLookup GetLookupData(RetailPoolingFileType productName, int totalcount, DbContextType dbContextType)
{
switch (dbContextType)
{
case DbContextType.SOURCEDATACONTEXT:
return(_dbContextSourceData.ABSAPoolingLookups.Where(x => totalcount <= x.FilterMaximum).Where(x => totalcount >= x.FilterMinimum).
Where(x => string.Compare(x.Product, productName.ToString(), true) == 0).ToList().FirstOrDefault());
case DbContextType.RFSTAGINGCONTEXT:
return(_dbContextRFStaging.ABSAPoolingLookups.Where(x => totalcount <= x.FilterMaximum).Where(x => totalcount >= x.FilterMinimum).
Where(x => string.Compare(x.Product, productName.ToString(), true) == 0).ToList().FirstOrDefault());
default:
throw new NotImplementedException();
}
}
//TM
public bool InitialTMLogic(RetailPoolingFileType productName, DbContextType dbContextType, Guid rowId)
{
var sql = $"[dbo].[ABSA_INITIALIZETMLOGIC] '{productName.ToString()}', '{rowId}'";
switch (dbContextType)
{
case DbContextType.SOURCEDATACONTEXT:
_dbContextSourceData.Database.ExecuteSqlCommand(sql);
return(true);
case DbContextType.RFSTAGINGCONTEXT:
_dbContextRFStaging.Database.ExecuteSqlCommand(sql);
return(true);
default:
throw new NotImplementedException();
}
}
public bool UpdateInputFileFinalLGDRank(RetailPoolingFileType productName, DbContextType dbContextType, Guid rowId)
{
var sql = $"[dbo].[ABSA_LGDUPDATEFINALRANK] '{productName.ToString()}', '{rowId}'";
switch (dbContextType)
{
case DbContextType.SOURCEDATACONTEXT:
_dbContextSourceData.Database.ExecuteSqlCommand(sql);
return(true);
case DbContextType.RFSTAGINGCONTEXT:
_dbContextRFStaging.Database.ExecuteSqlCommand(sql);
return(true);
default:
throw new NotImplementedException();
}
}
public List <ABSARetailPoolingGroupTM> GetTMPERPDLGDGroup(RetailPoolingFileType productName, DbContextType dbContextType, Guid rowId)
{
var result = new List <ABSARetailPoolingGroupTM>();
var sql = $"[dbo].[ABSA_TMPERPDLGDGROUP] '{productName.ToString()}', '{rowId}'";
switch (dbContextType)
{
case DbContextType.SOURCEDATACONTEXT:
result = _dbContextSourceData.Database.SqlQuery <ABSARetailPoolingGroupTM>(sql).ToList();
break;
case DbContextType.RFSTAGINGCONTEXT:
result = _dbContextRFStaging.Database.SqlQuery <ABSARetailPoolingGroupTM>(sql).ToList();
break;
default:
throw new NotImplementedException();
}
return(result);
}
public List <ABSARetailPoolingPDSegment> GetSegmentContents(DbContextType dbContextType, int clusterRowId, int segmentRowId, string segment, RetailPoolingFileType productName, Guid rowId)
{
var param = new ABSARetailPoolingPDSegmentParam()
{
DbContextType = dbContextType, ClusterRowId = clusterRowId, Segment = segment, SegmentRowId = segmentRowId, RetailPoolingFileType = productName, RowId = rowId
};
return(_iDbContextAccess.GetSegmentContents(param));
}
public List <ABSARetailPoolingClusterSegment> GetClusterSegments(RetailPoolingFileType productName, DbContextType dbContextType, Guid rowId)
{
return(_iDbContextAccess.GetClusterSegments(productName, dbContextType, rowId));
}
public bool InitialClusterPDLogic(RetailPoolingFileType productName, DbContextType dbContextType, Guid rowId)
{
return(_iDbContextAccess.InitialClusterPDLogic(productName, dbContextType, rowId));
}
public ABSAPoolingLookup GetLookupData(RetailPoolingFileType productName, int totalcount, DbContextType dbContextType)
{
return(_iDbContextAccess.GetLookupData(productName, totalcount, dbContextType));
}