protected void btnUpdateCorrections_Click(object sender, EventArgs e)
{
const string sql = @"SELECT [MessageSpec_ID] ,[IsCorrection] , [CBCReport] FROM [FDR].[dbo].[X_MessageSpec] WHERE [CBCReport] IS NOT NULL AND [IsCorrection]= 0";
const string sqlUpdate = @"UPDATE [FDR].[dbo].[X_MessageSpec] SET [IsCorrection] = 1, [OriginalMessageRefId] = @OriginalMessageRefId WHERE [MessageSpec_ID] = @MessageSpec_ID";
using (var data = new RecordSet(sql, QueryType.TransectSQL, null))
{
if (data.HasRows)
{
foreach (DataRow row in data.Tables[0].Rows)
{
var xml = row["CBCReport"].ToString();
var messageSpecId = row["MessageSpec_ID"].ToString();
var cbcXmldoc = new XmlDocument();
cbcXmldoc.LoadXml(xml);
var cbcXmlNodeList = cbcXmldoc.GetElementsByTagName("CBC_OECD", "*");
if (cbcXmlNodeList.Count > 0)
{
var cbcXml = cbcXmlNodeList[0].OuterXml;
var cbcMessage = XmlObjectSerializer.ConvertXmlToObject <CBC_OECD>(cbcXml);
if (cbcMessage == null)
{
continue;
}
var correctedData = 0;
var deletedData = 0;
var docTypeIndics = new List <OECDDocTypeIndic_EnumType>();
string originalMessageRefId = null;
foreach (var cbcBodyType in cbcMessage.CbcBody)
{
if (cbcBodyType.ReportingEntity != null)
{
var docSpec = cbcBodyType.ReportingEntity.DocSpec;
if (docSpec != null)
{
docTypeIndics.Add(docSpec.DocTypeIndic);
if (originalMessageRefId == null)
{
if (docSpec.DocTypeIndic == OECDDocTypeIndic_EnumType.OECD2 ||
docSpec.DocTypeIndic == OECDDocTypeIndic_EnumType.OECD3 ||
docSpec.DocTypeIndic == OECDDocTypeIndic_EnumType.OECD12 ||
docSpec.DocTypeIndic == OECDDocTypeIndic_EnumType.OECD13)
{
originalMessageRefId =
DBReadManager.GetOriginalMessageRefId(docSpec.CorrDocRefId,
cbcMessage.MessageSpec.TransmittingCountry.ToString());
}
}
}
}
if (cbcBodyType.CbcReports != null)
{
foreach (var cbcReport in cbcBodyType.CbcReports)
{
var reportDocSpec = cbcReport.DocSpec;
if (reportDocSpec != null)
{
docTypeIndics.Add(reportDocSpec.DocTypeIndic);
if (originalMessageRefId == null)
{
if (reportDocSpec.DocTypeIndic == OECDDocTypeIndic_EnumType.OECD2 ||
reportDocSpec.DocTypeIndic == OECDDocTypeIndic_EnumType.OECD3 ||
reportDocSpec.DocTypeIndic == OECDDocTypeIndic_EnumType.OECD12 ||
reportDocSpec.DocTypeIndic == OECDDocTypeIndic_EnumType.OECD13)
{
originalMessageRefId =
DBReadManager.GetOriginalMessageRefId(reportDocSpec.CorrDocRefId,
cbcMessage.MessageSpec.TransmittingCountry.ToString());
}
}
}
}
}
if (cbcBodyType.AdditionalInfo != null)
{
foreach (var info in cbcBodyType.AdditionalInfo)
{
var addInfoDocSpec = info.DocSpec;
if (addInfoDocSpec != null)
{
docTypeIndics.Add(addInfoDocSpec.DocTypeIndic);
if (originalMessageRefId == null)
{
if (addInfoDocSpec.DocTypeIndic == OECDDocTypeIndic_EnumType.OECD2 ||
addInfoDocSpec.DocTypeIndic == OECDDocTypeIndic_EnumType.OECD3 ||
addInfoDocSpec.DocTypeIndic == OECDDocTypeIndic_EnumType.OECD12 ||
addInfoDocSpec.DocTypeIndic == OECDDocTypeIndic_EnumType.OECD13)
{
originalMessageRefId =
DBReadManager.GetOriginalMessageRefId(addInfoDocSpec.CorrDocRefId,
cbcMessage.MessageSpec.TransmittingCountry.ToString());
}
}
}
}
}
}
var mixedDocTypeIndicators = docTypeIndics.Distinct().ToList();
foreach (var docTypeInd in mixedDocTypeIndicators)
{
switch (docTypeInd)
{
case OECDDocTypeIndic_EnumType.OECD2:
case OECDDocTypeIndic_EnumType.OECD12:
{
correctedData++;
break;
}
case OECDDocTypeIndic_EnumType.OECD3:
case OECDDocTypeIndic_EnumType.OECD13:
{
deletedData++;
break;
}
}
}
if (deletedData > 0 || correctedData > 0)
{
var oParams = new DBParamCollection
{
{ "@MessageSpec_ID", messageSpecId }
, { "@OriginalMessageRefId", originalMessageRefId ?? "" }
};
using (var command = new DBCommand(sqlUpdate, QueryType.TransectSQL, oParams))
{
command.Execute();
}
}
}
}
}
}
}