using Mall.Model.Entity.MarketingCenter;
using Mall.Model.Entity.Product;
using Mall.Model.Entity.User;
using Mall.Model.Extend.User;
using Mall.Model.Query;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Mall.Repository.User
{
    /// <summary>
    /// 会员优惠券仓储层
    /// </summary>
    public class RB_Member_CouponRepository : RepositoryBase<RB_Member_DiscountCoupon>
    {
        public string MemberTableName { get { return nameof(RB_Member_User); } }
        /// <summary>
        /// 表名称
        /// </summary>
        public string TableName { get { return nameof(RB_Member_DiscountCoupon); } }
        /// <summary>
        /// 表名称
        /// </summary>
        public string DiscountCouponTableName { get { return nameof(RB_DiscountCoupon); } }

        /// <summary>
        /// 表名称
        /// </summary>
        public string OrderTableName { get { return nameof(RB_Goods_Order); } }

        /// <summary>
        /// 会员优惠券
        /// </summary>
        /// <returns></returns>
        public List<RB_Member_DiscountCoupon_Extend> GetList(RB_Member_DiscountCoupon_Extend query)
        {
            StringBuilder builder = new StringBuilder();
            if (query != null)
            {
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Member_DiscountCoupon_Extend.TenantId)}={query.TenantId}");
                }
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Member_DiscountCoupon_Extend.MallBaseId)}={query.MallBaseId}");
                }
                if (query.PlatformType > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Member_DiscountCoupon_Extend.PlatformType)}={query.PlatformType}");
                }
                if (query.UserId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Member_DiscountCoupon_Extend.UserId)}={query.UserId}");
                }
                if (query.CouponId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Member_DiscountCoupon_Extend.CouponId)}={query.CouponId}");
                }
                if (!string.IsNullOrEmpty(query.CouponIds))
                {
                    builder.Append($" AND a.{nameof(RB_Member_DiscountCoupon_Extend.CouponId)} in ({query.CouponIds})");
                }
                if (!string.IsNullOrEmpty(query.Ids)) {
                    builder.Append($" AND a.{nameof(RB_Member_DiscountCoupon_Extend.Id)} in ({query.Ids})");
                }
                if (query.UseState >= 0)
                {
                    builder.Append($" AND a.{nameof(RB_Member_DiscountCoupon_Extend.UseState)}={query.UseState}");
                }
            }
            string sql = @$" select * FROM {TableName} AS a  where 1=1 {builder.ToString()} ";
            return Get<RB_Member_DiscountCoupon_Extend>(sql).ToList();
        }



        /// <summary>
        /// 会员优惠券
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <param name="rowCount">总条数</param>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public List<RB_Member_DiscountCoupon_Extend> GetIntegraStatisticslList(int pageIndex, int pageSize, out long rowCount, RB_Member_DiscountCoupon_Extend query)
        {
            StringBuilder builder = new StringBuilder();
            if (query != null)
            {
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Member_DiscountCoupon_Extend.TenantId)}={query.TenantId}");
                }
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Member_DiscountCoupon_Extend.MallBaseId)}={query.MallBaseId}");
                }
                if (query.PlatformType > 0)
                {
                    builder.Append($" AND b.{nameof(RB_Member_User.Source)}={query.PlatformType}");
                }
                if (query.UserId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Member_DiscountCoupon_Extend.UserId)}={query.UserId}");
                }
                //if (!string.IsNullOrWhiteSpace(query.StartDate))
                //{
                //    builder.Append($" AND DATE_FORMAT({nameof(RB_Member_Coupon_Extend.CreateDate)},'%Y-%m-%d' )>= DATE_FORMAT('{nameof(RB_Member_Coupon_Extend.StartDate)}','%Y-%m-%d' )");
                //}
                //if (!string.IsNullOrWhiteSpace(query.EndDate))
                //{
                //    builder.Append($" AND DATE_FORMAT({nameof(RB_Member_Coupon_Extend.CreateDate)},'%Y-%m-%d' )<= DATE_FORMAT('{nameof(RB_Member_Coupon_Extend.EndDate)}','%Y-%m-%d' )");
                //}
            }
            string sql = @$" select * from {TableName} as A  LEFT JOIN {MemberTableName} as b on a.UserId=b.Id  where 1=1 {builder.ToString()} ";
            return GetPage<RB_Member_DiscountCoupon_Extend>(pageIndex, pageSize, out rowCount, sql).ToList();
        }


        /// <summary>
        /// 会员优惠券
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <param name="rowCount">总条数</param>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public List<RB_Member_DiscountCoupon_Extend> GetExportIntegraStatisticslList(RB_Member_DiscountCoupon_Extend query)
        {
            StringBuilder builder = new StringBuilder();
            if (query != null)
            {
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Member_DiscountCoupon_Extend.TenantId)}={query.TenantId}");
                }
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Member_DiscountCoupon_Extend.MallBaseId)}={query.MallBaseId}");
                }
                if (query.PlatformType > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Member_DiscountCoupon_Extend.PlatformType)}={query.PlatformType}");
                }
                if (query.UserId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Member_DiscountCoupon_Extend.UserId)}={query.UserId}");
                }
                if (query.StartDate.HasValue)
                {
                    builder.Append($" AND DATE_FORMAT(c.{nameof(RB_DiscountCoupon.CreateDate)},'%Y-%m-%d' )>= DATE_FORMAT('{query.StartDate}','%Y-%m-%d' )");
                }
                if (query.EndDate.HasValue)
                {
                    builder.Append($" AND DATE_FORMAT(c.{nameof(RB_DiscountCoupon.CreateDate)},'%Y-%m-%d' )<= DATE_FORMAT('{query.EndDate}','%Y-%m-%d' )");
                }
            }
            string sql = @$" select * FROM
	{TableName} AS a
LEFT JOIN {MemberTableName} AS b ON a.UserId = b.Id
LEFT JOIN {DiscountCouponTableName} as c on c.ID=a.CouponId   where 1=1 {builder.ToString()} ";
            return Get<RB_Member_DiscountCoupon_Extend>(sql).ToList();
        }


        /// <summary>
        /// 优惠券发放统计
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <param name="rowCount">总条数</param>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public List<MemberCoupon_Query> GetCouponStatisticslList(int pageIndex, int pageSize, out long rowCount, MemberCoupon_Query query)
        {
            StringBuilder builder = new StringBuilder();
            if (query != null)
            {
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Member_DiscountCoupon_Extend.TenantId)}={query.TenantId}");
                }
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Member_DiscountCoupon_Extend.MallBaseId)}={query.MallBaseId}");
                }
                if (query.PlatformType > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Member_DiscountCoupon_Extend.PlatformType)}={query.PlatformType}");
                }
                if (!string.IsNullOrWhiteSpace(query.StartDate))
                {
                    builder.Append($" AND DATE_FORMAT(c.{nameof(RB_DiscountCoupon.CreateDate)},'%Y-%m-%d' )>= DATE_FORMAT('{query.StartDate}','%Y-%m-%d' )");
                }
                if (!string.IsNullOrWhiteSpace(query.EndDate))
                {
                    builder.Append($" AND DATE_FORMAT(c.{nameof(RB_DiscountCoupon.CreateDate)},'%Y-%m-%d' )<= DATE_FORMAT('{query.EndDate}','%Y-%m-%d' )");
                }

            }
            string sql = @$" SELECT t.CouponId,MAX(t.`Name`) as Name,SUM(t.IsSued) as IsSued,SUM(t.Used) as Used,SUM(t.UnUsed) as UnUsed,SUM(t.Voksi) as Voksi,MAX(t.CreateDate)as CreateDate from (SELECT
	DATE_FORMAT(c.CreateDate, '%Y-%m-%d') AS CreateDate,a.CouponId,c.`Name`,count(*) as IsSued,0 as Used,0 as UnUsed,0 as Voksi
FROM
	{TableName} AS a
LEFT JOIN {MemberTableName} AS b ON a.UserId = b.Id
LEFT JOIN {DiscountCouponTableName} as c on c.ID=a.CouponId 
union all 
SELECT
	DATE_FORMAT(c.CreateDate, '%Y-%m-%d') AS CreateDate,a.CouponId,c.`Name`,0 as IsSued,count(UseState) as Used,0 as UnUsed,0 as Voksi
FROM
	{TableName} AS a
LEFT JOIN {MemberTableName} AS b ON a.UserId = b.Id
LEFT JOIN {DiscountCouponTableName} as c on c.ID=a.CouponId   where a.UseState=1
union all 
SELECT
	DATE_FORMAT(c.CreateDate, '%Y-%m-%d') AS CreateDate,a.CouponId,c.`Name`,0 as IsSued,0 as Used,count(*) as UnUsed,0 as Voksi
FROM
	{TableName} AS a
LEFT JOIN {MemberTableName} AS b ON a.UserId = b.Id
LEFT JOIN {DiscountCouponTableName} as c on c.ID=a.CouponId   where a.UseState=0
union all 
SELECT
	DATE_FORMAT(c.CreateDate, '%Y-%m-%d') AS CreateDate,a.CouponId,c.`Name`,0 as IsSued,0 as Used,0 as UnUsed,count(*) as Voksi
FROM
	{TableName} AS a
LEFT JOIN {MemberTableName} AS b ON a.UserId = b.Id
LEFT JOIN {DiscountCouponTableName} as c on c.ID=a.CouponId   where a.EndDate<NOW() and a.UseState=0) as t WHERE t.CouponId>0  GROUP BY t.CouponId ";
            return GetPage<MemberCoupon_Query>(pageIndex, pageSize, out rowCount, sql).ToList();
        }


        /// <summary>
        /// 优惠券发放统计
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <param name="rowCount">总条数</param>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public List<MemberCoupon_Query> GetExportCouponStatisticslList(MemberCoupon_Query query)
        {
            StringBuilder builder = new StringBuilder();
            if (query != null)
            {
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Member_DiscountCoupon_Extend.TenantId)}={query.TenantId}");
                }
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Member_DiscountCoupon_Extend.MallBaseId)}={query.MallBaseId}");
                }
                if (query.PlatformType > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Member_DiscountCoupon_Extend.PlatformType)}={query.PlatformType}");
                }
                if (!string.IsNullOrWhiteSpace(query.StartDate))
                {
                    builder.Append($" AND DATE_FORMAT(c.{nameof(RB_DiscountCoupon.CreateDate)},'%Y-%m-%d' )>= DATE_FORMAT('{query.StartDate}','%Y-%m-%d' )");
                }
                if (!string.IsNullOrWhiteSpace(query.EndDate))
                {
                    builder.Append($" AND DATE_FORMAT(c.{nameof(RB_DiscountCoupon.CreateDate)},'%Y-%m-%d' )<= DATE_FORMAT('{query.EndDate}','%Y-%m-%d' )");
                }

            }
            string sql = @$" SELECT t.CouponId,MAX(t.`Name`) as Name,SUM(t.IsSued) as IsSued,SUM(t.Used) as Used,SUM(t.UnUsed) as UnUsed,SUM(t.Voksi) as Voksi,MAX(t.CreateDate)as CreateDate from (SELECT
	DATE_FORMAT(c.CreateDate, '%Y-%m-%d') AS CreateDate,a.CouponId,c.`Name`,count(*) as IsSued,0 as Used,0 as UnUsed,0 as Voksi
FROM
	{TableName} AS a
LEFT JOIN {MemberTableName} AS b ON a.UserId = b.Id
LEFT JOIN {DiscountCouponTableName} as c on c.ID=a.CouponId 
union all 
SELECT
	DATE_FORMAT(c.CreateDate, '%Y-%m-%d') AS CreateDate,a.CouponId,c.`Name`,0 as IsSued,count(UseState) as Used,0 as UnUsed,0 as Voksi
FROM
	{TableName} AS a
LEFT JOIN {MemberTableName} AS b ON a.UserId = b.Id
LEFT JOIN {DiscountCouponTableName} as c on c.ID=a.CouponId   where a.UseState=1
union all 
SELECT
	DATE_FORMAT(c.CreateDate, '%Y-%m-%d') AS CreateDate,a.CouponId,c.`Name`,0 as IsSued,0 as Used,count(*) as UnUsed,0 as Voksi
FROM
	{TableName} AS a
LEFT JOIN {MemberTableName} AS b ON a.UserId = b.Id
LEFT JOIN {DiscountCouponTableName} as c on c.ID=a.CouponId   where a.UseState=0
union all 
SELECT
	DATE_FORMAT(c.CreateDate, '%Y-%m-%d') AS CreateDate,a.CouponId,c.`Name`,0 as IsSued,0 as Used,0 as UnUsed,count(*) as Voksi
FROM
	{TableName} AS a
LEFT JOIN {MemberTableName} AS b ON a.UserId = b.Id
LEFT JOIN {DiscountCouponTableName} as c on c.ID=a.CouponId   where a.EndDate<NOW() and a.UseState=0) as t WHERE t.CouponId>0  GROUP BY t.CouponId ";
            return Get<MemberCoupon_Query>(sql).ToList();
        }

        /// <summary>
        /// 使用记录
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <param name="rowCount">总条数</param>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public List<RB_Member_DiscountCoupon_Extend> GetUseCouponList(int pageIndex, int pageSize, out long rowCount, RB_Member_DiscountCoupon_Extend query)
        {
            StringBuilder builder = new StringBuilder();
            if (query != null)
            {
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Member_DiscountCoupon_Extend.TenantId)}={query.TenantId}");
                }
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Member_DiscountCoupon_Extend.MallBaseId)}={query.MallBaseId}");
                }

                if (query.StartDate.HasValue)
                {
                    builder.Append($" AND DATE_FORMAT(b.{nameof(RB_Goods_Order.CreateDate)},'%Y-%m-%d' )>= DATE_FORMAT('{query.StartDate}','%Y-%m-%d' )");
                }
                if (query.EndDate.HasValue)
                {
                    builder.Append($" AND DATE_FORMAT(b.{nameof(RB_Goods_Order.CreateDate)},'%Y-%m-%d' )<= DATE_FORMAT('{query.EndDate}','%Y-%m-%d' )");
                }
                if (!string.IsNullOrWhiteSpace(query.AliasName))
                {
                    builder.Append($" AND  (d.{nameof(RB_Member_User.AliasName)} like '%{query.AliasName}%' or b.{nameof(RB_Goods_Order.OrderId)} like '%{query.AliasName}%')");
                }

            }
            string sql = @$" SELECT a.*,b.CreateDate as OrderCreateDate,c.`Name`,c.MinConsumePrice,c.CouponType,c.DiscountsPrice,d.`Name` as AliasName,d.Photo from rb_member_discountcoupon as a 
LEFT JOIN rb_goods_order as b on a.OrderId=b.OrderId
LEFT JOIN rb_discountcoupon as c on c.ID=a.CouponId
LEFT JOIN rb_member_user as d on d.AliasName=a.UserId   where a.UseState=1 {builder.ToString()}";
            return GetPage<RB_Member_DiscountCoupon_Extend>(pageIndex, pageSize, out rowCount, sql).ToList();
        }



        /// <summary>
        /// 我的优惠券
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public List<RB_Member_DiscountCoupon_Extend> GetMemberCouponPageList(int pageIndex, int pageSize, out long rowCount, RB_Member_DiscountCoupon_Extend query)
        {
            string where = "   where a.`Status`=0 ";
            if (query != null)
            {

                if (query.TenantId > 0)
                {
                    where += $" AND a.{nameof(RB_Member_DiscountCoupon_Extend.TenantId)}={query.TenantId}";

                }
                if (query.MallBaseId > 0)
                {
                    where += $" AND a.{nameof(RB_Member_DiscountCoupon_Extend.MallBaseId)}={query.MallBaseId}";
                }
                if (query.UserId > 0)
                {
                    where += $" AND a.{nameof(RB_Member_DiscountCoupon_Extend.UserId)}={query.UserId}";
                }
                if (query.UseState == 0)
                {
                    where += $" AND a.{nameof(RB_Member_DiscountCoupon_Extend.UseState)}={query.UseState} AND a.{nameof(RB_Member_DiscountCoupon_Extend.EndDate)}> NOW()";
                }
                else if (query.UseState == 1)
                {
                    where += $" AND a.{nameof(RB_Member_DiscountCoupon_Extend.UseState)}={query.UseState}";
                }
                else if (query.UseState == 2)
                {
                    where += $" AND a.{nameof(RB_Member_DiscountCoupon_Extend.EndDate)}<NOW()   AND a.{nameof(RB_Member_DiscountCoupon_Extend.UseState)}=0";
                }
            }
            string sql = @$" SELECT a.* from rb_member_discountcoupon as a  {where} ";
            return GetPage<RB_Member_DiscountCoupon_Extend>(pageIndex, pageSize, out rowCount, sql).ToList();
        }




        /// <summary>
        /// 我的优惠券
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public List<RB_Member_DiscountCoupon_Extend> GetAllMemberCouponPageList( RB_Member_DiscountCoupon_Extend query)
        {
            string where = "   where a.`Status`=0 ";
            if (query != null)
            {

                if (query.TenantId > 0)
                {
                    where += $" AND a.{nameof(RB_Member_DiscountCoupon_Extend.TenantId)}={query.TenantId}";

                }
                if (query.MallBaseId > 0)
                {
                    where += $" AND a.{nameof(RB_Member_DiscountCoupon_Extend.MallBaseId)}={query.MallBaseId}";
                }
                if (query.UserId > 0)
                {
                    where += $" AND a.{nameof(RB_Member_DiscountCoupon_Extend.UserId)}={query.UserId}";
                }
                if (query.UseState == 0)
                {
                    where += $" AND a.{nameof(RB_Member_DiscountCoupon_Extend.UseState)}={query.UseState} AND a.{nameof(RB_Member_DiscountCoupon_Extend.EndDate)}> NOW()";
                }
                else if (query.UseState == 1)
                {
                    where += $" AND a.{nameof(RB_Member_DiscountCoupon_Extend.UseState)}={query.UseState}";
                }
                else if (query.UseState == 2)
                {
                    where += $" AND a.{nameof(RB_Member_DiscountCoupon_Extend.EndDate)}<NOW()   AND a.{nameof(RB_Member_DiscountCoupon_Extend.UseState)}=0";
                }
            }
            string sql = @$" SELECT a.* from rb_member_discountcoupon as a  {where} ";
            return Get<RB_Member_DiscountCoupon_Extend>( sql).ToList();
        }
    }
}
