using Newtonsoft.Json; using Newtonsoft.Json.Linq; using Pub_Class; using RestSharp; using System; using System.Collections; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.IO; using System.Web; using System.Web.Http; using System.Windows.Forms; using WebAPI.Models; using WebAPI.Service; using System.Text.RegularExpressions; using WebAPI.Controllers.SCGL.日计划管理; using SyntacticSugar.constant; namespace WebAPI.Controllers.BaseSet { public class Kf_WIPBalController : ApiController { //获取系统参数 Pub_Class.ClsXt_SystemParameter oSystemParameter = new Pub_Class.ClsXt_SystemParameter(); public DBUtility.ClsPub.Enum_BillStatus BillStatus; public string sWhere = ""; private json objJsonResult = new json(); SQLHelper.ClsCN oCN = new SQLHelper.ClsCN(); DataSet ds; string fileip = System.Configuration.ConfigurationManager.AppSettings["FileIP"]; #region 期初在产品报表 文件导入保存 #region 期初在产品报表 文件上传检测 [Route("Kf_WIPBal/Kf_WIPBal_Excel")] [HttpPost] public object Kf_WIPBal_Excel() { try { //获取文件名称 var file = HttpContext.Current.Request.Files[0]; //获取文件物理路径 string ExcelPath = HttpContext.Current.Server.MapPath("~/" + file.FileName); //保存文件 file.SaveAs(ExcelPath); NpoiHelper np = new NpoiHelper(); DataSet ExcelDs = np.ReadExcel(ExcelPath, 1, 1, "0"); //删除文件 File.Delete(ExcelPath); //创建临时表 DataTable tb2 = new DataTable("dt2"); //添加列名（从Excel第一行获取） for (int i = 0; i < ExcelDs.Tables[0].Columns.Count; i++) { tb2.Columns.Add(ExcelDs.Tables[0].Rows[0][i].ToString()); } //添加需要从数据库查询的ID字段（隐藏字段） tb2.Columns.Add("HOrgID", typeof(Int32));//组织ID tb2.Columns.Add("HWorkShopID", typeof(Int32));//车间ID tb2.Columns.Add("HCostObjID", typeof(Int32));//成本对象ID tb2.Columns.Add("HCostItemID", typeof(Int32));//成本项目ID tb2.Columns.Add("HMaterID", typeof(Int32));//物料ID tb2.Columns.Add("HProcID", typeof(Int32));//工序ID //添加数据（从Excel第二行开始） for (int i = 1; i < ExcelDs.Tables[0].Rows.Count; i++) { DataRow row = tb2.NewRow(); for (int j = 0; j < ExcelDs.Tables[0].Columns.Count; j++) { row[j] = ExcelDs.Tables[0].Rows[i][j].ToString(); } tb2.Rows.Add(row); } var error = ""; //判断导入文件列是否完整（只检查表格中显示的字段） if (!tb2.Columns.Contains("组织代码")) error += "没有找到【组织代码】的标题,"; if (!tb2.Columns.Contains("车间代码")) error += "没有找到【车间代码】的标题,"; if (!tb2.Columns.Contains("成本对象代码")) error += "没有找到【成本对象代码】的标题,"; if (!tb2.Columns.Contains("成本项目代码")) error += "没有找到【成本项目代码】的标题,"; if (!tb2.Columns.Contains("期初金额")) error += "没有找到【期初金额】的标题,"; if (!tb2.Columns.Contains("期初数量")) error += "没有找到【期初数量】的标题,"; if (error.Length > 0) { objJsonResult.code = CodeConstant.FAIL; objJsonResult.count = CountConstant.FAIL; objJsonResult.Message = $"Excel模板存在错误,{error}\r\n"; objJsonResult.data = null; return objJsonResult; } //数据验证（逐行检查） for (int i = 0; i < tb2.Rows.Count; i++) { int index = i + 1; //Excel行号（从1开始） string HORGNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["组织代码"].ToString()); string workshopCode = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["车间代码"].ToString()); string costObject = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["成本对象代码"].ToString()); string costItem = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["成本项目代码"].ToString()); string beginAmount = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["期初金额"].ToString()); string beginQuantity = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["期初数量"].ToString()); string HORGName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["组织名称"].ToString()); string HProc = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["工序代码"].ToString()); string HMate = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["物料代码"].ToString()); if (HORGNumber != "") { //查询组织 ds = oCN.RunProcReturn("select * from Xt_ORGANIZATIONS where HNumber='" + HORGNumber + "'", "Xt_ORGANIZATIONS"); if (ds.Tables[0].Rows.Count == 0) { objJsonResult.code = CodeConstant.FAIL; objJsonResult.count = CountConstant.FAIL; objJsonResult.Message = "第" + index + "行,组织！"; objJsonResult.data = null; return objJsonResult; } else { tb2.Rows[i]["HOrgID"] = ds.Tables[0].Rows[0]["HItemID"].ToString(); } string HORGid = ds.Tables[0].Rows[0]["HItemID"].ToString(); //查询成本对象 ds = oCN.RunProcReturn("select * from Gy_Material where HNumber='" + costObject + "' and HUSEORGID=" + HORGid + "", "Gy_Material"); if (ds.Tables[0].Rows.Count == 0 && costObject != "") { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "第" + index + "行,该" + HORGName + "组织,成本对象:" + costObject + ",不存在！"; objJsonResult.data = null; return objJsonResult; } else if (costObject == "") { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "第" + index + "行,该" + HORGName + "组织,成本对象:" + costObject + ",为空"; objJsonResult.data = null; return objJsonResult; } else { tb2.Rows[i]["HCostObjID"] = ds.Tables[0].Rows[0]["HItemID"].ToString(); } //查询物料 ds = oCN.RunProcReturn("select * from Gy_Material where HNumber='" + HMate + "' and HUSEORGID=" + HORGid + "", "Gy_Material"); if (ds.Tables[0].Rows.Count == 0 && HMate != "") { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "第" + index + "行,该" + HORGName + "组织,物料:" + HMate + ",不存在！"; objJsonResult.data = null; return objJsonResult; } else if (HMate == "") { tb2.Rows[i]["HMaterID"] = 0; } else { tb2.Rows[i]["HMaterID"] = ds.Tables[0].Rows[0]["HItemID"].ToString(); } if (workshopCode == "") { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "第" + index + "行,该" + HORGName + "组织,车间:" + workshopCode + ",为空！"; objJsonResult.data = null; return objJsonResult; } //查询车间 ds = oCN.RunProcReturn("select * from Gy_Department where HNumber='" + workshopCode + "' and HUSEORGID=" + HORGid + "", "Gy_Department"); if (ds.Tables[0].Rows.Count == 0) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "第" + index + "行,该" + HORGName + "组织,车间:" + workshopCode + ",不存在！"; objJsonResult.data = null; return objJsonResult; } else { tb2.Rows[i]["HWorkShopID"] = ds.Tables[0].Rows[0]["HItemID"].ToString(); } if (costItem != "") { //查询成本项目 ds = oCN.RunProcReturn("select * from Gy_CostItem where HNumber='" + costItem + "' and HUSEORGID=" + HORGid + "", "Gy_CostItem"); if (ds.Tables[0].Rows.Count == 0) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "第" + index + "行,该" + HORGName + "组织,成本项目:" + costItem + ",不存在！"; objJsonResult.data = null; return objJsonResult; } else { tb2.Rows[i]["HCostItemID"] = ds.Tables[0].Rows[0]["HItemID"].ToString(); } } else { if (ds.Tables[0].Rows.Count == 0) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "第" + index + "行,该" + HORGName + "组织,成本项目:" + costItem + ",为空！"; objJsonResult.data = null; return objJsonResult; } } if (HProc != "") { //查询工序 ds = oCN.RunProcReturn("select * from Gy_Process where HNumber='" + HProc + "'and HUSEORGID=" + HORGid + "", "Gy_Process"); if (ds.Tables[0].Rows.Count == 0) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "第" + index + "行,该" + HORGName + "组织,工序:" + HProc + ",不存在！"; objJsonResult.data = null; return objJsonResult; } else { tb2.Rows[i]["HProcID"] = ds.Tables[0].Rows[0]["HItemID"].ToString(); } } else { tb2.Rows[i]["HProcID"] = 0; } //期初金额 if (beginAmount == "") { objJsonResult.code = CodeConstant.FAIL; objJsonResult.count = CountConstant.FAIL; objJsonResult.Message = "第" + index + "行,期初金额不能为空！"; objJsonResult.data = null; return objJsonResult; } //期初数量 if (beginQuantity == "") { objJsonResult.code = CodeConstant.FAIL; objJsonResult.count = CountConstant.FAIL; objJsonResult.Message = "第" + index + "行,期初数量不能为空！"; objJsonResult.data = null; return objJsonResult; } } else { objJsonResult.code = CodeConstant.FAIL; objJsonResult.count = CountConstant.FAIL; objJsonResult.Message = "第" + index + "行,组织代码为空"; objJsonResult.data = null; return objJsonResult; } } objJsonResult.code = CodeConstant.SUCCEED; objJsonResult.count = CountConstant.SUCCEED; objJsonResult.Message = "文件上传成功！"; objJsonResult.data = tb2; return objJsonResult; } catch (Exception e) { objJsonResult.code = CodeConstant.FAIL; objJsonResult.count = CountConstant.FAIL; objJsonResult.Message = "Exception！" + e.ToString(); objJsonResult.data = null; return objJsonResult; } } #endregion #region 期初在产品报表 导入(保存) [Route("Kf_WIPBal/Kf_WIPBal_btnSave")] [HttpPost] public object Gy_Mould_btnSave([FromBody] JObject sMainSub) { var _value = sMainSub["sMainSub"].ToString(); string msg1 = _value.ToString(); string[] sArray = msg1.Split(new string[] { "&和" }, StringSplitOptions.RemoveEmptyEntries); string msg2 = sArray[0].ToString(); string user = sArray[1].ToString(); try { if (!DBUtility.ClsPub.Security_Log("Kf_WIPBal_Edit", 1, false, user)) { objJsonResult.code = CodeConstant.FAIL; objJsonResult.count = CountConstant.FAIL; objJsonResult.Message = "无保存权限！"; objJsonResult.data = null; return objJsonResult; } List Excel = Newtonsoft.Json.JsonConvert.DeserializeObject>(msg2); List> list = new List>(); foreach (JObject item in Excel) { Dictionary dic = new Dictionary(); foreach (var itm in item.Properties()) { dic.Add(itm.Name, itm.Value.ToString()); } list.Add(dic); } oCN.BeginTran(); int i = 1; try { foreach (Dictionary item in list) { string HOrgID = item["HOrgID"].ToString();//组织ID int HYear = int.Parse(item["年"].ToString()); int HPeriod = int.Parse(item["月"].ToString()); int HWorkShopID = int.Parse(item["HWorkShopID"].ToString()); //车间ID int HCostObjID = int.Parse(item["HCostObjID"].ToString()); //成本对象ID int HCostItemID = int.Parse(item["HCostItemID"].ToString()); //成本项目ID int HMaterID = int.Parse(item["HMaterID"].ToString()); //物料ID int HProcID = int.Parse(item["HProcID"].ToString()); //工序ID int HBeginQty = int.Parse(item["期初数量"].ToString()); int HBeginBal = int.Parse(item["期初金额"].ToString()); // 1. 检查主表是否存在记录 string checkMainSql = $"select HInterID from Kf_WIPBalMain where HOrgID='{HOrgID}' and HWorkShopID={HWorkShopID} and HCostObjID={HCostObjID}"; DataSet ds = oCN.RunProcReturn(checkMainSql, "Kf_WIPBalMain"); int HInterID; int HEntryID = 1; // 2. 如果主表不存在，插入主表记录 if (ds.Tables[0].Rows.Count == 0) { string getMaxIDSql = "select isnull(max(HInterID), 0) + 1 from Kf_WIPBalMain"; DataSet dsID = oCN.RunProcReturn(getMaxIDSql, "NewID"); HInterID = Convert.ToInt32(dsID.Tables[0].Rows[0][0]); // 插入主表 string insertMainSql = $@" insert into Kf_WIPBalMain ( HYear, HPeriod, HCostCenterID, HWorkShopID, HCostObjID, HICMOInterID, HProcExchInterID, HProcExchEntryID, HIsBack, HBatchNo, HMaker, HMakeDate, HOrgID) values ( {HYear}, {HPeriod}, 0, {HWorkShopID}, {HCostObjID}, 0, 0, 0, 0, '', '{user}', getdate(), '{HOrgID}')"; oCN.RunProc(insertMainSql); } else { HInterID = Convert.ToInt32(ds.Tables[0].Rows[0]["HInterID"]); // 获取子表当前最大HEntryID string getMaxEntrySql = $"select isnull(max(HEntryID), 0) + 1 from Kf_WIPBalSub where HInterID={HInterID}"; DataSet dsEntry = oCN.RunProcReturn(getMaxEntrySql, "MaxEntry"); HEntryID = Convert.ToInt32(dsEntry.Tables[0].Rows[0][0]); } // 3. 插入子表记录（无论主表是否存在都要插入子表） string insertSubSql = $@" insert into Kf_WIPBalSub (HInterID, HEntryID, HCostItemID, HMaterID, HProcID, HDeptID, HBeginQty, HChangeQty, HReceive, HReceive_Sum, HSend, HYtdReceive, HYtdSend, HEndQty, HEndQty_Rel, HBeginBal, HCnangeBal, HDebit, HDebit_Sum, HCredit, HEndBal, HEndBal_Rel, HYtdDebit, HYtdCredit, HBeginDiff, HReceiveDiff, HSendDiff, HEndDiff, HYtdReceiveDiff, HYtdSendDiff) values ({HInterID}, {HEntryID}, {HCostItemID}, {HMaterID}, {HProcID}, 0, {HBeginQty}, 0, 0, 0, 0, 0, 0, 0, 0, {HBeginBal}, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)"; oCN.RunProc(insertSubSql); i++; } oCN.Commit(); objJsonResult.code = CodeConstant.SUCCEED; objJsonResult.count = CountConstant.SUCCEED; objJsonResult.Message = "导入成功!"; objJsonResult.data = null; return objJsonResult; } catch (Exception e) { LogService.Write(e); objJsonResult.code = CodeConstant.FAIL; objJsonResult.count = CountConstant.FAIL; objJsonResult.Message = "Exception！" + e.ToString(); objJsonResult.data = null; return objJsonResult; } } catch (Exception e) { LogService.Write(e); objJsonResult.code = CodeConstant.FAIL; objJsonResult.count = CountConstant.FAIL; objJsonResult.Message = "Exception！" + e.ToString(); objJsonResult.data = null; return objJsonResult; } } #endregion #endregion } }