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.
386 lines
17 KiB
386 lines
17 KiB
using Newtonsoft.Json;
|
|
using Newtonsoft.Json.Linq;
|
|
using NFine.Code;
|
|
using NFine.Data.Extensions;
|
|
using NFine.Domain._03_Entity.SRM;
|
|
using NFine.Repository;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Data.Common;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
|
|
namespace NFine.Application.KBSSRM
|
|
{
|
|
public class PalletplateApp : RepositoryFactory<ICSVendor>
|
|
{
|
|
public DataTable GetGridJson(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
// string sqlDelete = @" DELETE a FROM ICSContainer a
|
|
//LEFT JOIN dbo.ICSContainerLot b ON a.ContainerID=b.ContainerID
|
|
// WHERE b.ContainerID IS NULL and a.EATTRIBUTE3='SRM'";
|
|
// SqlHelper.ExecuteNonQuery(sqlDelete);
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"SELECT a.ID,a.ContainerCode,a.MUSERName, CONVERT(NVARCHAR(50),a.MTIME,23) as MTIME,b.VenName,w.WorkPointName,w.WorkPointCode,a.EATTRIBUTE8
|
|
FROM dbo.ICSContainer a
|
|
LEFT JOIN Sys_WorkPoint w on a.WorkPoint=w.WorkPointCode
|
|
LEFT JOIN dbo.ICSVendor b ON a.EATTRIBUTE8=b.VenCode AND a.WorkPoint=b.WorkPoint
|
|
WHERE 1=1 and a.EATTRIBUTE3='SRM' and a.ContainerType='ContainerType04' ";
|
|
if (queryParam["Type"].ToString() == "2")
|
|
{
|
|
sql += "and a.EATTRIBUTE1='2' ";
|
|
}
|
|
else if (queryParam["Type"].ToString() == "1")
|
|
{
|
|
sql += "and a.EATTRIBUTE1='1' ";
|
|
}
|
|
else
|
|
{
|
|
sql += "";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["CartonNo"].ToString()))
|
|
{
|
|
sql += " and ContainerCode like '%" + queryParam["CartonNo"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["VenCode"].ToString()))
|
|
{
|
|
sql += " and a.EATTRIBUTE8 like '%" + queryParam["VenCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["VenName"].ToString()))
|
|
{
|
|
sql += " and b.VenName like '%" + queryParam["VenName"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
|
|
{
|
|
sql += " and CONVERT(NVARCHAR(50),a.MTIME,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["TimeTo"].ToString()))
|
|
{
|
|
sql += " and CONVERT(NVARCHAR(50),a.MTIME,23) <= '" + queryParam["TimeTo"].ToString() + "' ";
|
|
}
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
|
|
{
|
|
sql += " and a.WorkPoint in (" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',') + ")";
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
|
|
{
|
|
sql += " and a.EATTRIBUTE8='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
|
|
}
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 子表查询
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <param name="jqgridparam"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetSubGridJson(string ContainerID, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
DataTable dt = new DataTable();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"
|
|
|
|
select a.ID, a.ContainerCode,a.ContainerName,a.Memo,a.ContainerType,a.MTIME,a.MUSER,a.MUSERName from ICSContainer a
|
|
WHERE a.ContainerType='ContainerType01' and a.EATTRIBUTE3='SRM' and a.ContainerID='" + ContainerID + "' ";
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
|
|
{
|
|
sql += " and a.WorkPoint in (" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',') + ")";
|
|
}
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
|
|
public DataTable GetSubGridJson_Add(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
|
|
string sql = @" select a.ID, a.ContainerCode,a.ContainerName,a.Memo,a.ContainerType,a.MUSERName,a.MTIME from ICSContainer a
|
|
|
|
where a.ContainerType='ContainerType01' and a.EATTRIBUTE3='SRM'
|
|
and a.ContainerID='" + queryParam["CartonNo"].ToString() + "' and a.WorkPoint='" + queryParam["WorkPoint"].ToString() + "'";
|
|
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
|
|
}
|
|
public DataTable GetSubGridJson_AddByASN(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
|
|
string sql = @" SELECT DISTINCT c.id,c.ContainerCode,c.MUSERName,c.MTIME FROM dbo.ICSASNDETAIL a
|
|
inner JOIN dbo.ICSContainerLot b ON a.LotNO =b.LotNO AND a.WorkPoint=b.WorkPoint
|
|
LEFT JOIN ICSContainer c ON b.ContainerID=c.id AND c.WorkPoint=b.WorkPoint AND c.ContainerType='ContainerType01'
|
|
LEFT JOIN ICSContainer d ON d.id=c.ContainerID AND d.WorkPoint=c.WorkPoint AND d.ContainerType='ContainerType04'
|
|
LEFT JOIN ICSInventoryLot e ON b.LotNO=e.LotNO AND b.WorkPoint=e.WorkPoint
|
|
WHERE c.id IS NOT NULL
|
|
AND d.id IS NULL
|
|
and a.ASNCode='" + queryParam["ASNCode"].ToString() + "' and a.WorkPoint='" + queryParam["WorkPoint"].ToString() + "'";
|
|
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
|
|
{
|
|
sql += " and e.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["LotNo"].ToString()))
|
|
{
|
|
sql += " and e.LotNo like '%" + queryParam["LotNo"].ToString() + "%' ";
|
|
}
|
|
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
|
|
}
|
|
|
|
|
|
|
|
public string GetContainerCode(string RoleEnCode,string WorkPoint)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
string Date = DateTime.Now.ToString("yyMMdd");
|
|
string Pre = "Z" + RoleEnCode + Date;
|
|
string CartonNo = GetSerialCode(WorkPoint, "ICSContainer", "ContainerCode", Pre, 3);
|
|
return CartonNo;
|
|
}
|
|
|
|
|
|
|
|
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 GetContainerForBox(string invcode, string Code, string LotNo, string VenCode, string WorkPoint, string Type,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 c.InvCode like '%" + invcode + "%'";
|
|
}
|
|
if (!string.IsNullOrEmpty(Code))
|
|
{
|
|
wherestr += " and a.ContainerCode like '%" + Code + "%'";
|
|
}
|
|
if (!string.IsNullOrEmpty(LotNo))
|
|
{
|
|
wherestr += " and b.LotNo like '%" + LotNo + "%'";
|
|
}
|
|
string sql = @" SELECT DISTINCT a.ContainerCode,a.ID,
|
|
a.MUSERName, a.MTIME,Dil.F_ItemName,d.InvCode,d.InvName,d.InvStd
|
|
FROM ICSContainer a
|
|
LEFT JOIN dbo.ICSContainerLot c ON a.ID=c.ContainerID and a.WorkPoint=c.WorkPoint
|
|
LEFT join dbo.ICSInventoryLot lot on c.LotNo=lot.LotNo and a.WorkPoint=lot.WorkPoint
|
|
left join Sys_SRM_ItemsDetail Dil on F_ItemId='6230217e-4413-4427-9bec-c67ba789eca9' and F_EnabledMark='1' and a.ContainerType=Dil.F_ItemCode
|
|
LEFT JOIN ICSInventory d ON d.InvCode=lot.InvCode AND d.WorkPoint=lot.WorkPoint
|
|
WHERE
|
|
a.ID not IN
|
|
(SELECT b.ContainerID FROM dbo.ICSASNDETAIL a inner JOIN dbo.ICSContainerLot b ON a.LotNO =b.LotNO AND a.WorkPoint=b.WorkPoint)
|
|
and a.ContainerID='' and a.ContainerType='ContainerType01'
|
|
and a.WorkPoint = '" + WorkPoint + "' and a.EATTRIBUTE8 = '" + VenCode + "' and a.EATTRIBUTE1='"+ Type + "' " + wherestr;
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
public string SaveICSContainerForPalletplate(string ICSASN)
|
|
{
|
|
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 msg = "";
|
|
string sql = string.Empty;
|
|
string str1 = "";
|
|
string ID = Guid.NewGuid().ToString();
|
|
JArray res = (JArray)JsonConvert.DeserializeObject(ICSASN);
|
|
foreach (var item in res)
|
|
{
|
|
JObject jo = (JObject)item;
|
|
//创建栈板
|
|
sql += @" INSERT INTO dbo.ICSContainer
|
|
( ID ,ContainerCode ,ContainerName,InventoryMixed,ProjectMixed,BatchMixed,Multiplex,
|
|
MUSER ,MUSERName ,MTIME,WorkPoint,EATTRIBUTE8,EATTRIBUTE3,ContainerType,ContainerID,EATTRIBUTE1)
|
|
VALUES ( '{0}','{1}','{1}','1','1','1','1','{2}','{3}',getdate(),'{4}','{5}','SRM','ContainerType04','','{6}')";
|
|
sql = string.Format(sql, ID, jo["ContainerCode"].ToString(),MUSER, MUSERNAME, jo["WorkPoint"].ToString(), jo["VenCode"].ToString(), jo["Type"].ToString());
|
|
|
|
JArray resdetail = (JArray)JsonConvert.DeserializeObject(jo["Detail"].ToString());
|
|
foreach (var detail in resdetail)
|
|
{
|
|
JObject det = (JObject)detail;
|
|
sql += @"update ICSContainer set ContainerID='{0}' where ContainerCode='{1}' and EATTRIBUTE3='SRM' and ContainerType='ContainerType01'";
|
|
sql = string.Format(sql, ID, det["CartonNo"].ToString());
|
|
|
|
}
|
|
}
|
|
|
|
try
|
|
{
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
return msg;
|
|
}
|
|
public string SaveICSContainerForPalletplateByASN(string ICSASN)
|
|
{
|
|
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 msg = "";
|
|
string sql = string.Empty;
|
|
string str1 = "";
|
|
string ID = Guid.NewGuid().ToString();
|
|
JArray res = (JArray)JsonConvert.DeserializeObject(ICSASN);
|
|
foreach (var item in res)
|
|
{
|
|
JObject jo = (JObject)item;
|
|
//创建栈板
|
|
sql += @" INSERT INTO dbo.ICSContainer
|
|
( ID ,ContainerCode ,ContainerName,InventoryMixed,ProjectMixed,BatchMixed,Multiplex,
|
|
MUSER ,MUSERName ,MTIME,WorkPoint,EATTRIBUTE8,EATTRIBUTE3,ContainerType,ContainerID,EATTRIBUTE1)
|
|
VALUES ( '{0}','{1}','{1}','1','1','1','1','{2}','{3}',getdate(),'{4}','{5}','SRM','ContainerType04','','{6}')";
|
|
sql = string.Format(sql, ID, jo["ContainerCode"].ToString(), MUSER, MUSERNAME, jo["WorkPoint"].ToString(), jo["VenCode"].ToString(), jo["Type"].ToString());
|
|
|
|
JArray resdetail = (JArray)JsonConvert.DeserializeObject(jo["Detail"].ToString());
|
|
foreach (var detail in resdetail)
|
|
{
|
|
JObject det = (JObject)detail;
|
|
sql += @"update ICSContainer set ContainerID='{0}' where ContainerCode='{1}' and EATTRIBUTE3='SRM' and ContainerType='ContainerType01'";
|
|
sql = string.Format(sql, ID, det["CartonNo"].ToString());
|
|
|
|
}
|
|
}
|
|
|
|
try
|
|
{
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
|
|
public string UpdateICSContainerForPalletplate(string ICSASN)
|
|
{
|
|
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 msg = "";
|
|
string sql = string.Empty;
|
|
JArray res = (JArray)JsonConvert.DeserializeObject(ICSASN);
|
|
foreach (var item in res)
|
|
{
|
|
JObject jo = (JObject)item;
|
|
JArray resdetail = (JArray)JsonConvert.DeserializeObject(jo["Detail"].ToString());
|
|
|
|
foreach (var detail in resdetail)
|
|
{
|
|
JObject det = (JObject)detail;
|
|
|
|
string DetailID = Guid.NewGuid().ToString();
|
|
sql += @" update ICSContainer set ContainerID='{0}' where ContainerCode='{1}' and EATTRIBUTE3='SRM' and ContainerType='ContainerType01'";
|
|
sql = string.Format(sql, jo["ContainerID"].ToString() , det["CartonNo"].ToString());
|
|
}
|
|
}
|
|
try
|
|
{
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "修改失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
public string DeleteInfo(string keyValue, string WorkPoint)
|
|
{
|
|
|
|
string msg = "";
|
|
string sql = string.Format(@"update ICSContainer set ContainerID='' where ContainerCode in ({0}) and EATTRIBUTE3='SRM' and ContainerType='ContainerType01' and WorkPoint='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
public string DeleteContainer(string keyValue, string WorkPoint)
|
|
{
|
|
WorkPoint = WorkPoint.Substring(1, WorkPoint.Length - 2);
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
string sqls = string.Format(@"SELECT * FROM dbo.ICSASNDETAIL
|
|
WHERE LOTNO IN (
|
|
SELECT LotNO FROM dbo.ICSContainer a
|
|
LEFT JOIN ICSContainerLot b ON a.ContainerID=b.ContainerID WHERE a.ContainerID in ({0}) and a.WorkPoint in ({1}))", keyValue.TrimEnd(','), WorkPoint.TrimEnd(','));
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sqls);
|
|
if (dt == null || dt.Rows.Count <= 0)
|
|
{
|
|
sql += string.Format(@" DELETE ICSContainer
|
|
WHERE ID in ({0}) AND WorkPoint in ({1})", keyValue.TrimEnd(','), WorkPoint.TrimEnd(','));
|
|
sql += string.Format(@" update ICSContainer set ContainerID=''
|
|
WHERE ContainerID in ({0}) AND WorkPoint in ({1})", keyValue.TrimEnd(','), WorkPoint.TrimEnd(','));
|
|
SqlHelper.CmdExecuteNonQueryLi(sql);
|
|
}
|
|
else
|
|
{
|
|
msg = "所选栈板中已有加入送货单中，请先在送单号中删除！";
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
|
|
}
|
|
}
|