mp-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 + "')";
                // condition += " and UserId in (select Id from Users 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);
            //     string start = datelist[0];
            //     string end = datelist[1];
            //     condition += " and CreateDate>='" + start + " 00:00:00' and CreateDate<='" + end + " 23:59:59'";
            // }
            if (!string.IsNullOrEmpty(CreateDateData))
            {
                string[] datelist = CreateDateData.Split(new string[] { " - " }, StringSplitOptions.None);
                // string start = datelist[0];
                // string end = datelist[1];
                // condition += " and CreateDate>='" + start + " 00:00:00' and CreateDate<='" + end + " 23:59:59'";
                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
                    {
                        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 导入数据
        /// <summary>
        /// 导入数据
        /// </summary>
        /// <param name="ExcelData"></param>
        public string Import(string ExcelData)
        {
            ExcelData = HttpUtility.UrlDecode(ExcelData);
            JsonData list = JsonMapper.ToObject(ExcelData);
            for (int i = 1; i < list.Count; i++)
            {
                JsonData dr = list[i];

                db.TradeRecord.Add(new TradeRecord()
                {
                    CreateDate = DateTime.Now,
                    UpdateDate = DateTime.Now,

                });
                db.SaveChanges();
            }
            AddSysLog("0", "TradeRecord", "Import");
            return "success";
        }
        #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

    }
}