using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XYY.Common.Standard;
using XYY.Core.Standard.Data.Infrastructure;
using XYY.Model.Standard.Order;

namespace XYY.Service.Standard.Order
{
    public interface ICustomerOrderStatisticsService
    {
        Task<deliverySignCollectResult> GetDeliverySignCollectPager(QueryModel qm, int CustomerId);
        Task<PageResult<deliverySignDetail>> GetDeliverySignDetailPager(QueryModel qm, int CustomerId);
        Task<IEnumerable<deliverySignDetail>> GetDeliverySignDetailList(QueryModel qm, int CustomerId);
        Task<bool> IsShowOnlineTime(int CustomerId);
    }
    public class CustomerOrderStatisticsService : ICustomerOrderStatisticsService
    {
        private readonly IUnitOfWork _unitOfWork;
        public CustomerOrderStatisticsService(IUnitOfWork unitOfWork)
        {
            this._unitOfWork = unitOfWork;
        }
        public async Task<deliverySignCollectResult> GetDeliverySignCollectPager(QueryModel qm, int CustomerId)
        {
            #region 基础sql
            //显示更多
            int[] moreTimes = null;
            var qmMore = qm.QueryParamer.Where(x => x.Filed == "MoreCreateTime").FirstOrDefault();
            if (qmMore != null) { var moreTimeStrs = qmMore.Value.Split(','); moreTimes = moreTimeStrs.Select(x => Convert.ToInt32(x)).Where(x => x != 0).OrderBy(x => x).ToArray(); qm.QueryParamer.Remove(qmMore); }
            //区间
            string TimeRange = string.Empty;
            var temp = qm.QueryParamer.Where(x => x.Filed == "ReceiveTime").ToList();
            if (temp != null && temp.Count > 0) { TimeRange = string.Join("_", temp.OrderBy(x => Convert.ToDateTime(x.Value)).Select(x => x.Value)); }
            //表字段条件
            qm.QueryParamer.ForEach(x => { if (x.Filed == "ReceiveTime") { x.Filed = "a.ReceiveTime"; } else if (x.Filed == "CreateOrderChannelId") { x.Filed = "a.CreateOrderChannelId"; } });
            StringBuilder sb = new StringBuilder();
            sb.Append(@"with temp as(
select a.Id,c.Name PublicName,a.CreateOrderChannelId,a.OrderStatus,a.OrderTime from Order_Order(nolock) a
left join Logistics_Channel(nolock) b on b.Id=a.CreateOrderChannelId
left join Logistics_Public(nolock) c on c.Code=b.PublicCode ");
            sb.Append(@$" where a.CustomerId={CustomerId} {_unitOfWork.GetWhere(qm.QueryParamer)}
group by a.Id,a.CreateOrderChannelId,c.Name,a.OrderStatus,a.OrderTime), ");
            sb.Append(@$" temp2 as (
	select temp.PublicName,temp.CreateOrderChannelId,COUNT(distinct temp.Id)OrderCount,
	sum(case when b.status='delivered' then 1 else 0 end)SignCount,
	SUM(case when temp.OrderStatus in (2,3) then 1 else 0 end)DeliveryCount");
            if (moreTimes != null)
            {
                int i = 0;
                foreach (var item in moreTimes)
                {
                    i++;
                    sb.Append($" ,SUM(case when {item}>=b.SignDay then 1 else 0 end)SignCount{i} ");
                }
            }
            sb.Append(@" from temp
	left join Logistics_Trace(nolock) b on b.OrderId=temp.Id 
	group by temp.PublicName,temp.CreateOrderChannelId
) ");
            #endregion
            #region 分页
            StringBuilder pager = new StringBuilder();
            pager.Append(sb.ToString());
            pager.Append($" select '{TimeRange}' TimeRange,* into #temp_deliverySignCollect from  temp2 ");
            //排序
            string sortString = string.Empty;
            foreach (var item in qm.QuerySort)
            {
                sortString += " " + item.Field + " " + (item.IsDesc ? "DESC" : "") + ",";
            }
            sortString = sortString.TrimEnd(',');
            pager.Append($@"select * from (select Row_Number()over(order by  {sortString})rowId,*
            from #temp_deliverySignCollect) T where rowId between {(qm.PageIndex - 1) * qm.PageSize + 1} and {qm.PageSize * qm.PageIndex};");
            pager.Append($"select count(0)TotalCount,SUM(OrderCount)OrderCount,SUM(SignCount)SignCount,SUM(DeliveryCount)DeliveryCount ");
            if (moreTimes != null)
            {
                int i = 0;
                foreach (var item in moreTimes)
                {
                    i++;
                    pager.Append($" ,SUM(SignCount{i})SignCount{i} ");
                }
            }
            pager.Append(" from #temp_deliverySignCollect ");
            #endregion
            #region 取值
            string sql = pager.ToString();
            var dr = await _unitOfWork.Connection.QueryMultipleAsync(sql);
            //需要注意读取顺序，依据语句执行顺序进行读取
            var deliverySignCollect = new PageResult<deliverySignCollect>()
            {
                Result = dr.Read<deliverySignCollect>(),
                PageIndex = qm.PageIndex,
                PageSize = qm.PageIndex,
            };
            var Total = dr.Read<TotalCollect>().FirstOrDefault();
            deliverySignCollect.TotalCount = Total.TotalCount;
            deliverySignCollectResult deliverySignCollectResult = new deliverySignCollectResult();
            deliverySignCollectResult.pageResult = deliverySignCollect;
            deliverySignCollectResult.totalCollect = Total;
            #endregion
            return deliverySignCollectResult;
        }
        public async Task<PageResult<deliverySignDetail>> GetDeliverySignDetailPager(QueryModel qm, int CustomerId)
        {
            #region 基础sql
            StringBuilder sb = new StringBuilder();
            sb.Append(" select distinct a.TrackingNumber,a.CreateOrderChannelId,a.CustomerOrderNo,a.SystemNo,a.OrderTime,a.ReceiveTime,c.PublicName,b.SignTime,b.SignDay,b.Status TraceStatus,b.LastMessage,b.TargetTime,b.LastGetTime, b.OnlineTime");
            sb.Append(@" from Order_Order(nolock) a
left join Logistics_Trace(nolock) b on b.OrderId=a.Id 
left join Logistics_Channel(nolock) c on c.Id=a.CreateOrderChannelId ");
            sb.Append($" where a.CustomerId={CustomerId}");
            #endregion
            #region 分页
            //排序
            string sortString = string.Empty;
            foreach (var item in qm.QuerySort)
            {
                sortString += " " + item.Field + " " + (item.IsDesc ? "DESC" : "") + ",";
            }
            sortString = sortString.TrimEnd(',');
            string sql1 = sb.ToString();
            string where = GetSpecialWhere(qm);
            StringBuilder pager = new StringBuilder();
            pager.Append($@"select * from (select Row_Number()over(order by  {sortString})rowId,*
            from ({sql1})a where 1=1 {_unitOfWork.GetWhere(qm.QueryParamer)} {where}) T where rowId between {(qm.PageIndex - 1) * qm.PageSize + 1} and {qm.PageSize * qm.PageIndex};");
            pager.Append(@$"select count(0)TotalCount from (select Row_Number()over(order by  {sortString})rowId,*
            from({sql1})a where 1 = 1 {_unitOfWork.GetWhere(qm.QueryParamer)} {where}) T where 1=1");
            #endregion
            #region 取值
            string sql = pager.ToString();
            var dr = await _unitOfWork.Connection.QueryMultipleAsync(sql);
            //需要注意读取顺序，依据语句执行顺序进行读取
            var pageResult = new PageResult<deliverySignDetail>()
            {
                Result = dr.Read<deliverySignDetail>(),
                PageIndex = qm.PageIndex,
                PageSize = qm.PageIndex,
                TotalCount = dr.Read<int>().FirstOrDefault()
            };
            #endregion
            return pageResult;
        }
        public async Task<IEnumerable<deliverySignDetail>> GetDeliverySignDetailList(QueryModel qm, int CustomerId)
        {
            #region 基础sql
            StringBuilder sb = new StringBuilder();
            sb.Append(" select a.TrackingNumber,a.CreateOrderChannelId,a.CustomerOrderNo,a.SystemNo,a.OrderTime,a.ReceiveTime,c.PublicName,b.SignTime,b.SignDay,b.Status TraceStatus,b.LastMessage,b.TargetTime,b.LastGetTime, b.OnlineTime");
            sb.Append(@" from Order_Order(nolock) a
left join Logistics_Trace(nolock) b on b.OrderId=a.Id 
left join Logistics_Channel(nolock) c on c.Id=a.CreateOrderChannelId ");
            sb.Append($" where a.CustomerId={CustomerId}");
            #endregion
            #region 取值
            string sortString = string.Empty;
            foreach (var item in qm.QuerySort)
            {
                sortString += " " + item.Field + " " + (item.IsDesc ? "DESC" : "") + ",";
            }
            string sql1 = sb.ToString();
            string where = GetSpecialWhere(qm);
            string sql2 = $" select* from ({sql1}) T where 1=1 {_unitOfWork.GetWhere(qm.QueryParamer)} {where}";
            return await _unitOfWork.QueryBySqlAsync<deliverySignDetail>(sql2);
            #endregion
        }
        private string GetSpecialWhere(QueryModel qm)
        {
            #region where条件处理
            string where = string.Empty;
            if (qm.QueryParamer != null)
            {
                string sqlFiled = string.Empty;
                if (qm.QueryParamer.Any(x => x.Filed == "numberType"))
                {
                    var tempNumberType = qm.QueryParamer.Where(x => x.Filed == "numberType").FirstOrDefault();
                    switch (tempNumberType.Value)
                    {
                        case "1"://运单
                            sqlFiled = "TrackingNumber";
                            break;
                        case "2"://客单
                            sqlFiled = "CustomerOrderNo";
                            break;
                        case "3"://系统单号
                            sqlFiled = "SystemNo";
                            break;
                    }
                    qm.QueryParamer.Remove(tempNumberType);//取值后去掉条件
                }
                if (qm.QueryParamer.Any(x => x.Filed == "Number"))
                {
                    var tempNumber = qm.QueryParamer.Where(x => x.Filed == "Number").FirstOrDefault();
                    var valueArry = tempNumber.Value.TrimEnd(',').Split(',').ToList();
                    string valueStr = string.Empty;
                    foreach (var item in valueArry)
                    {
                        valueStr += $"'{item}',";
                    }

                    if (!string.IsNullOrEmpty(sqlFiled))
                    {
                        where += $" and {sqlFiled} in ({valueStr.TrimEnd(',')}) ";
                    }
                    qm.QueryParamer.Remove(tempNumber);//取值后去掉条件
                }
            }
            return where;
            #endregion
        }

        public async Task<bool> IsShowOnlineTime(int CustomerId)
        {
            var result = await _unitOfWork.Connection.QueryAsync<bool>($" select isnull(IsShowOnlineTime,0) from User_Customer where Id={CustomerId} ");
            return result.FirstOrDefault();
        }
    }
}
