using System;
using System.Collections.Generic;
using System.Text;
using Mall.Model.Entity.Product;
using Mall.Model.Extend.Product;
using System.Linq;
using Mall.Model.Entity.User;
using Mall.Common.Enum.Goods;

namespace Mall.Repository.Product
{
    /// <summary>
    /// 商品订单返佣仓储层
    /// </summary>
    public class RB_Goods_OrderCommissionRepository : BaseRepository<RB_Goods_OrderCommission>
    {

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<RB_Goods_OrderCommission_Extend> GetList(RB_Goods_OrderCommission_Extend dmodel)
        {
            string where = $" 1=1 and oa.ReOrderId is null";
            if (dmodel.TenantId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.OrderId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.OrderId)}={dmodel.OrderId}";
            }
            if (dmodel.UserId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.UserId)}={dmodel.UserId}";
            }
            if (!string.IsNullOrEmpty(dmodel.OrderIds))
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.OrderId)} in({dmodel.OrderIds})";
            }

            string sql = $@"SELECT oc.* FROM RB_Goods_OrderCommission oc
left join rb_goods_orderaftersale oa on oc.OrderDetailId= oa.OrderDetialId and oa.Type=1 and oa.Status=0 and oa.ReOrderStatus in (2,3,4,5)
where {where} order by oc.Id asc";
            return Get<RB_Goods_OrderCommission_Extend>(sql).ToList();
        }

        /// <summary>
        /// 获取用户返佣
        /// </summary>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<RB_Goods_OrderCommission_Extend> GetListGroupByUser(RB_Goods_OrderCommission_Extend dmodel)
        {
            string where = $" 1=1 and oa.ReOrderId is null";
            if (dmodel.TenantId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.OrderId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.OrderId)}={dmodel.OrderId}";
            }
            if (dmodel.UserId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.UserId)}={dmodel.UserId}";
            }
            if (dmodel.OrderDetailId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.OrderDetailId)}={dmodel.OrderDetailId}";
            }
            if (!string.IsNullOrEmpty(dmodel.OrderIds))
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.OrderId)} in({dmodel.OrderIds})";
            }

            string sql = $@"SELECT oc.OrderId,oc.UserId,oc.Grade,oc.Type,oc.CommissionState,SUM(oc.Commission) as Commission FROM RB_Goods_OrderCommission oc 
left join rb_goods_orderaftersale oa on oc.OrderDetailId= oa.OrderDetialId and oa.Type=1 and oa.Status=0 and oa.ReOrderStatus in (2,3,4,5)
where {where} group by oc.OrderId,oc.UserId,oc.Grade,oc.Type order by oc.Grade asc";
            return Get<RB_Goods_OrderCommission_Extend>(sql).ToList();
        }




        /// <summary>
        /// 获取用户返佣
        /// </summary>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<RB_Goods_OrderCommission_Extend> GetListGroupByUser_V2(RB_Goods_OrderCommission_Extend dmodel)
        {
            string where = $" 1=1 and oa.ReOrderId is null";
            if (dmodel.TenantId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.OrderId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.OrderId)}={dmodel.OrderId}";
            }
            if (dmodel.UserId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.UserId)}={dmodel.UserId}";
            }
            if (dmodel.OrderDetailId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.OrderDetailId)}={dmodel.OrderDetailId}";
            }
            if (!string.IsNullOrEmpty(dmodel.OrderIds))
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.OrderId)} in({dmodel.OrderIds})";
            }
            if (!string.IsNullOrEmpty(dmodel.OrderDetailIds))
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.OrderDetailId)} in({dmodel.OrderDetailIds})";
            }

            string sql = $@"SELECT oc.OrderId,oc.OrderDetailId,oc.UserId,oc.Grade,oc.Type,oc.CommissionState,SUM(oc.Commission) as Commission,oc.CommissionType,IFNULL(oc.IsRemit,0 ) as IsRemit FROM All_Commission oc 
left join rb_goods_orderaftersale oa on oc.OrderDetailId= oa.OrderDetialId and oa.Type=1 and oa.Status=0 and oa.ReOrderStatus in (2,3,4,5)
where {where} group by oc.OrderDetailId,oc.UserId,oc.Grade,oc.Type,oc.CommissionType order by oc.Grade asc";
            return Get<RB_Goods_OrderCommission_Extend>(sql).ToList();
        }


        /// <summary>
        /// 获取用户返佣
        /// </summary>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<RB_Goods_OrderCommission_Extend> GetListGroupByOrderDetailIds(RB_Goods_OrderCommission_Extend dmodel)
        {
            string where = $" 1=1 and oa.ReOrderId is null";
            if (dmodel.TenantId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.OrderId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.OrderId)}={dmodel.OrderId}";
            }
            if (dmodel.UserId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.UserId)}={dmodel.UserId}";
            }
            //if (dmodel.Type > 0)
            //{
            //    where += $@" and oc.{nameof(RB_Goods_OrderCommission.Type)}={dmodel.Type}";
            //}
            if (dmodel.OrderDetailId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.OrderDetailId)}={dmodel.OrderDetailId}";
            }
            if (!string.IsNullOrEmpty(dmodel.OrderIds))
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.OrderId)} in({dmodel.OrderIds})";
            }

            string sql = $@"SELECT oc.OrderDetailId,oc.UserId,oc.Grade,oc.Type,oc.CommissionState,SUM(oc.Commission) as Commission FROM RB_Goods_OrderCommission oc 
left join rb_goods_orderaftersale oa on oc.OrderDetailId= oa.OrderDetialId and oa.Type=1 and oa.Status=0 and oa.ReOrderStatus in (2,3,4,5)
where {where} group by oc.OrderDetailId,oc.UserId,oc.Grade,oc.Type order by oc.Grade asc";
            return Get<RB_Goods_OrderCommission_Extend>(sql).ToList();
        }

        /// <summary>
        /// 获取用户返佣
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<RB_Goods_OrderCommission_Extend> GetListGroupByUser_V2(int pageIndex,int pageSize,out long count,RB_Goods_OrderCommission_Extend dmodel)
        {
            string where = $" 1=1 and o.Recycled=2 and oa.ReOrderId is null";
            if (dmodel.TenantId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.OrderId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.OrderId)}={dmodel.OrderId}";
            }
            if (dmodel.UserId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.UserId)}={dmodel.UserId}";
            }
            if (!string.IsNullOrEmpty(dmodel.OrderIds))
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.OrderId)} in({dmodel.OrderIds})";
            }
            if (dmodel.DistrbutionOrderSelectStatus > 0)
            {
                switch (dmodel.DistrbutionOrderSelectStatus)
                {
                    case 1: where += $@" and o.{nameof(RB_Goods_Order.OrderStatus)}={(int)OrderStatusEnum.NonPayment}"; break;
                    case 2: where += $@" and o.{nameof(RB_Goods_Order.OrderStatus)} in(2,3,4,6)"; break;
                    case 3: where += $@" and o.{nameof(RB_Goods_Order.OrderStatus)}={(int)OrderStatusEnum.Completed}"; break;
                }
            }
            else {
                where += $@" and o.{nameof(RB_Goods_Order.OrderStatus)} in(1,2,3,4,5,6)";
            }

            string sql = $@"SELECT oc.OrderId,o.OrderNo,o.UserId,o.OrderClassify,oc.Type,oc.Grade,oc.CommissionState,o.OrderStatus as DistrbutionOrderSelectStatus,SUM(oc.Commission) as Commission 
FROM RB_Goods_OrderCommission oc
inner join rb_goods_order o on oc.OrderId = o.OrderId
left join rb_goods_orderaftersale oa on oc.OrderDetailId= oa.OrderDetialId and oa.Type=1 and oa.Status=0 and oa.ReOrderStatus in (2,3,4,5)
where {where} group by oc.OrderId,o.OrderNo,oc.UserId,o.OrderClassify,oc.Type,oc.Grade order by oc.OrderId desc";
            return GetPage<RB_Goods_OrderCommission_Extend>(pageIndex, pageSize, out count, sql).ToList();
        }

        /// <summary>
        /// 获取用户返佣
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<RB_Goods_OrderCommission_Extend> GetListGroupByUserRepository(int pageIndex, int pageSize, out long count, RB_Goods_OrderCommission_Extend dmodel)
        {
            string where = $" 1=1 and o.Recycled=2 ";
            if (dmodel.TenantId > 0)
            {
                where += $@" and o.{nameof(RB_Goods_OrderCommission.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and o.{nameof(RB_Goods_OrderCommission.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.OrderId > 0)
            {
                where += $@" and o.{nameof(RB_Goods_OrderCommission.OrderId)}={dmodel.OrderId}";
            }
            if (dmodel.UserId > 0)
            {
                where += string.Format(@" and o.UserId={0} ", dmodel.UserId);
            }
            if (!string.IsNullOrEmpty(dmodel.OrderIds))
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.OrderId)} in({dmodel.OrderIds})";
            }
            if (dmodel.DistrbutionOrderSelectStatus > 0)
            {
                switch (dmodel.DistrbutionOrderSelectStatus)
                {
                    case 1: where += $@" and o.{nameof(RB_Goods_Order.OrderStatus)}={(int)OrderStatusEnum.NonPayment}"; break;
                    case 2: where += $@" and o.{nameof(RB_Goods_Order.OrderStatus)} in(2,3,4,6)"; break;
                    case 3: where += $@" and o.{nameof(RB_Goods_Order.OrderStatus)}={(int)OrderStatusEnum.Completed}"; break;
                }
            }
            else
            {
                where += $@" and o.{nameof(RB_Goods_Order.OrderStatus)} in(1,2,3,4,5,6)";
            }

            string sql = $@"
SELECT o.OrderId,o.OrderNo,o.UserId,o.OrderClassify,o.OrderStatus AS DistrbutionOrderSelectStatus
      ,IFNULL(oc.Commission,0) AS Commission,oc.Type AS Type,oc.Grade AS Grade,oc.CommissionState AS CommissionState
FROM rb_goods_order o 
      LEFT JOIN (
					SELECT goc.OrderId,goc.UserId,goc.Type,goc.Grade,goc.CommissionState,SUM(goc.Commission) as Commission 
					FROM RB_Goods_OrderCommission as goc
							LEFT  JOIN rb_goods_orderaftersale oa on goc.OrderDetailId= oa.OrderDetialId and oa.Type=1 and oa.Status=0 and oa.ReOrderStatus in (2,3,4,5)
					GROUP BY goc.OrderId,goc.UserId,goc.Type,goc.Grade,goc.CommissionState
                 )
       AS oc ON oc.OrderId = o.OrderId AND o.UserId=oc.UserId
WHERE  {where}
order by o.OrderId desc
";
            var list= GetPage<RB_Goods_OrderCommission_Extend>(pageIndex, pageSize, out count, sql).ToList();
            return list;
        }

        /// <summary>
        /// 获取用户返佣
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<RB_Goods_OrderCommission_Extend> GetListGroupByUser_V3(int pageIndex, int pageSize, out long count, RB_Goods_OrderCommission_Extend dmodel)
        {
            string where = $" 1=1 and o.Recycled=2 and oa.ReOrderId is null";
            if (dmodel.TenantId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.OrderId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.OrderId)}={dmodel.OrderId}";
            }
            if (dmodel.UserId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.UserId)}={dmodel.UserId}";
            }
            if (!string.IsNullOrEmpty(dmodel.OrderIds))
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.OrderId)} in({dmodel.OrderIds})";
            }
            if (!string.IsNullOrEmpty(dmodel.OrderNo)) {
                where += $@" and o.{nameof(RB_Goods_Order.OrderNo)}='{dmodel.OrderNo}'";
            }
            if (dmodel.CommissionState > 0)
            {
                switch (dmodel.CommissionState)
                {
                    case 2: where += $@" and o.{nameof(RB_Goods_Order.OrderStatus)}={(int)OrderStatusEnum.Completed}"; break;
                    case 1: where += $@" and o.{nameof(RB_Goods_Order.OrderStatus)} in(1,2,3,4,6)"; break;
                }
            }
            else{
                where += $@" and o.{nameof(RB_Goods_Order.OrderStatus)} in(1,2,3,4,5,6)";
            }

            string sql = $@"SELECT oc.OrderId,o.OrderNo,o.UserId,oc.Grade,oc.Type,oc.CommissionState,o.OrderStatus as DistrbutionOrderSelectStatus,SUM(oc.Commission) as Commission 
FROM RB_Goods_OrderCommission oc
inner join rb_goods_order o on oc.OrderId = o.OrderId
left join rb_goods_orderaftersale oa on oc.OrderDetailId= oa.OrderDetialId and oa.Type=1 and oa.Status=0 and oa.ReOrderStatus in (2,3,4,5)
where {where} group by oc.OrderId,o.OrderNo,oc.UserId,oc.Grade order by oc.OrderId desc";
            return GetPage<RB_Goods_OrderCommission_Extend>(pageIndex, pageSize, out count, sql).ToList();
        }

        /// <summary>
        /// 订单返佣
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public decimal GetUserOrderCommissionStatistics(int userId)
        {
            string sql = $@"SELECT SUM(Commission) as Commission FROM rb_goods_ordercommission oc
INNER JOIN rb_goods_order o on oc.OrderId=o.OrderId
left join rb_goods_orderaftersale oa on oc.OrderDetailId= oa.OrderDetialId and oa.Type=1 and oa.Status=0 and oa.ReOrderStatus in (2,3,4,5)
 WHERE oc.UserId={userId} and o.OrderStatus in (1,2,3,4,5,6) and o.Recycled=2 and oa.ReOrderId is null";

            var obj = ExecuteScalar(sql);
            if (obj != null) {
                return Convert.ToDecimal(obj);
            }
            return 0;
        }

        /// <summary>
        /// 用户未结算返佣
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public decimal GetUserOrderCommissionNoSettlement(int userId)
        {
            string sql = $@"SELECT SUM(Commission) as Commission FROM rb_goods_ordercommission oc
INNER JOIN rb_goods_order o on oc.OrderId=o.OrderId
left join rb_goods_orderaftersale oa on oc.OrderDetailId= oa.OrderDetialId and oa.Type=1 and oa.Status=0 and oa.ReOrderStatus in (2,3,4,5)
 WHERE oc.UserId={userId} and oc.CommissionState=1 and o.OrderStatus in (1,2,3,4,5,6) and o.Recycled=2 and oa.ReOrderId is null";

            var obj = ExecuteScalar(sql);
            if (obj != null)
            {
                return Convert.ToDecimal(obj);
            }
            return 0;
        }

        /// <summary>
        /// 订单返佣
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public List<RB_Goods_OrderCommission_Extend> GetUserCommissionStatistics(string userIds)
        {
            string sql = $@"SELECT oc.UserId
,SUM(case when oc.CommissionState =1 then oc.Commission else 0 end) as WaitCommission
,SUM(case when oc.CommissionState =2 then oc.Commission else 0 end) as Commission
FROM rb_goods_ordercommission oc
INNER JOIN rb_goods_order o on oc.OrderId=o.OrderId
left join rb_goods_orderaftersale oa on oc.OrderDetailId= oa.OrderDetialId and oa.Type=1 and oa.Status=0 and oa.ReOrderStatus in (2,3,4,5)
 WHERE oc.UserId in({userIds}) and o.OrderStatus in (1,2,3,4,5,6) and o.Recycled=2 and oa.ReOrderId is null
 group by oc.UserId";

            return Get<RB_Goods_OrderCommission_Extend>(sql).ToList();
        }

        /// <summary>
        /// 获取未提现的佣金列表
        /// </summary>
        /// <param name="UserId"></param>
        /// <returns></returns>
        public List<RB_Goods_OrderCommission_Extend> GetUserCommissionRemit(int UserId) {
            string sql = $@"SELECT * FROM rb_goods_ordercommission oc WHERE oc.CommissionState=2 and oc.UserId={UserId} and oc.IsRemit <> 1";
            return Get<RB_Goods_OrderCommission_Extend>(sql).ToList();
        }

        /// <summary>
        /// 获取已标记提现的佣金列表
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="OrderIds"></param>
        /// <returns></returns>
        public List<RB_Goods_OrderCommission_Extend> GetUserCommissionRemitedList(int UserId,string OrderIds)
        {
            string sql = $@"SELECT * FROM rb_goods_ordercommission oc WHERE oc.CommissionState=2 and oc.UserId={UserId} and oc.OrderId in ({OrderIds}) and oc.IsRemit = 1";
            return Get<RB_Goods_OrderCommission_Extend>(sql).ToList();
        }

        /// <summary>
        /// 获取已标记提现的佣金列表
        /// </summary>
        /// <param name="OrderDetailIds"></param>
        /// <returns></returns>
        public List<RB_Goods_OrderCommission_Extend> GetOrderHaveCommissionList(string OrderDetailIds, int UserId =0)
        {
            string sql = $@"SELECT * FROM rb_goods_ordercommission oc WHERE oc.CommissionState=2 and oc.OrderDetailId in ({OrderDetailIds})";
            if (UserId > 0) {
                sql += $@" and oc.UserId={UserId}";
            }
            return Get<RB_Goods_OrderCommission_Extend>(sql).ToList();
        }


        /// <summary>
        /// 获取所有订单已完成 未结算佣金的订单
        /// </summary>
        /// <param name="tenantId"></param>
        /// <param name="mallBaseId"></param>
        /// <returns></returns>
        public List<RB_Goods_OrderCommission_Extend> GetAllOrderCompletedNoCommissionForOrder(int tenantId, int mallBaseId)
        {
            string sql = $@"SELECT oc.OrderId FROM rb_goods_ordercommission oc 
INNER JOIN rb_goods_orderdetail od on oc.OrderDetailId = od.Id
INNER JOIN rb_goods_order o on oc.OrderId = o.OrderId
left join rb_goods_orderaftersale oa on oc.OrderId = oa.OrderId and oc.OrderDetailId = oa.OrderDetialId and oa.Type=1 and oa.Status=0 and oa.ReOrderStatus in (1,2,3,4,5)
where oc.TenantId={tenantId} and oc.MallBaseId={mallBaseId} and oc.CommissionState =1 and o.OrderStatus =5 and o.Recycled=2 and o.Status=0 and oa.ReOrderId is null
GROUP BY oc.OrderId";
            return Get<RB_Goods_OrderCommission_Extend>(sql).ToList();
        }

        /// <summary>
        /// 获取所有订单已完成 未结算佣金的订单
        /// </summary>
        /// <param name="tenantId"></param>
        /// <param name="mallBaseId"></param>
        /// <returns></returns>
        public List<RB_Goods_OrderCommission_Extend> GetAllOrderCompletedNoCommissionList(int tenantId, int mallBaseId)
        {
            string sql = $@"SELECT oc.* FROM rb_goods_ordercommission oc 
INNER JOIN rb_goods_orderdetail od on oc.OrderDetailId = od.Id
INNER JOIN rb_goods_order o on oc.OrderId = o.OrderId
left join rb_goods_orderaftersale oa on oc.OrderId = oa.OrderId and oc.OrderDetailId = oa.OrderDetialId and oa.Type=1 and oa.Status=0 and oa.ReOrderStatus in (1,2,3,4,5)
where oc.TenantId={tenantId} and oc.MallBaseId={mallBaseId} and oc.CommissionState =1 and o.OrderStatus =5 and o.Recycled=2 and o.Status=0 and oa.ReOrderId is null
";
            return Get<RB_Goods_OrderCommission_Extend>(sql).ToList();
        }
    }
}
