public string AOIInfoValidation(ref AOIInfo aoiInfo)
{
string sResult = string.Empty;
try {
//AOIInfoValidation檢查完畢之後才去執行GetLotInfo
//利用BARCODE_ID、USER_ID、DEVICE_ID、串出FAB、LINE、DEVICE_TYPE、STATION、MODEL_NAME、SHIFT_ID
sResult = GetLotInfo(ref aoiInfo);
//有其他Validation可以寫在下方
}
catch (Exception ex)
{
throw ex;
}
return(sResult);
}
public string InsertAOIProcessData(string sXML)
{
Message message = new Message();
AOIInfo aoiInfo = new AOIInfo();
//Sample XML
sXML = "<TRX><TRX_NAME>InsertAOIProcessData</TRX_NAME><TYPE_ID>IN</TYPE_ID><LOG_ID>17050803.07.11_20170829173900</LOG_ID><DEVICE_ID>IAOI01</DEVICE_ID><USER_ID>T1705016</USER_ID><BARCODE_ID>17050803.07.11</BARCODE_ID><TEST_TIME>2017/03/15 16:51:31</TEST_TIME><TEST_RESULT>OK</TEST_RESULT><DEFECT_CNT>10</DEFECT_CNT><O_SIZE_COUNT>1</O_SIZE_COUNT><L_SIZE_COUNT>2</L_SIZE_COUNT><M_SIZE_COUNT>3</M_SIZE_COUNT><S_SIZE_COUNT>4</S_SIZE_COUNT><TEST_PROGRAM>B140XTN02.235</TEST_PROGRAM><CENTERLV>2500</CENTERLV><IMAGE_FILE_PATH>D:\\Date\\20170515\\Images\\UZC7200107A130011_165131_00.png</IMAGE_FILE_PATH><CCD_NO>CCD_001</CCD_NO><DEFECT_INFO><DEFECT_CODE>DB12</DEFECT_CODE><DEFECT_DESC>BRIGHTPARTICLE</DEFECT_DESC><DEFECT_VALUE></DEFECT_VALUE><DEFECT_NUM>1</DEFECT_NUM><DEFECT_LOCATION_X>3</DEFECT_LOCATION_X><DEFECT_LOCATION_Y>3</DEFECT_LOCATION_Y><REVIEW>170728-6-21-3-27-0001.jpg</REVIEW><DATA_NO>113</DATA_NO><GATE_NO>18</GATE_NO><FLAG>0</FLAG><DEFECT_REAL_SIZE>45</DEFECT_REAL_SIZE><DEFECT_REAL_X_SIZE>45</DEFECT_REAL_X_SIZE><DEFECT_REAL_Y_SIZE>24</DEFECT_REAL_Y_SIZE><DEFECT_GRAY_LEVEL>24</DEFECT_GRAY_LEVEL><ZONE>2</ZONE><PI_NO>0</PI_NO><PANEL_ID>1</PANEL_ID></DEFECT_INFO><DEFECT_INFO><DEFECT_CODE>DB70</DEFECT_CODE><DEFECT_DESC>DARKPARTICLE</DEFECT_DESC><DEFECT_VALUE></DEFECT_VALUE><DEFECT_NUM>1</DEFECT_NUM><DEFECT_LOCATION_X>3</DEFECT_LOCATION_X><DEFECT_LOCATION_Y>3</DEFECT_LOCATION_Y><REVIEW>170728-6-21-3-27-0002.jpg</REVIEW><DATA_NO>113</DATA_NO><GATE_NO>18</GATE_NO><FLAG>0</FLAG><DEFECT_REAL_SIZE>45</DEFECT_REAL_SIZE><DEFECT_REAL_X_SIZE>45</DEFECT_REAL_X_SIZE><DEFECT_REAL_Y_SIZE>24</DEFECT_REAL_Y_SIZE><DEFECT_GRAY_LEVEL>24</DEFECT_GRAY_LEVEL><ZONE>2</ZONE><PI_NO>0</PI_NO><PANEL_ID>1</PANEL_ID></DEFECT_INFO></TRX>";
try {
//從XML中解析取得欄位資訊 並放入aoiInfo的資料結構中
aoiInfo = getAOIInfoFromXML(sXML);
//將解析到的資訊做資料檢查 , 並回傳檢查結果
string strMsg = AOIInfoValidation(ref aoiInfo);
if (strMsg == "") //空白表示通過Validation檢查 , 資料無異常
{
//將分析並驗證過的資料Insert至DB
//Insert Into Hearder and Line Table
string sResult = InsertDataToAOIDB(aoiInfo);
}
message = Combine_AOI_RTN_Code(aoiInfo);
}
catch (Exception ex)
{
//當有Exception時 Send email to Admin
string Admin = GetAdminMailAddress();
StringBuilder sBody = new StringBuilder();
string sTitle = "[FMM_WCF] " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
sBody.Append("Error Function : InsertAOIProcessData " + "<br>");
sBody.Append("Error Message : " + ex.ToString() + "<br>");
pb_ws.SendMail(Admin, "", "", sTitle, sBody.ToString());
}
return(ReturnMessage(message));
}
public Message Combine_AOI_RTN_Code(AOIInfo aoiInfo)
{
Message message = new Message();
try {
message.TRX_NAME = aoiInfo.TRX_NAME;
message.TYPE_ID = aoiInfo.TYPE_ID;
message.LOG_ID = aoiInfo.LOG_ID;
message.BARCODE_ID = aoiInfo.BARCODE_ID;
message.RTN_CODE = aoiInfo.RTN_CODE;
message.RTN_MSG = aoiInfo.RTN_MSG;
//根據RTN_MSG 取得錯誤訊息並塞入 message.RTN_CODE_MSG
//message.RTN_CODE_MSG = GetRTN_ErrMsg(RTN_MSG);
}
catch (Exception ex)
{
throw ex;
}
return(message);
}
//============================================================================================
public AOIInfo getAOIInfoFromXML(string strXMLFile)
{
AOIInfo aoiInfo = new AOIInfo();
DataSet ds = ConvertXMLFileToDataSet(strXMLFile);
try
{
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
aoiInfo.TRX_NAME = ds.Tables[0].Rows[0]["TRX_NAME"].ToString();
aoiInfo.TYPE_ID = ds.Tables[0].Rows[0]["TYPE_ID"].ToString();
aoiInfo.LOG_ID = ds.Tables[0].Rows[0]["LOG_ID"].ToString();
aoiInfo.DEVICE_ID = ds.Tables[0].Rows[0]["DEVICE_ID"].ToString();
aoiInfo.USER_ID = ds.Tables[0].Rows[0]["USER_ID"].ToString();
aoiInfo.BARCODE_ID = ds.Tables[0].Rows[0]["BARCODE_ID"].ToString();
aoiInfo.TEST_TIME = ds.Tables[0].Rows[0]["TEST_TIME"].ToString();
aoiInfo.TEST_RESULT = ds.Tables[0].Rows[0]["TEST_RESULT"].ToString();
aoiInfo.DEFECT_CNT = ds.Tables[0].Rows[0]["DEFECT_CNT"].ToString();
aoiInfo.O_SIZE_COUNT = ds.Tables[0].Rows[0]["O_SIZE_COUNT"].ToString();
aoiInfo.L_SIZE_COUNT = ds.Tables[0].Rows[0]["L_SIZE_COUNT"].ToString();
aoiInfo.M_SIZE_COUNT = ds.Tables[0].Rows[0]["M_SIZE_COUNT"].ToString();
aoiInfo.S_SIZE_COUNT = ds.Tables[0].Rows[0]["S_SIZE_COUNT"].ToString();
aoiInfo.TEST_PROGRAM = ds.Tables[0].Rows[0]["TEST_PROGRAM"].ToString();
aoiInfo.CENTERLV = ds.Tables[0].Rows[0]["CENTERLV"].ToString();
aoiInfo.IMAGE_FILE_PATH = ds.Tables[0].Rows[0]["IMAGE_FILE_PATH"].ToString();
aoiInfo.CCD_NO = ds.Tables[0].Rows[0]["CCD_NO"].ToString();
if (ds.Tables.Count > 1 && ds.Tables[1].Rows.Count > 0)
{
AOIDefectInfo[] lineinfo = new AOIDefectInfo[ds.Tables[1].Rows.Count];
for (int i = 0; i < ds.Tables[1].Rows.Count; i++)
{
lineinfo[i] = new AOIDefectInfo();
lineinfo[i].DEFECT_CODE = ds.Tables[1].Rows[i]["DEFECT_CODE"].ToString();
lineinfo[i].DEFECT_DESC = ds.Tables[1].Rows[i]["DEFECT_DESC"].ToString();
lineinfo[i].DEFECT_VALUE = ds.Tables[1].Rows[i]["DEFECT_VALUE"].ToString();
lineinfo[i].DEFECT_NUM = ds.Tables[1].Rows[i]["DEFECT_NUM"].ToString();
lineinfo[i].DEFECT_LOCATION_X = ds.Tables[1].Rows[i]["DEFECT_LOCATION_X"].ToString();
lineinfo[i].DEFECT_LOCATION_Y = ds.Tables[1].Rows[i]["DEFECT_LOCATION_Y"].ToString();
lineinfo[i].REVIEW = ds.Tables[1].Rows[i]["REVIEW"].ToString();
lineinfo[i].DATA_NO = ds.Tables[1].Rows[i]["DATA_NO"].ToString();
lineinfo[i].GATE_NO = ds.Tables[1].Rows[i]["GATE_NO"].ToString();
lineinfo[i].FLAG = ds.Tables[1].Rows[i]["FLAG"].ToString();
lineinfo[i].DEFECT_REAL_SIZE = ds.Tables[1].Rows[i]["DEFECT_REAL_SIZE"].ToString();
lineinfo[i].DEFECT_REAL_X_SIZE = ds.Tables[1].Rows[i]["DEFECT_REAL_X_SIZE"].ToString();
lineinfo[i].DEFECT_REAL_Y_SIZE = ds.Tables[1].Rows[i]["DEFECT_REAL_Y_SIZE"].ToString();
lineinfo[i].DEFECT_GRAY_LEVEL = ds.Tables[1].Rows[i]["DEFECT_GRAY_LEVEL"].ToString();
lineinfo[i].ZONE = ds.Tables[1].Rows[i]["ZONE"].ToString();
lineinfo[i].PI_NO = ds.Tables[1].Rows[i]["PI_NO"].ToString();
lineinfo[i].PANEL_ID = ds.Tables[1].Rows[i]["PANEL_ID"].ToString();
}
aoiInfo.DefectLineInfo = lineinfo;
}
else
{
AOIDefectInfo[] lineinfo = new AOIDefectInfo[0];
aoiInfo.DefectLineInfo = lineinfo;
}
}
}
catch (Exception ex)
{
throw ex;
}
return(aoiInfo);
}
public string InsertDataToAOIDB(AOIInfo aoiInfo)
{
DataSet ds = null;
string sResult = string.Empty;
try {
StringBuilder strSql = new StringBuilder();
strSql.Append(" Insert Into " + "FMM_WCF_AOI_HEADER (");
strSql.Append(" HEADER_ID , ");
strSql.Append(" FAB , ");
strSql.Append(" LINE , ");
strSql.Append(" STATION , ");
strSql.Append(" DEVICE_TYPE , ");
strSql.Append(" MODEL_NAME , ");
strSql.Append(" SHIFT_ID , ");
strSql.Append(" WO , ");
strSql.Append(" TRX_NAME , ");
strSql.Append(" TYPE_ID , ");
strSql.Append(" LOG_ID , ");
strSql.Append(" DEVICE_ID , ");
strSql.Append(" USER_ID , ");
strSql.Append(" BARCODE_ID , ");
strSql.Append(" TEST_TIME , ");
strSql.Append(" TEST_RESULT , ");
strSql.Append(" DEFECT_CNT , ");
strSql.Append(" O_SIZE_COUNT , ");
strSql.Append(" L_SIZE_COUNT , ");
strSql.Append(" M_SIZE_COUNT , ");
strSql.Append(" S_SIZE_COUNT , ");
strSql.Append(" TEST_PROGRAM , ");
strSql.Append(" CENTERLV , ");
strSql.Append(" IMAGE_FILE_PATH , ");
strSql.Append(" CCD_NO "); //最後一個參數不加入分隔符號
strSql.Append(" ) ");
strSql.Append(" Values ( ");
strSql.Append("'" + aoiInfo.LOG_ID + "' , ");
strSql.Append("'" + aoiInfo.FAB + "' , ");
strSql.Append("'" + aoiInfo.LINE + "' , ");
strSql.Append("'" + aoiInfo.STATION + "' , ");
strSql.Append("N'" + aoiInfo.DEVICE_TYPE + "' , ");
strSql.Append("'" + aoiInfo.MODEL_NAME + "' , ");
strSql.Append("N'" + aoiInfo.SHIFT_ID + "' , ");
strSql.Append("'" + aoiInfo.WO + "' , ");
strSql.Append("'" + aoiInfo.TRX_NAME + "' , ");
strSql.Append("'" + aoiInfo.TYPE_ID + "' , ");
strSql.Append("'" + aoiInfo.LOG_ID + "' , ");
strSql.Append("'" + aoiInfo.DEVICE_ID + "' , ");
strSql.Append("'" + aoiInfo.USER_ID + "' , ");
strSql.Append("'" + aoiInfo.BARCODE_ID + "' , ");
strSql.Append("'" + aoiInfo.TEST_TIME + "' , ");
strSql.Append("'" + aoiInfo.TEST_RESULT + "' , ");
strSql.Append("'" + aoiInfo.DEFECT_CNT + "' , ");
strSql.Append("'" + aoiInfo.O_SIZE_COUNT + "' , ");
strSql.Append("'" + aoiInfo.L_SIZE_COUNT + "' , ");
strSql.Append("'" + aoiInfo.M_SIZE_COUNT + "' , ");
strSql.Append("'" + aoiInfo.S_SIZE_COUNT + "' , ");
strSql.Append("'" + aoiInfo.TEST_PROGRAM + "' , ");
strSql.Append("'" + aoiInfo.CENTERLV + "' , ");
strSql.Append("'" + aoiInfo.IMAGE_FILE_PATH + "' , ");
strSql.Append("'" + aoiInfo.CCD_NO + "'"); //最後一個參數不加入分隔符號
strSql.Append(" ) ");
ds = pb_ws.ExcuteSQL_Query("CIMDB_" + sEnvironment, strSql.ToString(), "CIMWCFService");
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
sResult = "SUCCESS";
}
StringBuilder sbsql = new StringBuilder();
List <string> ListSql = new List <string>();
System.String[] aSql = null;
if (aoiInfo.DefectLineInfo.Length > 0)
{
for (int i = 0; i < aoiInfo.DefectLineInfo.Length; i++)
{
sbsql.Length = 0;
sbsql.Append(" Insert Into " + "FMM_WCF_AOI_LINE (");
sbsql.Append(" LINE_ID , ");
sbsql.Append(" DEFECT_CODE , ");
sbsql.Append(" DEFECT_DESC , ");
sbsql.Append(" DEFECT_VALUE , ");
sbsql.Append(" DEFECT_NUM , ");
sbsql.Append(" DEFECT_LOCATION_X , ");
sbsql.Append(" DEFECT_LOCATION_Y , ");
sbsql.Append(" REVIEW , ");
sbsql.Append(" DATA_NO , ");
sbsql.Append(" GATE_NO , ");
sbsql.Append(" FLAG , ");
sbsql.Append(" DEFECT_REAL_SIZE , ");
sbsql.Append(" DEFECT_REAL_X_SIZE , ");
sbsql.Append(" DEFECT_REAL_Y_SIZE , ");
sbsql.Append(" DEFECT_GRAY_LEVEL , ");
sbsql.Append(" ZONE , ");
sbsql.Append(" PI_NO , ");
sbsql.Append(" PANEL_ID "); //最後一個參數不加入分隔符號
sbsql.Append(" ) ");
sbsql.Append(" Values ( ");
sbsql.Append("'" + aoiInfo.LOG_ID + "' , ");
sbsql.Append("'" + aoiInfo.DefectLineInfo[i].DEFECT_CODE + "' , ");
sbsql.Append("'" + aoiInfo.DefectLineInfo[i].DEFECT_DESC + "' , ");
sbsql.Append("'" + aoiInfo.DefectLineInfo[i].DEFECT_VALUE + "' , ");
sbsql.Append("'" + aoiInfo.DefectLineInfo[i].DEFECT_NUM + "' , ");
sbsql.Append("'" + aoiInfo.DefectLineInfo[i].DEFECT_LOCATION_X + "' , ");
sbsql.Append("'" + aoiInfo.DefectLineInfo[i].DEFECT_LOCATION_Y + "' , ");
sbsql.Append("'" + aoiInfo.DefectLineInfo[i].REVIEW + "' , ");
sbsql.Append("'" + aoiInfo.DefectLineInfo[i].DATA_NO + "' , ");
sbsql.Append("'" + aoiInfo.DefectLineInfo[i].GATE_NO + "' , ");
sbsql.Append("'" + aoiInfo.DefectLineInfo[i].FLAG + "' , ");
sbsql.Append("'" + aoiInfo.DefectLineInfo[i].DEFECT_REAL_SIZE + "' , ");
sbsql.Append("'" + aoiInfo.DefectLineInfo[i].DEFECT_REAL_X_SIZE + "' , ");
sbsql.Append("'" + aoiInfo.DefectLineInfo[i].DEFECT_REAL_Y_SIZE + "' , ");
sbsql.Append("'" + aoiInfo.DefectLineInfo[i].DEFECT_GRAY_LEVEL + "' , ");
sbsql.Append("'" + aoiInfo.DefectLineInfo[i].ZONE + "' , ");
sbsql.Append("'" + aoiInfo.DefectLineInfo[i].PI_NO + "' , ");
sbsql.Append("'" + aoiInfo.DefectLineInfo[i].PANEL_ID + "'"); //最後一個參數不加入分隔符號
sbsql.Append(" ) ");
ListSql.Add(sbsql.ToString());
}
int iResult = 0;
aSql = ListSql.ToArray();
if (aSql.Length > 0)
{
iResult = pb_ws.ExcuteSQL_Batch("CIMDB" + "_" + sEnvironment, aSql, "AP_JOB");
}
}
}
catch (Exception ex)
{
throw ex;
}
return("");
}
public string GetLotInfo(ref AOIInfo aoiInfo)
{
string sResult = string.Empty;
string sFail = "FAIL";
try
{
string BARCODE_ID, USER_ID, DEVICE_ID = string.Empty;
BARCODE_ID = aoiInfo.BARCODE_ID;
USER_ID = aoiInfo.USER_ID;
DEVICE_ID = aoiInfo.DEVICE_ID;
DataSet ds = null;
//DataTable dt = null;
StringBuilder sSql = new StringBuilder();
sSql.Append(" SELECT distinct USR_ID , E.class_id ");
sSql.Append(" FROM C_USER_OPID C, emp_data_all E ");
sSql.Append(" WHERE 1=1 ");
sSql.Append(" and E.emp_no = C.USR_ID ");
sSql.Append(" and E.emp_no ='" + USER_ID + "'");
ds = pb_ws.ExcuteSQL_Query("FMMDB_" + sEnvironment, sSql.ToString(), "CIMWCFService");
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
aoiInfo.SHIFT_ID = ds.Tables[0].Rows[0]["class_id"].ToString();
}
else
{
sResult = "ERR_MSG_LOT_001";
aoiInfo.RTN_CODE = sFail;
aoiInfo.RTN_MSG = sResult;
}
//抓取FAB LINE EQP_TYPE 資料 , 從FMM MES DB
sSql = new StringBuilder();
sSql.Append(" SELECT distinct EQP_ID , EQP_TYPE , FAB , LINE , OP_ID ");
sSql.Append(" FROM C_EQP_EQPT ");
sSql.Append(" WHERE 1=1 ");
sSql.Append(" and EQP_ID ='" + DEVICE_ID + "'");
ds = pb_ws.ExcuteSQL_Query("FMMDB_" + sEnvironment, sSql.ToString(), "CIMWCFService");
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
aoiInfo.FAB = ds.Tables[0].Rows[0]["FAB"].ToString();
aoiInfo.LINE = ds.Tables[0].Rows[0]["LINE"].ToString();
aoiInfo.DEVICE_TYPE = ds.Tables[0].Rows[0]["EQP_TYPE"].ToString();
aoiInfo.STATION = ds.Tables[0].Rows[0]["OP_ID"].ToString();
}
else
{
sResult = "ERR_MSG_LOT_002";
aoiInfo.RTN_CODE = sFail;
aoiInfo.RTN_MSG = sResult;
}
sSql = new StringBuilder();
sSql.Append(" SELECT WO_ID , BARCODE_ID , MODEL_NO ");
sSql.Append(" FROM R_WOM_WOBARCODE ");
sSql.Append(" WHERE 1=1 ");
sSql.Append(" and BARCODE_ID ='" + BARCODE_ID + "'");
ds = pb_ws.ExcuteSQL_Query("FMMDB_" + sEnvironment, sSql.ToString(), "CIMWCFService");
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
aoiInfo.WO = ds.Tables[0].Rows[0]["WO_ID"].ToString();
aoiInfo.MODEL_NAME = ds.Tables[0].Rows[0]["MODEL_NO"].ToString();
}
else
{
sResult = "ERR_MSG_LOT_003";
aoiInfo.RTN_CODE = sFail;
aoiInfo.RTN_MSG = sResult;
}
}
catch (Exception ex)
{
throw ex;
}
return(sResult);
}
