kxs-admin/AppStart/OpExcelHelper.cs

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

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

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

        public void ImportPostDo()
        {
            while (true)
            {
                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 == "OpUserCashRecord")
                        {
                            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 == 4)
                                            {
                                                edit.Status = Status;
                                                edit.Remark = Remark;
                                                edit.UpdateMan = Operator;
                                                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("OpUserAccount:" + edit.UserId, account);
                                                    }
                                                }
                                                db.SaveChanges();
                                                RedisDbconn.Instance.Clear("OpUserCashRecord:" + edit.Id);
                                                SuccessCount += 1;
                                                RedisDbconn.Instance.Set("OpCheckImport:" + 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("OpUserAccount:" + 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)
                                                        {
                                                            // TODO: 需要返回错误提示消息
                                                            // 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("OpUserAccount:" + edit.UserId, account);
                                                        }
                                                    }
                                                }
                                                db.SaveChanges();
                                                RedisDbconn.Instance.Clear("OpUserCashRecord:" + edit.Id);
                                                SuccessCount += 1;
                                                RedisDbconn.Instance.Set("OpCheckImport:" + checkKey, SuccessCount + " / " + TotalCount);
                                            }

                                        }
                                    }
                                    DoCount += Size;

                                    db.SaveChanges();
                                    tran.Commit();
                                    RedisDbconn.Instance.Set("OpCheckImport:" + checkKey, "success|" + SuccessCount);
                                    RedisDbconn.Instance.SetExpire("OpCheckImport:" + 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;
            }
        }
    }
}