/// <summary> /// Internal helper method to return the data in a View as a Data Table that can be added to /// a View Model in the public method. This dynamically generates SQL to query the table. /// </summary> /// <param name="viewID">The ID of the Staging Table to be queried</param> /// <returns></returns> private DataTable getViewDataDetail(int viewID) { DataTable dt; SqlDataAdapter sda; Geographical_NeedsEntities context = new Geographical_NeedsEntities(); String columns = String.Join(",", context.DataViews.Single(x => x.DataViewID.Equals(viewID)).DataViewColumns.Select(x => String.Format("[{0}]", x.ColumnName))); String viewName = context.DataViews.Single(x => x.DataViewID.Equals(viewID)).ViewName; String sql = String.Format("Select {1} from [{0}]", viewName, columns); using (SqlConnection connection = new SqlConnection( context.Database.Connection.ConnectionString)) { SqlCommand command = new SqlCommand(sql, connection); command.Connection.Open(); sda = new SqlDataAdapter(command); dt = new DataTable("Results"); sda.Fill(dt); } context.Dispose(); return(dt); }
/// <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> /// Method to return the details of a Staging Table from the /// database. This includes the name of the Staging Table and all /// the columns associated with it. /// </summary> /// <param name="datasetID">The ID of the Staging Table to be returned</param> /// <returns></returns> public StagingDetailModel getStagingDetails(int datasetID) { DataTable dt; SqlDataAdapter sda; StagingDetailModel model = new StagingDetailModel(); Geographical_NeedsEntities context = new Geographical_NeedsEntities(); model.StagingDatasetName = context.StagingDatasets.Single(x => x.StagingDatasetID.Equals(datasetID)).DatasetName; List <String> columns = context.StagingColumns.Where(x => x.StagingDatasetID.Equals(datasetID)).Select(x => x.ColumnName).ToList(); String sql = String.Format("Select {1} from [{0}]", model.StagingDatasetName, String.Join(",", columns.Select(x => String.Format("[{0}]", x)))); using (SqlConnection connection = new SqlConnection( context.Database.Connection.ConnectionString)) { SqlCommand command = new SqlCommand(sql, connection); command.Connection.Open(); sda = new SqlDataAdapter(command); dt = new DataTable("Results"); sda.Fill(dt); } context.Dispose(); model.Data = dt; return(model); }
/// <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> /// Method to add the data from a DataTable to a Staging Table. This /// uses a SQLBulkCopy to update the database /// </summary> /// <param name="dt">The Table containing the data</param> /// <param name="tableID">The ID of the table to be updated.</param> private void copyDataToTable(DataTable dt, int tableID) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); string datasetName = context.StagingDatasets.Single(x => x.StagingDatasetID.Equals(tableID)).DatasetName; using (SqlConnection conn = new SqlConnection(context.Database.Connection.ConnectionString)) { //make our command and dispose at the end using (var copy = new SqlBulkCopy(conn)) { //Open our connection conn.Open(); ///Set target table and tell the number of rows copy.DestinationTableName = datasetName; copy.BatchSize = dt.Rows.Count; try { //Send it to the server copy.WriteToServer(dt); } catch (Exception ex) { } } } 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> /// Validate that the CreateMeasureModel is valid prior to /// submission to the database /// </summary> /// <param name="model">The model to be validated</param> /// <returns>Boolean indicating valid or not</returns> public Boolean isCreateMeasureModelValid(CreateMeasureModel model) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); int count = context.Measures.Where(x => x.MeasureName.Equals(model.MeasureName)).Count(); return(count == 0); }
/// <summary> /// Validate that the Create Data View Model is valid prior to /// submission to the database /// </summary> /// <param name="model">The model to be validated</param> /// <returns>Boolean indicating valid or not</retu public Boolean isCreateViewModelValid(CreateViewModel model) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); int count = context.DataViews.Where(x => x.ViewName.Equals(model.ViewName)).Count(); return(count == 0); }
/// <summary> /// Validate that the Create Staging Table Model is valid prior to /// submission to the database /// </summary> /// <param name="model">The model to be validated</param> /// <returns>Boolean indicating valid or not</returns> public Boolean isCreateStagingModelValid(GeneralStagingModel model) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); int count = context.StagingDatasets.Where(x => x.DatasetName.Equals(model.TableName)).Count(); return(count == 0); }
/// <summary> /// Validate that the CreateDimensionModel is valid prior to /// submission to the database /// </summary> /// <param name="model">The model to be validated</param> /// <returns>Boolean indicating valid or not</returns> public Boolean IsCreateDimensionModelValid(CreateDimensionModel model) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); int count = context.Dimensions.Where(x => x.DimensionName.Equals(model.DimensionName)).Count(); return(count == 0); }
/// <summary> /// Method to get a list of Measure Breakdowns that are under a Measure /// </summary> /// <param name="measureID">The ID of the measure</param> /// <returns>A set of Select List Items containing the Measure Breakdowns</returns> public IEnumerable <SelectListItem> getMeasureBreakdownsForMeasure(int measureID) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); return(context.MeasureBreakdowns.Where(x => x.MeasureID.Equals(measureID)).Select(x => new SelectListItem() { Text = x.MeasureBreakdownName, Value = x.MeasureBreakdownID.ToString() })); }
///MEASURES /// <summary> /// Method to create and populate a CreateMeasureModel ready for the View to use /// </summary> /// <param name="datasetID">The ID of the Staging Table this Measure is based off</param> /// <returns></returns> public CreateMeasureModel populateCreateMeasureModel(int datasetID) { CreateMeasureModel model = new CreateMeasureModel(); Geographical_NeedsEntities context = new Geographical_NeedsEntities(); model.StagingDatasetID = datasetID; model.StagingTableName = context.StagingDatasets.Single(x => x.StagingDatasetID.Equals(datasetID)).DatasetName; model.StagingColumnsForMeasure = context.StagingDatasets.Single(x => x.StagingDatasetID.Equals(datasetID)).StagingColumns.Select(x => new SelectListItem() { Text = x.ColumnName, Value = x.StagingColumnID.ToString() }); model.StagingColumnsForGeography = context.StagingDatasets.Single(x => x.StagingDatasetID.Equals(datasetID)).StagingColumns.Select(x => new SelectListItem() { Text = x.ColumnName, Value = x.StagingColumnID.ToString() }); model.GeographyTypes = context.GeographyTypes.Select(x => new SelectListItem() { Text = x.GeographyType1, Value = x.GeographyTypeID.ToString() }); model.DimensionsForMeasureBreakdown = context.Dimensions.Select(x => new SelectListItem { Text = x.DimensionName, Value = x.DimensionID.ToString() }); List <CreateMeasureDetailModel> modelDetails = new List <CreateMeasureDetailModel>(); foreach (StagingColumn column in context.StagingDatasets.Single(x => x.StagingDatasetID.Equals(datasetID)).StagingColumns) { if (column.ColumnName != "UploadRef") { CreateMeasureDetailModel detail = new CreateMeasureDetailModel(); detail.StagingColumnID = column.StagingColumnID; detail.StagingColumnName = column.ColumnName; detail.AvailableDimensions = context.Dimensions.ToList().Select(x => new SelectListItem() { Text = x.DimensionName, Value = x.DimensionID.ToString() }); modelDetails.Add(detail); } } model.MeasureDetails = modelDetails; return(model); }
/// <summary> /// Method to get a list of Dimension Set Combinations which are related /// to all the Measures Instance values under a breakdown /// </summary> /// <param name="measureBreadownID">The ID of the Measure Breakdown</param> /// <returns>A set of Select List Items containing the Dimension Set Combinations</returns> public IEnumerable <SelectListItem> getDimensionSetCombinationsForMeasureBreakdowns(int measureBreadownID) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); return(context.MeasureBreakdowns.Single(x => x.MeasureBreakdownID.Equals(measureBreadownID)).MeasureInstances.Select(x => x.DimensionSetCombination).Distinct().Select(x => new SelectListItem() { Text = x.DimensionSetCombinationName, Value = x.DimensionSetCombinationID.ToString() })); }
/// <summary> /// Method to return the model which is used to map the Staging Dimension Values to Dimension Values /// in the database. This is used in the second stage in the Create Measure process. /// </summary> /// <param name="mappings">The mappings between the Staging Dimension Column and a Dimension in the Database. This is passed in as a Tuple where the first item is a Staging Column ID and the second item a Dimension ID</param> /// <param name="stagingTableName">The name of the staging table being used</param> /// <param name="measureName">The name of the measure to be created</param> /// <param name="measureColumnStagingID">The ID of the column used for the Measure values in Staging</param> /// <param name="geographyColumnID">The ID of the colum used for the Geography value in Staging</param> /// <returns></returns> public MeasureValueModel populateMeasureValueModel(IEnumerable <Tuple <int, int> > mappings, String stagingTableName, String measureName, int?measureColumnStagingID, int geographyColumnID, int geographyTypeID) { List <MeasureValueModel> retVal = new List <MeasureValueModel>(); Geographical_NeedsEntities context = new Geographical_NeedsEntities(); MeasureValueModel model = new MeasureValueModel(); if (measureColumnStagingID.HasValue) { model.MeasureStagingColumnName = context.StagingColumns.Single(x => x.StagingColumnID.Equals(measureColumnStagingID.Value)).ColumnName; model.UseMeasureColumn = true; } else { model.UseMeasureColumn = false; } model.GeographyTypeID = geographyTypeID; model.StagingGeographyColumn = context.StagingColumns.Single(x => x.StagingColumnID.Equals(geographyColumnID)).ColumnName; model.MeasureName = measureName; model.StagingTableName = stagingTableName; List <MeasureValueDetailModel> detailModels = new List <MeasureValueDetailModel>(); foreach (Tuple <int, int> tuple in mappings) { String dimColumnInStaging = context.StagingColumns.Single(x => x.StagingColumnID.Equals(tuple.Item1)).ColumnName; String sqlQuery = String.Format("select distinct {0} from {1}", dimColumnInStaging, stagingTableName); IEnumerable <String> stagingMeasureValues = context.Database.SqlQuery <String>(sqlQuery); foreach (DimensionValue dim in context.DimensionValues.Where(x => x.DimensionID.Equals(tuple.Item2))) { MeasureValueDetailModel modelDetail = new MeasureValueDetailModel(); modelDetail.DimensionID = tuple.Item2; modelDetail.StagingDimensionValues = stagingMeasureValues.Select(x => new SelectListItem { Text = x, Value = x, Selected = dim.DimensionValue1.Equals(x) }); modelDetail.DimValue = dim.DimensionValue1; modelDetail.DimValueID = dim.DimensionValueID; modelDetail.DimColumnInStaging = dimColumnInStaging; detailModels.Add(modelDetail); } } model.MeasureValueDetails = detailModels; return(model); }
/// <summary> /// This method returns a list of all the Staging Tables held in the Database /// </summary> /// <returns></returns> public IEnumerable <GeneralStagingModel> getStagingTables() { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); IEnumerable <GeneralStagingModel> tables = context.StagingDatasets.Select(x => new GeneralStagingModel() { TableName = x.DatasetName, TableID = x.StagingDatasetID }); return(tables); }
/// <summary> /// Method which returns all the Data Views held in the Database /// </summary> /// <returns></returns> public IEnumerable <DataViewModel> getViews() { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); IEnumerable <DataViewModel> models = context.DataViews.Select(x => new DataViewModel() { DataViewID = x.DataViewID, DataViewName = x.ViewName }); return(models); }
/// <summary> /// A method to get the basic Create View model with drop down /// data populated /// </summary> /// <returns>The populated model</returns> public CreateViewModel getDefaultCreateViewModel() { CreateViewModel model = new CreateViewModel(); Geographical_NeedsEntities context = new Geographical_NeedsEntities(); model.GeographyTypes = context.GeographyTypes.Select(x => new SelectListItem() { Text = x.GeographyType1, Value = x.GeographyTypeID.ToString() }); return(model); }
/// <summary> /// A method to get back all the data held in a view and return a model which /// can be used to display the data /// </summary> /// <param name="viewID">The database ID of the View to be returned</param> /// <returns></returns> public DataViewDetailModel getViewData(int viewID) { DataViewDetailModel model = new DataViewDetailModel(); Geographical_NeedsEntities context = new Geographical_NeedsEntities(); model.ViewName = context.DataViews.Single(x => x.DataViewID.Equals(viewID)).ViewName; // model.ViewData = getViewDataDetailFromService(viewID); model.ViewData = getViewDataDetail(viewID); return(model); }
/// <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(); }
/// DIMENSIONS /// <summary> /// Method to create and populate a CreateDimensionModel ready for the View to use /// </summary> /// <param name="datasetID">The ID of the Staging Table this Dimension is based off</param> /// <returns></returns> public CreateDimensionModel PopulateCreateDimensionModel(int datasetID) { CreateDimensionModel model = new CreateDimensionModel(); Geographical_NeedsEntities context = new Geographical_NeedsEntities(); model.StagingDatasetID = datasetID; model.StagingColumnsForDimension = context.StagingDatasets.Single(x => x.StagingDatasetID.Equals(datasetID)).StagingColumns.Select(x => new SelectListItem() { Text = x.ColumnName, Value = x.StagingColumnID.ToString() }); return(model); }
/// <summary> /// A method to get the model which contains the /// data to add a column to a View /// </summary> /// <returns>The populated model</returns> public CreateViewColumnModel getDefaultColumnModel() { CreateViewColumnModel model = new CreateViewColumnModel(); Geographical_NeedsEntities context = new Geographical_NeedsEntities(); model.Measures = context.Measures.Select(x => new SelectListItem() { Text = x.MeasureName, Value = x.MeasureID.ToString() }); model.MeasureBreakdowns = new List <SelectListItem>(); model.DimensionValues = new List <SelectListItem>(); return(model); }
/// <summary> /// Contains the data passed back from the view which can then be added into the model dynamically /// </summary> /// <param name="measureID"> The ID of the measure used</param> /// <param name="measureBreakdownID"> The ID of the measure breakdown used</param> /// <param name="dimValueID"> The ID of the Dim combinations to use</param> /// <param name="newColumnName"> The name of the new column</param> /// <returns>A model to be added into the View</returns> public ViewColumnModel getColumnModel(int measureID, int measureBreakdownID, int dimValueID, String newColumnName) { ViewColumnModel model = new ViewColumnModel(); Geographical_NeedsEntities context = new Geographical_NeedsEntities(); model.ColumnName = newColumnName; model.SelectedMeasureID = measureID; model.SelectedMeasure = context.Measures.Single(x => x.MeasureID.Equals(measureID)).MeasureName; model.SelectedMeasureBreakdownID = measureBreakdownID; model.SelectedMeasureBreakdown = context.MeasureBreakdowns.Single(x => x.MeasureBreakdownID.Equals(measureBreakdownID)).MeasureBreakdownName; model.SelectedDimensionValueID = dimValueID; model.SelectedDimensionValue = context.DimensionSetCombinations.Single(x => x.DimensionSetCombinationID.Equals(dimValueID)).DimensionSetCombinationName; return(model); }
/// <summary> /// Contains the data passed back from the view which can then be added into the model dynamically /// </summary> /// <param name="measureID"> The ID of the measure used</param> /// <param name="measureBreakdownID"> The ID of the measure breakdown used</param> /// <param name="dimValueID"> The ID of the Dim combinations to use</param> /// <param name="newColumnName"> The name of the new column</param> /// <returns>A model to be added into the View</returns> public ViewColumnModel getColumnModel(int measureID, int measureBreakdownID, int dimValueID, String newColumnName) { ViewColumnModel model = new ViewColumnModel(); Geographical_NeedsEntities context = new Geographical_NeedsEntities(); model.ColumnName = newColumnName; model.SelectedMeasureID = measureID; model.SelectedMeasure = context.Measures.Single(x => x.MeasureID.Equals(measureID)).MeasureName; model.SelectedMeasureBreakdownID = measureBreakdownID; model.SelectedMeasureBreakdown = context.MeasureBreakdowns.Single(x => x.MeasureBreakdownID.Equals(measureBreakdownID)).MeasureBreakdownName; model.SelectedDimensionValueID = dimValueID; model.SelectedDimensionValue = context.DimensionSetCombinations.Single(x => x.DimensionSetCombinationID.Equals(dimValueID)).DimensionSetCombinationName; return model; }
public void createMeasureValues(MeasureValueModel model) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); var breakdowns = context.Measures.Single(x => x.MeasureName.Equals(model.MeasureName)).MeasureBreakdowns; foreach (var breakdown in breakdowns) { var combinations = breakdown.DimensionSet.DimensionSetCombinations; foreach (var combination in combinations) { var values = combination.DimensionSetCombinationMembers.Select(x => x.DimensionValue); List <String> whereClauses = new List <string>(); foreach (var value in values) { var detail = model.MeasureValueDetails.Single(x => x.DimValueID.Equals(value.DimensionValueID)); whereClauses.Add(String.Format("[{0}] = '{1}'", detail.DimColumnInStaging, detail.DimValueInStaging.Replace("'", "''"))); } String whereClause = String.Format("Where {0}", String.Join(" AND ", whereClauses)); InsertMeasureValuesTemplate template = new InsertMeasureValuesTemplate(); template.DimensionSetCombinationID = combination.DimensionSetCombinationID; template.GeographyTypeID = model.GeographyTypeID; template.MeasureBreakdownID = breakdown.MeasureBreakdownID; template.MeasureColumnName = model.MeasureStagingColumnName; template.StagingGeographyColumn = model.StagingGeographyColumn; template.StagingTableName = model.StagingTableName; template.UseMeasureColumn = model.UseMeasureColumn; template.WhereClause = whereClause; String output = template.TransformText(); context.Database.ExecuteSqlCommand(output); } } 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> /// 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(); }
public void createMeasureValues(MeasureValueModel model) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); var breakdowns = context.Measures.Single(x => x.MeasureName.Equals(model.MeasureName)).MeasureBreakdowns; foreach (var breakdown in breakdowns) { var combinations = breakdown.DimensionSet.DimensionSetCombinations; foreach (var combination in combinations) { var values = combination.DimensionSetCombinationMembers.Select(x => x.DimensionValue); List<String> whereClauses = new List<string>(); foreach (var value in values) { var detail = model.MeasureValueDetails.Single(x => x.DimValueID.Equals(value.DimensionValueID)); whereClauses.Add(String.Format("[{0}] = '{1}'", detail.DimColumnInStaging, detail.DimValueInStaging.Replace("'", "''"))); } String whereClause = String.Format("Where {0}", String.Join(" AND ", whereClauses)); InsertMeasureValuesTemplate template = new InsertMeasureValuesTemplate(); template.DimensionSetCombinationID = combination.DimensionSetCombinationID; template.GeographyTypeID = model.GeographyTypeID; template.MeasureBreakdownID = breakdown.MeasureBreakdownID; template.MeasureColumnName = model.MeasureStagingColumnName; template.StagingGeographyColumn = model.StagingGeographyColumn; template.StagingTableName = model.StagingTableName; template.UseMeasureColumn = model.UseMeasureColumn; template.WhereClause = whereClause; String output = template.TransformText(); context.Database.ExecuteSqlCommand(output); } } context.Dispose(); }
/// <summary> /// Validate that the Create Data View Model is valid prior to /// submission to the database /// </summary> /// <param name="model">The model to be validated</param> /// <returns>Boolean indicating valid or not</retu public Boolean isCreateViewModelValid(CreateViewModel model) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); int count = context.DataViews.Where(x => x.ViewName.Equals(model.ViewName)).Count(); return count == 0; }
/// <summary> /// Internal helper method to return the data in a View as a Data Table that can be added to /// a View Model in the public method. This dynamically generates SQL to query the table. /// </summary> /// <param name="viewID">The ID of the Staging Table to be queried</param> /// <returns></returns> private DataTable getViewDataDetail(int viewID) { DataTable dt; SqlDataAdapter sda; Geographical_NeedsEntities context = new Geographical_NeedsEntities(); String columns = String.Join(",", context.DataViews.Single(x => x.DataViewID.Equals(viewID)).DataViewColumns.Select(x => String.Format("[{0}]", x.ColumnName))); String viewName = context.DataViews.Single(x => x.DataViewID.Equals(viewID)).ViewName; String sql = String.Format("Select {1} from [{0}]", viewName, columns); using (SqlConnection connection = new SqlConnection( context.Database.Connection.ConnectionString)) { SqlCommand command = new SqlCommand(sql, connection); command.Connection.Open(); sda = new SqlDataAdapter(command); dt = new DataTable("Results"); sda.Fill(dt); } context.Dispose(); return dt; }
/// <summary> /// A method to get back all the data held in a view and return a model which /// can be used to display the data /// </summary> /// <param name="viewID">The database ID of the View to be returned</param> /// <returns></returns> public DataViewDetailModel getViewData(int viewID) { DataViewDetailModel model = new DataViewDetailModel(); Geographical_NeedsEntities context = new Geographical_NeedsEntities(); model.ViewName = context.DataViews.Single(x => x.DataViewID.Equals(viewID)).ViewName; // model.ViewData = getViewDataDetailFromService(viewID); model.ViewData = getViewDataDetail(viewID); return model; }
/// <summary> /// Method which returns all the Data Views held in the Database /// </summary> /// <returns></returns> public IEnumerable<DataViewModel> getViews() { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); IEnumerable<DataViewModel> models = context.DataViews.Select(x => new DataViewModel() { DataViewID = x.DataViewID, DataViewName = x.ViewName }); return models; }
/// <summary> /// Method to get a list of Dimension Set Combinations which are related /// to all the Measures Instance values under a breakdown /// </summary> /// <param name="measureBreadownID">The ID of the Measure Breakdown</param> /// <returns>A set of Select List Items containing the Dimension Set Combinations</returns> public IEnumerable<SelectListItem> getDimensionSetCombinationsForMeasureBreakdowns(int measureBreadownID) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); return context.MeasureBreakdowns.Single(x => x.MeasureBreakdownID.Equals(measureBreadownID)).MeasureInstances.Select(x => x.DimensionSetCombination).Distinct().Select(x => new SelectListItem() { Text = x.DimensionSetCombinationName, Value = x.DimensionSetCombinationID.ToString() }); }
/// <summary> /// Method to get a list of Measure Breakdowns that are under a Measure /// </summary> /// <param name="measureID">The ID of the measure</param> /// <returns>A set of Select List Items containing the Measure Breakdowns</returns> public IEnumerable<SelectListItem> getMeasureBreakdownsForMeasure(int measureID) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); return context.MeasureBreakdowns.Where(x => x.MeasureID.Equals(measureID)).Select(x => new SelectListItem() { Text = x.MeasureBreakdownName, Value = x.MeasureBreakdownID.ToString() }); }
/// <summary> /// Validate that the CreateMeasureModel is valid prior to /// submission to the database /// </summary> /// <param name="model">The model to be validated</param> /// <returns>Boolean indicating valid or not</returns> public Boolean isCreateMeasureModelValid(CreateMeasureModel model) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); int count = context.Measures.Where(x => x.MeasureName.Equals(model.MeasureName)).Count(); return count == 0; }
/// <summary> /// Validate that the CreateDimensionModel is valid prior to /// submission to the database /// </summary> /// <param name="model">The model to be validated</param> /// <returns>Boolean indicating valid or not</returns> public Boolean IsCreateDimensionModelValid(CreateDimensionModel model) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); int count = context.Dimensions.Where(x => x.DimensionName.Equals(model.DimensionName)).Count(); return count == 0; }
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(); }
/// <summary> /// Method to return the model which is used to map the Staging Dimension Values to Dimension Values /// in the database. This is used in the second stage in the Create Measure process. /// </summary> /// <param name="mappings">The mappings between the Staging Dimension Column and a Dimension in the Database. This is passed in as a Tuple where the first item is a Staging Column ID and the second item a Dimension ID</param> /// <param name="stagingTableName">The name of the staging table being used</param> /// <param name="measureName">The name of the measure to be created</param> /// <param name="measureColumnStagingID">The ID of the column used for the Measure values in Staging</param> /// <param name="geographyColumnID">The ID of the colum used for the Geography value in Staging</param> /// <returns></returns> public MeasureValueModel populateMeasureValueModel(IEnumerable<Tuple<int, int>> mappings, String stagingTableName, String measureName, int? measureColumnStagingID, int geographyColumnID, int geographyTypeID) { List<MeasureValueModel> retVal = new List<MeasureValueModel>(); Geographical_NeedsEntities context = new Geographical_NeedsEntities(); MeasureValueModel model = new MeasureValueModel(); if (measureColumnStagingID.HasValue) { model.MeasureStagingColumnName = context.StagingColumns.Single(x => x.StagingColumnID.Equals(measureColumnStagingID.Value)).ColumnName; model.UseMeasureColumn = true; } else { model.UseMeasureColumn = false; } model.GeographyTypeID = geographyTypeID; model.StagingGeographyColumn = context.StagingColumns.Single(x => x.StagingColumnID.Equals(geographyColumnID)).ColumnName; model.MeasureName = measureName; model.StagingTableName = stagingTableName; List<MeasureValueDetailModel> detailModels = new List<MeasureValueDetailModel>(); foreach (Tuple<int, int> tuple in mappings) { String dimColumnInStaging = context.StagingColumns.Single(x => x.StagingColumnID.Equals(tuple.Item1)).ColumnName; String sqlQuery = String.Format("select distinct {0} from {1}", dimColumnInStaging, stagingTableName); IEnumerable<String> stagingMeasureValues = context.Database.SqlQuery<String>(sqlQuery); foreach (DimensionValue dim in context.DimensionValues.Where(x => x.DimensionID.Equals(tuple.Item2))) { MeasureValueDetailModel modelDetail = new MeasureValueDetailModel(); modelDetail.DimensionID = tuple.Item2; modelDetail.StagingDimensionValues = stagingMeasureValues.Select(x => new SelectListItem { Text = x, Value = x, Selected = dim.DimensionValue1.Equals(x) }); modelDetail.DimValue = dim.DimensionValue1; modelDetail.DimValueID = dim.DimensionValueID; modelDetail.DimColumnInStaging = dimColumnInStaging; detailModels.Add(modelDetail); } } model.MeasureValueDetails = detailModels; return model; }
/// DIMENSIONS /// <summary> /// Method to create and populate a CreateDimensionModel ready for the View to use /// </summary> /// <param name="datasetID">The ID of the Staging Table this Dimension is based off</param> /// <returns></returns> public CreateDimensionModel PopulateCreateDimensionModel(int datasetID) { CreateDimensionModel model = new CreateDimensionModel(); Geographical_NeedsEntities context = new Geographical_NeedsEntities(); model.StagingDatasetID = datasetID; model.StagingColumnsForDimension = context.StagingDatasets.Single(x => x.StagingDatasetID.Equals(datasetID)).StagingColumns.Select(x => new SelectListItem() { Text = x.ColumnName, Value = x.StagingColumnID.ToString() }); return model; }
///MEASURES /// <summary> /// Method to create and populate a CreateMeasureModel ready for the View to use /// </summary> /// <param name="datasetID">The ID of the Staging Table this Measure is based off</param> /// <returns></returns> public CreateMeasureModel populateCreateMeasureModel(int datasetID) { CreateMeasureModel model = new CreateMeasureModel(); Geographical_NeedsEntities context = new Geographical_NeedsEntities(); model.StagingDatasetID = datasetID; model.StagingTableName = context.StagingDatasets.Single(x => x.StagingDatasetID.Equals(datasetID)).DatasetName; model.StagingColumnsForMeasure = context.StagingDatasets.Single(x => x.StagingDatasetID.Equals(datasetID)).StagingColumns.Select(x => new SelectListItem() { Text = x.ColumnName, Value = x.StagingColumnID.ToString() }); model.StagingColumnsForGeography = context.StagingDatasets.Single(x => x.StagingDatasetID.Equals(datasetID)).StagingColumns.Select(x => new SelectListItem() { Text = x.ColumnName, Value = x.StagingColumnID.ToString() }); model.GeographyTypes = context.GeographyTypes.Select(x => new SelectListItem() { Text = x.GeographyType1, Value = x.GeographyTypeID.ToString() }); model.DimensionsForMeasureBreakdown = context.Dimensions.Select(x => new SelectListItem { Text = x.DimensionName, Value = x.DimensionID.ToString() }); List<CreateMeasureDetailModel> modelDetails = new List<CreateMeasureDetailModel>(); foreach (StagingColumn column in context.StagingDatasets.Single(x => x.StagingDatasetID.Equals(datasetID)).StagingColumns) { if (column.ColumnName != "UploadRef") { CreateMeasureDetailModel detail = new CreateMeasureDetailModel(); detail.StagingColumnID = column.StagingColumnID; detail.StagingColumnName = column.ColumnName; detail.AvailableDimensions = context.Dimensions.ToList().Select(x => new SelectListItem() { Text = x.DimensionName, Value = x.DimensionID.ToString() }); modelDetails.Add(detail); } } model.MeasureDetails = modelDetails; return model; }
/// <summary> /// A method to get the model which contains the /// data to add a column to a View /// </summary> /// <returns>The populated model</returns> public CreateViewColumnModel getDefaultColumnModel() { CreateViewColumnModel model = new CreateViewColumnModel(); Geographical_NeedsEntities context = new Geographical_NeedsEntities(); model.Measures = context.Measures.Select(x => new SelectListItem() { Text = x.MeasureName, Value = x.MeasureID.ToString() }); model.MeasureBreakdowns = new List<SelectListItem>(); model.DimensionValues= new List<SelectListItem>(); return model; }
/// <summary> /// A method to get the basic Create View model with drop down /// data populated /// </summary> /// <returns>The populated model</returns> public CreateViewModel getDefaultCreateViewModel() { CreateViewModel model = new CreateViewModel(); Geographical_NeedsEntities context = new Geographical_NeedsEntities(); model.GeographyTypes = context.GeographyTypes.Select(x => new SelectListItem() { Text = x.GeographyType1, Value = x.GeographyTypeID.ToString() }); return model; }