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.
889 lines
43 KiB
889 lines
43 KiB
using Newtonsoft.Json;
|
|
using Newtonsoft.Json.Linq;
|
|
using NFine.Code;
|
|
using NFine.Data.Extensions;
|
|
using NFine.Domain._03_Entity.SRM;
|
|
using NFine.Domain.Entity.ProductManage;
|
|
using NFine.Domain.IRepository.ProductManage;
|
|
using NFine.Repository;
|
|
using NFine.Repository.ProductManage;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Configuration;
|
|
using System.Data;
|
|
using System.Data.Common;
|
|
using System.Linq;
|
|
using System.Text;
|
|
|
|
|
|
namespace NFine.Application.SRM
|
|
{
|
|
public class ASNMangeWeiWaiApp : RepositoryFactory<ICSVendor>
|
|
{
|
|
public DataTable GetGridJson(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
string ParentId = "";
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"
|
|
SELECT distinct a.ID, a.OASNCode,a.VenCode,b.VenName AS VENDORNAME,w.WorkPointName,w.WorkPointCode,a.MUSERName,
|
|
a.MTIME,CONVERT(NVARCHAR(50),a.PlanArriveDate,23) as EXPARRIVALDATE,
|
|
CASE WHEN c.ODNCode IS NULL THEN a.Status ELSE '3' END AS STATUS,a.EATTRIBUTE1,convert(nvarchar(20),c.MTIME,120) DMTIME
|
|
FROM dbo.ICSOASN a
|
|
LEFT JOIN ICSOASNDetail d on a.OASNCode=d.OASNCode and a.WorkPoint=d.WorkPoint
|
|
left join ICSInventoryLotDetail f on d.LotNo=f.LotNo and d.WorkPoint=f.WorkPoint
|
|
left join dbo.ICSOutsourcingOrder g on f.TransCode=g.OOCode and f.TransSequence=g.Sequence and f.WorkPoint=g.WorkPoint
|
|
LEFT JOIN dbo.ICSVendor b ON a.VenCode=b.VenCode and a.WOrkPoint=b.WorkPoint
|
|
LEFT JOIN ICSODeliveryNotice c ON a.OASNCode = c.OASNCode
|
|
LEFT JOIN Sys_WorkPoint w on a.WorkPoint=w.WorkPointCode
|
|
WHERE 1=1";
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["STNO"].ToString()))
|
|
{
|
|
sql += " and a.OASNCode like '%" + queryParam["STNO"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["VenCode"].ToString()))
|
|
{
|
|
sql += " and a.VenCode 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 a.MTIME >= '" + queryParam["TimeFrom"].ToString() + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["TimeTo"].ToString()))
|
|
{
|
|
sql += " and a.MTIME <= '" + queryParam["TimeTo"].ToString() + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["PoCode"].ToString()))
|
|
{
|
|
sql += " and g.OOCode like '%" + queryParam["PoCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
|
|
{
|
|
sql += " and g.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["ReleaseState"].ToString()))
|
|
{
|
|
string ReleaseState = queryParam["ReleaseState"].ToString();
|
|
if (ReleaseState == "0")
|
|
sql += " AND CASE WHEN c.ODNCode IS NULL THEN a.Status ELSE '3' END = '0'";
|
|
else if (ReleaseState == "1")
|
|
sql += " AND CASE WHEN c.ODNCode IS NULL THEN a.Status ELSE '3' END = '1'";
|
|
else if (ReleaseState == "2")
|
|
sql += " AND CASE WHEN c.ODNCode IS NULL THEN a.Status ELSE '3' END = '2'";
|
|
else if (ReleaseState == "3")
|
|
{
|
|
sql += " AND CASE WHEN c.ODNCode IS NULL THEN a.Status ELSE '3' END='3'";
|
|
}
|
|
else if (ReleaseState == "4")
|
|
sql += " AND CASE WHEN c.ODNCode IS NULL THEN a.Status ELSE '3' END = '4'";
|
|
}
|
|
}
|
|
//多站点
|
|
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.Contains("Vendor"))
|
|
{
|
|
sql += " and a.VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().VenCode + "'";
|
|
}
|
|
ParentId = SqlHelper.Organize_F_ParentId(NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode);
|
|
if (!string.IsNullOrWhiteSpace(ParentId) && ParentId != "0" && NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "Vendor")
|
|
{
|
|
return SqlHelper.FindTablePageBySql_OtherTemp(sql.ToString(), sql, ParentId, parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
else
|
|
{
|
|
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 STNO, ref Pagination jqgridparam,string WorkPoint)
|
|
{
|
|
//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.LotNo,d.InvCode,d.InvName,c.ContainerID,b.Quantity,
|
|
a.OASNCode,f.BatchCode,f.ProjectCode,m.TransCode,m.TransSequence,d.InvDesc,d.ClassName,d.InvStd,w.WorkPointName,w.WorkPointCode,x.ContainerCode as XH,y.ContainerCode as ZB
|
|
FROM dbo.ICSOASNDetail a
|
|
LEFT JOIN dbo.ICSInventoryLot b ON a.LotNo =b.LotNo AND a.WorkPoint=b.WorkPoint
|
|
left join ICSExtension f on b.ExtensionID=f.ID and b.WorkPoint=f.WorkPoint
|
|
left join ICSInventoryLotDetail m on b.LotNo=m.LotNo and b.WorkPoint=m.WorkPoint
|
|
LEFT JOIN dbo.ICSContainerLot c ON b.LotNo=c.LotNo AND a.WorkPoint=c.WorkPoint
|
|
LEFT JOIN dbo.ICSInventory d ON b.InvCode=d.InvCode AND a.WorkPoint=d.WorkPoint
|
|
LEFT JOIN dbo.ICSOutsourcingOrder e ON m.TransCode=e.OOCode AND m.TransSequence=e.Sequence AND a.WorkPoint=e.WorkPoint
|
|
LEFT JOIN Sys_WorkPoint w on e.WorkPoint=w.WorkPointCode
|
|
LEFT JOIN ICSContainer X ON c.ContainerID=X.ID AND c.WorkPoint=x.WorkPoint and X.ContainerType='ContainerType01'
|
|
left join ICSContainer y on X.ContainerID=y.ID and X.WorkPoint=y.WorkPoint and y.ContainerType='ContainerType04'
|
|
WHERE (b.TYPE='201' OR ISNULL(b.EATTRIBUTE7,'') ='入库前分批') AND a.OASNCode='" + STNO + "' and a.WorkPoint='" + WorkPoint + "' ";
|
|
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 删除送货单
|
|
/// </summary>
|
|
/// <param name="keyValue"></param>
|
|
/// <returns></returns>
|
|
public string DeleteSTNO(string keyValue, string WorkPoint, string EATTRIBUTE1)
|
|
{
|
|
// string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string sql = string.Empty;
|
|
sql = "SELECT OASNCode as STNO FROM dbo.ICSODeliveryNotice WHERE OASNCode IN (" + keyValue.TrimEnd(',') + ")";
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
foreach (DataRow dr in dt.Rows)
|
|
{
|
|
string STNO = dr["STNO"].ToString();
|
|
if (!string.IsNullOrEmpty(STNO))
|
|
{
|
|
msg += "送货单号：" + STNO + "已生成到货单，无法删除！";
|
|
}
|
|
}
|
|
if (string.IsNullOrEmpty(msg))
|
|
{
|
|
sql = string.Format(@" DELETE FROM dbo.ICSOASNDetail WHERE OASNCode IN ({0}) and WorkPoint in ('{1}')
|
|
DELETE FROM dbo.ICSOASN WHERE OASNCode IN ({0}) and WorkPoint in ('{1}')
|
|
UPDATE ICSASN SET EATTRIBUTE1='{2}'WHERE ASNCode IN ({0}) and WorkPoint in ('{1}')", keyValue.TrimEnd(','), WorkPoint.TrimEnd(','), EATTRIBUTE1);
|
|
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
public string CheckIsAll(string STNO)
|
|
{
|
|
string sql = @"SELECT * FROM dbo.ICSOASNDetail WHERE OASNCode='" + STNO + "' AND ISNULL(ODNQuantity,0)<>0";
|
|
sql += " and WorkPoint in(" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',') + ")";
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
if (dt != null && dt.Rows.Count > 0)
|
|
{
|
|
return "0";
|
|
}
|
|
else
|
|
{
|
|
return "1";
|
|
}
|
|
}
|
|
|
|
|
|
public string GetSTNO(string WorkPoint)
|
|
{
|
|
string STNO = string.Empty;
|
|
string VenCode = NFine.Code.OperatorProvider.Provider.GetCurrent().VenCode;
|
|
//string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
if (!string.IsNullOrEmpty(VenCode))
|
|
{
|
|
string Date = DateTime.Now.ToString("yy");
|
|
string Pre = "DN" + VenCode + Date;
|
|
STNO = GetSerialCode(WorkPoint, "ICSASN", "STNO", Pre, 5);
|
|
}
|
|
if (!string.IsNullOrEmpty(STNO))
|
|
{
|
|
string sql = string.Empty;
|
|
string configSql = string.Empty;
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string Status = "2";
|
|
|
|
#region [获取送货单配置信息,判断状态]
|
|
configSql = @"SELECT a.F_ItemCode,a.F_ItemName FROM Sys_SRM_ItemsDetail a
|
|
LEFT JOIN Sys_SRM_Items b ON a.F_ItemId = b.F_Id
|
|
WHERE b.F_EnCode = 'DeliveryNoteConfig'";
|
|
DataTable dt = SqlHelper.GetDataTableBySql(configSql);
|
|
foreach (DataRow item in dt.Rows)
|
|
{
|
|
if (item["F_ItemName"].ToString().Equals("IsSub"))
|
|
{
|
|
Status = item["F_ItemCode"].ToString();
|
|
}
|
|
else if (item["F_ItemName"].ToString().Equals("IsEAV"))
|
|
{
|
|
if (Status == "1" && item["F_ItemCode"].ToString() == "3")
|
|
Status = "2";
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
if (string.IsNullOrWhiteSpace(WorkPoint))
|
|
{
|
|
throw new Exception("站点为空！");
|
|
}
|
|
else
|
|
{
|
|
sql = @"INSERT INTO dbo.ICSOASN( ID ,MTIME,MUSER,MUSERNAME ,OASNCode ,VenCode ,WorkPoint,Status)
|
|
VALUES (NEWID(),GETDATE(),'" + VenCode + "','" + MUSERNAME + "','" + STNO + "','" + VenCode + "','" + WorkPoint + "','" + Status + "')";
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
|
|
}
|
|
return STNO;
|
|
}
|
|
|
|
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 string GetSTNOByPerson(string VenCode, string ExpArrivalDate, string WorkPoint)
|
|
{
|
|
string STNO = string.Empty;
|
|
string Date = DateTime.Now.ToString("yy");
|
|
string Pre = "DN" + VenCode + Date;
|
|
|
|
//string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
STNO = GetSerialCode(WorkPoint, "ICSASN", "STNO", Pre, 5);
|
|
if (!string.IsNullOrEmpty(STNO))
|
|
{
|
|
string sql = string.Empty;
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
if (string.IsNullOrWhiteSpace(WorkPoint))
|
|
{
|
|
throw new Exception("站点为空！");
|
|
}
|
|
else
|
|
{
|
|
sql = @" INSERT INTO dbo.ICSOASN
|
|
( ID ,OASNCode,VenCode ,
|
|
Status ,MUSER ,MUSERName ,MTIME,WorkPoint)
|
|
VALUES (NEWID(),'" + STNO + "','" + VenCode + "','2','" + MUSER + "','" + MUSERNAME + "',getdate(),'" + WorkPoint + "')";
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
}
|
|
return STNO;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取供应商列表
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public DataTable GetVendor()
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string sql = @"select '' as F_VenCode,'' as cVenName
|
|
union all
|
|
SELECT DISTINCT a.F_VenCode,isnull(b.VenName,'') as cVenName FROM dbo.Sys_SRM_User a
|
|
LEFT JOIN dbo.ICSVendor b ON a.F_VenCode=b.VenCode and a.F_Location=b.WorkPoint
|
|
WHERE a.F_VenCode IS NOT NULL AND a.F_VenCode <>''";
|
|
string role = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode;
|
|
if (role != "admin")
|
|
{
|
|
sql += " and b.WorkPoint in (" + WorkPoint.TrimEnd(',') + ")";
|
|
}
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取组织号信息
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public DataTable GetVendors()
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string role = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode;
|
|
string sql = @" select '' as WorkPointCode,'' as WorkPointName
|
|
union all
|
|
SELECT distinct (b.WorkPointCode),b.WorkPointName FROM Sys_SRM_User a
|
|
LEFT JOIN Sys_WorkPoint b ON a.F_Location=b.WorkPointCode where b.WorkPointCode is not null";
|
|
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 选择条码信息
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <param name="jqgridparam"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetSubGridJson_Add(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.ID as ZJID, a.LOTNO,d.InvCode,d.InvName,c.ContainerID,b.Quantity,
|
|
a.OASNCode,m.BatchCode,f.OOCode,f.Sequence,d.InvDesc,d.ClassName,d.InvStd,b.Amount
|
|
FROM dbo.ICSOASNDetail a
|
|
LEFT JOIN dbo.ICSInventoryLot b ON a.LotNo =b.LotNo AND a.WorkPoint=b.WorkPoint
|
|
left join ICSExtension m on b.ExtensionID=m.ID and b.WorkPoint=m.WorkPoint
|
|
LEFT JOIN dbo.ICSContainerLot c ON a.LotNo=c.LotNo AND a.WorkPoint=c.WorkPoint
|
|
LEFT JOIN dbo.ICSInventory d ON b.InvCode=d.InvCode AND a.WorkPoint=d.WorkPoint
|
|
left join ICSInventoryLotDetail e on b.LotNo=e.LotNo and b.WorkPoint=e.WorkPoint
|
|
LEFT JOIN dbo.ICSOutsourcingOrder f ON e.TransCode=f.OOCode AND e.TransSequence=f.Sequence
|
|
WHERE 1=1
|
|
AND a.OASNCode='" + queryParam["STNO"].ToString() + "' and a.WorkPoint='" + queryParam["WorkPoint"].ToString() + "' ";
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// 选择条码
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public DataTable GetInfoBySelectItemCode(string queryJson, ref Pagination jqgridparam,string WorkPoint)
|
|
{
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @" SELECT a.ID, a.LotNO,d.InvCode,d.InvName,a.Quantity,a.TYPE,
|
|
g.ProjectCode,e.OOCode,e.Sequence,d.InvDesc,d.ClassName,d.INVSTD,g.BatchCode,a.Amount,a.WorkPoint
|
|
|
|
FROM dbo.ICSInventoryLot a
|
|
left join ICSExtension g on a.ExtensionID=g.ID and a.WorkPoint=g.WorkPoint
|
|
LEFT JOIN dbo.ICSContainerLot b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint
|
|
|
|
LEFT JOIN dbo.ICSOASNDetail c ON a.LotNO=c.LotNO AND a.WorkPoint=c.WorkPoint
|
|
LEFT JOIN dbo.ICSInventory d ON a.InvCode=d.InvCode AND a.WorkPoint=d.WorkPoint
|
|
left join ICSInventoryLotDetail f on a.LotNo=f.LotNo and a.WorkPoint=f.WorkPoint
|
|
LEFT JOIN dbo.ICSOutsourcingOrder e ON f.TransCode=e.OOCode AND f.TransSequence=e.Sequence
|
|
WHERE c.LotNo is null and B.LotNo IS NULL and ISNULL(a.EATTRIBUTE1,'') ='' and a.Type='201' AND e.Status<>'3' ";
|
|
//AND a.TYPE='采购'";
|
|
if (!string.IsNullOrEmpty(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
|
|
sql += " and d.InvCode like '%" + queryParam["InvCode"].ToString() + "%'";
|
|
if (!string.IsNullOrWhiteSpace(queryParam["InvName"].ToString()))
|
|
sql += " and d.InvName like '%" + queryParam["InvName"].ToString() + "%'";
|
|
if (!string.IsNullOrWhiteSpace(queryParam["OrderNo"].ToString()))
|
|
sql += " and g.ProjectCode like '%" + queryParam["OrderNo"].ToString() + "%'";
|
|
if (!string.IsNullOrWhiteSpace(queryParam["TransNo"].ToString()))
|
|
sql += " and e.OOCode like '%" + queryParam["TransNo"].ToString() + "%'";
|
|
if (!string.IsNullOrWhiteSpace(queryParam["TransLine"].ToString()))
|
|
sql += " and e.Sequence like '%" + queryParam["TransLine"].ToString() + "%'";
|
|
if (!string.IsNullOrWhiteSpace(queryParam["VenCode"].ToString()))
|
|
sql += " and e.VenCode like '%" + queryParam["VenCode"].ToString() + "%'";
|
|
}
|
|
//string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
sql += " and a.WorkPoint='" + WorkPoint + "'";
|
|
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
public string CheckSTNO_ItemCode(string JsonData, string STNO, string WorkPoint)
|
|
{
|
|
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.ICSOASNDetail
|
|
( ID ,OASNCode ,Sequence ,MUSER ,
|
|
LotNo , Quantity ,Amount ,ODNQuantity ,ODNAmount ,MUSERName,
|
|
MTIME,WorkPoint)
|
|
SELECT NEWID(),'" + STNO + "',ROW_NUMBER()over(partition by LotNO order by mtime desc),'" + MUSER + @"',
|
|
a.LotNO,a.Quantity,a.Quantity*Amount as Amount,0,0,'" + MUSERNAME + @"',
|
|
getdate()," + WorkPoint.TrimEnd(',') + @"
|
|
FROM dbo.ICSInventoryLot a
|
|
WHERE a.ID IN (" + JsonData.TrimEnd(',') + ")";
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取箱号主表信息
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <param name="jqgridparam"></param>
|
|
/// <returns></returns>
|
|
public DataTable ASNCartonGetCartonGridJson(string queryJson, ref Pagination jqgridparam, string WorkPoint)
|
|
{
|
|
string sql = string.Empty;
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
if (queryParam["Type"].ToString()== "ContainerType01")
|
|
{
|
|
sql = @" SELECT DISTINCT a.ContainerCode,a.ID,
|
|
c.MUSERName, a.MTIME,Dil.F_ItemName
|
|
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 and lot.Type='201'
|
|
left join Sys_SRM_ItemsDetail Dil on F_ItemId='6230217e-4413-4427-9bec-c67ba789eca9' and F_EnabledMark='1' and a.ContainerType=Dil.F_ItemCode
|
|
WHERE
|
|
a.ID not IN
|
|
(SELECT b.ContainerID FROM dbo.ICSOASNDetail a inner JOIN dbo.ICSContainerLot b ON a.LotNO =b.LotNO AND a.WorkPoint=b.WorkPoint)
|
|
and a.EATTRIBUTE1='2' and a.ContainerID=''
|
|
";
|
|
}
|
|
else
|
|
{
|
|
sql = @" SELECT DISTINCT a.ContainerCode,a.ID,
|
|
c.MUSERName, a.MTIME,Dil.F_ItemName
|
|
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 and lot.Type='201'
|
|
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 ICSContainer xz on a.ID=xz.ContainerID and a.WorkPoint=xz.WorkPoint
|
|
WHERE
|
|
xz.ID not IN
|
|
(SELECT b.ContainerID FROM dbo.ICSOASNDetail a inner JOIN dbo.ICSContainerLot b ON a.LotNO =b.LotNO AND a.WorkPoint=b.WorkPoint)
|
|
and a.EATTRIBUTE1='2'
|
|
|
|
";
|
|
}
|
|
|
|
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["CartonNo"].ToString()))
|
|
{
|
|
sql += " and a.ContainerID like '%" + queryParam["CartonNo"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["VenCode"].ToString()))
|
|
{
|
|
sql += " and a.EATTRIBUTE8 = '" + queryParam["VenCode"].ToString() + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["Type"].ToString()))
|
|
{
|
|
sql += " and a.ContainerType = '" + queryParam["Type"].ToString() + "' ";
|
|
}
|
|
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
|
|
{
|
|
sql += " and a.WorkPoint='" + WorkPoint + "'";
|
|
|
|
}
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 箱号子表查询
|
|
/// </summary>
|
|
/// <param name="CartonNo"></param>
|
|
/// <param name="jqgridparam"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetCartonSubGridJson(string CartonNo, 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.LotNo,a.ID,c.InvCode,d.InvName,f.TransCode,f.TransSequence,d.InvDesc,d.InvStd
|
|
from ICSContainerLot a
|
|
left join ICSContainer b on a.ContainerID=b.ID and a.WorkPoint=b.WorkPoint
|
|
left join ICSInventoryLot c on a.LotNo=c.LotNo and a.WorkPoint=c.WorkPoint
|
|
left join ICSInventory d on c.InvCode=d.InvCode and c.WorkPoint=d.WorkPoint
|
|
left join ICSInventoryLotDetail f on c.LotNo=f.LotNo and f.WorkPoint=c.WorkPoint
|
|
left join ICSOutsourcingOrder e on f.TransCode=e.OOCode and f.TransSequence=e.Sequence and f.WorkPoint=e.WorkPoint
|
|
WHERE a.ContainerID='" + CartonNo + "' and a.WorkPoint='" + WorkPoint + "'";
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
public string CheckSTNO_CartonNo(string JsonData, string STNO, string WorkPoint)
|
|
{
|
|
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.ICSOASNDetail
|
|
( ID ,OASNCode ,Sequence ,MUSER ,
|
|
LotNo , Quantity ,Amount ,ODNQuantity ,ODNAmount ,MUSERName,
|
|
MTIME,WorkPoint)
|
|
SELECT NEWID(),'" + STNO + "',row_number(),'" + MUSER + @"',
|
|
a.LotNO,b.Quantity,b.Quantity*b.Amount as Amount,0,0,'" + MUSERNAME + @"',
|
|
,getdate()," + WorkPoint.TrimEnd(',') + @"
|
|
FROM dbo.ICSContainerLot a
|
|
left JOIN dbo.ICSInventoryLot b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint
|
|
LEFT JOIN dbo.ICSInventory c ON c.InvCode=b.InvCode AND a.WorkPoint=c.WorkPoint
|
|
WHERE a.ContainerID IN (" + JsonData.TrimEnd(',') + ") and a.WorkPoint='" + WorkPoint + "'";
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
/// <summary>
|
|
/// 删除送货单内的条码或者箱号
|
|
/// </summary>
|
|
/// <param name="keyValue"></param>
|
|
/// <returns></returns>
|
|
public string DeleteInfo(string keyValue,string WorkPoint)
|
|
{
|
|
//string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
string sql = string.Format(@" DELETE FROM dbo.ICSOASNDetail
|
|
WHERE ID IN (
|
|
SELECT ID FROM dbo.ICSOASNDetail WHERE LotNo IN (
|
|
SELECT LotNo FROM dbo.ICSContainerLot WHERE ContainerID IN (
|
|
SELECT a.ContainerID
|
|
FROM dbo.ICSContainerLot a
|
|
LEFT JOIN dbo.ICSOASNDetail b ON a.LotNO=b.LotNo AND a.WorkPoint=b.WorkPoint
|
|
WHERE b.ID IN ({0}) AND a.WorkPoint='{1}')
|
|
))", keyValue.TrimEnd(','), WorkPoint);
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
sql = string.Format(@" DELETE
|
|
FROM dbo.ICSOASNDetail
|
|
WHERE ID IN ({0}) and WorkPoint ='{1}' ", keyValue.TrimEnd(','), WorkPoint);
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 更新送货单
|
|
/// </summary>
|
|
/// <param name="keyValue"></param>
|
|
/// <returns></returns>
|
|
public string UpdateSTNO(string keyValue, string EATTRIBUTE1)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string[] keyValues = keyValue.Split(',');
|
|
string sql = string.Empty;
|
|
sql = "UPDATE ICSOASN SET MTIME = getdate() , EATTRIBUTE1='{2}' WHERE OASNCode = {0} AND WorkPoint in ({1})";
|
|
sql = string.Format(sql, keyValues[0].TrimStart(',').TrimEnd(','), WorkPoint.Trim(','), EATTRIBUTE1);
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
return msg;
|
|
}
|
|
public int UpDateBySTNO(string keyValue, string Status, string WorkPoint)
|
|
{
|
|
//string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
WorkPoint = WorkPoint.Substring(1, WorkPoint.Length - 2);
|
|
string sql = string.Empty;
|
|
sql = @"UPDATE ICSOASN SET Status = '{1}' WHERE OASNCode IN ({0}) AND WorkPoint in({2})";
|
|
sql = string.Format(sql, keyValue.TrimEnd(','), Status, WorkPoint.TrimEnd(','));
|
|
return SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
|
|
|
|
public string GetWWICSASNCode(string RoleEnCode, string WorkPoint)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
string Date = DateTime.Now.ToString("yy");
|
|
string Pre = "DN" + RoleEnCode + Date;
|
|
string ASNCode = GetSerialCode(WorkPoint, "ICSASN", "STNO", Pre, 5);
|
|
return ASNCode;
|
|
}
|
|
|
|
|
|
public string SaveWWICSASNAndDetail(string ICSASN, string WorkPoint)
|
|
{
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
string ID = Guid.NewGuid().ToString();
|
|
string Status;
|
|
#region [获取送货单配置信息,判断状态]
|
|
string F_ItemName = SqlHelper.GetSHDZDSHItemsDetails("ASNAudit", WorkPoint);
|
|
if (!string.IsNullOrWhiteSpace(F_ItemName))
|
|
{
|
|
Status = "2";
|
|
}
|
|
else
|
|
{
|
|
Status = "0";
|
|
}
|
|
#endregion
|
|
JArray res = (JArray)JsonConvert.DeserializeObject(ICSASN);
|
|
foreach (var item in res)
|
|
{
|
|
JObject jo = (JObject)item;
|
|
//创建送货单主表
|
|
sql += @" INSERT INTO dbo.ICSOASN
|
|
( ID ,OASNCode ,VenCode,Status,
|
|
MUSER ,MUSERName ,MTIME,WorkPoint)
|
|
VALUES ( newid(),'{0}','{1}','{2}','{3}','{4}',getdate(),'{5}')";
|
|
sql = string.Format(sql, jo["ASNCode"].ToString(), jo["VenCode"].ToString(), Status, MUSER, MUSERNAME, jo["WorkPoint"].ToString());
|
|
|
|
JArray resdetail = (JArray)JsonConvert.DeserializeObject(jo["Detail"].ToString());
|
|
foreach (var detail in resdetail)
|
|
{
|
|
//创建送货单子表
|
|
JObject det = (JObject)detail;
|
|
sql += @" INSERT INTO dbo.ICSOASNDetail
|
|
( ID ,OASNCode ,Sequence,LotNo,Quantity,Amount,ODNQuantity,ODNAmount,
|
|
MUSER ,MUSERName ,MTIME,WorkPoint)
|
|
VALUES ( newid(),'{0}','{1}','{2}','{3}','{4}',0,0,'{5}','{6}',getdate(),'{7}')";
|
|
sql = string.Format(sql, jo["ASNCode"].ToString(), det["Sequence"].ToString(), det["LotNo"].ToString(), det["Quantity"].ToString(), det["Amount"].ToString(), MUSER, MUSERNAME, jo["WorkPoint"].ToString());
|
|
|
|
}
|
|
}
|
|
|
|
try
|
|
{
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "新增失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
|
|
public string UpdateWWICSASNAndDetail(string ICSASN)
|
|
{
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
string idss = "";
|
|
string Code = "";
|
|
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)
|
|
{
|
|
Code = jo["ASNCode"].ToString();
|
|
JObject det = (JObject)detail;
|
|
|
|
if (string.IsNullOrWhiteSpace(det["ZJID"].ToString()))
|
|
{
|
|
string DetailID = Guid.NewGuid().ToString();
|
|
sql += @"INSERT INTO dbo.ICSOASNDetail
|
|
( ID ,OASNCode ,Sequence,LotNo,Quantity,Amount,ODNQuantity,ODNAmount,
|
|
MUSER ,MUSERName ,MTIME,WorkPoint)
|
|
VALUES ( '{0}','{1}','{2}','{3}','{4}','{5}',0,0,'{6}','{7}',getdate(),'{8}')";
|
|
sql = string.Format(sql, DetailID, jo["ASNCode"].ToString(), det["Sequence"].ToString(), det["LotNo"].ToString(), det["Quantity"].ToString(),
|
|
det["Amount"].ToString(), MUSER, MUSERNAME, jo["WorkPoint"].ToString());
|
|
idss += "'" + DetailID + "',";
|
|
}
|
|
else
|
|
{
|
|
idss += "'" + det["ZJID"].ToString() + "',";
|
|
}
|
|
}
|
|
}
|
|
sql += @"DELETE ICSOASNDetail where OASNCode ='{0}' and id not in ({1})";
|
|
sql = string.Format(sql, Code, idss.Substring(0, idss.Length - 1));
|
|
|
|
try
|
|
{
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "修改失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
|
|
|
|
public DataTable GetLotByContainer(string Value, string WorkPoint)
|
|
{
|
|
Value = Value.TrimEnd(',');
|
|
string sql = @"
|
|
WITH ICSBomALL AS
|
|
(
|
|
SELECT
|
|
ContainerCode AS TContainerCode,
|
|
ContainerCode AS PContainerCode,
|
|
ContainerCode AS ContainerCode,
|
|
ID,
|
|
ContainerID,
|
|
0 AS [Level],
|
|
CAST(1 AS nvarchar(MAX)) AS SortSeq,
|
|
CAST('00001' AS nvarchar(MAX)) AS Sort
|
|
FROM ICSContainer
|
|
WHERE ContainerCode in ({0})
|
|
UNION ALL
|
|
SELECT
|
|
b.TContainerCode,
|
|
b.ContainerCode AS PContainerCode,
|
|
a.ContainerCode,
|
|
a.ID,
|
|
a.ContainerID,
|
|
b.[Level]+1 AS [Level],
|
|
CAST(b.SortSeq AS nvarchar(MAX))+'.'+CAST(row_number() over (order by a.ContainerCode) AS nvarchar(MAX)) AS SortSeq,
|
|
CAST(b.Sort+'.'+REPLICATE('0',5-len(row_number() over (order by a.ContainerCode)))+CAST(row_number() over (order by a.ContainerCode) AS nvarchar(MAX)) AS nvarchar(MAX)) AS Sort
|
|
FROM
|
|
ICSContainer a
|
|
INNER JOIN ICSBomALL b ON a.ID=b.ContainerID
|
|
)
|
|
SELECT a.ID,
|
|
con.ContainerCode,
|
|
con.ContainerName,
|
|
a.LotNo,
|
|
lotD.TransCode,
|
|
lotD.TransSequence,
|
|
a.InvCode,
|
|
inv.InvName,
|
|
inv.InvStd,
|
|
inv.InvUnit,
|
|
inv.InvDesc,
|
|
a.Quantity AS Quantity,
|
|
a.Quantity*(a.Amount/a.Quantity) AS Amount,
|
|
'' AS WHCode,
|
|
'' AS WHName,
|
|
'' AS LocationCode,
|
|
'' AS LocationName,
|
|
ext.BatchCode AS BatchCode,
|
|
inv.AmountUnit,
|
|
ext.ID AS ExtensionID,
|
|
ext.ProjectCode,
|
|
ext.Version,
|
|
--ext.BatchCode,
|
|
ext.Brand,
|
|
ext.cFree1,
|
|
ext.cFree2,
|
|
ext.cFree3,
|
|
ext.cFree4,
|
|
ext.cFree5,
|
|
ext.cFree6,
|
|
ext.cFree7,
|
|
ext.cFree8,
|
|
ext.cFree9,
|
|
ext.cFree10,
|
|
a.MUSER AS [User],
|
|
a.MTIME AS [MTime],
|
|
a.WorkPoint
|
|
FROM ICSInventoryLot a
|
|
left join dbo.ICSInventoryLotDetail lotD on a.LotNo=lotD.LotNo and a.WorkPoint=lotD.WorkPoint
|
|
INNER JOIN ICSExtension ext ON a.ExtensionID=ext.ID AND a.WorkPoint=ext.WorkPoint
|
|
LEFT JOIN ICSContainerLot conlot ON a.LotNo=conlot.LotNo AND a.WorkPoint=conlot.WorkPoint
|
|
|
|
LEFT JOIN ICSContainer con ON conlot.ContainerID=con.ID AND conlot.WorkPoint=con.WorkPoint
|
|
|
|
LEFT JOIN ICSBomALL bom ON bom.ID=con.ContainerID
|
|
INNER JOIN ICSInventory inv ON a.InvCode=inv.InvCode AND a.WorkPoint=inv.WorkPoint
|
|
WHERE bom.ContainerCode in ({0}) AND a.WorkPoint='{1}'
|
|
UNION ALL
|
|
SELECT a.ID,
|
|
con.ContainerCode,
|
|
con.ContainerName,
|
|
a.LotNo,
|
|
lotD.TransCode,
|
|
lotD.TransSequence,
|
|
a.InvCode,
|
|
inv.InvName,
|
|
inv.InvStd,
|
|
inv.InvUnit,
|
|
inv.InvDesc,
|
|
a.Quantity AS Quantity,
|
|
a.Quantity*(a.Amount/a.Quantity) AS Amount,
|
|
'' AS WHCode,
|
|
'' AS WHName,
|
|
'' AS LocationCode,
|
|
'' AS LocationName,
|
|
ext.BatchCode AS BatchCode,
|
|
inv.AmountUnit,
|
|
ext.ID AS ExtensionID,
|
|
ext.ProjectCode,
|
|
ext.Version,
|
|
--ext.BatchCode,
|
|
ext.Brand,
|
|
ext.cFree1,
|
|
ext.cFree2,
|
|
ext.cFree3,
|
|
ext.cFree4,
|
|
ext.cFree5,
|
|
ext.cFree6,
|
|
ext.cFree7,
|
|
ext.cFree8,
|
|
ext.cFree9,
|
|
ext.cFree10,
|
|
a.MUSER AS [User],
|
|
a.MTIME AS [MTime],
|
|
a.WorkPoint
|
|
FROM ICSInventoryLot a
|
|
left join dbo.ICSInventoryLotDetail lotD on a.LotNo=lotD.LotNo and a.WorkPoint=lotD.WorkPoint
|
|
INNER JOIN ICSExtension ext ON a.ExtensionID=ext.ID AND a.WorkPoint=ext.WorkPoint
|
|
LEFT JOIN ICSContainerLot conlot ON a.LotNo=conlot.LotNo AND a.WorkPoint=conlot.WorkPoint
|
|
|
|
LEFT JOIN ICSContainer con ON conlot.ContainerID=con.ID AND conlot.WorkPoint=con.WorkPoint
|
|
|
|
LEFT JOIN ICSBomALL bom ON bom.ID=conlot.ContainerID
|
|
INNER JOIN ICSInventory inv ON a.InvCode=inv.InvCode AND a.WorkPoint=inv.WorkPoint
|
|
WHERE bom.ContainerCode in ({0}) AND a.WorkPoint='{1}'
|
|
";
|
|
sql = string.Format(sql, Value, WorkPoint);
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
}
|
|
}
|