public string AddAnalysisJobSettings(AnalysisJobManager analysisJobSettings, DbContextType dbContextType)
{
var result = string.Empty;
try {
switch (dbContextType)
{
case DbContextType.RFSTAGINGCONTEXT:
return(AddStagingDbAnalysisJobSettings(analysisJobSettings));
case DbContextType.SOURCEDATACONTEXT:
_dbContextSourceData.AnalysisJobManagers.Add(analysisJobSettings);
_dbContextSourceData.SaveChanges();
return(analysisJobSettings.AnalysisJobManagerId.ToString());
default:
throw new NotImplementedException();
}
}
catch (DbEntityValidationException e) {
result = string.Empty;
foreach (var eve in e.EntityValidationErrors)
{
foreach (var ve in eve.ValidationErrors)
{
result += string.Format("{0}: {1} || ", ve.PropertyName, ve.ErrorMessage);
}
}
return(result);
}
catch (Exception ex) {
return(ex.Message.ToString());
}
}
public bool CreateRetailProcessingProgressDetails(DbContextType dbContextType, Guid rowId, string message, ProgressStatus progressStatus)
{
var details = new ABSARetailProcessingProgressDetail()
{
RowId = rowId,
Status = progressStatus.ToString(),
Message = message,
CreatedDate = DateTime.Now
};
switch (dbContextType)
{
case DbContextType.SOURCEDATACONTEXT:
_dbContextSourceData.ABSARetailProcessingProgressDetails.AddOrUpdate(details);
_dbContextSourceData.SaveChanges();
break;
case DbContextType.RFSTAGINGCONTEXT:
_dbContextRFStaging.ABSARetailProcessingProgressDetails.AddOrUpdate(details);
break;
default:
throw new NotImplementedException();
}
return(true);
}
/// <summary>
/// 获取上下文
/// </summary>
/// <returns></returns>
public static DbContext GetContext(DbOpertionType OpertionType)
{
DbContextType ContextType = DBInitializer.DbContextType;
if (ContextType == DbContextType.MySql)
{
if (OpertionType == DbOpertionType.Read)
{
return(new ReadMySqlContext());
}
else
{
return(new WriteMySqlContext());
}
}
else
{
if (OpertionType == DbOpertionType.Read)
{
return(new ReadSqlServiceContext());
}
else
{
return(new WriteSqlServiceContext());
}
}
}
public TService Build(IServiceProvider provider)
{
var dbContextName = DbContextType.FullName;
var constructor = DbContextType
.GetConstructors(BindingFlags.Instance | BindingFlags.Public)
.FirstOrDefault(p =>
p.GetParameters()
.Count(x =>
x.ParameterType == typeof(MongoDbContextOptions)) > 0);
var constructorParamsInfo = constructor.GetParameters();
var dbContextOptions = BuildOptions(constructorParamsInfo);
var constratorParams = new object[constructorParamsInfo.Length];
var i = 0;
foreach (var p in constructorParamsInfo)
{
if (p.ParameterType == typeof(MongoDbContextOptions))
{
constratorParams[i] = dbContextOptions;
}
else if (provider != default(IServiceProvider))
{
constratorParams[i] = provider.GetService(p.ParameterType);
}
else
{
throw new ArgumentNullException("You should pass a provider or database context options");
}
i++;
}
return((TService)constructor.Invoke(constratorParams));
}
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 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 static void combineParams(DbContextType type, ref DbCommand command, object @params = null)
{
if (@params != null)
{
Type paramType;
switch (type)
{
case DbContextType.SqlServer:
paramType = typeof(SqlParameter);
break;
case DbContextType.MySql:
paramType = typeof(MySqlParameter);
break;
case DbContextType.Sqlite:
paramType = typeof(SqliteParameter);
break;
default:
throw new Exception("未实现的数据库类型");
}
foreach (var param in @params as SqlParameter[])
{
var paramItem = Activator.CreateInstance(paramType, $"{param.ParameterName}", (object)param.Value);
command.Parameters.Add(paramItem);
}
}
}
public DbContext(DbContextBuilder builder)
{
Connection = builder.Connection;
_xmlResovle = builder.XmlResovle;
_typeMapper = builder.TypeMapper ?? new TypeMapper();
DbContextType = builder.DbContextType;
}
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 List <Type> GetDbSetBodyTypes()
{
return(DbContextType.GetProperties()
.Select(t => t.PropertyType)
.Where(t => t.IsGenericType)
.Select(s => s.GetGenericArguments()[0])
.ToList());
}
private Guid CreateRetailPoolingProgress(DbContextType dbContextType, RetailPoolingFileType productName)
{
return(_iDbContextAccess.CreateRetailProcessingProgress(dbContextType, new ABSARetailProcessingProgress()
{
ProductName = productName.ToString(),
DateCreated = DateTime.Now
}));
}
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));
}
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);
}
protected DbContext()
{
var builder = OnConfiguring(new DbContextBuilder());
Connection = builder.Connection;
_xmlResovle = builder.XmlResovle;
_typeMapper = builder.TypeMapper ?? new TypeMapper();
DbContextType = builder.DbContextType;
}
//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 DbContextOptions Create(DbContextType type)
{
switch (type)
{
case DbContextType.Tenant:
return(BuildTenantOptions());
case DbContextType.Auth:
return(BuildAuthOptions());
}
throw new ArgumentOutOfRangeException(nameof(type), $"Unhandled type: {type}");
}
public List <ABSARetailPoolingConfigTypeDetail> GetRetailPoolingConfigs(DbContextType dbContextType)
{
switch (dbContextType)
{
case DbContextType.SOURCEDATACONTEXT:
return(_dbContextSourceData.ABSARetailPoolingConfigTypeDetails.Where(x => string.Compare(x.Code, ABSARetailPoolingEnumConfigType.RP.ToString(), true) == 0).ToList());
case DbContextType.RFSTAGINGCONTEXT:
return(_dbContextRFStaging.ABSARetailPoolingConfigTypeDetails.Where(x => string.Compare(x.Code, ABSARetailPoolingEnumConfigType.RP.ToString(), true) == 0).ToList());
default:
throw new NotImplementedException();
}
}
public int GetSegmentTMCount(string uniqueSegmentPDLGD, DbContextType dbContextType)
{
switch (dbContextType)
{
case DbContextType.SOURCEDATACONTEXT:
return(_dbContextSourceData.ABSARetailPoolingTMSegments.Where(x => string.Compare(x.UniqueSegmentPDLGD, uniqueSegmentPDLGD, true) == 0).Sum(x => x.TMCount));
case DbContextType.RFSTAGINGCONTEXT:
return(_dbContextRFStaging.ABSARetailPoolingTMSegments.Where(x => string.Compare(x.UniqueSegmentPDLGD, uniqueSegmentPDLGD, true) == 0).Sum(x => x.TMCount));
default:
throw new NotImplementedException();
}
}
private bool RatioInitialRankFinalPoolUpdate(DbContextType dbContextType, string sql)
{
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 UpdateJob(int jobId, DbContextType dbContextType)
{
var sql = string.Format("UPDATE Job SET status = 'Loading Complete' WHERE jobId = {0}", jobId);
switch (dbContextType)
{
case DbContextType.RFSTAGINGCONTEXT:
_dbContextRFStaging.Database.ExecuteSqlCommand(sql);
return(true);
case DbContextType.SOURCEDATACONTEXT:
_dbContextSourceData.Database.ExecuteSqlCommand(sql);
return(true);
default:
throw new NotImplementedException();
}
}
/// <summary>
/// 创建数据库连接
/// </summary>
/// <param name="cnstr"></param>
/// <returns></returns>
protected virtual DbConnection CreateConnection(DbContextType type, string cnstr)
{
_type = type;
return(new SqlConnection(cnstr)); // default or mssql;
//switch (type)
//{
// case DbContextType.MySql:
// return new MySqlConnection(cnstr);
// case DbContextType.Oracle:
// return new OracleConnection(cnstr);
// default:
// return new SqlConnection(cnstr); // default or mssql;
//}
}
public bool AddImportAsOfDate(int jobId, DbContextType dbContextType)
{
var sql = string.Format("INSERT INTO importAsOfDate(jobId,asOfDate) VALUES({0},'{1}')", jobId, DateTime.Now.Date);
switch (dbContextType)
{
case DbContextType.RFSTAGINGCONTEXT:
_dbContextRFStaging.Database.ExecuteSqlCommand(sql);
return(true);
case DbContextType.SOURCEDATACONTEXT:
_dbContextSourceData.Database.ExecuteSqlCommand(sql);
return(true);
default:
throw new NotImplementedException();
}
}
public bool SyncCountryFactorRSquared(int jobId, DbContextType dbContextType)
{
var sql = string.Format("[dbo].[ABSA_SyncCountryRSqured] @jobId");
switch (dbContextType)
{
case DbContextType.SOURCEDATACONTEXT:
_dbContextSourceData.Database.ExecuteSqlCommand(sql, new SqlParameter("@jobId", jobId));
return(true);
case DbContextType.RFSTAGINGCONTEXT:
_dbContextRFStaging.Database.ExecuteSqlCommand(sql, new SqlParameter("@jobId", jobId));
return(true);
default:
throw new NotImplementedException();
}
}
public bool TruncateRetailPoolingInputTable(DbContextType dbContextType)
{
var sql = $"[dbo].[ABSA_CLEANRETAILINPUTTABLES] ";
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();
}
}
//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 MongoDbContextBuilder()
{
DbContextType = typeof(TContext);
var properties = DbContextType.GetProperties();
foreach (var property in properties)
{
var propertyType = property.PropertyType;
if (!propertyType.IsConstructedGenericType)
{
continue;
}
var doumentType = propertyType.GenericTypeArguments[0];
AddCollection(doumentType);
}
}
public bool UpdateAnalysisJobSettings(int analysisJobSettingsId, DbContextType dbContextType)
{
switch (dbContextType)
{
case DbContextType.SOURCEDATACONTEXT:
var sql = string.Format("UPDATE AnalysisJobManager SET [IsProcessed] = 1, [ProcessingDate] = GETDATE() WHERE [AnalysisJobManagerId] = {0}", analysisJobSettingsId);
_dbContextSourceData.Database.ExecuteSqlCommand(sql);
return(true);
case DbContextType.RFSTAGINGCONTEXT:
var sqlStagingDb = string.Empty;
sqlStagingDb = string.Format("[dbo].[ABSA_Update_AnalysisJobManager_CORE_DB] @AnalysisJobManagerId");
_dbContextRFStaging.Database.ExecuteSqlCommand(sqlStagingDb, new SqlParameter("@AnalysisJobManagerId", analysisJobSettingsId));
return(true);
default:
throw new NotImplementedException();
}
}
public bool RemoveRetailPoolingInputContents(DbContextType dbContextType)
{
var sql = string.Format("TRUNCATE TABLE [dbo].[ABSARetailPoolingInput]");
switch (dbContextType)
{
case DbContextType.SOURCEDATACONTEXT:
_dbContextSourceData.Database.ExecuteSqlCommand(sql);
break;
case DbContextType.RFSTAGINGCONTEXT:
_dbContextRFStaging.Database.ExecuteSqlCommand(sql);
break;
default:
throw new NotImplementedException();
}
return(true);
}
public List <ABSARetailPoolingTMSegment> GetFileContentsTM(DbContextType dbContextType, Guid rowId)
{
var result = new List <ABSARetailPoolingTMSegment>();
var sql = $"[dbo].[ABSA_TMPoolingOutput] '{rowId}'";
switch (dbContextType)
{
case DbContextType.SOURCEDATACONTEXT:
result = _dbContextSourceData.Database.SqlQuery <ABSARetailPoolingTMSegment>(sql).ToList();
break;
case DbContextType.RFSTAGINGCONTEXT:
result = _dbContextRFStaging.Database.SqlQuery <ABSARetailPoolingTMSegment>(sql).ToList();
break;
default:
throw new NotImplementedException();
}
return(result);
}
