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.
629 lines
34 KiB
629 lines
34 KiB
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using ICSSoft.Frame.Data.Entity;
|
|
using ICSSoft.Base.Config.AppConfig;
|
|
using ICSSoft.Base.Config.DBHelper;
|
|
using System.Data;
|
|
using System.Data.Sql;
|
|
using System.Data.Linq;
|
|
|
|
namespace ICSSoft.Frame.Data.DAL
|
|
{
|
|
public class ICSGaugeDAL
|
|
{
|
|
|
|
#region 根据工单查询子件机汇总信息
|
|
public static DataSet select_(String MOCode, String TransNO, String Appconstr)
|
|
{
|
|
try
|
|
{
|
|
#region 20190627增加仓库库存数量
|
|
//生产工单下 1C707AJ2017091289 有总数量
|
|
string sql = @" SELECT
|
|
e.MOCODE,
|
|
e.MOSEQ,
|
|
e.MOVER,
|
|
e.ITEMCODE,
|
|
b.INVNAME,
|
|
b.INVSTD AS INVTYPE,
|
|
c.MOBITEMCODE,
|
|
d.INVNAME AS CINVNAME,
|
|
d.INVSTD AS CINVTYPE,
|
|
d.INVUOM,
|
|
CAST(ISNULL(c.MOBITEMQTY, 0) AS DECIMAL(18,6)) AS QTY,
|
|
--CAST(ISNULL(c.MOBITEMQTY, 0)*e.MOPLANQTY AS DECIMAL(18,2)) AS QTY,
|
|
ISNULL(f.HasQuantity, 0) AS QTYS,
|
|
c.MOBITEMLOCATION AS Wh,
|
|
ISNULL(z.BinQTY, 0) as QTYTotal
|
|
FROM
|
|
ICSMO e
|
|
LEFT JOIN ICSINVENTORY b ON e.ITEMCODE = b.INVCODE
|
|
LEFT JOIN ICSMOBOM c ON e.MOCODE = c.MOCODE AND c.SEQ=e.MOSEQ AND e.ITEMCODE = c.ITEMCODE
|
|
LEFT JOIN ICSINVENTORY d ON c.MOBITEMCODE = d.INVCODE
|
|
LEFT JOIN ICSMOPickLog f ON c.MOCODE=f.MOCODE AND c.MOBOMLINE=f.MOSEQ and c.SEQ=f.SEQ
|
|
LEFT JOIN (
|
|
SELECT
|
|
P.INVCode,
|
|
P.WHCode,
|
|
SUM (P.LotQty) AS BinQTY
|
|
FROM
|
|
ICSWareHouseLotInfo p
|
|
INNER JOIN ICSStorage t ON P.WHCode = t.StorageCode
|
|
WHERE
|
|
P.WorkPoint = '{1}' -- AND WHCode=''
|
|
GROUP BY
|
|
P.INVCode,
|
|
P.WHCode
|
|
) z ON z.INVCode = c.MOBITEMCODE AND z.WHCode = c.MOBITEMLOCATION
|
|
WHERE e.MOCODE='{0}' AND
|
|
e.WorkPoint = '{1}' AND ISNULL(d.INVCLASS, '') <> 'I09'
|
|
ORDER BY c.MOBITEMCODE,e.MOSEQ,c.MOBOMLINE
|
|
";
|
|
|
|
sql += @"
|
|
SELECT c.MOCODE,
|
|
c.MOBITEMCODE,
|
|
c.CINVNAME,
|
|
c.CINVTYPE,
|
|
c.INVUOM,
|
|
CAST(ISNULL(c.MOBITEMQTY, 0) AS DECIMAL(18,2)) AS QTYXBC,
|
|
ISNULL(f.HasQuantity, 0) AS MOBITEMQTY
|
|
FROM
|
|
(
|
|
SELECT
|
|
a.MOCODE,
|
|
a.MOBOMLINE,
|
|
a.SEQ,
|
|
a.MOBITEMCODE,
|
|
b.INVNAME AS CINVNAME,
|
|
b.INVSTD AS CINVTYPE,
|
|
b.INVUOM,
|
|
b.INVMACHINETYPE,
|
|
SUM(a.MOBITEMQTY) AS MOBITEMQTY
|
|
--SUM(a.MOBITEMQTY*e.MOPLANQTY) AS MOBITEMQTY
|
|
FROM ICSMOBOM a
|
|
LEFT JOIN ICSINVENTORY b ON a.MOBITEMCODE = b.INVCODE AND a.WorkPoint=b.WorkPoint
|
|
INNER JOIN ICSMO e ON a.MOCODE = e.MOCODE AND a.SEQ=e.MOSEQ AND a.WorkPoint=e.WorkPoint
|
|
WHERE a.MOCODE='{0}' AND
|
|
a.WorkPoint = '{1}' AND ISNULL(b.INVCLASS, '') <> 'I09'
|
|
GROUP BY a.MOCODE,a.MOBITEMCODE,a.MOBOMLINE, a.SEQ,b.INVNAME,b.INVSTD,b.INVUOM,b.INVMACHINETYPE
|
|
)c
|
|
|
|
LEFT JOIN ICSMOPickLog f ON c.MOCODE=f.MOCODE AND c.MOBOMLINE=f.MOSEQ AND c.SEQ=f.SEQ
|
|
ORDER BY c.MOBITEMCODE
|
|
";
|
|
#endregion
|
|
|
|
#region 20190627_bak
|
|
|
|
|
|
// string sql = @" SELECT
|
|
// e.MOCODE,
|
|
// e.MOSEQ,
|
|
// e.MOVER,
|
|
// e.ITEMCODE,
|
|
// b.INVNAME,
|
|
// b.INVSTD AS INVTYPE,
|
|
// c.MOBITEMCODE,
|
|
// d.INVNAME AS CINVNAME,
|
|
// d.INVSTD AS CINVTYPE,
|
|
// d.INVUOM,
|
|
// CAST(ISNULL(c.MOBITEMQTY, 0) AS DECIMAL(18,6)) AS QTY,
|
|
// --CAST(ISNULL(c.MOBITEMQTY, 0)*e.MOPLANQTY AS DECIMAL(18,2)) AS QTY,
|
|
// ISNULL(f.HasQuantity, 0) AS QTYS
|
|
// FROM
|
|
// ICSMO e
|
|
// LEFT JOIN ICSINVENTORY b ON e.ITEMCODE = b.INVCODE
|
|
// LEFT JOIN ICSMOBOM c ON e.MOCODE = c.MOCODE AND c.SEQ=e.MOSEQ AND e.ITEMCODE = c.ITEMCODE
|
|
// LEFT JOIN ICSINVENTORY d ON c.MOBITEMCODE = d.INVCODE
|
|
// LEFT JOIN ICSMOPickLog f ON c.MOCODE=f.MOCODE AND c.MOBOMLINE=f.MOSEQ and c.SEQ=f.SEQ
|
|
// WHERE e.MOCODE='{0}' AND
|
|
// e.WorkPoint = '{1}' AND ISNULL(d.INVCLASS, '') <> 'I09'
|
|
// ORDER BY c.MOBITEMCODE,e.MOSEQ,c.MOBOMLINE
|
|
// ";
|
|
|
|
// sql += @"
|
|
// SELECT c.MOCODE,
|
|
// c.MOBITEMCODE,
|
|
// c.CINVNAME,
|
|
// c.CINVTYPE,
|
|
// c.INVUOM,
|
|
// CAST(ISNULL(c.MOBITEMQTY, 0) AS DECIMAL(18,2)) AS QTYXBC,
|
|
// ISNULL(f.HasQuantity, 0) AS MOBITEMQTY
|
|
// FROM
|
|
// (
|
|
// SELECT
|
|
// a.MOCODE,
|
|
// a.MOBOMLINE,
|
|
// a.SEQ,
|
|
// a.MOBITEMCODE,
|
|
// b.INVNAME AS CINVNAME,
|
|
// b.INVSTD AS CINVTYPE,
|
|
// b.INVUOM,
|
|
// b.INVMACHINETYPE,
|
|
// SUM(a.MOBITEMQTY) AS MOBITEMQTY
|
|
// --SUM(a.MOBITEMQTY*e.MOPLANQTY) AS MOBITEMQTY
|
|
// FROM ICSMOBOM a
|
|
// LEFT JOIN ICSINVENTORY b ON a.MOBITEMCODE = b.INVCODE AND a.WorkPoint=b.WorkPoint
|
|
// INNER JOIN ICSMO e ON a.MOCODE = e.MOCODE AND a.SEQ=e.MOSEQ AND a.WorkPoint=e.WorkPoint
|
|
// WHERE a.MOCODE='{0}' AND
|
|
// a.WorkPoint = '{1}' AND ISNULL(b.INVCLASS, '') <> 'I09'
|
|
// GROUP BY a.MOCODE,a.MOBITEMCODE,a.MOBOMLINE, a.SEQ,b.INVNAME,b.INVSTD,b.INVUOM,b.INVMACHINETYPE
|
|
// )c
|
|
//
|
|
// LEFT JOIN ICSMOPickLog f ON c.MOCODE=f.MOCODE AND c.MOBOMLINE=f.MOSEQ AND c.SEQ=f.SEQ
|
|
// ORDER BY c.MOBITEMCODE
|
|
// ";
|
|
#endregion
|
|
sql = string.Format(sql, MOCode, AppConfig.WorkPointCode, TransNO);
|
|
DataSet ds = DBHelper.ExecuteDataset(Appconstr, CommandType.Text, sql);
|
|
return ds;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
#region 根据工单查询子件机汇总信息
|
|
public static DataSet select(String MOCode, String TransNO, String Appconstr, String WHERE)
|
|
{
|
|
try
|
|
{
|
|
#region 20190917注释ZM
|
|
// string sql = @" SELECT
|
|
// e.MOCODE,
|
|
// e.MOSEQ,
|
|
// e.MOVER,
|
|
// e.ITEMCODE,
|
|
// b.INVNAME,
|
|
// b.INVSTD AS INVTYPE,
|
|
// c.MOBITEMCODE,
|
|
// d.INVNAME AS CINVNAME,
|
|
// d.INVSTD AS CINVTYPE,
|
|
//e.FACTORY AS Wh,
|
|
//
|
|
// d.INVUOM,
|
|
// CAST(ISNULL(c.MOBITEMQTY, 0) AS DECIMAL(18,2)) QTY,
|
|
// CAST(ISNULL(f.HasQuantity, 0) AS DECIMAL(18,2)) AS QTYS
|
|
// FROM
|
|
// ICSMO e
|
|
// LEFT JOIN ICSINVENTORY b ON e.ITEMCODE = b.INVCODE
|
|
// LEFT JOIN ICSMOBOM c ON e.MOCODE = c.MOCODE AND c.SEQ=e.MOSEQ AND e.ITEMCODE = c.ITEMCODE
|
|
// LEFT JOIN ICSINVENTORY d ON c.MOBITEMCODE = d.INVCODE
|
|
// LEFT JOIN ICSMOPickLog f ON c.MOCODE=f.MOCODE AND c.MOBOMLINE=f.MOSEQ and c.SEQ=f.SEQ
|
|
// WHERE e.MOCODE='{0}' AND
|
|
// e.WorkPoint = '{1}' AND ISNULL(d.INVCLASS, '') <> 'I09'
|
|
// ORDER BY c.MOBITEMCODE,e.MOSEQ,c.MOBOMLINE
|
|
// ";
|
|
|
|
// sql += @"
|
|
// SELECT c.MOCODE,
|
|
// c.MOBITEMCODE,
|
|
// c.CINVNAME,
|
|
// c.CINVTYPE,
|
|
// c.INVUOM,
|
|
// CAST(ISNULL(c.MOBITEMQTY, 0) AS DECIMAL(18,2)) AS QTYXBC,
|
|
// CAST(ISNULL(f.HasQuantity, 0) AS DECIMAL(18,2)) MOBITEMQTY,
|
|
// c.WorkPoint
|
|
// FROM
|
|
// (
|
|
// SELECT
|
|
// a.MOCODE,
|
|
// a.MOBOMLINE,
|
|
// a.SEQ,
|
|
// a.WorkPoint,
|
|
// a.MOBITEMCODE,
|
|
// b.INVNAME AS CINVNAME,
|
|
// b.INVSTD AS CINVTYPE,
|
|
// b.INVUOM,
|
|
// b.INVMACHINETYPE,
|
|
// SUM(a.MOBITEMQTY) AS MOBITEMQTY
|
|
// --SUM(a.MOBITEMQTY*e.MOPLANQTY) AS MOBITEMQTY
|
|
// FROM ICSMOBOM a
|
|
// LEFT JOIN ICSINVENTORY b ON a.MOBITEMCODE = b.INVCODE AND a.WorkPoint=b.WorkPoint
|
|
// INNER JOIN ICSMO e ON a.MOCODE = e.MOCODE AND a.SEQ=e.MOSEQ AND a.WorkPoint=e.WorkPoint
|
|
// WHERE a.MOCODE='{0}'
|
|
// GROUP BY a.MOCODE,a.MOBITEMCODE,a.MOBOMLINE, a.SEQ,b.INVNAME,b.INVSTD,b.INVUOM,b.INVMACHINETYPE,a.WorkPoint
|
|
// )c
|
|
// LEFT JOIN ICSMOPickLog f ON c.MOCODE=f.MOCODE AND c.MOBOMLINE=f.MOSEQ AND c.SEQ=f.SEQ
|
|
// where {3} AND
|
|
// c.WorkPoint = '{1}' --AND ISNULL(b.INVCLASS, '') <> 'I09'
|
|
// ORDER BY c.MOBITEMCODE
|
|
// ";
|
|
#endregion
|
|
|
|
#region delete by summer 2020.08.31
|
|
// string sql = @" SELECT
|
|
// e.MOCODE,
|
|
// e.MOSEQ,
|
|
// e.MOVER,
|
|
// e.ITEMCODE,
|
|
// b.INVNAME,
|
|
// b.INVSTD AS INVTYPE,
|
|
// c.MOBITEMCODE,
|
|
// d.INVNAME AS CINVNAME,
|
|
// d.INVSTD AS CINVTYPE,
|
|
// --e.FACTORY AS Wh,
|
|
//c.MOBITEMLOCATION AS WH,
|
|
//g.StorageName AS WHName,
|
|
// d.INVUOM,
|
|
// CAST (
|
|
// ISNULL(c.MOBITEMQTY, 0) AS DECIMAL (18, 2)
|
|
// ) QTY,
|
|
// CAST (
|
|
// ISNULL(f.HasQuantity, 0) AS DECIMAL (18, 2)
|
|
// ) AS QTYS,
|
|
// CAST ( ISNULL(k.QTYTotal, 0) AS DECIMAL (18, 2)) QTYTotal
|
|
//FROM
|
|
// ICSMO e
|
|
//LEFT JOIN ICSINVENTORY b ON e.ITEMCODE = b.INVCODE
|
|
//LEFT JOIN ICSMOBOM c ON e.MOCODE = c.MOCODE
|
|
//AND c.SEQ = e.MOSEQ
|
|
//AND e.ITEMCODE = c.ITEMCODE
|
|
//LEFT JOIN ICSINVENTORY d ON c.MOBITEMCODE = d.INVCODE
|
|
// LEFT JOIN ICSMOPickLog f ON c.MOCODE=f.MOCODE AND c.SEQ=f.MOSEQ and c.MOBOMLINE=f.SEQ
|
|
//LEFT JOIN ICSStorage g ON g.StorageCode=c.MOBITEMLOCATION
|
|
//LEFT JOIN
|
|
//(SELECT
|
|
// INVCode,
|
|
// SUM (LotQty) AS QTYTotal
|
|
// FROM
|
|
// ICSWareHouseLotInfo
|
|
// WHERE
|
|
// WorkPoint = '{1}'
|
|
// GROUP BY
|
|
// INVCode
|
|
//) k ON d.INVCODE = k.INVCode
|
|
//WHERE
|
|
// e.MOCODE = '{0}'
|
|
//AND e.WorkPoint = '{1}'
|
|
//AND ISNULL(d.INVCLASS, '') <> 'I09'
|
|
//
|
|
//and e.MOBOM!=4
|
|
//
|
|
//ORDER BY
|
|
// c.MOBITEMCODE,
|
|
// e.MOSEQ,
|
|
// c.MOBOMLINE ";
|
|
#endregion
|
|
#region add by summer 2020.08.31
|
|
string sql = @"
|
|
SELECT E.MOCODE, E.MOSEQ, E.MOVER, E.ITEMCODE, B.INVNAME, B.INVSTD AS INVTYPE, C.MOBITEMCODE,
|
|
D.INVNAME AS CINVNAME, D.INVSTD AS CINVTYPE, A.WHCode AS WH, G.StorageName AS WHName, D.INVUOM,
|
|
CAST(ISNULL(C.MOBITEMQTY, 0) AS DECIMAL(18, 2)) AS QTY, CAST(ISNULL(F.HasQuantity, 0) AS DECIMAL(18, 2)) AS QTYS,
|
|
CAST(ISNULL(K.QTYTotal, 0) AS DECIMAL(18, 2)) AS QTYTotal
|
|
FROM ICSMO E LEFT JOIN ICSINVENTORY B ON E.ITEMCODE = B.INVCODE
|
|
LEFT JOIN ICSMOBOM C ON E.MOCODE = C.MOCODE AND C.SEQ = E.MOSEQ AND E.ITEMCODE = C.ITEMCODE
|
|
LEFT JOIN ICSINVENTORY D ON C.MOBITEMCODE = D.INVCODE
|
|
LEFT JOIN ICSMOPickLog F ON C.MOCODE = F.MOCODE AND C.SEQ = F.MOSEQ AND C.MOBOMLINE= F.SEQ
|
|
LEFT JOIN (SELECT DISTINCT A.INVCode, A.WHCode FROM ICSWareHouseLotInfo A INNER JOIN Sys_EnumValues B ON A.WHCode = B.EnumText
|
|
WHERE A.WorkPoint = '{1}' AND B.EnumKey = '00008' AND A.LotQty > 0) A ON D.INVCODE = A.INVCode
|
|
LEFT JOIN ICSStorage G ON G.StorageCode = A.WHCode
|
|
LEFT JOIN (SELECT INVCode, SUM (LotQty) AS QTYTotal FROM ICSWareHouseLotInfo WHERE WorkPoint = '{1}' GROUP BY INVCode) K ON D.INVCODE = K.INVCode
|
|
WHERE E.MOCODE = '{0}' AND E.WorkPoint = '{1}' AND ISNULL(D.INVCLASS, '') <> 'I09' AND E.MOBOM != 4
|
|
ORDER BY C.MOBITEMCODE, E.MOSEQ, C.MOBOMLINE ";
|
|
#endregion
|
|
|
|
sql += @"
|
|
SELECT c.MOCODE,
|
|
c.MOBITEMCODE,
|
|
c.CINVNAME,
|
|
c.CINVTYPE,
|
|
c.INVUOM,
|
|
CAST(ISNULL(c.MOBITEMQTY, 0) AS DECIMAL(18,2)) AS QTYXBC,
|
|
CAST(ISNULL(f.HasQuantity, 0) AS DECIMAL(18,2)) MOBITEMQTY,
|
|
c.WorkPoint
|
|
FROM
|
|
(
|
|
SELECT
|
|
a.MOCODE,
|
|
a.MOBOMLINE,
|
|
a.SEQ,
|
|
a.WorkPoint,
|
|
a.MOBITEMCODE,
|
|
b.INVNAME AS CINVNAME,
|
|
b.INVSTD AS CINVTYPE,
|
|
b.INVUOM,
|
|
b.INVMACHINETYPE,
|
|
SUM(a.MOBITEMQTY) AS MOBITEMQTY
|
|
--SUM(a.MOBITEMQTY*e.MOPLANQTY) AS MOBITEMQTY
|
|
FROM ICSMOBOM a
|
|
LEFT JOIN ICSINVENTORY b ON a.MOBITEMCODE = b.INVCODE AND a.WorkPoint=b.WorkPoint
|
|
INNER JOIN ICSMO e ON a.MOCODE = e.MOCODE AND a.SEQ=e.MOSEQ AND a.WorkPoint=e.WorkPoint
|
|
WHERE a.MOCODE='{0}' and a.MOBOM!=4
|
|
GROUP BY a.MOCODE,a.MOBITEMCODE,a.MOBOMLINE, a.SEQ,b.INVNAME,b.INVSTD,b.INVUOM,b.INVMACHINETYPE,a.WorkPoint
|
|
)c
|
|
LEFT JOIN ICSMOPickLog f ON c.MOCODE=f.MOCODE AND c.MOBOMLINE=f.SEQ AND c.SEQ=f.MOSEQ
|
|
where {3} AND
|
|
c.WorkPoint = '{1}' --AND ISNULL(b.INVCLASS, '') <> 'I09'
|
|
ORDER BY c.MOBITEMCODE
|
|
";
|
|
|
|
sql = string.Format(sql, MOCode, AppConfig.WorkPointCode, TransNO, WHERE);
|
|
DataSet ds = DBHelper.ExecuteDataset(Appconstr, CommandType.Text, sql);
|
|
return ds;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
|
|
|
|
#region 根据工单查询拣料表信息
|
|
public static DataSet GetGauge_(String MOCode, String Appconstr)
|
|
{
|
|
try
|
|
{
|
|
//--ICSStorage --ICSWareHouseInfo
|
|
#region 20190705ZM
|
|
string sql = @" SELECT c.MOCODE,
|
|
c.MOBITEMCODE,
|
|
c.CINVNAME,
|
|
c.CINVTYPE,
|
|
c.INVUOM,
|
|
CAST(ISNULL(c.MOBITEMQTY, 0) AS DECIMAL(18,2)) AS MOBITEMQTY,
|
|
ISNULL(e.QTYTotal, 0) AS QTYTotal,
|
|
ISNULL(f.QTYXBC, 0) AS QTYXBC,
|
|
CASE WHEN ISNULL(c.MOBITEMQTY, 0)-ISNULL(f.QTYXBC, 0) < 0 THEN '0' ELSE ISNULL(c.MOBITEMQTY, 0)-ISNULL(f.QTYXBC, 0) END AS QTYNeed,
|
|
d.WHCode,
|
|
d.BinCode,
|
|
d.LotNO,
|
|
g.VenderLotNO AS VenderLotNO,
|
|
|
|
ISNULL(d.LotQty, 0) AS QTY,
|
|
d.MTIME
|
|
FROM
|
|
(
|
|
SELECT
|
|
a.MOCODE,
|
|
a.MOBITEMCODE,
|
|
b.INVNAME AS CINVNAME,
|
|
b.INVSTD AS CINVTYPE,
|
|
b.INVUOM,
|
|
b.INVMACHINETYPE,
|
|
SUM(a.MOBITEMQTY) AS MOBITEMQTY
|
|
--SUM(a.MOBITEMQTY*e.MOPLANQTY) AS MOBITEMQTY
|
|
FROM ICSMOBOM a
|
|
LEFT JOIN ICSINVENTORY b ON a.MOBITEMCODE = b.INVCODE AND a.WorkPoint=b.WorkPoint
|
|
INNER JOIN ICSMO e ON a.MOCODE = e.MOCODE AND a.SEQ=e.MOSEQ AND a.WorkPoint=e.WorkPoint
|
|
WHERE a.MOCODE='{0}' AND
|
|
a.WorkPoint = '{1}' AND ISNULL(b.INVCLASS, '') <> 'I09'
|
|
GROUP BY a.MOCODE,a.MOBITEMCODE,b.INVNAME,b.INVSTD,b.INVUOM,b.INVMACHINETYPE
|
|
)c
|
|
LEFT JOIN ICSWareHouseLotInfo d ON c.MOBITEMCODE=d.INVCode AND d.WorkPoint='{1}' AND d.LotQty>0
|
|
|
|
LEFT JOIN ICSITEMLot g ON g.LotNO=d.LotNO
|
|
|
|
LEFT JOIN (SELECT INVCode,SUM(LotQty) AS QTYTotal FROM ICSWareHouseLotInfo WHERE WorkPoint='{1}' GROUP BY INVCode) e ON c.MOBITEMCODE=e.INVCode
|
|
LEFT JOIN (SELECT INVCode,WHCode,SUM(LotQty) AS QTYXBC FROM ICSWareHouseLotInfo WHERE WorkPoint='{1}' GROUP BY INVCode,WHCode) f ON c.MOBITEMCODE=f.INVCode AND c.INVMACHINETYPE=f.WHCode
|
|
ORDER BY c.MOBITEMCODE,d.MTIME,d.LotNO
|
|
";
|
|
#endregion
|
|
|
|
#region 20190705ZM_新
|
|
// string sql = @"SELECT
|
|
// c.MOCODE,
|
|
// c.MOBITEMCODE,
|
|
// c.CINVNAME,
|
|
// c.CINVTYPE,
|
|
// c.INVUOM,
|
|
// CAST (
|
|
// ISNULL(c.MOBITEMQTY, 0) AS DECIMAL (18, 2)
|
|
// ) AS MOBITEMQTY,
|
|
// ISNULL(e.QTYTotal, 0) AS QTYTotal,
|
|
// ISNULL(f.QTYXBC, 0) AS QTYXBC,
|
|
// CASE
|
|
// WHEN ISNULL(c.MOBITEMQTY, 0) - ISNULL(f.QTYXBC, 0) < 0 THEN
|
|
// '0'
|
|
// ELSE
|
|
// ISNULL(c.MOBITEMQTY, 0) - ISNULL(f.QTYXBC, 0)
|
|
// END AS QTYNeed,
|
|
// d.WHCode,
|
|
// d.BinCode,
|
|
// d.LotNO,
|
|
// ISNULL(d.LotQty, 0) AS QTY,
|
|
// d.MTIME
|
|
// FROM
|
|
// (
|
|
// SELECT
|
|
// a.MOCODE,
|
|
// a.MOBITEMCODE,
|
|
// b.INVNAME AS CINVNAME,
|
|
// b.INVSTD AS CINVTYPE,
|
|
// b.INVUOM,
|
|
// b.INVMACHINETYPE,
|
|
// a.MOBITEMLOCATION,
|
|
// SUM (a.MOBITEMQTY) AS MOBITEMQTY --SUM(a.MOBITEMQTY*e.MOPLANQTY) AS MOBITEMQTY
|
|
// FROM
|
|
// ICSMOBOM a
|
|
// LEFT JOIN ICSINVENTORY b ON a.MOBITEMCODE = b.INVCODE
|
|
// AND a.WorkPoint = b.WorkPoint
|
|
// INNER JOIN ICSMO e ON a.MOCODE = e.MOCODE
|
|
// AND a.SEQ = e.MOSEQ
|
|
// AND a.WorkPoint = e.WorkPoint
|
|
// WHERE
|
|
// a.MOCODE='{0}' AND a.WorkPoint = '{1}'
|
|
// AND ISNULL(b.INVCLASS, '') <> 'I09'
|
|
// GROUP BY
|
|
// a.MOCODE,
|
|
// a.MOBITEMCODE,
|
|
// b.INVNAME,
|
|
// b.INVSTD,
|
|
// b.INVUOM,
|
|
// b.INVMACHINETYPE,
|
|
// a.MOBITEMLOCATION
|
|
// ) c
|
|
// LEFT JOIN ICSWareHouseLotInfo d ON c.MOBITEMCODE = d.INVCode
|
|
// AND d.WorkPoint = '6000'
|
|
// AND d.LotQty > 0
|
|
// LEFT JOIN (
|
|
// SELECT DISTINCT
|
|
// P.INVCode,
|
|
// P.WHCode,
|
|
// SUM (P.LotQty) AS QTYTotal
|
|
// FROM
|
|
// ICSWareHouseLotInfo p
|
|
// LEFT JOIN ICSStorage t ON P.WHCode = t.StorageCode
|
|
// WHERE
|
|
// P.WorkPoint = '{1}'
|
|
// GROUP BY
|
|
// P.INVCode,
|
|
// P.WHCode
|
|
// ) e ON c.MOBITEMCODE = e.INVCode
|
|
// AND e.WHCode = c.MOBITEMLOCATION
|
|
// LEFT JOIN (
|
|
// SELECT
|
|
// INVCode,
|
|
// WHCode,
|
|
// SUM (LotQty) AS QTYXBC
|
|
// FROM
|
|
// ICSWareHouseLotInfo
|
|
// WHERE
|
|
// WorkPoint = '{1}'
|
|
// GROUP BY
|
|
// INVCode,
|
|
// WHCode
|
|
// ) f ON c.MOBITEMCODE = f.INVCode
|
|
// AND c.INVMACHINETYPE = f.WHCode
|
|
// ORDER BY
|
|
// c.MOBITEMCODE,
|
|
// d.MTIME,
|
|
// d.LotNO";
|
|
#endregion
|
|
|
|
|
|
sql = string.Format(sql, MOCode, AppConfig.WorkPointCode);
|
|
DataSet ds = DBHelper.ExecuteDataset(Appconstr, CommandType.Text, sql);
|
|
return ds;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
#region 根据工单查询拣料表信息
|
|
public static DataSet GetGauge(String MOCode, String Appconstr, String where)
|
|
{
|
|
try
|
|
{
|
|
#region 20190917ZM
|
|
// string sql = @"SELECT c.MOCODE,
|
|
// c.MOBITEMCODE,
|
|
// c.CINVNAME,
|
|
// c.CINVTYPE,
|
|
// c.INVUOM,
|
|
// CAST(ISNULL(c.MOBITEMQTY, 0) AS DECIMAL(18,2)) AS MOBITEMQTY,
|
|
// CAST( ISNULL(e.QTYTotal, 0) AS DECIMAL(18,2)) QTYTotal,
|
|
// CAST(ISNULL(f.QTYXBC, 0) AS DECIMAL(18,2)) QTYXBC,
|
|
//CASE WHEN ISNULL(c.MOBITEMQTY, 0)-ISNULL(f.QTYXBC, 0) < 0 THEN '0' ELSE CAST(ISNULL(c.MOBITEMQTY, 0)-ISNULL(f.QTYXBC, 0) AS DECIMAL(18,2) ) END AS QTYNeed,
|
|
//d.WHCode,
|
|
// d.BinCode,
|
|
// d.LotNO,
|
|
// CAST( ISNULL(d.LotQty, 0)AS DECIMAL(18,2)) QTY,
|
|
// d.MTIME,
|
|
// g.VenderLotNO AS VenderLotNO,
|
|
// c.MOCODE,
|
|
// c.WorkPoint
|
|
// FROM
|
|
// (
|
|
// SELECT
|
|
// a.MOCODE,
|
|
// a.MOBITEMCODE,
|
|
// b.INVNAME AS CINVNAME,
|
|
// b.INVSTD AS CINVTYPE,
|
|
// b.INVUOM,
|
|
// b.INVMACHINETYPE,
|
|
// SUM(a.MOBITEMQTY) AS MOBITEMQTY,
|
|
// --SUM(a.MOBITEMQTY*e.MOPLANQTY) AS MOBITEMQTY,
|
|
// a.WorkPoint
|
|
// FROM ICSMOBOM a
|
|
// LEFT JOIN ICSINVENTORY b ON a.MOBITEMCODE = b.INVCODE AND a.WorkPoint=b.WorkPoint
|
|
// INNER JOIN ICSMO e ON a.MOCODE = e.MOCODE AND a.SEQ=e.MOSEQ AND a.WorkPoint=e.WorkPoint
|
|
// WHERE a.MOCODE='{0}'
|
|
// GROUP BY a.MOCODE,a.MOBITEMCODE,b.INVNAME,b.INVSTD,b.INVUOM,b.INVMACHINETYPE,a.WorkPoint
|
|
// )c
|
|
// LEFT JOIN ICSWareHouseLotInfo d ON c.MOBITEMCODE=d.INVCode AND d.WorkPoint='{1}' AND d.LotQty>0 AND WHCode IN
|
|
// (SELECT EnumText FROM Sys_EnumValues WHERE EnumKey = '00006' AND StartFlag = '1')
|
|
//
|
|
//LEFT JOIN ICSITEMLot g ON g.LotNO=d.LotNO
|
|
//
|
|
//LEFT JOIN (SELECT INVCode,SUM(LotQty) AS QTYTotal FROM ICSWareHouseLotInfo WHERE WorkPoint='{1}' GROUP BY INVCode) e ON c.MOBITEMCODE=e.INVCode
|
|
// LEFT JOIN (SELECT INVCode,WHCode,SUM(LotQty) AS QTYXBC FROM ICSWareHouseLotInfo WHERE WorkPoint='{1}' GROUP BY INVCode,WHCode) f ON c.MOBITEMCODE=f.INVCode AND c.INVMACHINETYPE=f.WHCode
|
|
// where c.WorkPoint = '{1}' AND {2}
|
|
// ORDER BY c.MOBITEMCODE,d.MTIME,d.LotNO
|
|
// ";
|
|
#endregion
|
|
|
|
#region 20191023ZM
|
|
|
|
string sql = @"SELECT c.MOCODE,
|
|
c.MOBITEMCODE,
|
|
c.CINVNAME,
|
|
c.CINVTYPE,
|
|
c.INVUOM,
|
|
CAST(ISNULL(c.MOBITEMQTY, 0) AS DECIMAL(18,2)) AS MOBITEMQTY,
|
|
CAST( ISNULL(e.QTYTotal, 0) AS DECIMAL(18,2)) QTYTotal,
|
|
CAST(ISNULL(f.QTYXBC, 0) AS DECIMAL(18,2)) QTYXBC,
|
|
CASE WHEN ISNULL(c.MOBITEMQTY, 0)-ISNULL(f.QTYXBC, 0) < 0 THEN '0' ELSE CAST(ISNULL(c.MOBITEMQTY, 0)-ISNULL(f.QTYXBC, 0) AS DECIMAL(18,2) ) END AS QTYNeed,
|
|
d.WHCode,
|
|
d.BinCode,
|
|
d.LotNO,
|
|
CAST( ISNULL(d.LotQty, 0)AS DECIMAL(18,2)) QTY,
|
|
d.MTIME,
|
|
g.VenderLotNO AS VenderLotNO,
|
|
c.MOCODE,
|
|
c.WorkPoint
|
|
FROM
|
|
(
|
|
SELECT
|
|
a.MOCODE,
|
|
a.MOBITEMCODE,
|
|
b.INVNAME AS CINVNAME,
|
|
b.INVSTD AS CINVTYPE,
|
|
b.INVUOM,
|
|
b.INVMACHINETYPE,
|
|
SUM(a.MOBITEMQTY) AS MOBITEMQTY,
|
|
--SUM(a.MOBITEMQTY*e.MOPLANQTY) AS MOBITEMQTY,
|
|
--e.FACTORY,
|
|
a.MOBITEMLOCATION AS FACTORY,
|
|
a.WorkPoint
|
|
FROM ICSMOBOM a
|
|
LEFT JOIN ICSINVENTORY b ON a.MOBITEMCODE = b.INVCODE AND a.WorkPoint=b.WorkPoint
|
|
INNER JOIN ICSMO e ON a.MOCODE = e.MOCODE AND a.SEQ=e.MOSEQ AND a.WorkPoint=e.WorkPoint
|
|
WHERE a.MOCODE='{0}'
|
|
GROUP BY a.MOCODE,a.MOBITEMCODE,b.INVNAME,b.INVSTD,b.INVUOM,b.INVMACHINETYPE,a.MOBITEMLOCATION,a.WorkPoint
|
|
)c
|
|
LEFT JOIN ICSWareHouseLotInfo d ON c.MOBITEMCODE=d.INVCode AND d.WHCode=c.FACTORY AND d.WorkPoint='{1}' AND d.LotQty>0
|
|
AND WHCode IN (SELECT EnumText FROM Sys_EnumValues WHERE EnumKey = '00008' AND StartFlag = '1')
|
|
|
|
LEFT JOIN ICSITEMLot g ON g.LotNO=d.LotNO
|
|
|
|
LEFT JOIN (SELECT INVCode,SUM(LotQty) AS QTYTotal FROM ICSWareHouseLotInfo WHERE WorkPoint='{1}' GROUP BY INVCode) e ON c.MOBITEMCODE=e.INVCode
|
|
LEFT JOIN (SELECT INVCode,WHCode,SUM(LotQty) AS QTYXBC FROM ICSWareHouseLotInfo WHERE WorkPoint='{1}' GROUP BY INVCode,WHCode) f ON c.MOBITEMCODE=f.INVCode AND c.INVMACHINETYPE=f.WHCode
|
|
where c.WorkPoint = '{1}' AND {2}
|
|
ORDER BY c.MOBITEMCODE,d.MTIME,d.LotNO
|
|
";
|
|
#endregion
|
|
|
|
|
|
sql = string.Format(sql, MOCode, AppConfig.WorkPointCode, where);
|
|
DataSet ds = DBHelper.ExecuteDataset(Appconstr, CommandType.Text, sql);
|
|
return ds;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
#endregion
|
|
}
|
|
}
|