public Row_R_LOT_DETAIL GetByLotID(string _LotID, string StrSN, OleExec DB) //add by StrSN by LLF 2018-02-22
{
string strsql = "";
if (DBType == DB_TYPE_ENUM.Oracle)
{
//modify by LLF 2018-02-22
//strsql = $@" select ID from r_lot_detail where lot_id='{_LotID.Replace("'", "''")}' and rownum=1";
if (string.IsNullOrEmpty(StrSN))
{
strsql = $@" select ID from r_lot_detail where lot_id='{_LotID.Replace("'", "''")}' and rownum=1 ";
}
else
{
strsql = $@" select ID from r_lot_detail where lot_id='{_LotID.Replace("'", "''")}' and SN='{StrSN}' ";
}
string ID = DB.ExecSelectOneValue(strsql)?.ToString();
if (ID == null)
{
string errMsg = MESReturnMessage.GetMESReturnMessage("MES00000007", new string[] { "LotID:" + _LotID });
throw new MESReturnMessage(errMsg);
}
Row_R_LOT_DETAIL Res = (Row_R_LOT_DETAIL)this.GetObjByID(ID, DB);
return(Res);
}
else
{
string errMsg = MESReturnMessage.GetMESReturnMessage("MES00000019", new string[] { DBType.ToString() });
throw new MESReturnMessage(errMsg);
}
}
public List <R_LOT_DETAIL> GetLotDetailByLotNo(string lotNo, OleExec DB)
{
List <R_LOT_DETAIL> res = new List <R_LOT_DETAIL>();
string strSql = $@" select a.* from r_lot_detail a ,r_lot_status b where a.lot_id=b.id and b.lot_no='{lotNo}' ";
DataSet ds = DB.ExecSelect(strSql);
foreach (DataRow VARIABLE in ds.Tables[0].Rows)
{
Row_R_LOT_DETAIL r = (Row_R_LOT_DETAIL)this.NewRow();
r.loadData(VARIABLE);
res.Add(r.GetDataObject());
}
return(res);
}
public void InLotPassStation(string NewLotFlag, R_SN SNObj, string LotNo, string LotSatusID, string Station, string EmpNo, string AQL_TYPE, string Line, string BU, OleExec DB)
{
T_R_LOT_STATUS Table_R_Lot_Status = new T_R_LOT_STATUS(DB, DBType);
Row_R_LOT_STATUS Row_R_Lot_Status = (Row_R_LOT_STATUS)NewRow();
T_R_LOT_DETAIL Table_R_Lot_Detail = new T_R_LOT_DETAIL(DB, DBType);
Row_R_LOT_DETAIL Row_R_Lot_Detail = (Row_R_LOT_DETAIL)Table_R_Lot_Detail.NewRow();
T_C_AQLTYPE Table_C_AQLTYPE = new T_C_AQLTYPE(DB, DBType);
try
{
string LotID = "";
if (NewLotFlag == "1")
{
//Modify by LLF 2018-03-19,生成ID，需根據Table生成
//LotID= GetNewID(BU, DB);
LotID = Table_R_Lot_Status.GetNewID(BU, DB);
Row_R_Lot_Status.ID = LotID;
Row_R_Lot_Status.LOT_NO = LotNo;
Row_R_Lot_Status.SKUNO = SNObj.SKUNO;
Row_R_Lot_Status.AQL_TYPE = AQL_TYPE;
Row_R_Lot_Status.LOT_QTY = 1;
Row_R_Lot_Status.REJECT_QTY = 0;
Row_R_Lot_Status.SAMPLE_QTY = 1;
Row_R_Lot_Status.PASS_QTY = 0;
Row_R_Lot_Status.FAIL_QTY = 0;
Row_R_Lot_Status.CLOSED_FLAG = "0";
Row_R_Lot_Status.LOT_STATUS_FLAG = "0";
Row_R_Lot_Status.LINE = Line;
Row_R_Lot_Status.SAMPLE_STATION = Station;
Row_R_Lot_Status.EDIT_EMP = EmpNo;
Row_R_Lot_Status.EDIT_TIME = GetDBDateTime(DB);
DB.ExecSQL(Row_R_Lot_Status.GetInsertString(DBType));
}
else
{
LotID = LotSatusID;
Row_R_Lot_Status = (Row_R_LOT_STATUS)Table_R_Lot_Status.GetObjByID(LotSatusID, DB);
int LotQty = (int)Row_R_Lot_Status.LOT_QTY + 1;
int SampleQty = Table_C_AQLTYPE.GetSampleQty(AQL_TYPE, LotQty, DB);
Row_R_Lot_Status.SAMPLE_QTY = SampleQty;
Row_R_Lot_Status.LOT_QTY += 1;
DB.ExecSQL(Row_R_Lot_Status.GetUpdateString(DBType));
}
//Modify by LLF 2018-03-19,生成ID，需根據Table生成
//Row_R_Lot_Detail.ID = GetNewID(BU, DB);
Row_R_Lot_Detail.ID = Table_R_Lot_Detail.GetNewID(BU, DB);
Row_R_Lot_Detail.LOT_ID = LotID;
//Row_R_Lot_Detail.LOT_ID = LotID;
Row_R_Lot_Detail.SN = SNObj.SN;
Row_R_Lot_Detail.WORKORDERNO = SNObj.WORKORDERNO;
Row_R_Lot_Detail.CREATE_DATE = GetDBDateTime(DB);
Row_R_Lot_Detail.SAMPLING = "0";
Row_R_Lot_Detail.STATUS = "0";
Row_R_Lot_Detail.FAIL_CODE = "";
Row_R_Lot_Detail.FAIL_LOCATION = "";
Row_R_Lot_Detail.DESCRIPTION = "";
Row_R_Lot_Detail.CARTON_NO = "";
Row_R_Lot_Detail.PALLET_NO = "";
Row_R_Lot_Detail.EDIT_EMP = EmpNo;
Row_R_Lot_Detail.EDIT_TIME = GetDBDateTime(DB);
DB.ExecSQL(Row_R_Lot_Detail.GetInsertString(DBType));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// FQC Lot 過站
/// </summary>
/// <param name="SerialNo"></param>
/// <param name="LotNo"></param>
/// <param name="PassOrFail"></param>
/// <param name="EmpNo"></param>
/// <param name="Station"></param>
/// <param name="Line"></param>
/// <param name="BU"></param>
/// <param name="DB"></param>
/// <param name="FailInfos"></param>
public void LotPassStation(string SerialNo, string LotNo, string PassOrFail, string EmpNo, string Station, string DeviceName, string Line, string BU, OleExec DB, params string[] FailInfos)
{
bool PassedFlag = true;
string sql = string.Empty;
DataTable dt = new DataTable();
Row_R_LOT_STATUS StatusRow = (Row_R_LOT_STATUS)NewRow();
T_R_LOT_DETAIL DetailTable = new T_R_LOT_DETAIL(DB, this.DBType);
Row_R_LOT_DETAIL DetailRow = (Row_R_LOT_DETAIL)DetailTable.NewRow();
R_LOT_STATUS Status = null;
R_LOT_DETAIL Detail = null;
T_R_SN SnTable = new T_R_SN(DB, this.DBType);
List <string> LotsSN = new List <string>();
if (this.DBType == DB_TYPE_ENUM.Oracle)
{
PassedFlag = PassOrFail.ToUpper().Equals("PASS") ? true : false;
//sql = $@"SELECT * FROM R_LOT_STATUS WHERE LOT_NO='{LotNo}' AND SAMPLE_STATION='{Station}' AND LINE='{Line}'"; //判斷有沒有 LOT
sql = $@"SELECT * FROM R_LOT_STATUS WHERE LOT_NO='{LotNo}' AND SAMPLE_STATION='{Station}'"; //判斷有沒有 LOT
dt = DB.ExecSelect(sql).Tables[0];
if (dt.Rows.Count > 0)
{
StatusRow.loadData(dt.Rows[0]);
Status = StatusRow.GetDataObject();
sql = $@"SELECT A.* FROM R_LOT_DETAIL A,R_SN B WHERE LOT_ID='{StatusRow.ID}' AND B.SN='{SerialNo}' AND A.SN=B.SN"; //判斷Lot中有沒有這個SN並且沒有被抽檢過
dt = DB.ExecSelect(sql).Tables[0];
if (dt.Rows.Count > 0)
{
DetailRow.loadData(dt.Rows[0]);
Detail = DetailRow.GetDataObject();
if (Detail.SAMPLING.Equals("1"))
{
throw new MESReturnMessage(MESReturnMessage.GetMESReturnMessage("MES00000093", new string[] { SerialNo }));
}
}
else
{
throw new MESReturnMessage(MESReturnMessage.GetMESReturnMessage("MES00000094", new string[] { SerialNo, LotNo }));
}
if (Status.CLOSED_FLAG == "1") //Lot 關閉
{
if (PassedFlag)
{
//更新 R_LOT_DETAIL STATUS
Detail.STATUS = "1"; //1 表示抽檢通過
Detail.SAMPLING = "1"; //1 表示被抽檢了
//更新 R_LOT_STATUS PASS_QTY
Status.PASS_QTY++;
}
else
{
//更新 R_LOT_DETAIL STATUS ,FAIL_CODE,FAIL_LOCATION,DESCRIPTION
Detail.STATUS = "0"; //0 表示抽檢失敗
Detail.SAMPLING = "1"; //1 表示被抽檢了
if (FailInfos != null && FailInfos.Length == 3) //記錄失敗原因
{
Detail.FAIL_CODE = FailInfos[0];
Detail.FAIL_LOCATION = FailInfos[1];
Detail.DESCRIPTION = FailInfos[2];
}
//更新 R_LOT_STATUS FAIL_QTY
Status.FAIL_QTY++;
}
if (Status.FAIL_QTY >= Status.REJECT_QTY && Status.FAIL_QTY != 0)
{
//更新 R_LOT_STATUS 關閉，NG，
//Status.CLOSED_FLAG = "1";// 1 表示關閉Lot
Status.LOT_STATUS_FLAG = "2";// 2 表示整個 Lot 不良
//更新 R_LOT_DETAIL 鎖定LOT 中所有
Detail.EDIT_EMP = EmpNo;
Detail.EDIT_TIME = GetDBDateTime(DB);
DetailRow.ConstructRow(Detail);
DB.ExecSQL(DetailRow.GetUpdateString(this.DBType));
//該批次鎖定--add by Eden 2018-05-04
sql = $@"update r_lot_detail set sampling='4' where lot_id='{Detail.LOT_ID}'";
DB.ExecSQL(sql);
//DetailTable.LockLotBySn(SerialNo, EmpNo, DB);
}
else
{
if (Status.PASS_QTY + Status.FAIL_QTY >= Status.SAMPLE_QTY)
{
//更新 R_LOT_STATUS 關閉，OK
//Status.CLOSED_FLAG = "1";
Status.LOT_STATUS_FLAG = "1"; // 1 表示整個 Lot 正常
//更新 R_LOT_DETAIL 鎖定FAIL 的，其他的正常過站
//sql = $@"SELECT * FROM R_LOT_DETAIL WHERE LOT_ID='{StatusRow.ID}' AND STATUS='0'";
sql = $@"SELECT * FROM R_LOT_DETAIL WHERE LOT_ID='{StatusRow.ID}' AND ((SAMPLING='1' AND STATUS='1') OR (SAMPLING='0'))";
dt = DB.ExecSelect(sql).Tables[0];
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
LotsSN.Add(dr["SN"].ToString());
}
SnTable.LotsPassStation(LotsSN, Line, Station, DeviceName, BU, PassOrFail, EmpNo, DB); // 過站
}
//記錄通過數 ,UPH
foreach (string SN in LotsSN)
{
SnTable.RecordYieldRate(Detail.WORKORDERNO, 1, SN, "PASS", Line, Station, EmpNo, BU, DB);
SnTable.RecordUPH(Detail.WORKORDERNO, 1, SN, "PASS", Line, Station, EmpNo, BU, DB);
}
}
Detail.EDIT_EMP = EmpNo;
Detail.EDIT_TIME = GetDBDateTime(DB);
DetailRow.ConstructRow(Detail);
DB.ExecSQL(DetailRow.GetUpdateString(this.DBType));
}
Status.EDIT_EMP = EmpNo;
Status.EDIT_TIME = GetDBDateTime(DB);
StatusRow.ConstructRow(Status);
DB.ExecSQL(StatusRow.GetUpdateString(this.DBType));
}
else
{
throw new MESReturnMessage(MESReturnMessage.GetMESReturnMessage("MES00000201", new string[] { LotNo }));
}
}
else
{
throw new MESReturnMessage(MESReturnMessage.GetMESReturnMessage("MES00000091", new string[] { LotNo }));
}
}
else
{
string errMsg = MESReturnMessage.GetMESReturnMessage("MES00000019", new string[] { DBType.ToString() });
throw new MESReturnMessage(errMsg);
}
}