public bool VerifyLists() { List <string> DBUnitAbbr = this.unittypeList.Select(k => k.Abbreviation.Trim()).ToList(); List <string> DBVariableList = this.variableTypeList.Select(vt => vt.Code.Trim().ToUpper()).ToList(); List <string> DBStatisticGroupList = this.statisticGroupTypeList.Select(vt => vt.Code.Trim().ToUpper()).ToList(); List <string> DBregressionList = this.regressionTypeList.Select(vt => vt.Code.Trim().ToUpper()).ToList(); List <string> DBstationTypeList = this.stationTypeList.Select(vt => vt.Code.Trim().ToUpper()).ToList(); List <string> DBAgencyList = this.agencies.Select(a => a.Code.Trim().ToUpper()).ToList(); List <string> ssdbUnitAbbr = null; List <GageStatsVariableType> ssdbDBVariableList = null; List <GageStatsStatisticGroupType> ssdbStatisticGroupList = null; List <GageStatsRegressionType> ssdbRegressionList = null; List <GageStatsStationType> ssdbStationTypeList = null; List <string> ssAgencyList = null; using (var ssdb = new GageStatsDbOps(SSDBConnectionstring, GageStatsDbOps.ConnectionType.e_access)) { ssdbUnitAbbr = ssdb.GetItems <FUString>(GageStatsDbOps.SQLType.e_unittype).Select(f => f.Value.Trim()).ToList(); ssdbDBVariableList = ssdb.GetItems <GageStatsVariableType>(GageStatsDbOps.SQLType.e_variabletype).ToList(); ssdbStatisticGroupList = ssdb.GetItems <GageStatsStatisticGroupType>(GageStatsDbOps.SQLType.e_statisticgrouptype).ToList(); ssdbRegressionList = ssdb.GetItems <GageStatsRegressionType>(GageStatsDbOps.SQLType.e_regressiontype).ToList(); ssdbStationTypeList = ssdb.GetItems <GageStatsStationType>(GageStatsDbOps.SQLType.e_stationtype).ToList(); ssAgencyList = ssdb.GetItems <FUString>(GageStatsDbOps.SQLType.e_agency).Select(e => e.Value).ToList(); }//end using var diffUnits = ssdbUnitAbbr.Except(DBUnitAbbr).ToList(); var diffVariable = ssdbDBVariableList.Where(v => !DBVariableList.Contains(v.Code.Trim().ToUpper())).ToList(); var diffSG = ssdbStatisticGroupList.Where(sg => !DBStatisticGroupList.Contains(sg.Code.Trim().ToUpper())).ToList(); var diffRegList = ssdbRegressionList.Where(r => !DBregressionList.Contains(r.Code.Trim().ToUpper())).ToList(); var diffStationtypeList = ssdbStationTypeList.Where(r => !DBstationTypeList.Contains(r.Code.Trim().ToUpper())).ToList(); var diffAgencies = ssAgencyList.Where(r => !DBAgencyList.Contains(r.Trim().ToUpper())).ToList(); if (diffRegList.Count > 0) { createUpdateList(diffRegList); } if (diffSG.Count > 0) { createUpdateList(diffSG); } if (diffStationtypeList.Count > 0) { createUpdateList(diffStationtypeList); } //if (diffAgencies.Count > 0) createUpdateList(diffAgencies); // need all unit types and stat groups to update variable types if (diffVariable.Count > 0 && diffUnits.Count < 2 && diffSG.Count < 1) { createUpdateList(diffVariable, unittypeList, statisticGroupTypeList); } return(diffUnits.Count < 2 && diffVariable.Count < 1 && diffSG.Count < 1 && diffRegList.Count < 1 && diffStationtypeList.Count < 1); }
private void init() { using (var GageStatsDBOps = new GageStatsDbOps(GagesStatsDBConnectionstring, GageStatsDbOps.ConnectionType.e_postgresql)) { statisticGroupTypeList = GageStatsDBOps.GetItems <GageStatsStatisticGroupType>(GageStatsDbOps.SQLType.e_getstatisticgroups).ToList <StatisticGroupType>(); variableTypeList = GageStatsDBOps.GetItems <GageStatsVariableType>(GageStatsDbOps.SQLType.e_getvariabletypes).ToList <VariableType>(); unittypeList = GageStatsDBOps.GetItems <GagesStatsUnitType>(GageStatsDbOps.SQLType.e_getunittypes).ToList <UnitType>(); regressionTypeList = GageStatsDBOps.GetItems <GageStatsRegressionType>(GageStatsDbOps.SQLType.e_getregressiontypes).ToList <RegressionType>(); stationTypeList = GageStatsDBOps.GetItems <GageStatsStationType>(GageStatsDbOps.SQLType.e_stationtype).ToList <StationType>(); agencies = GageStatsDBOps.GetItems <GageStatsAgency>(GageStatsDbOps.SQLType.e_agency).ToList <Agency>(); regions = GageStatsDBOps.GetItems <GageStatsRegion>(GageStatsDbOps.SQLType.e_region).ToList <Region>(); }//end using }
private void updateCitationIDs(GageStatsDbOps gsDBOps, Int32 stationID, List <FU_Statistics> statistics, List <GageStatsCitations> citationlist) { List <GageStatsStatistic> gsStatistics = gsDBOps.GetItems <GageStatsStatistic>(GageStatsDbOps.SQLType.e_getstatistics, new object[] { stationID }).ToList(); List <GageStatsCharacteristic> gsChars = gsDBOps.GetItems <GageStatsCharacteristic>(GageStatsDbOps.SQLType.e_getcharacteristics, new object[] { stationID }).ToList(); if (gsStatistics.Count < 1 && gsChars.Count < 1) { return; } foreach (var stat in gsStatistics) { var regType = this.regressionTypeList.FirstOrDefault(rt => rt.ID == stat.RegressionTypeID); var statType = this.statisticGroupTypeList.FirstOrDefault(sg => sg.ID == stat.StatisticGroupTypeID); var unitType = this.unittypeList.FirstOrDefault(ut => ut.ID == stat.UnitTypeID); var ssdbStat = statistics.FirstOrDefault(s => String.Equals(s.StatisticDefType, "FS", StringComparison.OrdinalIgnoreCase) && String.Equals(s.StatisticCode, regType.Code) && s.StatisticValue == stat.Value && String.Equals(s.StatisticUnitAbbr, unitType.Abbreviation) && String.Equals(s.StatisticTypeCode, statType.Code)); if (ssdbStat != null) { var cit = citationlist.FirstOrDefault(s => string.Equals(s.Title, ssdbStat.Citation.Title, StringComparison.OrdinalIgnoreCase)); if (cit != null) { stat.CitationID = cit.ID; var updatedStat = gsDBOps.Update(GageStatsDbOps.SQLType.e_updatestatistic, stat.ID, new object[] { cit.ID, stat.ID }); } } } foreach (var stat in gsChars) { var varType = this.variableTypeList.FirstOrDefault(vt => vt.ID == stat.VariableTypeID); var unitType = this.unittypeList.FirstOrDefault(ut => ut.ID == stat.UnitTypeID); var ssdbStat = statistics.FirstOrDefault(s => String.Equals(s.StatisticDefType, "BC", StringComparison.OrdinalIgnoreCase) && String.Equals(s.StatisticCode, varType.Code) && s.StatisticValue == stat.Value && String.Equals(s.StatisticUnitAbbr, unitType.Abbreviation)); if (ssdbStat != null) { var cit = citationlist.FirstOrDefault(s => string.Equals(s.Title, ssdbStat.Citation.Title, StringComparison.OrdinalIgnoreCase)); if (cit != null) { stat.CitationID = cit.ID; var updatedChar = gsDBOps.Update(GageStatsDbOps.SQLType.e_updatecharacteristic, stat.ID, new object[] { cit.ID, stat.ID }); } } } }
public void Load() { try { sm("Starting migration " + DateTime.Today.ToShortDateString()); using (var ssdb = new GageStatsDbOps(SSDBConnectionstring, GageStatsDbOps.ConnectionType.e_access)) { using (var gsDBOps = new GageStatsDbOps(GagesStatsDBConnectionstring, GageStatsDbOps.ConnectionType.e_postgresql)) { // COMMENT OUT if rerunning script gsDBOps.ResetTables(); bool DBcontainsMoreRecords = true; var stationcount = ssdb.GetItems <FUInt>(GageStatsDbOps.SQLType.e_stationCount).FirstOrDefault().Value; sm("Uploading Citations"); //citations // COMMENT OUT next line if rerunning script so citations don't duplicate gsDBOps.AddItems(GageStatsDbOps.SQLType.e_postcitation, ssdb.GetItems <FU_Citation>(GageStatsDbOps.SQLType.e_citation).Select(c => new object[] { c.Title, c.Author, c.CitationURL }), new object[] { }); List <GageStatsCitations> citationlist = gsDBOps.GetItems <GageStatsCitations>(GageStatsDbOps.SQLType.e_citation, new object[] { }).ToList(); Int32 limit = 1000; Int32 offset = 0; Int32 currentcount = 0; sm("Uploading Stations"); var existingStations = gsDBOps.GetItems <GageStatsStations>(GageStatsDbOps.SQLType.e_getstations, new object[] { }); while (DBcontainsMoreRecords) { sm($"LIMIT: {limit} Offset: {offset} "); if ((stationcount - offset) < limit) { limit = (stationcount - offset); DBcontainsMoreRecords = false; }//endif //#warning TODO Impove method with threading and parallelizm // improvements https://docs.microsoft.com/en-us/dotnet/api/system.threading.tasks.parallel.foreach?view=netcore-2.2 //https://docs.microsoft.com/en-us/dotnet/api/system.threading.tasks.parallel.for?view=netcore-2.2 foreach (var item in ssdb.GetItems <FU_Station>(GageStatsDbOps.SQLType.e_station, limit, limit + offset)) { currentcount++; //Processing Station 06934600 15613 / 36683 - comma in stats value //Processing Station 06485500 17172/36683 - text in stats year 73 (123) //Processing Station 06479640 17225 / 36683 - text in stats year 29 (33) //Processing Station 06479515 17230/36683 - text in stats year 29 (30) //Processing Station 06479438 17238 / 36683 - text in stats year 29 (121) //Processing Station 06473500 17305 / 36683 - text in stats year 29 (55) //Processing Station 06445685 17456 / 36683 - TextReader in stats year (54) //Processing Station 01656600 32323/36683 // if (currentcount < 27437) continue; sm($"Processing Station {item.Code} {currentcount}/{stationcount}"); if (string.IsNullOrEmpty(item.Name)) { item.Name = "Undefined in Database"; } //POST Station var agency = this.agencies.FirstOrDefault(e => String.Equals(e.Code, item.Agency_cd, StringComparison.OrdinalIgnoreCase)) ?? this.agencies.FirstOrDefault(e => string.Equals(e.Name, "Undefined")); var stationType = this.stationTypeList.FirstOrDefault(e => String.Equals(e.Code, item.StationTypeCode)) ?? this.stationTypeList.FirstOrDefault(st => string.Equals(st.Name, "Undefined")); var region = this.regions.FirstOrDefault(e => String.Equals(e.Code, item.StateCode)) ?? this.regions.FirstOrDefault(e => string.Equals(e.Name, "Undefined")); // if station already exists (useful when running function to update citations) var currentStation = existingStations.FirstOrDefault(s => s.Code == item.Code); if (currentStation != null) { item.ID = currentStation.ID; } else { item.ID = gsDBOps.AddItem(GageStatsDbOps.SQLType.e_station, new object[] { item.Code, agency.ID, item.Name.Replace("'", " "), item.IsRegulated, stationType.ID, item.Location.AsText(), region.ID }); } if (item.ID < 1) { sm($"99999999 Error pushing station {item.Code} 99999999"); continue; } //get stats and characteristics to push List <FU_Statistics> statistics = ssdb.GetItems <FU_Statistics>(GageStatsDbOps.SQLType.e_statistic_data, item.Code).ToList(); // writing this in in case a citation fails, can use the next two lines to update any null citation IDs and skip adding chars/stats //updateCitationIDs(gsDBOps, item.ID, statistics, citationlist); //continue; //charactersitics gsDBOps.AddItems(GageStatsDbOps.SQLType.e_characteristics, statistics.Where(s => String.Equals(s.StatisticDefType, "BC", StringComparison.OrdinalIgnoreCase)) .Select(c => new object[] { this.variableTypeList.FirstOrDefault(v => String.Equals(v.Code, c.StatisticCode)).ID, this.unittypeList.FirstOrDefault(u => string.Equals(u.Abbreviation, c.StatisticUnitAbbr)).ID, citationlist.FirstOrDefault(s => string.Equals(s.Title, c.Citation.Title, StringComparison.OrdinalIgnoreCase) && string.Equals(s.Author, c.Citation.Author, StringComparison.OrdinalIgnoreCase) && (s.CitationURL == "null" || string.Equals(s.CitationURL, c.Citation.CitationURL, StringComparison.OrdinalIgnoreCase)))?.ID, c.StatisticValue, c.StatisticRemarks }).ToList(), new object[] { item.ID }); //Statistics gsDBOps.AddItems(statistics.Where(s => String.Equals(s.StatisticDefType, "FS", StringComparison.OrdinalIgnoreCase)) .Select(c => new Statistic { StationID = item.ID, CitationID = citationlist.FirstOrDefault(s => string.Equals(s.Title, c.Citation.Title, StringComparison.OrdinalIgnoreCase) && string.Equals(s.Author, c.Citation.Author, StringComparison.OrdinalIgnoreCase) && string.Equals(s.CitationURL, c.Citation.CitationURL, StringComparison.OrdinalIgnoreCase))?.ID, Comments = createComment(c.StatisticStartDate, c.StatisticEndDate, c.StatisticRemarks), RegressionTypeID = this.regressionTypeList.FirstOrDefault(v => String.Equals(v.Code, c.StatisticCode)).ID, StatisticGroupTypeID = this.statisticGroupTypeList.FirstOrDefault(v => String.Equals(v.Code, c.StatisticTypeCode)).ID, UnitTypeID = this.unittypeList.FirstOrDefault(u => string.Equals(u.Abbreviation, c.StatisticUnitAbbr)).ID, Value = c.StatisticValue, YearsofRecord = c.StatisticYears, IsPreferred = c.StatisticIsPreferred, PredictionInterval = (c.StatisticLowerCI.HasValue || c.StatisticUpperCI.HasValue || c.StatisticVariance.HasValue) ? new PredictionInterval() { LowerConfidenceInterval = c.StatisticLowerCI, UpperConfidenceInterval = c.StatisticUpperCI, Variance = c.StatisticVariance } : null, StatisticErrors = c.StatisticError.HasValue ? new List <StatisticError>() { new StatisticError() { ErrorTypeID = 1, Value = c.StatisticError.Value } } : null }).ToList()); }//next station //increment offset = offset + 1000; } //DO } //end using } //end using } catch (Exception ex) { throw; } }