public override void Run()
{
//base.Run();
string sn = snObj.Value.ToString();
string wo = woObj.Value.ToString();
string runSql = $@" select sn.skuno,sn.workorderno,sn.sn,kp.value,kp.partno,kp.kp_name,kp.mpn,kp.scantype,kp.itemseq,
kp.scanseq,kp.detailseq,kp.station,kp.valid_flag,kp.edit_time,kp.edit_emp from r_sn_kp kp, r_sn sn
where kp.r_sn_id = sn.id and sn.valid_flag = '1' ";
if (sn == "" && wo == "")
{
ReportAlart alart = new ReportAlart("Please input a sn or wo");
Outputs.Add(alart);
return;
}
if (sn != "")
{
runSql = runSql + $@" and (sn.sn='{sn}' or sn.boxsn='{sn}') ";
}
if (wo != "")
{
runSql = runSql + $@" and sn.workorderno='{wo}' ";
}
OleExec sfcdb = DBPools["SFCDB"].Borrow();
DataTable dt = new DataTable();
try
{
dt = sfcdb.RunSelect(runSql).Tables[0];
if (sfcdb != null)
{
DBPools["SFCDB"].Return(sfcdb);
}
if (dt.Rows.Count == 0)
{
throw new Exception("NO data");
}
ReportTable reportTable = new ReportTable();
reportTable.LoadData(dt, null);
reportTable.Tittle = "SN keypart detail";
Outputs.Add(reportTable);
}
catch (Exception ex)
{
if (sfcdb != null)
{
DBPools["SFCDB"].Return(sfcdb);
}
ReportAlart alart = new ReportAlart(ex.Message);
Outputs.Add(alart);
}
}
public override void Run()
{
DateTime ft = (DateTime)fromDate.Value;
var DB = this.DBPools["SFCDB"].Borrow();
try
{
string strSql = $@"SELECT rownum NO,
SKU.CUST_PARTNO,
SN.SN,
S.LOT_NO,
S.LOT_QTY,
'XXX' WORKTIME,
'QQQ' DAYS,
S.SAMPLE_STATION
FROM R_LOT_STATUS S
INNER JOIN R_LOT_DETAIL D
ON S.LOT_NO = D.LOT_ID
INNER JOIN R_SN SN
ON D.SN = SN.SN
INNER JOIN C_SKU SKU
ON SN.SKUNO = SKU.SKUNO
WHERE S.LOT_NO LIKE 'LOT-%'
AND S.AQL_TYPE IS NULL
AND SN.VALID_FLAG = 1
AND D.STATUS = 0
AND S.EDIT_TIME >to_date('{ft.ToString("yyyy-MM-dd")}','yyyy-mm-dd')";
if (StockNo.Value.ToString() != "ALL")
{
strSql += $@" AND S.SAMPLE_STATION = '{StockNo.Value.ToString()}'";
}
strSql += " order by s.lot_no";
var res = DB.RunSelect(strSql);
var yearCodeMapping = Common.SNmaker.GetCodeMapping("2YEAR", DB);
var MMCodeMapping = Common.SNmaker.GetCodeMapping("HWDSN_MM", DB);
var DDCodeMapping = Common.SNmaker.GetCodeMapping("HWDSN_DD", DB);
var now = DateTime.Now;
for (int i = 0; i < res.Tables[0].Rows.Count; i++)
{
try
{
DataRow dr = res.Tables[0].Rows[i];
string SN = dr["SN"].ToString();
string yearCode = SN.Substring(6, 2);
string MMCode = SN.Substring(8, 1);
string DDCode = SN.Substring(9, 1);
var year = yearCodeMapping.Find(T => T.CODEVALUE == yearCode).VALUE;
var MM = MMCodeMapping.Find(T => T.CODEVALUE == MMCode).VALUE;
var DD = DDCodeMapping.Find(T => T.CODEVALUE == DDCode).VALUE;
dr["WORKTIME"] = year + MM + DD;
dr["DAYS"] = ((int)(now - DateTime.Parse($@"{year}-{MM}-{DD}")).TotalDays).ToString();
}
catch
{
}
}
ReportTable retTab = new ReportTable();
retTab.LoadData(res.Tables[0], null);
retTab.Tittle = "OBA REPORT";
Outputs.Add(retTab);
}
catch (Exception e)
{
ReportAlart alart = new ReportAlart(e.Message);
Outputs.Add(alart);
}
finally
{
DBPools["SFCDB"].Return(DB);
}
}
public override void Run()
{
if (Skuno.Value == null)
{
throw new Exception("SKUNO Can not be null");
}
string skuno = Skuno.Value.ToString();
DataRow linkDataRow = null;
string closeflag = CloseFlag.Value.ToString();
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
string Sqlsku = $@"select workorderno,wo_type,skuno,sku_ver,workorder_qty from r_wo_base where skuno ='{skuno}'";
if (closeflag == "Y")
{
Sqlsku = Sqlsku + " and CLOSED_FLAG = 1";
}
else if (closeflag == "N")
{
Sqlsku = Sqlsku + " and CLOSED_FLAG = 0";
}
DataTable dtsku = SFCDB.RunSelect(Sqlsku).Tables[0];
if (SFCDB != null)
{
DBPools["SFCDB"].Return(SFCDB);
}
if (dtsku.Rows.Count == 0)
{
ReportAlart alart = new ReportAlart("No Data!");
Outputs.Add(alart);
return;
}
DataTable linkTable = new DataTable();
linkTable.Columns.Add("workorderno");
linkTable.Columns.Add("wo_type");
linkTable.Columns.Add("skuno");
linkTable.Columns.Add("sku_ver");
linkTable.Columns.Add("workorder_qty");
for (int i = 0; i < dtsku.Rows.Count; i++)
{
linkDataRow = linkTable.NewRow();
//跳轉的頁面鏈接
linkDataRow["workorderno"] = "Link#/FunctionPage/Report/Report.html?ClassName=MESReport.BaseReport.WoReport&RunFlag=1&WO=" + dtsku.Rows[i]["workorderno"].ToString() + "&EventName=";
linkDataRow["wo_type"] = "";
linkDataRow["skuno"] = "";
linkDataRow["sku_ver"] = "";
linkDataRow["workorder_qty"] = "";
linkTable.Rows.Add(linkDataRow);
}
ReportTable retTab = new ReportTable();
retTab.LoadData(dtsku, linkTable);
retTab.Tittle = "Skuno Report";
Outputs.Add(retTab);
}
catch (Exception exception)
{
if (SFCDB != null)
{
DBPools["SFCDB"].Return(SFCDB);
}
throw exception;
}
}
public override void Run()
{
string skuno = skuInput.Value?.ToString();
string line = lineInput.Value?.ToString();
string eventpoint = eventInput.Value?.ToString();
string from = fromDate.Value?.ToString().Replace("/", "-");
string to = toDate.Value?.ToString().Replace("/", "-");
from = from.Substring(0, from.Length - 3);
to = to.Substring(0, to.Length - 3);
if (string.IsNullOrEmpty(skuno))
{
ReportAlart alart = new ReportAlart("請輸入機種!");
Outputs.Add(alart);
return;
}
try
{
DataTable dt = GetUphRateDetail(skuno, line, eventpoint, from, to);
if (dt.Rows.Count == 0)
{
ReportAlart alart = new ReportAlart("輸入內容無信息!");
Outputs.Add(alart);
return;
}
#region 時間點去重 重複的重 去重后的 List 為 timeList
var tempList = new List <string>();
var timeList = new ArrayList();
for (int i = 0; i < dt.Rows.Count; i++)
{
tempList.Add(dt.Rows[i]["HOURPERIOD"].ToString());
}
for (int i = 0; i < tempList.Count; i++)
{
if (!timeList.Contains(tempList[i]))
{
timeList.Add(tempList[i]);
}
}
#endregion
#region 構建需要顯示的 DataTable => showDt 把時間點作列名
DataTable showDt = new DataTable();
showDt.Columns.Add("機種");
showDt.Columns.Add("採集點");
showDt.Columns.Add("區分類");
showDt.Columns.Add("UPM*10");
for (int i = 0; i < timeList.Count; i++)
{
showDt.Columns.Add(timeList[i].ToString());
}
#endregion
#region 構建內容行 : 機種 | 工站 | 區分類 | UPM*10 | 時:分的值
List <string> qualifyRowList = MakeContentRow("良品數", dt, showDt); //查詢出的所有工站的良品數行的List集合
List <string> unQualifyRowList = MakeContentRow("不良數", dt, showDt); //查詢出的所有工站的不良品數行的List集合
List <string> dataList = new List <string>(); //將良品數行,不良數行,損失率行,良品率行拼在一起的List集合
for (int i = 0; i < qualifyRowList.Count; i++)
{
dataList.Add(qualifyRowList[i]);
dataList.Add(unQualifyRowList[i]);
string[] qualifyRowArray = qualifyRowList[i].Split(','); //良品數行的數組
string[] unQualifyRowArray = unQualifyRowList[i].Split(','); //不良數行的數組
string qualifyRateRow = string.Empty; //良品率行
string unQualifyRateRow = string.Empty; //損失率行
//分別拼接良品率行與損失率行字符串
qualifyRateRow += qualifyRowList[i].Split(',')[0] + "," + qualifyRowList[i].Split(',')[1] + ",良品率," + qualifyRowList[i].Split(',')[3];
unQualifyRateRow += qualifyRowList[i].Split(',')[0] + "," + qualifyRowList[i].Split(',')[1] + ",損失率," + qualifyRowList[i].Split(',')[3];
for (int j = 4; j < qualifyRowList[i].Split(',').Length; j++)
{
double upm10 = Convert.ToDouble(qualifyRowList[i].Split(',')[3]);
int total = Convert.ToInt32(qualifyRowList[i].Split(',')[j]) + Convert.ToInt32(unQualifyRowList[i].Split(',')[j]);
int good = Convert.ToInt32(qualifyRowList[i].Split(',')[j]);
string qualifyRate = (total == 0 ? 0 : (double)good / total).ToString("0.00%");
string unQualifyRate = (upm10 == 0 ? 0 : (double)(upm10 - total) / upm10).ToString("0.00%");
qualifyRateRow += "," + qualifyRate;
unQualifyRateRow += "," + unQualifyRate;
}
dataList.Add(unQualifyRateRow); //先將損失率行Add進List
dataList.Add(qualifyRateRow); //再講良品率行Add進List
}
#endregion
#region 根據機種路由工站順序重新排列dataList集合使之呈現最終報表的樣子 => showList
DataTable eventDt = GetEventSeqNo(skuno);
List <string> showList = new List <string>();
for (int i = 0; i < eventDt.Rows.Count; i++)
{
for (int j = 0; j < dataList.Count; j++)
{
if (eventDt.Rows[i]["station_name"].ToString() == dataList[j].Split(',')[1])
{
showList.Add(dataList[j]);
}
}
}
#endregion
#region 循環 showList 填充 showDt 再顯示出來
for (int i = 0; i < showList.Count; i++)
{
DataRow dtRow = showDt.NewRow();
for (int j = 0; j < showDt.Columns.Count; j++)
{
dtRow[j] = showList[i].Split(',')[j];
}
showDt.Rows.Add(dtRow);
}
ReportTable retTab = new ReportTable();
retTab.LoadData(showDt, null);
retTab.Tittle = "ZERO DEFECT UPH RATE REPORT";
Outputs.Add(retTab);
#endregion
}
catch (Exception ex)
{
Outputs.Add(new ReportAlart(ex.Message));
}
}
public override void Run()
{
//base.Run();
string sn = snObj.Value.ToString();
string wo = woObj.Value.ToString();
string sqlNoLinkSN = "";
string sqlNoLinkWO = "";
string sqlLinkSN_1 = "";
string sqlLinkSN_2 = "";
string sqlLinkWO_1 = "";
string sqlLinkWO_2 = "";
string sqlNoLink = "";
string sqlLink = "";
if (sn == "" && wo == "")
{
ReportAlart alart = new ReportAlart("Please input a sn or wo");
Outputs.Add(alart);
return;
}
if (sn != "")
{
sqlNoLinkSN = $@" and (a.sn='{sn}' or a.boxsn='{sn}') ";
sqlLinkSN_1 = $@" and (a.sn='{sn}' or a.boxsn='{sn}') ";
sqlLinkSN_2 = $@" and (d.sn='{sn}' or d.boxsn='{sn}') ";
}
if (wo != "")
{
sqlNoLinkWO = $@" and a.workorderno='{wo}' ";
sqlLinkWO_1 = $@" and a.workorderno='{wo}' ";
sqlLinkWO_2 = $@" and d.workorderno='{wo}' ";
}
sqlNoLink = $@"select a.sn,a.skuno,a.workorderno,a.completed_flag,a.completed_time,a.shipped_flag,a.shipdate,a.current_station,a.next_station,a.edit_time from r_sn a
where 1=1 {sqlNoLinkSN} {sqlNoLinkWO} and a.valid_flag='1' and not exists (select * from r_sn_kp b where a.sn=b.value)
and not exists (select * from r_sn_keypart_detail c where a.sn=c.keypart_sn) ";
sqlLink = $@"select c.workorderno as wo,c.sn, a.workorderno as keypart_wo,a.sn as keypart_sn,b.station as link_station,b.edit_time as link_time,b.edit_emp as link_by
from r_sn a,r_sn_kp b,r_sn c where a.sn=b.value and c.id=b.r_sn_id
{sqlLinkSN_1} {sqlLinkWO_1} and a.valid_flag='1' and not exists (select * from r_sn_keypart_detail n where a.sn=n.keypart_sn)
union
select distinct f.workorderno as wo,f.sn, d.workorderno as keypart_wo,d.sn as keypart_sn,e.station_name as link_station,e.edit_time as link_time,e.edit_emp as link_by
from r_sn d,r_sn_keypart_detail e,r_sn f where d.sn=e.keypart_sn and e.sn=f.sn and f.valid_flag='1' and d.valid_flag='1'
{sqlLinkSN_2} {sqlLinkWO_2} ";
OleExec sfcdb = DBPools["SFCDB"].Borrow();
DataTable dtLink = new DataTable();
DataTable dtNoLnk = new DataTable();
try
{
dtNoLnk = sfcdb.RunSelect(sqlNoLink).Tables[0];
dtLink = sfcdb.RunSelect(sqlLink).Tables[0];
if (sfcdb != null)
{
DBPools["SFCDB"].Return(sfcdb);
}
ReportTable reportTable = new ReportTable();
reportTable.LoadData(dtNoLnk, null);
reportTable.Tittle = "SN no link detail";
Outputs.Add(reportTable);
ReportTable retTab2 = new ReportTable();
retTab2.LoadData(dtLink, null);
retTab2.Tittle = "SN link detail";
Outputs.Add(retTab2);
}
catch (Exception ex)
{
if (sfcdb != null)
{
DBPools["SFCDB"].Return(sfcdb);
}
ReportAlart alart = new ReportAlart(ex.Message);
Outputs.Add(alart);
}
}
public override void Run()
{
DataTable dt = null;
string pack_no = PackNo.Value.ToString();
OleExec sfcdb = DBPools["SFCDB"].Borrow();
DataRow linkDataRow = null;
if (pack_no == "" || PackNo.Value == null)
{
ReportAlart alart = new ReportAlart("PackNo or SN Can not be null");
Outputs.Add(alart);
return;
}
DataTable linkTable = new DataTable();
try
{
string packSql = $@"select * from r_packing where pack_no ='{pack_no}'";
dt = sfcdb.RunSelect(packSql).Tables[0];
if (dt.Rows.Count == 0)
{
packSql = $@"SELECT B.sn,
B.skuno,
B.workorderno,
B.CARTON,
rppk.pack_no PALLET,
B.EDIT_TIME
FROM(select rsn.sn,
rsn.skuno,
rsn.workorderno,
rpk.parent_pack_id,
rpk.pack_no CARTON,
RPK.EDIT_TIME
from r_sn rsn, R_SN_PACKING rsnp, r_packing rpk
where rsn.SN = '{pack_no}'
and rsn.id = rsnp.sn_id
and rsnp.pack_id = rpk.id) B
LEFT JOIN r_packing rppk
ON B.parent_pack_id = rppk.id";
dt = sfcdb.RunSelect(packSql).Tables[0];
if (dt.Rows.Count == 0)
{
if (sfcdb != null)
{
DBPools["SFCDB"].Return(sfcdb);
}
ReportAlart alart = new ReportAlart("No Data!");
Outputs.Add(alart);
return;
}
}
else if (dt.Rows[0]["PACK_TYPE"].ToString() == "PALLET")
{
packSql = $@"select rsn.sn,
rsn.skuno,
rsn.workorderno,
rcp.pack_no carton,
rp.pack_no pallet,
rcp.edit_time
from r_packing rp
left join r_packing rcp
on rp.id = rcp.parent_pack_id
left join r_sn_packing rsp
on rcp.id = rsp.pack_id
left join r_sn rsn
on rsp.sn_id = rsn.id
where rp.pack_no = '{pack_no}'
order by pallet,carton";
}
else
{
packSql = $@"select b.sn,
b.skuno,
b.workorderno,
b.carton,
rppk.pack_no pallet,
b.edit_time
from (select rsn.sn,
rsn.skuno,
rsn.workorderno,
rp.pack_no carton,
rp.parent_pack_id,
rp.edit_time
from r_packing rp, r_sn_packing rpk, r_sn rsn
where rp.pack_no = '{pack_no}'
and rp.id = rpk.pack_id
and rpk.sn_id = rsn.id) b
left join r_packing rppk
on b.parent_pack_id = rppk.id
order by pallet, carton";
}
//string packSql = $@"select r.sn,r.skuno,r.workorderno,rp.pack_no,rp.edit_time from r_sn r,r_sn_packing rsp,r_packing rp
// where rp.id =rsp.pack_id and rsp.sn_id = r.id and rp.pack_no ='{pack_no}'";
dt = sfcdb.RunSelect(packSql).Tables[0];
if (sfcdb != null)
{
DBPools["SFCDB"].Return(sfcdb);
}
linkTable.Columns.Add("sn");
linkTable.Columns.Add("skuno");
linkTable.Columns.Add("workorderno");
linkTable.Columns.Add("carton");
linkTable.Columns.Add("pallet");
linkTable.Columns.Add("edit_time");
for (int i = 0; i < dt.Rows.Count; i++)
{
linkDataRow = linkTable.NewRow();
//跳轉的頁面鏈接
linkDataRow["sn"] = "Link#/FunctionPage/Report/Report.html?ClassName=MESReport.BaseReport.SNReport&RunFlag=1&SN=" + dt.Rows[i]["SN"].ToString();
linkDataRow["skuno"] = "";
linkDataRow["workorderno"] = "Link#/FunctionPage/Report/Report.html?ClassName=MESReport.BaseReport.WoReport&RunFlag=1&WO=" + dt.Rows[i]["workorderno"].ToString() + "&EventName=";
linkDataRow["carton"] = "";
linkDataRow["pallet"] = "";
linkDataRow["edit_time"] = "";
linkTable.Rows.Add(linkDataRow);
}
ReportTable reportTable = new ReportTable();
reportTable.LoadData(dt, linkTable);
reportTable.Tittle = "SN Packing detail";
Outputs.Add(reportTable);
}
catch (Exception ex)
{
if (sfcdb != null)
{
DBPools["SFCDB"].Return(sfcdb);
}
ReportAlart alart = new ReportAlart(ex.Message);
Outputs.Add(alart);
}
}
public override void Run()
{
if (WO.Value == null)
{
throw new Exception("WO Can not be null");
}
// string runSql = string.Format(Sqls["strGetSN"], WO.Value.ToString());
// RunSqls.Add(runSql);
string wo = WO.Value.ToString();
string columnName = "";
string closeflag = CloseFlag.Value.ToString();
string linkURL = "Link#/FunctionPage/Report/Report.html?ClassName=MESReport.BaseReport.SNListByWo&RunFlag=1&WO=" + wo + "&EventName=";
;
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
string Sqlwo = $@"SELECT workorderno ,skuno,ROUTE_ID,WORKORDER_QTY , trunc ( sysdate - DOWNLOAD_DATE) DATS,INPUT_QTY,FINISHED_QTY FROM R_WO_BASE where
WORKORDERNO = '{wo}' ";
if (closeflag == "Y")
{
Sqlwo = Sqlwo + " and CLOSED_FLAG = 1";
}
else if (closeflag == "N")
{
Sqlwo = Sqlwo + " and CLOSED_FLAG = 0";
}
DataTable dtwo = SFCDB.RunSelect(Sqlwo).Tables[0];
RunSqls.Add(Sqlwo);
if (dtwo.Rows.Count == 0)
{
ReportAlart alart = new ReportAlart("No Data!");
Outputs.Add(alart);
return;
}
//string SqlRoute = $@"select * from C_ROUTE_DETAIL where route_id='{dtwo.Rows[0]["route_id"].ToString()}' order by seq_no";
string SqlRoute = $@"select * from C_ROUTE_DETAIL where route_id='{dtwo.Rows[0]["route_id"].ToString()}' order by seq_no";
DataTable dtroute = SFCDB.RunSelect(SqlRoute).Tables[0];
RunSqls.Add(SqlRoute);
//string SqlStationRoute = $@" SELECT distinct next_station FROM r_sn where REPAIR_FAILED_FLAG <> 1 and(COMPLETED_FLAG = 0 or NEXT_STATION = 'JOBFINISH') and workorderno = '{wo}'
// MINUS
// select STATION_NAME from c_route_detail where ROUTE_ID='{dtwo.Rows[0]["route_id"].ToString()}' ";
string SqlStationRoute = $@" SELECT distinct next_station FROM r_sn where REPAIR_FAILED_FLAG <> 1 and workorderno = '{wo}'
MINUS
select STATION_NAME from c_route_detail where ROUTE_ID='{dtwo.Rows[0]["route_id"].ToString()}' ";
DataTable dtstationroute = SFCDB.RunSelect(SqlStationRoute).Tables[0];
RunSqls.Add(SqlStationRoute);
DataTable resdt = new DataTable();
DataTable linkTable = new DataTable();
resdt.Columns.Add("WorkOrderNo");
resdt.Columns.Add("Skuno");
resdt.Columns.Add("DATS");
resdt.Columns.Add("QTY");
linkTable.Columns.Add("WorkOrderNo");
linkTable.Columns.Add("Skuno");
linkTable.Columns.Add("DATS");
linkTable.Columns.Add("QTY");
for (int i = 0; i < dtroute.Rows.Count; i++)
{
resdt.Columns.Add(dtroute.Rows[i]["STATION_NAME"].ToString());
linkTable.Columns.Add(dtroute.Rows[i]["STATION_NAME"].ToString());
}
for (int i = 0; i < dtstationroute.Rows.Count; i++)
{
if (dtstationroute.Rows[i]["next_station"].ToString().Equals("JOBFINISH"))
{
continue;
}
resdt.Columns.Add(dtstationroute.Rows[i]["next_station"].ToString());
linkTable.Columns.Add(dtstationroute.Rows[i]["next_station"].ToString());
}
// resdt.Columns.Add("STOCKIN");
// resdt.Columns.Add("JOBFINISH");
//resdt.Columns.Add("NA");
resdt.Columns.Add("RepairWip");
resdt.Columns.Add("MRB");
//resdt.Columns.Add("REWORK");
resdt.Columns.Add("JOBFINISH");
//linkTable.Columns.Add("NA");
linkTable.Columns.Add("RepairWip");
linkTable.Columns.Add("MRB");
//linkTable.Columns.Add("REWORK");
linkTable.Columns.Add("JOBFINISH");
DataRow drd = resdt.NewRow();
DataRow linkDataRow = linkTable.NewRow();
drd["WorkOrderNo"] = wo;
drd["Skuno"] = dtwo.Rows[0]["Skuno"].ToString();
drd["DATS"] = dtwo.Rows[0]["DATS"].ToString();
drd["QTY"] = dtwo.Rows[0]["WORKORDER_QTY"].ToString();
// drd["STOCKIN"]= dtwo.Rows[0]["FINISHED_QTY"].ToString();
//string Sqlsncount =$@" select NEXT_STATION, count(NEXT_STATION)c from r_sn where (REPAIR_FAILED_FLAG <> 1 or REPAIR_FAILED_FLAG is null)
// and(COMPLETED_FLAG = 0 or NEXT_STATION = 'JOBFINISH')
// and workorderno = '{wo}' group by NEXT_STATION";
//
string Sqlsncount = $@" select NEXT_STATION, count(NEXT_STATION)c from r_sn where (REPAIR_FAILED_FLAG <> 1 or REPAIR_FAILED_FLAG is null)
and workorderno = '{wo}' and NEXT_STATION <>'REWORK' group by NEXT_STATION";
long loadingNum = 0;
long mrbNum = 0;
DataTable dtsncont = SFCDB.RunSelect(Sqlsncount).Tables[0];
RunSqls.Add(Sqlsncount);
for (int i = 0; i < dtsncont.Rows.Count; i++)
{
drd[dtsncont.Rows[i]["NEXT_STATION"].ToString()] = dtsncont.Rows[i]["c"].ToString();
linkDataRow[dtsncont.Rows[i]["NEXT_STATION"].ToString()] = (dtsncont.Rows[i]["c"].ToString() != "0") ? (linkURL + dtsncont.Rows[i]["NEXT_STATION"].ToString()) : "";
loadingNum = loadingNum + Convert.ToInt64(dtsncont.Rows[i]["c"].ToString());
}
string SqlRepairCount = $@" select count(1) repaircount from r_sn where REPAIR_FAILED_FLAG = 1 and workorderno = '{wo}'";
DataTable dtrepaircont = SFCDB.RunSelect(SqlRepairCount).Tables[0];
RunSqls.Add(SqlRepairCount);
drd["RepairWip"] = dtrepaircont.Rows[0]["repaircount"].ToString();
linkDataRow["RepairWip"] = (dtrepaircont.Rows[0]["repaircount"].ToString() != "0") ? (linkURL + "RepairWip") : "";
//string SqlMrbCount = $@"select count(1) mrbcount from r_mrb where workorderno = '{wo}' and rework_wo is null";
string SqlMrbCount = $@"select count(1) mrbcount from r_mrb where workorderno = '{wo}' ";
DataTable dtmrbcont = SFCDB.RunSelect(SqlMrbCount).Tables[0];
RunSqls.Add(SqlMrbCount);
drd["MRB"] = dtmrbcont.Rows[0]["mrbcount"].ToString();
mrbNum = Convert.ToInt64(dtmrbcont.Rows[0]["mrbcount"].ToString());
linkDataRow["MRB"] = (dtmrbcont.Rows[0]["mrbcount"].ToString() != "0") ? (linkURL + "MRB") : "";
//loadingNum = loadingNum + Convert.ToInt64(dtrepaircont.Rows[0]["repaircount"].ToString()) + Convert.ToInt64(dtmrbcont.Rows[0]["mrbcount"].ToString());
foreach (DataColumn dc in resdt.Columns)
{
if (dc.ColumnName.ToString().ToUpper().IndexOf("LOADING") > 0 || dc.ColumnName.ToString().ToUpper().IndexOf("LINK") > -1)
{
drd[dc.ColumnName.ToString()] = Convert.ToInt64(dtwo.Rows[0]["WORKORDER_QTY"].ToString()) - loadingNum - mrbNum;
}
}
resdt.Rows.Add(drd);
linkTable.Rows.Add(linkDataRow);
ReportTable retTab = new ReportTable();
retTab.LoadData(resdt, linkTable);
retTab.Tittle = "WO WIP";
//retTab.ColNames.RemoveAt(0);
Outputs.Add(retTab);
if (resdt.Rows.Count > 0)
{
List <object> objList = new List <object>();
pieChart pie = new pieChart();
pie.Tittle = "工單" + wo + "WIP分佈餅狀圖";
pie.ChartTitle = "主標題";
pie.ChartSubTitle = "副標題";
ChartData chartData = new ChartData();
chartData.name = "WOLIST";
chartData.type = ChartType.pie.ToString();
for (int j = 0; j < resdt.Rows.Count; j++)
{
foreach (DataColumn column in resdt.Columns)
{
columnName = column.ColumnName.ToString().ToUpper();
if (columnName != "WORKORDERNO" && columnName != "SKUNO" && columnName != "DATS" && columnName != "QTY" && resdt.Rows[j][columnName].ToString() != "" && resdt.Rows[j][columnName].ToString() != "0")
{
objList.Add(new List <object> {
columnName, Convert.ToInt64(resdt.Rows[j][columnName].ToString())
});
}
}
}
chartData.data = objList;
chartData.colorByPoint = true;
List <ChartData> _ChartDatas = new List <ChartData> {
chartData
};
pie.ChartDatas = _ChartDatas;
Outputs.Add(pie);
}
DBPools["SFCDB"].Return(SFCDB);
}
catch (Exception ee)
{
DBPools["SFCDB"].Return(SFCDB);
}
}
public override void Run()
{
DataTable dt = null;
string lot_no = LotNo.Value.ToString();
OleExec sfcdb = DBPools["SFCDB"].Borrow();
DataRow linkDataRow = null;
if (lot_no == "" || LotNo.Value == null)
{
ReportAlart alart = new ReportAlart("lot_no Can not be null");
Outputs.Add(alart);
return;
}
DataTable linkTable = new DataTable();
try {
string strSql = $@"select rld.sn,
rld.workorderno,
rpk.pack_no carton,
rlp.packno pallet,
decode(rld.status,'','未抽檢',1,'PASS',2,'FAIL') status,
rls.lot_no,
rls.edit_emp,
rld.edit_time
from r_lot_status rls,
r_lot_pack rlp,
r_lot_detail rld,
r_sn rsn,
r_sn_packing rspk,
r_packing rpk
where rls.id = rld.lot_id
and rlp.lotno = rls.lot_no
and rld.sn = rsn.sn
and rsn.id = rspk.sn_id
and rspk.pack_id = rpk.id
and rls.lot_no = '{lot_no}' order by rld.edit_time";
dt = sfcdb.RunSelect(strSql).Tables[0];
if (sfcdb != null)
{
DBPools["SFCDB"].Return(sfcdb);
}
if (dt.Rows.Count == 0)
{
ReportAlart alart = new ReportAlart("No Data!");
Outputs.Add(alart);
return;
}
linkTable.Columns.Add("sn");
linkTable.Columns.Add("skuno");
linkTable.Columns.Add("workorderno");
linkTable.Columns.Add("carton");
linkTable.Columns.Add("pallet");
linkTable.Columns.Add("status");
linkTable.Columns.Add("lotno");
linkTable.Columns.Add("edit_emp");
linkTable.Columns.Add("edit_time");
for (int i = 0; i < dt.Rows.Count; i++)
{
linkDataRow = linkTable.NewRow();
//跳轉的頁面鏈接
linkDataRow["sn"] = "Link#/FunctionPage/Report/Report.html?ClassName=MESReport.BaseReport.SNReport&RunFlag=1&SN=" + dt.Rows[i]["SN"].ToString();
linkDataRow["skuno"] = "";
linkDataRow["workorderno"] = "";
linkDataRow["carton"] = "";
linkDataRow["pallet"] = "";
linkDataRow["status"] = "";
linkDataRow["lotno"] = "";
linkDataRow["edit_emp"] = "";
linkDataRow["edit_time"] = "";
linkTable.Rows.Add(linkDataRow);
}
ReportTable reportTable = new ReportTable();
reportTable.LoadData(dt, linkTable);
reportTable.Tittle = "OBASAMPLE DETAIL";
Outputs.Add(reportTable);
} catch (Exception ex) {
if (sfcdb != null)
{
DBPools["SFCDB"].Return(sfcdb);
}
ReportAlart alart = new ReportAlart(ex.Message);
Outputs.Add(alart);
return;
}
}
