private ParameterCollectionUIOptions Create(ExtractionConfiguration extractionConfiguration)
{
var globals = extractionConfiguration.GlobalExtractionFilterParameters;
var paramManager = new ParameterManager(globals);
foreach (var selectedDatasets in extractionConfiguration.SelectedDataSets)
{
var rootFilterContainer = selectedDatasets.RootFilterContainer;
if (rootFilterContainer != null)
{
var allFilters = SqlQueryBuilderHelper.GetAllFiltersUsedInContainerTreeRecursively(rootFilterContainer).ToList();
paramManager.AddParametersFor(allFilters);//query level
}
}
return(new ParameterCollectionUIOptions(UseCaseExtractionConfigurationGlobals, extractionConfiguration, ParameterLevel.Global, paramManager, CreateNewParameterForExtractionConfiguration));
}
protected override void GenerateReport()
{
var rules = Session.Get <OrderRules>((uint)ClientCode);
_client = Session.Get <Client>((uint)ClientCode);
GetOffers();
SetFilterDescriptions();
var matrixHelper = new MatrixHelper(rules);
var sql = matrixHelper.BuyingMatrixCondition(false);
sql.Having = Regex.Replace(sql.Having.Trim(), @"[\d$]", "0");
var matrixPatr = GetPartsData(rules);
var query = SqlQueryBuilderHelper.GetFromPartForCoreTable(sql, false);
query.Join(string.Format(@"
left join farm.Core0 core01 on core01.ProductId = {0}.ProductId and core01.PriceCode = {0}.PriceId and if({0}.ProducerId is not null, {0}.ProducerId = core01.CodeFirmCr, 1)
left join farm.Synonym syn on syn.SynonymCode = core01.SynonymCode
left join farm.Synonymfirmcr synCr on synCr.SynonymFirmCrCode = core01.SynonymFirmCrCode
join catalogs.Producers prod on prod.Id = core.CodeFirmCr
left join farm.Synonym origSyn on origSyn.SynonymCode = Core.SynonymCode
left join farm.Synonymfirmcr origSynCr on origSynCr.SynonymFirmCrCode = Core.SynonymFirmCrCode
{1}
", sql.Alias, matrixPatr.Join));
query.Select(String.Format(@"
{0}.ProductId as ProductId,
{0}.ProducerId as ProducerId,
syn.Synonym as ProductSynonym,
synCr.Synonym as ProducerSynonym,
catalog.Name as CatalogName,
prod.Name as ProducerName,
Core.Code as OriginalCode,
Core.CodeCr as OriginalCodeCr,
origSyn.Synonym as OriginalName,
origSynCr.Synonym as OriginalProducerName,
AT.FirmName as FirmName,
AT.PriceDate as PriceDate", sql.Alias));
query.Select(matrixPatr.Select);
query.Select(sql.Select.Trim(','));
DataAdapter.SelectCommand.CommandText = query.ToSql();
if (rules.OfferMatrix.HasValue)
{
DataAdapter.SelectCommand.Parameters.AddWithValue("ClientCode", ClientCode);
}
var result = new DataTable("Results");
DataAdapter.Fill(result);
foreach (DataRow row in result.Rows)
{
row.SetAdded();
}
var whitePriceName = GetPriceForWhiteMatrix(rules);
result.Columns.Add("BuyingMatrixTypeString", typeof(string));
foreach (DataRow row in result.Rows)
{
row["BuyingMatrixTypeString"] = GetMatrigTypeString(row);
if (row["MatrixPriceName"] is DBNull && !string.IsNullOrEmpty(whitePriceName))
{
row["MatrixPriceName"] = whitePriceName;
}
}
for (int i = 0; i < Header.Count; i++)
{
var row = result.NewRow();
result.Rows.InsertAt(row, 0);
}
PrepareCollumns(result, rules);
SetTableCollomnNames(result);
SetTableCollumnWidth(result);
_dsReport.Tables.Add(result);
}
/// <summary>
/// Creates a new CohortAggregateContainer tree containing a clone container for each container in the original tree and a clone AggregateConfiguration for each in the original tree
/// but with a rename in which AggregateConfigurations in the first tree are expected to start cic_X where X is the original cohort identification configuration ID, this will be replaced
/// with the new clone's ID
/// </summary>
/// <param name="notifier"></param>
/// <param name="original"></param>
/// <param name="clone"></param>
/// <param name="parentToCloneJoinablesDictionary"></param>
public CohortAggregateContainer CloneEntireTreeRecursively(ICheckNotifier notifier, CohortIdentificationConfiguration original, CohortIdentificationConfiguration clone, Dictionary <JoinableCohortAggregateConfiguration, JoinableCohortAggregateConfiguration> parentToCloneJoinablesDictionary)
{
//what is in us?
var contents = GetOrderedContents();
//clone us with same order (in parents)
var cloneContainer = new CohortAggregateContainer((ICatalogueRepository)Repository, Operation);
cloneContainer.Name = Name;
cloneContainer.Order = Order;
cloneContainer.SaveToDatabase();
//for each thing in us
foreach (IOrderable content in contents)
{
int order = content.Order;
var config = content as AggregateConfiguration;
var container = content as CohortAggregateContainer;
//its a config, clone the config and add it to the clone container
if (config != null)
{
var configClone = clone.ImportAggregateConfigurationAsIdentifierList(config, null, false);
notifier.OnCheckPerformed(new CheckEventArgs("Created clone dataset " + configClone + " with ID " + configClone.ID, CheckResult.Success));
cloneContainer.AddChild(configClone, order);
//if the original used any joinable patient index tables
var usedJoins = config.PatientIndexJoinablesUsed;
//our dictionary should have a record of it along with a clone patient index table we should hook our clone up to
foreach (var j in usedJoins)
{
//for some reason the CohortIdentificationConfiguration didn't properly clone the joinable permission or didn't add it to the dictionary
if (!parentToCloneJoinablesDictionary.ContainsKey(j.JoinableCohortAggregateConfiguration))
{
throw new KeyNotFoundException("Configuration " + configClone + " uses Patient Index Table " + j.AggregateConfiguration + " but our dictionary did not have the key, why was that joinable not cloned?");
}
//we do have a clone copy of the joinable permission, set the clone aggregate
var cloneJoinable = parentToCloneJoinablesDictionary[j.JoinableCohortAggregateConfiguration];
var cloneJoinUse = cloneJoinable.AddUser(configClone);
cloneJoinUse.JoinType = j.JoinType;
cloneJoinUse.SaveToDatabase();
//Now! (brace yourself). Some the filters in the AggregateConfiguration we just cloned might reference a table called ix2934 or whetever, this
//is the Joinable we need to do a replace to point them at the correct ix number (although if they are good users they will have aliased any
//patient index columns anyway)
if (configClone.RootFilterContainer_ID != null)
{
foreach (var clonedFilter in SqlQueryBuilderHelper.GetAllFiltersUsedInContainerTreeRecursively(configClone.RootFilterContainer))
{
var oldTableAlias = j.GetJoinTableAlias();
var newTableAlias = cloneJoinUse.GetJoinTableAlias();
clonedFilter.WhereSQL = clonedFilter.WhereSQL.Replace(oldTableAlias, newTableAlias);
clonedFilter.SaveToDatabase();
}
}
}
}
//its another container (a subcontainer), recursively call the clone operation on it and add that subtree to teh clone container
if (container != null)
{
var cloneSubContainer = container.CloneEntireTreeRecursively(notifier, original, clone, parentToCloneJoinablesDictionary);
notifier.OnCheckPerformed(new CheckEventArgs("Created clone container " + cloneSubContainer + " with ID " + cloneSubContainer.ID, CheckResult.Success));
cloneContainer.AddChild(cloneSubContainer);
}
}
//return the clone we created
return(cloneContainer);
}
private void BuildJoinOrder(bool isChecks)
{
_qb = new QueryBuilder(null, null);
var memory = new MemoryRepository();
foreach (TableInfo t in TablesToIsolate)
{
_qb.AddColumn(new ColumnInfoToIColumn(memory, t.ColumnInfos.First()));
}
_primaryTable = TablesToIsolate.Length == 1 ? TablesToIsolate[0] : TablesToIsolate.Single(t => t.IsPrimaryExtractionTable);
_primaryTablePk = _primaryTable.ColumnInfos.Single(c => c.IsPrimaryKey);
_qb.PrimaryExtractionTable = _primaryTable;
_qb.RegenerateSQL();
_joins = _qb.JoinsUsedInQuery ?? new List <JoinInfo>();
_fromSql = SqlQueryBuilderHelper.GetFROMSQL(_qb);
if (!isChecks)
{
foreach (TableInfo tableInfo in TablesToIsolate)
{
_fromSql = _fromSql.Replace(tableInfo.GetFullyQualifiedName(), GetRAWTableNameFullyQualified(tableInfo));
}
}
if (_joins.Any(j => j.GetSupplementalJoins().Any()))
{
throw new Exception("Supplemental (2 column) joins are not supported when resolving multi table primary key collisions");
}
//order the tables in order of dependency
List <TableInfo> tables = new List <TableInfo>();
TableInfo next = _primaryTable;
int overflow = 10;
while (next != null)
{
tables.Add(next);
var jnext = _joins.SingleOrDefault(j => j.PrimaryKey.TableInfo.Equals(next));
if (jnext == null)
{
break;
}
next = jnext.ForeignKey.TableInfo;
if (overflow-- == 0)
{
throw new Exception("Joins resulted in a loop overflow");
}
}
TablesToIsolate = tables.ToArray();
}
public void OpportunisticJoinRequired()
{
var memory = new MemoryRepository();
//tables and columns
TableInfo head = new TableInfo(CatalogueRepository, "Head");
ColumnInfo col1 = new ColumnInfo(CatalogueRepository, "TestResultSetNumber", "int", head);
ColumnInfo col2 = new ColumnInfo(CatalogueRepository, "PK", "int", head);
TableInfo result = new TableInfo(CatalogueRepository, "[biochemistry]..[Result]");
ColumnInfo col3 = new ColumnInfo(CatalogueRepository, "FK", "int", result);
ColumnInfo col4 = new ColumnInfo(CatalogueRepository, "Code", "varchar(10)", result);
ColumnInfo col5 = new ColumnInfo(CatalogueRepository, "[biochemistry]..[Result].[OmgBob]", "varchar(10)", result);
//we can join on col2 = col3
new JoinInfo(CatalogueRepository, col3, col2, ExtractionJoinType.Right, "");
//CASE 1 : Only 1 column used so no join needed
var queryBuilder = new QueryBuilder(null, null);
var icol1 = new ColumnInfoToIColumn(memory, col1);
icol1.Order = 1;
queryBuilder.AddColumn(icol1);
TableInfo primary;
var tablesUsed = SqlQueryBuilderHelper.GetTablesUsedInQuery(queryBuilder, out primary, null);
Assert.AreEqual(1, tablesUsed.Count);
Assert.AreEqual(head, tablesUsed[0]);
//CASE 2 : 2 columns used one from each table so join is needed
queryBuilder = new QueryBuilder(null, null);
queryBuilder.AddColumn(new ColumnInfoToIColumn(memory, col1));
var icol4 = new ColumnInfoToIColumn(memory, col4);
icol4.Order = 2;
queryBuilder.AddColumn(icol4);
tablesUsed = SqlQueryBuilderHelper.GetTablesUsedInQuery(queryBuilder, out primary, null);
Assert.AreEqual(2, tablesUsed.Count);
Assert.AreEqual(head, tablesUsed[0]);
Assert.AreEqual(result, tablesUsed[1]);
Assert.AreEqual(CollapseWhitespace(@"SELECT
TestResultSetNumber,
Code
FROM
[biochemistry]..[Result] Right JOIN Head ON FK = PK"), CollapseWhitespace(queryBuilder.SQL));
var memoryRepository = new MemoryCatalogueRepository();
var spontContainer = new SpontaneouslyInventedFilterContainer(memoryRepository, null, null, FilterContainerOperation.AND);
var spontFilter = new SpontaneouslyInventedFilter(memoryRepository, spontContainer, "[biochemistry]..[Result].[OmgBob] = 'T'",
"My Filter", "Causes spontaneous requirement for joining compeltely", null);
spontContainer.AddChild(spontFilter);
//CASE 3 : Only 1 column from Head but filter contains a reference to Result column
queryBuilder = new QueryBuilder(null, null);
queryBuilder.AddColumn(new ColumnInfoToIColumn(memory, col1));
//without the filter
tablesUsed = SqlQueryBuilderHelper.GetTablesUsedInQuery(queryBuilder, out primary, null);
Assert.AreEqual(1, tablesUsed.Count);
//set the filter
queryBuilder.RootFilterContainer = spontContainer;
//this is super sneaky but makes the queryBuilder populate it's Filters property... basically your not supposed to use SqlQueryBuilderHelper for this kind of thing
Console.WriteLine(queryBuilder.SQL);
queryBuilder.ParameterManager.ClearNonGlobals();
//with the filter
tablesUsed = SqlQueryBuilderHelper.GetTablesUsedInQuery(queryBuilder, out primary, null);
Assert.AreEqual(2, tablesUsed.Count);
}
