public static bool RasterUpdatedSinceMinimumLastUpdatedDate(RioConfiguration rioConfiguration, RioDbContext rioDbContext, int year, OpenETSyncHistoryDto newSyncHistory) { var openETRequestURL = $"{rioConfiguration.OpenETCheckRasterUpdatedDateBaseURL}?geom=-120.30921936035156,36.99542364399086,-120.30887603759766,36.98143783973302,-120.2918815612793,36.982260605282676,-120.29170989990234,36.99556074698967,-120.30921936035156,36.99542364399086&api_key={rioConfiguration.OpenETAPIKey}&start_date={new DateTime(year, 1, 1):yyyy-MM-dd}&end_date={new DateTime(year, 12, 31):yyyy-MM-dd}&model=ensemble&vars=et&prop=date_ingested"; var httpClient = new HttpClient { Timeout = new TimeSpan(60 * TimeSpan.TicksPerSecond) }; var response = httpClient.GetAsync(openETRequestURL).Result; //If we don't get a good response, let's just say that there needs to be an update to cover our bases if (!response.IsSuccessStatusCode) { return(true); } var responseObject = JsonConvert.DeserializeObject <RasterMetadataDateIngested>(response.Content.ReadAsStringAsync().Result); if (string.IsNullOrEmpty(responseObject.DateIngested) || !DateTime.TryParse(responseObject.DateIngested, out DateTime responseDate)) { OpenETSyncHistory.UpdateSyncResultByID(rioDbContext, newSyncHistory.OpenETSyncHistoryID, OpenETSyncResultTypeEnum.DataNotAvailable); return(false); } var openETSyncHistoriesThatHaventFailed = rioDbContext.OpenETSyncHistory .Include(x => x.WaterYear) .Where(x => x.WaterYear.Year == year && (x.OpenETSyncResultTypeID != (int)OpenETSyncResultTypeEnum.Failed) && x.OpenETSyncHistoryID != newSyncHistory.OpenETSyncHistoryID); if (!openETSyncHistoriesThatHaventFailed.Any()) { return(true); } var mostRecentSyncHistory = openETSyncHistoriesThatHaventFailed?.OrderByDescending(x => x.UpdateDate).First(); if (responseDate > mostRecentSyncHistory.UpdateDate) { return(true); } OpenETSyncHistory.UpdateSyncResultByID(rioDbContext, newSyncHistory.OpenETSyncHistoryID, OpenETSyncResultTypeEnum.NoNewData); return(false); }
//private static readonly ILog _logger = LogManager.GetLogger(typeof(SitkaSmtpClient)); public SitkaSmtpClientService(RioConfiguration rioConfiguration) { _rioConfiguration = rioConfiguration; }
public static HttpResponseMessage TriggerOpenETGoogleBucketRefresh(RioConfiguration rioConfiguration, RioDbContext rioDbContext, int waterYearID) { if (!rioConfiguration.AllowOpenETSync) { return(new HttpResponseMessage() { StatusCode = HttpStatusCode.BadRequest, Content = new StringContent("Syncing with OpenET is not enabled at this time") }); } var waterYearDto = WaterYear.GetByWaterYearID(rioDbContext, waterYearID); if (waterYearDto == null) { return(new HttpResponseMessage() { StatusCode = HttpStatusCode.BadRequest, Content = new StringContent("Requested Water Year not found") }); } var year = waterYearDto.Year; if (rioDbContext.OpenETSyncHistory .Include(x => x.WaterYear) .Any(x => x.WaterYear.Year == year && x.OpenETSyncResultTypeID == (int)OpenETSyncResultTypeEnum.InProgress)) { return(new HttpResponseMessage() { StatusCode = HttpStatusCode.BadRequest, Content = new StringContent($"Sync already in progress for {year}") }); } var newSyncHistory = OpenETSyncHistory.New(rioDbContext, year); if (!RasterUpdatedSinceMinimumLastUpdatedDate(rioConfiguration, rioDbContext, year, newSyncHistory)) { return(new HttpResponseMessage() { StatusCode = HttpStatusCode.UnprocessableEntity, Content = new StringContent($"The request was successful, however the sync for {year} will not be completed for the following reason: {OpenETSyncHistory.GetByOpenETSyncHistoryID(rioDbContext, newSyncHistory.OpenETSyncHistoryID).OpenETSyncResultType.OpenETSyncResultTypeDisplayName}") }); } var openETRequestURL = $"{rioConfiguration.OpenETTriggerTimeSeriesBaseURL}?shapefile_fn={rioConfiguration.OpenETShapefilePath}&start_date={new DateTime(year, 1, 1):yyyy-MM-dd}&end_date={new DateTime(year, 12, 31):yyyy-MM-dd}&model=ensemble&vars=et&aggregation_type=mean&api_key={rioConfiguration.OpenETAPIKey}&to_cloud=openet_raster_api_storage&suffix={newSyncHistory.GetFileSuffixForOpenETSyncHistoryDto(rioConfiguration.LeadOrganizationShortName)}&out_columns=ParcelNumb"; var httpClient = new HttpClient { Timeout = new TimeSpan(60 * TimeSpan.TicksPerSecond) }; var responseResult = httpClient.GetAsync(openETRequestURL).Result; if (!responseResult.IsSuccessStatusCode) { OpenETSyncHistory.UpdateSyncResultByID(rioDbContext, newSyncHistory.OpenETSyncHistoryID, OpenETSyncResultTypeEnum.Failed); } return(responseResult); }
public static void UpdateParcelMonthlyEvapotranspirationWithETData(RioDbContext rioDbContext, RioConfiguration rioConfiguration, int syncHistoryID) { var syncHistoryObject = OpenETSyncHistory.GetByOpenETSyncHistoryID(rioDbContext, syncHistoryID); if (syncHistoryObject == null || syncHistoryObject.OpenETSyncResultType.OpenETSyncResultTypeID != (int)OpenETSyncResultTypeEnum.InProgress) { //Bad request, we completed already and somehow were called again, or someone else decided we were done return; } var openETRequestURL = $"{rioConfiguration.OpenETGoogleBucketBaseURL}/testing_multi_mean_{rioConfiguration.OpenETAPIKey}_{syncHistoryObject.GetFileSuffixForOpenETSyncHistoryDto(rioConfiguration.LeadOrganizationShortName)}.csv"; var httpClient = new HttpClient { Timeout = new TimeSpan(60 * TimeSpan.TicksPerSecond) }; var response = httpClient.GetAsync(openETRequestURL).Result; if (!response.IsSuccessStatusCode) { var timeBetweenSyncCreationAndNow = DateTime.UtcNow.Subtract(syncHistoryObject.CreateDate).Hours; OpenETSyncHistory.UpdateSyncResultByID(rioDbContext, syncHistoryObject.OpenETSyncHistoryID, timeBetweenSyncCreationAndNow > 2 ? OpenETSyncResultTypeEnum.Failed : OpenETSyncResultTypeEnum.InProgress); return; } List <OpenETGoogleBucketResponseEvapotranspirationData> distinctRecords; using (var reader = new StreamReader(response.Content.ReadAsStreamAsync().Result)) { var csvr = new CsvReader(reader, CultureInfo.CurrentCulture); var finalizedWaterYears = rioDbContext.WaterYear .Where(x => x.FinalizeDate.HasValue) .Select(x => x.Year) .ToList(); distinctRecords = csvr.GetRecords <OpenETCSVFormat>().Where(x => !finalizedWaterYears.Contains(x.Date.Year)) .Distinct(new DistinctOpenETCSVFormatComparer()) .Select(x => x.AsOpenETGoogleBucketResponseEvapotranspirationData()) .ToList();; } if (!distinctRecords.Any()) { OpenETSyncHistory.UpdateSyncResultByID(rioDbContext, syncHistoryObject.OpenETSyncHistoryID, OpenETSyncResultTypeEnum.NoNewData); return; } try { rioDbContext.Database.ExecuteSqlRaw( "TRUNCATE TABLE dbo.OpenETGoogleBucketResponseEvapotranspirationData"); DataTable table = new DataTable(); table.Columns.Add("OpenETGoogleBucketResponseEvapotranspirationDataID", typeof(int)); table.Columns.Add("ParcelNumber", typeof(string)); table.Columns.Add("WaterMonth", typeof(int)); table.Columns.Add("WaterYear", typeof(int)); table.Columns.Add("EvapotranspirationRateInMM", typeof(decimal)); int i = 0; distinctRecords.ForEach(x => { table.Rows.Add(++i, x.ParcelNumber, x.WaterMonth, x.WaterYear, x.EvapotranspirationRateInMM); }); using (SqlConnection con = new SqlConnection(rioConfiguration.DB_CONNECTION_STRING)) { using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con)) { sqlBulkCopy.DestinationTableName = "dbo.OpenETGoogleBucketResponseEvapotranspirationData"; con.Open(); sqlBulkCopy.WriteToServer(table); con.Close(); } } rioDbContext.Database.ExecuteSqlRaw("EXECUTE dbo.pUpdateParcelMonthlyEvapotranspirationWithETData"); OpenETSyncHistory.UpdateSyncResultByID(rioDbContext, syncHistoryObject.OpenETSyncHistoryID, OpenETSyncResultTypeEnum.Succeeded); } catch (Exception ex) { OpenETSyncHistory.UpdateSyncResultByID(rioDbContext, syncHistoryObject.OpenETSyncHistoryID, OpenETSyncResultTypeEnum.Failed); } }