/// <summary>
/// ByPackNo加載該PackNo所在未關閉Lot信息
/// </summary>
/// <param name="packNo"></param>
/// <returns></returns>
public List <R_LOT_STATUS> GetRLotStatusWithWaitClose(OleExec DB, string packNo)
{
string strSql = $@" select DISTINCT B.* from R_LOT_PACK A,R_LOT_STATUS B where A.LOTNO=B.LOT_NO AND B.CLOSED_FLAG<>'2' AND A.PACKNO='{packNo}' ";
DataSet ds = DB.ExecSelect(strSql);
List <R_LOT_STATUS> res = new List <R_LOT_STATUS>();
T_R_LOT_STATUS tRLotStatus = new T_R_LOT_STATUS(DB, this.DBType);
foreach (DataRow VARIABLE in ds.Tables[0].Rows)
{
Row_R_LOT_STATUS r = (Row_R_LOT_STATUS)tRLotStatus.NewRow();
r.loadData(VARIABLE);
res.Add(r.GetDataObject());
}
return(res);
}
public List <R_SN_LOCK> GetLockListByPackNo(string packNo, OleExec DB)
{
List <R_SN_LOCK> res = new List <R_SN_LOCK>();
string strSql = $@" select E.* from R_PACKING A,R_PACKING B ,R_SN_PACKING C,R_SN D,R_SN_LOCK E WHERE A.ID=C.PACK_ID AND B.ID=A.PARENT_PACK_ID AND C.SN_ID=D.ID AND D.SN=E.SN
AND B.PACK_NO='{packNo}' AND E.LOCK_STATUS='1' ";
DataSet ds = DB.ExecSelect(strSql);
foreach (DataRow VARIABLE in ds.Tables[0].Rows)
{
Row_R_SN_LOCK r = (Row_R_SN_LOCK)this.NewRow();
r.loadData(VARIABLE);
res.Add(r.GetDataObject());
}
return(res);
}
public bool PackNoIsOnOBASampling(string packNo, OleExec DB)
{
string sql = $@"select * from r_lot_pack pack,r_lot_status status where pack.lotno=status.lot_no and status.closed_flag in ('0','1') and pack.packno='{packNo}'";
DataTable dt = DB.ExecSelect(sql).Tables[0];
if (dt.Rows.Count > 0)
{
return(true);
}
else
{
sql = $@"select * from r_lot_pack pack,r_lot_status status,(select b.* from r_packing a,r_packing b where a.pack_no='{packNo}'
and a.parent_pack_id=b.id) p where pack.lotno=status.lot_no and status.closed_flag in ('0','1') and pack.packno=p.pack_no ";
dt = DB.ExecSelect(sql).Tables[0];
if (dt.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
}
public DataTable GetCurrentDate(OleExec DB, string StrTimeList)
{
DataTable Dt = null;
try
{
string StrSql = $@"SELECT SYSDATE AS CurrentDate,TO_CHAR(SYSDATE,'HH24:MI:SS') AS CurrentTime FROM DUAL ";
Dt = DB.ExecSelect(StrSql).Tables[0];
}
catch (Exception ex)
{
throw (ex);
}
return(Dt);
}
public DataTable GetItemInfo(string Program_Name, string Item_Name, OleExec DB)
{
DataTable Dt = null;
try
{
string StrSql = $@"SELECT * from C_INTERFACE where program_name='{Program_Name}' and Item_Name='{Item_Name}'";
Dt = DB.ExecSelect(StrSql).Tables[0];
}
catch (Exception ex)
{
throw (ex);
}
return(Dt);
}
/// <summary>
/// add by ZGJ 2018-03-16
/// 判斷該 SN 是否有 MRB 過
/// </summary>
/// <param name="SerialNumber"></param>
/// <param name="WorkOrder"></param>
/// <param name="DB"></param>
/// <returns></returns>
public bool HadMrbed(string SerialNumber, OleExec DB)
{
bool isMrbed = false;
string sql = string.Empty;
DataTable dt = new DataTable();
sql = $@"SELECT * FROM R_MRB WHERE SN='{SerialNumber}' AND REWORK_WO IS NULL AND MRB_FLAG='1'";
dt = DB.ExecSelect(sql, null).Tables[0];
if (dt.Rows.Count > 0)
{
isMrbed = true;
}
return(isMrbed);
}
public List <R_PACKING> GetListPackByPackno(string packno, OleExec DB)
{
List <R_PACKING> packingList = new List <R_PACKING>();
Row_R_PACKING rowPacking;
string strSql = $@"select * from r_packing where PACK_NO='{packno}'";
DataSet ds = DB.ExecSelect(strSql);
foreach (DataRow row in ds.Tables[0].Rows)
{
rowPacking = (Row_R_PACKING)this.NewRow();
rowPacking.loadData(row);
packingList.Add(rowPacking.GetDataObject());
}
return(packingList);
}
/// <summary>
/// add by ZGJ 2018-03-16
/// 判斷是否有配置該機種不需要經過TS101檢測
/// </summary>
/// <param name="Skuno"></param>
/// <param name="DB"></param>
/// <returns></returns>
public bool SkuCheckTS101(string Skuno, OleExec DB)
{
bool NeedCheck = false;
string sql = string.Empty;
DataTable dt = new DataTable();
sql = $@"SELECT * FROM C_SKU_DETAIL S WHERE S.CATEGORY_NAME='MRB_NOT_CHECK_TS101'
AND S.CATEGORY='MRB_CTRL' AND S.SKUNO='{Skuno}' AND UPPER(VALUE)='TRUE'";
dt = DB.ExecSelect(sql, null).Tables[0];
if (dt.Rows.Count > 0)
{
NeedCheck = true;
}
return(NeedCheck);
}
/// <summary>
/// add by ZGJ 2018-03-16
/// 判斷該產品是不是有預報廢
/// </summary>
/// <param name="SerialNumber"></param>
/// <param name="DB"></param>
/// <returns></returns>
public bool IsPreScrap(string SerialNumber, OleExec DB)
{
bool PreScrap = false;
string sql = string.Empty;
DataTable dt = new DataTable();
sql = $@"SELECT NEXT_STATION FROM R_SN_STATION_DETAIL WHERE SN='{SerialNumber}'
ORDER BY EDIT_TIME DESC";
dt = DB.ExecSelect(sql).Tables[0];
if (dt.Rows.Count > 0 && dt.Rows[0]["NEXT_STATION"].Equals("PRE_SCRAP"))
{
PreScrap = true;
}
return(PreScrap);
}
public string GetUserID(string EmpNo, OleExec DB)
{
string res = null;
string sql = string.Empty;
DataTable dt = new DataTable();
sql = $@" SELECT * FROM C_USER WHERE EMP_NO='{EmpNo}' ";
dt = DB.ExecSelect(sql).Tables[0];
if (dt.Rows.Count != 0)
{
res = dt.Rows[0]["ID"].ToString();
}
return(res);
}
public List <R_Station> QueryFailStation(OleExec DB)
{
string sql = string.Empty;
DataTable dt = new DataTable();
List <R_Station> stationaction = new List <R_Station>();
sql = $@"SELECT * FROM r_Station where fail_station_flag=1 ";
dt = DB.ExecSelect(sql).Tables[0];
foreach (DataRow dr in dt.Rows)
{
stationaction.Add(CreatecstationactionClass(dr));
}
return(stationaction);
}
public int Insert(string FACTORY_CODE, string FACTORY_NAME, string FACTORY_ADDRESS, string DESCRIPTION, string EDIT_EMP, string BU, DB_TYPE_ENUM dbt, OleExec DB)
{
OleDbParameter[] param = null;
string sql;
int result;
string ID;
T_C_FACTORY factory = null;
DataTable table;
try
{
sql = $@"SELECT * FROM C_FACTORY WHERE UPPER(FACTORY_CODE)='{FACTORY_CODE}' AND UPPER(FACTORY_NAME)='{FACTORY_NAME}' AND UPPER(FACTORY_ADDRESS)='{FACTORY_ADDRESS}'";
table = DB.ExecSelect(sql).Tables[0];
if (table.Rows.Count > 0)
{ // factorycode+factoryname+factoryaddress existed fail primary key
return(0);
}
factory = new T_C_FACTORY(DB, dbt);
ID = factory.GetNewID(BU, DB, dbt);
param = new OleDbParameter[]
{
new OleDbParameter("ID", ID),
new OleDbParameter("FACTORY_CODE", FACTORY_CODE.ToUpper()),
new OleDbParameter("FACTORY_NAME", FACTORY_NAME.ToUpper()),
new OleDbParameter("FACTORY_ADDRESS", FACTORY_ADDRESS.ToUpper()),
new OleDbParameter("DESCRIPTION", DESCRIPTION),
new OleDbParameter("EDIT_EMP", EDIT_EMP)
};
sql = "INSERT INTO C_FACTORY VALUES(:ID,:FACTORY_CODE,:FACTORY_NAME,:FACTORY_ADDRESS,:DESCRIPTION, SYSDATE,:EDIT_EMP)";
result = DB.ExecuteNonQuery(sql, CommandType.Text, param);
if (result > 0)
{
return(1); //Insert success
}
else
{
return(2); //Insert fail
}
}
catch (Exception e)
{
return(3); //exception
throw e;
}
}
public C_SKU_VER_MAPPING GetMappingBySkuAndVersion(string sku, string version, OleExec sfcdb)
{
string sql = $@" select * from C_SKU_VER_MAPPING where fox_skuno='{sku}' and fox_version1='{version}' ";
DataTable dataTable = sfcdb.ExecSelect(sql).Tables[0];
C_SKU_VER_MAPPING skuMapping = null;
Row_C_SKU_VER_MAPPING rowMapping = null;
if (dataTable.Rows.Count > 0)
{
rowMapping = (Row_C_SKU_VER_MAPPING)this.NewRow();
rowMapping.loadData(dataTable.Rows[0]);
skuMapping = rowMapping.GetDataObject();
}
return(skuMapping);
}
public DataTable getC_Rolebyrolename(string Role_Name, OleExec DB, DB_TYPE_ENUM DBType)
{
string strSql;
if (Role_Name == "")
{
strSql = $@"select * from c_role ";
}
else
{
strSql = $@"select * from c_role where Role_Name like '%{Role_Name}%' ";
}
DataTable res = DB.ExecSelect(strSql).Tables[0];
return(res);
}
/// <summary>
/// add by fgg 2018.03.14 Get Lock Object
/// </summary>
/// <param name="lockName"></param>
/// <param name="db"></param>
/// <param name="dbType"></param>
/// <returns></returns>
public R_SYNC_LOCK GetSYNCLockObject(string lockName, OleExec db, DB_TYPE_ENUM dbType)
{
string StrSql = $@"SELECT * From R_SYNC_LOCK where Lock_Name='{lockName}' ";
DataTable dt = db.ExecSelect(StrSql).Tables[0];
Row_R_SYNC_LOCK Row_SYNC = (Row_R_SYNC_LOCK)this.NewRow();
if (dt.Rows.Count > 0)
{
Row_SYNC.loadData(dt.Rows[0]);
return(Row_SYNC.GetDataObject());
}
else
{
return(null);
}
}
/// <summary>
/// 取Sn loading之後每個測試工站的最後一筆測試記錄
/// </summary>
/// <param name="DB"></param>
/// <param name="snId"></param>
/// <param name="StartTime"></param>
/// <returns></returns>
public List <R_TEST_RECORD> GetTestDataByTimeBefor(OleExec DB, string snId, DateTime StartTime)
{
List <R_TEST_RECORD> l = new List <R_TEST_RECORD>();
string strSql = $@" select * from (
select a.*,RANK() over(partition by messtation order by endtime desc) as rk from r_test_record a
where r_sn_id ='{snId}' and endtime>to_date('{StartTime.ToString("yyyy-MM-dd HH:mm:ss")}','yyyy/mm/dd hh24:mi:ss')) where rk=1 ";
DataSet ds = DB.ExecSelect(strSql);
foreach (DataRow dr in ds.Tables[0].Rows)
{
Row_R_TEST_RECORD r = (Row_R_TEST_RECORD)this.NewRow();
r.loadData(dr);
l.Add(r.GetDataObject());
}
return(l);
}
public Row_C_TAB_COLUMN_MAP GetTableColumnMap(string Table_Name, OleExec DB, DB_TYPE_ENUM DBType)
{
string strSql = $@"select * from C_TAB_COLUMN_MAP where tab_name='{Table_Name}' ";
DataSet res = DB.ExecSelect(strSql);
if (res.Tables[0].Rows.Count > 0)
{
Row_C_TAB_COLUMN_MAP ret = (Row_C_TAB_COLUMN_MAP)NewRow();
ret.loadData(res.Tables[0].Rows[0]);
return(ret);
}
else
{
return(null);
}
}
public Row_c_user getC_Userbyempno(string empno, OleExec DB, DB_TYPE_ENUM DBType)
{
string strSql = $@"select * from c_user where emp_no in ('{empno}') ";
DataSet res = DB.ExecSelect(strSql);
if (res.Tables[0].Rows.Count > 0)
{
Row_c_user ret = (Row_c_user)NewRow();
ret.loadData(res.Tables[0].Rows[0]);
return(ret);
}
else
{
return(null);
}
}
/// <summary>
/// 獲取時間點當期數據庫時間的對應的班別
/// </summary>
/// <param name="workSection">workSection</param>
/// <param name="DB">DB</param>
/// <returns></returns>
public string GetShift(string workSection, OleExec DB)
{
string sql = $@"select * from c_class_time where work_section='{workSection}'
and start_time < to_char (sysdate, 'HH24:MI:SS')
and end_time >= to_char (sysdate, 'HH24:MI:SS')";
DataSet dsClassTime = DB.ExecSelect(sql);
if (dsClassTime.Tables[0].Rows.Count > 0)
{
return(dsClassTime.Tables[0].Rows[0]["day_distinct"].ToString());
}
else
{
throw new Exception("This " + workSection + " Can't Config");
}
}
public R_PACKING GetPackingObjectBySN(string sn, OleExec sfcdb)
{
string sql = $@"select * from r_packing where id in (select n.pack_id from r_sn_packing n,r_sn m where n.sn_id=m.id and m.sn='{sn}' and m.valid_flag='1')";
DataSet ds = sfcdb.ExecSelect(sql);
if (ds.Tables[0].Rows.Count > 0)
{
Row_R_PACKING rowPacking = (Row_R_PACKING)this.NewRow();
rowPacking.loadData(ds.Tables[0].Rows[0]);
return(rowPacking.GetDataObject());
}
else
{
return(null);
}
}
/// <summary>
/// get expand list no id
/// </summary>
/// <param name="db"></param>
/// <param name="id"></param>
/// <returns></returns>
public List <ExpandNoID> GetExpandDetailNoID(OleExec db, string id)
{
string sql = $@"select * from {this.TableName} where id='{id}'";
DataSet dsDetail = db.ExecSelect(sql);
List <ExpandNoID> tableExpandList = new List <ExpandNoID>();
foreach (DataRow row in dsDetail.Tables[0].Rows)
{
tableExpandList.Add(new ExpandNoID {
SEQ_NO = (double)Convert.ToInt32(row["SEQ_NO"].ToString()),
NAME = row["NAME"].ToString(),
VALUE = row["VALUE"].ToString()
});
}
return(tableExpandList);
}
public Row_C_MES_MESSAGE GetMESMessageByMessageCode(string MessageCode, OleExec DB, DB_TYPE_ENUM DBType)
{
string strSql = $@"select * from C_MES_MESSAGE where message_code='{MessageCode}' ";
DataSet res = DB.ExecSelect(strSql);
if (res.Tables[0].Rows.Count > 0)
{
Row_C_MES_MESSAGE ret = (Row_C_MES_MESSAGE)NewRow();
ret.loadData(res.Tables[0].Rows[0]);
return(ret);
}
else
{
return(null);
}
}
/// <summary>
/// 獲取未拋賬的工單
/// </summary>
/// <param name="wo"></param>
/// <param name="confirmed_flag"></param>
/// <param name="sfcdb"></param>
/// <returns></returns>
public R_STOCK_GT GetNotGTbjByWO(string wo, string confirmed_flag, OleExec sfcdb)
{
string sql = $@"select * from R_STOCK_GT where workorderno='{wo}' and sap_flag='0' and confirmed_flag='{confirmed_flag}' and backflush_time is null ";
DataTable dt = sfcdb.ExecSelect(sql).Tables[0];
if (dt.Rows.Count > 0)
{
Row_R_STOCK_GT rowObj = (Row_R_STOCK_GT)this.NewRow();
rowObj.loadData(dt.Rows[0]);
return(rowObj.GetDataObject());
}
else
{
return(null);
}
}
public List <C_ACTION_PARA> QueryActionParaByStation(string ActionID, OleExec DB)
{
string sql = string.Empty;
DataTable dt = new DataTable();
List <C_ACTION_PARA> paralist = new List <C_ACTION_PARA>();
// sql = $@"select b.* from R_Station_Action a, C_Action_Para b where a.C_STATION_ACTION_ID= b.C_STATION_ACTION_ID and A.R_STATION_INPUT_ID='{InputID}' ";
sql = $@"select * from c_action_para where c_station_action_id='{ActionID}' order by seq_no";
dt = DB.ExecSelect(sql).Tables[0];
foreach (DataRow dr in dt.Rows)
{
paralist.Add(CreateActionParaClass(dr));
}
return(paralist);
}
public bool CheckPrivilegeData(string RoleId, String RolePrivilegeId, OleExec DB)
{
bool res = false;
string sql = string.Empty;
string RolePrivileges = "";
DataTable dt = new DataTable();
sql = $@" SELECT *FROM C_ROLE_PRIVILEGE WHERE ROLE_ID='{RoleId}'AND PRIVILEGE_ID ='{RolePrivilegeId}' ";
dt = DB.ExecSelect(sql).Tables[0];
if (dt.Rows.Count == 0)
{
res = true;
}
return(res);
}
public Row_C_USER_PRIVILEGE getC_PrivilegebyIDemp(string id, string emp, OleExec DB)
{
string strSql = $@" SELECT * FROM C_USER_PRIVILEGE a,c_user b where a.PRIVILEGE_ID='{id}' and EMP_NO='{emp}' and A.USER_ID=B.ID ";
DataSet res = DB.ExecSelect(strSql);
if (res.Tables[0].Rows.Count > 0)
{
Row_C_USER_PRIVILEGE ret = (Row_C_USER_PRIVILEGE)NewRow();
ret.loadData(res.Tables[0].Rows[0]);
return(ret);
}
else
{
return(null);
}
}
public Row_C_USER_PRIVILEGE getC_PrivilegebyID(string id, OleExec DB)
{
string strSql = $@" SELECT * FROM C_USER_PRIVILEGE where PRIVILEGE_ID='{id}' ";
DataSet res = DB.ExecSelect(strSql);
if (res.Tables[0].Rows.Count > 0)
{
Row_C_USER_PRIVILEGE ret = (Row_C_USER_PRIVILEGE)NewRow();
ret.loadData(res.Tables[0].Rows[0]);
return(ret);
}
else
{
return(null);
}
}
public Row_C_MENU getC_MenubyID(string id, OleExec DB)
{
string strSql = $@" SELECT * FROM C_MENU where ID='{id}' ";
DataSet res = DB.ExecSelect(strSql);
if (res.Tables[0].Rows.Count > 0)
{
Row_C_MENU ret = (Row_C_MENU)NewRow();
ret.loadData(res.Tables[0].Rows[0]);
return(ret);
}
else
{
return(null);
}
}
public bool NAME_VALUE_IsExist(string NAME, string VALUE, OleExec DB)
{
string sql = string.Empty;
DataTable dt = new DataTable();
sql = "SELECT * FROM C_BU_EX WHERE NAME=:NAME AND VALUE=:VALUE";
OleDbParameter[] parameters = new OleDbParameter[] {
new OleDbParameter("NAME", NAME),
new OleDbParameter("VALUE", VALUE)
};
dt = DB.ExecSelect(sql, parameters).Tables[0];
if (dt.Rows.Count > 0)
{
return(true);
}
return(false);
}
public List <R_SN> GetValidSnByPanel(string _panel, OleExec DB)
{
if (DBType == DB_TYPE_ENUM.Oracle)
{
List <R_SN> list = new List <R_SN>();
string sql = $@"SELECT * FROM R_SN WHERE valid_flag='1' and SN IN (SELECT SN FROM R_PANEL_SN WHERE PANEL='{_panel.Replace("'", "''")}')";
DataTable dt = new DataTable();
dt = DB.ExecSelect(sql).Tables[0];
list = DataTableToList <R_SN>(dt);
return(list);
}
else
{
string errMsg = MESReturnMessage.GetMESReturnMessage("MES00000019", new string[] { DBType.ToString() });
throw new MESReturnMessage(errMsg);
}
}
