using Newtonsoft.Json;
|
using Newtonsoft.Json.Linq;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.IO;
|
using System.Linq;
|
using System.Web;
|
using System.Web.Http;
|
using System.Web.Script.Serialization;
|
using WebAPI.Models;
|
using System.Globalization;
|
namespace WebAPI.Controllers.SCGL.日计划管理
|
{
|
public class JIT_DayPlanPlatFormImportController : ApiController
|
{
|
private json objJsonResult = new json();
|
public DataSet ds = new DataSet();
|
public WebServer webserver = new WebServer();
|
SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();
|
public static string BillType = "4610";
|
Sc_WorkBillSortBillMain omdelMian = new Sc_WorkBillSortBillMain();
|
Pub_Class.ClsXt_SystemParameter oSystemParameter = new Pub_Class.ClsXt_SystemParameter();
|
|
|
#region 生产日计划 文件上传
|
[Route("JIT_DayPlanPlatFormImport/JIT_DayPlanPlatFormImport_DR")]
|
[HttpPost]
|
public object JIT_DayPlanPlatFormImport_DR()
|
{
|
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");
|
|
//添加列名
|
for (int i = 0; i < ExcelDs.Tables[0].Columns.Count; i++)
|
{
|
tb2.Columns.Add(ExcelDs.Tables[0].Rows[0][i].ToString());
|
}
|
//模板缺少列 但需要从数据库中查询出来显示在页面的字段
|
tb2.Columns.Add("hmainid", typeof(Int32));
|
tb2.Columns.Add("HMaterID", typeof(Int32));//物料ID
|
tb2.Columns.Add("单据号", typeof(string));
|
tb2.Columns.Add("HWorkShopID", typeof(Int32));//生产车间
|
tb2.Columns.Add("HEmpID", typeof(Int32));//职员
|
tb2.Columns.Add("员工", typeof(string));//职员
|
tb2.Columns.Add("HUnitID", typeof(Int32));//计量单位
|
tb2.Columns.Add("HSourceID", typeof(Int32));//生产资源id
|
tb2.Columns.Add("ICMOBillHInterID", typeof(Int32));//生产订单主id
|
tb2.Columns.Add("ICMOBillHEntryID", typeof(Int32));//生产订单子id
|
tb2.Columns.Add("HProdORGID", typeof(Int32));//组织id
|
tb2.Columns.Add("HSeOrderBillNo", typeof(string));//销售订单号
|
|
//获取系统参数
|
string Ret = "";
|
oSystemParameter.ShowBill(ref Ret);
|
|
LogService.Write("excel值：" + ExcelDs.Tables[0].Rows[1][15].ToString());//2024-11-26
|
//添加数据
|
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();
|
}
|
//如果表格第i行的第一列为空，则判断为这一行的数据为空，跳出循环并且不把数据写入 tb2
|
if (ExcelDs.Tables[0].Rows[i][0].ToString() == "" && ExcelDs.Tables[0].Rows[i][1].ToString() == "")
|
{
|
continue;
|
}
|
//else if (oSystemParameter.omodel.JIT_DayPlan_TimeFrame != "0" && oSystemParameter.omodel.JIT_DayPlan_TimeFrame != "")
|
//{
|
// Int32 HDays = Convert.ToInt32(oSystemParameter.omodel.JIT_DayPlan_TimeFrame) * 7;
|
|
|
|
|
// for (int j = 0; j < ExcelDs.Tables[0].Columns.Count; j++)
|
// {
|
// if (Convert.ToInt32(ExcelDs.Tables[0].Rows[i][j].ToString()) > 0 )
|
// {
|
// continue;
|
// }
|
// }
|
//}
|
else
|
{
|
tb2.Rows.Add(row);
|
}
|
}
|
|
var error = "";
|
var ErrorResult = "";
|
|
//查询生产订单表判断有没有生产订单信息
|
for (int i = 0; i < tb2.Rows.Count; i++)
|
{
|
if (tb2.Rows[i]["组织编码*"].ToString() == "")
|
error += "组织编码不能为空;";
|
if (tb2.Rows[i]["生产订单号*"].ToString() == "")
|
error += "生产订单号不能为空;\n";
|
if (tb2.Rows[i]["生产订单明细行号*"].ToString() == "")
|
error += "生产订单明细行号不能为空;";
|
if (tb2.Rows[i]["生产车间编码*"].ToString() == "")
|
error += "生产车间编码不能为空;";
|
if (tb2.Rows[i]["生产资源编码*"].ToString() == "")
|
error += "生产资源编码不能为空;";
|
if (tb2.Rows[i]["物料编码*"].ToString() == "")
|
error += "物料编码不能为空;";
|
|
if (error.Length > 0)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = ErrorResult += $"Excel模板存在错误，行数{i + 1}:{error}\r\n"; ;
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
|
string sql1 = string.Format("select * from h_v_JIT_ICMOBillList_DR where 单据号='{0}' and 生产车间代码='{1}' and 产品代码='{2}' and 明细行号={3} ",
|
tb2.Rows[i]["生产订单号*"].ToString(), tb2.Rows[i]["生产车间编码*"].ToString(), tb2.Rows[i]["物料编码*"].ToString(), tb2.Rows[i]["生产订单明细行号*"].ToString());
|
ds = oCN.RunProcReturn(sql1, "h_v_JIT_ICMOBillList_DR");
|
|
if (ds.Tables[0].Rows.Count == 0)
|
{
|
error += $"没有生产订单信息，生产订单号:{ tb2.Rows[i]["生产订单号*"].ToString()},生产车间编码:{tb2.Rows[i]["生产车间编码*"].ToString()}" +
|
$",生产订单明细行号:{ tb2.Rows[i]["生产订单明细行号*"].ToString()},物料编码:{ tb2.Rows[i]["物料编码*"].ToString()}";
|
}
|
|
if (error.Length > 0)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = ErrorResult += $"Excel模板存在错误，行数{i + 1}:{error}\r\n"; ;
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
else
|
{
|
tb2.Rows[i]["HMaterID"] = ds.Tables[0].Rows[0]["HMaterID"].ToString();
|
tb2.Rows[i]["物料名称"] = ds.Tables[0].Rows[0]["产品名称"].ToString();
|
tb2.Rows[i]["规格型号"] = ds.Tables[0].Rows[0]["规格型号"].ToString();
|
tb2.Rows[i]["HWorkShopID"] = ds.Tables[0].Rows[0]["HDeptID"].ToString();
|
tb2.Rows[i]["ICMOBillHInterID"] = ds.Tables[0].Rows[0]["hmainid"].ToString();//生产订单主id
|
tb2.Rows[i]["ICMOBillHEntryID"] = ds.Tables[0].Rows[0]["HEntryID"].ToString();//生产订单子id
|
string sErr = "";
|
if (oSystemParameter.ShowBill(ref sErr))
|
{
|
if (oSystemParameter.omodel.WMS_CampanyName != "宝工")
|
{
|
tb2.Rows[i]["生产订单数量"] = ds.Tables[0].Rows[0]["生产任务单数量"].ToString();//生产订单数量
|
}
|
}
|
//tb2.Rows[i]["生产订单数量"] = ds.Tables[0].Rows[0]["生产任务单数量"].ToString();//生产订单数量
|
tb2.Rows[i]["HSeOrderBillNo"] = ds.Tables[0].Rows[0]["销售订单号"].ToString();//销售订单号
|
}
|
|
ds = oCN.RunProcReturn("select * from h_v_JIT_WorkBillSortBillList_DR " +
|
$"where 生产订单号='{tb2.Rows[i]["生产订单号*"].ToString()}' and 生产订单明细行号='{tb2.Rows[i]["生产订单明细行号*"].ToString()}' " +
|
$"and 生产车间编码='{tb2.Rows[i]["生产车间编码*"].ToString()}' and 物料代码='{tb2.Rows[i]["物料编码*"].ToString()}' " +
|
$"and 生产资源编码='{tb2.Rows[i]["生产资源编码*"].ToString()}' ", "h_v_JIT_WorkBillSortBillList_DR");
|
//组织编码='{tb2.Rows[i]["组织编码*"].ToString()}' and
|
//and 生产资源编码='{tb2.Rows[i]["生产资源编码*"].ToString()}'
|
|
if (ds.Tables[0].Rows.Count > 0)
|
{
|
tb2.Rows[i]["hmainid"] = ds.Tables[0].Rows[0]["hmainid"].ToString();
|
tb2.Rows[i]["单据号"] = ds.Tables[0].Rows[0]["单据号"].ToString();
|
if (oSystemParameter.omodel.WMS_CampanyName != "宝工")
|
{
|
tb2.Rows[i]["优先级"] = ds.Tables[0].Rows[0]["优先级"].ToString();
|
}
|
tb2.Rows[i]["HSourceID"] = ds.Tables[0].Rows[0]["HSourceID"].ToString();
|
tb2.Rows[i]["HEmpID"] = ds.Tables[0].Rows[0]["HEmpID"].ToString();
|
tb2.Rows[i]["员工编码"] = ds.Tables[0].Rows[0]["职员代码"].ToString();
|
tb2.Rows[i]["员工"] = ds.Tables[0].Rows[0]["职员"].ToString();
|
tb2.Rows[i]["HUnitID"] = ds.Tables[0].Rows[0]["HUnitID"].ToString();
|
tb2.Rows[i]["计量单位"] = ds.Tables[0].Rows[0]["计量单位"].ToString();
|
tb2.Rows[i]["批次号"] = ds.Tables[0].Rows[0]["批次"].ToString();
|
tb2.Rows[i]["销售订单数量"] = ds.Tables[0].Rows[0]["销售订单数量"].ToString();
|
tb2.Rows[i]["总齐套数量"] = ds.Tables[0].Rows[0]["齐套数量"].ToString();
|
}
|
else
|
{
|
//员工
|
ds = oCN.RunProcReturn($"select * from Gy_Employee with(nolock) where HNumber='{tb2.Rows[i]["员工编码"].ToString()}'", "Gy_Employee");
|
if (ds.Tables[0].Rows.Count > 0) {
|
tb2.Rows[i]["HEmpID"] = ds.Tables[0].Rows[0]["HItemID"].ToString();
|
tb2.Rows[i]["员工"] = ds.Tables[0].Rows[0]["HName"].ToString();
|
}
|
else
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = $"第{i + 1}行,员工查无数据!";
|
objJsonResult.data = tb2;
|
return objJsonResult;
|
}
|
|
//计量单位
|
ds = oCN.RunProcReturn($"select * from Gy_Unit with(nolock) where HNumber='{tb2.Rows[i]["计量单位"].ToString()}'", "Gy_Unit");
|
if (ds.Tables[0].Rows.Count > 0)
|
{
|
tb2.Rows[i]["HUnitID"] = ds.Tables[0].Rows[0]["HItemID"].ToString();
|
}
|
else
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = $"第{i + 1}行,计量单位查无数据!";
|
objJsonResult.data = tb2;
|
return objJsonResult;
|
}
|
|
//生产资源
|
ds = oCN.RunProcReturn($"select * from Gy_Source with(nolock) where HNumber='{tb2.Rows[i]["生产资源编码*"].ToString()}'", "Gy_Source");
|
if (ds.Tables[0].Rows.Count > 0)
|
{
|
tb2.Rows[i]["HSourceID"] = ds.Tables[0].Rows[0]["HItemID"].ToString();
|
}
|
else {
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = $"第{i+1}行,生产资源查无数据!";
|
objJsonResult.data = tb2;
|
return objJsonResult;
|
}
|
}
|
//组织
|
ds = oCN.RunProcReturn($"select * from Xt_ORGANIZATIONS with(nolock) where HNumber='{tb2.Rows[i]["组织编码*"].ToString()}'", "Xt_ORGANIZATIONS");
|
if (ds.Tables[0].Rows.Count > 0)
|
{
|
tb2.Rows[i]["HProdORGID"] = ds.Tables[0].Rows[0]["HItemID"].ToString();
|
}
|
else
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = $"第{i + 1}行,组织查无数据!";
|
objJsonResult.data = tb2;
|
return objJsonResult;
|
}
|
}
|
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = ErrorResult;
|
objJsonResult.data = tb2;
|
return objJsonResult;
|
}
|
catch (Exception e)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "Exception！" + e.ToString();
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
}
|
#endregion
|
|
#region 生产日计划 导入(保存)
|
[Route("JIT_DayPlanPlatFormImport/JIT_DayPlanPlatFormImport_btnSave")]
|
[HttpPost]
|
public object JIT_DayPlanPlatFormImport_btnSave([FromBody] JObject sMainSub)
|
{
|
var _value = sMainSub["sMainSub"].ToString();
|
string msg1 = _value.ToString();
|
oCN.BeginTran();
|
//保存主表
|
objJsonResult = AddBillMain(msg1);
|
if (objJsonResult.code == "0")
|
{
|
oCN.RollBack();
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = objJsonResult.Message;
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
oCN.Commit();
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = "单据保存成功！";
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
|
public json AddBillMain(string msg1)
|
{
|
string[] sArray = msg1.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
|
string msg2 = sArray[0].ToString();
|
string msg3 = sArray[1].ToString();
|
string user = sArray[2].ToString();
|
|
try
|
{
|
if (!DBUtility.ClsPub.Security_Log("Sc_WorkBillSortBill_Edit", 1, false, user))
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "无保存权限！";
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
|
//表头字段
|
omdelMian = Newtonsoft.Json.JsonConvert.DeserializeObject<Sc_WorkBillSortBillMain>(msg2);
|
|
//JSON序列化转换字典集合
|
List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();
|
List<object> jb = JsonConvert.DeserializeObject<List<object>>(msg3);
|
foreach (JObject item in jb)
|
{
|
Dictionary<string, string> dic = new Dictionary<string, string>();
|
foreach (var itm in item.Properties())
|
{
|
dic.Add(itm.Name, itm.Value.ToString());
|
}
|
list.Add(dic);
|
}
|
|
|
|
|
for (int i = 0; i < list.Count; i++)
|
{
|
int TrueCount = 0, SumCount = 0;
|
long HInterID = 0;
|
var HBillNo = "";
|
if (list[i]["单据号"].ToString() == "" && list[i]["hmainid"].ToString() == "")
|
{
|
HBillNo = DBUtility.ClsPub.CreateBillCode_Prod(BillType, ref DBUtility.ClsPub.sExeReturnInfo, true);//获得一个新的单据号
|
HInterID = DBUtility.ClsPub.CreateBillID_Prod(BillType, ref DBUtility.ClsPub.sExeReturnInfo);//获得一个新的id
|
var HICMOBillNo = list[i]["生产订单号*"].ToString();
|
var HICMOEntrySEQ = list[i]["生产订单明细行号*"].ToString();
|
var HProdORGID = list[i]["HProdORGID"].ToString();
|
var HWorkShopID = list[i]["HWorkShopID"].ToString();
|
var HSourceID = list[i]["HSourceID"].ToString();
|
var HWorkQty = DBUtility.ClsPub.isDoule(list[i]["产能"]);
|
var HYX = list[i]["优先级"].ToString();
|
var HEmpID = list[i]["HEmpID"].ToString();
|
var HMaterID = list[i]["HMaterID"].ToString();
|
var HMaterName = list[i]["物料名称"].ToString();
|
var HMaterModel = list[i]["规格型号"].ToString();
|
var HUnitID = list[i]["HUnitID"].ToString();
|
var HBatchNo = list[i]["批次号"].ToString();
|
var HSeOrderBillQty = list[i]["销售订单数量"].ToString();
|
var HPlanQty = list[i]["生产订单数量"].ToString();
|
var HCompleteQty = list[i]["总齐套数量"].ToString();
|
var HOrderType = omdelMian.HOrderType;
|
var HDate = omdelMian.HDate;
|
var HMainSourceInterID = list[i]["ICMOBillHInterID"].ToString();
|
var HMainSourceEntryID = list[i]["ICMOBillHEntryID"].ToString();
|
var HSeOrderBillNo = list[i]["HSeOrderBillNo"].ToString();
|
|
string sql = $"select * from Sc_WorkBillSortBillMain where HMainSourceInterID={HMainSourceInterID}" +
|
$" and HMainSourceEntryID={HMainSourceEntryID} and HICMOBillNo='{HICMOBillNo}' and HICMOEntrySEQ={HICMOEntrySEQ} and HSourceID={HSourceID} and ISNULL(HDeleteMan,'') = ''";
|
LogService.Write("sql:" + sql);
|
ds = oCN.RunProcReturn(sql, "Sc_WorkBillSortBillMain");
|
if (ds.Tables[0].Rows.Count > 0)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = $"第{i + 1}行生产资源有重复,请修改!";
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
|
//主表
|
oCN.RunProc("insert into Sc_WorkBillSortBillMain(HInterID,HBillNo,HYear,HPeriod,HBillType," +
|
"HBillSubType,HDate,HBillStatus,HMaker,HMakeDate,HICMOBillNo,HOrderType," +
|
"HICMOEntrySEQ,HWorkShopID,HSourceID, HYX,HWorkQty, HEmpID," +
|
" HMaterID, HMaterName, HMaterModel, HUnitID, HBatchNo," +
|
" HSeOrderBillQty, HPlanQty, HCompleteQty,HPreparatDate," +
|
"HMainSourceInterID,HMainSourceEntryID,HICMOInterID,HICMOEntryID,HProdORGID,HICMOInterID_Sec,HICMOEntryID_Sec" +
|
",HSeOrderBillNo)values" +
|
$"({HInterID},'{HBillNo}',{DateTime.Now.Year},{DateTime.Now.Month},'{BillType}'," +
|
$"'{BillType}',GETDATE(),1,'{user}','{HDate}','{HICMOBillNo}','{HOrderType}'," +
|
$"{(HICMOEntrySEQ == "" ? 0.ToString() : HICMOEntrySEQ)},{(HWorkShopID == "" ? 0.ToString() : HWorkShopID)},{(HSourceID == "" ? 0.ToString() : HSourceID)}, {(HYX == "" ? 0.ToString() : HYX)},{(HWorkQty)}, {(HEmpID == "" ? 0.ToString() : HEmpID)}," +
|
$" {(HMaterID == "" ? 0.ToString() : HMaterID)}, '{HMaterName}', '{HMaterModel}', {(HUnitID == "" ? 0.ToString() : HUnitID)}, '{HBatchNo}'," +
|
$" {(HSeOrderBillQty == "" ? 0.ToString() : HSeOrderBillQty)}, {(HPlanQty == "" ? 0.ToString() : HPlanQty)}, {(HCompleteQty == "" ? 0.ToString() : HCompleteQty)},getdate()," +
|
$" {HMainSourceInterID},{HMainSourceEntryID},{HMainSourceInterID},{HMainSourceEntryID},{HProdORGID}, {HMainSourceInterID},{HMainSourceEntryID}" +
|
$",'{HSeOrderBillNo}')");
|
}
|
|
int SumDay = 31; //动态两月之差 DateTime.Now.AddMonths(1).AddDays(-1).Subtract(DateTime.Now).Days;
|
|
// 统计总天数
|
HashSet<DateTime> uniqueDates = new HashSet<DateTime>();
|
|
foreach (var key in list[i].Keys)
|
{
|
if (DateTime.TryParseExact(key, "yyyy-M-d", CultureInfo.InvariantCulture, DateTimeStyles.None, out DateTime date))
|
{
|
uniqueDates.Add(date);
|
}
|
}
|
|
SumDay = uniqueDates.Count;
|
|
|
for (int j = 0; j < SumDay; j++)
|
{
|
LogService.Write("1:" + list[i][DateTime.Now.AddDays(j).ToString("yyyy-MM-dd")].ToString());
|
LogService.Write("2:" + DateTime.Now.AddDays(j).ToString("yyyy-MM-dd"));
|
//主表子表都有数据
|
if (list[i][DateTime.Now.AddDays(j).ToString("yyyy-MM-dd")].ToString() != "")
|
{
|
SumCount += 1;
|
////保存子表
|
objJsonResult = AddBillSub(HInterID == 0 ? list[i]["hmainid"].ToString() : HInterID.ToString()
|
, HBillNo == "" ? list[i]["单据号"].ToString() : HBillNo
|
, DateTime.Parse(DateTime.Now.AddDays(j).ToString("yyyy-MM-dd").ToString())
|
, int.Parse(list[i][DateTime.Now.AddDays(j).ToString("yyyy-MM-dd")].ToString()));
|
|
if (objJsonResult.count == 1)
|
{
|
TrueCount += 1;
|
}
|
}
|
//主表有数据 子表无数据
|
if (j == SumDay - 1 && SumCount == 0 && HInterID != 0 && HBillNo != "")
|
{
|
objJsonResult = AddBillSub(HInterID.ToString(), HBillNo, DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd").ToString()), 0);
|
}
|
}
|
if (TrueCount != SumCount)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = objJsonResult.Message;
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
}
|
|
|
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = null;
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
catch (Exception e)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "Exception！" + e.ToString();
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
}
|
|
public json AddBillSub(string HInterID,string HBillNo, DateTime date, int HQTY)
|
{
|
//获取表格数据
|
ds = oCN.RunProcReturn($"select * from h_v_JIT_Sc_WorkBillSortBillList where 单据号='{HBillNo}'", "h_v_JIT_Sc_WorkBillSortBillList");
|
int count = 0;
|
if (ds.Tables[0].Rows.Count > 0)
|
{
|
//判断子表是否有当日的日期以及日期所对应的数量
|
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
|
{
|
if (DateTime.Parse(ds.Tables[0].Rows[i]["日计划生产日期"].ToString()) == date)
|
{
|
oCN.RunProc($"update Sc_WorkBillSortBillSub set HMasterDate='{date}',HQty={HQTY}" +
|
$" where HInterID={HInterID} and HEntryID={ds.Tables[0].Rows[i]["hsubid"].ToString()}");
|
count += 1;
|
}
|
}
|
}
|
|
if (count != 1)
|
{
|
//插入子表
|
oCN.RunProc("insert into Sc_WorkBillSortBillSub(HInterID,HSEQ," +
|
"HMasterDate,HQty)" +
|
$"values({HInterID}, {(ds.Tables[0].Rows.Count + 1)}," +
|
$" '{date}', {HQTY})");
|
}
|
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = null;
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
#endregion
|
|
#region 生产日计划(三升导入) 文件上传
|
[Route("JIT_DayPlanPlatFormImport/JIT_DayPlanPlatFormImport_SS")]
|
[HttpPost]
|
public object JIT_DayPlanPlatFormImport_SS()
|
{
|
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");
|
|
//添加列名
|
for (int i = 0; i < ExcelDs.Tables[0].Columns.Count; i++)
|
{
|
tb2.Columns.Add(ExcelDs.Tables[0].Rows[0][i].ToString());
|
}
|
//模板缺少列 但需要从数据库中查询出来显示在页面的字段
|
tb2.Columns.Add("hmainid", typeof(Int32));
|
tb2.Columns.Add("HMaterID", typeof(Int32));
|
tb2.Columns.Add("ICMOBillHInterID", typeof(Int32));
|
tb2.Columns.Add("ICMOBillHEntryID", typeof(Int32));
|
//获取系统参数
|
string Ret = "";
|
oSystemParameter.ShowBill(ref Ret);
|
|
LogService.Write("excel值：" + ExcelDs.Tables[0].Rows[1][1].ToString());//2024-11-26
|
//添加数据
|
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();
|
}
|
//如果表格第i行的第一列为空，则判断为这一行的数据为空，跳出循环并且不把数据写入 tb2
|
if (ExcelDs.Tables[0].Rows[i][0].ToString() == "" && ExcelDs.Tables[0].Rows[i][1].ToString() == "")
|
{
|
continue;
|
}
|
|
else
|
{
|
tb2.Rows.Add(row);
|
}
|
}
|
|
var error = "";
|
var ErrorResult = "";
|
|
//查询生产订单表判断有没有生产订单信息
|
for (int i = 0; i < tb2.Rows.Count; i++)
|
{
|
//if (tb2.Rows[i]["计划结束日期"].ToString() == "")
|
// error += "计划结束日期不能为空;";
|
if (tb2.Rows[i]["客户订单号"].ToString() == "")
|
error += "客户订单号不能为空;";
|
if (tb2.Rows[i]["物料编码"].ToString() == "")
|
error += "物料编码不能为空;";
|
|
if (error.Length > 0)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = ErrorResult += $"Excel模板存在错误，行数{i + 1}:{error}\r\n"; ;
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
//查找是否存在对应生产订单
|
string sql1 = string.Format("select * from h_v_IF_ICMOBillList where 客户订单号='{0}' and 产品代码='{1}' and 状态 in ('计划确认','开工')",
|
tb2.Rows[i]["客户订单号"].ToString(), tb2.Rows[i]["物料编码"].ToString());
|
ds = oCN.RunProcReturn(sql1, "h_v_IF_ICMOBillList");
|
|
if (ds.Tables[0].Rows.Count == 0)
|
{
|
error += $"没有符合条件得生产订单信息，客户订单号:{ tb2.Rows[i]["客户订单号"].ToString()},产品代码:{tb2.Rows[i]["物料编码"].ToString()}";
|
}
|
|
if (error.Length > 0)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = ErrorResult += $"Excel模板存在错误，行数{i + 1}:{error}\r\n"; ;
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
else
|
{
|
tb2.Rows[i]["HMaterID"] = ds.Tables[0].Rows[0]["HMaterID"].ToString();
|
tb2.Rows[i]["ICMOBillHInterID"] = ds.Tables[0].Rows[0]["hmainid"].ToString();//生产订单主id
|
tb2.Rows[i]["ICMOBillHEntryID"] = ds.Tables[0].Rows[0]["HEntryID"].ToString();//生产订单子id
|
}
|
//查找是否存在对应日计划工单
|
var dt = oCN.RunProcReturn($"select * from h_v_JIT_Sc_WorkBillSortBillList where 源单主内码 in({ds.Tables[0].Rows[0]["hmainid"].ToString()}) and 源单子内码 in({ds.Tables[0].Rows[0]["HEntryID"].ToString()})", "h_v_JIT_Sc_WorkBillSortBillList");
|
if (dt.Tables[0].Rows.Count > 0)
|
{
|
tb2.Rows[i]["hmainid"] = dt.Tables[0].Rows[0]["hmainid"].ToString();
|
}
|
|
}
|
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = ErrorResult;
|
objJsonResult.data = tb2;
|
return objJsonResult;
|
}
|
catch (Exception e)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "Exception！" + e.ToString();
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
}
|
#endregion
|
|
#region 生产日计划三升 导入(保存)
|
[Route("JIT_DayPlanPlatFormImport/JIT_DayPlanPlatFormImport_btnSave_SS")]
|
[HttpPost]
|
public object JIT_DayPlanPlatFormImport_btnSave_SS([FromBody] JObject sMainSub)
|
{
|
var _value = sMainSub["sMainSub"].ToString();
|
string msg1 = _value.ToString();
|
oCN.BeginTran();
|
//保存主表
|
objJsonResult = AddBillMain_SS(msg1);
|
if (objJsonResult.code == "0")
|
{
|
oCN.RollBack();
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = objJsonResult.Message;
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
oCN.Commit();
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = "单据保存成功！";
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
|
public json AddBillMain_SS(string msg1)
|
{
|
string[] sArray = msg1.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
|
string msg2 = sArray[0].ToString();
|
string msg3 = sArray[1].ToString();
|
string user = sArray[2].ToString();
|
|
try
|
{
|
if (!DBUtility.ClsPub.Security_Log("Sc_WorkBillSortBill_Edit", 1, false, user))
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "无保存权限！";
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
|
//表头字段
|
omdelMian = Newtonsoft.Json.JsonConvert.DeserializeObject<Sc_WorkBillSortBillMain>(msg2);
|
|
//JSON序列化转换字典集合
|
List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();
|
List<object> jb = JsonConvert.DeserializeObject<List<object>>(msg3);
|
foreach (JObject item in jb)
|
{
|
Dictionary<string, string> dic = new Dictionary<string, string>();
|
foreach (var itm in item.Properties())
|
{
|
dic.Add(itm.Name, itm.Value.ToString());
|
}
|
list.Add(dic);
|
}
|
|
|
|
|
for (int i = 0; i < list.Count; i++)
|
{
|
long HInterID = 0;
|
var HBillNo = "";
|
if (list[i]["hmainid"].ToString() == "")
|
{
|
HBillNo = DBUtility.ClsPub.CreateBillCode_Prod(BillType, ref DBUtility.ClsPub.sExeReturnInfo, true);//获得一个新的单据号
|
HInterID = DBUtility.ClsPub.CreateBillID_Prod(BillType, ref DBUtility.ClsPub.sExeReturnInfo);//获得一个新的id
|
var HMainSourceInterID = list[i]["ICMOBillHInterID"].ToString();
|
var HMainSourceEntryID = list[i]["ICMOBillHEntryID"].ToString();
|
var HPlanEndDate = list[i]["计划结束日期"].ToString();
|
//获取对应订单信息
|
ds = oCN.RunProcReturn($"exec h_p_ICMOBillList_PrimarySubTable '{HMainSourceInterID}','{HMainSourceEntryID}'", "h_p_ICMOBillList_PrimarySubTable");
|
if (ds.Tables[0].Rows.Count == 0)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = $"没找到对应符合条件得订单信息，行数{i + 1}\r\n"; ;
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
var HICMOBillNo = ds.Tables[0].Rows[0]["生产订单号"].ToString();
|
var HICMOEntrySEQ = ds.Tables[0].Rows[0]["生产订单明细行号"].ToString();
|
var HProdORGID = ds.Tables[0].Rows[0]["HProdORGID"].ToString();
|
var HWorkShopID = ds.Tables[0].Rows[0]["HWorkShopID"].ToString();
|
var HSourceID = ds.Tables[0].Rows[0]["HSourceID"].ToString();
|
var HWorkQty = DBUtility.ClsPub.isDoule(ds.Tables[0].Rows[0]["小时产能"].ToString());
|
var HYX = ds.Tables[0].Rows[0]["优先级"].ToString();
|
var HEmpID = ds.Tables[0].Rows[0]["操作工"].ToString();
|
var HMaterID = ds.Tables[0].Rows[0]["HMaterID"].ToString();
|
var HMaterName = ds.Tables[0].Rows[0]["物料名称"].ToString();
|
var HMaterModel = ds.Tables[0].Rows[0]["规格型号"].ToString();
|
var HUnitID = ds.Tables[0].Rows[0]["HUnitID"].ToString();
|
var HBatchNo = "";//批次号
|
var HSeOrderBillQty = ds.Tables[0].Rows[0]["销售订单数量"].ToString();
|
var HPlanQty = ds.Tables[0].Rows[0]["计划数量"].ToString();
|
var HCompleteQty = "";//总齐套数量
|
var HOrderType = omdelMian.HOrderType;
|
var HDate = omdelMian.HDate;
|
var HSeOrderBillNo = ds.Tables[0].Rows[0]["销售订单号"].ToString();
|
//主表
|
oCN.RunProc("insert into Sc_WorkBillSortBillMain(HInterID,HBillNo,HYear,HPeriod,HBillType," +
|
"HBillSubType,HDate,HBillStatus,HMaker,HMakeDate,HICMOBillNo,HOrderType," +
|
"HICMOEntrySEQ,HWorkShopID,HSourceID, HYX,HWorkQty, HEmpID," +
|
" HMaterID, HMaterName, HMaterModel, HUnitID, HBatchNo," +
|
" HSeOrderBillQty, HPlanQty, HCompleteQty,HPreparatDate," +
|
"HMainSourceInterID,HMainSourceEntryID,HICMOInterID,HICMOEntryID,HProdORGID,HICMOInterID_Sec,HICMOEntryID_Sec" +
|
",HSeOrderBillNo,HPlanEndDate)values" +
|
$"({HInterID},'{HBillNo}',{DateTime.Now.Year},{DateTime.Now.Month},'{BillType}'," +
|
$"'{BillType}',GETDATE(),1,'{user}','{HDate}','{HICMOBillNo}','{HOrderType}'," +
|
$"{(HICMOEntrySEQ == "" ? 0.ToString() : HICMOEntrySEQ)},{(HWorkShopID == "" ? 0.ToString() : HWorkShopID)},{(HSourceID == "" ? 0.ToString() : HSourceID)}, {(HYX == "" ? 0.ToString() : HYX)},{(HWorkQty)}, {(HEmpID == "" ? 0.ToString() : HEmpID)}," +
|
$" {(HMaterID == "" ? 0.ToString() : HMaterID)}, '{HMaterName}', '{HMaterModel}', {(HUnitID == "" ? 0.ToString() : HUnitID)}, '{HBatchNo}'," +
|
$" {(HSeOrderBillQty == "" ? 0.ToString() : HSeOrderBillQty)}, {(HPlanQty == "" ? 0.ToString() : HPlanQty)}, {(HCompleteQty == "" ? 0.ToString() : HCompleteQty)},getdate()," +
|
$" {HMainSourceInterID},{HMainSourceEntryID},{HMainSourceInterID},{HMainSourceEntryID},{HProdORGID}, {HMainSourceInterID},{HMainSourceEntryID}" +
|
$",'{HSeOrderBillNo}','{HPlanEndDate}')");
|
//插入子表数据
|
objJsonResult = AddBillSub(HInterID.ToString(), HBillNo, DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd").ToString()), 0);
|
}//如果已存在日计划工单更新对应计划结束日期
|
else
|
{
|
var hmainid = list[i]["hmainid"].ToString();
|
var HPlanEndDate = list[i]["计划结束日期"].ToString();
|
//主表
|
oCN.RunProc($"update Sc_WorkBillSortBillMain set HPlanEndDate = '{HPlanEndDate}' where HInterID ={hmainid}" );
|
}
|
|
}
|
|
|
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = null;
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
catch (Exception e)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "Exception！" + e.ToString();
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
}
|
#endregion
|
|
}
|
}
|
