public bool InsertStationShopfloorData(string ApolloTRID, string StationName, StationTestResult StationResult, string ApplicationPath, out string ErrorMessage)
{
bool CheckFlag = true;
ErrorMessage = "";
string MOCODE = "";
string RCARD = "";
string RCARDSEQ = "";
string TCARD = "";
string MODELCODE = "";
string ITEMCODE = "";
string ROUTECODE = "";
string OPCODE = "";
string SEGCODE = "";
string SSCODE = "";
string RESCODE = "";
string SHIFTTYPECODE = "";
string SHIFTCODE = "";
string TPCODE = "";
string SHIFTDAY = "";
string ECODE = "";
string ACTION = "";
string ACTIONRESULT = "";
string CARTONCODE = "";
string PALLETCODE = "";
string MUSER = "";
string MDATE = "";
string MTIME = "";
string FLAG = "";
string EMESSAGE = "";
string EATTRIBUTE1 = "";
ProductInfo tmpParoduct = new ProductInfo(ApolloTRID);
Timecode tmpTimeCode = new Timecode();
if (CheckTRID(ApolloTRID, ref tmpParoduct, out ErrorMessage) == false)
{
ErrorMessage = ErrorMessage + "\n 故無法插入過站資料至MES資料庫!!!";
return(false);
}
if (GetRoute(tmpParoduct, out ErrorMessage) == false)
{
ErrorMessage = ErrorMessage + "\n 故無法插入過站資料至MES資料庫!!!";
return(false);
}
if (GetTimeCode(ref tmpTimeCode, out ErrorMessage) == false)
{
ErrorMessage = ErrorMessage + "\n 故無法插入過站資料至MES資料庫!!!";
return(false);
}
if (File.Exists(ApplicationPath) == false)
{
ErrorMessage = "外部檔不存在!!!\n路徑 : " + ApplicationPath;
return(false);
}
string tmpLineName = Ini.IniReadValue("MesLineCode", "LineName", ApplicationPath);
if (tmpLineName == "")
{
ErrorMessage = "外部檔 : " + ApplicationPath + "\n Section: [MesLineCode] \n Key : LineName \n 內容為空!!!";
return(false);
}
string tmpSegment = SelectTableValue("PICS.dbo.picslne", "Segment", "MESLINE", tmpLineName, out ErrorMessage);
MOCODE = tmpParoduct.SoNo.Substring(tmpParoduct.SoNo.Length - 6);
RCARD = tmpParoduct.TRID;
RCARDSEQ = "0";
TCARD = "";
MODELCODE = tmpParoduct.ModelName;
ITEMCODE = tmpParoduct.PartNo;
ROUTECODE = tmpParoduct.Routecode;
OPCODE = StationName.ToUpper();
SEGCODE = tmpSegment;
SSCODE = tmpLineName;
RESCODE = SSCODE + "-" + OPCODE;
SHIFTTYPECODE = tmpTimeCode.SHIFTTYPECODE;
SHIFTCODE = tmpTimeCode.SHIFTCODE;
TPCODE = tmpTimeCode.TPCODE;
SHIFTDAY = tmpTimeCode.NowDate;
switch ((int)StationResult)
{
case (int)StationTestResult.GOOD:
ECODE = "";
ACTION = "GOOD";
ACTIONRESULT = "GOOD";
break;
case (int)StationTestResult.NG:
ECODE = StationName.ToUpper().Trim();
ACTION = "NG";
ACTIONRESULT = "NG";
break;
}
CARTONCODE = "";
PALLETCODE = "";
MUSER = "******";
MDATE = tmpTimeCode.NowDate;
MTIME = tmpTimeCode.NowTime;
FLAG = "Q";
EMESSAGE = "";
EATTRIBUTE1 = "";
using (SqlConnection cn = new SqlConnection())
{
cn.ConnectionString = "server=" + dbHost + ";uid=" + dbUser + ";pwd=" + dbPass + ";database=" + dbName;
try
{
cn.Open();
}
catch (SqlException ex)
{
ErrorMessage = ex.Message;
return(false);
}
if (cn.State == ConnectionState.Open)
{
string InsertToTBLERPONWIP = "INSERT INTO ShopFloor.dbo.TBLERPONWIP(";
string VALUES = ") VALUES(";
SQL_TBLIERPONWIP sql_TBLERPONWIP = new SQL_TBLIERPONWIP();
SqlTransaction tran = cn.BeginTransaction();
try
{
string sqlstr = InsertToTBLERPONWIP
+ sql_TBLERPONWIP.MOCODE + "," + sql_TBLERPONWIP.RCARD + ","
+ sql_TBLERPONWIP.RCARDSEQ + ","
+ sql_TBLERPONWIP.TCARD + ","
+ sql_TBLERPONWIP.MODELCODE + "," + sql_TBLERPONWIP.ITEMCODE + ","
+ sql_TBLERPONWIP.ROUTECODE + ","
+ sql_TBLERPONWIP.OPCODE + ","
+ sql_TBLERPONWIP.SEGCODE + ","
+ sql_TBLERPONWIP.SSCODE + ","
+ sql_TBLERPONWIP.RESCODE + ","
+ sql_TBLERPONWIP.SHIFTTYPECODE + ","
+ sql_TBLERPONWIP.SHIFTCODE + ","
+ sql_TBLERPONWIP.TPCODE + ","
+ sql_TBLERPONWIP.SHIFTDAY + ","
+ sql_TBLERPONWIP.ECODE + ","
+ sql_TBLERPONWIP.ACTION + ","
+ sql_TBLERPONWIP.ACTIONRESULT + ","
+ sql_TBLERPONWIP.CARTONCODE + ","
+ sql_TBLERPONWIP.PALLETCODE + ","
+ sql_TBLERPONWIP.MUSER + ","
+ sql_TBLERPONWIP.MDATE + ","
+ sql_TBLERPONWIP.MTIME + ","
+ sql_TBLERPONWIP.FLAG + ","
+ sql_TBLERPONWIP.EMESSAGE + ","
+ sql_TBLERPONWIP.EATTRIBUTE1
+ VALUES
+ "'" + MOCODE + "'" + "," + "'" + RCARD + "'" + ","
+ "'" + RCARDSEQ + "'" + ","
+ "'" + TCARD + "'" + ","
+ "'" + MODELCODE + "'" + "," + "'" + ITEMCODE + "'" + ","
+ "'" + ROUTECODE + "'" + ","
+ "'" + OPCODE + "'" + ","
+ "'" + SEGCODE + "'" + ","
+ "'" + SSCODE + "'" + ","
+ "'" + RESCODE + "'" + ","
+ "'" + SHIFTTYPECODE + "'" + ","
+ "'" + SHIFTCODE + "'" + ","
+ "'" + TPCODE + "'" + ","
+ "'" + SHIFTDAY + "'" + ","
+ "'" + ECODE + "'" + ","
+ "'" + ACTION + "'" + ","
+ "'" + ACTIONRESULT + "'" + ","
+ "'" + CARTONCODE + "'" + ","
+ "'" + PALLETCODE + "'" + ","
+ "'" + MUSER + "'" + ","
+ "'" + MDATE + "'" + ","
+ "'" + MTIME + "'" + ","
+ "'" + FLAG + "'" + ","
+ "'" + EMESSAGE + "'" + ","
+ "'" + EATTRIBUTE1 + "'"
+ ")";
SqlCommand cmd = new SqlCommand(sqlstr, cn, tran);
cmd.ExecuteNonQuery();
tran.Commit();
return(true);
}
catch (SqlException ex)
{
tran.Rollback();
ErrorMessage = ex.Message;
return(false);
}
}
else
{
return(false);
}
}
}
//----------------------------------------------------------------------------------------------
private bool GetRoute(ProductInfo NowProductInfo, out string ErrorMessage)
{
bool CheckFlag = false;
ErrorMessage = "";
using (SqlConnection cn = new SqlConnection())
{
cn.ConnectionString = "server=" + dbHost + ";uid=" + dbUser + ";pwd=" + dbPass + ";database=" + dbName;
try
{
cn.Open();
}
catch (SqlException ex)
{
ErrorMessage = ex.Message;
CheckFlag = false;
return(CheckFlag);
}
if (cn.State == ConnectionState.Open)
{
SQL_TBLITEM2ROUTE sql_Tblitem2routeTable = new SQL_TBLITEM2ROUTE();
string SelectColum = sql_Tblitem2routeTable.ROUTECODE;
string sqlstr = "SELECT top 1 " + SelectColum + " From Shopfloor.dbo.tblitem2route WHERE " + sql_Tblitem2routeTable.ITEMCODE + " = @PartNo order by MDATE desc,MTIME desc";
SqlCommand cmd = new SqlCommand(sqlstr, cn);
cmd.Parameters.AddWithValue("@PartNo", NowProductInfo.PartNo);
SqlDataReader dr = cmd.ExecuteReader();
try
{
if (dr.Read() == true)
{
if (!dr.Equals(DBNull.Value))
{
NowProductInfo.Routecode = dr[sql_Tblitem2routeTable.ROUTECODE].ToString().Trim();
CheckFlag = true;
}
}
else
{
ErrorMessage = "查無此PN : " + NowProductInfo.PartNo + " RouteCode資料!!!";
CheckFlag = false;
}
}
catch
{
ErrorMessage = "資料庫查詢錯誤!!!!(Shopfloor.dbo.tblitem2route : " + NowProductInfo.PartNo + ")";
CheckFlag = false;
}
finally
{
dr.Close();
}
}
else
{
ErrorMessage = "資料庫連接錯誤!!!!(Shopfloor.dbo.tblitem2route)";
CheckFlag = false;
}
}
return(CheckFlag);
}
/// <summary>
/// 將檢測資料插入資料庫中
/// </summary>
/// <param name="NowProductInfo">當前機台資料物件</param>
/// <param name="Station">當站站名</param>
/// <param name="TestItem">測試項目</param>
/// <param name="TestResult">測試結果 PASS,NG</param>
/// <param name="MeasureValue">測試數值</param>
/// <param name="NowDate">測試日期YYYYMMDD</param>
/// <param name="NowTime">測試時間HHMMSS</param>
/// <param name="ErrorMessage">錯誤訊息</param>
/// <returns></returns>
public bool InsertTestResultRecord(ProductInfo NowProductInfo, string Station, string TestItem, string TestResult, string MeasureValue,
string HwVersion, string SwVersion, string ToolVersion,
string NowDate, string NowTime, out string ErrorMessage)
{
ErrorMessage = "";
///抓取機台產品訊息(將剛刷入的TRID進資料庫抓取相對應的機台資訊(包含ModelName,料號,工廠流線ID號碼))
CheckTRID(NowProductInfo.TRID, ref NowProductInfo, out ErrorMessage);
using (SqlConnection cn = new SqlConnection())
{
cn.ConnectionString = "server=" + dbHost + ";uid=" + dbUser + ";pwd=" + dbPass + ";database=" + dbName;
try
{
cn.Open();
}
catch (SqlException ex)
{
ErrorMessage = ex.Message;
return(false);
}
if (cn.State == ConnectionState.Open)
{
string InsertToUsbTestRecord = "INSERT INTO PICS.dbo.HCS_UsbTestRecode(";
string VALUES = ") VALUES(";
SQL_HcsUsbTestRecordTable sql_TestRecord = new SQL_HcsUsbTestRecordTable();
SqlTransaction tran = cn.BeginTransaction();
try
{
string sqlstr = InsertToUsbTestRecord
+ sql_TestRecord.TRID + "," + sql_TestRecord.ID + ","
+ sql_TestRecord.PN + ","
+ sql_TestRecord.Model + ","
+ sql_TestRecord.Datein + "," + sql_TestRecord.Timein + ","
+ sql_TestRecord.TestItem + ","
+ sql_TestRecord.TestResult + ","
+ sql_TestRecord.SO + ","
+ sql_TestRecord.Station + ","
+ sql_TestRecord.MeasureValue + ","
+ sql_TestRecord.HwVersion + ","
+ sql_TestRecord.SwVersion + ","
+ sql_TestRecord.ToolVersion
+ VALUES
+ "'" + NowProductInfo.TRID + "'" + "," + "'" + NowProductInfo.ID + "'" + ","
+ "'" + NowProductInfo.PartNo + "'" + ","
+ "'" + NowProductInfo.ModelName + "'" + ","
+ "'" + NowDate + "'" + "," + "'" + NowTime + "'" + ","
+ "'" + TestItem + "'" + ","
+ "'" + TestResult + "'" + ","
+ "'" + NowProductInfo.SoNo + "'" + ","
+ "'" + Station + "'" + ","
+ "'" + MeasureValue + "'" + ","
+ "'" + HwVersion + "'" + ","
+ "'" + SwVersion + "'" + ","
+ "'" + ToolVersion + "'"
+ ")";
SqlCommand cmd = new SqlCommand(sqlstr, cn, tran);
cmd.ExecuteNonQuery();
tran.Commit();
return(true);
}
catch (SqlException ex)
{
tran.Rollback();
ErrorMessage = ex.Message;
return(false);
}
}
else
{
return(false);
}
}
}
public bool GetPCBAIDandSN(string ApolloTRID, ref List <PCBAInfo> PCBAInfoList, string ApplicationPath, out string ErrorMessage)
{
bool CheckFlag = true;
ErrorMessage = "";
PCBAInfoList.Clear();
FindInfoList.Clear();
string SQL = "";
string ReadFindInfo = "";
ProductInfo tmpParoduct = new ProductInfo(ApolloTRID);
if (CheckTRID(ApolloTRID, ref tmpParoduct, out ErrorMessage) == false)
{
ErrorMessage = ErrorMessage + "\n 故無法取得PCBA資料!!!";
return(false);
}
if (File.Exists(ApplicationPath) == false)
{
ErrorMessage = "外部檔不存在!!!\n路徑 : " + ApplicationPath;
CheckFlag = false;
return(CheckFlag);
}
ReadFindInfo = Ini.IniReadValue("BoardIDGet", tmpParoduct.PartNo.ToUpper(), ApplicationPath);
if (ReadFindInfo == "")
{
ErrorMessage = "外部檔 : " + ApplicationPath + "\n Section: [BoardIDGet] \n Key : " + tmpParoduct.PartNo.ToUpper() + "\n 內容為空!!!";
CheckFlag = false;
return(CheckFlag);
}
string[] tmpSplit = new string[] { "," };
string[] tmp = ReadFindInfo.Split(tmpSplit, StringSplitOptions.RemoveEmptyEntries);
string[] SubtmpSplit = new string[] { "#" };
//將外部檔設定解碼看物件需要搜索幾層
for (int i = 0; i < tmp.Count(); i++)
{
string[] Subtmp = tmp[i].Split(SubtmpSplit, StringSplitOptions.RemoveEmptyEntries);
FindPCBAInfo tmpFindInfo = new FindPCBAInfo();
if (Subtmp.Count() == 2)
{
tmpFindInfo.MainAssy = Subtmp[0];
tmpFindInfo.SubAssy = Subtmp[1];
FindInfoList.Add(tmpFindInfo);
PCBAInfo tmpPCBAInfo = new PCBAInfo();
if (GetMainAssySubAssyPCBInfo(ApolloTRID, tmpFindInfo.MainAssy, tmpFindInfo.SubAssy, out tmpPCBAInfo, out ErrorMessage) == false)
{
CheckFlag = false;
return(CheckFlag);
}
PCBAInfoList.Add(tmpPCBAInfo);
}
else
{
tmpFindInfo.MainAssy = Subtmp[0];
tmpFindInfo.SubAssy = "";
FindInfoList.Add(tmpFindInfo);
PCBAInfo tmpPCBAInfo = new PCBAInfo();
if (GetMainAssyPCBAInfo(ApolloTRID, tmpFindInfo.MainAssy, out tmpPCBAInfo, out ErrorMessage) == false)
{
CheckFlag = false;
return(CheckFlag);
}
PCBAInfoList.Add(tmpPCBAInfo);
}
}
GC.Collect();
return(CheckFlag);
}
private bool ProductInfoSearch(string TRID, ProductInfo NowProductInfo, out string ErrorMessage)
{
bool CheckFlag = false;
ErrorMessage = "";
using (SqlConnection cn = new SqlConnection())
{
cn.ConnectionString = "server=" + dbHost + ";uid=" + dbUser + ";pwd=" + dbPass + ";database=" + dbName;
try
{
cn.Open();
}
catch (SqlException ex)
{
ErrorMessage = ex.Message;
CheckFlag = false;
return(CheckFlag);
}
if (cn.State == ConnectionState.Open)
{
SQL_PCBIDTable sql_PCBIDTable = new SQL_PCBIDTable();
string SelectColum = sql_PCBIDTable.SO + "," + sql_PCBIDTable.ID + "," + sql_PCBIDTable.Part_No + "," + sql_PCBIDTable.Model;
string sqlstr = "SELECT " + SelectColum + " From WIP.dbo.PCBID WHERE " + sql_PCBIDTable.TRID + " = " + "'" + TRID + "'";
SqlCommand cmd = new SqlCommand(sqlstr, cn);
SqlDataReader dr = cmd.ExecuteReader();
try
{
if (dr.Read() == true)
{
if (!dr.Equals(DBNull.Value))
{
NowProductInfo.SoNo = dr[sql_PCBIDTable.SO].ToString().Trim();
NowProductInfo.PartNo = dr[sql_PCBIDTable.Part_No].ToString().Trim();
NowProductInfo.ModelName = dr[sql_PCBIDTable.Model].ToString().Trim();
NowProductInfo.ID = dr[sql_PCBIDTable.ID].ToString().Trim();
NowProductInfo.TRID = TRID;
CheckFlag = true;
}
}
else
{
ErrorMessage = "查無此TRID : " + TRID + "資料!!!";
CheckFlag = false;
}
}
catch
{
ErrorMessage = "資料庫查詢錯誤!!!!(WIP.dbo.PCBID,TRID : " + TRID + ")";
CheckFlag = false;
}
finally
{
dr.Close();
}
}
else
{
ErrorMessage = "資料庫連接錯誤!!!!(WIP.dbo.TRID)";
CheckFlag = false;
}
}
return(CheckFlag);
}
