public bool CheckWoTextByWo(string Workorderno, bool Download_Auto, string ColumnName, OleExec DB, DB_TYPE_ENUM DBType)
{
bool CheckFlag = false;
string StrSql = "";
string StrReturnMsg = "";
int n = 0;
if (Download_Auto)
{
StrSql = $@"select * from R_WO_TEXT where AUFNR='{Workorderno}' ";
DataTable dt = DB.ExecSelect(StrSql).Tables[0];
if (dt.Rows.Count > 0)
{
CheckFlag = true;
}
}
else
{
StrSql = $@"select * from R_WO_TEXT where AUFNR='{Workorderno}' ";
DataTable dt = DB.ExecSelect(StrSql).Tables[0];
if (dt.Rows.Count > 0)
{
StrSql = $@"insert into H_WO_TEXT(ID,{ ColumnName }) ";
StrSql = StrSql + $@" select* from R_WO_TEXT where AUFNR = '{Workorderno}' ";
StrReturnMsg = DB.ExecSqlReturn(StrSql);
int.TryParse(StrReturnMsg, out n);
if (n > 1)
{
StrSql = $@" delete from R_WO_TEXT where AUFNR = '{Workorderno}' ";
StrReturnMsg = DB.ExecSQL(StrSql);
int.TryParse(StrReturnMsg, out n);
CheckFlag = false;
}
}
}
return(CheckFlag);
}
public string GetCustomerName(OleExec oleDB, string strID)
{
string sql = $@"select customer_name from c_customer where id='{strID}' ";
return(oleDB.ExecSqlReturn(sql));
}
public override void Run()
{
OleExec sfcdb = DBPools["SFCDB"].Borrow();
OleExec apdb = DBPools["APDB"].Borrow();
DateTime onlineTime = DateTime.MinValue;
DataTable Data = new DataTable();
Data.Columns.Add("SMT線體");
Data.Columns.Add("工單號");
Data.Columns.Add("料號");
Data.Columns.Add("工單批量");
Data.Columns.Add("UPH");
Data.Columns.Add("Release時間");
Data.Columns.Add("工單上線及時性預警");
Data.Columns.Add("下線時間");
Data.Columns.Add("完成數量");
Data.Columns.Add("完成率");
Data.Columns.Add("SMT完工預警");
Data.Columns.Add("測試上線時間預警");
Data.Columns.Add("測試完成數量");
Data.Columns.Add("測試完成時間預警");
Data.Columns.Add("SMT工單關結及時預警");
Data.Columns.Add("任務令");
//查詢未關閉的工單 //只查SMT工單怎么查？
string strSql = @"SELECT WORKORDERNO, SKUNO, RELEASE_DATE, WORKORDER_QTY, FINISHED_QTY, CUST_ORDER_NO, SYSDATE
FROM R_WO_BASE
WHERE SUBSTR(WORKORDERNO, 1, 5) IN('00251', '00254')
AND CLOSED_FLAG = 0
AND RELEASE_DATE > TO_DATE('2018/05/01','YYYY/MM/DD')
--AND INPUT_QTY > 0
ORDER BY WORKORDERNO";
foreach (DataRow dr in sfcdb.RunSelect(strSql).Tables[0].Rows)
{
DataRow data = Data.NewRow();
Data.Rows.Add(data);
string strWo = dr["WORKORDERNO"].ToString();
data["工單號"] = strWo;
data["料號"] = dr["SKUNO"].ToString();
data["工單批量"] = dr["WORKORDER_QTY"].ToString();
data["Release時間"] = dr["RELEASE_DATE"].ToString();
if (dr["RELEASE_DATE"].ToString().Trim() == "")
{
data["Release時間"] = "Err:無Release時間";
continue;
}
DateTime releaseTime = DateTime.Parse(dr["RELEASE_DATE"].ToString());
DateTime nowTime = DateTime.Parse(dr["SYSDATE"].ToString());
data["任務令"] = dr["CUST_ORDER_NO"].ToString();
strSql = "SELECT COUNT(DISTINCT SN) FROM R_SN WHERE WORKORDERNO='" + strWo + "' AND COMPLETED_FLAG='1'";
data["測試完成數量"] = sfcdb.ExecSqlReturn(strSql);
strSql = "SELECT COUNT(DISTINCT SN) FROM R_SN_STATION_DETAIL WHERE WORKORDERNO='" + strWo + "' AND STATION_NAME='BIP'";
int bipFinishQty = Int32.Parse(sfcdb.ExecSqlReturn(strSql));
data["完成數量"] = bipFinishQty.ToString();
data["完成率"] = ConvertTimeHour((((float)bipFinishQty / Int32.Parse(dr["WORKORDER_QTY"].ToString())) * 100).ToString()) + "%";
strSql = $@"SELECT MIN(LINE) LINE, MIN(EDIT_TIME) ONLINE_TIME
FROM R_SN_STATION_DETAIL
WHERE WORKORDERNO = '{strWo}'
AND STATION_NAME IN('PRINT1', 'LINK')
AND EDIT_TIME IN
(SELECT MIN(EDIT_TIME)
FROM R_SN_STATION_DETAIL
WHERE WORKORDERNO = '{strWo}'
AND STATION_NAME IN('PRINT1', 'LINK'))";
//上線超時計算
DataTable dt = sfcdb.RunSelect(strSql).Tables[0];
//增加ONLINE_TIME不為空判斷，否則導致異常 fyq20180523
if (dt.Rows.Count > 0 && dt.Rows[0]["ONLINE_TIME"].ToString() != "")
{
data["SMT線體"] = dt.Rows[0]["LINE"].ToString();
onlineTime = DateTime.Parse(dt.Rows[0]["ONLINE_TIME"].ToString());
if (dt.Rows[0]["LINE"].ToString() == "")
{
data["工單上線及時性預警"] = "未上線";
//TimeSpan ts = DateTime.Now - relTime;
TimeSpan ts = nowTime - releaseTime;
if (ts.TotalHours > 16)
{
data["工單上線及時性預警"] = data["工單上線及時性預警"].ToString() +
"(已超時" + ConvertTimeHour((ts.TotalHours - 16).ToString()) + "小時)";
}
}
else
{
data["工單上線及時性預警"] = "已上線";
TimeSpan ts = onlineTime - releaseTime;
if (ts.TotalHours > 16)
{
data["工單上線及時性預警"] = data["工單上線及時性預警"].ToString() + "(已超時)";
}
}
}
strSql = $@"SELECT STATION_NAME
FROM (SELECT A.STATION_NAME, A.SEQ_NO
FROM C_ROUTE_DETAIL A,
R_WO_BASE B,
(SELECT M.ROUTE_ID, M.STATION_NAME, M.SEQ_NO
FROM C_ROUTE_DETAIL M, R_WO_BASE N
WHERE M.ROUTE_ID = N.ROUTE_ID
AND M.STATION_NAME IN ('P_FQC', 'SMT_FQC')
AND N.WORKORDERNO = '{strWo}') C
WHERE A.ROUTE_ID = B.ROUTE_ID
AND A.ROUTE_ID = C.ROUTE_ID(+)
AND (A.SEQ_NO < C.SEQ_NO OR
A.STATION_NAME IN ('VI1', 'VI2', 'BIP'))
AND B.WORKORDERNO = '{strWo}'
ORDER BY A.SEQ_NO DESC)
WHERE ROWNUM = 1";
//獲取下線工站
string strOfflineStation = sfcdb.ExecSqlReturn(strSql);
strSql = $@"SELECT TO_CHAR(MAX(EDIT_TIME), 'YYYY/MM/DD HH24:MI:SS') OFFLINE_TIME
FROM R_SN_STATION_DETAIL
WHERE WORKORDERNO = '{strWo}'
AND STATION_NAME = '{strOfflineStation}'";
data["下線時間"] = sfcdb.ExecSqlReturn(strSql);
strSql = $@"SELECT COUNT(DISTINCT SN) OFFQTY
FROM R_SN_STATION_DETAIL
WHERE WORKORDERNO = '{strWo}'
AND STATION_NAME = '{strOfflineStation}'
AND VALID_FLAG = '1'";
int offlineCount = Int32.Parse(sfcdb.ExecSqlReturn(strSql));
if (data["SMT線體"].ToString() != "")
{
strSql = $@"SELECT MAX(UPH_QTY)
FROM MES1.C_PNO_UPH
WHERE P_NO = '{data["料號"].ToString()}'
AND LINE_NAME = '{data["SMT線體"].ToString()}'
AND PROCESS_FLAG = 'B/T'";
data["UPH"] = apdb.ExecSqlReturn(strSql);
string strUPH = data["UPH"].ToString();
int indexPoint = strUPH.IndexOf('.');
if (indexPoint >= 0)
{
data["UPH"] = strUPH.Substring(0, indexPoint);
}
if (data["UPH"].ToString() == "")
{
data["UPH"] = "未配置UPH";
}
}
try
{
double uph = double.Parse(data["UPH"].ToString());
double uphTime = Int32.Parse(data["工單批量"].ToString()) / (uph * 0.8);
if (offlineCount == Int32.Parse(data["工單批量"].ToString()))
{
data["SMT完工預警"] = "已完工";
}
else
{
data["SMT完工預警"] = "未完工";
}
if (data["下線時間"].ToString() != "" && onlineTime != DateTime.MinValue)
{
DateTime offlineTime = DateTime.Parse(data["下線時間"].ToString());
TimeSpan ts = offlineTime - onlineTime;
if (ts.TotalHours - uphTime > 3)
{
data["SMT完工預警"] = data["SMT完工預警"] + "(已超時" + ConvertTimeHour((ts.TotalHours - uphTime - 3).ToString()) + "小時)";
}
}
}
catch
{ }
strSql = $@"SELECT COUNT(SN)
FROM R_SN
WHERE SN IN(SELECT SN
FROM R_SN_STATION_DETAIL
WHERE STATION_NAME = 'BIP'
AND WORKORDERNO = '{strWo}'
AND SYSDATE - EDIT_TIME > 0.5)
AND COMPLETED_FLAG = '0'";
data["測試完成時間預警"] = sfcdb.ExecSqlReturn(strSql); //時間為什么是數量呢？
strSql = $@"SELECT SN, EDIT_TIME
FROM R_SN_STATION_DETAIL
WHERE EDIT_TIME IN (SELECT MIN(EDIT_TIME)
FROM R_SN_STATION_DETAIL
WHERE STATION_NAME = 'BIP'
AND WORKORDERNO = '{strWo}'
AND (SYSDATE - EDIT_TIME) > (1 / 6))
AND WORKORDERNO = '{strWo}'";
DataTable dtFirstBIP = sfcdb.RunSelect(strSql).Tables[0];
if (dtFirstBIP.Rows.Count > 0)
{
DateTime fistBIPTime = DateTime.Parse(dtFirstBIP.Rows[0]["EDIT_TIME"].ToString());
//TimeSpan ts = DateTime.Now - fistBIPTime;
TimeSpan ts = nowTime - fistBIPTime;
//增加工單條件，因為一個SN可對應兩工單(SMT工單和BIP分板工單) fyq20180522
strSql = $@"SELECT COMPLETED_FLAG
FROM R_SN
WHERE SN = '{dtFirstBIP.Rows[0]["SN"].ToString()}'
AND WORKORDERNO = '{strWo}'";
if (sfcdb.ExecSqlReturn(strSql) != "1")
{
if (ts.TotalHours > 8)
{
data["測試上線時間預警"] = "超時" + ConvertTimeHour((ts.TotalHours - 8).ToString()) + "小時 未上線";
}
else
{
data["測試上線時間預警"] = "測試未完成";
}
}
else
{
data["測試上線時間預警"] = "測試完成";
//下面這個sql沒被調用？初衷是要取fistFinishTime？ 先屏蔽了fyq20180522
//strSql = $@"SELECT MAX(EDIT_TIME) EDIT_TIME
// FROM R_SN_STATION_DETAIL
// WHERE SN = '{dtFirstBIP.Rows[0]["SN"].ToString()}'
// AND WORKORDERNO = '{strWo}'
// AND STATION_NAME IN ('P_FQC', 'SMT_FQC')";
try
{
DateTime fistFinishTime = DateTime.Parse(dtFirstBIP.Rows[0]["EDIT_TIME"].ToString());//為什么fistFinishTime要取值于fistBIPTime？
ts = fistFinishTime - fistBIPTime;
if (ts.TotalHours > 8)
{
data["測試上線時間預警"] = "測試完成,超時" + ConvertTimeHour((ts.TotalHours - 8).ToString()) + "小時)";
}
else
{
data["測試上線時間預警"] = "測試完成";
}
}
catch
{
data["測試上線時間預警"] = "測試完成" + "(ERR)";
}
}
strSql = "SELECT COUNT(1) FROM R_SN WHERE WORKORDERNO='" + strWo + "' AND COMPLETED_FLAG='1'";
string jobfinishCount = sfcdb.ExecSqlReturn(strSql);
if (jobfinishCount == data["工單批量"].ToString())
{
data["SMT工單關結及時預警"] = "已完工";
try
{
strSql = $@"SELECT MAX(EDIT_TIME) FROM R_SN_STATION_DETAIL WHERE WORKORDERNO = '{strWo}' AND STATION_NAME IN('P_FQC', 'SMT_FQC')";
DateTime lastFinishTime = DateTime.Parse(sfcdb.ExecSqlReturn(strSql));
strSql = $@"SELECT MAX(EDIT_TIME) FROM R_SN_STATION_DETAIL WHERE WORKORDERNO = '{strWo}' AND STATION_NAME = 'BIP'";
DateTime lastBIPTime = DateTime.Parse(sfcdb.ExecSqlReturn(strSql));
ts = lastFinishTime - lastBIPTime;
if (ts.TotalHours > 24)
{
data["SMT工單關結及時預警"] = "已完工(超時" + ConvertTimeHour((ts.TotalHours - 24).ToString()) + "小時)";
}
}
catch
{ }
}
else
{
data["SMT工單關結及時預警"] = "未完工";
if (ts.TotalHours > 24)
{
data["SMT工單關結及時預警"] = "未完工(超時" + ConvertTimeHour((ts.TotalHours - 24).ToString()) + "小時)";
}
}
}
}
DBPools["SFCDB"].Return(sfcdb);
DBPools["APDB"].Return(apdb);
ReportTable retTab = new ReportTable();
retTab.LoadData(Data, null);
retTab.Tittle = "HWD SMT E2E Report";
Outputs.Add(retTab);
}
