sys/Hh.Mes.Service/WebService/Production/ProductionOrderService.cs

using Hh.Mes.Common.log;
using Hh.Mes.Common.Request;
using Hh.Mes.POJO.ApiEntity;
using Hh.Mes.POJO.Entity;
using Hh.Mes.POJO.EnumEntitys;
using Hh.Mes.POJO.Response;
using Hh.Mes.POJO.WebEntity.bus;
using Hh.Mes.Service.Repository;
using NPOI.POIFS.FileSystem;
using NPOI.SS.Formula.Functions;
using Org.BouncyCastle.Crypto;
using Org.BouncyCastle.Crypto.Digests;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static Microsoft.AspNetCore.Hosting.Internal.HostingApplication;
namespace Hh.Mes.Service.Material
{
    public class ProductionOrderService : RepositorySqlSugar<dynamic>
    {
        /// <summary>
        /// 获取工单列表
        /// </summary>
        public Response GetProductionOrderList(PageReq pageReq, bus_pro_plan_head entity)
        {
            var result = new Response();
            string orderBy = (pageReq == null || string.IsNullOrEmpty(pageReq.field)) ? " id desc" : $"{pageReq.field} {pageReq.order} ";
            string sqlWhere = SqlProductionOrderWhere(entity);
            var stringBuilder = new StringBuilder();
            //页码，页数
            //Exel ture 不分页
            if (!entity.Exel && pageReq != null)
            {
                stringBuilder.Append("declare @pageIndex int,@pageSize int,@offset int");
                stringBuilder.AppendLine($"  select @pageIndex={pageReq.page}, @pageSize={pageReq.limit}, @offset=(@pageIndex - 1) * @pageSize");
            }
            stringBuilder.AppendLine($@" select * from bus_pro_plan_head where {sqlWhere} order by {orderBy} ");
            //Exel ture 不分页
            if (!entity.Exel)
            {
                stringBuilder.AppendLine("  offset @offset row fetch next @pageSize row only ");
                stringBuilder.Append($@" select rowTotal= count(*) from bus_pro_plan_head where {sqlWhere}");
            }
            var parameters = new List<SugarParameter>(){
                new SugarParameter("@planCode",entity.planCode),
                new SugarParameter("@planName",entity.planName),
                new SugarParameter("@productCode",entity.productCode),
                new SugarParameter("@productName",entity.productName)
            };
            var ds = base.Context.Ado.GetDataSetAll(stringBuilder.ToString(), parameters);
            result.Result = ds.Tables[0];
            result.Count = entity.Exel ? (int)result.Result.Rows.Count : (int)ds.Tables[1].Rows[0]["rowTotal"];
            return result;
        }
        /// <summary>
        /// 获取工单列表明细
        /// </summary>
        public Response GetProductionOrderDetailList(PageReq pageReq, bus_pro_plan_head entity)
        {
            var result = new Response();
            string orderBy = (pageReq == null || string.IsNullOrEmpty(pageReq.field)) ? " id asc" : $"{pageReq.field} {pageReq.order} ";
            string sqlWhere = SqlProductionOrderDetailWhere(entity);
            var stringBuilder = new StringBuilder();
            //页码，页数
            //Exel ture 不分页
            if (!entity.Exel && pageReq != null)
            {
                stringBuilder.Append("declare @pageIndex int,@pageSize int,@offset int");
                stringBuilder.AppendLine($"  select @pageIndex={pageReq.page}, @pageSize={pageReq.limit}, @offset=(@pageIndex - 1) * @pageSize");
            }
            stringBuilder.AppendLine($@" select * from bus_pro_plan_detail where {sqlWhere} order by {orderBy} ");
            //Exel ture 不分页
            if (!entity.Exel)
            {
                stringBuilder.AppendLine("  offset @offset row fetch next @pageSize row only ");
                stringBuilder.Append($@" select rowTotal= count(*) from bus_pro_plan_detail where {sqlWhere}");
            }
            var parameters = new List<SugarParameter>(){
                new SugarParameter("@proPlanHeadKeys",entity.keys),
            };
            var ds = base.Context.Ado.GetDataSetAll(stringBuilder.ToString(), parameters);
            result.Result = ds.Tables[0];
            result.Count = entity.Exel ? (int)result.Result.Rows.Count : (int)ds.Tables[1].Rows[0]["rowTotal"];
            return result;
        }
        /// <summary>
        /// 获取工单列表明细Bom
        /// </summary>
        public Response GetProductionOrderDetailBomList(PageReq pageReq, bus_pro_plan_detail entity)
        {
            var result = new Response();
            string orderBy = (pageReq == null || string.IsNullOrEmpty(pageReq.field)) ? " id asc" : $"{pageReq.field} {pageReq.order} ";
            string sqlWhere = SqlProductionOrderDetailBomWhere();
            var stringBuilder = new StringBuilder();
            //页码，页数
            //Exel ture 不分页
            if (!entity.Exel && pageReq != null)
            {
                stringBuilder.Append("declare @pageIndex int,@pageSize int,@offset int");
                stringBuilder.AppendLine($"  select @pageIndex={pageReq.page}, @pageSize={pageReq.limit}, @offset=(@pageIndex - 1) * @pageSize");
            }
            stringBuilder.AppendLine($@" select * from bus_pro_plan_detail_bom where {sqlWhere} order by {orderBy} ");
            //Exel ture 不分页
            if (!entity.Exel)
            {
                stringBuilder.AppendLine("  offset @offset row fetch next @pageSize row only ");
                stringBuilder.Append($@" select rowTotal= count(*) from bus_pro_plan_detail_bom where {sqlWhere}");
            }
            var parameters = new List<SugarParameter>(){
                new SugarParameter("@proPlanDetailKeys",entity.bodyKeys),
            };
            var ds = base.Context.Ado.GetDataSetAll(stringBuilder.ToString(), parameters);
            result.Result = ds.Tables[0];
            result.Count = entity.Exel ? (int)result.Result.Rows.Count : (int)ds.Tables[1].Rows[0]["rowTotal"];
            return result;
        }
        public string SqlProductionOrderWhere(bus_pro_plan_head model)
        {
            var stringBuilder = new StringBuilder();
            stringBuilder.Append(" 1=1 ");
            if (!string.IsNullOrEmpty(model.planCode))
            {
                stringBuilder.Append(" and  planCode = @planCode ");
            }
            if (!string.IsNullOrEmpty(model.planName))
            {
                stringBuilder.Append(" and  planName like '%'+@planName+'%' ");
            }
            if (!string.IsNullOrEmpty(model.productCode))
            {
                stringBuilder.Append(" and  productCode = @productCode ");
            }
            if (!string.IsNullOrEmpty(model.productName))
            {
                stringBuilder.Append(" and  productName like '%'+@productName+'%' ");
            }
            //下拉弹出搜索框
            //if (!string.IsNullOrEmpty(model.codeOrName))
            //{
            //    stringBuilder.Append($" and  (t1.materialCode like '%'+@codeOrName+'%' or  t1.materialName like '%'+@codeOrName+'%' )   ");
            //}
            return stringBuilder.ToString();
        }
        public string SqlProductionOrderDetailWhere(bus_pro_plan_head model)
        {
            var stringBuilder = new StringBuilder();
            stringBuilder.Append(" 1=1 ");
            stringBuilder.Append(" and  proPlanHeadKeys = @proPlanHeadKeys ");
            return stringBuilder.ToString();
        }
        public string SqlProductionOrderDetailBomWhere()
        {
            var stringBuilder = new StringBuilder();
            stringBuilder.Append(" 1=1 ");
            stringBuilder.Append(" and  proPlanDetailKeys = @proPlanDetailKeys ");
            return stringBuilder.ToString();
        }
        /// <summary>
        /// 新增工单
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public dynamic Ins(bus_pro_plan_head entity)
        {
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var response = new Response();
                var isExistCount = Context.Queryable<bus_pro_plan_head>().Where(x => x.planCode == entity.planCode).Count();
                if (isExistCount > 0)
                {
                    response.ResponseErr("已存在工单为【"+ entity.planCode + "】的记录！请勿重复！");
                    return response;
                }
                if (entity.planEndTime < entity.planStartTime)
                {
                    response.ResponseErr("计划结束时间不能小于计划开始时间！");
                    return response;
                }
                entity.keys = Guid.NewGuid();
                entity.createBy = sysWebUser?.Account;
                entity.createTime = DateTime.Now;
                entity.status = 10;
                Context.Insertable(entity).ExecuteCommand();
                for (int index = 0; index < entity.quantity; index++)
                {
                    var detail = new bus_pro_plan_detail();
                    detail.bodyKeys = Guid.NewGuid();
                    detail.proPlanHeadKeys = entity.keys;
                    detail.workOrderCode = DateTime.Now.ToString("yyyyMMddHHmmss") + getNumber((index + 1).ToString());
                    detail.workOrderName = entity.planName + getNumber((index + 1).ToString());
                    detail.lineCode = entity.lineCode;
                    detail.productCode = entity.productCode;
                    detail.productName = entity.productName;
                    detail.createBy = sysWebUser?.Account;
                    detail.createTime = DateTime.Now;
                    var productCodeList = Context.Queryable<base_product_header>().Where(x => x.productCode == entity.productCode).Select(x => x.keys).ToList();
                    var materialCodeList = Context.Queryable<base_product_detail>().Where(x => productCodeList.Contains(x.headKeys)).Select(x => x.materialCode).ToList();
                    var materialList = Context.Queryable<base_material_rate>().Where(x => materialCodeList.Contains(x.materialCode)).ToList();
                    int count = 0;
                    int i = 1;
                    foreach (var material in materialList)
                    {
                        count += material.quantityRate.Value;
                        var bom = new bus_pro_plan_detail_bom();
                        bom.bodyKeys = Guid.NewGuid();
                        bom.proPlanHeadKeys = entity.keys;
                        bom.proPlanDetailKeys = detail.bodyKeys;
                        bom.materialCode = material.materialCode;
                        bom.materialName = material.materialName;
                        bom.oprSequenceCode = material.oprSequenceCode;
                        bom.detailSN = DateTime.Now.ToString("yyyyMMddHHmmss") + getNumber((i++).ToString());
                        bom.spec = material.specifications;
                        bom.unit = material.unitCode;
                        bom.productCode = entity.productCode;
                        bom.quantity = material.quantityRate.Value;
                        bom.createBy = sysWebUser?.Account;
                        bom.createTime = DateTime.Now;
                        Context.Insertable(bom).ExecuteCommand();
                        var rel = new bus_pro_plan_detail_bom_rel();
                        rel.proPlanHeadKeys = entity.keys;
                        rel.proPlanDetailBodyKeys = detail.bodyKeys;
                        rel.proPlanDetailBomBodyKeys = bom.bodyKeys;
                        rel.createBy = sysWebUser?.Account;
                        rel.createTime = DateTime.Now;
                        Context.Insertable(rel).ExecuteCommand();
                    }
                    detail.quantity = count;
                    Context.Insertable(detail).ExecuteCommand();
                }
                return response;
            });
        }
        private string getNumber(string index)
        {
            while(index.Length < 5)
            {
                index = "0" + index;
            }
            return index;
        }
        /// <summary>
        /// 更新工单
        /// </summary>
        public dynamic Upd(bus_pro_plan_head entity)
        {
            var response = new Response();
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var isExistCount = Context.Queryable<bus_pro_plan_head>().Where(x => x.planCode == entity.planCode && x.id != entity.id).Count();
                if (isExistCount > 0)
                {
                    response.ResponseErr("已存在工单为【" + entity.planCode + "】的记录！请勿重复！");
                    return response;
                }
                if (entity.planEndTime < entity.planStartTime)
                {
                    response.ResponseErr("计划结束时间不能小于计划开始时间！");
                    return response;
                }
                entity.updateBy = sysWebUser?.Account;
                entity.updateTime = DateTime.Now;
                entity.status = 10;
                response.Status = Context.Updateable(entity).Where(u => u.id == entity.id).ExecuteCommand() > 0;
                if (!response.Status) response.Message = "更新失败";
                if (response.Status)
                {
                    var countResult = Context.Queryable<bus_pro_plan_detail>().Where(x => x.proPlanHeadKeys == entity.keys).Count();
                    if (countResult != entity.quantity)
                    {
                        Context.Deleteable<bus_pro_plan_detail>().Where(x => x.proPlanHeadKeys == entity.keys).ExecuteCommand();
                        Context.Deleteable<bus_pro_plan_detail_bom>().Where(x => x.proPlanHeadKeys == entity.keys).ExecuteCommand();
                        Context.Deleteable<bus_pro_plan_detail_bom_rel>().Where(x => x.proPlanHeadKeys == entity.keys).ExecuteCommand();
                        for (int index = 0; index < entity.quantity; index++)
                        {
                            var detail = new bus_pro_plan_detail();
                            detail.bodyKeys = Guid.NewGuid();
                            detail.proPlanHeadKeys = entity.keys;
                            detail.workOrderCode = DateTime.Now.ToString("yyyyMMddHHmmss") + getNumber((index + 1).ToString());
                            detail.workOrderName = entity.planName + getNumber((index + 1).ToString());
                            detail.lineCode = entity.lineCode;
                            detail.productCode = entity.productCode;
                            detail.productName = entity.productName;
                            detail.createBy = sysWebUser?.Account;
                            detail.createTime = DateTime.Now;
                            var productCodeList = Context.Queryable<base_product_header>().Where(x => x.productCode == entity.productCode).Select(x => x.keys).ToList();
                            var materialCodeList = Context.Queryable<base_product_detail>().Where(x => productCodeList.Contains(x.headKeys)).Select(x => x.materialCode).ToList();
                            var materialList = Context.Queryable<base_material_rate>().Where(x => materialCodeList.Contains(x.materialCode)).ToList();
                            int count = 0;
                            int i = 1;
                            foreach (var material in materialList)
                            {
                                count += material.quantityRate.Value;
                                var bom = new bus_pro_plan_detail_bom();
                                bom.bodyKeys = Guid.NewGuid();
                                bom.proPlanHeadKeys = entity.keys;
                                bom.proPlanDetailKeys = detail.bodyKeys;
                                bom.materialCode = material.materialCode;
                                bom.materialName = material.materialName;
                                bom.oprSequenceCode = material.oprSequenceCode;
                                bom.detailSN = DateTime.Now.ToString("yyyyMMddHHmmss") + getNumber((i++).ToString());
                                bom.spec = material.specifications;
                                bom.unit = material.unitCode;
                                bom.productCode = entity.productCode;
                                bom.quantity = material.quantityRate.Value;
                                bom.createBy = sysWebUser?.Account;
                                bom.createTime = DateTime.Now;
                                Context.Insertable(bom).ExecuteCommand();
                                var rel = new bus_pro_plan_detail_bom_rel();
                                rel.proPlanHeadKeys = entity.keys;
                                rel.proPlanDetailBodyKeys = detail.bodyKeys;
                                rel.proPlanDetailBomBodyKeys = bom.bodyKeys;
                                rel.createBy = sysWebUser?.Account;
                                rel.createTime = DateTime.Now;
                                Context.Insertable(rel).ExecuteCommand();
                            }
                            detail.quantity = count;
                            Context.Insertable(detail).ExecuteCommand();
                        }
                    }
                }
                return response;
            });
        }
        /// <summary>
        /// 更新工单
        /// </summary>
        public dynamic UpdDetail(bus_pro_plan_detail entity)
        {
            var response = new Response();
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                entity.updateBy = sysWebUser?.Account;
                entity.updateTime = DateTime.Now;
                response.Status = Context.Updateable(entity).Where(u => u.id == entity.id).ExecuteCommand() > 0;
                if (!response.Status) response.Message = "更新失败";
                return response;
            });
        }
        /// <summary>
        /// 更新工单
        /// </summary>
        public dynamic UpdDetailBom(bus_pro_plan_detail_bom entity)
        {
            var response = new Response();
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                entity.updateBy = sysWebUser?.Account;
                entity.updateTime = DateTime.Now;
                response.Status = Context.Updateable(entity).Where(u => u.id == entity.id).ExecuteCommand() > 0;
                if (!response.Status) response.Message = "更新失败";
                return response;
            });
        }
        /// <summary>
        /// 删除工单
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public dynamic DelByIds(int[] ids)
        {
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var response = new Response();
                var dataList = Context.Queryable<bus_pro_plan_head>().Where(x => ids.Contains(x.id)).ToList();
                int isDelete = 0;
                bool isSuccess = true;
                for (int index = 0; index < dataList.Count; index++)
                {
                    if (dataList[index].status == 10)
                    {
                        isDelete++;
                        Context.Deleteable<bus_pro_plan_head>().Where(x => x.keys == dataList[index].keys).ExecuteCommand();
                        Context.Deleteable<bus_pro_plan_detail>().Where(x => x.proPlanHeadKeys == dataList[index].keys).ExecuteCommand();
                        Context.Deleteable<bus_pro_plan_detail_bom>().Where(x => x.proPlanHeadKeys == dataList[index].keys).ExecuteCommand();
                        Context.Deleteable<bus_pro_plan_detail_bom_rel>().Where(x => x.proPlanHeadKeys == dataList[index].keys).ExecuteCommand();
                    } 
                    else
                    {
                        isSuccess = false;
                    }
                }
                if (!isSuccess)
                {
                    response.ResponseErr("已确认的无法删除，已删除初始的【"+isDelete+"】条");
                }
                return response;
            });
        }
        /// <summary>
        /// 完成工单
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public dynamic Complete(bus_pro_plan_head entity)
        {
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var response = new Response();
                var dataObj = Context.Queryable<bus_pro_plan_head>().Where(x => x.planCode == entity.planCode).First();
                if (dataObj == null)
                {
                    response.ResponseErr("查询不到计划工单！无法完成工单编制！");
                    return response;
                }
                dataObj.status = 20;
                response.Status = Context.Updateable(dataObj).ExecuteCommand() > 0;
                if (!response.Status) response.Message = "工单编制失败";
                if (response.Status)
                {
                    //找工序为1的
                    var oprSequenceCode = "";
                    var oprSequenceName = "";
                    var processDetailList = Context.Queryable<base_process_route_detail>().Where(x => x.headkeys == dataObj.processRouteKeys).OrderBy(t => t.oprSequence).ToList();
                    if (processDetailList.Count > 0)
                    {
                        oprSequenceCode = processDetailList[0].oprSequenceCode;
                        oprSequenceName = processDetailList[0].oprSequenceName;
                    }
                    //循环工单明细 保存工单
                    var planDetailList = Context.Queryable<bus_pro_plan_detail>().Where(x => x.proPlanHeadKeys == dataObj.keys).OrderBy(x => x.workOrderCode, OrderByType.Asc).ToList();
                    foreach (var planDetail in planDetailList)
                    {
                        var head = new bus_workOrder_head();
                        head.keys = Guid.NewGuid();
                        head.processHeadKeys = dataObj.processRouteKeys;
                        head.productHeaderCode = dataObj.productCode;
                        head.lineCode = dataObj.lineCode;
                        head.nowOprSequenceCode = oprSequenceCode;
                        head.workOrderCode = dataObj.planCode;
                        head.planCode = planDetail.workOrderCode; 
                        head.state = (int)EnumOrderHeadStatus.初始化;
                        head.planStartTime = dataObj.planStartTime;
                        head.planEndTime = dataObj.planEndTime;
                        head.isDelete = (int)EnumDeleteOrAdd.新增或更新;
                        head.createBy = sysWebUser?.Account;
                        head.createTime = DateTime.Now;
                        Context.Insertable(head).ExecuteCommand();
                        //保存工单明细
                        foreach (var processDetail in processDetailList)
                        {                           
                            var i = 0;
                            var bomList = Context.Queryable<bus_pro_plan_detail_bom>().Where(x => x.proPlanDetailKeys == planDetail.bodyKeys && x.oprSequenceCode == processDetail.oprSequenceCode).ToList();
                            foreach (var bomObject in bomList)
                            {
                                for (int index = 0; index < bomObject.quantity; index++)
                                {
                                    var list = Context.Queryable<base_work_center_station>().Where(x => x.workCenterCode.Equals(processDetail.workCenterCode)).ToList();
                                    var detail = new bus_workOrder_detail();
                                    detail.bodyKeys = Guid.NewGuid();
                                    detail.headKeys = head.keys;
                                    detail.serialNumber = processDetail.oprSequence;
                                    detail.lineCode = head.lineCode;
                                    detail.oprSequenceCode = processDetail.oprSequenceCode;
                                    detail.oprSequenceName = processDetail.oprSequenceName;
                                    detail.state = (int)EnumOrderBodyStatus.初始化;
                                    detail.isRework = false;
                                    detail.planStartTime = dataObj.planStartTime;
                                    detail.planEndTime = dataObj.planEndTime;
                                    detail.productHeaderCode = dataObj.productCode;
                                    detail.isDelete = (int)EnumDeleteOrAdd.新增或更新;
                                    detail.qty = 1;
                                    detail.workOrderCode = head.workOrderCode;
                                    if (list.Count == 1)
                                    {
                                        detail.stationCode = list[0].workStationCode;
                                    }
                                    detail.workReportStatus = (int)EnumWorkReportStatus.未报工;
                                    detail.workCenterCode = processDetail.workCenterCode;
                                    detail.createBy = sysWebUser?.Account;
                                    detail.createTime = DateTime.Now;
                                    detail.materialCode = bomObject.materialCode;
                                    detail.materialName = bomObject.materialName;
                                    detail.whCode = bomObject.unit;
                                    detail.serialNumberName = processDetail.oprSequence.ToString() + "-" + (++i);
                                    Context.Insertable(detail).ExecuteCommand();
                                }
                            }
                        }
                    }
                }
                return response;
            });
        }
    }
}