kxs-admin/AppStart/ExcelHelper.cs

using System;
using System.Threading;
using System.Linq;
using System.Data;
using Library;
using MySystem.Models;
using System.Collections.Generic;

namespace MySystem
{
    public class ExcelHelper
    {
        public readonly static ExcelHelper Instance = new ExcelHelper();
        private ExcelHelper()
        {
        }

        public void Start()//启动
        {
            Thread thread = new Thread(ImportPostDo);
            thread.IsBackground = true;
            thread.Start();
        }

        public void ImportPostDo()
        {
            while (true)
            {
                string data = RedisDbconn.Instance.RPop<string>("ExcelImportV2");
                // string data = RedisDbconn.Instance.RPop<string>("ExcelImportV3");
                if (!string.IsNullOrEmpty(data))
                {
                    string[] dataList = data.Split("#cut#");
                    string _ExcelPath = dataList[0];
                    string _Kind = dataList[1];
                    string checkKey = dataList[2];
                    string Operator = dataList[3]; // 操作人
                    int SuccessCount = 0;
                    int DoCount = 0;
                    string FullExcelPath = function.getPath(_ExcelPath);
                    FullExcelPath = FullExcelPath.Replace("//", "/");
                    DataTable list = new PublicFunction().ExcelToDataTable(FullExcelPath);
                    int TotalCount = list.Rows.Count;
                    while (DoCount < list.Rows.Count)
                    {
                        WebCMSEntities db = new WebCMSEntities();
                        if (_Kind == "1")
                        {
                            var tran = db.Database.BeginTransaction();
                            try
                            {
                                int Size = 100;
                                if (list.Rows.Count - DoCount < 100)
                                {
                                    Size = list.Rows.Count - DoCount;
                                }
                                Dictionary<string, int> storeData = new Dictionary<string, int>();
                                for (int i = DoCount; i < DoCount + Size; i++)
                                {
                                    DataRow dr = list.Rows[i];
                                    string BatchNo = dr[0].ToString(); // 业务批次号
                                    string SnNo = dr[1].ToString(); // SN编号
                                    SnNo = SnNo.Replace(" ", "");
                                    string DeviceName = dr[2].ToString(); // 设备厂商
                                    string DeviceKind = dr[3].ToString(); // 设备型号
                                    string DeviceType = dr[4].ToString(); // 设备类型区分(客小爽电签POS填写：KysSignPos/KssSignPos)
                                    string StoreNo = dr[5].ToString(); // 收货仓库编号
                                    string BrandIdString = dr[6].ToString(); // 产品类型(5-客小爽Mpos,6-客小爽大POS,61-客小爽电签POS,8-客小爽超级Mpos)
                                    string InputMan = dr[7].ToString(); // 入库操作人
                                    string InputNote = dr[8].ToString(); // 入库备注
                                    MachineForSnNo machinefor = db.MachineForSnNo.FirstOrDefault(m => m.SnNo == SnNo);
                                    if (machinefor == null)
                                    {
                                        int BrandId = int.Parse(function.CheckInt(BrandIdString));
                                        KqProducts kqProduct = db.KqProducts.FirstOrDefault(m => m.Id == BrandId) ?? new KqProducts();
                                        if (kqProduct.Id > 0)
                                        {
                                            StoreForCode storefor = db.StoreForCode.FirstOrDefault(m => m.Code == StoreNo) ?? new StoreForCode();
                                            StoreHouse store = db.StoreHouse.FirstOrDefault(m => m.Id == storefor.StoreId) ?? new StoreHouse();
                                            PosMachinesTwo pos = db.PosMachinesTwo.Add(new PosMachinesTwo()
                                            {
                                                CreateDate = DateTime.Now,
                                                PosSn = SnNo, //SN编号
                                                BrandId = BrandId, //产品类型
                                                StoreId = storefor.StoreId, //所在仓库
                                                DeviceType = DeviceType, //设备类型
                                                BatchNo = BatchNo,
                                                DeviceName = DeviceName,
                                                DeviceKind = DeviceKind,
                                                SourceStoreId = storefor.StoreId, //源仓库
                                            }).Entity;
                                            store.ProductName = kqProduct.Name;
                                            store.BrandId = BrandId.ToString();

                                            StoreStockChange stockchange = db.StoreStockChange.Add(new StoreStockChange()
                                            {
                                                CreateDate = DateTime.Now,
                                                CreateMan = Operator,
                                                StoreId = storefor.StoreId, //仓库
                                                BrandId = BrandId, //产品类型
                                                ProductName = RelationClass.GetKqProductBrandInfo(BrandId), //产品名称
                                                BizBatchNo = BatchNo, //业务批次号
                                                TransType = 0, //交易类型
                                                SnNo = SnNo, //SN编号
                                                StockOpDirect = 0, //库存操作方向
                                                SnStatus = 0, //SN状态
                                                DeviceVendor = DeviceName, //设备厂商
                                                DeviceModel = DeviceKind, //设备型号
                                                DeviceType = DeviceType, //设备类型
                                                ToDate = DateTime.Now, //入库时间
                                                ToRemark = InputNote, //入库备注
                                                BrandType = DeviceType, //品牌类型
                                                SourceStoreId = storefor.StoreId, //源仓库
                                            }).Entity;
                                            db.SaveChanges();
                                            string ChangeNo = "SC";
                                            int StoreStockChangeId = stockchange.Id;
                                            string StoreStockChangeIdString = StoreStockChangeId.ToString();
                                            for (int j = 0; j < 18 - StoreStockChangeId.ToString().Length; j++)
                                            {
                                                StoreStockChangeIdString = "0" + StoreStockChangeIdString;
                                            }
                                            ChangeNo += StoreStockChangeIdString;
                                            stockchange.ChangeNo = ChangeNo; //交易流水编号

                                            db.StoreChangeHistory.Add(new StoreChangeHistory()
                                            {
                                                CreateDate = DateTime.Now,
                                                CreateMan = Operator,
                                                UserId = store.UserId, //创客
                                                BrandId = BrandId, //产品类型
                                                ChangeRecordNo = ChangeNo, //变更记录单号
                                                BizBatchNo = BatchNo, //业务批次号
                                                TransType = 0, //交易类型
                                                SnNo = SnNo, //SN编号
                                                StockOpDirect = 0, //库存操作方向
                                                DeviceVendor = DeviceName, //设备厂商
                                                DeviceType = DeviceType, //设备类型
                                                DeviceModel = DeviceKind, //设备型号
                                                ToUserId = store.UserId, //收货创客
                                                ToStoreId = store.Id, //创客退货收货仓库
                                                ToDate = DateTime.Now, //入库时间
                                                ToRemark = InputNote, //入库备注
                                                SourceStoreId = storefor.StoreId, //源仓库
                                                StoreId = storefor.StoreId, //仓库
                                            });
                                            machinefor = db.MachineForSnNo.Add(new MachineForSnNo()
                                            {
                                                SnNo = SnNo,
                                                SnId = pos.Id
                                            }).Entity;
                                            RedisDbconn.Instance.Set("MachineForSnNo:" + SnNo, machinefor);
                                            db.SaveChanges();

                                            RedisDbconn.Instance.AddList("PosMachineList:" + storefor.StoreId, pos);

                                            if (!storeData.ContainsKey(storefor.StoreId + "_" + BrandId))
                                            {
                                                storeData.Add(storefor.StoreId + "_" + BrandId, 1);
                                            }
                                            else
                                            {
                                                storeData[storefor.StoreId + "_" + BrandId] += 1;
                                            }

                                            SuccessCount += 1;
                                            RedisDbconn.Instance.Set("CheckImport:" + checkKey, SuccessCount + " / " + TotalCount);
                                        }
                                        else
                                        {
                                            RedisDbconn.Instance.AddList("ErrList" + checkKey, "机具" + SnNo + "的品牌填写有误");
                                        }
                                    }
                                    else
                                    {
                                        RedisDbconn.Instance.AddList("ErrList" + checkKey, "机具" + SnNo + "已存在，请勿重复录入");
                                    }
                                }
                                DoCount += Size;
                                foreach (string key in storeData.Keys)
                                {
                                    string[] keylist = key.Split('_');
                                    int StoreId = int.Parse(keylist[0]);
                                    int BrandId = int.Parse(keylist[1]);
                                    int OpStoreNum = storeData[key];
                                    StoreHouse store = db.StoreHouse.FirstOrDefault(m => m.Id == StoreId) ?? new StoreHouse();
                                    StoreBalance balance = db.StoreBalance.Add(new StoreBalance()
                                    {
                                        CreateDate = DateTime.Now,
                                        CreateMan = Operator,
                                        StoreId = StoreId, //仓库
                                        TransType = 0, //交易类型
                                        BrandId = BrandId, //产品类型
                                        OpStoreNum = OpStoreNum, //操作库存数
                                        OpSymbol = "+", //操作符
                                        BeforeTotalNum = store.TotalNum, //操作前总库存数
                                        AfterTotalNum = store.TotalNum + OpStoreNum, //操作后总库存数
                                        BeforeLaveNum = store.LaveNum, //操作前剩余库存数
                                        AfterLaveNum = store.LaveNum + OpStoreNum, //操作后剩余库存数
                                        BeforeOutNum = store.OutNum, //操作前出库数
                                        AfterOutNum = store.OutNum, //操作后出库数
                                    }).Entity;
                                    db.SaveChanges();
                                    string ChangeNo = "SC";
                                    int StoreStockChangeId = balance.Id;
                                    string StoreStockChangeIdString = StoreStockChangeId.ToString();
                                    for (int i = 0; i < 18 - StoreStockChangeId.ToString().Length; i++)
                                    {
                                        StoreStockChangeIdString = "0" + StoreStockChangeIdString;
                                    }
                                    ChangeNo += StoreStockChangeIdString;
                                    balance.TransRecordNo = ChangeNo; //交易流水编号
                                    store.TotalNum += OpStoreNum;
                                    store.LaveNum += OpStoreNum;
                                    db.SaveChanges();
                                }
                                db.SaveChanges();

                                tran.Commit();
                                RedisDbconn.Instance.Set("CheckImport:" + checkKey, "success|" + SuccessCount);
                                RedisDbconn.Instance.SetExpire("CheckImport:" + checkKey, 60000);
                            }
                            catch (Exception ex)
                            {
                                DoCount = list.Rows.Count;
                                function.WriteLog(ex.ToString(), "机具导入异常");
                                tran.Rollback();
                                ErrorMsg msg = new ErrorMsg()
                                {
                                    Time = DateTime.Now,
                                    ErrorContent = ex.ToString(),
                                };
                                function.WriteLog(Newtonsoft.Json.JsonConvert.SerializeObject(msg), "导入Excel文件异常");
                            }
                            tran.Dispose();
                        }
                        else if (_Kind == "2")
                        {
                            Dictionary<string, int> storeData = new Dictionary<string, int>();
                            Dictionary<string, int> toStoreData = new Dictionary<string, int>();
                            using (var tran = db.Database.BeginTransaction())
                            {
                                try
                                {
                                    int Size = 100;
                                    if (list.Rows.Count - DoCount < 100)
                                    {
                                        Size = list.Rows.Count - DoCount;
                                    }
                                    for (int i = DoCount; i < DoCount + Size; i++)
                                    {
                                        DataRow dr = list.Rows[i];
                                        string BatchNo = dr[0].ToString(); // 业务批次号
                                        string OutStoreNo = dr[1].ToString(); // 出货仓库编号
                                        string OutOperator = dr[2].ToString(); // 出库操作人
                                        string SnNo = dr[3].ToString(); // SN编号
                                        SnNo = SnNo.Replace(" ", "");
                                        string StoreNo = dr[4].ToString(); // 收货仓库编号
                                        string BrandIdString = dr[5].ToString(); // 产品类型
                                        string OutNote = "";
                                        if (list.Columns.Count > 6)
                                        {
                                            OutNote = dr[6].ToString(); // 出库备注
                                        }

                                        int BrandId = int.Parse(function.CheckInt(BrandIdString));

                                        //调拨时若出货仓库与填写仓库不符，则该条出货失败
                                        var posinfo = db.PosMachinesTwo.FirstOrDefault(m => m.PosSn == SnNo && m.BrandId == BrandId) ?? new PosMachinesTwo();
                                        var storeinfo = db.StoreForCode.FirstOrDefault(m => m.Code == OutStoreNo && m.StoreId == posinfo.StoreId) ?? new StoreForCode();
                                        if (posinfo.Id > 0)
                                        {
                                            if (storeinfo.StoreId > 0)
                                            {
                                                KqProducts kqProduct = db.KqProducts.FirstOrDefault(m => m.Id == BrandId) ?? new KqProducts();
                                                if (kqProduct.Id > 0)
                                                {
                                                    StoreForCode storefor = db.StoreForCode.FirstOrDefault(m => m.Code == OutStoreNo) ?? new StoreForCode();
                                                    StoreHouse store = db.StoreHouse.FirstOrDefault(m => m.Id == storefor.StoreId) ?? new StoreHouse();
                                                    if (store.LaveNum > 0)
                                                    {
                                                        StoreForCode tostorefor = db.StoreForCode.FirstOrDefault(m => m.Code == StoreNo) ?? new StoreForCode();
                                                        StoreHouse tostore = db.StoreHouse.FirstOrDefault(m => m.Id == tostorefor.StoreId) ?? new StoreHouse();
                                                        if (tostore.Id > 0)
                                                        {
                                                            MachineForSnNo machinefor = db.MachineForSnNo.FirstOrDefault(m => m.SnNo == SnNo) ?? new MachineForSnNo();
                                                            PosMachinesTwo machine = db.PosMachinesTwo.FirstOrDefault(m => m.Id == machinefor.SnId && m.StoreId != tostore.Id && m.BuyUserId == 0 && m.UserId == 0) ?? new PosMachinesTwo();
                                                            if (machine.Id > 0)
                                                            {
                                                                if (machine.PreUserId == 0)
                                                                {
                                                                    StoreStockChange stockchange = db.StoreStockChange.Add(new StoreStockChange()
                                                                    {
                                                                        CreateDate = DateTime.Now,
                                                                        CreateMan = Operator,
                                                                        StoreId = storefor.StoreId, //仓库
                                                                        BrandId = BrandId, //产品类型
                                                                        ProductName = RelationClass.GetKqProductBrandInfo(BrandId), //产品名称
                                                                        BizBatchNo = BatchNo, //业务批次号
                                                                        TransType = 1, //交易类型
                                                                        SnNo = SnNo, //SN编号
                                                                        StockOpDirect = 1, //库存操作方向
                                                                        SnStatus = 1, //SN状态
                                                                        DeviceVendor = machine.DeviceName, //设备厂商
                                                                        DeviceModel = machine.DeviceKind, //设备型号
                                                                        DeviceType = machine.DeviceType, //设备类型
                                                                        FromUserId = store.UserId, //出货人
                                                                        FromDate = DateTime.Now, //出库时间
                                                                        FromRemark = OutNote, //出库备注
                                                                        ToUserId = tostore.UserId, //收货人
                                                                        ToStoreId = tostorefor.StoreId, //退货收货仓库                        
                                                                        SourceStoreId = machine.SourceStoreId, //源仓库编号
                                                                        BrandType = machine.DeviceType, //品牌类型
                                                                    }).Entity;
                                                                    db.SaveChanges();
                                                                    string ChangeNo = "SC";
                                                                    int StoreStockChangeId = stockchange.Id;
                                                                    string StoreStockChangeIdString = StoreStockChangeId.ToString();
                                                                    for (int j = 0; j < 18 - StoreStockChangeId.ToString().Length; j++)
                                                                    {
                                                                        StoreStockChangeIdString = "0" + StoreStockChangeIdString;
                                                                    }
                                                                    ChangeNo += StoreStockChangeIdString;
                                                                    stockchange.ChangeNo = ChangeNo; //交易流水编号
                                                                    machine.StoreId = tostorefor.StoreId;

                                                                    db.StoreChangeHistory.Add(new StoreChangeHistory()
                                                                    {
                                                                        CreateDate = DateTime.Now,
                                                                        CreateMan = Operator,
                                                                        UserId = store.UserId, //创客
                                                                        ToUserId = tostore.UserId, //收货创客
                                                                        BrandId = BrandId, //产品类型
                                                                        ChangeRecordNo = ChangeNo, //变更记录单号
                                                                        BizBatchNo = BatchNo, //业务批次号
                                                                        TransType = 1, //交易类型
                                                                        SnNo = SnNo, //SN编号
                                                                        SnType = machine.PosSnType, //SN机具类型
                                                                        StockOpDirect = 1, //库存操作方向
                                                                        DeviceVendor = machine.DeviceName, //设备厂商
                                                                        DeviceModel = machine.DeviceKind, //设备型号
                                                                        DeviceType = machine.DeviceType, //设备类型
                                                                        FromUserId = store.UserId, //出货创客
                                                                        FromDate = DateTime.Now, //出库时间
                                                                        FromRemark = OutNote, //出库备注
                                                                        SourceStoreId = machine.SourceStoreId, //源仓库
                                                                        ToStoreId = tostore.Id, //收货仓库
                                                                        StoreId = store.Id, //仓库
                                                                    });

                                                                    // RemoveClass.DeletePosMachineList("PosMachineTwoList:" + storefor.StoreId, machine.Id);
                                                                    // RedisDbconn.Instance.AddList("PosMachineTwoList:" + tostorefor.StoreId, machine);

                                                                    if (!storeData.ContainsKey(storefor.StoreId + "_" + BrandId))
                                                                    {
                                                                        storeData.Add(storefor.StoreId + "_" + BrandId, 1);
                                                                    }
                                                                    else
                                                                    {
                                                                        storeData[storefor.StoreId + "_" + BrandId] += 1;
                                                                    }
                                                                    if (!toStoreData.ContainsKey(tostorefor.StoreId + "_" + BrandId))
                                                                    {
                                                                        toStoreData.Add(tostorefor.StoreId + "_" + BrandId, 1);
                                                                    }
                                                                    else
                                                                    {
                                                                        toStoreData[tostorefor.StoreId + "_" + BrandId] += 1;
                                                                    }
                                                                    SuccessCount += 1;
                                                                    RedisDbconn.Instance.Set("CheckImport:" + checkKey, SuccessCount + " / " + TotalCount);

                                                                }
                                                                else
                                                                {
                                                                    RedisDbconn.Instance.AddList("ErrList" + checkKey, "机具" + SnNo + "为预发机，不可调拨");
                                                                }
                                                            }
                                                            else
                                                            {
                                                                RedisDbconn.Instance.AddList("ErrList" + checkKey, "机具" + SnNo + "不是仓库机或已经在填写的收货仓库中");
                                                            }
                                                            if (string.IsNullOrEmpty(tostore.ProductName))
                                                            {
                                                                tostore.BrandId = BrandId.ToString();
                                                                tostore.ProductName = kqProduct.Name;
                                                            }
                                                        }
                                                        else
                                                        {
                                                            RedisDbconn.Instance.AddList("ErrList" + checkKey, "机具" + SnNo + "填写的收货仓库编号不正确");
                                                        }
                                                    }
                                                    else
                                                    {
                                                        RedisDbconn.Instance.AddList("ErrList" + checkKey, "机具" + SnNo + "所在的仓库" + store.StoreName + "(" + store.StoreNo + ")库存不足");
                                                    }
                                                    db.SaveChanges();
                                                }
                                                else
                                                {
                                                    RedisDbconn.Instance.AddList("ErrList" + checkKey, "机具" + SnNo + "的品牌填写有误");
                                                }
                                            }
                                            else
                                            {
                                                RedisDbconn.Instance.AddList("ErrList" + checkKey, "机具" + SnNo + "出货仓库与填写仓库不符");
                                            }
                                        }
                                        else
                                        {
                                            RedisDbconn.Instance.AddList("ErrList" + checkKey, "机具" + SnNo + "的品牌填写有误");
                                        }
                                    }
                                    DoCount += Size;
                                    foreach (string key in storeData.Keys)
                                    {
                                        string[] keylist = key.Split('_');
                                        int StoreId = int.Parse(keylist[0]);
                                        int BrandId = int.Parse(keylist[1]);
                                        int OpStoreNum = storeData[key];
                                        StoreHouse store = db.StoreHouse.FirstOrDefault(m => m.Id == StoreId) ?? new StoreHouse();
                                        StoreBalance balance = db.StoreBalance.Add(new StoreBalance()
                                        {
                                            CreateDate = DateTime.Now,
                                            CreateMan = Operator,
                                            StoreId = StoreId, //仓库
                                            TransType = 1, //交易类型
                                            BrandId = BrandId, //产品类型
                                            OpStoreNum = OpStoreNum, //操作库存数
                                            OpSymbol = "-", //操作符
                                            BeforeTotalNum = store.TotalNum, //操作前总库存数
                                            AfterTotalNum = store.TotalNum, //操作后总库存数
                                            BeforeLaveNum = store.LaveNum, //操作前剩余库存数
                                            AfterLaveNum = store.LaveNum - OpStoreNum, //操作后剩余库存数
                                            BeforeOutNum = store.OutNum, //操作前出库数
                                            AfterOutNum = store.OutNum + OpStoreNum, //操作后出库数
                                        }).Entity;
                                        db.SaveChanges();
                                        string ChangeNo = "SC";
                                        int StoreStockChangeId = balance.Id;
                                        string StoreStockChangeIdString = StoreStockChangeId.ToString();
                                        for (int i = 0; i < 18 - StoreStockChangeId.ToString().Length; i++)
                                        {
                                            StoreStockChangeIdString = "0" + StoreStockChangeIdString;
                                        }
                                        ChangeNo += StoreStockChangeIdString;
                                        balance.TransRecordNo = ChangeNo; //交易流水编号
                                        store.LaveNum -= OpStoreNum;
                                        store.OutNum += OpStoreNum;
                                        db.SaveChanges();
                                        string SendData = "{\"Kind\":\"5\",\"Data\":{\"StoreId\":\"" + StoreId + "\",\"BrandId\":\"" + BrandId + "\",\"OpStoreNum\":\"" + OpStoreNum + "\"}}";
                                        RedisDbconn.Instance.AddList("StoreApplyQueue", SendData);
                                    }
                                    foreach (string key in toStoreData.Keys)
                                    {
                                        string[] keylist = key.Split('_');
                                        int StoreId = int.Parse(keylist[0]);
                                        int BrandId = int.Parse(keylist[1]);
                                        int OpStoreNum = toStoreData[key];
                                        StoreHouse store = db.StoreHouse.FirstOrDefault(m => m.Id == StoreId) ?? new StoreHouse();
                                        StoreBalance balance = db.StoreBalance.Add(new StoreBalance()
                                        {
                                            CreateDate = DateTime.Now,
                                            CreateMan = Operator,
                                            StoreId = StoreId, //仓库
                                            TransType = 0, //交易类型
                                            BrandId = BrandId, //产品类型
                                            OpStoreNum = OpStoreNum, //操作库存数
                                            OpSymbol = "+", //操作符
                                            BeforeTotalNum = store.TotalNum, //操作前总库存数
                                            AfterTotalNum = store.TotalNum + OpStoreNum, //操作后总库存数
                                            BeforeLaveNum = store.LaveNum, //操作前剩余库存数
                                            AfterLaveNum = store.LaveNum + OpStoreNum, //操作后剩余库存数
                                            BeforeOutNum = store.OutNum, //操作前出库数
                                            AfterOutNum = store.OutNum, //操作后出库数
                                        }).Entity;
                                        db.SaveChanges();
                                        string ChangeNo = "SC";
                                        int StoreStockChangeId = balance.Id;
                                        string StoreStockChangeIdString = StoreStockChangeId.ToString();
                                        for (int i = 0; i < 18 - StoreStockChangeId.ToString().Length; i++)
                                        {
                                            StoreStockChangeIdString = "0" + StoreStockChangeIdString;
                                        }
                                        ChangeNo += StoreStockChangeIdString;
                                        balance.TransRecordNo = ChangeNo; //交易流水编号
                                        store.TotalNum += OpStoreNum;
                                        store.LaveNum += OpStoreNum;
                                        db.SaveChanges();
                                        string SendData = "{\"Kind\":\"5\",\"Data\":{\"StoreId\":\"" + StoreId + "\",\"BrandId\":\"" + BrandId + "\",\"OpStoreNum\":\"-" + OpStoreNum + "\"}}";
                                        RedisDbconn.Instance.AddList("StoreApplyQueue", SendData);
                                    }
                                    db.SaveChanges();
                                    tran.Commit();
                                    RedisDbconn.Instance.Set("CheckImport:" + checkKey, "success|" + SuccessCount);
                                    RedisDbconn.Instance.SetExpire("CheckImport:" + checkKey, 60000);
                                }
                                catch (Exception ex)
                                {
                                    DoCount = list.Rows.Count;
                                    tran.Rollback();
                                    ErrorMsg msg = new ErrorMsg()
                                    {
                                        Time = DateTime.Now,
                                        ErrorContent = ex.ToString(),
                                    };
                                    function.WriteLog(Newtonsoft.Json.JsonConvert.SerializeObject(msg), "导入Excel文件异常");
                                }
                            }
                        }
                        else if (_Kind == "3")
                        {
                            Dictionary<string, int> storeData = new Dictionary<string, int>();
                            using (var tran = db.Database.BeginTransaction())
                            {
                                try
                                {
                                    int Size = 100;
                                    if (list.Rows.Count - DoCount < 100)
                                    {
                                        Size = list.Rows.Count - DoCount;
                                    }
                                    for (int i = DoCount; i < DoCount + Size; i++)
                                    {
                                        DataRow dr = list.Rows[i];
                                        string BatchNo = dr[0].ToString(); // 业务批次号
                                        BatchNo = BatchNo.Replace(" ", "");
                                        string OutStoreNo = dr[1].ToString(); // 出货仓库编号
                                        OutStoreNo = OutStoreNo.Replace(" ", "");
                                        string OutOperator = dr[2].ToString(); // 出库操作人
                                        OutOperator = OutOperator.Replace(" ", "");
                                        string SnNo = dr[3].ToString(); // SN编号
                                        SnNo = SnNo.Replace(" ", "");
                                        string MakerCode = dr[4].ToString(); // 收货创客编号
                                        MakerCode = MakerCode.Replace(" ", "");
                                        string BrandIdString = dr[5].ToString(); // 产品类型(5-客小爽Mpos,6-客小爽大POS,61-客小爽电签POS,8-客小爽超级Mpos))
                                        BrandIdString = BrandIdString.Replace(" ", "");
                                        string OutNote = "";
                                        if (list.Columns.Count > 6)
                                        {
                                            OutNote = dr[6].ToString(); // 出库备注
                                        }

                                        int BrandId = int.Parse(function.CheckInt(BrandIdString));

                                        KqProducts kqProduct = db.KqProducts.FirstOrDefault(m => m.Id == BrandId) ?? new KqProducts();
                                        if (kqProduct.Id > 0)
                                        {
                                            StoreForCode storefor = db.StoreForCode.FirstOrDefault(m => m.Code == OutStoreNo) ?? new StoreForCode();
                                            StoreHouse store = db.StoreHouse.FirstOrDefault(m => m.Id == storefor.StoreId) ?? new StoreHouse();
                                            if (store.LaveNum > 0)
                                            {
                                                UserForMakerCode userfor = db.UserForMakerCode.FirstOrDefault(m => m.MakerCode == MakerCode) ?? new UserForMakerCode();
                                                Users touser = db.Users.FirstOrDefault(m => m.Id == userfor.UserId && m.AuthFlag == 1) ?? new Users();
                                                if (touser.Id > 0)
                                                {
                                                    MachineForSnNo machinefor = db.MachineForSnNo.FirstOrDefault(m => m.SnNo == SnNo) ?? new MachineForSnNo();
                                                    PosMachinesTwo machine = db.PosMachinesTwo.FirstOrDefault(m => m.Id == machinefor.SnId && m.BuyUserId == 0 && m.UserId == 0) ?? new PosMachinesTwo();
                                                    if (machine.Id > 0)
                                                    {
                                                        if(machine.BrandId == BrandId)
                                                        {
                                                            StoreStockChange stockchange = db.StoreStockChange.Add(new StoreStockChange()
                                                            {
                                                                CreateDate = DateTime.Now,
                                                                CreateMan = Operator,
                                                                StoreId = storefor.StoreId, //仓库
                                                                BrandId = BrandId, //产品类型
                                                                ProductName = RelationClass.GetKqProductBrandInfo(BrandId), //产品名称
                                                                BizBatchNo = BatchNo, //业务批次号
                                                                TransType = 2, //交易类型
                                                                SnNo = SnNo, //SN编号
                                                                SnType = machine.PosSnType, //SN机具类型
                                                                StockOpDirect = 1, //库存操作方向
                                                                SnStatus = 1, //SN状态
                                                                BindStatus = machine.BindingState, //绑定状态
                                                                BindMerchant = machine.BindMerchantId, //绑定商户
                                                                ActiveStatus = machine.ActivationState, //激活状态
                                                                ActRewardUserId = machine.BuyUserId, //激活奖励人
                                                                DeviceVendor = machine.DeviceName, //设备厂商
                                                                DeviceModel = machine.DeviceKind, //设备型号
                                                                DeviceType = machine.DeviceType, //设备类型
                                                                FromUserId = store.UserId, //出货人
                                                                FromDate = DateTime.Now, //出库时间
                                                                FromRemark = OutNote, //出库备注
                                                                ToUserId = userfor.UserId, //收货人
                                                                SourceStoreId = machine.SourceStoreId, //源仓库编号
                                                                BrandType = machine.DeviceType, //品牌类型
                                                            }).Entity;
                                                            db.SaveChanges();
                                                            string ChangeNo = "SC";
                                                            int StoreStockChangeId = stockchange.Id;
                                                            string StoreStockChangeIdString = StoreStockChangeId.ToString();
                                                            for (int j = 0; j < 18 - StoreStockChangeId.ToString().Length; j++)
                                                            {
                                                                StoreStockChangeIdString = "0" + StoreStockChangeIdString;
                                                            }
                                                            ChangeNo += StoreStockChangeIdString;
                                                            stockchange.ChangeNo = ChangeNo; //交易流水编号
                                                            db.SaveChanges();

                                                            UserStoreChange userstore = db.UserStoreChange.Add(new UserStoreChange()
                                                            {
                                                                CreateDate = DateTime.Now,
                                                                CreateMan = Operator,
                                                                UserId = store.UserId, //创客
                                                                BrandId = BrandId, //产品类型
                                                                ChangeRecordNo = ChangeNo, //变更记录单号
                                                                TransType = 0, //交易类型
                                                                SnNo = SnNo, //SN编号
                                                                SnType = machine.PosSnType, //SN机具类型
                                                                StockOpDirect = 0, //库存操作方向
                                                                DeviceVendor = machine.DeviceName, //设备厂商
                                                                DeviceType = machine.DeviceKind, //设备类型
                                                                DeviceModel = machine.DeviceType, //设备型号
                                                                ToUserId = userfor.UserId, //收货创客
                                                                ToDate = DateTime.Now, //入库时间
                                                                ToRemark = OutNote, //入库备注
                                                                SourceStoreId = machine.SourceStoreId, //源仓库
                                                                SnStatus = 1, //SN状态
                                                                BizBatchNo = BatchNo, //业务批次号
                                                                BindStatus = (int)machine.BindingState, //绑定状态
                                                                BindMerchantId = machine.BindMerchantId, //绑定商户
                                                                ActiveStatus = (int)machine.ActivationState, //激活状态
                                                                ActRewardUserId = machine.BuyUserId, //激活奖励创客
                                                                BrandType = machine.DeviceType, //品牌类型
                                                            }).Entity;

                                                            db.StoreChangeHistory.Add(new StoreChangeHistory()
                                                            {
                                                                CreateDate = DateTime.Now,
                                                                CreateMan = Operator,
                                                                UserId = store.UserId, //创客
                                                                ToUserId = touser.Id, //收货创客
                                                                BrandId = BrandId, //产品类型
                                                                ChangeRecordNo = ChangeNo, //变更记录单号
                                                                BizBatchNo = BatchNo, //业务批次号
                                                                TransType = 2, //交易类型
                                                                SnNo = SnNo, //SN编号
                                                                SnType = machine.PosSnType, //SN机具类型
                                                                StockOpDirect = 1, //库存操作方向
                                                                DeviceVendor = machine.DeviceName, //设备厂商
                                                                DeviceModel = machine.DeviceKind, //设备型号
                                                                DeviceType = machine.DeviceType, //设备类型
                                                                FromUserId = store.UserId, //出货创客
                                                                FromDate = DateTime.Now, //出库时间
                                                                FromRemark = OutNote, //出库备注
                                                                SourceStoreId = machine.SourceStoreId, //源仓库
                                                                StoreId = store.Id, //仓库
                                                            });

                                                            machine.BuyUserId = touser.Id;
                                                            machine.UserId = touser.Id;
                                                            machine.TransferTime = DateTime.Now;
                                                            machine.RecycEndDate = DateTime.Now.AddDays(360); // 循环结束时间
                                                            db.SaveChanges();
                                                            string IdBrand = touser.Id + "_" + BrandId;
                                                            UserMachineData MachineData = db.UserMachineData.FirstOrDefault(m => m.IdBrand == IdBrand);
                                                            if (MachineData == null)
                                                            {
                                                                MachineData = db.UserMachineData.Add(new UserMachineData()
                                                                {
                                                                    IdBrand = IdBrand,
                                                                }).Entity;
                                                                db.SaveChanges();
                                                            }
                                                            MachineData.IdBrand = IdBrand;
                                                            MachineData.TotalMachineCount += 1;
                                                            MachineData.UnBindCount += 1;
                                                            db.SaveChanges();

                                                            RemoveClass.DeletePosMachineList("PosMachineTwoList:" + storefor.StoreId, machine.Id);
                                                            
                                                            if (!storeData.ContainsKey(storefor.StoreId + "_" + BrandId))
                                                            {
                                                                storeData.Add(storefor.StoreId + "_" + BrandId, 1);
                                                            }
                                                            else
                                                            {
                                                                storeData[storefor.StoreId + "_" + BrandId] += 1;
                                                            }
                                                            SuccessCount += 1;
                                                            RedisDbconn.Instance.Set("CheckImport:" + checkKey, SuccessCount + " / " + TotalCount);
                                                        }
                                                        else
                                                        {
                                                            RedisDbconn.Instance.AddList("ErrList" + checkKey, "机具" + SnNo + "与表格里填写的品牌不一致");
                                                        }
                                                    }
                                                    else
                                                    {
                                                        RedisDbconn.Instance.AddList("ErrList" + checkKey, "机具" + SnNo + "不是仓库机");
                                                    }
                                                }
                                                else
                                                {
                                                    RedisDbconn.Instance.AddList("ErrList" + checkKey, "机具" + SnNo + "填写的收货创客编号不正确或创客未认证");
                                                }
                                            }
                                            else
                                            {
                                                RedisDbconn.Instance.AddList("ErrList" + checkKey, "机具" + SnNo + "所在的仓库" + store.StoreName + "(" + store.StoreNo + ")库存不足");
                                            }
                                        }
                                        else
                                        {
                                            RedisDbconn.Instance.AddList("ErrList" + checkKey, "机具" + SnNo + "的品牌填写有误");
                                        }
                                    }
                                    DoCount += Size;
                                    foreach (string key in storeData.Keys)
                                    {
                                        string[] keylist = key.Split('_');
                                        int StoreId = int.Parse(keylist[0]);
                                        int BrandId = int.Parse(keylist[1]);
                                        int OpStoreNum = storeData[key];
                                        StoreHouse store = db.StoreHouse.FirstOrDefault(m => m.Id == StoreId) ?? new StoreHouse();
                                        StoreBalance balance = db.StoreBalance.Add(new StoreBalance()
                                        {
                                            CreateDate = DateTime.Now,
                                            CreateMan = Operator,
                                            StoreId = StoreId, //仓库
                                            TransType = 1, //交易类型
                                            BrandId = BrandId, //产品类型
                                            OpStoreNum = OpStoreNum, //操作库存数
                                            OpSymbol = "-", //操作符
                                            BeforeTotalNum = store.TotalNum, //操作前总库存数
                                            AfterTotalNum = store.TotalNum, //操作后总库存数
                                            BeforeLaveNum = store.LaveNum, //操作前剩余库存数
                                            AfterLaveNum = store.LaveNum - OpStoreNum, //操作后剩余库存数
                                            BeforeOutNum = store.OutNum, //操作前出库数
                                            AfterOutNum = store.OutNum + OpStoreNum, //操作后出库数
                                        }).Entity;
                                        db.SaveChanges();
                                        string ChangeNo = "SC";
                                        int StoreStockChangeId = balance.Id;
                                        string StoreStockChangeIdString = StoreStockChangeId.ToString();
                                        for (int i = 0; i < 18 - StoreStockChangeId.ToString().Length; i++)
                                        {
                                            StoreStockChangeIdString = "0" + StoreStockChangeIdString;
                                        }
                                        ChangeNo += StoreStockChangeIdString;
                                        balance.TransRecordNo = ChangeNo; //交易流水编号
                                        store.LaveNum -= OpStoreNum;
                                        store.OutNum += OpStoreNum;
                                        db.SaveChanges();
                                    }

                                    db.SaveChanges();
                                    tran.Commit();
                                    RedisDbconn.Instance.Set("CheckImport:" + checkKey, "success|" + SuccessCount);
                                    RedisDbconn.Instance.SetExpire("CheckImport:" + checkKey, 60000);
                                }
                                catch (Exception ex)
                                {
                                    DoCount = list.Rows.Count;
                                    tran.Rollback();
                                    ErrorMsg msg = new ErrorMsg()
                                    {
                                        Time = DateTime.Now,
                                        ErrorContent = ex.ToString(),
                                    };
                                    function.WriteLog(Newtonsoft.Json.JsonConvert.SerializeObject(msg), "导入Excel文件异常");
                                }
                            }
                        }
                        else if (_Kind == "4")
                        {
                            Dictionary<string, int> storeData = new Dictionary<string, int>();
                            Dictionary<string, int> toStoreData = new Dictionary<string, int>();
                            using (var tran = db.Database.BeginTransaction())
                            {
                                try
                                {
                                    int Size = 100;
                                    if (list.Rows.Count - DoCount < 100)
                                    {
                                        Size = list.Rows.Count - DoCount;
                                    }
                                    for (int i = DoCount; i < DoCount + Size; i++)
                                    {
                                        DataRow dr = list.Rows[i];
                                        string BatchNo = dr[0].ToString(); // 业务批次号
                                        string OldStoreNo = dr[1].ToString(); // 原仓库编号
                                        string OldOperator = dr[2].ToString(); // 操作人
                                        string SnNo = dr[3].ToString(); // SN编号
                                        SnNo = SnNo.Replace(" ", "");
                                        string StoreNo = dr[4].ToString(); // 退回仓库编号
                                        string BrandIdString = dr[5].ToString(); // 产品类型
                                        string OutNote = "";
                                        if (list.Columns.Count > 6)
                                        {
                                            OutNote = dr[6].ToString(); // 出库备注
                                        }

                                        int BrandId = int.Parse(function.CheckInt(BrandIdString));

                                        decimal amount = 0;
                                        KqProducts kqProduct = db.KqProducts.FirstOrDefault(m => m.Id == BrandId) ?? new KqProducts();
                                        if (kqProduct.Name.Contains("电签"))
                                        {
                                            amount = 200;
                                        }
                                        if (kqProduct.Name.Contains("大POS"))
                                        {
                                            amount = 300;
                                        }
                                        if (kqProduct.Id > 0)
                                        {
                                            StoreForCode storefor = db.StoreForCode.FirstOrDefault(m => m.Code == OldStoreNo) ?? new StoreForCode();
                                            StoreHouse store = db.StoreHouse.FirstOrDefault(m => m.Id == storefor.StoreId) ?? new StoreHouse();
                                            if (store.Id > 0)
                                            {
                                                StoreForCode tostorefor = db.StoreForCode.FirstOrDefault(m => m.Code == StoreNo) ?? new StoreForCode();
                                                StoreHouse tostore = db.StoreHouse.FirstOrDefault(m => m.Id == tostorefor.StoreId && m.Status > -1) ?? new StoreHouse();
                                                if (tostore.Id > 0)
                                                {
                                                    MachineForSnNo machinefor = db.MachineForSnNo.FirstOrDefault(m => m.SnNo == SnNo) ?? new MachineForSnNo();
                                                    PosMachinesTwo machine = db.PosMachinesTwo.FirstOrDefault(m => m.Id == machinefor.SnId && m.BuyUserId > 0) ?? new PosMachinesTwo();
                                                    if (machine.Id > 0)
                                                    {
                                                        function.WriteLog(DateTime.Now.ToString() + "\n" + Newtonsoft.Json.JsonConvert.SerializeObject(machine), "机具回仓库日志");
                                                        machine.StoreId = tostorefor.StoreId;
                                                        machine.BuyUserId = 0;
                                                        machine.UserId = 0;
                                                        machine.BindingState = 0;
                                                        machine.ActivationState = 0;
                                                        machine.BindingTime = null;
                                                        machine.ActivationTime = null;
                                                        machine.OrderId = 0;
                                                        machine.RecycEndDate = null;
                                                        machine.PosSnType = 0;
                                                        machine.BindMerchantId = 0;
                                                        machine.ScanQrTrade = 0;
                                                        machine.DebitCardTrade = 0;
                                                        machine.CreditTrade = 0;
                                                        machine.UserNav = null;
                                                        machine.IsPurchase = 0;
                                                        machine.LeaderUserId = 0;
                                                        machine.PreUserId = 0;
                                                        PublicFunction.ClearPosHistory(db, SnNo); //清除机具历史记录
                                                        db.SaveChanges();
                                                        if (!storeData.ContainsKey(storefor.StoreId + "_" + BrandId))
                                                        {
                                                            storeData.Add(storefor.StoreId + "_" + BrandId, 1);
                                                        }
                                                        else
                                                        {
                                                            storeData[storefor.StoreId + "_" + BrandId] += 1;
                                                        }
                                                        if (!toStoreData.ContainsKey(tostorefor.StoreId + "_" + BrandId))
                                                        {
                                                            toStoreData.Add(tostorefor.StoreId + "_" + BrandId, 1);
                                                        }
                                                        else
                                                        {
                                                            toStoreData[tostorefor.StoreId + "_" + BrandId] += 1;
                                                        }
                                                        SuccessCount += 1;
                                                        RedisDbconn.Instance.Set("CheckImport:" + checkKey, SuccessCount + " / " + TotalCount);
                                                    }
                                                    else
                                                    {
                                                        RedisDbconn.Instance.AddList("ErrList" + checkKey, "机具" + SnNo + "已在仓库中");
                                                    }
                                                }
                                                else
                                                {
                                                    RedisDbconn.Instance.AddList("ErrList" + checkKey, "机具" + SnNo + "填写的退回仓库编号不正确");
                                                }
                                            }
                                            else
                                            {
                                                RedisDbconn.Instance.AddList("ErrList" + checkKey, "机具" + SnNo + "填写的原仓库编号不正确");
                                            }
                                            db.SaveChanges();
                                        }
                                        else
                                        {
                                            RedisDbconn.Instance.AddList("ErrList" + checkKey, "机具" + SnNo + "的品牌填写有误");
                                        }
                                    }
                                    DoCount += Size;
                                    foreach (string key in storeData.Keys)
                                    {
                                        string[] keylist = key.Split('_');
                                        int StoreId = int.Parse(keylist[0]);
                                        int BrandId = int.Parse(keylist[1]);
                                        int OpStoreNum = storeData[key];
                                        StoreHouse store = db.StoreHouse.FirstOrDefault(m => m.Id == StoreId) ?? new StoreHouse();
                                        StoreBalance balance = db.StoreBalance.Add(new StoreBalance()
                                        {
                                            CreateDate = DateTime.Now,
                                            CreateMan = Operator,
                                            StoreId = StoreId, //仓库
                                            TransType = 1, //交易类型
                                            BrandId = BrandId, //产品类型
                                            OpStoreNum = OpStoreNum, //操作库存数
                                            OpSymbol = "-", //操作符
                                            BeforeTotalNum = store.TotalNum, //操作前总库存数
                                            AfterTotalNum = store.TotalNum, //操作后总库存数
                                            BeforeLaveNum = store.LaveNum, //操作前剩余库存数
                                            AfterLaveNum = store.LaveNum - OpStoreNum, //操作后剩余库存数
                                            BeforeOutNum = store.OutNum, //操作前出库数
                                            AfterOutNum = store.OutNum + OpStoreNum, //操作后出库数
                                        }).Entity;
                                        db.SaveChanges();
                                        string ChangeNo = "JJBH";
                                        int StoreStockChangeId = balance.Id;
                                        string StoreStockChangeIdString = StoreStockChangeId.ToString();
                                        for (int i = 0; i < 18 - StoreStockChangeId.ToString().Length; i++)
                                        {
                                            StoreStockChangeIdString = "0" + StoreStockChangeIdString;
                                        }
                                        ChangeNo += StoreStockChangeIdString;
                                        balance.TransRecordNo = ChangeNo; //交易流水编号
                                        store.LaveNum -= OpStoreNum;
                                        db.SaveChanges();
                                    }
                                    foreach (string key in toStoreData.Keys)
                                    {
                                        string[] keylist = key.Split('_');
                                        int StoreId = int.Parse(keylist[0]);
                                        int BrandId = int.Parse(keylist[1]);
                                        int OpStoreNum = toStoreData[key];
                                        StoreHouse store = db.StoreHouse.FirstOrDefault(m => m.Id == StoreId) ?? new StoreHouse();
                                        StoreBalance balance = db.StoreBalance.Add(new StoreBalance()
                                        {
                                            CreateDate = DateTime.Now,
                                            CreateMan = Operator,
                                            StoreId = StoreId, //仓库
                                            TransType = 0, //交易类型
                                            BrandId = BrandId, //产品类型
                                            OpStoreNum = OpStoreNum, //操作库存数
                                            OpSymbol = "+", //操作符
                                            BeforeTotalNum = store.TotalNum, //操作前总库存数
                                            AfterTotalNum = store.TotalNum + OpStoreNum, //操作后总库存数
                                            BeforeLaveNum = store.LaveNum, //操作前剩余库存数
                                            AfterLaveNum = store.LaveNum + OpStoreNum, //操作后剩余库存数
                                            BeforeOutNum = store.OutNum, //操作前出库数
                                            AfterOutNum = store.OutNum, //操作后出库数
                                        }).Entity;
                                        db.SaveChanges();
                                        string ChangeNo = "SC";
                                        int StoreStockChangeId = balance.Id;
                                        string StoreStockChangeIdString = StoreStockChangeId.ToString();
                                        for (int i = 0; i < 18 - StoreStockChangeId.ToString().Length; i++)
                                        {
                                            StoreStockChangeIdString = "0" + StoreStockChangeIdString;
                                        }
                                        ChangeNo += StoreStockChangeIdString;
                                        balance.TransRecordNo = ChangeNo; //交易流水编号
                                        store.LaveNum += OpStoreNum;
                                        db.SaveChanges();
                                    }
                                    db.SaveChanges();
                                    tran.Commit();
                                    RedisDbconn.Instance.Set("CheckImport:" + checkKey, "success|" + SuccessCount);
                                    RedisDbconn.Instance.SetExpire("CheckImport:" + checkKey, 60000);
                                }
                                catch (Exception ex)
                                {
                                    DoCount = list.Rows.Count;
                                    tran.Rollback();
                                    ErrorMsg msg = new ErrorMsg()
                                    {
                                        Time = DateTime.Now,
                                        ErrorContent = ex.ToString(),
                                    };
                                    function.WriteLog(Newtonsoft.Json.JsonConvert.SerializeObject(msg), "导入Excel文件异常");
                                }
                            }
                        }
                        else if (_Kind == "UserCashRecord")
                        {
                            using (var tran = db.Database.BeginTransaction())
                            {
                                try
                                {
                                    int Size = 100;
                                    if (list.Rows.Count - DoCount < 100)
                                    {
                                        Size = list.Rows.Count - DoCount;
                                    }
                                    for (int i = DoCount; i < DoCount + Size; i++)
                                    {
                                        DataRow dr = list.Rows[i];
                                        string CashOrderNo = dr[2].ToString(); // 提现单号
                                        decimal Amount = decimal.Parse(function.CheckNum(dr[3].ToString())); // 发佣金额
                                        int Status = int.Parse(function.CheckInt(dr[4].ToString())); // 提现状态
                                        string Remark = dr[5].ToString(); // 备注
                                        CashOrderNo = CashOrderNo.Replace(" ", "");
                                        UserCashRecord edit = db.UserCashRecord.FirstOrDefault(m => m.CashOrderNo == CashOrderNo && m.ActualTradeAmount == Amount && m.Status == 0);
                                        if (edit != null)
                                        {
                                            if (edit.TradeType == 3)
                                            {
                                                edit.Status = Status;
                                                edit.Remark = Remark;
                                                edit.UpdateMan = Operator;
                                                if (Status == 1)
                                                {
                                                    UserAccount account = db.UserAccount.FirstOrDefault(m => m.Id == edit.UserId);
                                                    if (account != null)
                                                    {
                                                        decimal TradeAmount = edit.TradeAmount;
                                                        db.LeaderAccountRecord.Add(new LeaderAccountRecord()
                                                        {
                                                            CreateDate = DateTime.Now,
                                                            UserId = edit.UserId, //创客
                                                            ChangeType = 3, //变动类型
                                                            ProductType = 99, //产品类型
                                                            ChangeAmount = TradeAmount, //变更金额
                                                            BeforeBalanceAmount = account.LeaderBalanceAmount, //变更前余额
                                                            AfterBalanceAmount = account.LeaderBalanceAmount, //变更后余额
                                                        });
                                                        db.SaveChanges();
                                                        RedisDbconn.Instance.Set("UserAccount:" + edit.UserId, account);
                                                    }
                                                }
                                                else if (Status == 2)
                                                {
                                                    UserAccount account = db.UserAccount.FirstOrDefault(m => m.Id == edit.UserId);
                                                    if (account != null)
                                                    {
                                                        decimal TradeAmount = edit.TradeAmount;
                                                        account.LeaderBalanceAmount += TradeAmount;
                                                        db.LeaderAccountRecord.Add(new LeaderAccountRecord()
                                                        {
                                                            CreateDate = DateTime.Now,
                                                            UserId = edit.UserId, //创客
                                                            ChangeType = 6, //变动类型
                                                            ProductType = 99, //产品类型
                                                            ChangeAmount = TradeAmount, //变更金额
                                                            BeforeBalanceAmount = account.LeaderBalanceAmount - TradeAmount, //变更前余额
                                                            AfterBalanceAmount = account.LeaderBalanceAmount, //变更后余额
                                                        });
                                                        db.SaveChanges();
                                                        RedisDbconn.Instance.Set("UserAccount:" + edit.UserId, account);
                                                    }
                                                }
                                                db.SaveChanges();
                                                RedisDbconn.Instance.Clear("UserCashRecord:" + edit.Id);
                                                SuccessCount += 1;
                                                RedisDbconn.Instance.Set("CheckImport:" + checkKey, SuccessCount + " / " + TotalCount);
                                            }
                                            else if (edit.TradeType == 4)
                                            {
                                                edit.Status = Status;
                                                edit.Remark = Remark;
                                                edit.UpdateMan = Operator;
                                                if (Status == 1)
                                                {
                                                    UserAccount account = db.UserAccount.FirstOrDefault(m => m.Id == edit.UserId);
                                                    if (account != null)
                                                    {
                                                        decimal TradeAmount = edit.TradeAmount;
                                                        decimal ActualTradeAmount = edit.ActualTradeAmount;
                                                        account.HelpProfitBalanceAmount -= TradeAmount;
                                                        db.HelpProfitAccountRecord.Add(new HelpProfitAccountRecord()
                                                        {
                                                            CreateDate = DateTime.Now,
                                                            UserId = edit.UserId, //创客
                                                            ChangeType = 3, //变动类型
                                                            ProductType = 99, //产品类型
                                                            ChangeAmount = ActualTradeAmount, //变更金额
                                                            BeforeBalanceAmount = account.HelpProfitBalanceAmount + TradeAmount, //变更前余额
                                                            AfterBalanceAmount = account.HelpProfitBalanceAmount, //变更后余额
                                                        });
                                                        db.HelpProfitAccountRecord.Add(new HelpProfitAccountRecord()
                                                        {
                                                            CreateDate = DateTime.Now,
                                                            UpdateDate = DateTime.Now,
                                                            UserId = edit.UserId, //创客
                                                            ChangeType = 4, //变动类型
                                                            ProductType = 99, //产品类型
                                                            ChangeAmount = edit.TradeFee, //变更金额
                                                            BeforeBalanceAmount = account.HelpProfitBalanceAmount + TradeAmount, //变更前余额
                                                            AfterBalanceAmount = account.HelpProfitBalanceAmount, //变更后余额
                                                        });
                                                        db.HelpProfitAccountRecord.Add(new HelpProfitAccountRecord()
                                                        {
                                                            CreateDate = DateTime.Now,
                                                            UpdateDate = DateTime.Now,
                                                            UserId = edit.UserId, //创客
                                                            ChangeType = 5, //变动类型
                                                            ProductType = 99, //产品类型
                                                            ChangeAmount = edit.ManageFee, //变更金额
                                                            BeforeBalanceAmount = account.HelpProfitBalanceAmount + TradeAmount, //变更前余额
                                                            AfterBalanceAmount = account.HelpProfitBalanceAmount, //变更后余额
                                                        });
                                                        db.SaveChanges();
                                                        RedisDbconn.Instance.Set("HelpProfitAccount:" + edit.UserId, account);
                                                    }
                                                }
                                                else if (Status == 2)
                                                {
                                                    UserAccount account = db.UserAccount.FirstOrDefault(m => m.Id == edit.UserId);
                                                    if (account != null)
                                                    {
                                                        decimal TradeAmount = edit.TradeAmount;
                                                        decimal BeforeTotalAmount = account.TotalAmount; //变更前总金额
                                                        decimal BeforeFreezeAmount = account.FreezeAmount; //变更前冻结金额
                                                        decimal BeforeBalanceAmount = account.BalanceAmount; //变更前余额
                                                        account.BalanceAmount += TradeAmount;
                                                        account.FreezeAmount -= TradeAmount;
                                                        decimal AfterTotalAmount = account.TotalAmount; //变更后总金额
                                                        decimal AfterFreezeAmount = account.FreezeAmount; //变更后冻结金额
                                                        decimal AfterBalanceAmount = account.BalanceAmount; //变更后余额
                                                        db.UserAccountRecord.Add(new UserAccountRecord()
                                                        {
                                                            CreateDate = DateTime.Now,
                                                            UpdateDate = DateTime.Now,
                                                            UserId = edit.UserId, //创客
                                                            ChangeType = 6, //变动类型
                                                            ProductType = 99, //产品类型
                                                            ChangeAmount = TradeAmount, //变更金额
                                                            BeforeTotalAmount = BeforeTotalAmount, //变更前总金额
                                                            AfterTotalAmount = AfterTotalAmount, //变更后总金额
                                                            BeforeFreezeAmount = BeforeFreezeAmount, //变更前冻结金额
                                                            AfterFreezeAmount = AfterFreezeAmount, //变更后冻结金额
                                                            BeforeBalanceAmount = BeforeBalanceAmount, //变更前余额
                                                            AfterBalanceAmount = AfterBalanceAmount, //变更后余额
                                                        });
                                                        db.SaveChanges();
                                                        RedisDbconn.Instance.Set("UserAccount:" + edit.UserId, account);
                                                    }
                                                }
                                                db.SaveChanges();
                                                RedisDbconn.Instance.Clear("UserCashRecord:" + edit.Id);
                                                SuccessCount += 1;
                                                RedisDbconn.Instance.Set("CheckImport:" + checkKey, SuccessCount + " / " + TotalCount);
                                            }
                                            else
                                            {
                                                edit.Status = Status;
                                                edit.Remark = Remark;
                                                edit.UpdateMan = Operator;
                                                if (Status == 1)
                                                {
                                                    UserAccount account = db.UserAccount.FirstOrDefault(m => m.Id == edit.UserId);
                                                    if (account != null)
                                                    {                                                        
                                                        account.QueryCount = 1;
                                                        decimal TradeAmount = edit.TradeAmount;
                                                        decimal BeforeTotalAmount = account.TotalAmount; //变更前总金额
                                                        decimal BeforeFreezeAmount = account.FreezeAmount; //变更前冻结金额
                                                        decimal BeforeBalanceAmount = account.BalanceAmount; //变更前余额
                                                        account.WithdrawAmount += edit.TradeAmount;
                                                        account.FreezeAmount -= edit.TradeAmount;
                                                        decimal AfterTotalAmount = account.TotalAmount; //变更后总金额
                                                        decimal AfterFreezeAmount = account.FreezeAmount; //变更后冻结金额
                                                        decimal AfterBalanceAmount = account.BalanceAmount; //变更后余额
                                                        db.UserAccountRecord.Add(new UserAccountRecord()
                                                        {
                                                            CreateDate = DateTime.Now,
                                                            UpdateDate = DateTime.Now,
                                                            UserId = edit.UserId, //创客
                                                            ChangeType = 3, //变动类型
                                                            ProductType = 99, //产品类型
                                                            ChangeAmount = edit.ActualTradeAmount, //变更金额
                                                            BeforeTotalAmount = BeforeTotalAmount, //变更前总金额
                                                            AfterTotalAmount = AfterTotalAmount, //变更后总金额
                                                            BeforeFreezeAmount = BeforeFreezeAmount, //变更前冻结金额
                                                            AfterFreezeAmount = AfterFreezeAmount, //变更后冻结金额
                                                            BeforeBalanceAmount = BeforeBalanceAmount, //变更前余额
                                                            AfterBalanceAmount = AfterBalanceAmount, //变更后余额
                                                        });
                                                        db.UserAccountRecord.Add(new UserAccountRecord()
                                                        {
                                                            CreateDate = DateTime.Now,
                                                            UpdateDate = DateTime.Now,
                                                            UserId = edit.UserId, //创客
                                                            ChangeType = 4, //变动类型
                                                            ProductType = 99, //产品类型
                                                            ChangeAmount = edit.TradeFee, //变更金额
                                                            BeforeTotalAmount = BeforeTotalAmount, //变更前总金额
                                                            AfterTotalAmount = AfterTotalAmount, //变更后总金额
                                                            BeforeFreezeAmount = BeforeFreezeAmount, //变更前冻结金额
                                                            AfterFreezeAmount = AfterFreezeAmount, //变更后冻结金额
                                                            BeforeBalanceAmount = BeforeBalanceAmount, //变更前余额
                                                            AfterBalanceAmount = AfterBalanceAmount, //变更后余额
                                                        });
                                                        db.UserAccountRecord.Add(new UserAccountRecord()
                                                        {
                                                            CreateDate = DateTime.Now,
                                                            UpdateDate = DateTime.Now,
                                                            UserId = edit.UserId, //创客
                                                            ChangeType = 5, //变动类型
                                                            ProductType = 99, //产品类型
                                                            ChangeAmount = edit.ManageFee, //变更金额
                                                            BeforeTotalAmount = BeforeTotalAmount, //变更前总金额
                                                            AfterTotalAmount = AfterTotalAmount, //变更后总金额
                                                            BeforeFreezeAmount = BeforeFreezeAmount, //变更前冻结金额
                                                            AfterFreezeAmount = AfterFreezeAmount, //变更后冻结金额
                                                            BeforeBalanceAmount = BeforeBalanceAmount, //变更前余额
                                                            AfterBalanceAmount = AfterBalanceAmount, //变更后余额
                                                        });
                                                        db.SaveChanges();
                                                        RedisDbconn.Instance.Set("UserAccount:" + edit.UserId, account);
                                                    }
                                                }
                                                else if (Status == 2)
                                                {
                                                    UserAccount account = db.UserAccount.FirstOrDefault(m => m.Id == edit.UserId);
                                                    if (account != null)
                                                    {
                                                        decimal TradeAmount = edit.TradeAmount;
                                                        if(account.FreezeAmount < TradeAmount)
                                                        {
                                                            RedisDbconn.Instance.AddList("ErrList" + checkKey, "提现记录单号为" + CashOrderNo + "的创客冻结金额异常");
                                                        }
                                                        else
                                                        {
                                                            decimal BeforeTotalAmount = account.TotalAmount; //变更前总金额
                                                            decimal BeforeFreezeAmount = account.FreezeAmount; //变更前冻结金额
                                                            decimal BeforeBalanceAmount = account.BalanceAmount; //变更前余额
                                                            account.BalanceAmount += TradeAmount;
                                                            account.FreezeAmount -= TradeAmount;
                                                            decimal AfterTotalAmount = account.TotalAmount; //变更后总金额
                                                            decimal AfterFreezeAmount = account.FreezeAmount; //变更后冻结金额
                                                            decimal AfterBalanceAmount = account.BalanceAmount; //变更后余额
                                                            db.UserAccountRecord.Add(new UserAccountRecord()
                                                            {
                                                                CreateDate = DateTime.Now,
                                                                UpdateDate = DateTime.Now,
                                                                UserId = edit.UserId, //创客
                                                                ChangeType = 6, //变动类型
                                                                ProductType = 99, //产品类型
                                                                ChangeAmount = TradeAmount, //变更金额
                                                                BeforeTotalAmount = BeforeTotalAmount, //变更前总金额
                                                                AfterTotalAmount = AfterTotalAmount, //变更后总金额
                                                                BeforeFreezeAmount = BeforeFreezeAmount, //变更前冻结金额
                                                                AfterFreezeAmount = AfterFreezeAmount, //变更后冻结金额
                                                                BeforeBalanceAmount = BeforeBalanceAmount, //变更前余额
                                                                AfterBalanceAmount = AfterBalanceAmount, //变更后余额
                                                            });
                                                            db.SaveChanges();
                                                            RedisDbconn.Instance.Set("UserAccount:" + edit.UserId, account);
                                                        }
                                                    }
                                                }
                                                db.SaveChanges();
                                                RedisDbconn.Instance.Clear("UserCashRecord:" + edit.Id);
                                                SuccessCount += 1;
                                                RedisDbconn.Instance.Set("CheckImport:" + checkKey, SuccessCount + " / " + TotalCount);
                                            }

                                        }
                                        else
                                        {
                                            RedisDbconn.Instance.AddList("ErrList" + checkKey, "提现记录单号为" + CashOrderNo + "不存在或者金额不符");
                                        }
                                    }
                                    DoCount += Size;

                                    db.SaveChanges();
                                    tran.Commit();
                                    RedisDbconn.Instance.Set("CheckImport:" + checkKey, "success|" + SuccessCount);
                                    RedisDbconn.Instance.SetExpire("CheckImport:" + checkKey, 60000);
                                }
                                catch (Exception ex)
                                {
                                    DoCount = list.Rows.Count;
                                    tran.Rollback();
                                    ErrorMsg msg = new ErrorMsg()
                                    {
                                        Time = DateTime.Now,
                                        ErrorContent = ex.ToString(),
                                    };
                                    function.WriteLog(Newtonsoft.Json.JsonConvert.SerializeObject(msg), "导入Excel文件异常");
                                }
                            }
                        }
                        else if (_Kind == "ChangeFee")
                        {
                            using (var tran = db.Database.BeginTransaction())
                            {
                                try
                                {
                                    int Size = 100;
                                    if (list.Rows.Count - DoCount < 100)
                                    {
                                        Size = list.Rows.Count - DoCount;
                                    }
                                    string posIds = "";
                                    string userIds = "";
                                    string warning = "";
                                    for (int i = DoCount; i < DoCount + Size; i++)
                                    {
                                        DataRow dr = list.Rows[i];
                                        string PosNo = dr[0].ToString(); // 机具Sn
                                        decimal Amount = decimal.Parse(function.CheckNum(dr[1].ToString())); // 调整费率
                                        int Kind = int.Parse(function.CheckInt(dr[2].ToString())); // 1 调升 2 调低
                                        var pos = db.PosMachinesTwo.FirstOrDefault(m => m.PosSn == PosNo) ?? new PosMachinesTwo();
                                        if (pos.BrandId == 1 || pos.BrandId == 3 || pos.BrandId == 6)
                                        {
                                            warning += PosNo + ",";
                                        }
                                    }
                                    if (!string.IsNullOrEmpty(warning))
                                    {
                                        RedisDbconn.Instance.AddList("ErrList" + checkKey, "机具" + warning + "该机具应直接设置，请全部重新导入");
                                    }
                                    else
                                    {
                                        for (int i = DoCount; i < DoCount + Size; i++)
                                        {
                                            DataRow dr = list.Rows[i];
                                            string PosNo = dr[0].ToString(); // 机具Sn
                                            decimal Amount = decimal.Parse(function.CheckNum(dr[1].ToString())); // 调整费率
                                            int Kind = int.Parse(function.CheckInt(dr[2].ToString())); // 1 调升 2 调低
                                            var pos = db.PosMachinesTwo.FirstOrDefault(m => m.PosSn == PosNo) ?? new PosMachinesTwo();
                                            if (pos.BrandId == 1 || pos.BrandId == 3 || pos.BrandId == 6)
                                            {
                                                warning += PosNo + ",";
                                            }
                                            if (Kind == 1)
                                            {
                                                pos.UpFeeFlag = 1;
                                                pos.UpFeeDate = DateTime.Now;
                                                pos.UpFeeMan = Operator;
                                                db.SaveChanges();
                                            }
                                            if (Kind == 2)
                                            {
                                                pos.DownFeeFlag = 1;
                                                pos.DownFeeDate = DateTime.Now;
                                                pos.DownFeeMan = Operator;
                                                db.SaveChanges();
                                            }
                                            if (!userIds.Contains(pos.BuyUserId.ToString()))
                                            {
                                                userIds += pos.BuyUserId + ",";
                                            }
                                            posIds += pos.Id + ",";
                                        }
                                        string[] lists = userIds.Split(new char[] { ',' });
                                        int times = lists.ToList().Count;
                                        for (int i = 0; i < times; i++)
                                        {
                                            var UserId = lists[i];
                                            var posInfo = db.PosMachinesTwo.Where(m => posIds.Contains(m.Id.ToString()) && m.BuyUserId == Convert.ToInt32(UserId));
                                            string snhtml = "<div style='margin-bottom: .48rem;'>";
                                            foreach (var items in posInfo)
                                            {
                                                var mer = db.PosMerchantInfo.FirstOrDefault(m => m.Id == items.BindMerchantId) ?? new PosMerchantInfo();
                                                var brand = db.KqProducts.FirstOrDefault(m => m.Id == items.BrandId) ?? new KqProducts();
                                                snhtml += "<div style='margin-bottom: .48rem;'><div class='f12'>商户姓名：" + mer.MerRealName + "</div>";
                                                snhtml += "<div class='f12'>机具品牌：" + brand.Name + "</div>";
                                                snhtml += "<div class='f12'>SN：" + items.PosSn + "</div>";
                                                snhtml += "<div class='f12'>当前费率：0.6%</div>";
                                                snhtml += "<div class='f12'>费率调整时间：" + items.DownFeeDate + "</div>";
                                            }
                                            snhtml += "</div>";
                                            RedisDbconn.Instance.AddList("MsgPersonalQueue", Newtonsoft.Json.JsonConvert.SerializeObject(new MsgPersonal()
                                            {
                                                UserId = Convert.ToInt32(UserId), //创客
                                                Title = "商户费率变更通知", //标题
                                                Content = "<div class='f12' style='margin-bottom: .72rem'>您的商户刷卡交易费率已变更成功！</div>" + snhtml, //内容
                                                Summary = "您的商户刷卡交易费率已变更成功！",
                                                CreateDate = DateTime.Now,

                                            }));
                                        }
                                        RedisDbconn.Instance.Set("CheckImport:" + checkKey, "success|" + SuccessCount);
                                        RedisDbconn.Instance.SetExpire("CheckImport:" + checkKey, 60000);
                                    }
                                }
                                catch (Exception ex)
                                {
                                    DoCount = list.Rows.Count;
                                    tran.Rollback();
                                    ErrorMsg msg = new ErrorMsg()
                                    {
                                        Time = DateTime.Now,
                                        ErrorContent = ex.ToString(),
                                    };
                                    function.WriteLog(Newtonsoft.Json.JsonConvert.SerializeObject(msg), "导入Excel文件异常");
                                }
                            }
                        }
                        db.Dispose();
                    }
                }
                else
                {
                    Thread.Sleep(5000);
                }
                // return "success|" + SuccessCount;
            }
        }
    }
}