private async void ExecuteEditSelectedItemCommand(object o)
{
UserControl view = null;
object editedObject = null;
if (TreeSelectedItem is SourceDatabase)
{
editedObject = new SourceDatabase((SourceDatabase)TreeSelectedItem);
view = new Domain.SourceEditorDialog
{
DataContext = new SourceEditorViewModel()
{
Source = (SourceDatabase)editedObject
}
};
view.Width = 480;
view.Height = 600;
}
else if (TreeSelectedItem is SqlScriptFile)
{
editedObject = new SqlScriptFile((SqlScriptFile)TreeSelectedItem);
view = new ScriptEditor
{
DataContext = new ScriptEditorViewModel()
{
Script = (SqlScriptFile)editedObject
}
};
view.Width = WindowSize.Width * 0.80;
view.Height = WindowSize.Height * 0.75;
}
if (view == null)
{
return;
}
//show the dialog
var result = await MaterialDesignThemes.Wpf.DialogHost.Show(view, "RootDialog");
if (result is bool)
{
if ((bool)result)
{
if (TreeSelectedItem is SourceDatabase)
{
((SourceDatabase)TreeSelectedItem).CopyFrom(editedObject as SourceDatabase);
}
else if (TreeSelectedItem is SqlScriptFile)
{
((SqlScriptFile)TreeSelectedItem).CopyFrom(editedObject as SqlScriptFile);
}
}
}
}
public PublishSchedule(Item item)
{
InnerItem = item;
SchedulerUsername = item[SchedulerUsernameId];
Unpublish = "1" == item[UnpublishId];
PublishChildren = "1" == item[PublishChildrenId];
PublishRelatedItems = "1" == item[PublishRelatedItemsId];
PublishMode = ParseMode(item[PublishModeId]);
IsPublished = "1" == item[IsPublishedId];
if (!string.IsNullOrWhiteSpace(SchedulerUsername))
{
User user = User.FromName(SchedulerUsername, false);
if (user != null && user.Profile != null)
{
SchedulerEmail = user.Profile.Email;
}
}
string sourceDatabaseName = item[SourceDatabaseId];
if (!string.IsNullOrWhiteSpace(sourceDatabaseName))
{
SourceDatabase = Database.GetDatabase(sourceDatabaseName);
}
string targetDatabaseNames = item[TargetDatabasesId];
if (!string.IsNullOrWhiteSpace(targetDatabaseNames))
{
TargetDatabases = targetDatabaseNames.Split('|').Select(Database.GetDatabase);
}
string itemToPublishPath = item[ItemToPublishId];
if (!string.IsNullOrWhiteSpace(itemToPublishPath) && SourceDatabase != null)
{
ItemToPublish = SourceDatabase.GetItem(itemToPublishPath);
}
string dateString = item[PublishDateId];
if (!string.IsNullOrWhiteSpace(dateString))
{
PublishDate = DateUtil.IsoDateToDateTime(dateString, DateTime.MinValue);
}
string languages = item[TargetLanguagesId];
if (!string.IsNullOrWhiteSpace(languages))
{
TargetLanguages = languages.Split('|').Select(LanguageManager.GetLanguage).Where(l => l != null);
}
}
/// <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);
}
}
}
}
public ConversionManager(string srcConnectionString, string destConnectionString, ConversionOption options)
{
_packageOutputPath = Path.Combine(ConfigurationManager.AppSettings["PackageOutputFolder"]);
_options = options;
_destinationDatabase = new DestinationDatabase(destConnectionString);
_destinationDatabase.Initialize();
_sourceDatabase = new SourceDatabase(srcConnectionString);
_sourceDatabase.Initialize();
_sourceDatabase.LearnPrimaryKeys(_destinationDatabase, options.ExplicitTableMappings);
CreatePackageFolders();
}
/// <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>
/// 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.notes record = obj as DatabaseConversion.CleanerTool.EclipseDataAccess.notes;
//
// Run blob pointer converter if needed
if (record.not_object != null && !IgnoreBlobs)
{
string fullPath = BlobConverter.ConvertToFile(BlobStoreCategories.NOTE, BlobStoreCategories.NOTE, record.not_object);
this._temporyFilePath.Add(record.not_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.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);
}
}
public ConversionManager(string srcConnectionString, string destConnectionString, ConversionOption options)
{
_packageOutputPath = Path.Combine(ConfigurationManager.AppSettings["PackageOutputFolder"]);
_options = options;
_destinationDatabase = new DestinationDatabase(destConnectionString);
_destinationDatabase.Initialize();
_sourceDatabase = new SourceDatabase(srcConnectionString);
_sourceDatabase.Initialize();
_sourceDatabase.LearnPrimaryKeys(_destinationDatabase, options.ExplicitTableMappings);
CreatePackageFolders();
}
/// <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.tasks_sub_tasks record = obj as DatabaseConversion.CleanerTool.EclipseDataAccess.tasks_sub_tasks;
//
// Run blob pointer converter if needed
if (record.tassubta_image != null && !IgnoreBlobs)
{
string fullPath = BlobConverter.ConvertToFile(BlobStoreCategories.CLIENT_TASK_DOCUMENT, BlobStoreCategories.CLIENT_TASK_DOCUMENT, record.tassubta_image);
this._temporyFilePath.Add(record.tassubta_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);
}
}
public MigrationManager(string sourceConnectionString, string destinationConnectionString, MigrationOptions options)
{
_options = options;
_sourceDatabase = new SourceDatabase(sourceConnectionString);
_sourceDatabase.Initialize();
_destinationDatabase = new DestinationDatabase(destinationConnectionString);
_destinationDatabase.Initialize();
_sourceDatabase.LearnDestinationDatabaseReference(_destinationDatabase, options.ExplicitTableMappings);
ConfigIgnoreCircleReferences();
}
/// <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>
/// 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.gen_ins_documents record = obj as DatabaseConversion.CleanerTool.EclipseDataAccess.gen_ins_documents;
//
// Run blob pointer converter if needed
if (record.geninsdo_object_eclipse != null && !IgnoreBlobs)
{
string fullPath = BlobConverter.ConvertToFile(BlobStoreCategories.POLICY_TASK_TEMPLATE, BlobStoreCategories.POLICY_TASK_TEMPLATE, record.geninsdo_object_eclipse);
this._temporyFilePath.Add(record.geninsdo_id, fullPath);
}
}
public MigrationManager(string sourceConnectionString, string destinationConnectionString, MigrationOptions options)
{
_options = options;
_sourceDatabase = new SourceDatabase(sourceConnectionString);
_sourceDatabase.Initialize();
_destinationDatabase = new DestinationDatabase(destinationConnectionString);
_destinationDatabase.Initialize();
_sourceDatabase.LearnDestinationDatabaseReference(_destinationDatabase, options.ExplicitTableMappings);
ConfigIgnoreCircleReferences();
}
public async Task <object> AddSource(object input)
{
var Source = new SourceDatabase();
Source.ConnectionString = "Data Source=abismo-net.com, 28760;Initial Catalog=WgmControl ;User ID=WgmControl ;Password=w2JWYJKtd6S#R4p;MultipleActiveResultSets=True; Max Pool Size=100;Connection Timeout=20";
Source.ProviderName = "System.Data.SqlClient";
Source.Type = SourceType.DataSource;
Source.Name = "Production Server";
//if(!await Source.TryConnect())
// return false;
// Source.PopulateTargets();
DocumentManager.Current.Deployment.Sources.Add(Source);
return(DocumentManager.Current);
}
/// <summary>
/// Manual function before mapping record
/// </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 record)
{
//
// Because this table has self reference
// Column bra_parent_branches references to bra_id column
// So at first, we need to set bra_parent_branch to null
// After moving all record from Branches table in source to destination
// We will try update reference later
BOALedgerDataAccess.branches branchesRecord = record as BOALedgerDataAccess.branches;
if (branchesRecord.bra_parent_branch != null)
{
this._listReferenceValue.Add(branchesRecord.bra_id, branchesRecord.bra_parent_branch);
branchesRecord.bra_parent_branch = null;
}
}
/// <summary>
/// Create auto mapper thread
/// </summary>
/// <param name="manager"></param>
/// <param name="multiThreadMapper"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
/// <param name="databaseSentinel"></param>
/// <param name="sourceTableName"></param>
/// <param name="destTableName"></param>
/// <param name="sourceTableType"></param>
/// <param name="destTableType"></param>
/// <param name="manualMapping"></param>
public AutoMapperThread(MappingManager manager,
MultiThreadManager multiThreadMapper,
SourceDatabase sourceDatabase, DestinationDatabase destinationDatabase, DatabaseSentinel databaseSentinel,
string sourceTableName, string destTableName, Type sourceTableType, Type destTableType, ManualMapping manualMapping)
{
this._multiThreadMapper = multiThreadMapper;
this._sourceDatabase = sourceDatabase;
this._destinationDatabase = destinationDatabase;
this._databaseSentinel = databaseSentinel;
this._sourceTableName = sourceTableName;
this._destTableType = destTableType;
this._sourceTableType = sourceTableType;
this._destTableName = destTableName;
this._manualMapping = manualMapping;
}
/// <summary>
/// Create multi threading mapper
/// </summary>
/// <param name="manager"></param>
/// <param name="sourceDatabase"></param>
/// <param name="destinationDatabase"></param>
/// <param name="databaseSentinel"></param>
/// <param name="maxThread"></param>
/// <param name="minTaskForThread"></param>
public MultiThreadManager(MappingManager manager,
SourceDatabase sourceDatabase,
DestinationDatabase destinationDatabase,
DatabaseSentinel databaseSentinel,
int maxThread,
int minTaskForThread)
{
this._manager = manager;
this._sourceDatabase = sourceDatabase;
this._destinationDatabase = destinationDatabase;
this._databaseSentinel = databaseSentinel;
this._maxSubThread = maxThread;
this._minTaskForThread = minTaskForThread;
this._locker = new object();
}
/// <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();
}
}
/// <inheritdoc />
public virtual IDbCommand GetLastInsertedID_Cmd(IDbConnection conn)
{
if (SourceDatabase.HasFlagFast(DbAccessType.MsSql))
{
return(CreateCommand("SELECT SCOPE_IDENTITY() as Value", conn));
}
if (SourceDatabase.HasFlagFast(DbAccessType.SqLite))
{
return(CreateCommand("SELECT last_insert_rowid() as Value", conn));
}
if (SourceDatabase.HasFlagFast(DbAccessType.MySql))
{
return(CreateCommand("SELECT LAST_INSERT_ID();", conn));
}
throw new NotImplementedException($"The GetLastInsertedId ist not implemented for the mixed type of '{SourceDatabase}' please overwrite this function to provide an query.");
}
public void Manage()
{
SourceDatabase.cerealObject().ApplyModifiedProperties();
if (firstManage)
{
if (DataAttribute.AutoGenerate)
{
GenerateFromTypes(DataAttribute.ScriptBaseType);
}
firstManage = false;
}
DataItem[] data = Data;
for (int i = 0; i < data.Length; i++)
{
data[i].Manage();
}
Apply();
}
/// <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>
/// 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>
/// 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 = "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 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>
/// Manual mapping function for view_DofiReport_Table2
/// </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_Table2";
//
// Generate SQL script
string sqlQuery = @"INSERT INTO {DESTINATION_DATABASE_NAME}.dbo.view_DofiReport_Table2(spid, gentrans_name, genins_parent_id, pol_tran_id, pol_posted_when, pol_date_effective, apracob_name, merged_base_premium, ent_name, limex_name, uficoco_code, extyp_name, merged_insurer_id, hvi_value)" + Environment.NewLine +
"SELECT spid, gentrans_name, genins_parent_id, pol_tran_id, pol_posted_when, pol_date_effective, apracob_name, merged_base_premium, ent_name, limex_name, uficoco_code, extyp_name, merged_insurer_id, hvi_value FROM {SOURCE_DATABASE_NAME}.dbo.view_DofiReport_Table2" + 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_inspay
/// </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_inspay";
//
// Exec sql script to insert data
string sqlQuery = @"INSERT INTO {DESTINATION_DATABASE_NAME}.dbo.view_inspay(pol_id, pol_parent_id, pol_tran_id, pol_transaction_type, pol_policy_number, pol_date_from, pol_date_effective, pol_date_to, pol_date_due, pol_debtor, pay_insurer_id, pol_sub_agent, pol_total_gross_premium, pol_total_net_premium, pay_gross_premium, pay_commission, pay_net_premium, pay_debtor_bal, pay_debtor_adj, pay_insurer_bal, pay_insurer_adj, pay_payable, spid)" + Environment.NewLine +
"SELECT pol_id, pol_parent_id, pol_tran_id, pol_transaction_type, pol_policy_number, pol_date_from, pol_date_effective, pol_date_to, pol_date_due, pol_debtor, pay_insurer_id, pol_sub_agent, pol_total_gross_premium, pol_total_net_premium, pay_gross_premium, pay_commission, pay_net_premium, pay_debtor_bal, pay_debtor_adj, pay_insurer_bal, pay_insurer_adj, pay_payable, spid FROM {SOURCE_DATABASE_NAME}.dbo.view_inspay" + 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_earnings
/// </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_earnings";
//
// Generate SQL script
string sqlQuery = @"INSERT INTO {DESTINATION_DATABASE_NAME}.dbo.view_earnings(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)" + 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 FROM {SOURCE_DATABASE_NAME}.dbo.view_earnings" + 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>
/// Manual mapping function for vims_unallocated_csh_credits
/// </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_unallocated_csh_credits";
//
// Generate SQL script
string sqlQuery = @"INSERT INTO {DESTINATION_DATABASE_NAME}.dbo.vims_unallocated_csh_credits(ent_id, insured_name, prof_sales_team, prof_service_team, insurer, sub_agent, bal_tran_id, tran_time, tran_type, particulars, bal_amount, sertea_name, saltea_name, insurer_name, sub_agent_name, spid)" + Environment.NewLine +
"SELECT ent_id, insured_name, prof_sales_team, prof_service_team, insurer, sub_agent, bal_tran_id, tran_time, tran_type, particulars, bal_amount, sertea_name, saltea_name, insurer_name, sub_agent_name, spid FROM {SOURCE_DATABASE_NAME}.dbo.vims_unallocated_csh_credits" + 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);
}
}
}
public TableScriptGenerator(SourceDatabase sourceDatabase, TableMappingDefinition definition)
{
_sourceDatabase = sourceDatabase;
_definition = definition;
}
