public DataTable getClr_noByMy_no(string[] parameters)
{
string sqlstr = @"SELECT DISTINCT
b.clr_no ,
b.style_id,
h.my_no,
h.cust_id
FROM odh h
LEFT JOIN odb b
ON h.od_no = b.od_no
WHERE 1=1
and h.my_no LIKE +'%'+ ISNULL(@my_no,h.my_no) +'%'
and b.style_id LIKE +'%'+ ISNULL(@style_id,b.style_id) +'%'";
object my_no = DBValue(parameters[0]);
object style_id = DBValue(parameters[1]);
SqlParameter[] paras =
{
new SqlParameter("@my_no", my_no),
new SqlParameter("@style_id", style_id)
};
DataTable dt = BEST_SqlHelper.ExcuteTable(sqlstr, paras);
return(dt);
}
public DataTable getSizeByMy_no(string my_nos, string linkServer)
{
string sqlstr = @"SELECT my_no,
us01,
us02,
us03,
us04,
us05,
us06,
us07,
us08,
us09,
us10,
us11,
us12
FROM odh
WHERE 1 = 1
AND my_no IN ( " + my_nos + @" );";
DataTable dt = new DataTable();
if (linkServer == "BESTconnStr_KM")
{
dt = BEST_SqlHelper_KM.ExcuteTable(sqlstr);
}
else if (linkServer == "BESTconnStr")
{
dt = BEST_SqlHelper.ExcuteTable(sqlstr);
}
return(dt);
}
public DataTable getAllSizeRunByMy_no(string my_no)
{
string sqlstr = @"
select type.type_tt,cust_dom.cust_abbr,h1.od_date,h1.season_id,h1.my_no,
case h1.be_id when 'SAA' then 'SAA' else 'TOP' end as org ,
buyid.yymm,type.type_name,t.*
from ( select b.od_no,b.style_id,b.clr_no,size_code=h.us01,qty=b.qty01,b.def_date,b.po_no from odb b,odh h where b.od_no=h.od_no
union all select b.od_no,b.style_id,b.clr_no,size_code=h.us02,qty=b.qty02,b.def_date,b.po_no from odb b,odh h where b.od_no=h.od_no
union all select b.od_no,b.style_id,b.clr_no,size_code=h.us03,qty=b.qty03,b.def_date,b.po_no from odb b,odh h where b.od_no=h.od_no
union all select b.od_no,b.style_id,b.clr_no,size_code=h.us04,qty=b.qty04,b.def_date,b.po_no from odb b,odh h where b.od_no=h.od_no
union all select b.od_no,b.style_id,b.clr_no,size_code=h.us05,qty=b.qty05,b.def_date,b.po_no from odb b,odh h where b.od_no=h.od_no
union all select b.od_no,b.style_id,b.clr_no,size_code=h.us06,qty=b.qty06,b.def_date,b.po_no from odb b,odh h where b.od_no=h.od_no
union all select b.od_no,b.style_id,b.clr_no,size_code=h.us07,qty=b.qty07,b.def_date,b.po_no from odb b,odh h where b.od_no=h.od_no
union all select b.od_no,b.style_id,b.clr_no,size_code=h.us08,qty=b.qty08,b.def_date,b.po_no from odb b,odh h where b.od_no=h.od_no
union all select b.od_no,b.style_id,b.clr_no,size_code=h.us09,qty=b.qty09,b.def_date,b.po_no from odb b,odh h where b.od_no=h.od_no
union all select b.od_no,b.style_id,b.clr_no,size_code=h.us10,qty=b.qty10,b.def_date,b.po_no from odb b,odh h where b.od_no=h.od_no
union all select b.od_no,b.style_id,b.clr_no,size_code=h.us11,qty=b.qty11,b.def_date,b.po_no from odb b,odh h where b.od_no=h.od_no
union all select b.od_no,b.style_id,b.clr_no,size_code=h.us12,qty=b.qty12,b.def_date,b.po_no from odb b,odh h where b.od_no=h.od_no ) t
,odh h1
Inner join cust_dom ON h1.cust_id = cust_dom.cust_id
Left join tb_sfcbuy buyid ON CONVERT (datetime,h1.od_date) between buyid.begin_day AND buyid.end_day AND buyid.cust_buy_id= case h1.cust_id when 'A0000' then 'A0001' else 'SAB' end
left join types type on h1.type_id=type.type_id
where 1=1
and qty>0
and h1.od_no=t.od_no
and h1.my_no = '" + my_no + @"'
order by type.type_tt,cust_dom.cust_name,h1.season_id,h1.my_no;";
DataTable dt = BEST_SqlHelper.ExcuteTable(sqlstr);
return(dt);
}
/// <summary>
/// 从BEST取回订单资料
/// </summary>
/// <param name="po_no"></param>
/// <param name="style_id"></param>
/// <param name="clr_no"></param>
/// <returns></returns>
public DataTable getOD_POFromBestByPSC(List <outGoing_pos> pscs)
{
string style_id = "";
string clr_no = "";
string po_no = "";
if (pscs.Count <= 0)
{
DataTable dt = new DataTable();
return(dt);
}
for (int i = 0; i < pscs.Count; i++)
{
style_id = style_id + "'" + pscs[i].style_ids + "',";
clr_no = clr_no + "'" + pscs[i].clr_nos + "',";
po_no = po_no + "'" + pscs[i].po_ids + "',";
}
style_id = style_id.Substring(0, style_id.Length - 1);
clr_no = clr_no.Substring(0, clr_no.Length - 1);
po_no = po_no.Substring(0, po_no.Length - 1);
string sql = @"
SELECT b1.po_no,
h1.cust_id,
h1.season_id,
h1.release_who,
b1.style_id,
b1.clr_no
FROM dbo.odh h1
LEFT JOIN odb b1
ON h1.od_no = b1.od_no
AND b1.style_id IN ( " + style_id + @" )
AND b1.clr_no IN ( " + clr_no + @" )
WHERE b1.po_no IN ( " + po_no + @" )
AND b1.style_id IN ( " + style_id + @" )
AND b1.clr_no IN ( " + clr_no + @" )
AND b1.po_no != ''
GROUP BY b1.po_no,
h1.cust_id,
h1.season_id,
h1.release_who,
b1.style_id,
b1.clr_no;" ;
DataTable result = BEST_SqlHelper.ExcuteTable(sql);
return(result);
}
public DataTable getPoNumbersByODdate(string startDate, string stopDate)
{
string sqlstr = @"
SELECT h.my_no,b.po_no,SUM(b.qty) qty,b.mark ,h.cust_id ,c.cust_abbr,c.cust_name FROM odb b
LEFT JOIN dbo.odh h ON b.od_no = h.od_no
LEFT JOIN dbo.cust_dom c ON c.cust_id = h.cust_id
LEFT JOIN dbo.types t ON t.type_id=h.type_id
WHERE h.od_date BETWEEN '" + startDate + "' AND '" + stopDate + @"'
AND t.type_tt LIKE '002%'
GROUP BY b.po_no,b.mark ,h.my_no ,h.cust_id ,c.cust_abbr,c.cust_name
ORDER BY h.my_no";
DataTable result = BEST_SqlHelper.ExcuteTable(sqlstr);
return(result);
}
/// <summary>
/// 从BEST取回月BUY
/// </summary>
/// <param name="po_no"></param>
/// <param name="style_id"></param>
/// <param name="clr_no"></param>
/// <returns></returns>
public DataTable getYYMMFromBestByPo(string po)
{
string sql = @" SELECT buyid.yymm,
buyid.buy_cname,
b.po_no
FROM tb_sfcbuy buyid
LEFT JOIN odh h
ON h.od_date
BETWEEN buyid.begin_day AND buyid.end_day
AND buyid.cust_buy_id = 'A0001'
LEFT JOIN odb b
ON b.od_no = h.od_no
WHERE b.po_no = '" + po + @"'
GROUP BY buyid.yymm,
buyid.buy_cname,
b.po_no;" ;
DataTable result = BEST_SqlHelper.ExcuteTable(sql);
return(result);
}
public DataTable getSizesByStyle(string style)
{
string sqlstr = @"
SELECT us01,
us02,
us03,
us04,
us05,
us06,
us07,
us08,
us09,
us10,
us11,
us12
FROM dbo.odh h
LEFT JOIN odb b
ON h.od_no = b.od_no
WHERE b.style_id = '" + style + "';";
return(BEST_SqlHelper.ExcuteTable(sqlstr));
}
public DataTable getSizeByMy_no(string my_nos)
{
string sqlstr = @"SELECT my_no,
us01,
us02,
us03,
us04,
us05,
us06,
us07,
us08,
us09,
us10,
us11,
us12
FROM odh
WHERE 1 = 1
AND my_no IN ( " + my_nos + @" );";
DataTable dt = BEST_SqlHelper.ExcuteTable(sqlstr);
return(dt);
}
public DataTable getMy_NoFromBest(string po_no, string clr_no, string style_id, string area_id, string def_date)
{
string sql = @"
SELECT b.po_no,
mark = (STUFF(
(
SELECT b.po_no,
b.od_no,
b.qty,
h.my_no
FROM dbo.odb b
LEFT JOIN odh h
ON b.od_no = h.od_no
WHERE b.po_no = '" + po_no + @"'
AND b.clr_no = '" + clr_no + @"'
AND b.style_id = '" + style_id + @"'
AND b.area_id = '" + area_id + @"'
AND b.def_date='" + def_date + @"'
FOR XML PATH('')
),
1,
0,
''
)
)
FROM odb b
WHERE b.po_no = '" + po_no + @"'
AND b.clr_no = '" + clr_no + @"'
AND b.style_id = '" + style_id + @"'
AND b.area_id = '" + area_id + @"'
AND b.def_date='" + def_date + @"'
GROUP BY po_no;" ;
DataTable result = BEST_SqlHelper.ExcuteTable(sql);
return(result);
}
public DataTable getProductionWip(ProductionStatusSearch parameters)
{
string sql = "";
int type = parameters.datetype;
if (type == 0)
{
if (parameters.checkedDate)
{
sql = @"SELECT h.my_no,
h.season_id,
h.cust_id,
h.od_date,
buyid.yymm,
s.style_name,
s.style_id,
b.clr_no,
c.clr_name,
SUM(b.qty) qty,
MIN(b.def_date) def_date
FROM odh h
LEFT JOIN odb b
ON h.od_no = b.od_no
LEFT JOIN style s
ON b.style_id = s.style_id
LEFT JOIN clr c
ON c.clr_no = b.clr_no
LEFT JOIN tb_sfcbuy buyid
ON CONVERT(DATETIME, h.od_date)
BETWEEN buyid.begin_day AND buyid.end_day
AND buyid.cust_buy_id = CASE h.cust_id
WHEN 'A0000' THEN
'A0001'
ELSE
'SAB'
END
WHERE 1 = 1 AND
h.my_no like '' + @myNumber + '%' AND
buyid.yymm like '' + @buyid + '%' AND
h.season_id like '' + @season + '%' AND
b.po_no NOT LIKE '%樣%' AND
CONVERT (datetime, h.od_date) BETWEEN @stardate AND @enddate
GROUP BY h.my_no,
h.season_id,
h.cust_id,
h.od_date,
s.style_name,
s.style_id,
b.clr_no,
buyid.yymm,
c.clr_name";
}
else
{
sql = @"SELECT h.my_no,
h.season_id,
h.cust_id,
h.od_date,
buyid.yymm,
s.style_name,
s.style_id,
b.clr_no,
c.clr_name,
SUM(b.qty) qty,
MIN(b.def_date) def_date
FROM odh h
LEFT JOIN odb b
ON h.od_no = b.od_no
LEFT JOIN style s
ON b.style_id = s.style_id
LEFT JOIN clr c
ON c.clr_no = b.clr_no
LEFT JOIN tb_sfcbuy buyid
ON CONVERT(DATETIME, h.od_date)
BETWEEN buyid.begin_day AND buyid.end_day
AND buyid.cust_buy_id = CASE h.cust_id
WHEN 'A0000' THEN
'A0001'
ELSE
'SAB'
END
WHERE 1 = 1 AND
h.my_no like '' + @myNumber + '%' AND
buyid.yymm like '' + @buyid + '%' AND
b.po_no NOT LIKE '%樣%' AND
h.season_id like '' + @season + '%'
GROUP BY h.my_no,
h.season_id,
h.cust_id,
h.od_date,
s.style_name,
s.style_id,
b.clr_no,
buyid.yymm,
c.clr_name";
}
}
else if (type == 1)
{
if (parameters.checkedDate)
{
sql = @"SELECT h.my_no,
h.season_id,
h.cust_id,
h.od_date,
buyid.yymm,
s.style_name,
s.style_id,
b.clr_no,
c.clr_name,
SUM(b.qty) qty,
MIN(b.def_date) def_date
FROM odh h
LEFT JOIN odb b
ON h.od_no = b.od_no
LEFT JOIN style s
ON b.style_id = s.style_id
LEFT JOIN clr c
ON c.clr_no = b.clr_no
LEFT JOIN tb_sfcbuy buyid
ON CONVERT(DATETIME, h.od_date)
BETWEEN buyid.begin_day AND buyid.end_day
AND buyid.cust_buy_id = CASE h.cust_id
WHEN 'A0000' THEN
'A0001'
ELSE
'SAB'
END
WHERE 1 = 1 AND
h.my_no like '' + @myNumber + '%' AND
buyid.yymm like '' + @buyid + '%' AND
h.season_id like '' + @season + '%' AND
b.po_no NOT LIKE '%樣%' AND
CONVERT (datetime, b.def_date) BETWEEN @stardate AND @enddate
GROUP BY h.my_no,
h.season_id,
h.cust_id,
h.od_date,
s.style_name,
s.style_id,
b.clr_no,
buyid.yymm,
c.clr_name";
}
else
{
sql = @"SELECT h.my_no,
h.season_id,
h.cust_id,
h.od_date,
buyid.yymm,
s.style_name,
s.style_id,
b.clr_no,
c.clr_name,
SUM(b.qty) qty,
MIN(b.def_date) def_date
FROM odh h
LEFT JOIN odb b
ON h.od_no = b.od_no
LEFT JOIN style s
ON b.style_id = s.style_id
LEFT JOIN clr c
ON c.clr_no = b.clr_no
LEFT JOIN tb_sfcbuy buyid
ON CONVERT(DATETIME, h.od_date)
BETWEEN buyid.begin_day AND buyid.end_day
AND buyid.cust_buy_id = CASE h.cust_id
WHEN 'A0000' THEN
'A0001'
ELSE
'SAB'
END
WHERE 1 = 1 AND
h.my_no like '' + @myNumber + '%' AND
buyid.yymm like '' + @buyid + '%' AND
b.po_no NOT LIKE '%樣%' AND
h.season_id like '' + @season + '%'
GROUP BY h.my_no,
h.season_id,
h.cust_id,
h.od_date,
s.style_name,
s.style_id,
b.clr_no,
buyid.yymm,
c.clr_name";
}
}
SqlParameter[] ps =
{
new SqlParameter("myNumber", parameters.mynumber),
new SqlParameter("buyid", parameters.buyid),
new SqlParameter("season", parameters.season),
new SqlParameter("stardate", parameters.stardate),
new SqlParameter("enddate", parameters.enddate)
};
DataTable dt = BEST_SqlHelper.ExcuteTable(sql, ps);
return(dt);
}
public DataTable getSizeRunByMy_no(string my_no)
{
string sqlstr = @"
SELECT
a.type_tt,
a.cust_abbr,
a.od_date,
a.season_id,
a.my_no,
a.org,
a.yymm,
a.type_name,
a.od_no,
a.style_id,
a.clr_no,
size_code,
sum(a.qty) qty
FROM (
SELECT type.type_tt,
cust_dom.cust_abbr,
h1.od_date,
h1.season_id,
h1.my_no,
CASE h1.be_id
WHEN 'SAA' THEN
'SAA'
ELSE
'TOP'
END AS org,
buyid.yymm,
type.type_name,
t.od_no,
t.style_id,
t.clr_no,
size_code,
t.qty
from ( select b.od_no,b.style_id,b.clr_no,size_code=h.us01,qty=b.qty01 from odb b,odh h where b.od_no=h.od_no
union all select b.od_no,b.style_id,b.clr_no,size_code=h.us02,qty=b.qty02 from odb b,odh h where b.od_no=h.od_no
union all select b.od_no,b.style_id,b.clr_no,size_code=h.us03,qty=b.qty03 from odb b,odh h where b.od_no=h.od_no
union all select b.od_no,b.style_id,b.clr_no,size_code=h.us04,qty=b.qty04 from odb b,odh h where b.od_no=h.od_no
union all select b.od_no,b.style_id,b.clr_no,size_code=h.us05,qty=b.qty05 from odb b,odh h where b.od_no=h.od_no
union all select b.od_no,b.style_id,b.clr_no,size_code=h.us06,qty=b.qty06 from odb b,odh h where b.od_no=h.od_no
union all select b.od_no,b.style_id,b.clr_no,size_code=h.us07,qty=b.qty07 from odb b,odh h where b.od_no=h.od_no
union all select b.od_no,b.style_id,b.clr_no,size_code=h.us08,qty=b.qty08 from odb b,odh h where b.od_no=h.od_no
union all select b.od_no,b.style_id,b.clr_no,size_code=h.us09,qty=b.qty09 from odb b,odh h where b.od_no=h.od_no
union all select b.od_no,b.style_id,b.clr_no,size_code=h.us10,qty=b.qty10 from odb b,odh h where b.od_no=h.od_no
union all select b.od_no,b.style_id,b.clr_no,size_code=h.us11,qty=b.qty11 from odb b,odh h where b.od_no=h.od_no
union all select b.od_no,b.style_id,b.clr_no,size_code=h.us12,qty=b.qty12 from odb b,odh h where b.od_no=h.od_no ) t
,odh h1
INNER join cust_dom ON h1.cust_id = cust_dom.cust_id
Left join tb_sfcbuy buyid ON CONVERT (datetime,h1.od_date) between buyid.begin_day AND buyid.end_day AND buyid.cust_buy_id= case h1.cust_id when 'A0000' then 'A0001' else 'SAB' end
left join types type on h1.type_id=type.type_id
where 1=1
and qty>0
and h1.od_no=t.od_no
and h1.my_no = '" + my_no + @"'
) a
WHERE a.my_no = '" + my_no + @"'
GROUP BY
a.type_tt,
a.cust_abbr,
a.od_date,
a.season_id,
a.my_no,
a.org,
a.yymm,
a.type_name,
a.od_no,
a.style_id,
a.clr_no,
a.size_code
ORDER BY a.type_tt,a.cust_abbr,a.season_id,a.my_no;";
DataTable dt = BEST_SqlHelper.ExcuteTable(sqlstr);
return(dt);
}
public DataTable getColorsByStyle(string style)
{
string sqlstr = @"SELECT DISTINCT clr_no FROM odb WHERE style_id='" + style + "' ORDER BY clr_no";
return(BEST_SqlHelper.ExcuteTable(sqlstr));
}
/// <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);
}
}