using Newtonsoft.Json;
|
using Newtonsoft.Json.Linq;
|
using Pub_Class;
|
using System;
|
using System.Collections;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Web;
|
using System.Web.Http;
|
using WebAPI.Models;
|
using WebAPI.Controllers.SCGL.日计划管理;
|
using System.IO;
|
using System.Linq;
|
using System.Text;
|
|
namespace WebAPI.Controllers.成本管理
|
{
|
public class CB_WipProcessTableController : ApiController
|
{
|
public DBUtility.ClsPub.Enum_BillStatus BillStatus;
|
public DAL.ClsCB_ItemMoneyBillMain BillOld = new DAL.ClsCB_ItemMoneyBillMain();
|
public DAL.ClsCB_ItemMoneyBillMain BillNew0 = new DAL.ClsCB_ItemMoneyBillMain();
|
|
private json objJsonResult = new json();
|
SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();
|
DataSet ds;
|
|
|
#region 文件上传
|
[Route("CB_WipProcessTableController/CB_WipProcessTable_Excel")]
|
[HttpPost]
|
public json CB_WipProcessTable_Excel()
|
{
|
json res = new json();
|
try
|
{
|
//获取文件名称
|
var file = HttpContext.Current.Request.Files[0];
|
//获取文件物理路径
|
string ExcelPath = HttpContext.Current.Server.MapPath("~/" + file.FileName);
|
//保存文件
|
file.SaveAs(ExcelPath);
|
|
SCGL.日计划管理.NpoiHelper np = new NpoiHelper();
|
DataSet ExcelDs = np.ReadExcel(ExcelPath, 1, 1, "0");
|
|
//删除文件
|
File.Delete(ExcelPath);
|
|
//创建临时表
|
DataTable provisional = new DataTable("dt2");
|
|
//添加列名
|
for (int i = 0; i < ExcelDs.Tables[0].Columns.Count; i++)
|
{
|
provisional.Columns.Add(ExcelDs.Tables[0].Rows[0][i].ToString());
|
}
|
//provisional.Columns.Add("单据号", typeof(string));
|
|
//添加数据
|
for (int i = 1; i < ExcelDs.Tables[0].Rows.Count; i++)
|
{
|
DataRow row = provisional.NewRow();
|
for (int j = 0; j < ExcelDs.Tables[0].Columns.Count; j++)
|
{
|
row[j] = ExcelDs.Tables[0].Rows[i][j].ToString();
|
}
|
provisional.Rows.Add(row);
|
}
|
|
//判断列
|
string error = JudgmentColumns(provisional);
|
if (error.Length > 0)
|
{
|
res.code = "0";
|
res.count = 0;
|
res.Message = $"Excel模板存在错误,{error}\r\n";
|
res.data = null;
|
return res;
|
}
|
|
for (int i = 0; i <= provisional.Rows.Count - 1; i++)
|
{
|
|
string HProcName = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["产出工序"].ToString());
|
string HCusName = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["客户"].ToString());
|
|
string HPartNumber = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["成品料号"].ToString());
|
string HWIPQty = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["WIP数量"].ToString());
|
|
string HInProcessWIPQty = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["在制WIP数量"].ToString());
|
|
string HPanelQty = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["拼版数"].ToString());
|
string HLot = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["LOT"].ToString());
|
|
string HOrderTime = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["下单时间"].ToString());
|
|
string HStartTime = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["投产时间"].ToString());
|
string HOutputTime = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["产出时间"].ToString());
|
|
string HProductionTime = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["生产时间"].ToString());
|
string HStatus = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["状态"].ToString());
|
|
string HStayTime = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["停留时间(分钟)"].ToString());
|
|
//string remark = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["备注"].ToString());
|
|
//获取真实行数
|
int line = i + 1;
|
}
|
|
res.code = "1";
|
res.count = 1;
|
res.Message = error;
|
res.data = provisional;
|
return res;
|
}
|
catch (Exception e)
|
{
|
res.code = "0";
|
res.count = 0;
|
res.Message = "Exception！" + e.ToString();
|
res.data = null;
|
return res;
|
}
|
}
|
|
/// <summary>
|
/// 判断列
|
/// </summary>
|
/// <param name="provisional"></param>
|
/// <returns></returns>
|
private static string JudgmentColumns(DataTable provisional)
|
{
|
var error = "";
|
|
//查询没有的列
|
if (!provisional.Columns.Contains("客户"))
|
error += "没有找到【客户】的标题,";
|
|
if (!provisional.Columns.Contains("成品料号"))
|
error += "没有找到【成品料号】的标题,";
|
return error;
|
}
|
#endregion
|
|
#region 上传保存
|
[Route("CB_WipProcessTableController/Excel_btnSave")]
|
[HttpPost]
|
public object Excel_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();
|
string organ = sArray[2].ToString();
|
json res = new json();
|
|
try
|
{
|
oCN.BeginTran();
|
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);
|
}
|
|
// 4. 查询用户信息
|
DataSet emp = oCN.RunProcReturn($"SELECT Czybm FROM Gy_Czygl WHERE Czymc = '{user.Replace("'", "''")}'", "Gy_Czygl");
|
if (emp.Tables[0].Rows.Count == 0)
|
{
|
res.code = "0";
|
res.count = 0;
|
res.Message = "用户不存在！";
|
res.data = null;
|
return res;
|
}
|
string HUserID = emp.Tables[0].Rows[0]["Czybm"].ToString();
|
|
StringBuilder errorMessage = new StringBuilder();
|
StringBuilder insertSql = new StringBuilder();
|
StringBuilder updateSql = new StringBuilder();
|
string currentDate = DateTime.Today.ToString("yyyy-MM-dd");
|
int HEntryID = 1;
|
|
// 遍历数据并验证
|
for (int i = 0; i < list.Count; i++)
|
{
|
var item = list[i];
|
|
List<string> intFields = new List<string>
|
{
|
"WIP数量", "在制WIP数量", "拼版数"
|
};
|
|
bool hasError = false;
|
foreach (var field in intFields)
|
{
|
if (item.ContainsKey(field))
|
{
|
string value = item[field];
|
// 检查是否为整数
|
if (!string.IsNullOrWhiteSpace(value))
|
{
|
if (!int.TryParse(value, out _))
|
{
|
errorMessage.AppendLine($"第{i + 1}行数据错误：字段 '{field}' 的值 '{value}' 不是有效的整数");
|
hasError = true;
|
}
|
}
|
}
|
}
|
|
// 如果有错误，跳过插入
|
if (hasError)
|
{
|
continue;
|
}
|
|
|
insertSql.AppendLine($"INSERT INTO CB_WipProcessTable ([HYear], [HPeriod], [HDay], [HProcName], [HCusName], [HPartNumber], [HWIPQty], [HInProcessWIPQty], [HPanelQty], [HLot], [HOrderTime], [HStartTime], [HOutputTime], [HProductionTime], [HStatus], [HStayTime], [HChecker], [HCheckDate], [HMaker], [HMakeDate], [HUpDater], [HUpDateDate], [HCloseMan], [HCloseDate], [HCloseType], [HDeleteMan], [HDeleteDate], [HReamrk]) VALUES (");
|
insertSql.AppendLine($" {item["年份"]}, {item["月份"]}, {DateTime.Now.Day}, '{item["产出工序"]}', '{item["客户"]}', '{item["成品料号"]}', '{item["WIP数量"]}', '{item["在制WIP数量"]}', '{item["拼版数"]}', '{item["LOT"]}', '{item["下单时间"]}', '{item["投产时间"]}', '{item["产出时间"]}', '{item["生产时间"]}', '{item["状态"]}', '{item["停留时间(分钟)"]}', '{""}', '{""}', '{user}', '{DateTime.Now}', '{""}', '{DateTime.Now}', '{""}', '{""}', '{"0"}', '{""}', '{""}', '{"excel导入"}');");
|
}
|
|
if (errorMessage.Length > 0)
|
{
|
//oCN.Rollback();
|
res.code = "0";
|
res.count = 0;
|
res.Message = "数据验证失败：\n" + errorMessage.ToString();
|
res.data = null;
|
return res;
|
}
|
|
if (insertSql.Length == 0)
|
{
|
// oCN.Rollback();
|
res.code = "0";
|
res.count = 0;
|
res.Message = "没有有效的整数数据可以导入";
|
res.data = null;
|
return res;
|
}
|
|
// 执行批量操作
|
if (insertSql.Length > 0)
|
{
|
oCN.RunProc(insertSql.ToString());
|
}
|
|
//if (updateSql.Length > 0)
|
//{
|
// oCN.RunProc(updateSql.ToString());
|
// oCN.RunProc(updateSql_sub.ToString());
|
//}
|
|
oCN.Commit();
|
|
res.code = "1";
|
res.count = 1;
|
res.Message = "导入成功!";
|
res.data = null;
|
return res;
|
}
|
catch (Exception e)
|
{
|
//oCN.Rollback();
|
LogService.Write(e);
|
res.code = "0";
|
res.count = 0;
|
res.Message = "Exception！" + e.ToString();
|
res.data = null;
|
return res;
|
}
|
}
|
#endregion
|
}
|
}
|
