kxs-sp/AppStart/Helper/AddTableService.cs

using System;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using System.Linq;
using Microsoft.Extensions.Hosting;
using MySystem;
using MySystem.Models;
using Library;
using LitJson;

public class AddTableService
{
    public readonly static AddTableService Instance = new AddTableService();
    private AddTableService()
    { }

    //自动创建表
    public void CreateTable(string data)
    {
        JsonData jsonObj = JsonMapper.ToObject(data);
        string tbname = jsonObj["TableName"].ToString();
        int tbType = int.Parse(jsonObj["TableType"].ToString());
        WebCMSEntities db = new WebCMSEntities();
        CustomerTableRecord check = db.CustomerTableRecord.FirstOrDefault(m => m.TableEnName == tbname);
        if (check != null)
        {
            //创建主表
            if(tbType == 1) CreateActiveTableForMonth(tbname);
            if(tbType == 2) CreateTradeTableForMonth(tbname);
            if(tbType == 3) CreateTradeFluxTableForMonth(tbname);
            db.CustomerTableRecord.Add(new CustomerTableRecord()
            {
                TableEnName = tbname,
                CreateDate = DateTime.Now,
                AddTableDate = DateTime.Now,
            });
            db.SaveChanges();
        }
        else
        {
            //从主表中分离3个月前的数据，每个月创建一张表
            DateTime AddTableDate = check.AddTableDate.Value.AddMonths(-3);
            if (AddTableDate > check.CreateDate)
            {
                string addTableName = tbname + AddTableDate.ToString("yyMM");
                bool checkAdd = db.CustomerTableRecord.Any(m => m.TableEnName == addTableName);
                if (!checkAdd)
                { 
                    if(tbType == 1) CreateActiveTableForMonth(addTableName);
                    if(tbType == 2) CreateTradeTableForMonth(addTableName);
                    if(tbType == 3) CreateTradeFluxTableForMonth(addTableName);
                    db.CustomerTableRecord.Add(new CustomerTableRecord()
                    {
                        TableEnName = addTableName,
                        CreateDate = DateTime.Now
                    });
                    db.SaveChanges();
                }
            }
        }
        db.Dispose();
    }

    //创建激活记录表
    private void CreateActiveTableForMonth(string tbname)
    { 
        string addTableSql = "CREATE table " + tbname + "(Id int NOT NULL AUTO_INCREMENT,Sort int not null default 0,QueryCount int not null default 0,Status int not null default 0,CreateDate datetime,UpdateDate datetime,SeoTitle varchar(100),SeoKeyword varchar(200),SeoDescription varchar(500),PRIMARY KEY(Id))ENGINE=InnoDB DEFAULT charset=utf8;";
        addTableSql += "Alter table " + tbname + " add BizEnterName varchar(32);";
        addTableSql += "Alter table " + tbname + " add BizEnterNo varchar(16);";
        addTableSql += "Alter table " + tbname + " add MerIdcardNo varchar(18);";
        addTableSql += "Alter table " + tbname + " add MerRealName varchar(16);";
        addTableSql += "Alter table " + tbname + " add ActivateDate datetime;";
        addTableSql += "Alter table " + tbname + " add ActivateStatus varchar(2);";
        addTableSql += "Alter table " + tbname + " add AssessMonthCount varchar(8);";
        addTableSql += "Alter table " + tbname + " add AssessMonth varchar(8);";
        addTableSql += "Alter table " + tbname + " add MerRegDate datetime;";
        addTableSql += "Alter table " + tbname + " add SnNo varchar(32);";
        addTableSql += "Alter table " + tbname + " add BrandId int not null default 0;";
        addTableSql += "Alter table " + tbname + " add MerMobile varchar(32);";
        addTableSql += "Alter table " + tbname + " add MerName varchar(32);";
        dbconn.op(addTableSql);
    }

    //自动创建交易记录表
    public void CreateTradeTableForMonth(string tbname)
    { 
        string addTableSql = "CREATE table " + tbname + "(Id int NOT NULL AUTO_INCREMENT,Sort int not null default 0,QueryCount int not null default 0,Status int not null default 0,CreateDate datetime,UpdateDate datetime,SeoTitle varchar(100),SeoKeyword varchar(200),SeoDescription varchar(500),PRIMARY KEY(Id))ENGINE=InnoDB DEFAULT charset=utf8;";
        addTableSql += "Alter table " + tbname + " add ReceiptType varchar(16);";
        addTableSql += "Alter table " + tbname + " add IsStoreCashier varchar(16);";
        addTableSql += "Alter table " + tbname + " add DigAmt numeric(18,2) not null default 0;";
        addTableSql += "Alter table " + tbname + " add DigAmtFlag varchar(4);";
        addTableSql += "Alter table " + tbname + " add MerMobile varchar(11);";
        addTableSql += "Alter table " + tbname + " add DeviceType varchar(16);";
        addTableSql += "Alter table " + tbname + " add TradeSnNo varchar(32);";
        addTableSql += "Alter table " + tbname + " add BankCardType varchar(16);";
        addTableSql += "Alter table " + tbname + " add SerEntryMode varchar(32);";
        addTableSql += "Alter table " + tbname + " add TradeType varchar(32);";
        addTableSql += "Alter table " + tbname + " add TradeTime varchar(8);";
        addTableSql += "Alter table " + tbname + " add TradeDate varchar(8);";
        addTableSql += "Alter table " + tbname + " add ErrorMsg varchar(32);";
        addTableSql += "Alter table " + tbname + " add ErrorCode varchar(16);";
        addTableSql += "Alter table " + tbname + " add Trade_status varchar(16);";
        addTableSql += "Alter table " + tbname + " add BrandId int not null default 0;";
        addTableSql += "Alter table " + tbname + " add DiscountRateFlag varchar(16);";
        addTableSql += "Alter table " + tbname + " add BankAuthCode varchar(32);";
        addTableSql += "Alter table " + tbname + " add TradeReferNo varchar(32);";
        addTableSql += "Alter table " + tbname + " add TradeAmount numeric(18,2) not null default 0;";
        addTableSql += "Alter table " + tbname + " add MerName varchar(32);";
        dbconn.op(addTableSql);
    }

    //自动创建流量卡分佣交易记录表
    public void CreateTradeFluxTableForMonth(string tbname)
    { 
        string addTableSql = "CREATE table " + tbname + "(Id int NOT NULL AUTO_INCREMENT,Sort int not null default 0,QueryCount int not null default 0,Status int not null default 0,CreateDate datetime,UpdateDate datetime,SeoTitle varchar(100),SeoKeyword varchar(200),SeoDescription varchar(500),PRIMARY KEY(Id))ENGINE=InnoDB DEFAULT charset=utf8;";
        addTableSql += "Alter table " + tbname + " add DeviceNetwork varchar(32);";
        addTableSql += "Alter table " + tbname + " add TerminalNo varchar(32);";
        addTableSql += "Alter table " + tbname + " add InputModel varchar(32);";
        addTableSql += "Alter table " + tbname + " add KqTradeSeq varchar(32);";
        addTableSql += "Alter table " + tbname + " add BillType varchar(32);";
        addTableSql += "Alter table " + tbname + " add SourceType varchar(16);";
        addTableSql += "Alter table " + tbname + " add BizEnterName varchar(64);";
        addTableSql += "Alter table " + tbname + " add BizEnterNo varchar(32);";
        addTableSql += "Alter table " + tbname + " add SmallDouble varchar(4);";
        addTableSql += "Alter table " + tbname + " add SettleFee numeric(18,2) not null default 0;";
        addTableSql += "Alter table " + tbname + " add SettleMethod varchar(16);";
        addTableSql += "Alter table " + tbname + " add FeeAmount numeric(18,2) not null default 0;";
        addTableSql += "Alter table " + tbname + " add BankCardNo varchar(32);";
        addTableSql += "Alter table " + tbname + " add DigAmt numeric(18,2) not null default 0;";
        addTableSql += "Alter table " + tbname + " add DigAmtFlag varchar(4);";
        addTableSql += "Alter table " + tbname + " add TradeSnNo varchar(32);";
        addTableSql += "Alter table " + tbname + " add BankCardType varchar(16);";
        addTableSql += "Alter table " + tbname + " add TradeTime varchar(8);";
        addTableSql += "Alter table " + tbname + " add TradeDate varchar(8);";
        addTableSql += "Alter table " + tbname + " add TradeStatus varchar(16);";
        addTableSql += "Alter table " + tbname + " add BrandId int not null default 0;";
        addTableSql += "Alter table " + tbname + " add TradeAmount numeric(18,2) not null default 0;";
        addTableSql += "Alter table " + tbname + " add MerName varchar(32);";
        dbconn.op(addTableSql);
    }
}