FarmMapsApiClient/FarmmapsCleanUp/CleanUpApplication.cs

144 lines
7.0 KiB
C#
Raw Permalink Normal View History

using System;
using System.Collections.Generic;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Threading.Tasks;
using FarmmapsApi;
using FarmmapsApi.Models;
using FarmmapsApi.Services;
using FarmmapsBulkSatDownload.Models;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using Npgsql;
using Newtonsoft.Json.Linq;
using static FarmmapsApiSamples.Constants;
namespace FarmmapsCleanup
{
public class CleanupApplication : IApplication
{
private readonly ILogger<CleanupApplication> _logger;
private readonly FarmmapsApiService _farmmapsApiService;
private readonly CleanupService _cleanupService;
private readonly GeneralService _generalService;
public CleanupApplication(ILogger<CleanupApplication> logger, FarmmapsApiService farmmapsApiService,
GeneralService generalService, CleanupService cleanupService)
{
_logger = logger;
_farmmapsApiService = farmmapsApiService;
_generalService = generalService;
_cleanupService = cleanupService;
}
public async Task RunAsync()
{
// !! this call is needed the first time an api is called with a fresh clientid and secret !!
await _farmmapsApiService.GetCurrentUserCodeAsync();
var roots = await _farmmapsApiService.GetCurrentUserRootsAsync();
//Establish a database connection. Expecting you are using same database server as in project FarmmapsBulkSatDownload
DB dbparcels = JsonConvert.DeserializeObject<DB>(File.ReadAllText("DBsettings.secrets.json"));
string schemaname = "bigdata";
string parceltablename = "parcel_bollenrevolutie_tulips2020"; //"parcelsijbrandij" "parcel"; "parcel_flowerbulbs"; "parcel_disac"; ""parcel_bollenrevolutie_tulips2020""
//Query to get lists of items to delete from FarmmapsDatabase and from parceltablename
string cropfielditemcode;
string satellitetaskcode;
List<string> cropfieldItemCodes = new List<string>();
List<string> satellitetaskCodes = new List<string>();
string connectionString = dbparcels.GetConnectionString();
string readSql = string.Format(
@"
SELECT pt.cropfielditemcode, pt.satellitetaskcode
FROM {0}.{1} pt
WHERE
pt.arbid IN(1,2)
ORDER BY pt.arbid
;", schemaname, parceltablename);
string updateCropfieldItemCodesSql = string.Format(
@"
UPDATE {0}.{1}
SET cropfielditemcode=NULL
WHERE arbid IN(1,2)
;", schemaname, parceltablename); //Same WHERE AS above
string updateSatellitetaskCodesSql = string.Format(
@"
UPDATE {0}.{1}
SET satellitetaskcode=NULL
WHERE arbid IN(1,2)
;", schemaname, parceltablename); //Same WHERE AS above
using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
connection.Open();
// Read data (run query) = build a list of fields for which to download images
NpgsqlCommand command = connection.CreateCommand();
command.CommandText = readSql;
NpgsqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
cropfielditemcode = dr.GetString(0);
satellitetaskcode = dr.GetString(1);
if(!string.IsNullOrEmpty(cropfielditemcode))
cropfieldItemCodes.Add(cropfielditemcode);
if (!string.IsNullOrEmpty(satellitetaskcode))
satellitetaskCodes.Add(satellitetaskcode);
}
connection.Close();
}
_logger.LogWarning($"// FarmmapsCleanUp: WARNING: you are about to delete {cropfieldItemCodes.Count} cropfieldItemCodes and {satellitetaskCodes.Count} satellitetaskCodes from the FarmMaps database and your own table {schemaname}.{parceltablename}");
_logger.LogInformation($"// Nice of you to clean up after the work is done. You would typically do this for cropfieldItemCodes used only once.");
_logger.LogInformation($"// You do NOT want to do this if you think you may later on still want to use these items.");
_logger.LogInformation($"// Please carefully check the SQL queries 'readSql' 'updateSql' in CleanUpApplication.cs before proceeding.");
_logger.LogInformation($"// FarmmapsCleanUp: delete selected cropfieldItemCodes from FarmMaps database and table {schemaname}.{parceltablename}? 0 = no, 1 = yes");
int i;
i = Int32.Parse(Console.ReadLine());
if (i == 1)
{
await _farmmapsApiService.DeleteItemsAsync(cropfieldItemCodes);
//TODO _farmmapsApiService.DeleteItemsAsync throws an error: {StatusCode: 415, ReasonPhrase: 'Unsupported Media Type', Version: 1.1, Content: System.Net.Http.HttpConnectionResponseContent, Headers: {...}}
//what is wrong with cropfieldItemCodes?
//and shouldn't we be telling _farmmapsApiService.DeleteItemsAsync what item type to delete?
using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
connection.Open();
NpgsqlCommand updateCmd = connection.CreateCommand();
updateCmd.CommandText = updateCropfieldItemCodesSql;
int r = updateCmd.ExecuteNonQuery();
if (r == -1)
throw new Exception("// FarmmapsCleanUp: Update cropfielditemcode Failed");
connection.Close();
}
}
_logger.LogInformation($"// FarmmapsCleanUp: delete selected satellitetaskCodes from FarmMaps database and table {schemaname}.{parceltablename}? 0 = no, 1 = yes");
i = Int32.Parse(Console.ReadLine());
if (i == 1)
{
await _farmmapsApiService.DeleteItemsAsync(satellitetaskCodes);
//TODO _farmmapsApiService.DeleteItemsAsync throws an error: {StatusCode: 415, ReasonPhrase: 'Unsupported Media Type', Version: 1.1, Content: System.Net.Http.HttpConnectionResponseContent, Headers: {...}}
//what is wrong with satellitetaskCodes?
//and shouldn't we be telling _farmmapsApiService.DeleteItemsAsync what item type to delete?
using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
connection.Open();
NpgsqlCommand updateCmd = connection.CreateCommand();
updateCmd.CommandText = updateSatellitetaskCodesSql;
int r = updateCmd.ExecuteNonQuery();
if (r == -1)
throw new Exception("// FarmmapsCleanUp: Update cropfielditemcode Failed");
connection.Close();
}
}
_logger.LogInformation($"// FarmmapsCleanUp: done! Hit any key to exit ...");
Console.ReadKey();
}
}
}