/// <summary>
/// The method OpenConnections() should be called before attempting any
/// call to GetReconData. A list of datatables will be returned, with the
/// first entry being the data for the first query, and if there is a second query
/// then the second entry will be its datatable.
///
/// When done CloseConnections() should be called.
/// </summary>
/// <param name="recon"></param>
/// <returns></returns>
public List <DataTable> GetReconData(ReconReport recon)
{
if (sources == null)
{
throw new Exception("RRDataService: Sources not provided yet");
}
var reconData = new List <DataTable>();
// UPDATED 13 Aug 2015 ejy
// Now only pass queryvariables that are either specific to the query being run
// or are set to be non-query-specific
// Get data for first query
var firstQuery = sources.Queries.Single(query => query.Name == recon.FirstQueryName);
var queryVariables = (from queryVariable in recon.QueryVariables
where queryVariable.QueryNumber == 1 || !queryVariable.QuerySpecific
select queryVariable).ToList();
reconData.Add(getQueryDataTable(firstQuery, queryVariables));
// If necessary, get data for second query
if (recon.SecondQueryName != "")
{
var secondQuery = sources.Queries.Single(query => query.Name == recon.SecondQueryName);
queryVariables = (from queryVariable in recon.QueryVariables
where queryVariable.QueryNumber == 2 || !queryVariable.QuerySpecific
select queryVariable).ToList();
reconData.Add(getQueryDataTable(secondQuery, queryVariables));
}
return(reconData);
}
/// <summary>
/// Use to make a deep copy of a ReconReport
/// </summary>
/// <param name="reconReport">A ReconReport object to be copied</param>
/// <returns>A deep copy of the ReconReport object provided</returns>
public ReconReport CopyReconReport(ReconReport reconReport)
{
using (MemoryStream stream = new MemoryStream())
{
XmlSerializer serializer = new XmlSerializer(typeof(ReconReport));
serializer.Serialize(stream, reconReport);
stream.Position = 0;
return((ReconReport)serializer.Deserialize(stream));
}
}
/// <summary>
/// Use to make a deep copy of a ReconReport
/// </summary>
/// <param name="reconReport">A ReconReport object to be copied</param>
/// <returns>A deep copy of the ReconReport object provided</returns>
public ReconReport CopyReconReport(ReconReport reconReport)
{
using (MemoryStream stream = new MemoryStream())
{
XmlSerializer serializer = new XmlSerializer(typeof(ReconReport));
serializer.Serialize(stream, reconReport);
stream.Position = 0;
return (ReconReport)serializer.Deserialize(stream);
}
}
/// <summary>
/// Look at one or two connections required by the recon report. If they're already
/// present in openConnections then do nothing, otherwise create a connection and add it
/// to the collection.
/// </summary>
/// <param name="rr">A recon report</param>
private void openReconConnections(ReconReport rr)
{
// Open connection for first query
try
{
var firstConnection = sources.ConnectionStrings.Where(cs => cs.Name == sources.Queries.Where(query => query.Name == rr.FirstQueryName).First().ConnStringName).First();
openConnection(firstConnection);
if (rr.SecondQueryName != "")
{
var secondConnection = sources.ConnectionStrings.Where(cs => cs.Name == sources.Queries.Where(query => query.Name == rr.SecondQueryName).First().ConnStringName).First();
openConnection(secondConnection);
}
}
catch (Exception ex)
{
throw new Exception(string.Format("Error while trying to open connection for report {0}: {1}", rr.Name, getFullErrorMessage(ex)));
}
}
/// <summary>
/// Prepares the data for writing to spreadsheet for 2-query recons by creating a key for each datarow
/// that is created by concatenating the values for any columns designated as identity columns. Not necessary
/// for single query recons.
/// </summary>
/// <param name="recon"></param>
/// <param name="q1Data"></param>
/// <param name="q2Data"></param>
private void populateIndexedRowCollections(ReconReport recon, DataTable q1Data, DataTable q2Data)
{
string rowKey;
q1Rows = new Dictionary<string, DataRow>();
q2Rows = new Dictionary<string, DataRow>();
try
{
foreach (DataRow row in q1Data.Rows)
{
rowKey = "";
foreach (QueryColumn column in recon.Columns)
{
if (column.IdentifyingColumn)
{
rowKey += row[column.FirstQueryColName].ToString();
}
}
q1Rows.Add(rowKey, row);
}
}
catch (Exception ex)
{
throw new Exception("Error (most likely identifying columns not sufficient to guarantee uniqueness) while preparing first query data: " + GetFullErrorMessage(ex));
}
try
{
foreach (DataRow row in q2Data.Rows)
{
rowKey = "";
foreach (QueryColumn column in recon.Columns)
{
if (column.IdentifyingColumn)
{
rowKey += row[column.SecondQueryColName].ToString();
}
}
q2Rows.Add(rowKey, row);
}
}
catch (Exception ex)
{
throw new Exception("Error (most likely identifying columns not sufficient to guarantee uniqueness) while preparing second query data: " + GetFullErrorMessage(ex));
}
}
/// <summary>
/// Take one or two queries' data and create the corresponding tab in the spreadsheet. For recon involving
/// two queries it will correlate the two rows and create a tab with header plus 3 sections. If a single query
/// report (recon has null second query) then it will just create a tab with header and 1 section.
/// </summary>
/// <param name="recon"></param>
/// <param name="firstQueryData"></param>
/// <param name="secondQueryData">May pass empty datatable for single query recons</param>
private void createReconTab(ReconReport recon, DataTable firstQueryData, DataTable secondQueryData)
{
int numQueries = recon.SecondQueryName == "" ? 1 : 2;
if (recon.SecondQueryName != "")
populateIndexedRowCollections(recon, firstQueryData, secondQueryData);
// Create a new worksheet within our spreadsheet to hold the results
rrExcelService.CreateWorksheet(recon.TabLabel);
// Write the title rows
writeWorkSheetHeaderRows(recon);
// The next two sections are only needed for two-query recons
if (recon.SecondQueryName != "")
{
// Write the first section showing rows in the first query without a match in the second query
writeOrphanFirstQuerySection(recon);
rrExcelService.AddBlankRow();
rrExcelService.AddBlankRow();
// Write the second section that shows rows in the second query without a match in the first query
writeOrphanSecondQuerySection(recon);
rrExcelService.AddBlankRow();
rrExcelService.AddBlankRow();
}
// Create the section of the report that either shows all the rows returned by a single query recon, or
// is the comparison section of a 2-query recon that shows any pairs of records that have differing values
// in one or more columns that are supposed to match
writeDataProblems(recon, firstQueryData, secondQueryData);
}
private void getReportData(ReconReport recon)
{
var reconAndData = new ReconReportAndData(recon);
sendActionStatus(this, RequestState.Information, string.Format("Getting data for recon {0}.", recon.Name), false);
var reconData = rrDataService.GetReconData(recon);
reconAndData.FirstQueryData = reconData[0];
if (recon.SecondQueryName != "")
reconAndData.SecondQueryData = reconData[1];
reconReportsAndData.Add(reconAndData);
sendActionStatus(this, RequestState.Information, string.Format("Finished getting data for recon {0}.", recon.Name), false);
}
/// <summary>
/// Write the title and subtitle rows plus a blank row to the current worksheet.
/// Header rows will vary based on whether this is a one or two query recon.
/// </summary>
/// <param name="recon">The recon report we're processing</param>
private void writeWorkSheetHeaderRows(ReconReport recon)
{
var firstQueryName = recon.FirstQueryName == recon.SecondQueryName ? recon.FirstQueryName + " (1)" : recon.FirstQueryName;
var secondQueryName = recon.FirstQueryName == recon.SecondQueryName ? recon.SecondQueryName + " (2)" : recon.SecondQueryName;
// Main title
excelRow = new Dictionary<string, Cell>();
excelRow.Add("A", new Cell(recon.Name, CellStyle.Bold));
rrExcelService.AddRow(excelRow);
// Sub-title row with names of queries
excelRow.Clear();
if (recon.SecondQueryName != "")
{
excelRow.Add("A", new Cell("Comparing"));
excelRow.Add("B", new Cell(firstQueryName));
excelRow.Add("C", new Cell("to"));
excelRow.Add("D", new Cell(secondQueryName));
}
else
{
excelRow.Add("A", new Cell("Rows returned by"));
excelRow.Add("B", new Cell(firstQueryName));
}
rrExcelService.AddRow(excelRow);
// Write row with query variable values
excelRow.Clear();
excelRow.Add("A", new Cell("Query variables:"));
var currColumnIndex = 1;
if (recon.QueryVariables.Count > 0)
{
recon.QueryVariables.ForEach(queryVar =>
{
var queryVarName = queryVar.QuerySpecific ? string.Format("{0} ({1})", queryVar.SubName, queryVar.QueryNumber) : queryVar.SubName;
var currColumn = columnLetters[currColumnIndex].ToString();
excelRow.Add(currColumn, new Cell(string.Format("{0}: {1}", queryVarName, queryVar.SubValue)));
++currColumnIndex;
});
}
else
excelRow.Add("B", new Cell("None"));
rrExcelService.AddRow(excelRow);
// Sub-title row with key to column header names
// Only add if two-query recon
// The header text for matched column will vary depending on whether any non-zero tolerances exist
// If none, just (M) = Matched Column, else (M x) = Matched column with tolerance of x
if (secondQueryName != "")
{
string matchedColumnText = string.Empty;
if (recon.Columns.FindAll(col => col.Tolerance > 0).Count() == 0)
matchedColumnText = "(M) = Matched Column";
else
matchedColumnText = "(M x) = Matched Column and Tolerance";
excelRow.Clear();
excelRow.Add("A", new Cell("(Id) = Part of Unique ID", CellStyle.Italic));
excelRow.Add("B", new Cell("(1) = 1st Query", CellStyle.Italic));
excelRow.Add("C", new Cell("(2) = 2nd Query", CellStyle.Italic));
excelRow.Add("D", new Cell(matchedColumnText, CellStyle.Italic));
rrExcelService.AddRow(excelRow);
/* ***** RESUME HERE: Add tolerance label to column headers for query 1/2 orphan sections ***** */
}
// Spacer row
rrExcelService.AddBlankRow();
}
public ReconReport CopyReconReport(ReconReport reconReport)
{
return getNewFileService().CopyReconReport(reconReport);
}
public ReconReportAndData(ReconReport recon)
{
reconReport = recon;
}
/// <summary>
/// Creates the section of the recon that shows rows in the second query without a match found
/// in the first query.
/// </summary>
/// <param name="recon"></param>
private void writeOrphanSecondQuerySection(ReconReport recon)
{
CellStyle currStyle = CellStyle.LightBlue;
int orphanCounter = 0;
string counterText;
setReconQueryLabels(recon);
// Start the section with a label
excelRow = new Dictionary<string, Cell>();
excelRow.Add("A", new Cell("Rows in ", CellStyle.Bold));
excelRow.Add("B", new Cell(secondQueryLabel, CellStyle.Bold));
excelRow.Add("C", new Cell("Without a Match in", CellStyle.Bold));
excelRow.Add("D", new Cell(firstQueryLabel, CellStyle.Bold));
rrExcelService.AddRow(excelRow);
// Now write out the common columns at the left of each section
writeCommonHeaders(2, recon.Columns, true, true);
// There are no additional columns for this section, so now write out the identifying and 'always display' columns
// in the first query that don't have a match in the second query.
foreach (string rowKey in q2Rows.Keys)
{
if (!q1Rows.ContainsKey(rowKey))
{
// Toggle background color of the row being written
currStyle = toggleStyle(currStyle);
// We have a row in the first query without a match in the second
writeRowCommonColumns(q2Rows[rowKey], recon.Columns, reportSection.SecondQueryRowWithoutMatch, currStyle);
++orphanCounter;
}
}
excelRow.Clear();
if (orphanCounter == 1)
{
counterText = orphanCounter.ToString() + " orphan found.";
}
else
{
counterText = orphanCounter.ToString() + " orphans found.";
}
excelRow.Add("A", new Cell(counterText));
rrExcelService.AddRow(excelRow);
}
private void setReconQueryLabels(ReconReport recon)
{
if (recon.FirstQueryName != recon.SecondQueryName)
{
firstQueryLabel = recon.FirstQueryName;
secondQueryLabel = recon.SecondQueryName;
}
else
{
firstQueryLabel = recon.FirstQueryName + " (1)";
secondQueryLabel = recon.SecondQueryName + " (2)";
}
}
/// <summary>
/// The method OpenConnections() should be called before attempting any
/// call to GetReconData. A list of datatables will be returned, with the
/// first entry being the data for the first query, and if there is a second query
/// then the second entry will be its datatable.
///
/// When done CloseConnections() should be called.
/// </summary>
/// <param name="recon"></param>
/// <returns></returns>
public List<DataTable> GetReconData(ReconReport recon)
{
if (sources == null)
throw new Exception("RRDataService: Sources not provided yet");
var reconData = new List<DataTable>();
// UPDATED 13 Aug 2015 ejy
// Now only pass queryvariables that are either specific to the query being run
// or are set to be non-query-specific
// Get data for first query
var firstQuery = sources.Queries.Single(query => query.Name == recon.FirstQueryName);
var queryVariables = (from queryVariable in recon.QueryVariables
where queryVariable.QueryNumber == 1 || !queryVariable.QuerySpecific
select queryVariable).ToList();
reconData.Add(getQueryDataTable(firstQuery, queryVariables));
// If necessary, get data for second query
if (recon.SecondQueryName != "")
{
var secondQuery = sources.Queries.Single(query => query.Name == recon.SecondQueryName);
queryVariables = (from queryVariable in recon.QueryVariables
where queryVariable.QueryNumber == 2 || !queryVariable.QuerySpecific
select queryVariable).ToList();
reconData.Add(getQueryDataTable(secondQuery, queryVariables));
}
return reconData;
}
private void populateSampleReconObjects()
{
sampleSources = new Sources();
sampleRecons = new Recons();
// Create sample objects to use to populate sample Sources and RRReports XML files
// for user to use as basis for real recons.
// Create RRSourcesSample.xml
// Contains sample connection template, connection, and query objects in SampleRRSources
// Sample Connection String Templates
var sqlServerTemplate = new ConnectionStringTemplate();
sqlServerTemplate.Name = "SQL Server";
sqlServerTemplate.Template = "Provider=SQLNCLI11;Server=|ServerName|;Database=|DatabaseName|;Trusted_Connection=yes";
var teradataTemplate = new ConnectionStringTemplate();
teradataTemplate.Name = "Teradata";
teradataTemplate.Template = "Data Source=|ServerName|;User ID=|User|;Password=|Password|;Session Mode=TERADATA;";
var oracleTemplate = new ConnectionStringTemplate();
oracleTemplate.Name = "Oracle";
oracleTemplate.Template = "Provider=MSDAORA.1;Data Source=|TNSName|;User Id=|User|;Password=|Password|;Persist Security Info=false;";
sampleSources.ConnStringTemplates.Add(sqlServerTemplate);
sampleSources.ConnStringTemplates.Add(teradataTemplate);
sampleSources.ConnStringTemplates.Add(oracleTemplate);
// Sample Connection Strings
var warehouseConnectionString = new RRConnectionString();
warehouseConnectionString.Name = "DataWarehouse Dev";
warehouseConnectionString.TemplateName = "SQL Server";
warehouseConnectionString.DatabaseType = DatabaseType.SQLServer;
warehouseConnectionString.TemplateVariables = new List<Variable>
{
new Variable {SubName="ServerName", SubValue="bos-dbdevwh02"},
new Variable {SubName="DatabaseName", SubValue="DataWarehouse" }
};
var dalUatConnectionString = new RRConnectionString();
dalUatConnectionString.Name = "Teradata UAT";
dalUatConnectionString.TemplateName = "Teradata";
dalUatConnectionString.DatabaseType = DatabaseType.Teradata;
dalUatConnectionString.TemplateVariables = new List<Variable>
{
new Variable {SubName="ServerName", SubValue="tddevbos"},
new Variable {SubName="User", SubValue="DAL_READ"},
new Variable {SubName="Password", SubValue="DAL_READ"}
};
var edmUatConnectionString = new RRConnectionString();
edmUatConnectionString.Name = "EDM UAT";
edmUatConnectionString.TemplateName = "Oracle";
edmUatConnectionString.DatabaseType = DatabaseType.Oracle;
edmUatConnectionString.TemplateVariables = new List<Variable>
{
new Variable {SubName="TNSName", SubValue="EDM_T"},
new Variable {SubName="User", SubValue="edm_read"},
new Variable {SubName="Password", SubValue="edm_read"}
};
sampleSources.ConnectionStrings.Add(dalUatConnectionString);
sampleSources.ConnectionStrings.Add(edmUatConnectionString);
sampleSources.ConnectionStrings.Add(warehouseConnectionString);
// Sample Queries
var doc = new XmlDocument();
var portGicsSectorDetail = new RRQuery();
portGicsSectorDetail.Name = "PortGicsSectorDetails";
portGicsSectorDetail.ConnStringName = "DataWarehouse Dev";
portGicsSectorDetail.SQL = doc.CreateCDataSection(@"with latestAttrInsEffDate as
(
select
atins_id,
max(effect_date) LatestRowDate
from warehouse.attr_instrument
where effect_date <= '|DateWanted|'
group by atins_id
),
latestAttrIns as
(
select
laied.atins_id,
laied.LatestRowDate,
max(ai.atins_release_number) LatestReleaseNumber
from latestAttrInsEffDate laied
join warehouse.attr_instrument ai
on laied.atins_id = ai.atins_id
and laied.LatestRowDate = ai.effect_date
group by laied.atins_id, laied.LatestRowDate)
select
bigs.atptf_id,
p.portf_name,
bigs.calcul_date,
bigs.atins_id,
ai.atins_name,
ai.effect_date,
ai.end_valid_date,
ai.atins_release_number
from warehouse.bisamattributions_gics_sec bigs
join warehouse.portfolio p
on bigs.atptf_id = |PortId|
and bigs.atins_id <> 0
and bigs.calcul_date = '|DateWanted|'
and bigs.dateinactive = '31-dec-9999'
and bigs.atptf_id = p.portf_id
join latestAttrIns lai
on bigs.atins_id = lai.atins_id
and bigs.atins_release_number = lai.LatestReleaseNumber
join warehouse.attr_instrument ai
on lai.atins_id = ai.atins_id
and lai.LatestRowDate = ai.effect_date
and lai.LatestReleaseNumber = ai.ATINS_RELEASE_NUMBER
order by atins_name");
var portAttribGroupcheck = new RRQuery();
portAttribGroupcheck.Name = "PortAttribGroupCheck";
portAttribGroupcheck.ConnStringName = "DataWarehouse Dev";
portAttribGroupcheck.SQL = doc.CreateCDataSection(@"with portAttributionsRuns as (
select
max(checksum(atptf_id, atins_id, atins_release_number, calcul_date, frequency_id, begin_date, portf_config_id)) portAttribRunKey,
atptf_id,
atins_id,
atins_release_number,
calcul_date,
frequency_id,
begin_date,
portf_config_id,
user_defined_group_id
from warehouse.bisamAttributions_|SegmentTableName| bigs
where atptf_id = |PortId| and atins_id <> 0 and dateinactive = '31-dec-9999'
group by atptf_id, atins_id, atins_release_number, calcul_date, frequency_id, begin_date, portf_config_id, user_defined_group_id),
portMultiGroupCount as (
select
portAttribRunkey,
atptf_id,
atins_id,
atins_release_number,
calcul_date,
frequency_id,
begin_date,
portf_config_id
from portAttributionsRuns
group by portAttribRunKey, atptf_id, atins_id, atins_release_number, calcul_date, frequency_id, begin_date, portf_config_id
having count(*) > 1
)
select par.*, p.portf_name
from portAttributionsRuns par
join portMultiGroupCount pmgc
on par.portAttribRunKey = pmgc.portAttribRunKey
join warehouse.portfolio p
on par.atptf_id = p.portf_id
order by par.atins_id, par.atins_release_number, par.calcul_date, par.FREQUENCY_ID, par.begin_date, user_defined_group_id");
var edmQuery = new RRQuery();
edmQuery.Name = "EdmProductDayPositions";
edmQuery.ConnStringName = "EDM UAT";
edmQuery.SQL = doc.CreateCDataSection("select fm.snapshot_id, trim(fm.entity_id) entity_id, pd.security_alias, trim(pd.long_short_indicator) long_short_indicator, pd.market_value_base, pd.market_value_local from datamartdbo.fund_master fm join datamartdbo.position_details pd on fm.entity_id = '|ProductId|' and fm.effective_date = '|EdmDateWanted|' and fm.dmart_fund_id = pd.dmart_fund_id");
var teraQuery = new RRQuery();
teraQuery.Name = "DalProductDayPositions";
teraQuery.ConnStringName = "Teradata UAT";
teraQuery.SQL = doc.CreateCDataSection("select fp.snapshotid, dp.productid, dp.entitylongname, fp.OrigSecurityId, fp.LongShortIndicator, fp.marketvaluebase, fp.marketvaluelocal from dimproduct dp join factposition fp on dp.productid = '|ProductId|' and dp.dimproductid = fp.dimproductid and fp.dimtimeid = |DalDateWanted|");
var posFkQuery = new RRQuery();
posFkQuery.Name = "DalPositionsMissingFk";
posFkQuery.ConnStringName = "Teradata UAT";
posFkQuery.SQL = doc.CreateCDataSection("select * from (select dimtimeid, case when dimsecurityid = 'UNKNOWN' and dimproductid = 'UNKNOWN' then 'BOTH' when dimsecurityid = 'UNKNOWN' then 'SECURITY' else 'PRODUCT' end MissingEntity, case when dimsecurityid = 'UNKNOWN' and dimproductid = 'UNKNOWN' then 'Security ID: ' || OrigSecurityId || '; Product ID: ' || OrigProductId when dimsecurityid = 'UNKNOWN' then OrigSecurityId else OrigProductId end MissingEntityId from factposition where (dimsecurityid = 'UNKNOWN' or dimproductid = 'UNKNOWN') and dimtimeid >= 1131101) data group by MissingEntity, MissingEntityId, DimTimeId order by MissingEntity, MissingEntityId, DimTimeId");
sampleSources.Queries.Add(portGicsSectorDetail);
sampleSources.Queries.Add(portAttribGroupcheck);
sampleSources.Queries.Add(edmQuery);
sampleSources.Queries.Add(teraQuery);
sampleSources.Queries.Add(posFkQuery);
// Create RRReportsSample.xml
// Holds sample recon report definitions along with substitution values to use
// in sample queries.
// first recon report and write to file to create file to use as basis to create
// full XML file specifying all the recon reports to run.
// First two recons are examples of comparing two data sets
// Acadian-specific example (Note: the actual example is not necessarily useful,
// it's just being done to show how the process works)
// This recon uses the same query twice, so the query variables are query specific
ReconReport warehousePortGicsRecon = new ReconReport();
warehousePortGicsRecon.Name = "Compare GICS Sec Details for 2 days for 1 portfolio";
warehousePortGicsRecon.TabLabel = "GICS Sec Changes";
warehousePortGicsRecon.FirstQueryName = "PortGicsSectorDetails";
warehousePortGicsRecon.SecondQueryName = "PortGicsSectorDetails";
QueryColumn portfolioId = new QueryColumn();
portfolioId.Label = "Portfolio ID";
portfolioId.Type = ColumnType.number;
portfolioId.IdentifyingColumn = true;
portfolioId.AlwaysDisplay = true;
portfolioId.FirstQueryColName = "atptf_id";
portfolioId.SecondQueryColName = "atptf_id";
QueryColumn portfolioName = new QueryColumn();
portfolioName.Label = "Portfolio Name";
portfolioName.Type = ColumnType.text;
portfolioName.IdentifyingColumn = false;
portfolioName.AlwaysDisplay = true;
portfolioName.FirstQueryColName = "portf_name";
portfolioName.SecondQueryColName = "portf_name";
QueryColumn calcDate = new QueryColumn();
calcDate.Label = "Calcul Date";
calcDate.Type = ColumnType.date;
calcDate.IdentifyingColumn = false;
calcDate.AlwaysDisplay = true;
calcDate.FirstQueryColName = "calcul_date";
calcDate.SecondQueryColName = "calcul_date";
QueryColumn instrumentId = new QueryColumn();
instrumentId.Label = "Instrument Id";
instrumentId.Type = ColumnType.number;
instrumentId.IdentifyingColumn = true;
instrumentId.AlwaysDisplay = true;
instrumentId.FirstQueryColName = "atins_id";
instrumentId.SecondQueryColName = "atins_id";
QueryColumn instrumentName = new QueryColumn();
instrumentName.Label = "Instrument Name";
instrumentName.Type = ColumnType.text;
instrumentName.IdentifyingColumn = false;
instrumentName.AlwaysDisplay = true;
instrumentName.FirstQueryColName = "atins_name";
instrumentName.SecondQueryColName = "atins_name";
QueryColumn instrumentReleaseNum = new QueryColumn();
instrumentReleaseNum.Label = "Release #";
instrumentReleaseNum.Type = ColumnType.number;
instrumentReleaseNum.IdentifyingColumn = false;
instrumentReleaseNum.AlwaysDisplay = false;
instrumentReleaseNum.CheckDataMatch = true;
instrumentReleaseNum.Tolerance = 1;
instrumentReleaseNum.FirstQueryColName = "atins_release_number";
instrumentReleaseNum.SecondQueryColName = "atins_release_number";
warehousePortGicsRecon.Columns.Add(portfolioId);
warehousePortGicsRecon.Columns.Add(portfolioName);
warehousePortGicsRecon.Columns.Add(calcDate);
warehousePortGicsRecon.Columns.Add(instrumentId);
warehousePortGicsRecon.Columns.Add(instrumentName);
warehousePortGicsRecon.Columns.Add(instrumentReleaseNum);
warehousePortGicsRecon.QueryVariables = new List<QueryVariable>
{
new QueryVariable { SubName = "PortId", SubValue = "1003022", QuerySpecific=false },
new QueryVariable { SubName = "DateWanted", SubValue = "30-jul-2015", QuerySpecific=true, QueryNumber=1 },
new QueryVariable { SubName = "DateWanted", SubValue = "31-jul-2015",QuerySpecific=true, QueryNumber=2 }
};
ReconReport instMultGroupsRecon = new ReconReport();
instMultGroupsRecon.Name = "Instruments with Multiple Attribution Groups for Similar Runs";
instMultGroupsRecon.TabLabel = "Multi-group Inst";
instMultGroupsRecon.FirstQueryName = "PortAttribGroupCheck";
instMultGroupsRecon.SecondQueryName = string.Empty;
portfolioId = new QueryColumn();
portfolioId.Label = "Portfolio ID";
portfolioId.Type = ColumnType.number;
portfolioId.AlwaysDisplay = true;
portfolioId.FirstQueryColName = "atptf_id";
portfolioName = new QueryColumn();
portfolioName.Label = "Portfolio Name";
portfolioName.Type = ColumnType.text;
portfolioName.AlwaysDisplay = true;
portfolioName.FirstQueryColName = "portf_name";
instrumentId = new QueryColumn();
instrumentId.Label = "Instrument Id";
instrumentId.Type = ColumnType.number;
instrumentId.AlwaysDisplay = true;
instrumentId.FirstQueryColName = "atins_id";
instrumentReleaseNum = new QueryColumn();
instrumentReleaseNum.Label = "Release #";
instrumentReleaseNum.Type = ColumnType.number;
instrumentReleaseNum.AlwaysDisplay = true;
instrumentReleaseNum.FirstQueryColName = "atins_release_number";
calcDate = new QueryColumn();
calcDate.Label = "Calcul Date";
calcDate.Type = ColumnType.date;
calcDate.AlwaysDisplay = true;
calcDate.FirstQueryColName = "calcul_date";
var frequencyId = new QueryColumn();
frequencyId.Label = "Freq";
frequencyId.Type = ColumnType.number;
frequencyId.AlwaysDisplay = true;
frequencyId.FirstQueryColName = "frequency_id";
var beginDate = new QueryColumn();
beginDate.Label = "Begin Date";
beginDate.Type = ColumnType.date;
beginDate.AlwaysDisplay = true;
beginDate.FirstQueryColName = "begin_date";
var portfConfigId = new QueryColumn();
portfConfigId.Label = "Config Id";
portfConfigId.Type = ColumnType.number;
portfConfigId.AlwaysDisplay = true;
portfConfigId.FirstQueryColName = "portf_config_id";
var udGroupId = new QueryColumn();
udGroupId.Label = "Group Id";
udGroupId.Type = ColumnType.number;
udGroupId.AlwaysDisplay = true;
udGroupId.FirstQueryColName = "user_defined_group_id";
instMultGroupsRecon.Columns.Add(portfolioId);
instMultGroupsRecon.Columns.Add(portfolioName);
instMultGroupsRecon.Columns.Add(instrumentId);
instMultGroupsRecon.Columns.Add(instrumentReleaseNum);
instMultGroupsRecon.Columns.Add(calcDate);
instMultGroupsRecon.Columns.Add(frequencyId);
instMultGroupsRecon.Columns.Add(beginDate);
instMultGroupsRecon.Columns.Add(portfConfigId);
instMultGroupsRecon.Columns.Add(udGroupId);
instMultGroupsRecon.QueryVariables = new List<QueryVariable>
{
new QueryVariable { SubName = "PortId", SubValue = "1003022" },
new QueryVariable { SubName = "SegmentTableName", SubValue = "GICS_Sec" }
};
sampleRecons.ReconReports.Add(warehousePortGicsRecon);
sampleRecons.ReconReports.Add(instMultGroupsRecon);
/*
// Note that queries running on two separate platforms may still be compared
ReconReport edmDalPortDayPositionRecon = new ReconReport();
edmDalPortDayPositionRecon.Name = "EDM to DAL Product's Positions for Day";
edmDalPortDayPositionRecon.TabLabel = "EDM DAL Prod Pos";
edmDalPortDayPositionRecon.FirstQueryName = "EdmProductDayPositions";
edmDalPortDayPositionRecon.SecondQueryName = "DalProductDayPositions";
QueryColumn productId = new QueryColumn();
productId.Label = "Product ID";
productId.Type = ColumnType.text;
productId.IdentifyingColumn = true;
productId.AlwaysDisplay = true;
productId.FirstQueryColName = "entity_id";
productId.SecondQueryColName = "productid";
QueryColumn fundName = new QueryColumn();
fundName.Label = "Product Name";
fundName.Type = ColumnType.text;
fundName.AlwaysDisplay = true;
fundName.FirstQueryColName = null;
fundName.SecondQueryColName = "EntityLongName";
QueryColumn snapshotId = new QueryColumn();
snapshotId.Label = "Snapshot";
snapshotId.Type = ColumnType.text;
snapshotId.AlwaysDisplay = true;
snapshotId.IdentifyingColumn = true;
snapshotId.FirstQueryColName = "snapshot_id";
snapshotId.SecondQueryColName = "snapshotid";
QueryColumn securityId = new QueryColumn();
securityId.Label = "Security ID";
securityId.Type = ColumnType.number;
securityId.IdentifyingColumn = true;
securityId.AlwaysDisplay = true;
securityId.FirstQueryColName = "security_alias";
securityId.SecondQueryColName = "OrigSecurityId";
QueryColumn longShortIndicator = new QueryColumn();
longShortIndicator.Label = "Long-Short Indicator";
longShortIndicator.Type = ColumnType.text;
longShortIndicator.AlwaysDisplay = true;
longShortIndicator.IdentifyingColumn = true;
longShortIndicator.FirstQueryColName = "long_short_indicator";
longShortIndicator.SecondQueryColName = "LongShortIndicator";
QueryColumn marketValueBase = new QueryColumn();
marketValueBase.Label = "Market Value Base";
marketValueBase.Type = ColumnType.number;
marketValueBase.CheckDataMatch = true;
marketValueBase.FirstQueryColName = "market_value_base";
marketValueBase.SecondQueryColName = "marketvaluebase";
marketValueBase.Tolerance = 0;
QueryColumn marketValueLocal = new QueryColumn();
marketValueLocal.Label = "Market Value Local";
marketValueLocal.Type = ColumnType.number;
marketValueLocal.CheckDataMatch = true;
marketValueLocal.FirstQueryColName = "market_value_local";
marketValueLocal.SecondQueryColName = "marketvaluelocal";
marketValueLocal.Tolerance = 0;
edmDalPortDayPositionRecon.Columns.Add(productId);
edmDalPortDayPositionRecon.Columns.Add(fundName);
edmDalPortDayPositionRecon.Columns.Add(snapshotId);
edmDalPortDayPositionRecon.Columns.Add(securityId);
edmDalPortDayPositionRecon.Columns.Add(longShortIndicator);
edmDalPortDayPositionRecon.Columns.Add(marketValueBase);
edmDalPortDayPositionRecon.Columns.Add(marketValueLocal);
edmDalPortDayPositionRecon.QueryVariables = new List<QueryVariable>
{
new QueryVariable { SubName = "ProductId", SubValue = "EEUB", QuerySpecific=false },
new QueryVariable { SubName = "EdmDateWanted", SubValue = "01-jul-2014", QuerySpecific=false },
new QueryVariable { SubName = "DalDateWanted", SubValue = "1140701",QuerySpecific=false }
};
sampleRecons.ReconReports.Add(edmDalPortDayPositionRecon);
// This recon is an example of a recon with just one query, and any rows returned are assumed to
// indicate an issue and will be reported
ReconReport positionsMissingFkRecon = new ReconReport();
positionsMissingFkRecon.Name = "Positions With Unknown Security or Product";
positionsMissingFkRecon.TabLabel = "Pos Missing FK";
positionsMissingFkRecon.FirstQueryName = "DalPositionsMissingFk";
positionsMissingFkRecon.SecondQueryName = string.Empty;
var dimTimeId = new QueryColumn();
dimTimeId.Label = "Date";
dimTimeId.Type = ColumnType.date;
dimTimeId.FirstQueryColName = "dimtimeid";
var missingEntity = new QueryColumn();
missingEntity.Label = "Missing";
missingEntity.Type = ColumnType.text;
missingEntity.FirstQueryColName = "MissingEntity";
var originalEntityId = new QueryColumn();
originalEntityId.Label = "Entity Id(s)";
originalEntityId.Type = ColumnType.text;
originalEntityId.FirstQueryColName = "MissingEntityId";
positionsMissingFkRecon.Columns.Add(dimTimeId);
positionsMissingFkRecon.Columns.Add(missingEntity);
positionsMissingFkRecon.Columns.Add(originalEntityId);
sampleRecons.ReconReports.Add(positionsMissingFkRecon);
*/
}
private void populateSampleReconObjects()
{
// Create sample objects to use to populate sample RRSources and RRReports XML files
// for user to use as basis for real recons.
// Create RRSourcesSample.xml
// Contains sample connection template, connection, and query objects in SampleRRSources
// Sample Connection String Templates
var teradataTemplate = new ConnectionStringTemplate();
teradataTemplate.Name = "Teradata";
teradataTemplate.Template = "Provider=TDOLEDB;Data Source=|ServerName|;Persist Security Info=True;User ID=|User|;Password=|Password|;Session Mode=TERA;";
var oracleTemplate = new ConnectionStringTemplate();
oracleTemplate.Name = "Oracle";
oracleTemplate.Template = "Data Source=|TNSName|;User Id=|User|;Password=|Password|;Integrated Security=no;";
var sampleTemplates = new ConnectionStringTemplates();
sampleTemplates.TemplateList.Add(teradataTemplate);
sampleTemplates.TemplateList.Add(oracleTemplate);
// Sample Connection Strings
var dalUatConnectionString = new RRConnectionString();
dalUatConnectionString.Name = "Teradata UAT";
dalUatConnectionString.Template = "Teradata";
dalUatConnectionString.TemplateVariables = new List <Variable> {
new Variable {
SubName = "ServerName", SubValue = "tddevbos"
},
new Variable {
SubName = "User", SubValue = "DAL_READ"
},
new Variable {
SubName = "Password", SubValue = "DAL_READ"
}
};
var edmUatConnectionString = new RRConnectionString();
edmUatConnectionString.Name = "EDM UAT";
edmUatConnectionString.Template = "Oracle";
edmUatConnectionString.TemplateVariables = new List <Variable> {
new Variable {
SubName = "TNSName", SubValue = "EDM_T"
},
new Variable {
SubName = "User", SubValue = "edm_read"
},
new Variable {
SubName = "Password", SubValue = "edm_read"
}
};
var sampleConnStrings = new RRConnectionStrings();
sampleConnStrings.RRConnStringList.Add(dalUatConnectionString);
sampleConnStrings.RRConnStringList.Add(edmUatConnectionString);
// Sample Queries
var edmQuery = new RRQuery();
edmQuery.Name = "EdmProductDayPositions";
edmQuery.RRConnectionString = "EDM UAT";
edmQuery.SQL = @"select fm.snapshot_id, fm.entity_id, pd.market_value_base, pd.market_value_local from datamartdbo.fund_master fm join datamartdbo.position_details pd on fm.entity_id = '|ProductId|' and fm.effective_date = '|EdmDateWanted|' and fm.dmart_fund_id = pd.dmart_fund_id;";
var teraQuery = new RRQuery();
teraQuery.Name = "DalProductDayPositions";
teraQuery.RRConnectionString = "Teradata UAT";
teraQuery.SQL = @"select fp.snapshotid, dp.productid, dp.entitylongname, fp.marketvaluebase, fp.marketvaluelocal from dimproduct dp join factposition fp on dp.productid = '|ProductId|' and dp.dimproductid = fp.dimproductid and fp.dimtimeid = |TeraDateWanted|;";
var posFkQuery = new RRQuery();
posFkQuery.Name = "DalPositionsMissingFk";
posFkQuery.RRConnectionString = "Teradata UAT";
posFkQuery.SQL = @"select * from (select dimtimeid, case when dimsecurityid = 'UNKNOWN' and dimproductid = 'UNKNOWN' then 'BOTH' when dimsecurityid = 'UNKNOWN' then 'SECURITY' else 'PRODUCT' end MissingEntity, case when dimsecurityid = 'UNKNOWN' and dimproductid = 'UNKNOWN' then 'Security ID: '' || OrigSecurityId || ''; Product ID: '' || OrigProductId || ''' when dimsecurityid = 'UNKNOWN' then OrigSecurityId else OrigProductId end OriginalEntityId from factposition where (dimsecurityid = 'UNKNOWN' or dimproductid = 'UNKNOWN') and dimtimeid >= 1131101) data group by MissingEntity, OriginalEntityId, DimTimeId order by MissingEntity, OriginalEntityId, DimTimeId;";
var sampleQueries = new Queries();
sampleQueries.QueryList.Add(edmQuery);
sampleQueries.QueryList.Add(teraQuery);
sampleQueries.QueryList.Add(posFkQuery);
sampleRRSources = new RRSources();
sampleRRSources.ConnStringTemplates = sampleTemplates;
sampleRRSources.ConnectionStrings = sampleConnStrings;
sampleRRSources.Queries = sampleQueries;
// Create RRReportsSample.xml
// Holds sample recon report definitions along with substitution values to use
// in sample queries.
// first recon report and write to file to create file to use as basis to create
// full XML file specifying all the recon reports to run.
/* ****** RESUME TOMORROW: Add collection of VARIABLE values for queries.
* Adapt sample recon report to use new RRSources definitions as above
* Test creation of sample XML files. */
// First recon is an example of comparing two data sets from different databases
ReconReport edmDalPortDayPositionRecon = new ReconReport();
edmDalPortDayPositionRecon.Name = "EDM to DAL Product's Positions for Day";
edmDalPortDayPositionRecon.TabLabel = "EDM DAL Pros Pos";
edmDalPortDayPositionRecon.FirstQuery = "EdmProductDayPositions";
edmDalPortDayPositionRecon.SecondQuery = "DalProductDayPositions";
QueryColumn productId = new QueryColumn();
productId.Label = "Product ID";
productId.Type = ColumnType.text;
productId.IdentifyingColumn = true;
productId.AlwaysDisplay = true;
productId.FirstQueryName = "entity_id";
productId.SecondQueryName = "productid";
QueryColumn fundName = new QueryColumn();
fundName.Label = "Product Name";
fundName.Type = ColumnType.text;
fundName.AlwaysDisplay = true;
fundName.FirstQueryName = "EntityLongName";
fundName.SecondQueryName = null;
QueryColumn snapshotId = new QueryColumn();
snapshotId.Label = "Snapshot";
snapshotId.Type = ColumnType.text;
snapshotId.AlwaysDisplay = true;
snapshotId.IdentifyingColumn = true;
snapshotId.FirstQueryName = "snapshot_id";
snapshotId.SecondQueryName = "snapshotid";
QueryColumn marketValueBase = new QueryColumn();
marketValueBase.Label = "Market Value Base";
marketValueBase.Type = ColumnType.number;
marketValueBase.ShouldMatch = true;
marketValueBase.FirstQueryName = "market_value_base";
marketValueBase.SecondQueryName = "marketvaluebase";
QueryColumn marketValueLocal = new QueryColumn();
marketValueLocal.Label = "Market Value Local";
marketValueLocal.Type = ColumnType.number;
marketValueLocal.ShouldMatch = true;
marketValueLocal.FirstQueryName = "market_value_local";
marketValueLocal.SecondQueryName = "marketvaluelocal";
edmDalPortDayPositionRecon.Columns.Add(productId);
edmDalPortDayPositionRecon.Columns.Add(fundName);
edmDalPortDayPositionRecon.Columns.Add(snapshotId);
edmDalPortDayPositionRecon.Columns.Add(marketValueBase);
edmDalPortDayPositionRecon.Columns.Add(marketValueLocal);
edmDalPortDayPositionRecon.QueryVariables = new List <Variable> {
new Variable {
SubName = "ProductId", SubValue = "EEUB"
},
new Variable {
SubName = "EdmDateWanted", SubValue = "01-jul-2014"
},
new Variable {
SubName = "DalDateWanted", SubValue = "1140701"
}
};
sampleRecons.ReconList.Add(edmDalPortDayPositionRecon);
// This second recon is an example of a recon with just one query, and any rows returned are assumed to
// indicate an issue and will be reported
ReconReport positionsMissingFkRecon = new ReconReport();
positionsMissingFkRecon.Name = "Positions With Unknown Security or Product";
positionsMissingFkRecon.TabLabel = "Pos Missing FK";
positionsMissingFkRecon.FirstQuery = "DalPositionsMissingFk";
positionsMissingFkRecon.SecondQuery = string.Empty;
var dimTimeId = new QueryColumn();
dimTimeId.Label = "Date";
dimTimeId.Type = ColumnType.date;
dimTimeId.AlwaysDisplay = true;
dimTimeId.FirstQueryName = "dimtimeid";
var date = new QueryColumn();
date.Label = "Date";
date.Type = ColumnType.date;
date.AlwaysDisplay = true;
date.FirstQueryName = "DimTimeId";
var missingEntity = new QueryColumn();
missingEntity.Label = "Missing";
missingEntity.Type = ColumnType.text;
missingEntity.AlwaysDisplay = true;
missingEntity.FirstQueryName = "MissingEntity";
var originalEntityId = new QueryColumn();
originalEntityId.Label = "Entity Id";
originalEntityId.Type = ColumnType.text;
originalEntityId.AlwaysDisplay = true;
originalEntityId.FirstQueryName = "OriginalEntityId";
positionsMissingFkRecon.Columns.Add(dimTimeId);
positionsMissingFkRecon.Columns.Add(missingEntity);
positionsMissingFkRecon.Columns.Add(originalEntityId);
// Don't add single query recon to sample file until supporting logic in controller added
//sampleRecons.ReconList.Add(positionsMissingFkRecon);
}
public ReconReportAndData(ReconReport recon, DataTable query1Data, DataTable query2Data)
{
reconReport = recon;
firstQueryData = query1Data;
secondQueryData = query2Data;
}
private void populateSampleReconObjects()
{
sampleSources = new Sources();
sampleRecons = new Recons();
// Create sample objects to use to populate sample Sources and RRReports XML files
// for user to use as basis for real recons.
// Create RRSourcesSample.xml
// Contains sample connection template, connection, and query objects in SampleRRSources
// Sample Connection String Templates
var sqlServerTemplate = new ConnectionStringTemplate();
sqlServerTemplate.Name = "SQL Server";
sqlServerTemplate.Template = "Provider=SQLNCLI11;Server=|ServerName|;Database=|DatabaseName|;Trusted_Connection=yes";
var teradataTemplate = new ConnectionStringTemplate();
teradataTemplate.Name = "Teradata";
teradataTemplate.Template = "Data Source=|ServerName|;User ID=|User|;Password=|Password|;Session Mode=TERADATA;";
var oracleTemplate = new ConnectionStringTemplate();
oracleTemplate.Name = "Oracle";
oracleTemplate.Template = "Provider=MSDAORA.1;Data Source=|TNSName|;User Id=|User|;Password=|Password|;Persist Security Info=false;";
sampleSources.ConnStringTemplates.Add(sqlServerTemplate);
sampleSources.ConnStringTemplates.Add(teradataTemplate);
sampleSources.ConnStringTemplates.Add(oracleTemplate);
// Sample Connection Strings
var warehouseConnectionString = new RRConnectionString();
warehouseConnectionString.Name = "DataWarehouse Dev";
warehouseConnectionString.TemplateName = "SQL Server";
warehouseConnectionString.DatabaseType = DatabaseType.SQLServer;
warehouseConnectionString.TemplateVariables = new List <Variable>
{
new Variable {
SubName = "ServerName", SubValue = "bos-dbdevwh02"
},
new Variable {
SubName = "DatabaseName", SubValue = "DataWarehouse"
}
};
var dalUatConnectionString = new RRConnectionString();
dalUatConnectionString.Name = "Teradata UAT";
dalUatConnectionString.TemplateName = "Teradata";
dalUatConnectionString.DatabaseType = DatabaseType.Teradata;
dalUatConnectionString.TemplateVariables = new List <Variable>
{
new Variable {
SubName = "ServerName", SubValue = "tddevbos"
},
new Variable {
SubName = "User", SubValue = "DAL_READ"
},
new Variable {
SubName = "Password", SubValue = "DAL_READ"
}
};
var edmUatConnectionString = new RRConnectionString();
edmUatConnectionString.Name = "EDM UAT";
edmUatConnectionString.TemplateName = "Oracle";
edmUatConnectionString.DatabaseType = DatabaseType.Oracle;
edmUatConnectionString.TemplateVariables = new List <Variable>
{
new Variable {
SubName = "TNSName", SubValue = "EDM_T"
},
new Variable {
SubName = "User", SubValue = "edm_read"
},
new Variable {
SubName = "Password", SubValue = "edm_read"
}
};
sampleSources.ConnectionStrings.Add(dalUatConnectionString);
sampleSources.ConnectionStrings.Add(edmUatConnectionString);
sampleSources.ConnectionStrings.Add(warehouseConnectionString);
// Sample Queries
var doc = new XmlDocument();
var portGicsSectorDetail = new RRQuery();
portGicsSectorDetail.Name = "PortGicsSectorDetails";
portGicsSectorDetail.ConnStringName = "DataWarehouse Dev";
portGicsSectorDetail.SQL = doc.CreateCDataSection(@"with latestAttrInsEffDate as
(
select
atins_id,
max(effect_date) LatestRowDate
from warehouse.attr_instrument
where effect_date <= '|DateWanted|'
group by atins_id
),
latestAttrIns as
(
select
laied.atins_id,
laied.LatestRowDate,
max(ai.atins_release_number) LatestReleaseNumber
from latestAttrInsEffDate laied
join warehouse.attr_instrument ai
on laied.atins_id = ai.atins_id
and laied.LatestRowDate = ai.effect_date
group by laied.atins_id, laied.LatestRowDate)
select
bigs.atptf_id,
p.portf_name,
bigs.calcul_date,
bigs.atins_id,
ai.atins_name,
ai.effect_date,
ai.end_valid_date,
ai.atins_release_number
from warehouse.bisamattributions_gics_sec bigs
join warehouse.portfolio p
on bigs.atptf_id = |PortId|
and bigs.atins_id <> 0
and bigs.calcul_date = '|DateWanted|'
and bigs.dateinactive = '31-dec-9999'
and bigs.atptf_id = p.portf_id
join latestAttrIns lai
on bigs.atins_id = lai.atins_id
and bigs.atins_release_number = lai.LatestReleaseNumber
join warehouse.attr_instrument ai
on lai.atins_id = ai.atins_id
and lai.LatestRowDate = ai.effect_date
and lai.LatestReleaseNumber = ai.ATINS_RELEASE_NUMBER
order by atins_name");
var portAttribGroupcheck = new RRQuery();
portAttribGroupcheck.Name = "PortAttribGroupCheck";
portAttribGroupcheck.ConnStringName = "DataWarehouse Dev";
portAttribGroupcheck.SQL = doc.CreateCDataSection(@"with portAttributionsRuns as (
select
max(checksum(atptf_id, atins_id, atins_release_number, calcul_date, frequency_id, begin_date, portf_config_id)) portAttribRunKey,
atptf_id,
atins_id,
atins_release_number,
calcul_date,
frequency_id,
begin_date,
portf_config_id,
user_defined_group_id
from warehouse.bisamAttributions_|SegmentTableName| bigs
where atptf_id = |PortId| and atins_id <> 0 and dateinactive = '31-dec-9999'
group by atptf_id, atins_id, atins_release_number, calcul_date, frequency_id, begin_date, portf_config_id, user_defined_group_id),
portMultiGroupCount as (
select
portAttribRunkey,
atptf_id,
atins_id,
atins_release_number,
calcul_date,
frequency_id,
begin_date,
portf_config_id
from portAttributionsRuns
group by portAttribRunKey, atptf_id, atins_id, atins_release_number, calcul_date, frequency_id, begin_date, portf_config_id
having count(*) > 1
)
select par.*, p.portf_name
from portAttributionsRuns par
join portMultiGroupCount pmgc
on par.portAttribRunKey = pmgc.portAttribRunKey
join warehouse.portfolio p
on par.atptf_id = p.portf_id
order by par.atins_id, par.atins_release_number, par.calcul_date, par.FREQUENCY_ID, par.begin_date, user_defined_group_id");
var edmQuery = new RRQuery();
edmQuery.Name = "EdmProductDayPositions";
edmQuery.ConnStringName = "EDM UAT";
edmQuery.SQL = doc.CreateCDataSection("select fm.snapshot_id, trim(fm.entity_id) entity_id, pd.security_alias, trim(pd.long_short_indicator) long_short_indicator, pd.market_value_base, pd.market_value_local from datamartdbo.fund_master fm join datamartdbo.position_details pd on fm.entity_id = '|ProductId|' and fm.effective_date = '|EdmDateWanted|' and fm.dmart_fund_id = pd.dmart_fund_id");
var teraQuery = new RRQuery();
teraQuery.Name = "DalProductDayPositions";
teraQuery.ConnStringName = "Teradata UAT";
teraQuery.SQL = doc.CreateCDataSection("select fp.snapshotid, dp.productid, dp.entitylongname, fp.OrigSecurityId, fp.LongShortIndicator, fp.marketvaluebase, fp.marketvaluelocal from dimproduct dp join factposition fp on dp.productid = '|ProductId|' and dp.dimproductid = fp.dimproductid and fp.dimtimeid = |DalDateWanted|");
var posFkQuery = new RRQuery();
posFkQuery.Name = "DalPositionsMissingFk";
posFkQuery.ConnStringName = "Teradata UAT";
posFkQuery.SQL = doc.CreateCDataSection("select * from (select dimtimeid, case when dimsecurityid = 'UNKNOWN' and dimproductid = 'UNKNOWN' then 'BOTH' when dimsecurityid = 'UNKNOWN' then 'SECURITY' else 'PRODUCT' end MissingEntity, case when dimsecurityid = 'UNKNOWN' and dimproductid = 'UNKNOWN' then 'Security ID: ' || OrigSecurityId || '; Product ID: ' || OrigProductId when dimsecurityid = 'UNKNOWN' then OrigSecurityId else OrigProductId end MissingEntityId from factposition where (dimsecurityid = 'UNKNOWN' or dimproductid = 'UNKNOWN') and dimtimeid >= 1131101) data group by MissingEntity, MissingEntityId, DimTimeId order by MissingEntity, MissingEntityId, DimTimeId");
sampleSources.Queries.Add(portGicsSectorDetail);
sampleSources.Queries.Add(portAttribGroupcheck);
sampleSources.Queries.Add(edmQuery);
sampleSources.Queries.Add(teraQuery);
sampleSources.Queries.Add(posFkQuery);
// Create RRReportsSample.xml
// Holds sample recon report definitions along with substitution values to use
// in sample queries.
// first recon report and write to file to create file to use as basis to create
// full XML file specifying all the recon reports to run.
// First two recons are examples of comparing two data sets
// Acadian-specific example (Note: the actual example is not necessarily useful,
// it's just being done to show how the process works)
// This recon uses the same query twice, so the query variables are query specific
ReconReport warehousePortGicsRecon = new ReconReport();
warehousePortGicsRecon.Name = "Compare GICS Sec Details for 2 days for 1 portfolio";
warehousePortGicsRecon.TabLabel = "GICS Sec Changes";
warehousePortGicsRecon.FirstQueryName = "PortGicsSectorDetails";
warehousePortGicsRecon.SecondQueryName = "PortGicsSectorDetails";
QueryColumn portfolioId = new QueryColumn();
portfolioId.Label = "Portfolio ID";
portfolioId.Type = ColumnType.number;
portfolioId.IdentifyingColumn = true;
portfolioId.AlwaysDisplay = true;
portfolioId.FirstQueryColName = "atptf_id";
portfolioId.SecondQueryColName = "atptf_id";
QueryColumn portfolioName = new QueryColumn();
portfolioName.Label = "Portfolio Name";
portfolioName.Type = ColumnType.text;
portfolioName.IdentifyingColumn = false;
portfolioName.AlwaysDisplay = true;
portfolioName.FirstQueryColName = "portf_name";
portfolioName.SecondQueryColName = "portf_name";
QueryColumn calcDate = new QueryColumn();
calcDate.Label = "Calcul Date";
calcDate.Type = ColumnType.date;
calcDate.IdentifyingColumn = false;
calcDate.AlwaysDisplay = true;
calcDate.FirstQueryColName = "calcul_date";
calcDate.SecondQueryColName = "calcul_date";
QueryColumn instrumentId = new QueryColumn();
instrumentId.Label = "Instrument Id";
instrumentId.Type = ColumnType.number;
instrumentId.IdentifyingColumn = true;
instrumentId.AlwaysDisplay = true;
instrumentId.FirstQueryColName = "atins_id";
instrumentId.SecondQueryColName = "atins_id";
QueryColumn instrumentName = new QueryColumn();
instrumentName.Label = "Instrument Name";
instrumentName.Type = ColumnType.text;
instrumentName.IdentifyingColumn = false;
instrumentName.AlwaysDisplay = true;
instrumentName.FirstQueryColName = "atins_name";
instrumentName.SecondQueryColName = "atins_name";
QueryColumn instrumentReleaseNum = new QueryColumn();
instrumentReleaseNum.Label = "Release #";
instrumentReleaseNum.Type = ColumnType.number;
instrumentReleaseNum.IdentifyingColumn = false;
instrumentReleaseNum.AlwaysDisplay = false;
instrumentReleaseNum.CheckDataMatch = true;
instrumentReleaseNum.Tolerance = 1;
instrumentReleaseNum.FirstQueryColName = "atins_release_number";
instrumentReleaseNum.SecondQueryColName = "atins_release_number";
warehousePortGicsRecon.Columns.Add(portfolioId);
warehousePortGicsRecon.Columns.Add(portfolioName);
warehousePortGicsRecon.Columns.Add(calcDate);
warehousePortGicsRecon.Columns.Add(instrumentId);
warehousePortGicsRecon.Columns.Add(instrumentName);
warehousePortGicsRecon.Columns.Add(instrumentReleaseNum);
warehousePortGicsRecon.QueryVariables = new List <QueryVariable>
{
new QueryVariable {
SubName = "PortId", SubValue = "1003022", QuerySpecific = false
},
new QueryVariable {
SubName = "DateWanted", SubValue = "30-jul-2015", QuerySpecific = true, QueryNumber = 1
},
new QueryVariable {
SubName = "DateWanted", SubValue = "31-jul-2015", QuerySpecific = true, QueryNumber = 2
}
};
ReconReport instMultGroupsRecon = new ReconReport();
instMultGroupsRecon.Name = "Instruments with Multiple Attribution Groups for Similar Runs";
instMultGroupsRecon.TabLabel = "Multi-group Inst";
instMultGroupsRecon.FirstQueryName = "PortAttribGroupCheck";
instMultGroupsRecon.SecondQueryName = string.Empty;
portfolioId = new QueryColumn();
portfolioId.Label = "Portfolio ID";
portfolioId.Type = ColumnType.number;
portfolioId.AlwaysDisplay = true;
portfolioId.FirstQueryColName = "atptf_id";
portfolioName = new QueryColumn();
portfolioName.Label = "Portfolio Name";
portfolioName.Type = ColumnType.text;
portfolioName.AlwaysDisplay = true;
portfolioName.FirstQueryColName = "portf_name";
instrumentId = new QueryColumn();
instrumentId.Label = "Instrument Id";
instrumentId.Type = ColumnType.number;
instrumentId.AlwaysDisplay = true;
instrumentId.FirstQueryColName = "atins_id";
instrumentReleaseNum = new QueryColumn();
instrumentReleaseNum.Label = "Release #";
instrumentReleaseNum.Type = ColumnType.number;
instrumentReleaseNum.AlwaysDisplay = true;
instrumentReleaseNum.FirstQueryColName = "atins_release_number";
calcDate = new QueryColumn();
calcDate.Label = "Calcul Date";
calcDate.Type = ColumnType.date;
calcDate.AlwaysDisplay = true;
calcDate.FirstQueryColName = "calcul_date";
var frequencyId = new QueryColumn();
frequencyId.Label = "Freq";
frequencyId.Type = ColumnType.number;
frequencyId.AlwaysDisplay = true;
frequencyId.FirstQueryColName = "frequency_id";
var beginDate = new QueryColumn();
beginDate.Label = "Begin Date";
beginDate.Type = ColumnType.date;
beginDate.AlwaysDisplay = true;
beginDate.FirstQueryColName = "begin_date";
var portfConfigId = new QueryColumn();
portfConfigId.Label = "Config Id";
portfConfigId.Type = ColumnType.number;
portfConfigId.AlwaysDisplay = true;
portfConfigId.FirstQueryColName = "portf_config_id";
var udGroupId = new QueryColumn();
udGroupId.Label = "Group Id";
udGroupId.Type = ColumnType.number;
udGroupId.AlwaysDisplay = true;
udGroupId.FirstQueryColName = "user_defined_group_id";
instMultGroupsRecon.Columns.Add(portfolioId);
instMultGroupsRecon.Columns.Add(portfolioName);
instMultGroupsRecon.Columns.Add(instrumentId);
instMultGroupsRecon.Columns.Add(instrumentReleaseNum);
instMultGroupsRecon.Columns.Add(calcDate);
instMultGroupsRecon.Columns.Add(frequencyId);
instMultGroupsRecon.Columns.Add(beginDate);
instMultGroupsRecon.Columns.Add(portfConfigId);
instMultGroupsRecon.Columns.Add(udGroupId);
instMultGroupsRecon.QueryVariables = new List <QueryVariable>
{
new QueryVariable {
SubName = "PortId", SubValue = "1003022"
},
new QueryVariable {
SubName = "SegmentTableName", SubValue = "GICS_Sec"
}
};
sampleRecons.ReconReports.Add(warehousePortGicsRecon);
sampleRecons.ReconReports.Add(instMultGroupsRecon);
/*
* // Note that queries running on two separate platforms may still be compared
* ReconReport edmDalPortDayPositionRecon = new ReconReport();
* edmDalPortDayPositionRecon.Name = "EDM to DAL Product's Positions for Day";
* edmDalPortDayPositionRecon.TabLabel = "EDM DAL Prod Pos";
* edmDalPortDayPositionRecon.FirstQueryName = "EdmProductDayPositions";
* edmDalPortDayPositionRecon.SecondQueryName = "DalProductDayPositions";
*
* QueryColumn productId = new QueryColumn();
* productId.Label = "Product ID";
* productId.Type = ColumnType.text;
* productId.IdentifyingColumn = true;
* productId.AlwaysDisplay = true;
* productId.FirstQueryColName = "entity_id";
* productId.SecondQueryColName = "productid";
*
* QueryColumn fundName = new QueryColumn();
* fundName.Label = "Product Name";
* fundName.Type = ColumnType.text;
* fundName.AlwaysDisplay = true;
* fundName.FirstQueryColName = null;
* fundName.SecondQueryColName = "EntityLongName";
*
* QueryColumn snapshotId = new QueryColumn();
* snapshotId.Label = "Snapshot";
* snapshotId.Type = ColumnType.text;
* snapshotId.AlwaysDisplay = true;
* snapshotId.IdentifyingColumn = true;
* snapshotId.FirstQueryColName = "snapshot_id";
* snapshotId.SecondQueryColName = "snapshotid";
*
* QueryColumn securityId = new QueryColumn();
* securityId.Label = "Security ID";
* securityId.Type = ColumnType.number;
* securityId.IdentifyingColumn = true;
* securityId.AlwaysDisplay = true;
* securityId.FirstQueryColName = "security_alias";
* securityId.SecondQueryColName = "OrigSecurityId";
*
* QueryColumn longShortIndicator = new QueryColumn();
* longShortIndicator.Label = "Long-Short Indicator";
* longShortIndicator.Type = ColumnType.text;
* longShortIndicator.AlwaysDisplay = true;
* longShortIndicator.IdentifyingColumn = true;
* longShortIndicator.FirstQueryColName = "long_short_indicator";
* longShortIndicator.SecondQueryColName = "LongShortIndicator";
*
* QueryColumn marketValueBase = new QueryColumn();
* marketValueBase.Label = "Market Value Base";
* marketValueBase.Type = ColumnType.number;
* marketValueBase.CheckDataMatch = true;
* marketValueBase.FirstQueryColName = "market_value_base";
* marketValueBase.SecondQueryColName = "marketvaluebase";
* marketValueBase.Tolerance = 0;
*
* QueryColumn marketValueLocal = new QueryColumn();
* marketValueLocal.Label = "Market Value Local";
* marketValueLocal.Type = ColumnType.number;
* marketValueLocal.CheckDataMatch = true;
* marketValueLocal.FirstQueryColName = "market_value_local";
* marketValueLocal.SecondQueryColName = "marketvaluelocal";
* marketValueLocal.Tolerance = 0;
*
* edmDalPortDayPositionRecon.Columns.Add(productId);
* edmDalPortDayPositionRecon.Columns.Add(fundName);
* edmDalPortDayPositionRecon.Columns.Add(snapshotId);
* edmDalPortDayPositionRecon.Columns.Add(securityId);
* edmDalPortDayPositionRecon.Columns.Add(longShortIndicator);
* edmDalPortDayPositionRecon.Columns.Add(marketValueBase);
* edmDalPortDayPositionRecon.Columns.Add(marketValueLocal);
*
* edmDalPortDayPositionRecon.QueryVariables = new List<QueryVariable>
* {
* new QueryVariable { SubName = "ProductId", SubValue = "EEUB", QuerySpecific=false },
* new QueryVariable { SubName = "EdmDateWanted", SubValue = "01-jul-2014", QuerySpecific=false },
* new QueryVariable { SubName = "DalDateWanted", SubValue = "1140701",QuerySpecific=false }
* };
* sampleRecons.ReconReports.Add(edmDalPortDayPositionRecon);
*
* // This recon is an example of a recon with just one query, and any rows returned are assumed to
* // indicate an issue and will be reported
* ReconReport positionsMissingFkRecon = new ReconReport();
* positionsMissingFkRecon.Name = "Positions With Unknown Security or Product";
* positionsMissingFkRecon.TabLabel = "Pos Missing FK";
* positionsMissingFkRecon.FirstQueryName = "DalPositionsMissingFk";
* positionsMissingFkRecon.SecondQueryName = string.Empty;
*
* var dimTimeId = new QueryColumn();
* dimTimeId.Label = "Date";
* dimTimeId.Type = ColumnType.date;
* dimTimeId.FirstQueryColName = "dimtimeid";
*
* var missingEntity = new QueryColumn();
* missingEntity.Label = "Missing";
* missingEntity.Type = ColumnType.text;
* missingEntity.FirstQueryColName = "MissingEntity";
*
* var originalEntityId = new QueryColumn();
* originalEntityId.Label = "Entity Id(s)";
* originalEntityId.Type = ColumnType.text;
* originalEntityId.FirstQueryColName = "MissingEntityId";
*
* positionsMissingFkRecon.Columns.Add(dimTimeId);
* positionsMissingFkRecon.Columns.Add(missingEntity);
* positionsMissingFkRecon.Columns.Add(originalEntityId);
*
* sampleRecons.ReconReports.Add(positionsMissingFkRecon);
*/
}
private void populateSampleReconObjects()
{
// Create sample objects to use to populate sample RRSources and RRReports XML files
// for user to use as basis for real recons.
// Create RRSourcesSample.xml
// Contains sample connection template, connection, and query objects in SampleRRSources
// Sample Connection String Templates
var teradataTemplate = new ConnectionStringTemplate();
teradataTemplate.Name = "Teradata";
teradataTemplate.Template = "Provider=TDOLEDB;Data Source=|ServerName|;Persist Security Info=True;User ID=|User|;Password=|Password|;Session Mode=TERA;";
var oracleTemplate = new ConnectionStringTemplate();
oracleTemplate.Name = "Oracle";
oracleTemplate.Template = "Data Source=|TNSName|;User Id=|User|;Password=|Password|;Integrated Security=no;";
var sampleTemplates = new ConnectionStringTemplates();
sampleTemplates.TemplateList.Add(teradataTemplate);
sampleTemplates.TemplateList.Add(oracleTemplate);
// Sample Connection Strings
var dalUatConnectionString = new RRConnectionString();
dalUatConnectionString.Name = "Teradata UAT";
dalUatConnectionString.Template = "Teradata";
dalUatConnectionString.TemplateVariables = new List<Variable>{
new Variable {SubName="ServerName", SubValue="tddevbos"},
new Variable {SubName="User", SubValue="DAL_READ"},
new Variable {SubName="Password", SubValue="DAL_READ"}
};
var edmUatConnectionString = new RRConnectionString();
edmUatConnectionString.Name = "EDM UAT";
edmUatConnectionString.Template = "Oracle";
edmUatConnectionString.TemplateVariables = new List<Variable>{
new Variable {SubName="TNSName", SubValue="EDM_T"},
new Variable {SubName="User", SubValue="edm_read"},
new Variable {SubName="Password", SubValue="edm_read"}
};
var sampleConnStrings = new RRConnectionStrings();
sampleConnStrings.RRConnStringList.Add(dalUatConnectionString);
sampleConnStrings.RRConnStringList.Add(edmUatConnectionString);
// Sample Queries
var edmQuery = new RRQuery();
edmQuery.Name = "EdmProductDayPositions";
edmQuery.RRConnectionString = "EDM UAT";
edmQuery.SQL = @"select fm.snapshot_id, fm.entity_id, pd.market_value_base, pd.market_value_local from datamartdbo.fund_master fm join datamartdbo.position_details pd on fm.entity_id = '|ProductId|' and fm.effective_date = '|EdmDateWanted|' and fm.dmart_fund_id = pd.dmart_fund_id;";
var teraQuery = new RRQuery();
teraQuery.Name = "DalProductDayPositions";
teraQuery.RRConnectionString = "Teradata UAT";
teraQuery.SQL = @"select fp.snapshotid, dp.productid, dp.entitylongname, fp.marketvaluebase, fp.marketvaluelocal from dimproduct dp join factposition fp on dp.productid = '|ProductId|' and dp.dimproductid = fp.dimproductid and fp.dimtimeid = |TeraDateWanted|;";
var posFkQuery = new RRQuery();
posFkQuery.Name = "DalPositionsMissingFk";
posFkQuery.RRConnectionString = "Teradata UAT";
posFkQuery.SQL = @"select * from (select dimtimeid, case when dimsecurityid = 'UNKNOWN' and dimproductid = 'UNKNOWN' then 'BOTH' when dimsecurityid = 'UNKNOWN' then 'SECURITY' else 'PRODUCT' end MissingEntity, case when dimsecurityid = 'UNKNOWN' and dimproductid = 'UNKNOWN' then 'Security ID: '' || OrigSecurityId || ''; Product ID: '' || OrigProductId || ''' when dimsecurityid = 'UNKNOWN' then OrigSecurityId else OrigProductId end OriginalEntityId from factposition where (dimsecurityid = 'UNKNOWN' or dimproductid = 'UNKNOWN') and dimtimeid >= 1131101) data group by MissingEntity, OriginalEntityId, DimTimeId order by MissingEntity, OriginalEntityId, DimTimeId;";
var sampleQueries = new Queries();
sampleQueries.QueryList.Add(edmQuery);
sampleQueries.QueryList.Add(teraQuery);
sampleQueries.QueryList.Add(posFkQuery);
sampleRRSources = new RRSources();
sampleRRSources.ConnStringTemplates = sampleTemplates;
sampleRRSources.ConnectionStrings = sampleConnStrings;
sampleRRSources.Queries = sampleQueries;
// Create RRReportsSample.xml
// Holds sample recon report definitions along with substitution values to use
// in sample queries.
// first recon report and write to file to create file to use as basis to create
// full XML file specifying all the recon reports to run.
/* ****** RESUME TOMORROW: Add collection of VARIABLE values for queries.
* Adapt sample recon report to use new RRSources definitions as above
* Test creation of sample XML files. */
// First recon is an example of comparing two data sets from different databases
ReconReport edmDalPortDayPositionRecon = new ReconReport();
edmDalPortDayPositionRecon.Name = "EDM to DAL Product's Positions for Day";
edmDalPortDayPositionRecon.TabLabel = "EDM DAL Pros Pos";
edmDalPortDayPositionRecon.FirstQuery = "EdmProductDayPositions";
edmDalPortDayPositionRecon.SecondQuery = "DalProductDayPositions";
QueryColumn productId = new QueryColumn();
productId.Label = "Product ID";
productId.Type = ColumnType.text;
productId.IdentifyingColumn = true;
productId.AlwaysDisplay = true;
productId.FirstQueryName = "entity_id";
productId.SecondQueryName = "productid";
QueryColumn fundName = new QueryColumn();
fundName.Label = "Product Name";
fundName.Type = ColumnType.text;
fundName.AlwaysDisplay = true;
fundName.FirstQueryName = "EntityLongName";
fundName.SecondQueryName = null;
QueryColumn snapshotId = new QueryColumn();
snapshotId.Label = "Snapshot";
snapshotId.Type = ColumnType.text;
snapshotId.AlwaysDisplay = true;
snapshotId.IdentifyingColumn = true;
snapshotId.FirstQueryName = "snapshot_id";
snapshotId.SecondQueryName = "snapshotid";
QueryColumn marketValueBase = new QueryColumn();
marketValueBase.Label = "Market Value Base";
marketValueBase.Type = ColumnType.number;
marketValueBase.ShouldMatch = true;
marketValueBase.FirstQueryName = "market_value_base";
marketValueBase.SecondQueryName = "marketvaluebase";
QueryColumn marketValueLocal = new QueryColumn();
marketValueLocal.Label = "Market Value Local";
marketValueLocal.Type = ColumnType.number;
marketValueLocal.ShouldMatch = true;
marketValueLocal.FirstQueryName = "market_value_local";
marketValueLocal.SecondQueryName = "marketvaluelocal";
edmDalPortDayPositionRecon.Columns.Add(productId);
edmDalPortDayPositionRecon.Columns.Add(fundName);
edmDalPortDayPositionRecon.Columns.Add(snapshotId);
edmDalPortDayPositionRecon.Columns.Add(marketValueBase);
edmDalPortDayPositionRecon.Columns.Add(marketValueLocal);
edmDalPortDayPositionRecon.QueryVariables = new List<Variable> {
new Variable { SubName = "ProductId", SubValue = "EEUB" },
new Variable { SubName = "EdmDateWanted", SubValue = "01-jul-2014" },
new Variable { SubName = "DalDateWanted", SubValue = "1140701" } };
sampleRecons.ReconList.Add(edmDalPortDayPositionRecon);
// This second recon is an example of a recon with just one query, and any rows returned are assumed to
// indicate an issue and will be reported
ReconReport positionsMissingFkRecon = new ReconReport();
positionsMissingFkRecon.Name = "Positions With Unknown Security or Product";
positionsMissingFkRecon.TabLabel = "Pos Missing FK";
positionsMissingFkRecon.FirstQuery = "DalPositionsMissingFk";
positionsMissingFkRecon.SecondQuery = string.Empty;
var dimTimeId = new QueryColumn();
dimTimeId.Label = "Date";
dimTimeId.Type = ColumnType.date;
dimTimeId.AlwaysDisplay = true;
dimTimeId.FirstQueryName = "dimtimeid";
var date = new QueryColumn();
date.Label = "Date";
date.Type = ColumnType.date;
date.AlwaysDisplay = true;
date.FirstQueryName = "DimTimeId";
var missingEntity = new QueryColumn();
missingEntity.Label = "Missing";
missingEntity.Type = ColumnType.text;
missingEntity.AlwaysDisplay = true;
missingEntity.FirstQueryName = "MissingEntity";
var originalEntityId = new QueryColumn();
originalEntityId.Label = "Entity Id";
originalEntityId.Type = ColumnType.text;
originalEntityId.AlwaysDisplay = true;
originalEntityId.FirstQueryName = "OriginalEntityId";
positionsMissingFkRecon.Columns.Add(dimTimeId);
positionsMissingFkRecon.Columns.Add(missingEntity);
positionsMissingFkRecon.Columns.Add(originalEntityId);
// Don't add single query recon to sample file until supporting logic in controller added
//sampleRecons.ReconList.Add(positionsMissingFkRecon);
}
/// <summary>
/// If a single query recon will show all the rows returned by the query. If a two query recon
/// then will show all the rows matched between the two queries that have data differences in the
/// columns that are marked "ShouldMatch"
/// </summary>
/// <param name="recon"></param>
/// <param name="firstQueryData"></param>
/// <param name="secondQueryData"></param>
private void writeDataProblems(ReconReport recon, DataTable firstQueryData, DataTable secondQueryData)
{
CellStyle currStyle = CellStyle.LightBlue;
int currColumnIndex;
int numDifferencesFound = 0;
int numQueries = recon.SecondQueryName == "" ? 1 : 2;
string counterText;
setReconQueryLabels(recon);
// Start the section with a label if 2-query recon
if (recon.SecondQueryName != "")
{
excelRow = new Dictionary<string, Cell>();
excelRow.Add("A", new Cell("Rows in ", CellStyle.Bold));
excelRow.Add("B", new Cell(firstQueryLabel, CellStyle.Bold));
excelRow.Add("C", new Cell("that have one or more", CellStyle.Bold));
excelRow.Add("D", new Cell("differences compared to", CellStyle.Bold));
excelRow.Add("E", new Cell(secondQueryLabel, CellStyle.Bold));
rrExcelService.AddRow(excelRow);
}
// Now write out the common columns at the left of each section
// If a 1 query recon then we don't need extra columns and should add the header row as is
currColumnIndex = writeCommonHeaders(numQueries, recon.Columns, recon.SecondQueryName == "", false);
if (recon.SecondQueryName != "")
{
// We have to add three more columns: one to list the item the differences was found in, and then
// two columns to show the two values found.
excelRow.Add(columnLetters[currColumnIndex].ToString(), new Cell("Column", CellStyle.DarkBlueBold));
excelRow.Add(columnLetters[currColumnIndex + 1].ToString(), new Cell("1st Query Value", CellStyle.DarkBlueBold));
excelRow.Add(columnLetters[currColumnIndex + 2].ToString(), new Cell("2nd Query Value", CellStyle.DarkBlueBold));
rrExcelService.AddRow(excelRow);
}
// Now go through the queries and write one row per row returned in the case of 1-query recons or one row
// per difference found in the case of 2-query recons
if (recon.SecondQueryName == "")
{
// Write out rows for 1-query recons
if (firstQueryData.Rows.Count == 0)
{
excelRow = new Dictionary<string, Cell>();
excelRow.Add("A", new Cell("No rows returned", CellStyle.Bold));
rrExcelService.AddRow(excelRow);
}
else
{
foreach (DataRow row in firstQueryData.Rows)
{
excelRow = new Dictionary<string, Cell>();
currStyle = toggleStyle(currStyle);
// Always show all columns on a 1-query recon
for (int n = 0; n < recon.Columns.Count; n++)
{
excelRow.Add(columnLetters[n].ToString(), new Cell(row[recon.Columns[n].FirstQueryColName].ToString(), currStyle));
}
rrExcelService.AddRow(excelRow);
}
// Write a summary row with number of rows returned
counterText = string.Format("{0} {1} returned.", firstQueryData.Rows.Count, firstQueryData.Rows.Count == 1 ? "row" : "rows");
excelRow.Clear();
excelRow.Add("A", new Cell(counterText));
rrExcelService.AddRow(excelRow); }
}
else
{ // Write out rows for 2-query recons
foreach (string rowKey in q1Rows.Keys)
{
if (q2Rows.ContainsKey(rowKey))
{
// Toggle background color of the row being written
currStyle = toggleStyle(currStyle);
// Get the two matched rows and see if any of their columns that should match
// actually have any differences that need to be reported.
foreach (QueryColumn column in recon.Columns)
{
if (column.CheckDataMatch)
{
if (!valuesMatch(q1Rows[rowKey][column.FirstQueryColName], q2Rows[rowKey][column.SecondQueryColName], column))
{
// We have a mismatch that needs to be reported
// First, write the identifying and 'Always Display' columns, and get the column index we're at
currColumnIndex = writeDiffRowCommonColumns(q1Rows[rowKey], q2Rows[rowKey], recon.Columns, currStyle);
// Now write out the three columns regarding the difference found:
// column name, first query value, and second query value
excelRow.Add(columnLetters[currColumnIndex].ToString(), new Cell(column.Label, currStyle));
excelRow.Add(columnLetters[currColumnIndex + 1].ToString(), new Cell(q1Rows[rowKey][column.FirstQueryColName].ToString(), currStyle));
excelRow.Add(columnLetters[currColumnIndex + 2].ToString(), new Cell(q2Rows[rowKey][column.SecondQueryColName].ToString(), currStyle));
// Finally, add this row to the recon report
rrExcelService.AddRow(excelRow);
++numDifferencesFound;
}
}
}
}
}
// If no differences were found, write one row to that effect
if (numDifferencesFound == 0)
{
excelRow = new Dictionary<string, Cell>();
excelRow.Add("A", new Cell("No differences found", CellStyle.Bold));
rrExcelService.AddRow(excelRow);
}
else
{
excelRow.Clear();
if (numDifferencesFound == 1)
{
counterText = numDifferencesFound.ToString() + " difference found.";
}
else
{
counterText = numDifferencesFound.ToString() + " differences found.";
}
excelRow.Add("A", new Cell(counterText));
rrExcelService.AddRow(excelRow);
}
}
}
/// <summary>
/// Look at one or two connections required by the recon report. If they're already
/// present in openConnections then do nothing, otherwise create a connection and add it
/// to the collection.
/// </summary>
/// <param name="rr">A recon report</param>
private void openReconConnections(ReconReport rr)
{
// Open connection for first query
try
{
var firstConnection = sources.ConnectionStrings.Where(cs => cs.Name == sources.Queries.Where(query => query.Name == rr.FirstQueryName).First().ConnStringName).First();
openConnection(firstConnection);
if (rr.SecondQueryName != "")
{
var secondConnection = sources.ConnectionStrings.Where(cs => cs.Name == sources.Queries.Where(query => query.Name == rr.SecondQueryName).First().ConnStringName).First();
openConnection(secondConnection);
}
}
catch (Exception ex)
{
throw new Exception(string.Format("Error while trying to open connection for report {0}: {1}", rr.Name, getFullErrorMessage(ex)));
}
}
