using System;
using System.Collections.Generic;
using System.Text;
using Mall.Model.Entity.Product;
using Mall.Model.Extend.Product;
using System.Linq;
using VT.FW.DB.Dapper;

namespace Mall.Repository.Product
{
    /// <summary>
    /// 商品仓储层
    /// </summary>
    public class RB_GoodsRepository : BaseRepository<RB_Goods>
    {

        /// <summary>
        /// 分页列表
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <param name="rowCount">总条数</param>
        /// <param name="dmodel">查询条件</param>
        /// <returns></returns>
        public List<RB_Goods_Extend> GetPageList(int pageIndex, int pageSize, out long rowCount, RB_Goods_Extend dmodel)
        {
            string where = $" 1=1 and g.{nameof(RB_Goods_Extend.Status)}=0";
            if (dmodel.TenantId > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.NotTenantId > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.TenantId)}<>{dmodel.NotTenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.Id > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.Id)}={dmodel.Id}";
            }
            if (!string.IsNullOrEmpty(dmodel.GoodsIds))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.Id)} in({dmodel.GoodsIds})";
            }
            if (!string.IsNullOrEmpty(dmodel.Name))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.Name)} like '%{dmodel.Name}%'";
            }
            if (dmodel.GoodsStatus > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.GoodsStatus)}={dmodel.GoodsStatus}";
            }
            if (dmodel.IsSelectSellOut == 1)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.InventoryNum)}<=0";
            }
            if (!string.IsNullOrEmpty(dmodel.CategoryIds))
            {
                where += $@" and c.{nameof(RB_Goods_Category.CategoryId)} in({dmodel.CategoryIds})";
            }
            if (!string.IsNullOrEmpty(dmodel.StartTime))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.CreateDate)} >='{dmodel.StartTime}'";
            }
            if (!string.IsNullOrEmpty(dmodel.EndTime))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.CreateDate)} <='{dmodel.EndTime + " 23:59:59"}'";
            }
            if (dmodel.IsProcurement > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.IsProcurement)}={dmodel.IsProcurement}";
            }
            if (dmodel.IsProxy > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.IsProxy)}={dmodel.IsProxy}";
            }
            if (dmodel.SupplierId > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.SupplierId)}={dmodel.SupplierId}";
            }

            string orderBy = " g.CreateDate desc";
            if (dmodel.OrderBy == 1)
            {
                //id升序
                orderBy = " g.Id asc";
            }
            else if (dmodel.OrderBy == 2)
            {
                //id降序
                orderBy = " g.Id desc";
            }
            else if (dmodel.OrderBy == 3)
            {
                //排序升序
                orderBy = " g.Sort asc";
            }
            else if (dmodel.OrderBy == 4)
            {
                //排序降序
                orderBy = " g.Sort desc";
            }
            else if (dmodel.OrderBy == 5)
            {
                //售价升序
                orderBy = " g.SellingPrice asc";
            }
            else if (dmodel.OrderBy == 6)
            {
                //售价降序
                orderBy = " g.SellingPrice desc";
            }
            else if (dmodel.OrderBy == 7)
            {
                //库存升序
                orderBy = " g.InventoryNum asc";
            }
            else if (dmodel.OrderBy == 8)
            {
                //库存降序
                orderBy = " g.InventoryNum desc";
            }

            string sql = $@"select g.* from RB_Goods g
inner join rb_goods_category c on g.Id=c.GoodsId 
where {where} group by g.Id order by {orderBy}";
            return GetPage<RB_Goods_Extend>(pageIndex, pageSize, out rowCount, sql).ToList();
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<RB_Goods_Extend> GetList(RB_Goods_Extend dmodel)
        {
            string where = $" 1=1 and g.{nameof(RB_Goods_Extend.Status)}=0";
            if (dmodel.TenantId > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.Id > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.Id)}={dmodel.Id}";
            }
            if (!string.IsNullOrEmpty(dmodel.GoodsIds))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.Id)} in({dmodel.GoodsIds})";
            }
            if (!string.IsNullOrEmpty(dmodel.Name))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.Name)} like '%{dmodel.Name}%'";
            }
            if (dmodel.GoodsStatus > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.GoodsStatus)}={dmodel.GoodsStatus}";
            }
            if (dmodel.IsSelectSellOut == 1)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.InventoryNum)}<=0";
            }
            if (!string.IsNullOrEmpty(dmodel.CategoryIds))
            {
                where += $@" and c.{nameof(RB_Goods_Category.CategoryId)} in({dmodel.CategoryIds})";
            }
            if (!string.IsNullOrEmpty(dmodel.SupplierIds))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.SupplierId)} in({dmodel.SupplierIds})";
            }

            if (!string.IsNullOrEmpty(dmodel.StartTime))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.CreateDate)} >='{dmodel.StartTime}'";
            }
            if (!string.IsNullOrEmpty(dmodel.EndTime))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.CreateDate)} <='{dmodel.EndTime + " 23:59:59"}'";
            }
            if (dmodel.SupplierId > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.SupplierId)}={dmodel.SupplierId}";
            }
            string sql = $@"
SELECT g.*,C.CategoryId
FROM RB_Goods g INNER JOIN rb_goods_category c on g.Id=c.GoodsId 
WHERE {where} group by g.Id order by g.CreateDate desc";
            return Get<RB_Goods_Extend>(sql).ToList();
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<RB_Goods_Extend> GetSingleList(RB_Goods_Extend dmodel)
        {
            string where = $" 1=1 and g.{nameof(RB_Goods_Extend.Status)}=0";
            if (dmodel.TenantId > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.Id > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.Id)}={dmodel.Id}";
            }
            if (!string.IsNullOrEmpty(dmodel.GoodsIds))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.Id)} in({dmodel.GoodsIds})";
            }
            if (!string.IsNullOrEmpty(dmodel.Name))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.Name)} like '%{dmodel.Name}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.GoodsNameStr))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.Name)} in ({dmodel.GoodsNameStr})";
            }
            if (dmodel.GoodsStatus > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.GoodsStatus)}={dmodel.GoodsStatus}";
            }
            if (dmodel.IsSelectSellOut == 1)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.InventoryNum)}<=0";
            }
            if (!string.IsNullOrEmpty(dmodel.StartTime))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.CreateDate)} >='{dmodel.StartTime}'";
            }
            if (!string.IsNullOrEmpty(dmodel.EndTime))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.CreateDate)} <='{dmodel.EndTime + " 23:59:59"}'";
            }

            string sql = $@"select g.* from RB_Goods g where {where}";
            return Get<RB_Goods_Extend>(sql).ToList();
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<RB_Goods_Extend> GetSingleListForGoodsId(RB_Goods_Extend dmodel)
        {
            string where = $" 1=1 and g.{nameof(RB_Goods_Extend.Status)}=0";
            if (dmodel.TenantId > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.Id > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.Id)}={dmodel.Id}";
            }
            if (!string.IsNullOrEmpty(dmodel.GoodsIds))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.Id)} in({dmodel.GoodsIds})";
            }
            if (!string.IsNullOrEmpty(dmodel.Name))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.Name)} like '%{dmodel.Name}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.GoodsNameStr))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.Name)} in ({dmodel.GoodsNameStr})";
            }
            if (dmodel.GoodsStatus > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.GoodsStatus)}={dmodel.GoodsStatus}";
            }
            if (dmodel.IsSelectSellOut == 1)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.InventoryNum)}<=0";
            }
            if (!string.IsNullOrEmpty(dmodel.StartTime))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.CreateDate)} >='{dmodel.StartTime}'";
            }
            if (!string.IsNullOrEmpty(dmodel.EndTime))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.CreateDate)} <='{dmodel.EndTime + " 23:59:59"}'";
            }

            string sql = $@"select g.Id from RB_Goods g where {where}";
            return Get<RB_Goods_Extend>(sql).ToList();
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<RB_Goods_Extend> GetSingleListForGoodsSubName(RB_Goods_Extend dmodel)
        {
            string where = $" 1=1 and g.{nameof(RB_Goods_Extend.Status)}=0";
            if (dmodel.TenantId > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.Id > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.Id)}={dmodel.Id}";
            }
            if (!string.IsNullOrEmpty(dmodel.GoodsIds))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.Id)} in({dmodel.GoodsIds})";
            }
            if (!string.IsNullOrEmpty(dmodel.Name))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.Name)} like '%{dmodel.Name}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.GoodsNameStr))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.Name)} in ({dmodel.GoodsNameStr})";
            }
            if (dmodel.GoodsStatus > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.GoodsStatus)}={dmodel.GoodsStatus}";
            }
            if (dmodel.IsSelectSellOut == 1)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.InventoryNum)}<=0";
            }
            if (!string.IsNullOrEmpty(dmodel.StartTime))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.CreateDate)} >='{dmodel.StartTime}'";
            }
            if (!string.IsNullOrEmpty(dmodel.EndTime))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.CreateDate)} <='{dmodel.EndTime + " 23:59:59"}'";
            }

            string sql = $@"select g.Id,g.Name,g.SubName from RB_Goods g where {where}";
            return Get<RB_Goods_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_Extend> GetAppletGoodsPageList(int pageIndex, int pageSize, out long count, RB_Goods_Extend dmodel)
        {
            var parameters = new DynamicParameters();
            string where = $" 1=1 and g.{nameof(RB_Goods_Extend.Status)}=0";
            if (dmodel.TenantId > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.Id > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.Id)}={dmodel.Id}";
            }
            if (!string.IsNullOrEmpty(dmodel.GoodsIds))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.Id)} in({dmodel.GoodsIds})";
            }
            if (!string.IsNullOrEmpty(dmodel.SupplierIds))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.SupplierId)} in({dmodel.SupplierIds})";
            }
            if (!string.IsNullOrEmpty(dmodel.Name))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.Name)} like @Name ";
                parameters.Add("Name", "%" + dmodel.Name.Trim() + "%");
            }
            if (dmodel.GoodsStatus > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.GoodsStatus)}={dmodel.GoodsStatus}";
            }
            if (dmodel.GoodsType > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.GoodsType)}={dmodel.GoodsType}";
            }
            if (dmodel.IsSelectSellOut == 1)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.InventoryNum)}<=0";
            }
            if (!string.IsNullOrEmpty(dmodel.CategoryIds))
            {
                where += $@" and c.{nameof(RB_Goods_Category.CategoryId)} in({dmodel.CategoryIds})";
            }
            if (!string.IsNullOrEmpty(dmodel.StartTime))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.CreateDate)} >='{dmodel.StartTime}'";
            }
            if (!string.IsNullOrEmpty(dmodel.EndTime))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.CreateDate)} <='{dmodel.EndTime + " 23:59:59"}'";
            }
            if (dmodel.IsVideo > 0)
            {
                where += $@" and IFNULL(g.{nameof(RB_Goods_Extend.VideoAddress)},'') <>'' ";
            }

            //默认综合【升序】
            string orderBy = $" order by g.{nameof(RB_Goods_Extend.Sort)} asc";
            //上架时间【降序】
            if (dmodel.OrderBy == 1)
            {
                orderBy = $" order by g.{nameof(RB_Goods_Extend.CreateDate)} DESC";
            }
            //上架时间【升序】
            else if (dmodel.OrderBy == 5)
            {
                orderBy = $" order by g.{nameof(RB_Goods_Extend.CreateDate)} ASC ";
            }
            //价格低=>高【升序】
            else if (dmodel.OrderBy == 2)
            {
                orderBy = $" order by g.{nameof(RB_Goods_Extend.SellingPrice)} ASC";
            }
            //价格高=>低【降序】
            else if (dmodel.OrderBy == 3)
            {
                orderBy = $" order by g.{nameof(RB_Goods_Extend.SellingPrice)} DESC";
            }
            //销量高=>低【降序】
            else if (dmodel.OrderBy == 4)
            {
                orderBy = $" order by (g.SalesNum+IFNULL(tempOrder.OrderNum,0)) DESC ";
            }
            //销量低=>高【升序】
            else if (dmodel.OrderBy == 6)
            {
                orderBy = $" order by (g.SalesNum+IFNULL(tempOrder.OrderNum,0)) ASC ";
            }
            //综合【降序】
            else if (dmodel.OrderBy == 7)
            {
                orderBy = $" order by g.{nameof(RB_Goods_Extend.Sort)} DESC ";
            }

            if (dmodel.IsSelectVideo == 1)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.VideoAddress)} !=''";
            }
            if (dmodel.GoodsId > 0)
            {
                if (dmodel.SelectVideoDirection == 1)
                {
                    where += $@" and g.{nameof(RB_Goods_Extend.Id)}>={dmodel.Id}";
                    orderBy = $" order by g.{nameof(RB_Goods_Extend.Id)} asc";
                }
                else
                {
                    where += $@" and g.{nameof(RB_Goods_Extend.Id)}<={dmodel.Id}";
                    orderBy = $" order by g.{nameof(RB_Goods_Extend.Id)} desc";
                }
            }

            string sql = $@"
SELECT g.* 
FROM RB_Goods g INNER JOIN rb_goods_category c on g.Id=c.GoodsId 
     LEFT JOIN (
                    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 1=1 and o.`Status`=0 and o.OrderStatus <> 7 group by od.GoodsId
               ) AS tempOrder ON g.Id=tempOrder.GoodsId
WHERE {where} group by g.Id {orderBy}";
            return GetPage<RB_Goods_Extend>(pageIndex, pageSize, out count, sql, parameters).ToList();
        }

        /// <summary>
        /// 获取会员收藏商品分页列表
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <param name="rowCount">总条数</param>
        /// <param name="dmodel">查询条件</param>
        /// <returns></returns>
        public List<RB_Goods_Extend> GetMemberCollectGoodsPageList(int pageIndex, int pageSize, out long rowCount, RB_Goods_Extend dmodel)
        {
            string where = $" 1=1 and col.{nameof(RB_Goods_Extend.Status)}=0";
            where += $" and col.Type=1 and col.UserId={dmodel.UserId} and g.{nameof(RB_Goods_Extend.Status)}=0 and g.{nameof(RB_Goods_Extend.GoodsStatus)}=1";
            if (dmodel.TenantId > 0)
            {
                where += $@" and col.{nameof(RB_Goods_Extend.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and col.{nameof(RB_Goods_Extend.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.Id > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.Id)}={dmodel.Id}";
            }
            if (!string.IsNullOrEmpty(dmodel.GoodsIds))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.Id)} in({dmodel.GoodsIds})";
            }
            if (!string.IsNullOrEmpty(dmodel.Name))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.Name)} like '%{dmodel.Name}%'";
            }
            if (dmodel.GoodsStatus > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.GoodsStatus)}={dmodel.GoodsStatus}";
            }
            if (dmodel.IsSelectSellOut == 1)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.InventoryNum)}<=0";
            }
            if (!string.IsNullOrEmpty(dmodel.CategoryIds))
            {
                where += $@" and c.{nameof(RB_Goods_Category.CategoryId)} in({dmodel.CategoryIds})";
            }
            if (!string.IsNullOrEmpty(dmodel.StartTime))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.CreateDate)} >='{dmodel.StartTime}'";
            }
            if (!string.IsNullOrEmpty(dmodel.EndTime))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.CreateDate)} <='{dmodel.EndTime + " 23:59:59"}'";
            }

            string sql = $@"select g.* from 
rb_member_collection col
inner join RB_Goods g on col.GoodsId=g.Id
inner join rb_goods_category c on g.Id=c.GoodsId 
where {where} group by g.Id order by col.Id desc";
            return GetPage<RB_Goods_Extend>(pageIndex, pageSize, out rowCount, sql).ToList();
        }

        public List<RB_Goods_Extend> GetGoodsInventoryNumList(RB_Goods_Extend dmodel)
        {
            string where = $" 1=1 and g.{nameof(RB_Goods_Extend.Status)}=0";
            if (dmodel.TenantId > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (!string.IsNullOrEmpty(dmodel.GoodsIds))
            {
                where += $@" and g.{nameof(RB_Goods_Extend.Id)} in({dmodel.GoodsIds})";
            }
            if (dmodel.GoodsStatus > 0)
            {
                where += $@" and g.{nameof(RB_Goods_Extend.GoodsStatus)}={dmodel.GoodsStatus}";
            }

            string sql = $@"select g.Id,g. from RB_Goods g where {where} group by g.Id asc";
            return Get<RB_Goods_Extend>(sql).ToList();
        }

        /// <summary>
        /// 更新商品id
        /// </summary>
        /// <param name=""></param>
        /// <param name="OldId"></param>
        /// <returns></returns>
        public bool SetGoodsNewId(int newGoodsId, int oldGoodsId, System.Data.IDbTransaction trans)
        {
            string sql = $@"UPDATE rb_goods SET Id={newGoodsId} WHERE Id={oldGoodsId}";
            return Execute(sql, trans) > 0;
        }
    }
}
