using Infrastructure.Attribute;
using SqlSugar;
using ZR.Model;
using ZR.Model.MES.wms;
using ZR.Model.MES.wms.Dto;
using ZR.Repository;
using ZR.Service.mes.wms.IService;

namespace ZR.Service.Business
{
    /// <summary>
    /// 仓库操作日志Service业务层处理
    /// </summary>
    [AppService(ServiceType = typeof(IWmGoodsBatchSearchService), ServiceLifetime = LifeTime.Transient)]
    public class WmGoodsBatchSearchService : BaseService<WmGoodsBatchSearchDto>, IWmGoodsBatchSearchService
    {

        public PagedInfo<WmGoodsBatchTableDto> GetBatchNowProductionByPackageCodeShort(WmGoodsBatchSearchDto parm)
        {
            try
            {
                // 查询条件
                var exp = Expressionable.Create<WmGoodsNowProduction>()
                .AndIF(!string.IsNullOrEmpty(parm.Partnumber), it => it.Partnumber.Contains(parm.Partnumber))
                // 根据sql语句进行切换
                .AndIF(!string.IsNullOrEmpty(parm.PackageCode), it => it.PackageCode.Contains(parm.PackageCode))
                .ToExpression();
                string sql = "SELECT\n" +
                            "	MAX( id ) AS ID,\n" +
                            "	SUBSTRING_INDEX( package_code_client, '_', 1 ) AS PackageCodeClient_son,\n" +
                            "	NULL AS PackageCodeClient_short_parent,\n" +
                            "	partnumber AS Partnumber,\n" +
                            "	'' AS Description,\n" +
                            "	SUBSTRING_INDEX( package_code_client, '_', 1 ) AS PackageCode,\n" +
                            "	SUBSTRING_INDEX( package_code_client, '_', 1 ) AS package_code,\n" +
                            "	MAX( location_code ) AS LocationCode,\n" +
                            "	COUNT( * ) AS PackageNum,\n" +
                            "	SUM( goods_num_logic ) AS GoodsNumLogic,\n" +
                            "	SUM( goods_num_action ) AS GoodsNumAction,\n" +
                            "	MAX( remark ) AS Remark,\n" +
                            "	MAX( entry_warehouse_time ) AS EntryWarehouseTime,\n" +
                            "	TRUE AS HasChild \n" +
                            "FROM\n" +
                            "	wm_goods_now_production \n" +
                            "GROUP BY\n" +
                            "	PackageCode, partnumber \n" +
                            "ORDER BY\n" +
                            "	PackageCodeClient_son";
                PagedInfo<WmGoodsBatchTableDto> result = Context.SqlQueryable<WmGoodsNowProduction>(sql)
                    .Where(exp)
                    .Select<WmGoodsBatchTableDto>()
                    .ToPage(parm);
                foreach (WmGoodsBatchTableDto item in result.Result)
                {
                    WmMaterial material = Context.Queryable<WmMaterial>()
                            .Where(it => it.Partnumber == item.Partnumber)
                            .Where(it => it.Type == 1)
                            .First();
                    if (material == null)
                    {
                        item.Description = "此零件号不在物料清单内！";
                        continue;
                    }
                    item.Description = !string.IsNullOrEmpty(material.Description) ? material.Description : material.ProductName;
                }
                return result;
            }
            catch
            {
                return null;
            }
        }

        public PagedInfo<WmGoodsBatchTableDto> GetBatchNowProductionTreeLazyByPackageCodeShort(WmGoodsBatchSearchDto parm)
        {
            try
            {
                PagedInfo<WmGoodsBatchTableDto> result = new PagedInfo<WmGoodsBatchTableDto>();
                // 入库记录
                List<WmGoodsBatchTableDto> list = Context.Queryable<WmGoodsNowProduction>()
                        .Where(it => it.PackageCodeClient.Contains(parm.PackageCode))
                        .Select(it => new WmGoodsBatchTableDto
                        {
                            Id = it.Id,
                            PackageCode = it.PackageCodeClient,
                            PackageCodeClient_son = null,
                            PackageCodeClient_short_parent = parm.PackageCode,
                            Partnumber = it.Partnumber,
                            Description = "",
                            LocationCode = it.LocationCode,
                            PackageNum = 1,
                            GoodsNumLogic = (int)it.GoodsNumLogic,
                            GoodsNumAction = (int)it.GoodsNumAction,
                            EntryWarehouseTime = it.EntryWarehouseTime,
                            Remark = it.Remark,
                            HasChild = false
                        })
                        .OrderByDescending(it => it.EntryWarehouseTime)
                        .ToList();
                foreach (WmGoodsBatchTableDto item in list)
                {
                    WmMaterial material = Context.Queryable<WmMaterial>()
                            .Where(it => it.Partnumber == item.Partnumber)
                            .Where(it => it.Type == 1)
                            .First();
                    if (material == null)
                    {
                        item.Description = "此零件号不在物料清单内！";
                        continue;
                    }
                    item.Description = !string.IsNullOrEmpty(material.Description) ? material.Description : material.ProductName;
                }
                result.Result = list;
                return result;
            }
            catch
            {
                return null;
            }
        }

        public PagedInfo<WmGoodsBatchTableDto> GetBatchNowProductionByPartnumber(WmGoodsBatchSearchDto parm)
        {
            try
            {
                // 查询条件
                var exp = Expressionable.Create<WmGoodsNowProduction>()
                .AndIF(!string.IsNullOrEmpty(parm.Partnumber), it => it.Partnumber.Contains(parm.Partnumber))
                // 根据sql语句进行切换
                //.AndIF(!string.IsNullOrEmpty(parm.PackageCode), it => it.PackageCode.Contains(parm.PackageCode))
                .ToExpression();
                string sql = "SELECT\n" +
                            "	MAX( id ) AS ID,\n" +
                            "	MAX( partnumber ) AS PackageCodeClient_son,\n" +
                            "	NULL AS PackageCodeClient_short_parent,\n" +
                            "	MAX( partnumber ) AS Partnumber,\n" +
                            "	'' AS Description,\n" +
                            "	'' AS PackageCode,\n" +
                            "	'' AS LocationCode,\n" +
                            "	COUNT( * ) AS PackageNum,\n" +
                            "	SUM( goods_num_logic ) AS GoodsNumLogic,\n" +
                            "	SUM( goods_num_action ) AS GoodsNumAction,\n" +
                            "	'' AS Remark,\n" +
                            "	NULL AS EntryWarehouseTime,\n" +
                            "	TRUE AS HasChild \n" +
                            "FROM\n" +
                            "	wm_goods_now_production \n" +
                            "GROUP BY\n" +
                            "	Partnumber \n" +
                            "ORDER BY\n" +
                            "	PackageCodeClient_son";
                PagedInfo<WmGoodsBatchTableDto> result = Context.SqlQueryable<WmGoodsNowProduction>(sql)
                    .Where(exp)
                    .Select<WmGoodsBatchTableDto>()
                    .ToPage(parm);
                foreach (WmGoodsBatchTableDto item in result.Result)
                {
                    WmMaterial material = Context.Queryable<WmMaterial>()
                            .Where(it => it.Partnumber == item.Partnumber)
                            .Where(it => it.Type == 1)
                            .First();
                    if (material == null)
                    {
                        item.Description = "此零件号不在物料清单内！";
                        continue;
                    }
                    item.Description = !string.IsNullOrEmpty(material.Description) ? material.Description : material.ProductName;
                }
                return result;
            }
            catch
            {
                return null;
            }
        }

        public PagedInfo<WmGoodsBatchTableDto> GetBatchNowProductionTreeLazyByPartnumber(WmGoodsBatchSearchDto parm)
        {
            try
            {
                PagedInfo<WmGoodsBatchTableDto> result = new PagedInfo<WmGoodsBatchTableDto>();
                // 出库记录
                List<WmGoodsBatchTableDto> list = Context.Queryable<WmGoodsNowProduction>()
                        .Where(it => it.Partnumber == parm.Partnumber)
                        .Select(it => new WmGoodsBatchTableDto
                        {
                            Id = it.Id,
                            PackageCode = it.PackageCodeClient,
                            PackageCodeClient_son = null,
                            PackageCodeClient_short_parent = it.Partnumber,
                            Partnumber = it.Partnumber,
                            Description = "",
                            LocationCode = it.LocationCode,
                            PackageNum = 1,
                            GoodsNumLogic = (int)it.GoodsNumLogic,
                            GoodsNumAction = (int)it.GoodsNumAction,
                            EntryWarehouseTime = it.EntryWarehouseTime,
                            Remark = it.Remark,
                            HasChild = false
                        })
                        .OrderByDescending(it => it.EntryWarehouseTime)
                        .ToList();
                foreach (WmGoodsBatchTableDto item in list)
                {
                    WmMaterial material = Context.Queryable<WmMaterial>()
                            .Where(it => it.Partnumber == item.Partnumber)
                            .Where(it => it.Type == 1)
                            .First();
                    if (material == null)
                    {
                        item.Description = "此零件号不在物料清单内！";
                        continue;
                    }
                    item.Description = !string.IsNullOrEmpty(material.Description) ? material.Description : material.ProductName;
                }
                result.Result = list;
                return result;
            }
            catch
            {
                return null;
            }
        }


        public PagedInfo<WmGoodsBatchTableDto> GetBatchOutRecordByPackageCodeShort(WmGoodsBatchSearchDto parm)
        {
            try
            {
                // 查询条件
                var exp = Expressionable.Create<WmGoodsOutRecord>()
                .AndIF(!string.IsNullOrEmpty(parm.Partnumber), it => it.Partnumber.Contains(parm.Partnumber))
                // 根据sql语句进行切换
                .AndIF(!string.IsNullOrEmpty(parm.PackageCode), it => it.PackageCode.Contains(parm.PackageCode))
                .ToExpression();
                string sql = "SELECT\n" +
                            "	MAX( id ) AS ID,\n" +
                            "	SUBSTRING_INDEX( package_code_client, '_', 1 ) AS PackageCodeClient_son,\n" +
                            "	NULL AS PackageCodeClient_short_parent,\n" +
                            "	MAX( partnumber ) AS Partnumber,\n" +
                            "	'' AS Description,\n" +
                            "	SUBSTRING_INDEX( package_code_client, '_', 1 ) AS PackageCode,\n" +
                            "	SUBSTRING_INDEX( package_code_client, '_', 1 ) AS package_code,\n" +
                            "	MAX( location_code ) AS LocationCode,\n" +
                            "	COUNT( * ) AS PackageNum,\n" +
                            "	SUM( goods_num_logic ) AS GoodsNumLogic,\n" +
                            "	SUM( goods_num_action ) AS GoodsNumAction,\n" +
                            "	MAX( remark ) AS Remark,\n" +
                            "	MAX( out_time ) AS OutTime,\n" +
                            "	TRUE AS HasChild \n" +
                            "FROM\n" +
                            "	wm_goods_out_record \n" +
                            "GROUP BY\n" +
                            "	PackageCode \n" +
                            "ORDER BY\n" +
                            "	PackageCodeClient_son";
                PagedInfo<WmGoodsBatchTableDto> result = Context.SqlQueryable<WmGoodsOutRecord>(sql)
                    .Where(exp)
                    .Select<WmGoodsBatchTableDto>()
                    .ToPage(parm);
                foreach (WmGoodsBatchTableDto item in result.Result)
                {
                    WmMaterial material = Context.Queryable<WmMaterial>()
                            .Where(it => it.Partnumber == item.Partnumber)
                            .Where(it => it.Type == 1)
                            .First();
                    if (material == null)
                    {
                        item.Description = "此零件号不在物料清单内！";
                        continue;
                    }
                    item.Description = !string.IsNullOrEmpty(material.Description) ? material.Description : material.ProductName;
                }
                return result;
            }
            catch
            {
                return null;
            }

        }


        public PagedInfo<WmGoodsBatchTableDto> GetBatchOutRecordTreeLazyByPackageCodeShort(WmGoodsBatchSearchDto parm)
        {
            try
            {
                PagedInfo<WmGoodsBatchTableDto> result = new PagedInfo<WmGoodsBatchTableDto>();
                // 出库记录
                List<WmGoodsBatchTableDto> list = Context.Queryable<WmGoodsOutRecord>()
                        .Where(it => it.PackageCodeClient.Contains(parm.PackageCode))
                        .Select(it => new WmGoodsBatchTableDto
                        {
                            Id = it.Id,
                            PackageCode = it.PackageCodeClient,
                            PackageCodeClient_son = null,
                            PackageCodeClient_short_parent = parm.PackageCode,
                            Partnumber = it.Partnumber,
                            Description = "",
                            LocationCode = it.LocationCode,
                            PackageNum = 1,
                            GoodsNumLogic = (int)it.GoodsNumLogic,
                            GoodsNumAction = (int)it.GoodsNumAction,
                            OutTime = it.OutTime,
                            Remark = it.Remark,
                            HasChild = false
                        })
                        .OrderByDescending(it => it.OutTime)
                        .ToList();
                foreach (WmGoodsBatchTableDto item in list)
                {
                    WmMaterial material = Context.Queryable<WmMaterial>()
                            .Where(it => it.Partnumber == item.Partnumber)
                            .Where(it => it.Type == 1)
                            .First();
                    if (material == null)
                    {
                        item.Description = "此零件号不在物料清单内！";
                        continue;
                    }
                    item.Description = !string.IsNullOrEmpty(material.Description) ? material.Description : material.ProductName;
                }
                result.Result = list;
                return result;
            }
            catch
            {
                return null;
            }
        }

        public PagedInfo<WmGoodsBatchTableDto> GetBatchOutRecordByPartnumber(WmGoodsBatchSearchDto parm)
        {
            try
            {
                // 查询条件
                var exp = Expressionable.Create<WmGoodsOutRecord>()
                .AndIF(!string.IsNullOrEmpty(parm.Partnumber), it => it.Partnumber.Contains(parm.Partnumber))
                // 根据sql语句进行切换
                //.AndIF(!string.IsNullOrEmpty(parm.PackageCode), it => it.PackageCode.Contains(parm.PackageCode))
                .ToExpression();
                string sql = "SELECT\n" +
                            "	MAX( id ) AS ID,\n" +
                            "	MAX( partnumber ) AS PackageCodeClient_son,\n" +
                            "	NULL AS PackageCodeClient_short_parent,\n" +
                            "	MAX( partnumber ) AS Partnumber,\n" +
                            "	'' AS Description,\n" +
                            "	'' AS PackageCode,\n" +
                            "	'' AS LocationCode,\n" +
                            "	COUNT( * ) AS PackageNum,\n" +
                            "	SUM( goods_num_logic ) AS GoodsNumLogic,\n" +
                            "	SUM( goods_num_action ) AS GoodsNumAction,\n" +
                            "	'' AS Remark,\n" +
                            "	NULL AS OutTime,\n" +
                            "	TRUE AS HasChild \n" +
                            "FROM\n" +
                            "	wm_goods_out_record \n" +
                            "GROUP BY\n" +
                            "	Partnumber \n" +
                            "ORDER BY\n" +
                            "	PackageCodeClient_son";
                PagedInfo<WmGoodsBatchTableDto> result = Context.SqlQueryable<WmGoodsOutRecord>(sql)
                    .Where(exp)
                    .Select<WmGoodsBatchTableDto>()
                    .ToPage(parm);
                foreach (WmGoodsBatchTableDto item in result.Result)
                {
                    WmMaterial material = Context.Queryable<WmMaterial>()
                            .Where(it => it.Partnumber == item.Partnumber)
                            .Where(it => it.Type == 1)
                            .First();
                    if (material == null)
                    {
                        item.Description = "此零件号不在物料清单内！";
                        continue;
                    }
                    item.Description = !string.IsNullOrEmpty(material.Description) ? material.Description : material.ProductName;
                }
                return result;
            }
            catch
            {
                return null;
            }
        }
        public PagedInfo<WmGoodsBatchTableDto> GetBatchOutRecordTreeLazyByPartnumber(WmGoodsBatchSearchDto parm)
        {
            try
            {
                PagedInfo<WmGoodsBatchTableDto> result = new PagedInfo<WmGoodsBatchTableDto>();
                // 出库记录
                List<WmGoodsBatchTableDto> list = Context.Queryable<WmGoodsOutRecord>()
                        .Where(it => it.Partnumber == parm.Partnumber)
                        .Select(it => new WmGoodsBatchTableDto
                        {
                            Id = it.Id,
                            PackageCode = it.PackageCodeClient,
                            PackageCodeClient_son = null,
                            PackageCodeClient_short_parent = it.Partnumber,
                            Partnumber = it.Partnumber,
                            Description = "",
                            LocationCode = it.LocationCode,
                            PackageNum = 1,
                            GoodsNumLogic = (int)it.GoodsNumLogic,
                            GoodsNumAction = (int)it.GoodsNumAction,
                            OutTime = it.OutTime,
                            Remark = it.Remark,
                            HasChild = false
                        })
                        .OrderByDescending(it => it.OutTime)
                        .ToList();
                foreach (WmGoodsBatchTableDto item in list)
                {
                    WmMaterial material = Context.Queryable<WmMaterial>()
                            .Where(it => it.Partnumber == item.Partnumber)
                            .Where(it => it.Type == 1)
                            .First();
                    if (material == null)
                    {
                        item.Description = "此零件号不在物料清单内！";
                        continue;
                    }
                    item.Description = !string.IsNullOrEmpty(material.Description) ? material.Description : material.ProductName;
                }
                result.Result = list;
                return result;
            }
            catch
            {
                return null;
            }
        }

        public PagedInfo<WmGoodsBatchTableDto> GetBatchOutRecordByShipmentNum(WmGoodsBatchSearchDto parm)
        {
            try
            {
                // 查询条件
                var exp = Expressionable.Create<WmGoodsOutRecord>()
                .AndIF(!string.IsNullOrEmpty(parm.ShipmentNum), it => it.FkOutOrderId.Contains(parm.ShipmentNum))
                .And(it => it.FkOutOrderId != string.Empty)
                // 根据sql语句进行切换
                //.AndIF(!string.IsNullOrEmpty(parm.PackageCode), it => it.PackageCode.Contains(parm.PackageCode))
                .ToExpression();
                string sql = "SELECT\n" +
                            "	MAX( id ) AS ID,\n" +
                            "	MAX( fk_out_order_id ) AS fk_out_order_id,\n" +
                            "	IFNULL( fk_out_order_id, '无出库单' ) AS ShipmentNum,\n" +
                            "	IFNULL( fk_out_order_id, '无出库单' ) AS PackageCodeClient_son,\n" +
                            "	NULL AS PackageCodeClient_short_parent,\n" +
                            "	'' AS Partnumber,\n" +
                            "	'' AS Description,\n" +
                            "	'' AS PackageCode,\n" +
                            "	'' AS LocationCode,\n" +
                            "	COUNT( * ) AS PackageNum,\n" +
                            "	SUM( goods_num_logic ) AS GoodsNumLogic,\n" +
                            "	SUM( goods_num_action ) AS GoodsNumAction,\n" +
                            "	'' AS Remark,\n" +
                            "	NULL AS OutTime,\n" +
                            "	TRUE AS HasChild \n" +
                            "FROM\n" +
                            "	wm_goods_out_record \n" +
                            "GROUP BY\n" +
                            "	ShipmentNum \n" +
                            "ORDER BY\n" +
                            "	ShipmentNum DESC";
                PagedInfo<WmGoodsBatchTableDto> result = Context.SqlQueryable<WmGoodsOutRecord>(sql)
                    .Where(exp)
                    .Select<WmGoodsBatchTableDto>()
                    .ToPage(parm);
                return result;
            }
            catch
            {
                return null;
            }
        }

        public PagedInfo<WmGoodsBatchTableDto> GetBatchOutRecordTreeLazyByShipmentNum(WmGoodsBatchSearchDto parm)
        {
            try
            {
                PagedInfo<WmGoodsBatchTableDto> result = new PagedInfo<WmGoodsBatchTableDto>();
                // 出库记录
                List<WmGoodsBatchTableDto> list = Context.Queryable<WmGoodsOutRecord>()
                        .Where(it => it.FkOutOrderId == parm.ShipmentNum)
                        .Select(it => new WmGoodsBatchTableDto
                        {
                            Id = it.Id,
                            ShipmentNum = it.FkOutOrderId,
                            PackageCode = it.PackageCodeClient,
                            PackageCodeClient_son = null,
                            PackageCodeClient_short_parent = it.FkOutOrderId,
                            Partnumber = it.Partnumber,
                            Description = "",
                            LocationCode = it.LocationCode,
                            PackageNum = 1,
                            GoodsNumLogic = (int)it.GoodsNumLogic,
                            GoodsNumAction = (int)it.GoodsNumAction,
                            OutTime = it.OutTime,
                            Remark = it.Remark,
                            HasChild = false
                        })
                        .OrderByDescending(it => it.OutTime)
                        .ToList();
                foreach (WmGoodsBatchTableDto item in list)
                {
                    WmMaterial material = Context.Queryable<WmMaterial>()
                            .Where(it => it.Partnumber == item.Partnumber)
                            .Where(it => it.Type == 1)
                            .First();
                    if (material == null)
                    {
                        item.Description = "此零件号不在物料清单内！";
                        continue;
                    }
                    item.Description = !string.IsNullOrEmpty(material.Description) ? material.Description : material.ProductName;
                }
                result.Result = list;
                return result;
            }
            catch
            {
                return null;
            }
        }
    }
}