skyscraper8/DataTableStorages/skyscraper5.Data.PostgreSql/Beams.cs

using Npgsql;
using NpgsqlTypes;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using skyscraper5.Skyscraper.Gps;

namespace skyscraper5.Data.PostgreSql
{
	public partial class PostgresqlDataStore
	{
		public void BeamsDisableAll()
		{
			using (NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString()))
			{
				connection.Open();
				NpgsqlCommand command = connection.CreateCommand();
				command.CommandText = "UPDATE skyscraper5_beams SET enabled = FALSE";
				command.ExecuteNonQuery();
				connection.Close();
			}
		}

		public void BeamsEnable(int id, float satpos, string name, DateTime processTimestamp)
		{
			using (NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString()))
			{
				connection.Open();
				NpgsqlCommand command = connection.CreateCommand();
				command.CommandText = "SELECT dateadded FROM skyscraper5_beams WHERE id = @id AND process_timestamp = @process_timestamp";
				command.Parameters.AddWithValue("@id", NpgsqlDbType.Integer, id);
				command.Parameters.AddWithValue("@process_timestamp", NpgsqlDbType.Timestamp, processTimestamp);
				NpgsqlDataReader dataReader = command.ExecuteReader();
				bool isKnown = dataReader.Read();
				dataReader.Close();
				if (isKnown)
				{
					command.CommandText = "UPDATE skyscraper5_beams SET enabled = TRUE WHERE id = @id AND process_timestamp = @process_timestamp";
					command.ExecuteNonQuery();
				}
				else
				{
					command.CommandText = "INSERT INTO skyscraper5_beams (id,satpos,name,process_timestamp) VALUES (@id,@satpos,@name,@process_timestamp)";
					command.Parameters.AddWithValue("@satpos", NpgsqlDbType.Double, (double)satpos);
					command.Parameters.AddWithValue("@name", NpgsqlDbType.Text, name);
					command.ExecuteNonQuery();
				}
				connection.Close();
			}
		}

		public void BeamFootprintStore(int databasePointerId, DateTime databasePointerBeamsProcessTimestamp,
		string name,
			string getPolygonString, string id)
		{
			using (NpgsqlConnection conn = new NpgsqlConnection(connectionStringBuilder.ToString()))
			{
				conn.Open();
				NpgsqlCommand command = conn.CreateCommand();
				command.CommandText =
					"insert into skyscraper5_beams_footprints (beam_id, beam_process_timestamp, name, polygon,polygonid)\r\n" +
					"values (@beam_id,@beam_process_timestamp,@name,@polygon,@polygonid)";
				command.Parameters.AddWithValue("@beam_id", NpgsqlDbType.Integer, databasePointerId);
				command.Parameters.AddWithValue("@beam_process_timestamp", NpgsqlDbType.Timestamp, databasePointerBeamsProcessTimestamp);
				command.Parameters.AddWithValue("@name", NpgsqlDbType.Varchar, name);
				command.Parameters.AddWithValue("@polygon", NpgsqlDbType.Text, getPolygonString);
				command.Parameters.AddWithValue("@polygonid", NpgsqlDbType.Text, id);
				command.ExecuteNonQuery();
				conn.Close();
			}
		}

		public bool TestForBeamFootprint(int databasePointerId, DateTime databasePointerBeamsProcessTimestamp, string name, string polygonid)
		{
			bool result;
			using (NpgsqlConnection conn = new NpgsqlConnection(connectionStringBuilder.ToString()))
			{
				conn.Open();
				NpgsqlCommand command = conn.CreateCommand();
				command.CommandText = "SELECT dateadded " +
									  "FROM skyscraper5_beams_footprints " +
									  "WHERE beam_id = @beam_id " +
									  "AND beam_process_timestamp = @beam_process_timestamp " +
									  "AND name = @name " +
									  "AND polygonid = @polygonid ";
				command.Parameters.AddWithValue("@beam_id", NpgsqlDbType.Integer, databasePointerId);
				command.Parameters.AddWithValue("@beam_process_timestamp", NpgsqlDbType.Timestamp, databasePointerBeamsProcessTimestamp);
				command.Parameters.AddWithValue("@name", NpgsqlDbType.Text, name);
				command.Parameters.AddWithValue("@polygonid", NpgsqlDbType.Text, polygonid);
				NpgsqlDataReader dataReader = command.ExecuteReader();
				result = dataReader.Read();
				dataReader.Close();
				conn.Close();
			}

			return result;
		}

		public void BeamsDisableSpecific(int id, float databasePointerSatpos, string databasePointerName,
			DateTime processTimestamp)
		{
			using (NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString()))
			{
				connection.Open();
				NpgsqlCommand command = connection.CreateCommand();
				command.CommandText = "SELECT dateadded FROM skyscraper5_beams WHERE id = @id AND process_timestamp = @process_timestamp";
				command.Parameters.AddWithValue("@id", NpgsqlDbType.Integer, id);
				command.Parameters.AddWithValue("@process_timestamp", NpgsqlDbType.Timestamp, processTimestamp);
				NpgsqlDataReader dataReader = command.ExecuteReader();
				bool isKnown = dataReader.Read();
				dataReader.Close();
				if (isKnown)
				{
					command.CommandText = "UPDATE skyscraper5_beams SET enabled = FALSE WHERE id = @id AND process_timestamp = @process_timestamp";
					command.ExecuteNonQuery();
				}
				else
				{
					//Not yet known, so we won't need to update. Okay, keep going.
				}
				connection.Close();
			}
		}

		public IEnumerable<SatelliteBeam> BeamsSelectEnabled()
		{
			using (NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString()))
			{
				connection.Open();
				NpgsqlCommand command = connection.CreateCommand();
				command.CommandText = "SELECT id, satpos, name, process_timestamp FROM skyscraper5_beams";
				NpgsqlDataReader dataReader = command.ExecuteReader();
				while (dataReader.Read())
				{
					int id = dataReader.GetInt32(0);
					float satpos = dataReader.GetFloat(1);
					string name = dataReader.GetString(2);
					DateTime processTimestamp = dataReader.GetDateTime(3);
					yield return new SatelliteBeam(id, satpos, name, processTimestamp);
				}
				dataReader.Close();
				command.Dispose();
				connection.Close();
			}
		}

		public List<SatelliteBeamFootprint> BeamsSelectFootprints(int satelliteBeamId, DateTime satelliteBeamProcessTimestamp)
		{
			List<SatelliteBeamFootprint> result = new List<SatelliteBeamFootprint>();
			using (NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString()))
			{
				connection.Open();
				NpgsqlCommand command = connection.CreateCommand();
				command.CommandText = "SELECT name, polygon, dateadded, polygonid FROM skyscraper5_beams_footprints WHERE beam_id = @id AND beam_process_timestamp = @process_timestamp";
				command.Parameters.AddWithValue("@id", NpgsqlDbType.Integer, satelliteBeamId);
				command.Parameters.AddWithValue("@process_timestamp", NpgsqlDbType.Timestamp, satelliteBeamProcessTimestamp);
				NpgsqlDataReader dataReader = command.ExecuteReader();
				while (dataReader.Read())
				{
					string name = dataReader.GetString(0);
					string polygon = dataReader.GetString(1);
					DateTime dateadded = dataReader.GetDateTime(2);
					string polygonId = dataReader.GetString(3);
					result.Add(new SatelliteBeamFootprint(satelliteBeamId, satelliteBeamProcessTimestamp, name, polygon, dateadded, polygonId));
				}
			}
			return result;
		}
	}
}