using System;
|
using System.Data.SqlClient;
|
using System.Data;
|
using System.Configuration;
|
using System.IO;
|
using System.Text;
|
using System.Windows.Forms;
|
using System.Drawing;
|
|
namespace M423project
|
{
|
public class DetectionData : IDisposable
|
{
|
private SqlConnection connection;
|
private SqlConnection connForUpload;
|
private string _standardCheckMode;
|
|
int enableUploadMinutes = 10;
|
private static readonly object synObj = new object();
|
public DetectionData()
|
{
|
string connStr = ConfigurationManager.AppSettings["connectionString"];
|
_standardCheckMode = ConfigurationManager.AppSettings["StandardCheckMode"];
|
|
connection = new SqlConnection(connStr);
|
try
|
{
|
connection.Open();
|
}
|
catch (Exception)
|
{
|
CommonUtil.WriteLog(LogType.Err, "连接数据库失败！");
|
}
|
|
connForUpload = new SqlConnection(connStr);
|
try
|
{
|
connForUpload.Open();
|
}
|
catch (Exception)
|
{
|
CommonUtil.WriteLog(LogType.Err, "DataUpload连接数据库失败！");
|
}
|
|
string enableUploadMinutesStr = ConfigurationManager.AppSettings["EnableUploadMinutes"];
|
|
int temp = 0;
|
if (int.TryParse(enableUploadMinutesStr, out temp))
|
{
|
enableUploadMinutes = temp;
|
}
|
else
|
{
|
enableUploadMinutes = 10;
|
}
|
}
|
|
public void Dispose()
|
{
|
connection.Close();
|
connForUpload.Close();
|
}
|
|
/// <summary>
|
///
|
/// </summary>
|
/// <returns></returns>
|
public int SaveProductDetection(string _productNo, int productType, OPC opc, Label label)
|
{
|
lock (synObj)
|
{
|
int result = 0;
|
string sql = string.Format("EXEC DBO.Proc_SaveProduct '{0}','{1}' ", _productNo, productType);
|
|
using (SqlCommand cmd = new SqlCommand(sql, connection))
|
{
|
SqlDataReader dr = cmd.ExecuteReader();
|
if (dr.Read())
|
{
|
result = (int)dr.GetValue(0);
|
}
|
dr.Close();
|
}
|
|
if (productType == (int)DetectionOption.doStandardBlock)
|
{
|
label.BackColor = Color.Green;
|
label.Text = "";
|
opc.Write(OPCOutputTag.StandardCheck, true);
|
}
|
|
return result;
|
}
|
}
|
|
public void SaveHeightData(int _detectID, double _height, double _cellHeight, MeasureState _productNoResult,
|
MeasureState _heightResult, MeasureState _cellHeightResult, string productNo)
|
{
|
lock (synObj)
|
{
|
string sql = string.Format(
|
" UPDATE ProductDetectionDetail "
|
+ " SET ProductHeight = {0}, "
|
+ " ProductCellHeight = {1}, "
|
+ " ProductHeightStatus = '{2}', "
|
+ " ProductCellHeightStatus = '{3}', "
|
+ " ProductNoStatus = '{4}', "
|
+ " ProductNo='{5}',"
|
+ " DetectTime = GetDATE() "
|
+ " WHERE AutoID = {6} ", _height, _cellHeight, _heightResult.ToString(),
|
_cellHeightResult.ToString(), _productNoResult.ToString(), productNo, _detectID);
|
|
using (SqlCommand cmd = new SqlCommand(sql, connection))
|
{
|
cmd.ExecuteNonQuery();
|
}
|
}
|
}
|
|
public void SaveSizeData(int detectID, double _length, double _width, double _cellWidth,
|
string productState, int plantID, string imageFileName, string lengthState, string widthState, string productNo, string productNoStatus)
|
{
|
lock (synObj)
|
{
|
string sql = string.Format("UPDATE ProductDetectionDetail "
|
+ " SET ProductLength = {0}, "
|
+ " ProductWidth = {1}, "
|
+ " ProductCellWidth = {2}, "
|
+ " PlateID = {3}, "
|
+ " ImageFileName = '{4}', "
|
+ " DetectTime = GetDATE(), "
|
+ " ProductLengthStatus = '{5}', "
|
+ " ProductWidthStatus = '{6}', "
|
+ " ProductStatus = '{7}',"
|
+ " ProductNo='{8}',"
|
+ " ProductNoStatus='{9}'"
|
//+ " CarrierBarcode='{8}'"
|
+ " WHERE AutoID = {10} ", _length, _width, _cellWidth, plantID, imageFileName, lengthState.ToString()
|
, widthState.ToString(), productState.ToString(), productNo, productNoStatus, detectID);
|
|
using (SqlCommand cmd = new SqlCommand(sql, connection))
|
{
|
cmd.ExecuteNonQuery();
|
}
|
}
|
}
|
|
#region add by Patrick 2018-07-13
|
public void SaveTrayBarcode(int detectID)
|
{
|
string trayCode = "";
|
trayCode = CarrierBarcodeCtrl.GlobalCarrierBarcode;
|
|
if (string.IsNullOrEmpty(trayCode))
|
{
|
return;
|
}
|
lock (synObj)
|
{
|
string sql = string.Format("UPDATE ProductDetectionDetail "
|
+ " SET CarrierBarcode='{0}'"
|
+ " WHERE AutoID = {1} ", trayCode, detectID);
|
|
using (SqlCommand cmd = new SqlCommand(sql, connection))
|
{
|
cmd.ExecuteNonQuery();
|
}
|
}
|
}
|
#endregion
|
|
#region add by Patrick 2019-04-16
|
public void SaveSpotCheck(int detectID)
|
{
|
lock (synObj)
|
{
|
string sql = string.Format("UPDATE ProductDetectionDetail "
|
+ " SET IsSample={0}"
|
+ " WHERE AutoID = {1} ", 1, detectID);
|
|
using (SqlCommand cmd = new SqlCommand(sql, connection))
|
{
|
cmd.ExecuteNonQuery();
|
}
|
}
|
}
|
#endregion
|
|
public DataTable Query(string sql)
|
{
|
DataTable dt = new DataTable();
|
using (SqlCommand cmd = new SqlCommand(sql, connection))
|
{
|
SqlDataReader dr = cmd.ExecuteReader();
|
dt.Load(dr);
|
}
|
return dt;
|
}
|
|
public DataTable QueryUnloadData()
|
{
|
DataTable dt = new DataTable();
|
//string sql = string.Format(@"SELECT TOP 100 AutoID,PlateID, ProductNo,CarrierBarcode,DetectTime,ProductStatus,ProductLengthStatus
|
// ,ProductWidthStatus,ProductHeightStatus,ProductNoStatus,LocalUploadFlag = cast(0 as int),ProductLength,ProductWidth,ProductHeight,IsSample
|
// FROM ProductDetectionDetail
|
// WHERE ISNULL(UploadFlag, 0) = 0 AND ISNULL(ProductType,0)=0
|
// AND ProductLengthStatus <> 'UNKNOWN'
|
// AND ProductWidthStatus <> 'UNKNOWN'
|
// AND ((ProductNoStatus = 'OK' AND ISNULL(CarrierBarcode,'')<>'') OR (ProductNoStatus = 'OK' AND ProductStatus = 'NG' AND ISNULL(CarrierBarcode,'')=''))
|
// AND DATEDIFF(DAY,'2019-11-29',DetectTime)>=0
|
// ORDER BY DetectTime DESC");
|
|
string sql = string.Format(@"SELECT TOP 100 AutoID,PlateID, ProductNo,CarrierBarcode,DetectTime,ProductStatus,ProductLengthStatus
|
,ProductWidthStatus,ProductHeightStatus,ProductNoStatus,LocalUploadFlag = cast(0 as int),ProductLength,ProductWidth,ProductHeight,IsSample
|
FROM ProductDetectionDetail
|
WHERE ISNULL(UploadFlag, 0) = 0 AND ISNULL(ProductType,0)=0
|
AND ProductLengthStatus <> 'UNKNOWN'
|
AND ProductWidthStatus <> 'UNKNOWN'
|
AND ProductNoStatus = 'OK'
|
AND ProductStatus<>'NA'
|
AND (ISNULL(CarrierBarcode,'')<>'' OR ProductStatus = 'NG')
|
AND DATEDIFF(MINUTE,DetectTime,'{0}')<={1}
|
ORDER BY DetectTime DESC", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), enableUploadMinutes.ToString());
|
|
SqlDataReader dr = null;
|
using (SqlCommand cmd = new SqlCommand(sql, connForUpload))
|
{
|
try
|
{
|
dr = cmd.ExecuteReader();
|
dt.Load(dr);
|
}
|
catch (Exception e)
|
{
|
CommonUtil.WriteLog(LogType.Inf, string.Format("获取待上传数据时出现异常：{0}", e.Message));
|
}
|
finally
|
{
|
if (dr != null)
|
dr.Close();
|
}
|
}
|
return dt;
|
}
|
|
public int SetUploadDataStatus(string _autoIDList)
|
{
|
int result = 0;
|
string sql = string.Format("EXEC Proc_UpdateUploadStatus '{0}' ", _autoIDList);
|
|
using (SqlCommand cmd = new SqlCommand(sql, connForUpload))
|
{
|
SqlDataReader dr = cmd.ExecuteReader();
|
if (dr.Read())
|
{
|
|
result = (int)dr.GetValue(0);
|
}
|
dr.Close();
|
}
|
return result;
|
}
|
|
public void CleanData()
|
{
|
lock (synObj)
|
{
|
try
|
{
|
string sql = string.Format("DELETE FROM ProductDetectionDetail WHERE DetectTime < GetDATE() - 40 ");
|
|
using (SqlCommand cmd = new SqlCommand(sql, connection))
|
{
|
cmd.ExecuteNonQuery();
|
}
|
}
|
catch (Exception)
|
{
|
|
}
|
}
|
}
|
|
static object csvLock = new object();
|
public void SaveToCsv(ProductMeasureResult pmr)
|
{
|
string fileName = @"E:\DailyReport\detectdata" + DateTime.Now.ToString("yyyyMMdd") + ".csv";
|
bool IsNewFile = !File.Exists(fileName);
|
|
lock (csvLock)
|
{
|
using (FileStream myStream = new FileStream(fileName, FileMode.OpenOrCreate))
|
{
|
myStream.Seek(0, SeekOrigin.End);
|
using (StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.UTF8))
|
{
|
try
|
{
|
if (IsNewFile)
|
{
|
sw.WriteLine("檢測編號,檢測時間,工位,序列碼,判定,FAI9,判定,FAI2,判定,FAI3,判定,FAI12,判定,結果,类型");
|
}
|
|
string data = $"{pmr.DetectID}," +
|
$"{pmr.DetectTime.ToString("HH:mm:ss")}," +
|
$"{pmr.PlateID},{pmr.ProductNo}," +
|
$"{GetMeasureResult(pmr.ProductNoResult)}," +
|
$"{GetMeasureValueStr(pmr.Height)}," +
|
$"{GetMeasureResult(pmr.HeightResult)}," +
|
$"{GetMeasureValueStr(pmr.CellHeight)}," +
|
$"{GetMeasureResult(pmr.CellHeightResult)}," +
|
$"{GetMeasureValueStr(pmr.Length)}," +
|
$"{GetMeasureResult(pmr.LengthResult)}," +
|
$"{GetMeasureValueStr(pmr.Width)}," +
|
$"{GetMeasureResult(pmr.WidthResult)}," +
|
$"{GetMeasureResult(pmr.ProductResult)}," +
|
$"{(CommonUtil.DetectionOption == DetectionOption.doProduct ? "产品" : "标准块")}";
|
|
sw.WriteLine(data);
|
sw.Flush();
|
//sw.WriteLine(
|
// string.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14}",
|
// pmr.DetectID, pmr.DetectTime.ToString("HH:mm:ss")
|
// , pmr.PlateID, pmr.ProductNo, pmr.ProductNoResult.ToString()
|
// , pmr.Height.ToString("0.00000")
|
// , pmr.HeightResult.ToString()
|
// , pmr.CellHeight.ToString("0.00000")
|
// , pmr.CellHeightResult
|
// , pmr.Length.ToString("0.00000")
|
// , pmr.LengthResult.ToString()
|
// , pmr.Width.ToString("0.00000")
|
// , pmr.WidthResult.ToString(),
|
// pmr.ProductResult.ToString(),
|
// CommonUtil.DetectionOption == DetectionOption.doProduct ? "产品" : "标准块"
|
// ));
|
}
|
catch (Exception e)
|
{
|
CommonUtil.WriteLog(LogType.Inf, string.Format("记录检测结果出现异常：{0}", e.Message));
|
}
|
}
|
}
|
}
|
}
|
|
private string GetMeasureValueStr(double measureValue)
|
{
|
return (measureValue == 999.999 || measureValue == -999.999 || measureValue == CommonUtil.InvalidValue) ? "NA" : measureValue.ToString("f5");
|
}
|
|
private string GetMeasureResult(string result)
|
{
|
return result == "OK" ? "OK" : "NG";
|
}
|
|
public bool IsStandardCheck()
|
{
|
try
|
{
|
int count = 0;
|
if (_standardCheckMode == "0")
|
{
|
return true;
|
}
|
StringBuilder sb = new StringBuilder();
|
sb.AppendFormat(@"select COUNT(AutoID) from ProductDetectionDetail
|
WHERE ProductType=1 AND DATEDIFF({0},GETDATE(),DetectTime)=0", _standardCheckMode == "1" ? "WEEK" : "MONTH");
|
using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
|
{
|
count = Convert.ToInt32(cmd.ExecuteScalar());
|
}
|
return count > 0;
|
}
|
catch (Exception ex)
|
{
|
return false;
|
}
|
|
}
|
}
|
}
|
