MES-Toolkit/Chenfeng.MES.Archiver/Commands/MysqlCommand.cs

using MySqlConnector;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using CliFx;
using CliFx.Infrastructure;
using CliFx.Attributes;
using System.Data;
using System.Data.Common;

namespace Chenfeng.MES.Archiver.Commands
{
    [Command()]
    public class MysqlCommand : ICommand
    {
        [CommandParameter(0, Description = "数据库连接")]
        public string Connection { get; set; } = "";

        public string TableSuffix { get; set; } = "new";

        public DbConnection? Db { get; private set; }

        public Action<string> Print { get; set; } = (text) => { };

        [CommandOption("legacy", Description = "旧版MES")]
        public bool Legacy { get; set; } = false;

        [CommandOption("replace", Description = "替换表")]
        public bool Replace { get; set; } = false;

        [CommandOption("restore", Description = "回复表")]
        public bool Restore { get; set; } = false;

        [CommandOption("rm-backup", Description = "删除备份表")]
        public bool RemoveBackup { get; set; }

        [CommandOption("timeout", Description = "sql命令超时时间")]
        public int Timeout { get; set; } = 3;

       

        public ValueTask ExecuteAsync(IConsole console)
        {
            Print = (text) => console.Output.WriteLine($"{DateTime.Now.ToLongTimeString()} {text}");

            var connBuilder = new MySqlConnectionStringBuilder(this.Connection);
            connBuilder.SslMode = MySqlSslMode.None;
            connBuilder.CharacterSet = "utf8";

            var starMonth = DateTime.Now.AddMonths(-12).ToString("yyyyMM");

            SqlMapper.Settings.CommandTimeout = 60 * Timeout;

            if (RemoveBackup)
            {
                console.Output.WriteLine("删除备份表");
            }
            else if (Restore)
            {
                console.Output.WriteLine("还原备份表");
            }
            else
            {
                console.Output.WriteLine($"拷贝"+( Legacy ? "旧" :"新")+"版表数据"+(Replace?"并替换":""));
            }

            try
            {
                Db = new MySqlConnection(connBuilder.ConnectionString);

                Db.Open();
                if (Legacy)
                {
                    CopyMesLegacyTables(starMonth);
                }
                else
                {
                    CopyMesTables(starMonth);
                }


                Print("操作成功");

            }
            catch (Exception ex)
            {
                console.Output.WriteLine(ex.ToString());
            }
            finally
            {
                Db?.Close();
            }

            return default;
        }

        private void CopyMesTables(string starMonth)
        {
            CopyStep((table, newTable) =>
            {
                CopyData(table, newTable, "LEFT(orderno,6) >= @starMonth", new { starMonth });
            }, "order", "order_box_block", "order_data_block", "order_data_goods", "order_data_parts", "order_item", "order_module_extra", "order_module_item", "order_package");


            CopyStep((tProcess, newProcess) =>
            {
                CopyData(tProcess, newProcess, "LEFT(orderno,6) >= @starMonth", new { starMonth });
                CopyStep((table, newTable) =>
                {
                    CopyChildData(table, newTable, newProcess, "parent.ID = child.OrderProcessID");
                }, "order_process_step_item", "order_process_step");
            }, "order_process");



            CopyStep((table, newTable) =>
            {
                CopyData(table, newTable, "LEFT(DATE_FORMAT(CreateTime,'%Y%m'),6) >= @starMonth", new { starMonth });
            }, "simple_plan_order");


            CopyStep((parent, newParent) =>
            {
                CopyData(parent, newParent, "LEFT(DATE_FORMAT(CreateTime,'%Y%m'),6) >= @starMonth", new { starMonth });
                CopyStep((child, newChild) =>
                {
                    CopyChildData(child, newChild, newParent, "parent.ID = child.ID");
                }, "order_block_plan_result");
            }, "order_block_plan");

            //CopyStep((table, newTable) =>
            //{
            //    CopyData(table, newTable, "concat('20',left(id,4)) >= @starMonth ", new { starMonth });
            //}, "order_scrap_board");
        }

        private void CopyMesLegacyTables(string starMonth)
        {
            CopyStep((order, newOrder) =>
            {
                CopyData(order, newOrder, "LEFT(DATE_FORMAT(SaleDate,'%Y%m'),6) >= @starMonth", new { starMonth });

                CopyStep((child, newChild) =>
                {
                    CopyChildData(child, newChild, newOrder, "parent.orderno=child.orderno");
                }, "SaleOrderItem", "saleblock", "saleobject", "saleorderobject", "saleorderoffer", "saleorderpackage", "saleblockobjids", "saleobjectobjids", "saleorder_block_cadmodelinfo", "saleorderblockbaseposition",
            "saleordergoodsinfo", "saleorder_block_point", "saleorder_block_pointinfo", "orderprocess", "orderprocessstep", "orderprocessstepitem");

            }, "saleorder");


            CopyStep((plan, newPlan) =>
            {
                CopyData(plan, newPlan, "LEFT(DATE_FORMAT(createtime,'%Y%m'),6) >= @starMonth", new { starMonth });

                CopyStep((child, newChild) =>
                {
                    CopyChildData(child, newChild, newPlan, "parent.id=child.id");
                }, "orderblockprocessplanplaceresult");
            }, "orderblockprocessplan");
        }

        private void CopyData(string table, string newTable, string where, object param)
        {
            Db.Execute($"INSERT INTO `{newTable}` SELECT * FROM `{table}` WHERE {where}", param);
        }
        private void CopyChildData(string table, string newTable, string parentTable, string condition)
        {
            Db.Execute($"INSERT INTO `{newTable}`  SELECT child.* FROM `{parentTable}` parent join `{table}` child on {condition} ");
        }

        private void CopyStep(Action<string, string> step, params string[] tables)
        {
            var list = tables.Select(table => new
            {
                Table = table,
                NewTable = table + "_" + this.TableSuffix
            });

            foreach (var item in list)
            {
                if (RemoveBackup)
                {
                    Db.Execute($"DROP TABLE IF EXISTS `{item.Table}_bak`");
                }
                else if (Restore)
                {
                    Db.Execute($"DROP TABLE IF EXISTS `{item.Table}`");
                    Db.Execute($"RENAME TABLE `{item.Table}_bak` TO `{item.Table}`");
                }
                else
                {
                    Db.Execute($"DROP TABLE IF EXISTS `{item.NewTable}`");
                    Db.Execute($"CREATE TABLE `{item.NewTable}` LIKE `{item.Table}`"); // IF NOT EXISTS

                    Print(item.NewTable + " 创建完成");
                    step(item.Table, item.NewTable);
                    Print(item.NewTable + " 拷贝完成");

                    if (Replace)
                    {
                        Db.Execute($"RENAME TABLE `{item.Table}` TO `{item.Table}_bak`");
                        Db.Execute($"RENAME TABLE `{item.NewTable}` TO `{item.Table}`");
                    }
                }
            }

        }
    }
}