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

using Newtonsoft.Json;
using Npgsql;
using NpgsqlTypes;
using skyscraper8.Ses;
using skyscraper8.Skyscraper.Scraper.Storage;
using skyscraper8.Skyscraper.Scraper.Storage.Utilities;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace skyscraper5.Data.PostgreSql
{
	public partial class PostgresqlDataStore : DataStorage
	{
		private List<ushort> _existingSgtLists;

		public bool TestForSgtList(SgtList list)
		{
			if (_existingSgtLists == null)
				_existingSgtLists = new List<ushort>();

			if (_existingSgtLists.Contains(list.ServiceListId))
				return true;

			bool result;
			using (NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString()))
			{
				connection.Open();
				NpgsqlCommand command = connection.CreateCommand();
				command.CommandText = "SELECT dateadded FROM astra_sgt WHERE slid = @slid";
				command.Parameters.AddParameter("@slid", NpgsqlDbType.Integer, (int)list.ServiceListId);
				NpgsqlDataReader npgsqlDataReader = command.ExecuteReader();
				if (result = npgsqlDataReader.Read())
				{
					_existingSgtLists.Add(list.ServiceListId);
				}
				npgsqlDataReader.Close();
				command.Dispose();
				connection.Close();
			}
			return result;
		}

		public void InsertSgtList(SgtList list)
		{
			using (NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString()))
			{
				connection.Open();
				NpgsqlCommand command = connection.CreateCommand();
				command.CommandText = "INSERT INTO astra_sgt VALUES (@slid,DEFAULT,@pds,@names,@cavailabilities)";
				command.Parameters.AddWithValue("@slid", NpgsqlDbType.Integer, (int)list.ServiceListId);
				command.Parameters.AddWithValue("@pds", NpgsqlDbType.Bigint, (long)list.PrivateDataSpecifier);
				command.Parameters.AddWithValue("@names", NpgsqlDbType.Json, JsonConvert.SerializeObject(list.Names));
				command.Parameters.AddWithValue("@cavailabilities", NpgsqlDbType.Json, JsonConvert.SerializeObject(list.CountryAvailabilities));
				SetNulls(command);
				command.ExecuteNonQuery();
				connection.Close();
				connection.Dispose();
			}

			if (_existingSgtLists == null)
				_existingSgtLists = new List<ushort>();
			if (!_existingSgtLists.Contains(list.ServiceListId))
				_existingSgtLists.Add(list.ServiceListId);
		}

		private HashSet<DatabaseKeySgtService> _knownSgtServices;
		public bool TestForSgtService(SgtService child)
		{
			if (_knownSgtServices == null)
				_knownSgtServices = new HashSet<DatabaseKeySgtService>();

			DatabaseKeySgtService key = child.GetDatabaseKey();
			if (_knownSgtServices.Contains(key))
				return true;

			bool result;
			using (NpgsqlConnection connection = new NpgsqlConnection(connectionStringBuilder.ToString()))
			{
				connection.Open();
				NpgsqlCommand command = connection.CreateCommand();
				command.CommandText = "SELECT dateadded FROM astra_sgt_services WHERE slid = @slid AND sid = @sid AND tsid = @tsid AND onid = @onid";
				command.Parameters.AddWithValue("@slid", NpgsqlDbType.Integer, (int)child.ServiceListId);
				command.Parameters.AddWithValue("@sid", NpgsqlDbType.Integer, (int)child.ServiceId);
				command.Parameters.AddWithValue("@tsid", NpgsqlDbType.Integer, (int)child.TransportStreamId);
				command.Parameters.AddWithValue("@onid", NpgsqlDbType.Integer, (int)child.OriginalNetworkId);
				NpgsqlDataReader dataReader = command.ExecuteReader();
				if (result = dataReader.Read())
				{
					_knownSgtServices.Add(key);
				}
				dataReader.Close();
				command.Dispose();
				connection.Close();
			}
			return result;
		}

		public void InsertSgtService(SgtService child)
		{
			DatabaseKeySgtService key = child.GetDatabaseKey();
			if (_knownSgtServices.Contains(key))
				return;

			EnqueueTask(x => InsertSgtServiceEx(x, child));
			_knownSgtServices.Add(key);
		}

		private void InsertSgtServiceEx(NpgsqlConnection x, SgtService child)
		{
			NpgsqlCommand command = x.CreateCommand();
			command.CommandText = "INSERT INTO astra_sgt_services " +
				"VALUES (@slid,@sid,@tsid,@onid,DEFAULT,@lcn,@visible,@newService,@genrecode,@csids,@sname,@spname,@stype,@vsids,@blist)";
			command.Parameters.AddWithValue("@slid", NpgsqlDbType.Integer, (int)child.ServiceListId);
			command.Parameters.AddWithValue("@sid", NpgsqlDbType.Integer, (int)child.ServiceId);
			command.Parameters.AddWithValue("@tsid", NpgsqlDbType.Integer, (int)child.TransportStreamId);
			command.Parameters.AddWithValue("@onid", NpgsqlDbType.Integer, (int)child.OriginalNetworkId);
			command.Parameters.AddWithValue("@lcn", NpgsqlDbType.Integer, child.Lcn);
			command.Parameters.AddWithValue("@visible", NpgsqlDbType.Boolean, child.VisibleServiceFlag);
			command.Parameters.AddWithValue("@newService", NpgsqlDbType.Boolean, child.NewServiceFlag);
			command.Parameters.AddWithValue("@genrecode", NpgsqlDbType.Integer, (int)child.GenreCode);
			command.Parameters.AddWithValue("@csids",NpgsqlDbType.Json, JsonConvert.SerializeObject(child.CaSystemIds));
			command.Parameters.AddWithValue("@sname", NpgsqlDbType.Text, child.ServiceDescriptor?.ServiceName);
			command.Parameters.AddWithValue("@spname", NpgsqlDbType.Text, child.ServiceDescriptor?.ServiceProviderName);
			command.Parameters.AddWithValue("@stype", NpgsqlDbType.Integer, (int)child.ServiceDescriptor?.ServiceType);
			command.Parameters.AddWithValue("@vsids", NpgsqlDbType.Json, JsonConvert.SerializeObject(child.VirtualServiceIds));
			command.Parameters.AddWithValue("@blist", NpgsqlDbType.Text, child.BouquetList);
			SetNulls(command);
			command.ExecuteNonQuery();
		}
	}
}