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.
318 lines
14 KiB
318 lines
14 KiB
using NFine.Code;
|
|
using NFine.Data.Extensions;
|
|
using NFine.Domain._03_Entity.SRM;
|
|
using NFine.Repository;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Data.SqlClient;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
|
|
namespace NFine.Application.WMS
|
|
{
|
|
public class IntelligentDeviceIntegrationApp: RepositoryFactory<ICSVendor>
|
|
{
|
|
|
|
//获取仓库
|
|
public DataTable GetWarehouseID()
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string sql = @"
|
|
select '' as ID,'' as WarehouseName
|
|
union all
|
|
SELECT DISTINCT a.ID,a.WarehouseName FROM dbo.ICSWarehouse a where 1=1 and Enable='1' ";
|
|
string role = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode;
|
|
|
|
sql += " and WorkPoint in('" + WorkPoint + "')";
|
|
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
//根据仓库获取区域
|
|
public DataTable GetRegion(string WHID)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string sql = @"
|
|
select '' as Region,'' as RegionName
|
|
union all
|
|
select distinct Region ,Region+'区' as RegionName from dbo.ICSLocation where 1=1 and WHID='" + WHID + "'";
|
|
string role = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode;
|
|
|
|
sql += " and WorkPoint in('" + WorkPoint + "')";
|
|
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
|
|
|
|
public string OperateICSCoordinate(string keyValue)
|
|
{
|
|
string msg = "";
|
|
try
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
|
|
string sql = string.Empty;
|
|
sql = @"IF NOT EXISTS(SELECT ID FROM dbo.ICSCoordinate WHERE ID='{8}')
|
|
BEGIN
|
|
INSERT INTO dbo.ICSCoordinate
|
|
(
|
|
ID, WHID, Region, X, Y, Z,MUSER,MUSERName,MTIME,WorkPoint
|
|
)
|
|
VALUES
|
|
(newid(),'{0}','{1}','{2}','{3}','{4}','{5}','{6}',getdate(),'{7}')
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE dbo.ICSCoordinate
|
|
SET X='{2}',Y='{3}',Z='{4}',MUSER='{5}',MUSERName='{6}',MTIME=getdate()
|
|
WHERE ID='{8}'
|
|
END
|
|
";
|
|
sql = string.Format(sql, queryParam["WHID"].ToString(), queryParam["Region"].ToString(), queryParam["X"].ToString(), queryParam["Y"].ToString(), queryParam["Z"].ToString(),MUSER, MUSERNAME, WorkPoint, queryParam["ID"].ToString());
|
|
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "操作失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
|
|
|
|
|
|
public DataTable GetInfo(string ID)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
|
|
string sql = @" select a.ID,a.LocationCode,a.LocationName,b.WarehouseCode,a.Region,c.X,c.Y,c.Z,c.Landmark ,isnull(d.X,0) as XMax ,isnull(d.Y,0) as YMax,isnull(d.Z,0) as ZMax,c.EATTRIBUTE1 from dbo.ICSLocation a
|
|
left join dbo.ICSWarehouse b on a.WHID=b.ID and a.WorkPoint=b.WorkPoint
|
|
left join ICSCoordinateLandmark c on a.ID=c.LocationID and a.WorkPoint=c.WorkPoint
|
|
left join ICSCoordinate d on a.WHID=d.WHID and a.Region=d.Region and a.WorkPoint=d.WorkPoint
|
|
where 1=1
|
|
and a.ID='" + ID + "' ";
|
|
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
|
|
|
|
|
|
public string OperateICSCoordinateLandmark(string keyValue)
|
|
{
|
|
string msg = "";
|
|
try
|
|
{
|
|
var queryParam = keyValue.ToJObject();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
|
|
string sql = string.Empty;
|
|
string SeachSql = @"select a.ID,a.LocationCode,a.LocationName,b.WarehouseCode,a.Region,c.X,c.Y,c.Z,c.Landmark ,isnull(d.X,0) as XMax ,isnull(d.Y,0) as YMax,isnull(d.Z,0) as ZMax,c.EATTRIBUTE1
|
|
from dbo.ICSLocation a
|
|
left join dbo.ICSWarehouse b on a.WHID=b.ID and a.WorkPoint=b.WorkPoint
|
|
left join ICSCoordinateLandmark c on a.ID=c.LocationID and a.WorkPoint=c.WorkPoint
|
|
left join ICSCoordinate d on a.WHID=d.WHID and a.Region=d.Region and a.WorkPoint=d.WorkPoint
|
|
where 1=1 and b.WarehouseCode='{0}' AND c.EATTRIBUTE1='{1}' and isnull(c.EATTRIBUTE1,'')<>'' and a.Region='{2}' ";
|
|
SeachSql = string.Format(SeachSql, queryParam["WHCode"].ToString(), queryParam["EATTRIBUTE1"].ToString(), queryParam["Region"].ToString());
|
|
DataTable dt = SqlHelper.GetDataTableBySql(SeachSql);
|
|
if (dt.Rows.Count>0)
|
|
{
|
|
msg = "操作失败,所输入的库位分配分配次序在仓库:"+ dt.Rows[0]["WarehouseCode"].ToString() + ",区域:"+ dt.Rows[0]["Region"].ToString() + ",中已存在,请重新输入!";
|
|
return msg;
|
|
}
|
|
sql = @"
|
|
IF NOT EXISTS(SELECT LocationID FROM dbo.ICSCoordinateLandmark WHERE LocationID='{0}')
|
|
BEGIN
|
|
INSERT INTO dbo.ICSCoordinateLandmark
|
|
(
|
|
ID, LocationID, X, Y, Z,Landmark,state,MUSER,MUSERName,MTIME,WorkPoint,EATTRIBUTE1
|
|
)
|
|
VALUES
|
|
(newid(),'{0}','{1}','{2}','{3}','{4}','0','{5}','{6}',getdate(),'{7}','{8}')
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE dbo.ICSCoordinateLandmark
|
|
SET X='{1}',Y='{2}',Z='{3}',Landmark='{4}',MUSER='{5}',MUSERName='{6}',MTIME=getdate(),EATTRIBUTE1='{8}'
|
|
WHERE LocationID='{0}'
|
|
END
|
|
";
|
|
sql = string.Format(sql, queryParam["LocationID"].ToString(), queryParam["X"].ToString(), queryParam["Y"].ToString(), queryParam["Z"].ToString(), queryParam["Landmark"].ToString(), MUSER, MUSERNAME, WorkPoint, queryParam["EATTRIBUTE1"].ToString() );
|
|
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
else
|
|
{
|
|
msg = "操作失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
|
|
public DataTable GetRegionRange(string WHID, string region)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
|
|
string sql = @"SELECT TOP 1 X, Y FROM dbo.ICSCoordinate
|
|
WHERE WHID = '" + WHID + "' AND Region = '" + region + "' ";
|
|
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
public DataTable GetLandmarkData(string WHID, string region)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
|
|
string sql = @"SELECT cl.X, cl.Y, cl.Landmark, cl.State, cl.LocationID, c.WHID, c.Region
|
|
FROM dbo.ICSCoordinateLandmark cl
|
|
JOIN dbo.ICSCoordinate c ON cl.X <= c.X AND cl.Y <= c.Y
|
|
WHERE WHID = '" + WHID + "' AND Region = '" + region + "' ";
|
|
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
public string SetData_PR(String savePath, string Type)
|
|
{
|
|
string msg = "";
|
|
//数据获取
|
|
try
|
|
{
|
|
int index = 1;
|
|
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
SqlConnection conn = SqlHelper.GetDataCenterConn();
|
|
string sql = "";
|
|
int count = 0;
|
|
DataTable data = FileToExcel.ExcelToTable(savePath);
|
|
|
|
List<Dictionary<string, object>> parametersList = new List<Dictionary<string, object>>();
|
|
string SeachSql = string.Format(@"select ID, LocationCode, LocationName, WHID, MUSER, MUSERName, MTIME, WorkPoint, Region, Row, GoodsShelf, Tier, Grid, EATTRIBUTE1, EATTRIBUTE2, EATTRIBUTE3, EATTRIBUTE4, EATTRIBUTE5 from ICSLocation where WorkPoint='{0}'", WorkPoint);
|
|
// SeachSql += string.Format(@"select ID, WarehouseCode, WarehouseName, Enable, BatchEnable, Asset, Proxy, InCost, MUSER, MUSERName, MTIME, WorkPoint, EATTRIBUTE1, EATTRIBUTE2, EATTRIBUTE3, EATTRIBUTE4, EATTRIBUTE5 from ICSWarehouse where WorkPoint='{0}'", WorkPoint);
|
|
SeachSql += string.Format(@" select a.ID,a.LocationCode,a.LocationName,b.WarehouseCode,b.ID as WHID, a.Region,c.X,c.Y,c.Z,c.Landmark ,isnull(d.X,0) as XMax ,isnull(d.Y,0) as YMax,isnull(d.Z,0) as ZMax,c.EATTRIBUTE1
|
|
from dbo.ICSLocation a
|
|
left join dbo.ICSWarehouse b on a.WHID=b.ID and a.WorkPoint=b.WorkPoint
|
|
left join ICSCoordinateLandmark c on a.ID=c.LocationID and a.WorkPoint=c.WorkPoint
|
|
left join ICSCoordinate d on a.WHID=d.WHID and a.Region=d.Region and a.WorkPoint=d.WorkPoint
|
|
where 1=1 and a.WorkPoint='{0}'", WorkPoint);
|
|
DataSet ds = SqlHelper.GetDataSetBySql(SeachSql);
|
|
|
|
foreach (DataRow dr in data.Rows)
|
|
{
|
|
string WHID = "";
|
|
string LocationID = "";
|
|
index++;
|
|
string LocationCode = dr["库位"].ToString();
|
|
string Region = dr["区域"].ToString();
|
|
string X = dr["地标X"].ToString();
|
|
string Y = dr["地标Y"].ToString();
|
|
//string Z = dr["地标Z"].ToString();
|
|
string Z = "";
|
|
string Landmark = dr["AGV地标码"].ToString();
|
|
string EATTRIBUTE1 = dr["库位分配分配次序"].ToString();
|
|
|
|
|
|
if (LocationCode == "" || LocationCode == null)
|
|
{
|
|
throw new Exception("第 " + index + " 行库位不能为空！");
|
|
}
|
|
if (Region == "" || Region == null)
|
|
{
|
|
throw new Exception("第 " + index + " 行区域不能为空！");
|
|
}
|
|
if (X == "" || X == null)
|
|
{
|
|
throw new Exception("第 " + index + " 行地标X不能为空！");
|
|
}
|
|
if (Y == "" || Y == null)
|
|
{
|
|
throw new Exception("第 " + index + " 行地标Y不能为空！");
|
|
}
|
|
if (Landmark == "" || Landmark == null)
|
|
{
|
|
throw new Exception("第 " + index + " 行AGV地标码不能为空！");
|
|
}
|
|
var itemdrs = ds.Tables[0].Select(string.Format("LocationCode='{0}'", LocationCode));
|
|
if (itemdrs == null || itemdrs.Length <= 0)
|
|
{
|
|
throw new Exception("第 " + index + " 行，库位编码:" + LocationCode + " 不存在！");
|
|
}
|
|
else
|
|
{
|
|
WHID = itemdrs[0]["WHID"].ToString();
|
|
LocationID = itemdrs[0]["ID"].ToString();
|
|
}
|
|
var itemdrs2 = ds.Tables[1].Select(string.Format("WHID='{0}' and Region='{1}' and EATTRIBUTE1='{2}' and isnull(EATTRIBUTE1,'')<>''", WHID, Region, EATTRIBUTE1));
|
|
if ( itemdrs2.Length > 0)
|
|
{
|
|
throw new Exception("第 " + index + " 行，库位分配分配次序:" + EATTRIBUTE1 + "在当前仓库区域已存在！");
|
|
}
|
|
|
|
sql += @" INSERT INTO dbo.ICSCoordinateLandmark
|
|
(
|
|
ID, LocationID, X, Y, Z,Landmark,state,MUSER,MUSERName,MTIME,WorkPoint,EATTRIBUTE1
|
|
)
|
|
VALUES
|
|
(newid(),'{0}','{1}','{2}','{3}','{4}','0','{5}','{6}',getdate(),'{7}','{8}')";
|
|
sql = string.Format(sql, LocationID, X,Y, Z, Landmark, MUSER, MUSERNAME, WorkPoint,EATTRIBUTE1);
|
|
}
|
|
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
msg = "";
|
|
}
|
|
else
|
|
{
|
|
|
|
msg = "删除失败!";
|
|
|
|
}
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
}
|
|
}
|