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;
|
|
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();
|
|
#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("HUnitID", typeof(Int32));//计量单位
|
tb2.Columns.Add("HSourceID", typeof(Int32));//生产资源id
|
tb2.Columns.Add("ICMOBillHInterID", typeof(Int32));//生产订单主id
|
tb2.Columns.Add("ICMOBillHEntryID", typeof(Int32));//生产订单子id
|
|
//添加数据
|
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 = "";
|
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_IF_ICMOBillList 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_IF_ICMOBillList");
|
|
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
|
tb2.Rows[i]["生产订单数量"] = ds.Tables[0].Rows[0]["生产任务单数量"].ToString();//生产订单数量
|
}
|
|
ds = oCN.RunProcReturn("select * from h_v_JIT_Sc_WorkBillSortBillList " +
|
$"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_Sc_WorkBillSortBillList");
|
//组织编码='{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();
|
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]["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 where HNumber='{tb2.Rows[i]["员工编码"].ToString()}'", "Gy_Employee");
|
if (ds.Tables[0].Rows.Count > 0)
|
tb2.Rows[i]["员工编码"] = ds.Tables[0].Rows[0]["HItemID"].ToString();
|
|
//计量单位
|
ds = oCN.RunProcReturn($"select * from Gy_Unit 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();
|
|
//生产资源
|
ds = oCN.RunProcReturn($"select * from Gy_Source 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();
|
}
|
}
|
|
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);
|
}
|
|
|
int TrueCount = 0, SumCount = 0;
|
|
for (int i = 0; i < list.Count; i++)
|
{
|
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]["组织编码*"].ToString();
|
var HWorkShopID = list[i]["HWorkShopID"].ToString();
|
var HSourceID = list[i]["HSourceID"].ToString();
|
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();
|
|
ds = oCN.RunProcReturn($"select * from Sc_WorkBillSortBillMain where HMainSourceInterID={HMainSourceInterID}" +
|
$" and HMainSourceEntryID={HMainSourceEntryID} and HICMOBillNo='{HICMOBillNo}' and HICMOEntrySEQ={HICMOEntrySEQ} and HSourceID={HSourceID}", "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, HEmpID," +
|
" HMaterID, HMaterName, HMaterModel, HUnitID, HBatchNo," +
|
" HSeOrderBillQty, HPlanQty, HCompleteQty,HPreparatDate," +
|
"HMainSourceInterID,HMainSourceEntryID,HProdORGID,HICMOInterID_Sec,HICMOEntryID_Sec)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)}, {(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},{HProdORGID}, {HMainSourceInterID},{HMainSourceEntryID})");
|
}
|
|
int SumDay = 31; //动态两月之差 DateTime.Now.AddMonths(1).AddDays(-1).Subtract(DateTime.Now).Days;
|
|
for (int j = 0; j < SumDay; j++)
|
{
|
//主表子表都有数据
|
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 == 30 && 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
|
}
|
}
|
