skyscraper8/DataTableStorages/skyscraper5.Data.MySql/Tot.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Versioning;
using System.Text;
using System.Threading.Tasks;
using MySqlConnector;
using skyscraper5.Dvb.Descriptors;
using skyscraper8.Skyscraper.Scraper.Storage;

namespace skyscraper5.Data.MySql
{
    public partial class MySqlDataStorage : DataStorage
    {
        private DateTime? TestForTimeOffset(MySqlTransaction transaction, int currentNetworkId, int currentTransportStreamId, string timeOffsetCountryCode)
        {
            DateTime? result = null;
            if (_totCoordinates == null)
                _totCoordinates = new HashSet<TotCoordinate>();
            TotCoordinate coordinate = new TotCoordinate(currentNetworkId, currentTransportStreamId, timeOffsetCountryCode);
            TotCoordinate cachedCoordinate;
            bool wasCached = _totCoordinates.TryGetValue(coordinate, out cachedCoordinate);
            if (wasCached)
                return cachedCoordinate.Timestamp;

            MySqlCommand command = transaction.Connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText = "SELECT utc FROM dvb_tot WHERE nid = @nid AND tsid = @tsid AND countrycode = @country_code";
            command.Parameters.AddWithValue("@nid", currentNetworkId);
            command.Parameters.AddWithValue("@tsid", currentTransportStreamId);
            command.Parameters.AddWithValue("@country_code", timeOffsetCountryCode);
            MySqlDataReader dataReader = command.ExecuteReader();
            if (dataReader.Read())
            {
                result = dataReader.GetDateTime(0);
                coordinate.Timestamp = result.Value;
            }

            dataReader.Close();
            return result;
        }

        private void UpdateTimeOffset(MySqlTransaction transaction, int currentNetworkId, int currentTransportStreamId, DateTime utcTime, LocalTimeOffsetDescriptor.LocalTime timeOffset)
        {
            MySqlCommand command = transaction.Connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText =
                "UPDATE dvb_tot SET utc = @utc, local_time_offset = @local_time_offset, time_of_change = @time_of_change, next_time_offset = @next_time_offset " +
                "WHERE nid = @nid AND tsid = @tsid AND countrycode = @country_code";
            command.Parameters.AddWithValue("@nid", currentNetworkId);
            command.Parameters.AddWithValue("@tsid", currentTransportStreamId);
            command.Parameters.AddWithValue("@country_code", timeOffset.CountryCode);
            command.Parameters.AddWithValue("@utc", utcTime);
            command.Parameters.AddWithValue("@local_time_offset", (int)timeOffset.LocalTimeOffset.TotalSeconds);
            command.Parameters.AddWithValue("@time_of_change", timeOffset.TimeOfChange);
            command.Parameters.AddWithValue("@next_time_offset", timeOffset.NextTimeOffset);
            command.ExecuteNonQuery();
        }

        private void InsertTimeOffset(MySqlTransaction transaction, int currentNetworkId, int currentTransportStreamId, DateTime utcTime, LocalTimeOffsetDescriptor.LocalTime timeOffset)
        {
            MySqlCommand command = transaction.Connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText =
                "INSERT dvb_tot (nid, tsid, utc, countrycode, country_region_id, local_time_offset_polarity, local_time_offset, time_of_change, next_time_offset) " +
                "VALUES (@nid, @tsid, @utc, @countrycode, @country_region_id, @local_time_offset_polarity, @local_time_offset, @time_of_change, @next_time_offset)";
            command.Parameters.AddWithValue("@nid", currentNetworkId);
            command.Parameters.AddWithValue("@tsid", currentTransportStreamId);
            command.Parameters.AddWithValue("@utc", utcTime);
            command.Parameters.AddWithValue("@countrycode", timeOffset.CountryCode);
            command.Parameters.AddWithValue("@country_region_id", timeOffset.CountryRegionId);
            command.Parameters.AddWithValue("@local_time_offset_polarity", timeOffset.LocalTimeOffsetPolarity);
            command.Parameters.AddWithValue("@local_time_offset", (int)timeOffset.LocalTimeOffset.TotalSeconds);
            command.Parameters.AddWithValue("@time_of_change", timeOffset.TimeOfChange);
            command.Parameters.AddWithValue("@next_time_offset", timeOffset.NextTimeOffset);
            command.ExecuteNonQuery();

            TdtCoordinate tdtCoordinate = new TdtCoordinate(currentNetworkId, currentTransportStreamId);
            tdtCoordinate.CoordinateTimestamp = utcTime;
            _tdtCoordinates.Add(tdtCoordinate);
        }

        struct TotCoordinate
        {
            private readonly int _currentNetworkId;
            private readonly int _currentTransportStreamId;
            private readonly string _timeOffsetCountryCode;

            public TotCoordinate(int currentNetworkId, int currentTransportStreamId, string timeOffsetCountryCode)
            {
                _currentNetworkId = currentNetworkId;
                _currentTransportStreamId = currentTransportStreamId;
                _timeOffsetCountryCode = timeOffsetCountryCode;
                Timestamp = DateTime.MinValue;
            }

            public bool Equals(TotCoordinate other)
            {
                return _currentNetworkId == other._currentNetworkId && _currentTransportStreamId == other._currentTransportStreamId && _timeOffsetCountryCode == other._timeOffsetCountryCode;
            }

            public override bool Equals(object obj)
            {
                return obj is TotCoordinate other && Equals(other);
            }

            public override int GetHashCode()
            {
                return HashCode.Combine(_currentNetworkId, _currentTransportStreamId, _timeOffsetCountryCode);
            }

            public DateTime Timestamp { get; set; }
        }

        private HashSet<TotCoordinate> _totCoordinates;

		public bool UpdateTimeOffsetTable(int currentNetworkId, int currentTransportStreamId, DateTime utcTime, LocalTimeOffsetDescriptor ltod)
		{
			if (ltod.LocalTimeOffsets == null)
				return false;

			if (ltod.LocalTimeOffsets.Length == 0)
				return false;

			using (MySqlConnection connection = new MySqlConnection(_mcsb.ToString()))
			{
				bool result = false;
				connection.Open();
				MySqlTransaction transaction = connection.BeginTransaction();
				foreach (LocalTimeOffsetDescriptor.LocalTime timeOffset in ltod.LocalTimeOffsets)
				{
					DateTime? older = TestForTimeOffset(transaction, currentNetworkId, currentTransportStreamId, timeOffset.CountryCode);
					if (older.HasValue)
					{
						if (utcTime > older.Value)
						{
							//UpdateTimeOffset(transaction, currentNetworkId, currentTransportStreamId, utcTime, timeOffset);
							EnqueueSpeedhack(SpeedhackType.UpdateTimeOffset, currentNetworkId, currentTransportStreamId, utcTime, timeOffset);
							result = true;
						}
					}
					else
					{
						//InsertTimeOffset(transaction, currentNetworkId, currentTransportStreamId, utcTime, timeOffset);
						EnqueueSpeedhack(SpeedhackType.InsertTimeOffset, currentNetworkId, currentTransportStreamId, utcTime, timeOffset);
						result = true;
					}
				}
				transaction.Commit();
				connection.Close();
				return result;
			}
		}


	}
}