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<object> Excel = Newtonsoft.Json.JsonConvert.DeserializeObject<List<object>>(msg2);
|
List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();
|
|
foreach (JObject item in Excel)
|
{
|
Dictionary<string, string> dic = new Dictionary<string, string>();
|
foreach (var itm in item.Properties())
|
{
|
dic.Add(itm.Name, itm.Value.ToString());
|
}
|
list.Add(dic);
|
}
|
|
oCN.BeginTran();
|
int i = 1;
|
try
|
{
|
foreach (Dictionary<string, string> 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
|
Double HBeginQty = Double.Parse(item["期初数量"].ToString());
|
Double HBeginBal = Double.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
|
|
}
|
|
}
|
