using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; using FineUICore; using Microsoft.AspNetCore.Authorization; using Microsoft.AspNetCore.Http; using Microsoft.AspNetCore.Mvc; using Microsoft.EntityFrameworkCore; using MESModel; using Newtonsoft.Json.Linq; using MES.Common; using MES.Controllers; using System.IO; using OfficeOpenXml; using OfficeOpenXml.Drawing; using OfficeOpenXml.Style; using System.Drawing; using NPOI.XSSF.UserModel; using NPOI.HSSF.UserModel; using System.Data; using System.Text; namespace MES.Areas.Plan.Controllers { /// <summary> ///创建人：许加龙 ///日 期：2018/10/15 ///描 述：计划处理打印 /// </summary> [Authorize] [Area("Plan")] public class PrintPlanOrderController : BaseController { #region 服务端事件 /// <summary> /// 页面展示 /// </summary> /// <returns></returns> [CheckPower(ISPower = false)] public IActionResult Index(int PlanOrderID) { Grid grid1 = new Grid(); var gchelper = new GridConfigHelp(db, Url); grid1.Title = "计划处理打印"; grid1.ID = "Grid1"; gchelper.SetGridColumns(UserInfo, RouteData, grid1.ID, new MES_Bus_Plan_PrintModel(), true);//管理员配置表 grid1.SortField = "Order";//默认排序 grid1.DataIDField = "PrintModelID";//主键 grid1.PageSize = 1000;//每页显示数量 gchelper.ConfigGridColumn(grid1, RouteData, PositionInfo);//配置Grid表字段 gchelper.SetGridAttribute(grid1, Url, RouteData, true);//配置Grid表字段为可编辑 gchelper.DeleteBtn(grid1, "btnSave", "fileImport", "btnNew"); var PlanOrd = db.MES_Bus_Plan_Orders.Find(PlanOrderID); var productModel = db.Mes_Bus_Base_ProductModel.FirstOrDefault(p => p.ProductModelName == PlanOrd.Pro_model && p.FactoryID == PlanOrd.FactoryID); if (productModel.PrintModelHeadID == null) { productModel.PrintModelHeadID = 0; } #region 控件替换字段 var ModelType = (RenderField)grid1.Columns.FirstOrDefault(p => p.ID == "ModelType"); if (ModelType != null) { var ddlModelType = new DropDownList() { ID = "ddlModelType", Required = false, ForceSelection = false }; ddlModelType.Items.Add(new ListItem("生产进度计划单", "0")); ddlModelType.Items.Add(new ListItem("领料单", "1")); ddlModelType.Items.Add(new ListItem("子领料单", "2")); ddlModelType.Items.Add(new ListItem("装配流水线清单", "3")); ddlModelType.Items.Add(new ListItem("附件/铜排", "4")); ddlModelType.Items.Add(new ListItem("单台清单", "5")); ddlModelType.Items.Add(new ListItem("特殊要求", "6")); ddlModelType.Items.Add(new ListItem("预装清单", "7")); ddlModelType.EnableEdit = true; ModelType.Editor.Clear(); ModelType.Editor.Add(ddlModelType); ddlModelType.Readonly = true; ModelType.RendererFunction = "renderModelType"; } #endregion var Grid1data = GetData(productModel.PrintModelHeadID.Value, gchelper.GetQuery(grid1));//获取数据集 int RecordCount = Grid1data.Count(); grid1.DataSource = Paging(Grid1data, 0, grid1.PageSize, RecordCount, grid1.SortField, grid1.SortDirection); grid1.RecordCount = RecordCount; grid1.DataBind(); //grid1.Toolbars.First().Items.Add(new Button() { ID = "BtnPrintSingleList", Text = "单台清单", OnClick = new Event("click", Url.Action("BtnPrintSingleList_Click"), new Parameter("PlanOrderID", PlanOrderID.ToString())) }); grid1.Toolbars.First().Items.Add(new Button() { ID = "BtnPrint", Text = "打印", OnClick = new Event("click", Url.Action("BtnPrint_Click"), new Parameter("PlanOrderID", PlanOrderID.ToString()), new Parameter("Grid1_Data", "F.toJSON(F.ui.Grid1.getMergedData())"), new Parameter("SelectRows", "F.ui.Grid1.getSelectedRows()")) }); ViewBag.Grid = new ControlBase[] { grid1, new HiddenField() { ID = "PrintModelHeadID", Text = productModel.PrintModelHeadID.ToString() } }; return View(); } /// <summary> /// 查询条件变更事件 /// </summary> /// <param name="Grid1_fields">字段集合</param> /// <param name="Grid1_filteredData">过滤条件JSON</param> /// <returns></returns> [HttpPost] [ValidateAntiForgeryToken] [CheckPower(ISPower = false)] public IActionResult Grid1_Changed(int PrintModelHeadID, string Grid1_Query, JArray Grid1_fields, JArray Grid1_filteredData, string Grid1_sortField, string Grid1_sortDirection, int Grid1_pageIndex, int Grid1_PageSize, bool IsChanged = false) { var Grid1data = GetData(PrintModelHeadID, Grid1_Query, Grid1_filteredData);//获取数据集 var grid1 = UIHelper.Grid("Grid1"); if (IsChanged) { Grid1_pageIndex = 0; grid1.PageIndex(Grid1_pageIndex); } int RecordCount = Grid1data.Count(); grid1.RecordCount(RecordCount);//总行数 grid1.DataSource(Paging(Grid1data, Grid1_pageIndex, Grid1_PageSize, RecordCount, Grid1_sortField, Grid1_sortDirection), Grid1_fields);//分页 return UIHelper.Result(); } /// <summary> /// 导出数据事件 /// </summary> /// <param name="Grid1_filteredData">过滤条件JSON</param> /// <param name="columns">字段集合</param> /// <returns></returns> [CheckPower(ISPower = false, Name = "计划处理打印_导出")] public IActionResult Grid1_Export(int PrintModelHeadID, string Grid1_Query, JArray Grid1_filteredData, JArray columns, JArray Grid1_title, string title) { var Grid1data = GetData(PrintModelHeadID, Grid1_Query, Grid1_filteredData).ToList(); var data = ListHelp.ListToDataTable<MES_Bus_Plan_PrintModel>(Grid1data); ExcelHelper.ExportExcelByGrid(data, columns, title); return UIHelper.Result(); } /// <summary> /// 保存事件 /// </summary> /// <param name="Grid1_fields">字段集合</param> /// <param name="Grid1_modifiedData">已修改的数据JSON</param> /// <param name="Columns">字段集合</param> /// <returns></returns> [CheckPower(ISPower = false, Name = "计划处理打印_保存")] [HttpPost] [ValidateAntiForgeryToken] public IActionResult Grid1_Save(int PrintModelHeadID, string Grid1_Query, JArray Grid1_fields, JArray Grid1_modifiedData, JArray Columns, JArray Grid1_filteredData, string Grid1_sortField, string Grid1_sortDirection, int Grid1_pageIndex, int Grid1_PageSize) { List<MES_Bus_Plan_PrintModel> models = UpdateModel<MES_Bus_Plan_PrintModel>(Grid1_modifiedData, true); foreach (var model in models) { model.PrintModelHeadID = PrintModelHeadID; } db.SaveChanges(); var Grid1data = GetData(PrintModelHeadID, Grid1_Query, Grid1_filteredData); UIHelper.Grid("Grid1").DataSource(Paging(Grid1data, Grid1_pageIndex, Grid1_PageSize, Grid1data.Count(), Grid1_sortField, Grid1_sortDirection), Grid1_fields); UIHelper.Grid("Grid1").RecordCount(Grid1data.Count()); Alert.Show("保存成功,并已重新绑定数据", MessageBoxIcon.Success); return UIHelper.Result(); } /// <summary> /// 导入数据事件 /// </summary> /// <param name="fileImport">导入文件</param> /// <param name="Columns">字段集合</param> /// <returns></returns> [CheckPower(ISPower = false, Name = "计划处理打印_导入")] [HttpPost] [ValidateAntiForgeryToken] public IActionResult Grid1_Import(int PrintModelHeadID, string Grid1_Query, IFormFile fileImport, JArray Columns, JArray Grid1_fields, JArray Grid1_filteredData, string Grid1_sortField, string Grid1_sortDirection, int Grid1_pageIndex, int Grid1_PageSize, int OrderID) { Dictionary<string, object> dics = new Dictionary<string, object>(); dics.Add("PrintModelHeadID", PrintModelHeadID); var models = ExcelHelper.ImportExcelToGrid<MES_Bus_Plan_PrintModel>(fileImport, Columns, dics); var Grid1data = GetData(PrintModelHeadID, Grid1_Query, Grid1_filteredData); UIHelper.Grid("Grid1").DataSource(Paging(Grid1data, Grid1_pageIndex, Grid1_PageSize, Grid1data.Count(), Grid1_sortField, Grid1_sortDirection), Grid1_fields); UIHelper.Grid("Grid1").RecordCount(Grid1data.Count()); UIHelper.FileUpload("fileImport").Reset(); Alert.Show("导入成功,并已重新绑定数据", MessageBoxIcon.Success); return UIHelper.Result(); } /// <summary> /// 绑定列事件 /// </summary> /// <param name="Columns"></param> /// <returns></returns> [CheckPower(ISPower = false, Name = "计划处理打印_绑定列")] [HttpPost] [ValidateAntiForgeryToken] public IActionResult Grid1_Bind(JArray Columns) { new GridConfigHelp(db, Url).SetColumnWidth(Columns, "Grid1", RouteData); Alert.Show("绑定列成功！", MessageBoxIcon.Success); return UIHelper.Result(); } [HttpPost] [ValidateAntiForgeryToken] [CheckPower(ISPower = false, Name = "计划处理打印_打印")] public IActionResult BtnPrint_Click(int PlanOrderID, JArray Grid1_Data, int[] SelectRows) { var somdb = new Model.SomContext(); var ord = db.MES_Bus_Plan_Orders.Find(PlanOrderID);//计划订单表 var heads = db.MES_Bus_Plan_PartHead.Include(p => p.Bodies).Where(p => p.PlanOrderID == PlanOrderID).AsNoTracking().ToList();//计划订单零件 ListHelp.NullToEmpty(heads); new MesHelp(db).CountPart(heads, false);//重新计算备注转序列 var products = db.MES_Bus_Plan_Product.Where(p => p.PlanOrderID == PlanOrderID).AsNoTracking().ToList();//计划订单柜体 var SomOrd = somdb.ViewOrders.First(p => p.OrderID == ord.OrderID);//获取OMS订单数据 //var lists = GetOrderCustomerDemandToList(somdb, SomOrd.rowguid); var ContainerNOS = products.Select(p => p.ContainerNO).ToList(); //Modify By:Jundi Date:2019-06-06 Desc:特殊要求数据源从静态表取数 var lists = GetCustomerDemandToList(ord.OrderIDS); #region 数量校验 var Error = ""; foreach (var head in heads) { if (head.Quantity != head.Bodies.Where(p => ContainerNOS.Contains(p.ContainerNO)).Sum(p => p.Quantity)) { if (Error != "") { Error += ","; } Error += head.CodeName; } } if (Error != "") { Alert.Show(Error + "这些图号数量异常,请重新修改!"); return UIHelper.Result(); } #endregion #region 获取特殊要求工位 /* var data1 = (from ocd in lists.Where(p => p.CusDemandID == 0) join cds in somdb.TCustomerDemandOrderStation on ocd.ID equals cds.OrderCustDemandID select new Model.TCustomerDemandOrderStation { AssemblingStation = cds.AssemblingStation, BusinessCode = cds.BusinessCode, Checker = cds.Checker, REMARK = cds.REMARK, OrderCustDemandID = ocd.ID, CusDemandName = ocd.CusDemandName, CusDemandType = ocd.CusDemandType, UsedRemark = ocd.UsedRemark, UsedStatus = ocd.UsedStatus, UsedType = ocd.UsedType, }).ToList();//开启查询项目特殊要求工位 var data2 = (from ocd in lists.Where(p => p.CusDemandID != 0) join cds in somdb.TCustomerDemandStation on ocd.CusDemandID equals cds.CusDemandID select new Model.TCustomerDemandOrderStation { AssemblingStation = cds.AssemblingStation, BusinessCode = cds.BusinessCode, Checker = cds.Checker, REMARK = cds.REMARK, OrderCustDemandID = ocd.ID, CusDemandName = ocd.CusDemandName, CusDemandType = ocd.CusDemandType, UsedRemark = ocd.UsedRemark, UsedStatus = ocd.UsedStatus, UsedType = ocd.UsedType, }).ToList();//开启查询标准特殊要求工位 */ var data1 = (from ocd in lists.Where(p => p.CusDemandID == 0)//开启查询项目特殊要求工位 select new Model.TCustomerDemandOrderStation { AssemblingStation = ocd.PartSID, OrderCustDemandID = ocd.ID, CusDemandName = ocd.CusDemandName, CusDemandType = ocd.CusDemandType, UsedRemark = ocd.UsedRemark, UsedStatus = ocd.UsedStatus, UsedType = ocd.UsedType, }).ToList(); var data2 = (from ocd in lists.Where(p => p.CusDemandID != 0)//开启查询标准特殊要求工位 select new Model.TCustomerDemandOrderStation { AssemblingStation = ocd.PartSID, OrderCustDemandID = ocd.ID, CusDemandName = ocd.CusDemandName, CusDemandType = ocd.CusDemandType, UsedRemark = ocd.UsedRemark, UsedStatus = ocd.UsedStatus, UsedType = ocd.UsedType, }).ToList(); var cdos = new List<Model.TCustomerDemandOrderStation>(); cdos.AddRange(data1); cdos.AddRange(data2); #endregion var ProInventorys = new List<Model.TProInventory>(); var OrderIDList = ord.OrderIDS.Split(',').ToList().Distinct().ToList(); var pros = somdb.TProInventory.Where(p => p.OrderID != null && p.IsCabinet == 1 && OrderIDList.Contains(p.OrderID.ToString())).ToList(); var ppp = from pro in pros join product in products on new { pro.OrderID, pro.Cabinet_no } equals new { product.OrderID, Cabinet_no = product.ContainerNumber.ToString() } select new Model.TProInventory { U9Pro_model= pro.U9Pro_model , Spec= pro.Spec, OrderID= pro.OrderID }; if (OrderIDList.Count>1) { var ords = somdb.TOrders.Where(p => OrderIDList.Contains(p.rowguid.ToString())).ToList(); ppp= from p in ppp join o in ords on p.OrderID equals o.rowguid select new Model.TProInventory { U9Pro_model = p.U9Pro_model+"_"+o.Contract_i_no, Spec = p.Spec }; } ProInventorys = ppp.GroupBy(p => new { p.U9Pro_model, p.Spec }).Select(g => new Model.TProInventory { U9Pro_model = g.Key.U9Pro_model, Spec = g.Key.Spec, OrderID = g.Count() }).ToList(); //获取OMS柜体台数 List<MES_Bus_Plan_PartBody> bodys = new List<MES_Bus_Plan_PartBody>(); foreach (var head in heads) { foreach (var bod in head.Bodies) { if (head.ISMoveBatche) { bod.BatchNo = 1; } else { var product = products.First(p => p.ContainerNO == bod.ContainerNO); bod.BatchNo = product.BatchNo.Value;//如果没有批次号 那么等于柜子的批次号 } bodys.Add(bod); } }//获取批次零件 Dictionary<string, int> dics = new Dictionary<string, int>(); string File = "~/FileTemp/计划处理模板.xlsm"; string FullPathFileName = PageContext.MapPath(File); var printModels = JsonHelper.GridJsonToList<MES_Bus_Plan_PrintModel>(Grid1_Data).OrderBy(p => p.Order).ToList();//获取当前页面的打印数据 if (SelectRows.Count() > 0) { printModels = printModels.Where(p => SelectRows.Contains(p.PrintModelID)).ToList(); } using (FileStream stream = new FileStream(FullPathFileName, FileMode.Open, FileAccess.Read)) { using (ExcelPackage package = new ExcelPackage(stream)) { foreach (var printModel in printModels) { if (printModel.ModelType == 0) {//导出生产进度计划表 for (int i = 0; i < printModel.Copies; i++) { ProductionSchedule(ord, package, dics, SomOrd, ProInventorys, printModel); } } else if (printModel.ModelType == 1) {//领料单 for (int i = 0; i < printModel.Copies; i++) { Picking(heads, ord, package, dics, SomOrd, cdos, printModel); } } else if (printModel.ModelType == 2) {//子领料单 for (int i = 0; i < printModel.Copies; i++) { ChildPicking(heads, bodys, products, ord, package, dics, printModel); } } else if (printModel.ModelType == 3) {//装配流水线清单 for (int i = 0; i < printModel.Copies; i++) { Assemble(heads, bodys, products, ord, package, dics, cdos, printModel); } } else if (printModel.ModelType == 4) {//铜排/附件 for (int i = 0; i < printModel.Copies; i++) { CopperOrAppendix(heads, ord, package, dics, cdos, printModel); } } else if (printModel.ModelType == 5) {//单台清单 for (int i = 0; i < printModel.Copies; i++) { SingleList(cdos, heads, bodys, ord, printModel); // PartGroupBOM( heads, bodys, ord, printModel); } } else if (printModel.ModelType == 6) {//特殊要求 for (int i = 0; i < printModel.Copies; i++) { DemandOrderStation(cdos, ord, package, dics, printModel); } } else if (printModel.ModelType == 7) {//预装清单 for (int i = 0; i < printModel.Copies; i++) { // PartGroupBOM( heads, bodys, ord, printModel); } } } package.Workbook.Properties.Title = "计划处理导出";//设置excel的标题 package.Workbook.Properties.Author = "许加龙";//作者 package.Workbook.Properties.Company = "万控智造(浙江)电气有限公司";//公司 package.Workbook.Worksheets.Delete("生产进度计划单模板"); package.Workbook.Worksheets.Delete("铜排模板"); package.Workbook.Worksheets.Delete("特殊要求模板"); package.Workbook.Worksheets[0].Select(); if (package.Workbook.Worksheets.Count > 1) { var data = package.GetAsByteArray(); var FileName = DateTime.Now.ToString("yyyyMMddHHmmssff") + ord.Contract_i_no + ord.CustomerName + Math.Round(ord.Plat_number.Value, 0) + "台" + UserInfo.UserName + ".xlsm"; DirFileHelp.CreateFile(MesConfig.UploadPath + FileName, data); DirFileHelp.DownFile(MesConfig.UploadPath + FileName); } } } return UIHelper.Result(); } #endregion #region C#方法 /// <summary> /// 获取数据源 /// </summary> /// <param name="Grid1_filteredData">过滤条件JSON</param> /// <returns></returns> private IQueryable<MES_Bus_Plan_PrintModel> GetData(int PrintModelHeadID, string Grid1_Query, JArray Grid1_filteredData = null) { LambdaHelper<MES_Bus_Plan_PrintModel> lamada = new LambdaHelper<MES_Bus_Plan_PrintModel>(); GridFilterHelp.SetFilter(lamada, Grid1_filteredData, Grid1_Query, UserInfo);//将过滤条件转换为兰姆达表达式 lamada.And(p => p.PrintModelHeadID == PrintModelHeadID); return db.MES_Bus_Plan_PrintModel.Where(lamada.andwhere); } #region 导出计划处理清单 /// <summary> /// 导出领料清单 /// </summary> /// <param name="heads">表头</param> /// <param name="ord">计划订单</param> /// <param name="package">EXCEL</param> /// <param name="dics"></param> /// <param name="ProductClan">产品族</param> private void Picking(List<MES_Bus_Plan_PartHead> heads, MES_Bus_Plan_Orders ord, ExcelPackage package, Dictionary<string, int> dics, Som.ViewModel.ViewOrders SomOrd, List<Model.TCustomerDemandOrderStation> cdos, MES_Bus_Plan_PrintModel PrintModel) { LambdaHelper<MES_Bus_Plan_PartHead> lamada = new LambdaHelper<MES_Bus_Plan_PartHead>(); if (!string.IsNullOrEmpty(PrintModel.Condition)) { GridFilterHelp.SetFilter(lamada, JArray.Parse(PrintModel.Condition), "", UserInfo);//将过滤条件转换为兰姆达表达式 } var models = heads.AsQueryable().Where(lamada.andwhere).ToList(); if (models.Count == 0) { return; } var newsheet = PrintModel.REMARK; var NO = 1; if (dics.ContainsKey(newsheet))//判断是否存在该清单 { NO = dics[newsheet]; NO++; dics[newsheet] = NO; } else { dics.Add(newsheet, 1); } var worksheet = package.Workbook.Worksheets.Add(newsheet + GetRome(NO)); //创建sheet worksheet.Column(1).Width = 8; //产品族 worksheet.Column(2).Width = 6; //组别 worksheet.Column(3).Width = 7; //工位 worksheet.Column(4).Width = 30; //名称 worksheet.Column(5).Width = 15; //图号 worksheet.Column(6).Width = 16; //规格 worksheet.Column(7).Width = 10; //材料 worksheet.Column(8).Width = 6; //数量 worksheet.Column(9).Width = 15; //转序 worksheet.Column(10).Width = 23; //备注 worksheet.Cells["A1:J6"].Style.Font.Name = "宋体"; //字体设置 worksheet.Cells["A1:J6"].Style.Font.Bold = true; //加粗 worksheet.Cells["A1:J6"].Style.Font.Size = 14; worksheet.Cells["A5"].Style.Font.Color.SetColor(Color.White);//字体颜色：白色 worksheet.Cells["A5:C5"].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells["A5:C5"].Style.Fill.BackgroundColor.SetColor(Color.Red);//背景颜色：酒红色 worksheet.Cells["A6:J6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//字体水平居中 worksheet.Cells["A5:C5"].Merge = true; //合并单元格 worksheet.Cells["D2:I2"].Merge = true; //合并单元格 worksheet.Cells["D3:F3"].Merge = true; //合并单元格 //worksheet.Cells["E5:J5"].Merge = true; //合并单元格 worksheet.Cells["A5"].Value = newsheet;//A5 领料清单-喷塑线 worksheet.Cells["D2"].Value = "客户名称：" + ord.CustomerName;//客户名称:中骏电气 worksheet.Cells["D3"].Value = "合同编号：" + ord.Contract_i_no;//合同编号:G180930-190 worksheet.Cells["D4"].Value = "产品型号：" + ord.Pro_model;//产品型号:KYN28A-12(II) worksheet.Cells["D5"].Value = "柜体台数：" + Math.Round(ord.Plat_number.Value, 0);//台数：29 worksheet.Cells["E4"].Value = "装配日期：" + (ord.Assemble_time == null ? "" : ord.Assemble_time.Value.ToShortDateString());//装配日期：2018-01-01 worksheet.Cells["E5"].Value = ord.PlanRemark;//重点备注：//待取值 worksheet.Cells["J2"].Value = "计划员：" + UserInfo.UserName;//计划员：李媛媛 // 张爱强说 谁打印就显示谁的名字 worksheet.Cells["J3"].Value = "技术员：" + ord.DesignName;//技术员 if (ord.FactoryID == 4)//辛柏 { worksheet.Cells["J4"].Value = "编程员：" + ord.NameplateHole;//编程员 } else { worksheet.Cells["J4"].Value = "手车/抽屉：" + ord.HandcartOrDrawerNumber;//手车或抽屉数量 var OrderIDList = ord.OrderIDS.Split(',').ToList().Distinct().ToList(); var ConvCarNumber = new Model.SomContext().TOrders.Where(p => OrderIDList.Contains(p.rowguid.ToString())).Sum(q => q.ConvCarNumber); worksheet.Cells["J5"].Value = "转运车：" + ConvCarNumber;//转车数量 } worksheet.Cells["G3:I5"].Merge = true; //合并单元格 worksheet.Cells["G3"].Value = PrintModel.AllocationDepartment;//分配部门 worksheet.Cells["G3"].Style.Font.Size = 24; worksheet.Cells["A6"].Value = "产品族"; worksheet.Cells["B6"].Value = "组别"; worksheet.Cells["C6"].Value = "工位"; worksheet.Cells["D6"].Value = "名称"; worksheet.Cells["E6"].Value = "图号"; worksheet.Cells["F6"].Value = "规格"; worksheet.Cells["G6"].Value = "材料"; worksheet.Cells["H6"].Value = "数量"; worksheet.Cells["I6"].Value = "转序"; worksheet.Cells["J6"].Value = "备注"; int Row = 6; //循环写入清单 foreach (var model in models.Where(p=>p.PartName=="梁"||p.PartName.Contains("安装梁")).OrderBy(p => p.AssemblingStation).ThenBy(p => p.ProductClan).ThenBy(p => p.PartName)) { Row++; worksheet.Cells[Row, 1].Value = model.ProductClan;//产品族 worksheet.Cells[Row, 2].Value = model.AssemblingGroup;//组别 worksheet.Cells[Row, 3].Value = model.AssemblingStation;//工位 worksheet.Cells[Row, 4].Value = model.PartName;//名称 worksheet.Cells[Row, 5].Value = model.CodeName;//图号 worksheet.Cells[Row, 6].Value = model.Specification;//规格 worksheet.Cells[Row, 7].Value = model.MaterialQuality;//材料 worksheet.Cells[Row, 8].Value = model.Quantity;//数量 worksheet.Cells[Row, 9].Value = model.TransferOrder;//转序 if (string.IsNullOrEmpty(model.SkillRemark)) { if (!string.IsNullOrEmpty(model.Opening))//如果开孔编号不为空 { model.SkillRemark += "【" + model.Opening + "】/ "; } model.SkillRemark += "版本"; if (!string.IsNullOrEmpty(model.MaterialVersion))//版本号不为空 { model.SkillRemark += model.MaterialVersion; } } else { if (!string.IsNullOrEmpty(model.Opening) && !model.SkillRemark.Contains("【" + model.Opening + "】")) { model.SkillRemark = "【" + model.Opening + "】/ " + model.SkillRemark; } } worksheet.Cells[Row, 10].Value = model.SkillRemark;//备注 } //循环写入清单 foreach (var model in models.Where(p=>p.PartName != "梁" && !p.PartName.Contains("安装梁")).OrderBy(p => p.AssemblingStation).ThenBy(p => p.ProductClan).ThenBy(p => p.PartName)) { Row++; worksheet.Cells[Row, 1].Value = model.ProductClan;//产品族 worksheet.Cells[Row, 2].Value = model.AssemblingGroup;//组别 worksheet.Cells[Row, 3].Value = model.AssemblingStation;//工位 worksheet.Cells[Row, 4].Value = model.PartName;//名称 worksheet.Cells[Row, 5].Value = model.CodeName;//图号 worksheet.Cells[Row, 6].Value = model.Specification;//规格 worksheet.Cells[Row, 7].Value = model.MaterialQuality;//材料 worksheet.Cells[Row, 8].Value = model.Quantity;//数量 worksheet.Cells[Row, 9].Value = model.TransferOrder;//转序 if (string.IsNullOrEmpty(model.SkillRemark)) { if (!string.IsNullOrEmpty(model.Opening) && !model.SkillRemark.Contains("【" + model.Opening + "】"))//如果开孔编号不为空 { model.SkillRemark += "【" + model.Opening + "】/ "; } model.SkillRemark += "版本"; if (!string.IsNullOrEmpty(model.MaterialVersion))//版本号不为空 { model.SkillRemark += model.MaterialVersion; } } else { if (!string.IsNullOrEmpty(model.Opening) && !model.SkillRemark.Contains("【" + model.Opening + "】")) { model.SkillRemark = "【" + model.Opening + "】/ " + model.SkillRemark; } } worksheet.Cells[Row, 10].Value = model.SkillRemark;//备注 } if (PrintModel.REMARK.Contains("抽屉组") && cdos.Where(p => p.AssemblingStation != null && p.AssemblingStation.Contains("CT")).Count() > 0) {//写入特殊求 Row++; worksheet.Cells[Row, 1].Value = "特殊要求";//特殊要求 worksheet.Cells[Row, 2].Value = "检验员";//检验员 worksheet.Cells[Row, 3].Value = "工位";//工位 worksheet.Cells[Row, 4].Value = "特殊要求类别";//特殊要求类别 worksheet.Cells[Row, 5].Value = "特殊要求";//特殊要求 worksheet.Cells["E" + Row + ":J" + Row].Merge = true; //合并单元格 worksheet.Cells["A" + Row + ":E" + Row].Style.Font.Bold = true; //设置字体加粗 //循环写入特殊要求 foreach (var cdo in cdos.Where(p => p.AssemblingStation != null && p.AssemblingStation.Contains("CT"))) { Row++; worksheet.Cells[Row, 1].Value = "特殊要求";//特殊要求 worksheet.Cells[Row, 2].Value = cdo.Checker;//检验员 worksheet.Cells[Row, 3].Value = cdo.AssemblingStation;//工位 worksheet.Cells[Row, 4].Value = cdo.CusDemandType;//特殊要求类别 worksheet.Cells[Row, 5].Value = cdo.CusDemandName;//特殊要求 worksheet.Cells[Row, 5].Style.WrapText = true;//自动换行 worksheet.Cells["E" + Row + ":J" + Row].Merge = true; //合并单元格 } } if (PrintModel.REMARK.Contains("手车") && cdos.Where(p => p.AssemblingStation!=null&& p.AssemblingStation.Contains("手车")).Count() > 0) {//写入特殊求 Row++; worksheet.Cells[Row, 1].Value = "特殊要求";//特殊要求 worksheet.Cells[Row, 2].Value = "检验员";//检验员 worksheet.Cells[Row, 3].Value = "工位";//工位 worksheet.Cells[Row, 4].Value = "特殊要求类别";//特殊要求类别 worksheet.Cells[Row, 5].Value = "特殊要求";//特殊要求 worksheet.Cells["E" + Row + ":J" + Row].Merge = true; //合并单元格 worksheet.Cells["A" + Row + ":E" + Row].Style.Font.Bold = true; //设置字体加粗 //循环写入特殊要求 foreach (var cdo in cdos.Where(p => p.AssemblingStation != null && p.AssemblingStation.Contains("手车"))) { Row++; worksheet.Cells[Row, 1].Value = "特殊要求";//特殊要求 worksheet.Cells[Row, 2].Value = cdo.Checker;//检验员 worksheet.Cells[Row, 3].Value = cdo.AssemblingStation;//工位 worksheet.Cells[Row, 4].Value = cdo.CusDemandType;//特殊要求类别 worksheet.Cells[Row, 5].Value = cdo.CusDemandName;//特殊要求 worksheet.Cells[Row, 5].Style.WrapText = true;//自动换行 worksheet.Cells["E" + Row + ":J" + Row].Merge = true; //合并单元格 } } var Cells = worksheet.Cells[6, 1, Row, 10]; Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin; //边框样式 Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin; Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin; Cells.Style.ShrinkToFit = true;//字体自动填充 //worksheet.PrinterSettings.PrintArea.Address = new ExcelAddress(1,1,Row,10).Address; var BodyCells = worksheet.Cells[7, 1, Row, 10]; BodyCells.Style.Font.Name = "宋体"; //字体设置 BodyCells.Style.Font.Size = 12; worksheet.PrinterSettings.Scale = 75;//打印缩放 worksheet.PrinterSettings.RepeatRows = new ExcelAddress("$1:$6");//设置打印标题 worksheet.View.FreezePanes(7, 1);//冻结窗格 worksheet.PrinterSettings.LeftMargin = 0.2m;//左边距 worksheet.PrinterSettings.RightMargin = 0.1m;//右边距 worksheet.PrinterSettings.TopMargin = 0m;//上边距 worksheet.PrinterSettings.BottomMargin = 0.5m;//下边距 worksheet.HeaderFooter.OddFooter.LeftAlignedText = "编制:" + UserInfo.UserName + " " + "日期:" + Now.ToShortDateString() + " " + "领料/日期: 出库/日期: 记账/日期: " + "&P/&N"; worksheet.Row(1).Hidden = true;//隐藏第一行 string File = "~/FileTemp/万控文件LOGO.bmp"; string FullPathFileName = PageContext.MapPath(File); using (FileStream stream = new FileStream(FullPathFileName, FileMode.Open, FileAccess.Read)) { ExcelPicture picture = worksheet.Drawings.AddPicture("logo", System.Drawing.Image.FromStream(stream));//插入图片 picture.SetPosition(38, 4);//设置图片的位置 picture.SetSize(127, 36);//设置图片的大小 } } /// <summary> /// 导出生产进度计划单 /// </summary> private void ProductionSchedule(MES_Bus_Plan_Orders ord, ExcelPackage package, Dictionary<string, int> dics, Som.ViewModel.ViewOrders SomOrd, List<Model.TProInventory> models, MES_Bus_Plan_PrintModel PrintModel) { var newsheet = "生产进度计划单"; var NO = 1; if (dics.ContainsKey(newsheet))//判断是否存在该清单 { NO = dics[newsheet]; NO++; dics[newsheet] = NO; } else { dics.Add(newsheet, 1); } var worksheet = package.Workbook.Worksheets.Copy("生产进度计划单模板", newsheet + GetRome(NO)); worksheet.Cells["B2"].Value = ord.CustomerName;//客户名称 worksheet.Cells["L2"].Value = ord.Plat_colour;//面板颜色 worksheet.Cells["B3"].Value = ord.Contract_i_no;//内部合同号 worksheet.Cells["E3"].Value = Math.Round(ord.Plat_number.Value) + "台";//数量 worksheet.Cells["L3"].Value = ord.Frame_colour;//框架颜色 worksheet.Cells["B4"].Value = ord.AreaName;//办事处 worksheet.Cells["L4"].Value = UserInfo.UserName;//计划员 worksheet.Cells["B5"].Value = SomOrd.Plat_door;//柜型 worksheet.Cells["E5"].Value = ord.Pro_model;//产品型号 worksheet.Cells["L5"].Value = ord.DesignName;//技术员 if (ord.FactoryID == 4) { worksheet.Cells["C7"].Value = ord.REC_CREATE_TIME.ToString("yyyy年MM月dd日") + "前完成。";//计调车间时间 } else { worksheet.Cells["C7"].Value = ord.Assemble_time == null ? "" : ord.Assemble_time.Value.AddDays(-2).ToString("yyyy年MM月dd日") + "前完成。";//计调车间时间 } worksheet.Cells["C8"].Value = ord.Assemble_time == null ? "" : ord.Assemble_time.Value.AddDays(-1).ToString("yyyy年MM月dd日") + "前完成。";//生产车间时间 worksheet.Cells["C9"].Value = ord.Assemble_time == null ? "" : ord.Assemble_time.Value.AddDays(-1).ToString("yyyy年MM月dd日") + "前完成。";//装配车间时间 worksheet.Cells["L8"].Value = ord.AnalogCardHole;//模拟牌孔 worksheet.Cells["L9"].Value = ord.NameplateHole;//铭牌孔 worksheet.Cells["B10"].Value = SomOrd.PackType;//包装要求 worksheet.Cells["B12"].Value = ord.Blows_colour;//眉头颜色 worksheet.Cells["B15"].Value = ord.MakeType;//常规 worksheet.Cells["B16"].Value = ord.HandcartOrDrawerNumber;//手车数量或抽屉数量 var OrderIDList = ord.OrderIDS.Split(',').ToList().Distinct().ToList(); var ConvCarNumber = new Model.SomContext().TOrders.Where(p => OrderIDList.Contains(p.rowguid.ToString())).Sum(q => q.ConvCarNumber); worksheet.Cells["B17"].Value = ConvCarNumber;//转运车数量 worksheet.Cells["G11"].Value = ord.PlanRemark;//计划备注 worksheet.Cells["L1"].Value = PrintModel.AllocationDepartment;//分配部门 worksheet.Cells["B14"].Value = ord.PanelDemand;//板材要求 if (ord.FactoryID == 4)//辛柏 { worksheet.Cells["B11"].Value = ord.Cabinet_colour;// worksheet.Cells["B13"].Value = ord.AnalogCardHole;// worksheet.Cells["H3"].Value = "机柜颜色";//机柜颜色 worksheet.Cells["L3"].Value = ord.Frame_colour;//机柜颜色 } else { worksheet.Cells["B11"].Value = ord.InstrumentDoor;//仪表门 worksheet.Cells["B13"].Value = ord.CabinetTop_Color;//柜顶颜色 } int Row = 20; //循环写入清单 foreach (var model in models) { Row++; worksheet.Cells["A" + Row + ":D" + Row].Merge = true; //合并单元格 worksheet.Cells["E" + Row + ":L" + Row].Merge = true; //合并单元格 worksheet.Cells["M" + Row + ":O" + Row].Merge = true; //合并单元格 worksheet.Cells["A" + Row].Value = model.U9Pro_model;//品名 worksheet.Cells["E" + Row].Value = model.Spec;//规格 worksheet.Cells["M" + Row].Value = model.OrderID;//数量 } var Cells = worksheet.Cells[20, 1, Row, 15]; Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin; //边框样式 Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin; Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin; Cells.Style.ShrinkToFit = true;//字体自动填充 if (ord.FactoryID != 4) //辛柏不需要 { Row++; worksheet.Cells["A" + Row].Value = " 说明：";//品名 Row++; worksheet.Cells["A" + Row].Value = " 1、此单作为生产指令与技术规范同时下发。";//品名 Row++; worksheet.Cells["A" + Row].Value = " 2、生产过程中的有关事项可与项目负责人直接协商。";//品名 } worksheet.HeaderFooter.OddFooter.LeftAlignedText = "编制:" + UserInfo.UserName + " " + "日期:" + Now.ToShortDateString() + " " + "领料/日期: 出库/日期: 记账/日期: " + "&P/&N"; } /// <summary> /// 导出铜排/附件 /// </summary> private void CopperOrAppendix(List<MES_Bus_Plan_PartHead> heads, MES_Bus_Plan_Orders ord, ExcelPackage package, Dictionary<string, int> dics, List<Model.TCustomerDemandOrderStation> cdos, MES_Bus_Plan_PrintModel PrintModel) { var newsheet = PrintModel.REMARK; LambdaHelper<MES_Bus_Plan_PartHead> lamada = new LambdaHelper<MES_Bus_Plan_PartHead>(); if (!string.IsNullOrEmpty(PrintModel.Condition)) { GridFilterHelp.SetFilter(lamada, JArray.Parse(PrintModel.Condition), "", UserInfo);//将过滤条件转换为兰姆达表达式 } var models = heads.AsQueryable().Where(lamada.andwhere).ToList(); if (models.Count == 0) { return; } var NO = 1; if (dics.ContainsKey(newsheet))//判断是否存在该清单 { NO = dics[newsheet]; NO++; dics[newsheet] = NO; } else { dics.Add(newsheet, 1); } var worksheet = package.Workbook.Worksheets.Copy("铜排模板", newsheet + GetRome(NO)); worksheet.Cells["A9"].Value = "客户名称:" + ord.CustomerName;//客户名称 worksheet.Cells["C9"].Value = "数量:" + Math.Round(ord.Plat_number.Value, 2);//数量 worksheet.Cells["D9"].Value = "合同编号:" + ord.Contract_i_no;//内部合同号 worksheet.Cells["G9"].Value = "计划员:" + UserInfo.UserName;//计划员 worksheet.Cells["G3"].Value = PrintModel.AllocationDepartment;//分配部门 if (ord.Pro_name == "高压柜体") { worksheet.Cells["C10"].Formula = "TODAY()+1";// worksheet.Cells["C11"].Formula = "TODAY()+2";// } else { worksheet.Cells["C10"].Value = ord.Assemble_time.Value;// worksheet.Cells["C11"].Value = ord.Assemble_time.Value.AddDays(1);// } int Row = 15; //循环写入清单 var newmodels = models; if (ord.Pro_name == "低压柜体") { newmodels = models.OrderBy(p => p.Specification).ThenBy(p => p.PartName).ToList(); } else { newmodels = models.OrderBy(p => p.PartName).ThenBy(p => p.Specification).ToList(); } foreach (var model in newmodels.OrderBy(p => p.AssemblingStation).ThenBy(p => p.ProductClan).ThenBy(p => p.PartName)) { Row++; worksheet.Cells["A" + Row].Value = model.AssemblingGroup;//组别 worksheet.Cells["B" + Row].Value = model.PartName;//部件名称 worksheet.Cells["C" + Row].Value = model.CodeName;//图号 worksheet.Cells["D" + Row].Value = model.MaterialQuality;//材料 worksheet.Cells["E" + Row].Value = model.Quantity;//数量 worksheet.Cells["F" + Row].Value = model.Specification;//规格 worksheet.Cells["G" + Row].Value = model.REMARK+" "+model.SkillRemark;//备注 worksheet.Cells["H" + Row].Value = model.MaterialCode;//物料编码 worksheet.Cells["I" + Row].Value = model.CuPunching;//铜排 } if (cdos.Where(p => p.AssemblingStation != null && PrintModel.REMARK.Contains( p.AssemblingStation)).Count() > 0) {//写入特殊要求 Row++; worksheet.Cells[Row, 1].Value = "特殊要求";//特殊要求 worksheet.Cells[Row, 2].Value = "检验员";//检验员 worksheet.Cells[Row, 3].Value = "工位";//工位 worksheet.Cells[Row, 4].Value = "特殊要求类别";//特殊要求类别 worksheet.Cells[Row, 5].Value = "特殊要求";//特殊要求 worksheet.Cells["E" + Row + ":J" + Row].Merge = true; //合并单元格 worksheet.Cells["A" + Row + ":E" + Row].Style.Font.Bold = true; //设置字体加粗 //循环写入特殊要求 foreach (var cdo in cdos.Where(p => p.AssemblingStation != null && PrintModel.REMARK.Contains(p.AssemblingStation))) { Row++; worksheet.Cells[Row, 1].Value = "特殊要求";//特殊要求 worksheet.Cells[Row, 2].Value = cdo.Checker;//检验员 worksheet.Cells[Row, 3].Value = cdo.AssemblingStation;//工位 worksheet.Cells[Row, 4].Value = cdo.CusDemandType;//特殊要求类别 worksheet.Cells[Row, 5].Value = cdo.CusDemandName;//特殊要求 worksheet.Cells[Row, 5].Style.WrapText = true;//自动换行 worksheet.Cells["E" + Row + ":J" + Row].Merge = true; //合并单元格 } } var Cells = worksheet.Cells[16, 1, Row, 9]; Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin; //边框样式 Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin; Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin; Cells.Style.ShrinkToFit = true;//字体自动填充 Cells.Style.Font.Size = 14; worksheet.HeaderFooter.OddFooter.LeftAlignedText = "编制:" + UserInfo.UserName + " " + "日期:" + Now.ToShortDateString() + " " + "领料/日期: 出库/日期: 记账/日期: " + "&P/&N"; } /// <summary> /// 导出子清单 /// </summary> /// <param name="heads">表头</param> /// <param name="ord">计划订单</param> /// <param name="package">EXCEL</param> /// <param name="dics"></param> /// <param name="ProductClan">产品族</param> private void ChildPicking(List<MES_Bus_Plan_PartHead> heads, List<MES_Bus_Plan_PartBody> bodys, List<MES_Bus_Plan_Product> products, MES_Bus_Plan_Orders ord, ExcelPackage package, Dictionary<string, int> dics, MES_Bus_Plan_PrintModel PrintModel) { var Batchs = bodys.GroupBy(p => p.BatchNo).OrderBy(p => p.Key).Select(p => p.Key).ToList();//所有批次 foreach (var Batch in Batchs) { LambdaHelper<MES_Bus_Plan_PartHead> lamada = new LambdaHelper<MES_Bus_Plan_PartHead>(); if (!string.IsNullOrEmpty(PrintModel.Condition)) { GridFilterHelp.SetFilter(lamada, JArray.Parse(PrintModel.Condition), "", UserInfo);//将过滤条件转换为兰姆达表达式 } var models = (from head in heads join body in bodys on head.ID equals body.HeadID where body.BatchNo == Batch.Value select new MES_Bus_Plan_PartHead { ID = head.ID,//主键 ProductClan = head.ProductClan,//产品族 AssemblingGroup = head.AssemblingGroup,//组别 AssemblingStation = head.AssemblingStation,//工位 PartName = head.PartName,//名称 CodeName = head.CodeName,//代号 ConsultCode = head.ConsultCode,//参考代号 MaterialVersion = head.MaterialVersion,//版本号 Specification = head.Specification,//规格 MaterialQuality = head.MaterialQuality,//材料 PlateSize = head.PlateSize,//下料尺寸 ReferencePlateSize = head.ReferencePlateSize,//板材规格 MultiParts = head.MultiParts,//双件 MultiPartsRemark = head.MultiPartsRemark,//双件备注 Add By:Jundi Date:2019-05-08 MoldRemark = head.MoldRemark,//模具备注 ClipPlateMachineNumber = head.ClipPlateMachineNumber,//剪板机 PunchingMachineNumber = head.PunchingMachineNumber,//冲床 BendingMachineNumber = head.BendingMachineNumber,//折弯机 Purchasing = head.Purchasing,//采购 Guillotining = head.Guillotining,//剪板 Punching = head.Punching,//冲制 CuPunching = head.CuPunching,//铜排 Tapping = head.Tapping,//攻丝 Bending = head.Bending,//折弯 Stock = head.Stock,//仓库 Carving = head.Carving,//附件 Turning = head.Turning,//模具 Welding = head.Welding,//电焊 PlasticSprying = head.PlasticSprying,//喷塑 Plating = head.Plating,//电镀 Assembling = head.Assembling,//装配 PowderCoatedSize = head.PowderCoatedSize,//喷塑面积 Standard = head.Standard,//标准 PunchingMachineProgramNumber = head.PunchingMachineProgramNumber,//程序号 Category = head.Category,//类别 PlateLength = head.PlateLength,//展开料长 PlateWidth = head.PlateWidth,//展开料宽 PlateThickness = head.PlateThickness,//板厚 Quality = head.Quality,//材质 IsSpecial = head.IsSpecial,//用定尺板 IsResidual = head.IsResidual,//用边料 PlateParts = head.PlateParts,//展开料零件数量 RawPlateCodeName = head.RawPlateCodeName,//原材料代号 RawPlateLength = head.RawPlateLength,//原材料长 RawPlateWidth = head.RawPlateWidth,//原材料宽 RawPlateParts = head.RawPlateParts,//原材料零件数量 GuillotiningStock = head.GuillotiningStock,//剪板超市件 NotInSingleBom = head.NotInSingleBom,//单台配置清单不体现 MaterialCode = head.MaterialCode,//物料编码 Folder = head.Folder,//文件夹 IsConManu = head.IsConManu,//是否为集中制造 ConManuProcedure = head.ConManuProcedure,//集中制造工序 ConManuItemno = head.ConManuItemno,//集中制造物料编码 SpecialRemark = head.SpecialRemark,//领导特殊要求 Quantity = body.Quantity,//数量 Bulks = head.Bulks,//散件 Weight = head.Weight,//净重(Kg) Opening = head.Opening,//开孔编号 SurfaceTAreatment = head.SurfaceTAreatment,//表面处理 ParentItem = head.ParentItem,//父项 WhetherWelding = head.WhetherWelding,//焊接 TransferOrder = head.TransferOrder,//转序 PlanOrderID = head.PlanOrderID,//计划主键 REC_CREATOR = head.REC_CREATOR,//创建人 REC_CREATE_TIME = head.REC_CREATE_TIME,//创建时间 REC_REVISOR = head.REC_REVISOR,//修改人 REC_REVISE_TIME = head.REC_REVISE_TIME,//修改时间 REC_DELETOR = head.REC_DELETOR,//删除人 REC_DELETE_TIME = head.REC_DELETE_TIME,//删除时间 DELETE_FLAG = head.DELETE_FLAG,//逻辑删除标识 REMARK = head.REMARK,//备注 SkillRemark = head.SkillRemark, Bodies = head.Bodies }).AsQueryable().Where(lamada.andwhere).ToList(); if (models.Count == 0) { return; } var BactchNo = Batch; foreach (var model in models) { if (string.IsNullOrEmpty(model.SkillRemark)) { if (!string.IsNullOrEmpty(model.Opening))//如果开孔编号不为空 { model.SkillRemark += "【" + model.Opening + "】/ "; } model.SkillRemark += "版本"; if (!string.IsNullOrEmpty(model.MaterialVersion))//版本号不为空 { model.SkillRemark += model.MaterialVersion; } } else { if (!string.IsNullOrEmpty(model.Opening)) { model.SkillRemark = "【" + model.Opening + "】/ " + model.SkillRemark; } } if (ord.Pro_name == "高压柜体") { var cns = from body in model.Bodies join p in products on body.ContainerNumber equals p.ContainerNumber orderby p.BatchNo, p.ContainerNumber select new { p.BatchNo, p.ContainerNumber }; var BatchNo = 0; if (BactchNo != 1 || !model.ISMoveBatche) { cns = cns.Where(p => p.BatchNo == BactchNo).ToList(); } foreach (var cn in cns) { if (cn.BatchNo != BatchNo) { BatchNo = cn.BatchNo.Value; model.SkillRemark += " C" + BatchNo + ": "; } else { model.SkillRemark += ","; } model.SkillRemark += cn.ContainerNumber; } } } Dictionary<int, decimal?> dicQuantity = new Dictionary<int, decimal?>(); foreach (var model in models) { if (dicQuantity.ContainsKey(model.ID)) { dicQuantity[model.ID] = dicQuantity[model.ID] + model.Quantity; } else { dicQuantity.Add(model.ID, model.Quantity); } } var newsheet = PrintModel.REMARK + "C" + Batch; var NO = 1; if (dics.ContainsKey(newsheet))//判断是否存在该清单 { NO = dics[newsheet]; NO++; dics[newsheet] = NO; } else { dics.Add(newsheet, 1); } var worksheet = package.Workbook.Worksheets.Add(newsheet + GetRome(NO)); //创建sheet worksheet.Column(1).Width = 8; //产品族 worksheet.Column(2).Width = 6; //组别 worksheet.Column(3).Width = 7; //工位 worksheet.Column(4).Width = 30; //名称 worksheet.Column(5).Width = 15; //图号 worksheet.Column(6).Width = 16; //规格 worksheet.Column(7).Width = 10; //材料 worksheet.Column(8).Width = 6; //数量 worksheet.Column(9).Width = 15; //转序 worksheet.Column(10).Width = 23; //备注 worksheet.Cells["A1:J8"].Style.Font.Name = "宋体"; //字体设置 worksheet.Cells["A1:J8"].Style.Font.Bold = true; //加粗 worksheet.Cells["A1:J8"].Style.Font.Size = 16; worksheet.Cells["A1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//字体水平居中 worksheet.Cells["A1"].Style.Font.Size = 24; if (ord.FactoryID == 4) { worksheet.Cells["A1"].Value = PrintModel.REMARK; } else { worksheet.Cells["A1"].Value = "物料配送卡"; } worksheet.Cells["A1:I1"].Merge = true; //合并单元格 worksheet.Cells["J1"].Value = PrintModel.AllocationDepartment; worksheet.Cells["J1"].Style.Font.Size = 24; worksheet.Cells["A2:C2"].Merge = true; //合并单元格 worksheet.Cells["A3:C3"].Merge = true; //合并单元格 worksheet.Cells["A4:C4"].Merge = true; //合并单元格 worksheet.Cells["A5:C5"].Merge = true; //合并单元格 worksheet.Cells["A6:C7"].Merge = true; //合并单元格 worksheet.Cells["D2:E2"].Merge = true; //合并单元格 worksheet.Cells["D3:E3"].Merge = true; //合并单元格 worksheet.Cells["D4:E4"].Merge = true; //合并单元格 worksheet.Cells["D5:E5"].Merge = true; //合并单元格 worksheet.Cells["D6:H7"].Merge = true; //合并单元格 worksheet.Cells["G2:H2"].Merge = true; //合并单元格 worksheet.Cells["G3:H3"].Merge = true; //合并单元格 worksheet.Cells["G4:H4"].Merge = true; //合并单元格 worksheet.Cells["G5:H5"].Merge = true; //合并单元格 worksheet.Cells["A2"].Value = "生产线名称:"; worksheet.Cells["D2"].Value = ord.Pro_name + newsheet;//高压柜体喷塑线 worksheet.Cells["F2"].Value = "产品型号:"; worksheet.Cells["G2"].Value = ord.Pro_model;//产品型号 worksheet.Cells["I2"].Value = "装配日期:"; worksheet.Cells["J2"].Value = ord.Assemble_time == null ? "" : ord.Assemble_time.Value.ToShortDateString();//装配日期 worksheet.Cells["A3"].Value = "客 户 名称:"; worksheet.Cells["D3"].Value = ord.CustomerName;//中骏电气 worksheet.Cells["F3"].Value = "总计台数:"; worksheet.Cells["G3"].Value = Math.Round(ord.Plat_number.Value, 0);//总计台数 worksheet.Cells["G3"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;//字体水平居← worksheet.Cells["I3"].Value = "技 术 员:"; worksheet.Cells["J3"].Value = ord.DesignName;//技术员 worksheet.Cells["A4"].Value = "合 同 编号:"; worksheet.Cells["D4"].Value = ord.Contract_i_no;//G180930-190 worksheet.Cells["F4"].Value = "生产批次:"; worksheet.Cells["G4"].Value = Batch.Value;//生产批次号 worksheet.Cells["G4"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;//字体水平居← worksheet.Cells["I4"].Value = "计 划 员:"; worksheet.Cells["J4"].Value = UserInfo.UserName;//计划员 worksheet.Cells["A5"].Value = "批 次 编号:"; worksheet.Cells["D5"].Value = ord.Contract_i_no + "C" + Batch;//G180930-190C1 worksheet.Cells["F5"].Value = "批次台数:"; worksheet.Cells["G5"].Value = products.Count(p => p.BatchNo == Batch);//批次台数 worksheet.Cells["G5"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;//字体水平居← worksheet.Cells["I5"].Value = "眉头颜色:"; worksheet.Cells["J5"].Value = ord.Blows_colour;//眉头颜色 worksheet.Cells["A6"].Value = "备 注:"; worksheet.Cells["D6"].Value = ord.PlanRemark;//计划备注 worksheet.Cells["D6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;//字体水平居← worksheet.Cells["D6"].Style.VerticalAlignment = ExcelVerticalAlignment.Top;//字体水平居↑ worksheet.Cells["D6"].Style.WrapText = true; worksheet.Cells["D6"].Style.Font.Size = 12; worksheet.Row(6).Height = 30; worksheet.Row(7).Height = 30; worksheet.Cells["I6"].Value = "面板颜色:"; worksheet.Cells["J6"].Value = ord.Plat_colour;//面板颜色 if (ord.FactoryID == 4)//辛柏 { worksheet.Cells["I7"].Value = "机柜颜色:"; worksheet.Cells["J7"].Value = ord.Frame_colour;//机柜颜色 worksheet.Cells["I4"].Value = "底座颜色:"; worksheet.Cells["J4"].Value = ord.Cabinet_colour;//底座颜色 worksheet.Cells["I3:J3"].Merge = true; //合并单元格 worksheet.Cells["I3"].Value = "技术员:"+ ord.DesignName+";计划员:"+ UserInfo.UserName; } else { worksheet.Cells["I7"].Value = "框架颜色:"; worksheet.Cells["J7"].Value = ord.Frame_colour;//框架颜色 } worksheet.Cells["A8"].Value = "产品族"; worksheet.Cells["B8"].Value = "组别"; worksheet.Cells["C8"].Value = "工位"; worksheet.Cells["D8"].Value = "名称"; worksheet.Cells["E8"].Value = "图号"; worksheet.Cells["F8"].Value = "规格"; worksheet.Cells["G8"].Value = "材料"; worksheet.Cells["H8"].Value = "数量"; worksheet.Cells["I8"].Value = "转序"; worksheet.Cells["J8"].Value = "备注"; int Row = 8; //循环写入清单 foreach (var model in models.GroupBy(p => p.ID).Select(g => g.First()).OrderBy(p => p.AssemblingStation).ThenBy(p => p.ProductClan).ThenBy(p=>p.PartName)) { Row++; worksheet.Cells[Row, 1].Value = model.ProductClan;//产品族 worksheet.Cells[Row, 2].Value = model.AssemblingGroup;//组别 worksheet.Cells[Row, 3].Value = model.AssemblingStation;//工位 worksheet.Cells[Row, 4].Value = model.PartName;//名称 worksheet.Cells[Row, 5].Value = model.CodeName;//图号 worksheet.Cells[Row, 6].Value = model.Specification;//规格 worksheet.Cells[Row, 7].Value = model.MaterialQuality;//材料 worksheet.Cells[Row, 8].Value = dicQuantity[model.ID];//数量 worksheet.Cells[Row, 9].Value = model.TransferOrder;//转序 worksheet.Cells[Row, 10].Value = model.SkillRemark;//备注 } var Cells = worksheet.Cells[2, 1, Row, 10]; Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin; //边框样式 Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin; Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin; Cells.Style.ShrinkToFit = true;//字体自动填充 //worksheet.PrinterSettings.PrintArea.Address = new ExcelAddress(1, 1, Row, 10).Address; var BodyCells = worksheet.Cells[9, 1, Row, 10]; BodyCells.Style.Font.Name = "宋体"; //字体设置 BodyCells.Style.Font.Size = 14; worksheet.PrinterSettings.Scale = 75;//打印缩放 worksheet.PrinterSettings.LeftMargin = 0.2m;//左边距 worksheet.PrinterSettings.RightMargin = 0.1m;//右边距 worksheet.PrinterSettings.TopMargin = 0m;//上边距 worksheet.PrinterSettings.BottomMargin = 0.5m;//下边距 worksheet.HeaderFooter.OddFooter.LeftAlignedText = "编制:" + UserInfo.UserName + " " + "日期:" + Now.ToShortDateString() + " " + "领料/日期: 出库/日期: 记账/日期: " + "&P/&N"; worksheet.PrinterSettings.RepeatRows = new ExcelAddress("$1:$8");//设置打印标题 worksheet.View.FreezePanes(9, 1);//冻结窗格 string File = "~/FileTemp/万控文件LOGO.bmp"; string FullPathFileName = PageContext.MapPath(File); using (FileStream stream = new FileStream(FullPathFileName, FileMode.Open, FileAccess.Read)) { ExcelPicture picture = worksheet.Drawings.AddPicture("logo", System.Drawing.Image.FromStream(stream));//插入图片 picture.SetPosition(0, 5);//设置图片的位置 picture.SetSize(127, 36);//设置图片的大小 } } } /// <summary> /// 导出装配流水线清单 /// </summary> /// <param name="heads">表头</param> /// <param name="ord">计划订单</param> /// <param name="package">EXCEL</param> /// <param name="dics"></param> /// <param name="ProductClan">产品族</param> private void Assemble(List<MES_Bus_Plan_PartHead> heads, List<MES_Bus_Plan_PartBody> bodys, List<MES_Bus_Plan_Product> products, MES_Bus_Plan_Orders ord, ExcelPackage package, Dictionary<string, int> dics, List<Model.TCustomerDemandOrderStation> cdos, MES_Bus_Plan_PrintModel PrintModel) { var Stations = heads.Where(p => !string.IsNullOrEmpty(p.AssemblingStation)).GroupBy(p => p.AssemblingStation.ToUpper()).OrderBy(p => p.Key).Select(p => p.Key).ToList();//所有工位 foreach (var Station in Stations) { LambdaHelper<MES_Bus_Plan_PartHead> lamada = new LambdaHelper<MES_Bus_Plan_PartHead>(); if (!string.IsNullOrEmpty(PrintModel.Condition)) { GridFilterHelp.SetFilter(lamada, JArray.Parse(PrintModel.Condition), "", UserInfo);//将过滤条件转换为兰姆达表达式 } var models = heads.Where(p => p.AssemblingStation == Station).AsQueryable().Where(lamada.andwhere).ToList(); if (models.Count == 0) { continue; } foreach (var model in models) { var str = ""; var batch = 0; foreach (var body in bodys.Where(p => p.HeadID == model.ID).ToList().OrderBy(p => p.ContainerNumber)) { var BatchNo = products.First(p => p.ContainerNumber == body.ContainerNumber).BatchNo; if (BatchNo != batch) { if (str != "") { str += " "; } batch = BatchNo.Value; str += "C" + batch + "：" + body.ContainerNumber; } else { str += "," + body.ContainerNumber; } } model.TransferOrder = str; } // var newsheet = PrintModel.REMARK + Station; var newsheet = Station; var NO = 1; if (dics.ContainsKey(newsheet))//判断是否存在该清单 { NO = dics[newsheet]; NO++; dics[newsheet] = NO; } else { dics.Add(newsheet, 1); } var worksheet = package.Workbook.Worksheets.Add(newsheet + GetRome(NO)); //创建sheet worksheet.Column(1).Width = 7; //产品族 worksheet.Column(2).Width = 5; //组别 worksheet.Column(3).Width = 6; //工位 worksheet.Column(4).Width = 36; //名称 worksheet.Column(5).Width = 14; //图号 worksheet.Column(6).Width = 15; //规格 worksheet.Column(7).Width = 12; //材料 worksheet.Column(8).Width = 6; //数量 worksheet.Column(9).Width = 60; //柜号列表 worksheet.Column(10).Width = 23; //备注 worksheet.Cells["A1:J6"].Style.Font.Name = "宋体"; //字体设置 worksheet.Cells["A1:J6"].Style.Font.Bold = true; //加粗 worksheet.Cells["A1:J6"].Style.Font.Size = 14; worksheet.Cells["A5"].Style.Font.Color.SetColor(Color.White);//字体颜色：白色 worksheet.Cells["A5:C5"].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells["A5:C5"].Style.Fill.BackgroundColor.SetColor(Color.Red);//背景颜色：酒红色 worksheet.Cells["A6:J6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//字体水平居中 worksheet.Cells["A5:C5"].Merge = true; //合并单元格 worksheet.Cells["D2:I2"].Merge = true; //合并单元格 worksheet.Cells["E3:F3"].Merge = true; //合并单元格 worksheet.Cells["E5:J5"].Merge = true; //合并单元格 worksheet.Cells["A5"].Value = "装配流水线清单";//A5 领料清单-喷塑线 worksheet.Cells["D2"].Value = "客户名称：" + ord.CustomerName;//客户名称:中骏电气 worksheet.Cells["D3"].Value = "合同编号：" + ord.Contract_i_no;//合同编号:G180930-190 worksheet.Cells["D4"].Value = "产品型号：" + ord.Pro_model;//产品型号:KYN28A-12(II) worksheet.Cells["D5"].Value = "柜体台数：" + Math.Round(ord.Plat_number.Value, 0);//台数：29 worksheet.Cells["E4"].Value = "装配日期：" + (ord.Assemble_time == null ? "" : ord.Assemble_time.Value.ToShortDateString());//装配日期：2018-01-01 worksheet.Cells["E5"].Value = ord.PlanRemark;//重点备注：//待取值 worksheet.Cells["J2"].Value = "计划员：" + UserInfo.UserName;//计划员：李媛媛 // 张爱强说 谁打印就显示谁的名字 worksheet.Cells["J3"].Value = "技术员：" + ord.DesignName;//技术员 worksheet.Cells["J4"].Value = "分配部门:" + PrintModel.AllocationDepartment; worksheet.Cells["E3"].Value = "工位：" + Station;//工位 worksheet.Cells["J2"].Style.ShrinkToFit = true;//计划员：李媛媛 // 张爱强说 谁打印就显示谁的名字 worksheet.Cells["J3"].Style.ShrinkToFit = true;//技术员 worksheet.Cells["J4"].Style.ShrinkToFit = true; worksheet.Cells["A6"].Value = "产品族"; worksheet.Cells["B6"].Value = "组别"; worksheet.Cells["C6"].Value = "工位"; worksheet.Cells["D6"].Value = "名称"; worksheet.Cells["E6"].Value = "图号"; worksheet.Cells["F6"].Value = "规格"; worksheet.Cells["G6"].Value = "材料"; worksheet.Cells["H6"].Value = "数量"; worksheet.Cells["I6"].Value = "柜号列表"; worksheet.Cells["J6"].Value = "备注"; int Row = 6; //循环写入清单 foreach (var model in models.OrderBy(p => p.PartName).ThenBy(p => p.CodeName)) { Row++; worksheet.Cells[Row, 1].Value = model.ProductClan;//产品族 worksheet.Cells[Row, 2].Value = model.AssemblingGroup;//组别 worksheet.Cells[Row, 3].Value = model.AssemblingStation;//工位 worksheet.Cells[Row, 4].Value = model.PartName;//名称 worksheet.Cells[Row, 5].Value = model.CodeName;//图号 worksheet.Cells[Row, 6].Value = model.Specification;//规格 worksheet.Cells[Row, 7].Value = model.MaterialQuality;//材料 worksheet.Cells[Row, 8].Value = model.Quantity;//数量 worksheet.Cells[Row, 9].Value = model.TransferOrder;//柜号列表 worksheet.Row(Row).Height = (Encoding.Default.GetByteCount(model.TransferOrder) / 59 + 1) * worksheet.Row(Row).Height; worksheet.Cells[Row, 9].Style.WrapText = true;//自动换行 if (!string.IsNullOrEmpty(model.Opening))//如果开孔编号不为空 { model.SkillRemark = "【" + model.Opening + "】/ " + model.SkillRemark; } worksheet.Cells[Row, 10].Value = model.SkillRemark;//技术备注 } if (cdos.Where(p => p.AssemblingStation == Station).Count() > 0) {//写入特殊要求 Row++; worksheet.Cells[Row, 1].Value = "特殊要求";//特殊要求 worksheet.Cells[Row, 2].Value = "检验员";//检验员 worksheet.Cells[Row, 3].Value = "工位";//工位 worksheet.Cells[Row, 4].Value = "特殊要求类别";//特殊要求类别 worksheet.Cells[Row, 5].Value = "特殊要求";//特殊要求 worksheet.Cells["E" + Row + ":J" + Row].Merge = true; //合并单元格 worksheet.Cells["A" + Row + ":E" + Row].Style.Font.Bold = true; //设置字体加粗 //循环写入特殊要求 foreach (var cdo in cdos.Where(p => p.AssemblingStation == Station)) { Row++; worksheet.Cells[Row, 1].Value = "特殊要求";//特殊要求 worksheet.Cells[Row, 2].Value = cdo.Checker;//检验员 worksheet.Cells[Row, 3].Value = cdo.AssemblingStation;//工位 worksheet.Cells[Row, 4].Value = cdo.CusDemandType;//特殊要求类别 worksheet.Cells[Row, 5].Value = cdo.CusDemandName;//特殊要求 worksheet.Cells[Row, 5].Style.WrapText = true;//自动换行 worksheet.Cells["E" + Row + ":J" + Row].Merge = true; //合并单元格 } } var Cells = worksheet.Cells[6, 1, Row, 10]; Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin; //边框样式 Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin; Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin; Cells.Style.ShrinkToFit = true;//字体自动填充 //worksheet.PrinterSettings.PrintArea.Address = new ExcelAddress(1,1,Row,10).Address; var BodyCells = worksheet.Cells[7, 1, Row, 10]; BodyCells.Style.Font.Name = "宋体"; //字体设置 BodyCells.Style.Font.Size = 12; worksheet.PrinterSettings.Scale = 75;//打印缩放 worksheet.PrinterSettings.RepeatRows = new ExcelAddress("$1:$6");//设置打印标题 worksheet.View.FreezePanes(7, 1);//冻结窗格 worksheet.PrinterSettings.LeftMargin = 0.2m;//左边距 worksheet.PrinterSettings.RightMargin = 0.1m;//右边距 worksheet.PrinterSettings.TopMargin = 0m;//上边距 worksheet.PrinterSettings.BottomMargin = 0.5m;//下边距 worksheet.PrinterSettings.Orientation = eOrientation.Landscape;//横向打印 worksheet.HeaderFooter.OddFooter.LeftAlignedText = "编制:" + UserInfo.UserName + " " + "日期:" + Now.ToShortDateString() + " " + "领料/日期: 出库/日期: 记账/日期: " + "&P/&N"; worksheet.Row(1).Hidden = true;//隐藏第一行 string File = "~/FileTemp/万控文件LOGO.bmp"; string FullPathFileName = PageContext.MapPath(File); using (FileStream stream = new FileStream(FullPathFileName, FileMode.Open, FileAccess.Read)) { ExcelPicture picture = worksheet.Drawings.AddPicture("logo", System.Drawing.Image.FromStream(stream));//插入图片 picture.SetPosition(38, 4);//设置图片的位置 picture.SetSize(127, 36);//设置图片的大小 } } } /// <summary> /// 单台清单 /// </summary> /// <param name="heads"></param> /// <param name="bodys"></param> /// <param name="ord"></param> /// <param name="PrintModel"></param> private void SingleList(List<Model.TCustomerDemandOrderStation> cdos, List<MES_Bus_Plan_PartHead> heads, List<MES_Bus_Plan_PartBody> bodys, MES_Bus_Plan_Orders ord, MES_Bus_Plan_PrintModel PrintModel) { var ContainerNumbers = bodys.GroupBy(p => p.ContainerNumber).OrderBy(p => p.Key).Select(p => p.Key).ToList();//所有柜号 string File = "~/FileTemp/单台清单模板.xlsm"; if (PrintModel.REMARK.Contains("柜体")) { File = "~/FileTemp/柜体事业群单台清单模板.xlsm"; } string FullPathFileName = PageContext.MapPath(File); LambdaHelper<MES_Bus_Plan_PartHead> lamada = new LambdaHelper<MES_Bus_Plan_PartHead>(); if (!string.IsNullOrEmpty(PrintModel.Condition)) { GridFilterHelp.SetFilter(lamada, JArray.Parse(PrintModel.Condition), "", UserInfo);//将过滤条件转换为兰姆达表达式 } using (FileStream stream = new FileStream(FullPathFileName, FileMode.Open, FileAccess.Read)) { XSSFWorkbook book = new XSSFWorkbook(stream); for (int i = 2; i <= Convert.ToInt32(ord.Plat_number.Value); i++) { book.GetSheet("1").CopySheet(i.ToString()); } System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); #region using (ExcelPackage package = new ExcelPackage(ms)) { foreach (var ContainerNumber in ContainerNumbers) { var models = (from head in heads join body in bodys on head.ID equals body.HeadID where body.ContainerNumber == ContainerNumber select new MES_Bus_Plan_PartHead { ID = head.ID,//主键 ProductClan = head.ProductClan,//产品族 AssemblingGroup = head.AssemblingGroup,//组别 AssemblingStation = head.AssemblingStation,//工位 PartName = head.PartName,//名称 CodeName = head.CodeName,//代号 ConsultCode = head.ConsultCode,//参考代号 MaterialVersion = head.MaterialVersion,//版本号 Specification = head.Specification,//规格 MaterialQuality = head.MaterialQuality,//材料 PlateSize = head.PlateSize,//下料尺寸 ReferencePlateSize = head.ReferencePlateSize,//板材规格 MultiParts = head.MultiParts,//双件 MultiPartsRemark = head.MultiPartsRemark,//双件备注 Add By:Jundi Date:2019-05-08 MoldRemark = head.MoldRemark,//模具备注 ClipPlateMachineNumber = head.ClipPlateMachineNumber,//剪板机 PunchingMachineNumber = head.PunchingMachineNumber,//冲床 BendingMachineNumber = head.BendingMachineNumber,//折弯机 Purchasing = head.Purchasing,//采购 Guillotining = head.Guillotining,//剪板 Punching = head.Punching,//冲制 CuPunching = head.CuPunching,//铜排 Tapping = head.Tapping,//攻丝 Bending = head.Bending,//折弯 Stock = head.Stock,//仓库 Carving = head.Carving,//附件 Turning = head.Turning,//模具 Welding = head.Welding,//电焊 PlasticSprying = head.PlasticSprying,//喷塑 Plating = head.Plating,//电镀 Assembling = head.Assembling,//装配 PowderCoatedSize = head.PowderCoatedSize,//喷塑面积 Standard = head.Standard,//标准 PunchingMachineProgramNumber = head.PunchingMachineProgramNumber,//程序号 Category = head.Category,//类别 PlateLength = head.PlateLength,//展开料长 PlateWidth = head.PlateWidth,//展开料宽 PlateThickness = head.PlateThickness,//板厚 Quality = head.Quality,//材质 IsSpecial = head.IsSpecial,//用定尺板 IsResidual = head.IsResidual,//用边料 PlateParts = head.PlateParts,//展开料零件数量 RawPlateCodeName = head.RawPlateCodeName,//原材料代号 RawPlateLength = head.RawPlateLength,//原材料长 RawPlateWidth = head.RawPlateWidth,//原材料宽 RawPlateParts = head.RawPlateParts,//原材料零件数量 GuillotiningStock = head.GuillotiningStock,//剪板超市件 NotInSingleBom = head.NotInSingleBom,//单台配置清单不体现 MaterialCode = head.MaterialCode,//物料编码 Folder = head.Folder,//文件夹 IsConManu = head.IsConManu,//是否为集中制造 ConManuProcedure = head.ConManuProcedure,//集中制造工序 ConManuItemno = head.ConManuItemno,//集中制造物料编码 SpecialRemark = head.SpecialRemark,//领导特殊要求 Quantity = body.Quantity,//数量 Bulks = head.Bulks,//散件 Weight = head.Weight,//净重(Kg) Opening = head.Opening,//开孔编号 SurfaceTAreatment = head.SurfaceTAreatment,//表面处理 ParentItem = head.ParentItem,//父项 WhetherWelding = head.WhetherWelding,//焊接 TransferOrder = head.TransferOrder,//转序 PlanOrderID = head.PlanOrderID,//计划主键 REC_CREATOR = head.REC_CREATOR,//创建人 REC_CREATE_TIME = head.REC_CREATE_TIME,//创建时间 REC_REVISOR = head.REC_REVISOR,//修改人 REC_REVISE_TIME = head.REC_REVISE_TIME,//修改时间 REC_DELETOR = head.REC_DELETOR,//删除人 REC_DELETE_TIME = head.REC_DELETE_TIME,//删除时间 DELETE_FLAG = head.DELETE_FLAG,//逻辑删除标识 REMARK = head.REMARK,//备注 }).AsQueryable().Where(lamada.andwhere).ToList(); if (models.Count() == 0) { continue; } var newsheet = ContainerNumber; if (newsheet==0) { newsheet = 1; } var worksheet = package.Workbook.Worksheets[newsheet.ToString()]; if (File=="~/FileTemp/柜体事业群单台清单模板.xlsm") { worksheet.Cells["D1"].Value = "合同编号:"+ord.Contract_i_no;//内部合同号 worksheet.Cells["D2"].Value ="合同名称:"+ ord.CustomerName;//客户名称 worksheet.Cells["F1"].Value ="产品型号:"+ ord.Pro_model;//产品型号 worksheet.Cells["F2"].Value = Math.Round(ord.Plat_number.Value, 0);//台数 worksheet.Cells["G1"].Value = "技术员:" + ord.DesignName;//技术员 worksheet.Cells["G2"].Value = "计划员:" + UserInfo.UserName;//计划员 worksheet.Cells["K1"].Value = ContainerNumber;//柜号 } else { worksheet.Cells["F4"].Value = ord.Contract_i_no;//内部合同号 worksheet.Cells["F5"].Value = ord.CustomerName;//客户名称 worksheet.Cells["F6"].Value = ord.Pro_model;//产品型号 worksheet.Cells["F7"].Value = ord.Assemble_time;//装配日期 worksheet.Cells["K4"].Value = ContainerNumber;//柜号 worksheet.Cells["K5"].Value ="台数:"+ Math.Round(ord.Plat_number.Value, 0);//台数 worksheet.Cells["K6"].Value = ord.PlanRemark;//计划备注 worksheet.Cells["L4"].Value = "技术员:" + ord.DesignName;//技术员 worksheet.Cells["L5"].Value = "计划员:" + UserInfo.UserName;//计划员 } int Row = 8; //循环写入清单 foreach (var model in models.OrderBy(p=>p.AssemblingStation).ThenBy(p => p.PartName).ThenBy(p => p.CodeName)) { Row++; worksheet.Cells["B" + Row].Value = model.ProductClan;//产品族 worksheet.Cells["C" + Row].Value = model.AssemblingGroup;//组别 worksheet.Cells["D" + Row].Value = model.AssemblingStation;//工位 worksheet.Cells["E" + Row].Value = model.PartName;//名称 worksheet.Cells["F" + Row].Value = model.CodeName;//图号 worksheet.Cells["G" + Row].Value = model.Specification;//规格 worksheet.Cells["H" + Row].Value = model.MaterialQuality;//材料 worksheet.Cells["I" + Row].Value = model.Quantity;//数量 //worksheet.Cells["J" + Row].Value = model.TransferOrder;//转序 worksheet.Cells["K" + Row].Value = model.SkillRemark;//备注 } var BodyCells = worksheet.Cells[9, 2, Row, 11]; BodyCells.Style.Font.Name = "宋体"; //字体设置 BodyCells.Style.Font.Size = 10; if (cdos.Count() > 0 && PrintModel.REMARK != null && PrintModel.REMARK.Contains("特殊要求")) {//写入特殊要求 Row++; worksheet.Cells[Row, 2].Value = "特殊要求";//特殊要求 worksheet.Cells[Row, 3].Value = "工位";//工位 worksheet.Cells[Row, 4].Value = "检验员";//检验员 worksheet.Cells[Row, 5].Value = "特殊要求类别";//特殊要求类别 worksheet.Cells[Row, 6].Value = "特殊要求";//特殊要求 worksheet.Cells[Row, 6, Row, 11].Merge = true; //合并单元格 worksheet.Cells[Row, 6, Row, 11].Style.Font.Bold = true; //设置字体加粗 //循环写入清单 foreach (var cdo in cdos.OrderBy(p => p.AssemblingStation).ThenBy(p => p.CusDemandName)) { Row++; worksheet.Cells[Row, 3].Value = cdo.AssemblingStation;//工位 worksheet.Cells[Row, 4].Value = cdo.Checker;//检验员 worksheet.Cells[Row, 5].Value = cdo.CusDemandType;//技术要求类别 worksheet.Cells[Row, 6].Value = cdo.CusDemandName;//特殊要求 worksheet.Cells[Row, 6, Row, 11].Merge = true; //合并单元格 worksheet.Cells[Row, 6].Style.WrapText = true;//自动换行 worksheet.Cells[Row, 6].Style.Font.Size = 8; worksheet.Row(Row).Height = 20;//自动调整行高 worksheet.Cells[Row, 6].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;//字体水平居中 } } var Cells = worksheet.Cells[9, 2, Row, 11]; Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin; //边框样式 Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin; Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin; Cells.Style.ShrinkToFit = true;//字体自动填充 //worksheet.PrinterSettings.PrintArea.Address = new ExcelAddress(1, 1, Row, 10).Address; worksheet.PrinterSettings.Scale = 71;//打印缩放 worksheet.PrinterSettings.LeftMargin = 0.2m;//左边距 worksheet.PrinterSettings.RightMargin = 0.1m;//右边距 worksheet.PrinterSettings.TopMargin = 0m;//上边距 worksheet.PrinterSettings.BottomMargin = 0.5m;//下边距 worksheet.PrinterSettings.RepeatRows = new ExcelAddress("$1:$8");//设置打印标题 worksheet.HeaderFooter.OddFooter.LeftAlignedText = "编制:" + UserInfo.UserName + " " + "日期:" + Now.ToShortDateString() + " " + "领料/日期: 出库/日期: 记账/日期: "; worksheet.HeaderFooter.OddFooter.RightAlignedText = newsheet.ToString(); } package.Workbook.Properties.Title = "单台清单导出";//设置excel的标题 package.Workbook.Properties.Author = "许加龙";//作者 package.Workbook.Properties.Company = "万控智造(浙江)电气有限公司";//公司 // package.Workbook.Worksheets.Delete("模板"); var data = package.GetAsByteArray(); var FileName = DateTime.Now.ToString("yyyyMMddHHmmssff") + ord.Contract_i_no + ord.CustomerName + Math.Round(ord.Plat_number.Value, 0) + "台" + UserInfo.UserName + ".xlsm"; DirFileHelp.CreateFile(MesConfig.UploadPath + FileName, data); DirFileHelp.DownFile(MesConfig.UploadPath + FileName); } #endregion ms.Close(); ms.Dispose(); } } /// <summary> /// 预装清单 /// </summary> /// <param name="heads"></param> /// <param name="bodys"></param> /// <param name="ord"></param> /// <param name="PrintModel"></param> private void PartGroupBOM(List<MES_Bus_Plan_PartHead> heads, List<MES_Bus_Plan_PartBody> bodys, MES_Bus_Plan_Orders ord, ExcelPackage package, Dictionary<string, int> dics, MES_Bus_Plan_PrintModel PrintModel) { var BusinessAxleID = db.MES_Bus_Base_BusinessAxle.First(p => p.FactoryID == ord.FactoryID && p.BusinessAxleName == ord.Pro_name).BusinessAxleID; var cpBOM = db.MES_Bus_Factory_PartGroupBom.Where(p => p.BusinessAxleID == BusinessAxleID && p.PreType == "成品").Include(p => p.Bodies).ToList();//成品BOM var bcpBOM = db.MES_Bus_Factory_PartGroupBom.Where(p => p.BusinessAxleID == BusinessAxleID && p.PreType == "半成品").Include(p => p.Bodies).ToList();//半成品BOM var Parts = (from head in heads join body in bodys on head.ID equals body.HeadID select new MES_Bus_Plan_PartHead { ID = head.ID,//主键 ProductClan = head.ProductClan,//产品族 AssemblingGroup = head.AssemblingGroup,//组别 AssemblingStation = head.AssemblingStation,//工位 PartName = head.PartName,//名称 CodeName = head.CodeName,//代号 ConsultCode = head.ConsultCode,//参考代号 MaterialVersion = head.MaterialVersion,//版本号 Specification = head.Specification,//规格 MaterialQuality = head.MaterialQuality,//材料 PlateSize = head.PlateSize,//下料尺寸 ReferencePlateSize = head.ReferencePlateSize,//板材规格 MultiParts = head.MultiParts,//双件 MultiPartsRemark = head.MultiPartsRemark,//双件备注 Add By:Jundi Date:2019-05-08 MoldRemark = head.MoldRemark,//模具备注 ClipPlateMachineNumber = head.ClipPlateMachineNumber,//剪板机 PunchingMachineNumber = head.PunchingMachineNumber,//冲床 BendingMachineNumber = head.BendingMachineNumber,//折弯机 Purchasing = head.Purchasing,//采购 Guillotining = head.Guillotining,//剪板 Punching = head.Punching,//冲制 CuPunching = head.CuPunching,//铜排 Tapping = head.Tapping,//攻丝 Bending = head.Bending,//折弯 Stock = head.Stock,//仓库 Carving = head.Carving,//附件 Turning = head.Turning,//模具 Welding = head.Welding,//电焊 PlasticSprying = head.PlasticSprying,//喷塑 Plating = head.Plating,//电镀 Assembling = head.Assembling,//装配 PowderCoatedSize = head.PowderCoatedSize,//喷塑面积 Standard = head.Standard,//标准 PunchingMachineProgramNumber = head.PunchingMachineProgramNumber,//程序号 Category = head.Category,//类别 PlateLength = head.PlateLength,//展开料长 PlateWidth = head.PlateWidth,//展开料宽 PlateThickness = head.PlateThickness,//板厚 Quality = head.Quality,//材质 IsSpecial = head.IsSpecial,//用定尺板 IsResidual = head.IsResidual,//用边料 PlateParts = head.PlateParts,//展开料零件数量 RawPlateCodeName = head.RawPlateCodeName,//原材料代号 RawPlateLength = head.RawPlateLength,//原材料长 RawPlateWidth = head.RawPlateWidth,//原材料宽 RawPlateParts = head.RawPlateParts,//原材料零件数量 GuillotiningStock = head.GuillotiningStock,//剪板超市件 NotInSingleBom = head.NotInSingleBom,//单台配置清单不体现 MaterialCode = head.MaterialCode,//物料编码 Folder = head.Folder,//文件夹 IsConManu = head.IsConManu,//是否为集中制造 ConManuProcedure = head.ConManuProcedure,//集中制造工序 ConManuItemno = head.ConManuItemno,//集中制造物料编码 SpecialRemark = head.SpecialRemark,//领导特殊要求 Quantity = body.Quantity,//数量 Bulks = head.Bulks,//散件 Weight = head.Weight,//净重(Kg) Opening = head.Opening,//开孔编号 SurfaceTAreatment = head.SurfaceTAreatment,//表面处理 ParentItem = head.ParentItem,//父项 WhetherWelding = head.WhetherWelding,//焊接 TransferOrder = head.TransferOrder,//转序 PlanOrderID = head.PlanOrderID,//计划主键 REC_CREATOR = head.REC_CREATOR,//创建人 REC_CREATE_TIME = head.REC_CREATE_TIME,//创建时间 REC_REVISOR = head.REC_REVISOR,//修改人 REC_REVISE_TIME = head.REC_REVISE_TIME,//修改时间 REC_DELETOR = head.REC_DELETOR,//删除人 REC_DELETE_TIME = head.REC_DELETE_TIME,//删除时间 DELETE_FLAG = head.DELETE_FLAG,//逻辑删除标识 REMARK = head.REMARK,//备注 ContainerNumber = body.ContainerNumber//柜号 }).ToList(); List<BOM> bOMs = new List<BOM>(); foreach (var part in Parts.Where(m => cpBOM.Select(p => p.CodeName).Contains(m.CodeName)).ToList()) { foreach (var BOM in cpBOM.Where(p => p.CodeName == part.CodeName))//循环BOM { int a = 9999;//循环BOM并判断可预装数量a,冒泡法比较获取a foreach (var body in BOM.Bodies) { var b = (int)Parts.Where(p => p.ContainerNumber == part.ContainerNumber && p.CodeName == body.CodeName).Sum(p => p.Quantity) / (int)body.Quantity; if (b < a) { a = b; } if (body.PreType == "半成品") { foreach (var bcpbom in bcpBOM.First(p => p.PartGroupBomID == body.PartGroupBomID).Bodies) { var c = (int)Parts.Where(p => p.ContainerNumber == part.ContainerNumber && p.CodeName == bcpbom.CodeName).Sum(p => p.Quantity) / (int)bcpbom.Quantity / (int)body.Quantity; if (c < a) { a = c; } } } if (a == 0) { break; } } if (a > 0)//符合条件的最小预装数量 { var 成品 = new BOM(); 成品.成品图号 = part.CodeName; 成品.产品族 = part.ProductClan; 成品.组别 = part.AssemblingGroup; 成品.工位 = part.AssemblingStation; 成品.预装工位 = BOM.PreAssemblingStation; 成品.名称 = part.PartName; 成品.图号 = part.CodeName; 成品.规格 = part.Specification; 成品.材料 = part.MaterialQuality; 成品.数量 = a; 成品.类型 = "成品"; 成品.柜号 = part.ContainerNumber.Value; bOMs.Add(成品); foreach (var body in BOM.Bodies) { var Part = Parts.First(p => p.ContainerNumber == part.ContainerNumber && p.CodeName == body.CodeName); if (Part.CodeName != body.CodeName) { Part.Quantity = Part.Quantity - a * body.Quantity; } if (body.PreType == "半成品") { var 半成品 = new BOM(); 半成品.成品图号 = part.CodeName; 半成品.半成品图号 = Part.CodeName; 半成品.产品族 = Part.ProductClan; 半成品.组别 = Part.AssemblingGroup; 半成品.工位 = Part.AssemblingStation; 半成品.预装工位 = BOM.PreAssemblingStation; 半成品.名称 = Part.PartName; 半成品.图号 = Part.CodeName; 半成品.规格 = Part.Specification; 半成品.材料 = Part.MaterialQuality; 半成品.数量 = a * (int)body.Quantity; 半成品.类型 = "半成品"; 半成品.柜号 = Part.ContainerNumber.Value; bOMs.Add(半成品); foreach (var bcpbom in bcpBOM.First(p => p.PartGroupBomID == body.PartGroupBomID).Bodies) { var c = Parts.First(p => p.ContainerNumber == part.ContainerNumber && p.CodeName == bcpbom.CodeName); if (Part.CodeName != bcpbom.CodeName) { Part.Quantity = Part.Quantity - a * body.Quantity * bcpbom.Quantity; } var 半成品零件 = new BOM(); 半成品零件.成品图号 = part.CodeName; 半成品零件.产品族 = c.ProductClan; 半成品零件.组别 = c.AssemblingGroup; 半成品零件.工位 = c.AssemblingStation; 半成品零件.预装工位 = BOM.PreAssemblingStation; 半成品零件.名称 = c.PartName; 半成品零件.图号 = c.CodeName; 半成品零件.规格 = c.Specification; 半成品零件.材料 = c.MaterialQuality; 半成品零件.数量 = a * (int) body.Quantity * (int)bcpbom.Quantity; 半成品零件.类型 = "半成品零件"; 半成品零件.柜号 = c.ContainerNumber.Value; bOMs.Add(半成品零件); } } else { var 成品零件 = new BOM(); 成品零件.成品图号 = part.CodeName; 成品零件.产品族 = Part.ProductClan; 成品零件.组别 = Part.AssemblingGroup; 成品零件.工位 = Part.AssemblingStation; 成品零件.预装工位 = BOM.PreAssemblingStation; 成品零件.名称 = Part.PartName; 成品零件.图号 = Part.CodeName; 成品零件.规格 = Part.Specification; 成品零件.材料 = Part.MaterialQuality; 成品零件.数量 = a * (int)body.Quantity; 成品零件.类型 = "成品零件"; 成品零件.柜号 = Part.ContainerNumber.Value; bOMs.Add(成品零件); } } } } } var models = bOMs.GroupBy(p => new { p.产品族, p.半成品图号, p.名称, p.图号, p.工位, p.成品图号, p.材料, p.类型, p.组别, p.规格, p.预装工位 }).OrderBy(p=>p.Key.成品图号).ThenBy(p=>p.Key.半成品图号).ThenBy(p=>p.Key.图号) .ToList(); var newsheet = PrintModel.REMARK; var NO = 1; if (dics.ContainsKey(newsheet))//判断是否存在该清单 { NO = dics[newsheet]; NO++; dics[newsheet] = NO; } else { dics.Add(newsheet, 1); } var worksheet = package.Workbook.Worksheets.Copy("预装模板", newsheet + GetRome(NO)); int Row = 8; foreach (var model in models) { Row++; } var Cells = worksheet.Cells[16, 1, Row, 9]; Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin; //边框样式 Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin; Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin; Cells.Style.ShrinkToFit = true;//字体自动填充 Cells.Style.Font.Size = 14; worksheet.HeaderFooter.OddFooter.LeftAlignedText = "编制:" + UserInfo.UserName + " " + "日期:" + Now.ToShortDateString() + " " + "领料/日期: 出库/日期: 记账/日期: "; } /// <summary> /// 特殊要求工位 /// </summary> /// <param name="heads"></param> /// <param name="bodys"></param> /// <param name="ord"></param> /// <param name="PrintModel"></param> private void DemandOrderStation(List<Model.TCustomerDemandOrderStation> cdos, MES_Bus_Plan_Orders ord, ExcelPackage package, Dictionary<string, int> dics, MES_Bus_Plan_PrintModel PrintModel) { if (cdos.Count() == 0) { return; } var newsheet = PrintModel.REMARK; var NO = 1; if (dics.ContainsKey(newsheet))//判断是否存在该清单 { NO = dics[newsheet]; NO++; dics[newsheet] = NO; } else { dics.Add(newsheet, 1); } var worksheet = package.Workbook.Worksheets.Copy("特殊要求模板", newsheet + GetRome(NO)); worksheet.Cells["C5"].Value = ord.Contract_i_no;//内部合同号 worksheet.Cells["D5"].Value = ord.CustomerName;//客户名称 worksheet.Cells["D6"].Value = ord.Assemble_time;//装配日期 worksheet.Cells["D1"].Value = PrintModel.AllocationDepartment;//分配部门 worksheet.Cells["D1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;//字体水平右 var Row = 7; //循环写入清单 foreach (var cdo in cdos.OrderBy(p => p.AssemblingStation).ThenBy(p => p.CusDemandName)) { Row++; worksheet.Cells[Row, 1].Value = cdo.AssemblingStation;//工位 worksheet.Cells[Row, 2].Value = cdo.Checker;//检验员 worksheet.Cells[Row, 3].Value = cdo.CusDemandType;//技术要求类别 worksheet.Cells[Row, 4].Value = cdo.CusDemandName;//特殊要求 worksheet.Cells[Row, 4].Style.WrapText = true;//自动换行 } var Cells = worksheet.Cells[7, 1, Row, 4]; Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin; //边框样式 Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin; Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin; worksheet.HeaderFooter.OddFooter.LeftAlignedText = "编制:" + UserInfo.UserName + " " + "日期:" + Now.ToShortDateString() + " " + "领料/日期: 出库/日期: 记账/日期: " + "&P/&N"; } #endregion /// <summary> /// 获取订单特殊要求 弃用 /// </summary> /// <param name="OrderID"></param> public List<Model.TOrderCustomerDemand> GetOrderCustomerDemandToList(Model.SomContext somdb, string OrderIDS) { var OrderIDList = OrderIDS.Split(',').ToList().Distinct().ToList(); List<Model.TOrderCustomerDemand> list = new List<Model.TOrderCustomerDemand>(); foreach (var o in OrderIDList) { var OrderID = Convert.ToInt32(o); var ord = somdb.TOrders.Find(OrderID); var orda = somdb.TOrders_Assist.First(p => p.OrderID == ord.rowguid); if (somdb.TCustomerDemandType.Count(p => p.Pro_name == ord.Pro_name && p.Pro_model == ord.Pro_model) == 0) { list.AddRange( somdb.TOrderCustomerDemand.Where(m => m.OrderID == OrderID).ToList());//开启查询 } else { list.AddRange(somdb.TOrderCustomerDemand.Where(m => m.OrderID == OrderID).ToList());//开启查询 } if ((orda.IsNotSpec == null || orda.IsNotSpec == false) && (list == null || !list.Any())) { IQueryable<Model.CustomerDemandImport> temp = null; if (somdb.TCustomerDemandType.Count(p => p.Pro_name == ord.Pro_name && p.Pro_model == ord.Pro_model) == 0) { var pron = somdb.TPronModel.First(p => p.Pro_name == ord.Pro_name && p.Pro_model == ord.Pro_model); if (pron.Pro_Series != null && pron.Pro_Series == "Aikko") { pron.Pro_Series = "二代"; } string[] Series = new string[] { "通用", pron.Pro_Series }; temp = somdb.CustomerDemandImport.Where(m => m.Pro_name == ord.Pro_name && ord.Pro_name.Contains(m.Pro_model) && m.CustomerID == ord.Custom_no && Series.Contains(m.UsedType)).AsNoTracking();//开启查询 } else { temp = somdb.CustomerDemandImport.Where(m => m.Pro_model == ord.Pro_model && m.CustomerID == ord.Custom_no).AsNoTracking();//开启查询 } list.AddRange ((from item in temp select new Model.TOrderCustomerDemand { CusDemandID = item.CusDemandID, CusDemandName = item.CusDemandName, CusDemandType = item.CusDemandType, CusDemandTypeSecond = item.CusDemandTypeSecond, UsedType = item.UsedType, UsedStatus = item.UsedStatus, UsedRemark = item.UsedRemark, IsAssemble = item.IsAssemble, IsQuote = item.IsQuote, IsSkill = item.IsSkill, PartSID = item.PartSID, AsmSID = item.AsmSID }).ToList()); } } return list; } /// <summary> /// 获取特殊要求MES静态表 /// Add By:Jundi Date:2019-06-06 /// </summary> /// <param name="OrderID">订单ID</param> public IQueryable<Model.TOrderCustomerDemand> GetCustomerDemandToList(string OrderIDS) { IQueryable<Model.TOrderCustomerDemand> list = null; var OrderIDList = OrderIDS.Split(',').ToList(); var model = db.MES_Bus_Ord_CustomerDemandStation.Where(p=> OrderIDList.Contains(p.OrderId.ToString()) ); list = (from item in model select new Model.TOrderCustomerDemand { PartSID = item.AssemblingStation,//工位 ID = item.CustomerDemandStationID,//主键 CusDemandID = item.CusDemandID,//特殊要求ID CusDemandName = item.CusDemandName,//客户特殊要求 CusDemandType = item.CusDemandType,//特殊要求类别(一级) CusDemandTypeSecond = item.CusDemandTypeSecond,//特殊要求类别(二级) UsedType = item.UsedType,//应用类别 UsedStatus = item.UsedStatus,//应用状态 UsedRemark = item.UsedRemark,//应用备注 }).AsQueryable(); return list; } /// <summary> /// 数字转罗马数字 /// </summary> /// <param name="No"></param> /// <returns></returns>D private string GetRome(int No) { if (No == 1) { return ""; } else if (No == 2) { return "II"; } else if (No == 3) { return "III"; } else if (No == 4) { return "IV"; } else if (No == 5) { return "V"; } else if (No == 6) { return "VI"; } else if (No == 7) { return "VII"; } else if (No == 8) { return "VIII"; } else if (No == 9) { return "IX"; } else if (No == 10) { return "X"; } else if (No == 11) { return "XI"; } else if (No == 12) { return "XII"; } else if (No == 13) { return "XIII"; } else if (No == 14) { return "XIV"; } else if (No == 15) { return "XV"; } else if (No == 16) { return "XVI"; } else if (No == 17) { return "XVII"; } else if (No == 18) { return "XVIII"; } else if (No == 19) { return "XIX"; } else if (No == 20) { return "XX"; } else if (No == 21) { return "XXI"; } else if (No == 22) { return "XXII"; } else if (No == 23) { return "XXIII"; } else if (No == 24) { return "XXIV"; } else if (No == 25) { return "XXV"; } else if (No == 26) { return "XXVI"; } else if (No == 27) { return "XXVII"; } else if (No == 28) { return "XXVII"; } else if (No == 29) { return "XXIX"; } else if (No == 30) { return "XXX"; } return ""; } private class BOM { public string 成品图号 { get; set; } public string 半成品图号 { get; set; } public string 产品族 { get; set; } public string 组别 { get; set; } public string 工位 { get; set; } public string 预装工位 { get; set; } public string 名称 { get; set; } public string 图号 { get; set; } public string 规格 { get; set; } public string 材料 { get; set; } public int 数量 { get; set; } public string 类型 { get; set; } public int 柜号 { get; set; } public string 柜号列表 { get; set; } } } #endregion }