/// <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); } } }