mp-ap/AppStart/Redis/Mysql/LargeDataScreenDbconn.cs

using System;
using System.Collections.Generic;
using Library;
using System.Linq;
using System.Data;
using MySystem.MainModels;

namespace MySystem
{
    public class LargeDataScreenDbconn
    {
        public readonly static LargeDataScreenDbconn Instance = new LargeDataScreenDbconn();


        #region 本月新增创客数
        public int GetTeamMakerCountThisMonth(int UserId)
        {
            DateTime start = DateTime.Parse(DateTime.Now.ToString("yyyy-MM") + "-01 00:00:00");
            DateTime end = start.AddMonths(1);
            WebCMSEntities db = new WebCMSEntities();
            string IdString = "," + UserId + ",";
            int MakerCount = db.Users.Count(m => m.ParentNav.Contains(IdString) && m.AuthDate >= start && m.AuthDate < end);
            db.Dispose();
            return MakerCount;
        }
        #endregion

        #region 上月新增创客数
        public int GetTeamMakerCountLastMonth(int UserId)
        {
            DateTime end = DateTime.Parse(DateTime.Now.ToString("yyyy-MM") + "-01 00:00:00");
            DateTime start = end.AddMonths(-1);
            WebCMSEntities db = new WebCMSEntities();
            string IdString = "," + UserId + ",";
            int MakerCount = db.Users.Count(m => m.ParentNav.Contains(IdString) && m.AuthDate >= start && m.AuthDate < end);
            db.Dispose();
            return MakerCount;
        }
        #endregion

        #region 团队新增创客
        public int GetNewUserCount(int UserId, string Day = "")
        {
            string key = "AddUser:" + UserId;
            if (!string.IsNullOrEmpty(Day))
            {
                key += ":" + Day;
            }
            if (RedisDbconn.Instance.Exists(key))
            {
                int obj = RedisDbconn.Instance.Get<int>(key);
                if (obj > 0)
                {
                    return obj;
                }
            }
            int ckcount = 0;
            string UserIdString = "," + UserId + ",";
            WebCMSEntities db = new WebCMSEntities();
            if (!string.IsNullOrEmpty(Day))
            {
                if (Day.Length == 8)
                {
                    DateTime start = DateTime.Parse(Day.Substring(0, 4) + "-" + Day.Substring(4, 2) + "-" + Day.Substring(6, 2) + " 00:00:00");
                    DateTime end = start.AddDays(1);
                    ckcount = db.Users.Count(m => m.ParentNav.Contains(UserIdString) && m.CreateDate >= start && m.CreateDate < end);
                }
                else
                {
                    DateTime start = DateTime.Parse(Day.Substring(0, 4) + "-" + Day.Substring(4, 2) + "-01 00:00:00");
                    DateTime end = start.AddDays(1);
                    ckcount = db.Users.Count(m => m.ParentNav.Contains(UserIdString) && m.CreateDate >= start && m.CreateDate < end);
                }
            }
            else
            {
                ckcount = db.Users.Count(m => m.ParentNav.Contains(UserIdString));
            }
            RedisDbconn.Instance.Set(key, ckcount);
            RedisDbconn.Instance.SetExpire(key, Library.function.get_Random(60, 180));
            db.Dispose();
            return ckcount;
        }
        #endregion


        #region 团队新增商户
        public int GetNewPosMerCount(int UserId, string Day = "")
        {
            string key = "AddPosMer:" + UserId;
            if (!string.IsNullOrEmpty(Day))
            {
                key += ":" + Day;
            }
            if (RedisDbconn.Instance.Exists(key))
            {
                int obj = RedisDbconn.Instance.Get<int>(key);
                if (obj > 0)
                {
                    return obj;
                }
            }
            int shcount = 0;
            string UserIdString = "," + UserId + ",";
            WebCMSEntities db = new WebCMSEntities();
            if (!string.IsNullOrEmpty(Day))
            {
                if (Day.Length == 8)
                {
                    DateTime start = DateTime.Parse(Day.Substring(0, 4) + "-" + Day.Substring(4, 2) + "-" + Day.Substring(6, 2) + " 00:00:00");
                    DateTime end = start.AddDays(1);
                    shcount = db.PosMerchantInfo.Count(m => m.CreateDate >= start && m.CreateDate < end);
                }
                else
                {
                    DateTime start = DateTime.Parse(Day.Substring(0, 4) + "-" + Day.Substring(4, 2) + "-01 00:00:00");
                    DateTime end = start.AddDays(1);
                    shcount = db.PosMerchantInfo.Count(m => m.CreateDate >= start && m.CreateDate < end);
                }
            }
            else
            {
                shcount = db.PosMerchantInfo.Count();
            }
            RedisDbconn.Instance.Set(key, shcount);
            RedisDbconn.Instance.SetExpire(key, Library.function.get_Random(60, 180));
            db.Dispose();
            return shcount;
        }
        #endregion

        #region 全国今日新增创客和商户
        public List<MakerShopAdds> GetMakerShopAddsList()
        {
            List<MakerShopAdds> Obj = RedisDbconn.Instance.GetList<MakerShopAdds>("MakerShopAddsList");
            if (Obj.Count > 0)
            {
                return Obj;
            }
            OtherMySqlConn.connstr = Library.ConfigurationManager.AppSettings["SqlConnStr"].ToString();
            DataTable dt = OtherMySqlConn.dtable("select (select sum(RecUserAuthNum) from PullnewSummary where SeoTitle='team' and UserId=1) ckcount,(select count(0) count from PosMerchantInfo) shcount from PullnewSummary,PosMerchantInfo limit 1");
            foreach (DataRow item in dt.Rows)
            {
                Obj.Add(new MakerShopAdds()
                {
                    shcount = Convert.ToInt32(item["shcount"]),
                    ckcount = Convert.ToInt32(item["ckcount"])
                });
            }
            foreach (var sub in Obj)
            {
                RedisDbconn.Instance.AddRightList("MakerShopAddsList", sub);
            }
            RedisDbconn.Instance.SetExpire("MakerShopAddsList", Library.function.get_Random(60, 180));
            dt.Dispose();
            return Obj;
        }
        #endregion

        #region 全国创客开机列表
        public List<MakerOpenMachines> GetMakerOpenMachinesList(string key, int pageNum = 1, int pageSize = 30)
        {
            List<MakerOpenMachines> Obj = RedisDbconn.Instance.GetList<MakerOpenMachines>("MakerOpenMachinesList: "+pageNum, 1, pageSize);
            if (Obj.Count > 0)
            {
                return Obj;
            }

            List<MakerOpenMachines> list = new List<MakerOpenMachines>();
            int Skip = pageSize * (pageNum - 1);
            OtherMySqlConn.connstr = Library.ConfigurationManager.AppSettings["SqlConnStr"].ToString();
            DataTable dt = OtherMySqlConn.dtable("select b.ActivationTime ActivationTime,k.NAME productName,a.RealName realname,c.count count from Users a left join PosMachinesTwo b on a.Id=b.userId LEFT JOIN KqProducts k ON k.Id=b.BrandId left join (select a.Id,count(a.Id) as count from Users a left join PosMachinesTwo b on a.Id=b.UserId LEFT JOIN KqProducts k ON k.Id=b.BrandId where BindingState=1 group by a.Id)c on a.Id=c.Id where BindingState=1 AND MONTH(ActivationTime)=MONTH(now()) and YEAR(ActivationTime)=YEAR(now())order by b.ActivationTime desc limit " + Skip + "," + pageSize + "");
            foreach (DataRow item in dt.Rows)
            {
                list.Add(new MakerOpenMachines()
                {
                    ActivationTime = Convert.ToDateTime(item["ActivationTime"]).ToString("yyyy-MM-dd HH:mm:ss"),
                    productName = item["productName"].ToString(),
                    realname = SetSensitiveName(item["RealName"].ToString()),
                    count = Convert.ToInt32(item["count"])
                });
            }
            list = list.OrderByDescending(m => m.ActivationTime).ToList();
            foreach (var sub in list)
            {
                RedisDbconn.Instance.AddRightList("MakerOpenMachinesList: "+pageNum, sub);
            }
            RedisDbconn.Instance.SetExpire("MakerOpenMachinesList: "+pageNum, Library.function.get_Random(60, 180));
            dt.Dispose();
            return list;
        }
        #endregion

        #region 全国展业数据
        public List<Exhibitions> GetExhibitionList()
        {
            List<Exhibitions> Obj = RedisDbconn.Instance.GetList<Exhibitions>("ExhibitionList");
            if (Obj.Count > 0)
            {
                return Obj;
            }
            OtherMySqlConn.connstr = Library.ConfigurationManager.AppSettings["SqlConnStr"].ToString();
            DataTable dt = OtherMySqlConn.dtable("select(select count(0) count from StoreHouse where YEAR(CreateDate)=YEAR(now())) count,(select Sum(TotalAmount) WithdrawAmount from UserAccount) WithdrawAmount,(select sum(NonDirectTradeAmt) WithdrawAmount from UserTradeDaySummary where UserId=1 and SeoTitle='team' and left(TradeMonth,4) = YEAR(NOW())) yearsum,(select sum(NonDirectDebitCapNum) from UserTradeDaySummary where UserId=1 and SeoTitle='team' and left(TradeMonth,4) = YEAR(NOW())) yearcount,(select sum(NonDirectTradeAmt) from UserTradeDaySummary where UserId=1 and SeoTitle='team' and quarter(TradeDate)=quarter(now())) quartersum, (select sum(NonDirectDebitCapNum) from UserTradeDaySummary where UserId=1 and SeoTitle='team' and quarter(TradeDate)=quarter(now())) quartercount,(select sum(NonDirectTradeAmt) WithdrawAmount from UserTradeDaySummary where UserId=1 and SeoTitle='team' and RIGHT(TradeMonth,2) = MONTH(NOW())) monthsum,(select sum(NonDirectDebitCapNum) WithdrawAmount from UserTradeDaySummary where UserId=1 and SeoTitle='team' and RIGHT(TradeMonth,2) = MONTH(NOW())) monthcount,(select sum(NonDirectTradeAmt) from UserTradeDaySummary where UserId=1 and SeoTitle='team' and TO_DAYS(TradeDate)=TO_DAYS(NOW() - INTERVAL 1 DAY)) nowsum, (select sum(NonDirectDebitCapNum) from UserTradeDaySummary where UserId=1 and SeoTitle='team' and TO_DAYS(TradeDate)=TO_DAYS(NOW() - INTERVAL 1 DAY)) nowcount from UserTradeDaySummary limit 1");
            foreach (DataRow item in dt.Rows)
            {
                Obj.Add(new Exhibitions()
                {
                    WithdrawAmount = decimal.Parse(function.CheckNum(item["WithdrawAmount"].ToString())),
                    count = Convert.ToInt32(function.CheckNum(item["count"].ToString())),
                    yearsum = decimal.Parse(function.CheckNum(item["yearsum"].ToString())),
                    yearcount = Convert.ToInt32(function.CheckNum(item["yearcount"].ToString())),
                    quartersum = decimal.Parse(function.CheckNum(item["quartersum"].ToString())),
                    quartercount = Convert.ToInt32(function.CheckNum(item["quartercount"].ToString())),
                    monthsum = decimal.Parse(function.CheckNum(item["monthsum"].ToString())),
                    monthcount = Convert.ToInt32(function.CheckNum(item["monthcount"].ToString())),
                    nowsum = decimal.Parse(function.CheckNum(item["nowsum"].ToString())),
                    nowcount = Convert.ToInt32(function.CheckNum(item["nowcount"].ToString()))
                });
            }
            foreach (var sub in Obj)
            {
                RedisDbconn.Instance.AddRightList("ExhibitionList", sub);
            }
            RedisDbconn.Instance.SetExpire("ExhibitionList", Library.function.get_Random(60, 180));
            dt.Dispose();
            return Obj;
        }
        #endregion


        #region 团队累计开机榜排名前十
        public List<OpenMachines> GetOpenMachinesList(int UserId)
        {
            // string key = "OpenMachinesList:" + UserId;
            // List<int> list = new List<int>();
            // if (RedisDbconn.Instance.Exists(key))
            // {
            //     list = RedisDbconn.Instance.GetList<int>(key, pageNum, pageSize);
            //     if (list.Count > 0)
            //     {
            //         return list;
            //     }
            // }
            // DateTime start = DateTime.Now.AddDays(1 - DateTime.Now.Day).Date;
            // DateTime end = DateTime.Now.AddDays(1 - DateTime.Now.Day).Date.AddMonths(1).AddSeconds(-1);
            // WebCMSEntities db = new WebCMSEntities();
            // List<int> newlist = new List<int>();
            // var mysqllist = db.PosMachinesTwo.Select(m => new { m.Id, m.UserId, m.BindingState, m.BindingTime, m.Status }).Where(m => m.Status > -1 && m.UserId == UserId && m.BindingState==1 && m.BindingTime >= start && m.BindingTime <= end).ToList();
            // if (mysqllist.Count > 0)
            // {
            //     foreach (var sub in mysqllist)
            //     {
            //         newlist.Add(sub.Id);
            //     }
            //     RedisDbconn.Instance.Clear(key);
            //     foreach (var sub in newlist)
            //     {
            //         RedisDbconn.Instance.AddRightList(key, sub);
            //     }
            //     RedisDbconn.Instance.SetExpire(key, Library.function.get_Random(60, 180));
            // }
            // db.Dispose();

            List<OpenMachines> Obj = RedisDbconn.Instance.GetList<OpenMachines>("OpenMachinesList:" + UserId);
            if (Obj.Count > 0)
            {
                return Obj;
            }
            OtherMySqlConn.connstr = Library.ConfigurationManager.AppSettings["SqlConnStr"].ToString();
            DataTable dt = OtherMySqlConn.dtable("select a.UserId UserId,b.RealName RealName,a.sum Sum from(select UserId, sum(NonDirectDebitCapNum) sum from UserTradeDaySummary where SeoTitle='team' and UserId in (select Id from Users where ParentNav like '%," + UserId + ",%')GROUP BY UserId ORDER BY sum desc limit 10)a left join Users b on a.UserId = b.Id");//累计数据统计
            foreach (DataRow item in dt.Rows)
            {
                Obj.Add(new OpenMachines()
                {
                    userId = Convert.ToInt32(item["UserId"]),
                    realName = SetSensitiveName(item["RealName"].ToString()),
                    openCount = decimal.Parse(item["Sum"].ToString())
                });
            }
            Obj = Obj.OrderByDescending(m => m.openCount).ToList();
            foreach (var sub in Obj)
            {
                RedisDbconn.Instance.AddRightList("OpenMachinesList:" + UserId, sub);
            }
            RedisDbconn.Instance.SetExpire("OpenMachinesList:" + UserId, Library.function.get_Random(60, 180));
            dt.Dispose();
            return Obj;
        }
        #endregion

        #region 团队累计流水榜排名前十
        public List<FlowingWater> GetFlowingWaterList(int UserId)
        {
            List<FlowingWater> Obj = RedisDbconn.Instance.GetList<FlowingWater>("FlowingWaterList:" + UserId);
            if (Obj.Count > 0)
            {
                return Obj;
            }
            OtherMySqlConn.connstr = Library.ConfigurationManager.AppSettings["SqlConnStr"].ToString();
            DataTable dt = OtherMySqlConn.dtable("select a.UserId UserId,b.RealName RealName,a.sum Sum from(select UserId, sum(NonDirectDebitTradeAmt + NonDirectTradeAmt) sum from UserTradeDaySummary where SeoTitle='team' and UserId in (select Id from Users where ParentNav like '%," + UserId + ",%')GROUP BY UserId ORDER BY sum desc limit 10)a left join Users b on a.UserId = b.Id");//累计数据统计
            foreach (DataRow item in dt.Rows)
            {
                Obj.Add(new FlowingWater()
                {
                    UserId = Convert.ToInt32(item["UserId"]),
                    RealName = SetSensitiveName(item["RealName"].ToString()),
                    Sum = decimal.Parse(item["Sum"].ToString())
                });
            }
            Obj = Obj.OrderByDescending(m => m.Sum).ToList();
            foreach (var sub in Obj)
            {
                RedisDbconn.Instance.AddRightList("FlowingWaterList:" + UserId, sub);
            }
            RedisDbconn.Instance.SetExpire("FlowingWaterList:" + UserId, Library.function.get_Random(60, 180));
            dt.Dispose();
            return Obj;
        }
        #endregion

        #region 累计收益榜排名前十
        public List<InCome> GetIncomeList()
        {
            List<InCome> Obj = RedisDbconn.Instance.GetList<InCome>("IncomeList");
            if (Obj.Count > 0)
            {
                return Obj;
            }
            OtherMySqlConn.connstr = Library.ConfigurationManager.AppSettings["SqlConnStr"].ToString();
            // DataTable dt = OtherMySqlConn.dtable("select c.RealName RealName,b.Sum Sum from(select a.UserId, sum(a.ChangeAmount) Sum from(select * from UserAccountRecord where ChangeType = 1 or ChangeType = 12 or ChangeType = 50 or ChangeType = 60 or ChangeType = 111 or ChangeType = 112)a where a.UserId != 0 GROUP BY a.UserId ORDER BY Sum desc limit 10)b left join Users c on b.UserId = c.Id");//累计数据统计
            DataTable dt = OtherMySqlConn.dtable("select b.RealName RealName,a.TotalAmount Sum from UserAccount a left join Users b on a.UserId=b.Id ORDER BY a.TotalAmount desc limit 10");
            foreach (DataRow item in dt.Rows)
            {
                Obj.Add(new InCome()
                {
                    RealName = SetSensitiveName(item["RealName"].ToString()),
                    Sum = decimal.Parse(item["Sum"].ToString())
                });
            }
            Obj = Obj.OrderByDescending(m => m.Sum).ToList();
            foreach (var sub in Obj)
            {
                RedisDbconn.Instance.AddRightList("IncomeList", sub);
            }
            RedisDbconn.Instance.SetExpire("IncomeList", Library.function.get_Random(60, 180));
            dt.Dispose();
            return Obj;
        }
        #endregion

        // #region 交易额统计-本年每月
        // public List<TradeYearMonth> GetTradeYearMonthList(int UserId)
        // {
        //     List<TradeYearMonth> Obj = RedisDbconn.Instance.GetList<TradeYearMonth>("TradeYearMonthList:" + UserId);
        //     if (Obj.Count > 0)
        //     {
        //         return Obj;
        //     }
        //     OtherMySqlConn.connstr = Library.ConfigurationManager.AppSettings["SqlConnStr"].ToString();
        //     DataTable dt = OtherMySqlConn.dtable("select year(a.CreateDate) year,month(a.CreateDate) month,count(*) count,sum(a.TradeAmount) sumamount from TradeRecord a where YEAR(a.CreateDate) = YEAR(NOW())group by year(a.CreateDate), month(a.CreateDate)order by month(a.CreateDate)");
        //     foreach (DataRow item in dt.Rows)
        //     {
        //         Obj.Add(new TradeYearMonth()
        //         {
        //             year = item["year"].ToString(),
        //             month = item["month"].ToString(),
        //             count = int.Parse(item["count"].ToString()),
        //             sumamount = decimal.Parse(item["sumamount"].ToString())
        //         });
        //     }
        //     foreach (var sub in Obj)
        //     {
        //         RedisDbconn.Instance.AddRightList("TradeYearMonthList:" + UserId, sub);
        //     }
        //     RedisDbconn.Instance.SetExpire("TradeYearMonthList:" + UserId, Library.function.get_Random(60, 180));
        //     dt.Dispose();
        //     return Obj;
        // }
        // #endregion

        #region 机型统计-机型占比统计
        public List<ModelType> GetModelTypeList(int UserId)
        {
            List<ModelType> Obj = RedisDbconn.Instance.GetList<ModelType>("ModelTypeList:" + UserId);
            if (Obj.Count > 0)
            {
                return Obj;
            }
            OtherMySqlConn.connstr = Library.ConfigurationManager.AppSettings["SqlConnStr"].ToString();
            DataTable dt = OtherMySqlConn.dtable("select b.Name name,a.count count from(select BrandId,count(0) count from PosMachinesTwo where UserId in(select Id from Users where ParentNav like '%," + UserId + ",%' or Id=" + UserId + ")GROUP BY BrandId)a left join KqProducts b on a.BrandId=b.Id");
            foreach (DataRow item in dt.Rows)
            {
                Obj.Add(new ModelType()
                {
                    name = item["name"].ToString(),
                    count = Convert.ToInt32(item["count"])
                });
            }
            foreach (var sub in Obj)
            {
                RedisDbconn.Instance.AddRightList("ModelTypeList:" + UserId, sub);
            }
            RedisDbconn.Instance.SetExpire("ModelTypeList:" + UserId, Library.function.get_Random(60, 180));
            dt.Dispose();
            return Obj;
        }
        #endregion

        #region 机型统计-交易额统计
        public List<ModelTypeTrade> GetModelTypeTradeList(int UserId)
        {
            List<ModelTypeTrade> Obj = RedisDbconn.Instance.GetList<ModelTypeTrade>("ModelTypeTradeList:" + UserId);
            if (Obj.Count > 0)
            {
                return Obj;
            }
            OtherMySqlConn.connstr = Library.ConfigurationManager.AppSettings["SqlConnStr"].ToString();
            DataTable dt = OtherMySqlConn.dtable("select b.Name name,a.sum sum from(select BrandId,sum(TradeAmount) sum from TradeRecord where UserId in (select Id from Users where ParentNav like '%," + UserId + ",%' or Id=" + UserId + ")GROUP BY BrandId)a left join KqProducts b on a.BrandId=b.Id");
            foreach (DataRow item in dt.Rows)
            {
                Obj.Add(new ModelTypeTrade()
                {
                    name = item["name"].ToString(),
                    sum = decimal.Parse(item["sum"].ToString())
                });
            }
            foreach (var sub in Obj)
            {
                RedisDbconn.Instance.AddRightList("ModelTypeTradeList:" + UserId, sub);
            }
            RedisDbconn.Instance.SetExpire("ModelTypeTradeList:" + UserId, Library.function.get_Random(60, 180));
            dt.Dispose();
            return Obj;
        }
        #endregion

        #region 交易额统计近一年
        public decimal GetMonthTrade(int UserId, string TradeMonth, string kind = "self")
        {
            decimal amt = 0;
            WebCMSEntities db = new WebCMSEntities();
            bool check = db.UserTradeDaySummary.Any(m => m.UserId == UserId && m.TradeMonth == TradeMonth && m.SeoTitle == kind);
            if (check)
            {
                if (kind == "self")
                {
                    amt = db.UserTradeDaySummary.Where(m => m.UserId == UserId && m.TradeMonth == TradeMonth && m.SeoTitle == kind).Sum(m => m.DirectTradeAmt + m.DirectDebitTradeAmt);
                }
                else if (kind == "team")
                {
                    amt = db.UserTradeDaySummary.Where(m => m.UserId == UserId && m.TradeMonth == TradeMonth && m.SeoTitle == kind).Sum(m => m.NonDirectTradeAmt + m.NonDirectDebitTradeAmt);
                }
            }
            db.Dispose();
            return amt;
        }
        #endregion


        #region 姓名脱敏
        /// <summary>
        /// 姓名敏感处理
        /// </summary>
        /// <param name="fullName">姓名</param>
        /// <returns>脱敏后的姓名</returns>
        public static string SetSensitiveName(string fullName)
        {
            if (string.IsNullOrEmpty(fullName)) return string.Empty;
            string familyName = fullName.Substring(0, 1);
            string end = fullName.Substring(fullName.Length - 1, 1);
            string name = string.Empty;
            //长度为2
            if (fullName.Length <= 2) name = familyName + "*";
            //长度⼤于2
            else if (fullName.Length >= 3)
            {
                name = familyName.PadRight(fullName.Length - 1, '*') + end;
            }
            return name;
        }
        #endregion

    }
}