SDLC_WMS/Server/Wms.Core.Application.Query/Implements/StockService.cs

using AutoMapper;
using Kean.Application.Query.Interfaces;
using Kean.Application.Query.ViewModels;
using Kean.Infrastructure.Database;
using Kean.Infrastructure.Database.Repository.Default;
using Kean.Infrastructure.Database.Repository.Default.Entities;
using Kean.Infrastructure.Utilities;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace Kean.Application.Query.Implements
{
    /// <summary>
    /// 库存信息查询服务实现
    /// </summary>
    public sealed class StockService : IStockService
    {
        private readonly IMapper _mapper; // 模型映射
        private readonly IDefaultDb _database; // 默认数据库

        /// <summary>
        /// 依赖注入
        /// </summary>
        public StockService(
            IMapper mapper,
            IDefaultDb database)
        {
            _mapper = mapper;
            _database = database;
        }

        /*
         * 实现 Kean.Application.Query.Interfaces.IStockService.GetStockCount 方法
         */
        public async Task<int> GetStockCount(
            int[] area,
            bool? pallet,
            bool? palletized,
            int[] category,
            string code,
            string name,
            string batch,
            string barcode,
            string cell,
            string qc,
            DateTime? inboundFrom,
            DateTime? inboundTo,
            DateTime? inventoryFrom,
            DateTime? inventoryTo,
            bool? overdue,
            bool? enabled,
            bool? excludeTask,
            string qualityState,
            string materialAgeStatus,
            DateTime? manufacturingDateFrom,
            DateTime? manufacturingDateTo,
            string remark,
            string supplier,
            string laneway,
            string workorderNo,
            string bill)
        {
            return (await GetStockSchema(area, pallet, palletized, category, code, name, batch, barcode, cell, qc, inboundFrom, inboundTo, inventoryFrom, inventoryTo, overdue, enabled, excludeTask, qualityState, materialAgeStatus, manufacturingDateFrom, manufacturingDateTo, remark, supplier, laneway, workorderNo, bill)
                .Single(s => new { Count = Function.Count(s.STORAGE_LIST_ID) }))
                .Count;
        }

        /*
        * 实现 Kean.Application.Query.Interfaces.IStockService.GetStockQtySum 方法
        */
        public async Task<decimal> GetStockQtySum(
             int[] area,
            bool? pallet,
            bool? palletized,
            int[] category,
            string code,
            string name,
            string batch,
            string barcode,
            string cell,
            string qc,
            DateTime? inboundFrom,
            DateTime? inboundTo,
            DateTime? inventoryFrom,
            DateTime? inventoryTo,
            bool? overdue,
            bool? enabled,
            bool? excludeTask,
            string sort,
            int? offset,
            int? limit,
            string qualityState,
            string materialAgeStatus,
            DateTime? manufacturingDateFrom,
            DateTime? manufacturingDateTo,
            string remark,
            string supplier,
            string laneway,
            string workorderNo,
            string bill)
        {
            var temp = (await GetStockSchema(area, pallet, palletized, category, code, name, batch, barcode, cell, qc, inboundFrom, inboundTo, inventoryFrom, inventoryTo, overdue, enabled, excludeTask, qualityState, materialAgeStatus, manufacturingDateFrom, manufacturingDateTo, remark, supplier, laneway, workorderNo, bill)
                .Sort<V_STORAGE_LIST, Stock>(sort, _mapper)
                .OrderBy(r => r.STORAGE_LIST_ID, Infrastructure.Database.Order.Ascending)
                .Page(offset, limit)
                .Select());

            return (temp.Sum(r=>r.QTY));
        }

        /*
         * 实现 Kean.Application.Query.Interfaces.IStockService.GetStockList 方法
         */
        public async Task<IEnumerable<Stock>> GetStockList(
            int[] area,
            bool? pallet,
            bool? palletized,
            int[] category,
            string code,
            string name,
            string batch,
            string barcode,
            string cell,
            string qc,
            DateTime? inboundFrom,
            DateTime? inboundTo,
            DateTime? inventoryFrom,
            DateTime? inventoryTo,
            bool? overdue,
            bool? enabled,
            bool? excludeTask,
            string sort,
            int? offset,
            int? limit,
            string qualityState,
            string materialAgeStatus,
            DateTime? manufacturingDateFrom,
            DateTime? manufacturingDateTo,
            string remark,
            string supplier,
            string laneway,
            string workorderNo,
            string bill)
        {
            var stock =  _mapper.Map<IEnumerable<Stock>>(await GetStockSchema(area, pallet, palletized, category, code, name, batch, barcode, cell, qc, inboundFrom, inboundTo, inventoryFrom, inventoryTo, overdue, enabled, excludeTask, qualityState, materialAgeStatus, manufacturingDateFrom, manufacturingDateTo, remark, supplier, laneway, workorderNo, bill)
                .Sort<V_STORAGE_LIST, Stock>(sort, _mapper)
                .OrderBy(r => r.STORAGE_LIST_ID, Infrastructure.Database.Order.Ascending)
                .Page(offset, limit)
                .Select());
            return stock;
        }

        /*
         * 组织 GetStock 相关方法的条件
         */
        private ISchema<V_STORAGE_LIST> GetStockSchema(
            int[] area,
            bool? pallet,
            bool? palletized,
            int[] category,
            string code,
            string name,
            string batch,
            string barcode,
            string cell,
            string qc,
            DateTime? inboundFrom,
            DateTime? inboundTo,
            DateTime? inventoryFrom,
            DateTime? inventoryTo,
            bool? overdue,
            bool? enabled,
            bool? excludeTask,
            string qualityState,
            string materialAgeStatus,
            DateTime? manufacturingDateFrom,
            DateTime? manufacturingDateTo,
            string remark,
            string supplier,
            string laneway,
            string workorderNo,
            string bill)
        {
            var schema = _database.From<V_STORAGE_LIST>();
            if (area != null)
            {
                schema = schema.Where(s => area.Contains(s.AREA_ID));
            }
            if (pallet.HasValue)
            {
                schema = pallet.Value ?
                    schema.Where(s => s.CLASS_ID > 4) :
                    schema.Where(s => s.CLASS_ID < 4);
            }
            if (palletized.HasValue)
            {
                schema = palletized.Value ?
                    schema.Where(s => !s.STOCK_BARCODE.StartsWith("#@")) :
                    schema.Where(s => s.STOCK_BARCODE.StartsWith("#@"));
            }
            if (category != null)
            {
                schema = schema.Where(s => category.Contains(s.CLASS_ID.Value));
            }
            if (code != null)
            {
                schema = schema.Where(s => s.GOODS_CODE.Contains(code));
            }
            if (name != null)
            {
                schema = schema.Where(s => s.GOODS_NAME.Contains(name));
            }
            if (batch != null)
            {
                schema = schema.Where(s => s.GOODS_BATCH_NO.Contains(batch));
            }
            if (barcode != null)
            {
                schema = barcode == string.Empty ?
                    schema.Where(s => s.STOCK_BARCODE == barcode) :
                    schema = schema.Where(s => s.STOCK_BARCODE.Contains(barcode));
            }
            if (cell != null)
            {
                schema = schema.Where(s => s.CELL_NAME == cell);
            }
            if (qc != null)
            {
                schema = schema.Where(s => s.GOODS_QC_STATE == qc);
            }
            if (qualityState != null)
            {
                schema = schema.Where(s => s.GOODS_QC_STATE == qualityState);
            }
            if (materialAgeStatus != null)
            {
                schema = schema.Where(s => s.GOODS_AGE_STATUS == materialAgeStatus);
            }
            if (inboundFrom.HasValue)
            {
                schema = schema.Where(s => s.INBOUND_TIME >= inboundFrom.Value);
            }
            if (inboundTo.HasValue)
            {
                schema = schema.Where(s => s.INBOUND_TIME <= inboundTo.Value.AddDays(1));
            }
            if (inventoryFrom.HasValue)
            {
                schema = schema.Where(s => s.INVENTORY_TIME >= inventoryFrom.Value);
            }
            if (inventoryTo.HasValue)
            {
                schema = schema.Where(s => s.INVENTORY_TIME <= inventoryTo.Value.AddDays(1));
            }
            if (overdue != null)
            {
                schema = overdue.Value ?
                    schema.Where(s => s.OVERDUE_TIME != null && s.OVERDUE_TIME != 0) :
                    schema.Where(s => s.OVERDUE_TIME == null || s.OVERDUE_TIME == 0);
            }
            if (enabled != null)
            {
                schema = enabled.Value ?
                    schema.Where(s => s.STORAGE_LIST_FLAG == true || (s.STORAGE_LIST_FLAG == null && (s.GOODS_QC_STATE == null || s.GOODS_QC_STATE == "ok") && (s.OVERDUE_TIME == null || s.OVERDUE_TIME == 0))) :
                    schema.Where(s => s.STORAGE_LIST_FLAG == false || (s.STORAGE_LIST_FLAG == null && (s.GOODS_QC_STATE == "na" || s.GOODS_QC_STATE == "ng" || s.OVERDUE_TIME != 0)));
            }
            if (excludeTask == true)
            {
                var query = _database.From<T_MANAGE_MAIN>().Query(m => m.STOCK_BARCODE);
                schema = schema.Where(s => !query.Contains(s.STOCK_BARCODE));
            }
            if (manufacturingDateFrom.HasValue)
            {
                schema = schema.Where(s => s.GOODS_MFG >= manufacturingDateFrom.Value);
            }
            if (manufacturingDateTo.HasValue)
            {
                schema = schema.Where(s => s.GOODS_MFG <= manufacturingDateTo.Value.AddDays(1));
            }
            if (remark != null)
            {
                schema = schema.Where(s => s.STORAGE_LIST_REMARK.Contains(remark));
            }
            if (supplier != null)
            {
                schema = schema.Where(s => s.GOODS_SUPPLIER == supplier);
            }
            if (laneway != null)
            {
                int way = 0;
                try
                {
                    way = Convert.ToInt32(laneway);
                }
                catch { }
                schema = schema.Where(s => s.CELL_LANEWAY == way);
            }
            if (workorderNo != null)
            {
                schema = schema.Where(s => s.WORKORDER_NO == workorderNo);
            }
            if (bill != null)
            {
                schema = schema.Where(s => s.GOODS_BILL_NO.Contains(bill));
            }
            return schema;
        }

        /*
         * 实现 Kean.Application.Query.Interfaces.IStockService.GetSafetyCount 方法
         */
        public async Task<int> GetSafetyCount(
            string type,
            int[] warehouse,
            int[] category,
            string code,
            string name)
        {
            return (await GetSafetySchema(type, warehouse, category, code, name)
                .Single(s => new { Count = Function.Count(s.SAFETY_ID) }))
                .Count;
        }

        /*
         * 实现 Kean.Application.Query.Interfaces.IStockService.GetSafetyList 方法
         */
        public async Task<IEnumerable<Safety>> GetSafetyList(
            string type,
            int[] warehouse,
            int[] category,
            string code,
            string name,
            string sort,
            int? offset,
            int? limit)
        {
            return _mapper.Map<IEnumerable<Safety>>(await GetSafetySchema(type, warehouse, category, code, name)
                .Sort<V_STORAGE_SAFETY, Safety>(sort, _mapper)
                .OrderBy(s => s.GOODS_CODE, Infrastructure.Database.Order.Ascending)
                .Page(offset, limit)
                .Select());
        }

        /*
         * 组织 GetSafety 相关方法的条件
         */
        private ISchema<V_STORAGE_SAFETY> GetSafetySchema(
            string type,
            int[] warehouse,
            int[] category,
            string code,
            string name)
        {
            var schema = _database.From<V_STORAGE_SAFETY>()
                .Where(s => s.GOODS_FLAG == true || s.STORAGE_QUANTITY > 0);
            switch (type)
            {
                case null:
                    break;
                case "safety":
                    schema = schema.Where(s => (s.LOWER_LIMIT == null || s.STORAGE_QUANTITY >= s.LOWER_LIMIT) && (s.UPPER_LIMIT == null || s.STORAGE_QUANTITY <= s.UPPER_LIMIT));
                    break;
                case "warning":
                    schema = schema.Where(s => s.STORAGE_QUANTITY < s.LOWER_LIMIT || s.STORAGE_QUANTITY > s.UPPER_LIMIT);
                    break;
                case "shortage":
                    schema = schema.Where(s => s.STORAGE_QUANTITY < s.LOWER_LIMIT);
                    break;
                case "excess":
                    schema = schema.Where(s => s.STORAGE_QUANTITY > s.UPPER_LIMIT);
                    break;
                default:
                    schema = schema.Where(s => s.STORAGE_QUANTITY < 0);
                    break;
            }
            if (warehouse != null)
            {
                var query = _database.From<T_GOODS_SAFETY_LIST>().Where(s => warehouse.Contains(s.WAREHOUSE_ID)).Query(s => s.SAFETY_ID);
                schema = schema.Where(s => query.Contains(s.SAFETY_ID));
            }
            if (category != null)
            {
                schema = schema.Where(s => category.Contains(s.CLASS_ID));
            }
            if (code != null)
            {
                schema = schema.Where(s => s.GOODS_CODE.Contains(code));
            }
            if (name != null)
            {
                schema = schema.Where(s => s.GOODS_NAME.Contains(name));
            }
            return schema;
        }

        /*
         * 实现 Kean.Application.Query.Interfaces.IStockService.GetRecordCount 方法
         */
        public async Task<int> GetRecordCount(
            int[] area,
            int[] category,
            string code,
            string name,
            string batch,
            string[] transaction,
            string barcode,
            string cell,
            string original,
            string destination,
            DateTime? beginFrom,
            DateTime? beginTo,
            DateTime? endFrom,
            DateTime? endTo,
            string bill,
            string slTarget,
            string orderInfo)
        {
            return (await GetRecordSchema(area, category, code, name, batch, transaction, barcode, cell, original, destination, beginFrom, beginTo, endFrom, endTo, bill, slTarget, orderInfo)
                .Single(r => new { Count = Function.Count(r.RECORD_ID) }))
                .Count;
        }

        /*
         * 实现 Kean.Application.Query.Interfaces.IStockService.GetRecordList 方法
         */
        public async Task<IEnumerable<Record>> GetRecordList(
            int[] area,
            int[] category,
            string code,
            string name,
            string batch,
            string[] transaction,
            string barcode,
            string cell,
            string original,
            string destination,
            DateTime? beginFrom,
            DateTime? beginTo,
            DateTime? endFrom,
            DateTime? endTo,
            string sort,
            int? offset,
            int? limit,
            string bill,
            string slTarget,
            string orderInfo)
        {
            var properties = new Dictionary<string, string>();
            var record = await GetRecordSchema(area, category, code, name, batch, transaction, barcode, cell, original, destination, beginFrom, beginTo, endFrom, endTo, bill, slTarget, orderInfo)
                .Sort<V_RECORD_LIST, Record>(sort, _mapper)
                //.OrderBy(r => r.BEGIN_TIME, Infrastructure.Database.Order.Descending)
                .OrderBy(r => r.RECORD_ID, Infrastructure.Database.Order.Descending)
                .Page(offset, limit)
                .Select();
            return _mapper.Map<IEnumerable<Record>>(record);
        }

        /*
        * 实现 Kean.Application.Query.Interfaces.IStockService.GetRecordListQtySum 方法
        */
        public async Task<decimal> GetRecordListQtySum(
            int[] area,
            int[] category,
            string code,
            string name,
            string batch,
            string[] transaction,
            string barcode,
            string cell,
            string original,
            string destination,
            DateTime? beginFrom,
            DateTime? beginTo,
            DateTime? endFrom,
            DateTime? endTo,
            string sort,
            int? offset,
            int? limit,
            string bill,
            string slTarget,
            string orderInfo)
        {
            var properties = new Dictionary<string, string>();
            var temp = await GetRecordSchema(area, category, code, name, batch, transaction, barcode, cell, original, destination, beginFrom, beginTo, endFrom, endTo, bill, slTarget, orderInfo)
                .Sort<V_RECORD_LIST, Record>(sort, _mapper)
                //.OrderBy(r => r.BEGIN_TIME, Infrastructure.Database.Order.Descending)
                .OrderBy(r => r.RECORD_ID, Infrastructure.Database.Order.Descending)
                .Page(offset, limit)
                .Select();

            return (temp.Sum(r => r.QTY));

        }
        /*
         * 组织 GetRecord 相关方法的条件
         */
        private ISchema<V_RECORD_LIST> GetRecordSchema(
            int[] area,
            int[] category,
            string code,
            string name,
            string batch,
            string[] transaction,
            string barcode,
            string cell,
            string original,
            string destination,
            DateTime? beginFrom,
            DateTime? beginTo,
            DateTime? endFrom,
            DateTime? endTo,
            string bill,
            string slTarget,
            string orderInfo)
        {
            var schema = _database.From<V_RECORD_LIST>();
            if (area != null)
            {
                schema = schema.Where(r => (area.Contains(r.START_AREA_ID) || area.Contains(r.END_AREA_ID)));
            }
            if (category != null)
            {
                schema = schema.Where(r => category.Contains(r.CLASS_ID.Value));
            }
            if (code != null)
            {
                schema = schema.Where(r => r.GOODS_CODE.Contains(code));
            }
            if (name != null)
            {
                schema = schema.Where(r => r.GOODS_NAME.Contains(name));
            }
            if (batch != null)
            {
                schema = schema.Where(r => r.GOODS_BATCH_NO.Contains(batch));
            }
            if (transaction != null)
            {

                schema = schema.Where(r => transaction.Contains(r.RECORD_TYPE));
            }
            if (barcode != null)
            {
                schema = schema.Where(r => r.STOCK_BARCODE.Contains(barcode));
            }
            if (cell != null)
            {
                schema = schema.Where(r => r.START_CELL_NAME == cell || r.END_CELL_NAME == cell);
            }
            if (original != null)
            {
                schema = schema.Where(r => r.START_CELL_NAME.Contains(original));
            }
            if (destination != null)
            {
                schema = schema.Where(r => r.END_CELL_NAME.Contains(destination));
            }
            if (beginFrom.HasValue)
            {
                schema = schema.Where(r => r.BEGIN_TIME >= beginFrom.Value);
            }
            if (beginTo.HasValue)
            {
                schema = schema.Where(r => r.BEGIN_TIME <= beginTo.Value);
            }
            if (endFrom.HasValue)
            {
                schema = schema.Where(r => r.END_TIME >= endFrom.Value);
            }
            if (endTo.HasValue)
            {
                schema = schema.Where(r => r.END_TIME <= endTo.Value);
            }
            if (bill != null)
            {
                schema = schema.Where(r => r.GOODS_BILL_NO== bill);
            }
            if (slTarget != null)
            {
                schema = schema.Where(r => r.SlTarget == slTarget);
            }
            if (orderInfo != null)
            {
                schema = schema.Where(r => r.OrderInfo == orderInfo);
            }
            return schema;
        }

        ////////////////////////////////////////////

        /*
         * 实现 Kean.Application.Query.Interfaces.IStockService.GetBatchList 方法
         */
        public async Task<IEnumerable<Item>> GetBatchList(int? material)
        {
            var schema = _database.From<T_STORAGE_LIST>()
                .Where(r=>r.GOODS_BATCH_NO != null)
                .OrderBy(r => r.GOODS_BATCH_NO, Infrastructure.Database.Order.Ascending);
            if (material.HasValue && material > 0)
            {
                schema = schema.Where(r => r.GOODS_ID == material);
            }
            var temp = (await schema.Select(r => new { r.GOODS_BATCH_NO }))
                .DistinctBy(r => new { r.GOODS_BATCH_NO });

            List<Item> list = new List<Item>();
            foreach (var t in temp)
            {
                Item item = new Item();
                item.Code = t.GOODS_BATCH_NO;
                item.Name = t.GOODS_BATCH_NO;

                list.Add(item);
            }

            return list;
        }
    }
}