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>
        /// <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 <> 7 ");
            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 ");
            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 <> 7 ");
            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)
            {
                selectFileds = " SUBSTR(DATE_FORMAT(CreateDate,'%Y%m%d%H'),9,2) AS TimeStr, ";
                groupFileds = " GROUP BY DATE_FORMAT(CreateDate,'%Y%m%d%H') ";
            }
            else
            {
                selectFileds = " DATE_FORMAT(CreateDate,'%Y-%m-%d') AS TimeStr, ";
                groupFileds = " GROUP BY DATE_FORMAT(CreateDate,'%Y%m%d') ";
            }

            builder.AppendFormat(@"
SELECT {0} COUNT(1) AS OrderCount,SUM(A.Income) AS Income,COUNT(DISTINCT A.UserId) AS UserCount,SUM(B.GoodCount) AS GoodCount
FROM rb_goods_order A LEFT JOIN (SELECT OrderId,COUNT(1) AS GoodCount FROM rb_goods_orderdetail GROUP BY OrderId)  AS B ON A.OrderId=B.OrderId
WHERE 1=1 AND OrderStatus>=2 AND OrderStatus<=5
", 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
            {
                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"));
            }
            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}={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();
            //            builder.AppendFormat(@"
            //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 {0}
            //GROUP BY A.GoodsId,A.GoodsName
            //ORDER BY COunt(1) DESC
            //LIMIT 100
            //", where);
            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}={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);
            }

            StringBuilder builder = new StringBuilder();
            //            builder.AppendFormat(@"
            //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 {0}
            //GROUP BY B.UserId,C.`Name`
            //ORDER BY COUNT(1) DESC
            //LIMIT 100
            //", where);
            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");
            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;
        }
    }
}
