kxs-admin/Areas/Admin/Controllers/MainServer/TradeRecordController.cs

/*
 * 交易记录
 */

using System;
using System.Web;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Data;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using MySystem.Models;
using Library;
using LitJson;
using MySystemLib;

namespace MySystem.Areas.Admin.Controllers
{
    [Area("Admin")]
    [Route("Admin/[controller]/[action]")]
    public class TradeRecordController : BaseController
    {
        public TradeRecordController(IHttpContextAccessor accessor, ILogger<BaseController> logger, IOptions<Setting> setting) : base(accessor, logger, setting)
        {
            OtherMySqlConn.connstr = ConfigurationManager.AppSettings["SqlConnStr"].ToString();
        }

        #region 交易记录列表

        /// <summary>
        /// 根据条件查询交易记录列表
        /// </summary>
        /// <returns></returns>
        public IActionResult Index(TradeRecord data, string right, string BrandId)
        {
            ViewBag.RightInfo = RightInfo;
            ViewBag.right = right;
            ViewBag.BrandId = BrandId;

            return View();
        }

        #endregion

        #region 根据条件查询交易记录列表

        /// <summary>
        /// 交易记录列表
        /// </summary>
        /// <returns></returns>
        public JsonResult IndexData(TradeRecord data, string right, string BrandId, string MerchantNo, string MerchantName, string MakerCode, string RealName, string MerMakerCode, string MerRealName, string MerNo, string SnNo, string BankCardTypeSelect, string MerBuddyTypeSelect, string CreateDateData, int page = 1, int limit = 30)
        {

            Dictionary<string, string> Fields = new Dictionary<string, string>();

            Fields.Add("BrandId", "1");
            Fields.Add("MerNo", "2"); //快钱商户编号
            Fields.Add("SnNo", "2"); //快钱SN号
            Fields.Add("TopUserId", "0"); //顶级创客

            string condition = " and Status>-1 and BrandId =" + BrandId;
            //商户编号
            if (!string.IsNullOrEmpty(MerchantNo))
            {
                condition += " and MerchantId in (select MerchantId from MerchantForCode where Code='" + MerchantNo + "')";
            }
            //商户名称
            if (!string.IsNullOrEmpty(MerchantName))
            {
                condition += " and MerchantId in (select MerchantId from MerchantForName where Name='" + MerchantName + "')";
            }
            //创客编号
            if (!string.IsNullOrEmpty(MakerCode))
            {
                condition += " and UserId in (select UserId from UserForMakerCode where MakerCode='" + MakerCode + "')";
            }
            //创客名称
            if (!string.IsNullOrEmpty(RealName))
            {
                condition += " and UserId in (select UserId from UserForRealName where RealName='" + RealName + "')";
            }
            //商户创客编号
            if (!string.IsNullOrEmpty(MerMakerCode))
            {
                condition += " and MerUserId in (select UserId from UserForMakerCode where MakerCode='" + MerMakerCode + "')";
            }
            //商户创客名称
            if (!string.IsNullOrEmpty(MerRealName))
            {
                condition += " and MerUserId in (select UserId from UserForRealName where RealName='" + MerRealName + "')";
            }
            //快钱商户编号
            if (!string.IsNullOrEmpty(MerNo))
            {
                condition += " and MerNo='" + MerNo + "'";
            }
            //块钱SN号
            if (!string.IsNullOrEmpty(SnNo))
            {
                condition += " and SnNo='" + SnNo + "'";
            }
            //银行卡类型
            if (!string.IsNullOrEmpty(BankCardTypeSelect))
            {
                condition += " and BankCardType=" + BankCardTypeSelect;
            }
            //商户创客类型
            if (!string.IsNullOrEmpty(MerBuddyTypeSelect))
            {
                condition += " and MerBuddyType=" + MerBuddyTypeSelect;
            }
            if (!string.IsNullOrEmpty(CreateDateData))
            {
                string[] datelist = CreateDateData.Split(new string[] { " - " }, StringSplitOptions.None);
                var start = Convert.ToDateTime(Convert.ToDateTime(datelist[0]).ToString("yyyy-MM-dd"));
                var end = Convert.ToDateTime(Convert.ToDateTime(datelist[1]).ToString("yyyy-MM-dd") + " 23:59:59");
                var check = db.TradeRecord.Any(m => m.CreateDate >= start && m.BrandId == Convert.ToInt32(BrandId));
                var checks = db.TradeRecord.Any(m => m.CreateDate <= end && m.BrandId == Convert.ToInt32(BrandId));
                if (check)
                {
                    var sId = db.TradeRecord.Where(m => m.CreateDate >= start && m.BrandId == Convert.ToInt32(BrandId)).Min(m => m.Id);
                    condition += "  and Id >=" + sId;
                }
                if (checks)
                {
                    var eId = db.TradeRecord.Where(m => m.CreateDate <= end && m.BrandId == Convert.ToInt32(BrandId)).Max(m => m.Id);
                    condition += " and Id <=" + eId;
                }
            }
            else
            {
                var start = Convert.ToDateTime(DateTime.Now.AddMonths(-1).ToString("yyyy-MM") + "-01");
                var check = db.TradeRecord.Any(m => m.CreateDate >= start && m.BrandId == Convert.ToInt32(BrandId));
                if (check)
                {
                    var minId = db.TradeRecord.Where(m => m.CreateDate >= start && m.BrandId == Convert.ToInt32(BrandId)).Min(m => m.Id);
                    var Info = function.ReadInstance("/WritePage/TradeRecord/TradeRecord" + BrandId + ".txt");
                    if (string.IsNullOrEmpty(Info))
                    {
                        function.WritePage("/WritePage/TradeRecord/", "TradeRecord" + BrandId + ".txt", minId.ToString());
                        condition += " and Id >=" + minId;
                    }
                    else if (minId != int.Parse(Info))
                    {
                        function.WritePage("/WritePage/TradeRecord/", "TradeRecord" + BrandId + ".txt", minId.ToString());
                        condition += " and Id >=" + minId;
                    }
                    else
                    {
                        condition += " and Id >=" + Convert.ToInt32(Info);
                    }
                }
                else
                {
                    condition += " and Id =0";
                }
            }

            Dictionary<string, object> obj = new AdminContentOther(_accessor.HttpContext, PublicFunction.MainTables).IndexData("TradeRecord", Fields, "Id desc", "0", page, limit, condition);
            List<Dictionary<string, object>> diclist = obj["data"] as List<Dictionary<string, object>>;
            foreach (Dictionary<string, object> dic in diclist)
            {
                dic["MerHelpFlag"] = dic["MerHelpFlag"].ToString() == "1" ? "是" : "否";
                dic["CapFlag"] = dic["CapFlag"].ToString() == "1" ? "封顶" : "非封顶";
                dic["QrPayFlag"] = dic["QrPayFlag"].ToString() == "1" ? "云闪付" : "非云闪付";
                dic["DirectFlag"] = dic["DirectFlag"].ToString() == "1" ? "直营" : "非直营";
                //直属创客
                int UserId = int.Parse(dic["UserId"].ToString());
                Users puser = db.Users.FirstOrDefault(m => m.Id == UserId) ?? new Users();
                dic["MakerCode"] = puser.MakerCode;
                dic["RealName"] = puser.RealName;
                //顶级创客
                int TopUserId = int.Parse(dic["TopUserId"].ToString());
                Users tuser = db.Users.FirstOrDefault(m => m.Id == TopUserId) ?? new Users();
                dic["TopMakerCode"] = tuser.MakerCode;
                dic["TopRealName"] = tuser.RealName;
                //商户创客
                int MerUserId = int.Parse(dic["MerUserId"].ToString());
                Users muser = db.Users.FirstOrDefault(m => m.Id == MerUserId) ?? new Users();
                dic["MerMakerCode"] = muser.MakerCode;
                dic["MerRealName"] = muser.RealName;
                //商户
                int MerchantId = int.Parse(dic["MerchantId"].ToString());
                MerchantInfo merchant = db.MerchantInfo.FirstOrDefault(m => m.Id == MerchantId) ?? new MerchantInfo();
                dic["MerchantNo"] = "";
                dic["MerchantName"] = merchant.Name;
                //商户创客类型
                int MerBuddyType = int.Parse(dic["MerBuddyType"].ToString());
                if (MerBuddyType == 0) dic["MerBuddyType"] = "非商户型创客";
                if (MerBuddyType == 1) dic["MerBuddyType"] = "商户型创客";
                //银行卡类型
                int BankCardType = int.Parse(dic["BankCardType"].ToString());
                if (BankCardType == 0) dic["BankCardType"] = "借记卡";
                if (BankCardType == 1) dic["BankCardType"] = "贷记卡";
                if (BankCardType == 2) dic["BankCardType"] = "准贷记卡";
            }
            Dictionary<string, object> other = new Dictionary<string, object>();

            string TotalTrade0 = "0.00";//贷记卡交易总额
            string TotalTrade1 = "0.00";//借记卡交易总额
            DataTable dt = OtherMySqlConn.dtable("SELECT SUM(if(BankCardType=1,TradeAmount,0)),SUM(if(BankCardType=0,TradeAmount,0)) FROM TradeRecord where 1=1" + condition);
            if (dt.Rows.Count > 0)
            {
                TotalTrade0 = decimal.Parse(function.CheckNum(dt.Rows[0][0].ToString())).ToString("f2");
                TotalTrade1 = decimal.Parse(function.CheckNum(dt.Rows[0][1].ToString())).ToString("f2");
            }
            other.Add("TotalTrade0", TotalTrade0);
            other.Add("TotalTrade1", TotalTrade1);
            obj.Add("other", other);
            return Json(obj);
        }

        #endregion

        #region 增加交易记录

        /// <summary>
        /// 增加或修改交易记录信息
        /// </summary>
        /// <returns></returns>
        public IActionResult Add(string right)
        {
            ViewBag.RightInfo = RightInfo;
            ViewBag.right = right;


            return View();
        }

        #endregion

        #region 增加交易记录

        /// <summary>
        /// 增加或修改交易记录信息
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public string Add(TradeRecord data)
        {
            Dictionary<string, object> Fields = new Dictionary<string, object>();


            Fields.Add("SeoTitle", data.SeoTitle);
            Fields.Add("SeoKeyword", data.SeoKeyword);
            Fields.Add("SeoDescription", data.SeoDescription);
            int Id = new AdminContentOther(_accessor.HttpContext, PublicFunction.MainTables).Add("TradeRecord", Fields, 0);
            AddSysLog(data.Id.ToString(), "TradeRecord", "add");
            db.SaveChanges();

            return "success";
        }

        #endregion

        #region 修改交易记录

        /// <summary>
        /// 增加或修改交易记录信息
        /// </summary>
        /// <returns></returns>
        public IActionResult Edit(string right, int Id = 0)
        {
            ViewBag.RightInfo = RightInfo;
            ViewBag.right = right;


            TradeRecord editData = db.TradeRecord.FirstOrDefault(m => m.Id == Id) ?? new TradeRecord();
            ViewBag.data = editData;
            return View();
        }

        #endregion

        #region 修改交易记录

        /// <summary>
        /// 增加或修改交易记录信息
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public string Edit(TradeRecord data)
        {
            Dictionary<string, object> Fields = new Dictionary<string, object>();


            Fields.Add("SeoTitle", data.SeoTitle);
            Fields.Add("SeoKeyword", data.SeoKeyword);
            Fields.Add("SeoDescription", data.SeoDescription);
            new AdminContentOther(_accessor.HttpContext, PublicFunction.MainTables).Edit("TradeRecord", Fields, data.Id);
            AddSysLog(data.Id.ToString(), "TradeRecord", "update");
            db.SaveChanges();

            return "success";
        }

        #endregion

        #region 删除交易记录信息

        /// <summary>
        /// 删除交易记录信息
        /// </summary>
        /// <returns></returns>
        public string Delete(string Id)
        {
            string[] idlist = Id.Split(new char[] { ',' });
            AddSysLog(Id, "TradeRecord", "del");
            foreach (string subid in idlist)
            {
                int id = int.Parse(subid);
                Dictionary<string, object> Fields = new Dictionary<string, object>();
                Fields.Add("Status", -1);
                new AdminContentOther(_accessor.HttpContext, PublicFunction.MainTables).Edit("TradeRecord", Fields, id);
            }
            db.SaveChanges();

            return "success";
        }

        #endregion

        #region 开启

        /// <summary>
        /// 开启
        /// </summary>
        /// <returns></returns>
        public string Open(string Id)
        {
            string[] idlist = Id.Split(new char[] { ',' });
            AddSysLog(Id, "TradeRecord", "open");
            foreach (string subid in idlist)
            {
                int id = int.Parse(subid);
                Dictionary<string, object> Fields = new Dictionary<string, object>();
                Fields.Add("Status", 1);
                new AdminContentOther(_accessor.HttpContext, PublicFunction.MainTables).Edit("TradeRecord", Fields, id);
            }
            db.SaveChanges();
            return "success";
        }

        #endregion

        #region 关闭

        /// <summary>
        /// 关闭
        /// </summary>
        /// <returns></returns>
        public string Close(string Id)
        {
            string[] idlist = Id.Split(new char[] { ',' });
            AddSysLog(Id, "TradeRecord", "close");
            foreach (string subid in idlist)
            {
                int id = int.Parse(subid);
                Dictionary<string, object> Fields = new Dictionary<string, object>();
                Fields.Add("Status", 0);
                new AdminContentOther(_accessor.HttpContext, PublicFunction.MainTables).Edit("TradeRecord", Fields, id);
            }
            db.SaveChanges();
            return "success";
        }

        #endregion

        #region 排序
        /// <summary>
        /// 排序
        /// </summary>
        /// <param name="Id"></param>
        public string Sort(int Id, int Sort)
        {
            new AdminContentOther(_accessor.HttpContext, PublicFunction.MainTables).Sort("TradeRecord", Sort, Id);

            AddSysLog(Id.ToString(), "TradeRecord", "sort");
            return "success";
        }
        #endregion



        #region 导入数据
        public IActionResult Import(string right)
        {
            ViewBag.RightInfo = RightInfo;
            ViewBag.right = right;
            return View();
        }
        /// <summary>
        /// 导入数据
        /// </summary>
        /// <param name="ExcelData"></param>
        public string ImportPost(string ExcelPath)
        {
            string FullExcelPath = function.getPath(ExcelPath);
            FullExcelPath = FullExcelPath.Replace("//", "/");
            DataTable list = new PublicFunction().ExcelToDataTable(FullExcelPath);
            foreach (DataRow dr in list.Rows)
            {
                string OrderNo = dr["订单编码"].ToString();
                string CreateDate = dr["订单创建时间"].ToString();
                string PhoneNumber = dr["选择号码"].ToString();
                string HasFirstCharge = dr["是否有首充"].ToString();
                string FirstChargeAmount = dr["首充金额（元）"].ToString();
                string Province = dr["号码归属省份"].ToString();
                string City = dr["号码归属地市"].ToString();
                string ProductName = dr["商品名称"].ToString();
                string ActiveName = dr["促销活动名称"].ToString();
                string ProductType = dr["商品类型名称"].ToString();
                string ActualAmount = dr["实付金额(元)"].ToString();
                string OrderStatus = dr["订单状态"].ToString();
                string ActStatus = dr["激活状态名称"].ToString();
                string ActDate = dr["激活时间"].ToString();
                string WorkNo = dr["BOSS员工工号"].ToString();
                string AccountName = dr["账号姓名"].ToString();
                string SimNo = dr["SIM卡号"].ToString();

                // PosMerchantInfo checkMer = db.PosMerchantInfo.FirstOrDefault(m => m.KqMerNo == PhoneNumber && m.Field5 == ActStatus);
                // if (checkMer == null)
                // {
                string json = "{";
                json += "\"OrderNo\":\"" + OrderNo + "\",";
                json += "\"CreateDate\":\"" + CreateDate + "\",";
                json += "\"PhoneNumber\":\"" + PhoneNumber + "\",";
                json += "\"HasFirstCharge\":\"" + HasFirstCharge + "\",";
                json += "\"FirstChargeAmount\":\"" + FirstChargeAmount + "\",";
                json += "\"Province\":\"" + Province + "\",";
                json += "\"City\":\"" + City + "\",";
                json += "\"ProductName\":\"" + ProductName + "\",";
                json += "\"ActiveName\":\"" + ActiveName + "\",";
                json += "\"ProductType\":\"" + ProductType + "\",";
                json += "\"ActualAmount\":\"" + ActualAmount + "\",";
                json += "\"OrderStatus\":\"" + OrderStatus + "\",";
                json += "\"ActStatus\":\"" + ActStatus + "\",";
                json += "\"ActDate\":\"" + ActDate + "\",";
                json += "\"WorkNo\":\"" + WorkNo + "\",";
                json += "\"AccountName\":\"" + AccountName + "\",";
                json += "\"SimNo\":\"" + SimNo + "\"";
                json += "}";

                spdb.BindRecord.Add(new SpModels.BindRecord()
                {
                    CreateDate = DateTime.Parse(CreateDate),
                    UpdateTime = DateTime.Parse(CreateDate), //机具绑定、解绑时间
                    CreateTime = DateTime.Parse(CreateDate), //商户操作时间
                    MerNewSnNo = PhoneNumber, //商户手机号
                    MerSnNo = SimNo, //序列号
                    MerName = AccountName, //商户名称
                    MerNo = PhoneNumber, //商户编号
                    Remark = ProductType,
                    Field1 = Province,
                    Field2 = ActiveName,
                    Field3 = ProductName,
                    Field4 = WorkNo,
                    Field5 = ActStatus,
                    SeoTitle = ActualAmount,
                    SeoKeyword = ActDate,
                    SeoDescription = json,
                    ProductType = "14",
                    Status = 1,
                });

                spdb.Merchants.Add(new SpModels.Merchants()
                {
                    SnNo = SimNo,
                    CreateTime = DateTime.Parse(CreateDate),
                    UpdateTime = DateTime.Parse(CreateDate),
                    AgentName = AccountName,
                    MerRealName = AccountName,
                    MerMobile = PhoneNumber,
                    MerNo = PhoneNumber,
                    MerName = AccountName,
                    ProductType = "14",
                    Status = 1,
                });
                // }

                // spdb.TradeRecord.Add(new SpModels.TradeRecord()
                // {
                //     CreateDate = DateTime.Parse(CreateDate), //创建时间
                //     UpdateDate = DateTime.Parse(ActDate), //激活时间
                //     BankCardNo = PhoneNumber, //卡号（掩码）
                //     TradeSerialNo = OrderNo, //订单编号
                //     TradeAmount = decimal.Parse(ActualAmount), //交易金额
                //     TradeSnNo = SimNo, //序列号
                //     AgentNo = PhoneNumber, //一级服务商编号
                //     MerNo = PhoneNumber, //商户编
                //     TradeStatus = OrderStatus, //订单状态
                //     ChannelSerial = OrderNo, //请求号
                //     Field1 = OrderSource,
                //     Field2 = HasFirstCharge,
                //     Field3 = FirstChargeAmount,
                //     Field4 = ProductType,
                //     Field5 = ActiveName,
                //     SerEntryMode = ActStatus,
                //     ProductType = "14",
                //     Status = 1,
                // });

                spdb.SaveChanges();
            }
            AddSysLog("0", "TradeRecord", "Import");
            return "success";
        }
        #endregion

        #region 导入交易数据
        public IActionResult ImportTrade(string right, string ExcelKind)
        {
            ViewBag.RightInfo = RightInfo;
            ViewBag.right = right;
            ViewBag.ExcelKind = ExcelKind;
            return View();
        }
        /// <summary>
        /// 导入数据
        /// </summary>
        /// <param name="ExcelData"></param>
        public string ImportTradePost(string ExcelPath)
        {
            int DoCount = 0;
            string FullExcelPath = function.getPath(ExcelPath);
            FullExcelPath = FullExcelPath.Replace("//", "/");
            DataTable list = new PublicFunction().ExcelToDataTable(FullExcelPath);
            foreach (DataRow dr in list.Rows)
            {
                string Mobile = dr["用户手机号"].ToString();
                string SimNo = dr["SIM卡号"].ToString();
                string CreateMonth = dr["入网月份"].ToString();
                string SettleMonth = dr["结算月份"].ToString();
                string SettleTime = dr["结算账期"].ToString();
                string SettleSpace = dr["结算场景"].ToString();
                string BusinessType = dr["业务类型"].ToString();
                string Province = dr["用户省份"].ToString();
                string City = dr["用户地市"].ToString();
                string Team = dr["团队归属"].ToString();
                string WorkName = dr["行销宝姓名"].ToString();
                string WorkNo = dr["行销宝工号"].ToString();
                string ProductName = dr["商品名称"].ToString();
                string TotalAmount = dr["账单总金额"].ToString();
                string InAmount = dr["套内金额"].ToString();
                string OutAmount = dr["套外金额"].ToString();
                string UserStatus = dr["用户状态"].ToString();
                string ChannelCode = dr["渠道编码"].ToString();
                string ChannelName = dr["渠道名称"].ToString();
                string OrderCreateTime = dr["订单创建时间"].ToString();
                string OrderStatus = dr["订单状态"].ToString();
                string ActTime = dr["激活时间"].ToString();
                string SourceProductName = dr["原订单商品名称"].ToString();
                string ProductType = dr["商品类型名称"].ToString();
                string ActivityName = dr["促销活动名称"].ToString();
                string Percent = dr["分成比例"].ToString();
                string OrgDivi = dr["机构分成"].ToString();

                string json = "{";
                json += "\"Mobile\":\"" + Mobile + "\",";
                json += "\"SimNo\":\"" + SimNo + "\",";
                json += "\"CreateMonth\":\"" + CreateMonth + "\",";
                json += "\"SettleMonth\":\"" + SettleMonth + "\",";
                json += "\"SettleTime\":\"" + SettleTime + "\",";
                json += "\"SettleSpace\":\"" + SettleSpace + "\",";
                json += "\"BusinessType\":\"" + BusinessType + "\",";
                json += "\"Province\":\"" + Province + "\",";
                json += "\"City\":\"" + City + "\",";
                json += "\"Team\":\"" + Team + "\",";
                json += "\"WorkName\":\"" + WorkName + "\",";
                json += "\"WorkNo\":\"" + WorkNo + "\",";
                json += "\"ProductName\":\"" + ProductName + "\",";
                json += "\"TotalAmount\":\"" + TotalAmount + "\",";
                json += "\"InAmount\":\"" + InAmount + "\",";
                json += "\"OutAmount\":\"" + OutAmount + "\",";
                json += "\"UserStatus\":\"" + UserStatus + "\",";
                json += "\"ChannelCode\":\"" + ChannelCode + "\",";
                json += "\"ChannelName\":\"" + ChannelName + "\",";
                json += "\"OrderCreateTime\":\"" + OrderCreateTime + "\",";
                json += "\"OrderStatus\":\"" + OrderStatus + "\",";
                json += "\"ActTime\":\"" + ActTime + "\",";
                json += "\"SourceProductName\":\"" + SourceProductName + "\",";
                json += "\"ProductType\":\"" + ProductType + "\",";
                json += "\"ActivityName\":\"" + ActivityName + "\",";
                json += "\"Percent\":\"" + Percent + "\",";
                json += "\"OrgDivi\":\"" + OrgDivi + "\"";
                json += "}";

                bool op = true;
                string OrderNo = DateTime.Now.ToString("yyyyMMddHHmmssfff") + function.get_Random(8);
                decimal TotalAmountNum = decimal.Parse(function.CheckNum(numberString: TotalAmount));
                DateTime CreateDate = DateTime.Parse(OrderCreateTime);
                string CheckDate = DateTime.Now.AddMonths(-3).ToString("yyyyMM");
                if (int.Parse(CreateMonth) >= int.Parse(CheckDate)) op = false;
                if (OrderStatus == "待激活") op = false;
                if (UserStatus.Contains("注销") || UserStatus.Contains("欠费"))
                {
                    op = false;
                    SimposMerchantInfo checkSimMer = db.SimposMerchantInfo.FirstOrDefault(m => m.KqMerNo == Mobile && m.KqSnNo == SimNo);
                    if (checkSimMer != null)
                    {
                        checkSimMer.Status = -1;
                        if (UserStatus.Contains("欠费") && checkSimMer.Status != -1)
                        {
                            checkSimMer.Status = -2;
                        }
                        db.SaveChanges();
                    }
                    PosMerchantInfo checkMer = db.PosMerchantInfo.FirstOrDefault(m => m.KqMerNo == Mobile && m.KqSnNo == SimNo);
                    if (checkMer != null)
                    {
                        checkMer.Status = -1;
                        if (UserStatus.Contains("欠费") && checkMer.Status != -1)
                        {
                            checkMer.Status = -2;
                        }
                        db.SaveChanges();
                    }
                    PosMachinesTwo pos = db.PosMachinesTwo.FirstOrDefault(m => m.PosSn == SimNo);
                    if (pos != null)
                    {
                        pos.Status = -1;
                        if (UserStatus.Contains("欠费") && pos.Status != -1)
                        {
                            pos.Status = -2;
                        }
                        db.SaveChanges();
                    }
                }
                if (UserStatus.Contains("正常"))
                {
                    SimposMerchantInfo checkSimMer = db.SimposMerchantInfo.FirstOrDefault(m => m.KqMerNo == Mobile && m.KqSnNo == SimNo);
                    if (checkSimMer != null)
                    {
                        if (checkSimMer.Status == -2) checkSimMer.Status = 0;
                        db.SaveChanges();
                    }
                    PosMerchantInfo checkMer = db.PosMerchantInfo.FirstOrDefault(m => m.KqMerNo == Mobile && m.KqSnNo == SimNo);
                    if (checkMer != null)
                    {
                        if (checkMer.Status == -2) checkMer.Status = 1;
                        db.SaveChanges();
                    }

                    PosMachinesTwo pos = db.PosMachinesTwo.FirstOrDefault(m => m.PosSn == SimNo);
                    if (pos != null && pos.Status < 0)
                    {
                        if (pos.Status == -2)
                        {
                            pos.Status = 0;
                        }
                        db.SaveChanges();
                    }
                }

                if (TotalAmountNum > 0 && op)
                {
                    spdb.TradeRecord.Add(new SpModels.TradeRecord()
                    {
                        CreateDate = DateTime.Parse(DateTime.Now.AddMonths(-1).ToString("yyyy-MM-20 HH:mm:ss")), //创建时间
                        TradeSerialNo = OrderNo, //订单编号
                        AgentNo = WorkName + WorkNo, //一级服务商编号
                        MerNo = Mobile, //商户编
                        TradeSnNo = SimNo, //sim卡号
                        ChannelSerial = OrderNo, //请求号
                        TradeAmount = TotalAmountNum, //账单总金额
                        TradeStatus = OrderStatus, //订单状态
                        TradeDate = CreateMonth,
                        TradeTime = CreateDate.ToString("yyyyMMdd"),
                        BankCardType = "01",
                        TradeType = "01",
                        ProductType = "14",
                        Status = 1,
                        Field1 = ProductName,
                        Field2 = SourceProductName,
                        Field3 = ProductType,
                        Field4 = BusinessType,
                        SeoDescription = json,
                    });

                    spdb.SaveChanges();

                    DoCount += 1;
                }
            }
            AddSysLog("0", "TradeRecord", "Import");
            return "success|" + DoCount;
        }
        #endregion


        #region 通过队列导入数据
        /// <summary>
        /// 通过队列导入数据
        /// </summary>
        /// <param name="ExcelPath"></param>
        [HttpPost]
        public string ImportByQueuePost(string ExcelPath, int Kind = 0)
        {
            string key = function.MD5_16(Guid.NewGuid().ToString());
            RedisDbconn.Instance.AddList("SIMPosMerchantTradeQueue", ExcelPath + "#cut#" + Kind + "#cut#" + key + "#cut#" + SysUserName);
            return "success|" + key;
        }
        public string CheckImport(string key)
        {
            string result = RedisDbconn.Instance.Get<string>("SIMPosMerchantTradeCheckImport:" + key);
            if (!string.IsNullOrEmpty(result))
            {
                string[] datalist = result.Split('|');
                if (datalist[0] == "success")
                {
                    return result;
                }
                return datalist[0];
            }
            return "0";
        }
        public Dictionary<string, object> CheckImportV2(string key)
        {
            Dictionary<string, object> Obj = new Dictionary<string, object>();
            string result = RedisDbconn.Instance.Get<string>("SIMPosMerchantTradeCheckImport:" + key);
            if (!string.IsNullOrEmpty(result))
            {
                string[] datalist = result.Split('|');
                if (datalist[0] == "success")
                {
                    List<string> errList = RedisDbconn.Instance.GetList<string>("ErrList" + key);
                    if (errList.Count > 0)
                    {
                        Obj.Add("status", 2);
                        Obj.Add("errList", errList);
                    }
                    else
                    {
                        Obj.Add("status", 1);
                        Obj.Add("data", result);
                    }
                    return Obj;
                }
                Obj.Add("status", 0);
                Obj.Add("data", datalist[0]);
                return Obj;
            }
            Obj.Add("status", -1);
            Obj.Add("data", "执行中...");
            return Obj;
        }
        #endregion

        #region 导出Excel

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <returns></returns>
        public JsonResult ExportExcel(TradeRecord data, string MerchantNo, string MerchantName, string MakerCode, string RealName, string MerMakerCode, string MerRealName, string BankCardTypeSelect, string MerBuddyTypeSelect)
        {
            Dictionary<string, string> Fields = new Dictionary<string, string>();

            Fields.Add("BrandId", "1");
            Fields.Add("MerNo", "2"); //快钱商户编号
            Fields.Add("SnNo", "2"); //快钱SN号
            Fields.Add("TradeDate", "3"); //交易日期
            Fields.Add("TopUserId", "0"); //顶级创客

            string condition = " and Status>-1";
            //商户编号
            if (!string.IsNullOrEmpty(MakerCode))
            {
                condition += " and MerchantId in (select MerchantId from MerchantForCode where Code='" + MerchantNo + "')";
            }
            //商户名称
            if (!string.IsNullOrEmpty(RealName))
            {
                condition += " and MerchantId in (select MerchantId from MerchantForName where Name='" + MerchantName + "')";
            }
            //创客编号
            if (!string.IsNullOrEmpty(MakerCode))
            {
                condition += " and UserId in (select UserId from UserForMakerCode where MakerCode='" + MakerCode + "')";
            }
            //创客名称
            if (!string.IsNullOrEmpty(RealName))
            {
                condition += " and UserId in (select UserId from UserForRealName where RealName='" + RealName + "')";
            }
            //商户创客编号
            if (!string.IsNullOrEmpty(MerMakerCode))
            {
                condition += " and MerUserId in (select UserId from UserForMakerCode where MakerCode='" + MerMakerCode + "')";
            }
            //商户创客名称
            if (!string.IsNullOrEmpty(MerRealName))
            {
                condition += " and MerUserId in (select UserId from UserForRealName where RealName='" + MerRealName + "')";
            }
            //银行卡类型
            if (!string.IsNullOrEmpty(BankCardTypeSelect))
            {
                condition += " and BankCardType=" + BankCardTypeSelect;
            }
            //商户创客类型
            if (!string.IsNullOrEmpty(MerBuddyTypeSelect))
            {
                condition += " and MerBuddyType=" + MerBuddyTypeSelect;
            }

            Dictionary<string, object> obj = new AdminContentOther(_accessor.HttpContext, PublicFunction.MainTables).IndexData("TradeRecord", Fields, "Id desc", "0", 1, 20000, condition, "UserId,TopUserId,MerchantId,MerUserId,MerNo,MerBuddyType,SnNo,MerHelpFlag,HelpMonthCount,TradeDate,TradeSerialNo,TradeAmount,BankCardType,CapFlag,QrPayFlag,DirectFlag", false);
            List<Dictionary<string, object>> diclist = obj["data"] as List<Dictionary<string, object>>;
            foreach (Dictionary<string, object> dic in diclist)
            {
                dic["MerHelpFlag"] = dic["MerHelpFlag"].ToString() == "1" ? "是" : "否";
                dic["CapFlag"] = dic["CapFlag"].ToString() == "1" ? "封顶" : "非封顶";
                dic["QrPayFlag"] = dic["QrPayFlag"].ToString() == "1" ? "云闪付" : "非云闪付";
                dic["DirectFlag"] = dic["DirectFlag"].ToString() == "1" ? "直营" : "非直营";
                //直属创客
                int UserId = int.Parse(dic["UserId"].ToString());
                Users puser = db.Users.FirstOrDefault(m => m.Id == UserId) ?? new Users();
                dic["MakerCode"] = puser.MakerCode;
                dic["RealName"] = puser.RealName;
                //顶级创客
                int TopUserId = int.Parse(dic["TopUserId"].ToString());
                Users tuser = db.Users.FirstOrDefault(m => m.Id == TopUserId) ?? new Users();
                dic["TopMakerCode"] = tuser.MakerCode;
                dic["TopRealName"] = tuser.RealName;
                //商户创客
                int MerUserId = int.Parse(dic["MerUserId"].ToString());
                Users muser = db.Users.FirstOrDefault(m => m.Id == MerUserId) ?? new Users();
                dic["MerMakerCode"] = muser.MakerCode;
                dic["MerRealName"] = muser.RealName;
                //商户
                int MerchantId = int.Parse(dic["MerchantId"].ToString());
                MerchantInfo merchant = db.MerchantInfo.FirstOrDefault(m => m.Id == MerchantId) ?? new MerchantInfo();
                dic["MerchantNo"] = "";
                dic["MerchantName"] = merchant.Name;
                //商户创客类型
                int MerBuddyType = int.Parse(dic["MerBuddyType"].ToString());
                if (MerBuddyType == 0) dic["MerBuddyType"] = "非商户型创客";
                if (MerBuddyType == 1) dic["MerBuddyType"] = "商户型创客";
                //银行卡类型
                int BankCardType = int.Parse(dic["BankCardType"].ToString());
                if (BankCardType == 0) dic["BankCardType"] = "贷记卡";
                if (BankCardType == 1) dic["BankCardType"] = "借记卡";
                dic.Remove("UserId");
                dic.Remove("TopUserId");
                dic.Remove("MerUserId");
                dic.Remove("MerchantId");
            }

            Dictionary<string, object> result = new Dictionary<string, object>();
            result.Add("Status", "1");
            result.Add("Info", "Excel报表-" + DateTime.Now.ToString("yyyy-MM-ddTHH:mm:ss") + ".xlsx");
            result.Add("Obj", diclist);
            Dictionary<string, object> ReturnFields = new Dictionary<string, object>();
            ReturnFields.Add("MakerCode", "创客编码");
            ReturnFields.Add("RealName", "创客姓名");
            ReturnFields.Add("TopMakerCode", "顶级创客编码");
            ReturnFields.Add("TopRealName", "顶级创客名称");
            ReturnFields.Add("MerchantNo", "商户编号");
            ReturnFields.Add("MerchantName", "商户名称");
            ReturnFields.Add("MerMakerCode", "商户直属创客编号");
            ReturnFields.Add("MerRealName", "商户直属创客姓名");
            ReturnFields.Add("MerNo", "快钱商户编号");
            ReturnFields.Add("MerBuddyType", "商户创客类型");
            ReturnFields.Add("SnNo", "快钱SN号");
            ReturnFields.Add("MerHelpFlag", "是否属于扶持周期内商户");
            ReturnFields.Add("HelpMonthCount", "扶持第几个月");
            ReturnFields.Add("TradeDate", "交易日期");
            ReturnFields.Add("TradeSerialNo", "交易流水号");
            ReturnFields.Add("TradeAmount", "交易金额(元)");
            ReturnFields.Add("BankCardType", "银行卡类型");
            ReturnFields.Add("CapFlag", "借记卡封顶交易标志");
            ReturnFields.Add("QrPayFlag", "云闪付标识");
            ReturnFields.Add("DirectFlag", "是否直营");
            result.Add("Fields", ReturnFields);
            AddSysLog("0", "TradeRecord", "ExportExcel");
            return Json(result);
        }

        #endregion

        #region 获取激活信息预览

        public IActionResult IndexDataSIM(string right, string DateStr)
        {
            ViewBag.RightInfo = RightInfo;
            ViewBag.right = right;
            ViewBag.DateStr = DateStr;

            return View();
        }

        #endregion


        #region 获取激活信息预览

        public IActionResult IndexDataSIMDo(string DateStr)
        {
            List<Dictionary<string, object>> dataList = new List<Dictionary<string, object>>();
            Dictionary<string, object> dataInfo = new Dictionary<string, object>();
            var result = RadioCardHelper.Instance.QueryCardOrderInfo(DateStr, DateStr, "1", "99999");
            result = result.Replace("null", "\"\"");
            JsonData jsonObj = JsonMapper.ToObject(result);
            if (jsonObj["resultCode"].ToString() == "1")
            {
                var data = jsonObj["data"]["records"];
                for (int i = 0; i < data.Count; i++)
                {
                    dataInfo.Add("OrderNo", data[i]["orderNo"].ToString()); //订单编码
                    dataInfo.Add("CreateDate", data[i]["orderCreateTime"].ToString()); //订单创建时间
                    dataInfo.Add("PhoneNumber", data[i]["phoneNo"].ToString()); //选择号码
                    dataInfo.Add("HasFirstCharge", data[i]["firstCharge"].ToString()); //是否有首充
                    dataInfo.Add("FirstChargeAmount", data[i]["initialChargeAmount"].ToString()); //首充金额（元）
                    dataInfo.Add("Province", data[i]["province"].ToString()); //号码归属省份
                    dataInfo.Add("City", data[i]["city"].ToString()); //号码归属地市
                    dataInfo.Add("ProductName", data[i]["productName"].ToString()); //商品名称
                    dataInfo.Add("ActiveName", data[i]["promotionName"].ToString()); //促销活动名称
                    dataInfo.Add("ProductType", data[i]["productTypeName"].ToString()); //商品类型名称
                    dataInfo.Add("ActualAmount", data[i]["paidInAmount"].ToString()); //实付金额(元)
                    dataInfo.Add("OrderStatus", data[i]["orderStatus"].ToString()); //订单状态
                    dataInfo.Add("ActStatus", data[i]["activationStatusName"].ToString()); //激活状态名称
                    dataInfo.Add("ActDate", data[i]["activationTime"].ToString()); //激活时间
                    dataInfo.Add("WorkNo", data[i]["jobId"].ToString()); //BOSS员工工号
                    dataInfo.Add("AccountName", ""); //账号姓名
                    dataInfo.Add("SimNo", data[i]["iccId"].ToString()); //SIM卡号
                    dataList.Add(dataInfo);
                }
            }

            Dictionary<string, object> obj = new Dictionary<string, object>();
            obj.Add("code", 0);
            obj.Add("msg", "");
            obj.Add("count", dataList.Count);
            obj.Add("data", dataList);

            AddSysLog("0", "TradeRecord", "PreviewGetSimTrade");
            return Json(obj);
        }
        #endregion


        #region 获取激活信息
        public IActionResult GetSimTrade(string right)
        {
            ViewBag.RightInfo = RightInfo;
            ViewBag.right = right;
            return View();
        }

        public string GetSimTradePost(string DateStr)
        {
            string check = RedisDbconn.Instance.Get<string>("CheckSIMActInfo:" + SysId);
            if (!string.IsNullOrEmpty(check))
            {
                return "请勿重复操作";
            }
            if (DateTime.Parse(DateStr) >= DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd")))
            {
                return "请输入早于今天的日期";
            }
            RedisDbconn.Instance.Set("CheckSIMActInfo:" + SysId, "1");
            RedisDbconn.Instance.SetExpire("CheckSIMActInfo:" + SysId, 10);
            RedisDbconn.Instance.AddList("SIMActInfo", DateStr);
            AddSysLog("0", "TradeRecord", "GetSimTrade");
            return "success";
        }
        #endregion



    }
}