|
|
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;
}
}}
|
