/// <summary> /// Creates a StringWriter containing all specified data values to download in CSV format /// </summary> /// <param name="db">The original HISDataContext, containing a list of ODM databases.</param> /// <param name="dataQueryForm">The data query form.</param> /// <param name="form">The form.</param> /// <returns></returns> public static void Download(HISDataContext db, DataQueryFormModel dataQueryForm, FormCollection form) { var dataQueryModel = NewDataQueryModel(db, dataQueryForm); foreach (TimeSeriesModel item in dataQueryModel.SeriesCatalog) { if (IsChecked(form, (item.TimeSeries.SeriesID + "@" + item.OdmDb).ToString())) { DownloadTimeSeries(db, dataQueryForm, form, item); } } }
/// <summary> /// Downloads the time series. /// </summary> /// <param name="db">The db.</param> /// <param name="Downloader">The downloader.</param> /// <param name="item">The item.</param> public static void DownloadTimeSeries(HISDataContext db, DataQueryFormModel dataQueryForm, FormCollection form, TimeSeriesModel item) { var odmDb = db.ODMDatabases.SingleOrDefault(d => d.Title == item.OdmDb); using (ODMDbDataContext db2 = GetODMDbDataContext(odmDb)) { var dataList = (from data in db2.DataValues where (data.SiteID == item.TimeSeries.SiteID) & (data.SourceID == item.TimeSeries.SourceID) & (data.VariableID == item.TimeSeries.VariableID) & (dataQueryForm.StartTime != null ? (data.LocalDateTime.CompareTo(dataQueryForm.StartTime) >= 0) : true) & (dataQueryForm.EndTime != null ? (data.LocalDateTime.CompareTo(dataQueryForm.EndTime) <= 0) : true) orderby data.LocalDateTime select data).ToList(); ExportMyDbData(dataList, ",", dataQueryForm, form, item); } }
/// <summary> /// Converts a list of unparsed DbID strings into DbIDs. /// </summary> /// <param name="form">The form.</param> /// <returns></returns> public static DataQueryFormModel ParsedDataQueryForm(UnparsedDataQueryFormModel form, HISDataContext db) { DataQueryFormModel dataQueryForm = new DataQueryFormModel() { RegionIDs = new List <DbID>(), VariableIDs = new List <DbID>(), CustomVariable = form.CustomVariable }; if (form.StartTime != null) { dataQueryForm.StartTime = Convert.ToDateTime(form.StartTime); } else { dataQueryForm.StartTime = null; } if (form.EndTime != null) { dataQueryForm.EndTime = Convert.ToDateTime(form.EndTime); } else { dataQueryForm.EndTime = null; } foreach (var item in form.RegionIDs) { dataQueryForm.RegionIDs.Add(ParsedDbID(item, db)); } if (form.VariableIDs != null) { foreach (var item in form.VariableIDs) { if (item.Equals("None") != true) { dataQueryForm.VariableIDs.Add(ParsedDbID(item, db)); } } } return(dataQueryForm); }
/// <summary> /// Get a new DataQueryModel from submitted information. /// </summary> /// <param name="db">The db.</param> /// <param name="dataQueryForm">The data query form.</param> /// <returns></returns> public static DataQueryModel NewDataQueryModel(HISDataContext db, DataQueryFormModel dataQueryForm) { var dataQueryModel = new DataQueryModel() { Regions = new List <RegionModel>(), Sites = new List <SiteModel>(), Sources = new List <SourceModel>(), Variables = new List <VariableModel>(), SeriesCatalog = new List <TimeSeriesModel>(), DataQueryForm = dataQueryForm }; var items = db.ODMDatabases; //need to select databases that are in the regions that have been selected //get list of selected regions //loop through regions and get list of all databases in the regions dataQueryModel = DataQueryRepository.AddRegions(dataQueryModel, db); foreach (var odmDb in items) { ODMDbDataContext db2 = DataQueryRepository.GetODMDbDataContext(odmDb); dataQueryModel = DataQueryRepository.AddSitesSourcesVars(dataQueryModel, db2, odmDb); if (dataQueryForm != null) { List <RegionDatabase> matches = (from rd in db.RegionDatabases where (rd.DatabaseID == odmDb.DatabaseID) select rd).ToList <RegionDatabase>(); var isInRegion = false; if (matches != null) { foreach (var match in matches) { if (dataQueryForm.RegionIDs.Any(s => s.ID == match.RegionID)) { isInRegion = true; } } } if (isInRegion) { foreach (var item in db2.TimeSeries) { if (dataQueryForm.VariableIDs.Count > 0) { if ( dataQueryForm.VariableIDs.Any(s => (s.ID == item.VariableID) & (s.OdmDb == odmDb)) || ( (dataQueryForm.CustomVariable != null) && db2.Variables.SingleOrDefault(v => v.VariableID == item.VariableID).VariableName.ToUpper().Contains(dataQueryForm.CustomVariable.ToUpper()) ) ) { dataQueryModel.SeriesCatalog.Add(new TimeSeriesModel() { TimeSeries = item, OdmDb = odmDb.Title }); } } else { if ((dataQueryForm.CustomVariable != null) && db2.Variables.SingleOrDefault(v => v.VariableID == item.VariableID).VariableName.ToUpper().Contains(dataQueryForm.CustomVariable.ToUpper())) { dataQueryModel.SeriesCatalog.Add(new TimeSeriesModel() { TimeSeries = item, OdmDb = odmDb.Title }); } } } } } } var allRegionIDs = new List <DbID>(); var allVarIDs = new List <DbID>(); foreach (var item in dataQueryModel.Regions) { allRegionIDs.Add(new DbID() { ID = item.Region.RegionID, OdmDb = item.OdmDb }); } foreach (var item in dataQueryModel.Variables) { allVarIDs.Add(new DbID() { ID = item.Variable.VariableID, OdmDb = item.OdmDb }); } //how can i tell what regions are selected foreach (var item in dataQueryModel.Variables) { List <RegionDatabase> matches2 = (from rd in db.RegionDatabases where (rd.DatabaseID == item.OdmDb.DatabaseID) select rd).ToList <RegionDatabase>(); if (matches2.Count > 0) { if (dataQueryForm != null) { if (dataQueryForm.RegionIDs.Any(s => s.ID == matches2.First().RegionID)) { allVarIDs.Add(new DbID() { ID = item.Variable.VariableID, OdmDb = item.OdmDb }); } } } } if (dataQueryModel.DataQueryForm == null) { dataQueryModel.DataQueryForm = new DataQueryFormModel() { RegionIDs = allRegionIDs, VariableIDs = allVarIDs }; } return(dataQueryModel); }
private static void ExportMyDbData(List <DataValue> queryTable, string delimiter, DataQueryFormModel dataQueryForm, FormCollection form, TimeSeriesModel item) { //'Takes the MyDB QueryTable data and stores it in the selected .csv file //'Inputs: QueryTable-> A table containing all the data to export //' FullFileName->The filename and path to export to (including extension) //'Outputs: Returns true if successful //// Saves the QueryTable to a comma/tab delimited table string newLine = string.Empty; try { //'If using Comma Delimited Format //'write the headers //newLine += db_fld_SCSeriesID + delimiter); newLine += db_fld_ValID + delimiter; newLine += db_fld_ValValue + delimiter; newLine += db_fld_ValAccuracyStdDev + delimiter; newLine += db_fld_ValDateTime + delimiter; if (IsChecked(form, "selectTime")) { //OPTIONAL newLine += db_fld_ValUTCDateTime + delimiter; newLine += db_fld_ValUTCOffset + delimiter; } newLine += db_fld_SiteCode + delimiter; if (IsChecked(form, "selectSite")) { //OPTIONAL newLine += db_fld_SiteName + delimiter; newLine += db_fld_SiteLat + delimiter; newLine += db_fld_SiteLong + delimiter; newLine += db_fld_SRSRSName + delimiter; } newLine += db_fld_VarCode + delimiter; if (IsChecked(form, "selectVariable")) { //OPTIONAL newLine += db_fld_VarName + delimiter; //newLine += db_fld_VarSpeciation + delimiter; newLine += db_expr_VarUnits_Name + delimiter; newLine += db_expr_VarUnits_Abbr + delimiter; //newLine += db_fld_VarSampleMed + delimiter; } newLine += db_fld_MethID + delimiter; if (IsChecked(form, "selectMethod")) { //OPTIONAL newLine += db_fld_MethDesc + delimiter; } newLine += db_fld_ValOffsetValue + delimiter; newLine += db_fld_ValOffsetTypeID + delimiter; //if (IsChecked(form, "selectOffset")) //{ //OPTIONAL // newLine += db_fld_OTDesc + delimiter; // newLine += db_expr_OffsetUnits_Name + delimiter; //} newLine += db_fld_ValCensorCode + delimiter; newLine += db_fld_ValQualifierID + delimiter; if (IsChecked(form, "selectQualifier")) { //OPTIONAL newLine += db_fld_QlfyCode + delimiter; newLine += db_fld_QlfyDesc + delimiter; } if (IsChecked(form, "selectSource")) { //OPTIONAL newLine += db_fld_SrcOrg + delimiter; newLine += db_fld_SrcDesc + delimiter; //newLine += db_fld_SrcCitation + delimiter; } if (IsChecked(form, "selectQualityControlLevels")) { newLine += db_fld_QCLQCLevel + delimiter; newLine += db_fld_QCLDefinition + delimiter; newLine += db_fld_QCLExplanation + delimiter; } if (IsChecked(form, "selectSample")) { //OPTIONAL newLine += db_fld_SampleType; } WriteResponseLine(newLine + Environment.NewLine); for (int i = 0; i < queryTable.Count - 1; i++) { //Write each line of data, placing commas in between each value in the same row //newLine += queryTable[i].serItem(db_fld_SCSeriesID) + delimiter; newLine = queryTable[i].ValueID.ToString() + delimiter; newLine += queryTable[i].DataValue1 + delimiter; newLine += (queryTable[i].ValueAccuracy != null ? queryTable[i].ValueAccuracy.ToString() : "") + delimiter; newLine += queryTable[i].LocalDateTime.ToString() + delimiter; if (IsChecked(form, "selectTime")) { //OPTIONAL newLine += queryTable[i].DateTimeUTC.ToString() + delimiter; newLine += queryTable[i].UTCOffset + delimiter; } newLine += "\"" + queryTable[i].Site.SiteCode.ToString() + "\"" + delimiter; if (IsChecked(form, "selectSite")) { if (queryTable[i].Site != null) { //OPTIONAL newLine += "\"" + queryTable[i].Site.SiteName + "\"" + delimiter; newLine += queryTable[i].Site.Latitude + delimiter; newLine += queryTable[i].Site.Longitude + delimiter; newLine += queryTable[i].Site.VerticalDatum + delimiter; } else { newLine += delimiter; newLine += delimiter; newLine += delimiter; newLine += delimiter; } } newLine += queryTable[i].Variable.VariableCode + delimiter; if (IsChecked(form, "selectVariable")) { if (queryTable[i].Variable != null) { //OPTIONAL newLine += "\"" + queryTable[i].Variable.VariableName + "\"" + delimiter; //newLine += queryTable[i].Variable.Item(db_fld_VarSpeciation) + delimiter; newLine += queryTable[i].Variable.Unit.UnitsName + delimiter; newLine += queryTable[i].Variable.Unit.UnitsAbbreviation + delimiter; //newLine += queryTable[i].Variable.Item(db_fld_VarSampleMed) + delimiter; } else { newLine += delimiter; newLine += delimiter; newLine += delimiter; } } newLine += queryTable[i].MethodID + delimiter; if (IsChecked(form, "selectMethod")) { if (queryTable[i].Method != null) { //OPTIONAL newLine += "\"" + queryTable[i].Method.MethodDescription + "\"" + delimiter; } else { newLine += delimiter; } } newLine += queryTable[i].OffsetValue + delimiter; newLine += queryTable[i].OffsetTypeID + delimiter; //if (IsChecked(form, "selectOffset")) //{ //OPTIONAL // newLine += queryTable[i].Item(db_fld_OTDesc) + delimiter; // newLine += queryTable[i].Item(db_expr_OffsetUnits_Name) + delimiter; //} newLine += queryTable[i].CensorCode + delimiter; newLine += queryTable[i].QualifierID + delimiter; if (IsChecked(form, "selectQualifier")) { if (queryTable[i].Qualifier != null) { //OPTIONAL newLine += "\"" + queryTable[i].Qualifier.QualifierCode + "\"" + delimiter; newLine += "\"" + queryTable[i].Qualifier.QualifierDescription + "\"" + delimiter; } else { newLine += delimiter; newLine += delimiter; } } if (IsChecked(form, "selectSource")) { if (queryTable[i].Source != null) { //OPTIONAL; newLine += "\"" + queryTable[i].Source.Organization + "\"" + delimiter; newLine += "\"" + queryTable[i].Source.SourceDescription + "\"" + delimiter; //newLine += queryTable[i].Source.Item(db_fld_SrcCitation) + delimiter; } else { newLine += delimiter; newLine += delimiter; } } if (IsChecked(form, "selectQualityControlLevels")) { if (queryTable[i].QualityControlLevel != null) { //OPTIONAL newLine += queryTable[i].QualityControlLevelID + delimiter; newLine += "\"" + queryTable[i].QualityControlLevel.Definition + "\"" + delimiter; newLine += "\"" + queryTable[i].QualityControlLevel.Explanation + "\"" + delimiter; } else { newLine += delimiter; newLine += delimiter; newLine += delimiter; } } newLine += queryTable[i].SampleID + "\n"; if (IsChecked(form, "selectSample")) { if (queryTable[i].Sample != null) { //OPTIONAL newLine += queryTable[i].Sample.SampleType; } } WriteResponseLine(newLine); if (i % 100 == 0) { HttpContext.Current.Response.Flush(); } } } catch (Exception ex) { //return false; } }