/// <summary>
/// The controller method for the second phase in the process
/// to create a Measure. This completes the population of the
/// appropriate model.
///
/// This is accessed from /Dataset/AddMeasureValues although this
/// will not be reached directly but as a redirect from the first
/// phase of the process.
/// </summary>
/// <param name="stagingTableName">The name of the Staging Table that the data is sourced from</param>
/// <param name="measureName">The name of the Measure to be created</param>
/// <param name="measureColumnStagingID">The Column in the Staging table that contains the measure values</param>
/// <param name="geographyColumnID">The Column in the Staging table that contains the Geography</param>
/// <returns></returns>
public ActionResult AddMeasureValues(String stagingTableName, String measureName, int?measureColumnStagingID, int geographyColumnID, int geographyTypeID)
{
DatasetDataAccess dataAccess = new DatasetDataAccess();
IEnumerable <Tuple <int, int> > mappings = (IEnumerable <Tuple <int, int> >)TempData["DimensionMappings"];
MeasureValueModel model = dataAccess.populateMeasureValueModel(mappings, stagingTableName, measureName, measureColumnStagingID, geographyColumnID, geographyTypeID);
List <Breadcrumb> trail = new List <Breadcrumb>();
trail.Add(new Breadcrumb()
{
LinkText = "Home", Action = "Index", Controller = "Home", isCurrent = false
});
trail.Add(new Breadcrumb()
{
LinkText = "Staging Index", Action = "Index", Controller = "Staging", isCurrent = false
});
trail.Add(new Breadcrumb()
{
LinkText = "Create Measure", Action = "", Controller = "", isCurrent = true
});
model.Breadcrumbs = trail;
return(View(model));
}
public ActionResult AddMeasureValues(MeasureValueModel model)
{
DatasetDataAccess dataAccess = new DatasetDataAccess();
dataAccess.createMeasureValues(model);
TempData["SuccessMessage"] = String.Format("The Measure - {0}, was successfully created", model.MeasureName);
return(RedirectToAction("Index", "Staging"));
}
/// <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);
}
private void OpenOnClick()
{
CurrentContext.DataList = new List <DataModel>();
ViewModel.Measurements = new ObservableCollection <DataModel>();
var openFileDialog = new OpenFileDialog
{
Filter = "СSV (*.csv)|*.csv"
};
if (openFileDialog.ShowDialog() == true)
{
string fileName = openFileDialog.FileName;
string searchString = "__";
int indexOfStart = fileName.IndexOf(searchString) + 2;
var nameMode = fileName.Substring(indexOfStart);
nameMode = nameMode.Substring(0, nameMode.Length - 4);
var dataList = File.ReadAllLines(openFileDialog.FileName)
.Skip(1)
.Select(x => x.Split(','))
.Select(x => new DataModel
{
Id = x[0] != "" ? int.Parse(x[0]) : 0,
X = x[1] != "" ? double.Parse(x[1]) : 0,
Y = x[2] != "" ? double.Parse(x[2]) : 0,
HorizontalDistance = x[3] != "" ? double.Parse(x[3]) : 0,
VerticalDistance = x[4] != "" ? double.Parse(x[4]) : 0,
SlopeDistance = x[5] != "" ? double.Parse(x[5]) : 0,
Azimuth = x[6] != "" ? double.Parse(x[6]) : 0,
Bias = x[7] != "" ? double.Parse(x[7]) : 0,
DiameterOne = x[8] != "" ? double.Parse(x[8]) : 0,
DiameterTwo = x[9] != "" ? double.Parse(x[9]) : 0,
Species = x[10]
}).ToList();
if (CurrentContext.MeasureValues.FirstOrDefault(x => x.Name == nameMode) == null || dataList.Count == 0)
{
MessageBox.Show("Ошибка. Не удалось открыть файл");
return;
}
DeleteOldColumns();
_selectMeasure = CurrentContext.MeasureValues.FirstOrDefault(x => x.Name == nameMode);
ViewModel.SelectMeasure = _selectMeasure;
//AddNewColumns();
CurrentContext.DataList = dataList;
ViewModel.Measurements = new ObservableCollection <DataModel>(dataList);
CurrentContext.GlobalId = CurrentContext.DataList.Max(x => x.Id);
}
}
private void Selector_OnSelectionChanged(object sender, SelectionChangedEventArgs e)
{
if (DataGrid != null)
{
if (DataGrid.Items.Count != 0)
{
SaveOnClick(true);
}
DeleteOldColumns();
ViewModel.SelectMeasure = (sender as ComboBox)?.SelectedItem as MeasureValueModel;
_selectMeasure = ViewModel.SelectMeasure;
AddNewColumns();
}
}
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();
}
