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.
1473 lines
74 KiB
1473 lines
74 KiB
using NFine.Data.Extensions;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Text;
|
|
using NFine.Code;
|
|
using NFine.Repository;
|
|
using System.Data.Common;
|
|
using NFine.Domain._03_Entity.SRM;
|
|
using Newtonsoft.Json;
|
|
using System.Configuration;
|
|
using System.Net;
|
|
using System.IO;
|
|
using Newtonsoft.Json.Linq;
|
|
using System.Data.SqlClient;
|
|
using ICS.Data;
|
|
using System.Linq;
|
|
|
|
namespace NFine.Application.PNWMS
|
|
{
|
|
public class BlitemApp : RepositoryFactory<ICSVendor>
|
|
{
|
|
private static string ERPDB = ConfigurationManager.ConnectionStrings["ERPDB"].ConnectionString;
|
|
public DataTable GetGridJson(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
|
|
#region [SQL]
|
|
string sql = @"SELECT ID, CheckCode, SelectLevel, Status, Quantity, Amount, MUSER, MUSERName, MTIME, WorkPoint
|
|
FROM
|
|
dbo.ICSCheck";
|
|
sql += " WHERE 1=1";
|
|
sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
|
|
#endregion
|
|
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
|
|
{
|
|
sql += " and CheckCode like '%" + queryParam["POCode"].ToString() + "%' ";
|
|
}
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
|
|
{
|
|
sql += " and WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
|
|
{
|
|
sql += " and VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
|
|
}
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
public DataTable GetGridJsonForIntermediate(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
|
|
#region [SQL]
|
|
string sql = @" select A.ID,CheckCode,Case When Status='1' Then '开立' When Status='2' Then '已修正WMS库存' ELSE '已生成盘点差异单' END AS Status
|
|
,A.InvCode,B.InvStd,A.WHCode,CASE WHEN C.BatchEnable=1 THEN A.BatchCode ELSE '' END AS BatchCode,SUM(A.InvQTY) AS Quantity,SUM(A.PrimaryQTY) AS FirstQuantity
|
|
,D.U9WhQuantity,D.U9WhQuantity-SUM(A.PrimaryQTY) as FirstWMSU9DiffQTY
|
|
,SUM(ISNULL(A.PrimaryQTY,0)-ISNULL(A.InvQTY,0)) AS PrimaryDiff,SUM(A.ReplayQTY) AS ReplayQTY,SUM(ISNULL(A.ReplayQTY,0)-ISNULL(A.InvQTY,0)) AS ReplayDiff
|
|
,SUM(A.CheckQTY) AS CheckQTY,SUM(ISNULL(A.CheckQTY,0)-ISNULL(A.InvQTY,0)) AS CheckDiff
|
|
,CASE WHEN SUM(A.PrimaryQTY)>D.U9WhQuantity THEN '盘盈' WHEN SUM(A.PrimaryQTY)=D.U9WhQuantity THEN '无差异'
|
|
ELSE '盘亏' END AS DiffType
|
|
from ICSCheckIntermediate A
|
|
INNER JOIN ICSInventory B ON B.InvCode=A.InvCode AND B.WorkPoint=A.WorkPoint
|
|
LEFT JOIN ICSWarehouse C ON C.WarehouseCode=A.WHCode AND C.WorkPoint=A.WorkPoint
|
|
LEFT JOIN (SELECT Wh,ItemInfo_ItemID,LotInfo_LotCode
|
|
,isnull(SUM ([StoreQty]+[ToRetStQty]),0) AS U9WhQuantity
|
|
FROM {0}.[dbo].InvTrans_WhQoh
|
|
GROUP BY Wh,ItemInfo_ItemID,LotInfo_LotCode)D
|
|
ON D.Wh=C.ID AND D.ItemInfo_ItemID=B.ID AND ISNULL(D.LotInfo_LotCode,'')=CASE WHEN C.BatchEnable=1 THEN A.BatchCode ELSE '' END";
|
|
sql += " WHERE 1=1";
|
|
sql = string.Format(sql, ERPDB);
|
|
#endregion
|
|
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
|
|
{
|
|
sql += " and A.CheckCode like '%" + queryParam["POCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
|
|
{
|
|
sql += " and A.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
|
|
{
|
|
sql += " and a.MTIME >= '" + queryParam["TimeFrom"].ToString() + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
|
|
{
|
|
sql += " and a.MTIME <= '" + queryParam["TimeArrive"].ToString() + "' ";
|
|
}
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
|
|
{
|
|
sql += " and A.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
|
|
}
|
|
sql += " GROUP BY A.ID,A.CheckCode,A.Status,A.InvCode,B.InvStd,A.WHCode,CASE WHEN C.BatchEnable=1 THEN A.BatchCode ELSE '' END,D.U9WhQuantity";
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
public DataTable GetSubGridJsonForIntermediate(string ID, string Inventory, string InvCode, ref Pagination jqgridparam)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string sql = @" select F.ID,A.LotNo,B.InvCode AS INVCode,B.WarehouseCode AS WHCode,D.BatchCode,A.Quantity
|
|
,A.PrimaryQuantity,ISNULL(A.PrimaryQuantity,0)-ISNULL(A.Quantity,0) AS DifferenceNum
|
|
,A.ReplayQuantity,ISNULL(A.ReplayQuantity,0)-ISNULL(A.Quantity,0) AS ReplayDifferenceNum
|
|
,A.CheckQuantity,ISNULL(A.CheckQuantity,0)-ISNULL(A.Quantity,0) AS CheckDifferenceNum
|
|
,G.LocationCode,G.LocationName,A.MUSERName,A.MTIME,C.Type from ICSCheckDetail A
|
|
INNER JOIN ICSWareHouseLotInfo B ON B.LotNo=A.LotNo AND B.WorkPoint=A.WorkPoint
|
|
INNER JOIN ICSInventoryLot C ON C.LotNo=A.LotNo AND C.WorkPoint=A.WorkPoint
|
|
INNER JOIN ICSExtension D ON D.ID=C.ExtensionID
|
|
LEFT JOIN ICSCheck E ON E.ID=A.CheckID
|
|
LEFT JOIN ICSCheckIntermediate F ON F.CheckCode=E.CheckCode AND F.InvCode=B.InvCode AND F.BatchCode=D.BatchCode AND F.WHCode=B.WarehouseCode
|
|
LEFT JOIN ICSLocation G ON G.LocationCode=B.LocationCode AND G.WorkPoint=B.WorkPoint
|
|
WHERE F.ID='" + ID + "' and a.WorkPoint in ('" + WorkPoint + "')";
|
|
if (!string.IsNullOrWhiteSpace(InvCode))
|
|
{
|
|
sql += " and F.InvCode like '%" + InvCode + "%' ";
|
|
}
|
|
if (Inventory == "1")
|
|
{
|
|
sql += "and A.Quantity>0";
|
|
}
|
|
else if (Inventory == "2")
|
|
{
|
|
sql += "and A.Quantity<=0";
|
|
|
|
}
|
|
else
|
|
{
|
|
|
|
}
|
|
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
public DataTable GetGridJsonForApprove(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
|
|
#region [SQL]
|
|
string sql = @" SELECT ID, CheckCode, SelectLevel, Status, Quantity, Amount, MUSER, MUSERName, MTIME, WorkPoint
|
|
FROM
|
|
dbo.ICSCheck";
|
|
sql += " WHERE 1=1";
|
|
sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
|
|
#endregion
|
|
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
|
|
{
|
|
sql += " and CheckCode like '%" + queryParam["POCode"].ToString() + "%' ";
|
|
}
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
|
|
{
|
|
sql += " and WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
|
|
{
|
|
sql += " and VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
|
|
}
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
public DataTable GetSubGridJson(string ID, string Inventory, string InvCode, ref Pagination jqgridparam)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string sql = @" SELECT distinct a.ID, b.InvCode,
|
|
b.InvName ,
|
|
b.InvUnit,
|
|
b.InvStd ,
|
|
a.LotNo,
|
|
e.BatchCode,
|
|
a.Quantity,
|
|
|
|
d.WarehouseCode,
|
|
d.LocationCode,
|
|
a.PrimaryQuantity,
|
|
a.PrimaryAmount,
|
|
a.ReplayQuantity,
|
|
a.ReplayAmount,
|
|
a.CheckQuantity,
|
|
a.CheckAmount,
|
|
a.PrimaryQuantity- a.Quantity as DifferenceNum,
|
|
a.ReplayQuantity- a.Quantity as ReplayDifferenceNum,
|
|
a.CheckQuantity- a.Quantity as CheckDifferenceNum,
|
|
d.LockQuantity
|
|
|
|
FROM ICSCheckDetail a
|
|
left join ICSInventoryLot c on a.LotNo=c.LotNO and a.WorkPoint=c.WorkPoint
|
|
left join ICSInventory b on c.InvCode=b.InvCode AND c.WorkPoint=b.WorkPoint
|
|
left join ICSWareHouseLotInfo d on c.LotNO=d.LotNO AND c.WorkPoint=d.WorkPoint
|
|
left join dbo.ICSExtension e on c.ExtensionID=e.ID and c.WorkPoint=e.WorkPoint
|
|
WHERE a.CheckID='" + ID + "' and a.WorkPoint in ('" + WorkPoint + "')";
|
|
if (!string.IsNullOrWhiteSpace(InvCode))
|
|
{
|
|
sql += " and b.InvCode like '%" + InvCode + "%' ";
|
|
}
|
|
if (Inventory == "1")
|
|
{
|
|
sql += "and a.Quantity>0";
|
|
}
|
|
else if (Inventory == "2")
|
|
{
|
|
sql += "and a.Quantity<=0";
|
|
|
|
}
|
|
else
|
|
{
|
|
|
|
}
|
|
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
public string DeleteICSCheckDetail(string ID)
|
|
{
|
|
//站点信息
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
try
|
|
{
|
|
sql = string.Format(@"DELETE FROM ICSCheckDetail WHERE ID ='{0}' and WorkPoint ='{1}'", ID, WorkPoint);
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取仓库
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public DataTable GetWHCode()
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string sql = @" select '' as ID,'' as WarehouseName union all
|
|
SELECT ID+'|'+WarehouseCode,WarehouseName FROM ICSWarehouse WITH (NOLOCK) WHERE WorkPoint = '{0}' ";
|
|
sql = string.Format(sql, WorkPoint);
|
|
//string role = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode;
|
|
//if (role != "admin")
|
|
//{
|
|
// sql += " and b.WorkPoint='" + WorkPoint + "'";
|
|
//}
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
public DataTable GetQU(string WHCodeID)
|
|
{
|
|
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string sql = @" select '' as QUValue,'' as QUName union all
|
|
SELECT Region as QUValue,Region+'区' as QUName FROM ICSLocation WITH (NOLOCK) WHERE WHID = '{0}' AND WorkPoint='{1}'
|
|
";
|
|
sql = string.Format(sql, WHCodeID, WorkPoint);
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
|
|
//public DataTable GetQU(string WHCodeID)
|
|
//{
|
|
// string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
// string sql = @"SELECT Region as QUValue,Region+'区' as QUName FROM ICSLocation WHERE WHID = '{0}' AND WorkPoint='{1}'";
|
|
// sql = string.Format(sql, WHCodeID, WorkPoint);
|
|
// DataTable dt = Repository().FindTableBySql(sql);
|
|
// return dt;
|
|
//}
|
|
|
|
public DataTable GetPai(string WHCodeID, string Qu)
|
|
{
|
|
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string sql = @" select '' as PaiValue,'' as PaiName union all
|
|
SELECT Row as PaiValue,Row+'排' as PaiName FROM ICSLocation WITH (NOLOCK) WHERE WHID = '{0}' AND WorkPoint='{1}' and Region='{2}'
|
|
";
|
|
sql = string.Format(sql, WHCodeID, WorkPoint, Qu);
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
public DataTable GetHuoJia(string WHCodeID, string Qu, string Pai)
|
|
{
|
|
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string sql = @" select '' as HuoJiaValue,'' as HuoJiaName union all
|
|
SELECT GoodsShelf as HuoJiaValue,GoodsShelf +'货架' as HuoJiaName FROM ICSLocation WITH (NOLOCK) WHERE WHID = '{0}' AND WorkPoint='{1}' and Region='{2}' and Row='{3}'
|
|
|
|
";
|
|
sql = string.Format(sql, WHCodeID, WorkPoint, Qu, Pai);
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
public DataTable GetCeng(string WHCodeID, string Qu, string Pai, string HuoJia)
|
|
{
|
|
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string sql = @" select '' as CengValue,'' as CengName union all
|
|
SELECT Tier as CengValue,Tier +'层' as CengName FROM ICSLocation WITH (NOLOCK) WHERE WHID = '{0}' AND WorkPoint='{1}' and Region='{2}' and Row='{3}' and GoodsShelf ='{4}'
|
|
|
|
";
|
|
sql = string.Format(sql, WHCodeID, WorkPoint, Qu, Pai, HuoJia);
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
public DataTable GetGe(string WHCodeID, string Qu, string Pai, string HuoJia, string Ceng)
|
|
{
|
|
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string sql = @" select '' as GeValue,'' as GeName union all
|
|
SELECT Grid as GeValue,Grid +'格' as GeName FROM ICSLocation WITH (NOLOCK) WHERE WHID = '{0}' AND WorkPoint='{1}' and Region='{2}' and Row='{3}' and GoodsShelf ='{4}' and Tier='{5}'
|
|
|
|
";
|
|
sql = string.Format(sql, WHCodeID, WorkPoint, Qu, Pai, HuoJia, Ceng);
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
public string AddICSCheck(string Parameter)
|
|
{
|
|
string msg = "";
|
|
string APIURL = ConfigurationManager.ConnectionStrings["APIURL"].ConnectionString + "Check/Create";
|
|
string result = HttpPost(APIURL, Parameter);
|
|
JObject Obj = (JObject)JsonConvert.DeserializeObject(result);//或者JObject jo = JObject.Parse(jsonText);
|
|
string MessAge = Obj["Message"].ToString();
|
|
string Success = Obj["Success"].ToString();
|
|
if (Success.ToUpper() == "FALSE")
|
|
{
|
|
msg = MessAge;
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
|
|
//接口api解析
|
|
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 DeleteICSCheck(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(@" IF EXISTS(SELECT CheckID FROM ICSCheckDetail WHERE CheckID in ({0}) and EATTRIBUTE1 ='1' )
|
|
BEGIN
|
|
RAISERROR('该盘点计划已执行,无法删除！',16,1);
|
|
RETURN
|
|
END
|
|
DELETE FROM dbo.ICSCheck WHERE ID IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
sql += string.Format(@"DELETE FROM dbo.ICSCheckDetail WHERE CheckID IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
|
|
public DataTable GetGridJsonWarehouse(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
|
|
#region [SQL]
|
|
string sql = @"select distinct a.ID, a.WarehouseCode as SelectLevel ,a.WarehouseCode,a.WarehouseName
|
|
from ICSWarehouse a ";
|
|
|
|
#endregion
|
|
|
|
if (!string.IsNullOrWhiteSpace(queryJson) && !string.IsNullOrWhiteSpace(queryParam["StartDate"].ToString()) && !string.IsNullOrWhiteSpace(queryParam["EndDate"].ToString()))
|
|
{
|
|
sql += " INNER JOIN ICSWareHouseLotInfoLog c ON (c.FromWarehouseCode=a.WarehouseCode or c.ToWarehouseCode=a.WarehouseCode) AND a.WorkPoint=c.WorkPoint AND (c.TransType='1' or c.TransType='2') WHERE 1=1 ";
|
|
if (!string.IsNullOrWhiteSpace(queryParam["StartDate"].ToString()))
|
|
{
|
|
sql += " and c.MTIME >= '" + queryParam["StartDate"].ToString() + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["EndDate"].ToString()))
|
|
{
|
|
sql += " and c.MTIME < '" + queryParam["EndDate"].ToString() + "' ";
|
|
}
|
|
}
|
|
else
|
|
{
|
|
sql += " WHERE 1=1";
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
|
|
{
|
|
sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
|
|
{
|
|
sql += " and a.VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
|
|
}
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
|
|
|
|
public DataTable GetSubGridJsonRegion(string ID, ref Pagination jqgridparam)
|
|
{
|
|
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string sql = @" select distinct a.Region+a.Row as SelectLevel,
|
|
--dbo.GetSelectLevel(a.LocationCode,b.WarehouseCode,a.Region) as SelectLevel,
|
|
a.WHID, b.WarehouseCode,a.Region,a.Row from ICSLocation a left join ICSWarehouse b on a.WHID=b.ID and a.WorkPoint=b.WorkPoint
|
|
WHERE a.WHID='" + ID + "' and a.WorkPoint in ('" + WorkPoint + "')";
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public DataTable GetSubGridJsonRow(string WHID, string Region, ref Pagination jqgridparam)
|
|
{
|
|
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string sql = @" select distinct isnull(a.Region,'')+isnull(a.Row,'')+isnull(a.GoodsShelf,'') as SelectLevel,
|
|
--dbo.GetSelectLevel(a.LocationCode,b.WarehouseCode,a.Region+'-'+a.Row) as SelectLevel,
|
|
a.WHID, b.WarehouseCode,a.Region ,a.Row ,a.GoodsShelf
|
|
from ICSLocation a left join ICSWarehouse b on a.WHID=b.ID and a.WorkPoint=b.WorkPoint
|
|
WHERE a.WHID='" + WHID + "' and a.Region='" + Region + "' and isnull(a.Row,'') !='' and a.WorkPoint in ('" + WorkPoint + "')";
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
|
|
|
|
public DataTable GetSubGridJsonGoodsShelf(string WHID, string Region, string Row, ref Pagination jqgridparam)
|
|
{
|
|
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string sql = @" select distinct isnull(a.Region,'')+isnull(a.Row,'')+isnull(a.GoodsShelf,'') +isnull(a.Tier,'') as SelectLevel,
|
|
--dbo.GetSelectLevel(a.LocationCode,b.WarehouseCode,a.Region+'-'+a.Row+'-'+a.GoodsShelf) as SelectLevel,
|
|
a.WHID, b.WarehouseCode,a.Region ,a.Row,a.GoodsShelf ,a.Tier
|
|
from ICSLocation a left join ICSWarehouse b on a.WHID=b.ID and a.WorkPoint=b.WorkPoint
|
|
WHERE a.WHID='" + WHID + "' and a.Region='" + Region + "' and a.Row='" + Row + "' and isnull(a.GoodsShelf,'') !='' and a.WorkPoint in ('" + WorkPoint + "')";
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
public DataTable GetSubGridJsonTier(string WHID, string Region, string Row, string GoodsShelf, ref Pagination jqgridparam)
|
|
{
|
|
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string sql = @" select distinct isnull(a.Region,'')+isnull(a.Row,'')+isnull(a.GoodsShelf,'') +isnull(a.Tier,'')+isnull(a.Grid,'')+ as SelectLevel,
|
|
--dbo.GetSelectLevel(a.LocationCode,b.WarehouseCode,a.Region+'-'+a.Row+'-'+a.GoodsShelf+'-'+a.Tier) as SelectLevel,
|
|
a.WHID, b.WarehouseCode,a.Region ,a.Row,a.GoodsShelf ,a.Tier
|
|
from ICSLocation a left join ICSWarehouse b on a.WHID=b.ID and a.WorkPoint=b.WorkPoint
|
|
WHERE a.WHID='" + WHID + "' and a.Region='" + Region + "' and a.Row='" + Row + "' and a.GoodsShelf='" + GoodsShelf + "' and isnull(a.Tier,'') !='' and a.WorkPoint in ('" + WorkPoint + "')";
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
|
|
|
|
public DataTable GetSubGridJsonGrid(string WHID, string Region, string Row, string GoodsShelf, string Tier, ref Pagination jqgridparam)
|
|
{
|
|
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string sql = @" select distinct isnull(a.Region,'')+isnull(a.Row,'')+isnull(a.GoodsShelf,'') +isnull(a.Tier,'')+isnull(a.Grid,'') as SelectLevel,
|
|
--dbo.GetSelectLevel(a.LocationCode,b.WarehouseCode,a.Region+'-'+a.Row+'-'+a.GoodsShelf+'-'+a.Tier) as SelectLevel,
|
|
a.WHID, b.WarehouseCode,a.Region ,a.Row ,a.GoodsShelf ,a.Tier,a.Grid
|
|
from ICSLocation a left join ICSWarehouse b on a.WHID=b.ID and a.WorkPoint=b.WorkPoint
|
|
WHERE a.WHID='" + WHID + "' and a.Region='" + Region + "' and a.Row='" + Row + "' and a.Tier='" + Tier + "' and a.GoodsShelf='" + GoodsShelf + "' and isnull(a.Grid,'') !='' and a.WorkPoint in ('" + WorkPoint + "')";
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
|
|
public DataTable StatementExportAll(string ID, string Inventory, string InvCode)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string sql = string.Empty;
|
|
object Figure = GetDecimalDigits();
|
|
string TableCode = string.Empty;
|
|
#region sql语句
|
|
|
|
sql = @"SELECT distinct e.CheckCode as 盘点单号, a.LotNo as 条码,
|
|
f.BatchCode as 批次,
|
|
b.InvCode as 料品代码,
|
|
b.InvName as 料品名称,
|
|
b.InvStd as 规格型号,
|
|
b.InvUnit as 单位,
|
|
CAST(a.Quantity AS DECIMAL(18,{0})) as 库存数量,
|
|
d.WarehouseCode as 仓库,
|
|
d.LocationCode as 库位,
|
|
CAST(a.PrimaryQuantity AS DECIMAL(18,{0})) as 初盘数量,
|
|
CAST(a.PrimaryAmount AS DECIMAL(18,{0})) as 初盘辅计量数量,
|
|
a.PrimaryQuantity- a.Quantity as 初盘差异,
|
|
CAST(a.ReplayQuantity as DECIMAL(18,{0})) as 复盘数量,
|
|
CAST(a.ReplayAmount as DECIMAL(18,{0})) as 复盘辅计量数量,
|
|
a.ReplayQuantity- a.Quantity as 复盘差异,
|
|
CAST(a.CheckQuantity as DECIMAL(18,{0})) as 抽盘数量,
|
|
CAST(a.CheckAmount as DECIMAL(18,{0})) as 抽盘辅计量数量,
|
|
a.CheckQuantity- a.Quantity as 抽盘差异,
|
|
a.EATTRIBUTE1,a.EATTRIBUTE2,a.EATTRIBUTE3,a.EATTRIBUTE4,a.EATTRIBUTE5,a.EATTRIBUTE6,a.EATTRIBUTE7,a.EATTRIBUTE8,
|
|
a.EATTRIBUTE9,a.EATTRIBUTE10
|
|
|
|
FROM ICSCheck e
|
|
left join dbo.ICSCheckDetail a on a.CheckID=e.ID and a.WorkPoint=e.WorkPoint
|
|
left join ICSInventoryLot c on a.LotNo=c.LotNO and a.WorkPoint=c.WorkPoint
|
|
left join ICSInventory b on c.InvCode=b.InvCode AND c.WorkPoint=b.WorkPoint
|
|
left join ICSWareHouseLotInfo d on c.LotNO=d.LotNO AND c.WorkPoint=d.WorkPoint
|
|
left join dbo.ICSExtension f on c.ExtensionID=f.ID and c.WorkPoint=f.WorkPoint
|
|
WHERE 1=1 and e.CheckCode in (" + ID.TrimEnd(',') + ") ";
|
|
TableCode = "ICSCheckDetail";
|
|
|
|
|
|
#endregion
|
|
sql = sql + " and a.WorkPoint='{1}'";
|
|
if (!string.IsNullOrWhiteSpace(InvCode))
|
|
{
|
|
sql += " and b.InvCode like '%" + InvCode + "%' ";
|
|
}
|
|
if (Inventory == "1")
|
|
{
|
|
sql += "and a.Quantity>0";
|
|
}
|
|
else if (Inventory == "2")
|
|
{
|
|
sql += "and a.Quantity<=0";
|
|
|
|
}
|
|
sql = string.Format(sql, Figure, WorkPoint);
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
|
|
|
|
DataTable dtColumn = ColumnEnable(TableCode);
|
|
foreach (DataRow drC in dtColumn.Rows)
|
|
{
|
|
if (dt.Columns.Contains(drC["ColumnCode"].ToString()))
|
|
{
|
|
if (!Convert.ToBoolean(drC["Enable"]))
|
|
{
|
|
dt.Columns.Remove(drC["ColumnCode"].ToString());
|
|
}
|
|
else if (Convert.ToBoolean(drC["Enable"]))
|
|
{
|
|
dt.Columns[drC["ColumnCode"].ToString()].ColumnName = drC["Name"].ToString();
|
|
}
|
|
}
|
|
}
|
|
return dt;
|
|
}
|
|
/// <summary>
|
|
/// 盘点审核界面按单据导出
|
|
/// </summary>
|
|
/// <param name="ID"></param>
|
|
/// <param name="Inventory"></param>
|
|
/// <param name="InvCode"></param>
|
|
/// <returns></returns>
|
|
public DataTable StatementExportAllByDoc(string ID, string Inventory, string InvCode)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string sql = string.Empty;
|
|
#region sql语句
|
|
|
|
sql = @"select CheckCode AS 盘点单号
|
|
,Case When Status='1' Then '开立' When Status='2' Then '已修正WMS库存' ELSE '已生成盘点差异单' END AS 盘点单状态
|
|
,A.InvCode AS 物料编码,B.InvName AS 物料名称,B.InvStd AS 物料规格,A.WHCode AS 仓库编码
|
|
,C.WarehouseName AS 仓库名称,A.BatchCode AS 批号,SUM(A.InvQTY) AS 库存数量
|
|
,SUM(A.PrimaryQTY) AS WMS盘点数量,ISNULL(D.U9WhQuantity,0) AS U9现存量
|
|
,ISNULL(D.U9WhQuantity,0)-SUM(A.PrimaryQTY) as U9盘点差异
|
|
,SUM(ISNULL(A.PrimaryQTY,0)-ISNULL(A.InvQTY,0)) AS WMS盘点差异
|
|
,CASE WHEN SUM(A.PrimaryQTY)>ISNULL(D.U9WhQuantity,0) THEN '盘盈' WHEN SUM(A.PrimaryQTY)=ISNULL(D.U9WhQuantity,0) THEN '无差异'
|
|
ELSE '盘亏' END AS 盘点类型
|
|
from ICSCheckIntermediate A
|
|
INNER JOIN ICSInventory B ON B.InvCode=A.InvCode AND B.WorkPoint=A.WorkPoint
|
|
LEFT JOIN ICSWarehouse C ON C.WarehouseCode=A.WHCode AND C.WorkPoint=A.WorkPoint
|
|
LEFT JOIN (SELECT Wh,ItemInfo_ItemID,LotInfo_LotCode
|
|
,isnull(SUM ([StoreQty]+[ToRetStQty]),0) AS U9WhQuantity
|
|
FROM {0}.[dbo].InvTrans_WhQoh
|
|
GROUP BY Wh,ItemInfo_ItemID,LotInfo_LotCode)D
|
|
ON D.Wh=C.ID AND D.ItemInfo_ItemID=B.ID AND D.LotInfo_LotCode=A.BatchCode
|
|
WHERE 1=1 and A.CheckCode in (" + ID.TrimEnd(',') + ") ";
|
|
|
|
#endregion
|
|
sql = sql + " and a.WorkPoint='{1}'";
|
|
if (!string.IsNullOrWhiteSpace(InvCode))
|
|
{
|
|
sql += " and A.InvCode like '%" + InvCode + "%' ";
|
|
}
|
|
if (Inventory == "1")
|
|
{
|
|
sql += "and A.InvQTY>0";
|
|
}
|
|
else if (Inventory == "2")
|
|
{
|
|
sql += "and A.InvQTY<=0";
|
|
}
|
|
sql += " GROUP BY A.ID,A.CheckCode,A.Status,A.InvCode,B.InvName,B.InvStd,A.WHCode,C.WarehouseName,A.BatchCode,D.U9WhQuantity";
|
|
sql = string.Format(sql, ERPDB, WorkPoint);
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
/// <summary>
|
|
/// 盘点审核界面按条码导出
|
|
/// </summary>
|
|
/// <param name="ID"></param>
|
|
/// <param name="Inventory"></param>
|
|
/// <param name="InvCode"></param>
|
|
/// <returns></returns>
|
|
public DataTable StatementExportAllByLot(string ID, string Inventory, string InvCode)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string sql = string.Empty;
|
|
#region sql语句
|
|
|
|
sql = @"select A.LotNo AS 物料条码,B.InvCode AS 物料编码,H.InvName AS 物料名称,B.WarehouseCode AS 仓库编码,I.WarehouseName AS 仓库名称
|
|
,D.BatchCode AS 批号,A.Quantity AS WMS库存,A.PrimaryQuantity AS 盘点数量
|
|
,ISNULL(A.PrimaryQuantity,0)-ISNULL(A.Quantity,0) AS 盘点差异
|
|
,G.LocationCode AS 库位编码,G.LocationName AS 库位名称
|
|
,A.MUSERName AS 盘点人,A.MTIME AS 盘点时间 from ICSCheckDetail A
|
|
INNER JOIN ICSWareHouseLotInfo B ON B.LotNo=A.LotNo AND B.WorkPoint=A.WorkPoint
|
|
INNER JOIN ICSInventoryLot C ON C.LotNo=A.LotNo AND C.WorkPoint=A.WorkPoint
|
|
INNER JOIN ICSExtension D ON D.ID=C.ExtensionID
|
|
LEFT JOIN ICSCheck E ON E.ID=A.CheckID
|
|
LEFT JOIN ICSCheckIntermediate F ON F.CheckCode=E.CheckCode AND F.InvCode=B.InvCode AND F.BatchCode=D.BatchCode AND F.WHCode=B.WarehouseCode
|
|
LEFT JOIN ICSLocation G ON G.LocationCode=B.LocationCode AND G.WorkPoint=B.WorkPoint
|
|
INNER JOIN ICSInventory H ON H.InvCode=B.InvCode AND H.WorkPoint=B.WorkPoint
|
|
LEFT JOIN ICSWarehouse I ON I.WarehouseCode=B.WarehouseCode AND I.WorkPoint=B.WorkPoint
|
|
WHERE F.CheckCode in (" + ID.TrimEnd(',') + ")";
|
|
|
|
|
|
#endregion
|
|
sql = sql + " and a.WorkPoint='{0}'";
|
|
if (!string.IsNullOrWhiteSpace(InvCode))
|
|
{
|
|
sql += " and A.InvCode like '%" + InvCode + "%' ";
|
|
}
|
|
if (Inventory == "1")
|
|
{
|
|
sql += "and A.Quantity>0";
|
|
}
|
|
else if (Inventory == "2")
|
|
{
|
|
sql += "and A.Quantity<=0";
|
|
}
|
|
sql = string.Format(sql, WorkPoint);
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
|
|
public DataTable ColumnEnable(string TableCode)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string sql = string.Empty;
|
|
sql = @"select ColumnCode,Name,Enable from ICSColumnEnable where TableCode='{0}' and WorkPoint='{1}'";
|
|
sql = string.Format(sql, TableCode, WorkPoint);
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
|
|
public object GetDecimalDigits()
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
try
|
|
{
|
|
string sql = string.Empty;
|
|
sql = @"select Figure from ICSConfiguration where Code='Figure001' and Enable='1' and WorkPoint='" + WorkPoint + "'";
|
|
object Figure = SqlHelper.ExecuteScalar(sql);
|
|
return Figure;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message.ToString());
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 按照物料显示数据
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <param name="jqgridparam"></param>
|
|
/// <returns></returns>
|
|
|
|
public DataTable GetGridJsonInv(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
|
|
#region [SQL]
|
|
string sql = @"select DISTINCT a.ID ,a.InvName,a.InvStd,a.InvCode,a.InvUnit from ICSWareHouseLotInfo b
|
|
INNER JOIN ICSInventory a ON a.InvCode=b.InvCode AND a.WorkPoint=b.WorkPoint ";
|
|
sql += " WHERE 1=1";
|
|
sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
|
|
#endregion
|
|
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
//if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
|
|
//{
|
|
// sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
|
|
//}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
|
|
{
|
|
string[] values = queryParam["InvCode"].ToString().Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
|
|
string InvCode = "'" + string.Join("','", values) + "'";
|
|
if (values.Length > 1)
|
|
{
|
|
sql += " and a.InvCode in (" + InvCode + ") ";
|
|
}
|
|
else
|
|
{
|
|
sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
|
|
}
|
|
|
|
|
|
}
|
|
|
|
|
|
if (!string.IsNullOrWhiteSpace(queryParam["InvName"].ToString()))
|
|
{
|
|
sql += " and a.InvName like '%" + queryParam["InvName"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["InvStd"].ToString()))
|
|
{
|
|
sql += " and a.InvStd like '%" + queryParam["InvStd"].ToString() + "%' ";
|
|
}
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
|
|
{
|
|
sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
|
|
}
|
|
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
|
|
|
|
|
|
public string AuditICSCheck(string keyValue)
|
|
{
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
try
|
|
{
|
|
string CheckCodeList = "";
|
|
foreach (string CheckCode in keyValue.TrimEnd(',').Split(','))
|
|
{
|
|
if (!CheckCodeList.Contains(CheckCode))
|
|
{
|
|
CheckCodeList += CheckCode + ",";
|
|
}
|
|
}
|
|
CheckCodeList = CheckCodeList.TrimEnd(',');
|
|
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
|
|
{
|
|
string sql = @" SELECT a.WorkPoint,a.INVID,a.InvCode,a.WHID,a.WHCode,SUM(a.PrimaryQTY) as PrimaryQTY,a.BatchCode INTO #TempWMS FROM (
|
|
select a.WorkPoint,B.ID AS INVID,a.InvCode,C.ID AS WHID,a.WHCode as WHCode ,
|
|
A.PrimaryQTY
|
|
,CASE WHEN C.BatchEnable=1 THEN A.BatchCode ELSE '' END AS BatchCode
|
|
from ICSCheckIntermediate A
|
|
INNER JOIN ICSInventory B ON B.InvCode=A.InvCode AND B.WorkPoint=A.WorkPoint
|
|
LEFT JOIN ICSWarehouse C ON C.WarehouseCode=A.WHCode AND C.WorkPoint=A.WorkPoint
|
|
where a.CheckCode in ({0}) and a.WorkPoint='{1}'
|
|
group by a.WorkPoint,a.InvCode,a.WHCode,a.BatchCode,A.PrimaryQTY,B.ID,C.ID,C.BatchEnable
|
|
UNION ALL
|
|
SELECT A.WorkPoint AS WorkPoint,I.ID AS INVID,D.InvCode AS InvCode,G.ID AS WHID,F.WarehouseCode AS WHCode
|
|
,SUM(ISNULL(A.PrimaryQuantity,0))
|
|
,CASE WHEN G.BatchEnable=1 THEN E.BatchCode ELSE '' END AS BatchCode
|
|
FROM ICSCheckDetail A
|
|
LEFT JOIN ICSCheckIntermediateTable B
|
|
ON B.LotNo=A.LotNo AND B.CheckID=A.CheckID AND B.WorkPoint=A.WorkPoint
|
|
LEFT JOIN ICSCheck C ON C.ID=A.CheckID AND C.WorkPoint=A.WorkPoint
|
|
LEFT JOIN ICSInventoryLot D ON D.LotNo=A.LotNo AND D.WorkPoint=A.WorkPoint
|
|
LEFT JOIN ICSExtension E ON E.ID=D.ExtensionID AND E.WorkPoint=D.WorkPoint
|
|
LEFT JOIN ICSWareHouseLotInfo F ON F.LotNo=A.LotNo AND F.WorkPoint=A.WorkPoint
|
|
LEFT JOIN ICSWarehouse G ON G.WarehouseCode=F.WarehouseCode AND G.WorkPoint=A.WorkPoint
|
|
LEFT JOIN ICSInventory I ON I.InvCode=D.InvCode AND I.WorkPoint=D.WorkPoint
|
|
WHERE B.LotNo IS NULL
|
|
AND C.CheckCode IN ({0}) AND A.WorkPoint='{1}'
|
|
AND F.WorkPoint='{1}'
|
|
GROUP BY A.WorkPoint,D.InvCode,F.WarehouseCode,E.BatchCode,G.ID,I.ID,G.BatchEnable) A
|
|
GROUP BY a.WorkPoint,a.INVID,a.InvCode,a.WHID,a.WHCode,a.BatchCode
|
|
|
|
select A.WorkPoint AS WorkPoint,A.InvCode AS InvCode,A.WHCode AS WHCode
|
|
,ISNULL(A.PrimaryQTY,0)-isnull(SUM (B.[StoreQty]+B.[ToRetStQty]),0) AS Num
|
|
,A.BatchCode AS BatchCode INTO #TempERP from #TempWMS A
|
|
LEFT JOIN {2}.[dbo].InvTrans_WhQoh B ON B.Wh=A.WHID
|
|
AND B.ItemInfo_ItemID=A.INVID AND ISNULL(B.LotInfo_LotCode,'')=A.BatchCode
|
|
GROUP BY A.WorkPoint,A.InvCode,A.WHCode,A.BatchCode,ISNULL(A.PrimaryQTY,0)
|
|
|
|
|
|
select distinct WorkPoint as Costre,WorkPoint FROM #TempERP
|
|
select WorkPoint as Costre, InvCode,WHCode,Num as Quantity ,'0' as Amount ,BatchCode FROM #TempERP where Num<>0
|
|
DROP TABLE #TempWMS
|
|
DROP TABLE #TempERP";
|
|
sql = string.Format(sql, CheckCodeList, WorkPoint, ERPDB);
|
|
|
|
|
|
DataSet DSet = SqlCommandHelper.SQlReturnDataSet(sql, cmd);
|
|
|
|
string Inputstr = SqlHelper.DataSetToJson(DSet, "details", "Costre");
|
|
string APIURL = ConfigurationManager.ConnectionStrings["ERPAPIURL"].ConnectionString + "CheckDiffDoc/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("ERP接口调用失败:" + MessAge);
|
|
}
|
|
else
|
|
{
|
|
try
|
|
{
|
|
JArray res = (JArray)JsonConvert.DeserializeObject(Obj["Data"].ToString());
|
|
foreach (var item in res)
|
|
{
|
|
JObject jo = (JObject)item;
|
|
ERPSql = @"update ICSCheck set Status='3' ,EATTRIBUTE1='" + jo["ID"].ToString() + "' where CheckCode in (" + CheckCodeList + ") ";
|
|
ERPSql += @" update ICSCheckIntermediateTable set IsCheckDetail='3' where CheckNo in (" + CheckCodeList + ") ";
|
|
ERPSql += @" update ICSCheckIntermediate set Status='3' where CheckCode in (" + CheckCodeList + ") ";
|
|
}
|
|
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 ApproveWMSCheckResult(string keyValue)
|
|
{
|
|
string msg = "";
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
try
|
|
{
|
|
string Identification = Guid.NewGuid().ToString();
|
|
string CheckCodeList = "";
|
|
foreach (string CheckCode in keyValue.TrimEnd(',').Split(','))
|
|
{
|
|
if (!CheckCodeList.Contains(CheckCode))
|
|
{
|
|
CheckCodeList += CheckCode + ",";
|
|
}
|
|
}
|
|
CheckCodeList = CheckCodeList.TrimEnd(',');
|
|
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
|
|
{
|
|
int result = 0;
|
|
bool isSuccess = true;
|
|
///添加日志
|
|
string sql = @" IF NOT EXISTS(SELECT F_Account FROM Sys_SRM_User WHERE F_Account='{2}' AND F_Location='{1}')
|
|
BEGIN
|
|
RAISERROR('操作人：{2} 不存在！',16,1);
|
|
RETURN
|
|
END
|
|
|
|
select CheckNo, LotNo, Quantity, PrimaryQuantity,WorkPoint into #tempCheck from
|
|
(SELECT CheckNo, LotNo, Quantity, PrimaryQuantity,WorkPoint
|
|
FROM
|
|
dbo.ICSCheckIntermediateTable
|
|
where IsCheckDetail='1'
|
|
AND Quantity!=PrimaryQuantity
|
|
AND CheckNo IN ({0}) AND WorkPoint='{1}'
|
|
UNION ALL
|
|
SELECT C.CheckCode AS CheckNo,A.LotNo AS LotNo,A.Quantity AS Quantity
|
|
,A.PrimaryQuantity AS PrimaryQuantity,A.WorkPoint AS WorkPoint FROM ICSCheckDetail A
|
|
LEFT JOIN ICSCheckIntermediateTable B
|
|
ON B.LotNo=A.LotNo AND B.CheckID=A.CheckID AND B.WorkPoint=A.WorkPoint
|
|
LEFT JOIN ICSCheck C ON C.ID=A.CheckID AND C.WorkPoint=A.WorkPoint
|
|
WHERE B.LotNo IS NULL AND A.Quantity!=A.PrimaryQuantity
|
|
AND C.CheckCode IN ({0}) AND A.WorkPoint='{1}'
|
|
) A
|
|
|
|
INSERT INTO ICSWareHouseLotInfoLog(ID,Identification,TransCode,TransSequence,LotNo,InvCode,
|
|
FromWarehouseCode,FromLocationCode,ToWarehouseCode,ToLocationCode,Quantity,
|
|
Memo,Lock,TransType,BusinessCode,ERPUpload,ERPID,
|
|
ERPDetailID,ERPCode,ERPSequence,MUSER,MUSERName,
|
|
MTIME,WorkPoint,EATTRIBUTE1)
|
|
SELECT NEWID(),'{3}',a.CheckNo,'',a.LotNo ,b.InvCode ,
|
|
b.WarehouseCode,b.LocationCode,'','',a.Quantity,
|
|
'初盘修正库存','0','8','40','0','',
|
|
'','','',f.F_Account ,f.F_RealName ,
|
|
SYSDATETIME() ,a.WorkPoint ,''
|
|
FROM #tempCheck a
|
|
INNER JOIN ICSWareHouseLotInfo b on b.LotNo=a.LotNo and b.WorkPoint=a.WorkPoint
|
|
INNER JOIN Sys_SRM_User f ON f.F_Account='{2}' AND a.WorkPoint=f.F_Location
|
|
|
|
INSERT INTO ICSWareHouseLotInfoLog(ID,Identification,TransCode,TransSequence,LotNo,InvCode,
|
|
FromWarehouseCode,FromLocationCode,ToWarehouseCode,ToLocationCode,Quantity,
|
|
Memo,Lock,TransType,BusinessCode,ERPUpload,ERPID,
|
|
ERPDetailID,ERPCode,ERPSequence,MUSER,MUSERName,
|
|
MTIME,WorkPoint,EATTRIBUTE1)
|
|
SELECT NEWID(),'{3}',a.CheckNo,'',a.LotNo ,b.InvCode ,
|
|
b.WarehouseCode,b.LocationCode,'','',a.PrimaryQuantity,
|
|
'初盘修正库存','0','8','41','0','',
|
|
'','','',f.F_Account ,f.F_RealName ,
|
|
SYSDATETIME() ,a.WorkPoint ,''
|
|
FROM #tempCheck a
|
|
INNER JOIN ICSWareHouseLotInfo b on b.LotNo=a.LotNo and b.WorkPoint=a.WorkPoint
|
|
INNER JOIN Sys_SRM_User f ON f.F_Account='{2}' AND a.WorkPoint=f.F_Location
|
|
|
|
drop table #tempCheck ";
|
|
sql = string.Format(sql, CheckCodeList, WorkPoint, MUSER, Identification);
|
|
cmd.CommandText = sql;
|
|
result = cmd.ExecuteNonQuery();
|
|
if (isSuccess && result <= 0)
|
|
{
|
|
throw new Exception("库存日志写入失败！");
|
|
|
|
}
|
|
///更新库存
|
|
sql = @"select CheckNo, LotNo, Quantity, PrimaryQuantity,WorkPoint into #tempCheck from
|
|
(SELECT CheckNo, LotNo, Quantity, PrimaryQuantity,WorkPoint
|
|
FROM
|
|
dbo.ICSCheckIntermediateTable
|
|
where IsCheckDetail='1'
|
|
AND Quantity!=PrimaryQuantity
|
|
AND CheckNo IN ({0}) AND WorkPoint='{1}'
|
|
UNION ALL
|
|
SELECT C.CheckCode AS CheckNo,A.LotNo AS LotNo,A.Quantity AS Quantity
|
|
,A.PrimaryQuantity AS PrimaryQuantity,A.WorkPoint AS WorkPoint FROM ICSCheckDetail A
|
|
LEFT JOIN ICSCheckIntermediateTable B
|
|
ON B.LotNo=A.LotNo AND B.CheckID=A.CheckID AND B.WorkPoint=A.WorkPoint
|
|
LEFT JOIN ICSCheck C ON C.ID=A.CheckID AND C.WorkPoint=A.WorkPoint
|
|
WHERE B.LotNo IS NULL AND A.Quantity!=A.PrimaryQuantity
|
|
AND C.CheckCode IN ({0}) AND A.WorkPoint='{1}'
|
|
) A
|
|
|
|
UPDATE A SET A.Quantity=B.PrimaryQuantity
|
|
FROM ICSWareHouseLotInfo A
|
|
INNER JOIN #tempCheck B ON B.LotNo=A.LotNo AND B.WorkPoint=A.WorkPoint
|
|
|
|
UPDATE ICSCheckIntermediateTable SET IsCheckDetail='2'
|
|
WHERE CheckNo IN ({0}) AND WorkPoint='{1}'
|
|
|
|
UPDATE ICSCheckIntermediate SET Status='2'
|
|
WHERE CheckCode IN ({0}) AND WorkPoint='{1}'
|
|
|
|
UPDATE ICSCheck SET Status='2'
|
|
WHERE CheckCode IN ({0}) AND WorkPoint='{1}'
|
|
|
|
drop table #tempCheck";
|
|
sql = string.Format(sql, CheckCodeList, WorkPoint);
|
|
cmd.CommandText = sql;
|
|
result = cmd.ExecuteNonQuery();
|
|
if (isSuccess && result <= 0)
|
|
{
|
|
throw new Exception("库存表更新失败！");
|
|
|
|
}
|
|
|
|
cmd.Transaction.Commit();
|
|
}
|
|
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;
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// 派纳盘点单生成条码获取盘点单信息
|
|
/// </summary>
|
|
/// <param name="CheckCode"></param>
|
|
/// <param name="WorkPoint"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetCheckDocInfo(string CheckCode, string WorkPoint)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
object Figure = GetDecimalDigits();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
|
|
string sql = $@" select TOP 1 C.WarehouseCode AS WHCode,A.WorkPoint AS WorkPoint
|
|
,'' AS ProjectCode,'' AS BatchCode,'' AS Version,'' AS Brand,'' AS cFree1,'' AS cFree2,'' AS cFree3,'' AS cFree4,'' AS cFree5,'' AS cFree6,'' AS cFree7,'' AS cFree8,'' AS cFree9,'' AS cFree10
|
|
,'' EATTRIBUTE2 ,'' EATTRIBUTE3 ,'' EATTRIBUTE4 ,'' EATTRIBUTE5,'' EATTRIBUTE6 ,'' EATTRIBUTE7 ,'' EATTRIBUTE8 ,'' EATTRIBUTE9 ,'' EATTRIBUTE10
|
|
from ICSCheck A
|
|
LEFT JOIN ICSCheckDetail B ON B.CheckID=A.ID AND B.WorkPoint=A.WorkPoint
|
|
LEFT JOIN ICSWareHouseLotInfo C ON C.LotNo=B.LotNo AND C.WorkPoint=B.WorkPoint
|
|
where A.CheckCode='{CheckCode}' and A.WorkPoint='{WorkPoint}'";
|
|
sql = string.Format(sql, Figure);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
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 ,InvStd ,InvUnit ,ClassCode ,ClassName
|
|
,isnull(EffectiveEnable, 0 ) AS EffectiveEnable
|
|
,CAST ( isnull(EffectiveDays, 0 ) AS DECIMAL ( 38,6 ) ) AS EffectiveDays
|
|
from ICSInventory
|
|
where WorkPoint = '" + WorkPoint + "'" + wherestr;
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
|
|
}
|
|
|
|
public DataTable GetLocation(string WHCode, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
DataTable table = new DataTable();
|
|
string sql = @"select A.ID,B.WarehouseCode,B.WarehouseName,A.LocationCode,A.LocationName
|
|
from ICSLocation A
|
|
LEFT JOIN ICSWarehouse B ON B.ID=A.WHID AND B.WorkPoint=A.WorkPoint
|
|
WHERE B.WarehouseCode='" + WHCode + "' AND B.WorkPoint='" + WorkPoint + "'";
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// 派纳 盘点单生成条码 保存（生成条码的同时入库并写入对应盘点单）
|
|
/// </summary>
|
|
/// <param name="TransferNO"></param>
|
|
/// <param name="keyValue"></param>
|
|
/// <param name="WorkPoint"></param>
|
|
/// <returns></returns>
|
|
public int CreateLotNoForPD(string CheckCode, string keyValue, string WorkPoint)
|
|
{
|
|
try
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string WorkPoints = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
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 Pre = CheckCode;
|
|
string sql = string.Empty;
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string PRODUCTDATE = queryParam["PRODUCTDATE"].ToString();
|
|
string Identification = Guid.NewGuid().ToString();
|
|
string sqls = string.Empty;
|
|
string Colspan = "";
|
|
string str1 = "";
|
|
List<string> ExtensionIDList = new List<string>();
|
|
|
|
#region 装箱的功能
|
|
int createPackCount = Convert.ToInt32(queryParam["createPackCount"].ToString());//包装箱数 为0 则不生成
|
|
string VenCode = queryParam["VenCode"].ToString();
|
|
|
|
|
|
int PackCount = 0;//每箱放的数量
|
|
int Pack_YuShu = 0;//需要平摊的数量
|
|
string car = "CR" + DateTime.Now.ToString("yyyy") + DateTime.Now.ToString("MM");//箱号代码的前缀
|
|
|
|
List<PackModel> List_Pack = new List<PackModel>();
|
|
int ZQty = 0;
|
|
if (createPackCount > 0)
|
|
{
|
|
Pack_YuShu = (int)(createPageCount % createPackCount); ;//最后一箱放的数量
|
|
if (Pack_YuShu == 0)
|
|
{
|
|
PackCount = (createPageCount / createPackCount);
|
|
}
|
|
else
|
|
{
|
|
PackCount = ((createPageCount - Pack_YuShu) / (createPackCount));
|
|
}
|
|
|
|
for (int jj = 0; jj < createPackCount; jj++)
|
|
{
|
|
object CARID = Guid.NewGuid();
|
|
string Carton = GetSerialCode(WorkPoint, "ICSContainer", "ContainerCode", car, 5);
|
|
sql += @" INSERT INTO dbo.ICSContainer
|
|
( ID,ContainerCode ,
|
|
MUSER ,MUSERName ,MTIME,WorkPoint,EATTRIBUTE8,EATTRIBUTE3,ContainerID,ContainerName,
|
|
InventoryMixed,ProjectMixed,BatchMixed,Multiplex)
|
|
VALUES ( NEWID(),'" + Carton + "','" + MUSER + "','" + MUSERNAME + "',GETDATE(),'" + WorkPoint +
|
|
"','" + VenCode + "','WMS','" + CARID + "','" + Carton + "',1,1,1,1)";
|
|
PackModel PP = new PackModel();
|
|
PP.ID = CARID.ToString();
|
|
PP.PackCode = Carton;
|
|
if (jj < Pack_YuShu)
|
|
{
|
|
PP.FristBarIndex = ZQty;
|
|
PP.LastBarIndex = ZQty + Convert.ToInt32(Convert.ToDouble(PackCount));
|
|
|
|
}
|
|
else
|
|
{
|
|
PP.FristBarIndex = ZQty;
|
|
PP.LastBarIndex = ZQty + Convert.ToInt32(Convert.ToDouble(PackCount)) - 1;
|
|
}
|
|
ZQty += (PP.LastBarIndex - PP.FristBarIndex) + 1;
|
|
List_Pack.Add(PP);
|
|
}
|
|
}
|
|
|
|
#endregion
|
|
|
|
for (int i = 0; i < createPageCount; i++)
|
|
{
|
|
if (i + 1 == createPageCount)
|
|
{
|
|
if (minPackQty * createPageCount > thisCreateQty)
|
|
{
|
|
LOTQTY = thisCreateQty - (minPackQty * (createPageCount - 1));
|
|
}
|
|
}
|
|
|
|
|
|
Colspan = queryParam["ProjectCode"].ToString() + "~" + queryParam["BatchCode"].ToString() + "~" + queryParam["Version"].ToString() + "~" + queryParam["Brand"].ToString() + "~" + queryParam["cFree1"].ToString() + "~" + queryParam["cFree2"].ToString() + "~" + queryParam["cFree3"].ToString() + "~" + queryParam["cFree4"].ToString() + "~" + queryParam["cFree5"].ToString() + "~" + queryParam["cFree6"].ToString() + "~" + queryParam["cFree7"].ToString() + "~" + queryParam["cFree8"].ToString() + "~" + queryParam["cFree9"].ToString() + "~" + queryParam["cFree10"].ToString();
|
|
sqls = string.Format(@"select ID from ICSExtension where Colspan='{0}' and WorkPoint='{1}'", Colspan, WorkPoint);
|
|
object ExtensionID = SqlHelper.ExecuteScalar(sqls);
|
|
bool flag = true;
|
|
|
|
foreach (var item in ExtensionIDList)
|
|
{
|
|
if (item == Colspan + WorkPoint)
|
|
{
|
|
flag = false;
|
|
}
|
|
}
|
|
if (ExtensionID == null && flag == true)
|
|
{
|
|
str1 = Guid.NewGuid().ToString();
|
|
sql += string.Format(@"Insert into ICSExtension(ID, Colspan, ProjectCode, BatchCode, Version, Brand, cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, MTIME, MUSER, MUSERName, WorkPoint)
|
|
Values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}',getdate(),'{16}','{17}','{18}' )",
|
|
str1, Colspan, queryParam["ProjectCode"].ToString(), queryParam["BatchCode"].ToString(), queryParam["Version"].ToString(), queryParam["Brand"].ToString(), queryParam["cFree1"].ToString(), queryParam["cFree2"].ToString(), queryParam["cFree3"].ToString(), queryParam["cFree4"].ToString(), queryParam["cFree5"].ToString(), queryParam["cFree6"].ToString(), queryParam["cFree7"].ToString(), queryParam["cFree8"].ToString(), queryParam["cFree9"].ToString(), queryParam["cFree10"].ToString(),
|
|
MUSER, MUSERNAME, WorkPoints);
|
|
}
|
|
else if (ExtensionID != null)
|
|
{
|
|
str1 = ExtensionID.ToString();
|
|
}
|
|
ExtensionIDList.Add(Colspan + WorkPoint);
|
|
|
|
string LotNo = GetSerialCode(WorkPoint, "ICSInventoryLot", "LotNO", Pre, 5);
|
|
sql += string.Format(@"Insert into ICSInventoryLotDetail(LotNo, TransCode,TransSequence,MUSER,MUSERName,MTIME,WorkPoint)
|
|
Values('{0}','{1}','{2}','{3}','{4}',getdate(),'{5}' )",
|
|
LotNo, CheckCode, 1, MUSER, MUSERNAME, WorkPoints);
|
|
|
|
sql += string.Format(@"
|
|
insert into ICSInventoryLot
|
|
(ID,LotNo,InvCode,ProductDate,ExpirationDate,Quantity,ExtensionID,Type,MUSER,MUSERName,MTIME,WorkPoint,Amount ,EATTRIBUTE1,EATTRIBUTE2,EATTRIBUTE3,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7,EATTRIBUTE8,EATTRIBUTE9,EATTRIBUTE10)
|
|
values
|
|
(NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}',204,'{6}','{7}',GETDATE(),'{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}')",
|
|
LotNo, queryParam["InvCode"].ToString(), PRODUCTDATE, queryParam["ExpirationDate"].ToString(), LOTQTY, str1, MUSER, MUSERNAME, WorkPoints, Convert.ToDecimal(queryParam["Amount"].ToString()) / minPackQty * LOTQTY
|
|
, 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());
|
|
//派纳盘点单生成的条码需要自动入库
|
|
sql += string.Format(@"
|
|
INSERT INTO ICSWareHouseLotInfo
|
|
(ID,LotNO,WarehouseCode,LocationCode,InvCode,Quantity,InDate,LockQuantity,MUSER,MUSERName,MTIME,WorkPoint)
|
|
values
|
|
(NEWID(),'{0}','{1}','{2}','{3}',{4},SYSDATETIME(),0,'{5}','{6}',SYSDATETIME(),'{7}')",
|
|
LotNo, queryParam["WHCode"].ToString(), queryParam["LocationCode"].ToString(), queryParam["InvCode"].ToString(), LOTQTY, MUSER, MUSERNAME, WorkPoint);
|
|
|
|
//盘点生成的条码需要写入盘点表中
|
|
|
|
sql += string.Format(@"
|
|
SELECT E.ID,B.TransCode AS CheckCode,A.LotNo,D.WarehouseCode,D.LocationCode
|
|
,A.InvCode,C.BatchCode,D.Quantity,A.WorkPoint
|
|
INTO #TEMPLot{0}
|
|
FROM ICSInventoryLot A
|
|
LEFT JOIN ICSInventoryLotDetail B ON B.LotNo=A.LotNo AND B.WorkPoint=A.WorkPoint
|
|
LEFT JOIN ICSExtension C ON C.ID=A.ExtensionID AND C.WorkPoint=A.WorkPoint
|
|
LEFT JOIN ICSWareHouseLotInfo D ON D.LotNo=A.LotNo AND D.WorkPoint=A.WorkPoint
|
|
LEFT JOIN ICSCheck E ON E.CheckCode=B.TransCode AND E.WorkPoint=B.WorkPoint
|
|
WHERE A.LotNo='{0}' AND A.WorkPoint='{1}'
|
|
|
|
UPDATE A SET A.Quantity+=B.Quantity
|
|
FROM ICSCheck A
|
|
INNER JOIN #TEMPLot{0} B ON B.CheckCode=A.CheckCode AND B.WorkPoint=A.WorkPoint
|
|
|
|
Insert into ICSCheckDetail
|
|
(ID,CheckID,LotNo,LocationCode,Quantity,Amount,PrimaryLocationCode,PrimaryQuantity,PrimaryAmount,ReplayLocationCode,ReplayQuantity
|
|
,ReplayAmount,CheckLocationCode,CheckQuantity,CheckAmount,MUSER,MUSERName,MTIME,WorkPoint,EATTRIBUTE1)
|
|
SELECT NEWID(),ID,LotNo,LocationCode,Quantity,0,LocationCode,Quantity,0,'',0,0,'',0,0,'{2}','{3}'
|
|
,GETDATE(),WorkPoint,1
|
|
FROM #TEMPLot{0}
|
|
|
|
IF Exists(SELECT A.CheckCode FROM ICSCheckIntermediate A
|
|
INNER JOIN #TEMPLot{0} B ON B.CheckCode=A.CheckCode AND B.InvCode=A.InvCode
|
|
AND B.BatchCode=A.BatchCode AND B.WorkPoint=A.WorkPoint)
|
|
BEGIN
|
|
update A SET A.InvQTY+=B.Quantity,A.PrimaryQTY+=B.Quantity
|
|
FROM ICSCheckIntermediate A
|
|
INNER JOIN #TEMPLot{0} B ON B.CheckCode=A.CheckCode AND B.InvCode=A.InvCode
|
|
AND B.BatchCode=A.BatchCode AND B.WorkPoint=A.WorkPoint
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
INSERT INTO ICSCheckIntermediate
|
|
(ID,CheckCode,Status,InvCode,WHCode,BatchCode,InvQTY,PrimaryQTY,ReplayQTY,CheckQTY,MUSER,MUSERName,MTIME,WorkPoint)
|
|
SELECT NEWID(),CheckCode,2,InvCode,WarehouseCode,BatchCode,Quantity,Quantity,0,0,'{2}','{3}'
|
|
,GETDATE(),WorkPoint
|
|
FROM #TEMPLot{0}
|
|
END
|
|
|
|
insert into ICSCheckIntermediateTable
|
|
(ID,CheckID,CheckNo,LotNo,LocationCode,Quantity,Amount,PrimaryLocationCode,PrimaryQuantity,PrimaryAmount,ReplayLocationCode,ReplayQuantity,ReplayAmount
|
|
,CheckLocationCode,CheckQuantity,CheckAmount,CheckType,IsCheckDetail,IsWHUpdate,MUSER,MUSERName,MTIME,WorkPoint)
|
|
SELECT NEWID(),ID,CheckCode,LotNo,LocationCode,Quantity,0,LocationCode,Quantity,0,'',0,0
|
|
,'',0,0,'初盘',1,1,'{2}','{3}',GETDATE(),WorkPoint FROM #TEMPLot{0}
|
|
|
|
drop table #TEMPLot{0}",
|
|
LotNo, WorkPoint, MUSER, MUSERNAME);
|
|
|
|
sql += string.Format(@"
|
|
INSERT INTO ICSWareHouseLotInfoLog(ID,Identification,TransCode,TransSequence,LotNo,InvCode,
|
|
FromWarehouseCode,FromLocationCode,ToWarehouseCode,ToLocationCode,Quantity,
|
|
Memo,Lock,TransType,BusinessCode,ERPUpload,ERPID,
|
|
ERPDetailID,ERPCode,ERPSequence,MUSER,MUSERName,
|
|
MTIME,WorkPoint)
|
|
SELECT NEWID(),'{2}',b.TransCode,b.TransSequence,a.LotNo ,a.InvCode ,
|
|
'','',c.WarehouseCode,c.LocationCode,c.Quantity,
|
|
'','0','8','76','1',D.ID,
|
|
D.ID,D.CheckCode,1,'{3}' ,'{4}' ,
|
|
SYSDATETIME() ,a.WorkPoint
|
|
FROM ICSInventoryLot a
|
|
INNER JOIN ICSInventoryLotDetail b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint
|
|
INNER JOIN ICSWareHouseLotInfo c ON a.LotNo=c.LotNo AND a.WorkPoint=c.WorkPoint
|
|
INNER JOIN ICSCheck D ON D.CheckCode=B.TransCode AND D.WorkPoint=B.WorkPoint
|
|
WHERE a.LotNo='{0}' AND a.WorkPoint='{1}'",
|
|
LotNo, WorkPoint, Identification, MUSER, MUSERNAME);
|
|
sql += "\r\n";
|
|
|
|
//装箱的功能
|
|
if (List_Pack.Count > 0)
|
|
{
|
|
PackModel mm = new PackModel();
|
|
mm = List_Pack.Where(a => a.FristBarIndex <= i && a.LastBarIndex >= i).First();
|
|
sql += string.Format(@" INSERT INTO dbo.ICSContainerLot
|
|
( ID ,ContainerID ,LotNo , MUSER ,MUSERName ,MTIME ,WorkPoint )
|
|
Values( newid(),'{0}','{1}','{2}','{3}',getdate(),'{4}' ) ", mm.ID, LotNo, MUSER, MUSERNAME, WorkPoint);
|
|
}
|
|
|
|
}
|
|
int count = SqlHelper.CmdExecuteNonQueryLi(sql);
|
|
return count;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 派纳盘点删除条码
|
|
/// </summary>
|
|
/// <param name="keyValue"></param>
|
|
/// <returns></returns>
|
|
public string DeleteItemLot(string keyValue)
|
|
{
|
|
try
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string msg = string.Empty;
|
|
string sql = string.Format(@"select Type from ICSInventoryLot
|
|
where LotNo in ({0}) and WorkPoint in ('{1}')", keyValue.TrimEnd(','), WorkPoint);
|
|
DataTable lotdt = SqlHelper.GetDataTableBySql(sql);
|
|
if (lotdt.Rows[0]["Type"].ToString() != "204")
|
|
{
|
|
msg = "所选条码中存在不是根据盘点单生成的条码,无法删除！";
|
|
}
|
|
else
|
|
{
|
|
if (string.IsNullOrEmpty(msg))
|
|
{
|
|
string errorlot = "";
|
|
string chksql = @"select LotNo from ICSWareHouseLotInfoLog
|
|
where LotNo in ({0}) and WorkPoint='{1}' and BusinessCode!='76'";
|
|
chksql = string.Format(chksql, keyValue.TrimEnd(','), WorkPoint);
|
|
DataTable chkdt = SqlHelper.GetDataTableBySql(chksql);
|
|
foreach (DataRow dr in chkdt.Rows)
|
|
{
|
|
errorlot += dr["LotNo"].ToString() + ",";
|
|
}
|
|
if (errorlot != "")
|
|
{
|
|
msg = "条码:" + errorlot.TrimEnd(',') + "生成后已进行过其他操作,无法删除！";
|
|
return msg;
|
|
}
|
|
|
|
string sqls = string.Format(@"
|
|
select B.InvCode,D.WarehouseCode,C.BatchCode,A.Quantity,A.PrimaryQuantity,A.ReplayQuantity,A.CheckQuantity
|
|
, A.CheckNo, A.WorkPoint
|
|
INTO #tempLot
|
|
from ICSCheckIntermediateTable A
|
|
INNER JOIN ICSInventoryLot B ON B.LotNo = A.LotNo AND B.WorkPoint = A.WorkPoint
|
|
INNER JOIN ICSExtension C ON C.ID = B.ExtensionID AND C.WorkPoint = B.WorkPoint
|
|
INNER JOIN ICSWareHouseLotInfo D ON D.LotNo = A.LotNo AND D.WorkPoint = A.WorkPoint
|
|
where A.LotNo IN({0}) AND A.WorkPoint = '{1}'
|
|
|
|
UPDATE A SET A.InvQTY -= B.Quantity, A.PrimaryQTY -= B.PrimaryQuantity, A.ReplayQTY -= B.ReplayQuantity
|
|
, A.CheckQTY -= B.CheckQuantity
|
|
FROM ICSCheckIntermediate A
|
|
INNER JOIN #tempLot B ON B.CheckNo=A.CheckCode AND B.InvCode=A.InvCode AND B.WarehouseCode=A.WHCode
|
|
AND B.BatchCode = A.BatchCode AND B.WorkPoint = A.WorkPoint
|
|
|
|
DROP TABLE #tempLot", keyValue.TrimEnd(','), WorkPoint);
|
|
|
|
sqls += string.Format(@"
|
|
DELETE FROM ICSCheckIntermediateTable WHERE LotNo IN ({0}) and WorkPoint='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
|
|
sqls += string.Format(@"
|
|
UPDATE A set A.Quantity-=B.Quantity
|
|
FROM ICSCheck A
|
|
INNER JOIN ICSCheckDetail B ON B.CheckID=A.ID AND B.WorkPoint=A.WorkPoint
|
|
WHERE B.LotNo IN ({0}) AND B.WorkPoint='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
|
|
sqls += string.Format(@"
|
|
DELETE FROM ICSCheckDetail WHERE LotNo IN ({0}) and WorkPoint='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
|
|
sqls += string.Format(@"
|
|
DELETE FROM ICSWareHouseLotInfo WHERE LotNo IN ({0}) and WorkPoint='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
|
|
sqls += string.Format(@"
|
|
DELETE FROM ICSWareHouseLotInfoLog WHERE LotNo IN ({0}) and WorkPoint='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
|
|
sqls += string.Format(@"
|
|
DELETE FROM ICSInventoryLotDetail WHERE LotNO IN (select LotNO from ICSInventoryLot where ID in ({0}) ) and WorkPoint in ('{1}')", keyValue.TrimEnd(','), WorkPoint);
|
|
|
|
sqls += string.Format(@"
|
|
DELETE FROM dbo.ICSContainerLot WHERE LotNO IN (select LotNO from ICSInventoryLot where ID in ({0}) ) and WorkPoint in ('{1}')", keyValue.TrimEnd(','), WorkPoint);//删除箱子 条码对应关系
|
|
|
|
sqls += string.Format(@"
|
|
DELETE FROM dbo.ICSInventoryLot WHERE ID IN ({0}) and WorkPoint in ('{1}')", keyValue.TrimEnd(','), WorkPoint);
|
|
SqlHelper.CmdExecuteNonQueryLi(sqls);
|
|
}
|
|
}
|
|
return msg;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return ex.Message;
|
|
}
|
|
}
|
|
|
|
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 Repository().FindTableBySql(sql.ToString());
|
|
return SqlHelper.ExecuteScalar(sql).ToString();
|
|
//return DbHelper.ExecuteScalar(CommandType.Text, sql).ToString();
|
|
}
|
|
}
|
|
}
|