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;
using VT.FW.DB.Dapper;

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)
        {
            var parameters = new DynamicParameters();
            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 @OrderNo ";
                parameters.Add("OrderNo", "%" + dmodel.OrderNo.Trim() + "%");
            }
            if (!string.IsNullOrEmpty(dmodel.MerchantsNo))
            {
                where += $@" and o.{nameof(RB_Goods_Order.MerchantsNo)} like @MerchantsNo ";
                parameters.Add("MerchantsNo", "%" + dmodel.MerchantsNo.Trim() + "%");
            }
            if (!string.IsNullOrEmpty(dmodel.UserName))
            {
                where += $@" and u.{nameof(RB_Member_User.Name)} like @UserName ";
                parameters.Add("UserName", "%" + dmodel.UserName.Trim() + "%");
            }
            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 @GoodsName ";
                parameters.Add("GoodsName", "%" + dmodel.GoodsName.Trim() + "%");
            }
            if (!string.IsNullOrEmpty(dmodel.Consignee))
            {
                where += $@" and o.{nameof(RB_Goods_Order.Consignee)} like @Consignee ";
                parameters.Add("Consignee", "%" + dmodel.Consignee.Trim() + "%");
            }
            if (!string.IsNullOrEmpty(dmodel.Mobile))
            {
                where += $@" and o.{nameof(RB_Goods_Order.Mobile)} like @Mobile ";
                parameters.Add("Mobile", "%" + dmodel.Mobile.Trim() + "%");
            }
            if (!string.IsNullOrEmpty(dmodel.ProductCode))
            {
                where += $@" and od.{nameof(RB_Goods_OrderDetail.ProductCode)} like @ProductCode ";
                parameters.Add("ProductCode", "%" + dmodel.ProductCode.Trim() + "%");
            }
            if (!string.IsNullOrEmpty(dmodel.AnchorName))
            {
                where += $@" and o.{nameof(RB_Goods_Order.AnchorName)} like @AnchorName ";
                parameters.Add("AnchorName", "%" + dmodel.AnchorName.Trim() + "%");
            }
            if (dmodel.Recycled > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.Recycled)}={(int)dmodel.Recycled}";
            }
            if (dmodel.SmallShopsId.HasValue && dmodel.SmallShopsId > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.SmallShopsId)}={dmodel.SmallShopsId}";
            }
            if (dmodel.IsSelectPayMoney == 1)
            {
                where += $@" and o.{nameof(RB_Goods_Order.PaymentTime)} IS NOT NULL";
            }

            string sql = $@"SELECT o.*,u.Name as UserName,sshop.`Name` as SmallShopsName 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_smallshops_info as sshop on o.SmallShopsId=sshop.Id
where {where} group by o.OrderId order by o.CreateDate desc";
            return GetPage<RB_Goods_Order_Extend>(pageIndex, pageSize, out count, sql, parameters).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="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<RB_Goods_Order_Extend> GetSmallShopsOrdersPageList(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_SmallShops_Commission.UserId)}={dmodel.DistributionUserId}";
            }
            if (dmodel.SmallShopsId > 0)
            {
                where += $@" and o.{nameof(RB_Goods_Order.SmallShopsId)}={(int)dmodel.SmallShopsId}";
            }

            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_smallshops_commission 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.OrderSource <> 6 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.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_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({nameof(RB_Goods_OrderAfterSale.CreateDate)},'%Y-%m-%d' )<= DATE_FORMAT('{dmodel.EndDate}','%Y-%m-%d' )";
            }
            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,MAX(t.RepurchaseNum) as RepurchaseNum
                //from (SELECT DATE_FORMAT(a.CreateDate,'%Y-%m-%d' )as CreateDate,b.GoodsNum,a.UserId,a.Income,a.OrderId,c.PayMemerNum,d.RepurchaseNum 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' ) 
                //LEFT JOIN (SELECT temp.CreateDate,COUNT(UserId) as RepurchaseNum from (SELECT UserId,COUNT(DISTINCT UserId) as RepurchaseNum, DATE_FORMAT(CreateDate,'%Y-%m-%d' ) as CreateDate from rb_goods_order where 1=1 
                //{payMemerNumWhere}  GROUP BY UserId,DATE_FORMAT(CreateDate,'%Y-%m-%d' ) having count(UserId) > 1) as temp GROUP BY temp.CreateDate
                //)
                //as d on d.CreateDate=DATE_FORMAT(a.CreateDate,'%Y-%m-%d' ) 
                //{where} )
                //as t GROUP BY t.CreateDate desc";
                sql = $@"SELECT t.CreateDate,SUM(t.GoodsNum)as GoodsNum ,COUNT(t.OrderId)as OrderNum ,SUM(t.Income)as PayMoney,MAX(t.PayMemerNum) as PayMemerNum,GROUP_CONCAT(DISTINCT t.UserId) as UserIds
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 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";
            }

            return GetPage<DataStatistics_Query>(pageIndex, pageSize, out count, 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> GetOrderByUserIds(DataStatistics_Query dmodel)
        {
           
            string payMemerNumWhere = "  where 1=1 ";
            if (dmodel.TenantId > 0)
            {
                payMemerNumWhere += $@" and {nameof(RB_Goods_Order.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                payMemerNumWhere += $@" and {nameof(RB_Goods_Order.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.PlatformType > 0)
            {
                payMemerNumWhere += $@" and {nameof(RB_Goods_Order.OrderSource)}={(int)dmodel.PlatformType}";
            }
            if (!string.IsNullOrWhiteSpace(dmodel.UserIds))
            {
                payMemerNumWhere += $@" and {nameof(RB_Goods_Order.UserId)} in({dmodel.UserIds})";
            }
            if (dmodel.OrderStatus == 1 || dmodel.OrderStatus == 0)
            {
                payMemerNumWhere += $@" and {nameof(RB_Goods_Order.OrderStatus)} in (2,4,5,3,6)";
            }
            else if (dmodel.OrderStatus == 2)
            {
                payMemerNumWhere += $@" and {nameof(RB_Goods_Order.OrderStatus)}=5";
            }
            else if (dmodel.OrderStatus == 3)
            {
                payMemerNumWhere += $@" and {nameof(RB_Goods_Order.OrderStatus)}=7";
            }
            string sql = $@"SELECT  *  from rb_goods_order {payMemerNumWhere}";
            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<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.TenantId > 0)
                {
                    where += $" and  a.TenantId={dmodel.TenantId} ";
                }

                if (dmodel.MallBaseId > 0)
                {
                    where += $" and a.MallBaseId={dmodel.MallBaseId} ";
                }
                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.FXCommission,b.LiveCommission,b.LiveFinanceIds,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 FXCommission,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.TenantId > 0)
                {
                    where += $" and  a.TenantId={dmodel.TenantId} ";
                }

                if (dmodel.MallBaseId > 0)
                {
                    where += $" and a.MallBaseId={dmodel.MallBaseId} ";
                }
                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.FXCommission,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 FXCommission,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 粉象收益核算表
        public List<OrderStatistics_Query> GetFXOrderProfitLossList(int pageIndex, int pageSize, out long count, OrderStatistics_Query dmodel)
        {
            // string where = "where 1=1 and a.OrderStatus in(2,3,4,5,6) ";
            string where = "where 1=1 and (a.OrderStatus in(2,3,4,5,6) or (a.OrderStatus=7 and a.PaymentTime is not NULL and a.CancelTime is not NULL and DATE_FORMAT(a.PaymentTime,'%Y-%m-%d' )<DATE_FORMAT(a.CancelTime,'%Y-%m-%d' ) ) ) ";
            if (dmodel != null)
            {
                if (!string.IsNullOrWhiteSpace(dmodel.OrderNo))
                {
                    where += $" and  a.OrderNo like'%{dmodel.OrderNo}%'";
                }
                if (dmodel.TenantId > 0)
                {
                    where += $" and  a.TenantId={dmodel.TenantId} ";
                }

                if (dmodel.MallBaseId > 0)
                {
                    where += $" and a.MallBaseId={dmodel.MallBaseId} ";
                }
                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 (dmodel.CompanyId > 0)
                {
                    where += $" and  b.CompanyId={dmodel.CompanyId} ";
                }

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

            }

            //            string sql = $@"    SELECT B.Id,SUM(b.PaidCostMoney) as PaidCostMoney,MAX(b.OrderId) as OrderId,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,
            //0 as OtherPrice,e.FXCommission,f.RemitFXCommission,b.LiveCommission,b.LiveFinanceIds,b.LivePeopleNum,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 FXCommission,OrderDetailId from All_Commission  GROUP BY OrderDetailId ) as e on b.Id=e.OrderDetailId 
            //LEFT JOIN (SELECT SUM(Commission) as RemitFXCommission,OrderDetailId from All_Commission where IsRemit=1  GROUP BY OrderDetailId ) as f on b.Id=f.OrderDetailId 
            //left join rb_goods_orderaftersale oa on b.ID= oa.OrderDetialId and oa.Type=1 and oa.Status=0 and oa.ReOrderStatus in (2,3,4,5) {where}
            //GROUP BY b.Id order by b.Id desc";


            string sql = $@"    SELECT a.OrderStatus,B.Id,b.ServiceCharge,SUM(b.PaidCostMoney) as PaidCostMoney,MAX(b.OrderId) as OrderId,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,sum(b.FreightCostMoney) as GoodsFreight,
0 as OtherPrice,b.LiveCommission,b.LiveFinanceIds,b.LivePeopleNum,0 as Paid,oa.RefundActual,b.CouponMoney,b.CompanyProfitRate,b.CompanyId,muser.`Name` as CompanyName,b.YSMoney,b.RealMoney,b.YFMoney,b.PayMoney
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 rb_member_user as muser on muser.id=b.CompanyId 
left join rb_goods_orderaftersale oa on b.ID= oa.OrderDetialId and oa.Type=1 and oa.Status=0 and oa.ReOrderStatus in (2,3,4,5) {where}
GROUP BY b.Id order by b.Id asc";

            return GetPage<OrderStatistics_Query>(pageIndex, pageSize, out count, sql).ToList();
        }



        public List<OrderStatistics_Query> GetFXAllOrderProfitLossList(OrderStatistics_Query dmodel)
        {
            string where = "where 1=1 and (a.OrderStatus in(2,3,4,5,6) or (a.OrderStatus=7 and a.PaymentTime is not NULL and a.CancelTime is not NULL and DATE_FORMAT(a.PaymentTime,'%Y-%m-%d' )<DATE_FORMAT(a.CancelTime,'%Y-%m-%d' ) ) )  ";
            DynamicParameters parameters = new DynamicParameters();
            if (dmodel != null)
            {
                if (!string.IsNullOrWhiteSpace(dmodel.OrderNo))
                {
                    where += $" and  a.OrderNo like  @OrderNo ";
                    parameters.Add("OrderNo", "%" + dmodel.OrderNo.Trim() + "%");
                }
                if (dmodel.TenantId > 0)
                {
                    where += $" and  a.TenantId={dmodel.TenantId} ";
                }

                if (dmodel.MallBaseId > 0)
                {
                    where += $" and a.MallBaseId={dmodel.MallBaseId} ";
                }
                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 (dmodel.CompanyId > 0)
                {
                    where += $" and  b.CompanyId={dmodel.CompanyId} ";
                }

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

            }

            //            string sql = $@"      SELECT B.Id,SUM(b.PaidCostMoney) as PaidCostMoney,MAX(b.OrderId) as OrderId,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,
            //0 as OtherPrice,e.FXCommission,f.RemitFXCommission,b.LiveCommission,b.LiveFinanceIds,b.LivePeopleNum,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 FXCommission,OrderDetailId from All_Commission  GROUP BY OrderDetailId ) as e on b.Id=e.OrderDetailId 
            //LEFT JOIN (SELECT SUM(Commission) as RemitFXCommission,OrderDetailId from All_Commission where IsRemit=1  GROUP BY OrderDetailId ) as f on b.Id=f.OrderDetailId 
            //left join rb_goods_orderaftersale oa on b.ID= oa.OrderDetialId and oa.Type=1 and oa.Status=0 and oa.ReOrderStatus in (2,3,4,5) {where}
            //GROUP BY b.Id ";


            string sql = $@"   SELECT a.OrderStatus,B.Id,b.ServiceCharge,SUM(b.PaidCostMoney) as PaidCostMoney,MAX(b.OrderId) as OrderId,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,sum(b.FreightCostMoney) as GoodsFreight,
0 as OtherPrice,b.LiveCommission,b.LiveFinanceIds,b.LivePeopleNum,0 as Paid,oa.RefundActual,b.CouponMoney,b.CompanyProfitRate,b.CompanyId,muser.`Name` as CompanyName,b.YSMoney,b.RealMoney,b.YFMoney,b.PayMoney
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 rb_member_user as muser on muser.id=b.CompanyId 
left join rb_goods_orderaftersale oa on b.ID= oa.OrderDetialId and oa.Type=1 and oa.Status=0 and oa.ReOrderStatus in (2,3,4,5) {where}
GROUP BY b.Id order  by b.Id asc ";
            return Get<OrderStatistics_Query>(sql, parameters).ToList();
        }



        /// <summary>
        /// 获取用户  订单下的商品数量
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="limitGoodsIds"></param>
        /// <returns></returns>
        public List<RB_Goods_Order_Extend> GetFXUserOrderGoodsNum(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> GetFXUserOrderNum(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


        #region 店铺订单
        /// <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> GetSmallShopOrdersPageList(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_smallshops_commission 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();
        }
        #endregion
    }
}
