private void FrmJYNYDataUpload_Load(object sender, EventArgs e)
{
timer1.Enabled = false;
bool blFound = false;
string hostInfo = Dns.GetHostName();
System.Net.IPAddress[] addressList = Dns.GetHostEntry(Dns.GetHostName()).AddressList;
//IPAddress ipaddress = addressList[0];
//string ips = ipaddress.ToString();
//MessageBox.Show(ips);
//获取配置节信息
this._section = (JYNYTestConfigurationSection)ConfigurationManager.GetSection("mes.JYNYtest");
//增加线程个数。
foreach (JYNYTestDeviceElement element in this._section.Devices)
{
foreach (IPAddress ipAddress in addressList)
{
strIP = ipAddress.ToString();
if (strIP == element.Name)
{
blFound = true;
_deviceElement = element;
break;
}
}
}
if (blFound == false)
{
MessageBox.Show("此电脑的IP地址没有在配置文件中找到对应的配置,请联系IT");
Application.Exit();
}
else
{
txtFilePath.Text = CommonFun.GetFullFile(_deviceElement.Path, _deviceElement.Format);
//txtFilePath.Text = _deviceElement.Path;
}
string strSql = " select top 1 * from [dbo].[ZWIP_VIR_TEST] where EQUIPMENT_CODE='"
+ _deviceElement.EqpName + "' order by TEST_TIME desc";
DataTable dt = DatabaseEx.getDbInstance().getDataTable(strSql);
if (dt != null && dt.Rows.Count > 0)
{
txtTestTime.Text = string.Format("{0:yyyy-MM-dd HH:mm:ss}", dt.Rows[0]["TEST_TIME"].ToString());
}
else
{
txtTestTime.Text = "1900-01-01 01:01:01";
}
txtEqpName.Text = _deviceElement.EqpName;
btnStart.Enabled = true;
btnPause.Enabled = false;
txtEqpName.ReadOnly = true;
txtTestTime.ReadOnly = true;
//btnStart_Click(null, null);
notifyJYNYData.Visible = false;
if (CommonFun.CheckFileExists(txtFilePath.Text.Trim()) == false)
{
MessageBox.Show(string.Format("{0}不存在.", txtFilePath));
return;
}
strAccessConnection = string.Format(_deviceElement.ConnectionString, txtFilePath.Text.Trim());
dataTransferAction = new JYNYTestDataTransferAction(strAccessConnection);
dataTransferAction.OnDataTransferFinished += new EventHandler <DataTransferFinishedArgs>(DataTransferFinished);
int nSecond = 0;
if (int.TryParse(txtInterval.Text, out nSecond) == true)
{
if (nSecond < 10)
{
MessageBox.Show("时间设置不能小于(10)秒");
return;
}
}
timer1.Interval = nSecond * 1000;
timer1.Enabled = true;
StartTransfer();
}
/// <summary>
/// 将数据从Access数据库转移到MES数据库。
/// </summary>
/// <param name="element">设备配置。</param>
/// <returns>true:转置成功。false：转置失败。</returns>
public void Execute(JYNYTestDeviceElement element, System.DateTime MaxTestDateTimeFromServer)
{
bool blTransferDataResult = true;
string strTransferDataMsg = "";
dNewMaxTestTime = MaxTestDateTimeFromServer;
dMaxUploadTestTime = MaxTestDateTimeFromServer;
List <string> lstSqlForInsert = new List <string>();
List <string> lstSqlForUpdateEFCDataDefault = new List <string>();
try
{
strAccessFullPath = CommonFun.GetFullFile(element.Path, element.Format);
AccessConnectionString = string.Format(element.ConnectionString, strAccessFullPath);
if (string.IsNullOrEmpty(strAccessFullPath))
{
strTransferDataMsg = string.Format("没有找到对应的安规数据文件{0} ", strAccessFullPath);
blTransferDataResult = false;
}
DataSet dsJYNYTestData = new DataSet();
StringBuilder sbSql = new StringBuilder();
string sql = string.Empty;
if (blTransferDataResult)
{
#region //Access 数据库正确
//组织查询JYNY测试数据的SQL语句
if (element.Type == JYNYTestDeviceType.Customer)
{
sql = element.Sql;
}
else
{
sql = GetQueryTestDataSql(element.Type);
}
//SELECT Format(data.[日期],'yyyy-MM-dd ')+Format(data.[时间],'HH:mm:ss') AS TEST_TIME,data.[ID] AS TEST_ID,data.[产品条码] AS LOT_NUMBER,data.[测试结果] AS TEST_RESULT,
// data.[是否测试] AS TEST_FLAG,
//data.[步骤] AS TEST_STEP_SEQ,data.[测试类型] AS TEST_TYPE,data.[步骤判断] AS TEST_STEP_RESULT,data.[测试数据1] AS TEST_PARAM1,data.[测试数据2] AS TEST_PARAM2,data.[PROMPT],
// data.[VOLTAGE],data.[FREQUENCY],data.[FREQUENCY],data.[HILIMIT],data.[LOLIMIT],data.[RAMPUP],data.[DWELLTIME],data.[DELAYTIME],data.[RAMPHI],data.[CHARGELO],data.[OFFSET],
//data.[ARCSENSE],data.[ARCFAIL],data.[SCANNER] FROM data
sbSql.AppendFormat("SELECT TOP 1 '{2}' AS EQUIPMENT_CODE,a.* FROM ({0}) a WHERE a.TEST_TIME>'{1}' ORDER BY a.TEST_TIME ASC",
sql,
MaxTestDateTimeFromServer.ToString("yyyy-MM-dd HH:mm:ss"),
element.EqpName);
//创建 Access的连接对象。
using (OleDbConnection oleCon = new OleDbConnection(this.AccessConnectionString))
{
oleCon.Open();
using (OleDbCommand oleCmd = oleCon.CreateCommand())
{
//从Access数据库获取>开始日期和开始时间的数据。
oleCmd.CommandType = CommandType.Text;
oleCmd.CommandText = sbSql.ToString();
OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleCmd);
oleAdapter.Fill(dsJYNYTestData);
}
oleCon.Close();
}
#endregion //End Access数据库正确
}
DataTable dt = null;
//新增IV测试数据
if (dsJYNYTestData != null && dsJYNYTestData.Tables.Count > 0 && dsJYNYTestData.Tables[0].Rows.Count > 0)
{
#region //Get Access JYNY测试数据
string strInsertSql = "";
bool isDefault = false;
foreach (DataRow dr in dsJYNYTestData.Tables[0].Rows)
{
#region Builder Insert Sql
strInsertSql = @"INSERT INTO [dbo].[ZWIP_VIR_TEST]
([EQUIPMENT_CODE]
,[TEST_ID]
,[TEST_TIME]
,[LOT_NUMBER]
,[TEST_RESULT]
,[TEST_FLAG]
,[TEST_STEP_SEQ]
,[TEST_TYPE]
,[TEST_STEP_RESULT]
,[TEST_PARAM1]
,[TEST_PARAM2]
,[PROMPT]
,[VOLTAGE]
,[FREQUENCY]
,[ECURREN]
,[HILIMIT]
,[LOLIMIT]
,[RAMPUP]
,[DWELLTIME]
,[DELAYTIME]
,[RAMPHI]
,[CHARGELO]
,[OFFSET]
,[ARCSENSE]
,[ARCFAIL]
,[SCANNER])
VALUES
(";
strInsertSql = strInsertSql + "'" + element.EqpName + "',";
strInsertSql = strInsertSql + "'" + Convert.ToString(dr["TEST_ID"]).ToUpper() + "',";
strInsertSql = strInsertSql + "'" + Convert.ToString(dr["TEST_TIME"]).ToUpper() + "',";
if (DateTime.TryParse(dr["TEST_TIME"].ToString(), out dNewMaxTestTime) == false)
{
blTransferDataResult = false;
strTransferDataMsg = "安规数据生成的日期格式不正确，请联系设备厂商。";
return;
}
String LOT_NUMBER = Convert.ToString(dr["LOT_NUMBER"]).ToUpper();
if (LOT_NUMBER.Contains("'"))
{
LOT_NUMBER = LOT_NUMBER.Replace("'", "''");
}
strInsertSql = strInsertSql + "'" + LOT_NUMBER + "',";
strInsertSql = strInsertSql + "'" + Convert.ToString(dr["TEST_RESULT"]).ToUpper() + "',";
if (Convert.ToString(dr["TEST_FLAG"]).ToUpper() == "Y")
{
strInsertSql = strInsertSql + " " + 1 + ", ";
}
else
{
strInsertSql = strInsertSql + " " + 0 + ", ";
}
strInsertSql = strInsertSql + "'" + Convert.ToString(dr["TEST_STEP_SEQ"]).ToUpper() + "',";
strInsertSql = strInsertSql + "'" + Convert.ToString(dr["TEST_TYPE"]).ToUpper() + "',";
strInsertSql = strInsertSql + "'" + Convert.ToString(dr["TEST_STEP_RESULT"]).ToUpper() + "',";
//,[TEST_PARAM1],[TEST_PARAM2],[PROMPT],[VOLTAGE],[FREQUENCY],[ECURREN],[HILIMIT],[LOLIMIT]
strInsertSql = strInsertSql + "'" + Convert.ToString(dr["TEST_PARAM1"]).ToUpper() + "',";
strInsertSql = strInsertSql + "'" + Convert.ToString(dr["TEST_PARAM2"]).ToUpper() + "',";
strInsertSql = strInsertSql + "'" + Convert.ToString(dr["PROMPT"]).ToUpper() + "',";
strInsertSql = strInsertSql + "'" + Convert.ToString(dr["VOLTAGE"]).ToUpper() + "',";
strInsertSql = strInsertSql + "'" + Convert.ToString(dr["FREQUENCY"]).ToUpper() + "',";
strInsertSql = strInsertSql + "'" + Convert.ToString(dr["ECURREN"]).ToUpper() + "',";
strInsertSql = strInsertSql + "'" + Convert.ToString(dr["HILIMIT"]).ToUpper() + "',";
strInsertSql = strInsertSql + "'" + Convert.ToString(dr["LOLIMIT"]).ToUpper() + "',";
//[RAMPUP] ,[DWELLTIME],[DELAYTIME],[RAMPHI],[CHARGELO],[OFFSET],[ARCSENSE],[ARCFAIL],[SCANNER]
strInsertSql = strInsertSql + "'" + Convert.ToString(dr["RAMPUP"]).ToUpper() + "',";
strInsertSql = strInsertSql + "'" + Convert.ToString(dr["DWELLTIME"]).ToUpper() + "',";
strInsertSql = strInsertSql + "'" + Convert.ToString(dr["DELAYTIME"]).ToUpper() + "',";
strInsertSql = strInsertSql + "'" + Convert.ToString(dr["RAMPHI"]).ToUpper() + "',";
strInsertSql = strInsertSql + "'" + Convert.ToString(dr["CHARGELO"]).ToUpper() + "',";
strInsertSql = strInsertSql + "'" + Convert.ToString(dr["OFFSET"]).ToUpper() + "',";
strInsertSql = strInsertSql + "'" + Convert.ToString(dr["ARCSENSE"]).ToUpper() + "',";
strInsertSql = strInsertSql + "'" + Convert.ToString(dr["ARCFAIL"]).ToUpper() + "',";
strInsertSql = strInsertSql + "'" + Convert.ToString(dr["SCANNER"]).ToUpper() + "'";
strInsertSql = strInsertSql + ")";
//数据在数据库中是否已有（LOT_NUMBER\TEST_TIME\EQUIPMENT_CODE）
string TEST_ID = "";
TEST_ID = "select TEST_ID from ZWIP_VIR_TEST where TEST_ID='" + dr["TEST_ID"] + "'" + "and EQUIPMENT_CODE ='" + element.EqpName + "'";
if (TEST_ID != dr["TEST_ID"])
{
lstSqlForInsert.Add(strInsertSql);
}
strInsertSql = "";
isDefault = false;
#endregion
}
sql = "";
if (blTransferDataResult)
{
string strExeResult = DatabaseEx.getDbInstance().ExecuteNonQuery(lstSqlForInsert);
strInsertSql = "";
if (strExeResult != "" && strExeResult.Length > 0)
{
blTransferDataResult = false;
strTransferDataMsg = strExeResult;
}
}
#endregion //End Get Access JYNY测试数据
}
}
catch (Exception ex)
{
blTransferDataResult = false;
strTransferDataMsg = ex.Message;
}
finally
{
lstSqlForInsert.Clear();
lstSqlForUpdateEFCDataDefault.Clear();
}
DataTransferFinishedArgs arg = new DataTransferFinishedArgs();
arg.TransferDataResult = blTransferDataResult;
arg.TransferDbFile = strAccessFullPath;
if (blTransferDataResult)
{
strTransferDataMsg = string.Format("{1:yyyy-MM-dd HH:mm:ss}上传安规数据数据成功>{0} ", strTransferDataMsg, System.DateTime.Now.ToString());
arg.MaxTestDateTime = dNewMaxTestTime;
}
else
{
strTransferDataMsg = string.Format("{1:yyyy-MM-dd HH:mm:ss}上传安规数据数据失败>{0} ", strTransferDataMsg, System.DateTime.Now.ToString());
arg.MaxTestDateTime = dMaxUploadTestTime;
}
arg.TransferMsg = strTransferDataMsg;
if (OnDataTransferFinished != null)
{
OnDataTransferFinished(this, arg);
}
}
