using System; using System.Collections.Generic; using System.Text; using ApServerProvider; using System.Collections; using Estsh.Client.Base; using System.Data; using Com.Estsh.MES.App; using System.IO; namespace Estsh.Client { public class TorqueCollectApp : BaseApp { public TorqueCollectApp(RemotingProxy _proxy) : base(_proxy) { } public List GetKeyDataValue(string serialNumber, string terminalId) { lock (_remotingProxy) { StringBuilder querySen = new StringBuilder(1024); querySen.AppendLine("SELECT keydata_desc , keydata_value , "); querySen.AppendLine(" keydata_angle , "); querySen.AppendLine(" 'GOOD' AS keydata_status ,"); querySen.AppendLine(" keydata_result , "); querySen.AppendLine(" create_ymd AS keydata_ymd , "); querySen.AppendLine(" create_hms AS keydata_hms, "); querySen.AppendLine(" guid "); querySen.AppendLine("FROM dbo.g_sn_keydata_temp(NOLOCK) "); querySen.AppendLine("WHERE terminal_id='" + terminalId + "' "); querySen.AppendLine(" AND keydata_result='P' ORDER BY keydata_ymd,keydata_hms "); return _remotingProxy.GetDataTable(querySen.ToString()); } } /// /// 根据条码（总成零件号）和工站编号获取本工站需要采集的关键数据 /// /// 条码 /// 工站编号 /// 查询到的相应产品配置 public List GetKeyData(string serialNumber, string terminalId, int stepID) { try { lock (_remotingProxy) { StringBuilder SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("SELECT a.keydata_id , "); SqlStringBuilder.Append(" b.keydata_name , "); SqlStringBuilder.Append(" b.keydata_desc , "); SqlStringBuilder.Append(" b.min_value , "); SqlStringBuilder.Append(" b.max_value , "); SqlStringBuilder.Append(" CONVERT(VARCHAR(10),b.min_value) + '～' + CONVERT(VARCHAR(10),b.max_value) AS value , "); SqlStringBuilder.Append(" b.seq , "); SqlStringBuilder.Append(" a.item_count , "); SqlStringBuilder.Append(" '' AS keydata_value , "); SqlStringBuilder.Append(" '' AS keydata_angle , "); SqlStringBuilder.Append(" '' AS keydata_status , "); SqlStringBuilder.Append(" '' AS keydata_result , "); SqlStringBuilder.Append(" '' AS keydata_ymd , "); SqlStringBuilder.Append(" '' AS keydata_hms "); SqlStringBuilder.Append("FROM dbo.sys_part_keydata(NOLOCK) AS a "); SqlStringBuilder.Append(" LEFT JOIN dbo.sys_keydata(NOLOCK) AS b ON a.keydata_id=b.keydata_id "); SqlStringBuilder.Append(" LEFT JOIN dbo.sys_part(NOLOCK) AS c ON a.part_id=c.part_id "); SqlStringBuilder.Append(" LEFT JOIN dbo.g_workorder_sn(NOLOCK) AS d ON c.part_id=d.part_id "); SqlStringBuilder.Append("WHERE a.enabled='Y' AND b.enabled='Y' "); SqlStringBuilder.Append(" AND a.terminal_id=@terminal_id "); SqlStringBuilder.Append(" AND d.serial_number = @serial_number AND a.step_id=@stepID "); SqlStringBuilder.Append("ORDER BY b.seq "); Hashtable Params = new Hashtable(3); Params.Add("@terminal_id", terminalId); Params.Add("@serial_number", serialNumber); Params.Add("@stepID", stepID); return _remotingProxy.GetDataTable(SqlStringBuilder.ToString(), Params); } } catch (Exception ex) { // 记录日志 using (StreamWriter sw = new StreamWriter("PLC_Log-" + DateTime.Now.ToString("yyyy-MM") + ".txt", true)) { sw.WriteLine(string.Format("{0} {1}", "[" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "] GetKeyData()", ex.ToString())); sw.Flush(); sw.Close(); } return null; } } /// /// 查询工位相应的上一工位数据组 /// /// public string GetTerminalGroupValue() { string sql = "SELECT param_value FROM dbo.sys_base(NOLOCK) WHERE param_name='TerminalLastGroup'"; try { lock (_remotingProxy) { object obj = _remotingProxy.GetScalar(sql); if (obj == null) { return ""; } return obj.ToString(); } } catch { return "ERROR"; } } public bool UpdateOPCPointValue(string terminal_id, string terminal_params_name) { lock (_remotingProxy) { StringBuilder querySen = new StringBuilder(1024); querySen.AppendLine("UPDATE dbo.opc_point "); querySen.AppendLine("SET point_flag1 = 101 , "); querySen.AppendLine(" update_time = GETDATE() "); querySen.AppendLine("WHERE point_name = ( SELECT param_value "); querySen.AppendLine(" FROM dbo.sys_terminal_params "); querySen.AppendLine(" WHERE terminal_id = @terminal_id "); querySen.AppendLine(" AND param_name = @terminal_params_name "); querySen.AppendLine(" ) "); Hashtable ht = new Hashtable(2); ht.Add("@terminal_id", terminal_id); ht.Add("@terminal_params_name", terminal_params_name); int rows = _remotingProxy.ExecuteNonQuery(querySen.ToString(), ht); if (rows > 0) { return true; } else { return false; } } } /// /// 更新关键数据结果 /// /// /// /// public bool updateKeyDataResult(string sn, string keyDataValue, string guid) { StringBuilder SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("UPDATE dbo.g_sn_keydata_temp "); SqlStringBuilder.Append("SET keydata_result = 'Fail' "); SqlStringBuilder.Append("WHERE guid = @guid "); Hashtable ht = new Hashtable(2); ht.Add("@guid", guid); //ht.Add("@keyDataValue", keyDataValue); return _remotingProxy.ExecuteNonQuery(SqlStringBuilder.ToString(), ht) > 0; } /// /// 把当前条码扭矩信息转移 /// /// /// /// public bool atlasDataTransfer(string sn, string terminalId) { List SqlStrings = new List(3); List Parameters = new List(3); Hashtable Params = new Hashtable(); Params.Add("@sn", sn); Params.Add("@terminalId", terminalId); Params.Add("@userId", _remotingProxy.UserID); StringBuilder SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("INSERT INTO dbo.g_sn_keydata "); SqlStringBuilder.Append(" ( serial_number , "); SqlStringBuilder.Append(" part_id , "); SqlStringBuilder.Append(" terminal_id , "); SqlStringBuilder.Append(" keydata_value , "); SqlStringBuilder.Append(" keydata_result , "); SqlStringBuilder.Append(" keydata_desc , "); SqlStringBuilder.Append(" keydata_id , "); SqlStringBuilder.Append(" keydata_angle , "); SqlStringBuilder.Append(" enabled , "); SqlStringBuilder.Append(" update_userid , "); SqlStringBuilder.Append(" update_ymd , "); SqlStringBuilder.Append(" update_hms , "); SqlStringBuilder.Append(" create_userid , "); SqlStringBuilder.Append(" create_ymd , "); SqlStringBuilder.Append(" create_hms , "); SqlStringBuilder.Append(" guid "); SqlStringBuilder.Append(" ) "); SqlStringBuilder.Append(" SELECT @sn , "); SqlStringBuilder.Append(" aa.part_id , "); SqlStringBuilder.Append(" aa.terminal_id , "); SqlStringBuilder.Append(" aa.keydata_value , "); SqlStringBuilder.Append(" aa.keydata_result , "); SqlStringBuilder.Append(" bb.keydata_name , "); SqlStringBuilder.Append(" aa.keydata_id , "); SqlStringBuilder.Append(" aa.keydata_angle , "); SqlStringBuilder.Append(" aa.enabled , "); SqlStringBuilder.Append(" aa.update_userid , "); SqlStringBuilder.Append(" aa.update_ymd , "); SqlStringBuilder.Append(" aa.update_hms , "); SqlStringBuilder.Append(" aa.create_userid , "); SqlStringBuilder.Append(" aa.create_ymd , "); SqlStringBuilder.Append(" aa.create_hms , "); SqlStringBuilder.Append(" aa.guid "); SqlStringBuilder.Append(" FROM ( SELECT serial_number , "); SqlStringBuilder.Append(" part_id , "); SqlStringBuilder.Append(" terminal_id , "); SqlStringBuilder.Append(" keydata_value , "); SqlStringBuilder.Append(" keydata_result , "); SqlStringBuilder.Append(" keydata_desc , "); SqlStringBuilder.Append(" keydata_id , "); SqlStringBuilder.Append(" keydata_angle , "); SqlStringBuilder.Append(" enabled , "); SqlStringBuilder.Append(" update_userid , "); SqlStringBuilder.Append(" update_ymd , "); SqlStringBuilder.Append(" update_hms , "); SqlStringBuilder.Append(" @userId as create_userid, "); SqlStringBuilder.Append(" create_ymd , "); SqlStringBuilder.Append(" create_hms , "); SqlStringBuilder.Append(" guid , "); SqlStringBuilder.Append(" ROW_NUMBER() OVER ( ORDER BY create_ymd, create_hms ) AS tank "); SqlStringBuilder.Append(" FROM dbo.g_sn_keydata_temp "); SqlStringBuilder.Append(" WHERE terminal_id = @terminalId AND keydata_result='P'"); SqlStringBuilder.Append(" ) AS aa "); SqlStringBuilder.Append(" LEFT JOIN ( SELECT ROW_NUMBER() OVER ( ORDER BY seq ) AS tank , "); SqlStringBuilder.Append(" keydata_name "); SqlStringBuilder.Append(" FROM dbo.sys_part_keydata AS a "); SqlStringBuilder.Append(" LEFT JOIN dbo.sys_keydata AS b ON a.keydata_id = b.keydata_id "); SqlStringBuilder.Append(" WHERE terminal_id = @terminalId "); SqlStringBuilder.Append(" AND part_id IN ( "); SqlStringBuilder.Append(" SELECT part_id "); SqlStringBuilder.Append(" FROM dbo.g_sn_status "); SqlStringBuilder.Append(" WHERE serial_number = @sn ) "); SqlStringBuilder.Append(" ) AS bb ON bb.tank = aa.tank "); SqlStrings.Add(SqlStringBuilder.ToString()); Parameters.Add(Params); SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("INSERT INTO dbo.g_sn_keydata "); SqlStringBuilder.Append(" ( serial_number , "); SqlStringBuilder.Append(" part_id , "); SqlStringBuilder.Append(" terminal_id , "); SqlStringBuilder.Append(" keydata_value , "); SqlStringBuilder.Append(" keydata_result , "); SqlStringBuilder.Append(" keydata_desc , "); SqlStringBuilder.Append(" keydata_id , "); SqlStringBuilder.Append(" keydata_angle , "); SqlStringBuilder.Append(" enabled , "); SqlStringBuilder.Append(" update_userid , "); SqlStringBuilder.Append(" update_ymd , "); SqlStringBuilder.Append(" update_hms , "); SqlStringBuilder.Append(" create_userid , "); SqlStringBuilder.Append(" create_ymd , "); SqlStringBuilder.Append(" create_hms , "); SqlStringBuilder.Append(" guid "); SqlStringBuilder.Append(" ) "); SqlStringBuilder.Append(" SELECT @sn , "); SqlStringBuilder.Append(" aa.part_id , "); SqlStringBuilder.Append(" aa.terminal_id , "); SqlStringBuilder.Append(" aa.keydata_value , "); SqlStringBuilder.Append(" aa.keydata_result , "); SqlStringBuilder.Append(" bb.keydata_name , "); SqlStringBuilder.Append(" aa.keydata_id , "); SqlStringBuilder.Append(" aa.keydata_angle , "); SqlStringBuilder.Append(" aa.enabled , "); SqlStringBuilder.Append(" aa.update_userid , "); SqlStringBuilder.Append(" aa.update_ymd , "); SqlStringBuilder.Append(" aa.update_hms , "); SqlStringBuilder.Append(" aa.create_userid , "); SqlStringBuilder.Append(" aa.create_ymd , "); SqlStringBuilder.Append(" aa.create_hms , "); SqlStringBuilder.Append(" aa.guid "); SqlStringBuilder.Append(" FROM ( SELECT serial_number , "); SqlStringBuilder.Append(" part_id , "); SqlStringBuilder.Append(" terminal_id , "); SqlStringBuilder.Append(" keydata_value , "); SqlStringBuilder.Append(" keydata_result , "); SqlStringBuilder.Append(" keydata_desc , "); SqlStringBuilder.Append(" keydata_id , "); SqlStringBuilder.Append(" keydata_angle , "); SqlStringBuilder.Append(" enabled , "); SqlStringBuilder.Append(" update_userid , "); SqlStringBuilder.Append(" update_ymd , "); SqlStringBuilder.Append(" update_hms , "); SqlStringBuilder.Append(" @userId as create_userid, "); SqlStringBuilder.Append(" create_ymd , "); SqlStringBuilder.Append(" create_hms , "); SqlStringBuilder.Append(" guid , "); SqlStringBuilder.Append(" ROW_NUMBER() OVER ( ORDER BY create_ymd, create_hms ) AS tank "); SqlStringBuilder.Append(" FROM dbo.g_sn_keydata_temp "); SqlStringBuilder.Append(" WHERE terminal_id = @terminalId AND keydata_result='F'"); SqlStringBuilder.Append(" ) AS aa "); SqlStringBuilder.Append(" LEFT JOIN ( SELECT ROW_NUMBER() OVER ( ORDER BY seq ) AS tank , "); SqlStringBuilder.Append(" keydata_name "); SqlStringBuilder.Append(" FROM dbo.sys_part_keydata AS a "); SqlStringBuilder.Append(" LEFT JOIN dbo.sys_keydata AS b ON a.keydata_id = b.keydata_id "); SqlStringBuilder.Append(" WHERE terminal_id = @terminalId "); SqlStringBuilder.Append(" AND part_id IN ( "); SqlStringBuilder.Append(" SELECT part_id "); SqlStringBuilder.Append(" FROM dbo.g_sn_status "); SqlStringBuilder.Append(" WHERE serial_number = @sn ) "); SqlStringBuilder.Append(" ) AS bb ON bb.tank = aa.tank "); SqlStrings.Add(SqlStringBuilder.ToString()); Parameters.Add(Params); SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append(" DELETE dbo.g_sn_keydata_temp WHERE terminal_id=@terminalId "); SqlStrings.Add(SqlStringBuilder.ToString()); Parameters.Add(Params); return _remotingProxy.ExecuteSqlTransaction(SqlStrings, Parameters); } /// /// 把当前条码扭矩信息转移 /// /// /// /// public bool atlasDataTransfer(string sn, string terminalId,int typeStep2) { List SqlStrings = new List(3); List Parameters = new List(3); Hashtable Params = new Hashtable(2); Params.Add("@sn", sn); Params.Add("@terminalId", terminalId); Params.Add("@typeStep2", typeStep2); StringBuilder SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("INSERT INTO dbo.g_sn_keydata "); SqlStringBuilder.Append(" ( serial_number , "); SqlStringBuilder.Append(" part_id , "); SqlStringBuilder.Append(" terminal_id , "); SqlStringBuilder.Append(" keydata_value , "); SqlStringBuilder.Append(" keydata_result , "); SqlStringBuilder.Append(" keydata_desc , "); SqlStringBuilder.Append(" keydata_id , "); SqlStringBuilder.Append(" keydata_angle , "); SqlStringBuilder.Append(" enabled , "); SqlStringBuilder.Append(" update_userid , "); SqlStringBuilder.Append(" update_ymd , "); SqlStringBuilder.Append(" update_hms , "); SqlStringBuilder.Append(" create_userid , "); SqlStringBuilder.Append(" create_ymd , "); SqlStringBuilder.Append(" create_hms , "); SqlStringBuilder.Append(" guid "); SqlStringBuilder.Append(" ) "); SqlStringBuilder.Append(" SELECT @sn , "); SqlStringBuilder.Append(" aa.part_id , "); SqlStringBuilder.Append(" aa.terminal_id , "); SqlStringBuilder.Append(" aa.keydata_value , "); SqlStringBuilder.Append(" aa.keydata_result , "); SqlStringBuilder.Append(" bb.keydata_name , "); SqlStringBuilder.Append(" aa.keydata_id , "); SqlStringBuilder.Append(" aa.keydata_angle , "); SqlStringBuilder.Append(" aa.enabled , "); SqlStringBuilder.Append(" aa.update_userid , "); SqlStringBuilder.Append(" aa.update_ymd , "); SqlStringBuilder.Append(" aa.update_hms , "); SqlStringBuilder.Append(" aa.create_userid , "); SqlStringBuilder.Append(" aa.create_ymd , "); SqlStringBuilder.Append(" aa.create_hms , "); SqlStringBuilder.Append(" aa.guid "); SqlStringBuilder.Append(" FROM ( SELECT serial_number , "); SqlStringBuilder.Append(" part_id , "); SqlStringBuilder.Append(" terminal_id , "); SqlStringBuilder.Append(" keydata_value , "); SqlStringBuilder.Append(" keydata_result , "); SqlStringBuilder.Append(" keydata_desc , "); SqlStringBuilder.Append(" keydata_id , "); SqlStringBuilder.Append(" keydata_angle , "); SqlStringBuilder.Append(" enabled , "); SqlStringBuilder.Append(" update_userid , "); SqlStringBuilder.Append(" update_ymd , "); SqlStringBuilder.Append(" update_hms , "); SqlStringBuilder.Append(" create_userid , "); SqlStringBuilder.Append(" create_ymd , "); SqlStringBuilder.Append(" create_hms , "); SqlStringBuilder.Append(" guid , "); SqlStringBuilder.Append(" ROW_NUMBER() OVER ( ORDER BY create_ymd, create_hms ) AS tank "); SqlStringBuilder.Append(" FROM dbo.g_sn_keydata_temp "); SqlStringBuilder.Append(" WHERE terminal_id = @terminalId AND keydata_result='P'"); SqlStringBuilder.Append(" ) AS aa "); SqlStringBuilder.Append(" LEFT JOIN ( SELECT ROW_NUMBER() OVER ( ORDER BY seq ) AS tank , "); SqlStringBuilder.Append(" keydata_name "); SqlStringBuilder.Append(" FROM dbo.sys_part_keydata AS a "); SqlStringBuilder.Append(" LEFT JOIN dbo.sys_keydata AS b ON a.keydata_id = b.keydata_id "); SqlStringBuilder.Append(" WHERE terminal_id = @terminalId "); SqlStringBuilder.Append(" AND part_id IN ( "); SqlStringBuilder.Append(" SELECT part_id "); SqlStringBuilder.Append(" FROM dbo.g_sn_status "); //------------------------------------------------------------------------------------------------------------------------------------------------ SqlStringBuilder.Append(" WHERE serial_number = @sn ) and step_id=@typeStep2 "); SqlStringBuilder.Append(" ) AS bb ON bb.tank = aa.tank "); SqlStrings.Add(SqlStringBuilder.ToString()); Parameters.Add(Params); SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append(" DELETE dbo.g_sn_keydata_temp WHERE terminal_id=@terminalId "); SqlStrings.Add(SqlStringBuilder.ToString()); Parameters.Add(Params); return _remotingProxy.ExecuteSqlTransaction(SqlStrings, Parameters); } } }