using Newtonsoft.Json;
|
using Newtonsoft.Json.Linq;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.IO;
|
using System.Linq;
|
using System.Net;
|
using System.Net.Http;
|
using System.Text.RegularExpressions;
|
using System.Web;
|
using System.Web.Http;
|
using WebAPI.Code;
|
using WebAPI.Models;
|
using WebAPI.Utility;
|
namespace WebAPI.Controllers
|
{
|
public class MaterialHandingController : ApiController
|
{
|
// GET: MaterialHanding
|
private JsonResult objJsonResult = new JsonResult();
|
private json objjson = new json();
|
SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();
|
DataSet ds;
|
//获取系统参数
|
Pub_Class.ClsXt_SystemParameter oSystemParameter = new Pub_Class.ClsXt_SystemParameter();
|
[Route("MaterialHandingController/GetMaterialHandingList")]
|
[HttpGet]
|
public object GetMaterialHandingList(string sWhere, string user)
|
{
|
List<object> columnNameList = new List<object>();
|
try
|
{
|
string VsWhere = sWhere;
|
SQLHelper.ClsCN oCn = new SQLHelper.ClsCN();
|
DataSet ds;
|
//其他过滤
|
//string sql = " where 1 = 1 " + sqlWhere;
|
////转换特殊字符
|
//if (Common.SQLtoChange(sql, ref VsWhere, ref PcWhere) == false)
|
//{
|
// objjson.code = "0";
|
// objjson.count = 0;
|
// objjson.Message = "转换特殊字符失败";
|
// objjson.data = null;
|
// return objjson;
|
//}
|
|
//获取返回数据
|
ds = oCn.RunProcReturn("select * from h_v_Gy_UserMaterRelation where 1=1 " + sWhere, "h_v_Gy_UserMaterRelation");
|
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 == null || ds.Tables[0].Rows.Count == 0)
|
{
|
objjson.code = "0";
|
objjson.count = 0;
|
objjson.Message = "获取失败,没有相关数据";
|
objjson.data = null;
|
return objjson;
|
}
|
else
|
{
|
objjson.code = "0";
|
objjson.count = 10000;
|
objjson.Message = "获取成功";
|
objjson.data = ds.Tables[0];
|
objjson.list = columnNameList;
|
return objjson;
|
}
|
}
|
catch (Exception e)
|
{
|
objjson.code = "0";
|
objjson.count = 0;
|
objjson.Message = "获取失败" + e.ToString();
|
objjson.data = null;
|
return objjson;
|
}
|
}
|
|
#region 启用
|
|
[Route("MaterialHandingController/OpenMaterial")]
|
[HttpGet]
|
public object OpenMaterial(int HInterID, int IsUse, string CurUserName,string HMaterID)
|
{
|
try
|
{
|
var ds = oCN.RunProcReturn("select * from Gy_UserMaterRelation where HItemID=" + HInterID, "Gy_UserMaterRelation");
|
var ds_1 = oCN.RunProcReturn("select * from Gy_UserMaterRelation where HMaterID=" + HMaterID+ "order by HItemID desc", "Gy_UserMaterRelation");
|
if (ds.Tables[0].Rows.Count > 0)
|
{
|
IsUse = Convert.ToInt32(ds.Tables[0].Rows[0]["HUseFlag"]) ;
|
if (IsUse == 1) //判断
|
{
|
if (ds.Tables[0].Rows[0]["HUpDater"].ToString() != "")
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "单据已启用!不能再次启用！";
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
}
|
else
|
{
|
if (ds_1.Tables[0].Rows.Count > 1)
|
{
|
string sql = "update Gy_UserMaterRelation set HUseFlag=0 where HMaterID= " + "'" + HMaterID + "'" + "and HItemID!=" + HInterID;
|
oCN.BeginTran();
|
oCN.RunProc(sql);
|
oCN.Commit();
|
|
}
|
}
|
}
|
|
else
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "单据不存在!";
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
|
oCN.BeginTran();
|
|
if (IsUse == 0) //启用判断
|
{
|
|
//禁用前控制=========================================
|
string sql1 = "exec h_p_Gy_MaterPrice_BeforeStopCtrl " + HInterID + ",'" + CurUserName + "'";
|
ds = oCN.RunProcReturn(sql1, "h_p_Gy_MaterPrice_BeforeStopCtrl");
|
if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 1;
|
objJsonResult.Message = "禁用失败!原因:禁用前判断失败，请与网络管理人员联系";
|
objJsonResult.data = null;
|
oCN.RollBack();
|
return objJsonResult;
|
}
|
|
if (ds.Tables[0].Rows[0]["HBack"].ToString() != "0")
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 1;
|
objJsonResult.Message = "禁用失败!原因:" + ds.Tables[0].Rows[0]["HRemark"].ToString(); ;
|
objJsonResult.data = null;
|
oCN.RollBack();
|
return objJsonResult;
|
}
|
//==================================================================================
|
|
|
oCN.RunProc("update Gy_UserMaterRelation set HUpDater='" + CurUserName + "',HUpDateDate=getdate(),HUseFlag=1 where HItemID=" + HInterID);
|
|
|
//禁用后控制=========================================
|
string sql2 = "exec h_p_Gy_MaterPrice_AfterStopCtrl " + HInterID + ",'" + CurUserName + "'";
|
ds = oCN.RunProcReturn(sql2, "h_p_Gy_MaterPrice_AfterStopCtrl");
|
if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 1;
|
objJsonResult.Message = "禁用失败!原因:禁用后判断失败，请与网络管理人员联系";
|
objJsonResult.data = null;
|
oCN.RollBack();
|
return objJsonResult;
|
}
|
|
if (ds.Tables[0].Rows[0]["HBack"].ToString() != "0")
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 1;
|
objJsonResult.Message = "禁用失败!原因:" + ds.Tables[0].Rows[0]["HRemark"].ToString(); ;
|
objJsonResult.data = null;
|
oCN.RollBack();
|
return objJsonResult;
|
}
|
//==================================================================================
|
|
|
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = "启用成功";
|
objJsonResult.data = null;
|
|
|
|
}
|
//if (IsStop == 1) //反禁用判断
|
//{
|
// //反禁用前控制=========================================
|
// string sql1 = "exec h_p_Gy_Gy_MaterPrice_BeforeUnStopCtrl " + HInterID + ",'" + CurUserName + "'";
|
// ds = oCN.RunProcReturn(sql1, "h_p_Gy_Gy_MaterPrice_BeforeUnStopCtrl");
|
// if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
|
// {
|
// objJsonResult.code = "0";
|
// objJsonResult.count = 1;
|
// objJsonResult.Message = "反禁用失败!原因:反禁用前判断失败，请与网络管理人员联系";
|
// objJsonResult.data = null;
|
// oCN.RollBack();
|
// return objJsonResult;
|
// }
|
|
// if (ds.Tables[0].Rows[0]["HBack"].ToString() != "0")
|
// {
|
// objJsonResult.code = "0";
|
// objJsonResult.count = 1;
|
// objJsonResult.Message = "反禁用失败!原因:" + ds.Tables[0].Rows[0]["HRemark"].ToString(); ;
|
// objJsonResult.data = null;
|
// oCN.RollBack();
|
// return objJsonResult;
|
// }
|
// //==================================================================================
|
|
|
// oCN.RunProc("update Gy_MaterPrice set HStopEmp='',HStopTime=null,HStopflag=0 where HItemID=" + HInterID);
|
|
|
// //反禁用后控制=========================================
|
// string sql2 = "exec h_p_Gy_MaterPrice_AfterUnStopCtrl " + HInterID + ",'" + CurUserName + "'";
|
// ds = oCN.RunProcReturn(sql2, "h_p_Gy_MaterPrice_AfterUnStopCtrl");
|
// if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
|
// {
|
// objJsonResult.code = "0";
|
// objJsonResult.count = 1;
|
// objJsonResult.Message = "反禁用失败!原因:反禁用后判断失败，请与网络管理人员联系";
|
// objJsonResult.data = null;
|
// oCN.RollBack();
|
// return objJsonResult;
|
// }
|
|
// if (ds.Tables[0].Rows[0]["HBack"].ToString() != "0")
|
// {
|
// objJsonResult.code = "0";
|
// objJsonResult.count = 1;
|
// objJsonResult.Message = "反禁用失败!原因:" + ds.Tables[0].Rows[0]["HRemark"].ToString(); ;
|
// objJsonResult.data = null;
|
// oCN.RollBack();
|
// return objJsonResult;
|
// }
|
// //==================================================================================
|
|
// objJsonResult.code = "1";
|
// objJsonResult.count = 1;
|
// objJsonResult.Message = "反禁用成功";
|
// objJsonResult.data = null;
|
//}
|
oCN.Commit();
|
|
return objJsonResult;
|
}
|
catch (Exception e)
|
{
|
oCN.RollBack();
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "启用失败或者反禁用失败！" + e.ToString();
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
}
|
#endregion
|
|
#region 物料校验
|
[Route("MaterialHandingController/CheckMaterial")]
|
[HttpGet]
|
public object CheckMaterial(string HMaterID, string user, int param,string OrganizationID,string SupID)
|
{
|
try
|
{
|
//ds1获取的sql后期记得改回供应商，修改人->供应商
|
var ds1 = oCN.RunProcReturn("select * from h_v_Gy_UserMaterRelationEdit where 物料代码='" + HMaterID + "'" + "and HSupID ='" + SupID + "'", "h_v_Gy_UserMaterRelationEdit");
|
var ds = oCN.RunProcReturn("select * from Gy_Material where HNumber='" + HMaterID+ "'"+ " and HUSEORGID="+"'"+ OrganizationID+"'", "Gy_Material");
|
if(ds.Tables[0].Rows.Count == 0)
|
{
|
return 2;
|
}
|
if (param == 0)
|
{
|
if (ds1.Tables[0].Rows.Count > 0)//物料重复，弹出询问
|
{
|
return 1;
|
|
}
|
else
|
{
|
return GetMaterialResult(ds);
|
}
|
}
|
else
|
{
|
oCN.BeginTran();
|
string sqlUpd = "UPDATE a set a.HUseFlag=0 from Gy_UserMaterRelation a with(nolock) left join Gy_Material m with(nolock) " +
|
"on a.HMaterID = m.HItemID where a.HSupID=" + SupID + " and m.HNumber = '" + HMaterID + "'";
|
oCN.RunProc(sqlUpd);
|
oCN.Commit();
|
|
return GetMaterialResult(ds);
|
}
|
}
|
catch (Exception e)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "Exception！" + e.ToString();
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
}
|
|
private object GetMaterialResult(DataSet ds)
|
{
|
if (ds.Tables[0].Rows.Count > 0)
|
{
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = "Success！";
|
objJsonResult.data = ds.Tables[0];
|
return objJsonResult;
|
}
|
else
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "物料不存在！";
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
}
|
#endregion
|
|
#region 编辑查看信息
|
[Route("MaterialHandingController/Edit_M")]
|
[HttpGet]
|
public object Edit_M(string HItemID, string user)
|
{
|
try
|
{
|
List<object> columnNameList = new List<object>();
|
string sql1 = string.Format(@"select * from h_v_Gy_UserMaterRelationEdit where HItemID = " + HItemID);
|
|
ds = oCN.RunProcReturn(sql1, "h_v_Gy_UserMaterRelationEdit");
|
|
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = "Sucess！";
|
objJsonResult.data = ds.Tables[0];
|
return objJsonResult;
|
}
|
catch (Exception e)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "Exception！" + e.ToString();
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
}
|
#endregion
|
|
#region 新增保存
|
[Route("MaterialHandingController/AddBill")]
|
[HttpPost]
|
public object AddBill([FromBody] JObject msg)
|
{
|
try
|
{
|
//var _value = msg["msg"].ToString();
|
//string msg3 = _value.ToString();
|
////string[] sArray = msg3.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
|
////string msg1 = sArray[0].ToString();
|
////string msg2 = sArray[1].ToString();
|
//string msg2 = msg3.ToString();
|
|
var _value = msg["msg"].ToString();
|
string msg1 = _value.ToString();
|
string[] sArray = msg1.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
|
string msg2 = sArray[0].ToString();//
|
string OrganizationID = sArray[1].ToString();//
|
|
//反序列化
|
msg2 = "[" + msg2.ToString() + "]";
|
|
List<Model.Cls_MaterialHandingModel> list = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Model.Cls_MaterialHandingModel>>(msg2);
|
|
var ds = oCN.RunProcReturn("select * from Gy_Material where HNumber='" + list[0].HMaterNumber + "' and HUSEORGID = " + OrganizationID, "Gy_Material");
|
|
int HItemID =Convert.ToInt32(list[0].HItemID);
|
string HMaterNumber = list[0].HMaterNumber;
|
int HMaterID =Convert.ToInt32(ds.Tables[0].Rows[0]["HItemID"]);
|
int HSupID = list[0].HSupID;
|
string HUserID = list[0].HUserID;
|
decimal HMinPackQty = list[0].HMinPackQty;
|
decimal HInBoxPackQty = list[0].HInBoxPackQty;
|
decimal HOutBoxPackQty = list[0].HOutBoxPackQty;
|
int HUSEORGID = list[0].HUSEORGID;
|
bool HUseFlag = list[0].HUseFlag;
|
string HMaker = list[0].HMaker!=""? list[0].HMaker :"";
|
string HMakeTime = list[0].HMakeTime;
|
string HUpDater = list[0].HUpDater != "" ? list[0].HUpDater : "";
|
string HUpDateDate = list[0].HUpDateDate;
|
string HCheckEmp = list[0].HCheckEmp != "" ? list[0].HCheckEmp : "";
|
string HCheckTime = list[0].HCheckTime;
|
|
oCN.BeginTran();
|
|
#region 参数化
|
// 构建参数化 SQL 语句
|
string sql = @"INSERT INTO Gy_UserMaterRelation
|
(HMaterID, HSupID, HMinPackQty, HInBoxPackQty, HOutBoxPackQty, HUSEORGID, HUseFlag,
|
HMaker, HMakeTime, HUpDater, HUpDateDate, HCheckEmp, HCheckTime)
|
VALUES(@HMaterID, @HSupID, @HMinPackQty, @HInBoxPackQty, @HOutBoxPackQty, @HUSEORGID, @HUseFlag,
|
@HMaker, @HMakeTime, @HUpDater, @HUpDateDate, @HCheckEmp, @HCheckTime)";
|
|
// 使用 MakeInParam 方法创建参数
|
SqlParameter[] parameters = {
|
oCN.MakeInParam("@HMaterID", SqlDbType.Int, 4, HMaterID),
|
oCN.MakeInParam("@HSupID", SqlDbType.Int, 4, HSupID),
|
//oCN.MakeInParam("@HUserID", SqlDbType.NVarChar, 50, HUserID),
|
oCN.MakeInParam("@HMinPackQty", SqlDbType.Decimal, 8, HMinPackQty),
|
oCN.MakeInParam("@HInBoxPackQty", SqlDbType.Decimal, 8, HInBoxPackQty),
|
oCN.MakeInParam("@HOutBoxPackQty", SqlDbType.Decimal, 8, HOutBoxPackQty),
|
oCN.MakeInParam("@HUSEORGID", SqlDbType.Int, 4, HUSEORGID),
|
oCN.MakeInParam("@HUseFlag", SqlDbType.Bit, 1, HUseFlag),
|
oCN.MakeInParam("@HMaker", SqlDbType.NVarChar, 50, (object)HMaker ?? DBNull.Value),
|
oCN.MakeInParam("@HMakeTime", SqlDbType.NVarChar, 50, (object)HMakeTime ?? DBNull.Value),
|
oCN.MakeInParam("@HUpDater", SqlDbType.NVarChar, 50, (object)HUpDater ?? DBNull.Value),
|
oCN.MakeInParam("@HUpDateDate", SqlDbType.NVarChar, 50, (object)HUpDateDate ?? DBNull.Value),
|
oCN.MakeInParam("@HCheckEmp", SqlDbType.NVarChar, 50, (object)HCheckEmp ?? DBNull.Value),
|
oCN.MakeInParam("@HCheckTime", SqlDbType.NVarChar, 50, (object)HCheckTime ?? DBNull.Value)
|
|
};
|
|
DataSet result = oCN.RunProcReturn(sql, parameters, "Gy_UserMaterRelation");
|
#endregion
|
|
|
oCN.Commit();
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = "新增成功！";
|
//objJsonResult.data = null;
|
return objJsonResult;
|
}
|
catch (Exception e)
|
{
|
LogService.Write("送货物料信息保存异常：" + e.Message);
|
oCN.RollBack();
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "Exception！" + e.Message;
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
}
|
#endregion
|
|
#region 编辑保存
|
[Route("MaterialHandingController/EditSaveBill")]
|
[HttpPost]
|
public object EditSaveBill([FromBody] JObject msg)
|
{
|
try
|
{
|
var _value = msg["msg"].ToString();
|
string msg3 = _value.ToString();
|
string msg2 = msg3.ToString();
|
|
oCN.BeginTran();
|
//反序列化
|
msg2 = "[" + msg2.ToString() + "]";
|
|
List<Model.Cls_MaterialHandingModel> list = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Model.Cls_MaterialHandingModel>>(msg2);
|
|
|
int HItemID = Convert.ToInt32(list[0].HItemID);
|
int HMaterID = Convert.ToInt32(list[0].HMaterNumber);
|
int HSupID = list[0].HSupID;
|
//string HUserID = list[0].HUserID;
|
decimal HMinPackQty = list[0].HMinPackQty;
|
decimal HInBoxPackQty = list[0].HInBoxPackQty;
|
decimal HOutBoxPackQty = list[0].HOutBoxPackQty;
|
int HUSEORGID = list[0].HUSEORGID;
|
bool HUseFlag = list[0].HUseFlag;
|
string HMaker = list[0].HMaker != "" ? list[0].HMaker : "";
|
string HMakeTime = list[0].HMakeTime;
|
string HUpDater = list[0].HUpDater != "" ? list[0].HUpDater : "";
|
string HUpDateDate = list[0].HUpDateDate;
|
string HCheckEmp = list[0].HCheckEmp != "" ? list[0].HCheckEmp : "";
|
string HCheckTime = list[0].HCheckTime;
|
|
|
|
#region
|
//主表
|
string sql = @"UPDATE Gy_UserMaterRelation SET
|
HMaterID = @HMaterID,
|
HSupID = @HSupID,
|
HMinPackQty = @HMinPackQty,
|
HInBoxPackQty = @HInBoxPackQty,
|
HUSEORGID = @HUSEORGID,
|
HUseFlag = @HUseFlag,
|
HMaker = @HMaker,
|
HMakeTime = @HMakeTime,
|
HUpDater = @HUpDater,
|
HUpDateDate = @HUpDateDate,
|
HCheckEmp = @HCheckEmp,
|
HCheckTime = @HCheckTime
|
WHERE HItemID = @HItemID";
|
|
SqlParameter[] parameters = {
|
oCN.MakeInParam("@HItemID", SqlDbType.Int, 4, HItemID),
|
oCN.MakeInParam("@HMaterID", SqlDbType.Int, 4, HMaterID),
|
oCN.MakeInParam("@HSupID", SqlDbType.Int, 4, HSupID),
|
//oCN.MakeInParam("@HUserID", SqlDbType.NVarChar, 50, HUserID),
|
oCN.MakeInParam("@HMinPackQty", SqlDbType.Decimal, 8, HMinPackQty),
|
oCN.MakeInParam("@HInBoxPackQty", SqlDbType.Decimal, 8, HInBoxPackQty),
|
oCN.MakeInParam("@HOutBoxPackQty", SqlDbType.Decimal, 8, HOutBoxPackQty),
|
oCN.MakeInParam("@HUSEORGID", SqlDbType.Int, 4, HUSEORGID),
|
oCN.MakeInParam("@HUseFlag", SqlDbType.Bit, 1, HUseFlag),
|
oCN.MakeInParam("@HMaker", SqlDbType.NVarChar, 50, (object)HMaker ?? DBNull.Value),
|
oCN.MakeInParam("@HMakeTime", SqlDbType.NVarChar, 50, (object)HMakeTime ?? DBNull.Value),
|
oCN.MakeInParam("@HUpDater", SqlDbType.NVarChar, 50, (object)HUpDater ?? DBNull.Value),
|
oCN.MakeInParam("@HUpDateDate", SqlDbType.NVarChar, 50, (object)HUpDateDate ?? DBNull.Value),
|
oCN.MakeInParam("@HCheckEmp", SqlDbType.NVarChar, 50, (object)HCheckEmp ?? DBNull.Value),
|
oCN.MakeInParam("@HCheckTime", SqlDbType.NVarChar, 50, (object)HCheckTime ?? DBNull.Value),
|
};
|
|
// 使用 RunProcReturn 执行参数化UPDATE查询，忽略返回的 DataSet
|
DataSet result = oCN.RunProcReturn(sql, parameters, "Gy_UserMaterRelation");
|
#endregion
|
|
oCN.Commit();
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = "更新成功！";
|
return objJsonResult;
|
|
}
|
catch (Exception e)
|
{
|
oCN.RollBack();
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "Exception！" + e.Message;
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
}
|
#endregion
|
|
//#region 供应商报价单 删除
|
[Route("MaterialHandingController/DelBill")]
|
[HttpGet]
|
public object DelBill(string HItemID, string user)
|
{
|
|
try
|
{
|
if (HItemID == null || HItemID.Equals(""))
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "HInterID不能为空！";
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
|
oCN.BeginTran();
|
|
// oCN.RunProc("delete from Gy_UserMaterRelation where HInterID = " + HItemID);
|
oCN.RunProc("delete from Gy_UserMaterRelation where HItemID='" + HItemID + "'");
|
|
oCN.Commit();
|
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = "Sucess！";
|
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
|
#region 主页面启用
|
[Route("MaterialHandingController/CheckMaterialCount")]
|
[HttpGet]
|
public object CheckMaterialCount(string HMaterID, string HSupID, int HInterID)
|
{
|
try
|
{
|
//检查同供应商下是否有其他物料
|
string sql = "SELECT COUNT(*) as Count FROM Gy_UserMaterRelation " +
|
"WHERE HMaterID = '" + HMaterID + "' AND HSupID = " + HSupID +
|
" AND HItemID != " + HInterID;
|
var ds = oCN.RunProcReturn(sql, "Gy_UserMaterRelation");
|
int count = Convert.ToInt32(ds.Tables[0].Rows[0]["Count"]);
|
|
objJsonResult.code = "1";
|
objJsonResult.count = count;
|
objJsonResult.Message = "查询成功";
|
return objJsonResult;
|
}
|
catch (Exception e)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "查询失败: " + e.Message;
|
return objJsonResult;
|
}
|
}
|
|
[Route("MaterialHandingController/OpenMaterial")]
|
[HttpGet]
|
public object OpenMaterial(int HInterID, int IsUse, string CurUserName, string HMaterID, string HSupID, int disableOthers)
|
{
|
try
|
{
|
var ds = oCN.RunProcReturn("SELECT * FROM Gy_UserMaterRelation WHERE HItemID=" + HInterID, "Gy_UserMaterRelation");
|
|
if (ds.Tables[0].Rows.Count == 0)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "单据不存在!";
|
return objJsonResult;
|
}
|
|
int currentUseFlag = Convert.ToInt32(ds.Tables[0].Rows[0]["HUseFlag"]);
|
|
// 检查是否已启用
|
if (currentUseFlag == 1)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "单据已启用!不能再次启用！";
|
return objJsonResult;
|
}
|
|
oCN.BeginTran();
|
|
// 如果用户选择禁用
|
if (disableOthers == 1)
|
{
|
string disableSql = "UPDATE Gy_UserMaterRelation SET HUseFlag=0 " +
|
"WHERE HMaterID='" + HMaterID + "' AND HSupID=" + HSupID +
|
" AND HItemID!=" + HInterID;
|
oCN.RunProc(disableSql);
|
}
|
|
// 启用前控制
|
string sql1 = "EXEC h_p_Gy_MaterPrice_BeforeStopCtrl " + HInterID + ",'" + CurUserName + "'";
|
ds = oCN.RunProcReturn(sql1, "h_p_Gy_MaterPrice_BeforeStopCtrl");
|
|
if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "启用失败!原因:启用前判断失败，请与网络管理人员联系";
|
oCN.RollBack();
|
return objJsonResult;
|
}
|
|
if (ds.Tables[0].Rows[0]["HBack"].ToString() != "0")
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "启用失败!原因:" + ds.Tables[0].Rows[0]["HRemark"].ToString();
|
oCN.RollBack();
|
return objJsonResult;
|
}
|
|
// 启用当前记录
|
oCN.RunProc("UPDATE Gy_UserMaterRelation SET HUpDater='" + CurUserName +
|
"', HUpDateDate=GETDATE(), HUseFlag=1 WHERE HItemID=" + HInterID);
|
|
// 启用后控制
|
string sql2 = "EXEC h_p_Gy_MaterPrice_AfterStopCtrl " + HInterID + ",'" + CurUserName + "'";
|
ds = oCN.RunProcReturn(sql2, "h_p_Gy_MaterPrice_AfterStopCtrl");
|
|
if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "启用失败!原因:启用后判断失败，请与网络管理人员联系";
|
oCN.RollBack();
|
return objJsonResult;
|
}
|
|
if (ds.Tables[0].Rows[0]["HBack"].ToString() != "0")
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "启用失败!原因:" + ds.Tables[0].Rows[0]["HRemark"].ToString();
|
oCN.RollBack();
|
return objJsonResult;
|
}
|
|
oCN.Commit();
|
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = "启用成功";
|
return objJsonResult;
|
}
|
catch (Exception e)
|
{
|
oCN.RollBack();
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "启用失败!" + e.Message;
|
return objJsonResult;
|
}
|
}
|
#endregion
|
|
[Route("MaterialHandingController/list")]
|
[HttpGet]
|
public object list(string sWhere, string user, string Organization)
|
{
|
try
|
{
|
string sql1 = "";
|
List<object> columnNameList = new List<object>();
|
if (user == "admin")
|
{
|
sql1 = string.Format(@"select * from h_v_UserSupplierRelationMater where 1=1");
|
}
|
else
|
{
|
sql1 = string.Format(@"select * from h_v_UserSupplierRelationMater where 用户名称='" + user + "'");
|
}
|
|
if (sWhere == null || sWhere.Equals(""))
|
{
|
ds = oCN.RunProcReturn(sql1 + sWhere, "h_v_UserSupplierRelationMater");
|
}
|
else
|
{
|
string sql = sql1 + sWhere;
|
ds = oCN.RunProcReturn(sql, "h_v_UserSupplierRelationMater");
|
}
|
|
//添加列名
|
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列对象的列名
|
}
|
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = "Sucess！";
|
objJsonResult.data = ds.Tables[0];
|
objJsonResult.list = columnNameList;
|
return objJsonResult;
|
}
|
catch (Exception e)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "Exception！" + e.ToString();
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
}
|
|
#region 送货物料信息 文件上传
|
[Route("Gy_SupMaterPack/SupMaterPack_Excel")]
|
[HttpPost]
|
public object SupMaterPack_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");
|
|
//添加列名
|
for (int i = 0; i < ExcelDs.Tables[0].Columns.Count; i++)
|
{
|
tb2.Columns.Add(ExcelDs.Tables[0].Rows[0][i].ToString());
|
}
|
|
//模板缺少列 但需要从数据库中查询出来显示在页面的字段
|
tb2.Columns.Add("HMaterID", typeof(Int32));//物料ID
|
tb2.Columns.Add("HSupID", typeof(Int32));//供应商ID
|
tb2.Columns.Add("HUSEORGID", 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();
|
}
|
//如果表格第i行的第一列为空，则判断为这一行的数据为空，跳出循环并且不把数据写入 tb2
|
if (ExcelDs.Tables[0].Rows[i][0].ToString() == "")
|
{
|
continue;
|
}
|
else
|
{
|
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 (!tb2.Columns.Contains("规格型号"))
|
error += "没有找到【规格型号】的标题,";
|
|
if (!tb2.Columns.Contains("标准包装数量"))
|
error += "没有找到【标准包装数量】的标题,";
|
|
if (!tb2.Columns.Contains("最小包装数量"))
|
error += "没有找到【最小包装数量】的标题,";
|
|
if (error.Length > 0)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = $"Excel模板存在错误,{error}\r\n";
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
|
string HOrgNumber = "";
|
string HOrgName = "";
|
string HSupNumber = "";
|
string HSupName = "";
|
string HMaterNumber = "";
|
string HMaterName = "";
|
string HMaterModel = "";
|
double HInBoxPackQty = 0;
|
double HMinPackQty = 0;
|
|
for (int i = 0; i <= tb2.Rows.Count - 1; i++)
|
{
|
HOrgNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["组织代码"].ToString());
|
HOrgName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["组织名称"].ToString());
|
HSupNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["供应商代码"].ToString());
|
HSupName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["供应商名称"].ToString());
|
HMaterNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["物料代码"].ToString());
|
HMaterName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["物料名称"].ToString());
|
HMaterModel = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["规格型号"].ToString());
|
HInBoxPackQty = DBUtility.ClsPub.isDoule(tb2.Rows[i]["标准包装数量"].ToString());
|
HMinPackQty = DBUtility.ClsPub.isDoule(tb2.Rows[i]["最小包装数量"].ToString());
|
|
//检查表格数据
|
int index = i + 1;
|
|
if (HOrgNumber != "" && HSupNumber != "" && HMaterNumber != "" && HInBoxPackQty != 0)
|
{
|
//查询组织
|
ds = oCN.RunProcReturn("select HItemID from Xt_ORGANIZATIONS org with(nolock) where HNumber='" + HOrgNumber + "'", "Xt_ORGANIZATIONS");
|
|
if (ds.Tables[0].Rows.Count == 0)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "第" + index + "行,组织不存在！" + "组织代码：" + HOrgNumber;
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
else
|
{
|
tb2.Rows[i]["HUSEORGID"] = ds.Tables[0].Rows[0]["HItemID"].ToString();
|
}
|
|
|
//查询物料
|
ds = oCN.RunProcReturn("select m.HItemID from Gy_Material m with(nolock) " +
|
"left join Xt_ORGANIZATIONS org with(nolock) on m.HUSEORGID = org.HItemID " +
|
"where m.HNumber='" + HMaterNumber + "' and org.HNumber='" + HOrgNumber + "'"
|
, "Gy_Material");
|
|
if (ds.Tables[0].Rows.Count == 0)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "第" + index + "行,物料不存在！" + "使用组织代码：" + HOrgNumber + " ，物料代码：" + HMaterNumber;
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
else
|
{
|
tb2.Rows[i]["HMaterID"] = ds.Tables[0].Rows[0]["HItemID"].ToString();
|
}
|
|
|
//查询供应商
|
ds = oCN.RunProcReturn("select s.HItemID from Gy_Supplier s with(nolock) " +
|
"left join Xt_ORGANIZATIONS org with(nolock) on s.HUSEORGID = org.HItemID " +
|
"where s.HNumber='" + HSupNumber + "' and org.HNumber='" + HOrgNumber + "'"
|
, "Gy_Supplier");
|
|
if (ds.Tables[0].Rows.Count == 0)
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "第" + index + "行,供应商不存在！" + "使用组织代码：" + HOrgNumber + " ，供应商代码：" + HSupNumber; ;
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
else
|
{
|
tb2.Rows[i]["HSupID"] = ds.Tables[0].Rows[0]["HItemID"].ToString();
|
}
|
}
|
else
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "第" + index + "行,信息不全（组织代码,供应商代码,物料代码,标准包装数量）";
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
}
|
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = error;
|
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("Gy_SupMaterPack/SupMaterPack_btnSave")]
|
[HttpPost]
|
public object SupMaterPack_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("Gy_SupMaterPack_Query", 1, false, user))
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
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;
|
foreach (Dictionary<string, string> item in list)
|
{
|
string HSupID = item["HSupID"].ToString();//供应商
|
string HSupNumber = item["供应商代码"].ToString();//供应商代码
|
string HMaterID = item["HMaterID"].ToString();//物料
|
string HUSEORGID = item["HUSEORGID"].ToString();//组织
|
string HInBoxPackQty = item["标准包装数量"].ToString();//标准包装数量
|
string HMinPackQty = item["最小包装数量"].ToString();//最小包装数量
|
|
//拼接新增语句
|
string sql = "insert into Gy_UserMaterRelation(HMaterID,HUserID,HMinPackQty,HInBoxPackQty,HUSEORGID,HUseFlag,HSupID,HMaker,HMakeTime)" +
|
$"values({HMaterID}, '{HSupNumber}',{HMinPackQty}, {HInBoxPackQty}, {HUSEORGID},'1',{HSupID}, '{user}',getdate())";
|
|
//查询此供应商是否已经添加过送货物料信息
|
ds = oCN.RunProcReturn("select HInBoxPackQty from Gy_UserMaterRelation with(nolock) where HSupID = " + HSupID + " and HMaterID = " + HMaterID + " and HUSEORGID = " + HUSEORGID, "Gy_UserMaterRelation");
|
|
if (ds.Tables[0].Rows.Count == 0)
|
{
|
//执行新增语句
|
oCN.RunProc(sql);
|
}
|
else
|
{
|
var HIsReturn = "false";
|
|
for (var k = 0; k < ds.Tables[0].Rows.Count; k++)
|
{
|
if (Convert.ToDecimal(ds.Tables[0].Rows[k]["HInBoxPackQty"]) == Convert.ToDecimal(HInBoxPackQty))
|
{
|
HIsReturn = "true";
|
break;
|
}
|
}
|
|
if (HIsReturn == "true")
|
{
|
//本次excel导入的物料包装数量已经添加过，则跳过本次添加
|
continue;
|
}
|
else
|
{
|
//更新原来的送货物料信息为待启用状态
|
string sql2 = "update a set HUseFlag = 0,HUpDater = '" + user + "',HUpDateDate = getdate() from Gy_UserMaterRelation a with(nolock) where HSupID = " + HSupID + " and HMaterID = " + HMaterID + " and HUSEORGID = " + HUSEORGID;
|
//执行更新语句
|
oCN.RunProc(sql2);
|
|
//执行新增语句
|
oCN.RunProc(sql);
|
}
|
}
|
|
i++;
|
}
|
|
oCN.Commit();
|
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = "导入成功!";
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
catch (Exception e)
|
{
|
LogService.Write(e);
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = "Exception！" + e.ToString();
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
}
|
#endregion
|
|
}
|
}
|
