EEMS/Test/DBTest.cs

using DataService;
using DB_Proxima;
using Dm.util;
using Mapster;
using Newtonsoft.Json.Linq;
using SqlSugar;
using SqlSugar.Extensions;
using SqlSugarORM;
using System;
using System.Diagnostics;
using System.Dynamic;
using System.Numerics;
using System.Reflection;
using System.Runtime.CompilerServices;
using System.Text.Json;
using Universal;

namespace Test;

internal class DBTest
{

    private SqlSugarCustom _orm;

    public bool Initialize()
    {
        _orm = new SqlSugarCustom();
        _orm.CreateDataBase("DBTest");
        _orm.Initialize();
        string dbString = "Database=DBTest;Password=123456;Host=localhost;Username=postgres;Persist Security Info=True";
        return _orm.Open(dbString, DbType.PostgreSQL, true);

    }

    public void TestRaw()
    {

        SqlSugarCustom orm = new();

        orm.Initialize();
        string dbString = "Database=FROMTIN;Password=123456;Host=localhost;Username=postgres;Persist Security Info=True";
        //string dbString = "Database=Kepler;Password=123456;Host=localhost;Username=postgres;Persist Security Info=True";

        if (!orm.Open(dbString, SqlSugar.DbType.PostgreSQL, true))
            return;

        List<DbTableInfo> tables = orm.Client.DbMaintenance.GetTableInfoList();

        Stopwatch sw = new();
        sw.Start();
        int i = 0;
        Parallel.ForEach(tables, table =>
        {
            string localName = table.Name.Replace('.', '_');
            if (localName== "Recipe_History")
                return;
            if (localName == "event_data")
                return;

            var client = orm.Client.CopyNew();

            List<DbColumnInfo> dbColumns = client.DbMaintenance.GetColumnInfosByTableName(table.Name);
            RemoteToLocal remoteToLocal = new();
            //remoteToLocal.Initialize("DBTestKepler", "123456", "localhost", "postgres");
            remoteToLocal.Initialize("DBTestTIN", "123456", "localhost", "postgres");
            remoteToLocal.CreateTableRaw(localName, dbColumns);

            int totalCount = 0;
            int totalPage = 0;
            int currentPage = 1;

            int line = 0;
            lock (this)
                line = i++;
            do
            {
                dynamic[] dataCollection = client.Queryable<dynamic>().AS($"\"{table.Name}\"").ToPageList(currentPage, 100, ref totalCount, ref totalPage).ToArray();
                //Console.SetCursorPosition(0, Console.CursorTop - 1);
                lock (this)
                {
                    Console.SetCursorPosition(0, line);
                    Console.WriteLine($"{table.Name.PadRight(35)} Columns {dbColumns.Count.ToString().PadRight(15)}  100 Rows / page     Page {currentPage}/{totalPage}");
                }
                foreach (dynamic data in dataCollection)
                {
                    if (data is IDictionary<string, object> rawData)
                        remoteToLocal.InsertDataRaw(localName, rawData);
                }
            } while (++currentPage <= totalPage);
        });


        //foreach (DbTableInfo table in tables)
        //{
        //    string localName = table.Name.Replace('.', '_');
        //    List<DbColumnInfo> dbColumns = orm.Client.DbMaintenance.GetColumnInfosByTableName(table.Name);
        //    RemoteToLocal remoteToLocal = new();
        //    remoteToLocal.Initialize("DBTestKepler", "123456", "localhost", "postgres");
        //    remoteToLocal.CreateTableRaw(localName, dbColumns);

        //    int totalCount = 0;
        //    int totalPage = 0;
        //    int currentPage = 1;


        //    Console.WriteLine($"Move Table {localName} total column {dbColumns.Count}");
        //    Console.WriteLine();
        //    do
        //    {
        //        dynamic[] dataCollection = orm.Client!.Queryable<dynamic>().AS($"\"{table.Name}\"").ToPageList(currentPage, 100, ref totalCount, ref totalPage).ToArray();
        //        Console.SetCursorPosition(0, Console.CursorTop - 1);
        //        Console.WriteLine($"Page {currentPage}/{totalPage}");
        //        foreach (dynamic data in dataCollection)
        //        {
        //            if (data is IDictionary<string, object> rawData)
        //                remoteToLocal.InsertDataRaw(localName, rawData);
        //        }
        //    } while (++currentPage <= totalPage);
        //}

        sw.Stop();
        Console.SetCursorPosition(0, ++i);

        Console.WriteLine($"{sw.Elapsed.TotalSeconds} seconds");

    }

    public void TestOthers()
    {
        SqlSugarCustom orm = new();

        orm.Initialize();
        string dbString = "Database=tin01_db;Password=123456;Host=10.4.6.48;Username=postgres;Persist Security Info=True";

        if (!orm.Open(dbString, SqlSugar.DbType.PostgreSQL, true))
            return;

        dynamic system = orm.Client!.Queryable<dynamic>().AS("\"20250708.System\"").First();
        dynamic pm = orm.Client!.Queryable<dynamic>().AS("\"20250708.PM1\"").First();

        GeneralProcessData processData = new(0);
        if (!processData.ToDictionary(system, out Dictionary<string, object>? systemDic) || systemDic is null)
            return;
        if (!processData.ToDictionary(pm, out Dictionary<string, object>? pmDic) || pmDic is null)
            return;
        if (pmDic["PM1"] is not IDictionary<string, object> data)
            return;

        RemoteToLocal remoteToLocal = new();
        remoteToLocal.Initialize("DBTest", "123456", "localhost", "postgres");
        //remoteToLocal.CreateTableSystem(systemDic);
        remoteToLocal.CreateTablePM(data);
        Stopwatch sw = new();
        sw.Start();
        int totalCount = 0;
        int totalPage = 0;
        int currentPage = 1;
        do
        {
            Console.WriteLine($"page {currentPage}");
            foreach (var item in orm.Client!.Queryable<dynamic>().AS("\"20250708.PM1\"").ToPageList(currentPage, 1000, ref totalCount, ref totalPage).ToArray())
            {

                if (!processData.ToDictionary(item, out Dictionary<string, object>? pms) || pms is null)
                    return;

                DateTime time = LongToDateTime(pms["time"]).Value;
                Guid guid = Guid.NewGuid();

                if (pms["PM1"] is not IDictionary<string, object> pmc)
                    return;

                remoteToLocal.InsertDataPM(pmc, guid, time);

            }
        } while (++currentPage <= totalPage);

        //foreach (dynamic item in orm.Client!.Queryable<dynamic>().AS("\"20250708.PM1\"").ToArray())
        //{
        //    if (!processData.ToDictionary(item, out Dictionary<string, object>? pms) || pms is null)
        //        return;

        //    DateTime time = LongToDateTime(pms["time"]).Value;
        //    Guid guid = Guid.NewGuid();

        //    if (pms["PM1"] is not IDictionary<string, object> pmc)
        //        return;

        //    remoteToLocal.InsertDataPM(pmc, guid, time);
        //}
        sw.Stop();
        Console.WriteLine(sw.Elapsed.TotalSeconds);
    }

    //public void Test(IDictionary<string, object> data, Guid guid, DateTime time)
    public void Test()
    {
        SqlSugarCustom orm = new();
        orm.Initialize();

        string dbString = "Database=tin01_db;Password=123456;Host=10.4.6.48;Username=postgres;Persist Security Info=True";
        if (!orm.Open(dbString, SqlSugar.DbType.PostgreSQL, true))
            return;

        dynamic t = orm.Client!.Queryable<dynamic>().AS("\"20250708.PM1\"").First();
        GeneralProcessData processData = new(0);
        if (!processData.ToDictionary(t, out Dictionary<string, object>? outputs) || outputs is null)
            return;

        DateTime time = LongToDateTime(outputs["time"]).Value;

        if (outputs["PM1"] is not IDictionary<string, object> data)
            return;
        Guid guid = Guid.NewGuid();

        _orm.CreateTable<FFU>("FFU");
        _orm.CreateTable<MFC>("MFC");
        _orm.CreateTable<GaslineHeater>("GaslineHeater");
        _orm.CreateTable<BufferFoup>("GaslineHeater");
        _orm.CreateTable<AvValue>("AvValue");

        Dictionary<string, object> ValueSensor = [];
        Dictionary<string, object> StatusSensor = [];
        Dictionary<string, object> leakCheck = [];
        Dictionary<string, object> recipe = [];
        Dictionary<string, object> aoValue = [];


        foreach (var item in data)
        {
            if (item.Value is not IDictionary<string, object> values)
            {
                switch (item.Key)
                {
                    case string s when s.EndsWith("Enable"):
                        continue;
                    case string s when s.startsWith("LeakCheck"):
                        leakCheck.Add(item.Key, item.Value);
                        continue;
                    default:
                        recipe.Add(item.Key, item.Value);
                        continue;
                }
            }

            switch (item.Key)
            {
                case "APC":
                case "APCVATGV":
                case "BoatElevatorServo":
                case "BoatRotationServo":
                case "BufferServo":
                case "Shutter":
                    CreateTable(item.Key, values);
                    InsertData(item.Key, time, guid, values);
                    continue;

                case string s when s.startsWith("Trig"):
                    if (item.Value is IDictionary<string, object> value)
                        aoValue.Add(item.Key, value["AOValue"]);
                    continue;

                case string s when s.startsWith("FS"):
                case string s1 when s1.startsWith("PG"):
                case string s2 when s2.startsWith("PS"):
                case string s3 when s3.startsWith("VG"):
                    if (item.Value is IDictionary<string, object> vss)
                        ValueSensor.Add(item.Key, vss["Value"]);
                    continue;

                case string s when s.startsWith("ValveAV"):
                    _orm.Insert<AvValue>("AvValue", CreateData<AvValue>(guid, time, item));
                    continue;

                case string s when s.startsWith("Sensor"):
                    if (item.Value is IDictionary<string, object> status)
                        StatusSensor.Add(item.Key, status["Value"]);
                    continue;

                case string s when s.startsWith("FFU"):
                    _orm.Insert<FFU>("FFU", CreateData<FFU>(guid, time, item));
                    continue;

                case string s when s.startsWith("GaslineHeater"):
                    _orm.Insert<GaslineHeater>("GaslineHeater", CreateData<GaslineHeater>(guid, time, item));
                    continue;

                case string s when s.startsWith("MFC"):
                    _orm.Insert<MFC>("MFC", CreateData<MFC>(guid, time, item));
                    continue;
                default:
                    continue;
            }
        }

        CreateTable("ValueSensor", ValueSensor);
        CreateTable("StatusSensor", StatusSensor);
        CreateTable("Recipe", recipe);
        CreateTable("LeakCheck", leakCheck);
        CreateTable("AoValue", aoValue);

        InsertData("ValueSensor", time, guid, ValueSensor);
        InsertData("StatusSensor", time, guid, StatusSensor);
        InsertData("Recipe", time, guid, recipe);
        InsertData("LeakCheck", time, guid, leakCheck);
        InsertData("AoValue", time, guid, aoValue);
    }

    public void TestSystem()
    {
        SqlSugarCustom orm = new();

        orm.Initialize();
        string dbString = "Database=tin01_db;Password=123456;Host=10.4.6.48;Username=postgres;Persist Security Info=True";

        if (!orm.Open(dbString, SqlSugar.DbType.PostgreSQL, true))
            return;

        dynamic t = orm.Client!.Queryable<dynamic>().AS("\"20250708.System\"").First();
        GeneralProcessData processData = new(0);
        if (!processData.ToDictionary(t, out Dictionary<string, object>? outputs) || outputs is null)
            return;

        DateTime time = LongToDateTime(outputs["time"]).Value;
        Guid guid = Guid.NewGuid();

        Dictionary<string, object> systemCollection = [];
        Dictionary<string, object> alarmCollection = [];

        _orm.CreateTable<DB_Proxima.Heater>("Heater");
        _orm.CreateTable<Stocker>("Stocker");
        _orm.CreateTable<LoadPort>("LoadPort");
        _orm.CreateTable<FIMS>("FIMS");


        foreach (var item in outputs)
        {
            if (item.Value is not IDictionary<string, object> values)
                continue;

            switch (item.Key)
            {
                case "Boat":
                case "CarrierRobot":
                case "Scheduler":
                case "WaferRobot":
                    CreateTable(item.Key, values);
                    InsertData(item.Key, time, guid, values);
                    continue;

                case "System":
                    if (values is not IDictionary<string, object> systems)
                        continue;
                    foreach (var system in systems)
                    {
                        switch (system.Key)
                        {
                            case string s when s.startsWith("Heater"):
                                _orm.Insert<DB_Proxima.Heater>("Heater", CreateData<DB_Proxima.Heater>(guid, time, item));
                                continue;
                            case string s when s.startsWith("AlarmSignalHeater"):
                                alarmCollection.Add(system.Key, ((IDictionary<string, object>)system.Value)["Value"] ??= false);
                                continue;
                            default:
                                systemCollection.Add(system.Key, system.Value);
                                break;
                        }
                    }
                    continue;
                case string s when s.startsWith("Stocker"):
                    _orm.Insert<Stocker>("Stocker", CreateData<Stocker>(guid, time, item));
                    continue;
                case string s when s.startsWith("LP"):
                    continue;
                case string s when s.startsWith("FIMS"):
                    continue;

                default:
                    break;
            }
        }


        CreateTable("System", systemCollection);
        CreateTable("AlarmSignalHeater", alarmCollection);

        InsertData("System", time, guid, systemCollection);
        InsertData("AlarmSignalHeater", time, guid, alarmCollection);
    }


    public static T? CreateData<T>(Guid guid, DateTime time, KeyValuePair<string, object> input) where T : class, new()
    {
        if (input.Value is not IDictionary<string, object> values)
            return null;

        T source = new();
        if (source is not IBasicInfo basicInfo)
            return null;

        basicInfo.UID = guid;
        basicInfo.Time = time;
        basicInfo.Name = input.Key;

        DicToClass(ref source, values);
        return source;
    }


    public static DateTime? LongToDateTime(object o)
    {
        if (o is not long l)
            return null;
        return new(l);
    }

    public static void DicToClass<T>(ref T input, IDictionary<string, object> source)
    {
        foreach (PropertyInfo property in input.GetType().GetProperties())
        {
            if (!source.TryGetValue(property.Name, out object value) || value is null)
                continue;
            property.SetValue(input, value);
        }
    }

    public void CreateTable(string tableName, IDictionary<string, object> source)
    {
        DynamicProperyBuilder builder = _orm.Client.DynamicBuilder().CreateClass(tableName, new SugarTable());
        builder.CreateProperty("UID", typeof(Guid), new SugarColumn() { IsPrimaryKey = true });
        builder.CreateProperty("time", typeof(DateTime), new SugarColumn() { });
        foreach (var item in source)
            builder.CreateProperty(item.Key, item.Value.GetType(), new SugarColumn() { IsNullable = true });
        var type = builder.BuilderType();
        _orm.Client.CodeFirst.InitTables(type);
    }

    public int InsertData(string tableName, DateTime dateTime, Guid guid, IDictionary<string, object> source)
    {
        var dc = new Dictionary<string, object>();
        dc.Add("UID", guid);
        dc.Add("time", dateTime);
        foreach (var item in source)
            dc.Add(item.Key, item.Value);

        return _orm.Client.Insertable(dc).AS(tableName).ExecuteCommand();
    }

}