private void GenerateQuery()
{
QueryEditor.ReadOnly = false;
CommonFunctionality.ResetChecks();
try
{
var cic = (CohortIdentificationConfiguration)DatabaseObject;
var builder = new CohortQueryBuilder(cic, null);
if (!btnUseCache.Checked && cic.QueryCachingServer_ID.HasValue)
{
builder.CacheServer = null;
}
QueryEditor.Text = builder.SQL;
CommonFunctionality.ScintillaGoRed(QueryEditor, false);
}
catch (Exception ex)
{
QueryEditor.Text = "Could not build Sql:" + ex.Message;
CommonFunctionality.ScintillaGoRed(QueryEditor, true);
CommonFunctionality.Fatal("Failed to build query", ex);
}
QueryEditor.ReadOnly = true;
}
private void RegenerateCodeInQueryEditor()
{
try
{
if (_extractionConfiguration.Cohort_ID == null)
{
throw new Exception("No cohort has been defined for this ExtractionConfiguration");
}
//We are generating what the extraction SQL will be like, that only requires the dataset so empty bundle is fine
_request = new ExtractDatasetCommand(_extractionConfiguration, new ExtractableDatasetBundle(_extractableDataSet));
_request.GenerateQueryBuilder();
QueryEditor.ReadOnly = false;
//get the SQL from the query builder
QueryEditor.Text = _request.QueryBuilder.SQL;
QueryEditor.ReadOnly = true;
CommonFunctionality.ScintillaGoRed(QueryEditor, false);
}
catch (Exception ex)
{
CommonFunctionality.ScintillaGoRed(QueryEditor, ex);
}
}
private void RefreshUIFromDatabase()
{
CommonFunctionality.ResetChecks();
try
{
if (bLoading)
{
return;
}
//only allow reordering when all are visible or only internal are visible otherwise user could select core only and do a reorder leaving supplemental columns as freaky orphans all down at the bottom fo the SQL!
bLoading = true;
List <ExtractionInformation> extractionInformations = GetSelectedExtractionInformations();
//add the available filters
SetupAvailableFilters(extractionInformations);
//generate SQL -- only make it readonly after setting the .Text otherwise it ignores the .Text setting even though it is programatical
QueryPreview.ReadOnly = false;
var collection = GetCollection(extractionInformations);
QueryPreview.Text = collection.GetSql();
CommonFunctionality.ScintillaGoRed(QueryPreview, false);
QueryPreview.ReadOnly = true;
}
catch (Exception ex)
{
CommonFunctionality.ScintillaGoRed(QueryPreview, ex);
CommonFunctionality.Fatal(ex.Message, ex);
}
finally
{
bLoading = false;
}
}
private void RefreshUIFromDatabase()
{
lbPrimaryKeys.Items.Clear();
lbConflictResolutionColumns.Items.Clear();
foreach (var pkCol in _table.ColumnInfos.Where(col => col.IsPrimaryKey))
{
//primary keys are not used to resolve duplication of primary key values (obviously!)
if (pkCol.DuplicateRecordResolutionOrder != null)
{
//unset any that have accidentally gained an order e.g. if user set an order then made a new column a PK
pkCol.DuplicateRecordResolutionOrder = null;
pkCol.SaveToDatabase();
}
lbPrimaryKeys.Items.Add(pkCol);
}
List <IResolveDuplication> resolvers = new List <IResolveDuplication>();
resolvers.AddRange(_table.ColumnInfos.Where(col => !col.IsPrimaryKey));
resolvers.AddRange(_table.PreLoadDiscardedColumns);
//if there is no order yet
if (resolvers.All(r => r.DuplicateRecordResolutionOrder == null))
{
for (int i = 0; i < resolvers.Count; i++)
{
//set one up
resolvers[i].DuplicateRecordResolutionOrder = i;
resolvers[i].SaveToDatabase();
}
}
foreach (IResolveDuplication resolver in resolvers.OrderBy(o => o.DuplicateRecordResolutionOrder).ToArray())
{
//if it starts with hic_
if (SpecialFieldNames.IsHicPrefixed(resolver))
{
//do not use it for duplication resolution
resolver.DuplicateRecordResolutionOrder = null;
resolver.DuplicateRecordResolutionIsAscending = false; //default to descending
resolver.SaveToDatabase();
resolvers.Remove(resolver);
}
}
foreach (IResolveDuplication resolver in resolvers.OrderBy(c => c.DuplicateRecordResolutionOrder))
{
lbConflictResolutionColumns.Items.Add(resolver);
}
QueryEditor.ReadOnly = false;
try
{
//this is used only to generate the SQL preview of how to resolve primary key collisions so no username/password is required - hence the null,null
PrimaryKeyCollisionResolver resolver = new PrimaryKeyCollisionResolver(_table);
QueryEditor.Text = resolver.GenerateSQL();
CommonFunctionality.ScintillaGoRed(QueryEditor, false);
}
catch (Exception e)
{
CommonFunctionality.ScintillaGoRed(QueryEditor, e);
}
QueryEditor.ReadOnly = true;
}
private void RefreshUIFromDatabase()
{
CommonFunctionality.ResetChecks();
try
{
if (bLoading)
{
return;
}
//only allow reordering when all are visible or only internal are visible otherwise user could select core only and do a reorder leaving supplemental columns as freaky orphans all down at the bottom fo the SQL!
bLoading = true;
List <ExtractionInformation> extractionInformations = new List <ExtractionInformation>();
if (rbInternal.Checked)
{
extractionInformations.AddRange(_catalogue.GetAllExtractionInformation(ExtractionCategory.Internal));
}
else
{
//always add the project specific ones
extractionInformations.AddRange(_catalogue.GetAllExtractionInformation(ExtractionCategory.ProjectSpecific));
extractionInformations.AddRange(_catalogue.GetAllExtractionInformation(ExtractionCategory.Core));
if (rbSupplemental.Checked || rbSpecialApproval.Checked)
{
extractionInformations.AddRange(_catalogue.GetAllExtractionInformation(ExtractionCategory.Supplemental));
}
if (rbSpecialApproval.Checked)
{
extractionInformations.AddRange(_catalogue.GetAllExtractionInformation(ExtractionCategory.SpecialApprovalRequired));
}
}
//sort by Default Order
extractionInformations.Sort();
//add to listbox
olvExtractionInformations.ClearObjects();
olvExtractionInformations.AddObjects(extractionInformations.ToArray());
//add the available filters
var filters = extractionInformations.SelectMany(ei => ei.ExtractionFilters).ToArray();
//remove deleted ones
if (olvFilters.Objects != null)
{
foreach (ExtractionFilter f in olvFilters.Objects.Cast <ExtractionFilter>().Except(filters).ToArray())
{
olvFilters.RemoveObject(f);
}
}
//add new ones
foreach (ExtractionFilter f in filters)
{
if (olvFilters.IndexOf(f) == -1)
{
olvFilters.AddObject(f);
}
}
//generate SQL -- only make it readonly after setting the .Text otherwise it ignores the .Text setting even though it is programatical
QueryPreview.ReadOnly = false;
QueryPreview.Text = GenerateExtractionSQLForCatalogue(extractionInformations.ToArray());
CommonFunctionality.ScintillaGoRed(QueryPreview, false);
QueryPreview.ReadOnly = true;
}
catch (Exception ex)
{
CommonFunctionality.ScintillaGoRed(QueryPreview, ex);
CommonFunctionality.Fatal(ex.Message, ex);
}
finally
{
bLoading = false;
}
}