You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
4899 lines
210 KiB
4899 lines
210 KiB
using NFine.Data.Extensions;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using NFine.Code;
|
|
using NFine.Repository;
|
|
using System.Data.Common;
|
|
using NFine.Domain._03_Entity.SRM;
|
|
|
|
using Newtonsoft.Json;
|
|
using System.Configuration;
|
|
using System.Data.SqlClient;
|
|
|
|
using Newtonsoft.Json.Linq;
|
|
|
|
using System.Web.UI.WebControls;
|
|
using System.Runtime.InteropServices.ComTypes;
|
|
using System.Diagnostics;
|
|
using System.Net;
|
|
using System.IO;
|
|
|
|
namespace NFine.Application
|
|
{
|
|
public class BasicSettingsApp : RepositoryFactory<ICSVendor>
|
|
{
|
|
public static DataTable Invmes = new DataTable();
|
|
/// <summary>
|
|
/// 删除库位
|
|
/// </summary>
|
|
/// <param name="keyValue">传入ID</param>
|
|
/// <returns></returns>
|
|
public string DeleteStack(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
JArray res = (JArray)JsonConvert.DeserializeObject(keyValue);
|
|
//keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
foreach (var item in res)
|
|
{
|
|
JObject jo = (JObject)item;
|
|
sql = string.Format(@"
|
|
IF EXISTS(SELECT * FROM dbo.ICSWareHouseLotInfo a
|
|
LEFT JOIN dbo.ICSLocation b ON a.LocationCode=b.LocationCode and a.WorkPoint=b.WorkPoint
|
|
where b.ID = '{0}' and a.Quantity>0)
|
|
BEGIN
|
|
RAISERROR('选中库位{2}在库存中已经有库存，请先确认!',16,1);
|
|
RETURN
|
|
END
|
|
DELETE FROM dbo.ICSLocation WHERE ID = '{0}' and WorkPoint ='{1}'", jo["ID"].ToString(), WorkPoint, jo["LocationCode"].ToString());
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
//throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
|
|
|
|
return msg;
|
|
}
|
|
public void ClearTemp()
|
|
{
|
|
Invmes.Rows.Clear();
|
|
}
|
|
/// <summary>
|
|
/// 获取仓库信息
|
|
/// </summary>
|
|
/// <param name="jqgridparam"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetICSWarehouseInfoId(string ID, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select ID,WarehouseCode,WarehouseName ,Enable,BatchEnable,Asset,Proxy,InCost from ICSWarehouse where WorkPoint='{0}' ";
|
|
sql = string.Format(sql, WorkPoint);
|
|
if (!string.IsNullOrWhiteSpace(ID))
|
|
{
|
|
sql += " and Id in (" + ID + ") ";
|
|
}
|
|
|
|
DataTable table = Repository().FindDataSetBySql(sql).Tables[0];
|
|
DataTable dtCloned = table.Clone();
|
|
foreach (DataColumn col in dtCloned.Columns)
|
|
{
|
|
col.DataType = typeof(string);
|
|
}
|
|
foreach (DataRow row in table.Rows)
|
|
{
|
|
DataRow newrow = dtCloned.NewRow();
|
|
foreach (DataColumn column in dtCloned.Columns)
|
|
{
|
|
newrow[column.ColumnName] = row[column.ColumnName].ToString();
|
|
|
|
}
|
|
dtCloned.Rows.Add(newrow);
|
|
}
|
|
if (Invmes.Rows.Count > 0)
|
|
{
|
|
dtCloned.Merge(Invmes, false);
|
|
}
|
|
return dtCloned;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 删除周转箱
|
|
/// </summary>
|
|
/// <param name="keyValue">传入ID</param>
|
|
/// <returns></returns>
|
|
public string DeleteBoxes(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
JArray res = (JArray)JsonConvert.DeserializeObject(keyValue);
|
|
//keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
foreach (var item in res)
|
|
{
|
|
JObject jo = (JObject)item;
|
|
sql = $@"
|
|
IF EXISTS(SELECT * FROM dbo.ICSWorkingCapitalBoxAccount a
|
|
where a.BoxNumber='{jo["BoxNumber"]}' )
|
|
BEGIN
|
|
RAISERROR('选中库周转箱已经维护台账信息，请先确认!', 16, 1);
|
|
RETURN
|
|
END
|
|
DELETE FROM dbo.ICSWorkingCapitalBox WHERE ID = '{jo["ID"]}' ";
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
//throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 删除模具档案
|
|
/// </summary>
|
|
/// <param name="keyValue">传入ID</param>
|
|
/// <returns></returns>
|
|
public string DeleteMould(string keyValue)
|
|
{
|
|
//站点信息
|
|
string msg = "";
|
|
JArray res = (JArray)JsonConvert.DeserializeObject(keyValue);
|
|
//keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
foreach (var item in res)
|
|
{
|
|
JObject jo = (JObject)item;
|
|
sql = $@"
|
|
IF EXISTS(SELECT Id FROM dbo.ICSMouldAccount a
|
|
where a.MouldNumber='{jo["MouldNumber"]}')
|
|
BEGIN
|
|
RAISERROR('选中模具已经维护台账信息，请先确认!', 16, 1);
|
|
RETURN
|
|
END
|
|
DELETE FROM dbo.ICSMould WHERE ID = '{jo["ID"]}' ";
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
//throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
|
|
|
|
return msg;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 删除模具台账
|
|
/// </summary>
|
|
/// <param name="keyValue">传入ID</param>
|
|
/// <returns></returns>
|
|
public string DeleteMouldAccount(string keyValue)
|
|
{
|
|
//站点信息
|
|
string msg = "";
|
|
JArray res = (JArray)JsonConvert.DeserializeObject(keyValue);
|
|
//keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
foreach (var item in res)
|
|
{
|
|
JObject jo = (JObject)item;
|
|
sql = $@"
|
|
IF EXISTS(SELECT Id FROM dbo.ICSMouldLog a
|
|
where a.MouldCode='{jo["MouldCode"]}')
|
|
BEGIN
|
|
RAISERROR('选中模具已经存在使用记录，请先确认!', 16, 1);
|
|
RETURN
|
|
END
|
|
DELETE FROM dbo.ICSMouldAccount WHERE ID = '{jo["ID"]}' ";
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
//throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
|
|
|
|
return msg;
|
|
}
|
|
/// <summary>
|
|
/// 删除不良原因
|
|
/// </summary>
|
|
/// <param name="keyValue">传入ID</param>
|
|
/// <returns></returns>
|
|
public string DeleteBadCode(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
sql = string.Format(@"DELETE FROM dbo.ICSBadCode WHERE ID IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return msg;
|
|
}
|
|
/// <summary>
|
|
/// 获取库房信息
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public DataTable GetWarehouse()
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string sql = @"
|
|
select '' as WarehouseCode,'' as WarehouseName
|
|
union all
|
|
SELECT [WarehouseCode],[WarehouseName] FROM [ICSWarehouse] WITH (NOLOCK) WHERE 1=1 ";
|
|
//string role = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode;
|
|
//if (role != "admin")
|
|
//{
|
|
// sql += " and b.WorkPoint='" + WorkPoint + "'";
|
|
//}
|
|
sql += " and WorkPoint='" + WorkPoint + "'";
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取周转箱类型
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public DataTable GetType(string tableName)
|
|
{
|
|
string sql = $@"
|
|
select '' as Type,'' as TypeName
|
|
union all
|
|
SELECT Code AS Type,Name as TypeName FROM [ICSType] WITH (NOLOCK) WHERE TableCode='{tableName}' AND ColumnCode='Type' ";
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
/// <summary>
|
|
/// 获取周转箱编码信息
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public DataTable GetBoxNumber()
|
|
{
|
|
string sql = @"select '' as BoxNumber
|
|
union all
|
|
SELECT BoxNumber FROM [ICSWorkingCapitalBox] ";
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取模具编码信息
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public DataTable GetMouldNumber()
|
|
{
|
|
string sql = @"select '' as MouldNumber
|
|
union all
|
|
SELECT MouldNumber FROM [ICSMould] ";
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 获取模具代码信息
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public DataTable GetMouldCode()
|
|
{
|
|
string sql = @"select '' as MouldCode
|
|
union all
|
|
SELECT MouldCode FROM [ICSMouldAccount] ";
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 新增库位
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string InsertStack(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
string StackSql = @"select * from ICSWarehouse where WarehouseCode='{0}' and WorkPoint='{1}'";
|
|
StackSql = string.Format(StackSql, queryParam["Warehouse"].ToString(), WorkPoint);
|
|
DataTable dt = SqlHelper.GetDataTableBySql(StackSql);
|
|
string sql = string.Empty;
|
|
sql = @"IF ((SELECT 1 FROM ICSLocation WHERE LocationCode='{0}' AND LocationName='{1}' AND WHID='{2}' AND WorkPoint='{5}' ) IS NULL)
|
|
BEGIN
|
|
INSERT INTO dbo.ICSLocation
|
|
( ID ,LocationCode ,LocationName ,WHID ,MUSER ,
|
|
MUSERName ,MTIME ,WorkPoint ,Region,Row,GoodsShelf ,Tier,Grid) Values(NEWID(),'{0}','{1}','{2}','{3}','{4}',GETDATE(),'{5}','{6}','{7}','{8}','{9}','{10}')
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
RAISERROR('库位已存在',16,1);
|
|
END ";
|
|
sql = string.Format(sql, queryParam["StackCode"].ToString(), queryParam["StackName"].ToString(), dt.Rows[0]["ID"].ToString(), MUSER, MUSERNAME, WorkPoint, queryParam["Qu"].ToString(), queryParam["Pai"].ToString(), queryParam["Jia"].ToString(), queryParam["Ceng"].ToString(), queryParam["Ge"].ToString());
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
return msg;
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 周转箱信息
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string InsertBoxes(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
sql = $@"IF ((SELECT 1 FROM ICSWorkingCapitalBox WHERE BoxNumber='{queryParam["BoxNumber"]}') IS NULL)
|
|
BEGIN
|
|
INSERT INTO [dbo].[ICSWorkingCapitalBox] ([ID], [BoxNumber], [BoxName], [Type], [Version], [Specification], [Describe], [FirstCleanCycle], [SecondCleanCycle], [ThirdCleanCycle], [MTIME], [MUser], [MUSERName])
|
|
VALUES (NEWID(), '{queryParam["BoxNumber"]}', '{queryParam["BoxName"]}', '{queryParam["Type"]}', '{queryParam["Version"]}', '{queryParam["Specification"]}', '{queryParam["Describe"]}', {queryParam["FirstCleanCycle"]}, {queryParam["SecondCleanCycle"]}, {queryParam["ThirdCleanCycle"]}, GETDATE(), '{MUSER}', '{MUSERNAME}');
|
|
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
RAISERROR('周转箱编码已存在，操作失败',16,1);
|
|
END ";
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
return msg;
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 新增模具信息
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string InsertMould(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
sql = $@"IF ((SELECT 1 FROM ICSMould WHERE MouldNumber='{queryParam["MouldNumber"]}') IS NULL)
|
|
BEGIN
|
|
INSERT INTO [dbo].[ICSMould] ([ID], [MouldNumber], [MouldName], [Type], [Specification], [Unit],[EATTRIBUTE1], [MTIME], [MUser], [MUSERName])
|
|
VALUES (NEWID(), '{queryParam["MouldNumber"]}', '{queryParam["MouldName"]}', '{queryParam["Type"]}', '{queryParam["Specification"]}', '{queryParam["Unit"]}', '{queryParam["Phone"]}',GETDATE(), '{MUSER}', '{MUSERNAME}');
|
|
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
RAISERROR('模具编码已存在，操作失败',16,1);
|
|
END ";
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
return msg;
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
/// <summary>
|
|
/// 新增不良代码
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string InsertBadCode(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
sql = @"INSERT INTO dbo.ICSBadCode
|
|
( ID ,BadCode ,BadDesc ,MUSER ,
|
|
MUSERName ,MTIME ,WorkPoint,BCGroupID ) Values(NEWID(),'{0}','{1}','{2}','{3}',GETDATE(),'{4}','NULL')";
|
|
sql = string.Format(sql, queryParam["BadCode"].ToString(), queryParam["BadDesc"].ToString(), MUSER, MUSERNAME, WorkPoint);
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
/// <summary>
|
|
/// 修改不良代码
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string UpdateBadCode(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
sql = @"UPDATE dbo.ICSBadCode set BadCode='{0}',BadDesc='{1}',MUSER='{2}',MUSERName='{3}',MTIME=GetDate(),WorkPoint='{4}' WHERE ID='{5}'";
|
|
sql = string.Format(sql, queryParam["BadCode"].ToString(), queryParam["BadDesc"].ToString(), MUSER, MUSERNAME, WorkPoint, queryParam["ID"].ToString());
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
/// <summary>
|
|
/// 修改库位
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string UpdateStack(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
string StackSql = @"select * from ICSWarehouse where WarehouseCode='{0}'";
|
|
StackSql = string.Format(StackSql, queryParam["Warehouse"].ToString());
|
|
DataTable dt = SqlHelper.GetDataTableBySql(StackSql);
|
|
string sql = string.Empty;
|
|
sql = @"UPDATE dbo.ICSLocation set LocationCode='{0}',LocationName='{1}',WHID='{2}',MUSER='{3}',MUSERName='{4}',MTIME=GETDATE(),WorkPoint='{5}',Region='{7}',Row='{8}',GoodsShelf ='{9}',Tier='{10}',Grid='{11}' WHERE ID='{6}'";
|
|
sql = string.Format(sql, queryParam["StackCode"].ToString(), queryParam["StackName"].ToString(), dt.Rows[0]["ID"].ToString(), MUSER, MUSERNAME, WorkPoint, queryParam["ID"].ToString(), queryParam["Qu"].ToString(), queryParam["Pai"].ToString(), queryParam["Jia"].ToString(), queryParam["Ceng"].ToString(), queryParam["Ge"].ToString());
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "修改失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 修改周转箱信息
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string UpdateBoxes(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string msg = "";
|
|
|
|
string sql = string.Empty;
|
|
sql = $@"UPDATE [dbo].[ICSWorkingCapitalBox] SET [BoxName] = '{queryParam["BoxName"]}', [Type] = '{queryParam["Type"]}', [Version] = '{queryParam["Version"]}', [Specification] = '{queryParam["Specification"]}', [Describe] = '{queryParam["Describe"]}', [FirstCleanCycle] = {queryParam["FirstCleanCycle"]}, [SecondCleanCycle] = {queryParam["SecondCleanCycle"]}, [ThirdCleanCycle] = {queryParam["ThirdCleanCycle"]}, [MTIME] = GETDATE(), [MUser] = '{MUSER}', [MUSERName] = '{MUSERNAME}' WHERE ID='{queryParam["ID"]}';";
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "修改失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 修改模具信息
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string UpdateMould(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string msg = "";
|
|
|
|
string sql = string.Empty;
|
|
sql = $@"UPDATE [dbo].[ICSMould] SET [MouldName] = '{queryParam["MouldName"]}', [Type] = '{queryParam["Type"]}', [Unit] = '{queryParam["Unit"]}', [Specification] = '{queryParam["Specification"]}', [EATTRIBUTE1] = '{queryParam["Phone"]}', [MTIME] = GETDATE(), [MUser] = '{MUSER}', [MUSERName] = '{MUSERNAME}' WHERE ID='{queryParam["ID"]}';";
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "修改失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 修改模具状态信息
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string UpdateMouldStatus(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string msg = "";
|
|
|
|
string sql = string.Empty;
|
|
sql = $@"UPDATE [dbo].[ICSMouldAccount] SET [MajorStates] = '禁用', [SubStates] = '{queryParam["Status"]}' WHERE ID='{queryParam["ID"]}';
|
|
INSERT INTO [dbo].[ICSMouldLog] ([ID], [MouldCode], [MouldNumber], [Status], [DailyUses], [StartDate], [MTIME], [MUser], [MUSERName], [UsageCount]) SELECT NEWID(),MouldCode,MouldNumber,'{queryParam["Status"]}',0,GETDATE(),GETDATE(),'{MUSER}','{MUSERNAME}',0 FROM ICSMouldAccount WHERE ID='{queryParam["ID"]}';";
|
|
try
|
|
{
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "修改失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
/// <summary>
|
|
/// 新增类型
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string InsertICSType(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
sql = @"INSERT INTO dbo.ICSType
|
|
( ID ,TableCode ,ColumnCode ,Code,Name, Enable,MTIME,MUSER ,MUSERName ,WorkPoint,EATTRIBUTE1,EATTRIBUTE2,EATTRIBUTE3,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7,EATTRIBUTE8,EATTRIBUTE9,EATTRIBUTE10 )
|
|
Values(NEWID(),'{0}','{1}','{2}','{3}','{4}',GETDATE(),'{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}')
|
|
";
|
|
sql = string.Format(sql, queryParam["TableCode"].ToString(), queryParam["ColumnCode"].ToString(), queryParam["Code"].ToString(), queryParam["Name"].ToString(), queryParam["Enable"].ToString(), MUSER, MUSERNAME, WorkPoint,
|
|
queryParam["EATTRIBUTE1"].ToString(), queryParam["EATTRIBUTE2"].ToString(), queryParam["EATTRIBUTE3"].ToString(), queryParam["EATTRIBUTE4"].ToString(), queryParam["EATTRIBUTE5"].ToString(), queryParam["EATTRIBUTE6"].ToString(), queryParam["EATTRIBUTE7"].ToString(), queryParam["EATTRIBUTE8"].ToString(), queryParam["EATTRIBUTE9"].ToString(), queryParam["EATTRIBUTE10"].ToString());
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 修改类型
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string UpdateICSType(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
|
|
string sql = string.Empty;
|
|
sql = @"UPDATE dbo.ICSType set TableCode='{0}',ColumnCode='{1}',Code='{2}',Name='{3}',Enable='{4}',MTIME=GETDATE(),MUSER='{5}',MUSERName='{6}',WorkPoint='{7}' ,EATTRIBUTE1='{9}',EATTRIBUTE2='{10}',EATTRIBUTE3='{11}',EATTRIBUTE4='{12}',EATTRIBUTE5='{13}',EATTRIBUTE6='{14}',EATTRIBUTE7='{15}',EATTRIBUTE8='{16}',EATTRIBUTE9='{17}',EATTRIBUTE10='{18}' WHERE ID='{8}'";
|
|
sql = string.Format(sql, queryParam["TableCode"].ToString(), queryParam["ColumnCode"].ToString(), queryParam["Code"].ToString(), queryParam["Name"].ToString(), queryParam["Enable"].ToString(), MUSER, MUSERNAME, WorkPoint, queryParam["ID"].ToString()
|
|
, queryParam["EATTRIBUTE1"].ToString(), queryParam["EATTRIBUTE2"].ToString(), queryParam["EATTRIBUTE3"].ToString(), queryParam["EATTRIBUTE4"].ToString(), queryParam["EATTRIBUTE5"].ToString(), queryParam["EATTRIBUTE6"].ToString(), queryParam["EATTRIBUTE7"].ToString(), queryParam["EATTRIBUTE8"].ToString(), queryParam["EATTRIBUTE9"].ToString(), queryParam["EATTRIBUTE10"].ToString());
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "修改失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
/// <summary>
|
|
/// 删除类型
|
|
/// </summary>
|
|
/// <param name="keyValue">传入ID</param>
|
|
/// <returns></returns>
|
|
public string DeleteICSType(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
sql = string.Format(@"DELETE FROM dbo.ICSType WHERE ID IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
|
|
|
|
|
|
/// <summary>
|
|
/// 修改参数配置
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string UpdateICSConfiguration(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
|
|
string sql = string.Empty;
|
|
sql = @"UPDATE dbo.ICSConfiguration set Code='{0}',Name='{1}',Enable='{2}',Figure='{3}',MTIME=GETDATE(),MUSER='{4}',MUSERName='{5}',WorkPoint='{6}' WHERE ID='{7}'";
|
|
sql = string.Format(sql, queryParam["Code"].ToString(), queryParam["Name"].ToString(), queryParam["Enable"].ToString(), queryParam["Figure"].ToString(), MUSER, MUSERNAME, WorkPoint, queryParam["ID"].ToString());
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "修改失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 新增参数配置
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string InsertICSConfiguration(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
sql = @"INSERT INTO dbo.ICSConfiguration
|
|
( ID ,Code ,Name ,Enable,Figure,MTIME,MUSER ,MUSERName ,WorkPoint )
|
|
Values(NEWID(),'{0}','{1}','{2}','{3}',GETDATE(),'{4}','{5}','{6}')
|
|
";
|
|
sql = string.Format(sql, queryParam["Code"].ToString(), queryParam["Name"].ToString(), queryParam["Enable"].ToString(), queryParam["Figure"].ToString(), MUSER, MUSERNAME, WorkPoint);
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 删除参数配置
|
|
/// </summary>
|
|
/// <param name="keyValue">传入ID</param>
|
|
/// <returns></returns>
|
|
public string DeleteICSConfiguration(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
sql = string.Format(@"DELETE FROM dbo.ICSConfiguration WHERE ID IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 修改启用配置
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string UpdateICSExtensionEnable(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
|
|
string sql = string.Empty;
|
|
sql = @"UPDATE dbo.ICSExtensionEnable set ColCode='{0}',ColName='{1}',Enable='{2}',MTIME=GETDATE(),MUSER='{3}',MUSERName='{4}',WorkPoint='{5}' WHERE ID='{6}'";
|
|
sql = string.Format(sql, queryParam["ColCode"].ToString(), queryParam["ColName"].ToString(), queryParam["Enable"].ToString(), MUSER, MUSERNAME, WorkPoint, queryParam["ID"].ToString());
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "修改失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 修改启用配置
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string UpdateICSColumnEnable(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
string sqlJ = "";
|
|
// 遍历JArray并处理每个JObject
|
|
foreach (JObject jsonObject in queryParam["arrayOfObjects"])
|
|
{
|
|
foreach (var property in jsonObject.Properties())
|
|
{
|
|
string propertyName = property.Name; // 属性名
|
|
JToken propertyValue = property.Value; // 属性值
|
|
|
|
// 根据需要处理属性值...
|
|
if (propertyValue.Type == JTokenType.String)
|
|
{
|
|
string stringValue = (string)propertyValue;
|
|
// 处理字符串值...
|
|
}
|
|
else if (propertyValue.Type == JTokenType.Object)
|
|
{
|
|
JObject nestedObject = (JObject)propertyValue;
|
|
// 处理嵌套对象...
|
|
}
|
|
sqlJ += "," + propertyName + "='" + (string)propertyValue + "'";
|
|
// 处理其他类型的值...
|
|
}
|
|
}
|
|
|
|
string sql = string.Empty;
|
|
sql = @"UPDATE dbo.ICSColumnEnable set TableCode = '{0}',ColumnCode='{1}',Code='{2}',Name='{3}',Enable='{4}',MTIME=GETDATE(),MUSER='{5}',MUSERName='{6}',WorkPoint='{7}'{9} WHERE ID='{8}'";
|
|
sql = string.Format(sql, queryParam["TableCode"].ToString(), queryParam["ColumnCode"].ToString(), queryParam["Code"].ToString().Replace("EATTRIBUTE", ""), queryParam["Name"].ToString(), queryParam["Enable"].ToString(), MUSER, MUSERNAME, WorkPoint, queryParam["ID"].ToString(), sqlJ);
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "修改失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 新增启用配置
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string InsertICSExtensionEnable(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
sql = @"INSERT INTO dbo.ICSExtensionEnable
|
|
( ID ,ColCode ,ColName ,Enable,MTIME,MUSER ,MUSERName ,WorkPoint )
|
|
Values(NEWID(),'{0}','{1}','{2}',GETDATE(),'{3}','{4}','{5}')
|
|
";
|
|
sql = string.Format(sql, queryParam["ColCode"].ToString(), queryParam["ColName"].ToString(), queryParam["Enable"].ToString(), MUSER, MUSERNAME, WorkPoint);
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 新增启用配置
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string InsertICSColumnEnable(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
string sqlkey = string.Empty;
|
|
string sqlValue = string.Empty;
|
|
foreach (JObject jsonObject in queryParam["arrayOfObjects"])
|
|
{
|
|
foreach (var property in jsonObject.Properties())
|
|
{
|
|
string propertyName = property.Name; // 属性名
|
|
JToken propertyValue = property.Value; // 属性值
|
|
|
|
// 根据需要处理属性值...
|
|
if (propertyValue.Type == JTokenType.String)
|
|
{
|
|
string stringValue = (string)propertyValue;
|
|
// 处理字符串值...
|
|
}
|
|
else if (propertyValue.Type == JTokenType.Object)
|
|
{
|
|
JObject nestedObject = (JObject)propertyValue;
|
|
// 处理嵌套对象...
|
|
}
|
|
sqlkey += "," + propertyName + "";
|
|
sqlValue += ",'" + (string)propertyValue + "'";
|
|
// 处理其他类型的值...
|
|
}
|
|
}
|
|
sql = @"INSERT INTO dbo.ICSColumnEnable
|
|
( ID ,TableCode,ColumnCode,Code ,Name ,Enable,MTIME,MUSER ,MUSERName ,WorkPoint" + sqlkey + @") Values(NEWID(),'{0}','{1}','{2}','{3}','{4}',GETDATE(),'{5}','{6}','{7}'{8}) ";
|
|
sql = string.Format(sql, queryParam["TableCode"].ToString(), queryParam["ColumnCode"].ToString(), queryParam["Code"].ToString().Replace("EATTRIBUTE", ""), queryParam["Name"].ToString(), queryParam["Enable"].ToString(), MUSER, MUSERNAME, WorkPoint, sqlValue);
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 删除启用配置
|
|
/// </summary>
|
|
/// <param name="keyValue">传入ID</param>
|
|
/// <returns></returns>
|
|
public string DeleteICSExtensionEnable(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
sql = string.Format(@"DELETE FROM dbo.ICSExtensionEnable WHERE ID IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 删除单据启用配置
|
|
/// </summary>
|
|
/// <param name="keyValue">传入ID</param>
|
|
/// <returns></returns>
|
|
public string DeleteICSColumnEnable(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
sql = string.Format(@"DELETE FROM dbo.ICSColumnEnable WHERE ID IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
//不良代码
|
|
public DataTable GetICSBadCodeGroup(ref Pagination jqgridparam, string queryJson)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @" select ID,BCGCode,BCGDesc,MUSER,MUSERName,MTIME,EATTRIBUTE1,EATTRIBUTE2,EATTRIBUTE3,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7,EATTRIBUTE8,
|
|
EATTRIBUTE9,EATTRIBUTE10 from ICSBadCodeGroup ";
|
|
sql += " WHERE 1=1";
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["BadCode"].ToString()))
|
|
{
|
|
sql += " and BCGCode like '%" + queryParam["BadCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["BadCodeName"].ToString()))
|
|
{
|
|
sql += " and BCGDesc like '%" + queryParam["BadCodeName"].ToString() + "%' ";
|
|
}
|
|
}
|
|
sql = string.Format(sql);
|
|
DataTable dttest = Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
//不良原因
|
|
public DataTable GetICSBadReasonGroup(ref Pagination jqgridparam, string queryJson)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @" select ID,BRGCode,BRGDesc,MUSER,MUSERName,MTIME,EATTRIBUTE1,EATTRIBUTE2,EATTRIBUTE3,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7,EATTRIBUTE8,
|
|
EATTRIBUTE9,EATTRIBUTE10 from ICSBadReasonGroup ";
|
|
sql += " WHERE 1=1";
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["BadReason"].ToString()))
|
|
{
|
|
sql += " and BRGCode like '%" + queryParam["BadReason"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["BadReasonName"].ToString()))
|
|
{
|
|
sql += " and BRGDesc like '%" + queryParam["BadReasonName"].ToString() + "%' ";
|
|
}
|
|
}
|
|
sql = string.Format(sql);
|
|
DataTable dttest = Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
|
|
//不良代码子表
|
|
public DataTable GetICSBadCodeGroupdetil(string ID, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select BCGroupID,BadCode,BadDesc,MUSERName,MTIME,EATTRIBUTE1,EATTRIBUTE2,EATTRIBUTE3,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7,EATTRIBUTE8,
|
|
EATTRIBUTE9,EATTRIBUTE10 from ICSBadCode
|
|
where BCGroupID='" + ID + "' ";
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
|
|
public DataTable GetICSBadCode(string BRGCode, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
|
|
|
|
|
|
|
|
|
|
string sql = @"select BRGroupID,BadReasonCode,BadReasonDesc,MUSERName,MTIME from ICSBadReason
|
|
WHERE BRGroupID='" + BRGCode + "' ";
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
|
|
|
|
/// <summary>
|
|
/// 不良原因子表查询
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <param name="jqgridparam"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetICSBadReason(string BRGroupID, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select BRGroupID,BadReasonCode,BadReasonDesc,MUSERName,MTIME,EATTRIBUTE1,EATTRIBUTE2,EATTRIBUTE3,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7,EATTRIBUTE8,
|
|
EATTRIBUTE9,EATTRIBUTE10 from ICSBadReason
|
|
WHERE BRGroupID='" + BRGroupID + "' ";
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
///// <summary>
|
|
///// 不良原因子表查询2
|
|
///// </summary>
|
|
///// <param name="queryJson"></param>
|
|
///// <param name="jqgridparam"></param>
|
|
///// <returns></returns>
|
|
//public DataTable GetICSBadReason2(string BRGCode, ref Pagination jqgridparam)
|
|
//{
|
|
// string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
// DataTable dt = new DataTable();
|
|
// //var queryParam = queryJson.ToJObject();
|
|
// List<DbParameter> parameter = new List<DbParameter>();
|
|
// string sql = @"select BRGroupID,BadReasonCode,BadReasonDesc,MUSERName,MTIME from ICSBadReason
|
|
// WHERE BRGroupID='" + BRGCode + "' ";
|
|
// return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
//}
|
|
|
|
public void DeleteBCItem(string ID)
|
|
{
|
|
string sql = "DELETE FROM dbo.ICSBadCode WHERE ID='" + ID + "'";
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
|
|
public void DeleteItem(string ID)
|
|
{
|
|
string sql = "DELETE FROM dbo.ICSBadReason WHERE ID='" + ID + "'";
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
|
|
|
|
|
|
|
|
public DataTable GetICSBadCodeGroupdetil2(string ID, ref Pagination jqgridparam)
|
|
{
|
|
string sql = string.Empty;
|
|
DataTable dt = null;
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
sql = string.Format(@"select * from ICSBadCode where BCGroupID='{0}'", ID);
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
//dt = SqlHelper.GetDataTableBySql(sql);
|
|
//return dt;
|
|
}
|
|
|
|
public DataTable GetICSBadReason2(string BID, ref Pagination jqgridparam)
|
|
{
|
|
string sql = string.Empty;
|
|
DataTable dt = null;
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
sql = string.Format(@"select * from ICSBadReason where BRGroupID='{0}'", BID);
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
//dt = SqlHelper.GetDataTableBySql(sql);
|
|
//return dt;
|
|
}
|
|
|
|
|
|
|
|
/// <summary>
|
|
/// 周转箱使用记录查询
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <param name="jqgridparam"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetBoxesRecord(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
var queryParam = queryJson.ToJObject();
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"SELECT a.BoxCode ,a.BoxNumber,a.Picture,a.MTIME,a.MUSERName,b.BoxName FROM ICSWorkingCapitalBoxLog a
|
|
left JOIN ICSWorkingCapitalBoxAccount b ON a.BoxCode=b.BoxCode WHERE 1=1 ";
|
|
if (queryParam["BoxCode"] != null && queryParam["BoxCode"].ToString() != "")
|
|
{
|
|
sql = sql + $@"and a.BoxCode='{queryParam["BoxCode"]}' ";
|
|
}
|
|
if (queryParam["BoxName"] != null && queryParam["BoxName"].ToString() != "")
|
|
{
|
|
sql = sql + $@" and a.BoxCode='{queryParam["BoxName"]}'";
|
|
}
|
|
if (queryParam["BoxNumber"] != null && queryParam["BoxNumber"].ToString() != "")
|
|
{
|
|
sql = sql + $@" and a.BoxNumber='{queryParam["BoxNumber"]}'";
|
|
}
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
|
|
|
|
/// <summary>
|
|
/// 周转箱台账信息
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <param name="jqgridparam"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetBoxesAccountRecord(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
var queryParam = queryJson.ToJObject();
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"SELECT [ID], [BoxCode], [BoxNumber], [BoxName], [States], [Describe], [DailyUses], [TotalUses], [TotalClearn], [CleanDate], [Cleaner], [Picture], [MUser], [MUSERName], [MTIME] from ICSWorkingCapitalBoxAccount WHERE 1=1 ";
|
|
if (queryParam["BoxCode"] != null && queryParam["BoxCode"].ToString() != "")
|
|
{
|
|
sql = sql + $@"and a.BoxCode='{queryParam["BoxCode"]}' ";
|
|
}
|
|
if (queryParam["BoxName"] != null && queryParam["BoxName"].ToString() != "")
|
|
{
|
|
sql = sql + $@" and a.BoxCode='{queryParam["BoxName"]}'";
|
|
}
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
|
|
|
|
/// <summary>
|
|
/// 模具使用记录查询
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <param name="jqgridparam"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetMouldRecord(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
var queryParam = queryJson.ToJObject();
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"SELECT a.MouldCode,a.Status,a.EOPCode,a.Picture ,a.MouldNumber,a.DailyUses,b.MouldName,
|
|
(case when a.status = '保养' then a.StartDate else null end) as MaintenanceDate,
|
|
(case when a.status = '保养' then a.MUSERName else null end) as MaintenancePerson,
|
|
(case when a.status = '维修' then a.StartDate else null end) as MaintainDate,
|
|
(case when a.status = '维修' then a.MUSERName else null end) as MaintainPerson,
|
|
(case when a.status not in('保养','维修') then a.MTime else null end) as UseDate,
|
|
(case when a.status not in('保养','维修') then a.MUSERName else null end) as UsePerson
|
|
FROM ICSMouldLog a
|
|
left JOIN ICSMouldAccount b ON a.MouldCode=b.MouldCode WHERE 1=1 ";
|
|
if (queryParam["MouldCode"] != null && queryParam["MouldCode"].ToString() != "")
|
|
{
|
|
sql = sql + $@"and a.MouldCode='{queryParam["MouldCode"]}' ";
|
|
}
|
|
if (queryParam["MouldNumber"] != null && queryParam["MouldNumber"].ToString() != "")
|
|
{
|
|
sql = sql + $@"and a.MouldNumber='{queryParam["MouldNumber"]}' ";
|
|
}
|
|
if (queryParam["MouldName"] != null && queryParam["MouldName"].ToString() != "")
|
|
{
|
|
sql = sql + $@" and a.MouldCode='{queryParam["MouldName"]}'";
|
|
}
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 模具台账信息
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <param name="jqgridparam"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetMouldAccountRecord(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
var queryParam = queryJson.ToJObject();
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"SELECT [ID], [MouldCode], [MouldNumber], [MouldName], [MajorStates], [SubStates], [UpperLimit], [LowerLimit], [DailyUses],
|
|
[TotalUses], [Users], [UserDate], [Picture], [MUser], [MUSERName], [MTIME], [EATTRIBUTE1] FROM ICSMouldAccount WHERE 1=1 ";
|
|
if (queryParam["MouldCode"] != null && queryParam["MouldCode"].ToString() != "")
|
|
{
|
|
sql = sql + $@"and MouldCode='{queryParam["MouldCode"]}' ";
|
|
}
|
|
if (queryParam["MouldNumber"] != null && queryParam["MouldNumber"].ToString() != "")
|
|
{
|
|
sql = sql + $@"and MouldNumber='{queryParam["MouldNumber"]}' ";
|
|
}
|
|
if (queryParam["MouldName"] != null && queryParam["MouldName"].ToString() != "")
|
|
{
|
|
sql = sql + $@" and MouldCode='{queryParam["MouldName"]}'";
|
|
}
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 删除不良代码组
|
|
/// </summary>
|
|
/// <param name="keyValue">传入ID</param>
|
|
/// <returns></returns>
|
|
public string DeleteICSBadCodeGroup(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
|
|
sql += string.Format(@"DELETE FROM dbo.ICSBadCodeGroup WHERE ID IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
sql += string.Format(@"DELETE FROM dbo.ICSBadCode WHERE BCGroupID IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
try
|
|
{
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 删除不良原因组
|
|
/// </summary>
|
|
/// <param name="keyValue">传入ID</param>
|
|
/// <returns></returns>
|
|
public string DeleteICSBadReasonGroup(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
|
|
sql += string.Format(@"DELETE FROM dbo.ICSBadReasonGroup WHERE ID IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
sql += string.Format(@"DELETE FROM dbo.ICSBadReason WHERE BRGroupID IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
try
|
|
{
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 初次加载查询检验项目组
|
|
/// </summary>
|
|
/// <param name="jqgridparam"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetICSInspectionGroup(ref Pagination jqgridparam, string queryJson)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
string workPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = $@"select ID,GroupCode,GroupName,Enable,MUSER,MUSERName,MTIME,EATTRIBUTE1 as Type,EATTRIBUTE2 as IQCStatus from ICSInspectionGroup WHERE WorkPoint='{workPoint}' ";
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["ListCode"].ToString()))
|
|
{
|
|
sql += " and GroupCode like '%" + queryParam["ListCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["ListName"].ToString()))
|
|
{
|
|
sql += " and GroupName like '%" + queryParam["ListName"].ToString() + "%' ";
|
|
}
|
|
}
|
|
sql = string.Format(sql);
|
|
DataTable dttest = Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 检验项目子表查询
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <param name="jqgridparam"></param>
|
|
/// <returns></returns>
|
|
public DataTable ICSInspectionList(string GroupCode, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @" SELECT b.GroupID,b.ListCode,b.ListName,b.Unit,b.SetValueMax,b.SetValueMin,b.Enable,b.MUSERName,b.MTIME,b.EATTRIBUTE1,b.EATTRIBUTE2,b.EATTRIBUTE3,b.EATTRIBUTE4,b.EATTRIBUTE5,b.EATTRIBUTE6,b.EATTRIBUTE7,b.EATTRIBUTE8,
|
|
b.EATTRIBUTE9,b.EATTRIBUTE10 FROM ICSInspectionGroup a
|
|
LEFT JOIN ICSInspectionList b ON a.ID=b.GroupID
|
|
WHERE a.GroupCode='" + GroupCode + "'";
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
/// <summary>
|
|
/// 根据id删除自动表
|
|
/// </summary>
|
|
/// <param name="ID"></param>
|
|
public void DeleteList(string ID)
|
|
{
|
|
string sql = "DELETE FROM dbo.ICSInspectionList WHERE ID='" + ID + "'";
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 动态子表修改加载页面
|
|
/// </summary>
|
|
/// <param name="GroupCode"></param>
|
|
/// <returns></returns>
|
|
public DataTable ICSInspectionList2(string GroupCode)
|
|
{
|
|
string sql = string.Empty;
|
|
DataTable dt = null;
|
|
sql = string.Format(@"SELECT b.ID,b.GroupID,b.ListCode,b.ListName,b.Unit,b.SetValueMax,b.SetValueMin,
|
|
case when b.Enable='1' then '是' else '否' end as Enable,
|
|
b.MUSER,b.MUSERName,b.MTIME,b.WorkPoint,b.EATTRIBUTE1 as ICSInspectionList_EATTRIBUTE1,b.EATTRIBUTE2 ICSInspectionList_EATTRIBUTE2,b.EATTRIBUTE3 ICSInspectionList_EATTRIBUTE3,b.EATTRIBUTE4 ICSInspectionList_EATTRIBUTE4,b.EATTRIBUTE5 ICSInspectionList_EATTRIBUTE5,b.EATTRIBUTE6 ICSInspectionList_EATTRIBUTE6,b.EATTRIBUTE7 ICSInspectionList_EATTRIBUTE7,b.EATTRIBUTE8 ICSInspectionList_EATTRIBUTE8,b.EATTRIBUTE9 ICSInspectionList_EATTRIBUTE9,b.EATTRIBUTE10 ICSInspectionList_EATTRIBUTE10 FROM ICSInspectionGroup a
|
|
LEFT JOIN ICSInspectionList b ON a.ID=b.GroupID
|
|
where GroupCode='{0}'", GroupCode);
|
|
dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 删除不良原因组
|
|
/// </summary>
|
|
/// <param name="keyValue">传入ID</param>
|
|
/// <returns></returns>
|
|
public string DeleteICSInspectionGroup(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
string sqls = string.Empty;
|
|
sql = string.Format(@"DELETE FROM dbo.ICSInspectionGroup WHERE GroupCode IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
sqls = string.Format(@"DELETE FROM dbo.ICSInspectionList WHERE GroupID IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
SqlHelper.ExecuteNonQuery(sqls);
|
|
}
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 新增料品属性
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string InsertICSInventory(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
string sqlkey = string.Empty;
|
|
string sqlValue = string.Empty;
|
|
foreach (JObject jsonObject in queryParam["arrayOfObjects"])
|
|
{
|
|
foreach (var property in jsonObject.Properties())
|
|
{
|
|
string propertyName = property.Name; // 属性名
|
|
JToken propertyValue = property.Value; // 属性值
|
|
|
|
// 根据需要处理属性值...
|
|
if (propertyValue.Type == JTokenType.String)
|
|
{
|
|
string stringValue = (string)propertyValue;
|
|
// 处理字符串值...
|
|
}
|
|
else if (propertyValue.Type == JTokenType.Object)
|
|
{
|
|
JObject nestedObject = (JObject)propertyValue;
|
|
// 处理嵌套对象...
|
|
}
|
|
sqlkey += "," + propertyName + "";
|
|
sqlValue += ",'" + (string)propertyValue + "'";
|
|
// 处理其他类型的值...
|
|
}
|
|
}
|
|
sql = @"INSERT INTO dbo.ICSInventory
|
|
( ID ,InvCode ,InvName ,InvDesc,InvStd,InvUnit,AmountUnit ,ClassCode ,ClassName ,InvRate,InvVersion,InvBrand,InvIQC ,InvFQC ,EffectiveEnable,EffectiveDays,BatchEnable,LotEnable ,PrintEnable ,WHUser,MTIME,MUSER ,MUSERName ,WorkPoint ,AmountEnable,OverType,OverValue,OverInType,OverInValue,ContainerEnable,InvOQC" + sqlkey + @")
|
|
Values(NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}',GETDATE(),'{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}'{29})
|
|
";
|
|
sql = string.Format(sql,
|
|
queryParam["InvCode"].ToString(),
|
|
queryParam["InvName"].ToString(),
|
|
queryParam["InvDesc"].ToString(),
|
|
queryParam["InvStd"].ToString(),
|
|
queryParam["InvUnit"].ToString(),
|
|
queryParam["AmountUnit"].ToString(),
|
|
queryParam["ClassCode"].ToString(),
|
|
queryParam["ClassName"].ToString(),
|
|
queryParam["InvRate"].ToString(),
|
|
queryParam["InvVersion"].ToString(),
|
|
queryParam["InvBrand"].ToString(),
|
|
queryParam["InvIQC"].ToString(),
|
|
queryParam["InvFQC"].ToString(),
|
|
queryParam["EffectiveEnable"].ToString(),
|
|
queryParam["EffectiveDays"].ToString(),
|
|
queryParam["BatchEnable"].ToString(),
|
|
queryParam["LotEnable"].ToString(),
|
|
queryParam["PrintEnable"].ToString(),
|
|
queryParam["WHUser"].ToString(),
|
|
MUSER, MUSERNAME, WorkPoint, queryParam["AmountEnable"].ToString(),
|
|
queryParam["OverType"].ToString(),
|
|
queryParam["OverValue"].ToString(),
|
|
queryParam["OverInType"].ToString(),
|
|
queryParam["OverInValue"].ToString(),
|
|
queryParam["ContainerEnable"].ToString(),
|
|
queryParam["InvOQC"].ToString(), sqlValue
|
|
);
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
/// <summary>
|
|
/// 新增周转箱台账信息
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string InsertBoxAccount(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
sql = $@"IF ((SELECT 1 FROM ICSWorkingCapitalBoxAccount WHERE BoxCode='{queryParam["BoxCode"]}') IS NULL)
|
|
|
|
BEGIN
|
|
INSERT INTO[dbo].[ICSWorkingCapitalBoxAccount]([ID], [BoxCode], [BoxNumber], [BoxName], [States], [Describe], [DailyUses], [TotalUses], [TotalClearn], [MUser], [MUSERName], [MTIME]) VALUES(NEWID(), '{queryParam["BoxCode"]}', '{queryParam["BoxNumber"]}', '{queryParam["BoxName"]}', '正常', '{queryParam["Describe"]}', 0, 0, 0, '{MUSER}', '{MUSERNAME}', GETDATE())
|
|
|
|
END
|
|
ELSE
|
|
|
|
BEGIN
|
|
|
|
RAISERROR('周转箱代码码已存在，操作失败', 16, 1);
|
|
END ";
|
|
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 新增模具账信息
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string InsertMouldAccount(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
sql = $@"IF ((SELECT 1 FROM ICSMouldAccount WHERE MouldCode='{queryParam["MouldCode"]}') IS NULL)
|
|
|
|
BEGIN
|
|
INSERT INTO[dbo].[ICSMouldAccount]([ID], [MouldCode], [MouldNumber], [MouldName], [MajorStates],[UpperLimit], [LowerLimit], [Picture], [MUser], [MUSERName], [MTIME],[DailyUses],[TotalUses]) VALUES(NEWID(), '{queryParam["MouldCode"]}', '{queryParam["MouldNumber"]}', '{queryParam["MouldName"]}','{queryParam["Status"]}',{queryParam["UpperLimit"]}, {queryParam["LowerLimit"]},'{queryParam["Picture"]}','{MUSER}', '{MUSERNAME}', GETDATE(),0,0)
|
|
|
|
END
|
|
ELSE
|
|
|
|
BEGIN
|
|
|
|
RAISERROR('模具代码已存在，操作失败', 16, 1);
|
|
END ";
|
|
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 修改料品属性
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string UpdateICSInventory(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
string sqlJ = "";
|
|
string sql = string.Empty;
|
|
// 遍历JArray并处理每个JObject
|
|
foreach (JObject jsonObject in queryParam["arrayOfObjects"])
|
|
{
|
|
foreach (var property in jsonObject.Properties())
|
|
{
|
|
string propertyName = property.Name; // 属性名
|
|
JToken propertyValue = property.Value; // 属性值
|
|
|
|
// 根据需要处理属性值...
|
|
if (propertyValue.Type == JTokenType.String)
|
|
{
|
|
string stringValue = (string)propertyValue;
|
|
// 处理字符串值...
|
|
}
|
|
else if (propertyValue.Type == JTokenType.Object)
|
|
{
|
|
JObject nestedObject = (JObject)propertyValue;
|
|
// 处理嵌套对象...
|
|
}
|
|
sqlJ += "," + propertyName + "='" + (string)propertyValue + "'";
|
|
// 处理其他类型的值...
|
|
}
|
|
}
|
|
|
|
sql = @"UPDATE dbo.ICSInventory set
|
|
InvCode='{0}'
|
|
,InvName='{1}'
|
|
,InvDesc='{2}'
|
|
,InvStd='{3}'
|
|
,InvUnit='{4}'
|
|
,AmountUnit='{5}'
|
|
,ClassCode='{6}'
|
|
,ClassName='{7}'
|
|
,InvRate='{8}'
|
|
,InvVersion='{9}'
|
|
,InvBrand='{10}'
|
|
,InvIQC='{11}'
|
|
,InvFQC='{12}'
|
|
,EffectiveEnable='{13}'
|
|
,EffectiveDays='{14}'
|
|
,BatchEnable='{15}'
|
|
,LotEnable='{16}'
|
|
,PrintEnable='{17}'
|
|
,AmountEnable='{23}'
|
|
,WHUser='{18}'
|
|
,MTIME=GETDATE()
|
|
,MUSER='{19}'
|
|
,MUSERName='{20}'
|
|
,WorkPoint='{21}'
|
|
,OverType='{24}'
|
|
,OverValue='{25}'
|
|
,OverInType='{26}'
|
|
,OverInValue='{27}'
|
|
,ContainerEnable='{28}'
|
|
,InvOQC='{29}' {30}
|
|
WHERE ID='{22}'";
|
|
sql = string.Format(sql,
|
|
queryParam["InvCode"].ToString(),
|
|
queryParam["InvName"].ToString(),
|
|
queryParam["InvDesc"].ToString(),
|
|
queryParam["InvStd"].ToString(),
|
|
queryParam["InvUnit"].ToString(),
|
|
queryParam["AmountUnit"].ToString(),
|
|
queryParam["ClassCode"].ToString(),
|
|
queryParam["ClassName"].ToString(),
|
|
queryParam["InvRate"].ToString(),
|
|
queryParam["InvVersion"].ToString(),
|
|
queryParam["InvBrand"].ToString(),
|
|
queryParam["InvIQC"].ToString(),
|
|
queryParam["InvFQC"].ToString(),
|
|
queryParam["EffectiveEnable"].ToString(),
|
|
queryParam["EffectiveDays"].ToString(),
|
|
queryParam["BatchEnable"].ToString(),
|
|
queryParam["LotEnable"].ToString(),
|
|
queryParam["PrintEnable"].ToString(),
|
|
queryParam["WHUser"].ToString(),
|
|
MUSER, MUSERNAME, WorkPoint,
|
|
queryParam["ID"].ToString(), queryParam["AmountEnable"].ToString(),
|
|
queryParam["OverType"].ToString(),
|
|
queryParam["OverValue"].ToString(),
|
|
queryParam["OverInType"].ToString(),
|
|
queryParam["OverInValue"].ToString(),
|
|
queryParam["ContainerEnable"].ToString(),
|
|
queryParam["InvOQC"].ToString(), sqlJ
|
|
);
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "修改失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 新增部门
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string InsertICSDeptment(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
sql = @"IF EXISTS (SELECT DepCode FROM ICSDepartment WHERE DepCode='{0}' AND WorkPoint='{5}')
|
|
BEGIN
|
|
RAISERROR('客户编码已存在，新增失败', 16, 1);
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
insert into ICSDepartment
|
|
(DepCode,DepName,DepFullName,DepGrade,DepEnd,WorkPoint,MUSER,MUSERName,MTIME)
|
|
values
|
|
('{0}','{1}','{2}','{3}',{4},'{5}','{6}','{7}',GETDATE())
|
|
END";
|
|
sql = string.Format(sql,
|
|
queryParam["DepCode"].ToString(),
|
|
queryParam["DepName"].ToString(),
|
|
queryParam["DepFullName"].ToString(),
|
|
queryParam["DepGrade"].ToString(),
|
|
queryParam["DepEnd"].ToString(),
|
|
WorkPoint, MUSER, MUSERNAME);
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 修改部门
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string UpdateICSDeptment(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
|
|
string sql = string.Empty;
|
|
sql = @"update ICSDepartment set DepName='{0}',DepFullName='{1}',DepGrade='{2}',DepEnd={3}
|
|
,MUSER='{6}',MUSERName='{7}'
|
|
where DepCode='{4}' and WorkPoint='{5}'";
|
|
sql = string.Format(sql,
|
|
queryParam["DepName"].ToString(),
|
|
queryParam["DepFullName"].ToString(),
|
|
queryParam["DepGrade"].ToString(),
|
|
queryParam["DepEnd"].ToString(),
|
|
queryParam["DepCode"].ToString(),
|
|
WorkPoint, MUSER, MUSERNAME);
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "修改失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 删除部门
|
|
/// </summary>
|
|
/// <param name="keyValue">传入ID</param>
|
|
/// <returns></returns>
|
|
public string DeleteICSDeptment(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
sql = string.Format(@"DELETE FROM dbo.ICSDepartment WHERE ID IN ({0}) and WorkPoint ='{1}'"
|
|
, keyValue.TrimEnd(','), WorkPoint);
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 修改周转箱台账
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string UpdateBoxAccount(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string msg = "";
|
|
|
|
string sql = string.Empty;
|
|
sql = $@"IF ((SELECT 1 FROM ICSWorkingCapitalBoxAccount WHERE BoxCode='{queryParam["BoxCode"]}') IS NULL)
|
|
BEGIN
|
|
UPDATE [dbo].[ICSWorkingCapitalBoxAccount] SET [BoxNumber] = '{queryParam["BoxNumber"]}', [BoxCode]='{queryParam["BoxCode"]}', [BoxName] = '{queryParam["BoxName"]}', [Describe] = '{queryParam["Describe"]}' WHERE ID='{queryParam["ID"]}'
|
|
END
|
|
ELSE
|
|
|
|
BEGIN
|
|
|
|
RAISERROR('周转箱代码码已存在，操作失败', 16, 1);
|
|
END";
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "修改失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 修改模具台账
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string UpdateMouldAccount(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string msg = "";
|
|
|
|
string sql = string.Empty;
|
|
if (!string.IsNullOrEmpty(queryParam["Picture"].ToString()))
|
|
{
|
|
sql = $@" UPDATE [dbo].[ICSMouldAccount] SET [MouldNumber] = '{queryParam["MouldNumber"]}', [MouldName] = '{queryParam["MouldName"]}',MouldCode='{queryParam["MouldCode"]}', [MajorStates] = '{queryParam["Status"]}', [UpperLimit] = {queryParam["UpperLimit"]}, [LowerLimit] = {queryParam["LowerLimit"]}, [Picture] = '{queryParam["Picture"]}' WHERE ID='{queryParam["ID"]}'";
|
|
}
|
|
else
|
|
{
|
|
sql = $@" UPDATE [dbo].[ICSMouldAccount] SET [MouldNumber] = '{queryParam["MouldNumber"]}',MouldCode='{queryParam["MouldCode"]}', [MouldName] = '{queryParam["MouldName"]}', [MajorStates] = '{queryParam["Status"]}', [UpperLimit] = {queryParam["UpperLimit"]}, [LowerLimit] = {queryParam["LowerLimit"]} WHERE ID='{queryParam["ID"]}'";
|
|
}
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "修改失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 修改PDA版本
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string UpdatePDAVersion(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
|
|
string sql = string.Empty;
|
|
sql = @"UPDATE ICSVersions SET Version='{2}',URL='{3}',MUSERCode='{4}',MUSERName='{5}',MTIME=CONVERT(nvarchar(50),GETDATE(),120) WHERE guid='{0}' AND WorkPointCode IN('{1}')";
|
|
sql = string.Format(sql,
|
|
queryParam["guid"].ToString(),
|
|
WorkPoint,
|
|
queryParam["Version"].ToString(),
|
|
queryParam["URL"].ToString(),
|
|
MUSER,
|
|
MUSERNAME);
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "修改失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 获取下拉料品号获取其他信息
|
|
/// </summary>
|
|
|
|
public DataTable GetInputValue(string ID)
|
|
{
|
|
try
|
|
{
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select InvCode,InvName,InvDesc,InvStd,InvUnit,AmountUnit, ClassCode,ClassName,InvRate,InvVersion,InvBrand,InvIQC,InvOQC,
|
|
InvFQC,EffectiveEnable,EffectiveDays,BatchEnable,LotEnable,PrintEnable,WHUser,AmountEnable,OverType,OverValue,OverInType,OverInValue,ContainerEnable , {1}
|
|
from ICSInventory
|
|
WHERE 1=1 AND ID='{0}' ";
|
|
string EATTRIBUTE = @"EATTRIBUTE1, EATTRIBUTE2, EATTRIBUTE3, EATTRIBUTE4, EATTRIBUTE5, EATTRIBUTE6, EATTRIBUTE7, EATTRIBUTE8, EATTRIBUTE9, EATTRIBUTE10, EATTRIBUTE11, EATTRIBUTE12, EATTRIBUTE13, EATTRIBUTE14, EATTRIBUTE15, EATTRIBUTE16, EATTRIBUTE17, EATTRIBUTE18, EATTRIBUTE19, EATTRIBUTE20, EATTRIBUTE21, EATTRIBUTE22, EATTRIBUTE23, EATTRIBUTE24, EATTRIBUTE25, EATTRIBUTE26, EATTRIBUTE27, EATTRIBUTE28, EATTRIBUTE29, EATTRIBUTE30";
|
|
sql = string.Format(sql, ID, EATTRIBUTE);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message.ToString());
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取部门修改文本框值
|
|
/// </summary>
|
|
public DataTable GetDepInputValue(string ID)
|
|
{
|
|
try
|
|
{
|
|
DataTable dt = new DataTable();
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select DepCode,DepName,DepFullName,DepGrade,DepEnd from ICSDepartment
|
|
where 1=1 and ID='{0}' and WorkPoint='{1}' ";
|
|
sql = string.Format(sql, ID, WorkPoint);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message.ToString());
|
|
}
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 删除料品属性
|
|
/// </summary>
|
|
/// <param name="keyValue">传入ID</param>
|
|
/// <returns></returns>
|
|
public string DeleteICSInventory(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
sql = string.Format(@"IF EXISTS(select InvCode from ICSInventoryLot where InvCode in ({0}) and WorkPoint='{1}')
|
|
BEGIN
|
|
RAISERROR('选中料号已生成条码，不能删除！',16,1);
|
|
RETURN
|
|
end
|
|
DELETE FROM dbo.ICSInventory WHERE ID IN ({0}) and WorkPoint ='{1}'"
|
|
, keyValue.TrimEnd(','), WorkPoint);
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 删除周转箱台账信息
|
|
/// </summary>
|
|
/// <param name="keyValue">传入ID</param>
|
|
/// <returns></returns>
|
|
public string DeleteBoxAccount(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
sql = string.Format(@"DELETE FROM dbo.ICSWorkingCapitalBoxAccount WHERE ID IN ({0})", keyValue.TrimEnd(','));
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取料品信息
|
|
/// </summary>
|
|
/// <param name="jqgridparam"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetICSInventoryInfo(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select ID, InvCode,InvName,InvDesc,InvStd,InvUnit,AmountUnit,ClassCode,ClassName,InvRate,InvVersion,InvBrand,
|
|
InvIQC,InvFQC,EffectiveEnable,EffectiveDays,BatchEnable,LotEnable,PrintEnable,WHUser
|
|
from ICSInventory where WorkPoint='{0}' ";
|
|
sql = string.Format(sql, WorkPoint);
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
|
|
{
|
|
sql += " and InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["ClassCode"].ToString()))
|
|
{
|
|
sql += " and ClassCode like '" + queryParam["ClassCode"].ToString() + "%' ";
|
|
}
|
|
}
|
|
|
|
// DataTable dttest = Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取仓库信息
|
|
/// </summary>
|
|
/// <param name="jqgridparam"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetICSWarehouseInfo(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select ID,WarehouseCode,WarehouseName ,Enable,BatchEnable,Asset,Proxy,InCost from ICSWarehouse where WorkPoint='{0}' ";
|
|
sql = string.Format(sql, WorkPoint);
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["WHCode"].ToString()))
|
|
{
|
|
sql += " and WarehouseCode like '%" + queryParam["WHCode"].ToString() + "%' ";
|
|
}
|
|
}
|
|
|
|
DataTable dttest = Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 新增料品仓库批次关系
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string InsertICSInventoryBatchEnable(string keyValue, string keyValue2)
|
|
{
|
|
List<string> InvCodeList = keyValue.Substring(1, keyValue.Length - 3).Split(',').ToList<string>();
|
|
List<string> WarehouseCodeList = keyValue2.Substring(1, keyValue2.Length - 3).Split(',').ToList<string>();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
foreach (var item in InvCodeList)
|
|
{
|
|
foreach (var itemwh in WarehouseCodeList)
|
|
{
|
|
sql += @"IF EXISTS(select InvCode,WHCode from ICSInventoryBatchEnable where InvCode={0} and WHCode={1})
|
|
BEGIN
|
|
RAISERROR('请勿重复绑定料品仓库关系！',16,1);
|
|
RETURN
|
|
end
|
|
INSERT INTO dbo.ICSInventoryBatchEnable
|
|
( ID ,InvCode ,WHCode ,BatchEnable,MTIME,MUSER ,MUSERName ,WorkPoint )
|
|
Values(NEWID(),{0},{1},'{2}',GETDATE(),'{3}','{4}','{5}')
|
|
";
|
|
sql = string.Format(sql, item, itemwh, '1'.ToString(), MUSER, MUSERNAME, WorkPoint);
|
|
|
|
}
|
|
}
|
|
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 修改料品仓库批次关系
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string UpdateICSInventoryBatchEnable(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
|
|
string sql = string.Empty;
|
|
sql = @"UPDATE dbo.ICSInventoryBatchEnable set InvCode='{0}',WHCode='{1}',BatchEnable='{2}',MTIME=GETDATE(),MUSER='{3}',MUSERName='{4}',WorkPoint='{5}' WHERE ID='{6}'";
|
|
sql = string.Format(sql, queryParam["InvCode"].ToString(), queryParam["WHCode"].ToString(), queryParam["BatchEnable"].ToString(), MUSER, MUSERNAME, WorkPoint, queryParam["ID"].ToString());
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "修改失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 删除料品仓库批次关系
|
|
/// </summary>
|
|
/// <param name="keyValue">传入ID</param>
|
|
/// <returns></returns>
|
|
public string DeleteICSInventoryBatchEnable(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
sql = string.Format(@"DELETE FROM dbo.ICSInventoryBatchEnable WHERE ID IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取库位信息
|
|
/// </summary>
|
|
/// <param name="jqgridparam"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetICSLocation(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select a.ID,a.LocationCode,a.LocationName,a.WHID,a.MUSER,a.MUSERName,a.MTIME,a.WorkPoint
|
|
from ICSLocation a INNER JOIN ICSWarehouse b ON a.WHID=b.ID
|
|
where a.WorkPoint='{0}' ";
|
|
sql = string.Format(sql, WorkPoint);
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["LocationCode"].ToString()))
|
|
{
|
|
sql += " and a.LocationCode like '%" + queryParam["LocationCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["WHCode"].ToString()))
|
|
{
|
|
sql += " and b.WarehouseCode like '%" + queryParam["WHCode"].ToString() + "%' ";
|
|
}
|
|
}
|
|
|
|
//DataTable dttest = Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 新增料品仓库批次关系(3024-1-31 代码优化)
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string InsertICSInventoryLocation(string keyValue, string keyValue2)
|
|
{
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 3);
|
|
keyValue2 = keyValue2.Substring(1, keyValue2.Length - 3);
|
|
//List<string> InvCodeList = keyValue.Substring(1, keyValue.Length - 3).Split(',').ToList<string>();
|
|
//List<string> LocationCodeList = keyValue2.Substring(1, keyValue2.Length - 3).Split(',').ToList<string>();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
try
|
|
{
|
|
|
|
// foreach (var item in InvCodeList)
|
|
// {
|
|
// foreach (var itemwh in LocationCodeList)
|
|
// {
|
|
// sql += @"IF EXISTS(select InvCode,LocationCode from ICSInventoryLocation where InvCode={0} and LocationCode={1})
|
|
// BEGIN
|
|
// RAISERROR('请勿重复绑定料品库位关系！',16,1);
|
|
// RETURN
|
|
// end
|
|
// INSERT INTO dbo.ICSInventoryLocation
|
|
// ( ID ,InvCode ,LocationCode ,Enable,MTIME,MUSER ,MUSERName ,WorkPoint )
|
|
// Values(NEWID(),{0},{1},'{2}',GETDATE(),'{3}','{4}','{5}')
|
|
//";
|
|
// sql = string.Format(sql, item, itemwh, '1'.ToString(), MUSER, MUSERNAME, WorkPoint);
|
|
|
|
// }
|
|
// }
|
|
|
|
string sql = $@"if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#TempInventoryLocation'))
|
|
drop table #TempInventoryLocation ;
|
|
SELECT a.InvCode,b.LocationCode,a.WorkPoint INTO #TempInventoryLocation from ICSInventory a
|
|
left JOIN ICSLocation b ON a.WorkPoint=b.WorkPoint AND b.LocationCode IN ({keyValue2})
|
|
WHERE a.InvCode IN ({keyValue}) and a.WorkPoint={WorkPoint};
|
|
INSERT into ICSInventoryLocation ( ID ,InvCode ,LocationCode ,Enable,MTIME,MUSER ,MUSERName ,WorkPoint ) SELECT NEWID(),a.InvCode,a.LocationCode,'1',GETDATE(),'{MUSER}','{MUSERNAME}',a.WorkPoint FROM #TempInventoryLocation a
|
|
LEFT JOIN ICSInventoryLocation b ON a.LocationCode=b.LocationCode AND a.WorkPoint=b.WorkPoint AND a.InvCode=b.InvCode
|
|
WHERE b.LocationCode is NULL;
|
|
drop table #TempInventoryLocation;";
|
|
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 修改料品库位绑定关系
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string UpdateICSInventoryLocation(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
|
|
string sql = string.Empty;
|
|
sql = @"UPDATE dbo.ICSInventoryLocation set InvCode='{0}',LocationCode='{1}',Enable='{2}',MTIME=GETDATE(),MUSER='{3}',MUSERName='{4}',WorkPoint='{5}' WHERE ID='{6}'";
|
|
sql = string.Format(sql, queryParam["InvCode"].ToString(), queryParam["LocationCode"].ToString(), queryParam["Enable"].ToString(), MUSER, MUSERNAME, WorkPoint, queryParam["ID"].ToString());
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "修改失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 删除料品仓库批次关系
|
|
/// </summary>
|
|
/// <param name="keyValue">传入ID</param>
|
|
/// <returns></returns>
|
|
public string DeleteICSInventoryLocation(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
sql = string.Format(@"DELETE FROM dbo.ICSInventoryLocation WHERE ID IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 新增包装、容器
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
/// 20230717 lacy.xu Modifly 新增允许料品混装、允许项目号混装、允许批次混装、允许复用
|
|
public string InsertICSContainer(string keyValue)
|
|
{
|
|
string msg = "";
|
|
try
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
|
|
string sql = string.Empty;
|
|
sql = @"IF EXISTS(select ContainerCODE from ICSContainer where ContainerCODE='{1}' )
|
|
RAISERROR('容器：{1}，已存在！',16,1)
|
|
else
|
|
INSERT INTO dbo.ICSContainer
|
|
( ID ,ContainerID ,ContainerCODE ,ContainerName,Memo,MTIME,MUSER ,MUSERName ,WorkPoint
|
|
,InventoryMixed,ProjectMixed,BatchMixed,Multiplex,ContainerType)
|
|
Values(NEWID(),'{0}','{1}','{2}','{3}',GETDATE(),'{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}')
|
|
";
|
|
sql = string.Format(sql, queryParam["ContainerID"].ToString(), queryParam["ContainerCODE"].ToString(), queryParam["ContainerName"].ToString(), queryParam["Memo"].ToString(), MUSER, MUSERNAME, WorkPoint, queryParam["InventoryMixed"].ToString(), queryParam["ProjectMixed"].ToString(), queryParam["BatchMixed"].ToString(), queryParam["Multiplex"].ToString(), queryParam["ContainerType"].ToString());
|
|
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 修改包装、容器
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
/// 20230717 lacy.xu Modifly 新增允许料品混装、允许项目号混装、允许批次混装、允许复用
|
|
public string UpdateICSContainer(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
|
|
string sql = string.Empty;
|
|
// sql = @" IF EXISTS(select ContainerCODE from ICSContainer where ContainerCODE='{1}' )
|
|
// RAISERROR('容器：{1}，已存在！',16,1)
|
|
//UPDATE dbo.ICSContainer set ContainerID='{0}',ContainerCODE='{1}',ContainerName='{2}',Memo='{3}',MTIME=GETDATE(),MUSER='{4}',MUSERName='{5}',WorkPoint='{6}',InventoryMixed='{8}',ProjectMixed='{9}',BatchMixed='{10}',Multiplex='{11}' WHERE ID='{7}'";
|
|
sql = @"UPDATE dbo.ICSContainer set ContainerID='{0}',ContainerCODE='{1}',ContainerName='{2}',Memo='{3}',MTIME=GETDATE(),MUSER='{4}',MUSERName='{5}',WorkPoint='{6}',InventoryMixed='{8}',ProjectMixed='{9}',BatchMixed='{10}',Multiplex='{11}',ContainerType='{12}' WHERE ID='{7}'";
|
|
sql = string.Format(sql, queryParam["ContainerID"].ToString(), queryParam["ContainerCODE"].ToString(), queryParam["ContainerName"].ToString(), queryParam["Memo"].ToString(), MUSER, MUSERNAME, WorkPoint, queryParam["ID"].ToString(), queryParam["InventoryMixed"].ToString(), queryParam["ProjectMixed"].ToString(), queryParam["BatchMixed"].ToString(), queryParam["Multiplex"].ToString(), queryParam["ContainerType"].ToString());
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "修改失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 新增包装、容器关联条码
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string InsertICSContainerLot(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
sql = @"INSERT INTO dbo.ICSContainerLot
|
|
( ID ,ContainerID ,LotNo ,MTIME,MUSER ,MUSERName ,WorkPoint )
|
|
Values(NEWID(),'{0}','{1}',getdate(),'{2}','{3}','{4}')
|
|
";
|
|
sql = string.Format(sql, queryParam["ContainerID"].ToString(), queryParam["LotNo"].ToString(), MUSER, MUSERNAME, WorkPoint);
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 修改包装、容器
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string UpdateICSContainerLot(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
|
|
string sql = string.Empty;
|
|
sql = @"UPDATE dbo.ICSContainerLot set ContainerID='{0}',LotNo='{1}',MTIME=GETDATE(),MUSER='{2}',MUSERName='{3}',WorkPoint='{4}' WHERE ID='{5}'";
|
|
sql = string.Format(sql, queryParam["ContainerID"].ToString(), queryParam["LotNo"].ToString(), MUSER, MUSERNAME, WorkPoint, queryParam["ID"].ToString());
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "修改失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 获取料品信息
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public DataTable GetInvCode()
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string sql = @"
|
|
select '' as InvCode,'' as InvName
|
|
union all
|
|
SELECT InvCode,InvName FROM ICSInventory WITH (NOLOCK) WHERE 1=1 ";
|
|
string role = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode;
|
|
if (role != "admin")
|
|
{
|
|
sql += " and WorkPoint='" + WorkPoint + "'";
|
|
}
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 获取不良代码信息
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public DataTable GetBCGroup()
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string sql = @"select '' as ID,'' as BCGDesc
|
|
union all
|
|
SELECT ID,BCGDesc FROM ICSBadCodeGroup WITH (NOLOCK) WHERE 1=1 ";
|
|
string role = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode;
|
|
if (role != "admin")
|
|
{
|
|
sql += " and WorkPoint='" + WorkPoint + "'";
|
|
}
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 获取不良原因信息
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public DataTable BRGroupID()
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string sql = @"select '' as ID,'' as BRGDesc
|
|
union all
|
|
SELECT ID,BRGDesc FROM ICSBadReasonGroup WITH (NOLOCK) WHERE 1=1 ";
|
|
string role = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode;
|
|
if (role != "admin")
|
|
{
|
|
sql += " and WorkPoint='" + WorkPoint + "'";
|
|
}
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 新增料品不良信息
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string InsertInventoryBadGroup(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
sql = @"INSERT INTO dbo.ICSInventoryBadGroup
|
|
( ID,InvCode,BCGroupID,BRGroupID,Enable,MUSER,MUSERName,MTIME,WorkPoint)
|
|
Values(NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}',getdate(),'{6}')";
|
|
sql = string.Format(sql, queryParam["InvCode"].ToString(), queryParam["BCGroupID"].ToString(), queryParam["BRGroupID"].ToString(), queryParam["Enable"].ToString(), MUSER, MUSERNAME, WorkPoint);
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 修改料品不良信息
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string UpdateInventoryBadGroup(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
sql = @"UPDATE dbo.ICSInventoryBadGroup set BCGroupID='{0}',BRGroupID='{1}',Enable='{2}',MUSER='{3}',MUSERName='{4}',MTIME=GetDate(),WorkPoint='{5}' WHERE ID='{6}'";
|
|
sql = string.Format(sql, queryParam["BCGroupID"].ToString(), queryParam["BRGroupID"].ToString(), queryParam["Enable"].ToString(), MUSER, MUSERNAME, WorkPoint, queryParam["ID"].ToString());
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "修改失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 删除料品不良信息
|
|
/// </summary>
|
|
/// <param name="keyValue">传入ID</param>
|
|
/// <returns></returns>
|
|
public string DeleteInventoryBadGroup(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
sql = string.Format(@"DELETE FROM dbo.ICSInventoryBadGroup WHERE ID IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
|
|
|
|
|
|
public DataTable GetTXT(string ID)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"SELECT
|
|
ID,
|
|
WorkPointCode,
|
|
OtherDataBaseID,
|
|
WorkPointDataFlag,
|
|
SysDataFlag,
|
|
SqlTxt,
|
|
SourceName,
|
|
FormatSqlTxt,
|
|
Paras,
|
|
DbParas,
|
|
LinkCols,
|
|
CreateUserID,
|
|
CreateDate,
|
|
ModifyUserID,
|
|
ModifyDate,
|
|
Link2Cols
|
|
FROM
|
|
dbo.Sys_LableDataSource where ID='" + ID + "'";
|
|
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
|
|
public string updateLableDataSource(string Sys_LableDataSource)
|
|
{
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
string SqlTxt = "";
|
|
JArray res = (JArray)JsonConvert.DeserializeObject(Sys_LableDataSource);
|
|
foreach (var item in res)
|
|
{
|
|
JObject jo = (JObject)item;
|
|
if (jo["DbParas"].ToString().Trim() != "")
|
|
{
|
|
string[] dbvs = jo["DbParas"].ToString().Trim().Split(',');
|
|
List<string> vs = new List<string>();
|
|
foreach (string s in dbvs)
|
|
{
|
|
if (!string.IsNullOrEmpty(s))
|
|
{
|
|
vs.Add(s);
|
|
}
|
|
}
|
|
SqlTxt = string.Format(jo["FormatSqlTxt"].ToString(), vs.ToArray());
|
|
}
|
|
else
|
|
{
|
|
SqlTxt = jo["FormatSqlTxt"].ToString();
|
|
}
|
|
SqlTxt = jo["FormatSqlTxt"].ToString().Replace("'", "''");
|
|
sql += @"UPDATE dbo.Sys_LableDataSource set WorkPointCode={0},OtherDataBaseID='{1}',WorkPointDataFlag='{2}',SysDataFlag='{3}',SqlTxt='{4}',SourceName='{5}',FormatSqlTxt='{6}',Paras='{7}',DbParas='{8}',LinkCols='{9}',ModifyUserID='{10}',ModifyDate=getdate(),Link2Cols='{11}' WHERE ID='{12}'";
|
|
sql = string.Format(sql, WorkPoint, jo["OtherDataBaseID"].ToString(), jo["WorkPointDataFlag"].ToString(), jo["SysDataSourceFlag"].ToString(), SqlTxt, jo["SourceName"].ToString(), SqlTxt, jo["Paras"].ToString(), jo["DbParas"].ToString(), jo["LinkCols"].ToString(), MUSER, jo["Link2Cols"].ToString(), jo["ID"].ToString());
|
|
}
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "修改失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
public string DeleteLableDataSource(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
sql = string.Format(@"DELETE FROM dbo.Sys_LableDataSource WHERE ID IN ({0}) ", keyValue.TrimEnd(','));
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
|
|
public DataTable GetLableType()
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string sql = @"
|
|
select '' as F_ItemCode,'' as F_ItemName
|
|
union all
|
|
SELECT DISTINCT a.F_ItemCode,isnull(a.F_ItemName,'') as F_ItemName FROM dbo.Sys_SRM_ItemsDetail a left join Sys_SRM_Items b on a.F_ItemId=b.F_Id where b.F_EnCode='BQ001'";
|
|
string role = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode;
|
|
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
public DataTable GetSys_LablesID()
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string sql = @"
|
|
select '' as ID,'' as LableName
|
|
union all
|
|
select distinct ID,LableName from Sys_Lables";
|
|
string role = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode;
|
|
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
public string CreateLables(string txtLableName, string sel_LableType, string sel_LableSourceID)
|
|
{
|
|
string UserId = NFine.Code.OperatorProvider.Provider.GetCurrent().UserId;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
|
|
string LableCode = "";
|
|
string SqlTxt = string.Format(@"SELECT MAX(A.LableCode) AS LableCode FROM Sys_Lables A "); ;
|
|
DataSet ds = SqlHelper.GetDataSetBySql(SqlTxt);
|
|
DataTable dts = ds.Tables[0];
|
|
if (dts != null && dts.Rows.Count > 0)
|
|
{
|
|
string lot = dts.Rows[0]["LableCode"].ToString();
|
|
LableCode = (Convert.ToInt64(lot) + 1).ToString().PadLeft(5, '0');
|
|
}
|
|
else
|
|
{
|
|
LableCode = "00001";
|
|
}
|
|
sql += @" INSERT INTO dbo.Sys_Lables
|
|
(ID,WorkPointCode,LableType,LableCode,LableName,LableXml,LableSourceID,CreateUserID,CreateDate,ModifyUserID,ModifyDate,FileAddress )
|
|
Values(NEWID(),{0},'{1}','{2}','{3}','','{4}','{5}',getdate(),'{6}',getdate(),'')";
|
|
sql = string.Format(sql, WorkPoint, sel_LableType, LableCode, txtLableName, sel_LableSourceID, UserId, UserId);
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
|
|
public DataTable GetLabelTXT(string ID)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select ID,WorkPointCode,LableType,LableCode,LableName,LableXml,LableSourceID,CreateUserID,CreateDate,ModifyUserID,ModifyDate,FileAddress from Sys_Lables where ID='" + ID + "'";
|
|
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
|
|
public string updateLables(string ID, string txtLableName, string sel_LableType, string sel_LableSourceID)
|
|
{
|
|
string UserId = NFine.Code.OperatorProvider.Provider.GetCurrent().UserId;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
sql += @"update Sys_Lables set LableType='{0}' ,LableName='{1}' ,LableSourceID='{2}',ModifyUserID='{3}',ModifyDate=getdate() where ID='{4}'";
|
|
sql = string.Format(sql, sel_LableType, txtLableName, sel_LableSourceID, UserId, ID);
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "修改失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
public string DeleteICSLabelTemplate(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
sql = string.Format(@"DELETE FROM dbo.Sys_Lables WHERE ID IN ({0}) ", keyValue.TrimEnd(','));
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
//获取父容器
|
|
public DataTable GetContainerID()
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string sql = @"
|
|
select '' as ID,'' as ContainerName
|
|
union all
|
|
SELECT DISTINCT a.ID,a.ContainerName FROM dbo.ICSContainer a ";
|
|
string role = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode;
|
|
if (role != "admin")
|
|
{
|
|
sql += " and b.WorkPoint in(" + WorkPoint + ")";
|
|
}
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
//获取父容器
|
|
public DataTable GetContainerType()
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string sql = @"
|
|
select '' as F_ItemName,'' as F_ItemCode
|
|
union all
|
|
SELECT a.F_ItemName,a.F_ItemCode FROM Sys_SRM_ItemsDetail a
|
|
LEFT JOIN Sys_SRM_Items b ON a.F_ItemId = b.F_Id
|
|
WHERE b.F_EnCode = 'ContainerType' and a.F_EnabledMark='1' ";
|
|
string role = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode;
|
|
if (role != "admin")
|
|
{
|
|
sql += " and b.WorkPoint in(" + WorkPoint + ")";
|
|
}
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
public int UpLoadLabelFile(string ID, string fileName)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string sql = "";
|
|
sql = string.Format(@"update Sys_Lables set FileAddress='{0}'
|
|
where ID='{1}'",
|
|
fileName, ID);
|
|
|
|
StringBuilder Str = new StringBuilder(sql);
|
|
return Repository().ExecuteBySql(Str);
|
|
}
|
|
|
|
public int UpLoadFiles(string LotNo, string fileName)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string sql = "";
|
|
sql = string.Format(@"update ICSInspection set FileName='{0}'
|
|
where LotNo='{1}' and Enable='1'",
|
|
fileName, LotNo);
|
|
|
|
StringBuilder Str = new StringBuilder(sql);
|
|
return Repository().ExecuteBySql(Str);
|
|
}
|
|
|
|
//包装容器
|
|
public DataTable GetSendContainer(ref Pagination jqgridparam, string queryJson)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select top 0 ID,ContainerID,ContainerCode,ContainerName,Memo,MUSER,MUSERName,MTIME,WorkPoint from ICSContainer";
|
|
sql += " WHERE 1=1";
|
|
|
|
sql = string.Format(sql);
|
|
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
public DataTable GetContainerInfo(string Code)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @" select ID,ContainerID,ContainerCode,ContainerName,Memo,MUSER,MUSERName,MTIME,WorkPoint from ICSContainer where ContainerCode='{0}'";
|
|
sql = string.Format(sql, Code);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
public DataTable GetBindContainerID(string SireCode)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @" select ID from ICSContainer where ContainerCode='{0}'";
|
|
sql = string.Format(sql, SireCode);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
|
|
|
|
|
|
//包装容器关联条码
|
|
public DataTable GetSendContainerLot(ref Pagination jqgridparam, string queryJson)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"
|
|
select top 0 a.ID, a.LotNo,a.InvCode,b.InvName,b.InvStd,b.InvUnit, a.ProductDate,a.Quantity from ICSInventoryLot a
|
|
left join ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint";
|
|
|
|
sql = string.Format(sql);
|
|
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 回车后获取信息
|
|
/// </summary>
|
|
/// <param name="LotNo"></param>
|
|
/// <returns>20230719 lacy.xu modifly 增加自由项</returns>
|
|
public DataTable GetContainerLotInfo(string LotNo)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @" select a.ID, a.LotNo,a.InvCode,b.InvName,b.InvStd,b.InvUnit, a.ProductDate,a.Quantity
|
|
,c.ProjectCode,c.BatchCode,Version,Brand,c.cFree1,c.cFree2,c.cFree3,c.cFree4,c.cFree5,c.cFree6,c.cFree7
|
|
,c.cFree8,c.cFree9,c.cFree10
|
|
from ICSInventoryLot a
|
|
left join ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
|
|
LEFT JOIN dbo.ICSExtension c ON a.ExtensionID=c.ID AND a.WorkPoint=c.WorkPoint
|
|
where a.LotNo='{0}'";
|
|
sql = string.Format(sql, LotNo);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
/// <summary>
|
|
/// 删除站点
|
|
/// </summary>
|
|
/// <param name="keyValue">传入ID</param>
|
|
/// <returns></returns>
|
|
public string DeleteWorkPoint(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
sql = string.Format(@"DELETE FROM dbo.sys_WorkPoint WHERE ID IN ({0}) ", keyValue.TrimEnd(','), WorkPoint);
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
public DataTable GetICSInventoryLotInfo(string ID)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @" SELECT a.ID,
|
|
a.LotNo,
|
|
a.InvCode,
|
|
a.ProductDate,
|
|
a.ExpirationDate,
|
|
a.Quantity,
|
|
a.Amount,
|
|
a.ExtensionID,
|
|
a.Type,
|
|
a.PrintTimes,
|
|
a.LastPrintUser,
|
|
a.LastPrintTime,
|
|
a.MUSER,
|
|
a.MUSERName,
|
|
a.MTIME,
|
|
a.WorkPoint,
|
|
a.EATTRIBUTE1,
|
|
b.InvName,
|
|
b.InvUnit,
|
|
c.Enable
|
|
FROM
|
|
dbo.ICSInventoryLot a left join ICSInventory b on a.InvCode=b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
left join ICSInspection c on a.LotNo=c.LotNo AND a.WorkPoint=b.WorkPoint and c.Enable='1'
|
|
WHERE a.ID='" + ID + "'";
|
|
sql += " and a.WorkPoint='" + WorkPoint + "'";
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
public DataTable GetICSBatchInfo(string BatchCode, string INVCode)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @" select TOP 1 A.InvCode AS InvCode,C.InvName AS InvName,A.ExpirationDate AS CurrentExpirationDate
|
|
,B.BatchCode AS BatchCode,SUM(D.Quantity) AS Quantity from ICSInventoryLot A
|
|
LEFT JOIN ICSExtension B ON B.ID=A.ExtensionID AND B.WorkPoint=A.WorkPoint
|
|
LEFT JOIN ICSInventory C ON C.InvCode=A.InvCode AND C.WorkPoint=A.WorkPoint
|
|
LEFT JOIN ICSWareHouseLotInfo D ON D.LotNo=A.LotNo AND D.WorkPoint=A.WorkPoint
|
|
where B.BatchCode='{0}' AND A.InvCode='{1}' AND A.WorkPoint='{2}'
|
|
GROUP BY A.InvCode,A.ExpirationDate,B.BatchCode,C.InvName";
|
|
sql = string.Format(sql, BatchCode, INVCode, WorkPoint);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
|
|
public int UpdateExpirationDate(string ID, string ExpirationDate, string Inv, string LotNo, string InvCode, string Quantity)
|
|
{
|
|
//decimal QualifiedQuantity = 0;
|
|
//decimal UnqualifiedQuantity = 0;
|
|
// if (Inv == "1") { QualifiedQuantity = Convert.ToDecimal(Quantity); } else { UnqualifiedQuantity = Convert.ToDecimal(Quantity); }
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string sqls = string.Empty;
|
|
//sqls = @"update ICSInspection set Enable='0' WHERE LotNO='{0}' and Enable='1' and WorkPoint='{1}'";
|
|
//sqls = string.Format(sqls, LotNo, WorkPoint);
|
|
//SqlHelper.CmdExecuteNonQueryLi(sqls);
|
|
|
|
string sql = string.Empty;
|
|
sql += @"UPDATE ICSInventoryLot SET ExpirationDate='{0}' where ID='{1}' AND WorkPoint='{2}'";
|
|
// sql += @"INSERT INTO dbo.ICSInspection
|
|
// ( ID ,LotNo ,InvCode ,Quantity,QualifiedQuantity,UnqualifiedQuantity,WaiveQuantity,Type,Result,ExpirationDate,Enable,MUSER,MUSERName,MTIME,WorkPoint )
|
|
//Values(NEWID(),'{3}','{4}','{5}','{6}','{7}',0,'4','{8}','{0}','1','{9}','{10}',GETDATE(),'{2}')";
|
|
//sql = string.Format(sql, ExpirationDate, ID, WorkPoint, LotNo, InvCode, Quantity, QualifiedQuantity, UnqualifiedQuantity, Inv, MUSER, MUSERNAME);
|
|
sql = string.Format(sql, ExpirationDate, ID, WorkPoint);
|
|
int count = SqlHelper.CmdExecuteNonQueryLi(sql);
|
|
return count;
|
|
}
|
|
|
|
public int UpdateBatchExpirationDate(string ExpirationDate, string BatchCode, string InvCode)
|
|
{
|
|
try
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string sqls = string.Empty;
|
|
sqls = @"UPDATE A
|
|
SET A.ExpirationDate='{0}'
|
|
FROM ICSInventoryLot A
|
|
LEFT JOIN ICSExtension B ON B.ID=A.ExtensionID AND B.WorkPoint=A.WorkPoint
|
|
WHERE A.InvCode='{1}' AND B.BatchCode='{2}' AND A.WorkPoint='{3}'";
|
|
sqls = string.Format(sqls, ExpirationDate, InvCode, BatchCode, WorkPoint);
|
|
string Inputstr = "";
|
|
Inputstr += "{";
|
|
Inputstr += "\"ORGCode\":\"" + WorkPoint + "\",";
|
|
Inputstr += "\"ModifyUserCode\":\"" + MUSER + "\",";
|
|
Inputstr += "\"ListLot\": [{";
|
|
Inputstr += "\"LotCode\":\"" + BatchCode + "\",";
|
|
Inputstr += "\"INVCode\":\"" + InvCode + "\",";
|
|
Inputstr += "\"InvalidTime\":\"" + ExpirationDate + "\"";
|
|
Inputstr += "}]";
|
|
Inputstr += "}";
|
|
string APIURL = ConfigurationManager.ConnectionStrings["ERPAPIURL"].ConnectionString + "ModifyBatch/Modify";
|
|
string result = HttpPost(APIURL, Inputstr);
|
|
JObject Obj = (JObject)JsonConvert.DeserializeObject(result);
|
|
string MessAge = Obj["Message"].ToString();
|
|
string Success = Obj["Success"].ToString();
|
|
if (Success.ToUpper() == "FALSE")
|
|
{
|
|
throw new Exception(MessAge);
|
|
}
|
|
else
|
|
{
|
|
int count = SqlHelper.CmdExecuteNonQueryLi(sqls);
|
|
return count;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
|
|
public string CmdExecuteData(string sql, string MTDOCCode, string Identification)
|
|
{
|
|
string msg = "";
|
|
try
|
|
{
|
|
|
|
string connString = SqlHelper.DataCenterConnString;
|
|
SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString);
|
|
conn.Open();
|
|
SqlTransaction sqlTran = conn.BeginTransaction();
|
|
SqlCommand cmd = new SqlCommand();
|
|
cmd.Transaction = sqlTran;
|
|
cmd.Connection = conn;
|
|
try
|
|
{
|
|
DataSet DSet = SqlCommandHelper.SQlReturnDataSet(sql, cmd);
|
|
|
|
string Inputstr = SqlHelper.DataSetToJson(DSet, "details", "Costre");
|
|
string APIURL = ConfigurationManager.ConnectionStrings["ERPAPIURL"].ConnectionString + "MorphologicalTransformationDoc/Create";
|
|
string result = HttpPost(APIURL, Inputstr);
|
|
JObject Obj = (JObject)JsonConvert.DeserializeObject(result);//或者JObject jo = JObject.Parse(jsonText);
|
|
string MessAge = Obj["Message"].ToString();
|
|
string Success = Obj["Success"].ToString();
|
|
string ERPSql = "";
|
|
if (Success.ToUpper() == "FALSE")
|
|
{
|
|
throw new Exception(MessAge);
|
|
}
|
|
else
|
|
{
|
|
try
|
|
{
|
|
JArray res = (JArray)JsonConvert.DeserializeObject(Obj["Data"].ToString());
|
|
foreach (var item in res)
|
|
{
|
|
JObject jo = (JObject)item;
|
|
JArray resdetail = (JArray)JsonConvert.DeserializeObject(jo["details"].ToString());
|
|
foreach (var detail in resdetail)
|
|
{
|
|
JObject det = (JObject)detail;
|
|
ERPSql += @"UPDATE a set ERPID='{3}',ERPDetailID='{4}',ERPCode='{5}',ERPSequence='{6}',ERPUpload='1'
|
|
from ICSWareHouseLotInfoLog a
|
|
inner join ICSMTDOC b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint
|
|
where b.MTDOCCode='{0}' and b.Sequence='{1}' and a.Identification='{2}'";
|
|
ERPSql += @" update ICSMTDOC set MTDOCID='{3}',MTDOCDetailID='{4}' where MTDOCCode='{0}' and Sequence='{1}'";
|
|
ERPSql = string.Format(ERPSql, MTDOCCode, det["Sequence"].ToString(), Identification, jo["ID"].ToString(), det["DetailID"].ToString(), jo["MTCode"].ToString(), det["Sequence"].ToString());
|
|
}
|
|
}
|
|
SqlCommandHelper.CmdExecuteNonQuery(ERPSql, cmd);
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
}
|
|
|
|
cmd.Transaction.Commit();
|
|
//return dt;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
cmd.Transaction.Rollback();
|
|
msg = ex.Message;
|
|
}
|
|
finally
|
|
{
|
|
if (conn.State == ConnectionState.Open)
|
|
{
|
|
conn.Close();
|
|
}
|
|
conn.Dispose();
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
|
|
}
|
|
return msg;
|
|
|
|
}
|
|
|
|
//修改多语言
|
|
public string UpdateSys_Language(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
|
|
string sql = string.Empty;
|
|
sql = @"UPDATE Sys_Language set Code='{0}',CnValue='{1}',TwValue='{2}',EnValue='{3}' WHERE ID='{4}'";
|
|
sql = string.Format(sql, queryParam["Code"].ToString(), queryParam["CnValue"].ToString(), queryParam["TwValue"].ToString(), queryParam["EnValue"].ToString(), queryParam["ID"].ToString());
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "修改失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
//新增多语言
|
|
public string InsertSys_Language(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
sql = @" IF EXISTS(SELECT * FROM Sys_Language WHERE Code='{0}' )
|
|
begin
|
|
RAISERROR('代码重复请重新输入！',16,1)
|
|
end
|
|
INSERT INTO Sys_Language
|
|
( ID ,Code ,CnValue ,TwValue,EnValue )
|
|
Values(NEWID(),'{0}','{1}','{2}','{3}')
|
|
";
|
|
sql = string.Format(sql, queryParam["Code"].ToString(), queryParam["CnValue"].ToString(), queryParam["TwValue"].ToString(), queryParam["EnValue"].ToString());
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
|
|
public string GetSerialCode(string workPointCode, string tbName, string colName, string Pre, int numLen)
|
|
{
|
|
string sql = "EXEC Addins_GetSerialCode '{0}','{1}','{2}','{3}',{4}";
|
|
sql = string.Format(sql, new object[] { workPointCode, tbName, colName, Pre, numLen });
|
|
return DbHelper.ExecuteScalar(CommandType.Text, sql).ToString();
|
|
}
|
|
|
|
|
|
|
|
public DataTable GetSys_Language(string ID)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select ID,Code,CnValue,TwValue,EnValue from Sys_Language
|
|
WHERE 1 =1 and ID='" + ID + "' ";
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 删除多语言
|
|
/// </summary>
|
|
/// <param name="keyValue">传入ID</param>
|
|
/// <returns></returns>
|
|
public string DeleteSys_Language(string keyValue)
|
|
{
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
sql = string.Format(@"DELETE FROM Sys_Language WHERE ID IN ({0})", keyValue.TrimEnd(','));
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
//料品检验项目
|
|
public DataTable GetICSInventoryInspectionGroup(ref Pagination jqgridparam, string queryJson)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select ID,InvCode,GroupCode,GroupName,
|
|
case when Enable='1' then '是' else '否' end as Enable,
|
|
EATTRIBUTE1,EATTRIBUTE2,EATTRIBUTE3,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7,EATTRIBUTE8,
|
|
EATTRIBUTE9,EATTRIBUTE10
|
|
MUSER,MUSERName,MTIME,WorkPoint from ICSInventoryInspectionGroup ";
|
|
sql += " WHERE 1=1";
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
|
|
{
|
|
sql += " and InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["GroupCode"].ToString()))
|
|
{
|
|
sql += " and GroupCode like '%" + queryParam["GroupCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["GroupName"].ToString()))
|
|
{
|
|
sql += " and GroupName like '%" + queryParam["GroupName"].ToString() + "%' ";
|
|
}
|
|
}
|
|
sql = string.Format(sql);
|
|
DataTable dttest = Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
|
|
//料品检验项目子表
|
|
public DataTable ICSInventoryInspectionList(string ID, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @" select ID,InvGroupID,ListCode,ListName,Unit,SetValueMax, SetValueMin,
|
|
CASE WHEN Enable=0 THEN '否' else '是' END Enable,
|
|
MUSERName,MTIME,WorkPoint from ICSInventoryInspectionList
|
|
WHERE InvGroupID='" + ID + "'";
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
|
|
|
|
|
|
// 删除料品检验项目子表
|
|
|
|
public string DeleteICSInventoryInspectionGroup(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
|
|
sql += string.Format(@"DELETE FROM dbo.ICSInventoryInspectionGroup WHERE ID IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
sql += string.Format(@"DELETE FROM dbo.ICSInventoryInspectionList WHERE InvGroupID IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
try
|
|
{
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
public DataTable Getinv(string invcode, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
DataTable table = new DataTable();
|
|
string wherestr = "";
|
|
if (!string.IsNullOrEmpty(invcode))
|
|
{
|
|
wherestr += " and invcode like '%" + invcode + "%'";
|
|
}
|
|
string sql = "select ID,InvCode,InvName,InvDesc,InvUnit,WorkPoint from ICSInventory where workpoint='" + WorkPoint + "'" + wherestr;
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
|
|
}
|
|
|
|
public DataTable GetGroupCode()
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string role = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode;
|
|
string sql = @"select '' as ID,'' as GroupName
|
|
union all
|
|
SELECT distinct ID,GroupName FROM ICSInspectionGroup where WorkPoint='{0}'";
|
|
sql = string.Format(sql, WorkPoint);
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
public void DeleteINvItem(string ID)
|
|
{
|
|
string sql = "DELETE FROM dbo.ICSInventoryInspectionList WHERE ID='" + ID + "'";
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
|
|
public DataTable GetICSInventoryInspectionList(string ListCode, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select ID,ListCode,ListName,Unit,SetValueMax,SetValueMin,Enable,MUSERName,MTIME from ICSInventoryInspectionList
|
|
WHERE ListCode='" + ListCode + "' ";
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
|
|
public DataTable GetInspectionListInfo(string ID)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"
|
|
select ID,ListCode,ListName,Unit,SetValueMax,SetValueMin, CASE WHEN Enable=0 THEN '否' else '是' END Enable
|
|
from ICSInspectionList where GroupID='{0}'";
|
|
sql = string.Format(sql, ID);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
|
|
|
|
|
|
public DataTable GetICSInventoryInspectionList2(string BID)
|
|
{
|
|
string sql = string.Empty;
|
|
DataTable dt = null;
|
|
sql = string.Format(@" select ID,ListCode,ListName,Unit,SetValueMax,SetValueMin, CASE WHEN Enable='1' THEN '是' else '否' END Enable from ICSInventoryInspectionList where InvGroupID='{0}'", BID);
|
|
dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
|
|
//库位导入
|
|
public string UploadFileInvBadGroup(String savePath)
|
|
{
|
|
//数据获取
|
|
try
|
|
{
|
|
int index = 1;
|
|
string msg = "";
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string MTIME = DateTime.Now.ToString();
|
|
SqlConnection conn = SqlHelper.GetDataCenterConn();
|
|
string sql = "";
|
|
int count = 0;
|
|
DataTable data = FileToExcel.ExcelToTable(savePath);
|
|
foreach (DataRow dr in data.Rows)
|
|
{
|
|
index++;
|
|
var Enable = "";
|
|
string InvCode = dr["料品代码"].ToString().Trim().ToUpper();
|
|
string BCGroupCode = dr["不良代码组代码"].ToString().Trim().ToUpper();
|
|
string BRGroupCode = dr["不良原因组代码"].ToString().Trim().ToUpper();
|
|
string Enables = dr["是否启用"].ToString().Trim().ToUpper();
|
|
|
|
if (InvCode == "" || InvCode == null)
|
|
{
|
|
throw new Exception("第 " + index + " 行料品代码不能为空！");
|
|
}
|
|
if (BCGroupCode == "" || BCGroupCode == null)
|
|
{
|
|
throw new Exception("第 " + index + " 行不良代码组代码不能为空！");
|
|
}
|
|
if (BRGroupCode == "" || BRGroupCode == null)
|
|
{
|
|
throw new Exception("第 " + index + " 行不良原因组代码不能为空！");
|
|
}
|
|
if (Enables == "" || Enables == null)
|
|
{
|
|
throw new Exception("第 " + index + " 行是否启用不能为空！");
|
|
}
|
|
string sqlWH = @"select ID from dbo.ICSInventory where InvCode='{0}' and WorkPoint='{1}'";
|
|
sqlWH += @" select ID from ICSBadCodeGroup where BCGCode='{2}' and WorkPoint='{1}'";
|
|
sqlWH += @" select ID from ICSBadReasonGroup where BRGCode='{3}' and WorkPoint='{1}'";
|
|
sqlWH += @" select a.ID from ICSInventoryBadGroup a
|
|
inner join ICSBadCodeGroup b on a.BCGroupID=b.ID and a.WorkPoint=b.WorkPoint
|
|
inner join ICSBadReasonGroup c on a.BRGroupID=c.ID and a.WorkPoint=b.WorkPoint
|
|
where a.InvCode='{0}' and b.BCGCode='{2}'and c.BRGCode='{3}' and a.WorkPoint='{1}'";
|
|
sqlWH = string.Format(sqlWH, InvCode, WorkPoint, BCGroupCode, BRGroupCode);
|
|
DataSet ds = SqlHelper.GetDataSetBySql(sqlWH);
|
|
|
|
if (ds.Tables[0] == null || ds.Tables[0].Rows.Count <= 0)
|
|
{
|
|
throw new Exception("料品代码: " + InvCode + " 不存在！");
|
|
}
|
|
if (ds.Tables[1] == null || ds.Tables[1].Rows.Count <= 0)
|
|
{
|
|
throw new Exception("不良代码组代码: " + BCGroupCode + " 不存在！");
|
|
}
|
|
if (ds.Tables[2] == null || ds.Tables[2].Rows.Count <= 0)
|
|
{
|
|
throw new Exception("不良原因组代码: " + BRGroupCode + " 不存在！");
|
|
}
|
|
if (ds.Tables[3].Rows.Count > 0)
|
|
{
|
|
throw new Exception("第 " + index + " 行,料品: " + InvCode + " 已绑定相同不良信息！");
|
|
}
|
|
if (Enables == "是")
|
|
{
|
|
Enable = "1";
|
|
}
|
|
else
|
|
{
|
|
Enable = "0";
|
|
}
|
|
|
|
sql += @"INSERT INTO dbo.ICSInventoryBadGroup
|
|
( ID,InvCode,BCGroupID,BRGroupID,Enable,MUSER,MUSERName,MTIME,WorkPoint)
|
|
Values(NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')";
|
|
|
|
sql = string.Format(sql, InvCode, ds.Tables[1].Rows[0]["ID"].ToString(), ds.Tables[2].Rows[0]["ID"].ToString(), Enable, MUSER, MUSERNAME, MTIME, WorkPoint);
|
|
}
|
|
|
|
count = SqlHelper.CmdExecuteNonQueryLi(sql);
|
|
if (count > 0)
|
|
{
|
|
msg = "导入成功";
|
|
}
|
|
else
|
|
{
|
|
return "无有效的导入数据。";
|
|
|
|
}
|
|
return msg;
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return ex.Message;
|
|
|
|
}
|
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 根据ID获取其他站点
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public DataTable GetWorkPointNotInID(string WorkPointCode)
|
|
{
|
|
//string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string sql = @"select '' as WorkPointCode,'' as WorkPointName union all
|
|
SELECT WorkPointCode,WorkPointName FROM dbo.Sys_WorkPoint WITH (NOLOCK) WHERE WorkPointCode <>'{0}'
|
|
";
|
|
sql = string.Format(sql, WorkPointCode.TrimEnd(','));
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public string CloneWorkPointByCode(string WorkPoint, string WorkPointCode)
|
|
{
|
|
string msg = "";
|
|
//string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERName = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string sql = string.Empty;
|
|
var ID = Guid.NewGuid();
|
|
//克隆参数配置表
|
|
sql += @"INSERT INTO ICSConfiguration(ID, Code, Name, Enable, Figure, MTIME, MUSER, MUSERName, WorkPoint)
|
|
SELECT newid(),Code,Name,Enable,Figure,getdate(),'{0}','{1}','{2}'
|
|
FROM ICSConfiguration
|
|
where WorkPoint='{3}'
|
|
";
|
|
|
|
//克隆列类型
|
|
sql += @"INSERT INTO ICSType(ID, TableCode, ColumnCode, Code, Name, Enable, MTIME, MUSER, MUSERName, WorkPoint)
|
|
SELECT newid(),TableCode,ColumnCode,Code,Name,Enable,getdate(),'{0}','{1}','{2}'
|
|
FROM ICSType
|
|
where WorkPoint='{3}'
|
|
";
|
|
|
|
//克隆admin
|
|
// sql += @"INSERT INTO Sys_SRM_User( F_Id, F_Account, F_RealName,F_NickName,F_HeadIcon,F_Gender,F_Birthday,F_MobilePhone,F_Email,F_WeChat,F_ManagerId,F_SecurityLevel,F_Signature,F_OrganizeId,F_DepartmentId,F_RoleId,F_DutyId,F_IsAdministrator,F_SortCode,F_DeleteMark,F_EnabledMark,F_Description,F_CreatorTime,F_CreatorUserId,F_LastModifyTime,F_LastModifyUserId,F_DeleteTime,F_DeleteUserId,F_Location,F_VenCode)
|
|
// SELECT '{4}',F_Account,F_RealName,F_NickName,F_HeadIcon,F_Gender,F_Birthday,F_MobilePhone,F_Email,F_WeChat,F_ManagerId,F_SecurityLevel,F_Signature,F_OrganizeId,F_DepartmentId,F_RoleId,F_DutyId,F_IsAdministrator,F_SortCode,F_DeleteMark,F_EnabledMark,F_Description,getdate(),F_CreatorUserId,F_LastModifyTime,F_LastModifyUserId,F_DeleteTime,F_DeleteUserId,'{2}',F_VenCode
|
|
// FROM Sys_SRM_User
|
|
// where F_Location='{3}' and F_Account='admin'
|
|
//";
|
|
|
|
//克隆admin登陆
|
|
// sql += @"INSERT INTO Sys_SRM_UserLogOn( F_Id, F_UserId, F_UserPassword, F_UserSecretkey, F_AllowStartTime, F_AllowEndTime, F_LockStartDate, F_LockEndDate, F_FirstVisitTime, F_PreviousVisitTime, F_LastVisitTime, F_ChangePasswordDate, F_MultiUserLogin, F_LogOnCount, F_UserOnLine, F_Question, F_AnswerQuestion, F_CheckIPAddress, F_Language, F_Theme)
|
|
// SELECT '{4}','{4}',F_UserPassword,F_UserSecretkey,F_AllowStartTime,F_AllowEndTime,F_LockStartDate,F_LockEndDate,F_FirstVisitTime,F_PreviousVisitTime,F_LastVisitTime,F_ChangePasswordDate,F_MultiUserLogin,F_LogOnCount,F_UserOnLine,F_Question,F_AnswerQuestion,F_CheckIPAddress,F_Language,F_Theme
|
|
// from dbo.Sys_SRM_UserLogOn a
|
|
// left join dbo.Sys_SRM_User b on a.F_UserId=b.F_Id
|
|
// where b.F_Account='admin' and b.F_Location='{3}'
|
|
//";
|
|
|
|
|
|
sql = string.Format(sql, MUSER, MUSERName, WorkPointCode, WorkPoint, ID);
|
|
try
|
|
{
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "克隆失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
|
|
public string DeleteTableByCode(string TableCode)
|
|
{
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
JArray res = (JArray)JsonConvert.DeserializeObject(TableCode);
|
|
foreach (var item in res)
|
|
{
|
|
JObject jo = (JObject)item;
|
|
sql += @" delete " + jo["Code"].ToString() + " where WorkPoint='" + WorkPoint + "' ";
|
|
|
|
}
|
|
try
|
|
{
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "清除失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
//throw new Exception(ex.Message);
|
|
msg = ex.Message;
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
|
|
/// 抽检规则表查询
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <param name="jqgridparam"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetICSInspectionRulesList(string GroupCode, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select RulesID,SampleQuantity,SetValueMax,SetValueMin,Enable,MUSERName,MTIME,EATTRIBUTE1,EATTRIBUTE2,EATTRIBUTE3,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7,EATTRIBUTE8,
|
|
EATTRIBUTE9,EATTRIBUTE10
|
|
from ICSInspectionRulesList
|
|
WHERE RulesID='" + GroupCode + "' ";
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 动态子表修改加载页面
|
|
/// </summary>
|
|
/// <param name="GroupCode"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetICSInspectionRulesList2(string RulesCode)
|
|
{
|
|
string sql = string.Empty;
|
|
DataTable dt = null;
|
|
sql = string.Format(@"SELECT b.ID,RulesID,SampleQuantity,SetValueMax,SetValueMin,
|
|
case when b.Enable='1' then '是' else '否' end as Enable,
|
|
b.MUSER,b.MUSERName,b.MTIME,b.WorkPoint,b.EATTRIBUTE1
|
|
FROM ICSInspectionRulesGroup a
|
|
LEFT JOIN dbo.ICSInspectionRulesList b ON a.id=b.RulesID AND a.WorkPoint=b.WorkPoint
|
|
where RulesCode='{0}'", RulesCode);
|
|
dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 初次加载查询抽检规则
|
|
/// </summary>
|
|
/// <param name="jqgridparam"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetICSInspectionRulesGroup(ref Pagination jqgridparam, string queryJson)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select a.ID,RulesCode,RulesName,a.Enable,a.MUSER,a.MUSERName,a.MTIME,RulesDesc,b.Name Type
|
|
from ICSInspectionRulesGroup a
|
|
LEFT JOIN dbo.ICSType b ON a.Type=b.Code AND a.WorkPoint=b.WorkPoint ";
|
|
sql += " WHERE 1=1";
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["RulesCode"].ToString()))
|
|
{
|
|
sql += " and RulesCode like '%" + queryParam["RulesCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["RulesName"].ToString()))
|
|
{
|
|
sql += " and RulesName like '%" + queryParam["RulesName"].ToString() + "%' ";
|
|
}
|
|
}
|
|
sql = string.Format(sql);
|
|
DataTable dttest = Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
/// <summary>
|
|
/// 删除抽检规则
|
|
/// </summary>
|
|
/// <param name="keyValue">传入ID</param>
|
|
/// <returns></returns>
|
|
public string DeleteICSInspectionRulesGroup(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
try
|
|
{
|
|
sql = string.Format(@"SELECT rulescode FROM dbo.ICSInventoryInspectionRulesGroup
|
|
WHERE rulescode IN (({0}))", keyValue.TrimEnd(','));
|
|
DataTable dtASN = SqlHelper.GetDataTableBySql(sql);
|
|
if (dtASN != null && dtASN.Rows.Count > 0)
|
|
{
|
|
throw new Exception("所删除规则，已被物料绑定!");
|
|
}
|
|
sql = string.Format(@"DELETE a FROM dbo.ICSInspectionRulesList a
|
|
LEFT JOIN ICSInspectionRulesGroup b ON a.RulesID=b.ID AND a.WorkPoint=b.WorkPoint
|
|
WHERE RulesCode IN ({0}) and a.WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
sql += string.Format(@"DELETE FROM dbo.ICSInspectionRulesGroup
|
|
WHERE RulesCode IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
//if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
//{
|
|
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
//}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
return msg;
|
|
}
|
|
/// <summary>
|
|
/// 抽检规则表查询
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <param name="jqgridparam"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetICSInspectionRulesListBYCode(string RulesCode, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select RulesID,SampleQuantity,SetValueMax,SetValueMin,a.Enable,a.MUSERName,a.MTIME,a.EATTRIBUTE1,a.EATTRIBUTE2,a.EATTRIBUTE3,a.EATTRIBUTE4,a.EATTRIBUTE5,a.EATTRIBUTE6,a.EATTRIBUTE7,a.EATTRIBUTE8,
|
|
a.EATTRIBUTE9,a.EATTRIBUTE10
|
|
from ICSInspectionRulesList a
|
|
LEFT JOIN ICSInspectionRulesGroup b ON a.RulesID=b.ID AND a.WorkPoint=b.WorkPoint
|
|
WHERE b.RulesCode='" + RulesCode + "' ";
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
//抽检规则导入
|
|
public string UploadFileInspectionRulesList(String savePath)
|
|
{
|
|
//数据获取
|
|
try
|
|
{
|
|
int index = 1;
|
|
string msg = "";
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string MTIME = DateTime.Now.ToString();
|
|
SqlConnection conn = SqlHelper.GetDataCenterConn();
|
|
string sql = "";
|
|
int count = 0;
|
|
DataTable data = FileToExcel.ExcelToTable(savePath);
|
|
var parent = data.DefaultView.ToTable(true, "抽检规则代码", "抽检规则名称", "抽检规则描述", "是否启用", "抽检类型");
|
|
parent.PrimaryKey = new DataColumn[] { parent.Columns["column1"], parent.Columns["column2"], parent.Columns["column3"], parent.Columns["column4"], parent.Columns["column5"] };
|
|
DataTable distinctTable = parent.DefaultView.ToTable(true);
|
|
foreach (DataRow distinctds in distinctTable.Rows)
|
|
{
|
|
string ID = Guid.NewGuid().ToString();
|
|
index++;
|
|
var Enable = "";
|
|
string RulesCode = distinctds["抽检规则代码"].ToString().Trim();
|
|
string RulesName = distinctds["抽检规则名称"].ToString().Trim();
|
|
string RulesDesc = distinctds["抽检规则描述"].ToString().Trim();
|
|
string Enables = distinctds["是否启用"].ToString().Trim();
|
|
string Type = distinctds["抽检类型"].ToString().Trim();
|
|
string SetValueMin = distinctds["批量下限"].ToString().Trim();
|
|
string SetValueMax = distinctds["批量上限"].ToString().Trim();
|
|
string SampleQuantity = distinctds["取样数量"].ToString().Trim();
|
|
#region 判断必填项
|
|
if (RulesCode == "" || RulesCode == null)
|
|
{
|
|
throw new Exception("第 " + index + " 行抽检规则代码不能为空！");
|
|
}
|
|
if (RulesName == "" || RulesName == null)
|
|
{
|
|
throw new Exception("第 " + index + " 行抽检规则名称不能为空！");
|
|
}
|
|
if (RulesDesc == "" || RulesDesc == null)
|
|
{
|
|
throw new Exception("第 " + index + " 行不抽检规则描述不能为空！");
|
|
}
|
|
if (Enables == "" || Enables == null)
|
|
{
|
|
throw new Exception("第 " + index + " 行是否启用不能为空！");
|
|
}
|
|
if (Type == "" || Type == null)
|
|
{
|
|
throw new Exception("第 " + index + " 行抽检类型不能为空！");
|
|
}
|
|
if (SetValueMin == "" || SetValueMin == null)
|
|
{
|
|
throw new Exception("第 " + index + " 行批量下限不能为空！");
|
|
}
|
|
if (SetValueMax == "" || SetValueMax == null)
|
|
{
|
|
throw new Exception("第 " + index + " 行批量上限不能为空！");
|
|
}
|
|
if (SampleQuantity == "" || SampleQuantity == null)
|
|
{
|
|
throw new Exception("第 " + index + " 行取样数量不能为空！");
|
|
}
|
|
#endregion
|
|
if (Enables == "是")
|
|
{
|
|
Enable = "1";
|
|
}
|
|
else
|
|
{
|
|
Enable = "0";
|
|
}
|
|
|
|
sql += @"INSERT INTO dbo.ICSInspectionRulesGroup
|
|
( ID,RulesCode,RulesName,RulesDesc,Enable,MUSER,MUSERName,MTIME,WorkPoint,Type)
|
|
Values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{9}')";
|
|
DataRow[] dss = data.Select("抽检规则代码='" + distinctds["抽检规则代码"].ToString().Trim().ToUpper() + "'");
|
|
foreach (var item in dss)
|
|
{
|
|
var SetValueMinVal = item["批量下限"].ToString();
|
|
var SetValueMaxVal = item["批量上限"].ToString();
|
|
var SampleQuantityVal = item["取样数量"].ToString();
|
|
sql += @"INSERT INTO dbo.ICSInspectionRulesList
|
|
( ID,RulesID,SetValueMin,SetValueMax,SampleQuantity,Enable,MUSER,MUSERName,MTIME,WorkPoint,EATTRIBUTE1,EATTRIBUTE2,EATTRIBUTE3)
|
|
Values(NEWID(),'{0}','{10}','{11}','{12}','{4}','{5}','{6}','{7}','{8}')";
|
|
sql = string.Format(sql, ID, RulesCode, RulesName, RulesDesc, Enable, MUSER, MUSERNAME, MTIME, WorkPoint, Type, SetValueMinVal, SetValueMaxVal, SampleQuantityVal);
|
|
}
|
|
count = SqlHelper.CmdExecuteNonQueryLi(sql);
|
|
if (count > 0)
|
|
{
|
|
msg = "导入成功";
|
|
}
|
|
else
|
|
{
|
|
return "无有效的导入数据。";
|
|
|
|
}
|
|
}
|
|
return msg;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return ex.Message;
|
|
|
|
}
|
|
|
|
}
|
|
|
|
|
|
//检验项目组导入
|
|
public string UploadFileInspectionGoup(String savePath)
|
|
{
|
|
//数据获取
|
|
try
|
|
{
|
|
int index = 1;
|
|
string msg = "";
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string MTIME = DateTime.Now.ToString();
|
|
SqlConnection conn = SqlHelper.GetDataCenterConn();
|
|
string sql = "";
|
|
int count = 0;
|
|
DataTable data = FileToExcel.ExcelToTable(savePath);
|
|
//var parent = data.DefaultView.ToTable(true, "抽检规则代码", "抽检规则名称", "抽检规则描述", "是否启用", "抽检类型");
|
|
//parent.PrimaryKey = new DataColumn[] { parent.Columns["column1"], parent.Columns["column2"], parent.Columns["column3"], parent.Columns["column4"], parent.Columns["column5"] };
|
|
//DataTable distinctTable = parent.DefaultView.ToTable(true);
|
|
|
|
if (data != null && data.Rows.Count > 0)
|
|
{
|
|
var checkItmes = new List<string> {
|
|
"检验项目组代码",
|
|
"检验项目组名称",
|
|
"检验项目代码",
|
|
"检验项目名称" };
|
|
sql = "DECLARE @ID nvarchar(50); ";
|
|
foreach (DataRow distinctds in data.Rows)
|
|
{
|
|
string ID = Guid.NewGuid().ToString();
|
|
index++;
|
|
|
|
for (int idx = 0; idx < checkItmes.Count; idx++)
|
|
{
|
|
var tmp = distinctds[checkItmes[idx]].ToString();
|
|
if (string.IsNullOrWhiteSpace(tmp))
|
|
throw new Exception($"第 {index} 行{checkItmes[idx]}不能为空！");
|
|
}
|
|
|
|
|
|
string GroupCode = distinctds["检验项目组代码"].ToString().Trim();
|
|
string GroupName = distinctds["检验项目组名称"].ToString().Trim();
|
|
string Code = distinctds["检验项目代码"].ToString().Trim();
|
|
string Name = distinctds["检验项目名称"].ToString().Trim();
|
|
string Unit = distinctds["单位"].ToString().Trim();
|
|
string Min = distinctds["标准下限"].ToString().Trim().IsEmpty() ? "null" : distinctds["标准下限"].ToString();
|
|
string Max = distinctds["标准上限"].ToString().Trim().IsEmpty() ? "null" : distinctds["标准上限"].ToString();
|
|
string Enable = distinctds["是否启用"].ToString().Trim().ToUpper().Equals("是") ? "1" : "0";
|
|
string InspectionEnable = distinctds["抽检实际值是否必填"].ToString().Trim().ToUpper().Equals("是") ? "1" : "0";
|
|
string InspectionType = distinctds["检验类型"].ToString().Trim().ToUpper().Contains("尺寸") ? "1" : "2";
|
|
string EATTRIBUTE1 = distinctds["检验标准"].ToString().Trim();
|
|
string EATTRIBUTE2 = distinctds["抽样水准"].ToString().Trim();
|
|
string EATTRIBUTE3 = distinctds["允收标准"].ToString().Trim();
|
|
|
|
sql += $@" SET @ID=(select ID from ICSInspectionGroup where GroupCode='{GroupCode}');
|
|
IF @ID IS NOT NULL
|
|
BEGIN
|
|
IF EXISTS(select ListCode from ICSInspectionList where ListCode='{Code}' and GroupID=@ID and WorkPoint='{WorkPoint}')
|
|
BEGIN
|
|
RAISERROR('系统中已存在相同编号的检验项目代码！',16,1);
|
|
RETURN
|
|
end
|
|
INSERT INTO dbo.ICSInspectionList
|
|
( ID ,GroupID,ListCode ,ListName, Unit,SetValueMax ,SetValueMin,Enable,MTIME,MUSER ,MUSERName ,WorkPoint,EATTRIBUTE1,EATTRIBUTE2,EATTRIBUTE3 )
|
|
Values(NEWID(),@ID,'{Code}','{Name}','{Unit}',{Max},{Min},'{Enable}',getdate(),'{MUSER}','{MUSERNAME}','{WorkPoint}','{EATTRIBUTE1}','{EATTRIBUTE2}','{EATTRIBUTE3}')
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
INSERT INTO dbo.ICSInspectionGroup
|
|
( ID ,GroupCode ,GroupName,Enable, MTIME,MUSER ,MUSERName ,WorkPoint,EATTRIBUTE1,EATTRIBUTE2 )
|
|
Values('{ID}','{GroupCode}','{GroupName}','{Enable}',GETDATE(),'{MUSER}','{MUSERNAME}','{WorkPoint}','{InspectionType}','{InspectionEnable}');
|
|
IF EXISTS(select ListCode from ICSInspectionList where ListCode='{Code}' and GroupID=@ID and WorkPoint='{WorkPoint}')
|
|
BEGIN
|
|
RAISERROR('系统中已存在相同编号的检验项目代码！',16,1);
|
|
RETURN
|
|
end
|
|
INSERT INTO dbo.ICSInspectionList
|
|
( ID ,GroupID,ListCode ,ListName, Unit,SetValueMax ,SetValueMin,Enable,MTIME,MUSER ,MUSERName ,WorkPoint,EATTRIBUTE1,EATTRIBUTE2,EATTRIBUTE3 )
|
|
Values(NEWID(),'{ID}','{Code}','{Name}','{Unit}',{Max},{Min},'{Enable}',getdate(),'{MUSER}','{MUSERNAME}','{WorkPoint}','{EATTRIBUTE1}','{EATTRIBUTE2}','{EATTRIBUTE3}')
|
|
END
|
|
SET @ID= NULL;";
|
|
|
|
|
|
}
|
|
count = SqlHelper.CmdExecuteNonQueryLi(sql);
|
|
if (count > 0)
|
|
{
|
|
msg = "导入成功";
|
|
}
|
|
else
|
|
{
|
|
return "无有效的导入数据。";
|
|
|
|
}
|
|
}
|
|
return msg;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return ex.Message;
|
|
|
|
}
|
|
|
|
}
|
|
|
|
|
|
|
|
/// <summary>
|
|
/// 物料抽检规则绑定导入
|
|
/// </summary>
|
|
/// <param name="savePath"></param>
|
|
/// <returns></returns>
|
|
public string UploadFileInvInspectionRulesGroup(String savePath)
|
|
{
|
|
//数据获取
|
|
try
|
|
{
|
|
int index = 1;
|
|
string msg = "";
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string MTIME = DateTime.Now.ToString();
|
|
SqlConnection conn = SqlHelper.GetDataCenterConn();
|
|
string sql = "";
|
|
int count = 0;
|
|
DataTable data = FileToExcel.ExcelToTable(savePath);
|
|
if (data != null && data.Rows.Count > 0)
|
|
{
|
|
var checkItmes = new List<string> {
|
|
"料品编码",
|
|
"检验项目组",
|
|
"是否可编辑"
|
|
|
|
};
|
|
foreach (DataRow distinctds in data.Rows)
|
|
{
|
|
string ID = Guid.NewGuid().ToString();
|
|
index++;
|
|
|
|
for (int idx = 0; idx < checkItmes.Count; idx++)
|
|
{
|
|
var tmp = distinctds[checkItmes[idx]].ToString();
|
|
if (string.IsNullOrWhiteSpace(tmp))
|
|
throw new Exception($"第 {index} 行{checkItmes[idx]}不能为空！");
|
|
}
|
|
|
|
string Inventory = distinctds["料品编码"].ToString().Trim();
|
|
string GroupCode = distinctds["检验项目组"].ToString().Trim();
|
|
string IsWrite = distinctds["是否可编辑"].ToString().Trim().Equals("是") ? "1" : "0";
|
|
|
|
sql += $@" IF NOT EXISTS(select ID from ICSInventory where InvCode='{Inventory}' AND WorkPoint='{WorkPoint}')
|
|
BEGIN
|
|
RAISERROR('系统中不存在料品编码:{Inventory},请确认！',16,1);
|
|
RETURN
|
|
end
|
|
|
|
IF NOT EXISTS(select ID from ICSInspectionRulesGroup where RulesCode='{GroupCode}' AND WorkPoint='{WorkPoint}')
|
|
BEGIN
|
|
RAISERROR('系统中不存在检验项目组编码:{GroupCode},请确认！',16,1);
|
|
RETURN
|
|
end
|
|
INSERT INTO dbo.ICSInventoryInspectionRulesGroup
|
|
( ID ,InvCode ,RulesCode ,RulesName ,
|
|
Enable ,Type,MUSER ,MUSERName ,MTIME ,WorkPoint,Editable )
|
|
SELECT '{ID}','{Inventory}',RulesCode,RulesName,Enable,Type,'{MUSER}','{MUSERNAME}',getdate(),'{WorkPoint}',{IsWrite}
|
|
FROM dbo.ICSInspectionRulesGroup
|
|
WHERE RulesCode='{GroupCode}';
|
|
|
|
INSERT INTO dbo.ICSInventoryInspectionRulesList
|
|
(ID ,InvRulesID,SetValueMax,SetValueMin,Enable , MTIME,MUSER ,MUSERName ,WorkPoint,SampleQuantity)
|
|
SELECT newid(),'{ID}',SetValueMax,SetValueMin,Enable,getdate(),'{MUSER}','{MUSERNAME}','{WorkPoint}',SampleQuantity FROM ICSInspectionRulesList WHERE RulesID = (select ID from ICSInspectionRulesGroup where RulesCode='{GroupCode}' AND WorkPoint='{WorkPoint}'); ";
|
|
|
|
|
|
}
|
|
count = SqlHelper.CmdExecuteNonQueryLi(sql);
|
|
if (count > 0)
|
|
{
|
|
msg = "导入成功";
|
|
}
|
|
else
|
|
{
|
|
return "无有效的导入数据。";
|
|
|
|
}
|
|
}
|
|
return msg;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return ex.Message;
|
|
|
|
}
|
|
|
|
}
|
|
|
|
public DataTable GetICSInventoryInspectionRulesGroup(ref Pagination jqgridparam, string queryJson)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @" select ID,InvCode,RulesCode,RulesName,RulesDesc,
|
|
case when Enable='1' then '是' else '否' end as Enable,
|
|
EATTRIBUTE1,EATTRIBUTE2,EATTRIBUTE3,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7,EATTRIBUTE8,
|
|
EATTRIBUTE9,EATTRIBUTE10
|
|
MUSER,MUSERName,MTIME,WorkPoint from ICSInventoryInspectionRulesGroup ";
|
|
sql += " WHERE 1=1";
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
|
|
{
|
|
sql += " and InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["RulesCode"].ToString()))
|
|
{
|
|
sql += " and RulesCode like '%" + queryParam["RulesCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["RulesName"].ToString()))
|
|
{
|
|
sql += " and RulesName like '%" + queryParam["RulesName"].ToString() + "%' ";
|
|
}
|
|
}
|
|
sql = string.Format(sql);
|
|
DataTable dttest = Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
public DataTable ICSInventoryInspectionRulesList(string ID, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select ID,InvRulesID,SetValueMax, SetValueMin,
|
|
CASE WHEN Enable=0 THEN '否' else '是' END Enable,
|
|
MUSERName,MTIME,WorkPoint from ICSInventoryInspectionRulesList
|
|
WHERE InvRulesID='" + ID + "'";
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
public string DeleteICSInventoryInspectionRulesGroup(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
|
|
sql += string.Format(@"DELETE FROM dbo.ICSInventoryInspectionRulesGroup WHERE ID IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
sql += string.Format(@"DELETE FROM dbo.ICSInventoryInspectionRulesList WHERE InvRulesID IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
try
|
|
{
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return msg;
|
|
}
|
|
public DataTable GetRulesName()
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string role = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode;
|
|
string sql = @"select '' as ID,'' as RulesName
|
|
union all
|
|
SELECT distinct ID,RulesName FROM ICSInspectionRulesGroup where WorkPoint='{0}'";
|
|
sql = string.Format(sql, WorkPoint);
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
public DataTable GetInspectionListInfoByRules(string ID)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"
|
|
select ID,SetValueMax,SetValueMin, CASE WHEN Enable=0 THEN '否' else '是' END Enable,SampleQuantity
|
|
from ICSInspectionRulesList where RulesID='{0}'";
|
|
sql = string.Format(sql, ID);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
/// <summary>
|
|
/// 初次加载进来
|
|
/// </summary>
|
|
/// <param name="ListCode"></param>
|
|
/// <param name="jqgridparam"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetICSInventoryInspectionRulesList(string ListCode, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select ID,SetValueMax,SetValueMin,Enable,MUSERName,MTIME
|
|
from ICSInventoryInspectionRulesList
|
|
WHERE ListCode='" + ListCode + "' ";
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
/// <summary>
|
|
/// 通过ID获取列表信息
|
|
/// </summary>
|
|
/// <param name="BID"></param>
|
|
/// <param name="jqgridparam"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetICSInventoryInspectionRulesList2(string BID, ref Pagination jqgridparam)
|
|
{
|
|
string sql = string.Empty;
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
DataTable dt = null;
|
|
sql = string.Format(@" select ID,SetValueMax,SetValueMin,SampleQuantity, CASE WHEN Enable='1' THEN '是' else '否' END Enable from ICSInventoryInspectionRulesList where InvRulesID='{0}'", BID);
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 删除供应商
|
|
/// </summary>
|
|
/// <param name="keyValue">传入ID</param>
|
|
/// <returns></returns>
|
|
public string DeleteICSVender(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
sql = string.Format(@"DELETE FROM dbo.ICSVendor WHERE ID IN ({0}) and WorkPoint ='{1}'"
|
|
, keyValue.TrimEnd(','), WorkPoint);
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取供应商修改文本框值
|
|
/// </summary>
|
|
public DataTable GetVenderInputValue(string ID)
|
|
{
|
|
try
|
|
{
|
|
DataTable dt = new DataTable();
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @" select ID,VenCode,VenName,VenAbbName,VCCode,VCName,VenAddress
|
|
,VenRegCode,VenBank,VenAccount,VenDevDate,VenLPerson
|
|
,VenPhone,VenEmail,VenPerson,VenHand,VenDisRate,VenCreLine
|
|
,VenCreDate,VenPayCond,PayName,VenExch_name from ICSVendor
|
|
where ID='{0}' and WorkPoint='{1}' ";
|
|
sql = string.Format(sql, ID, WorkPoint);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message.ToString());
|
|
}
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 新增供应商
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string InsertICSVender(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
sql = @"IF EXISTS (SELECT VenCode FROM ICSVendor WHERE VenCode='{0}' AND WorkPoint='{5}')
|
|
BEGIN
|
|
RAISERROR('供应商编码已存在，新增失败', 16, 1);
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
insert into ICSVendor
|
|
(ID,VenCode,VenName,VenAbbName,VCCode,VCName,VenAddress,VenRegCode,VenBank,VenAccount,VenDevDate
|
|
,VenLPerson,VenPhone,VenEmail,VenPerson,VenHand,VenDisRate,VenCreLine,VenCreDate,VenPayCond,PayName
|
|
,VenExch_name,MUSER,MUSERNAME,WorkPoint,MTIME)
|
|
values
|
|
(NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}'
|
|
,'{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}',GETDATE())
|
|
END";
|
|
sql = string.Format(sql,
|
|
queryParam["VenCode"].ToString(),
|
|
queryParam["VenName"].ToString(),
|
|
queryParam["VenAbbName"].ToString(),
|
|
queryParam["VCCode"].ToString(),
|
|
queryParam["VCName"].ToString(),
|
|
queryParam["VenAddress"].ToString(),
|
|
queryParam["VenRegCode"].ToString(),
|
|
queryParam["VenBank"].ToString(),
|
|
queryParam["VenAccount"].ToString(),
|
|
queryParam["VenDevDate"].ToString(),
|
|
queryParam["VenLPerson"].ToString(),
|
|
queryParam["VenPhone"].ToString(),
|
|
queryParam["VenEmail"].ToString(),
|
|
queryParam["VenPerson"].ToString(),
|
|
queryParam["VenHand"].ToString(),
|
|
queryParam["VenDisRate"].ToString(),
|
|
queryParam["VenCreLine"].ToString(),
|
|
queryParam["VenCreDate"].ToString(),
|
|
queryParam["VenPayCond"].ToString(),
|
|
queryParam["PayName"].ToString(),
|
|
queryParam["VenExch_name"].ToString(),
|
|
MUSER, MUSERNAME, WorkPoint);
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 修改供应商
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string UpdateICSVender(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
|
|
string sql = string.Empty;
|
|
sql = @"update ICSVendor set VenName='{2}',VenAbbName='{3}',VCCode='{4}',VCName='{5}',VenAddress='{6}'
|
|
,VenRegCode='{7}',VenBank='{8}',VenAccount='{9}',VenDevDate='{10}',VenLPerson='{11}'
|
|
,VenPhone='{12}',VenEmail='{13}',VenPerson='{14}',VenHand='{15}',VenDisRate='{16}',VenCreLine='{17}'
|
|
,VenCreDate='{18}',VenPayCond='{19}',PayName='{20}',VenExch_name='{21}',MUSER='{22}',MUSERNAME='{23}',MTIME=GETDATE()
|
|
where 1=1 and VenCode='{0}' and WorkPoint='{1}'";
|
|
sql = string.Format(sql,
|
|
queryParam["VenCode"].ToString(),
|
|
WorkPoint,
|
|
queryParam["VenName"].ToString(),
|
|
queryParam["VenAbbName"].ToString(),
|
|
queryParam["VCCode"].ToString(),
|
|
queryParam["VCName"].ToString(),
|
|
queryParam["VenAddress"].ToString(),
|
|
queryParam["VenRegCode"].ToString(),
|
|
queryParam["VenBank"].ToString(),
|
|
queryParam["VenAccount"].ToString(),
|
|
queryParam["VenDevDate"].ToString(),
|
|
queryParam["VenLPerson"].ToString(),
|
|
queryParam["VenPhone"].ToString(),
|
|
queryParam["VenEmail"].ToString(),
|
|
queryParam["VenPerson"].ToString(),
|
|
queryParam["VenHand"].ToString(),
|
|
queryParam["VenDisRate"].ToString(),
|
|
queryParam["VenCreLine"].ToString(),
|
|
queryParam["VenCreDate"].ToString(),
|
|
queryParam["VenPayCond"].ToString(),
|
|
queryParam["PayName"].ToString(),
|
|
queryParam["VenExch_name"].ToString(),
|
|
MUSER, MUSERNAME);
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "修改失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取仓库修改文本框值
|
|
/// </summary>
|
|
public DataTable GetWareHouseInputValue(string ID)
|
|
{
|
|
try
|
|
{
|
|
DataTable dt = new DataTable();
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @" select ID,WarehouseCode,WarehouseName,Enable
|
|
,BatchEnable,Asset,Proxy,InCost from ICSWarehouse
|
|
where ID='{0}' and WorkPoint='{1}' ";
|
|
sql = string.Format(sql, ID, WorkPoint);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message.ToString());
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 新增仓库
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string InsertICSWareHouse(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
sql = @"IF EXISTS (SELECT WarehouseCode FROM ICSWarehouse WHERE WarehouseCode='{0}' AND WorkPoint='{5}')
|
|
BEGIN
|
|
RAISERROR('仓库编码已存在，新增失败', 16, 1);
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
insert into ICSWarehouse
|
|
(ID,WarehouseCode,WarehouseName,Enable,BatchEnable,Asset,Proxy,InCost,MUSER,MUSERName,MTIME,WorkPoint)
|
|
values
|
|
(NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}',GETDATE(),'{9}')
|
|
END";
|
|
sql = string.Format(sql,
|
|
queryParam["WarehouseCode"].ToString(),
|
|
queryParam["WarehouseName"].ToString(),
|
|
queryParam["Enable"].ToString(),
|
|
queryParam["BatchEnable"].ToString(),
|
|
queryParam["Asset"].ToString(),
|
|
queryParam["Proxy"].ToString(),
|
|
queryParam["InCost"].ToString(),
|
|
MUSER, MUSERNAME, WorkPoint);
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 修改仓库
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <returns></returns>
|
|
public string UpdateICSWareHouse(string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
|
|
string sql = string.Empty;
|
|
sql = @"update ICSWarehouse set WarehouseName='{2}',Enable='{3}',BatchEnable='{4}',Asset='{5}',Proxy='{6}',InCost='{7}'
|
|
,MUSER='{8}',MUSERName='{9}'
|
|
where WarehouseCode='{0}' and WorkPoint='{1}'";
|
|
sql = string.Format(sql,
|
|
queryParam["WarehouseCode"].ToString(),
|
|
WorkPoint,
|
|
queryParam["WarehouseName"].ToString(),
|
|
queryParam["Enable"].ToString(),
|
|
queryParam["BatchEnable"].ToString(),
|
|
queryParam["Asset"].ToString(),
|
|
queryParam["Proxy"].ToString(),
|
|
queryParam["InCost"].ToString(),
|
|
MUSER, MUSERNAME);
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "修改失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 删除仓库
|
|
/// </summary>
|
|
/// <param name="keyValue">传入ID</param>
|
|
/// <returns></returns>
|
|
public string DeleteICSWareHouse(string keyValue)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
sql = string.Format(@"IF EXISTS(select A.LocationCode from ICSLocation A
|
|
INNER JOIN ICSWarehouse B ON B.ID=A.WHID AND B.WorkPoint=A.WorkPoint
|
|
where B.ID IN ({0}) and b.WorkPoint='{1}')
|
|
BEGIN
|
|
RAISERROR('仓库已关联库位，不能删除！',16,1);
|
|
RETURN
|
|
end
|
|
DELETE FROM dbo.ICSWarehouse WHERE ID IN ({0}) and WorkPoint ='{1}'"
|
|
, keyValue.TrimEnd(','), WorkPoint);
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
|
|
|
|
|
|
public DataTable SelectICSColumnEnableForTypeEnable()
|
|
{
|
|
string WorkPoints = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string sql = @" select ColumnCode from ICSColumnEnable
|
|
where Enable='0' and TableCode='ICSType' and WorkPoint='" + WorkPoints + "' order by cast(Code as int)";
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
|
|
public DataTable GetLoadShowForColumn()
|
|
{
|
|
string WorkPoints = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
|
|
string sql = @"
|
|
select * from ICSColumnEnable where TableCode='ICSType' and WorkPoint='" + WorkPoints + "' order by cast(code as int) ";
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
|
|
}
|
|
|
|
#region 查询开启的自定义项
|
|
public DataTable SelectICSColumnEnableForEnablePulic(string TabName)
|
|
{
|
|
string WorkPoints = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string sql = @" select ColumnCode,Name from ICSColumnEnable
|
|
where Enable='1' and TableCode='" + TabName + "' and WorkPoint='" + WorkPoints + "' order by cast(Code as int)";
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
#endregion
|
|
|
|
|
|
|
|
|
|
public static string HttpPost(string url, string body)
|
|
{
|
|
try
|
|
{
|
|
Encoding encoding = Encoding.UTF8;
|
|
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
|
|
request.Method = "POST";
|
|
request.Accept = "application/json, text/javascript, */*"; //"text/html, application/xhtml+xml, */*";
|
|
request.ContentType = "application/json; charset=utf-8";
|
|
|
|
byte[] buffer = encoding.GetBytes(body);
|
|
request.ContentLength = buffer.Length;
|
|
request.GetRequestStream().Write(buffer, 0, buffer.Length);
|
|
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
|
|
using (StreamReader reader = new StreamReader(response.GetResponseStream(), encoding))
|
|
{
|
|
return reader.ReadToEnd();
|
|
}
|
|
}
|
|
catch (WebException ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
|
|
public string CreateLableDataSource(string Sys_LableDataSource)
|
|
{
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
string SqlTxt = "";
|
|
JArray res = (JArray)JsonConvert.DeserializeObject(Sys_LableDataSource);
|
|
foreach (var item in res)
|
|
{
|
|
JObject jo = (JObject)item;
|
|
if (jo["DbParas"].ToString().Trim() != "")
|
|
{
|
|
string[] dbvs = jo["DbParas"].ToString().Trim().Split(',');
|
|
List<string> vs = new List<string>();
|
|
foreach (string s in dbvs)
|
|
{
|
|
if (!string.IsNullOrEmpty(s))
|
|
{
|
|
vs.Add(s);
|
|
}
|
|
}
|
|
SqlTxt = string.Format(jo["FormatSqlTxt"].ToString(), vs.ToArray());
|
|
}
|
|
else
|
|
{
|
|
SqlTxt = jo["FormatSqlTxt"].ToString();
|
|
}
|
|
string sqlTxt = jo["FormatSqlTxt"].ToString().Replace("'", "''");
|
|
sql += @"INSERT INTO dbo.Sys_LableDataSource
|
|
(ID,WorkPointCode,OtherDataBaseID,WorkPointDataFlag,SysDataFlag,SqlTxt,SourceName,FormatSqlTxt,Paras,DbParas,LinkCols,CreateUserID,CreateDate,ModifyUserID,ModifyDate,Link2Cols )
|
|
Values(NEWID(),{0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}',getdate(),'{11}',getdate(),'{12}')";
|
|
sql = string.Format(sql, WorkPoint, jo["OtherDataBaseID"].ToString(), jo["WorkPointDataFlag"].ToString(), jo["SysDataSourceFlag"].ToString(), SqlTxt, jo["SourceName"].ToString(), jo["FormatSqlTxt"].ToString(), jo["Paras"].ToString(), jo["DbParas"].ToString(), jo["LinkCols"].ToString(), MUSER, MUSER, jo["Link2Cols"].ToString());
|
|
}
|
|
try
|
|
{
|
|
if (SqlHelper.ExecuteNonQuery(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
//throw new Exception(ex.Message);
|
|
msg = ex.Message;
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
}
|
|
}
|
|
|