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;
}
}