using System;
using System.Collections.Generic;
using System.Text;
using Mall.Model.Entity.User;
using Mall.Model.Extend.User;
using System.Linq;
using Mall.Model.Extend.Statistics;
using Mall.Model.Query;
using Mall.Model.Extend.Product;

namespace Mall.Repository.User
{
    /// <summary>
    /// 商城统计
    /// </summary>
    public class MallStatisticsRepository : BaseRepository<MallIndexStatistics>
    {
        /// <summary>
        /// 获取粉象等级
        /// </summary>
        private RB_Distributor_FXGradeRepository distributor_FXGradeRepository = new RB_Distributor_FXGradeRepository();

        /// <summary>
        /// 获取商城的总订单和总用户
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public MallIndexStatistics GetMallUserAndOrderRepository(StatisticsQuery query)
        {
            MallIndexStatistics model = new MallIndexStatistics();
            //总订单数
            StringBuilder orderNum = new StringBuilder();
            orderNum.AppendFormat("SELECT COUNT(1) AS OrderCount FROM rb_goods_order WHERE OrderStatus IN(2,3,4,5,6) ");
            orderNum.AppendFormat(" AND {0}={1} ", nameof(RB_Goods_Order_Extend.TenantId), query.TenantId);
            orderNum.AppendFormat(" AND {0}={1} ", nameof(RB_Goods_Order_Extend.MallBaseId), query.MallBaseId);
            var orderTotal = ExecuteScalar(orderNum.ToString());
            if (orderTotal != null && Convert.ToInt32(orderTotal) > 0)
            {
                model.TotalOrderNum = Convert.ToInt32(orderTotal);
            }
            //总小程序用户
            StringBuilder user = new StringBuilder();
            user.AppendFormat(" SELECT COUNT(1) FROM rb_member_user WHERE 1=1 ");
            user.AppendFormat(" AND {0}={1} ", nameof(RB_Member_User_Extend.TenantId), query.TenantId);
            user.AppendFormat(" AND {0}={1} ", nameof(RB_Member_User_Extend.MallBaseId), query.MallBaseId);
            var userTotal = ExecuteScalar(user.ToString());
            if (userTotal != null && Convert.ToInt32(userTotal) > 0)
            {
                model.UserTotalNum += Convert.ToInt32(userTotal);
            }
            //总后台账号
            //StringBuilder employee = new StringBuilder();
            //employee.AppendFormat(" SELECT COUNT(1) FROM rb_employee WHERE 1=1 ");
            //employee.AppendFormat(" AND {0}={1} ", nameof(RB_Employee_Extend.TenantId), query.TenantId);
            //employee.AppendFormat(" AND {0}={1} ", nameof(RB_Employee_Extend.MallBaseId), query.MallBaseId);
            //var employeeTotal = ExecuteScalar(employee.ToString());
            //if (employeeTotal != null && Convert.ToInt32(employeeTotal) > 0)
            //{
            //    model.UserTotalNum += Convert.ToInt32(employeeTotal);
            //}
            return model;
        }

        /// <summary>
        /// 订单数量、商品、用户统计
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public MallIndexStatistics MallIndexStatisticsRepository(StatisticsQuery query)
        {
            MallIndexStatistics model = new MallIndexStatistics();

            #region 用户统计
            StringBuilder user = new StringBuilder();
            user.AppendFormat(" SELECT COUNT(1) FROM rb_member_user WHERE 1=1 AND Id>20 ");
            user.AppendFormat(" AND {0}={1} ", nameof(RB_Member_User_Extend.TenantId), query.TenantId);
            user.AppendFormat(" AND {0}={1} ", nameof(RB_Member_User_Extend.MallBaseId), query.MallBaseId);
            if (query.Source > 0)
            {
                user.AppendFormat(" AND {0}={1} ", nameof(RB_Member_User_Extend.Source), (int)query.Source);
            }
            var userTotal = ExecuteScalar(user.ToString());
            if (userTotal != null && Convert.ToInt32(userTotal) > 0)
            {
                model.UserTotalNum = Convert.ToInt32(userTotal);
            }
            #endregion

            #region 商品统计
            StringBuilder good = new StringBuilder();
            good.Append(" SELECT COUNT(1) FROM rb_goods WHERE 1=1 AND Status=0 ");
            good.AppendFormat(" AND {0}={1} ", nameof(RB_Goods_Extend.TenantId), query.TenantId);
            good.AppendFormat(" AND {0}={1} ", nameof(RB_Goods_Extend.MallBaseId), query.MallBaseId);

            var goodTotal = ExecuteScalar(good.ToString());
            if (goodTotal != null && Convert.ToInt32(goodTotal) > 0)
            {
                model.GoodsTotalNum = Convert.ToInt32(goodTotal);
            }
            #endregion

            #region 订单数量统计
            StringBuilder orderNum = new StringBuilder();
            orderNum.AppendFormat("SELECT OrderStatus,COUNT(1) AS OrderCount FROM rb_goods_order WHERE OrderStatus IN(2,3,4,5,6) ");
            orderNum.AppendFormat(" AND {0}={1} ", nameof(RB_Goods_Order_Extend.TenantId), query.TenantId);
            orderNum.AppendFormat(" AND {0}={1} ", nameof(RB_Goods_Order_Extend.MallBaseId), query.MallBaseId);
            //订单来源
            if (query.Source > 0)
            {
                orderNum.AppendFormat(" AND {0}={1} ", nameof(RB_Goods_Order_Extend.OrderSource), (int)query.Source);
            }
            //开始时间
            if (query.StartDate != null && !string.IsNullOrWhiteSpace(query.StartDate))
            {
                orderNum.AppendFormat(" AND {0}>='{1}' ", nameof(RB_Goods_Order_Extend.CreateDate), query.StartDate);
            }
            //结束时间
            if (query.EndDate != null && !string.IsNullOrWhiteSpace(query.EndDate))
            {
                orderNum.AppendFormat(" AND {0}<='{1} 23:59:59' ", nameof(RB_Goods_Order_Extend.CreateDate), query.EndDate);
            }
            var orderCountList = Get<OrderNum>(orderNum.ToString()).ToList();

            if (orderCountList != null && orderCountList.Count > 0)
            {
                model.TotalOrderNum = orderCountList.Sum(qitem => qitem.OrderCount);
                model.NoSendOrderNum = orderCountList.Where(qitem => qitem.OrderStatus == Common.Enum.Goods.OrderStatusEnum.WaitSendGoods).FirstOrDefault()?.OrderCount ?? 0;
            }

            //维权订单【售后订单】
            StringBuilder afterSales = new StringBuilder();
            afterSales.AppendFormat(@"
SELECT COUNT(1) 
FROM rb_goods_orderaftersale AS A LEFT JOIN rb_goods_order AS B ON A.OrderId=B.OrderId 
WHERE 1=1 AND  A.ReOrderStatus IN(2,3,4) ");
            afterSales.AppendFormat(" AND A.{0}={1} ", nameof(RB_Goods_OrderAfterSale_Extend.TenantId), query.TenantId);
            afterSales.AppendFormat(" AND A.{0}={1} ", nameof(RB_Goods_OrderAfterSale_Extend.MallBaseId), query.MallBaseId);
            //订单来源
            if (query.Source > 0)
            {
                afterSales.AppendFormat(" AND B.{0}={1} ", nameof(RB_Goods_OrderAfterSale_Extend.OrderSource), (int)query.Source);
            }
            //开始时间
            if (query.StartDate != null && !string.IsNullOrWhiteSpace(query.StartDate))
            {
                afterSales.AppendFormat(" AND A.{0}>='{1}' ", nameof(RB_Goods_OrderAfterSale_Extend.CreateDate), query.StartDate);
            }
            //结束时间
            if (query.EndDate != null && !string.IsNullOrWhiteSpace(query.EndDate))
            {
                afterSales.AppendFormat(" AND A.{0}<='{1} 23:59:59' ", nameof(RB_Goods_OrderAfterSale_Extend.CreateDate), query.EndDate);
            }
            var afterSalesObj = ExecuteScalar(afterSales.ToString());
            if (afterSalesObj != null && Convert.ToInt32(afterSalesObj.ToString()) > 0)
            {
                model.ActivistOrderNum = Convert.ToInt32(afterSalesObj.ToString());
            }
            #endregion
            
            return model;
        }



        /// <summary>
        /// 销售情况统计
        /// </summary>
        /// <param name="query"></param>
        public List<MallSalesStatistics> MallIndexSalesStatisticsRepository(StatisticsQuery query)
        {
            StringBuilder builder = new StringBuilder();
            string selectFileds = "";
            string groupFileds = "";
            if (query.SalesTimeType == 1 || query.SalesTimeType == 0)
            {
                selectFileds = " SUBSTR(DATE_FORMAT(CreateDate,'%Y%m%d%H'),9,2) AS TimeStr, ";
                groupFileds = " GROUP BY DATE_FORMAT(CreateDate,'%Y%m%d%H'),A.UserId ";
            }
            else
            {
                selectFileds = " DATE_FORMAT(CreateDate,'%Y-%m-%d') AS TimeStr, ";
                groupFileds = " GROUP BY DATE_FORMAT(CreateDate,'%Y%m%d'),A.UserId ";
            }

            builder.AppendFormat(@"
SELECT {0} COUNT(1) AS OrderCount,SUM(A.Income) AS Income,A.UserId,SUM(B.GoodCount) AS GoodCount
FROM  rb_goods_order A LEFT JOIN (SELECT OrderId,SUM(Number) AS GoodCount FROM rb_goods_orderdetail GROUP BY OrderId)  AS B ON A.OrderId=B.OrderId
WHERE 1=1 AND OrderStatus IN(2,3,4,5,6)
", selectFileds);
            builder.AppendFormat(" AND {0}={1} ", nameof(RB_Goods_Order_Extend.TenantId), query.TenantId);
            builder.AppendFormat(" AND {0}={1} ", nameof(RB_Goods_Order_Extend.MallBaseId), query.MallBaseId);
            //订单来源
            if (query.Source > 0)
            {
                builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Goods_Order_Extend.OrderSource), (int)query.Source);
            }
            //开始时间
            if (query.SalesTimeType == 7)
            {
                builder.AppendFormat(" AND A.{0}>='{1}' ", nameof(RB_Goods_Order_Extend.CreateDate), DateTime.Now.AddDays(-7).ToString("yyyy-MM-dd"));
                builder.AppendFormat(" AND A.{0}<='{1} 23:59:59' ", nameof(RB_Goods_Order_Extend.CreateDate), DateTime.Now.ToString("yyyy-MM-dd"));
            }

            else if (query.SalesTimeType == 1)
            {
                builder.AppendFormat(" AND A.{0}>='{1}' AND A.{0}<='{1} 23:59:59' ", nameof(RB_Goods_Order_Extend.CreateDate), DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd"));
            }
            else if (query.SalesTimeType == 0)
            {
                builder.AppendFormat(" AND A.{0}>='{1}' AND A.{0}<='{1} 23:59:59' ", nameof(RB_Goods_Order_Extend.CreateDate), DateTime.Now.ToString("yyyy-MM-dd"));
            }
            builder.Append(groupFileds);
            var list = Get<MallSalesStatistics>(builder.ToString()).ToList();
            return list;
        }

        /// <summary>
        /// 商品购买力TOP排行
        /// </summary>
        /// <param name="query"></param>
        public List<MallSalesGoodsTop> MallIndesSalesIncomeStatisticsRepository(StatisticsQuery query)
        {
            string where = "";
            where += string.Format(" AND B.{0} IN (2,3,4,5,6) ", nameof(RB_Goods_Order_Extend.OrderStatus));
            where += string.Format(" AND B.{0}={1} ", nameof(RB_Goods_Order_Extend.TenantId), query.TenantId);
            where += string.Format(" AND B.{0}={1} ", nameof(RB_Goods_Order_Extend.MallBaseId), query.MallBaseId);
            //开始时间
            if (query.StartDate != null && !string.IsNullOrWhiteSpace(query.StartDate))
            {
                where += string.Format(" AND B.{0}>='{1}' ", nameof(RB_Goods_Order_Extend.CreateDate), query.StartDate);
            }
            //结束时间
            if (query.EndDate != null && !string.IsNullOrWhiteSpace(query.EndDate))
            {
                where += string.Format(" AND B.{0}<='{1} 23:59:59' ", nameof(RB_Goods_Order_Extend.CreateDate), query.EndDate);
            }

            StringBuilder builder = new StringBuilder();
            if (string.IsNullOrWhiteSpace(query.OrderBy))
            {
                query.OrderBy = "Income desc";
            }
            builder.AppendFormat(@$"SELECT * from (SELECT   A.GoodsName,Count(1) SaleCount,SUM(A.Final_Price) AS Income
FROM rb_goods_orderdetail AS A LEFT JOIN rb_goods_order AS B ON A.OrderId=B.OrderId
WHERE 1=1 AND A.GoodsId>0  {where}
GROUP BY A.GoodsId,A.GoodsName) as a ORDER BY a.{query.OrderBy}  LIMIT 100 ");

            var list = Get<MallSalesGoodsTop>(builder.ToString()).ToList();
            if (list != null && list.Count > 0)
            {
                int index = 1;
                foreach (var item in list)
                {
                    item.Num = index;
                    index++;
                }
            }
            return list;
        }


        /// <summary>
        /// 用户购买力TOP排行
        /// </summary>
        /// <param name="query"></param>
        public List<MallSalesUserTop> MallIndesSalesUserStatisticsRepository(StatisticsQuery query)
        {
            string where = "";
            where += string.Format(" AND B.{0} IN (2,3,4,5,6) ", nameof(RB_Goods_Order_Extend.OrderStatus));
            where += string.Format(" AND B.{0}={1} ", nameof(RB_Goods_Order_Extend.TenantId), query.TenantId);
            where += string.Format(" AND B.{0}={1} ", nameof(RB_Goods_Order_Extend.MallBaseId), query.MallBaseId);
            //开始时间
            if (query.StartDate != null && !string.IsNullOrWhiteSpace(query.StartDate))
            {
                where += string.Format(" AND B.{0}>='{1}' ", nameof(RB_Goods_Order_Extend.CreateDate), query.StartDate);
            }
            //结束时间
            if (query.EndDate != null && !string.IsNullOrWhiteSpace(query.EndDate))
            {
                where += string.Format(" AND B.{0}<='{1} 23:59:59' ", nameof(RB_Goods_Order_Extend.CreateDate), query.EndDate);
            }
            //订单来源
            if (query.Source > 0)
            {
                where += string.Format(" AND C.{0}={1} ", nameof(RB_Member_User_Extend.Source), (int)query.Source);
            }
            if (query.UserType > -1)
            {
                where += string.Format(" AND C.{0}={1} ", nameof(RB_Member_User_Extend.PostId), query.UserType);
            }

            StringBuilder builder = new StringBuilder();
            if (string.IsNullOrWhiteSpace(query.OrderBy))
            {
                query.OrderBy = "Income desc";
            }
//            builder.AppendFormat($@"
//SELECT * 
//FROM 
//    (   
//        SELECT  C.`Name` AS UserName,C.Photo,COUNT(1) SaleCount,SUM(A.Final_Price) AS Income
//        FROM rb_goods_orderdetail AS A LEFT JOIN rb_goods_order AS B ON A.OrderId=B.OrderId
//             LEFT JOIN rb_member_user AS C ON B.UserId=C.Id
//        WHERE 1=1 AND A.GoodsId>0 {where}
//        GROUP BY B.UserId,C.`Name`
//    ) as t ORDER BY t.{query.OrderBy}  LIMIT 100");

            builder.AppendFormat($@"
SELECT * 
FROM 
    (   
        SELECT  B.UserId, C.`Name` AS UserName,C.Photo,COUNT(1) SaleCount,SUM(A.Final_Price) AS Income
        FROM rb_goods_orderdetail AS A INNER JOIN RB_Goods_OrderCommission AS oc ON A.Id=oc.OrderDetailId AND A.OrderId=oc.OrderId
             INNER JOIN rb_goods_order AS B ON A.OrderId=B.OrderId
             LEFT JOIN rb_member_user AS C ON oc.UserId=C.Id
        WHERE 1=1 AND A.GoodsId>0  {where} 
        GROUP BY oc.UserId,C.`Name`
    ) as t ORDER BY t.{query.OrderBy}  LIMIT 100");
            var list = Get<MallSalesUserTop>(builder.ToString()).ToList();
            if (list != null && list.Count > 0)
            {
                int index = 1;
                foreach (var item in list)
                {
                    item.Num = index;
                    index++;
                }
            }
            return list;
        }


        /// <summary>
        /// 分销用户总计统计
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public object MallDistributorStatisticsRepository(StatisticsQuery query)
        {
            var fxGradeList = distributor_FXGradeRepository.GetList(new RB_Distributor_FXGrade_Extend()
            {
                MallBaseId = query.MallBaseId,
                TenantId = query.TenantId,
                Status = 0
            });
            string where = "";
            where += string.Format(" AND A.{0}={1} AND B.{0}={1} AND C.{0}={1} ", nameof(RB_Distributor_Info.TenantId), query.TenantId);
            where += string.Format(" AND A.{0}={1} AND B.{0}={1} AND C.{0}={1} ", nameof(RB_Distributor_Info.MallBaseId), query.MallBaseId);

            StringBuilder user = new StringBuilder();
            user.AppendFormat(@" 
SELECT B.FXGradeId,C.GradeName,COUNT(1) AS UserCount
FROM  rb_member_user AS A LEFT  JOIN rb_distributor_info AS B ON A.Id=B.UserId
      LEFT JOIN rb_distributor_fxgrade AS C ON  B.FXGradeId=C.Grade
WHERE  A.Id>19 AND B.`Status`=0  {0}
GROUP By B.FXGradeId,C.GradeName ", where);

            //总用户数量
            var tempTotalList = Get<MallDistributorTotal>(user.ToString()).ToList();
            int totalUserCount = 0;
            if (tempTotalList != null && tempTotalList.Count > 0)
            {
                totalUserCount = tempTotalList.Sum(qitem => qitem.UserCount);
            }
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@" 
SELECT B.FXGradeId,C.GradeName,COUNT(1) AS UserCount
FROM  rb_member_user AS A LEFT  JOIN rb_distributor_info AS B ON A.Id=B.UserId
      LEFT JOIN rb_distributor_fxgrade AS C ON  B.FXGradeId=C.Grade
WHERE  A.Id>19 AND B.`Status`=0  {0} AND DATE_FORMAT(A.CreateDate,'%Y-%m-%d')='{1}'
GROUP By B.FXGradeId,C.GradeName ", where, DateTime.Now.ToString("yyyy-MM-dd"));
            //今日用户数量
            var tempTodayList = Get<MallDistributorTotal>(builder.ToString()).ToList();

            List<MallDistributorTotal> totalList = new List<MallDistributorTotal>();
            List<MallDistributorTotal> todayList = new List<MallDistributorTotal>();
            foreach (var item in fxGradeList)
            {
                totalList.Add(new MallDistributorTotal()
                {
                    GradeName = item.GradeName,
                    UserCount = tempTotalList?.Where(qitem => qitem.FXGradeId == item.Grade)?.FirstOrDefault()?.UserCount ?? 0
                });
                todayList.Add(new MallDistributorTotal()
                {
                    GradeName = item.GradeName,
                    UserCount = tempTodayList?.Where(qitem => qitem.FXGradeId == item.Grade)?.FirstOrDefault()?.UserCount ?? 0
                });
            }
            var obj = new
            {
                totalUserCount,
                totalList = totalList.Select(qitem => new { qitem.GradeName, qitem.UserCount }),
                todayList = todayList.Select(qitem => new { qitem.GradeName, qitem.UserCount })
            };
            return obj;
        }

        /// <summary>
        /// 每日用户统计
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public object MallDistributorDayStatisticsRepository(int pageIndex, int pageSize, out long rowsCount, StatisticsQuery query)
        {
            string where = "";
            where += string.Format(" AND A.{0}={1} AND B.{0}={1} AND C.{0}={1} ", nameof(RB_Distributor_Info.TenantId), query.TenantId);
            where += string.Format(" AND A.{0}={1} AND B.{0}={1} AND C.{0}={1} ", nameof(RB_Distributor_Info.MallBaseId), query.MallBaseId);
            //订单来源
            if (query.Source > 0)
            {
                where += string.Format(" AND A.{0}={1} ", nameof(RB_Member_User_Extend.Source), (int)query.Source);
            }
            //开始时间
            if (query.StartDate != null && !string.IsNullOrWhiteSpace(query.StartDate))
            {
                where += string.Format(" AND A.{0}>='{1}' ", nameof(RB_Member_User_Extend.CreateDate), query.StartDate);
            }
            //结束时间
            if (query.EndDate != null && !string.IsNullOrWhiteSpace(query.EndDate))
            {
                where += string.Format(" AND A.{0}<='{1} 23:59:59' ", nameof(RB_Member_User_Extend.CreateDate), query.EndDate);
            }
            StringBuilder build = new StringBuilder();
            build.AppendFormat(@"
SELECT TabA.TimeStr
    ,MAX(CASE TabA.FXGradeId WHEN 1 THEN TabA.UserCount ELSE 0 END ) AS 'Pink' 
    ,MAX(CASE TabA.FXGradeId WHEN 2 THEN TabA.UserCount ELSE 0 END ) AS 'VIP' 
    ,MAX(CASE TabA.FXGradeId WHEN 3 THEN TabA.UserCount ELSE 0 END ) AS 'Silver' 
    ,MAX(CASE TabA.FXGradeId WHEN 4 THEN TabA.UserCount ELSE 0 END ) AS 'Gold' 
FROM
(
SELECT B.FXGradeId,C.GradeName,COUNT(1) AS UserCount,DATE_FORMAT(A.CreateDate,'%Y-%m-%d') AS TimeStr
FROM  rb_member_user AS A LEFT  JOIN rb_distributor_info AS B ON A.Id=B.UserId
      LEFT JOIN rb_distributor_fxgrade AS C ON  B.FXGradeId=C.Grade
WHERE A.Id>19 AND B.`Status`=0  {0} 
GROUP By B.FXGradeId,C.GradeName,DATE_FORMAT(A.CreateDate,'%Y%m%d')
) AS TabA
GROUP BY TabA.TimeStr
ORDER BY TabA.TimeStr DESC", where);
            List<object> resultList = new List<object>();
            var list = GetPage<MallDistributorDay>(pageIndex,pageSize,out rowsCount,build.ToString()).ToList();
            if (list != null && list.Count > 0)
            {
                foreach (var item in list)
                {
                    resultList.Add(new
                    {
                        TimeStr=Convert.ToDateTime(item.TimeStr).ToString("yyyy-MM-dd"),
                        item.Pink,
                        item.VIP,
                        item.Silver,
                        item.Gold
                    });
                }
            }
            return resultList;
        }
    }
}
