public string DownloadQueryData(FetchSiteAnalyteQueryViewModel queryViewModel)
{
var selectedAnalytes = _variableRepository.GetAllChemistryVariables()
.Where(x => queryViewModel.SelectedVariables.Contains(x.VariableID))
.AsEnumerable();
var matchedSite = _siteRepository.GetAll().Where(x => x.SamplingFeatureID == queryViewModel.SelectedSiteID).FirstOrDefault();
var siteName = (matchedSite == null || matchedSite.SamplingFeature == null || string.IsNullOrEmpty(matchedSite.SamplingFeature.SamplingFeatureName)) ?
"Unknown" :
matchedSite.SamplingFeature.SamplingFeatureName;
var fileName = string.Format("{0}_Data_From_{1}_To_{2}.xlsx", siteName, queryViewModel.StartDate.ToString("MMM-dd-yyyy"), queryViewModel.EndDate.ToString("MMM-dd-yyyy"));
var relativePathPart = Path.Combine("App_Data", "Query_Data", fileName);
var fileFullPath = HttpContext.Current.Server.MapPath("~/" + relativePathPart);
var dataViewModel = FetchStationData(queryViewModel);
var spreadSheet = SpreadsheetHelper.GenerateQueryDataResultSpreadshet("Results", dataViewModel, selectedAnalytes);
using (var fileStream = System.IO.File.Create(fileFullPath))
{
spreadSheet.Write(fileStream);
}
return(fileName);
}
public IEnumerable <StationAnalyteQueryViewModel> FetchStationData(FetchSiteAnalyteQueryViewModel queryViewModel)
{
var items = new List <StationAnalyteQueryViewModel>();
var actions = _wqDataRepository.GetAllWQAnalyteDataActions();
var versionHelper = new DataVersioningHelper(_wqDefaultValueProvider);
foreach (var action in actions)
{
foreach (var analyte in queryViewModel.SelectedVariables)
{
var latestAction = versionHelper.GetLatestVersionActionData(action);
var analyteResultViewModel = from featureAction in latestAction.FeatureActions
from analyteResult in featureAction.Results
where featureAction.SamplingFeatureID == queryViewModel.SelectedSiteID
where analyteResult.VariableID == analyte
select analyteResult;
var result = analyteResultViewModel.FirstOrDefault();
//var result = latestAction.FeatureActions.Where(x => x.SamplingFeatureID == queryViewModel.SelectedSiteID).FirstOrDefault().Results.Where(x => x.VariableID == analyte).FirstOrDefault();
if (result != null && result.ResultExtensionPropertyValues.Where(x => x.ExtensionProperty.PropertyName == "Result Type").FirstOrDefault().PropertyValue == "REG")
{
if (result.MeasurementResult != null && result.MeasurementResult.MeasurementResultValues.First().ValueDateTime <= queryViewModel.EndDate && result.MeasurementResult.MeasurementResultValues.First().ValueDateTime >= queryViewModel.StartDate)
{
var measurementValue = result.MeasurementResult.MeasurementResultValues.FirstOrDefault().DataValue;
var resultDateTime = latestAction.BeginDateTime;
var unitsName = result.Unit.UnitsName;
string prefix = null;
if (result.ResultExtensionPropertyValues.Where(x => x.ExtensionProperty.PropertyName == "Prefix").FirstOrDefault().PropertyValue != null)
{
prefix = result.ResultExtensionPropertyValues.Where(x => x.ExtensionProperty.PropertyName == "Prefix").FirstOrDefault().PropertyValue;
}
var variable = result.Variable.VariableDefinition;
double?detectionLimit = null;
if (result.ResultsDataQualities.Count > 0)
{
detectionLimit = result.ResultsDataQualities.Where(x => x.DataQuality.DataQualityTypeCV == "methodDetectionLimit").FirstOrDefault().DataQuality.DataQualityValue;
}
items.Add(new StationAnalyteQueryViewModel {
DataValue = measurementValue, ResultDateTime = resultDateTime.ToString("MMM-dd-yyyy, HH:mm tt"), UnitsName = unitsName, Variable = variable, MethodDetectionLimit = detectionLimit, Prefix = prefix
});
}
}
}
}
return(items);
}
