public DataTable getMesWorkTicketByMynos(List <string> my_nos, string serviceName)
{
string my_no = "";
foreach (string my in my_nos)
{
my_no = my_no + "'" + my + "',";
}
if (my_no.Length <= 0)
{
return(new DataTable());
}
my_no = my_no.Substring(0, my_no.Length - 1);
string sql = @"SELECT a.orderSKU,
a.productModel,
a.colorName,
a.partName,
SUM(a.OrderQTY) WLInStock,
CASE
WHEN SUM(a.FinishQty) IS NULL THEN
0
ELSE
SUM(a.FinishQty)
END WLOutStock,
CASE
WHEN a.ProcessID IS NULL THEN
'-1'
ELSE
a.ProcessID
END ProcessID,
CASE
WHEN a.ProcessName IS NULL THEN
''
ELSE
a.ProcessName
END ProcessName,
CASE
WHEN a.LineName IS NULL THEN
''
ELSE
a.LineName
END LineName,
CASE
WHEN a.LineID IS NULL THEN
'-1'
ELSE
a.LineID
END receiveLineID,
CASE
WHEN a.ReportPlaceID IS NULL THEN
'-1'
ELSE
a.ReportPlaceID
END ReportPlaceID,
CASE
WHEN a.ReportPlaceName IS NULL THEN
''
ELSE
a.ReportPlaceName
END ReportPlaceName
FROM
(
SELECT a.orderSKU,
a.productModel,
b.colorName,
b.partName,
SUM(m2.QTY) OrderQTY,
SUM(r.QTY) FinishQty,
r.ProcessID,
r.ProcessName,
r.LineName,
r.LineID,
r.ReportPlaceID,
r.ReportPlaceName
FROM t_ManufactureOrderList a
INNER JOIN t_ManufactureOrderDetails b
ON a.MOLID = b.MOLID
LEFT JOIN t_ManufactureOrderDetails2 m2
ON m2.MODID = b.MODID
LEFT JOIN t_ReportOutputDetail r
ON r.MOD2ID = m2.MOD2ID
AND r.TypeID = 1
AND r.ProcessID = 10
WHERE a.orderSKU IN ( " + my_no + @" )
AND b.partName = 'A'
AND a.Invalid = 0
GROUP BY a.orderSKU,
a.productModel,
b.colorName,
b.partName,
r.ProcessID,
r.ProcessName,
r.LineName,
r.LineID,
r.ReportPlaceID,
r.ReportPlaceName
) a
GROUP BY a.orderSKU,
a.productModel,
a.colorName,
a.partName,
a.ProcessID,
a.ProcessName,
a.ReportPlaceID,
a.LineName,
a.LineID,
a.ReportPlaceName
ORDER BY a.orderSKU,
a.productModel,
a.colorName,
a.ReportPlaceID";
DataTable dt = Mes_SqlHelper.ExcuteTable(sql, serviceName);
return(dt);
}
public List <CompeleteMes> getMesScanData(string style, string myNumber)
{
string sql = @"SELECT a.orderSKU,
tbb.my_no,
a.productModel,
b.partName,
SUM(b.SIZ_MULTIPLIER * b.Actual_HEIGHT) QTY,
convert(varchar(11), a.sysAddTime, 23) AS sysAddTime
FROM t_ManufactureOrderList a
INNER JOIN t_ManufactureOrderDetails b
ON a.MOLID = b.MOLID
LEFT JOIN
(
SELECT orderSKU,
my_no,
style_id
FROM dbo.t_BestBand
WHERE orderSKU LIKE '%" + myNumber + @"%'
AND style_id LIKE '%" + style + @"%'
GROUP BY orderSKU,
my_no,
style_id
) tbb
ON tbb.orderSKU = a.orderSKU
AND tbb.style_id = a.productModel
WHERE
b.partName = 'A'
AND a.orderSKU LIKE '%" + myNumber + @"%'
AND a.productModel LIKE '%" + style + @"%'
GROUP BY a.orderSKU,
tbb.my_no,
a.productModel,
b.partName,
convert(varchar(11), a.sysAddTime, 23)
ORDER BY
a.orderSKU,
tbb.my_no,
sysAddTime; " ;
List <CompeleteMes> lists = new List <CompeleteMes>();
DataTable SAAdt = Mes_SqlHelper.ExcuteTable(sql, "SAA");
if (SAAdt.Rows.Count > 0)
{
foreach (DataRow row in SAAdt.Rows)
{
CompeleteMes c = new CompeleteMes();
cMes(row, c);
lists.Add(c);
}
}
DataTable TOPdt = Mes_SqlHelper.ExcuteTable(sql, "TOP");
if (SAAdt.Rows.Count > 0)
{
foreach (DataRow row in TOPdt.Rows)
{
CompeleteMes c = new CompeleteMes();
cMes(row, c);
lists.Add(c);
}
}
return(lists);
}