/// <summary>
/// Update general insurance by generate sql script
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
/// <param name="referenceData">Reference data, store reference value from general_insurance -> workbook</param>
private void UpdateGeneralInsuranceBySQLScript(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase, Dictionary<int, int?> referenceData)
{
//
// Initialize
string destinationDatabaseName = destinationDatabase.GetDatabaseName();
string sqlScript = "-- This script updates general_insurance table when all records of workbooks table are mapped." + Environment.NewLine +
"USE [" + destinationDatabaseName + "]" + Environment.NewLine +
"GO" + Environment.NewLine;
string updateTemplate = "UPDATE {DESTINATION_DATABASE_NAME}.dbo." + Environment.NewLine +
"SET genins_current_workbook = {REFERENCE_VALUE}" + Environment.NewLine +
"WHERE genins_id = {PRIMARY_KEY_VALUE}" + Environment.NewLine;
updateTemplate.Replace("{DESTINATION_DATABASE_NAME}", destinationDatabaseName);
//
// Generate update script
foreach (int primaryKey in referenceData.Keys)
{
sqlScript = sqlScript + updateTemplate.Replace("{PRIMARY_KEY_VALUE}", primaryKey.ToString())
.Replace("{REFERENCE_VALUE}", referenceData[primaryKey].Value.ToString());
}
//
// Append to workbook script
manager.WriteSQLScript("workbooks", sqlScript, true);
}
/// <summary>
/// Create new orphan record validation manager
/// </summary>
/// <param name="database"></param>
public OrphanRecordValidationThreadManager(SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase)
{
this._sourceDatabase = sourceDatabase;
this._destinationDatabase = destinationDatabase;
this._locker = new object();
this.MaximumNumberOfThread = 60;
this.MinimumTaskForThread = 10;
}
/// <summary>
/// Set blob pointer before mapping
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
/// <param name="obj"></param>
public override void BeforeMappingRecord(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase, object obj)
{
DatabaseConversion.CleanerTool.BOALedgerDataAccess.EmailTemplates record = obj as DatabaseConversion.CleanerTool.BOALedgerDataAccess.EmailTemplates;
if (this._temporyFilePath.ContainsKey(record.et_id))
{
record.et_blob_pointer = this._temporyFilePath[record.et_id];
}
}
/// <summary>
/// Set blob pointer before mapping
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
/// <param name="obj"></param>
public override void BeforeMappingRecord(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase, object obj)
{
DatabaseConversion.CleanerTool.BOALedgerDataAccess.policy_transaction_documents record = obj as DatabaseConversion.CleanerTool.BOALedgerDataAccess.policy_transaction_documents;
if (this._temporyFilePath.ContainsKey(record.poltrado_id))
{
record.poltrado_blob_pointer = this._temporyFilePath[record.poltrado_id];
}
}
/// <summary>
/// Set blob pointer before mapping
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
/// <param name="obj"></param>
public override void BeforeMappingRecord(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase, object obj)
{
DatabaseConversion.CleanerTool.BOALedgerDataAccess.client_insurance_portfolio record = obj as DatabaseConversion.CleanerTool.BOALedgerDataAccess.client_insurance_portfolio;
if (this._temporyFilePath.ContainsKey(record.cip_id))
{
record.cip_blob_pointer = this._temporyFilePath[record.cip_id];
}
}
/// <summary>
/// Manual mapping function for SteadfastReport table
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
public override void Map(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase)
{
//
// Create dump migration script if needed
if (manager.IsGeneratedScript)
{
manager.WriteSQLScript("SteadfastReport", "", false);
}
}
/// <summary>
/// Create new mapping manager for source database and destination database
/// </summary>
/// <param name="sourceDatabase">Source database handler</param>
/// <param name="destinationDatabase">Destination database handler</param>
public MappingManager(SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase)
{
this._configManualMapping = new Dictionary <string, ManualMapping>();
this._configChangeName = new Dictionary <string, string>();
this._sourceDatabase = sourceDatabase;
this._destinationDatabase = destinationDatabase;
this._listPostMigrationScript = new List <string>();
this._databaseSentinel = new DatabaseSentinel();
this.MaximumNumberOfThread = 50;
this.MinimumTaskForThread = 10;
}
/// <summary>
/// Run BLOBPointer converter before mapping
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
/// <param name="obj"></param>
public override void BeforeRunningAutoMapper(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase, object obj)
{
//
// Cast type
DatabaseConversion.CleanerTool.EclipseDataAccess.confirmation_of_cover record = obj as DatabaseConversion.CleanerTool.EclipseDataAccess.confirmation_of_cover;
//
// Run blob pointer converter if needed
if (record.coc_document_object_eclipse != null && !IgnoreBlobs)
{
string fullPath = BlobConverter.ConvertToFile(BlobStoreCategories.CONFIRMATION_OF_COVER, BlobStoreCategories.CONFIRMATION_OF_COVER, record.coc_document_object_eclipse);
this._temporyFilePath.Add(record.coc_id, fullPath);
}
}
/// <summary>
/// Run BLOBPointer converter before mapping
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
/// <param name="obj"></param>
public override void BeforeRunningAutoMapper(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase, object obj)
{
//
// Cast type
DatabaseConversion.CleanerTool.EclipseDataAccess.DocumentRepository record = obj as DatabaseConversion.CleanerTool.EclipseDataAccess.DocumentRepository;
//
// Run blob pointer converter if needed
if (record.dr_document != null && !IgnoreBlobs)
{
string fullPath = BlobConverter.ConvertToFile(BlobStoreCategories.DOCUMENT_REPOSITORY, BlobStoreCategories.DOCUMENT_REPOSITORY, record.dr_document);
this._temporyFilePath.Add(record.dr_id, fullPath);
}
}
/// <summary>
/// Run BLOBPointer converter before mapping
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
/// <param name="obj"></param>
public override void BeforeRunningAutoMapper(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase, object obj)
{
//
// Cast type
DatabaseConversion.CleanerTool.EclipseDataAccess.claim_sub_tasks record = obj as DatabaseConversion.CleanerTool.EclipseDataAccess.claim_sub_tasks;
//
// Run blob pointer converter if needed
if (record.clasubta_image != null && !IgnoreBlobs)
{
string fullPath = BlobConverter.ConvertToFile(BlobStoreCategories.CLAIM_TASK_DOCUMENT, BlobStoreCategories.CLAIM_TASK_DOCUMENT, record.clasubta_image);
this._temporyFilePath.Add(record.clasubta_id, fullPath);
}
}
/// <summary>
/// Run BLOBPointer converter before mapping
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
/// <param name="obj"></param>
public override void BeforeRunningAutoMapper(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase, object obj)
{
//
// Cast type
DatabaseConversion.CleanerTool.EclipseDataAccess.EmailTemplates record = obj as DatabaseConversion.CleanerTool.EclipseDataAccess.EmailTemplates;
//
// Run blob pointer converter if needed
if (record.et_template != null && !IgnoreBlobs)
{
string fullPath = BlobConverter.ConvertToFile(BlobStoreCategories.EMAIL_TEMPLATE, BlobStoreCategories.EMAIL_TEMPLATE, record.et_template);
this._temporyFilePath.Add(record.et_id, fullPath);
}
}
/// <summary>
/// Run BLOBPointer converter before mapping
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
/// <param name="obj"></param>
public override void BeforeRunningAutoMapper(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase, object obj)
{
//
// Cast type
DatabaseConversion.CleanerTool.EclipseDataAccess.policy_transaction_documents record = obj as DatabaseConversion.CleanerTool.EclipseDataAccess.policy_transaction_documents;
//
// Run blob pointer converter if needed
if (record.poltrado_object_eclipse != null && !IgnoreBlobs)
{
string fullPath = BlobConverter.ConvertToFile(BlobStoreCategories.POLICY_TRANSACTION_DOCUMENTS, BlobStoreCategories.POLICY_TRANSACTION_DOCUMENTS, record.poltrado_object_eclipse);
this._temporyFilePath.Add(record.poltrado_id, fullPath);
}
}
/// <summary>
/// Run BLOBPointer converter before mapping
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
/// <param name="obj"></param>
public override void BeforeRunningAutoMapper(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase, object obj)
{
//
// Cast type
DatabaseConversion.CleanerTool.EclipseDataAccess.client_insurance_portfolio record = obj as DatabaseConversion.CleanerTool.EclipseDataAccess.client_insurance_portfolio;
//
// Run blob pointer converter if needed
if (record.cip_document_object_eclipse != null && !IgnoreBlobs)
{
string fullPath = BlobConverter.ConvertToFile(BlobStoreCategories.CLIENT_INSURANCE_PORTFOLIO, BlobStoreCategories.CLIENT_INSURANCE_PORTFOLIO, record.cip_document_object_eclipse);
this._temporyFilePath.Add(record.cip_id, fullPath);
}
}
/// <summary>
/// Before add policies record into BOALedger database, we will try to update value from Workbook_SOA Table
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
/// <param name="record"></param>
public override void BeforeMappingRecord(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase, object obj)
{
//
// Cast type
DatabaseConversion.CleanerTool.BOALedgerDataAccess.policies record = obj as DatabaseConversion.CleanerTool.BOALedgerDataAccess.policies;
//
// Check does this record reference to workbooks table or not ?
// If it does, we need to check the value of
if (record.pol_wor_id.HasValue)
{
DatabaseConversion.CleanerTool.EclipseDataAccess.EclipseDatabaseEntities sourceDbContext = sourceDatabase.GetNewDbContext() as DatabaseConversion.CleanerTool.EclipseDataAccess.EclipseDatabaseEntities;
//
// Get related workbook_SOA record
DatabaseConversion.CleanerTool.EclipseDataAccess.Workbook_SOA workbook_SOARecord =
(from workbook_SOATable in sourceDbContext.Workbook_SOA
join workbooksTable in sourceDbContext.workbooks on workbook_SOATable.ws_parent_id equals workbooksTable.wor_id
where workbooksTable.wor_id == record.pol_wor_id.Value
select workbook_SOATable).FirstOrDefault();
//
// If not null, try to convert and assign new value to record
if (workbook_SOARecord != null)
{
if (workbook_SOARecord.ws_scopeofadvice != null)
{
record.pol_scope_of_advice = WordGenerator.ConvertRtfToHtml(WordGenerator.ConvertDocxToRTF(workbook_SOARecord.ws_scopeofadvice));
}
if (workbook_SOARecord.ws_recommendations != null)
{
record.pol_recommendations = WordGenerator.ConvertRtfToHtml(WordGenerator.ConvertDocxToRTF(workbook_SOARecord.ws_recommendations));
}
if (workbook_SOARecord.ws_notice1 != null)
{
record.pol_notice1 = WordGenerator.ConvertRtfToHtml(WordGenerator.ConvertDocxToRTF(workbook_SOARecord.ws_notice1));
}
if (workbook_SOARecord.ws_notice2 != null)
{
record.pol_notice2 = WordGenerator.ConvertRtfToHtml(WordGenerator.ConvertDocxToRTF(workbook_SOARecord.ws_notice2));
}
if (workbook_SOARecord.ws_notice3 != null)
{
record.pol_notice3 = WordGenerator.ConvertRtfToHtml(WordGenerator.ConvertDocxToRTF(workbook_SOARecord.ws_notice3));
}
}
}
}
/// <summary>
/// Migration reference from destination to source
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
private void MigrationReference(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase)
{
List<string> listSourceTableName = sourceDatabase.GetListTable();
List<string> listDestTableName = destinationDatabase.GetListTable();
foreach (string sourceTableName in listSourceTableName)
{
//
// Check does it rename or not ?
string destTableName = manager.GetTableNewName(sourceTableName);
LogService.Log.Info("Migrate from " + destTableName + " to " + sourceTableName);
//
// Then check, it's still exist in destination database or not
if (listDestTableName.Contains(destTableName) == false)
{
LogService.Log.Info("Table " + sourceTableName + " has been dropped in destination database.");
continue;
}
//
// Get MapTable (class contains information about table, includes references)
MapTable sourceMapTable = sourceDatabase.GetMapTable(sourceTableName);
MapTable destMapTable = destinationDatabase.GetMapTable(destTableName);
//
// Add references
List<ReferenceInformation> listDestReference = destMapTable.GetReference();
foreach (ReferenceInformation reference in listDestReference)
{
//
// Try to revert from new name of table to old name of table
string PKTableOldName = manager.GetTableOldName(reference.PKTableName);
string FKTableOldName = manager.GetTableOldName(reference.FKTableName);
reference.PKTableName = PKTableOldName;
reference.FKTableName = FKTableOldName;
//
// Add new reference information to sourceMapTable
if (sourceMapTable.HasReference(reference) == false)
{
LogService.Log.Info("Add new reference from " + reference.FKTableName + "(" + reference.FKColumnName + ")" +
"to " + reference.FKTableName + "(" + reference.FKColumnName + ")");
sourceMapTable.AddReference(reference);
}
}
}
}
/// <summary>
/// Manual function, after mapping completed, we will re-update reference value
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
public override void AfterMapping(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase)
{
//
// First, try to load new record in destination database
string destinationDatabaseName = destinationDatabase.GetDatabaseName();
//
// Try to update
if (manager.IsGeneratedScript)
{
//
// First generate update script
string sqlScript = "USE [" + destinationDatabaseName + "]" + Environment.NewLine +
"GO" + Environment.NewLine;
string updateTemplate = "UPDATE {DESTINATION_DATABASE_NAME}.dbo.branches" + Environment.NewLine +
"SET bra_parent_branch = {REFERENCE_VALUE}" + Environment.NewLine +
"WHERE bra_id = {PRIMARY_KEY_VALUE}" + Environment.NewLine;
updateTemplate = updateTemplate.Replace("{DESTINATION_DATABASE_NAME}", destinationDatabase.GetDatabaseName());
foreach (int primaryKeyValue in this._listReferenceValue.Keys)
{
int? referenceValue = this._listReferenceValue[primaryKeyValue];
sqlScript = sqlScript + updateTemplate.Replace("{REFERENCE_VALUE}", referenceValue.Value.ToString())
.Replace("{PRIMARY_KEY_VALUE}", primaryKeyValue.ToString());
}
//
// Then append to file (existed file)
manager.WriteSQLScript("branches", sqlScript, true);
}
else
{
//
// If we do not generate sql script
// we need to update directly in destination table
BOALedgerDataAccess.BOALedgerEntities dbContext = destinationDatabase.GetDbContext() as BOALedgerDataAccess.BOALedgerEntities;
List<BOALedgerDataAccess.branches> listRecords = dbContext.branches.ToList();
foreach (BOALedgerDataAccess.branches record in listRecords)
{
if (this._listReferenceValue.ContainsKey(record.bra_id))
{
record.bra_parent_branch = this._listReferenceValue[record.bra_id];
}
}
dbContext.SaveChanges();
}
}
/// <summary>
/// Set blob pointer before mapping
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
/// <param name="obj"></param>
public override void BeforeMappingRecord(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase, object obj)
{
DatabaseConversion.CleanerTool.BOALedgerDataAccess.general_insurance_workbooks record = obj as DatabaseConversion.CleanerTool.BOALedgerDataAccess.general_insurance_workbooks;
if (this._documentFilePath.ContainsKey(record.geninswb_id))
{
record.geninswb_blob_pointer_document = this._documentFilePath[record.geninswb_id];
}
if (this._portfolioFilePath.ContainsKey(record.geninswb_id))
{
record.geninswb_blob_pointer_portfolio = this._portfolioFilePath[record.geninswb_id];
}
if (this._cocFilePath.ContainsKey(record.geninswb_id))
{
record.geninswb_blob_pointer_coc = this._cocFilePath[record.geninswb_id];
}
if (this._objectFilePath.ContainsKey(record.geninswb_id))
{
record.geninswb_blob_pointer_object = this._objectFilePath[record.geninswb_id];
}
}
/// <summary>
/// Do some stuffs before commit new value to destination database
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
/// <param name="listNewRecord"></param>
public override void BeforeCommitChanges(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase, List<object> listNewRecord)
{
//
// Initialize for sharing data
Dictionary<int, int?> shareData = new Dictionary<int, int?>();
//
// Loop and set null
foreach (object obj in listNewRecord)
{
DatabaseConversion.CleanerTool.BOALedgerDataAccess.general_insurance record = obj as DatabaseConversion.CleanerTool.BOALedgerDataAccess.general_insurance;
if (record.genins_current_workbook != null)
{
shareData.Add(record.genins_id, record.genins_current_workbook);
record.genins_current_workbook = null;
}
}
//
// Share data for ManualMapping_workbooks
ManualMappingDropBox.ShareData("general_insurance", shareData);
}
/// <summary>
/// Validate database
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
public override void Validate(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase)
{
//
// Initialize
this.MigrationReference(manager, sourceDatabase, destinationDatabase);
this._threadManager = new OrphanRecordValidationThreadManager(sourceDatabase, destinationDatabase);
List<string> listTable = sourceDatabase.GetListTable();
//
// Start validate
LogService.Log.Info("Start validate orphan record from database.");
foreach (string table in listTable)
{
LogService.Log.Info("Validate table : " + table);
this._threadManager.Validate(table);
}
//
// Write SQLScript to Orphans.sql
LogService.Log.Info("Create Orphans.sql to delete orphan record.");
ErrorHandlerManager.ExportScriptToDeleteOrphan(sourceDatabase, @"Orphans.sql");
}
/// <summary>
/// Manual mapping function for period_renewals_particulars_t
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
public override void Map(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase)
{
//
// Try to get source database name and destination database name
DbContext sourceDbContext = sourceDatabase.GetDbContext();
DbContext destDbContext = destinationDatabase.GetDbContext();
string sourceDatabaseName = sourceDbContext.Database.SqlQuery<string>(@"SELECT DB_NAME() AS [Current Database]").FirstOrDefault();
string destDatabaseName = destDbContext.Database.SqlQuery<string>(@"SELECT DB_NAME() AS [Current Database]").FirstOrDefault();
string tableName = "period_renewals_particulars_t";
//
// Generate SQL script
string sqlQuery = @"INSERT INTO {DESTINATION_DATABASE_NAME}.dbo.period_renewals_particulars_t(prp_pol_parent_id, prp_pol_particulars, prp_pol_id, prp_pol_sum_insured, prp_spid)" + Environment.NewLine +
"SELECT prp_pol_parent_id, prp_pol_particulars, prp_pol_id, prp_pol_sum_insured, prp_spid FROM {SOURCE_DATABASE_NAME}.dbo.period_renewals_particulars_t" + Environment.NewLine;
sqlQuery = sqlQuery.Replace("{SOURCE_DATABASE_NAME}", sourceDatabaseName)
.Replace("{DESTINATION_DATABASE_NAME}", destDatabaseName);
//
// Exec SQL script or write to file
if (manager.IsGeneratedScript)
{
sqlQuery = "USE [" + destDatabaseName + "]" + Environment.NewLine +
"GO" + Environment.NewLine +
sqlQuery;
manager.WriteSQLScript(tableName, sqlQuery, false);
}
else
{
try
{
destDbContext.Database.ExecuteSqlCommand(sqlQuery);
}
catch (Exception excExecScript)
{
LogService.Log.Error("Can not run migration script for " + tableName, excExecScript);
}
}
}
/// <summary>
/// Main mapping function
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
public override void Map(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase)
{
//
// Try to get source database name and destination database name
DbContext sourceDbContext = sourceDatabase.GetDbContext();
DbContext destDbContext = destinationDatabase.GetDbContext();
string sourceDatabaseName = sourceDbContext.Database.SqlQuery<string>(@"SELECT DB_NAME() AS [Current Database]").FirstOrDefault();
string destDatabaseName = destDbContext.Database.SqlQuery<string>(@"SELECT DB_NAME() AS [Current Database]").FirstOrDefault();
string tableName = "tmpPortfolioAnalysis";
//
// Exec sql script to insert data
string sqlQuery = @"INSERT INTO {DESTINATION_DATABASE_NAME}.dbo.tmpPortfolioAnalysis(GrpId, GrpName, SortName, Clientcount, TotalPolicies, Totalclients, Premiums, leviduties, Othercharges, bfees, comm, InvoiceTotal, spid, pol_sub_agent_fee_pay_net_gst, pol_sub_agent_comm_pay_net_gst, ccfees)" + Environment.NewLine +
"SELECT GrpId, GrpName, SortName, Clientcount, TotalPolicies, Totalclients, Premiums, leviduties, Othercharges, bfees, comm, InvoiceTotal, spid, pol_sub_agent_fee_pay_net_gst, pol_sub_agent_comm_pay_net_gst, ccfees FROM {SOURCE_DATABASE_NAME}.dbo.tmpPortfolioAnalysis" + Environment.NewLine;
sqlQuery = sqlQuery.Replace("{SOURCE_DATABASE_NAME}", sourceDatabaseName)
.Replace("{DESTINATION_DATABASE_NAME}", destDatabaseName);
//
// Exec SQL script or write to file
if (manager.IsGeneratedScript)
{
sqlQuery = "USE [" + destDatabaseName + "]" + Environment.NewLine +
"GO" + Environment.NewLine +
sqlQuery;
manager.WriteSQLScript(tableName, sqlQuery, false);
}
else
{
try
{
destDbContext.Database.ExecuteSqlCommand(sqlQuery);
}
catch (Exception excExecScript)
{
LogService.Log.Error("Can not run migration script for " + tableName, excExecScript);
}
}
}
/// <summary>
/// Main mapping function
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
public override void Map(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase)
{
//
// Try to get source database name and destination database name
DbContext sourceDbContext = sourceDatabase.GetDbContext();
DbContext destDbContext = destinationDatabase.GetDbContext();
string sourceDatabaseName = sourceDbContext.Database.SqlQuery<string>(@"SELECT DB_NAME() AS [Current Database]").FirstOrDefault();
string destDatabaseName = destDbContext.Database.SqlQuery<string>(@"SELECT DB_NAME() AS [Current Database]").FirstOrDefault();
string tableName = "statements";
//
// Generate SQL script
string sqlQuery = @"INSERT INTO {DESTINATION_DATABASE_NAME}.dbo.statements(gltr_glsl_id, glsl_entity_id, glsl_name, gltr_id, gltr_date, glmv_date, glmv_id, glmv_glrs_set, glmv_record_ref_id, origamount, gltr_amount, debit, credit, balance, unalloccash, netbalance, pol_id, pol_id1, pol_tran_id, pol_tran_id1, tran_id, Pol_policy_number, genins_name, genins_class_of_business, gencob_name, paymethod, client, sortid, spid, salesteam, BRComm, NetDue, BRClient, StatementInsurer, PaymentType, InvoiceComments)" + Environment.NewLine +
"SELECT gltr_glsl_id, glsl_entity_id, glsl_name, gltr_id, gltr_date, glmv_date, glmv_id, glmv_glrs_set, glmv_record_ref_id, origamount, gltr_amount, debit, credit, balance, unalloccash, netbalance, pol_id, pol_id1, pol_tran_id, pol_tran_id1, tran_id, Pol_policy_number, genins_name, genins_class_of_business, gencob_name, paymethod, client, sortid, spid, salesteam, BRComm, NetDue, BRClient, StatementInsurer, PaymentType, InvoiceComments FROM {SOURCE_DATABASE_NAME}.dbo.statements" + Environment.NewLine;
sqlQuery = sqlQuery.Replace("{SOURCE_DATABASE_NAME}", sourceDatabaseName)
.Replace("{DESTINATION_DATABASE_NAME}", destDatabaseName);
//
// Exec SQL script or write to file
if (manager.IsGeneratedScript)
{
sqlQuery = "USE [" + destDatabaseName + "]" + Environment.NewLine +
"GO" + Environment.NewLine +
sqlQuery;
manager.WriteSQLScript(tableName, sqlQuery, false);
}
else
{
try
{
destDbContext.Database.ExecuteSqlCommand(sqlQuery);
}
catch (Exception excExecScript)
{
LogService.Log.Error("Can not run migration script for " + tableName, excExecScript);
}
}
}
/// <summary>
/// Main mapping function
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
public override void Map(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase)
{
//
// Try to get source database name and destination database name
DbContext sourceDbContext = sourceDatabase.GetDbContext();
DbContext destDbContext = destinationDatabase.GetDbContext();
string sourceDatabaseName = sourceDbContext.Database.SqlQuery<string>(@"SELECT DB_NAME() AS [Current Database]").FirstOrDefault();
string destDatabaseName = destDbContext.Database.SqlQuery<string>(@"SELECT DB_NAME() AS [Current Database]").FirstOrDefault();
string tableName = "IBA";
//
// Exec sql script to insert data
string sqlQuery = @"INSERT INTO {DESTINATION_DATABASE_NAME}.dbo.IBA(Fromdate, Todate, adp77, adp78, adp79, adp80, adp81, adp82, adp83, adp84, adp85, adp86, adp87A, adp87B, adp87C, adp87, adp88, adp89, adp90, adp91, adp92, adp93, adp94, adp95, adp100, adp101, adp102, adp103, adp104, adp105, TotalSelfDrawings, TotalGSTDrawings, TotalBrokerRepEarnings, Earnings, GSTonEarnings, DrawnToBrokerRep, DrawnToGST, DrawnToSelf, Adjustments, adp106A, adp106b, adp106c, adp106, adp107, adp108, CashClosingBalance, Asic26DiffAmount, Asic27DiffAmount, spid)" + Environment.NewLine +
"SELECT Fromdate, Todate, adp77, adp78, adp79, adp80, adp81, adp82, adp83, adp84, adp85, adp86, adp87A, adp87B, adp87C, adp87, adp88, adp89, adp90, adp91, adp92, adp93, adp94, adp95, adp100, adp101, adp102, adp103, adp104, adp105, TotalSelfDrawings, TotalGSTDrawings, TotalBrokerRepEarnings, Earnings, GSTonEarnings, DrawnToBrokerRep, DrawnToGST, DrawnToSelf, Adjustments, adp106A, adp106b, adp106c, adp106, adp107, adp108, CashClosingBalance, Asic26DiffAmount, Asic27DiffAmount, spid FROM {SOURCE_DATABASE_NAME}.dbo.IBA" + Environment.NewLine;
sqlQuery = sqlQuery.Replace("{SOURCE_DATABASE_NAME}", sourceDatabaseName)
.Replace("{DESTINATION_DATABASE_NAME}", destDatabaseName);
//
// Exec SQL script or write to file
if (manager.IsGeneratedScript)
{
sqlQuery = "USE [" + destDatabaseName + "]" + Environment.NewLine +
"GO" + Environment.NewLine +
sqlQuery;
manager.WriteSQLScript(tableName, sqlQuery, false);
}
else
{
try
{
destDbContext.Database.ExecuteSqlCommand(sqlQuery);
}
catch (Exception excExecScript)
{
LogService.Log.Error("Can not run migration script for " + tableName, excExecScript);
}
}
}
/// <summary>
/// Manual mapping function for view_earnings2
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
public override void Map(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase)
{
//
// Try to get source database name and destination database name
DbContext sourceDbContext = sourceDatabase.GetDbContext();
DbContext destDbContext = destinationDatabase.GetDbContext();
string sourceDatabaseName = sourceDbContext.Database.SqlQuery<string>(@"SELECT DB_NAME() AS [Current Database]").FirstOrDefault();
string destDatabaseName = destDbContext.Database.SqlQuery<string>(@"SELECT DB_NAME() AS [Current Database]").FirstOrDefault();
string tableName = "view_earnings2";
//
// Generate SQL script
string sqlQuery = @"INSERT INTO {DESTINATION_DATABASE_NAME}.dbo.view_earnings2(ent_id, ent_name, genins_name, pol_parent_id, pol_id, pol_tran_id, pol_total_gross_premium, pol_total_net_premium, adj, acr, debtor_bal, earn_broker_comm, earn_sa_comm, earn_broker_fee, earn_sa_fee, earn_total, spid, earn_gst, broker_comm, broker_fee, total_earn, comm_earned, fees_earned, fees_gst, comm_gst, total_earn_gst, total_earn_net_gst, gst_rate, bal, bal2, sa_payable, pol_amount_remitted_sub, sa_gst, pol_fee_gst, pol_comm_gst, debtor_paid, paid_date, br_comm_adjustment, sa_fee_net, sa_comm_net, broker_rep, cob_name, sales_team, service_team, branch, insurer, tran_time)" + Environment.NewLine +
"SELECT ent_id, ent_name, genins_name, pol_parent_id, pol_id, pol_tran_id, pol_total_gross_premium, pol_total_net_premium, adj, acr, debtor_bal, earn_broker_comm, earn_sa_comm, earn_broker_fee, earn_sa_fee, earn_total, spid, earn_gst, broker_comm, broker_fee, total_earn, comm_earned, fees_earned, fees_gst, comm_gst, total_earn_gst, total_earn_net_gst, gst_rate, bal, bal2, sa_payable, pol_amount_remitted_sub, sa_gst, pol_fee_gst, pol_comm_gst, debtor_paid, paid_date, br_comm_adjustment, sa_fee_net, sa_comm_net, broker_rep, cob_name, sales_team, service_team, branch, insurer, tran_time FROM {SOURCE_DATABASE_NAME}.dbo.view_earnings2" + Environment.NewLine;
sqlQuery = sqlQuery.Replace("{SOURCE_DATABASE_NAME}", sourceDatabaseName)
.Replace("{DESTINATION_DATABASE_NAME}", destDatabaseName);
//
// Exec SQL script or write to file
if (manager.IsGeneratedScript)
{
sqlQuery = "USE [" + destDatabaseName + "]" + Environment.NewLine +
"GO" + Environment.NewLine +
sqlQuery;
manager.WriteSQLScript(tableName, sqlQuery, false);
}
else
{
try
{
destDbContext.Database.ExecuteSqlCommand(sqlQuery);
}
catch (Exception excExecScript)
{
LogService.Log.Error("Can not run migration script for " + tableName, excExecScript);
}
}
}
/// <summary>
/// Change format before running automapper
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
/// <param name="obj"></param>
public override void BeforeRunningAutoMapper(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase, object obj)
{
//
// Try to cast type first
DatabaseConversion.CleanerTool.EclipseDataAccess.soa_clauses record = null;
try
{
record = obj as DatabaseConversion.CleanerTool.EclipseDataAccess.soa_clauses;
}
catch (Exception excCastType)
{
LogService.Log.Error("Can not cast type as soa_clauses.", excCastType);
return;
}
//
// Change from rtf format to html format
if (record.nature_of_advice != null)
{
record.nature_of_advice = WordGenerator.ConvertRtfToHtml(record.nature_of_advice);
}
if (record.recommendations != null)
{
record.recommendations = WordGenerator.ConvertRtfToHtml(record.recommendations);
}
if (record.optional_notice1 != null)
{
record.optional_notice1 = WordGenerator.ConvertRtfToHtml(record.optional_notice1);
}
if (record.optional_notice2 != null)
{
record.optional_notice2 = WordGenerator.ConvertRtfToHtml(record.optional_notice2);
}
if (record.optional_notice3 != null)
{
record.optional_notice3 = WordGenerator.ConvertRtfToHtml(record.optional_notice3);
}
}
/// <summary>
/// Manual mapping function for ageing_debtors_v
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
public override void Map(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase)
{
//
// Try to get source database name and destination database name
DbContext sourceDbContext = sourceDatabase.GetDbContext();
DbContext destDbContext = destinationDatabase.GetDbContext();
string sourceDatabaseName = sourceDbContext.Database.SqlQuery<string>(@"SELECT DB_NAME() AS [Current Database]").FirstOrDefault();
string destDatabaseName = destDbContext.Database.SqlQuery<string>(@"SELECT DB_NAME() AS [Current Database]").FirstOrDefault();
string tableName = "ageing_debtors_v";
//
// Generate SQL script
string sqlQuery = @"INSERT INTO {DESTINATION_DATABASE_NAME}.dbo.ageing_debtors_v(bal_entity_id, genins_id, genins_name, genins_class_of_business, gencob_name, bal_tran_id, gentrans_name, pol_insurer, insurer_name, pol_policy_number, tran_type, tran_time, pol_date_effective, gross, balamount, days_between, ent_name, Saltea_ID, saltea_name, Sertea_ID, sertea_name, sa_id, sa_name, last_payment, RCPT_AMOUNT, SPID, debtor_fund_status, BRComm)" + Environment.NewLine +
"SELECT bal_entity_id, genins_id, genins_name, genins_class_of_business, gencob_name, bal_tran_id, gentrans_name, pol_insurer, insurer_name, pol_policy_number, tran_type, tran_time, pol_date_effective, gross, balamount, days_between, ent_name, Saltea_ID, saltea_name, Sertea_ID, sertea_name, sa_id, sa_name, last_payment, RCPT_AMOUNT, SPID, debtor_fund_status, BRComm FROM {SOURCE_DATABASE_NAME}.dbo.ageing_debtors_v" + Environment.NewLine;
sqlQuery = sqlQuery.Replace("{SOURCE_DATABASE_NAME}", sourceDatabaseName)
.Replace("{DESTINATION_DATABASE_NAME}", destDatabaseName);
//
// Exec SQL script or write to file
if (manager.IsGeneratedScript)
{
sqlQuery = "USE [" + destDatabaseName + "]" + Environment.NewLine +
"GO" + Environment.NewLine +
sqlQuery;
manager.WriteSQLScript(tableName, sqlQuery, false);
}
else
{
try
{
destDbContext.Database.ExecuteSqlCommand(sqlQuery);
}
catch (Exception excExecScript)
{
LogService.Log.Error("Can not run migration script for " + tableName, excExecScript);
}
}
}
/// <summary>
/// Main mapping function
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
public override void Map(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase)
{
//
// Try to get source database name and destination database name
DbContext sourceDbContext = sourceDatabase.GetDbContext();
DbContext destDbContext = destinationDatabase.GetDbContext();
string sourceDatabaseName = sourceDbContext.Database.SqlQuery<string>(@"SELECT DB_NAME() AS [Current Database]").FirstOrDefault();
string destDatabaseName = destDbContext.Database.SqlQuery<string>(@"SELECT DB_NAME() AS [Current Database]").FirstOrDefault();
string tableName = "renewal_retention_report";
//
// Generate SQL script
string sqlQuery = @"INSERT INTO {DESTINATION_DATABASE_NAME}.dbo.renewal_retention_report(PolID, TranID, Client, SalesTeam, ServiceTeam, Branch, COB, InsurerName, BrokerName, TransactionType, Paid, PartPaid, UnPaid, Lapsed, UnInvited, saltea_id, sertea_id, gencob_id, ent_id, pol_insurer, br_id, tratyp_id, bra_name, genins_id, genins_name, Rentention, Spid, Premium, Earnings, DueDate)" + Environment.NewLine +
"SELECT PolID, TranID, Client, SalesTeam, ServiceTeam, Branch, COB, InsurerName, BrokerName, TransactionType, Paid, PartPaid, UnPaid, Lapsed, UnInvited, saltea_id, sertea_id, gencob_id, ent_id, pol_insurer, br_id, tratyp_id, bra_name, genins_id, genins_name, Rentention, Spid, Premium, Earnings, DueDate FROM {SOURCE_DATABASE_NAME}.dbo.renewal_retention_report" + Environment.NewLine;
sqlQuery = sqlQuery.Replace("{SOURCE_DATABASE_NAME}", sourceDatabaseName)
.Replace("{DESTINATION_DATABASE_NAME}", destDatabaseName);
//
// Exec SQL script or write to file
if (manager.IsGeneratedScript)
{
sqlQuery = "USE [" + destDatabaseName + "]" + Environment.NewLine +
"GO" + Environment.NewLine +
sqlQuery;
manager.WriteSQLScript(tableName, sqlQuery, false);
}
else
{
try
{
destDbContext.Database.ExecuteSqlCommand(sqlQuery);
}
catch (Exception excExecScript)
{
LogService.Log.Error("Can not run migration script for " + tableName, excExecScript);
}
}
}
/// <summary>
/// Manual mapping function for vims_EarningsDiff
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
public override void Map(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase)
{
//
// Try to get source database name and destination database name
DbContext sourceDbContext = sourceDatabase.GetDbContext();
DbContext destDbContext = destinationDatabase.GetDbContext();
string sourceDatabaseName = sourceDbContext.Database.SqlQuery<string>(@"SELECT DB_NAME() AS [Current Database]").FirstOrDefault();
string destDatabaseName = destDbContext.Database.SqlQuery<string>(@"SELECT DB_NAME() AS [Current Database]").FirstOrDefault();
string tableName = "vims_EarningsDiff";
//
// Generate SQL script
string sqlQuery = @"INSERT INTO {DESTINATION_DATABASE_NAME}.dbo.vims_EarningsDiff(Pol_tran_id, BrRep_EarningWithAdj, Paid, Payable, Liability, Adjustment, BrRep_EarningWithoutAdj, pol_insured, gentrans_name, gencob_Name, short_payment_broker_rep_poriton)" + Environment.NewLine +
"SELECT Pol_tran_id, BrRep_EarningWithAdj, Paid, Payable, Liability, Adjustment, BrRep_EarningWithoutAdj, pol_insured, gentrans_name, gencob_Name, short_payment_broker_rep_poriton FROM {SOURCE_DATABASE_NAME}.dbo.vims_EarningsDiff" + Environment.NewLine;
sqlQuery = sqlQuery.Replace("{SOURCE_DATABASE_NAME}", sourceDatabaseName)
.Replace("{DESTINATION_DATABASE_NAME}", destDatabaseName);
//
// Exec SQL script or write to file
if (manager.IsGeneratedScript)
{
sqlQuery = "USE [" + destDatabaseName + "]" + Environment.NewLine +
"GO" + Environment.NewLine +
sqlQuery;
manager.WriteSQLScript(tableName, sqlQuery, false);
}
else
{
try
{
destDbContext.Database.ExecuteSqlCommand(sqlQuery);
}
catch (Exception excExecScript)
{
LogService.Log.Error("Can not run migration script for " + tableName, excExecScript);
}
}
}
/// <summary>
/// Main mapping function
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
public override void Map(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase)
{
//
// Try to get source database name and destination database name
DbContext sourceDbContext = sourceDatabase.GetDbContext();
DbContext destDbContext = destinationDatabase.GetDbContext();
string sourceDatabaseName = sourceDbContext.Database.SqlQuery<string>(@"SELECT DB_NAME() AS [Current Database]").FirstOrDefault();
string destDatabaseName = destDbContext.Database.SqlQuery<string>(@"SELECT DB_NAME() AS [Current Database]").FirstOrDefault();
string tableName = "PaidInvoices";
//
// Exec sql script to insert data
string sqlQuery = @"INSERT INTO {DESTINATION_DATABASE_NAME}.dbo.PaidInvoices(tritm_tran_id, tritm_amount, Paid, Remaining, tritm_inc_id, DatePaid, spid)" + Environment.NewLine +
"SELECT tritm_tran_id, tritm_amount, Paid, Remaining, tritm_inc_id, DatePaid, spid FROM {SOURCE_DATABASE_NAME}.dbo.PaidInvoices" + Environment.NewLine;
sqlQuery = sqlQuery.Replace("{SOURCE_DATABASE_NAME}", sourceDatabaseName)
.Replace("{DESTINATION_DATABASE_NAME}", destDatabaseName);
//
// Exec SQL script or write to file
if (manager.IsGeneratedScript)
{
sqlQuery = "USE [" + destDatabaseName + "]" + Environment.NewLine +
"GO" + Environment.NewLine +
sqlQuery;
manager.WriteSQLScript(tableName, sqlQuery, false);
}
else
{
try
{
destDbContext.Database.ExecuteSqlCommand(sqlQuery);
}
catch (Exception excExecScript)
{
LogService.Log.Error("Can not run migration script for " + tableName, excExecScript);
}
}
}
/// <summary>
/// Validate data and write to file
/// </summary>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
/// <param name="fileName"></param>
/// <returns></returns>
public bool ValidateData(SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase, string fileName)
{
//
// Initialize
bool result = false;
StreamWriter writer = new StreamWriter(fileName);
//
// Get all table of destination database
// Validate source table, and destination table
List <string> listDestTables = destinationDatabase.GetListTable();
bool validateTable = false;
foreach (string destTable in listDestTables)
{
writer.WriteLine("------------");
Console.WriteLine("------------");
validateTable = this.ValidateData(destTable, sourceDatabase, destinationDatabase, writer);
if (validateTable)
{
writer.WriteLine("Table name : " + destTable + " passed the test.");
Console.WriteLine("Table name : " + destTable + " passed the test.");
}
else
{
writer.WriteLine("Table name : " + destTable + " failed the test.");
Console.WriteLine("Table name : " + destTable + " failed the test.");
result = false;
}
}
//
// Return result
writer.Close();
return(result);
}
/// <summary>
/// Manual mapping function for glsl_Transactions
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
public override void Map(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase)
{
//
// Try to get source database name and destination database name
DbContext sourceDbContext = sourceDatabase.GetDbContext();
DbContext destDbContext = destinationDatabase.GetDbContext();
string sourceDatabaseName = sourceDbContext.Database.SqlQuery<string>(@"SELECT DB_NAME() AS [Current Database]").FirstOrDefault();
string destDatabaseName = destDbContext.Database.SqlQuery<string>(@"SELECT DB_NAME() AS [Current Database]").FirstOrDefault();
string tableName = "glsl_Transactions";
//
// Exec sql script to insert data
string sqlQuery = @"INSERT INTO {DESTINATION_DATABASE_NAME}.dbo.glsl_Transactions(Glsl_id, Glac_Balance, TranDate, TranNumber, TranType, InvNumber, Particulars, TranAmount, Spid, Glsl_balance)" + Environment.NewLine +
"SELECT Glsl_id, Glac_Balance, TranDate, TranNumber, TranType, InvNumber, Particulars, TranAmount, Spid, Glsl_balance FROM {SOURCE_DATABASE_NAME}.dbo.glsl_Transactions" + Environment.NewLine;
sqlQuery = sqlQuery.Replace("{SOURCE_DATABASE_NAME}", sourceDatabaseName)
.Replace("{DESTINATION_DATABASE_NAME}", destDatabaseName);
//
// Exec SQL script or write to file
if (manager.IsGeneratedScript)
{
sqlQuery = "USE [" + destDatabaseName + "]" + Environment.NewLine +
"GO" + Environment.NewLine +
sqlQuery;
manager.WriteSQLScript(tableName, sqlQuery, false);
}
else
{
try
{
destDbContext.Database.ExecuteSqlCommand(sqlQuery);
}
catch (Exception excExecScript)
{
LogService.Log.Error("Can not run migration script for " + tableName, excExecScript);
}
}
}
/// <summary>
/// Manual mapping function for view_DofiReport_Table1
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
public override void Map(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase)
{
//
// Try to get source database name and destination database name
DbContext sourceDbContext = sourceDatabase.GetDbContext();
DbContext destDbContext = destinationDatabase.GetDbContext();
string sourceDatabaseName = sourceDbContext.Database.SqlQuery<string>(@"SELECT DB_NAME() AS [Current Database]").FirstOrDefault();
string destDatabaseName = destDbContext.Database.SqlQuery<string>(@"SELECT DB_NAME() AS [Current Database]").FirstOrDefault();
string tableName = "view_DofiReport_Table1";
//
// Generate SQL script
string sqlQuery = @"INSERT INTO {DESTINATION_DATABASE_NAME}.dbo.view_DofiReport_Table1(spid, apra_authorised_during_reporting_period, apra_authorised_after_reporting_period, apra_authorised_before_reporting_period, lloyd_during_reporting_period, lloyd_after_reporting_period, lloyd_before_reporting_period, ufi_during_reporting_period, ufi_after_reporting_period, ufi_before_reporting_period, broker_during_reporting_period, broker_after_reporting_period, broker_before_reporting_period)" + Environment.NewLine +
"SELECT spid, apra_authorised_during_reporting_period, apra_authorised_after_reporting_period, apra_authorised_before_reporting_period, lloyd_during_reporting_period, lloyd_after_reporting_period, lloyd_before_reporting_period, ufi_during_reporting_period, ufi_after_reporting_period, ufi_before_reporting_period, broker_during_reporting_period, broker_after_reporting_period, broker_before_reporting_period FROM {SOURCE_DATABASE_NAME}.dbo.view_DofiReport_Table1" + Environment.NewLine;
sqlQuery = sqlQuery.Replace("{SOURCE_DATABASE_NAME}", sourceDatabaseName)
.Replace("{DESTINATION_DATABASE_NAME}", destDatabaseName);
//
// Exec SQL script or write to file
if (manager.IsGeneratedScript)
{
sqlQuery = "USE [" + destDatabaseName + "]" + Environment.NewLine +
"GO" + Environment.NewLine +
sqlQuery;
manager.WriteSQLScript(tableName, sqlQuery, false);
}
else
{
try
{
destDbContext.Database.ExecuteSqlCommand(sqlQuery);
}
catch (Exception excExecScript)
{
LogService.Log.Error("Can not run migration script for " + tableName, excExecScript);
}
}
}
/// <summary>
/// Validate data and write to file
/// </summary>
/// <param name="tableName"></param>
/// <param name="destinationDatabase"></param>
/// <param name="writer"></param>
/// <returns></returns>
public bool ValidateData(string tableName, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase, StreamWriter writer)
{
//
// Initialize result
bool result = true;
//
// Get primary key values
Dictionary <string, List <object> > destPKValues = destinationDatabase.GetListPrimaryKeyValue(tableName);
Dictionary <string, List <object> > sourcePKValues = sourceDatabase.GetListPrimaryKeyValue(tableName);
//
// Try to validate number of primary key
// If source table and destination table has different number of primary key
if (destPKValues.Keys.Count != sourcePKValues.Keys.Count)
{
writer.WriteLine("Number of primary keys in source and destination table does not match.");
return(false);
}
//
// Try to validate primary key names
// Source table and destination table must have the same primary key's name
foreach (string destPK in destPKValues.Keys.ToList())
{
if (sourcePKValues.ContainsKey(destPK) == false)
{
writer.WriteLine("Destination table has primary key name : " + destPK + ", which does not exist in source table.");
}
}
if (result == false)
{
return(result);
}
//
// Try to validate data
foreach (string destPK in destPKValues.Keys.ToList())
{
List <object> sourceValues = sourcePKValues[destPK];
List <object> destValues = destPKValues[destPK];
foreach (object obj in destValues)
{
if (sourceValues.Any(x => x.Equals(obj)) == false)
{
writer.WriteLine("Error, this primary key value does not exist in source database : " + obj);
result = false;
}
}
}
//
// Return result
return(result);
}
/// <summary>
/// Function is invoked before commit changes
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
/// <param name="listNewRecord"></param>
public virtual void BeforeCommitChanges(MappingManager manager,
SourceDatabase sourceDatabase,
DestinationDatabase destinationDatabase,
List <object> listNewRecord)
{
}
/// <summary>
/// Before running AutoMapper to map from old class to new class
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
/// <param name="record"></param>
public virtual void BeforeRunningAutoMapper(MappingManager manager,
SourceDatabase sourceDatabase,
DestinationDatabase destinationDatabase,
object record)
{
}
/// <summary>
/// Function is invoked after commit changes
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
public virtual void AfterCommitChanges(MappingManager manager,
SourceDatabase sourceDatabase,
DestinationDatabase destinationDatabase)
{
}
/// <summary>
/// Function is invoked after mapping single record completed
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
/// <param name="record"></param>
public virtual void AfterMappingRecord(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase, object record)
{
}
/// <summary>
/// Function is invoked after mapping completed
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
public virtual void AfterMapping(MappingManager manager, SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase)
{
}