using Mall.Model.Entity.Property;
using Mall.Model.Extend.Property;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Mall.Repository.Property
{
    /// <summary>
    /// 耗材入库单仓储类
    /// </summary>
    public partial class RB_Supplies_StockInRepository : BaseRepository<RB_Supplies_StockIn>
    {
        /// <summary>
        /// 获取耗材分页数据
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="dmodel"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public List<RB_Supplies_StockIn_Extend> GetPageList(int pageIndex, int pageSize, RB_Supplies_StockIn_Extend dmodel, out long count)
        {
            string where = " where 1=1 ";
            where += string.Format(" AND ss.{0}={1}", nameof(RB_Supplies_StockIn_Extend.Status), 0);
            where += $@" and ss.RB_Group_Id={dmodel.RB_Group_Id}";            
            if (!string.IsNullOrWhiteSpace(dmodel.StartTime))
            {
                where += $@" and ss.{nameof(RB_Supplies_StockIn_Extend.StockInDate)} >='{dmodel.StartTime}'";
            }
            if (!string.IsNullOrWhiteSpace(dmodel.EndTime))
            {
                where += $@" and ss.{nameof(RB_Supplies_StockIn_Extend.StockInDate)} <='{dmodel.EndTime + " 23:59:59"}'";
            }
            if (!string.IsNullOrWhiteSpace(dmodel.StockInNum))
            {
                where += " and ss." + nameof(RB_Supplies_StockIn_Extend.StockInNum) + " like '%" + dmodel.StockInNum + "%'";
            }
            if (!string.IsNullOrWhiteSpace(dmodel.SuppliesNum))
            {
                where += " and sm." + nameof(RB_Supplies_StockIn_Extend.SuppliesNum) + " like '%" + dmodel.SuppliesNum.Trim() + "%'";
            }
            if (!string.IsNullOrWhiteSpace(dmodel.SuppliesName))
            {
                where += " and sm." + nameof(RB_Supplies_Material.Name) + " like '%" + dmodel.SuppliesName.Trim() + "%'";
            }

            if (!string.IsNullOrWhiteSpace(dmodel.SupplierName))
            {
                where += " and ss." + nameof(RB_Supplies_StockIn_Extend.SupplierName) + " like '%" + dmodel.SupplierName.Trim() + "%'";
            }

            if (dmodel.WarehouseId > 0) {
                where += " and ss." + nameof(RB_Supplies_StockIn_Extend.WarehouseId) + "=" + dmodel.WarehouseId;
            }
            
            string sql = $@" select distinct ss.*,sw.Name as WareHouseName from RB_Supplies_StockIn ss
left join rb_supplies_warehouse sw on ss.WarehouseId=sw.Id
left join rb_supplies_stockindetail ssd on ss.Id=ssd.StockInId
left join rb_supplies_material sm on ssd.SuppliesId=sm.Id
{where} order by ss.Id desc";
            return GetPage<RB_Supplies_StockIn_Extend>(pageIndex, pageSize, out count, sql).ToList();
        }


        /// <summary>
        /// 获取耗材分页数据
        /// </summary>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<RB_Supplies_StockIn_Extend> GetList(RB_Supplies_StockIn_Extend dmodel)
        {
            string where = " where 1=1 ";
            where += string.Format(" AND ss.{0}={1}", nameof(RB_Supplies_StockIn_Extend.Status), 0);
            where += $@" and ss.RB_Group_Id={dmodel.RB_Group_Id}";
            if (!string.IsNullOrWhiteSpace(dmodel.StartTime))
            {
                where += $@" and ss.{nameof(RB_Supplies_StockIn_Extend.StockInDate)} >='{dmodel.StartTime}'";
            }
            if (!string.IsNullOrWhiteSpace(dmodel.EndTime))
            {
                where += $@" and ss.{nameof(RB_Supplies_StockIn_Extend.StockInDate)} <='{dmodel.EndTime + " 23:59:59"}'";
            }
            if (!string.IsNullOrWhiteSpace(dmodel.StockInNum))
            {
                where += " and ss." + nameof(RB_Supplies_StockIn_Extend.StockInNum) + " like '%" + dmodel.StockInNum + "%'";
            }

            if (!string.IsNullOrWhiteSpace(dmodel.SupplierName))
            {
                where += " and ss." + nameof(RB_Supplies_StockIn_Extend.SupplierName) + " like '%" + dmodel.SupplierName.Trim() + "%'";
            }
            if (dmodel.Id > 0) 
            {
                where += " and ss." + nameof(RB_Supplies_StockIn_Extend.Id) + "=" + dmodel.Id;
            }
            if (dmodel.WarehouseId > 0)
            {
                where += " and ss." + nameof(RB_Supplies_StockIn_Extend.WarehouseId) + "=" + dmodel.WarehouseId;
            }
            if (dmodel.ProcurementId > 0) 
            {
                where += " and ss." + nameof(RB_Supplies_StockIn_Extend.ProcurementId) + "=" + dmodel.ProcurementId;
            }

            string sql = $@" select ss.*,sw.Name as WareHouseName from RB_Supplies_StockIn ss
left join rb_supplies_warehouse sw on ss.WarehouseId=sw.Id
{where} order by ss.Id desc";
            return Get<RB_Supplies_StockIn_Extend>(sql).ToList();
        }

        /// <summary>
        /// 获取耗材入库出库情况
        /// </summary>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<RB_Supplies_StockIn_Extend> GetPropertyIndexSuppliesInStock(RB_Supplies_StockIn_Extend dmodel)
        {
            string where = " where 1=1 ";            
            where += $@" and si.RB_Group_Id={dmodel.RB_Group_Id}";
            if (dmodel.Type == 1)
            {
                //入库情况
                if (!string.IsNullOrEmpty(dmodel.StartTime))
                {
                    where += $@" and si.StockInDate >='{dmodel.StartTime}'";
                }
                if (!string.IsNullOrEmpty(dmodel.EndTime))
                {
                    where += $@" and si.StockInDate <='{dmodel.EndTime} 23:59:59'";
                }
                if (!string.IsNullOrEmpty(dmodel.SuppliesIds))
                {
                    where += $@" and sid.SuppliesId in({dmodel.SuppliesIds})";
                }
                string sql = $@" SELECT sid.SuppliesId,DATE_FORMAT(si.StockInDate,'%Y-%m') AS StartTime,SUM(IFNULL(sid.Number,0)) AS SuppliesNumber
 FROM rb_supplies_stockin si
LEFT JOIN rb_supplies_stockindetail sid on si.Id=sid.StockInId
{where} and sid.`Status`=0 and si.`Status`=0 AND si.StockInState=1
GROUP BY sid.SuppliesId,DATE_FORMAT(si.StockInDate,'%Y-%m')";
                return Get<RB_Supplies_StockIn_Extend>(sql).ToList();
            }
            else
            {
                //入库情况
                if (!string.IsNullOrEmpty(dmodel.StartTime))
                {
                    where += $@" and si.StockOutDate >='{dmodel.StartTime}'";
                }
                if (!string.IsNullOrEmpty(dmodel.EndTime))
                {
                    where += $@" and si.StockOutDate <='{dmodel.EndTime} 23:59:59'";
                }
                if (!string.IsNullOrEmpty(dmodel.SuppliesIds))
                {
                    where += $@" and sid.SuppliesId in({dmodel.SuppliesIds})";
                }
                string sql = $@" SELECT sid.SuppliesId,DATE_FORMAT(si.StockOutDate,'%Y-%m') AS StartTime,SUM(IFNULL(sid.Number,0)) AS SuppliesNumber
 FROM rb_supplies_stockout si
LEFT JOIN rb_supplies_stockoutdetail sid on si.Id=sid.StockOutId
{where} and sid.`Status`=0 and si.`Status`=0 AND si.StockOutState=1
GROUP BY sid.SuppliesId,DATE_FORMAT(si.StockOutDate,'%Y-%m')";
                return Get<RB_Supplies_StockIn_Extend>(sql).ToList();
            }
        }
    }
}
