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
}
