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;
using Mall.Model.Query;

namespace Mall.Repository.Product
{
    /// <summary>
    /// 商品订单仓储层
    /// </summary>
    public class RB_Goods_OrderRepository : BaseRepository<RB_Goods_Order>
    {

        /// <summary>
        /// 获取订单分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<RB_Goods_Order_Extend> GetOrderPageList(int pageIndex, int pageSize, out long count, RB_Goods_Order_Extend dmodel)
        {
            string where = $" 1=1 and o.{nameof(RB_Goods_Order.Status)}=0";
            if (dmodel.TenantId > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.OrderId > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.OrderId)}={dmodel.OrderId}";
            }
            if (dmodel.OrderSource > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.OrderSource)}={(int)dmodel.OrderSource}";
            }
            if (dmodel.OrderType > 0)
            {
                where += $@" and od.{nameof(RB_Goods_OrderDetail.OrderType)}={dmodel.OrderType}";
            }
            if (dmodel.DeliveryMethod > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.DeliveryMethod)}={(int)dmodel.DeliveryMethod}";
            }
            if (!string.IsNullOrEmpty(dmodel.StartTime))
            {
                where += $@" and o.{nameof(RB_Goods_Order.CreateDate)} >='{dmodel.StartTime}'";
            }
            if (!string.IsNullOrEmpty(dmodel.EndTime))
            {
                where += $@" and o.{nameof(RB_Goods_Order.CreateDate)} <='{dmodel.EndTime} 23:59:59'";
            }
            if (dmodel.OrderStatus > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.OrderStatus)}={(int)dmodel.OrderStatus}";
            }
            if (dmodel.IsNotSelectCancel == 1)
            {
                where += $@" and o.{nameof(RB_Goods_Order.OrderStatus)} <> {(int)OrderStatusEnum.Cancel}";
                where += $@" and o.{nameof(RB_Goods_Order.Recycled)} =2";
            }
            if (!string.IsNullOrEmpty(dmodel.OrderStatusIds))
            {
                where += $@" and o.{nameof(RB_Goods_Order.OrderStatus)} in({dmodel.OrderStatusIds})";
            }
            if (!string.IsNullOrEmpty(dmodel.OrderNo))
            {
                where += $@" and o.{nameof(RB_Goods_Order.OrderNo)} like '%{dmodel.OrderNo}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.MerchantsNo))
            {
                where += $@" and o.{nameof(RB_Goods_Order.MerchantsNo)} like '%{dmodel.MerchantsNo}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.UserName))
            {
                where += $@" and u.{nameof(RB_Member_User.Name)} like '%{dmodel.UserName}%'";
            }
            if (dmodel.UserId > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.UserId)}={(int)dmodel.UserId}";
            }
            if (!string.IsNullOrEmpty(dmodel.GoodsName))
            {
                where += $@" and od.{nameof(RB_Goods_OrderDetail.GoodsName)} like '%{dmodel.GoodsName}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.Consignee))
            {
                where += $@" and o.{nameof(RB_Goods_Order.Consignee)} like '%{dmodel.Consignee}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.Mobile))
            {
                where += $@" and o.{nameof(RB_Goods_Order.Mobile)} like '%{dmodel.Mobile}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.ProductCode))
            {
                where += $@" and od.{nameof(RB_Goods_OrderDetail.ProductCode)} like '%{dmodel.ProductCode}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.AnchorName)) {
                where += $@" and o.{nameof(RB_Goods_Order.AnchorName)} like '%{dmodel.AnchorName}%'";
            }
            if (dmodel.Recycled > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.Recycled)}={(int)dmodel.Recycled}";
            }

            string sql = $@"SELECT o.*,u.Name as UserName FROM rb_goods_order o
INNER JOIN rb_goods_orderdetail od on o.OrderId=od.OrderId
INNER JOIN rb_member_user u on o.UserId=u.Id
where {where} group by o.OrderId order by o.CreateDate desc";
            return GetPage<RB_Goods_Order_Extend>(pageIndex, pageSize, out count, sql).ToList();
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<RB_Goods_Order_Extend> GetOrderList(RB_Goods_Order_Extend dmodel)
        {
            string where = $" 1=1 and o.{nameof(RB_Goods_Order.Status)}=0";
            if (dmodel.TenantId > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.OrderId > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.OrderId)}={dmodel.OrderId}";
            }
            if (!string.IsNullOrEmpty(dmodel.OrderIds))
            {
                where += $@" and o.{nameof(RB_Goods_Order.OrderId)} in({dmodel.OrderIds})";
            }
            if (!string.IsNullOrEmpty(dmodel.orderNos))
            {
                where += $@" and o.{nameof(RB_Goods_Order.OrderNo)} in({dmodel.orderNos})";
            }

            string sql = $@"SELECT o.* FROM rb_goods_order o
where {where} group by o.OrderId asc";
            return Get<RB_Goods_Order_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_Order_Extend> GetMembersToBuyPageList(int pageIndex, int pageSize, out long count, RB_Goods_Order_Extend dmodel)
        {
            string where = $" 1=1 and o.{nameof(RB_Goods_Order.Status)}=0";
            where += $@" and u.{nameof(RB_Member_User.MemberGrade)} >0";
            where += $@" and o.{nameof(RB_Goods_Order.OrderStatus)} in(2,3,4,5,6)";
            if (dmodel.TenantId > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.OrderId > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.OrderId)}={dmodel.OrderId}";
            }
            if (dmodel.OrderSource > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.OrderSource)}={(int)dmodel.OrderSource}";
            }
            //if (dmodel.OrderType > 0)
            //{
            //    where += $@" and od.{nameof(RB_Goods_OrderDetail.OrderType)}={dmodel.OrderType}";
            //}
            if (dmodel.DeliveryMethod > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.DeliveryMethod)}={(int)dmodel.DeliveryMethod}";
            }
            if (!string.IsNullOrEmpty(dmodel.StartTime))
            {
                where += $@" and o.{nameof(RB_Goods_Order.CreateDate)} >='{dmodel.StartTime}'";
            }
            if (!string.IsNullOrEmpty(dmodel.EndTime))
            {
                where += $@" and o.{nameof(RB_Goods_Order.CreateDate)} <='{dmodel.EndTime} 23:59:59'";
            }
            if (dmodel.OrderStatus > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.OrderStatus)}={(int)dmodel.OrderStatus}";
            }
            if (!string.IsNullOrEmpty(dmodel.OrderNo))
            {
                where += $@" and o.{nameof(RB_Goods_Order.OrderNo)} like '%{dmodel.OrderNo}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.MerchantsNo))
            {
                where += $@" and o.{nameof(RB_Goods_Order.MerchantsNo)} like '%{dmodel.MerchantsNo}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.UserName))
            {
                where += $@" and u.{nameof(RB_Member_User.Name)} like '%{dmodel.UserName}%'";
            }
            if (dmodel.UserId > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.UserId)}={(int)dmodel.UserId}";
            }
            //if (!string.IsNullOrEmpty(dmodel.GoodsName))
            //{
            //    where += $@" and od.{nameof(RB_Goods_OrderDetail.GoodsName)} like '%{dmodel.GoodsName}%'";
            //}
            if (!string.IsNullOrEmpty(dmodel.Consignee))
            {
                where += $@" and o.{nameof(RB_Goods_Order.Consignee)} like '%{dmodel.Consignee}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.Mobile))
            {
                where += $@" and o.{nameof(RB_Goods_Order.Mobile)} like '%{dmodel.Mobile}%'";
            }
            //if (!string.IsNullOrEmpty(dmodel.ProductCode))
            //{
            //    where += $@" and od.{nameof(RB_Goods_OrderDetail.ProductCode)} like '%{dmodel.ProductCode}%'";
            //}
            if (dmodel.Recycled > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.Recycled)}={(int)dmodel.Recycled}";
            }

            string sql = $@"SELECT o.*,u.Name as UserName FROM rb_goods_order o
INNER JOIN rb_member_user u on o.UserId=u.Id
where {where} order by o.CreateDate desc";
            return GetPage<RB_Goods_Order_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_Order_Extend> GetDistributionOrdersPageList(int pageIndex, int pageSize, out long count, RB_Goods_Order_Extend dmodel)
        {
            string where = $" 1=1 and o.{nameof(RB_Goods_Order.Status)}=0";
            where += $@" and o.{nameof(RB_Goods_Order.OrderStatus)} in(1,2,3,4,5,6)";
            if (dmodel.TenantId > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.OrderId > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.OrderId)}={dmodel.OrderId}";
            }
            if (dmodel.OrderSource > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.OrderSource)}={(int)dmodel.OrderSource}";
            }
            if (dmodel.OrderType > 0)
            {
                where += $@" and od.{nameof(RB_Goods_OrderDetail.OrderType)}={dmodel.OrderType}";
            }
            if (dmodel.DeliveryMethod > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.DeliveryMethod)}={(int)dmodel.DeliveryMethod}";
            }
            if (!string.IsNullOrEmpty(dmodel.StartTime))
            {
                where += $@" and o.{nameof(RB_Goods_Order.CreateDate)} >='{dmodel.StartTime}'";
            }
            if (!string.IsNullOrEmpty(dmodel.EndTime))
            {
                where += $@" and o.{nameof(RB_Goods_Order.CreateDate)} <='{dmodel.EndTime} 23:59:59'";
            }
            if (dmodel.OrderStatus > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.OrderStatus)}={(int)dmodel.OrderStatus}";
            }
            if (!string.IsNullOrEmpty(dmodel.OrderNo))
            {
                where += $@" and o.{nameof(RB_Goods_Order.OrderNo)} like '%{dmodel.OrderNo}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.MerchantsNo))
            {
                where += $@" and o.{nameof(RB_Goods_Order.MerchantsNo)} like '%{dmodel.MerchantsNo}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.UserName))
            {
                where += $@" and u.{nameof(RB_Member_User.Name)} like '%{dmodel.UserName}%'";
            }
            if (dmodel.UserId > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.UserId)}={(int)dmodel.UserId}";
            }
            if (!string.IsNullOrEmpty(dmodel.GoodsName))
            {
                where += $@" and od.{nameof(RB_Goods_OrderDetail.GoodsName)} like '%{dmodel.GoodsName}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.Consignee))
            {
                where += $@" and o.{nameof(RB_Goods_Order.Consignee)} like '%{dmodel.Consignee}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.Mobile))
            {
                where += $@" and o.{nameof(RB_Goods_Order.Mobile)} like '%{dmodel.Mobile}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.ProductCode))
            {
                where += $@" and od.{nameof(RB_Goods_OrderDetail.ProductCode)} like '%{dmodel.ProductCode}%'";
            }
            if (dmodel.Recycled > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.Recycled)}={(int)dmodel.Recycled}";
            }
            if (dmodel.DistributionUserId > 0) 
            {
                where += $@" and oc.{nameof(RB_Goods_OrderCommission.UserId)}={dmodel.DistributionUserId}";
            }

            string sql = $@"SELECT o.*,u.Name as UserName FROM rb_goods_order o
INNER JOIN rb_goods_orderdetail od on o.OrderId=od.OrderId
INNER JOIN rb_member_user u on o.UserId=u.Id
LEFT JOIN rb_goods_ordercommission oc on o.OrderId = oc.OrderId
where {where} group by o.OrderId order by o.CreateDate desc";
            return GetPage<RB_Goods_Order_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_Order_Extend> GetDistributionOrdersPageListForRecommend(int pageIndex, int pageSize, out long count, RB_Goods_Order_Extend dmodel)
        {
            string where = $" 1=1 and o.{nameof(RB_Goods_Order.Status)}=0";
            where += $@" and o.{nameof(RB_Goods_Order.OrderStatus)} in(1,2,3,4,5,6)";
            if (dmodel.TenantId > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.OrderId > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.OrderId)}={dmodel.OrderId}";
            }
            if (dmodel.OrderSource > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.OrderSource)}={(int)dmodel.OrderSource}";
            }
            if (dmodel.OrderType > 0)
            {
                where += $@" and od.{nameof(RB_Goods_OrderDetail.OrderType)}={dmodel.OrderType}";
            }
            if (dmodel.DeliveryMethod > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.DeliveryMethod)}={(int)dmodel.DeliveryMethod}";
            }
            if (!string.IsNullOrEmpty(dmodel.StartTime))
            {
                where += $@" and o.{nameof(RB_Goods_Order.CreateDate)} >='{dmodel.StartTime}'";
            }
            if (!string.IsNullOrEmpty(dmodel.EndTime))
            {
                where += $@" and o.{nameof(RB_Goods_Order.CreateDate)} <='{dmodel.EndTime} 23:59:59'";
            }
            if (dmodel.OrderStatus > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.OrderStatus)}={(int)dmodel.OrderStatus}";
            }
            if (!string.IsNullOrEmpty(dmodel.OrderNo))
            {
                where += $@" and o.{nameof(RB_Goods_Order.OrderNo)} like '%{dmodel.OrderNo}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.MerchantsNo))
            {
                where += $@" and o.{nameof(RB_Goods_Order.MerchantsNo)} like '%{dmodel.MerchantsNo}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.UserName))
            {
                where += $@" and u.{nameof(RB_Member_User.Name)} like '%{dmodel.UserName}%'";
            }
            if (dmodel.UserId > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.UserId)}={(int)dmodel.UserId}";
            }
            if (!string.IsNullOrEmpty(dmodel.GoodsName))
            {
                where += $@" and od.{nameof(RB_Goods_OrderDetail.GoodsName)} like '%{dmodel.GoodsName}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.Consignee))
            {
                where += $@" and o.{nameof(RB_Goods_Order.Consignee)} like '%{dmodel.Consignee}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.Mobile))
            {
                where += $@" and o.{nameof(RB_Goods_Order.Mobile)} like '%{dmodel.Mobile}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.ProductCode))
            {
                where += $@" and od.{nameof(RB_Goods_OrderDetail.ProductCode)} like '%{dmodel.ProductCode}%'";
            }
            if (dmodel.Recycled > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.Recycled)}={(int)dmodel.Recycled}";
            }
            if (dmodel.DistributionUserId > 0)
            {
                where += $@" and oc.{nameof(RB_Goods_OrderIntroduction.UserId)}={dmodel.DistributionUserId}";
            }

            string sql = $@"SELECT o.*,u.Name as UserName FROM rb_goods_order o
INNER JOIN rb_goods_orderdetail od on o.OrderId=od.OrderId
INNER JOIN rb_member_user u on o.UserId=u.Id
inner JOIN rb_goods_orderintroduction oc on o.OrderId = oc.OrderId
where {where} group by o.OrderId order by o.CreateDate desc";
            return GetPage<RB_Goods_Order_Extend>(pageIndex, pageSize, out count, sql).ToList();
        }


        /// <summary>
        /// 清空回收站
        /// </summary>
        /// <param name="tenantId"></param>
        /// <param name="mallBaseId"></param>
        /// <returns></returns>
        public bool SetOrderEmptyRecycleBin(int tenantId, int mallBaseId)
        {
            string sql = $" update rb_goods_order set {nameof(RB_Goods_Order.Status)}=1 where {nameof(RB_Goods_Order.Recycled)}=1 " +
                $"and {nameof(RB_Goods_Order.Status)}=0 and {nameof(RB_Goods_Order.TenantId)}={tenantId} and {nameof(RB_Goods_Order.MallBaseId)}={mallBaseId}";
            return Execute(sql) > 0;
        }

        /// <summary>
        /// 根据商品id   获取其订单相关统计
        /// </summary>
        /// <param name="payment_amount"></param>
        /// <param name="payment_num"></param>
        /// <param name="payment_order"></param>
        /// <param name="payment_people"></param>
        /// <param name="goodsId"></param>
        public void GetGoodsOrderPeopleInfo(out decimal payment_amount, out int payment_num, out int payment_order, out int payment_people, int goodsId)
        {
            payment_amount = 0;
            payment_num = payment_order = payment_people = 0;
            string sql1 = $@"SELECT SUM(t.Income) as Income,COUNT(0) as OrderNum FROM(
SELECT o.OrderId,o.Income FROM rb_goods_order o
INNER JOIN rb_goods_orderdetail od on o.OrderId=od.OrderId
WHERE od.GoodsId={goodsId} and o.`Status`=0 and o.OrderStatus <> 7 GROUP BY o.OrderId)t";
            string sql2 = $@"
SELECT SUM(od.Number) as OrderNum FROM rb_goods_order o
INNER JOIN rb_goods_orderdetail od on o.OrderId=od.OrderId
WHERE od.GoodsId={goodsId} and o.`Status`=0 and o.OrderStatus <> 7";
            string sql3 = $@"SELECT COUNT(UserId) AS OrderNum FROM(
SELECT o.UserId FROM rb_goods_order o
INNER JOIN rb_goods_orderdetail od on o.OrderId=od.OrderId
WHERE od.GoodsId={goodsId} and o.`Status`=0 and o.OrderStatus <> 7 GROUP BY o.UserId)t";
            var model1 = Get<RB_Goods_Order_Extend>(sql1).FirstOrDefault();
            var model2 = Get<RB_Goods_Order_Extend>(sql2).FirstOrDefault();
            var model3 = Get<RB_Goods_Order_Extend>(sql3).FirstOrDefault();
            if (model1 != null)
            {
                payment_amount = model1.Income ?? 0;
                payment_order = model1.OrderNum ?? 0;
            }
            if (model2 != null)
            {
                payment_num = model2.OrderNum ?? 0;
            }
            if (model3 != null)
            {
                payment_people = model3.OrderNum ?? 0;
            }

        }

        /// <summary>
        /// 根据商品ids  获取商品的订单销量
        /// </summary>
        /// <param name="goodsIds"></param>
        /// <returns></returns>
        public List<RB_Goods_Order_Extend> GetGoodsOrderNum(string goodsIds)
        {
            string sql2 = $@"
SELECT od.GoodsId ,SUM(od.Number) as OrderNum FROM rb_goods_order o
INNER JOIN rb_goods_orderdetail od on o.OrderId=od.OrderId
WHERE od.GoodsId in({goodsIds}) and o.`Status`=0 and o.OrderStatus <> 7 group by od.GoodsId";
            return Get<RB_Goods_Order_Extend>(sql2).ToList();
        }

        /// <summary>
        /// 获取商品订单数量统计
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="tenantId"></param>
        /// <param name="mallBaseId"></param>
        /// <returns></returns>
        public List<RB_Goods_Order_Extend> GetAppletGoodsOrderNumStatistics(int userId, int tenantId, int mallBaseId)
        {
            string sql = $" SELECT OrderStatus,COUNT(0) as OrderNum FROM rb_goods_order WHERE `Status`=0 and OrderStatus in (1,2,3) and UserId={userId} and TenantId={tenantId} and MallBaseId={mallBaseId} GROUP BY OrderStatus";
            return Get<RB_Goods_Order_Extend>(sql).ToList();
        }

        /// <summary>
        /// 获取待评价数量
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="tenantId"></param>
        /// <param name="mallBaseId"></param>
        /// <returns></returns>
        public int GetAppletGoodsOrderWaitCommentNum(int userId, int tenantId, int mallBaseId)
        {
            string sql = $@"SELECT COUNT(0) as OrderNum FROM(
SELECT o.OrderId FROM rb_goods_order o
INNER JOIN rb_goods_orderdetail od on o.OrderId=od.OrderId
 WHERE o.`Status`=0 and o.OrderStatus =4 and od.IsComment=2 and o.UserId={userId} and o.TenantId={tenantId} and o.MallBaseId={mallBaseId}
GROUP BY o.OrderId)t";
            var obj = ExecuteScalar(sql);
            if (obj != null)
            {
                return Convert.ToInt32(obj);
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// 获取售后处理中
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="tenantId"></param>
        /// <param name="mallBaseId"></param>
        /// <returns></returns>
        public int GetAppletGoodsOrderAfterSaleNum(int userId, int tenantId, int mallBaseId)
        {
            string sql = $"SELECT COUNT(0) as OrderNum FROM rb_goods_orderaftersale WHERE `Status`=0 and ReOrderStatus <5 and UserId={userId} and TenantId={tenantId} and MallBaseId={mallBaseId}";
            var obj = ExecuteScalar(sql);
            if (obj != null)
            {
                return Convert.ToInt32(obj);
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// 获取用户订单数量+销售额
        /// </summary>
        /// <param name="userIds"></param>
        /// <param name="tenantId"></param>
        /// <param name="mallBaseId"></param>
        /// <returns></returns>
        public List<RB_Goods_Order_Extend> GetUserOrderNumAndMoney(string userIds, int tenantId, int mallBaseId)
        {
            string sql = $@"SELECT UserId,SUM(Income) as Income,count(0) as OrderNum FROM rb_goods_order WHERE OrderStatus in (2,3,4,5,6) and Recycled=2 and UserId in({userIds}) and TenantId={tenantId} and MallBaseId={mallBaseId} GROUP BY UserId";
            return Get<RB_Goods_Order_Extend>(sql).ToList();
        }

        /// <summary>
        /// 获取所有订单已完成 未结算佣金的订单
        /// </summary>
        /// <param name="tenantId"></param>
        /// <param name="mallBaseId"></param>
        /// <returns></returns>
        public List<RB_Goods_Order_Extend> GetAllOrderCompletedNoCommission(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.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 oa.ReOrderId is null
GROUP BY oc.OrderId";
            return Get<RB_Goods_Order_Extend>(sql).ToList();
        }

        /// <summary>
        /// 获取已退款  待制单的订单列表
        /// </summary>
        /// <param name="tenantId"></param>
        /// <param name="mallBaseId"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public List<RB_Goods_Order_Extend> GetOrderRefunedCreateList(int tenantId, int mallBaseId, string startTime, string endTime)
        {
            string sql = $@"SELECT * FROM rb_goods_order o
WHERE o.TenantId={tenantId} and o.MallBaseId={mallBaseId} and o.OrderStatus = 7 AND o.PaymentTime IS NOT NULL AND DATE_FORMAT(o.PaymentTime,'%Y-%m-%d') <> DATE_FORMAT(o.CancelTime,'%Y-%m-%d') AND o.CancelTime>='{startTime}' AND o.CancelTime <'{endTime}'";
            return Get<RB_Goods_Order_Extend>(sql).ToList();
        }


        #region 数据统计-数据概况
        /// <summary>
        /// 数据概况
        /// </summary>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<DataStatistics_Query> GetOrdertSatistics(DataStatistics_Query dmodel)
        {
            string where = $"   ";
            if (dmodel.TenantId > 0)
            {
                where += $@" and {nameof(RB_Goods_Order.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and {nameof(RB_Goods_Order.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.PlatformType > 0)
            {
                where += $@" and {nameof(RB_Goods_Order.OrderSource)}={(int)dmodel.PlatformType}";
            }
            if (!string.IsNullOrWhiteSpace(dmodel.StartDate))
            {
                where += $" AND DATE_FORMAT({nameof(RB_Goods_Order.CreateDate)},'%Y-%m-%d' )>= DATE_FORMAT('{dmodel.StartDate}','%Y-%m-%d' )";
            }
            if (!string.IsNullOrWhiteSpace(dmodel.EndDate))
            {
                where += $" AND DATE_FORMAT({nameof(RB_Goods_Order.CreateDate)},'%Y-%m-%d' )<= DATE_FORMAT('{dmodel.EndDate}','%Y-%m-%d' )";
            }
            string sql = $@"SELECT SUM(MemberNum)as MemberNum,SUM(GoodsNum)as GoodsNum,SUM(NotDeliverNum)as NotDeliverNum,SUM(OrderNum)as OrderNum,SUM(AfterSale)as AfterSale
 from (SELECT 0 as Total,COUNT(*) as MemberNum,0 as GoodsNum,0 as OrderNum,
0 as NotDeliverNum,0 as AfterSale from rb_member_user  where Blacklist=0  {where}
union all 
SELECT 0 as Total,0 as MemberNum, COUNT(*) as GoodsNum,0 as OrderNum,
0 as NotDeliverNum,0 as AfterSale from rb_goods  where Status=0  {where}
union all 
SELECT 0 as Total,0 as MemberNum, 0 as GoodsNum,COUNT(*) as OrderNum,
0 as NotDeliverNum,0 as AfterSale from rb_goods_order  where  1=1  {where}
union all 
SELECT 0 as Total,0 as MemberNum, 0 as GoodsNum,0 as OrderNum,
COUNT(*) as NotDeliverNum,0 as AfterSale from rb_goods_order   where  OrderStatus=2 {where}
union all 
SELECT 0 as Total,0 as MemberNum, 0 as GoodsNum,0 as OrderNum,
0 as NotDeliverNum,COUNT(*) as AfterSale from rb_goods_order where 1=1 {where}
) as t GROUP BY t.Total ";
            return Get<DataStatistics_Query>(sql).ToList();
        }


        /// <summary>
        /// 获取近一周的数据
        /// </summary>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<RB_Goods_Order_Extend> GetWeeklyOrder(DataStatistics_Query dmodel)
        {
            string where = $"   where 1=1  and  a.OrderStatus in(2,3,4,6,5)";
            string orderTypeWhere = "";
            if (dmodel.TenantId > 0)
            {
                where += $@" and a.{nameof(RB_Goods_Order.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and a.{nameof(RB_Goods_Order.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.PlatformType > 0)
            {
                where += $@" and a.{nameof(RB_Goods_Order.OrderSource)}={(int)dmodel.PlatformType}";
            }
            if (dmodel.OrderType > 0)
            {
                orderTypeWhere += $@" and {nameof(RB_Goods_OrderDetail.OrderType)}={dmodel.OrderType}";
            }
            if (!string.IsNullOrWhiteSpace(dmodel.StartDate))
            {
                where += $" AND DATE_FORMAT(a.{nameof(RB_Goods_Order.CreateDate)},'%Y-%m-%d' )>= DATE_FORMAT('{dmodel.StartDate}','%Y-%m-%d' )";
            }
            if (!string.IsNullOrWhiteSpace(dmodel.EndDate))
            {
                where += $" AND DATE_FORMAT(a.{nameof(RB_Goods_Order.CreateDate)},'%Y-%m-%d' )<= DATE_FORMAT('{dmodel.EndDate}','%Y-%m-%d' )";
            }
            string sql = $@" select a.*,b.GoodsTotalNum from  rb_goods_order as a LEFT JOIN (SELECT OrderId,COUNT(*) as GoodsTotalNum from rb_goods_orderdetail where 1=1 {orderTypeWhere}  GROUP BY OrderId)as b
on a.OrderId=b.OrderId {where}";
            return Get<RB_Goods_Order_Extend>(sql).ToList();
        }



        /// <summary>
        /// 商品购买力TOP排行
        /// </summary>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<DataStatistics_Query> GetGoosTop(DataStatistics_Query dmodel)
        {
            string where = $"   where 1=1 ";
            if (dmodel.TenantId > 0)
            {
                where += $@" and b.{nameof(RB_Goods_Order.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and b.{nameof(RB_Goods_Order.MallBaseId)}={dmodel.MallBaseId}";
            }
            string sql = $@" SELECT a.GoodsId,c.`Name`,SUM(a.Final_Price) as PayMoney ,SUM(a.Number) as BuyNum from rb_goods_orderdetail as a
LEFT JOIN rb_goods_order as b on a.OrderId=b.OrderId
LEFT JOIN rb_goods as c on a.GoodsId=c.Id  {where}
 GROUP BY  a.GoodsId ORDER BY SUM(a.Final_Price) desc LIMIT 100 ";
            return Get<DataStatistics_Query>(sql).ToList();
        }

        /// <summary>
        /// 用户购买力TOP排行
        /// </summary>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<DataStatistics_Query> GetMemberTop(DataStatistics_Query dmodel)
        {
            string where = $"   where 1=1 and  b.OrderStatus in(2,3,4,6,5) ";
            if (dmodel.TenantId > 0)
            {
                where += $@" and b.{nameof(RB_Goods_Order.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and b.{nameof(RB_Goods_Order.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.PlatformType > 0)
            {
                where += $@" and b.{nameof(RB_Goods_Order.OrderSource)}={(int)dmodel.PlatformType}";
            }
            if (!string.IsNullOrWhiteSpace(dmodel.StartDate))
            {
                where += $" AND DATE_FORMAT(b.{nameof(RB_Goods_Order.CreateDate)},'%Y-%m-%d' )>= DATE_FORMAT('{dmodel.StartDate}','%Y-%m-%d' )";
            }
            if (!string.IsNullOrWhiteSpace(dmodel.EndDate))
            {
                where += $" AND DATE_FORMAT(b.{nameof(RB_Goods_Order.CreateDate)},'%Y-%m-%d' )<= DATE_FORMAT('{dmodel.EndDate}','%Y-%m-%d' )";
            }
            string sql = $@" SELECT MAX(c.`Name`) as Name,SUM(a.Final_Price) as PayMoney ,SUM(a.Number) as BuyNum 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
 GROUP BY  c.Id ORDER BY SUM(a.Final_Price) desc LIMIT 100 ";
            return Get<DataStatistics_Query>(sql).ToList();
        }

        #endregion


        #region 销售报表
        /// <summary>
        /// 销售统计
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<DataStatistics_Query> GetSellPageList(int pageIndex, int pageSize, out long count, DataStatistics_Query dmodel)
        {
            string where = $"   where 1=1  ";
            string payMemerNumWhere = "";
            string orderTypeWhere = "";
            string sql = "";
            if (dmodel.TenantId > 0)
            {
                where += $@" and a.{nameof(RB_Goods_Order.TenantId)}={dmodel.TenantId}";
                payMemerNumWhere += $@" and {nameof(RB_Goods_Order.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and a.{nameof(RB_Goods_Order.MallBaseId)}={dmodel.MallBaseId}";
                payMemerNumWhere += $@" and {nameof(RB_Goods_Order.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.PlatformType > 0)
            {
                where += $@" and a.{nameof(RB_Goods_Order.OrderSource)}={(int)dmodel.PlatformType}";
                payMemerNumWhere += $@" and {nameof(RB_Goods_Order.OrderSource)}={(int)dmodel.PlatformType}";
            }
            if (dmodel.OrderType > 0)
            {
                orderTypeWhere += $@" and {nameof(RB_Goods_OrderDetail.OrderType)}={dmodel.OrderType}";
            }
            //if (dmodel.OrderType > 0)
            //{
            //    where += $@" and a.{nameof(RB_Goods_Order.or)}={(int)dmodel.OrderType}";
            //    payMemerNumWhere += $@" and {nameof(RB_Goods_Order.OrderSource)}={(int)dmodel.PlatformType}";
            //}


            if (dmodel.OrderStatus == 1 || dmodel.OrderStatus == 0)
            {
                where += $@" and a.{nameof(RB_Goods_Order.OrderStatus)}  in (2,4,3,5,6)";
                payMemerNumWhere += $@" and {nameof(RB_Goods_Order.OrderStatus)} in (2,4,5,3,6)";
            }
            else if (dmodel.OrderStatus == 2)
            {
                where += $@" and a.{nameof(RB_Goods_Order.OrderStatus)}=5";
                payMemerNumWhere += $@" and {nameof(RB_Goods_Order.OrderStatus)}=5";
            }
            else if (dmodel.OrderStatus == 3)
            {
                where += $@" and a.{nameof(RB_Goods_Order.OrderStatus)}=7";
                payMemerNumWhere += $@" and {nameof(RB_Goods_Order.OrderStatus)}=7";
            }
            else if (dmodel.OrderStatus == 4)//后面做因为现在还没有售后表
            {
                where += $@" and a.{nameof(RB_Goods_OrderAfterSale.ReOrderStatus)}  in (1,2,4,3)";
            }
            else if (dmodel.OrderStatus == 5)//后面做因为现在还没有售后表
            {
                where += $@" and a.{nameof(RB_Goods_OrderAfterSale.ReOrderStatus)} =5";
            }
        

            if (!string.IsNullOrWhiteSpace(dmodel.StartDate))
            {
                where += $" AND DATE_FORMAT(a.{nameof(RB_Goods_Order.CreateDate)},'%Y-%m-%d' )>= DATE_FORMAT('{dmodel.StartDate}','%Y-%m-%d' )";
                // payMemerNumWhere += $" AND DATE_FORMAT({nameof(RB_Goods_Order.CreateDate)},'%Y-%m-%d' )>= DATE_FORMAT('{dmodel.StartDate}','%Y-%m-%d' )";
                payMemerNumWhere += $" AND DATE_FORMAT(a.{nameof(RB_Goods_OrderAfterSale.CreateDate)},'%Y-%m-%d' )>= DATE_FORMAT('{dmodel.StartDate}','%Y-%m-%d' )";
            }
            if (!string.IsNullOrWhiteSpace(dmodel.EndDate))
            {
                where += $" AND DATE_FORMAT(a.{nameof(RB_Goods_Order.CreateDate)},'%Y-%m-%d' )<= DATE_FORMAT('{dmodel.EndDate}','%Y-%m-%d' )";
                payMemerNumWhere += $" AND DATE_FORMAT(a.{nameof(RB_Goods_OrderAfterSale.CreateDate)},'%Y-%m-%d' )<= DATE_FORMAT('{dmodel.EndDate}','%Y-%m-%d' )";
                // payMemerNumWhere += $" AND DATE_FORMAT({nameof(RB_Goods_Order.CreateDate)},'%Y-%m-%d' )<= DATE_FORMAT('{dmodel.EndDate}','%Y-%m-%d' )";
            }

            //            string sql = $@"SELECT t.CreateDate,SUM(t.GoodsNum)as GoodsNum ,COUNT(t.OrderId)as OrderNum ,SUM(t.Income)as PayMoney,
            //(SELECT COUNT(distinct UserId) from rb_goods_order where DATE_FORMAT(CreateDate,'%Y-%m-%d' )=t.CreateDate    {payMemerNumWhere} ) as PayMemerNum
            //from (SELECT DATE_FORMAT(a.CreateDate,'%Y-%m-%d' )as CreateDate,b.GoodsNum,a.UserId,a.Income,a.OrderId from rb_goods_order as  a
            // LEFT JOIN (SELECT OrderId,SUM(Number)as GoodsNum from rb_goods_orderdetail where 1=1 {orderTypeWhere} GROUP BY OrderId)as b on a.OrderId=b.OrderId  {where})
            //as t GROUP BY t.CreateDate desc";

            if (dmodel.OrderStatus == 0 || dmodel.OrderStatus == 1 || dmodel.OrderStatus == 2 || dmodel.OrderStatus == 3)
            {
                sql = $@"SELECT t.CreateDate,SUM(t.GoodsNum)as GoodsNum ,COUNT(t.OrderId)as OrderNum ,SUM(t.Income)as PayMoney,MAX(t.PayMemerNum) as PayMemerNum
from (SELECT DATE_FORMAT(a.CreateDate,'%Y-%m-%d' )as CreateDate,b.GoodsNum,a.UserId,a.Income,a.OrderId,c.PayMemerNum from rb_goods_order as  a
 LEFT JOIN (SELECT OrderId,SUM(Number)as GoodsNum from rb_goods_orderdetail where 1=1 {orderTypeWhere} GROUP BY OrderId)as b on a.OrderId=b.OrderId 
LEFT JOIN (SELECT COUNT(DISTINCT UserId) as PayMemerNum, DATE_FORMAT(CreateDate,'%Y-%m-%d' ) as CreateDate from rb_goods_order  where 1=1 {payMemerNumWhere} GROUP BY DATE_FORMAT(CreateDate,'%Y-%m-%d' ))
as c on c.CreateDate=DATE_FORMAT(a.CreateDate,'%Y-%m-%d' )  {where} )
as t GROUP BY t.CreateDate desc";
            }
            else 
            {
                sql = $@"SELECT t.CreateDate,SUM(t.ReNumber)as GoodsNum ,COUNT(t.ReOrderId)as OrderNum ,SUM(t.Income)as PayMoney,MAX(t.PayMemerNum) as PayMemerNum
from (SELECT DATE_FORMAT(a.CreateDate,'%Y-%m-%d' )as CreateDate,a.ReNumber,a.Refund as Income,c.PayMemerNum,a.ReOrderId from RB_Goods_OrderAfterSale as a 
LEFT JOIN (SELECT COUNT(DISTINCT UserId) as PayMemerNum, DATE_FORMAT(CreateDate,'%Y-%m-%d' ) as CreateDate from RB_Goods_OrderAfterSale  where {payMemerNumWhere} GROUP BY DATE_FORMAT(CreateDate,'%Y-%m-%d' ))
as c on c.CreateDate=DATE_FORMAT(a.CreateDate,'%Y-%m-%d' )  {where} )
as t GROUP BY t.CreateDate desc";
            }
//            else if (dmodel.OrderStatus == 4)
//            {
//                sql = $@"SELECT t.CreateDate,SUM(t.ReNumber)as GoodsNum ,COUNT(t.ReOrderId)as OrderNum ,SUM(t.Income)as PayMoney,MAX(t.PayMemerNum) as PayMemerNum
//from (SELECT DATE_FORMAT(a.CreateDate,'%Y-%m-%d' )as CreateDate,a.ReNumber,a.Refund as Income,c.PayMemerNum,a.ReOrderId from RB_Goods_OrderAfterSale as a 
//LEFT JOIN (SELECT COUNT(DISTINCT UserId) as PayMemerNum, DATE_FORMAT(CreateDate,'%Y-%m-%d' ) as CreateDate from RB_Goods_OrderAfterSale  where {payMemerNumWhere} GROUP BY DATE_FORMAT(CreateDate,'%Y-%m-%d' ))
//as c on c.CreateDate=DATE_FORMAT(a.CreateDate,'%Y-%m-%d' )  {where} )
//as t GROUP BY t.CreateDate desc";
//            }
//            else if (dmodel.OrderStatus == 5)
//            {
//                sql = $@"SELECT t.CreateDate,SUM(t.ReNumber)as GoodsNum ,COUNT(t.ReOrderId)as OrderNum ,SUM(t.Income)as PayMoney,MAX(t.PayMemerNum) as PayMemerNum
//from (SELECT DATE_FORMAT(a.CreateDate,'%Y-%m-%d' )as CreateDate,a.ReNumber,a.RefundActual as Income,c.PayMemerNum,a.ReOrderId from RB_Goods_OrderAfterSale as a 
//LEFT JOIN (SELECT COUNT(DISTINCT UserId) as PayMemerNum, DATE_FORMAT(CreateDate,'%Y-%m-%d' ) as CreateDate from RB_Goods_OrderAfterSale  where {payMemerNumWhere} GROUP BY DATE_FORMAT(CreateDate,'%Y-%m-%d' ))
//as c on c.CreateDate=DATE_FORMAT(a.CreateDate,'%Y-%m-%d' )  {where} )
//as t GROUP BY t.CreateDate desc";
//            }

            return GetPage<DataStatistics_Query>(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<DataStatistics_Query> GetSellExcel(DataStatistics_Query dmodel)
{
    string where = $"   where 1=1  ";
    string payMemerNumWhere = "";
    string orderTypeWhere = "";
    if (dmodel.TenantId > 0)
    {
        where += $@" and a.{nameof(RB_Goods_Order.TenantId)}={dmodel.TenantId}";
        payMemerNumWhere += $@" and {nameof(RB_Goods_Order.TenantId)}={dmodel.TenantId}";
    }
    if (dmodel.MallBaseId > 0)
    {
        where += $@" and a.{nameof(RB_Goods_Order.MallBaseId)}={dmodel.MallBaseId}";
        payMemerNumWhere += $@" and {nameof(RB_Goods_Order.MallBaseId)}={dmodel.MallBaseId}";
    }
    if (dmodel.PlatformType > 0)
    {
        where += $@" and a.{nameof(RB_Goods_Order.OrderSource)}={(int)dmodel.PlatformType}";
        payMemerNumWhere += $@" and {nameof(RB_Goods_Order.OrderSource)}={(int)dmodel.PlatformType}";
    }

    if (dmodel.OrderType > 0)
    {
        orderTypeWhere += $@" and {nameof(RB_Goods_OrderDetail.OrderType)}={dmodel.OrderType}";
    }
    if (dmodel.OrderStatus > 0)
    {
        if (dmodel.OrderStatus == 1)
        {
            where += $@" and a.{nameof(RB_Goods_Order.OrderStatus)} in(1,2,4,3)";
            payMemerNumWhere += $@" and {nameof(RB_Goods_Order.OrderStatus)}in(1,2,4,3)";
        }
        else if (dmodel.OrderStatus == 2)
        {
            where += $@" and a.{nameof(RB_Goods_Order.OrderStatus)}=5";
            payMemerNumWhere += $@" and {nameof(RB_Goods_Order.OrderStatus)}=5";
        }
        else if (dmodel.OrderStatus == 3)
        {
            where += $@" and a.{nameof(RB_Goods_Order.OrderStatus)}=7";
            payMemerNumWhere += $@" and {nameof(RB_Goods_Order.OrderStatus)}=7";
        }
        else if (dmodel.OrderStatus == 4)//后面做因为现在还没有售后表
        {

        }
        else if (dmodel.OrderStatus == 5)//后面做因为现在还没有售后表
        {

        }
    }
    if (!string.IsNullOrWhiteSpace(dmodel.StartDate))
    {
        where += $" AND DATE_FORMAT(a.{nameof(RB_Goods_Order.CreateDate)},'%Y-%m-%d' )>= DATE_FORMAT('{dmodel.StartDate}','%Y-%m-%d' )";
        payMemerNumWhere += $" AND DATE_FORMAT({nameof(RB_Goods_Order.CreateDate)},'%Y-%m-%d' )>= DATE_FORMAT('{dmodel.StartDate}','%Y-%m-%d' )";
    }
    if (!string.IsNullOrWhiteSpace(dmodel.EndDate))
    {
        where += $" AND DATE_FORMAT(a.{nameof(RB_Goods_Order.CreateDate)},'%Y-%m-%d' )<= DATE_FORMAT('{dmodel.EndDate}','%Y-%m-%d' )";
        payMemerNumWhere += $" AND DATE_FORMAT({nameof(RB_Goods_Order.CreateDate)},'%Y-%m-%d' )<= DATE_FORMAT('{dmodel.EndDate}','%Y-%m-%d' )";
    }

    string sql = $@"SELECT t.CreateDate,SUM(t.GoodsNum)as GoodsNum ,COUNT(t.OrderId)as OrderNum ,SUM(t.Income)as PayMoney,
(SELECT COUNT(UserId) from rb_goods_order where DATE_FORMAT(CreateDate,'%Y-%m-%d' )=t.CreateDate    {payMemerNumWhere}    GROUP BY UserId) as PayMemerNum
from (SELECT DATE_FORMAT(a.CreateDate,'%Y-%m-%d' )as CreateDate,b.GoodsNum,a.UserId,a.Income,a.OrderId from rb_goods_order as  a
 LEFT JOIN (SELECT OrderId,SUM(Number)as GoodsNum from rb_goods_orderdetail where 1=1 {orderTypeWhere}  GROUP BY OrderId)as b on a.OrderId=b.OrderId  {where})
as t GROUP BY t.CreateDate desc";
    return Get<DataStatistics_Query>(sql).ToList();
}
#endregion

#region 损益核算表
public List<OrderStatistics_Query> GetOrderProfitLossList(int pageIndex, int pageSize, out long count, OrderStatistics_Query dmodel)
{
    string where = "where 1=1 and a.OrderStatus in(2,3,4,5,6) ";

    if (dmodel != null)
    {
        if (!string.IsNullOrWhiteSpace(dmodel.OrderNo))
        {
            where += $" and  a.OrderNo like'%{dmodel.OrderNo}%'";
        }

        if (dmodel.SupplierId > 0)
        {
            where += $" and  b.SupplierId={dmodel.SupplierId} ";
        }
        if (dmodel.OneUserId > 0)
        {
            where += $" and  c.OneUserId={dmodel.OneUserId} and d.TwoUserId={dmodel.OneUserId} ";
        }
        if (dmodel.GoodsID > 0)
        {
            where += $" and  b.GoodsId={dmodel.GoodsID} ";
        }

        if (!string.IsNullOrWhiteSpace(dmodel.StartDate))
        {
            where += $" and  DATE_FORMAT(a.CreateDate,'%Y-%m-%d' )>=DATE_FORMAT('{dmodel.StartDate}','%Y-%m-%d' ) ";
        }
        if (!string.IsNullOrWhiteSpace(dmodel.EndDate))
        {
            where += $" and  DATE_FORMAT(a.CreateDate,'%Y-%m-%d' )< DATE_FORMAT('{dmodel.EndDate}','%Y-%m-%d' ) ";
        }

    }
    string sql = $@"  SELECT MAX( a.OrderNo) as OrderNo,MAX(b.GoodsName) as GoodsName,b.Specification,MAX(b.Unit_Price) as Unit_Price,SUM(b.Number) as Number,f.`Name` as SupplierName,
SUM(b.Final_Price) as Final_Price,SUM(b.FreightMoney) as FreightMoney,SUM(b.Final_Price+b.FreightMoney) as AllPrice ,
SUM(IFNULL(b.CostMoney,0)*b.Number) as CostMoney,0 as PackingMoney, 0 as CostFreight,0 as GoodsFreight,
c.OneCommission,c.OneUserId,c.OneUserName,d.TwoCommission,d.TwoUserId,d.TwoUserName,0 as OtherPrice,e.ALLCommission,0 as Paid
from rb_goods_orderdetail as b
LEFT JOIN rb_goods_order as A on a.OrderId=b.OrderId
LEFT JOIN rb_supplier as f on f.ID=b.SupplierId
LEFT JOIN (SELECT SUM(goc.Commission) as OneCommission,goc.OrderDetailId,goc.UserId as OneUserId,mu.`Name` as OneUserName from rb_goods_ordercommission as goc LEFT JOIN rb_member_user as mu on goc.UserId=mu.Id  where goc.Grade=1 and goc.CommissionState=2  GROUP BY goc.OrderDetailId ) as c on b.Id=c.OrderDetailId
LEFT JOIN (SELECT SUM(goc.Commission) as TwoCommission,goc.OrderDetailId,goc.UserId as TwoUserId,mu.`Name` as TwoUserName from rb_goods_ordercommission  as goc LEFT JOIN rb_member_user as mu on goc.UserId=mu.Id where goc.Grade=2 and goc.CommissionState=2  GROUP BY goc.OrderDetailId ) as d on b.Id=d.OrderDetailId
LEFT JOIN (SELECT SUM(Commission) as ALLCommission,OrderDetailId from rb_goods_ordercommission  GROUP BY OrderId ) as e on b.Id=e.OrderDetailId {where}
GROUP BY b.GoodsId,b.SupplierId,b.OrderId ";
    return GetPage<OrderStatistics_Query>(pageIndex, pageSize, out count, sql).ToList();
}



public List<OrderStatistics_Query> GetAllOrderProfitLossList(OrderStatistics_Query dmodel)
{
    string where = "where 1=1 and a.OrderStatus in(2,3,4,5,6) ";

    if (dmodel != null)
    {
        if (!string.IsNullOrWhiteSpace(dmodel.OrderNo))
        {
            where += $" and  a.OrderNo like'%{dmodel.OrderNo}%'";
        }

        if (dmodel.SupplierId > 0)
        {
            where += $" and  b.SupplierId={dmodel.SupplierId} ";
        }
        if (dmodel.OneUserId > 0)
        {
            where += $" and  c.OneUserId={dmodel.OneUserId} and d.TwoUserId={dmodel.OneUserId} ";
        }
        if (dmodel.GoodsID > 0)
        {
            where += $" and  b.GoodsId={dmodel.GoodsID} ";
        }

        if (!string.IsNullOrWhiteSpace(dmodel.StartDate))
        {
            where += $" and  DATE_FORMAT(a.CreateDate,'%Y-%m-%d' )>=DATE_FORMAT('{dmodel.StartDate}','%Y-%m-%d' ) ";
        }
        if (!string.IsNullOrWhiteSpace(dmodel.EndDate))
        {
            where += $" and  DATE_FORMAT(a.CreateDate,'%Y-%m-%d' )< DATE_FORMAT('{dmodel.EndDate}','%Y-%m-%d' ) ";
        }

    }


    string sql = $@"  SELECT MAX( a.OrderNo) as OrderNo,MAX(b.GoodsName) as GoodsName,b.Specification,MAX(b.Unit_Price) as Unit_Price,SUM(b.Number) as Number,f.`Name` as SupplierName,
SUM(b.Final_Price) as Final_Price,SUM(b.FreightMoney) as FreightMoney,SUM(b.Final_Price+b.FreightMoney) as AllPrice ,
SUM(IFNULL(b.CostMoney,0)*b.Number) as CostMoney,0 as PackingMoney, 0 as CostFreight,0 as GoodsFreight,
c.OneCommission,c.OneUserId,c.OneUserName,d.TwoCommission,d.TwoUserId,d.TwoUserName,0 as OtherPrice,e.ALLCommission,0 as Paid
from rb_goods_orderdetail as b
LEFT JOIN rb_goods_order as A on a.OrderId=b.OrderId
LEFT JOIN rb_supplier as f on f.ID=b.SupplierId
LEFT JOIN (SELECT SUM(goc.Commission) as OneCommission,goc.OrderDetailId,goc.UserId as OneUserId,mu.`Name` as OneUserName from rb_goods_ordercommission as goc LEFT JOIN rb_member_user as mu on goc.UserId=mu.Id  where goc.Grade=1 and goc.CommissionState=2  GROUP BY goc.OrderDetailId ) as c on b.Id=c.OrderDetailId
LEFT JOIN (SELECT SUM(goc.Commission) as TwoCommission,goc.OrderDetailId,goc.UserId as TwoUserId,mu.`Name` as TwoUserName from rb_goods_ordercommission  as goc LEFT JOIN rb_member_user as mu on goc.UserId=mu.Id where goc.Grade=2 and goc.CommissionState=2  GROUP BY goc.OrderDetailId ) as d on b.Id=d.OrderDetailId
LEFT JOIN (SELECT SUM(Commission) as ALLCommission,OrderDetailId from rb_goods_ordercommission  GROUP BY OrderId ) as e on b.Id=e.OrderDetailId {where}
GROUP BY b.GoodsId,b.SupplierId,b.OrderId ";
    //            string sql = $@" SELECT MAX( a.OrderNo) as OrderNo,MAX(b.GoodsName) as GoodsName,b.Specification,MAX(b.Unit_Price) as Unit_Price,SUM(b.Number) as Number,f.`Name` as SupplierName,
    //SUM(b.Final_Price) as Final_Price,SUM(b.FreightMoney) as FreightMoney,SUM(b.Final_Price+b.FreightMoney) as AllPrice ,
    //SUM(IFNULL(b.CostMoney,0)*b.Number) as CostMoney,0 as PackingMoney, 0 as CostFreight,0 as GoodsFreight,
    //c.OneCommission,d.TwoCommission,0 as OtherPrice,e.ALLCommission,0 as Paid
    //from rb_goods_orderdetail as b
    //LEFT JOIN rb_goods_order as A on a.OrderId=b.OrderId
    //LEFT JOIN rb_supplier as f on f.ID=b.SupplierId
    //LEFT JOIN (SELECT SUM(Commission) as OneCommission,OrderId from rb_goods_ordercommission  where Grade=1 and CommissionState=2  GROUP BY OrderId ) as c on a.OrderId=c.OrderId
    //LEFT JOIN (SELECT SUM(Commission) as TwoCommission,OrderId from rb_goods_ordercommission  where Grade=2 and CommissionState=2  GROUP BY OrderId ) as d on a.OrderId=d.OrderId
    //LEFT JOIN (SELECT SUM(Commission) as ALLCommission,OrderId from rb_goods_ordercommission  GROUP BY OrderId ) as e on a.OrderId=e.OrderId {where}
    //GROUP BY b.GoodsId,b.SupplierId,b.OrderId ";
    return Get<OrderStatistics_Query>(sql).ToList();
}



/// <summary>
/// 获取用户  订单下的商品数量
/// </summary>
/// <param name="userId"></param>
/// <param name="limitGoodsIds"></param>
/// <returns></returns>
public List<RB_Goods_Order_Extend> GetUserOrderGoodsNum(int userId, string limitGoodsIds, int tenantId, int mallBaseId)
{
    string sql = $@"SELECT od.GoodsId,SUM(od.Number) as GoodsTotalNum FROM rb_goods_orderdetail od
INNER JOIN rb_goods_order o on od.OrderId=o.OrderId
WHERE od.TenantId={tenantId} and od.MallBaseId={mallBaseId} and o.`Status`=0 and  o.OrderStatus in (1,2,3,4,5,6) and o.Recycled=2 AND o.UserId={userId} and od.GoodsId in ({limitGoodsIds})
GROUP BY od.GoodsId";

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

/// <summary>
/// 获取用户  商品订单得数量
/// </summary>
/// <param name="userId"></param>
/// <param name="limitGoodsIds"></param>
/// <param name="tenantId"></param>
/// <param name="mallBaseId"></param>
/// <returns></returns>
public List<RB_Goods_Order_Extend> GetUserOrderNum(int userId, string limitGoodsIds, int tenantId, int mallBaseId)
{
    string sql = $@"SELECT t.GoodsId,COUNT(0) as OrderNum FROM(
SELECT od.GoodsId,o.* FROM rb_goods_orderdetail od
INNER JOIN rb_goods_order o on od.OrderId=o.OrderId
WHERE od.TenantId={tenantId} and od.MallBaseId={mallBaseId} and o.`Status`=0 and  o.OrderStatus in (1,2,3,4,5,6) and o.Recycled=2 AND o.UserId={userId} and od.GoodsId in ({limitGoodsIds})
GROUP BY od.GoodsId,o.OrderId
)t GROUP BY t.GoodsId";

    return Get<RB_Goods_Order_Extend>(sql).ToList();
}
#endregion

#region

/// <summary>
/// 获取超时取消的订单列表
/// </summary>
/// <param name="cancelTime"></param>
/// <param name="t"></param>
/// <param name="m"></param>
/// <returns></returns>
public List<RB_Goods_Order_Extend> GetCanCancelOrderList(int cancelTime, int t, int m)
{
    string sql = $@"SELECT OrderId,UserId,OrderNo,OrderSource,CouponsIds FROM rb_goods_order where TenantId ={t} and MallBaseId ={m} and `Status`=0 and OrderStatus =1 and DATE_ADD(CreateDate,INTERVAL {cancelTime} MINUTE) < '{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}'";
    return Get<RB_Goods_Order_Extend>(sql).ToList();
}

/// <summary>
/// 获取可自动收货的订单列表
/// </summary>
/// <param name="day"></param>
/// <param name="t"></param>
/// <param name="m"></param>
/// <returns></returns>
public List<RB_Goods_Order_Extend> GetCanReceivingOrderList(int day, int t, int m)
{
    string sql = $@"SELECT OrderId,UserId,OrderNo,OrderSource,CouponsIds FROM rb_goods_order where TenantId ={t} and MallBaseId ={m} and `Status`=0 and OrderStatus =3 and DATE_ADD(DeliveryTime,INTERVAL {day} DAY) < '{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}'";
    return Get<RB_Goods_Order_Extend>(sql).ToList();
}
        #endregion
    }
}
