using Dapper;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XYY.Common.Standard;
using XYY.Core.Standard.Data.Infrastructure;
using XYY.Model.Standard.Enums;
using XYY.Service.Standard.Finance.Charging.Dto;

namespace XYY.Service.Standard.Finance.Charging.CustomerOrderFee
{
    public interface IOrder_ChargesService
    {
        Task<Order_ChargesReturnModel> QueryData(QueryModel queryModel);
        Task<IEnumerable<OrderChangeLog>> GetOrderChanges(List<string> systemOrderNos);
    }

    public class Order_ChargesService : IOrder_ChargesService
    {
        IUnitOfWork _unitOfWork;
        public Order_ChargesService(IUnitOfWork unitOfWork)
        {
            _unitOfWork = unitOfWork;
        }

        public async Task<IEnumerable<OrderChangeLog>> GetOrderChanges(List<string> systemOrderNos)
        {
            if (systemOrderNos.Count > 20000)
                throw new Exception("单次查询不可大于2万条，请分开查询");
            string sql = @"
                                    select a.SystemNo,case when isnull( b.ShippingFee,0) = 0 then 0 else 1 end as HasIncome
		                                , case when isnull(c.ShippingFee,0) =0 then 0 else 1 end as HasSettlement
		                                ,d.ErrorMessage as SettlementError from order_order(nolock)a
	                                 left join Finance_Order_Charges(nolock)b
		                                on a.SystemNo = b.SystemNo and b.ChargeTarget=1
	                                 left join Finance_Order_Charges(nolock)c
		                                on a.SystemNo = c.SystemNo and c.ChargeTarget=2
	                                 left join  Finance_Charge_Log(nolock)d
		                                on a.SystemNo = d.BillNo
	                                where a.SystemNo in ('" + string.Join("','", systemOrderNos) + "')";
            var data = await _unitOfWork.QueryBySqlAsync<OrderChangeLog>(sql, 10);
            foreach (var item in data)
            {
                if (item != null && item.SettlementError != null)
                {
                    if (item.SettlementError.Contains("Timeout performing EXISTS"))
                    {
                        item.SettlementError = "计算结算价时Redis超时，请联系技术人员处理";
                    }
                    else if (item.SettlementError.Contains("无匹配总报价，请确认是否导入报价"))
                    {
                        item.SettlementError = "系统中无结算价，请联系产品部处理";
                    }
                }
            }

            return data;
        }

        public async Task<Order_ChargesReturnModel> QueryData(QueryModel queryModel)
        {
            #region 只能自己写分页和多记录读取
            StringBuilder sb = new StringBuilder();
            //排序
            string sortString = string.Empty;
            foreach (var item in queryModel.QuerySort)
            {
                sortString += " " + item.Field + " " + (item.IsDesc ? "DESC" : "") + ",";
            }
            sortString = sortString.TrimEnd(',');
            //执行
            sb.Append(@$" select Row_Number()over(order by  {sortString})rowId, *  into #temo_Order_Charges--使用临时表
            from ( ");
            sb.Append(@$"  select 
    --搜索条件相关
 a.TrackingNumber,--结算时跟踪单号
 a.SystemNo,--结算时内部单号
 a.TransactionTime,--结算时间
 a.Offset,--是否冲抵
 --a.CustomerId,--分公司
 --b.CustomerId,--具体客户
 a.ChannelId,--服务渠道
 f.PublicName ChannelName,
 a.OrderCreateChannelId,--结算时下单渠道
g.Id PublicChannelId,
 g.Name OrderCreateChannelName,
 a.CountryCode,--结算时下单国家
 h.CnName CountryName,
 d.NickName Info_NickName,--业务员
 c.SalesmanUserId,


 a.ShippingFee,a.AttachFee,a.EUTariff,a.Profit,a.CustomerId,e.CompanyName,--分公司成本相关
 b.ShippingFee CusShippingFee,b.AttachFee CusAttachFee,b.EUTariff CusEUTariff,b.CustomerId CusCustomerId,c.CompanyName CusCustomerName -- 客户收入相关
 from Finance_Order_Charges(nolock)a 
 left join Finance_Order_Charges(nolock) b on a.SystemNo=b.SystemNo  and a.TransactionTime=b.TransactionTime and b.ChargeTarget={(int)ChargeTarget.客户}
 left join User_Customer(nolock)c on c.id=b.CustomerId
 left join User_Info(nolock)d on d.id=c.SalesmanUserId
 left join User_Customer(nolock)e on e.id=a.CustomerId
left join logistics_Channel(nolock) f on f.id= a.ChannelId
 left join logistics_Channel(nolock) g1 on g1.Id=a.OrderCreateChannelId
 left join Logistics_Public(nolock) g on g.code= g1.PublicCode
 outer apply (select top 1 Code,CnName from Sys_Country where Code=a.CountryCode) h
  where a.ChargeTarget={(int)ChargeTarget.分公司}  ");
            sb.Append(" ) Order_Charges_Statistical  WHERE 1=1   ");
            //where 条件处理
            if (queryModel.QueryParamer != null && queryModel.QueryParamer.Count > 0)
            {
                var where = _unitOfWork.GetWhere(queryModel.QueryParamer);
                if (!string.IsNullOrWhiteSpace(where))
                {
                    sb.Append(where);
                }
            }
            //多结果集,从上面语句得到的临时表中取值
            sb.Append($" select * from #temo_Order_Charges T where rowId between {(queryModel.PageIndex - 1) * queryModel.PageSize + 1} and {queryModel.PageSize * queryModel.PageIndex}; ");
            sb.Append($"  select count(0)TotalCount,COUNT(distinct SystemNo)SystemNoCount,SUM(ShippingFee+AttachFee+EUTariff)CostCount,SUM(CusShippingFee+CusAttachFee+CusEUTariff)IncomeCount from #temo_Order_Charges where 1=1 ");
            #endregion
            string sql2 = sb.ToString();

            var dr = await _unitOfWork.Connection.QueryMultipleAsync(sb.ToString());
            //需要注意读取顺序，依据语句执行顺序进行读取
            var order_Charges = new PageResult<Order_Charges_Statistical>()
            {
                Result = dr.Read<Order_Charges_Statistical>(),
                PageIndex = queryModel.PageIndex,
                PageSize = queryModel.PageIndex
            };
            var order_Charges_StatisticalToal = dr.Read<Order_Charges_StatisticalToal>();
            order_Charges.TotalCount = order_Charges_StatisticalToal.FirstOrDefault().TotalCount;
            Order_ChargesReturnModel returnModel = new Order_ChargesReturnModel()
            {
                order_Charges = order_Charges,
                order_Charges_StatisticalToal = order_Charges_StatisticalToal.FirstOrDefault()
            };
            return returnModel;
        }

    }
}
