public DataTable getLocalHostDt(string starTime, string stopTime, string org, string subinv, List <string> location)
{
string locations = "";
if (location.Count <= 0)
{
DataTable Nulldt = new DataTable();
return(Nulldt);
}
for (int i = 0; i < location.Count; i++)
{
locations = locations + location[i] + "','";
}
locations = locations.Substring(0, locations.Length - 2);
locations = "'" + locations;
string sql = @"SELECT
DATE_FORMAT( a.ScanTime, '%Y-%m-%d' ) AS ScanTime,
d.Buyer_Item,
d.color_code,
a.location,
CASE
WHEN t.AFTER != NULL THEN
t.AFTER ELSE d.Size1
END Size1 ,
CASE
WHEN (R.receiQty != 0 or R.receiQty != NULL) THEN R.receiQty + sum(d.qty )
ELSE sum( d.qty)
END size_qty
FROM
(
SELECT
A.id,
a.TagNumber,
a.cust_id,
a.location,
a.Update_Date,
a.org,
a.con_no,
a.Create_Pc,
a.kg,
a.subinv,
a.ScanTime
FROM
inv A,
(
SELECT
id,
min( ScanTime ) min_time
FROM
inv
WHERE
DATE_FORMAT(scantime , '%Y-%m-%d') BETWEEN STR_TO_DATE('" + starTime + @"','%Y-%m-%d')
AND STR_TO_DATE('" + stopTime + @"','%Y-%m-%d' )
AND org = '" + org + @"'
AND subinv = '" + subinv + @"'
AND location in (" + locations + @")
GROUP BY
ScanTime,
id
ORDER BY
ScanTime
) B
WHERE
A.id = B.id
AND A.ScanTime = B.min_time
) a
LEFT JOIN con_ppr p ON a.con_no = p.Serial_From
LEFT JOIN con_detail d ON a.con_no = d.Serial_From
AND p.PPrfNo = d.pprfno
LEFT JOIN transize t on d.Size1 = t.AFTER
OR d.Size1 = t.BEFORE
LEFT JOIN (
SELECT
(
SUM( qtyCount ) - sum( po )) AS receiQty,
org,
subinv,
line,
style,
color,
size,
receiDate
FROM
receis
GROUP BY
org,
subinv,
line,
style,
color,
size,
receiDate
) r ON R.org = A.org
AND R.subinv = A.subinv
AND R.line = A.location
AND R.style = D.Buyer_Item
AND R.color = D.color_code
AND R.size = D.Size1
AND R.receiDate = DATE_FORMAT( a.ScanTime, '%Y-%m-%d' )
GROUP BY
DATE_FORMAT( a.ScanTime, '%Y-%m-%d' ) ,
d.Buyer_Item,
d.color_code,
d.Size1
ORDER BY
DATE_FORMAT( a.ScanTime, '%Y-%m-%d' ) ,
a.con_no,
d.Size1 + 0;" ;
DataTable dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(dt);
}
public DataTable getReceis(receiSearch rs)
{
string locations = "";
string whereStr = "";
if (rs.location.Count > 0)
{
for (int i = 0; i < rs.location.Count; i++)
{
locations = locations + rs.location[i] + "','";
}
}
if (locations.Length > 0)
{
locations = locations.Substring(0, locations.Length - 2);
}
// 厂区
if (rs.org.Length > 0)
{
whereStr = whereStr + " AND org = '" + rs.org + "'";
}
// 仓库
if (rs.subinv.Length > 0)
{
whereStr = whereStr + " AND subinv = '" + rs.subinv + "'";
}
// 线别
if (locations.Length > 0)
{
whereStr = whereStr + " AND line in ( '" + locations + ")";
}
// 款式
if (rs.style.Length > 0)
{
whereStr = whereStr + " AND style = '" + rs.style + "'";
}
//颜色
if (rs.color.Length > 0)
{
whereStr = whereStr + " AND color = '" + rs.color + "'";
}
//PO
if (rs.poNumber.Length > 0)
{
whereStr = whereStr + " AND PO = '" + rs.poNumber + "'";
}
// 送货单 号
if (rs.ReceiNumber.Length > 0)
{
whereStr = whereStr + " AND receiNumber = '" + rs.ReceiNumber + "'";
}
if (rs.receiDate)
{
whereStr = whereStr + " AND DATE_FORMAT(receiInDate, '%Y-%m-%d') BETWEEN '" + rs.starTime + "' and '" + rs.stopTime + @"'";
}
// whereStr = whereStr.Substring(0, whereStr.Length - 1);
string sqlstr = @"
SELECT
org,
subinv,
line,
style,
color,
size,
SUM( qtyCount ) qtyCount
FROM
receis
WHERE isFull = 0 " + whereStr + @"
GROUP BY
org,
subinv,
line,
style,
color,
size" ;
DataTable dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sqlstr);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sqlstr);
}
return(dt);
}
//getMesworktagscansByinvoice
public DataTable getMesworktagscansSearch(string starDataTime, string stopDataTime, string receiptNumber, bool isCheckScanDate, string orgstr, string deptId)
{
string wherestr = "";
if (isCheckScanDate && receiptNumber.Length > 0)
{
wherestr = @" s.tagInvoice ='" + receiptNumber + @"'
and isInOrOut = 1
and tagScanDateTime BETWEEN '" + starDataTime + @"' and '" + stopDataTime + @"'
and tagOrg = '" + orgstr + @"'
and tagScanDeptID = " + deptId;
}
if (isCheckScanDate && receiptNumber.Length <= 0)
{
wherestr = @" s.tagInvoice !=''
and isInOrOut = 1
and tagScanDateTime BETWEEN '" + starDataTime + @"' and '" + stopDataTime + @"'
and tagOrg = '" + orgstr + @"'
and tagScanDeptID = " + deptId;
}
if (!isCheckScanDate && receiptNumber.Length > 0)
{
wherestr = @" s.tagInvoice ='" + receiptNumber + @"'
and isInOrOut = 1" ;
}
if (!isCheckScanDate && receiptNumber.Length <= 0)
{
wherestr = "";
}
string sql = @"
SELECT
s.tagInvoice,
s.tagStyle,
s.tagColor,
a.tagSize,
sum( s.tagQty ) tagQty,
s.taglocation,
d.DeptName,
s.tagOrg,
s.tagScanDeptID
FROM
mesworktagscans s
LEFT JOIN mesdepts d ON d.DeptNumber = s.tagScanDeptID
LEFT JOIN (
SELECT
group_concat( tagSize SEPARATOR ',' ) AS tagSize,
tagInvoice
FROM
(
SELECT DISTINCT
s.tagInvoice,
s.tagSize
FROM
mesworktagscans s
WHERE " + wherestr + @"
GROUP BY
s.tagSize,
s.tagInvoice
ORDER BY
s.tagSize
) a
GROUP BY
tagInvoice
) a ON a.tagInvoice = s.tagInvoice
WHERE " + wherestr + @"
GROUP BY
s.tagOrg,
s.tagInvoice,
s.taglocation,
d.DeptName,
s.tagScanDeptID,
s.tagStyle,
s.tagColor
ORDER BY
s.tagLocation,
s.tagOrg,
s.tagScanDeptID,
s.tagStyle,
s.tagColor" ;
DataTable result = new DataTable();
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(result);
}
public DataTable getDelByS(string starDate, string stopDate, string cust_id, string tagNumber, string org, int pageRows, int pages)
{
MyCatParameter[] mc = new MyCatParameter[7];
MySqlParameter[] ms = new MySqlParameter[7];
if (MiddleWare == "1")
{
mc[0] = new MyCatParameter("starDate", starDate);
mc[1] = new MyCatParameter("stopDate", stopDate);
mc[2] = new MyCatParameter("cust_id", cust_id);
mc[3] = new MyCatParameter("tagNumber", tagNumber);
mc[4] = new MyCatParameter("org", org);
mc[5] = new MyCatParameter("pageRows", pageRows);
mc[6] = new MyCatParameter("pages", pages);
}
else
{
ms[0] = new MySqlParameter("starDate", starDate);
ms[1] = new MySqlParameter("stopDate", stopDate);
ms[2] = new MySqlParameter("cust_id", cust_id);
ms[3] = new MySqlParameter("tagNumber", tagNumber);
ms[4] = new MySqlParameter("org", org);
ms[5] = new MySqlParameter("pageRows", pageRows);
ms[6] = new MySqlParameter("pages", pages);
};
// account ,processID, userName
string sql = @"SELECT
id,
TagNumber,
Cust_id,
Location,
org,
scantime,
con_no,
create_pc
FROM
inv
WHERE 1 = 1 and
IF ( @org != '', org = @org , 1 = 1 ) AND
IF ( @starDate != '', scantime BETWEEN @starDate AND @stopDate , 1 = 1 ) AND
IF (@cust_id != '', cust_id = @cust_id , 1 = 1 ) AND
IF (@tagNumber != '', tagNumber = @tagNumber , 1 = 1 )
ORDER BY
scantime,
tagnumber,
cust_id,
id
LIMIT " + (pages - 1) * pageRows + @",
" + pageRows + ";";
DataTable dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sql, mc);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sql, ms);
};
return(dt);
}
public DataTable getOutgoing(string org, string subinv, string location, string starTime, string stopTime)
{
string sql = @"
SELECT
DATE_FORMAT( a.ScanTime, '%Y-%m-%d' ) AS ScanTime,
a.cust_id,
d.Buyer_Item,
n.PONumber AS OrderPO,
g.po as GtnPO,
p.MAIN_LINE,
d.color_code ,
DATE_FORMAT( str_to_date(n.OGACDate, '%m/%d/%Y'), '%Y-%m-%d' ) AS OGACDate,
n.Plant,
a.con_no,
d.Size1,
d.qty size_qty,
cd.po_qty,
a.org,
a.subinv,
a.location,
a.TagNumber,
p.qty box_qty,
a.kg,
DATE_FORMAT( a.Update_Date, '%Y-%m-%d' ) AS Update_Date,
a.Create_Pc,
A.id
FROM
(
SELECT
A.id,
a.TagNumber,
a.cust_id,
a.location,
a.Update_Date,
a.org,
a.con_no,
a.Create_Pc,
a.kg,
a.subinv,
a.ScanTime
FROM
inv A,
(
SELECT
id,
min( ScanTime ) min_time
FROM
inv
WHERE
scantime BETWEEN STR_TO_DATE('" + starTime + @"','%Y-%m-%d')
AND STR_TO_DATE('" + stopTime + @"','%Y-%m-%d' )
AND org = '" + org + @"'
AND subinv = '" + subinv + @"'
AND location = '" + location + @"'
GROUP BY
ScanTime,
id
ORDER BY
ScanTime
) B
WHERE
A.id = B.id
AND A.ScanTime = B.min_time
) a
LEFT JOIN con_ppr p ON a.con_no = p.Serial_From
LEFT JOIN con_detail d ON a.con_no = d.Serial_From
AND p.PPrfNo = d.pprfno
LEFT JOIN ( SELECT SUM( qty ) AS po_qty, pprfno FROM con_detail GROUP BY pprfno ) cd ON cd.PPrfNo = p.pprfno
LEFT JOIN nikeconnect n ON n.TradingCompanyPO = p.PO
and n.POItem = p.MAIN_LINE
LEFT JOIN gtn_po g on g.GTN_PO = p.PO
ORDER BY a.con_no, d.Size1 + 0" ;
DataTable result = new DataTable();
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(result);
}
public DataTable getMesworktagscansByinvoiceGroup(string tagInvoice)
{
string sql = @"
SELECT
s.tagInvoice,
s.tagStyle,
s.tagColor,
SUBSTRING(s.tagNumber,-11,1) as part ,
a.tagSize,
a.tagQty,
s.taglocation,
d.DeptName,
s.tagOrg,
s.tagScanDeptID
FROM
mesworktagscans s
LEFT JOIN mesdepts d ON d.DeptNumber = s.tagScanDeptID
LEFT JOIN (
SELECT
group_concat( a.tagSize SEPARATOR ' , ' ) AS tagSize,
group_concat( a.tagQty SEPARATOR ' , ' ) AS tagQty,
tagInvoice
FROM
(
SELECT
s.tagSize,
sum( s.tagQty ) tagQty,
s.tagInvoice
FROM
mesworktagscans s
WHERE
s.tagInvoice = '" + tagInvoice + @"'
GROUP BY
s.tagInvoice,
s.tagInvoice,
s.tagSize
ORDER BY
s.tagInvoice,
s.tagSize
) a
) a ON a.tagInvoice = s.tagInvoice
WHERE
s.tagInvoice = '" + tagInvoice + @"'
GROUP BY
s.tagOrg,
s.tagInvoice,
s.taglocation,
d.DeptName,
s.tagScanDeptID,
s.tagStyle,
s.tagColor,
a.tagSize
ORDER BY
s.tagOrg,
s.tagScanDeptID,
s.tagStyle,
s.tagColor,
a.tagSize " ;
DataTable result = new DataTable();
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(result);
}
public DataTable getMesworktagscansByinvoice(string tagInvoice, string location)
{
string sql = "";
if (location == "%")
{
location = "";
sql = @"
SELECT
id,
isPrints,
tagOrg,
tagScanDeptID,
tagLine,
tagLocation,
tagStyle,
tagColor,
tagSize,
tagQty,
tagScanAccount,
tagNumber,
tagInvoice,
tagScanDateTime,
tagUploadDateTime,
tagScanPDASerial,
isUploaded,
isSyncMesData,
isDels
FROM
mesworktagscans
WHERE
isInOrOut = 1
and tagInvoice like '" + tagInvoice + "%'" + @"
and tagLocation = '" + location + "'" + @"
ORDER BY id" ;
}
else if (location == "")
{
sql = @"
SELECT
id,
isPrints,
tagOrg,
tagScanDeptID,
tagLine,
tagLocation,
tagStyle,
tagColor,
tagSize,
tagQty,
tagScanAccount,
tagNumber,
tagInvoice,
tagScanDateTime,
tagUploadDateTime,
tagScanPDASerial,
isUploaded,
isSyncMesData,
isDels
FROM
mesworktagscans
WHERE
isInOrOut = 1
and tagInvoice like '" + tagInvoice + "%'" + @"
ORDER BY id" ;
}
else
{
sql = @"
SELECT
id,
isPrints,
tagOrg,
tagScanDeptID,
tagLine,
tagLocation,
tagStyle,
tagColor,
tagSize,
tagQty,
tagScanAccount,
tagNumber,
tagInvoice,
tagScanDateTime,
tagUploadDateTime,
tagScanPDASerial,
isUploaded,
isSyncMesData,
isDels
FROM
mesworktagscans
WHERE
isInOrOut = 1
and tagInvoice like '" + tagInvoice + "%'" + @"
and tagLocation = '" + location + "'" + @"
ORDER BY id" ;
}
DataTable result = new DataTable();
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(result);
}
public DataTable getInventoryByStylePO(List <string> styles, List <List <string> > spo)
{
if (styles.Count <= 0 || spo.Count <= 0)
{
return(null);
}
string buyerItems = "";
string PPrfNobuyerItems = "";
string po = "";
for (int i = 0; i < styles.Count; i++)
{
string stylePos = "";
if (spo[i].Count > 0)
{
for (int j = 0; j < spo[i].Count; j++)
{
stylePos = stylePos + ",'" + spo[i][j] + "'";
}
stylePos = stylePos.Substring(1);
}
po = po + @" (d.Buyer_Item = '" + styles[i] + @"' and c.PO in (" + stylePos + @")) or ";
buyerItems = buyerItems + @"SELECT
org,
Subinv,
con_no,
TagNumber,
Cust_id,
location,
scantime
FROM
(
SELECT
c.Serial_From
FROM
(SELECT PPrfNo FROM con_detail WHERE Buyer_Item = '" + styles[i] + @"' GROUP BY PPrfNo) AS temp,
con_ppr c
WHERE
c.PPrfNo = temp.PPrfNo
and c.po in (" + stylePos + @")
GROUP BY
c.org,
c.Cust_id,
c.Serial_From,
c.PPrfNo,
c.po,
c.MAIN_LINE,
c.con_no
) temp,
inv i
WHERE
i.con_no = temp.Serial_From UNION ALL " ;
PPrfNobuyerItems = PPrfNobuyerItems + @"SELECT
PPrfNo
FROM
con_detail
WHERE
Buyer_Item = '" + styles[i] + @"'
GROUP BY
PPrfNo UNION ALL " ;
}
// for (int i = 0; i < pos.Count; i++)
// {
// po = po + @"'" + pos[i] + "',";
// }
buyerItems = buyerItems.Substring(0, buyerItems.Length - 12);
PPrfNobuyerItems = PPrfNobuyerItems.Substring(0, PPrfNobuyerItems.Length - 12);
// po = po.Substring(0, po.Length - 1);
// po = po + @" (d.Buyer_Item = '" + styles[i] + @"' and c.PO in (" + stylePos + @")) or ";
po = po.Substring(0, po.Length - 4);
string sql = @"
SELECT
c.cust_id,
c.org,
c.po,
c.main_line,
c.serial_from,
c.con_no,
c.qty,
c.pprfno,
a.con_no scanBoxs,
i.id,
i.subinv,
i.location,
d.Buyer_Item,
d.color_code
FROM
con_ppr c
LEFT JOIN (
SELECT
max( a.scantime ) scantime,
con_no
FROM
( " + buyerItems + @" ) a
GROUP BY
a.con_no
) a ON a.con_no = c.serial_from
LEFT JOIN inv i ON i.scantime = a.scantime
AND i.con_no = a.con_no
LEFT JOIN con_detail d ON d.pprfno = c.pprfno
AND d.serial_from = c.serial_from
WHERE
c.PPrfNo IN (
SELECT
a.PPrfNo
FROM
(" + PPrfNobuyerItems + @") a
GROUP BY
a.PPrfNo
)
and c.qty > 0
AND ( " + po + @" )
GROUP BY
c.org,
c.Cust_id,
c.Serial_From,
c.PPrfNo,
c.po,
c.MAIN_LINE,
c.con_no,
d.color_code,
i.subinv,
i.location,
d.Buyer_Item" ;
DataTable dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(dt);
}
public DataTable getMesWorktagScans(List <CompletedSearch> parameters)
{
string sql = @"SELECT
s.ID,
s.tagOrg,
d.DeptName,
s.tagLine,
s.tagLocation,
s.tagNumber,
s.tagStyle,
tagColor,
s.tagSize,
s.tagQty,
s.tagScanAccount,
s.tagScanDateTime,
s.tagScanPDAUUID,
CASE
s.isInOrOut
WHEN 0 THEN
'IN' ELSE 'OUT'
END AS isInOrOut,
s.tagInvoice,
s.tagReceiptNumber,
s.isPrints
FROM
mesworktagscans s
LEFT JOIN mesdepts d ON s.tagScanDeptID = d.DeptNumber
WHERE 1 = 1 and
if (@tagOrg = 'SAA',tagNumber like 'A%', 0 = 0 ) and
if (@tagOrg = 'TOP',tagNumber like 'T%', 0 = 0 ) and
if (@tagScanDeptID = '',0 = 0, tagScanDeptID = @tagScanDeptID ) and
if (@tagLocation = '',0 = 0, tagLocation = @tagLocation ) and
if (@isCheckDate = 'False',0 = 0, tagScanDateTime BETWEEN @starDate AND @stopDate) and
if (@searchType = '0' , isInOrOut = @searchType ,0 = 0 ) and
if (@searchType = '1', isInOrOut = @searchType ,0 = 0) and
if (@searchType = '-1', isDels != @searchType and isInOrOut = 0 , 0 = 0 ) and
if (@searchType = '' , 0 = 0, 0 = 0 )
ORDER BY Id" ;
string tagOrg = "";
string tagScanDeptID = "";
string tagLocation = "";
string isCheckDate = "";
string starDate = "";
string stopDate = "";
string searchType = "";
foreach (CompletedSearch item in parameters)
{
switch (item.key)
{
case "org":
tagOrg = item.value;
break;
case "dept":
tagScanDeptID = item.value;
break;
case "location":
tagLocation = item.value;
break;
case "starDate":
starDate = item.value;
break;
case "stopDate":
stopDate = item.value;
break;
case "isCheckDate":
isCheckDate = item.value;
break;
case "searchType":
searchType = item.value;
break;
}
}
DataTable users = new DataTable();
if (MiddleWare == "1")
{
MyCatParameter[] p =
{
new MyCatParameter("tagOrg", tagOrg),
new MyCatParameter("tagScanDeptID", tagScanDeptID),
new MyCatParameter("tagLocation", tagLocation),
new MyCatParameter("isCheckDate", isCheckDate),
new MyCatParameter("starDate", starDate),
new MyCatParameter("stopDate", stopDate),
new MyCatParameter("searchType", searchType)
};
users = MyCatfsg_SqlHelper.ExcuteTable(sql, p);
}
else
{
MySqlParameter[] p =
{
new MySqlParameter("tagOrg", tagOrg),
new MySqlParameter("tagScanDeptID", tagScanDeptID),
new MySqlParameter("tagLocation", tagLocation),
new MySqlParameter("isCheckDate", isCheckDate),
new MySqlParameter("starDate", starDate),
new MySqlParameter("stopDate", stopDate),
new MySqlParameter("searchType", searchType)
};
users = Mysqlfsg_SqlHelper.ExcuteTable(sql, p);
}
return(users);
}
public DataTable getCFoutPut(CFOutput cfoutput)
{
int searchType = cfoutput.searchType;
string org = cfoutput.org;
string subinv = cfoutput.subinv;
string style = cfoutput.style;
string startDate = cfoutput.starDate;
string stopDate = cfoutput.stopDate;
bool checkDate = cfoutput.checkDate;
string sql = "";
DataTable dt = new DataTable();
if (searchType == 0)
{
if (!checkDate && style.Length > 0)
{
sql = @"
SELECT shb.SHB02 AS CreateDate, occ.occ02 as custName,sfb.TA_SFB01 AS myNumber,shb.shb10 as style ,shb.shb09 as line ,
sfb.sfb08 as OrderQty,sum(shb.shb111) as qty ,
oea.TA_OEA01 as season,oea.ta_oea02 as buy,
ima.ima02 as name
FROM " + org + @".SHB_FILE shb
LEFT JOIN " + org + @".SFB_FILE sfb on shb.shb05 = sfb.sfb01
LEFT JOIN " + org + @".OEA_FILE oea on oea.oea01 = sfb.sfb22
LEFT JOIN " + org + @".OCC_FILE occ on occ.occ07 = sfb.sfb223
LEFT JOIN " + org + @".IMA_FILE ima on ima.ima01 = shb.shb10
WHERE 1 = 1
and sfb.sfb05 ='" + cfoutput.style + @"'
and shb.shb081 ='G022'
and shb.SHBCONF ='Y'
GROUP BY shb.SHB02, shb.shb05 ,shb.shb081 ,shb.shb10 ,shb.shb09 ,shb.shb07 ,
sfb.TA_SFB01 ,sfb.sfb05 ,sfb.sfb08 ,sfb.sfb22 ,sfb.sfb223 ,
oea.TA_OEA01 ,oea.ta_oea02 ,
occ.occ02 ,
ima.ima02
ORDER BY shb.SHB02 ,shb.shb09,oea.ta_oea02 ,sfb.sfb05,sfb.sfb22";
}
if (checkDate && style.Length > 0)
{
sql = @"
SELECT shb.SHB02 AS CreateDate, occ.occ02 as custName,sfb.TA_SFB01 AS myNumber,shb.shb10 as style ,shb.shb09 as line ,
sfb.sfb08 as OrderQty,sum(shb.shb111) as qty ,
oea.TA_OEA01 as season,oea.ta_oea02 as buy,
ima.ima02 as name
FROM " + org + @".SHB_FILE shb
LEFT JOIN " + org + @".SFB_FILE sfb on shb.shb05 = sfb.sfb01
LEFT JOIN " + org + @".OEA_FILE oea on oea.oea01 = sfb.sfb22
LEFT JOIN " + org + @".OCC_FILE occ on occ.occ07 = sfb.sfb223
LEFT JOIN " + org + @".IMA_FILE ima on ima.ima01 = shb.shb10
WHERE 1 = 1
and sfb.sfb05 ='" + cfoutput.style + @"'
and shb.SHB02 BETWEEN TO_DATE('" + startDate + @"', 'yyyy-MM-dd') and TO_DATE('" + stopDate + @"', 'yyyy-MM-dd')
and shb.shb081 ='G022'
and shb.SHBCONF ='Y'
GROUP BY shb.SHB02, shb.shb05 ,shb.shb081 ,shb.shb10 ,shb.shb09 ,shb.shb07 ,
sfb.TA_SFB01 ,sfb.sfb05 ,sfb.sfb08 ,sfb.sfb22 ,sfb.sfb223 ,
oea.TA_OEA01 ,oea.ta_oea02 ,
occ.occ02 ,
ima.ima02
ORDER BY shb.SHB02 ,shb.shb09,oea.ta_oea02 ,sfb.sfb05,sfb.sfb22";
}
if (checkDate && style.Length <= 0)
{
sql = @"
SELECT shb.SHB02 AS CreateDate, occ.occ02 as custName,sfb.TA_SFB01 AS myNumber,shb.shb10 as style ,shb.shb09 as line ,
sfb.sfb08 as OrderQty,sum(shb.shb111) as qty ,
oea.TA_OEA01 as season,oea.ta_oea02 as buy,
ima.ima02 as name
FROM " + org + @".SHB_FILE shb
LEFT JOIN " + org + @".SFB_FILE sfb on shb.shb05 = sfb.sfb01
LEFT JOIN " + org + @".OEA_FILE oea on oea.oea01 = sfb.sfb22
LEFT JOIN " + org + @".OCC_FILE occ on occ.occ07 = sfb.sfb223
LEFT JOIN " + org + @".IMA_FILE ima on ima.ima01 = shb.shb10
WHERE 1 = 1
and shb.SHB02 BETWEEN TO_DATE('" + startDate + @"', 'yyyy-MM-dd') and TO_DATE('" + stopDate + @"', 'yyyy-MM-dd')
and shb.shb081 ='G022'
and shb.SHBCONF ='Y'
GROUP BY shb.SHB02, shb.shb05 ,shb.shb081 ,shb.shb10 ,shb.shb09 ,shb.shb07 ,
sfb.TA_SFB01 ,sfb.sfb05 ,sfb.sfb08 ,sfb.sfb22 ,sfb.sfb223 ,
oea.TA_OEA01 ,oea.ta_oea02 ,
occ.occ02 ,
ima.ima02
ORDER BY shb.SHB02 ,shb.shb09,oea.ta_oea02 ,sfb.sfb05,sfb.sfb22";
}
dt = ERP_SqlHelper.ExcuteTable(sql);
}
else if (searchType == 1)
{
if (checkDate && style.Length > 0)
{
sql = @"
SELECT i.org,DATE_FORMAT(i.scantime, '%y-%m-%d') scantime ,i.Cust_id, c.PO,c.MAIN_LINE,
d.Buyer_Item,d.color_code,d.Size1 , CASE WHEN d.qty is null THEN 0 ELSE sum(d.qty) END qty ,
d.Item_desc,d.pprfno , i.location
from inv i , (
SELECT con_no,min(ScanTime) ScanTime FROM inv i WHERE con_no in (SELECT con_no FROM inv i WHERE
i.scantime BETWEEN '" + startDate + @"' and '" + stopDate + @"'
and i.org ='" + org + @"'
and (i.subinv ='" + subinv + @"' AND i.location ='HD' )
GROUP BY i.con_no ) GROUP BY i.con_no ) a
left join con_ppr c on a.con_no =c.Serial_From
left join con_detail d on a.con_no = d.Serial_From
WHERE i.con_no=a. con_no and i.ScanTime=a.ScanTime
and i.org ='" + org + @"'
and a.scantime BETWEEN '" + startDate + @"' and '" + stopDate + @"'
and (i.subinv ='" + subinv + @"' AND i.location ='HD' )
and d.Buyer_Item ='" + style + @"'
and d.qty != 0
GROUP BY DATE_FORMAT(i.scantime, '%y-%m-%d') ,i.org,i.Cust_id , i.location,
d.Buyer_Item,d.Item_desc,d.color_code,d.Size1 ,
c.PO,c.MAIN_LINE,
d.pprfno
ORDER BY DATE_FORMAT(i.scantime, '%y-%m-%d'),d.Buyer_Item ,d.color_code,d.Size1 ,c.PO,c.MAIN_LINE,i.Cust_id,i.Location,d.pprfno ;
";
}
else if (checkDate && style.Length <= 0)
{
sql = @"
SELECT i.org,DATE_FORMAT(i.scantime, '%y-%m-%d') scantime ,i.Cust_id, c.PO,c.MAIN_LINE,
d.Buyer_Item,d.color_code,d.Size1 , CASE WHEN d.qty is null THEN 0 ELSE sum(d.qty) END qty ,
d.Item_desc,d.pprfno , i.location
from inv i , (
SELECT con_no,min(ScanTime) ScanTime FROM inv i WHERE con_no in (SELECT con_no FROM inv i WHERE
i.scantime BETWEEN '" + startDate + @"' and '" + stopDate + @"'
and i.org ='" + org + @"'
and (i.subinv ='" + subinv + @"' AND i.location ='HD' )
GROUP BY i.con_no ) GROUP BY i.con_no ) a
left join con_ppr c on a.con_no =c.Serial_From
left join con_detail d on a.con_no = d.Serial_From
WHERE i.con_no=a. con_no and i.ScanTime=a.ScanTime
and i.org ='" + org + @"'
and a.scantime BETWEEN '" + startDate + @"' and '" + stopDate + @"'
and (i.subinv ='" + subinv + @"' AND i.location ='HD' )
and d.qty != 0
GROUP BY DATE_FORMAT(i.scantime, '%y-%m-%d') ,i.org,i.Cust_id , i.location,
d.Buyer_Item,d.Item_desc,d.color_code,d.Size1 ,
c.PO,c.MAIN_LINE,
d.pprfno
ORDER BY DATE_FORMAT(i.scantime, '%y-%m-%d'),d.Buyer_Item ,d.color_code,d.Size1 ,c.PO,c.MAIN_LINE,i.Cust_id,i.Location,d.pprfno ;
";
}
dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
}
else if (searchType == 2)
{
if (checkDate && style.Length > 0)
{
sql = @"
SELECT i.org,DATE_FORMAT(i.scantime, '%y-%m-%d') scantime ,i.Cust_id, c.PO,c.MAIN_LINE,
d.Buyer_Item,d.color_code,d.Size1 , CASE WHEN d.qty is null THEN 0 ELSE sum(d.qty) END qty ,
d.Item_desc,d.pprfno , i.location
from inv i , (
SELECT con_no,max(ScanTime) ScanTime FROM inv i WHERE con_no in (SELECT con_no FROM inv i WHERE
i.scantime BETWEEN '" + startDate + @"' and '" + stopDate + @"'
and i.org ='" + org + @"'
and (i.subinv ='" + subinv + @"' AND i.location !='GD' )
GROUP BY i.con_no ) GROUP BY i.con_no ) a
left join con_ppr c on a.con_no =c.Serial_From
left join con_detail d on a.con_no = d.Serial_From
WHERE i.con_no=a. con_no and i.ScanTime=a.ScanTime
and i.org ='" + org + @"'
and a.scantime BETWEEN '" + startDate + @"' and '" + stopDate + @"'
and (i.subinv ='" + subinv + @"' AND i.location !='GD' )
and d.Buyer_Item ='" + style + @"'
and d.qty != 0
GROUP BY DATE_FORMAT(i.scantime, '%y-%m-%d') ,i.org,i.Cust_id , i.location,
d.Buyer_Item,d.Item_desc,d.color_code,d.Size1 ,
c.PO,c.MAIN_LINE,
d.pprfno
ORDER BY DATE_FORMAT(i.scantime, '%y-%m-%d'),d.Buyer_Item ,d.color_code,d.Size1 ,c.PO,c.MAIN_LINE,i.Cust_id,i.Location,d.pprfno ;
";
}
else if (checkDate && style.Length <= 0)
{
sql = @"
SELECT i.org,DATE_FORMAT(i.scantime, '%y-%m-%d') scantime ,i.Cust_id, c.PO,c.MAIN_LINE,
d.Buyer_Item,d.color_code,d.Size1 , CASE WHEN d.qty is null THEN 0 ELSE sum(d.qty) END qty ,
d.Item_desc,d.pprfno , i.location
from inv i , (
SELECT con_no,max(ScanTime) ScanTime FROM inv i WHERE con_no in (SELECT con_no FROM inv i WHERE
i.scantime BETWEEN '" + startDate + @"' and '" + stopDate + @"'
and i.org ='" + org + @"'
and (i.subinv ='" + subinv + @"' AND i.location !='GD' )
GROUP BY i.con_no ) GROUP BY i.con_no ) a
left join con_ppr c on a.con_no =c.Serial_From
left join con_detail d on a.con_no = d.Serial_From
WHERE i.con_no=a. con_no and i.ScanTime=a.ScanTime
and i.org ='" + org + @"'
and a.scantime BETWEEN '" + startDate + @"' and '" + stopDate + @"'
and (i.subinv ='" + subinv + @"' AND i.location !='GD' )
and d.qty != 0
GROUP BY DATE_FORMAT(i.scantime, '%y-%m-%d') ,i.org,i.Cust_id , i.location,
d.Buyer_Item,d.Item_desc,d.color_code,d.Size1 ,
c.PO,c.MAIN_LINE,
d.pprfno
ORDER BY DATE_FORMAT(i.scantime, '%y-%m-%d'),d.Buyer_Item ,d.color_code,d.Size1 ,c.PO,c.MAIN_LINE,i.Cust_id,i.Location,d.pprfno ;
";
}
dt = new DataTable();
if (MiddleWare == "1")
{
dt = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
dt = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
}
return(dt);
}
public DataTable getScanByQuery(string org, string subinv, string location, string startDate, string stopDate, string styleCode, string colorCode)
{
string sql = @"SELECT i.ORG,
i.Cust_id,
i.subinv,
i.Location,
d.Buyer_Item,
p.po,
p.MAIN_LINE,
d.color_code,
d.Size1,
d.QTY,
i.con_no,
b.kg,
i.TagNumber,
DATE_FORMAT( i.scantime,'%Y-%m-%d %H:%m:%S') scantime ,
DATE_FORMAT( i.update_date,'%Y-%m-%d %T') update_date ,
i.create_pc
FROM inv i
LEFT JOIN (
SELECT max(kg) kg ,
TagNumber
FROM inv
WHERE ORG = '" + org + @"'
AND subinv = '" + subinv + @"'
AND update_date BETWEEN '" + startDate + @"'
AND '" + stopDate + @"'
AND kg IS NOT NULL
GROUP BY TagNumber
) b ON b.TagNumber = i.TagNumber
left join con_ppr p on p.Serial_From = i.con_no and p.Cust_id = i.Cust_id
left join con_detail d on d.Serial_From = i.con_no and d.Cust_id = i.Cust_id
WHERE i.ORG = '" + org + @"'
AND i.subinv ='" + subinv + @"'
AND i.Location ='" + location + @"'
AND i.update_date BETWEEN '" + startDate + @"'
AND '" + stopDate + @"'
and d.Buyer_Item like '%" + styleCode + @"%'
and d.color_code like '%" + colorCode + @"%'
GROUP BY i.TagNumber,
i.Cust_id,
i.Location,
i.ORG,
i.con_no,
i.create_pc,
i.subinv,
b.KG,
p.qty,
p.po,
p.MAIN_LINE,
p.Cust_id,
d.Buyer_Item,
d.color_code,
d.Size1,
d.QTY
order by org, Cust_id, subinv, Location, Buyer_Item, PO, MAIN_LINE,
color_code, Size1, con_no, scantime;";
DataTable result = new DataTable();
if (MiddleWare == "1")
{
result = MyCatfsg_SqlHelper.ExcuteTable(sql);
}
else
{
result = Mysqlfsg_SqlHelper.ExcuteTable(sql);
}
return(result);
}