/// <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));
}
}
}
}
public string GenerateBlobFieldUpdateScript()
{
string result = "";
List <FieldMappingDefinition> blobMappings = GetBlobMappings();
if (blobMappings.Any())
{
// Create temp table to store blob pointer
string createTempTableScript = @"CREATE TABLE #Temp (Id int, Value varchar(MAX))";
string insertScript = string.Format(SqlTemplates.INSERT, "#Temp", "[Id], [Value]");
string dropTempTableScript = @"DROP TABLE #Temp";
string truncateTempTableScript = @"TRUNCATE TABLE #Temp";
int emptyBlobsCount = 0;
string script = "";
Field destPK = _definition.DestinationTable.GetPrimaryKey();
blobMappings.ForEach(m =>
{
// Insert blob value into temp table
List <KeyValuePair <int, byte[]> > blobs;
if (!string.IsNullOrEmpty(m.GetBlobScriptPath)) // if explicit sql is provided to get blobs, use it
{
string sql = File.ReadAllText(m.GetBlobScriptPath);
blobs = _definition.SourceTable.GetBlobs(m.SourceField.Name, sql);
}
else
{
blobs = _definition.SourceTable.GetBlobs(m.SourceField.Name);
}
if (blobs.Any())
{
StringBuilder valuesScriptBuilder = new StringBuilder();
if (m.Type == FieldMappingType.BlobToBlobPointer)
{
blobs.ForEach(b =>
{
byte[] data = b.Value;
if (data != null)
{
string blobPointer = BlobConverter.ConvertToFile(m.BlobCategory, m.BlobCategory, data);
valuesScriptBuilder.Append(Environment.NewLine + string.Format("('{0}','{1}')", b.Key, blobPointer) + ",");
}
});
}
else if (m.Type == FieldMappingType.BlobToHtml)
{
blobs.ForEach(b =>
{
byte[] data = b.Value;
if (data != null)
{
string blobHtml = WordGenerator.ConvertRtfToHtml(WordGenerator.ConvertDocxToRTF(data));
valuesScriptBuilder.Append(Environment.NewLine + string.Format("('{0}','{1}')", b.Key, blobHtml) + ",");
}
});
}
string valuesScript = valuesScriptBuilder.ToString().Trim(',');
string insertBlobPointerScript = insertScript + NewLines(1) + string.Format(SqlTemplates.INSERT_VALUES, valuesScript);
// Merge blob pointer into destination table
string mergeBlobPointerScript = SqlTemplates.MERGE_UPDATE.Inject(new
{
TargetTable = _definition.DestinationTable.FullName,
SourceTable = "#Temp",
TargetPK = destPK.Name,
SourcePK = "Id",
TargetField = m.DestinationField.Name,
SourceField = "Value"
});
script += insertBlobPointerScript + NewLines(2) + mergeBlobPointerScript + NewLines(2) + truncateTempTableScript + NewLines(2);
}
else
{
emptyBlobsCount++;
}
});
// Prevent there is no blobs to update
if (emptyBlobsCount < blobMappings.Count)
{
result = createTempTableScript + NewLines(2) + script + dropTempTableScript;
}
}
return(result);
}