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.IO;
|
using System.Text;
|
using System.Web;
|
using System.Web.Http;
|
using WebAPI.Controllers.SCGL.日计划管理;
|
using WebAPI.Models;
|
using WebAPI.Service;
|
|
namespace WebAPI.Controllers.基础资料.基础资料
|
{
|
public class Cb_OtherOutBill_CostSubjectDetailController : ApiController
|
{
|
// GET: Cb_OtherOutBill_CostSubjectDetail
|
public DBUtility.ClsPub.Enum_BillStatus BillStatus;
|
|
private json objJsonResult = new json();
|
SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();
|
DataSet ds;
|
[Route("Cb_OtherOutBill_CostSubjectDetail/list")]
|
[HttpGet]
|
public object list(string sWhere, string user)
|
{
|
try
|
{
|
List<object> columnNameList = new List<object>();
|
//判断是否有查询权限
|
if (!DBUtility.ClsPub.Security_Log("Gy_CostAverageType_Query", 1, false, user))
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "无权限查询!";
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
|
if (sWhere == null || sWhere.Equals(""))
|
{
|
ds = oCN.RunProcReturn("select * from h_v_Cb_OtherOutBill_CostSubjectDetail where 1=1 order by 日期 desc ", "h_v_Cb_OtherOutBill_CostSubjectDetail");
|
}
|
else
|
{
|
string sql1 = "select * from h_v_Cb_OtherOutBill_CostSubjectDetail where 1=1 ";
|
string sql = sql1 + sWhere + " order by 日期 desc";
|
ds = oCN.RunProcReturn(sql, "h_v_Cb_OtherOutBill_CostSubjectDetail");
|
}
|
|
//添加列名
|
foreach (DataColumn col in ds.Tables[0].Columns)
|
{
|
Type dataType = col.DataType;
|
string ColmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}";
|
columnNameList.Add(JsonConvert.DeserializeObject(ColmString));//获取到DataColumn列对象的列名
|
}
|
|
//if (ds.Tables[0].Rows.Count != 0 || ds != null)
|
//{
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = "Sucess！";
|
objJsonResult.data = ds.Tables[0];
|
objJsonResult.list = columnNameList;
|
return objJsonResult;
|
//}
|
//else
|
//{
|
//objJsonResult.code = "0";
|
//objJsonResult.count = 0;
|
//objJsonResult.Message = "无数据";
|
//objJsonResult.data = null;
|
//return objJsonResult;
|
//}
|
}
|
catch (Exception e)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "Exception！" + e.ToString();
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
}
|
|
|
#region 文件上传
|
[Route("Cb_OtherOutBill_CostSubjectDetail/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 HDate = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["日期"].ToString());
|
string HBillNo = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["单据编号"].ToString());
|
string HWareHouse = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["发货仓库"].ToString());
|
string HStockPlace = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["仓位"].ToString());
|
|
string HUseType = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["领料类型"].ToString());
|
string HUseDepartment = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["领料部门"].ToString());
|
|
string HCostSubject = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["成本科目"].ToString());
|
|
string HCostSubjectSon = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["成本科目二级"].ToString());
|
string HSubjectProc = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["科目+工序"].ToString());
|
|
string HMaterNumber = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["物料编码"].ToString());
|
|
string HMaterName = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["物料名称"].ToString());
|
string HModel = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["规格型号"].ToString());
|
|
string HUnit = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["单位"].ToString());
|
string HQty = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["实发数量"].ToString());
|
|
string HCostPrice = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["成本价"].ToString());
|
string HTotalPrice = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["总成本"].ToString());
|
string HLOT = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["批号"].ToString());
|
string HRemark2 = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["备注"].ToString());
|
string HUsePerson = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["领料人"].ToString());
|
string HWareHousePerson = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["仓管员"].ToString());
|
string HAssetName = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["资产名称"].ToString());
|
string HAssetNumber = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["资产编号"].ToString());
|
string HSearchProject = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["研发项目"].ToString());
|
string HVerify = 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_OtherOutBill_CostSubjectDetail/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>
|
{
|
|
};
|
|
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;
|
}
|
if(!string.IsNullOrEmpty(item["日期"]) )
|
{
|
item["年份"]=item["日期"].Split('-')[0];
|
item["月份"] =item["日期"].Split('-')[1];
|
|
}
|
|
insertSql.AppendLine($"INSERT INTO [Cb_OtherOutBill_CostSubjectDetail] ([HYear], [HPeriod], [HBillType], [HBillSubType], [HDate], [HBillNo], [HBillStatus], [HCheckItemNowID], [HCheckItemNextID], [HCheckFlowID], [HRemark], [HBacker], [HBackDate], [HChecker], [HCheckDate], [HMaker], [HMakeDate], [HUpDater], [HUpDateDate], [HCloseMan], [HCloseDate], [HCloseType], [HDeleteMan], [HDeleteDate], [HWareHouse], [HStockPlace], [HUseType], [HUseDepartment], [HCostSubject], [HCostSubjectSon], [HSubjectProc], [HMaterNumber], [HMaterName], [HModel], [HUnit], [HQty], [HCostPrice], [HTotalPrice], [HLOT], [HRemark2], [HUsePerson], [HWareHousePerson], [HAssetName], [HAssetNumber], [HSearchProject], [HVerify]) VALUES (");
|
insertSql.AppendLine($" {item["年份"]}, {item["月份"]}, {1}, '{1}', '{item["日期"]}', '{item["单据编号"]}', '{1}', '{1}', '{1}', '{1}', '{item["备注"]}', '{""}', '{DateTime.Now}', '{""}', '{DateTime.Now}', '{""}', '{DateTime.Now}', '{""}', '{DateTime.Now}', '{""}', '{DateTime.Now}', '{"0"}', '{""}', '{DateTime.Now}', '{item["发货仓库"]}', '{item["仓位"]}', '{item["领料类型"]}', '{item["领料部门"]}', '{item["成本科目"]}', '{item["成本科目二级"]}', '{item["科目+工序"]}', '{item["物料编码"]}', '{item["物料名称"]}', '{item["规格型号"]}', '{item["单位"]}', '{item["实发数量"]}', '{item["成本价"]}', '{item["总成本"]}', '{item["批号"]}', '{"excel导入"}', '{item["领料人"]}', '{item["仓管员"]}', '{item["资产名称"]}', '{item["资产编号"]}', '{item["研发项目"]}', '{item["核对"]}');");
|
}
|
|
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
|
|
|
#region 删除
|
[Route("Cb_OtherOutBill_CostSubjectDetail/DeltetCb_OtherOutBill_CostSubjectDetail")]
|
[HttpGet]
|
public object DeltetCb_OtherOutBill_CostSubjectDetail(string HItemIDs, string user)
|
{
|
DataSet ds;
|
try
|
{
|
//删除权限
|
if (!DBUtility.ClsPub.Security_Log("Gy_CostAverageType_Drop", 1, false, user))
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "无删除权限";
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
|
SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();
|
if (string.IsNullOrWhiteSpace(HItemIDs))
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "HItemID为空！";
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
oCN.BeginTran();//开始事务
|
ds = oCN.RunProcReturn("select * from Cb_OtherOutBill_CostSubjectDetail where HInterID in (" + HItemIDs+" )", "Cb_OtherOutBill_CostSubjectDetail");
|
if (ds == null || ds.Tables[0].Rows.Count == 0)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "没有数据，无法删除！";
|
objJsonResult.data = null;
|
return objJsonResult; ;
|
}
|
else if (ds.Tables[0].Rows[0]["HChecker"] != null && ds.Tables[0].Rows[0]["HChecker"].ToString() != "")
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "已经审核不能删除！";
|
objJsonResult.data = null;
|
return objJsonResult; ;
|
}
|
else
|
{
|
|
}
|
|
|
//var HStopflag = Convert.ToBoolean(ds.Tables[0].Rows[0]["HStopflag"]);
|
//if (HStopflag)
|
//{
|
// oCN.RollBack();//回滚事务
|
// objJsonResult.code = "0";
|
// objJsonResult.count = 0;
|
// objJsonResult.Message = "数据已被禁用无法再次删除！";
|
// objJsonResult.data = null;
|
// return objJsonResult;
|
//}
|
|
oCN.RunProc("delete from Cb_OtherOutBill_CostSubjectDetail where HInterID in (" + HItemIDs + ")");
|
|
|
|
////写入日志
|
//string Operation = "删除"; //操作
|
//string ComputerName = System.Net.Dns.GetHostName(); //设备名称
|
//string WorkList = Operation + "成本分配类型，成本分配类型代码：" + HNumber + "；成本分配类型名称：" + HName; //操作详情
|
//string MvarReportTitle = "成本分配类型列表"; //操作模块
|
//string SystemName = "LMES-" + MvarReportTitle + "模块"; //操作模块
|
//string IPAddress = "";
|
////string IPAddress = Dns.GetHostEntry(Dns.GetHostName()).AddressList[0].ToString(); //IP地址
|
////string IPAddress = System.Web.HttpContext.Current.Request.UserHostAddress;
|
|
//oCN.RunProc("Insert into System_Log(GeginDate,userid,WorkstationName,WorkList,SystemName,NetUserName,State) values " +
|
// "(getdate(),'" + user + "','" + ComputerName + "','" + WorkList + "','" + SystemName + "','" + IPAddress + "','" + Operation + "')"
|
// );
|
|
|
oCN.Commit();//提交事务
|
objJsonResult.code = "0";
|
objJsonResult.count = 1;
|
objJsonResult.Message = "* 数据删除成功！";
|
objJsonResult.data = null;
|
return objJsonResult; ;
|
|
}
|
catch (Exception e)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "删除失败！" + e.ToString();
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
}
|
#endregion
|
}
|
}
|
