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 = "";
                    if (_Kind == "UserCashRecord")
                    {
                        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,
                                                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,
                                                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.Set("PosMachinesTwo:" + pos.Id, pos);
                                            RedisDbconn.Instance.AddList("PosMachineList:" + storefor.StoreId, pos);
                                            RedisDbconn.Instance.AddList("PosMachinesTwo:" + store.UserId + ":" + BrandId, new PosMachineList()
                                            {
                                                Id = pos.Id,
                                                SnNo = SnNo,
                                            });

                                            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,
                                        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();
                                    RedisDbconn.Instance.Clear("StoreHouse:" + StoreId);
                                    RedisDbconn.Instance.Clear("StoreHouseList:" + store.UserId);
                                }
                                db.SaveChanges();

                                tran.Commit();
                                RedisDbconn.Instance.Clear("StoreHouse");
                                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), "BsServerAdminError");
                            }
                            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));

                                        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)
                                                    {
                                                        StoreStockChange stockchange = db.StoreStockChange.Add(new StoreStockChange()
                                                        {
                                                            CreateDate = DateTime.Now,
                                                            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,
                                                            UserId = store.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, //源仓库
                                                            StoreId = store.Id, //仓库
                                                        });

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

                                                        // 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 + "不是仓库机或不在填写的收货仓库中");
                                                    }
                                                    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 + "的品牌填写有误");
                                        }
                                    }
                                    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,
                                            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();
                                        RedisDbconn.Instance.Clear("StoreHouse:" + StoreId);
                                        RedisDbconn.Instance.Clear("StoreHouseList:" + store.UserId);
                                    }
                                    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,
                                            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();
                                        RedisDbconn.Instance.Clear("StoreHouse:" + StoreId);
                                        RedisDbconn.Instance.Clear("StoreHouseList:" + store.UserId);
                                    }
                                    db.SaveChanges();
                                    tran.Commit();
                                    RedisDbconn.Instance.Clear("StoreHouse");
                                    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), "BsServerAdminError");
                                }
                            }
                        }
                        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)
                                                    {
                                                        StoreStockChange stockchange = db.StoreStockChange.Add(new StoreStockChange()
                                                        {
                                                            CreateDate = DateTime.Now,
                                                            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,
                                                            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,
                                                            UserId = store.UserId, //创客
                                                            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, //仓库
                                                        });

                                                        db.StoreChangeHistory.Add(new StoreChangeHistory()
                                                        {
                                                            CreateDate = DateTime.Now,
                                                            UserId = touser.Id, //创客
                                                            BrandId = BrandId, //产品类型
                                                            ChangeRecordNo = ChangeNo, //变更记录单号
                                                            BizBatchNo = BatchNo, //业务批次号
                                                            TransType = 0, //交易类型
                                                            SnNo = SnNo, //SN编号
                                                            SnType = machine.PosSnType, //SN机具类型
                                                            StockOpDirect = 0, //库存操作方向
                                                            DeviceVendor = machine.DeviceName, //设备厂商
                                                            DeviceModel = machine.DeviceKind, //设备型号
                                                            DeviceType = machine.DeviceType, //设备类型
                                                            ToUserId = touser.Id, //收货创客
                                                            ToDate = DateTime.Now, //入库时间
                                                            ToRemark = OutNote, //入库备注
                                                            SourceStoreId = machine.SourceStoreId, //源仓库
                                                        });
                                                        machine.BuyUserId = touser.Id;
                                                        machine.UserId = touser.Id;
                                                        machine.TransferTime = DateTime.Now;
                                                        machine.RecycEndDate = DateTime.Now.AddDays(180); // 循环结束时间
                                                        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();
                                                        RedisDbconn.Instance.Set("UserMachineData:" + IdBrand, MachineData);

                                                        RemoveClass.DeletePosMachineList("PosMachineTwoList:" + storefor.StoreId, machine.Id);
                                                        RedisDbconn.Instance.AddList("PosMachinesTwo:" + userfor.UserId + ":" + BrandId, new PosMachineList()
                                                        {
                                                            Id = machine.Id,
                                                            SnNo = SnNo,
                                                            IsBind = machine.BindingState == 1 ? true : false,
                                                        });

                                                        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 + "所在的仓库" + 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,
                                            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();
                                        RedisDbconn.Instance.Clear("StoreHouse:" + StoreId);
                                        RedisDbconn.Instance.Clear("StoreHouseList:" + store.UserId);
                                    }

                                    db.SaveChanges();
                                    tran.Commit();
                                    RedisDbconn.Instance.Clear("StoreHouse");
                                    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), "BsServerAdminError");
                                }
                            }
                        }
                        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);
                                        if (edit != null)
                                        {
                                            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;
                                                    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);
                                        }
                                    }
                                    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), "BsServerAdminError");
                                }
                            }
                        }
                        db.Dispose();
                    }
                }
                // return "success|" + SuccessCount;
            }
        }
    }
}