using Mall.Model.Entity.Product;
using Mall.Model.Entity.Reserve;
using Mall.Model.Extend.Reserve;
using NPOI.SS.Formula.Functions;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Mall.Repository.Reserve
{
    public class RB_Reserve_ServicePersonalRepository : BaseRepository<RB_Reserve_ServicePersonal>
    {

        /// <summary>
        /// 表名称
        /// </summary>
        public string TableName { get { return nameof(RB_Reserve_ServicePersonal); } }



        /// <summary>
        ///服务人员信息列表
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <param name="rowCount">总条数</param>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public List<RB_Reserve_ServicePersonal_Extend> GetServicePersonalPageList(int pageIndex, int pageSize, out long rowCount, RB_Reserve_ServicePersonal_Extend query)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(@$" SELECT a.*,b.`Name` as  StoreName,c.OrderNum FROM {TableName} as A  LEFT JOIN rb_stores as b on a.StoreId=b.Id LEFT JOIN (SELECT  od.ServicepersonalId,COUNT(*) OrderNum FROM rb_goods_orderdetail od
INNER JOIN rb_goods_order o on o.OrderId=od.OrderId 
where o.OrderClassify=3 and o.OrderStatus in(2,3,4,5,6) 
GROUP BY od.ServicepersonalId) as c on a.id=c.ServicepersonalId WHERE  a.{nameof(RB_Reserve_ServicePersonal_Extend.Status)}=0");
            if (query != null)
            {
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.TenantId)}={query.TenantId}");
                }
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.MallBaseId)}={query.MallBaseId}");
                }
                if (query.ServiceStatus > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.ServiceStatus)}={query.ServiceStatus}");
                }
                if (query.StoreId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.StoreId)}={query.StoreId}");
                }
                if (!string.IsNullOrWhiteSpace(query.Name))
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.Name)} like '%{query.Name}%'");
                }
            }
            builder.Append($"  order by  a.{nameof(RB_Reserve_ServicePersonal_Extend.ID)} desc");
            return GetPage<RB_Reserve_ServicePersonal_Extend>(pageIndex, pageSize, out rowCount, builder.ToString()).ToList();
        }






        /// <summary>
        ///小程序端服务人员信息列表
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <param name="rowCount">总条数</param>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public List<RB_Reserve_ServicePersonal_Extend> GetMinServicePersonalList(int pageIndex, int pageSize, out long rowCount, RB_Reserve_ServicePersonal_Extend query)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(@$" SELECT a.*,b.CommentNum FROM {TableName} as A  
LEFT JOIN (SELECT ServicePersonalId,COUNT(*) as CommentNum  from rb_goods_comment where Is_Show=1 and Status=0 GROUP by ServicePersonalId) as b on a.ID=b.ServicePersonalId  WHERE  a.{nameof(RB_Reserve_ServicePersonal_Extend.Status)}=0");
            if (query != null)
            {
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.TenantId)}={query.TenantId}");
                }
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.MallBaseId)}={query.MallBaseId}");
                }
                if (query.ServiceStatus > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.ServiceStatus)}={query.ServiceStatus}");
                }
                if (query.StoreId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.StoreId)}={query.StoreId}");
                }
                if (!string.IsNullOrWhiteSpace(query.Name))
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.Name)} like '%{query.Name}%'");
                }
            }
            builder.Append($"  order by  a.{nameof(RB_Reserve_ServicePersonal_Extend.ID)} desc");
            return GetPage<RB_Reserve_ServicePersonal_Extend>(pageIndex, pageSize, out rowCount, builder.ToString()).ToList();
        }




        /// <summary>
        ///小程序端服务人员信息列表  待完善
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <param name="rowCount">总条数</param>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public List<RB_Reserve_ServicePersonal_Extend> GetServicePersonalDetailList(int pageIndex, int pageSize, out long rowCount, RB_Reserve_ServicePersonal_Extend query)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(@$" SELECT a.*,b.OrderNum FROM {TableName} as A  
LEFT JOIN (SELECT  od.ServicepersonalId,COUNT(*) OrderNum FROM rb_goods_orderdetail od
INNER JOIN rb_goods_order o on o.OrderId=od.OrderId 
where o.OrderClassify=3 and o.OrderStatus in(2,3,4,5,6) 
and  DATE_FORMAT(o.PaymentTime,'%Y-%m-%d' )>=DATE_FORMAT('{System.DateTime.Now.AddDays(-30)}','%Y-%m-%d')
and  DATE_FORMAT(o.PaymentTime,'%Y-%m-%d' )<=DATE_FORMAT('{System.DateTime.Now}','%Y-%m-%d')
GROUP BY od.ServicepersonalId) as b
on a.ID=b.ServicePersonalId  WHERE  a.{nameof(RB_Reserve_ServicePersonal_Extend.Status)}=0");
            if (query != null)
            {
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.TenantId)}={query.TenantId}");
                }
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.MallBaseId)}={query.MallBaseId}");
                }
                if (query.ServiceStatus > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.ServiceStatus)}={query.ServiceStatus}");
                }
                if (query.StoreId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.StoreId)}={query.StoreId}");
                }
                if (!string.IsNullOrWhiteSpace(query.Name))
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.Name)} like '%{query.Name}%'");
                }
            }
            if (!string.IsNullOrWhiteSpace(query.WorkDate))
            {
                builder.Append($"  and  DATE_FORMAT(b.Date,'%Y-%m-%d' )=DATE_FORMAT('{query.WorkDate}','%Y-%m-%d')");
            }
            if (!string.IsNullOrEmpty(query.QIds))
            {
                builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.ID)} IN({query.QIds}) ");

            }


            if (query.OrderBy == 0)
            {
                builder.Append($"  order by  a.{nameof(RB_Reserve_ServicePersonal_Extend.ID)} desc");
            }
            else if (query.OrderBy == 1)
            {
                builder.Append($"  order by  a.{nameof(RB_Reserve_ServicePersonal_Extend.Score)} desc");
            }
            else if (query.OrderBy == 2)
            {
                builder.Append($"  order by  a.{nameof(RB_Reserve_ServicePersonal_Extend.Score)} asc");
            }
            else if (query.OrderBy == 3)
            {
                builder.Append($"  order by  b.OrderNum desc");
            }
            else if (query.OrderBy == 4)
            {
                builder.Append($"  order by  b.OrderNum asc");
            }

            return GetPage<RB_Reserve_ServicePersonal_Extend>(pageIndex, pageSize, out rowCount, builder.ToString()).ToList();
        }






        /// <summary>
        /// 服务人员信息排名
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public List<RB_Reserve_ServicePersonal_Extend> GetServicePersonalRank(RB_Reserve_ServicePersonal_Extend query, bool IsSelectDel = false)
        {
            StringBuilder builder = new StringBuilder();

            if (query != null)
            {
                if (query.ID > 0)
                {
                    builder.Append($" AND  a.ServicepersonalId={query.ID}");
                }

                if (query.TenantId > 0)
                {
                    builder.Append($" AND b.{nameof(RB_Reserve_ServicePersonal_Extend.TenantId)}={query.TenantId}");
                }
                if (query.TenantId > 0)
                {
                    builder.Append($" AND b.{nameof(RB_Reserve_ServicePersonal_Extend.MallBaseId)}={query.MallBaseId}");
                }

            }
            string sql = $@"SELECT t.ServicepersonalId,t.orderNum,
CASE 
WHEN @prevRank = t.orderNum THEN @curRank 
WHEN @prevRank := t.orderNum THEN @curRank := @curRank + 1
END AS Rank
FROM (SELECT a.ServicepersonalId,COUNT(*) orderNum from rb_goods_orderdetail  as a LEFT JOIN rb_goods_order as b 
on a.OrderId=b.OrderId where b.OrderStatus in(2,3,4,5,6)  and  DATE_FORMAT(b.PaymentTime,'%Y-%m-%d' )>=DATE_FORMAT('{System.DateTime.Now.AddMonths(-1)}','%Y-%m-%d' )  
and  DATE_FORMAT(b.PaymentTime,'%Y-%m-%d' )<= DATE_FORMAT('{System.DateTime.Now}','%Y-%m-%d' )  {builder.ToString()} GROUP BY a.ServicepersonalId ) as t, 
(SELECT @curRank :=0, @prevRank := NULL) r
ORDER BY t.orderNum desc ";
            return Get<RB_Reserve_ServicePersonal_Extend>(sql).ToList();
        }


        /// <summary>
        /// 服务人员信息列表
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public List<RB_Reserve_ServicePersonal_Extend> GetServicePersonalList(RB_Reserve_ServicePersonal_Extend query, bool IsSelectDel = false)
        {
            StringBuilder builder = new StringBuilder();
            if (IsSelectDel)
            {
                builder.Append($" SELECT a.*,b.`Name` as  StoreName,b.Address as StoreAddress FROM {TableName} as A  LEFT JOIN rb_stores as b on a.StoreId=b.Id WHERE 1=1");
            }
            else
            {
                builder.Append($" SELECT a.*,b.`Name` as  StoreName,b.Address as StoreAddress FROM {TableName} as A  LEFT JOIN rb_stores as b on a.StoreId=b.Id WHERE  a.{nameof(RB_Reserve_ServicePersonal_Extend.Status)}=0");
            }

            if (query != null)
            {
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.TenantId)}={query.TenantId}");
                }
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.MallBaseId)}={query.MallBaseId}");
                }
                if (query.ServiceStatus > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.ServiceStatus)}={query.ServiceStatus}");
                }
                if (query.StoreId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.StoreId)}={query.StoreId}");
                }
                if (query.ID > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.ID)}={query.ID}");
                }
                if (!string.IsNullOrWhiteSpace(query.Name))
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.Name)} like '%{query.Name}%'");
                }
            }
            builder.Append($"  order by  a.{nameof(RB_Reserve_ServicePersonal_Extend.ID)} desc");
            return Get<RB_Reserve_ServicePersonal_Extend>(builder.ToString()).ToList();
        }







        /// <summary>
        /// 当天排班服务人员列表
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public List<RB_Reserve_ServicePersonal_Extend> GetKanBanServicePersonalList(RB_Reserve_ServicePersonal_Extend query)
        {
            StringBuilder builder = new StringBuilder();

            builder.Append($" SELECT a.*,b.Date,c.`Name` as  StoreName,c.`Address` as  StoreAddress FROM rb_reserve_servicepersonal as A  LEFT JOIN rb_reserve_servicetargetdate as b on a.ID=b.ServiceId LEFT JOIN rb_stores as c on a.StoreId=c.Id WHERE  a.{nameof(RB_Reserve_ServicePersonal_Extend.Status)}=0");
            if (query != null)
            {
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.TenantId)}={query.TenantId}");
                }
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.MallBaseId)}={query.MallBaseId}");
                }
                if (query.ServiceStatus > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.ServiceStatus)}={query.ServiceStatus}");
                }
                if (query.StoreId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.StoreId)}={query.StoreId}");
                }
                if (!string.IsNullOrWhiteSpace(query.Name))
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.Name)} like '%{query.Name}%'");
                }


                if (!string.IsNullOrWhiteSpace(query.WorkDate))
                {
                    builder.Append($"  and  DATE_FORMAT(b.Date,'%Y-%m-%d' )=DATE_FORMAT('{query.WorkDate}','%Y-%m-%d')");
                }
                if (!string.IsNullOrEmpty(query.QIds))
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.ID)} IN({query.QIds}) ");

                }
            }
            builder.Append($"  order by  a.{nameof(RB_Reserve_ServicePersonal_Extend.ID)} desc");
            return Get<RB_Reserve_ServicePersonal_Extend>(builder.ToString()).ToList();
        }




        /// <summary>
        /// 根据门店、商品编号和商品分类编号获取服务人员列表
        /// </summary>
        /// <param name="storeId"></param>
        /// <param name="productId"></param>
        /// <param name="categoryIds"></param>
        /// <param name="servicePersionId">服务人员Id</param>
        /// <param name="chooseDate">服务人员Id</param>
        /// <returns></returns>
        public List<RB_Reserve_ServicePersonal_Extend> GetServicePersonalListExtRepository(int storeId, int productId, string categoryIds,int servicePersionId=0,string chooseDate="")
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT DISTINCT A.*,IFNULL(orderTab.OrderNum,0) AS OrderNum  
FROM(
			SELECT  A.* 
			FROM  RB_Reserve_ServicePersonal AS A 
			WHERE  A.Status=0  AND A.ServiceType=3
			UNION ALL
			SELECT  A.* 
			FROM  RB_Reserve_ServicePersonal AS A INNER JOIN rb_reserve_servicepersonalproduct AS C ON A.ServiceType=C.ServiceType AND C.`Status`=0 AND  C.ProductId={0}
			WHERE  A.Status=0  AND A.ServiceType=2
			UNION ALL 
			SELECT  A.* 
			FROM  RB_Reserve_ServicePersonal AS A INNER JOIN rb_reserve_servicepersonalproduct AS D ON A.ServiceType=D.ServiceType  AND D.`Status`=0 AND  D.ProductId IN({1})
			WHERE  A.Status=0  AND A.ServiceType=1
		) AS A  
LEFT JOIN 
(
  SELECT detailB.ServicepersonalId,COUNT(1) AS OrderNum 
  FROM rb_goods_order AS orderA INNER JOIN rb_goods_orderdetail AS detailB ON orderA.OrderId=detailB.OrderId
  WHERE 1=1 AND orderA.OrderStatus NOT IN(7)
  GROUP BY detailB.ServicepersonalId
) AS orderTab ON A.ID=orderTab.OrderNum
WHERE A.Status=0   AND A.storeId={2}
", productId, categoryIds, storeId);
            if (servicePersionId > 0)
            {
                builder.AppendFormat(" AND  A.ID={0} ", servicePersionId);
            }
            if (!string.IsNullOrEmpty(chooseDate))
            {
                builder.AppendFormat(" AND EXISTS(SELECT 1 FROM rb_reserve_servicetargetdate WHERE A.Id=ServiceId AND DATE_FORMAT(Date,'%y/%m/%d')= DATE_FORMAT('{0}','%y/%m/%d')) ", chooseDate);
            }
            return Get<RB_Reserve_ServicePersonal_Extend>(builder.ToString()).ToList();
        }


        /// <summary>
        ///服务人员绩效统计
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <param name="rowCount">总条数</param>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public List<RB_Reserve_ServicePersonal_Extend> GetAchievementsList(int pageIndex, int pageSize, out long rowCount, RB_Reserve_ServicePersonal_Extend query)
        {
            StringBuilder builder = new StringBuilder();
            string orderWhere = "";
            string dateWhere = "";
            if (query != null)
            {
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.TenantId)}={query.TenantId}");
                    orderWhere += $" AND o.{nameof(RB_Reserve_ServicePersonal_Extend.TenantId)}={query.TenantId}";
                }
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.MallBaseId)}={query.MallBaseId}");
                    orderWhere += $" AND o.{nameof(RB_Reserve_ServicePersonal_Extend.MallBaseId)}={query.MallBaseId}";
                }
                if (query.ServiceStatus > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.ServiceStatus)}={query.ServiceStatus}");
                }
                if (query.StoreId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.StoreId)}={query.StoreId}");
                    orderWhere += $" AND o.{nameof(RB_Goods_Order.StoresId)}={query.StoreId}";
                }
                if (!string.IsNullOrWhiteSpace(query.Name))
                {
                    builder.Append($" AND a.{nameof(RB_Reserve_ServicePersonal_Extend.Name)} like '%{query.Name}%'");
                }
                if (!string.IsNullOrWhiteSpace(query.WorkDate))
                {
                    orderWhere += $" and  DATE_FORMAT(o.PaymentTime,'%Y-%m-%d' )>=DATE_FORMAT('{query.WorkDate}','%Y-%m-%d' ) ";
                    dateWhere += $" and  DATE_FORMAT(Date,'%Y-%m-%d' )>=DATE_FORMAT('{query.WorkDate}','%Y-%m-%d' ) ";
                }
                if (!string.IsNullOrWhiteSpace(query.EndWorkDate))
                {
                    orderWhere += $" and  DATE_FORMAT(o.PaymentTime,'%Y-%m-%d' )<= DATE_FORMAT('{query.EndWorkDate}','%Y-%m-%d' ) ";
                    dateWhere += $" and  DATE_FORMAT(Date,'%Y-%m-%d' )>=DATE_FORMAT('{query.EndWorkDate}','%Y-%m-%d' ) ";
                }
            }
            builder.Append($"  order by  a.{nameof(RB_Reserve_ServicePersonal_Extend.ID)} desc");

            string sql = @$"SELECT b.ServiceTargetDateNum,c.OrderNum,c.OrderGuestNum,c.Final_Price,d.CommentNum,a.* from rb_reserve_servicepersonal as a
LEFT JOIN (SELECT ServiceId,COUNT(*) as  ServiceTargetDateNum  from rb_reserve_servicetargetdate where 1=1 {dateWhere}  GROUP BY ServiceId ) as b on a.ID=b.ServiceId
LEFT JOIN (SELECT  od.ServicepersonalId,COUNT(*) OrderNum,SUM(od.Number) OrderGuestNum,SUM(od.Final_Price) as Final_Price FROM rb_goods_orderdetail od
INNER JOIN rb_goods_order o on o.OrderId=od.OrderId 
where o.OrderClassify=3 and o.OrderStatus in(2,3,4,5,6) {orderWhere}  GROUP BY od.ServicepersonalId) as c on a.ID=c.ServicepersonalId
LEFT JOIN (SELECT go.ServicepersonalId,COUNT(*) CommentNum,SUM(dc.CommentScore) CommentScore from rb_goods_comment as dc INNER JOIN rb_goods_orderdetail as go on dc.OrderDetailId=go.Id
INNER JOIN rb_goods_order as o on go.OrderId=o.OrderId where dc.`Status`=0  {orderWhere} GROUP BY go.ServicepersonalId) as d on d.ServicepersonalId=a.ID  WHERE  a.{nameof(RB_Reserve_ServicePersonal_Extend.Status)}=0 {builder.ToString()}";
            return GetPage<RB_Reserve_ServicePersonal_Extend>(pageIndex, pageSize, out rowCount, sql).ToList();
        }
    }
}
