public void cERP(DataRow dr, MODEL.CompeleteERP list)
{
list.org = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["org"]));
list.myNumber = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["myNumber"]));
list.orderID = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["orderID"]));
list.moveOrderID = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["moveOrderID"]));
list.finishID = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["finishID"]));
list.custID = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["custID"]));
list.custName = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["custName"]));
list.Style = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["Style"]));
list.taskProcessName = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["taskProcessName"]));
list.OrderQty = Convert.ToInt32(ERP_SqlHelper.FromDbValue(dr["OrderQty"]));
list.makeQty = Convert.ToInt32(ERP_SqlHelper.FromDbValue(dr["makeQty"]));
list.FinishDate = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FinishDate"]));
list.emploreeID = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["emploreeID"]));
list.caption = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["caption"]));
list.ProcessID = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["ProcessID"]));
list.processName = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["processName"]));
list.lineID = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["lineID"]));
list.WorkID = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["WorkID"]));
list.workMachineID = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["workMachineID"]));
list.finishQty = Convert.ToInt32(ERP_SqlHelper.FromDbValue(dr["finishQty"]));
list.BonusQty = Convert.ToInt32(ERP_SqlHelper.FromDbValue(dr["BonusQty"]));
list.checkedID = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["checkedID"]));
}
public void cMes(DataRow dr, MODEL.CompeleteMes list)
{
list.orderSKU = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["orderSKU"]));
list.my_no = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["my_no"]));
list.productModel = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["productModel"]));
list.partName = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["partName"]));
list.QTY = Convert.ToInt32(ERP_SqlHelper.FromDbValue(dr["QTY"]));
list.sysAddTime = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["sysAddTime"]));
}
public DataTable getPropertysByPnumber(string org, string propertyIDs)
{
string[] propertyID = propertyIDs.Split('|');
string wherestr = "";
if (propertyID.Length > 0)
{
for (int i = 0; i < propertyID.Length; i++)
{
//('AE9O-0184','AE9O-0183','AE9O-0189')
wherestr = wherestr + "'" + propertyID[i] + "',";
}
}
wherestr = wherestr.Remove(wherestr.Length - 1, 1);
string sql = @" SELECT
FAJ.FAJ01 erpid,
FAJ.FAJ02 propertyID,
FAJ.FAJ22 org,
FAJ.FAJ06 propertyName,
FAJ.FAJ08 propertyMode,
FAJ.FAJ93 propertyType,
TO_CHAR(FAJ.FAJ25,'yyyy-mm-dd hh24:mi:ss') buyDate,
FAJ.FAJ20 propertyDept,
FAJ.FAJ21,
FAJ.FAJ47 propertyBuyID,
FAJ.FAJ19,
FAJ.FAJ18 propertyUnit,
FAF.FAF02,
GEN.GEN02
FROM
" + org + @".FAJ_FILE FAJ
LEFT JOIN " + org + @".FAF_FILE FAF ON FAJ.FAJ21 = FAF.FAF01
LEFT JOIN " + org + @".GEN_FILE GEN ON FAJ.FAJ19 = GEN.GEN01
WHERE
FAJ.FAJ02 IN ( " + wherestr + ")";
DataTable dt = ERP_SqlHelper.ExcuteTable(sql);
return(dt);
/*
* List<propertys> lists = null;
* if (dt.Rows.Count > 0)
* {
* lists = new List<propertys>();
* foreach (DataRow row in dt.Rows)
* {
* MODEL.propertys c = new MODEL.propertys();
* propertys(row, c);
* lists.Add(c);
* }
* }
* return lists;
*/
}
public void propertys(DataRow dr, MODEL.propertys list)
{
list.id = 0; // 索引
list.erpid = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FAJ01"])); // ERP索引
list.org = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FAJ22"])); // 厂别
list.propertyID = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FAJ02"])); // 财编
list.propertyName = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FAJ06"])); // 资产名称
list.propertyMode = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FAJ08"])); // 资产型号
list.propertyType = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FAJ93"])); // 固资分类
// DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss")
list.buyDate = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FAJ25"])); // 购入日期
list.propertyDept = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FAJ20"])); // 资产归属部门
list.propertyLocal = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FAJ21"])) + "-" + Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FAF02"])); // 资产存放位置
list.propertyBuyID = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FAJ47"])); // 资产采购单号
list.propertySavePerson = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FAJ19"])) + "-" + Convert.ToString(ERP_SqlHelper.FromDbValue(dr["GEN02"])); // 资产保管人
list.propertyUnit = Convert.ToString(ERP_SqlHelper.FromDbValue(dr["FAJ18"])); // 资产单位
list.propertyPrintTims = 0; // 财编打印次数
list.propertyPrintPC = ""; // 资产建立者PC名
list.propertyIsDel = 0; // 删除标记
list.propertyDelPC = ""; // 删除者PC
list.propertyDelDate = ""; // 删除日期
list.propertyDelNote = ""; // 删除备注
}
public DataTable getERPWorkTicketByMynos(List <string> my_nos, string serviceName)
{
string my_no = "";
foreach (string my in my_nos)
{
my_no = my_no + "'" + my + "',";
}
my_no = my_no.Substring(0, my_no.Length - 1);
string sql = @"SELECT sf.TA_SFB01 AS myNo,
sf.SFB01 AS MakeOrderNo,
sf.sfb05 AS StyleName,
sf.sfb22 AS OrderNo ,
to_char(sf.sfb071, 'YYYY-MM-DD') CreateDate,
sf.sfb08 AS MakeQty,
sf.sfb09 AS ProduFinishQty,
to_char(sf.sfb13, 'YYYY-MM-DD') PlanMakeDate,
sf.sfb87 AS Makechecked,
sf.sfbud04 AS OrgName,
sf.sfb223 AS CustName,
to_char(min(sh.shb02), 'YYYY-MM-DD') FinishDate,
sh.SHB05 AS FinishMakeOrderNo,
sh.SHB06 AS ProcessTypeID,
sh.shb08 AS OrderClass,
sh.shb09 AS MakeLine,
sh.shb081 AS ProcessType,
sh.shb082 AS ProcessName,
sh.shb10 AS StyleName,
sum(sh.shb111) AS CFFinishQty,
sum(sh.shb115) AS CFFinishBonus,
sh.shbacti AS FinishChecked,
sh.shbplant AS MakeOrgName
FROM
" + serviceName + @".SFB_FILE sf
LEFT JOIN " + serviceName + @".SHB_FILE sh ON
sf.SFB01 = sh.shb05
WHERE
sf.TA_SFB01 in(" + my_no + @")
AND sf.sfb87 = 'Y'
AND sh.shbacti = 'Y'
AND sh.shb06 = 55
GROUP BY
sf.TA_SFB01 ,
sf.SFB01 ,
sf.sfb05 ,
sf.sfb22 ,
sf.sfb071,
sf.sfb08 ,
sf.sfb09,
sf.sfb13,
sf.sfb87 ,
sf.sfbud04 ,
sf.sfb223 ,
sh.SHB05 ,
sh.SHB06 ,
sh.shb08,
sh.shb09 ,
sh.shb081 ,
sh.shb082 ,
sh.shb10 ,
sh.shbacti ,
sh.shbplant
ORDER BY
sf.TA_SFB01 ,
sh.shb05,
sh.shb09" ;
DataTable dt = ERP_SqlHelper.ExcuteTable(sql);
return(dt);
}
public List <CompeleteERP> getERPData(List <string> myNumbers)
{
string Sql_myNumbers = "";
foreach (string myNumber in myNumbers)
{
Sql_myNumbers = Sql_myNumbers + "'" + myNumber + "',";
}
Sql_myNumbers = Sql_myNumbers.Substring(0, Sql_myNumbers.Length - 1);
string sql = @"SELECT shb.shbplant as org,
sfb.TA_SFB01 AS myNumber,
sfb.sfb22 AS orderID,
shb.shb01 as moveOrderID,
shb.shb05 as finishID,
oea.oea03 as custID,
oea.oea032 as custName,
sfb.sfb05 AS Style,
eca.eca02 as taskProcessName,
sfb.sfb08 AS OrderQty,
ecm.ecm65 as makeQty,
to_char(shb02,'YYYY-MM-DD') as FinishDate,
shb.shb04 as emploreeID,
ecm.ecm45 as caption,
shb.shb06 as ProcessID,
shb.shb07 as processName,
shb.shb08 as lineID,
shb.shb081 as WorkID,
shb.shb09 as workMachineID,
shb.shb111 as finishQty,
shb.shb115 as BonusQty,
shb.shbacti as checkedID
FROM SAA.SFB_FILE sfb
LEFT JOIN SAA.OEA_FILE oea ON oea.oea01 = sfb.sfb22
left join SAA.ECM_FILE ecm ON ecm.ecm01 = sfb.SFB01
left join SAA.ECA_FILE eca ON eca.ECA01 = ecm.ecm06
LEFT JOIN SAA.SHB_FILE shb on shb.SHB05 = sfb.SFB01 and shb.shb07 = sfb.ecm.ecm06
WHERE sfb.TA_SFB01 in (" + Sql_myNumbers + @")
and ecm.ecm06 = 'CJ'
union
SELECT shb.shbplant as org,
sfb.TA_SFB01 AS myNumber,
sfb.sfb22 AS orderID,
shb.shb01 as moveOrderID,
shb.shb05 as finishID,
oea.oea03 as custID,
oea.oea032 as custName,
sfb.sfb05 AS Style,
eca.eca02 as taskProcessName,
sfb.sfb08 AS OrderQty,
ecm.ecm65 as makeQty,
to_char(shb02,'YYYY-MM-DD') as FinishDate,
shb.shb04 as emploreeID,
ecm.ecm45 as caption,
shb.shb06 as ProcessID,
shb.shb07 as processName,
shb.shb08 as lineID,
shb.shb081 as WorkID,
shb.shb09 as workMachineID,
shb.shb111 as finishQty,
shb.shb115 as BonusQty,
shb.shbacti as checkedID
FROM TOP.SFB_FILE sfb
LEFT JOIN TOP.OEA_FILE oea ON oea.oea01 = sfb.sfb22
left join TOP.ECM_FILE ecm ON ecm.ecm01 = sfb.SFB01
left join TOP.ECA_FILE eca ON eca.ECA01 = ecm.ecm06
LEFT JOIN TOP.SHB_FILE shb on shb.SHB05 = sfb.SFB01 and shb.shb07 = sfb.ecm.ecm06
WHERE sfb.TA_SFB01 in (" + Sql_myNumbers + @")
and ecm.ecm06 = 'CJ' ";
// sql = " SELECT sfb01 FROM TOP.SFB_FILE sfb WHERE sfb.TA_SFB01 ='NKB-21-01-0322'";
List <CompeleteERP> lists = new List <CompeleteERP>();
DataTable ERPdt = ERP_SqlHelper.ExcuteTable(sql);
if (ERPdt.Rows.Count > 0)
{
foreach (DataRow row in ERPdt.Rows)
{
CompeleteERP c = new CompeleteERP();
cERP(row, c);
lists.Add(c);
}
}
return(lists);
}
public DataTable getStyles(string org, string outLine)
{
DataTable asft = new DataTable();
asft.Columns.Add("STYLE");
asft.Columns.Add("QTY");
string stylestr = "";
string str705 = @"select SHB10 AS STYLE , SUM(SHB111) AS QTY from " + org + ".SHB_FILE WHERE SHB09 = '" + outLine + "' GROUP BY SHB10 ORDER BY SHB10";
DataTable asft705 = ERP_SqlHelper.ExcuteTable(str705);
for (int i = 0; i < asft705.Rows.Count; i++)
{
stylestr = asft705.Rows[i]["STYLE"].ToString().ToUpper();
if (!isExStyles(stylestr, asft))
{
DataRow row = asft.NewRow();
row["STYLE"] = stylestr;
row["QTY"] = 0;
asft.Rows.Add(row);
}
}
string str700 = @"select SGL05 AS STYLE, SUM(SGL08) AS QTY from " + org + ".sgl_file where sgl13 = '" + outLine + "' GROUP BY SGL05 ORDER BY SGL05";
DataTable asft700 = ERP_SqlHelper.ExcuteTable(str700);
for (int i = 0; i < asft700.Rows.Count; i++)
{
stylestr = asft700.Rows[i]["STYLE"].ToString().ToUpper();
if (!isExStyles(stylestr, asft))
{
DataRow row = asft.NewRow();
row["STYLE"] = stylestr;
row["QTY"] = 0;
asft.Rows.Add(row);
}
}
int qty = 0;
for (int i = 0; i < asft.Rows.Count; i++)
{
for (int j = 0; j < asft700.Rows.Count; j++)
{
if (asft.Rows[i]["STYLE"].ToString().ToUpper() == asft700.Rows[j]["STYLE"].ToString().ToUpper())
{
qty = qty + Convert.ToInt32(asft700.Rows[j]["QTY"].ToString());
}
}
if (qty > 0)
{
asft.Rows[i]["QTY"] = Convert.ToInt32(asft.Rows[i]["QTY"].ToString()) + qty;
qty = 0;
}
}
int tty = 0;
for (int i = 0; i < asft.Rows.Count; i++)
{
for (int j = 0; j < asft705.Rows.Count; j++)
{
if (asft.Rows[i]["STYLE"].ToString().ToUpper() == asft705.Rows[j]["STYLE"].ToString().ToUpper())
{
tty = tty + Convert.ToInt32(asft705.Rows[j]["QTY"].ToString());
}
}
if (tty > 0)
{
asft.Rows[i]["QTY"] = Convert.ToInt32(asft.Rows[i]["QTY"].ToString()) + tty;
tty = 0;
}
}
return(asft);
}
/// <summary>
/// 数据库连接操作，可替换为你自己的程序
/// </summary>
/// <param name="ConnectionString">连接字符串</param>
/// <returns></returns>
public List <string> TestConnection(string serName)
{
List <string> lists = new List <string>();
switch (serName)
{
case "ERPconnStr":
try
{
string sql = "select TABLE_NAME from all_tab_comments where ROWNUM <20";
DataTable dt = ERP_SqlHelper.ExcuteTable(sql);
if (dt.Rows.Count <= 0)
{
lists.Add("连接数据库错误");
}
else
{
for (int i = 0; i < dt.Rows.Count; i++)
{
lists.Add(dt.Rows[i]["TABLE_NAME"].ToString());
}
}
return(lists);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
lists.Add("连接数据库错误");
return(lists);
}
case "BESTconnStr":
try
{
string sql = "Select Name TABLE_NAME From Master..SysDatabases order By Name";
DataTable dt = BEST_SqlHelper.ExcuteTable(sql, serName);
if (dt.Rows.Count <= 0)
{
lists.Add("连接数据库错误");
}
else
{
for (int i = 0; i < dt.Rows.Count; i++)
{
lists.Add(dt.Rows[i]["TABLE_NAME"].ToString());
}
}
return(lists);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
lists.Add("连接数据库错误");
return(lists);
}
case "BESTconnStr_KM":
try
{
string sql = "Select Name TABLE_NAME From Master..SysDatabases order By Name";
DataTable dt = BEST_SqlHelper.ExcuteTable(sql, serName);
if (dt.Rows.Count <= 0)
{
lists.Add("连接数据库错误");
}
else
{
for (int i = 0; i < dt.Rows.Count; i++)
{
lists.Add(dt.Rows[i]["TABLE_NAME"].ToString());
}
}
return(lists);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
lists.Add("连接数据库错误");
return(lists);
}
case "MySqlconnStr":
try
{
string sql = "SHOW TABLES; ";
DataTable dt = Mysql_SqlHelper.ExcuteTable(sql);
if (dt.Rows.Count <= 0)
{
lists.Add("连接数据库错误");
}
else
{
for (int i = 0; i < dt.Rows.Count; i++)
{
lists.Add(dt.Rows[i]["TABLE_NAME"].ToString());
}
}
return(lists);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
lists.Add("连接数据库错误");
return(lists);
}
default:
lists.Add("未知错误");
return(lists);
}
}
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);
}