junjie.wang
/
Estsh-Yiri
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
You cannot select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
master
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'master'
${ noResults }
Estsh-Yiri/Estsh.Client.StepLibrary/TorqueCollect/TorqueCollectApp.cs

443 lines
25 KiB
C#
Raw Permalink Normal View History Unescape Escape

更新
2 years ago
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<dynamic> 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());
}
}
/// <summary>
/// 根据条码（总成零件号）和工站编号获取本工站需要采集的关键数据
/// </summary>
/// <param name="sn">条码</param>
/// <param name="terminalID">工站编号</param>
/// <returns>查询到的相应产品配置</returns>
public List<dynamic> 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;
}
}
/// <summary>
/// 查询工位相应的上一工位数据组
/// </summary>
/// <returns></returns>
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;
}
}
}
/// <summary>
/// 更新关键数据结果
/// </summary>
/// <param name="sn"></param>
/// <param name="keyDataValue"></param>
/// <returns></returns>
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;
}
/// <summary>
/// 把当前条码扭矩信息转移
/// </summary>
/// <param name="sn"></param>
/// <param name="terminalId"></param>
/// <returns></returns>
public bool atlasDataTransfer(string sn, string terminalId)
{
List<string> SqlStrings = new List<string>(3);
List<Hashtable> Parameters = new List<Hashtable>(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);
}
/// <summary>
/// 把当前条码扭矩信息转移
/// </summary>
/// <param name="sn"></param>
/// <param name="terminalId"></param>
/// <returns></returns>
public bool atlasDataTransfer(string sn, string terminalId,int typeStep2)
{
List<string> SqlStrings = new List<string>(3);
List<Hashtable> Parameters = new List<Hashtable>(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);
}
}
}