path: root/Software/Visual_Studio/Tango.Synchronization/Remote/RemoteDBComparer.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using remote = Tango.DAL.Remote.DB;
using local = Tango.DAL.Local.DB;
using System.Data.Entity;
using Tango.Logging;
using System.Collections;

namespace Tango.Synchronization.Remote
{
    public class RemoteDBComparer : IDBComparer
    {
        private remote.RemoteDB _remoteDB;
        private local.LocalDB _localDB;
        private String _machineSerial;
        private List<Diff> _diffs;

        public RemoteDBComparer(remote.RemoteDB remoteDB, local.LocalDB localDB, String machineSerial)
        {
            _machineSerial = machineSerial;
            _remoteDB = remoteDB;
            _localDB = localDB;
        }

        public List<Diff> Compare()
        {
            _diffs = new List<Diff>();

            LogManager.Log("Performing SQL Server <=> SQLite database comparison...");

            LogManager.Log("Loading SQLite database to memory...");

            LogManager.Log("Querying all local addresses...");
            var local_addresses = _localDB.ADDRESSES.ToList();
            LogManager.Log("Querying all local cartridges...");
            var local_cartridges = _localDB.CARTRIDGES.ToList();
            LogManager.Log("Querying all local configurations...");
            var local_configurations = _localDB.CONFIGURATIONS.ToList();
            LogManager.Log("Querying all local configuration dispensers...");
            var local_ids_packs = _localDB.IDS_PACKS.ToList();
            LogManager.Log("Querying all local contacts...");
            var local_contacts = _localDB.CONTACTS.ToList();
            LogManager.Log("Querying all local dispensers...");
            var local_dispensers = _localDB.DISPENSERS.ToList();
            LogManager.Log("Querying all local machines...");
            var local_machines = _localDB.MACHINES.Where(x => x.SERIAL_NUMBER == _machineSerial).ToList();
            LogManager.Log("Querying all local machines configurations...");
            var local_machines_configurations = _localDB.MACHINES_CONFIGURATIONS.ToList();
            LogManager.Log("Querying all local machines events...");
            var local_machines_events = _localDB.MACHINES_EVENTS.ToList();
            LogManager.Log("Querying all local organizations...");
            var local_organizations = _localDB.ORGANIZATIONS.ToList();
            LogManager.Log("Querying all local users...");
            var local_users = _localDB.USERS.ToList();
            LogManager.Log("Querying all local users roles...");
            var local_users_roles = _localDB.USERS_ROLES.ToList();
            LogManager.Log("Querying all CATS...");
            var local_cats = _localDB.CATS.ToList();

            LogManager.Log("Loading SQL Server database to memory...");

            List<String> guids = new List<string>();

            LogManager.Log("Querying all remote machines...");
            var remote_machines = _remoteDB.MACHINES.Where(x => x.SERIAL_NUMBER == _machineSerial).ToList();

            if (remote_machines.Count == 0)
            {
                throw LogManager.Log(new DataBaseComparisonException("Could not locate machine on remote server " + _machineSerial));
            }

            LogManager.Log("Querying all remote organizations...");
            guids = remote_machines.Select(x => x.ORGANIZATION_GUID).ToList();
            var remote_organizations = _remoteDB.ORGANIZATIONS.Where(x => guids.Contains(x.GUID)).ToList();

            LogManager.Log("Querying all remote machines configurations...");
            guids = remote_machines.Select(x => x.GUID).ToList();
            var remote_machines_configurations = _remoteDB.MACHINES_CONFIGURATIONS.Where(x => guids.Contains(x.MACHINE_GUID)).ToList();
            var remote_configurations = remote_machines_configurations.Select(x => x.CONFIGURATION).ToList();

            LogManager.Log("Querying all remote machines events...");
            var remote_machines_events = _remoteDB.MACHINES_EVENTS.Where(x => guids.Contains(x.MACHINE_GUID)).ToList();

            LogManager.Log("Querying all remote users...");
            guids = remote_organizations.Select(x => x.GUID).ToList();
            var remote_users = _remoteDB.USERS.Where(x => guids.Contains(x.ORGANIZATION_GUID)).ToList();

            LogManager.Log("Querying all remote users roles...");
            guids = remote_users.Select(x => x.GUID).ToList();
            var remote_users_roles = _remoteDB.USERS_ROLES.Where(x => guids.Contains(x.USER_GUID)).ToList();

            LogManager.Log("Querying all remote addresses...");
            var remote_addresses = remote_users.Select(x => x.ADDRESS).ToList();
            remote_addresses.AddRange(remote_machines.Select(x => x.ORGANIZATION).Select(x => x.ADDRESS));
            remote_addresses = remote_addresses.Distinct().ToList();

            LogManager.Log("Querying all remote contacts...");
            var remote_contacts = remote_users.Select(x => x.CONTACT).ToList();
            remote_contacts.AddRange(remote_machines.Select(x => x.ORGANIZATION).Select(x => x.CONTACT));
            remote_contacts = remote_contacts.Distinct().ToList();

            LogManager.Log("Querying all remote configurations dispensers...");
            guids = remote_configurations.Select(x => x.GUID).ToList();
            var remote_ids_packs = _remoteDB.IDS_PACKS.Where(x => guids.Contains(x.CONFIGURATION_GUID)).ToList();

            LogManager.Log("Querying all remote dispensers...");
            guids = remote_ids_packs.Select(x => x.DISPENSER_GUID).ToList();
            var remote_dispensers = _remoteDB.DISPENSERS.Where(x => guids.Contains(x.GUID)).ToList();

            LogManager.Log("Querying all remote cartridges...");
            guids = remote_ids_packs.Select(x => x.CARTRIDGE_GUID).ToList();
            var remote_cartridges = _remoteDB.CARTRIDGES.Where(x => guids.Contains(x.GUID)).ToList();

            LogManager.Log("Querying all remote CATS...");
            guids = remote_machines.Select(x => x.GUID).ToList();
            var remote_cats = _remoteDB.CATS.Where(x => guids.Contains(x.MACHINE_GUID)).ToList();


            foreach (var config in _remoteDB.SYNC_CONFIGURATIONS.Where(x => (SyncConfiguration)x.SYNC_TYPE == SyncConfiguration.OverwriteLocal))
            {
                OverrideTable(config);
            }


            LogManager.Log("Comparing addresses");
            CompareCollections(remote_addresses, local_addresses, _remoteDB.ADDRESSES, _localDB.ADDRESSES);

            LogManager.Log("Comparing cartridges");
            CompareCollections(remote_cartridges, local_cartridges, _remoteDB.CARTRIDGES, _localDB.CARTRIDGES);

            LogManager.Log("Comparing configurations");
            CompareCollections(remote_configurations, local_configurations, _remoteDB.CONFIGURATIONS, _localDB.CONFIGURATIONS);

            LogManager.Log("Comparing configurations dispensers");
            CompareCollections(remote_ids_packs, local_ids_packs, _remoteDB.IDS_PACKS, _localDB.IDS_PACKS);

            LogManager.Log("Comparing contacts");
            CompareCollections(remote_contacts, local_contacts, _remoteDB.CONTACTS, _localDB.CONTACTS);

            LogManager.Log("Comparing dispensers");
            CompareCollections(remote_dispensers, local_dispensers, _remoteDB.DISPENSERS, _localDB.DISPENSERS);

            LogManager.Log("Comparing machines");
            CompareCollections(remote_machines, local_machines, _remoteDB.MACHINES, _localDB.MACHINES);

            LogManager.Log("Comparing machines configuration");
            CompareCollections(remote_machines_configurations, local_machines_configurations, _remoteDB.MACHINES_CONFIGURATIONS, _localDB.MACHINES_CONFIGURATIONS);

            LogManager.Log("Comparing machines events");
            CompareCollections(remote_machines_events, local_machines_events, _remoteDB.MACHINES_EVENTS, _localDB.MACHINES_EVENTS);

            LogManager.Log("Comparing organizations");
            CompareCollections(remote_organizations, local_organizations, _remoteDB.ORGANIZATIONS, _localDB.ORGANIZATIONS);

            LogManager.Log("Comparing users");
            CompareCollections(remote_users, local_users, _remoteDB.USERS, _localDB.USERS);

            LogManager.Log("Comparing users roles");
            CompareCollections(remote_users_roles, local_users_roles, _remoteDB.USERS_ROLES, _localDB.USERS_ROLES);

            LogManager.Log("Comparing cats");
            CompareCollections(remote_cats, local_cats, _remoteDB.CATS, _localDB.CATS);

            LogManager.Log("Comparison done!");

            return _diffs;
        }

        public void Dispose()
        {
            _remoteDB.Dispose();
            _localDB.Dispose();
        }

        private void CopyEntity(Object source, Object destination)
        {
            foreach (var prop in source.GetType().GetProperties().Where(x => x.PropertyType.IsPrimitive || x.PropertyType == typeof(String) || x.PropertyType == typeof(DateTime) || x.PropertyType == typeof(byte[])))
            {
                if (destination.GetType().GetProperty(prop.Name) == null)
                {
                    throw LogManager.Log(new InvalidOperationException(String.Format("Property '{0}' not found on destination table '{1}'.", prop.Name, destination.GetType().Name)));
                }

                if (prop.PropertyType == typeof(Int64))
                {
                    destination.GetType().GetProperty(prop.Name).SetValue(destination, Convert.ToInt32((Int64)prop.GetValue(source)));
                }
                else
                {
                    destination.GetType().GetProperty(prop.Name).SetValue(destination, prop.GetValue(source));
                }
            }
        }

        private void CompareCollections<Master, Slave>(List<Master> masterCollection, List<Slave> slaveCollection, DbSet<Master> masterSet, DbSet<Slave> slaveSet) where Master : class where Slave : class
        {
            var slaveProp = typeof(Slave).GetProperty("GUID");
            var masterProp = typeof(Master).GetProperty("GUID");

            List<Slave> compared = new List<Slave>();

            foreach (var masterRow in masterCollection)
            {
                Slave slaveRow = slaveCollection.SingleOrDefault(x => slaveProp.GetValue(x).ToString() == masterProp.GetValue(masterRow).ToString());
                CompareEntities(masterRow, slaveRow, masterSet, slaveSet);

                if (slaveRow != null)
                {
                    compared.Add(slaveRow);
                }
            }

            foreach (var slaveRow in slaveCollection.Where(x => !compared.Contains(x)))
            {
                CompareEntities(masterCollection.SingleOrDefault(x => masterProp.GetValue(x).ToString() == slaveProp.GetValue(slaveRow).ToString()), slaveRow, masterSet, slaveSet);
            }
        }

        private void OverrideTable(remote.SYNC_CONFIGURATIONS config)
        {
            LogManager.Log("Generating table override difference for " + config.TABLE_NAME + "...");

            var master = _remoteDB.GetType().GetProperty(config.TABLE_NAME).GetValue(_remoteDB) as IEnumerable;
            var slave = _localDB.GetType().GetProperty(config.TABLE_NAME).GetValue(_localDB) as IEnumerable;

            _diffs.Add(new Diff(DiffAction.ReplaceTableDataInSlave, "Override all rows on slave table " + config.TABLE_NAME, () =>
            {
                LogManager.Log("Overwriting slave table " + config.TABLE_NAME + "...");

                _localDB.Database.ExecuteSqlCommand("DELETE FROM " + config.TABLE_NAME + ";");

                foreach (var entity in master)
                {
                    var newRow = slave.GetType().GetMethods().Where(x => x.Name == "Create").First().Invoke(slave, new object[] { });
                    CopyEntity(entity, newRow);
                    slave.GetType().GetMethod("Add").Invoke(slave, new object[] { newRow });
                }

            }, null));
        }

        private void CompareEntities<Master, Slave>(Master master, Slave slave) where Master : class where Slave : class
        {
            Diff diff = null;

            DateTime masterDate = (DateTime)master.GetType().GetProperty("LAST_UPDATED").GetValue(master);
            DateTime slaveDate = (DateTime)slave.GetType().GetProperty("LAST_UPDATED").GetValue(slave);

            if (masterDate > slaveDate)
            {
                diff = new Diff(DiffAction.UpdateRowInSlave, "Update row in slave table " + typeof(Master).Name, () => 
                {
                    LogManager.Log("Updating row in slave table " + typeof(Master).Name);
                    CopyEntity(master, slave);
                }, null);
            }
            else if (slaveDate > masterDate)
            {
                diff = new Diff(DiffAction.UpdateRowInMaster, "Update row in master table " + typeof(Master).Name, () => 
                {
                    LogManager.Log("Updating row in master table " + typeof(Master).Name);
                    CopyEntity(slave, master);
                }, null);
            }

            if (diff != null)
            {
                _diffs.Add(diff);
            }
        }

        private void CompareEntities<Master, Slave>(Master master, Slave slave, DbSet<Master> masterSet, DbSet<Slave> slaveSet) where Master : class where Slave : class
        {
            if (slave == null)
            {
                _diffs.Add(new Diff(DiffAction.AddRowToSlave, "Add row to slave table " + typeof(Master).Name, () =>
               {
                   LogManager.Log("Adding row to slave table " + typeof(Master).Name);
                   Slave newRow = slaveSet.Create();
                   CopyEntity(master, newRow);
                   slaveSet.Add(newRow);

               }, null));

                return;
            }

            if (master == null)
            {
                _diffs.Add(new Diff(DiffAction.AddRowToMaster, "Add row to master table " + typeof(Master).Name, () =>
                {
                    LogManager.Log("Adding row to master table " + typeof(Master).Name);
                    Master newRow = masterSet.Create();
                    CopyEntity(slave, newRow);
                    masterSet.Add(newRow);

                }, null));

                return;
            }

            CompareEntities(master, slave);
        }
    }
}