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.
584 lines
31 KiB
584 lines
31 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.Data;
|
|
using System.Data.Common;
|
|
using System.Linq;
|
|
using System.Text;
|
|
|
|
namespace NFine.Application
|
|
{
|
|
public class DXCartonNoManageApp : RepositoryFactory<ICSVendor>
|
|
{
|
|
|
|
public DataTable GetGridJson(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
// string sqlDelete = @" DELETE a FROM ICSContainer a
|
|
//LEFT JOIN dbo.ICSContainerLot b ON a.ID=b.ContainerID
|
|
// WHERE b.ContainerID IS NULL";
|
|
// SqlHelper.ExecuteNonQuery(sqlDelete);
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
//string sql = @"SELECT Serial,CartonNO,CartonStatus,PrintTimes,CONVERT(NVARCHAR(50),lastPrintTime,23) as lastPrintTime,MUSERName, CONVERT(NVARCHAR(50),MTIME,23) as MTIME,EATTRIBUTE8,EATTRIBUTE3,b.cVenName,w.WorkPointName,w.WorkPointCode
|
|
// FROM dbo.ICSCarton a
|
|
// LEFT JOIN Sys_WorkPoint w on a.WorkPoint=w.WorkPointCode
|
|
// LEFT JOIN dbo.ICSVendor b ON a.EATTRIBUTE8=b.cVenCode AND a.WorkPoint=b.WorkPoint
|
|
// WHERE 1=1 and EATTRIBUTE3='SRM'";
|
|
string sql = @"SELECT DISTINCT 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 ICSContainerLot c on a.ContainerID=c.ContainerID AND a.WorkPoint=c.WorkPoint
|
|
LEFT JOIN dbo.ICSInventoryLot d ON c.LotNo=d.LotNo AND c.WorkPoint=d.WorkPoint
|
|
left join ICSInventoryLotDetail f on d.LOTNO=f.LotNO
|
|
LEFT JOIN dbo.ICSPurchaseOrder e ON f.TransCode=e.POCode AND f.TransSequence=e.Sequence AND f.WorkPoint=e.WorkPoint
|
|
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' ";
|
|
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 a.MTIME >= '" + queryParam["TimeFrom"].ToString() + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["TimeTo"].ToString()))
|
|
{
|
|
sql += " and a.MTIME <= '" + queryParam["TimeTo"].ToString() + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["ORDERNO"].ToString()))
|
|
{
|
|
sql += " and e.EATTRIBUTE4 like '%" + queryParam["ORDERNO"].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 CartonNo, 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,c.ItemCODE,d.INVNAME,c.VenderLotNO,c.LOTQTY,c.TYPE,
|
|
// b.EATTRIBUTE8,c.ORDERNO,c.TransNo,c.TransLine,a.LotNo,d.INVDESC,d.INVTYPE,e.MEMO,d.INVSTD,d.INVPARSETYPE
|
|
// FROM dbo.ICSITEMLot2Carton a
|
|
// LEFT JOIN dbo.ICSCarton b ON a.CartonNO=b.CartonNO AND a.WorkPoint=b.WorkPoint
|
|
// LEFT JOIN dbo.ICSITEMLot c ON a.LotNo_ID=c.ID AND a.WorkPoint=c.WorkPoint
|
|
// LEFT JOIN dbo.ICSINVENTORY d ON c.ItemCODE=d.INVCODE AND a.WorkPoint=d.WorkPoint
|
|
// LEFT JOIN dbo.ICSPO_PoMain e ON c.TransNO=e.POCode AND c.TransLine=e.PORow AND a.WorkPoint=e.WorkPoint
|
|
// WHERE a.CartonNO='" + CartonNo + "' and a.WorkPoint in(" + WorkPoint + ")";
|
|
string sql = @"
|
|
|
|
SELECT a.ID,c.InvCode,d.INVNAME,c.Quantity,c.TYPE, b.EATTRIBUTE8,g.ProjectCode,f.TransCode,f.TransSequence,a.LotNo,d.INVDESC,d.INVSTD,e.EATTRIBUTE4
|
|
FROM dbo.ICSContainerLot a
|
|
LEFT JOIN dbo.ICSContainer b ON a.ContainerID=b.ContainerID AND a.WorkPoint=b.WorkPoint
|
|
LEFT JOIN dbo.ICSInventoryLot c ON a.LotNo=c.LotNo AND a.WorkPoint=c.WorkPoint
|
|
left join ICSInventoryLotDetail f on c.LOTNO=f.LotNO
|
|
LEFT JOIN dbo.ICSINVENTORY d ON c.InvCode=d.INVCODE AND a.WorkPoint=d.WorkPoint
|
|
LEFT JOIN dbo.ICSPurchaseOrder e ON f.TransCode=e.POCode AND f.TransSequence=e.Sequence AND a.WorkPoint=e.WorkPoint
|
|
LEFT JOIN dbo.ICSExtension g ON e.ExtensionID=g.ID
|
|
WHERE b.ContainerCode='" + CartonNo + "' and a.WorkPoint in(" + WorkPoint + ")";
|
|
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>();
|
|
if (!string.IsNullOrEmpty(queryParam["CartonNo"].ToString()))
|
|
{
|
|
string sql = @" SELECT a.ID,c.InvCode,d.INVNAME,c.Quantity,c.TYPE,
|
|
b.EATTRIBUTE8,g.ProjectCode,f.TransCode,f.TransSequence,a.LotNo,d.INVDESC,d.INVSTD
|
|
FROM dbo.ICSContainerLot a
|
|
LEFT JOIN dbo.ICSContainer b ON a.ContainerID=b.ContainerID AND a.WorkPoint=b.WorkPoint
|
|
LEFT JOIN dbo.ICSInventoryLot c ON a.LotNo=c.LotNo AND a.WorkPoint=c.WorkPoint
|
|
left join ICSInventoryLotDetail f on c.LOTNO=f.LotNO
|
|
LEFT JOIN dbo.ICSINVENTORY d ON c.InvCode=d.INVCODE AND a.WorkPoint=d.WorkPoint
|
|
LEFT JOIN dbo.ICSPurchaseOrder e ON f.TransCode=e.POCode AND f.TransSequence=e.Sequence AND a.WorkPoint=e.WorkPoint
|
|
LEFT JOIN dbo.ICSExtension g ON e.ExtensionID=g.ID
|
|
WHERE b.ContainerCode='" + queryParam["CartonNo"].ToString() + "' and a.WorkPoint=" + WorkPoint + "and c.Type = '200'";
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
else
|
|
{
|
|
string sql = @"SELECT '' as LotNo,'' as ID,'' as ItemCODE,'' as INVNAME,'' as VenderLotNO,'' as LOTQTY,'' as TYPE,
|
|
'' as EATTRIBUTE8,'' as ORDERNO,'' as TransNo,'' as TransLine,'' as INVDESC,'' as INVTYPE,'' as MEMO,'' as INVSTD,'' as INVPARSETYPE
|
|
";
|
|
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 CartonNo, 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,c.ItemCODE,d.INVNAME,c.VenderLotNO,c.LOTQTY,c.TYPE
|
|
FROM dbo.ICSITEMLot2Carton a
|
|
LEFT JOIN dbo.ICSCarton b ON a.CartonNO=b.CartonNO AND a.WorkPoint=b.WorkPoint
|
|
LEFT JOIN dbo.ICSITEMLot c ON a.LotNo_ID=c.ID AND a.WorkPoint=c.WorkPoint
|
|
LEFT JOIN dbo.ICSINVENTORY d ON c.ItemCODE=d.INVCODE AND a.WorkPoint=d.WorkPoint
|
|
WHERE a.CartonNO='" + CartonNo + "' and a.WorkPoint in(" + WorkPoint + ")";
|
|
if (!string.IsNullOrEmpty(queryJson))
|
|
{
|
|
sql += @"UNION ALL
|
|
SELECT a.ID,a.ItemCODE,b.INVNAME,a.VenderLotNO,a.LOTQTY,a.TYPE
|
|
FROM dbo.ICSITEMLot a
|
|
LEFT JOIN dbo.ICSINVENTORY b ON a.ItemCODE=b.INVCODE AND a.WorkPoint=b.WorkPoint
|
|
WHERE a.ID IN (" + queryJson.TrimEnd(',') + ") and a.WorkPonit in (" + WorkPoint + ")";
|
|
}
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
public DataTable GetSubGridJsonByCreate(string POCode, string PORow)
|
|
{
|
|
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.POCode,a.PORow, CONVERT(NVARCHAR(50),a.PODate,23) as PODate,a.ORDERNO,a.VenCode,a.VenName,a.InvCode,
|
|
b.INVNAME,b.INVSTD,b.INVDESC,b.INVUOM,a.Quantity,ISNULL(c.CreatedQty,0) AS CreatedQty,isnull(c.InQty,0) as InQty,a.WorkPoint
|
|
FROM dbo.ICSPO_PoMain a
|
|
LEFT JOIN dbo.ICSINVENTORY b ON a.InvCode=b.INVCODE AND a.WorkPoint=b.WorkPoint
|
|
LEFT JOIN (SELECT SUM(x.LOTQTY) CreatedQty,TransNO,TransLine,x.WorkPoint,
|
|
SUM(CASE WHEN y.LotNO IS NOT NULL THEN x.LOTQTY ELSE 0 END) AS InQty
|
|
FROM dbo.ICSITEMLot x
|
|
LEFT JOIN dbo.ICSWareHouseLotInfo y ON x.LotNO=y.LotNO AND x.WorkPoint=y.WorkPoint
|
|
GROUP BY TransNO,TransLine,x.WorkPoint) c ON a.POCode=c.TransNO AND a.PORow=c.TransLine AND a.WorkPoint=c.WorkPoint
|
|
WHERE 1=1
|
|
and a.POCode='" + POCode + "' and a.PORow='" + PORow + "' and a.WorkPonit in (" + WorkPoint + "）";
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
public DataTable GetVendorLotNo(string VenCode, string WorkPoint)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string dtPre = DateTime.Now.ToString("yyyyMMdd");
|
|
string sql = @"EXEC Addins_GetSerialCode '" + WorkPoint + "','ICSITEMLotNo','VendorLotNo','" + VenCode + dtPre + "',2";
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
/// <summary>
|
|
/// 生成条码
|
|
/// </summary>
|
|
/// <param name="POCode"></param>
|
|
/// <param name="PORow"></param>
|
|
/// <param name="keyValue"></param>
|
|
/// <returns></returns>
|
|
public int CreateItemLotNo(string POCode, string PORow, string keyValue)
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
int createPageCount = Convert.ToInt32(queryParam["createPageCount"].ToString());
|
|
decimal minPackQty = Convert.ToDecimal(queryParam["minPackQty"].ToString());
|
|
decimal thisCreateQty = Convert.ToDecimal(queryParam["thisCreateQty"].ToString());
|
|
decimal LOTQTY = minPackQty;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string VenCode = GetVendorCode(POCode, PORow, WorkPoint);
|
|
string Pre = VenCode + DateTime.Now.ToString("yyMMdd");
|
|
string sql = string.Empty;
|
|
string VendorLot = queryParam["VendorLot"].ToString();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
|
|
for (int i = 0; i < createPageCount; i++)
|
|
{
|
|
if (i + 1 == createPageCount)
|
|
{
|
|
if (minPackQty * createPageCount > thisCreateQty)
|
|
{
|
|
LOTQTY = thisCreateQty - (minPackQty * (createPageCount - 1));
|
|
}
|
|
}
|
|
string LotNo = GetSerialCode(WorkPoint, "ICSITEMLot", "LotNO", Pre, 5);
|
|
sql += string.Format(@"INSERT INTO dbo.ICSITEMLot
|
|
( ID ,LotNO ,ItemCODE ,TransNO ,TransLine ,VENDORITEMCODE ,VENDORCODE ,
|
|
VenderLotNO ,PRODUCTDATE ,LOTQTY ,ACTIVE ,Exdate ,WorkPoint ,
|
|
MUSER ,MUSERName ,MTIME ,TYPE,ORDERNO)
|
|
SELECT NEWID(),'{0}',InvCode,POCode,PORow,'',NULL,
|
|
'{1}',GETDATE(),'{2}','Y','2999-12-31 00:00:00.000','{3}',
|
|
'{4}','{5}',GETDATE(),'采购原料',ORDERNO
|
|
FROM dbo.ICSPO_PoMain WHERE POCode='{6}' AND PORow='{7}' AND WorkPoint='{3}'",
|
|
LotNo, VendorLot, LOTQTY, WorkPoint, MUSER, MUSERNAME, POCode, PORow);
|
|
sql += "\r\n";
|
|
}
|
|
int count = SqlHelper.ExecuteNonQuery(sql);
|
|
return count;
|
|
}
|
|
|
|
|
|
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 GetVendorCode(string POCode, string PORow, string WorkPoint)
|
|
{
|
|
string sql = string.Format(@"SELECT VenCode FROM dbo.ICSPO_PoMain
|
|
WHERE POCode='{0}' AND PORow='{1}' AND WorkPoint='{2}'", POCode, PORow, WorkPoint);
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
string VenCode = string.Empty;
|
|
if (dt != null && dt.Rows.Count > 0)
|
|
{
|
|
VenCode = dt.Rows[0][0].ToString();
|
|
}
|
|
return VenCode;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 删除条码
|
|
/// </summary>
|
|
/// <param name="keyValue"></param>
|
|
/// <returns></returns>
|
|
public string DeleteItemLot(string keyValue)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
|
|
string sql = string.Format(@"SELECT * FROM dbo.ICSASNDETAIL
|
|
WHERE LOTNO IN (
|
|
SELECT LotNo FROM dbo.ICSITEMLot2Carton WHERE LotNo_ID IN ({0})
|
|
) OR LOTNO IN (
|
|
SELECT LotNO FROM dbo.ICSITEMLot WHERE ID IN ({0})
|
|
) and WorkPoint='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
if (dt == null || dt.Rows.Count <= 0)
|
|
{
|
|
sql = string.Format(@"DELETE FROM dbo.ICSITEMLot2Carton WHERE LotNo_ID IN ({0}) and WorkPoint='{1}'; ", keyValue.TrimEnd(','), WorkPoint);
|
|
DbHelper.ExecuteNonQuery(CommandType.Text, sql);
|
|
}
|
|
else
|
|
{
|
|
msg = "所选条码已加入送货单，请先在送货单管理页面删除该条码！";
|
|
}
|
|
return msg;
|
|
}
|
|
public string DeleteCartonNos(string keyValue, string WorkPoint)
|
|
{
|
|
//string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
//WorkPoint = WorkPoint.Substring(1, WorkPoint.Length - 2);
|
|
string sql = string.Format(@"SELECT * FROM dbo.ICSInventoryLot
|
|
WHERE LOTNO IN (
|
|
SELECT LotNO FROM dbo.ICSContainerLot WHERE ContainerID in ({0}) and WorkPoint in ('{1}'))", keyValue.TrimEnd(','), WorkPoint.TrimEnd(','));
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
if (dt == null || dt.Rows.Count <= 0)
|
|
{
|
|
sql = string.Format(@"DELETE FROM dbo.ICSContainer WHERE ContainerCode in ({0}) and WorkPoint in ('{1}')
|
|
DELETE FROM dbo.ICSContainerLot WHERE ContainerID in ({0}) and WorkPoint in ('{1}') ", keyValue.TrimEnd(','), WorkPoint.TrimEnd(','));
|
|
DbHelper.ExecuteNonQuery(CommandType.Text, sql);
|
|
}
|
|
else
|
|
{
|
|
msg = "所选箱号中已有加入送货单中，请先在送单号中删除！";
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 删除箱号
|
|
/// </summary>
|
|
/// <param name="keyValue"></param>
|
|
/// <returns></returns>
|
|
public string DeleteCartonNo(string keyValue, string WorkPoint)
|
|
{
|
|
WorkPoint = WorkPoint.Substring(1, WorkPoint.Length - 2);
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
|
|
string sql = 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.ContainerCode in ({0}) and a.WorkPoint in ({1}))", keyValue.TrimEnd(','), WorkPoint.TrimEnd(','));
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
if (dt == null || dt.Rows.Count <= 0)
|
|
{
|
|
sql = string.Format(@" DELETE a FROM dbo.ICSContainerLot a
|
|
LEFT JOIN ICSContainer b ON a.ContainerID=b.ContainerID
|
|
WHERE b.ContainerCode in ({0}) AND a.WorkPoint in ({1})
|
|
DELETE FROM dbo.ICSContainer WHERE ContainerCode in ({0}) and WorkPoint in ({1})
|
|
", keyValue.TrimEnd(','), WorkPoint.TrimEnd(','));
|
|
DbHelper.ExecuteNonQuery(CommandType.Text, sql);
|
|
}
|
|
else
|
|
{
|
|
msg = "所选箱号中已有加入送货单中，请先在送单号中删除！";
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 选择条码
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public DataTable GetInfoBySelectItemCode(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"SELECT a.ID, a.LotNO,a.InvCode,d.INVNAME,a.Quantity,a.TYPE,g.ProjectCode,f.TransCode,
|
|
f.TransSequence,d.INVDESC,d.INVSTD
|
|
FROM dbo.ICSInventoryLot a
|
|
LEFT JOIN ICSInventoryLotDetail f ON a.LotNo=f.LotNo AND a.WorkPoint=f.WorkPoint
|
|
LEFT JOIN dbo.ICSContainerLot b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint
|
|
LEFT JOIN dbo.ICSASNDETAIL 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 dbo.ICSPurchaseOrder e ON f.TransCode=e.POCode AND f.TransSequence=e.Sequence AND a.WorkPoint=e.WorkPoint
|
|
left join (SELECT LotNo FROM dbo.ICSContainerLot WHERE ContainerID = '{0}') ss on a.LotNo=ss.LotNO
|
|
LEFT JOIN dbo.ICSExtension g ON e.ExtensionID=g.ID AND e.WorkPoint=g.WorkPoint
|
|
WHERE b.LotNo IS NULL AND c.LOTNO IS NULL and ss.LotNo is NULL";
|
|
sql = string.Format(sql, queryParam["CartonNo"].ToString());
|
|
if (!string.IsNullOrEmpty(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
|
|
sql += " and a.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 f.TransCode like '%" + queryParam["TransNo"].ToString() + "%'";
|
|
if (!string.IsNullOrWhiteSpace(queryParam["TransLine"].ToString()))
|
|
sql += " and f.TransSequence like '%" + queryParam["TransLine"].ToString() + "%'";
|
|
if (!string.IsNullOrWhiteSpace(queryParam["VenCode"].ToString()))
|
|
sql += " and e.VenCode like '%" + queryParam["VenCode"].ToString() + "%'";
|
|
if (!string.IsNullOrWhiteSpace(queryParam["LotNo"].ToString()))
|
|
sql += " and a.LotNo like '%" + queryParam["LotNo"].ToString() + "%'";
|
|
}
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
sql += " and a.WorkPoint=" + WorkPoint + "";
|
|
//string VenCode = NFine.Code.OperatorProvider.Provider.GetCurrent().VenCode;
|
|
//if (!string.IsNullOrEmpty(VenCode))
|
|
//{
|
|
// sql += " AND e.VenCode='"+ VenCode + "'";
|
|
//}
|
|
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取供应商列表
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public DataTable GetVendor()
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
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
|
|
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=" + WorkPoint + "";
|
|
}
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
public string GetCartonNo()
|
|
{
|
|
string CartonNo = string.Empty;
|
|
string VenCode = NFine.Code.OperatorProvider.Provider.GetCurrent().VenCode;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
if (!string.IsNullOrEmpty(VenCode))
|
|
{
|
|
string Date = DateTime.Now.ToString("yyMMdd");
|
|
string Pre = "C" + VenCode + Date;
|
|
CartonNo = GetSerialCode(WorkPoint, "ICSCarton", "CartonNo", Pre, 3);
|
|
}
|
|
if (!string.IsNullOrEmpty(CartonNo))
|
|
{
|
|
string sql = string.Empty;
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
|
|
sql = @"INSERT INTO dbo.ICSContainer
|
|
( ID ,ContainerCode ,ContainerID,ContainerName,InventoryMixed,ProjectMixed,BatchMixed,Multiplex,
|
|
MUSER ,MUSERName ,MTIME,WorkPoint,EATTRIBUTE8,EATTRIBUTE3)
|
|
VALUES ( NEWID(),'" + CartonNo + "',NEWID(),'','','','','','" + MUSER + "','" + MUSERNAME + "',GETDATE()," + WorkPoint + ",'" + VenCode + "','SRM')";
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
return CartonNo;
|
|
}
|
|
|
|
public string GetCartonNoByPerson(string VenCode, string WorkPoint)
|
|
{
|
|
string CartonNo = string.Empty;
|
|
string Date = DateTime.Now.ToString("yyMMdd");
|
|
string Pre = "C" + VenCode + Date;
|
|
CartonNo = GetSerialCode(WorkPoint, "ICSCarton", "CartonNo", Pre, 3);
|
|
if (!string.IsNullOrEmpty(CartonNo))
|
|
{
|
|
string sql = string.Empty;
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
sql = @"INSERT INTO dbo.ICSContainer
|
|
( ID ,ContainerCode ,ContainerID,ContainerName,InventoryMixed,ProjectMixed,BatchMixed,Multiplex,
|
|
MUSER ,MUSERName ,MTIME,WorkPoint,EATTRIBUTE8,EATTRIBUTE3)
|
|
VALUES ( NEWID(),'" + CartonNo + "',NEWID(),'','','','','','" + MUSER + "','" + MUSERNAME + "',GETDATE(),'" + WorkPoint + "','" + VenCode + "','SRM')";
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
return CartonNo;
|
|
}
|
|
|
|
public string CheckCartonNo(string JsonData, string CartonNo, 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 sql = string.Format(@"SELECT DISTINCT * FROM (
|
|
// SELECT DISTINCT INVCODE FROM dbo.ICSITEMLot2Carton
|
|
// WHERE CartonNO='{0}' and WorkPoint={2}
|
|
// UNION ALL
|
|
// SELECT DISTINCT ItemCODE AS INVCODE FROM dbo.ICSITEMLot WHERE ID IN ({1}) and WorkPoint={2}
|
|
// ) t", CartonNo, JsonData.TrimEnd(','), WorkPoint);
|
|
//DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
//if (dt.Rows.Count > 1)
|
|
//{
|
|
// msg = "箱号原有物料与新增物料编码不同！";
|
|
//}
|
|
//else
|
|
//{
|
|
//sql = string.Format(@"UPDATE dbo.ICSCarton SET ItemCode=
|
|
// ( SELECT DISTINCT ItemCODE AS INVCODE FROM dbo.ICSITEMLot WHERE ID IN ({0}) and WorkPoint={2})
|
|
// WHERE CartonNO='{1}' and WorkPoint={2}", JsonData.TrimEnd(','), CartonNo, WorkPoint);
|
|
//try
|
|
//{
|
|
// SqlHelper.ExecuteNonQuery(sql);
|
|
//}
|
|
//catch (Exception ex)
|
|
//{
|
|
// throw new Exception(ex.Message);
|
|
//}
|
|
sql = "SELECT ContainerID FROM ICSContainer WHERE ContainerCode='{0}' ";
|
|
sql = string.Format(sql, CartonNo);
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
string ID = dt.Rows[0]["ContainerID"].ToString();
|
|
sql = @"INSERT INTO dbo.ICSContainerLot
|
|
( ID ,ContainerID ,LotNo ,
|
|
MUSER ,MUSERName ,MTIME ,WorkPoint )
|
|
SELECT NEWID(), '" + ID + @"',a.LotNO,
|
|
'" + MUSER + "','" + MUSERNAME + "',getdate()," + WorkPoint + @"
|
|
FROM dbo.ICSInventoryLot a
|
|
left join ICSInventoryLotDetail b on a.LOTNO=b.LotNO
|
|
WHERE ID 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.ICSContainerLot WHERE ID in ({0}) and WorkPoint in ('{1}') ", keyValue.TrimEnd(','), WorkPoint.TrimEnd(','));
|
|
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
public string CheckIsAll(string CartonNo)
|
|
{
|
|
string sql = @"SELECT a.ContainerCode,b.ID
|
|
FROM dbo.ICSContainer a
|
|
LEFT JOIN ICSContainerLot b ON a.ContainerID=b.ContainerID AND a.WorkPoint=b.WorkPoint
|
|
LEFT JOIN dbo.ICSASNDETAIL c ON c.LotNo=b.LOTNO AND a.WorkPoint=c.WorkPoint
|
|
WHERE a.ContainerCode='" + CartonNo + "' AND c.ID IS not NULL";
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
if (dt != null && dt.Rows.Count > 0)
|
|
{
|
|
return "0";
|
|
}
|
|
else
|
|
{
|
|
return "1";
|
|
}
|
|
}
|
|
}
|
|
}
|