public override void Run()
{
DateTime stime = Convert.ToDateTime(StartTime.Value);
DateTime etime = Convert.ToDateTime(EndTime.Value);
string svalue = stime.ToString("yyyy/MM/dd HH:mm:ss");
string evalue = etime.ToString("yyyy/MM/dd HH:mm:ss");
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
string sqlOba = $@" SELECT* FROM R_LOT_STATUS WHERE EDIT_TIME BETWEEN TO_DATE('{svalue}', 'YYYY/MM/DD HH24:MI:SS')
AND TO_DATE('{evalue}', 'YYYY/MM/DD HH24:MI:SS')";
DataSet res = SFCDB.RunSelect(sqlOba);
ReportTable retTab = new ReportTable();
retTab.LoadData(res.Tables[0], null);
retTab.Tittle = "OBA";
retTab.ColNames.RemoveAt(0);//不顯示ID列 add by fgg 2018.03.09
Outputs.Add(retTab);
DBPools["SFCDB"].Return(SFCDB);
}
catch (Exception ee)
{
DBPools["SFCDB"].Return(SFCDB);
throw ee;
}
}
public override void Run()
{
//base.Run();
string wo = inputWo.Value.ToString();
string eventName = inputEventName.Value.ToString().ToUpper();
string sqlRun = string.Empty;
DataTable snListTable = new DataTable();
DataTable linkTable = new DataTable();
DataRow linkRow = null;
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
if (eventName.Equals("REPAIRWIP"))
{
sqlRun = $@"select distinct sn,next_station as station,edit_time from r_sn where REPAIR_FAILED_FLAG = 1 and workorderno ='{wo}'";
}
else if (eventName.Equals("MRB"))
{
sqlRun = $@"select distinct sn,'MRB' as station,edit_time from r_mrb where workorderno = '{wo}' and rework_wo is null";
}
else
{
//sqlRun = $@"select sn,next_station as station,edit_time from r_sn where workorderno='{wo}' and next_station='{eventName}'";
//sqlRun = $@"select a.sn,a.next_station as station,a.edit_time,b.panel from r_sn a,r_panel_sn b where a.workorderno='{wo}' and a.next_station='{eventName}' and a.sn=b.sn";
sqlRun = $@" select a.sn,a.next_station as station,a.edit_time,b.panel from r_sn a left join r_panel_sn b on a.sn=b.sn where a.workorderno='{wo}' and a.next_station='{eventName}' ";
}
RunSqls.Add(sqlRun);
//OleExec SFCDB = DBPools["SFCDB"].Borrow();
//try
//{
snListTable = SFCDB.RunSelect(sqlRun).Tables[0];
DBPools["SFCDB"].Return(SFCDB);
linkTable.Columns.Add("SN");
linkTable.Columns.Add("STATION");
linkTable.Columns.Add("EDIT_TIME");
linkTable.Columns.Add("PANEL");
for (int i = 0; i < snListTable.Rows.Count; i++)
{
linkRow = linkTable.NewRow();
linkRow["SN"] = "Link#/FunctionPage/Report/Report.html?ClassName=MESReport.BaseReport.SNReport&RunFlag=1&SN=" + snListTable.Rows[i]["SN"].ToString();
linkRow["STATION"] = "";
linkRow["EDIT_TIME"] = "";
linkRow["PANEL"] = "";
linkTable.Rows.Add(linkRow);
}
ReportTable reportTable = new ReportTable();
reportTable.LoadData(snListTable, linkTable);
reportTable.Tittle = "SNList";
//reportTable.ColNames.RemoveAt(0);
Outputs.Add(reportTable);
}
catch (Exception exception)
{
DBPools["SFCDB"].Return(SFCDB);
throw exception;
}
}
public override void Run()
{
string sqlCode = "";
string sqlRun = "";
if (errorCode.Value != null && !string.IsNullOrEmpty(errorCode.Value.ToString()))
{
sqlCode = $@" and error_code ='{errorCode.Value.ToString()}' ";
}
sqlRun = Sqls["SqlErrorCode"] + sqlCode;
RunSqls.Add(sqlRun);
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
DataSet dsUresymptom = SFCDB.RunSelect(sqlRun);
ReportTable reportTable = new ReportTable();
reportTable.LoadData(dsUresymptom.Tables[0], null);
reportTable.Tittle = "ErrorCodeTable";
reportTable.ColNames.RemoveAt(0);
Outputs.Add(reportTable);
DBPools["SFCDB"].Return(SFCDB);
}
catch (Exception exception)
{
DBPools["SFCDB"].Return(SFCDB);
throw exception;
}
}
public R_FILE GetFileByName(string Name, string UseType, OleExec DB)
{
string strSql = $@"select r.id, r.name,r.filename,r.md5,r.usetype,r.valid,r.state,r.edit_time,r.edit_emp,blob_file from r_file r where r.name='{Name}' and r.usetype ='{UseType}' and r.valid = 1";
DataSet ret = DB.RunSelect(strSql);
Row_R_FILE r = (Row_R_FILE)this.NewRow();
if (ret.Tables[0].Rows.Count > 0)
{
r.loadData(ret.Tables[0].Rows[0]);
}
//if (r.NAME != null)
//{
// strSql = $@"select ID , clob_file , blob_file from r_file r where r.name='{Name}' and r.usetype ='{UseType}' and r.valid = 1";
// //System.Data.OleDb.OleDbParameter para = new System.Data.OleDb.OleDbParameter(":FILE", "");
// //para.Direction = ParameterDirection.Output;
// //para.Size = 999999999;
// ////para.DbType = DbType.Object;
// //DB.ExecSqlNoReturn(strSql, new System.Data.OleDb.OleDbParameter[] { para });
// System.Data.OleDb.OleDbDataReader reader = DB.RunDataReader(strSql);
// if (reader.Read())
// {
// System.IO.TextReader TR = reader.GetTextReader(1);
// r.CLOB_FILE = TR.ReadToEnd();
// }
// reader.Close();
//}
return(r.GetDataObject());
}
private List <string> GetEvent()
{
OleExec SFCDB = DBPools["SFCDB"].Borrow();
List <string> listEvent = new List <string>();
try
{
DataTable dtEvent = SFCDB.RunSelect(Sqls["SqlGetEvent"]).Tables[0];
if (SFCDB != null)
{
DBPools["SFCDB"].Return(SFCDB);
}
foreach (DataRow row in dtEvent.Rows)
{
listEvent.Add(row["eventpoint"].ToString());
}
}
catch (Exception ex)
{
DBPools["SFCDB"].Return(SFCDB);
throw ex;
}
return(listEvent);
}
public void LockMe(OleExec DB)
{
string strSql = $@"select * from C_SN_RULE where ID = {ID} for update";
DataSet res = DB.RunSelect(strSql);
loadData(res.Tables[0].Rows[0]);
}
public List <string> GetItemDetailID(string ID, OleExec DB)
{
List <string> ret = new List <string>();
string strSql = $@"select ID from c_kp_list_item_detail c where c.item_id='{ID}' order by c.seq ";
DataSet res = DB.RunSelect(strSql);
for (int i = 0; i < res.Tables[0].Rows.Count; i++)
{
ret.Add(res.Tables[0].Rows[i]["ID"].ToString());
}
return(ret);
}
public List <string> GetListIDBySkuno(string Skuno, OleExec DB)
{
List <string> ret = new List <string>();
string strSql = $@"select ID from c_kp_list where skuno='{Skuno}'";
DataSet res = DB.RunSelect(strSql);
for (int i = 0; i < res.Tables[0].Rows.Count; i++)
{
ret.Add(res.Tables[0].Rows[i]["ID"].ToString());
}
return(ret);
}
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 Row_R_SN_PACKING GetDataBySNID(string SN_ID, OleExec DB)
{
string strSql = $@"select * from R_SN_PACKING where SN_ID = '{SN_ID}' ";
DataSet res = DB.RunSelect(strSql);
if (res.Tables[0].Rows.Count > 0)
{
Row_R_SN_PACKING ret = (Row_R_SN_PACKING)NewRow();
ret.loadData(res.Tables[0].Rows[0]);
return(ret);
}
return(null);
}
public R_FILE GetFileByFileName(string fileName, string UseType, OleExec DB)
{
string strSql = $@"select r.id, r.name,r.filename,r.md5,r.usetype,r.valid,r.state,r.edit_time,r.edit_emp,blob_file from r_file r where r.filename='{fileName}' and r.usetype ='{UseType}' and r.valid = 1";
DataSet ret = DB.RunSelect(strSql);
Row_R_FILE r = (Row_R_FILE)this.NewRow();
if (ret.Tables[0].Rows.Count > 0)
{
r.loadData(ret.Tables[0].Rows[0]);
}
return(r.GetDataObject());
}
public Row_R_Label GetLabelConfigByLabelName(string LabName, OleExec DB)
{
string strSql = $@"select * from R_Label r where r.labelname = '{LabName}'";
DataSet res = DB.RunSelect(strSql);
if (res.Tables[0].Rows.Count > 0)
{
Row_R_Label A = (Row_R_Label)NewRow();
A.loadData(res.Tables[0].Rows[0]);
return(A);
}
return(null);
}
public override void Run()
{
if (WO.Value == null)
{
throw new Exception("WO Can not be null");
}
string runSql = string.Format(Sqls["strGetWoSN"], WO.Value.ToString());
RunSqls.Add(runSql);
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
DataSet res = SFCDB.RunSelect(runSql);
ReportTable retTab = new ReportTable();
retTab.LoadData(res.Tables[0], null);
retTab.Tittle = "SN List";
retTab.ColNames.RemoveAt(0);
// piechart
pieChart retChart_pie = new pieChart();
retChart_pie.GetSample();
Outputs.Add(retChart_pie);
//linechart
LineChart retChart_line = new LineChart();
retChart_line.GetSample1();
LineChart retChart_spline = new LineChart();
retChart_spline.GetSample2();
LineChart retChart_area = new LineChart();
retChart_area.GetSample3();
//columnChart
columnChart retChart_column = new columnChart();
retChart_column.GetSample1();
Outputs.Add(retChart_column);
Outputs.Add(retChart_line);
Outputs.Add(retChart_spline);
Outputs.Add(retChart_area);
Outputs.Add(retTab);
DBPools["SFCDB"].Return(SFCDB);
}
catch (Exception ee)
{
DBPools["SFCDB"].Return(SFCDB);
}
}
public List <R_SN_KP> GetKPRecordBySnID(string SNID, OleExec SFCDB)
{
string strSql = $@"select * from r_sn_kp r where r_sn_id='{SNID}' order by r.itemseq,r.scanseq,r.detailseq ";
List <R_SN_KP> LR = new List <R_SN_KP>();
DataSet res = SFCDB.RunSelect(strSql);
for (int i = 0; i < res.Tables[0].Rows.Count; i++)
{
Row_R_SN_KP R = new Row_R_SN_KP(this.DataInfo);
R.loadData(res.Tables[0].Rows[i]);
LR.Add(R.GetDataObject());
}
return(LR);
}
public bool KpIsLinkBySN(string snID, string kp, OleExec sfcdb)
{
string sql = $@"select * from r_sn_kp where r_sn_id='{snID}' and value ='{kp}'";
DataSet ds = sfcdb.RunSelect(sql);
if (ds.Tables[0].Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
public List <C_SN_RULE> GetAllData(OleExec DB)
{
List <C_SN_RULE> ret = new List <C_SN_RULE>();
string strSql = $@"select * from C_SN_RULE ";
DataSet res = DB.RunSelect(strSql);
for (int i = 0; i < res.Tables[0].Rows.Count; i++)
{
Row_C_SN_RULE r = (Row_C_SN_RULE)NewRow();
r.loadData(res.Tables[0].Rows[0]);
ret.Add(r.GetDataObject());
}
return(ret);
}
public Row_C_SN_RULE GetDataByName(string Name, OleExec DB)
{
string strSql = $@"select * from C_SN_RULE where Name='{Name}'";
DataSet res = DB.RunSelect(strSql);
if (res.Tables[0].Rows.Count > 0)
{
Row_C_SN_RULE ret = (Row_C_SN_RULE)NewRow();
ret.loadData(res.Tables[0].Rows[0]);
return(ret);
}
return(null);
}
public List <C_Label_Type> GetLabelTypes(OleExec DB)
{
List <C_Label_Type> ret = new List <C_Label_Type>();
string strSql = $@"select * from C_LABEL_TYPE ";
DataSet res = DB.RunSelect(strSql);
for (int i = 0; i < res.Tables[0].Rows.Count; i++)
{
Row_C_Label_Type R = (Row_C_Label_Type)NewRow();
R.loadData(res.Tables[0].Rows[i]);
ret.Add(R.GetDataObject());
}
return(ret);
}
public bool CheckDataExist(string Wo, string Partno, string ReplacePartno, OleExec DB)
{
string strSql = $@" select * from R_WO_KP_Repalce where wo = {Wo} and partno='{Partno}' and repalcepartno='{ReplacePartno}' ";
DataSet res = DB.RunSelect(strSql);
if (res.Tables[0].Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool KpIDIsExist(string kpID, OleExec sfcdb)
{
string strSql = $@"select ID from c_kp_list where id='{kpID}'";
DataSet ds = sfcdb.RunSelect(strSql);
if (ds.Tables[0].Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
public List <Row_R_SN_PACKING> GetPackItem(string PackID, OleExec DB)
{
List <Row_R_SN_PACKING> ret = new List <Row_R_SN_PACKING>();
string strSql = $@"select * from R_SN_PACKING where PACK_ID = '{PackID}' ";
DataSet res = DB.RunSelect(strSql);
for (int i = 0; i < res.Tables[0].Rows.Count; i++)
{
Row_R_SN_PACKING R = (Row_R_SN_PACKING)NewRow();
R.loadData(res.Tables[0].Rows[i]);
ret.Add(R);
}
return(ret);
}
public List <R_FILE> GetFileList(string UseType, OleExec DB)
{
List <R_FILE> ret = new List <R_FILE>();
string strSql = $@"select r.id, r.name,r.filename,r.md5,r.usetype,r.valid,r.state,r.edit_time,r.edit_emp from r_file r where r.usetype ='{UseType}' and r.valid = 1";
DataSet res = DB.RunSelect(strSql);
for (int i = 0; i < res.Tables[0].Rows.Count; i++)
{
Row_R_FILE RRF = (Row_R_FILE)NewRow();
RRF.loadData(res.Tables[0].Rows[i]);
ret.Add(RRF.GetDataObject());
}
return(ret);
}
public bool CheckLinkBySNID(string snID, OleExec sfcdb)
{
string sql = $@"select * from r_sn_kp where r_sn_id='{snID}' and value is not null";
DataSet ds = sfcdb.RunSelect(sql);
if (ds.Tables[0].Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool CheckLinkByValue(string value, OleExec sfcdb)
{
string sql = $@"select * from r_sn_kp where value ='{value}' AND SCANTYPE IN ('SystemSN')";
DataSet ds = sfcdb.RunSelect(sql);
if (ds.Tables[0].Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
public List <R_Label> GetLabelList(OleExec DB)
{
List <R_Label> ret = new List <R_Label>();
string strSql = $@"select * from R_Label";
DataSet res = DB.RunSelect(strSql);
for (int i = 0; i < res.Tables[0].Rows.Count; i++)
{
Row_R_Label A = (Row_R_Label)NewRow();
A.loadData(res.Tables[0].Rows[i]);
ret.Add((R_Label)A.GetDataObject());
}
return(ret);
}
public List <R_WO_KP_Repalce> GetWoRepalceKpBySkuPartno(string Sku, string Partno, string ReplaceKp, OleExec DB)
{
List <R_WO_KP_Repalce> ret = new List <R_WO_KP_Repalce>();
string strSql = $@" select a.* from R_WO_KP_Repalce a,r_wo_base b where a.wo=b.workorderno and b.skuno='{Sku}' and a.partno='{Partno}' and a.repalcepartno='{ReplaceKp}' ";
DataSet res = DB.RunSelect(strSql);
for (int i = 0; i < res.Tables[0].Rows.Count; i++)
{
Row_R_WO_KP_Repalce R = new Row_R_WO_KP_Repalce(this.DataInfo);
R.loadData(res.Tables[0].Rows[i]);
ret.Add(R.GetDataObject());
}
return(ret);
}
public List <Row_C_CODE_MAPPING> GetDataByName(string name, OleExec DB)
{
List <Row_C_CODE_MAPPING> RET = new List <Row_C_CODE_MAPPING>();
string strSql = $@"select * from c_code_mapping where codetype='{name}' order by SEQ";
DataSet res = DB.RunSelect(strSql);
for (int i = 0; i < res.Tables[0].Rows.Count; i++)
{
Row_C_CODE_MAPPING newRow = (Row_C_CODE_MAPPING)NewRow();
newRow.loadData(res.Tables[0].Rows[i]);
RET.Add(newRow);
}
return(RET);
}
public Row_C_Label_Type GetConfigByName(string Name, OleExec DB)
{
string strSql = $@"select * from C_LABEL_TYPE where name = '{Name}'";
DataSet res = DB.RunSelect(strSql);
for (int i = 0; i < res.Tables[0].Rows.Count; i++)
{
Row_C_Label_Type R = (Row_C_Label_Type)NewRow();
R.loadData(res.Tables[0].Rows[i]);
return(R);
}
return(null);
}
public List <Row_C_SKU_Label> GetLabelConfigBySku(string Skuno, OleExec DB)
{
List <Row_C_SKU_Label> ret = new List <Row_C_SKU_Label>();
string strSql = $@"select * from c_sku_label where skuno='{Skuno}'";
DataSet res = DB.RunSelect(strSql);
for (int i = 0; i < res.Tables[0].Rows.Count; i++)
{
Row_C_SKU_Label R = (Row_C_SKU_Label)NewRow();
R.loadData(res.Tables[0].Rows[i]);
ret.Add(R);
}
return(ret);
}
public override void Run()
{
DateTime startDT = (DateTime)startTime.Value;
DateTime endDT = (DateTime)endTime.Value;
string dateFrom = $@"to_date('{startDT.ToString("yyyy-MM-dd HH:mm:ss")}', 'yyyy-MM-dd hh24:mi:ss')";
string dateTO = $@"to_date('{endDT.ToString("yyyy-MM-dd HH:mm:ss")}', 'yyyy-MM-dd hh24:mi:ss')";
string sqlTaskNo = "";
string sqlHWPN = "";
string sqlProductLine = "";
string sqlRun = "";
if (taskNo.Value != null && !string.IsNullOrEmpty(taskNo.Value.ToString()))
{
sqlTaskNo = $@" and TASK_NO ='{taskNo.Value.ToString()}' ";
}
if (item.Value != null && !string.IsNullOrEmpty(item.Value.ToString()))
{
sqlHWPN = $@" AND ITEM = '{item.Value.ToString()}' ";
}
if (productLine.Value.ToString() != "ALL" && !string.IsNullOrEmpty(productLine.Value.ToString()))
{
sqlProductLine = $@" and PRODUCT_LINE = '{productLine.Value.ToString()}'";
}
//現在數據庫中沒有r_7b5_xml_t這個表,故直接DB Link 舊的數據庫
sqlRun = $@"select * from r_7b5_xml_t@hwd where 1=1 and lasteditdt between {dateFrom} and {dateTO} {sqlTaskNo} {sqlHWPN} {sqlProductLine}
order by TASK_NO,lasteditdt";
RunSqls.Add(sqlRun);
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
DataSet ds7B5 = SFCDB.RunSelect(sqlRun);
if (SFCDB != null)
{
DBPools["SFCDB"].Return(SFCDB);
}
ReportTable reportTable = new ReportTable();
reportTable.LoadData(ds7B5.Tables[0], null);
reportTable.Tittle = "7B5Table";
Outputs.Add(reportTable);
DBPools["SFCDB"].Return(SFCDB);
}
catch (Exception exception)
{
DBPools["SFCDB"].Return(SFCDB);
throw exception;
}
}
