public DateTime GetDBDateTime() { OleExec sfcdb = _DBPools["SFCDB"].Borrow(); try { string strSql = "select sysdate from dual"; if (DBTYPE == DB_TYPE_ENUM.Oracle) { strSql = "select sysdate from dual"; } else if (DBTYPE == DB_TYPE_ENUM.SqlServer) { strSql = "select get_date() "; } else { throw new Exception(DBTYPE.ToString() + " not Work"); } DateTime DBTime = (DateTime)sfcdb.ExecSelectOneValue(strSql); _DBPools["SFCDB"].Return(sfcdb); return(DBTime); } catch (Exception e) { _DBPools["SFCDB"].Return(sfcdb); throw e; } }
/// <summary> /// Get DB system datetime /// </summary> /// <param name="DB">OleExec</param> /// <param name="dbType">DB_TYPE_ENUM</param> /// <returns></returns> public static DateTime GetDBDateTime(OleExec DB, DB_TYPE_ENUM dbType) { string strSql = "select sysdate from dual"; if (dbType == DB_TYPE_ENUM.Oracle) { strSql = "select sysdate from dual"; } else if (dbType == DB_TYPE_ENUM.SqlServer) { strSql = "select get_date() "; } else { throw new Exception(dbType.ToString() + " not Work"); } DateTime DBTime = (DateTime)DB.ExecSelectOneValue(strSql); return(DBTime); }
public int RecordPanelStationDetail(string PanelNo, string Line, string StationName, string DeviceName, string Bu, OleExec DB, DB_TYPE_ENUM DBType) { int result = 0; string strSql = string.Empty; DataTable dt = new DataTable(); string errMsg = string.Empty; T_R_SN RSN = new T_R_SN(DB, DBType); if (DBType.Equals(DB_TYPE_ENUM.Oracle)) { strSql = $@"SELECT * FROM R_SN WHERE ID IN (SELECT SN FROM R_PANEL_SN WHERE PANEL='{PanelNo}')"; dt = DB.ExecSelect(strSql, null).Tables[0]; foreach (DataRow dr in dt.Rows) { RSN.RecordPassStationDetail(dr["SN"].ToString(), Line, StationName, DeviceName, Bu, DB); } } else { errMsg = MESReturnMessage.GetMESReturnMessage("MES00000019", new string[] { DBType.ToString() }); throw new MESReturnMessage(errMsg); } return(result); }
/// <summary> /// 獲取BIP Panel未分板數量 /// </summary> /// <param name="PanelSn"></param> /// <param name="DB"></param> /// <param name="DBType"></param> /// <returns></returns> public int PanelNoBIPQty(string PanelSn, OleExec DB, DB_TYPE_ENUM DBType) { int NoBIPQty = 0; string errMsg = string.Empty; if (DBType.Equals(DB_TYPE_ENUM.Oracle)) { string strsql = $@"select count(1) from r_sn where sn in ( select sn from r_panel_sn where panel = '{PanelSn}' ) and id = sn"; NoBIPQty = int.Parse(DB.ExecSelectOneValue(strsql).ToString()); } else { errMsg = MESReturnMessage.GetMESReturnMessage("MES00000019", new string[] { DBType.ToString() }); throw new MESReturnMessage(errMsg); } return(NoBIPQty); }
public int SetPanelInValid(string PanelNo, OleExec DB, DB_TYPE_ENUM DBType) { string strSql = string.Empty; int result = 0; string errMsg = string.Empty; if (DBType.Equals(DB_TYPE_ENUM.Oracle)) { strSql = $@"UPDATE R_SN SET VALID_FLAG='0',CURRENT_STATION='UNDO_LOADING' WHERE ID IN (SELECT SN FROM R_PANEL_SN WHERE PANEL='{PanelNo}')"; result = DB.ExecSqlNoReturn(strSql, null); } else { errMsg = MESReturnMessage.GetMESReturnMessage("MES00000019", new string[] { DBType.ToString() }); throw new MESReturnMessage(errMsg); } return(result); }
public int ReplaceRPanelSn(string NewSn, string OldSn, OleExec DB, DB_TYPE_ENUM DBType) { int result = 0; string strSql = string.Empty; if (this.DBType == DB_TYPE_ENUM.Oracle) { strSql = $@"UPDATE r_panel_sn R SET R.SN='{NewSn}' WHERE R.SN='{OldSn}'"; result = DB.ExecSqlNoReturn(strSql, null); } else { string errMsg = MESReturnMessage.GetMESReturnMessage("MES00000019", new string[] { DBType.ToString() }); throw new MESReturnMessage(errMsg); } return(result); }
public int GetRepairedCount(string sn, OleExec DB, DB_TYPE_ENUM DBType) { int result = 0; string strSql = string.Empty; DataTable dt = new DataTable(); if (this.DBType == DB_TYPE_ENUM.Oracle) { strSql = $@"select * from r_repair_main where sn='{sn}' and closed_flag='1'"; dt = DB.ExecSelect(strSql).Tables[0]; result = dt.Rows.Count; } else { string errMsg = MESReturnMessage.GetMESReturnMessage("MES00000019", new string[] { DBType.ToString() }); throw new MESReturnMessage(errMsg); } return(result); }
public static DataObjectInfo GetDataObjectInfo(string TableName, OleExec DB, DB_TYPE_ENUM DBType) { //先從靜態緩存中檢索,命中則返回,否則加載新的 string strTableName = TableName.ToUpper(); string strSql = ""; DataObjectInfo info = null; if (TableConfigs.ContainsKey(strTableName)) { info = (DataObjectInfo)TableConfigs[strTableName].Data; return(info); } info = new DataObjectInfo(); if (DBType == DB_TYPE_ENUM.Oracle) { //查詢Table元數據 strSql = $@"SELECT T1.OWNER, T1.TABLE_NAME, T1.COLUMN_NAME, T1.NULLABLE, T1.DATA_TYPE , T1.DATA_LENGTH , T2.COMMENTS FROM all_tab_cols T1, all_col_comments T2 WHERE T1.TABLE_NAME = T2.TABLE_NAME AND T1.COLUMN_NAME = T2.COLUMN_NAME AND T1.TABLE_NAME = '{strTableName}'"; DataSet res = DB.ExecSelect(strSql); for (int i = 0; i < res.Tables[0].Rows.Count; i++) { DataObjectColInfo CI = new DataObjectColInfo(); CI.name = res.Tables[0].Rows[i]["COLUMN_NAME"].ToString(); switch (res.Tables[0].Rows[i]["DATA_TYPE"].ToString()) { case "VARCHAR2": case "NVARCHAR2": case "CHAR": case "CLOB": CI.DataType = typeof(string); break; case "DATE": CI.DataType = typeof(DateTime); break; case "NUMBER": case "FLOAT": CI.DataType = typeof(double); break; case "LONG": CI.DataType = typeof(long); break; case "BLOB": CI.DataType = typeof(object); break; default: throw new Exception($@"Table:{strTableName} Col: {CI.name} dataType:{res.Tables[0].Rows[i]["DATA_TYPE"].ToString()} 不受支持"); } CI.length = double.Parse(res.Tables[0].Rows[i]["DATA_LENGTH"].ToString()); CI.nullable = res.Tables[0].Rows[i]["NULLABLE"].ToString() == "Y" ? true : false; info.BaseColsInfo.Add(CI); } DictionaryItem item = new DictionaryItem(); info.TableName = TableName; item.Data = info; TableConfigs.Add(TableName, item); return(info); } else { throw new Exception("數據庫類型:" + DBType.ToString() + "不受支持"); } }
/// <summary> /// Get Work Class /// </summary> /// <param name="oleDB">OleExec</param> /// <param name="DBtype">DB_TYPE_ENUM</param> /// <returns></returns> public string GetWorkClass(OleExec oleDB, DB_TYPE_ENUM DBtype) { string strSql = "select sysdate from dual"; DateTime sysdate; DateTime start_time; DateTime end_time; DateTime tempTime1 = Convert.ToDateTime("00:00:00"); DateTime tempTime24 = Convert.ToDateTime("23:59:59"); string workClass = ""; string sql = $@"select * from c_work_class "; if (DBtype == DB_TYPE_ENUM.Oracle) { strSql = "select sysdate from dual"; } else if (DBtype == DB_TYPE_ENUM.SqlServer) { strSql = "select get_date() "; } else { throw new Exception(DBtype.ToString() + " not Work"); } try { sysdate = Convert.ToDateTime(oleDB.ExecSelectOneValue(strSql)); DataSet dsWorkClass = oleDB.ExecSelect(sql); foreach (DataRow row in dsWorkClass.Tables[0].Rows) { start_time = Convert.ToDateTime(row["START_TIME"].ToString()); end_time = Convert.ToDateTime(row["End_TIME"].ToString()); if (DateTime.Compare(start_time, end_time) > 0) { if ((DateTime.Compare(sysdate, tempTime1) >= 0) && (DateTime.Compare(sysdate, end_time) <= 0)) { workClass = row["NAME"].ToString(); break; } else if ((DateTime.Compare(sysdate, tempTime24) >= 0) && (DateTime.Compare(sysdate, start_time) <= 0)) { workClass = row["NAME"].ToString(); break; } } else { if ((DateTime.Compare(sysdate, start_time) >= 0) && (DateTime.Compare(sysdate, end_time) <= 0)) { workClass = row["NAME"].ToString(); break; } } } if (string.IsNullOrEmpty(workClass)) { throw new Exception("Get Work Class Fail"); } return(workClass); } catch (Exception ex) { throw ex; } }