/// <summary>
/// Method to create the measure in the database. This just creates the basic Measure information and
/// does not add values under the Measure. This process is based off creating SQL dynamically based on
/// a template file.
/// </summary>
/// <param name="model">The populated model containing the data needed to create the Measure</param>
public void createMeasure(CreateMeasureModel model)
{
Geographical_NeedsEntities context = new Geographical_NeedsEntities();
Measure newMeasure = context.Measures.Create();
newMeasure.MeasureName = model.MeasureName;
createDimSets(model.selectedDimensions);
IEnumerable <DimensionSet> dimSets = context.Dimensions.Where(x => model.selectedDimensions.Contains(x.DimensionID)).SelectMany(x => x.DimensionSetMembers).Select(x => x.DimensionSet).ToList();
IEnumerable <DimensionSet> dimSetsForExclusion = context.Dimensions.Where(x => !model.selectedDimensions.Contains(x.DimensionID)).SelectMany(x => x.DimensionSetMembers).Select(x => x.DimensionSet).ToList();
IEnumerable <DimensionSet> dimSetsFinal = dimSets.Where(x => !dimSetsForExclusion.Select(y => y.DimensionSetID).Contains(x.DimensionSetID)).Distinct();
foreach (DimensionSet set in dimSetsFinal)
{
MeasureBreakdown newBreakdown = context.MeasureBreakdowns.Create();
newBreakdown.MeasureBreakdownName = String.Format("{0} {1}", model.MeasureName, set.DimensionSetName);
newBreakdown.DimensionSetID = set.DimensionSetID;
newMeasure.MeasureBreakdowns.Add(newBreakdown);
}
context.Measures.Add(newMeasure);
context.SaveChanges();
context.Dispose();
}
/// <summary>
/// A method to add Columns to a Staging Table. This adds them to both the real table
/// and the meta data about the table. The columns to add are based from the Data
/// Table provided.
/// </summary>
/// <param name="dt">The Data Table to use to get the Columns to be added</param>
/// <param name="tableID">The ID of the Staging Table to be added to.</param>
private void addColumnsToTables(DataTable dt, int tableID)
{
Geographical_NeedsEntities context = new Geographical_NeedsEntities();
string datasetName = context.StagingDatasets.Single(x => x.StagingDatasetID.Equals(tableID)).DatasetName;
foreach (DataColumn column in dt.Columns)
{
if (column.ColumnName != "UploadRef")
{
SqlParameter param1 = new SqlParameter("@tableName", datasetName);
SqlParameter param2 = new SqlParameter("@columnName", column.ColumnName);
context.Database.ExecuteSqlCommand("AddColumnToTable @tableName, @columnName",
param1, param2);
}
StagingColumn newColumn = new StagingColumn();
newColumn.ColumnName = column.ColumnName;
newColumn.StagingDatasetID = tableID;
context.StagingColumns.Add(newColumn);
}
context.SaveChanges();
context.Dispose();
}
/// <summary>
/// Method to create a Staging Table initially. This intial creation creates
/// a record of the table in the meta data tables and then a trigger creates the
/// actual table. Initially there are no columns associated with the table
/// as these are added later when the data is uploaded.
/// </summary>
/// <param name="model">
/// The populated model containing the name of the table to be created.
/// </param>
public void createStagingTable(GeneralStagingModel model)
{
Geographical_NeedsEntities context = new Geographical_NeedsEntities();
StagingDataset staging = context.StagingDatasets.Create();
staging.DatasetName = model.TableName;
context.StagingDatasets.Add(staging);
context.SaveChanges();
context.Dispose();
}
/// <summary>
/// Method to create a Dimension based off the model returned from a View. This creates a Dimension
/// and then populates values with those found in the specified Staging Table column
/// </summary>
/// <param name="model">The populated model passed back from the View</param>
public void CreateDimension(CreateDimensionModel model)
{
Geographical_NeedsEntities context = new Geographical_NeedsEntities();
Dimension newDim = context.Dimensions.Create();
newDim.DimensionName = model.DimensionName;
//DimensionSet newSet = context.DimensionSets.Create();
//newSet.DimensionSetName = String.Format("By {0}", model.DimensionName);
//DimensionSetMember newMember = context.DimensionSetMembers.Create();
//newDim.DimensionSetMembers.Add(newMember);
//newSet.DimensionSetMembers.Add(newMember);
String stagingColumnName = context.StagingColumns.Single(x => x.StagingColumnID.Equals(model.DimColumnInStaging)).ColumnName;
String stagingTableName = context.StagingColumns.Single(x => x.StagingColumnID.Equals(model.DimColumnInStaging)).StagingDataset.DatasetName;
String sqlQuery = String.Format("select distinct [{0}] from [{1}]", stagingColumnName, stagingTableName);
IEnumerable <String> stagingDimensionValues = context.Database.SqlQuery <String>(sqlQuery);
foreach (String dimValue in stagingDimensionValues)
{
DimensionValue value = context.DimensionValues.Create();
value.DimensionValue1 = dimValue;
newDim.DimensionValues.Add(value);
}
context.Dimensions.Add(newDim);
//context.DimensionSets.Add(newSet);
//context.DimensionSetMembers.Add(newMember);
context.SaveChanges();
context.Dispose();
}
/// <summary>
/// A method to create the View from the user entered data that
/// has been specified through the web interface. It uses the template
/// for a new View to generate the necessary SQL.
/// </summary>
/// <param name="model">The fully created model</param>
public void CreateView(CreateViewModel model)
{
Geographical_NeedsEntities context = new Geographical_NeedsEntities();
DataView newView = context.DataViews.Create();
newView.ViewName = model.ViewName;
DataViewColumn geogColumn = context.DataViewColumns.Create();
geogColumn.ColumnName = context.GeographyTypes.Single(x => x.GeographyTypeID.Equals(model.SelectedGeographyType)).GeographyType1;
geogColumn.DataView = newView;
context.DataViewColumns.Add(geogColumn);
foreach (var column in model.Columns)
{
DataViewColumn newColumn = context.DataViewColumns.Create();
newColumn.ColumnName = column.ColumnName;
newColumn.DataView = newView;
context.DataViewColumns.Add(newColumn);
}
context.DataViews.Add(newView);
CreateViewTemplate template = new CreateViewTemplate();
template.Model = model;
String output = template.TransformText();
context.Database.ExecuteSqlCommand(output);
context.SaveChanges();
context.Dispose();
}
/// <summary>
/// Method to create a Dimension based off the model returned from a View. This creates a Dimension
/// and then populates values with those found in the specified Staging Table column
/// </summary>
/// <param name="model">The populated model passed back from the View</param>
public void CreateDimension(CreateDimensionModel model)
{
Geographical_NeedsEntities context = new Geographical_NeedsEntities();
Dimension newDim = context.Dimensions.Create();
newDim.DimensionName = model.DimensionName;
//DimensionSet newSet = context.DimensionSets.Create();
//newSet.DimensionSetName = String.Format("By {0}", model.DimensionName);
//DimensionSetMember newMember = context.DimensionSetMembers.Create();
//newDim.DimensionSetMembers.Add(newMember);
//newSet.DimensionSetMembers.Add(newMember);
String stagingColumnName = context.StagingColumns.Single(x => x.StagingColumnID.Equals(model.DimColumnInStaging)).ColumnName;
String stagingTableName = context.StagingColumns.Single(x => x.StagingColumnID.Equals(model.DimColumnInStaging)).StagingDataset.DatasetName;
String sqlQuery = String.Format("select distinct [{0}] from [{1}]", stagingColumnName, stagingTableName);
IEnumerable<String> stagingDimensionValues = context.Database.SqlQuery<String>(sqlQuery);
foreach (String dimValue in stagingDimensionValues)
{
DimensionValue value = context.DimensionValues.Create();
value.DimensionValue1 = dimValue;
newDim.DimensionValues.Add(value);
}
context.Dimensions.Add(newDim);
//context.DimensionSets.Add(newSet);
//context.DimensionSetMembers.Add(newMember);
context.SaveChanges();
context.Dispose();
}
/// <summary>
/// A method to create the View from the user entered data that
/// has been specified through the web interface. It uses the template
/// for a new View to generate the necessary SQL.
/// </summary>
/// <param name="model">The fully created model</param>
public void CreateView(CreateViewModel model)
{
Geographical_NeedsEntities context = new Geographical_NeedsEntities();
DataView newView = context.DataViews.Create();
newView.ViewName = model.ViewName;
DataViewColumn geogColumn = context.DataViewColumns.Create();
geogColumn.ColumnName = context.GeographyTypes.Single(x => x.GeographyTypeID.Equals(model.SelectedGeographyType)).GeographyType1;
geogColumn.DataView = newView;
context.DataViewColumns.Add(geogColumn);
foreach (var column in model.Columns)
{
DataViewColumn newColumn = context.DataViewColumns.Create();
newColumn.ColumnName = column.ColumnName;
newColumn.DataView = newView;
context.DataViewColumns.Add(newColumn);
}
context.DataViews.Add(newView);
CreateViewTemplate template = new CreateViewTemplate();
template.Model = model;
String output = template.TransformText();
context.Database.ExecuteSqlCommand(output);
context.SaveChanges();
context.Dispose();
}
/// <summary>
/// Method to create the measure in the database. This just creates the basic Measure information and
/// does not add values under the Measure. This process is based off creating SQL dynamically based on
/// a template file.
/// </summary>
/// <param name="model">The populated model containing the data needed to create the Measure</param>
public void createMeasure(CreateMeasureModel model)
{
Geographical_NeedsEntities context = new Geographical_NeedsEntities();
Measure newMeasure = context.Measures.Create();
newMeasure.MeasureName = model.MeasureName;
createDimSets(model.selectedDimensions);
IEnumerable<DimensionSet> dimSets = context.Dimensions.Where(x => model.selectedDimensions.Contains(x.DimensionID)).SelectMany(x => x.DimensionSetMembers).Select(x => x.DimensionSet).ToList();
IEnumerable<DimensionSet> dimSetsForExclusion = context.Dimensions.Where(x => !model.selectedDimensions.Contains(x.DimensionID)).SelectMany(x => x.DimensionSetMembers).Select(x => x.DimensionSet).ToList();
IEnumerable<DimensionSet> dimSetsFinal = dimSets.Where(x => !dimSetsForExclusion.Select(y => y.DimensionSetID).Contains(x.DimensionSetID)).Distinct();
foreach (DimensionSet set in dimSetsFinal)
{
MeasureBreakdown newBreakdown = context.MeasureBreakdowns.Create();
newBreakdown.MeasureBreakdownName = String.Format("{0} {1}", model.MeasureName, set.DimensionSetName);
newBreakdown.DimensionSetID = set.DimensionSetID;
newMeasure.MeasureBreakdowns.Add(newBreakdown);
}
context.Measures.Add(newMeasure);
context.SaveChanges();
context.Dispose();
}
private void createDimSets(IEnumerable<int> dimIDs)
{
Geographical_NeedsEntities context = new Geographical_NeedsEntities();
List<DimensionSet> allNewSets = new List<DimensionSet>();
foreach (int dimID in dimIDs)
{
Dimension dim = context.Dimensions.Single(x => x.DimensionID.Equals(dimID));
foreach (DimensionSet set in allNewSets)
{
DimensionSet derivedSet = context.DimensionSets.Create();
derivedSet.DimensionSetName = String.Format("{0} and {1}", set.DimensionSetName, dim.DimensionName);
context.DimensionSets.Add(derivedSet);
DimensionSetMember newDimMember = context.DimensionSetMembers.Create();
newDimMember.DimensionSet = derivedSet;
newDimMember.Dimension = dim;
context.DimensionSetMembers.Add(newDimMember);
foreach (DimensionSetMember dimSetMember in set.DimensionSetMembers)
{
DimensionSetMember derivedMember = context.DimensionSetMembers.Create();
derivedMember.Dimension = dimSetMember.Dimension;
derivedSet.DimensionSetMembers.Add(derivedMember);
context.DimensionSetMembers.Add(derivedMember);
}
foreach (DimensionSetCombination combination in set.DimensionSetCombinations)
{
foreach (DimensionValue dimValue in dim.DimensionValues)
{
DimensionSetCombination derivedCombination = context.DimensionSetCombinations.Create();
derivedCombination.DimensionSetCombinationName = String.Format("{0} | {1}: {2}", combination.DimensionSetCombinationName, dim.DimensionName, dimValue.DimensionValue1);
derivedCombination.DimensionSet = derivedSet;
context.DimensionSetCombinations.Add(derivedCombination);
DimensionSetCombinationMember newCombinationMember = context.DimensionSetCombinationMembers.Create();
newCombinationMember.DimensionSetCombination = derivedCombination;
newCombinationMember.DimensionValue = dimValue;
context.DimensionSetCombinationMembers.Add(newCombinationMember);
foreach (DimensionSetCombinationMember dimSetCombinationMember in combination.DimensionSetCombinationMembers)
{
DimensionSetCombinationMember derivedCombinationMember = context.DimensionSetCombinationMembers.Create();
derivedCombinationMember.DimensionSetCombination = derivedCombination;
derivedCombinationMember.DimensionValue = dimSetCombinationMember.DimensionValue;
context.DimensionSetCombinationMembers.Add(derivedCombinationMember);
}
}
}
}
String newDimSetName = String.Format("By {0}", dim.DimensionName);
if (context.DimensionSets.Count(x => x.DimensionSetName.Equals(newDimSetName)) == 0)
{
DimensionSet newSet = context.DimensionSets.Create();
newSet.DimensionSetName = String.Format("By {0}", dim.DimensionName);
DimensionSetMember newMember = context.DimensionSetMembers.Create();
dim.DimensionSetMembers.Add(newMember);
newSet.DimensionSetMembers.Add(newMember);
foreach (DimensionValue dimValue in dim.DimensionValues)
{
DimensionSetCombination newCombination = context.DimensionSetCombinations.Create();
newCombination.DimensionSet = newSet;
newCombination.DimensionSetCombinationName = String.Format("{0}: {1}", dim.DimensionName, dimValue.DimensionValue1);
DimensionSetCombinationMember newCombinationMember = context.DimensionSetCombinationMembers.Create();
newCombinationMember.DimensionValue = dimValue;
newCombinationMember.DimensionSetCombination = newCombination;
context.DimensionSetCombinations.Add(newCombination);
context.DimensionSetCombinationMembers.Add(newCombinationMember);
}
allNewSets.Add(newSet);
}
}
context.SaveChanges();
context.Dispose();
}
private void createDimSets(IEnumerable <int> dimIDs)
{
Geographical_NeedsEntities context = new Geographical_NeedsEntities();
List <DimensionSet> allNewSets = new List <DimensionSet>();
foreach (int dimID in dimIDs)
{
Dimension dim = context.Dimensions.Single(x => x.DimensionID.Equals(dimID));
foreach (DimensionSet set in allNewSets)
{
DimensionSet derivedSet = context.DimensionSets.Create();
derivedSet.DimensionSetName = String.Format("{0} and {1}", set.DimensionSetName, dim.DimensionName);
context.DimensionSets.Add(derivedSet);
DimensionSetMember newDimMember = context.DimensionSetMembers.Create();
newDimMember.DimensionSet = derivedSet;
newDimMember.Dimension = dim;
context.DimensionSetMembers.Add(newDimMember);
foreach (DimensionSetMember dimSetMember in set.DimensionSetMembers)
{
DimensionSetMember derivedMember = context.DimensionSetMembers.Create();
derivedMember.Dimension = dimSetMember.Dimension;
derivedSet.DimensionSetMembers.Add(derivedMember);
context.DimensionSetMembers.Add(derivedMember);
}
foreach (DimensionSetCombination combination in set.DimensionSetCombinations)
{
foreach (DimensionValue dimValue in dim.DimensionValues)
{
DimensionSetCombination derivedCombination = context.DimensionSetCombinations.Create();
derivedCombination.DimensionSetCombinationName = String.Format("{0} | {1}: {2}", combination.DimensionSetCombinationName, dim.DimensionName, dimValue.DimensionValue1);
derivedCombination.DimensionSet = derivedSet;
context.DimensionSetCombinations.Add(derivedCombination);
DimensionSetCombinationMember newCombinationMember = context.DimensionSetCombinationMembers.Create();
newCombinationMember.DimensionSetCombination = derivedCombination;
newCombinationMember.DimensionValue = dimValue;
context.DimensionSetCombinationMembers.Add(newCombinationMember);
foreach (DimensionSetCombinationMember dimSetCombinationMember in combination.DimensionSetCombinationMembers)
{
DimensionSetCombinationMember derivedCombinationMember = context.DimensionSetCombinationMembers.Create();
derivedCombinationMember.DimensionSetCombination = derivedCombination;
derivedCombinationMember.DimensionValue = dimSetCombinationMember.DimensionValue;
context.DimensionSetCombinationMembers.Add(derivedCombinationMember);
}
}
}
}
String newDimSetName = String.Format("By {0}", dim.DimensionName);
if (context.DimensionSets.Count(x => x.DimensionSetName.Equals(newDimSetName)) == 0)
{
DimensionSet newSet = context.DimensionSets.Create();
newSet.DimensionSetName = String.Format("By {0}", dim.DimensionName);
DimensionSetMember newMember = context.DimensionSetMembers.Create();
dim.DimensionSetMembers.Add(newMember);
newSet.DimensionSetMembers.Add(newMember);
foreach (DimensionValue dimValue in dim.DimensionValues)
{
DimensionSetCombination newCombination = context.DimensionSetCombinations.Create();
newCombination.DimensionSet = newSet;
newCombination.DimensionSetCombinationName = String.Format("{0}: {1}", dim.DimensionName, dimValue.DimensionValue1);
DimensionSetCombinationMember newCombinationMember = context.DimensionSetCombinationMembers.Create();
newCombinationMember.DimensionValue = dimValue;
newCombinationMember.DimensionSetCombination = newCombination;
context.DimensionSetCombinations.Add(newCombination);
context.DimensionSetCombinationMembers.Add(newCombinationMember);
}
allNewSets.Add(newSet);
}
}
context.SaveChanges();
context.Dispose();
}