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.
620 lines
30 KiB
620 lines
30 KiB
using NFine.Data.Extensions;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using NFine.Code;
|
|
using NFine.Repository;
|
|
using System.Data.Common;
|
|
using NFine.Domain._03_Entity.SRM;
|
|
using ICS.Application.Entity;
|
|
using Newtonsoft.Json;
|
|
using System.Configuration;
|
|
using System.Data.SqlClient;
|
|
using ICS.Data;
|
|
using Newtonsoft.Json.Linq;
|
|
|
|
namespace NFine.Application.WMS
|
|
{
|
|
public class YLICSMoPickApp : RepositoryFactory<ICSVendor>
|
|
{
|
|
public static DataTable Invmes = new DataTable();
|
|
//工单备料(工单子件)
|
|
public DataTable GetGridJson(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 = "";
|
|
#region [SQL]
|
|
|
|
// sql = @"select a.ID,b.MOCode as Code,a.Sequence as ZSequence,b.Sequence as MSequence,b.Sequence + '-' + a.Sequence AS Sequence,a.EATTRIBUTE2,
|
|
//a.MTIME,a.MUSERName,a.MUSER,a.WHCode,c.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
|
|
// case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
|
|
// ,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
// ,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10,a.IssueQuantity as IssueQuantity
|
|
// from ICSMOPick a
|
|
// left
|
|
// join ICSMO b on a.MODetailID = b.MODetailID and a.WorkPoint = b.WorkPoint and b.MOStatus<>'3'
|
|
// left join Sys_SRM_Items g on g.F_EnCode='Reduction'
|
|
// left join ICSWarehouse c on a.WHCode = c.WarehouseCode and a.WorkPoint = c.WorkPoint
|
|
// inner join ICSInventory d on a.InvCode = d.InvCode and a.WorkPoint = d.WorkPoint
|
|
// left join ICSMOPickMerge e on a.ID = e.SourceID and a.WorkPoint = e.WorkPoint
|
|
// left join ICSExtension f on a.ExtensionID = f.ID and a.WorkPoint = f.WorkPoint
|
|
// where a.EATTRIBUTE1 <> '1'
|
|
|
|
// and SUBSTRING(a.InvCode, 1, CONVERT(int,g.F_Description)) NOT IN (select b.F_ItemCode from Sys_SRM_Items a left join Sys_SRM_ItemsDetail b on a.F_Id=b.F_ItemId
|
|
|
|
//where a.F_EnCode='Reduction')";
|
|
|
|
sql = @"select a.ID,b.MOCode as Code,a.Sequence as ZSequence,b.Sequence as MSequence,b.Sequence + '-' + a.Sequence AS Sequence,a.EATTRIBUTE2,
|
|
a.MTIME,a.MUSERName,a.MUSER,a.WHCode,c.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
|
|
case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
|
|
,f.Colspan ,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10,a.IssueQuantity as IssueQuantity
|
|
from ICSMOPick a
|
|
left
|
|
join ICSMO b on a.MODetailID = b.MODetailID and a.WorkPoint = b.WorkPoint and b.MOStatus<>'3'
|
|
left join Sys_SRM_Items g on g.F_EnCode='Reduction'
|
|
left join ICSWarehouse c on a.WHCode = c.WarehouseCode and a.WorkPoint = c.WorkPoint
|
|
inner join ICSInventory d on a.InvCode = d.InvCode and a.WorkPoint = d.WorkPoint
|
|
left join ICSMOPickMerge e on a.ID = e.SourceID and a.WorkPoint = e.WorkPoint
|
|
left join ICSExtension f on a.ExtensionID = f.ID and a.WorkPoint = f.WorkPoint
|
|
where a.EATTRIBUTE1 <> '1'
|
|
|
|
";
|
|
|
|
sql = string.Format(sql, WorkPoint);
|
|
sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
|
|
#endregion
|
|
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
|
|
{
|
|
sql += " and b.MOCode = '" + queryParam["POCode"].ToString() + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
|
|
{
|
|
string[] values = queryParam["InvCode"].ToString().Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
|
|
string InvCode = "'" + string.Join("','", values) + "'";
|
|
sql += " and a.InvCode in (" + InvCode + ") ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["Mechanism"].ToString()))
|
|
{
|
|
sql += " and a.EATTRIBUTE2 = '" + queryParam["Mechanism"].ToString() + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
|
|
{
|
|
sql += " and convert(nvarchar(20),b.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
|
|
{
|
|
sql += " and convert(nvarchar(20),b.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
|
|
}
|
|
}
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
|
|
|
|
public DataTable GetICSMOPickMergeTemp(string ID, string Type)
|
|
{
|
|
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
if (ID!="")
|
|
{
|
|
//ID = ID.Replace("'", "''");
|
|
ID = ID.TrimEnd(',');
|
|
// ID = string.IsNullOrWhiteSpace(ID) ? "''" : ID.TrimEnd(',');
|
|
}
|
|
//string Code = SqlHelper.GetItemsDetailEnabledMark("MtimeControl");
|
|
|
|
string sql = @" --查询单据信息
|
|
SELECT ROW_NUMBER() OVER (ORDER BY mo.MOCode,a.EATTRIBUTE2,mo.Sequence+'~'+a.Sequence) AS rowNo,
|
|
mo.MOCode AS Code,
|
|
mo.Sequence+'~'+a.Sequence AS Sequence,
|
|
a.WhCode AS WarehouseCode,
|
|
ISNULL(a.Quantity, 0) - ISNULL(a.IssueQuantity, 0) AS iQuantity,
|
|
a.InvCode,
|
|
b.InvName,
|
|
b.InvStd,
|
|
b.InvUnit,
|
|
a.ExtensionID,
|
|
ISNULL(c.ProjectCode, '') AS ProjectCode,
|
|
ISNULL(c.BatchCode, '') AS BatchCode,
|
|
ISNULL(c.Version, '') AS Version,
|
|
ISNULL(c.Brand, '') AS Brand,
|
|
ISNULL(c.cFree1, '') AS cFree1,
|
|
ISNULL(c.cFree2, '') AS cFree2,
|
|
ISNULL(c.cFree3, '') AS cFree3,
|
|
ISNULL(c.cFree4, '') AS cFree4,
|
|
ISNULL(c.cFree5, '') AS cFree5,
|
|
ISNULL(c.cFree6, '') AS cFree6,
|
|
ISNULL(c.cFree7, '') AS cFree7,
|
|
ISNULL(c.cFree8, '') AS cFree8,
|
|
ISNULL(c.cFree9, '') AS cFree9,
|
|
ISNULL(c.cFree10, '') AS cFree10,
|
|
CAST(NULL AS nvarchar(100)) AS LocationCode,
|
|
CAST(NULL AS nvarchar(100)) AS LotNO,
|
|
CAST(NULL AS DECIMAL(18,6)) AS QTY,
|
|
CAST(NULL AS DECIMAL(18,6)) AS QTYTotal,
|
|
CAST(NULL AS DECIMAL(18,6)) AS ReserveQuantity,
|
|
CAST(NULL AS datetime2) AS MTIME,
|
|
b.EATTRIBUTE2 AS U8InvCode,a.EATTRIBUTE2, getdate() as NowMTIME
|
|
INTO #TempVouchs
|
|
FROM ICSMOPick a
|
|
INNER JOIN ICSMO mo ON a.MODetailID=mo.MODetailID AND a.WorkPoint=mo.WorkPoint AND mo.MOStatus<>'3'
|
|
LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint = b.WorkPoint
|
|
LEFT JOIN ICSExtension c ON a.ExtensionID = c.ID AND a.WorkPoint = c.WorkPoint
|
|
WHERE a.WorkPoint = '{1}' AND mo.MOCode+mo.Sequence+'-'+a.Sequence IN ({0}) AND ISNULL(a.Quantity, 0) > ISNULL(a.IssueQuantity, 0) AND a.EATTRIBUTE1<>'1'
|
|
|
|
DECLARE @VouchsTotal INT=@@rowcount, --单据总行数
|
|
@VouchsRowCurrent INT=1 --当前单据行
|
|
|
|
--查询库存信息
|
|
SELECT
|
|
a.WarehouseCode,
|
|
a.Quantity - a.LockQuantity AS LotQuantity,
|
|
a.INVCode,
|
|
b.LotNo,
|
|
a.InDate AS MTIME,
|
|
a.inDate,
|
|
a.LocationCode,
|
|
b.ExtensionID,
|
|
b.ProductDate,
|
|
b.ExpirationDate,
|
|
ISNULL(c.ProjectCode, '') AS ProjectCode,
|
|
ISNULL(c.BatchCode, '') AS BatchCode,
|
|
ISNULL(c.Version, '') AS Version,
|
|
ISNULL(c.Brand, '') AS Brand,
|
|
ISNULL(c.cFree1, '') AS cFree1,
|
|
ISNULL(c.cFree2, '') AS cFree2,
|
|
ISNULL(c.cFree3, '') AS cFree3,
|
|
ISNULL(c.cFree4, '') AS cFree4,
|
|
ISNULL(c.cFree5, '') AS cFree5,
|
|
ISNULL(c.cFree6, '') AS cFree6,
|
|
ISNULL(c.cFree7, '') AS cFree7,
|
|
ISNULL(c.cFree8, '') AS cFree8,
|
|
ISNULL(c.cFree9, '') AS cFree9,
|
|
ISNULL(c.cFree10, '') AS cFree10
|
|
INTO #TempWH
|
|
FROM ICSWareHouseLotInfo a
|
|
INNER JOIN ICSInventoryLot b ON a.LotNo = b.LotNo AND a.WorkPoint = b.WorkPoint
|
|
INNER JOIN ICSExtension c ON b.ExtensionID = c.ID AND b.WorkPoint = c.WorkPoint
|
|
WHERE a.WorkPoint = '{1}'
|
|
AND a.Inspect='0' --在检状态
|
|
AND a.Freeze='0' --锁定状态
|
|
AND a.Quantity > a.LockQuantity --库存数量大于该条吗占用数量
|
|
AND a.InvCode IN (SELECT InvCode FROM #TempVouchs)
|
|
--物料预留信息
|
|
SELECT a.InvCode,a.Quantity AS QTYTotal,b.Quantity AS ReserveQuantity,a.Quantity-ISNULL(b.Quantity, 0) AS CanOutQuantity
|
|
INTO #TempWHTotal
|
|
FROM (SELECT InvCode,Sum(LotQuantity) AS Quantity FROM #TempWH GROUP BY InvCode) a
|
|
LEFT JOIN ICSReserve b ON a.InvCode=b.InvCode AND b.Enable='1' AND b.WorkPoint='{1}' AND (GETDATE() BETWEEN b.BeginTime AND b.EndTime)
|
|
-- SELECT * FROM #TempWH
|
|
-- SELECT * FROM #TempWHTotal
|
|
--初始化返回结果临时表
|
|
SELECT TOP 0 Code,Sequence,InvCode,InvName,InvStd,InvUnit,FORMAT(iQuantity, '0.########') iQuantity,WarehouseCode,ExtensionID,ProjectCode,BatchCode,Version,Brand,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10,LocationCode,LotNO
|
|
,FORMAT(QTY, '0.########') QTY,FORMAT(QTYTotal, '0.########') QTYTotal,FORMAT(ReserveQuantity, '0.########') ReserveQuantity,MTIME,U8InvCode ,EATTRIBUTE2,NowMTIME
|
|
INTO #TempResult FROM #TempVouchs
|
|
--设置行号为自增列
|
|
ALTER TABLE #TempResult ADD [rowNo] INT IDENTITY(1,1);
|
|
|
|
|
|
--处理捡料逻辑
|
|
WHILE @VouchsRowCurrent<=@VouchsTotal
|
|
BEGIN
|
|
SELECT ROW_NUMBER() OVER (ORDER BY a.rowNo,b.MTIME,b.LotNO) AS rowNo,a.Code,a.Sequence,a.InvCode,a.InvName,a.InvStd,a.InvUnit,a.iQuantity,a.WarehouseCode,a.ExtensionID,a.ProjectCode,a.BatchCode,a.Version,a.Brand,a.cFree1,a.cFree2,a.cFree3,a.cFree4,a.cFree5,a.cFree6,a.cFree7,a.cFree8,a.cFree9,a.cFree10,
|
|
b.LocationCode,b.LotNO,b.LotQuantity,c.QTYTotal,c.ReserveQuantity,b.MTIME,a.iQuantity AS NeedQuantity,c.CanOutQuantity,a.U8InvCode,a.EATTRIBUTE2,a.NowMTIME
|
|
INTO #TempWHVouchs
|
|
FROM #TempVouchs a
|
|
LEFT JOIN #TempWH b ON a.InvCode=b.INVCode
|
|
AND (LEN(a.WarehouseCode)<=0 OR (LEN(a.WarehouseCode)>0 AND a.WarehouseCode=b.WarehouseCode))
|
|
AND (LEN(a.ProjectCode)<=0 OR (LEN(a.ProjectCode)>0 AND a.ProjectCode=b.ProjectCode))
|
|
AND (LEN(a.BatchCode)<=0 OR (LEN(a.BatchCode)>0 AND a.BatchCode=b.BatchCode))
|
|
AND (LEN(a.Version)<=0 OR (LEN(a.Version)>0 AND a.Version=b.Version))
|
|
AND (LEN(a.Brand)<=0 OR (LEN(a.Brand)>0 AND a.Brand=b.Brand))
|
|
AND (LEN(a.cFree1)<=0 OR (LEN(a.cFree1)>0 AND a.cFree1=b.cFree1))
|
|
AND (LEN(a.cFree2)<=0 OR (LEN(a.cFree2)>0 AND a.cFree2=b.cFree2))
|
|
AND (LEN(a.cFree3)<=0 OR (LEN(a.cFree3)>0 AND a.cFree3=b.cFree3))
|
|
AND (LEN(a.cFree4)<=0 OR (LEN(a.cFree4)>0 AND a.cFree4=b.cFree4))
|
|
AND (LEN(a.cFree5)<=0 OR (LEN(a.cFree5)>0 AND a.cFree5=b.cFree5))
|
|
AND (LEN(a.cFree6)<=0 OR (LEN(a.cFree6)>0 AND a.cFree6=b.cFree6))
|
|
AND (LEN(a.cFree7)<=0 OR (LEN(a.cFree7)>0 AND a.cFree7=b.cFree7))
|
|
AND (LEN(a.cFree8)<=0 OR (LEN(a.cFree8)>0 AND a.cFree8=b.cFree8))
|
|
AND (LEN(a.cFree9)<=0 OR (LEN(a.cFree9)>0 AND a.cFree9=b.cFree9))
|
|
AND (LEN(a.cFree10)<=0 OR (LEN(a.cFree10)>0 AND a.cFree10=b.cFree10))
|
|
LEFT JOIN #TempWHTotal c ON a.InvCode=c.INVCode
|
|
WHERE rowNo=@VouchsRowCurrent
|
|
|
|
DECLARE @WHTotal INT=@@rowcount, --库存总行数
|
|
@WHRowCurrent INT=1 --当前库存行
|
|
--如果可出库数量（已排除预留）小于等于单据数量，更新单据数量为可出库数量（已排除预留）
|
|
IF EXISTS(SELECT rowNo FROM #TempWHTotal a LEFT JOIN #TempVouchs b ON a.InvCode=b.InvCode WHERE rowNo=@VouchsRowCurrent AND a.CanOutQuantity<=ISNULL(b.iQuantity, 0))
|
|
BEGIN
|
|
--更新单据数量为可出库数量（已排除预留）
|
|
UPDATE #TempWHVouchs SET NeedQuantity=CanOutQuantity
|
|
--更新物料汇总表可出库数量（已排除预留）
|
|
UPDATE a SET CanOutQuantity=0 FROM #TempWHTotal a INNER JOIN #TempVouchs b ON a.InvCode=b.InvCode WHERE rowNo=@VouchsRowCurrent
|
|
--可出库数量（已排除预留）为0时，删除库存临时表的相关物料信息
|
|
DELETE FROM #TempWH WHERE InvCode=(SELECT InvCode FROM #TempVouchs WHERE rowNo=@VouchsRowCurrent)
|
|
END
|
|
--处理库存预留：如果可出库数量（已排除预留）大于单据数量，更新可出库数量（已排除预留）
|
|
ELSE
|
|
BEGIN
|
|
--更新物料汇总表可出库数量（已排除预留）
|
|
UPDATE a SET CanOutQuantity=a.CanOutQuantity-b.iQuantity FROM #TempWHTotal a INNER JOIN #TempVouchs b ON a.InvCode=b.InvCode WHERE rowNo=@VouchsRowCurrent
|
|
END
|
|
|
|
|
|
WHILE @WHRowCurrent<=@WHTotal
|
|
BEGIN
|
|
--剩余需领数量小于等于条码数量时，将结果插入结果集,并更新条码数量
|
|
IF EXISTS(SELECT rowNo FROM #TempWHVouchs WHERE rowNo=@WHRowCurrent AND NeedQuantity<=LotQuantity)
|
|
BEGIN
|
|
INSERT INTO #TempResult
|
|
SELECT a.Code,a.Sequence,a.InvCode,a.InvName,a.InvStd,a.InvUnit, FORMAT(a.iQuantity, '0.########') iQuantity,a.WarehouseCode,a.ExtensionID,a.ProjectCode,a.BatchCode,a.Version,a.Brand,a.cFree1,a.cFree2,a.cFree3,a.cFree4,a.cFree5,a.cFree6,a.cFree7,a.cFree8,a.cFree9,a.cFree10,
|
|
a.LocationCode,a.LotNO,FORMAT(a.NeedQuantity, '0.########') NeedQuantity,FORMAT(a.QTYTotal, '0.########') QTYTotal,FORMAT(a.ReserveQuantity, '0.########') ReserveQuantity,a.MTIME,a.U8InvCode,a.EATTRIBUTE2,a.NowMTIME
|
|
FROM #TempWHVouchs a
|
|
WHERE rowNo=@WHRowCurrent
|
|
--剩余应领数量和条码数量相同时，删除条码
|
|
IF EXISTS(SELECT rowNo FROM #TempWHVouchs WHERE rowNo=@WHRowCurrent AND NeedQuantity=LotQuantity)
|
|
BEGIN
|
|
--删除已使用条码
|
|
DELETE FROM #TempWH WHERE LotNO=(SELECT LotNO FROM #TempWHVouchs WHERE rowNo=@WHRowCurrent)
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
--更新已使用条码剩余数量
|
|
UPDATE a SET LotQuantity=b.LotQuantity-b.NeedQuantity FROM #TempWH a INNER JOIN #TempWHVouchs b ON a.LotNO=b.LotNO WHERE rowNo=@WHRowCurrent
|
|
END
|
|
--单据行处理完成，跳出库存循环，处理下一个单据行
|
|
BREAK
|
|
END
|
|
--剩余需领数量大于条码数量时，直接将结果插入结果集
|
|
ELSE
|
|
BEGIN
|
|
INSERT INTO #TempResult
|
|
SELECT a.Code,a.Sequence,a.InvCode,a.InvName,a.InvStd,a.InvUnit,FORMAT(a.iQuantity, '0.########') iQuantity,a.WarehouseCode,a.ExtensionID,a.ProjectCode,a.BatchCode,a.Version,a.Brand,a.cFree1,a.cFree2,a.cFree3,a.cFree4,a.cFree5,a.cFree6,a.cFree7,a.cFree8,a.cFree9,a.cFree10,
|
|
a.LocationCode,a.LotNO,FORMAT(a.LotQuantity, '0.########') LotQuantity,FORMAT(a.QTYTotal, '0.########') QTYTotal,FORMAT(a.ReserveQuantity, '0.########') ReserveQuantity,a.MTIME,a.U8InvCode,a.EATTRIBUTE2,a.NowMTIME
|
|
FROM #TempWHVouchs a
|
|
WHERE rowNo=@WHRowCurrent
|
|
--更新还需要领的数量、更新可出库数量（已排除预留）
|
|
UPDATE #TempWHVouchs SET NeedQuantity=NeedQuantity-(SELECT LotQuantity FROM #TempWHVouchs WHERE rowNo=@WHRowCurrent)
|
|
--删除已使用条码
|
|
DELETE FROM #TempWH WHERE LotNO=(SELECT LotNO FROM #TempWHVouchs WHERE rowNo=@WHRowCurrent)
|
|
END
|
|
--库存行号加1
|
|
SET @WHRowCurrent=@WHRowCurrent+1
|
|
END
|
|
|
|
--单据行号加1
|
|
SET @VouchsRowCurrent=@VouchsRowCurrent+1
|
|
DROP TABLE #TempWHVouchs
|
|
END
|
|
-- SELECT * FROM #TempWH
|
|
-- SELECT * FROM #TempWHTotal
|
|
-- SELECT * FROM #TempVouchs
|
|
SELECT * FROM #TempResult where isnull(LotNO,'')<>'' ORDER BY rowNo
|
|
--删除临时表
|
|
DROP TABLE #TempVouchs
|
|
DROP TABLE #TempWH
|
|
DROP TABLE #TempWHTotal
|
|
DROP TABLE #TempResult";
|
|
sql = string.Format(sql, ID,WorkPoint);
|
|
// sql = string.Format(sql, ID, Type, WorkPoint);
|
|
|
|
var dataset = Repository().FindDataSetBySql(sql);
|
|
if (dataset.Tables[0].Rows.Count == 0)
|
|
return null;
|
|
DataTable table = dataset.Tables[0];
|
|
try
|
|
{
|
|
DataRow[] dss = table.Select("LotNO=''");
|
|
foreach (var item in dss)
|
|
{
|
|
DataRow[] dsss = table.Select("Code='" + item["Code"].ToString() + "' and Sequence='" + item["Sequence"].ToString() + "' and LotNO <> '' ");
|
|
if (dsss != null && dsss.Length > 0)
|
|
{
|
|
table.Rows.Remove(item);
|
|
}
|
|
}
|
|
var result = ConvertCellToString(table);
|
|
if (Invmes.Rows.Count > 0)
|
|
{
|
|
result.Merge(Invmes, false);
|
|
}
|
|
return result;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
// public DataTable GetICSMOPickMergeTemp(string ID, string Type)
|
|
// {
|
|
|
|
// string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
// string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
// string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
// ID = string.IsNullOrWhiteSpace(ID) ? "''" : ID.TrimEnd(',');
|
|
|
|
// string sqls = "";
|
|
// string wheresql = "";
|
|
// #region 各单据sql
|
|
// if (Type == "1")
|
|
// {
|
|
// #region 工单备料sql
|
|
|
|
|
|
// sqls = @" SELECT
|
|
// c.MOCode as Code,
|
|
//c.Sequence+'-'+a.Sequence as Sequence,
|
|
// a.InvCode,
|
|
// b.InvName,
|
|
// b.InvStd,
|
|
// SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
|
|
// a.WhCode,
|
|
// b.InvUnit,
|
|
// a.ExtensionID,
|
|
//a.EATTRIBUTE2,
|
|
// f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
//,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10
|
|
// FROM
|
|
// ICSMOPick a
|
|
// inner join ICSMO c on a.MODetailID=c.MODetailID and a.WorkPoint=c.WorkPoint and c.MOStatus<>'3'
|
|
// LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
|
|
// LEFT JOIN ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
|
|
// WHERE c.MOCode+c.Sequence+'-'+a.Sequence in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0) and a.EATTRIBUTE1<>'1'
|
|
//GROUP BY c.MOCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,a.EATTRIBUTE2,c.Sequence+'-'+a.Sequence,f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
//,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10";
|
|
|
|
// #endregion
|
|
// }
|
|
|
|
|
|
|
|
// #endregion
|
|
|
|
// sqls = string.Format(sqls, ID, WorkPoint);
|
|
|
|
// string sql = $@"SELECT row_number() over ( order by c.EATTRIBUTE2,c.InvCode,d.LocationCode) AS rowNo,
|
|
// c.Code,
|
|
//c.Sequence,
|
|
// c.InvCode,
|
|
// c.InvName,
|
|
// c.InvStd,
|
|
// c.InvUnit,
|
|
// c.iQuantity AS iQuantity,
|
|
// c.WHCode AS WarehouseCode,
|
|
// d.LotNO,
|
|
// ISNULL(d.Quantity, 0) AS QTY,
|
|
// ISNULL(d.Quantity, 0) AS QTYLeft,
|
|
// CONVERT(decimal(18,6),0) AS SendQTY,
|
|
// d.LocationCode AS LocationCode,
|
|
// CONVERT(varchar(100),d.MTIME, 23) MTIME,
|
|
// f.QTYTotal QTYTotal,c.ExtensionID,
|
|
// c.EATTRIBUTE2,
|
|
// getdate() as NowMTIME
|
|
|
|
// FROM
|
|
// ({sqls}) c
|
|
// left join (select d.WarehouseCode,d.Quantity-d.LockQuantity as Quantity,d.INVCode, m.LotNo, d.MTIME,d.LocationCode,m.ExtensionID ,
|
|
// f.ProjectCode ,f.BatchCode ,f.Version ,f.Brand ,f.cFree1 ,f.cFree2 ,f.cFree3 ,f.cFree4
|
|
//,f.cFree5 ,f.cFree6 ,f.cFree7 ,f.cFree8 ,f.cFree9 ,f.cFree10
|
|
// from ICSWareHouseLotInfo d ,ICSInventoryLot m
|
|
// LEFT JOIN ICSExtension f on m.ExtensionID=f.ID and m.WorkPoint=f.WorkPoint
|
|
// where d.LotNo=m.LotNo and d.WorkPoint=m.WorkPoint
|
|
// and d.WorkPoint='{WorkPoint}' AND d.Quantity-d.LockQuantity>0 AND d.Quantity>0
|
|
|
|
// ) d
|
|
// on c.InvCode=d.INVCode
|
|
// AND (LEN(ISNULL(c.WHCode,''))<=0 OR (LEN(ISNULL(c.WHCode,''))>0 AND c.WHCode=d.WarehouseCode))
|
|
// AND (LEN(ISNULL(c.ProjectCode,''))<=0 OR (LEN(ISNULL(c.ProjectCode,''))>0 AND c.ProjectCode=d.ProjectCode))
|
|
// AND (LEN(ISNULL(c.BatchCode,''))<=0 OR (LEN(ISNULL(c.BatchCode,''))>0 AND c.BatchCode=d.BatchCode))
|
|
// AND (LEN(ISNULL(c.Version,''))<=0 OR (LEN(ISNULL(c.Version,''))>0 AND c.Version=d.Version))
|
|
// AND (LEN(ISNULL(c.Brand,''))<=0 OR (LEN(ISNULL(c.Brand,''))>0 AND c.Brand=d.Brand))
|
|
// AND (LEN(ISNULL(c.cFree1,''))<=0 OR (LEN(ISNULL(c.cFree1,''))>0 AND c.cFree1=d.cFree1))
|
|
// AND (LEN(ISNULL(c.cFree2,''))<=0 OR (LEN(ISNULL(c.cFree2,''))>0 AND c.cFree2=d.cFree2))
|
|
// AND (LEN(ISNULL(c.cFree3,''))<=0 OR (LEN(ISNULL(c.cFree3,''))>0 AND c.cFree3=d.cFree3))
|
|
// AND (LEN(ISNULL(c.cFree4,''))<=0 OR (LEN(ISNULL(c.cFree4,''))>0 AND c.cFree4=d.cFree4))
|
|
// AND (LEN(ISNULL(c.cFree5,''))<=0 OR (LEN(ISNULL(c.cFree5,''))>0 AND c.cFree5=d.cFree5))
|
|
// AND (LEN(ISNULL(c.cFree6,''))<=0 OR (LEN(ISNULL(c.cFree6,''))>0 AND c.cFree6=d.cFree6))
|
|
// AND (LEN(ISNULL(c.cFree7,''))<=0 OR (LEN(ISNULL(c.cFree7,''))>0 AND c.cFree7=d.cFree7))
|
|
// AND (LEN(ISNULL(c.cFree8,''))<=0 OR (LEN(ISNULL(c.cFree8,''))>0 AND c.cFree8=d.cFree8))
|
|
// AND (LEN(ISNULL(c.cFree9,''))<=0 OR (LEN(ISNULL(c.cFree9,''))>0 AND c.cFree9=d.cFree9))
|
|
// AND (LEN(ISNULL(c.cFree10,''))<=0 OR (LEN(ISNULL(c.cFree10,''))>0 AND c.cFree10=d.cFree10))
|
|
// LEFT JOIN (SELECT INVCode,WarehouseCode,SUM(Quantity-LockQuantity) AS QTYTotal FROM ICSWareHouseLotInfo WHERE WorkPoint='{WorkPoint}' GROUP BY INVCode,WarehouseCode) f ON c.InvCode=f.INVCode AND c.WhCode=f.WarehouseCode
|
|
// order by cast( row_number() over ( order by c.EATTRIBUTE2,c.InvCode,d.LocationCode) as int), c.EATTRIBUTE2,c.InvCode,d.LocationCode ";
|
|
// // sql = string.Format(sql, sqls)1;
|
|
// var dataset = Repository().FindDataSetBySql(sql);
|
|
// if (dataset.Tables[0].Rows.Count == 0)
|
|
// return null;
|
|
// DataTable table = dataset.Tables[0];
|
|
// decimal qtyCount = 0;
|
|
// bool remove = false;
|
|
// List<int> removeList = new List<int>();
|
|
// for (int i = 0; i < table.Rows.Count; i++)
|
|
// {
|
|
// if (i != 0
|
|
// && (!table.Rows[i]["InvCode"].ToString().Equals(table.Rows[i - 1]["InvCode"].ToString())
|
|
// || !table.Rows[i]["Sequence"].ToString().Equals(table.Rows[i - 1]["Sequence"].ToString())
|
|
// || !table.Rows[i]["Code"].ToString().Equals(table.Rows[i - 1]["Code"].ToString())
|
|
// || !table.Rows[i]["ExtensionID"].ToString().Equals(table.Rows[i - 1]["ExtensionID"].ToString()))
|
|
// )
|
|
// {
|
|
// qtyCount = 0;
|
|
// remove = false;
|
|
// }
|
|
// if (table.Rows[i]["QTYLeft"].ToString().ToDecimal() == 0 && table.Rows[i]["LotNO"].ToString() != "")
|
|
// {
|
|
// table.Rows[i]["LotNO"] = "";
|
|
// string a = "0.000000";
|
|
// table.Rows[i]["QTY"] = Convert.ToDecimal(a);
|
|
// //removeList.Add(i);
|
|
// continue;
|
|
// }
|
|
// if (remove && table.Rows[i]["LotNO"].ToString() != "")
|
|
// {
|
|
// removeList.Add(i);
|
|
// }
|
|
|
|
// else
|
|
// {
|
|
// var lotQty = table.Rows[i]["QTYLeft"].ToString().ToDecimal();
|
|
// var orderQty = table.Rows[i]["iQuantity"].ToString().ToDecimal();
|
|
// qtyCount += lotQty;
|
|
// foreach (DataRow dr in table.Rows)
|
|
// {
|
|
// if (dr["LotNO"].ToString() == table.Rows[i]["LotNO"].ToString())
|
|
// {
|
|
// if (qtyCount > orderQty)
|
|
// {
|
|
// dr["QTYLeft"] = Convert.ToDecimal(qtyCount) - Convert.ToDecimal(orderQty);
|
|
// }
|
|
// else
|
|
// {
|
|
// dr["QTYLeft"] = 0;
|
|
// }
|
|
// }
|
|
// }
|
|
// if (qtyCount >= orderQty)
|
|
// {
|
|
// table.Rows[i]["SendQTY"] = Convert.ToDecimal(lotQty) - (Convert.ToDecimal(qtyCount) - Convert.ToDecimal(orderQty));
|
|
// remove = true;
|
|
// }
|
|
// else
|
|
// {
|
|
// table.Rows[i]["SendQTY"] = lotQty;
|
|
// }
|
|
// }
|
|
// }
|
|
// if (removeList.Count > 0)
|
|
// {
|
|
// removeList.Reverse();
|
|
// foreach (var item in removeList)
|
|
// {
|
|
// table.Rows.RemoveAt(item);
|
|
// }
|
|
// }
|
|
// try
|
|
// {
|
|
// DataRow [] dss= table.Select("LotNO=''");
|
|
// foreach (var item in dss)
|
|
// {
|
|
// DataRow[] dsss = table.Select("Code='"+ item["Code"].ToString()+ "' and Sequence='"+ item["Sequence"].ToString() + "' and LotNO <> '' ");
|
|
// if (dsss!=null && dsss.Length>0)
|
|
// {
|
|
// table.Rows.Remove(item);
|
|
// }
|
|
// }
|
|
// for (int i = 0; i < table.Rows.Count; i++)
|
|
// {
|
|
// table.Rows[i]["rowNo"] = i+1;
|
|
// }
|
|
|
|
|
|
|
|
// var result = ConvertCellToString(table);
|
|
// if (Invmes.Rows.Count > 0)
|
|
// {
|
|
// result.Merge(Invmes, false);
|
|
// }
|
|
// return result;
|
|
// }
|
|
// catch (Exception ex)
|
|
// {
|
|
// throw new Exception(ex.Message);
|
|
// }
|
|
// }
|
|
|
|
public DataTable ConvertCellToString(DataTable data)
|
|
{
|
|
DataTable dtCloned = data.Clone();
|
|
foreach (DataColumn col in dtCloned.Columns)
|
|
{
|
|
col.DataType = typeof(string);
|
|
}
|
|
foreach (DataRow row in data.Rows)
|
|
{
|
|
DataRow newrow = dtCloned.NewRow();
|
|
foreach (DataColumn column in dtCloned.Columns)
|
|
{
|
|
newrow[column.ColumnName] = row[column.ColumnName].ToString();
|
|
|
|
}
|
|
dtCloned.Rows.Add(newrow);
|
|
}
|
|
return dtCloned;
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public string CheckQty(string objArr)
|
|
{
|
|
objArr = objArr.TrimEnd(',');
|
|
string sql = @"select * from ICSMOPickMerge a
|
|
left join ICSMOPick b on a.SourceID=b.ID and a.WorkPoint=b.WorkPoint
|
|
left join ICSMO c on b.MODetailID=c.MODetailID and b.WorkPoint=c.WorkPoint and c.MOStatus<>'3'
|
|
where MOCode in ({0})";
|
|
sql = string.Format(sql, objArr);
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
if (dt != null && dt.Rows.Count > 0)
|
|
{
|
|
return "1";
|
|
}
|
|
|
|
|
|
return "0";
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public object SeachPickingListType(string Type)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
|
|
string sql = @" select b.F_EnabledMark as flag from Sys_SRM_Items a left join Sys_SRM_ItemsDetail b on a.F_Id=b.F_ItemId
|
|
where a.F_EnCode='PL00001' and b.F_ItemCode='{0}' order by cast(b.F_SortCode as int) asc";
|
|
sql = string.Format(sql, Type);
|
|
object flag = SqlHelper.ExecuteScalar(sql);
|
|
|
|
return flag;
|
|
|
|
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
}
|
|
}
|