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/MesApp.cs

2284 lines
113 KiB
C#
Raw Permalink Blame History Unescape Escape

This file contains ambiguous Unicode characters!

This file contains ambiguous Unicode characters that may be confused with others in your current locale. If your use case is intentional and legitimate, you can safely ignore this warning. Use the Escape button to highlight these characters.

using Estsh.Client.Common.HttpClientUtil;
using Estsh.Core.Model.Result;
using System.Collections;
using System.Data;
using System.Security.Cryptography;
using System.Text;
using System.Text.Json;
namespace Estsh.Client
{
public class MesApp
{
private HttpClientHelper httpClient = null;
private int userId;
private string userNo;
public MesApp(HttpClientHelper _httpClient, int userId, string userNo)
{
httpClient = _httpClient;
this.userId = userId;
this.userNo = userNo;
}
/// <summary>
/// 获取员工Name
/// </summary>
/// <param name="user_id"></param>
/// <returns></returns>
public string GetEmp(int user_id)
{
string sqlString = "SELECT emp_name FROM sys_emp WHERE enabled = 'Y' and emp_id = " + user_id + "";
object emp_name = httpClient.GetScalar(sqlString);
if (emp_name != null)
{
return emp_name.ToString();
}
else
{
return "";
}
}
/// <summary>
/// 根据工位编号和参数名称从系统中查询参数的设定值
/// </summary>
/// <param name="terminalId">工位编号</param>
/// <param name="paramName">参数名称</param>
/// <returns>参数值</returns>
public string GetTerminalParams(int terminalId, string paramName)
{
string SqlString = "select param_value from dbo.sys_terminal_params where enabled='Y' and terminal_id=@p1 and param_name=@p2";
Dictionary<string, object> Params = new Dictionary<string, object>(2);
Params.Add("@p1", terminalId);
Params.Add("@p2", paramName);
object obj = httpClient.GetScalar(SqlString, Params);
return obj != null && obj != DBNull.Value ? obj.ToString() : string.Empty;
}
/// <summary>
/// 根据零件号和制程编号查询工艺流程编号
/// </summary>
/// <param name="partNo">零件号</param>
/// <param name="processId">制程编号</param>
/// <returns>工艺流程编号，查询不到时返回 0</returns>
public int GetRouteId(string partNo, int processId)
{
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("SELECT route_id ");
SqlStringBuilder.Append("FROM dbo.mes_part_route ");
SqlStringBuilder.Append("WHERE part_no = @part_no ");
SqlStringBuilder.Append(" AND process_id = @process_id ");
Dictionary<string, object> Params = new Dictionary<string, object>(2);
Params.Add("@part_no", partNo);
Params.Add("@process_id", processId);
object obj = httpClient.GetScalar(SqlStringBuilder.ToString(), Params);
return obj != null && obj != DBNull.Value ? Convert.ToInt32(obj) : 0;
}
/// <summary>
/// 根据过程条码获取零件号
/// </summary>
/// <param name="serialNumber"></param>
/// <returns></returns>
public int GetPartIDBySN(string serialNumber)
{
string SqlString = "select part_id from dbo.g_sn_status where serial_number=@sn";
Dictionary<string, object> Params = new Dictionary<string, object>(1);
Params.Add("@sn", serialNumber);
object obj = httpClient.GetScalar(SqlString, Params);
return obj == null ? 0 : Convert.ToInt32(obj);
}
/// <summary>
/// 根据过程条码获取零件号
/// </summary>
/// <param name="serialNumber"></param>
/// <returns></returns>
public List<dynamic> GetProcess(string serialNumber)
{
string SqlString = "SELECT d.* FROM dbo.g_workorder_sn a" +
" LEFT JOIN dbo.sys_part b ON a.part_id=b.part_id" +
" LEFT JOIN dbo.sys_bom_detail c ON a.part_id=c.part_id AND c.process_id !=0" +
" LEFT JOIN dbo.sys_process d ON c.process_id=d.process_id" +
" WHERE a.serial_number = @sn";
Dictionary<string, object> Params = new Dictionary<string, object>(1);
Params.Add("@sn", serialNumber);
return httpClient.GetEntityList(SqlString, Params);
}
/// <summary>
/// 获取服务器时间
/// </summary>
/// <returns></returns>
public DateTime GetServerTime()
{
return DateTime.Parse(httpClient.GetScalar("select getdate()").ToString());
}
/// <summary>
/// 根据电脑名称查询工站的相关信息
/// </summary>
/// <param name="stationName">电脑名称</param>
/// <returns>工站信息</returns>
public List<dynamic> GetStationInfo(string stationName)
{
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("SELECT a.unlock_pwd , a.station_ip, ");
SqlStringBuilder.Append(" a.view_board_id , ");
SqlStringBuilder.Append(" b.terminal_name , b.terminal_id , ");
SqlStringBuilder.Append(" c.pdline_desc ");
SqlStringBuilder.Append("FROM dbo.sys_terminal_control a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_terminal b ON a.terminal_id = b.terminal_id ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_pdline c ON b.pdline_id = c.pdline_id ");
SqlStringBuilder.Append("WHERE a.station_name = @station_name ");
Dictionary<string, object> Params = new Dictionary<string, object>(1);
Params.Add("@station_name", stationName);
return httpClient.GetEntityList(SqlStringBuilder.ToString(), Params);
}
/// <summary>
/// 读取PLC值
/// </summary>
/// <returns></returns>
public string ReadyPlc(string pointName, int terminalID)
{
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append(" SELECT a.point_value FROM dbo.opc_point a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_terminal_params b ON a.point_name=b.param_value ");
SqlStringBuilder.Append(" WHERE param_name=@pointName AND terminal_id=@terminalID ");
Dictionary<string, object> ht = new Dictionary<string, object>(2);
ht.Add("@pointName", pointName);
ht.Add("@terminalID", terminalID);
object obj = httpClient.GetScalar(SqlStringBuilder.ToString(), ht);
return obj != null && obj != DBNull.Value ? obj.ToString() : string.Empty;
}
public bool heartBeat(string pointName)
{
string sql = "SELECT * FROM dbo.opc_point WHERE point_name ='" + pointName + "'";
object obj = httpClient.GetScalar(sql);
return obj != null && obj != DBNull.Value ? true : false;
}
public bool sendHeartBeat(string pointName)
{
string sql = "UPDATE dbo.opc_point SET point_value='TRUE', point_flag1 =101,update_time =GETDATE() WHERE point_name ='" + pointName + "'";
return httpClient.Execute(sql) > 0;
}
/// <summary>
/// 根据工站编号查询工站的相关信息
/// </summary>
/// <param name="terminalID">工站编号</param>
/// <returns>工站信息</returns>
public dynamic GetTerminalInfo(int terminalID)
{
lock (httpClient)
{
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("SELECT a.unlock_pwd , a.station_ip, ");
SqlStringBuilder.Append(" a.view_board_id , ");
SqlStringBuilder.Append(" b.terminal_name , b.terminal_id , ");
SqlStringBuilder.Append(" c.pdline_desc ");
SqlStringBuilder.Append("FROM dbo.sys_terminal_control a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_terminal b ON a.terminal_id = b.terminal_id ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_pdline c ON b.pdline_id = c.pdline_id ");
SqlStringBuilder.Append("WHERE a.terminal_id = @terminal_id ");
Dictionary<string, object> Params = new Dictionary<string, object>(1);
Params.Add("@terminal_id", terminalID);
var dt = httpClient.GetEntityList(SqlStringBuilder.ToString(), Params);
if (dt != null && dt.Count > 0)
return dt[0];
return null;
}
}
/// <summary>
/// 获取排序生产数据
/// </summary>
/// <param name="terminalID">工站编号</param>
/// <param name="showABTest"
/// <returns>排序生产数据</returns>
public List<dynamic> GetOrderData(int terminalID)
{
lock (httpClient)
{
if (terminalID <= 0)
{
return null;
}
string sql = "SELECT param_value FROM dbo.sys_base WHERE param_name='SNCutCheck' AND enabled='Y' ";
object obj = httpClient.GetScalar(sql);
#region
//StringBuilder SqlStringBuilder = new StringBuilder(1024);
//SqlStringBuilder.Append("SELECT TOP 15 ");
//SqlStringBuilder.Append(" a.ruid , ");
//SqlStringBuilder.Append(" a.seq , ");
//SqlStringBuilder.Append(" b.car_no , ");
//SqlStringBuilder.Append(" h.type_id , ");
//SqlStringBuilder.Append(" h.type_code , ");
//SqlStringBuilder.Append(" h.type_name , ");
//SqlStringBuilder.Append(" g.model_id , ");
//SqlStringBuilder.Append(" g.model_name , ");
//SqlStringBuilder.Append(" g.model_desc , ");
//SqlStringBuilder.Append(" i.enum_desc , ");
//SqlStringBuilder.Append(" d.part_spec , ");
//SqlStringBuilder.Append(" a.part_id , ");
//SqlStringBuilder.Append(" b.type AS wo_type , ");
////SqlStringBuilder.Append(" a.type AS wo_detail_type , ");
//SqlStringBuilder.Append(" CASE a.type WHEN 'S' THEN '订单生产' WHEN 'E' THEN '插单生产' ELSE '其他' END AS wo_detail_type, ");
//SqlStringBuilder.Append(" c.serial_number , ");
//SqlStringBuilder.Append(" a.create_ymd + ' ' + a.create_hms AS ctime , ");
//SqlStringBuilder.Append(" d.part_no , ");
//SqlStringBuilder.Append(" a.ruid AS wo_detail_id , ");
//SqlStringBuilder.Append(" 0 AS row_select , ");
//SqlStringBuilder.Append(" d.option10 , ");
//SqlStringBuilder.Append(" d.part_location, ");
//SqlStringBuilder.Append(" RIGHT(c.serial_number ,10) AS number ");
//SqlStringBuilder.Append("FROM dbo.g_workorder_detail a ");
//SqlStringBuilder.Append(" LEFT JOIN dbo.g_workorder_sn c ON a.ruid = c.wo_detail_id ");
//SqlStringBuilder.Append(" LEFT JOIN dbo.g_sn_status j ON j.serial_number = c.serial_number ");
//SqlStringBuilder.Append(" LEFT JOIN dbo.g_workorder b ON a.workorder_id = b.ruid ");
//SqlStringBuilder.Append(" LEFT JOIN dbo.sys_part d ON a.part_id = d.part_id ");
//SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model g ON j.model_id = g.model_id ");
//SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model_type h ON g.model_type_id = h.type_id ");
//SqlStringBuilder.Append(" LEFT JOIN dbo.sys_enum i ON i.enum_type = 'sys_part_location' ");
//SqlStringBuilder.Append(" AND i.enum_value = d.part_location ");
//SqlStringBuilder.Append(" LEFT JOIN dbo.g_stock_order AS k ON k.order_no = b.order_no ");
//SqlStringBuilder.Append("WHERE RIGHT('00' + d.part_location, 2) IN ( ");
//SqlStringBuilder.Append(" SELECT RIGHT('00' + col, 2) ");
//SqlStringBuilder.Append(" FROM dbo.f_split(( SELECT ISNULL(view_part_location, '01,02') ");
//SqlStringBuilder.Append(" FROM dbo.sys_terminal_control ");
//SqlStringBuilder.Append(" WHERE terminal_id = @terminal_id ");
//SqlStringBuilder.Append(" ), ',') ) ");
//SqlStringBuilder.Append(" AND a.seq > ( SELECT wo_detail_seq ");
//SqlStringBuilder.Append(" FROM dbo.sys_terminal_point ");
//SqlStringBuilder.Append(" WHERE terminal_id = @terminal_id ");
//SqlStringBuilder.Append(" ) ");
//SqlStringBuilder.Append(" AND j.current_status NOT IN ('9','1') ");
//SqlStringBuilder.Append(" AND j.work_flag <> 1 ");
//SqlStringBuilder.Append(" AND a.enabled = 'Y' ");
//SqlStringBuilder.Append("ORDER BY a.seq ");
#endregion
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append(" SELECT TOP 15 ");
SqlStringBuilder.Append(" a.ruid , ");
SqlStringBuilder.Append(" l.seq , ");
SqlStringBuilder.Append(" b.car_no , ");
SqlStringBuilder.Append(" h.type_id , ");
SqlStringBuilder.Append(" h.type_code , ");
SqlStringBuilder.Append(" h.type_name , ");
SqlStringBuilder.Append(" g.model_id , ");
SqlStringBuilder.Append(" g.model_name , ");
SqlStringBuilder.Append(" g.model_desc , ");
SqlStringBuilder.Append(" g.model_type , ");
SqlStringBuilder.Append(" i.enum_desc , ");
SqlStringBuilder.Append(" d.part_spec , ");
SqlStringBuilder.Append(" a.part_id , ");
SqlStringBuilder.Append(" b.type AS wo_type , ");
SqlStringBuilder.Append(" CASE a.type WHEN 'S' THEN '订单生产' WHEN 'E' THEN '紧急插单' WHEN 'C'THEN '试制' WHEN 'Z'THEN '试制插单' WHEN 'P'THEN '培训' WHEN 'Q'THEN '培训插单' WHEN 'X'THEN 'EDI生产' ELSE '其他' END AS wo_detail_type, ");
SqlStringBuilder.Append(" c.serial_number , ");
SqlStringBuilder.Append(" a.create_ymd + ' ' + a.create_hms AS ctime , ");
SqlStringBuilder.Append(" d.part_no , ");
SqlStringBuilder.Append(" a.ruid AS wo_detail_id , ");
SqlStringBuilder.Append(" 0 AS row_select , ");
SqlStringBuilder.Append(" d.option10 , ");
SqlStringBuilder.Append(" d.part_location,k.edi_id, ");
//余姚工厂使用 SUBSTRING(RIGHT(c.serial_number,21),1,12) AS number 其他工厂使用：RIGHT(c.serial_number,10) AS number
if (obj != null)
{
if (obj.ToString() == "Y")
{
SqlStringBuilder.Append("SUBSTRING(RIGHT(c.serial_number,21),1,12) AS number ");
}
else
{
SqlStringBuilder.Append(" RIGHT(c.serial_number,10) AS number ");
}
}
else
{
SqlStringBuilder.Append(" RIGHT(c.serial_number,10) AS number ");
}
SqlStringBuilder.Append(" FROM dbo.g_workorder_detail a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.g_workorder_sn c ON a.ruid = c.wo_detail_id ");
SqlStringBuilder.Append(" LEFT JOIN dbo.g_sn_status j ON j.serial_number = c.serial_number ");
SqlStringBuilder.Append(" LEFT JOIN dbo.g_workorder b ON a.workorder_id = b.ruid ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_part d ON a.part_id = d.part_id ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model g ON j.model_id = g.model_id ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model_type h ON g.model_type_id = h.type_id ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_enum i ON i.enum_type = 'sys_part_location' ");
SqlStringBuilder.Append(" AND i.enum_value = d.part_location ");
SqlStringBuilder.Append(" LEFT JOIN dbo.g_stock_order AS k ON k.order_no = b.order_no ");
SqlStringBuilder.Append(" LEFT JOIN dbo.g_workorder_seq AS l ON a.ruid=l.workorder_id ");
//lvf 20210629 加入时间过滤，避免数据量大影响效率
SqlStringBuilder.Append(" WHERE 1=1 ");
SqlStringBuilder.Append(" and RIGHT('00' + d.part_location, 2) IN ( ");
SqlStringBuilder.Append(" SELECT RIGHT('00' + col, 2) ");
SqlStringBuilder.Append(" FROM dbo.f_split(( SELECT ISNULL(view_part_location, '01,02') ");
SqlStringBuilder.Append(" FROM dbo.sys_terminal_control ");
SqlStringBuilder.Append(" WHERE terminal_id = @terminal_id ");
SqlStringBuilder.Append(" ), ',') ) ");
SqlStringBuilder.Append(" AND l.seq > ( SELECT wo_detail_seq ");
SqlStringBuilder.Append(" FROM dbo.sys_terminal_point ");
SqlStringBuilder.Append(" WHERE terminal_id = @terminal_id ");
SqlStringBuilder.Append(" ) ");
SqlStringBuilder.Append(" AND j.current_status NOT IN ('9','1','8') ");
SqlStringBuilder.Append(" AND j.work_flag <> 1 AND j.prod_type <> 'A' AND a.enabled = 'Y' ");
if ("100056|100057|100058|100059|100060|100061|100062|100063|100064|100059|100069".Contains(terminalID.ToString()))
{
}
else
{
SqlStringBuilder.Append(" AND appoint_pdline=(SELECT pdline_id FROM dbo.sys_terminal WHERE terminal_id=@terminal_id) ");
}
SqlStringBuilder.Append(" AND l.stage_id=(SELECT stage_id FROM dbo.sys_terminal WHERE terminal_id=@terminal_id) ");
SqlStringBuilder.Append(" ORDER BY l.seq ");
Dictionary<string, object> Params = new Dictionary<string, object>(1);
Params.Add("@terminal_id", terminalID);
return httpClient.GetEntityList(SqlStringBuilder.ToString(), Params);
}
}
/// <summary>
/// 查询已经做过的历史数据（最后2笔）
/// </summary>
/// <param name="terminalID">工站编号</param>
/// <param name="showABTest"
/// <returns>排序生产数据</returns>
public List<dynamic> GetHistoryOrderData(int terminalID)
{
lock (httpClient)
{
if (terminalID <= 0)
{
return null;
}
string sql = "SELECT param_value FROM dbo.sys_base WHERE param_name='SNCutCheck' AND enabled='Y' ";
object obj = httpClient.GetScalar(sql);
#region
//StringBuilder SqlStringBuilder = new StringBuilder(1024);
//SqlStringBuilder.Append("SELECT DISTINCT ");
//SqlStringBuilder.Append(" * ");
//SqlStringBuilder.Append("FROM ( SELECT TOP 4 ");
//SqlStringBuilder.Append(" a.ruid , ");
//SqlStringBuilder.Append(" a.seq , ");
//SqlStringBuilder.Append(" b.car_no , ");
//SqlStringBuilder.Append(" h.type_id , ");
//SqlStringBuilder.Append(" h.type_code , ");
//SqlStringBuilder.Append(" h.type_name , ");
//SqlStringBuilder.Append(" g.model_id , ");
//SqlStringBuilder.Append(" g.model_name , ");
//SqlStringBuilder.Append(" g.model_desc , ");
//SqlStringBuilder.Append(" i.enum_desc , ");
//SqlStringBuilder.Append(" d.part_spec , ");
//SqlStringBuilder.Append(" a.part_id , ");
//SqlStringBuilder.Append(" b.type AS wo_type , ");
////SqlStringBuilder.Append(" a.type AS wo_detail_type , ");
//SqlStringBuilder.Append(" CASE a.type WHEN 'S' THEN '订单生产' WHEN 'E' THEN '插单生产' ELSE '其他' END AS wo_detail_type, ");
//SqlStringBuilder.Append(" c.serial_number , ");
//SqlStringBuilder.Append(" a.create_ymd + ' ' + a.create_hms AS ctime , ");
//SqlStringBuilder.Append(" d.part_no , ");
//SqlStringBuilder.Append(" a.ruid AS wo_detail_id , ");
//SqlStringBuilder.Append(" 0 AS row_select , ");
//SqlStringBuilder.Append(" d.option10 , ");
//SqlStringBuilder.Append(" d.part_location , ");
//SqlStringBuilder.Append(" RIGHT(c.serial_number, 10) AS number ");
//SqlStringBuilder.Append(" FROM dbo.g_workorder_detail a ");
//SqlStringBuilder.Append(" LEFT JOIN dbo.g_workorder_sn c ON a.ruid = c.wo_detail_id ");
//SqlStringBuilder.Append(" LEFT JOIN dbo.g_sn_status j ON j.serial_number = c.serial_number ");
//SqlStringBuilder.Append(" LEFT JOIN dbo.g_sn_travel AS e ON e.serial_number = j.serial_number ");
//SqlStringBuilder.Append(" AND e.terminal_id = @terminal_id ");
//SqlStringBuilder.Append(" LEFT JOIN dbo.g_workorder b ON a.workorder_id = b.ruid ");
//SqlStringBuilder.Append(" LEFT JOIN dbo.sys_part d ON a.part_id = d.part_id ");
//SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model g ON j.model_id = g.model_id ");
//SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model_type h ON g.model_type_id = h.type_id ");
//SqlStringBuilder.Append(" LEFT JOIN dbo.sys_enum i ON i.enum_type = 'sys_part_location' ");
//SqlStringBuilder.Append(" AND i.enum_value = d.part_location ");
//SqlStringBuilder.Append(" LEFT JOIN dbo.g_stock_order AS k ON k.order_no = b.order_no ");
//SqlStringBuilder.Append(" WHERE RIGHT('00' + d.part_location, 2) IN ( ");
//SqlStringBuilder.Append(" SELECT RIGHT('00' + col, 2) ");
//SqlStringBuilder.Append(" FROM dbo.f_split(( SELECT ISNULL(view_part_location, ");
//SqlStringBuilder.Append(" '01,02') ");
//SqlStringBuilder.Append(" FROM dbo.sys_terminal_control ");
//SqlStringBuilder.Append(" WHERE terminal_id = @terminal_id ");
//SqlStringBuilder.Append(" ), ',') ) ");
//SqlStringBuilder.Append(" AND a.seq <= ( SELECT wo_detail_seq ");
//SqlStringBuilder.Append(" FROM dbo.sys_terminal_point ");
//SqlStringBuilder.Append(" WHERE terminal_id = @terminal_id ");
//SqlStringBuilder.Append(" ) ");
//SqlStringBuilder.Append(" AND a.enabled = 'Y' ");
//SqlStringBuilder.Append(" AND a.create_ymd > ( SELECT CONVERT(VARCHAR(10), GETDATE() ");
//SqlStringBuilder.Append(" - 30, 120) ");
//SqlStringBuilder.Append(" ) ");
//SqlStringBuilder.Append(" AND c.create_ymd > ( SELECT CONVERT(VARCHAR(10), GETDATE() ");
//SqlStringBuilder.Append(" - 30, 120) ");
//SqlStringBuilder.Append(" ) ");
//SqlStringBuilder.Append(" AND j.create_ymd > ( SELECT CONVERT(VARCHAR(10), GETDATE() ");
//SqlStringBuilder.Append(" - 30, 120) ");
//SqlStringBuilder.Append(" ) ");
//SqlStringBuilder.Append(" AND e.create_ymd >( SELECT CONVERT(VARCHAR(10), GETDATE() ");
//SqlStringBuilder.Append(" - 30, 120) ");
//SqlStringBuilder.Append(" ) ");
//SqlStringBuilder.Append(" AND b.create_ymd > ( SELECT CONVERT(VARCHAR(10), GETDATE() ");
//SqlStringBuilder.Append(" - 30, 120) ");
//SqlStringBuilder.Append(" ) ");
//SqlStringBuilder.Append(" ORDER BY e.out_process_time DESC ");
//SqlStringBuilder.Append(" ) AS aa ");
#endregion
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("SELECT DISTINCT ");
SqlStringBuilder.Append(" * ");
SqlStringBuilder.Append("FROM ( SELECT TOP 4 ");
SqlStringBuilder.Append(" a.ruid , ");
SqlStringBuilder.Append(" l.seq , ");
SqlStringBuilder.Append(" b.car_no , ");
SqlStringBuilder.Append(" h.type_id , ");
SqlStringBuilder.Append(" h.type_code , ");
SqlStringBuilder.Append(" h.type_name , ");
SqlStringBuilder.Append(" g.model_id , ");
SqlStringBuilder.Append(" g.model_name , ");
SqlStringBuilder.Append(" g.model_desc , ");
SqlStringBuilder.Append(" i.enum_desc , ");
SqlStringBuilder.Append(" d.part_spec , ");
SqlStringBuilder.Append(" a.part_id , ");
SqlStringBuilder.Append(" b.type AS wo_type , ");
//SqlStringBuilder.Append(" a.type AS wo_detail_type , ");
SqlStringBuilder.Append(" CASE a.type WHEN 'S' THEN '订单生产' WHEN 'E' THEN '紧急插单' WHEN 'C'THEN '试制' WHEN 'Z'THEN '试制插单' WHEN 'P'THEN '培训' WHEN 'Q'THEN '培训插单' WHEN 'X'THEN 'EDI生产' ELSE '其他' END AS wo_detail_type, ");
SqlStringBuilder.Append(" c.serial_number , ");
SqlStringBuilder.Append(" a.create_ymd + ' ' + a.create_hms AS ctime , ");
SqlStringBuilder.Append(" d.part_no , ");
SqlStringBuilder.Append(" a.ruid AS wo_detail_id , ");
SqlStringBuilder.Append(" 0 AS row_select , ");
SqlStringBuilder.Append(" d.option10 , ");
SqlStringBuilder.Append(" d.part_location , k.edi_id, ");
//余姚工厂使用 SUBSTRING(RIGHT(c.serial_number,21),1,12) AS number 其他工厂使用：RIGHT(c.serial_number,10) AS number
if (obj != null)
{
if (obj.ToString() == "Y")
{
SqlStringBuilder.Append("SUBSTRING(RIGHT(c.serial_number,21),1,12) AS number ");
}
else
{
SqlStringBuilder.Append(" RIGHT(c.serial_number,10) AS number ");
}
}
else
{
SqlStringBuilder.Append(" RIGHT(c.serial_number,10) AS number ");
}
SqlStringBuilder.Append(" FROM dbo.g_workorder_detail a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.g_workorder_sn c ON a.ruid = c.wo_detail_id ");
SqlStringBuilder.Append(" LEFT JOIN dbo.g_sn_status j ON j.serial_number = c.serial_number ");
SqlStringBuilder.Append(" LEFT JOIN dbo.g_sn_travel AS e ON e.serial_number = j.serial_number ");
SqlStringBuilder.Append(" AND e.terminal_id = @terminal_id ");
SqlStringBuilder.Append(" LEFT JOIN dbo.g_workorder b ON a.workorder_id = b.ruid ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_part d ON a.part_id = d.part_id ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model g ON j.model_id = g.model_id ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model_type h ON g.model_type_id = h.type_id ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_enum i ON i.enum_type = 'sys_part_location' ");
SqlStringBuilder.Append(" AND i.enum_value = d.part_location ");
SqlStringBuilder.Append(" LEFT JOIN dbo.g_stock_order AS k ON k.order_no = b.order_no ");
SqlStringBuilder.Append(" LEFT JOIN dbo.g_workorder_seq AS l ON a.ruid=l.workorder_id ");
SqlStringBuilder.Append(" WHERE a.create_ymd > GETDATE() -30 and RIGHT('00' + d.part_location, 2) IN ( ");
SqlStringBuilder.Append(" SELECT RIGHT('00' + col, 2) ");
SqlStringBuilder.Append(" FROM dbo.f_split(( SELECT ISNULL(view_part_location, ");
SqlStringBuilder.Append(" '01,02') ");
SqlStringBuilder.Append(" FROM dbo.sys_terminal_control ");
SqlStringBuilder.Append(" WHERE terminal_id = @terminal_id ");
SqlStringBuilder.Append(" ), ',') ) ");
SqlStringBuilder.Append(" AND l.seq <= ( SELECT wo_detail_seq ");
SqlStringBuilder.Append(" FROM dbo.sys_terminal_point ");
SqlStringBuilder.Append(" WHERE terminal_id = @terminal_id ");
SqlStringBuilder.Append(" ) ");
SqlStringBuilder.Append(" AND a.enabled = 'Y' AND j.prod_type <> 'A' ");
SqlStringBuilder.Append(" AND l.stage_id=(SELECT stage_id FROM dbo.sys_terminal WHERE terminal_id=@terminal_id) ");
SqlStringBuilder.Append(" AND a.create_ymd > ( SELECT CONVERT(VARCHAR(10), GETDATE() ");
SqlStringBuilder.Append(" - 30, 120) ");
SqlStringBuilder.Append(" ) ");
SqlStringBuilder.Append(" AND c.create_ymd > ( SELECT CONVERT(VARCHAR(10), GETDATE() ");
SqlStringBuilder.Append(" - 30, 120) ");
SqlStringBuilder.Append(" ) ");
SqlStringBuilder.Append(" AND j.create_ymd > ( SELECT CONVERT(VARCHAR(10), GETDATE() ");
SqlStringBuilder.Append(" - 30, 120) ");
SqlStringBuilder.Append(" ) ");
SqlStringBuilder.Append(" AND e.create_ymd >( SELECT CONVERT(VARCHAR(10), GETDATE() ");
SqlStringBuilder.Append(" - 30, 120) ");
SqlStringBuilder.Append(" ) ");
SqlStringBuilder.Append(" AND b.create_ymd > ( SELECT CONVERT(VARCHAR(10), GETDATE() ");
SqlStringBuilder.Append(" - 30, 120) ");
SqlStringBuilder.Append(" ) ");
SqlStringBuilder.Append(" ORDER BY e.out_process_time DESC ");
SqlStringBuilder.Append(" ) AS aa ");
Dictionary<string, object> Params = new Dictionary<string, object>(1);
Params.Add("@terminal_id", terminalID);
return httpClient.GetEntityList(SqlStringBuilder.ToString(), Params);
}
}
/// <summary>
/// 根据过程条码和工位编号查询此条码在当前工位需要执行的工步信息
/// </summary>
/// <param name="serialNumber">过程条码</param>
/// <param name="terminalID">工位编号</param>
/// <returns>工步信息</returns>
public List<dynamic> GetRouteStepBySNAndTerminal(string serialNumber, int terminalID)
{
lock (httpClient)
{
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("SELECT f.route_id, f.route_name, g.seq,g.next_seq,g.fail_seq, h.* ");
SqlStringBuilder.Append("FROM dbo.g_sn_status a ");
SqlStringBuilder.Append("LEFT JOIN dbo.sys_part b ON a.part_id = b.part_id ");
SqlStringBuilder.Append("LEFT JOIN dbo.mes_route_group_detail c ON b.part_no = c.part_no ");
SqlStringBuilder.Append("LEFT JOIN dbo.mes_part_route d ON c.group_id = d.group_id ");
SqlStringBuilder.Append("LEFT JOIN dbo.sys_terminal e ON d.process_id = e.process_id ");
SqlStringBuilder.Append("LEFT JOIN dbo.mes_route f ON d.route_id = f.route_id ");
SqlStringBuilder.Append("LEFT JOIN dbo.mes_route_step g ON f.route_id = g.route_id ");
SqlStringBuilder.Append("LEFT JOIN dbo.mes_step h ON g.step_id = h.step_id ");
SqlStringBuilder.Append("WHERE f.enabled='Y' AND g.enabled = 'Y'AND h.enabled = 'Y' AND a.serial_number = @serial_number ");
SqlStringBuilder.Append("AND e.terminal_id = @terminal_id ");
SqlStringBuilder.Append("ORDER BY g.seq ");
Dictionary<string, object> Params = new Dictionary<string, object>(2);
Params.Add("@serial_number", serialNumber);
Params.Add("@terminal_id", terminalID);
return httpClient.GetEntityList(SqlStringBuilder.ToString(), Params);
}
}
/// <summary>
/// 获取配置的参数
/// </summary>
/// <returns></returns>
public List<dynamic> GetStepParams()
{
lock (httpClient)
{
string sqlString = " SELECT param_name,param_value FROM dbo.mes_part_route_params WHERE enabled = 'Y' ";
return httpClient.GetEntityList(sqlString);
}
}
public int GetRouteTime(string serialNumber, int terminalID)
{
lock (httpClient)
{
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("SELECT d.ct FROM dbo.g_sn_status a ");
SqlStringBuilder.Append("LEFT JOIN dbo.sys_part b ON a.part_id = b.part_id ");
SqlStringBuilder.Append("LEFT JOIN dbo.mes_route_group_detail c ON b.part_no = c.part_no ");
SqlStringBuilder.Append("LEFT JOIN dbo.mes_part_route d ON c.group_id = d.group_id ");
SqlStringBuilder.Append("LEFT JOIN dbo.sys_terminal e ON d.process_id = e.process_id ");
SqlStringBuilder.Append("WHERE a.serial_number =@serial_number AND e.terminal_id =@terminal_id ");
Dictionary<string, object> Params = new Dictionary<string, object>(2);
Params.Add("@serial_number", serialNumber);
Params.Add("@terminal_id", terminalID);
object ob = httpClient.GetScalar(SqlStringBuilder.ToString(), Params);
if (ob != null)
return Convert.ToInt32(ob);
else
return 0;
}
}
public int GetJPHTime(int terminalID)
{
lock (httpClient)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("SELECT b.ct ");
querySen.AppendLine("FROM dbo.sys_terminal a ");
querySen.AppendLine(" LEFT JOIN dbo.sys_pdline b ON a.pdline_id = b.pdline_id ");
querySen.AppendLine("WHERE a.terminal_id = @terminal_id ");
Dictionary<string, object> Params = new Dictionary<string, object>(1);
Params.Add("@terminal_id", terminalID);
object ob = httpClient.GetScalar(querySen.ToString(), Params);
if (ob != null)
return Convert.ToInt32(ob);
else
return 0;
}
}
public bool InsertLogDetail(string serialNumber, int terminalID, int routeID, int stepID,
int seq, double waitTime, double actionTime, int userId)
{
lock (httpClient)
{
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("INSERT INTO dbo.mes_sn_route_log ( serial_number, ");
SqlStringBuilder.Append("terminal_id,route_id,step_id, seq, wait_time, action_time,create_user ) ");
SqlStringBuilder.Append(" VALUES(@serialNumber,@terminalID,@routeID,@stepID,@seq,@waitTime,@actionTime,@userID) ");
Dictionary<string, object> ht = new Dictionary<string, object>(10);
ht.Add("@serialNumber", serialNumber);
ht.Add("@terminalID", terminalID);
ht.Add("@routeID", routeID);
ht.Add("@stepID", stepID);
ht.Add("@seq", seq);
ht.Add("@waitTime", waitTime);
ht.Add("@actionTime", actionTime);
ht.Add("@userID", userId);
int count = httpClient.Execute(SqlStringBuilder.ToString(), ht);
return count > 0;
}
}
public bool UpdateTerminalPoint(string value, int Tid)
{
lock (httpClient)
{
string sql = string.Format("UPDATE sys_terminal_point SET wo_detail_seq = '{0}' WHERE terminal_id = {1}", value, Tid);
int rows = httpClient.Execute(sql);
if (rows > 0)
{
return true;
}
else
{
return false;
}
}
}
public List<dynamic> UserCOM(int TerminalID)
{
lock (httpClient)
{
string sql = "SELECT * FROM dbo.sys_terminal_params WHERE terminal_id = " + TerminalID + " AND param_name='UserSerialPortScanner'";
return httpClient.GetEntityList(sql);
}
}
public string getSeq(string sn)
{
lock (httpClient)
{
string sql = "SELECT wo_detail_id FROM dbo.g_workorder_sn WHERE serial_number = '" + sn + "'";
object obj = httpClient.GetScalar(sql);
if (obj != null)
return obj.ToString();
else
return "0";
}
}
public string stationType(string terminalID)
{
lock (httpClient)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("SELECT param_value ");
querySen.AppendLine("FROM dbo.sys_terminal_params ");
querySen.AppendLine("WHERE param_name = 'StationType' ");
querySen.AppendLine(" AND terminal_id = @terminalID ");
Dictionary<string, object> ht = new Dictionary<string, object>(1);
ht.Add("@terminalID", terminalID);
object obj = httpClient.GetScalar(querySen.ToString(), ht);
return obj != null ? obj.ToString().ToUpper() : string.Empty;
}
}
/// <summary>
/// 获取补打条码的密码
/// </summary>
/// <returns></returns>
public string GetSNReprintPassword()
{
lock (httpClient)
{
return httpClient.GetSysBase("SNReprintPWD");
}
}
/// <summary>
/// 获取的密码
/// </summary>
/// <returns></returns>
public string GetPwd(string PWD)
{
lock (httpClient)
{
return httpClient.GetSysBase("" + PWD + "");
}
}
/// <summary>
/// 获取下线工位跳工步的密码
/// </summary>
/// <returns></returns>
public string GetJupStepPassword(int terminalID)
{
lock (httpClient)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("SELECT unlock_pwd FROM dbo.sys_terminal_control WHERE terminal_id =@terminalID ");
Dictionary<string, object> ht = new Dictionary<string, object>(1);
ht.Add("@terminalID", terminalID);
object obj = httpClient.GetScalar(querySen.ToString(), ht);
return obj != null ? obj.ToString().ToUpper() : string.Empty;
}
}
/// <summary>
/// 获取补写RFID的密码
/// </summary>
/// <returns></returns>
public string GetRFIDRewrite()
{
return httpClient.GetSysBase("RFIDRewrite");
}
/// <summary>
/// 获取末尾的条码
/// </summary>
/// <param name="count"></param>
/// <returns></returns>
public List<dynamic> GetTailSN(int count, int terminalID, bool isOrderByInLineTime)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("SELECT TOP " + count.ToString() + " ");
querySen.AppendLine(" g.car_no , ");
querySen.AppendLine(" a.serial_number , ");
querySen.AppendLine(" f.csn , ");
querySen.AppendLine(" a.create_ymd , ");
querySen.AppendLine(" a.create_hms , ");
querySen.AppendLine(" f.prod_type , ");
querySen.AppendLine(" d.part_no , ");
querySen.AppendLine(" d.part_spec , ");
querySen.AppendLine(" m.enum_desc , ");
querySen.AppendLine(" d.part_location , ");
querySen.AppendLine(" e.model_code , ");
querySen.AppendLine(" e.model_type , ");
querySen.AppendLine(" a.printed , ");
querySen.AppendLine(" a.reprint_count ");
querySen.AppendLine("FROM g_workorder_sn a ");
querySen.AppendLine(" LEFT JOIN g_sn_status f ON a.serial_number = f.serial_number ");
querySen.AppendLine(" LEFT JOIN sys_part d ON a.part_id = d.part_id ");
querySen.AppendLine(" LEFT JOIN dbo.sys_enum m ON d.part_location=m.enum_value AND m.enum_type='sys_part_location' ");
querySen.AppendLine(" LEFT JOIN sys_model e ON f.model_id = e.model_id ");
querySen.AppendLine(" LEFT JOIN g_workorder g ON a.workorder = g.ruid ");
querySen.AppendLine(" LEFT JOIN dbo.sys_model_type h ON e.model_type_id = h.type_id ");
querySen.AppendLine(" LEFT JOIN dbo.g_pdline_relation i ON h.cust_pdline_id = i.cust_pdline_id ");
querySen.AppendLine(" LEFT JOIN dbo.sys_terminal j ON i.pdline_id = j.pdline_id ");
querySen.AppendLine("WHERE d.part_location IN ( ");
querySen.AppendLine(" SELECT col ");
querySen.AppendLine(" FROM dbo.f_split(( SELECT view_part_location ");
querySen.AppendLine(" FROM dbo.sys_terminal_control ");
querySen.AppendLine(" WHERE terminal_id = @terminal_id ");
querySen.AppendLine(" ), ',') ) ");
querySen.AppendLine(" AND j.terminal_id = @terminal_id ");
querySen.AppendLine("ORDER BY a.ruid DESC ");
Dictionary<string, object> Params = new Dictionary<string, object>(1);
Params.Add("@terminal_id", terminalID);
return httpClient.GetEntityList(querySen.ToString(), Params);
}
public List<dynamic> GetTailSN(int count, int terminalID, bool isOrderByInLineTime, string sn)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("SELECT TOP " + count.ToString() + " ");
querySen.AppendLine(" g.car_no , ");
querySen.AppendLine(" a.serial_number , ");
querySen.AppendLine(" f.csn , ");
querySen.AppendLine(" a.create_ymd , ");
querySen.AppendLine(" a.create_hms , ");
querySen.AppendLine(" f.prod_type , ");
querySen.AppendLine(" d.part_no , ");
querySen.AppendLine(" d.part_spec , ");
querySen.AppendLine(" m.enum_desc , ");
querySen.AppendLine(" d.part_location , ");
querySen.AppendLine(" e.model_code , ");
querySen.AppendLine(" e.model_type , ");
querySen.AppendLine(" a.printed , ");
querySen.AppendLine(" a.reprint_count ");
querySen.AppendLine("FROM g_workorder_sn a ");
querySen.AppendLine(" LEFT JOIN g_sn_status f ON a.serial_number = f.serial_number ");
querySen.AppendLine(" LEFT JOIN sys_part d ON a.part_id = d.part_id ");
querySen.AppendLine(" LEFT JOIN dbo.sys_enum m ON d.part_location=m.enum_value AND m.enum_type='sys_part_location' ");
querySen.AppendLine(" LEFT JOIN sys_model e ON f.model_id = e.model_id ");
querySen.AppendLine(" LEFT JOIN g_workorder g ON a.workorder = g.ruid ");
querySen.AppendLine(" LEFT JOIN dbo.sys_model_type h ON e.model_type_id = h.type_id ");
querySen.AppendLine(" LEFT JOIN dbo.g_pdline_relation i ON h.cust_pdline_id = i.cust_pdline_id ");
//querySen .AppendLine (" LEFT JOIN dbo.sys_terminal j ON i.pdline_id = j.pdline_id ");
querySen.AppendLine("WHERE d.part_location IN ( ");
querySen.AppendLine(" SELECT col ");
querySen.AppendLine(" FROM dbo.f_split(( SELECT view_part_location ");
querySen.AppendLine(" FROM dbo.sys_terminal_control ");
querySen.AppendLine(" WHERE terminal_id = @terminal_id ");
querySen.AppendLine(" ), ',') ) ");
//querySen .AppendLine (" AND j.terminal_id = @terminal_id ");
querySen.AppendLine(" AND a.serial_number=@sn ");
querySen.AppendLine("ORDER BY a.ruid DESC ");
Dictionary<string, object> Params = new Dictionary<string, object>(2);
Params.Add("@terminal_id", terminalID);
Params.Add("@sn", sn);
return httpClient.GetEntityList(querySen.ToString(), Params);
}
/// <summary>
/// 根据条码获取需要打印信息
/// </summary>
/// <param name="serialNumber">条码</param>
/// <returns>条码的打印信息</returns>
public dynamic GetSN(string serialNumber)
{
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("SELECT TOP 1 ");
SqlStringBuilder.Append(" a.serial_number , ");
SqlStringBuilder.Append(" a.create_ymd + ' ' + a.create_hms AS create_time , ");
SqlStringBuilder.Append(" RIGHT(b.order_no, 3) + '-' + RIGHT('00' + CONVERT(VARCHAR, b.slot_no), ");
SqlStringBuilder.Append(" 2) AS order_seq , ");
SqlStringBuilder.Append(" b.slot_no , ");
SqlStringBuilder.Append(" b.car_no , ");
SqlStringBuilder.Append(" c.part_no , ");
SqlStringBuilder.Append(" c.part_spec , ");
SqlStringBuilder.Append(" c.part_spec2 , ");
SqlStringBuilder.Append(" c.part_location , ");
SqlStringBuilder.Append(" c.cust_part_no ,c.part_class,c.label_type,c.part_no_3c, ");
SqlStringBuilder.Append(" e.model_code , ");
SqlStringBuilder.Append(" e.model_desc , ");
SqlStringBuilder.Append(" e.model_alias , ");
SqlStringBuilder.Append(" f.enum_desc , ");
SqlStringBuilder.Append(" f.enum_alias , ");
SqlStringBuilder.Append(" g.csn , ");
SqlStringBuilder.Append(" (CONVERT(varchar(100), GETDATE(), 12)) AS dates , ");
SqlStringBuilder.Append(" SUBSTRING(RIGHT(@sn,21),1,12) AS seq , ");
SqlStringBuilder.Append(" SUBSTRING(@sn,19,4) AS number_T ,");
SqlStringBuilder.Append(" SUBSTRING(@sn,11,2) AS part_T ,");
SqlStringBuilder.Append(" h.type_desc , ");
SqlStringBuilder.Append(" g.prod_type,e.model_name,h.type_name ");
SqlStringBuilder.Append("FROM dbo.g_workorder_sn a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.g_workorder_detail z ON a.wo_detail_id=z.ruid ");
SqlStringBuilder.Append(" LEFT JOIN dbo.g_workorder b ON z.workorder_id = b.ruid ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_part c ON a.part_id = c.part_id ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_part d ON b.part_id = d.part_id ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model e ON d.model_id = e.model_id ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_enum f ON c.part_location = f.enum_value ");
SqlStringBuilder.Append(" AND f.enum_type = 'sys_part_location' ");
SqlStringBuilder.Append(" LEFT JOIN dbo.g_sn_status g ON a.serial_number = g.serial_number ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model_type h ON e.model_type_id = h.type_id ");
SqlStringBuilder.Append(" WHERE a.serial_number = @sn ");
Dictionary<string, object> Params = new Dictionary<string, object>(1);
Params.Add("@sn", serialNumber);
var dt = httpClient.GetEntityList(SqlStringBuilder.ToString(), Params);
return dt[0];
}
/// <summary>
/// 根据条码获取对应后排条码的打印信息
/// </summary>
/// <param name="serialNumber">条码</param>
/// <returns>后排条码的打印信息</returns>
public dynamic GetBackSN(string serialNumber, int terminalID)
{
lock (httpClient)
{
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("SELECT ");
SqlStringBuilder.Append(" RIGHT(f.workorder,3) + '-' + CAST(f.slot_no AS VARCHAR) + '-' + f.car_no AS car_no, ");
SqlStringBuilder.Append(" a.serial_number , ");
SqlStringBuilder.Append(" RIGHT(f.workorder,3) + '-' + CAST(f.slot_no AS VARCHAR) AS order_seq , ");
SqlStringBuilder.Append(" a.create_ymd ,f.slot_no,d.cust_part_no,f.csn,e.model_desc , ");
SqlStringBuilder.Append(" e.model_alias , ");
SqlStringBuilder.Append(" a.create_hms ,a.create_ymd +' ' + a.create_hms as create_time, ");
SqlStringBuilder.Append(" f.prod_type, ");
SqlStringBuilder.Append(" d.part_no , d.part_class, ");
SqlStringBuilder.Append(" d.part_spec ,d.part_spec2, ");
SqlStringBuilder.Append(" d.part_location , ");
SqlStringBuilder.Append(" e.model_code , ");
SqlStringBuilder.Append(" e.model_type , ");
SqlStringBuilder.Append(" a.printed , ");
SqlStringBuilder.Append(" a.reprint_count,m.enum_desc ");
SqlStringBuilder.Append("FROM g_workorder_sn a ");
SqlStringBuilder.Append("left join g_sn_status f on a.serial_number=f.serial_number ");
SqlStringBuilder.Append("left join sys_part d on a.part_id=d.part_id ");
SqlStringBuilder.Append("left join sys_model e on f.model_id=e.model_id ");
SqlStringBuilder.Append("LEFT JOIN dbo.sys_enum m ON d.part_location = m.enum_value ");
SqlStringBuilder.Append(" AND m.enum_type = 'sys_part_location' ");
SqlStringBuilder.Append("WHERE ");
SqlStringBuilder.Append(" f.current_status=0 ");
SqlStringBuilder.Append(" AND d.part_location IN ( ");
SqlStringBuilder.Append(" SELECT col ");
SqlStringBuilder.Append(" FROM ");
SqlStringBuilder.Append(" dbo.f_split(( SELECT b.view_part_type ");
SqlStringBuilder.Append(" FROM dbo.sys_terminal_control a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.g_view_board b ON a.view_board_id = b.view_board_id ");
SqlStringBuilder.Append(" WHERE terminal_id =@terminal_id ");
SqlStringBuilder.Append(" ), ',') ) ");
SqlStringBuilder.Append(" and a.serial_number=@serial_number ");
SqlStringBuilder.Append(" order by a.ruid desc ");
Dictionary<string, object> Params = new Dictionary<string, object>(2);
Params.Add("@serial_number", serialNumber);
Params.Add("@terminal_id", terminalID);
var dt = httpClient.GetEntityList(SqlStringBuilder.ToString(), Params);
if (dt == null || dt.Count == 0)
return null;
return dt[0];
}
}
public bool UpdateOPCPointValue(string pointName, int terminal_id, string pointValue)
{
lock (httpClient)
{
string sqlstring = @"update opc_point set point_value =@pointValue,point_flag1=101,update_time=GETDATE() where point_name IN (
SELECT param_value FROM sys_terminal_params(NOLOCK) WHERE param_name=@pointName AND terminal_id=@terminID ) ";
Dictionary<string, object> values = new Dictionary<string, object>(3);
values.Add("@pointName", pointName);
values.Add("@terminID", terminal_id);
values.Add("@pointValue", pointValue);
if (httpClient.Execute(sqlstring.ToString(), values) > 0)
{
return true;
}
else
{
return false;
}
}
}
public bool UpdateOPCPointRobot(string pointValue, string pointName)
{
lock (httpClient)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("UPDATE dbo.opc_point ");
querySen.AppendLine("SET point_value = @pointValue , ");
querySen.AppendLine(" point_flag1 = '101' ");
querySen.AppendLine("WHERE point_name = @pointName ");
Dictionary<string, object> ht = new Dictionary<string, object>(2);
ht.Add("@pointValue", pointValue);
ht.Add("@pointName", pointName);
int rows = httpClient.Execute(querySen.ToString(), ht);
if (rows > 0)
{
return true;
}
else
{
return false;
}
}
}
public bool UpdateSNReprintStatus(string sn)
{
lock (httpClient)
{
string SqlString = "UPDATE dbo.g_workorder_sn SET reprint_count =reprint_count+1,update_userid=@userid, update_ymd=dbo.get_ymd(),update_hms=dbo.get_hms() WHERE serial_number=@sn ";
Dictionary<string, object> Params = new Dictionary<string, object>(2);
Params.Add("@sn", sn);
Params.Add("@userid", userId);
return httpClient.Execute(SqlString, Params) == 1;
}
}
public bool insertSNReprint(string sn, int terminalID)
{
lock (httpClient)
{
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("INSERT INTO dbo.g_sn_print_detail ");
SqlStringBuilder.Append(" ( serial_number , ");
SqlStringBuilder.Append(" terminal_name , ");
SqlStringBuilder.Append(" create_userid , ");
SqlStringBuilder.Append(" create_ymd , ");
SqlStringBuilder.Append(" create_hms ");
SqlStringBuilder.Append(" ) ");
SqlStringBuilder.Append("VALUES ( @sn , ");
SqlStringBuilder.Append(" @terminalID , ");
SqlStringBuilder.Append(" @userid , ");
SqlStringBuilder.Append(" dbo.get_ymd() , ");
SqlStringBuilder.Append(" dbo.get_hms() ");
SqlStringBuilder.Append(" ) ");
Dictionary<string, object> Params = new Dictionary<string, object>(3);
Params.Add("@sn", sn);
Params.Add("@terminalID", terminalID);
Params.Add("@userid", userId);
return httpClient.Execute(SqlStringBuilder.ToString(), Params) == 1;
}
}
public string GetParamValue(int terminalID, string paramName)
{
lock (httpClient)
{
string sqlstring = "SELECT param_value FROM dbo.sys_terminal_params WHERE terminal_id = @terminalID AND param_name = @paramName";
Dictionary<string, object> values = new Dictionary<string, object>(2);
values.Add("@terminalID", terminalID);
values.Add("@paramName", paramName);
object obj = httpClient.GetScalar(sqlstring.ToString(), values);
return obj != null && obj != DBNull.Value ? obj.ToString() : string.Empty;
}
}
public List<dynamic> GetTorqurIP(int terminalID, string paramName)
{
lock (httpClient)
{
string sqlstring = "SELECT param_value FROM dbo.sys_terminal_params WHERE terminal_id = @terminalID AND param_name = @paramName";
Dictionary<string, object> values = new Dictionary<string, object>(2);
values.Add("@terminalID", terminalID);
values.Add("@paramName", paramName);
return httpClient.GetEntityList(sqlstring.ToString(), values);
}
}
public List<dynamic> GetRFIDIP(int terminalID, string paramName)
{
lock (httpClient)
{
string sqlstring = "SELECT top 1 param_value FROM dbo.sys_terminal_params WHERE terminal_id = @terminalID AND param_name = @paramName";
Dictionary<string, object> values = new Dictionary<string, object>(2);
values.Add("@terminalID", terminalID);
values.Add("@paramName", paramName);
return httpClient.GetEntityList(sqlstring.ToString(), values);
}
}
public List<dynamic> GetRFIDOperationType(int terminalID, string paramName)
{
lock (httpClient)
{
string sqlstring = "SELECT top 1 operation_type FROM dbo.sys_terminal_params WHERE terminal_id = @terminalID AND param_name = @paramName";
Dictionary<string, object> values = new Dictionary<string, object>(2);
values.Add("@terminalID", terminalID);
values.Add("@paramName", paramName);
return httpClient.GetEntityList(sqlstring.ToString(), values);
}
}
public string GetGroupNO(int partID)
{
lock (httpClient)
{
string sqlstring = "SELECT torque_job_no+torque_robot_no+robot_offline_no FROM dbo.sys_part_ext_group WHERE part_id = @partID ";
Dictionary<string, object> values = new Dictionary<string, object>(1);
values.Add("@partID", partID);
object obj = httpClient.GetScalar(sqlstring.ToString(), values);
return obj != null && obj != DBNull.Value ? obj.ToString() : string.Empty;
}
}
public string getSNStatus(string serial_number)
{
lock (httpClient)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("SELECT work_flag ");
querySen.AppendLine("FROM dbo.g_sn_status ");
querySen.AppendLine("WHERE serial_number = @serial_number ");
Dictionary<string, object> ht = new Dictionary<string, object>(1);
ht.Add("@serial_number", serial_number);
object obj = httpClient.GetScalar(querySen.ToString(), ht);
return obj != null && obj != DBNull.Value ? obj.ToString() : string.Empty;
}
}
public bool InsertSNTravel(string sn)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("insert into g_sn_travel select * from g_sn_status where serial_number=@sn ");
Dictionary<string, object> ht = new Dictionary<string, object>(1);
ht.Add("@sn", sn);
try
{
httpClient.Execute(querySen.ToString(), ht);
}
catch
{
return false;
}
return true;
}
public bool InsertUnlock(string terminalID, string sn)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("INSERT INTO g_user_unlock (serial_number,terminal_id,create_userid)VALUES(@serial_number,@terminal_id,@userid ) ");
Dictionary<string, object> ht = new Dictionary<string, object>(3);
ht.Add("@terminal_id", terminalID);
ht.Add("@serial_number", sn);
ht.Add("@userid", userId);
try
{
httpClient.Execute(querySen.ToString(), ht);
}
catch
{
return false;
}
return true;
}
public bool InsertUnlock(string terminalID, string sn, string remark)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("INSERT INTO g_user_unlock (serial_number,terminal_id,remark,create_userid)VALUES(@serial_number,@terminal_id,@remark,@userid) ");
Dictionary<string, object> ht = new Dictionary<string, object>(3);
ht.Add("@terminal_id", terminalID);
ht.Add("@serial_number", sn);
ht.Add("@remark", remark);
ht.Add("@userid", userId);
try
{
httpClient.Execute(querySen.ToString(), ht);
}
catch
{
return false;
}
return true;
}
public bool UpdateSNTerminal(int terminalid, string sn)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("UPDATE dbo.g_sn_status SET terminal_id = @terminal_id,out_process_time =GETDATE(),update_userid=@userid WHERE serial_number = @serial_number ");
Dictionary<string, object> ht = new Dictionary<string, object>(3);
ht.Add("@terminal_id", terminalid);
ht.Add("@serial_number", sn);
ht.Add("@userid", userId);
try
{
httpClient.Execute(querySen.ToString(), ht);
}
catch
{
return false;
}
return true;
}
public bool UpdateSNoutProcessTime(string sn)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("UPDATE dbo.g_sn_status SET out_process_time =GETDATE(),update_userid=@userid WHERE serial_number = @serial_number ");
Dictionary<string, object> ht = new Dictionary<string, object>(2);
ht.Add("@serial_number", sn);
ht.Add("@userid", userId);
try
{
httpClient.Execute(querySen.ToString(), ht);
}
catch
{
return false;
}
return true;
}
public bool UpdateSNinProcessTime(string sn)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("UPDATE dbo.g_sn_status SET in_process_time =GETDATE(),update_userid=@userid WHERE serial_number = @serial_number ");
Dictionary<string, object> ht = new Dictionary<string, object>(2);
ht.Add("@serial_number", sn);
ht.Add("@userid", userId);
try
{
httpClient.Execute(querySen.ToString(), ht);
}
catch
{
return false;
}
return true;
}
//恢复条码状态
public bool updateOnLineStatus(string serial_number)
{
lock (httpClient)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("UPDATE dbo.g_sn_status ");
querySen.AppendLine("SET work_flag = -1 , ");
querySen.AppendLine(" update_userid=@userid , ");
querySen.AppendLine(" in_pdline_time = GETDATE() ");
querySen.AppendLine("WHERE serial_number = @serial_number ");
Dictionary<string, object> ht = new Dictionary<string, object>(2);
ht.Add("@serial_number", serial_number);
ht.Add("@userid", userId);
return httpClient.Execute(querySen.ToString(), ht) > 0;
}
}
/// <summary>
/// 获取发送给机器人的最后一个条码
/// </summary>
/// <param name="terminalId"></param>
/// <returns></returns>
public string GetSendRobotLastSN(string terminalID)
{
lock (httpClient)
{
string SqlString = "SELECT param_value FROM dbo.sys_base WHERE param_name=@terminalID+'_SendRobotLastSN'";
Dictionary<string, object> Params = new Dictionary<string, object>(1);
Params.Add("@terminalID", terminalID);
object obj = httpClient.GetScalar(SqlString, Params);
return obj != null && obj != DBNull.Value ? obj.ToString() : string.Empty;
}
}
/// <summary>
/// 更新发送给机器人的条码
/// </summary>
/// <param name="sn"></param>
/// <param name="terminalID"></param>
/// <returns></returns>
public bool UpdateSendRobotLastSN(string sn, string terminalID)
{
lock (httpClient)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("UPDATE dbo.sys_base SET param_value=@sn WHERE param_name=@terminalID+'_SendRobotLastSN' ");
Dictionary<string, object> ht = new Dictionary<string, object>(2);
ht.Add("@sn", sn);
ht.Add("@terminalID", terminalID);
return httpClient.Execute(querySen.ToString(), ht) > 0;
}
}
public string prodPass()
{
lock (httpClient)
{
string sql = "SELECT param_value FROM dbo.sys_base WHERE param_name='sys_prod_pass'";
object obj = httpClient.GetScalar(sql);
return obj != null && obj != DBNull.Value ? obj.ToString() : string.Empty;
}
}
public string isExec(string flag)
{
lock (httpClient)
{
string sql = "SELECT param_value FROM dbo.sys_base WHERE param_name='" + flag + "' and enabled='Y'";
object obj = httpClient.GetScalar(sql);
return obj != null && obj != DBNull.Value ? obj.ToString() : string.Empty;
}
}
public bool IsSN(string input)
{
lock (httpClient)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("SELECT * ");
querySen.AppendLine("FROM dbo.g_sn_status(NOLOCK) ");
querySen.AppendLine("WHERE serial_number = @input ");
Dictionary<string, object> ht = new Dictionary<string, object>(1);
ht.Add("@input", input);
var dtTemp = httpClient.GetEntityList(querySen.ToString(), ht);
return dtTemp == null || dtTemp.Count == 0 ? false : true;
}
}
public List<dynamic> snDetail(string input)
{
lock (httpClient)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("SELECT c.serial_number , ");
querySen.AppendLine(" a.ruid , ");
querySen.AppendLine(" a.seq , ");
querySen.AppendLine(" b.car_no , ");
querySen.AppendLine(" h.type_id , ");
querySen.AppendLine(" h.type_code , ");
querySen.AppendLine(" h.type_name , ");
querySen.AppendLine(" g.model_id , ");
querySen.AppendLine(" g.model_name , ");
querySen.AppendLine(" g.model_desc , ");
querySen.AppendLine(" i.enum_desc , ");
querySen.AppendLine(" d.part_spec , ");
querySen.AppendLine(" a.part_id , ");
querySen.AppendLine(" b.type AS wo_type , ");
querySen.AppendLine(" a.type AS wo_detail_type , ");
querySen.AppendLine(" a.create_ymd + ' ' + a.create_hms AS ctime , ");
querySen.AppendLine(" d.part_no , ");
querySen.AppendLine(" a.ruid AS wo_detail_id , ");
querySen.AppendLine(" 0 AS row_select , ");
querySen.AppendLine(" d.option10 ");
querySen.AppendLine("FROM dbo.g_workorder_detail a ");
querySen.AppendLine(" LEFT JOIN dbo.g_workorder b ON a.workorder_id = b.ruid ");
querySen.AppendLine(" LEFT JOIN dbo.g_workorder_sn c ON a.ruid = c.wo_detail_id ");
querySen.AppendLine(" LEFT JOIN dbo.sys_part d ON a.part_id = d.part_id ");
querySen.AppendLine(" LEFT JOIN dbo.sys_part f ON b.part_id = f.part_id ");
querySen.AppendLine(" LEFT JOIN dbo.sys_model g ON f.model_id = g.model_id ");
querySen.AppendLine(" LEFT JOIN dbo.sys_model_type h ON g.model_type_id = h.type_id ");
querySen.AppendLine(" LEFT JOIN dbo.sys_enum i ON i.enum_type = 'sys_part_location' ");
querySen.AppendLine(" LEFT JOIN dbo.g_sn_status j ON j.serial_number = c.serial_number ");
querySen.AppendLine(" AND d.part_location = i.enum_value ");
querySen.AppendLine("WHERE j.serial_number=@input ");
Dictionary<string, object> ht = new Dictionary<string, object>(1);
ht.Add("@input", input);
return httpClient.GetEntityList(querySen.ToString(), ht);
}
}
public bool UpdateCurrentStatus(string serial_number)
{
StringBuilder SqlStringBuilder = new StringBuilder(1024);
// 标记为不良品
SqlStringBuilder.Append("UPDATE g_sn_status ");
SqlStringBuilder.Append("SET current_status = '1' ");
SqlStringBuilder.Append(" ,update_ymd = CONVERT(VARCHAR(10),GETDATE(),120) ");
SqlStringBuilder.Append(" ,update_hms = CONVERT(VARCHAR(10),GETDATE(),108) ");
SqlStringBuilder.Append(" ,update_userid = @userid ");
SqlStringBuilder.Append(" ,in_process_time = GETDATE() ");
SqlStringBuilder.Append("WHERE current_status = '0' ");
SqlStringBuilder.Append(" AND (serial_number = @serial_number); ");
Dictionary<string, object> Params = new Dictionary<string, object>(2);
Params.Add("@serial_number", serial_number);
Params.Add("@userid", userId);
bool isUpdated = httpClient.Execute(SqlStringBuilder.ToString(), Params) > 0;
return isUpdated;
}
public bool instertFailItem(string sn, string operation, string no, string name, string result)
{
string sql = "INSERT INTO g_repair (serial_number,operation,seq,name,result)VALUES(@sn,@operation,@no,@name,@result)";
Dictionary<string, object> ht = new Dictionary<string, object>(5);
ht.Add("@sn", sn);
ht.Add("@operation", operation);
ht.Add("@no", no);
ht.Add("@name", name);
ht.Add("@result", result);
return httpClient.Execute(sql, ht) > 0;
}
public bool updateStatus(string serial_number)
{
lock (httpClient)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("UPDATE dbo.g_sn_status ");
querySen.AppendLine("SET work_flag = 1 , ");
querySen.AppendLine(" update_userid = @userid , ");
querySen.AppendLine(" out_pdline_time = GETDATE() ");
querySen.AppendLine("WHERE serial_number = @serial_number ");
Dictionary<string, object> ht = new Dictionary<string, object>(2);
ht.Add("@serial_number", serial_number);
ht.Add("@userid", userId);
return httpClient.Execute(querySen.ToString(), ht) > 0;
}
}
public bool updataProdCount(string sn, int terminalID)
{
string sql = @"EXEC dbo.sys_prod_count @sn,@terminalID";
Dictionary<string, object> ht = new Dictionary<string, object>(2);
ht.Add("@sn", sn);
ht.Add("@terminalID", terminalID);
return httpClient.Execute(sql, ht) > 0;
}
/// <summary>
/// 把条码标记为不良品
/// </summary>
/// <param name="serialNumber">过程条码</param>
/// <returns>执行结果</returns>
public bool SetSNStatusNG(string serialNumber, int terminal_id)
{
string SqlString = "update dbo.g_sn_status set current_status=1,update_userid=@userid where serial_number=@serial_number";
Dictionary<string, object> Params = new Dictionary<string, object>(2);
Params.Add("@serial_number", serialNumber);
Params.Add("@userid", userId);
return httpClient.Execute(SqlString, Params) == 1;
}
/// <summary>
/// 更新Seq
/// </summary>
/// <param name="ruid"></param>
/// <returns></returns>
public bool UpdateViewBoard(string serialNumber, int terminal_id)
{
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("UPDATE dbo.sys_terminal_point ");
SqlStringBuilder.Append("SET wo_detail_seq = ( SELECT TOP 1 ");
SqlStringBuilder.Append(" b.seq ");
SqlStringBuilder.Append(" FROM dbo.g_workorder_sn AS a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.g_workorder_seq AS b ON a.wo_detail_id = b.workorder_id ");
SqlStringBuilder.Append(" WHERE a.serial_number =@serialNumber ");
SqlStringBuilder.Append(" ) ");
SqlStringBuilder.Append("WHERE terminal_id = @terminal_id ");
Dictionary<string, object> Params = new Dictionary<string, object>(1);
Params.Add("@terminal_id", terminal_id);
Params.Add("@serialNumber", serialNumber);
return httpClient.Execute(SqlStringBuilder.ToString(), Params) == 1;
}
/// <summary>
/// 查询打印过程条码需要信息
/// </summary>
/// <param name="serialNumber"></param>
/// <returns></returns>
public List<dynamic> GetPrintSN2(string serialNumber)
{
lock (httpClient)
{
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("SELECT TOP 1 ");
SqlStringBuilder.Append(" a.serial_number as f10, ");
SqlStringBuilder.Append(" a.serial_number as f1 , ");
SqlStringBuilder.Append(" a.create_ymd + ' ' + a.create_hms AS create_time , ");
SqlStringBuilder.Append(" RIGHT(b.order_no, 3) + '-' + RIGHT('00' + CONVERT(VARCHAR, b.slot_no), ");
SqlStringBuilder.Append(" 2) AS order_seq , ");
SqlStringBuilder.Append(" b.slot_no , ");
SqlStringBuilder.Append(" b.car_no as f7 , ");
SqlStringBuilder.Append(" c.part_no , ");
SqlStringBuilder.Append(" c.part_spec as f4 , ");
SqlStringBuilder.Append(" c.part_spec2 , ");
SqlStringBuilder.Append(" c.part_location , ");
SqlStringBuilder.Append(" c.cust_part_no ,c.part_class,c.cust_part_no + c.part_class as f8, c.label_type,c.part_no_3c as f5, ");
SqlStringBuilder.Append(" e.model_code , ");
SqlStringBuilder.Append(" e.model_desc , ");
SqlStringBuilder.Append(" e.model_alias , ");
SqlStringBuilder.Append(" f.enum_desc as f6 , ");
SqlStringBuilder.Append(" g.csn , ");
SqlStringBuilder.Append(" g.prod_type,e.model_name as f3,h.type_name as f2, ");
SqlStringBuilder.Append(" (CONVERT([varchar](10),getdate(),(120))) AS ymd, ");
SqlStringBuilder.Append(" (CONVERT([varchar](10),getdate(),(108))) AS hms ");
SqlStringBuilder.Append("FROM dbo.g_workorder_sn a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.g_workorder_detail z ON a.wo_detail_id=z.ruid ");
SqlStringBuilder.Append(" LEFT JOIN dbo.g_workorder b ON z.workorder_id = b.ruid ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_part c ON a.part_id = c.part_id ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_part d ON b.part_id = d.part_id ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model e ON d.model_id = e.model_id ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_enum f ON c.part_location = f.enum_value ");
SqlStringBuilder.Append(" AND f.enum_type = 'sys_part_location' ");
SqlStringBuilder.Append(" LEFT JOIN dbo.g_sn_status g ON a.serial_number = g.serial_number ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model_type h ON e.model_type_id = h.type_id ");
SqlStringBuilder.Append(" WHERE a.serial_number = @sn ");
Dictionary<string, object> Params = new Dictionary<string, object>(1);
Params.Add("@sn", serialNumber);
var dt = httpClient.GetEntityList(SqlStringBuilder.ToString(), Params);
return dt;
}
}
/// <summary>
/// 查询打印过程条码需要信息RE
/// </summary>
/// <param name="serialNumber"></param>
/// <returns></returns>
public List<dynamic> GetPrintSNRE(string serialNumber)
{
lock (httpClient)
{
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("SELECT TOP 1 ");
SqlStringBuilder.Append(" a.serial_number as f10, ");
SqlStringBuilder.Append(" a.serial_number as f1 , ");
SqlStringBuilder.Append(" dbo.get_ymd() + ' ' +dbo.get_hms() AS create_time , ");
SqlStringBuilder.Append(" RIGHT(b.order_no, 3) + '-' + RIGHT('00' + CONVERT(VARCHAR, b.slot_no), ");
SqlStringBuilder.Append(" 2) AS order_seq , ");
SqlStringBuilder.Append(" b.slot_no , ");
SqlStringBuilder.Append(" b.car_no as f7 , ");
SqlStringBuilder.Append(" c.part_no ,c.vendor_part_no,c.mfger_part_no,c.subboard_qty, ");
SqlStringBuilder.Append(" c.part_spec as f4 , ");
SqlStringBuilder.Append(" c.part_spec2 , ");
SqlStringBuilder.Append(" c.part_location , ");
SqlStringBuilder.Append(" c.cust_part_no ,c.part_class,c.cust_part_no + c.part_class as f8, c.label_type,c.part_no_3c as f5, ");
SqlStringBuilder.Append(" e.model_code , ");
SqlStringBuilder.Append(" e.model_desc , ");
SqlStringBuilder.Append(" SUBSTRING(e.model_alias,1,1) AS model_alias , ");
SqlStringBuilder.Append(" f.enum_desc as f6 , ");
SqlStringBuilder.Append(" g.csn , ");
SqlStringBuilder.Append(" g.prod_type,e.model_name as f3,h.type_name as f2, ");
SqlStringBuilder.Append(" SUBSTRING(@sn,19,4) AS number_T ,");
SqlStringBuilder.Append(" SUBSTRING(@sn,11,2) AS part_T ,");
SqlStringBuilder.Append(" CONVERT(VARCHAR,GETDATE(),23)+' '+SUBSTRING(CONVERT(VARCHAR,GETDATE(),24),1,2) AS ymd, ");
SqlStringBuilder.Append(" (CONVERT([varchar](10),getdate(),(108))) AS hms,c.part_id,'' AS part_no_m1,'' AS part_no_m2,'' AS vendor_name ");
SqlStringBuilder.Append("FROM dbo.g_workorder_sn a ");
SqlStringBuilder.Append(" LEFT JOIN dbo.g_workorder_detail z ON a.wo_detail_id=z.ruid ");
SqlStringBuilder.Append(" LEFT JOIN dbo.g_workorder b ON z.workorder_id = b.ruid ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_part c ON a.part_id = c.part_id ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_part d ON b.part_id = d.part_id ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model e ON d.model_id = e.model_id ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_enum f ON c.part_location = f.enum_value ");
SqlStringBuilder.Append(" AND f.enum_type = 'sys_part_location' ");
SqlStringBuilder.Append(" LEFT JOIN dbo.g_sn_status g ON a.serial_number = g.serial_number ");
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_model_type h ON e.model_type_id = h.type_id ");
SqlStringBuilder.Append(" LEFT JOIN dbo.g_stock_order j ON g.workorder = j.order_no ");
SqlStringBuilder.Append(" WHERE a.serial_number = @sn ");
Dictionary<string, object> Params = new Dictionary<string, object>(1);
Params.Add("@sn", serialNumber);
var dt = httpClient.GetEntityList(SqlStringBuilder.ToString(), Params);
//添加当前总成的头枕零件号和头枕供应商名称
if (dt.Count > 0)
{
string sql = @" SELECT DISTINCT(e.part_no) AS part_no,v.vendor_name
FROM sys_bom a,
sys_bom_detail b
LEFT JOIN sys_process c
ON b.process_id = c.process_id,
sys_part d,
sys_part e LEFT JOIN dbo.sys_vendor v ON e.vendor_id=v.vendor_code
WHERE a.part_id = d.part_id
AND b.item_part_id = e.part_id
AND a.bom_id = b.bom_id
AND d.part_id = '" + dt[0]["part_id"].ToString() + @"'
AND a.bom_type = '回冲'
AND a.enabled = 'Y'
AND e.part_spec LIKE '%头枕总成%'
ORDER BY e.part_no;";
var dt2 = httpClient.GetEntityList(sql);
if (dt2.Count == 1)
{
dt[0]["part_no_m1"] = dt2[0]["part_no"].ToString();
dt[0]["vendor_name"] = dt2[0]["vendor_name"].ToString();
}
if (dt2.Count == 2)
{
dt[0]["part_no_m1"] = dt2[0]["part_no"].ToString();
dt[0]["part_no_m2"] = dt2[1]["part_no"].ToString();
dt[0]["vendor_name"] = dt2[0]["vendor_name"].ToString();
}
}
return dt;
}
}
/// <summary>
/// 查询打印过程条码需要信息
/// </summary>
/// <param name="serialNumber"></param>
/// <returns></returns>
public List<dynamic> getSerialNumberData(string serialNumber)
{
lock (httpClient)
{
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append(" SELECT top 1 * FROM dbo.g_sn_status WHERE serial_number=@sn ");
Dictionary<string, object> Params = new Dictionary<string, object>(1);
Params.Add("@sn", serialNumber);
var dt = httpClient.GetEntityList(SqlStringBuilder.ToString(), Params);
return dt;
}
}
/// <summary>
/// 获取初始区段,工位
/// </summary>
/// <param name="terminalId"></param>
/// <returns>dt</returns>
public List<dynamic> GetStgTerminal(int terminalID)
{
lock (httpClient)
{
string SqlString = @"SELECT stage_name,terminal_name FROM dbo.sys_stage a LEFT JOIN dbo.sys_terminal b ON a.stage_id=b.stage_id
WHERE terminal_id =@terminalID ";
Dictionary<string, object> Params = new Dictionary<string, object>(1);
Params.Add("@terminalID", terminalID);
var dt = httpClient.GetEntityList(SqlString.ToString(), Params);
return dt;
}
}
/// <summary>
/// 获取区段
/// </summary>
/// <param name="terminalId"></param>
/// <returns>dt</returns>
public List<dynamic> GetState()
{
lock (httpClient)
{
string SqlString = @"SELECT stage_id,stage_name FROM sys_stage WHERE enabled='Y' ";
var dt = httpClient.GetEntityList(SqlString.ToString());
return dt;
}
}
/// <summary>
/// 获取工位
/// </summary>
/// <param name="terminalId"></param>
/// <returns>dt</returns>
public List<dynamic> GetTerminal(string stageid)
{
lock (httpClient)
{
string SqlString = @"SELECT terminal_id, terminal_name FROM dbo.sys_terminal a LEFT JOIN dbo.sys_stage b ON a.stage_id=b.stage_id
WHERE b.stage_id=@stageid ";
Dictionary<string, object> Params = new Dictionary<string, object>(1);
Params.Add("@stageid", stageid);
var dt = httpClient.GetEntityList(SqlString.ToString(), Params);
return dt;
}
}
/// <summary>
/// 用户登录
/// </summary>
/// <param name="userID">用户ID</param>
/// <param name="pwd">用户密码（明文）</param>
/// <param name="factoryId">员工所属厂别ID</param>
/// <returns>是否登录成功</returns>
public bool Logine(string userID, string pwd)
{
string SqlString = "SELECT 1 FROM sys_emp WHERE emp_no=@UserNo AND passwd=@Password AND enabled='Y'";
Dictionary<string, object> Params = new Dictionary<string, object>();
Params.Add("@UserNo", userID);
Params.Add("@Password", MD5(pwd));
if (httpClient.GetScalar(SqlString, Params) != null)
return true;
return false;
}
/// <summary>
/// 用户登录校验
/// </summary>
public List<dynamic> LoginCheck(string userNO, int terminal_id)
{
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("SELECT a.emp_id,a.emp_no,b.emp_name FROM dbo.sys_emp_matrix a ");
SqlStringBuilder.Append("LEFT JOIN dbo.sys_emp b ON a.emp_id=b.emp_id ");
SqlStringBuilder.Append("WHERE a.terminal_id=@terminal_id AND a.emp_no=@UserNo ");
Dictionary<string,object> Params = new Dictionary<string, object>();
Params.Add("@UserNo", userNO);
Params.Add("@terminal_id", terminal_id);
return httpClient.GetEntityList(SqlStringBuilder.ToString(), Params);
}
/// <summary>
/// 用户根据技能矩阵获取工位
/// </summary>
public List<dynamic> GetUserTerminal(string stageId)
{
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("SELECT c.terminal_id, c.terminal_name FROM dbo.sys_emp_matrix a ");
SqlStringBuilder.Append("LEFT JOIN dbo.sys_emp b ON a.emp_id=b.emp_id ");
SqlStringBuilder.Append("LEFT JOIN dbo.sys_terminal c ON a.terminal_id = c.terminal_id ");
SqlStringBuilder.Append("LEFT JOIN dbo.sys_stage d ON d.stage_id=c.stage_id ");
SqlStringBuilder.Append("WHERE a.emp_no=@userNo AND d.stage_id=@stageId ");
Dictionary<string, object> Params = new Dictionary<string, object>();
Params.Add("@userNo", userNo);
Params.Add("@stageId", stageId);
return httpClient.GetEntityList(SqlStringBuilder.ToString(), Params);
}
/// <summary>
/// 获取登录ID,登录名
/// </summary>
/// <param name="userNo"></param>
/// <returns></returns>
public List<dynamic> GetUserInfo(string userNo)
{
string sql = "SELECT TOP 1 emp_id,emp_name,emp_no FROM sys_emp WHERE emp_no=@empNo";
Dictionary<string, object> param = new Dictionary<string, object>();
param.Add("@empNo", userNo);
return httpClient.GetEntityList(sql, param);
}
/// <summary>
/// 是否启用上线工位条码写入RFID
/// </summary>
/// <param name="serialNumber">过程条码</param>
/// <returns>执行结果</returns>
public bool UpWriteRFID(int terminalID, int stepID, string enaden)
{
string SqlString = "UPDATE dbo.mes_route_step SET enabled=@enaden WHERE route_id=@terminalID AND step_id=@stepID ";
Dictionary<string, object> Params = new Dictionary<string, object>(3);
Params.Add("@terminalID", terminalID);
Params.Add("@stepID", stepID);
Params.Add("@enaden", enaden);
return httpClient.Execute(SqlString, Params) == 1;
}
#region MD5加密
/// <summary>
/// MD5 加密
/// </summary>
/// <param name="number">明文</param>
/// <returns>密文</returns>
private string MD5(string number)
{
ASCIIEncoding ASCIIenc = new ASCIIEncoding();
string strReturn = "";
byte[] ByteSourceText = ASCIIenc.GetBytes(number);
MD5CryptoServiceProvider Md5Hash = new MD5CryptoServiceProvider();
byte[] ByteHash = Md5Hash.ComputeHash(ByteSourceText);
foreach (byte b in ByteHash)
{
strReturn += b.ToString("x2");
}
return strReturn;
}
#endregion
/// <summary>
/// 获取扫描时间
/// </summary>
/// <param name="paramName"></param>
/// <returns></returns>
public string validataLimit(string paramName)
{
lock (httpClient)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("SELECT param_value ");
querySen.AppendLine("FROM sys_base ");
querySen.AppendLine("WHERE ( param_name = @paramName ) ");
Dictionary<string, object> ht = new Dictionary<string, object>(1);
ht.Add("@paramName", paramName);
object obj = httpClient.GetScalar(querySen.ToString(), ht);
return obj != null && obj != DBNull.Value ? obj.ToString().ToUpper() : string.Empty;
}
}
/// <summary>
/// 区分工厂条码补打,蜂鸣
/// </summary>
/// <param name="paramName"></param>
/// <returns></returns>
public string validataList(string paramName)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("SELECT param_value ");
querySen.AppendLine("FROM sys_base ");
querySen.AppendLine("WHERE ( param_name = @paramName ) and enabled='Y' ");
Dictionary<string,object> dic = new Dictionary<string, object>();
dic.Add("@paramName", paramName);
var obj = httpClient.GetScalar(querySen.ToString(), dic);
return obj != null && obj != DBNull.Value ? obj.ToString() : string.Empty;
}
/// <summary>
/// 获取打印机IP
/// </summary>
/// <param name="terminalID"></param>
/// <returns></returns>
public string GetPrintIP(string terminalID)
{
lock (httpClient)
{
string sqlstring = "SELECT param_value FROM dbo.sys_terminal_params WHERE param_name='printIP' AND terminal_id = @terminalID";
Dictionary<string, object> values = new Dictionary<string, object>(1);
values.Add("@terminalID", terminalID);
object obj = httpClient.GetScalar(sqlstring.ToString(), values);
return obj != null && obj != DBNull.Value ? obj.ToString() : string.Empty;
}
}
//更新上线时间
public bool updateOnLineStatus(string serial_number, string pdlineID)
{
lock (httpClient)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("UPDATE dbo.g_sn_status ");
querySen.AppendLine("SET work_flag = 0 , pdline_id = @pdlineID , update_userid=@userid, ");
querySen.AppendLine(" in_pdline_time = GETDATE() ");
querySen.AppendLine("WHERE serial_number = @serial_number ");
querySen.AppendLine("UPDATE dbo.g_workorder ");
querySen.AppendLine("SET status = 1 , ");
querySen.AppendLine(" in_pdline_ymd = dbo.get_ymd() , update_userid=@userid, ");
querySen.AppendLine(" in_pdline_hms = dbo.get_hms() ");
querySen.AppendLine("WHERE ruid = ( SELECT b.workorder_id ");
querySen.AppendLine(" FROM dbo.g_sn_status a ");
querySen.AppendLine(" LEFT JOIN dbo.g_workorder_detail b ON a.wo_detail_id = b.ruid ");
querySen.AppendLine(" LEFT JOIN dbo.sys_part c ON a.part_id = c.part_id ");
querySen.AppendLine(" WHERE a.serial_number = @serial_number ");
//querySen.AppendLine(" AND ( c.part_location = '02' ");
//querySen.AppendLine(" OR c.part_location = '04' ");
//querySen.AppendLine(" ) ");
querySen.AppendLine(" ) ");
Dictionary<string, object> ht = new Dictionary<string, object>(3);
ht.Add("@serial_number", serial_number);
ht.Add("@pdlineID", pdlineID);
ht.Add("@userid", userId);
return httpClient.Execute(querySen.ToString(), ht) > 0;
}
}
public string pdLineID(string terminalID)
{
lock (httpClient)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("SELECT pdline_id ");
querySen.AppendLine("FROM dbo.sys_terminal ");
querySen.AppendLine("WHERE terminal_id = @terminalID ");
Dictionary<string, object> ht = new Dictionary<string, object>(1);
ht.Add("@terminalID", terminalID);
object obj = httpClient.GetScalar(querySen.ToString(), ht);
return obj != null ? obj.ToString().ToUpper() : string.Empty;
}
}
#region mark 2019-01-24
/// <summary>
/// 查询当前班别
/// </summary>
/// <returns></returns>
public string getShiftID()
{
lock (httpClient)
{
Dictionary<string, object> param = new Dictionary<string, object>(1);
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine(" SELECT (dbo.GetShiftID ((SELECT CONVERT(varchar(100), GETDATE(), 108)),''))AS shift_id ");
object obj = httpClient.GetScalar(querySen.ToString());
return obj == null ? "" : obj.ToString();
}
}
/// <summary>
/// 当前工位完成数量
/// </summary>
/// <returns></returns>
public string getActualQty(int BoardType, string ShiftID, int terminal_id)
{
lock (httpClient)
{
Dictionary<string, object> Params = new Dictionary<string, object>(2);
StringBuilder SqlStringBuilder = new StringBuilder(1024);
SqlStringBuilder.Append("SELECT COUNT(DISTINCT serial_number) ");
SqlStringBuilder.Append("FROM g_sn_travel ");
SqlStringBuilder.Append("WHERE terminal_id = @terminal_id ");
SqlStringBuilder.Append(" AND out_process_time BETWEEN ( SELECT CONVERT(VARCHAR(100), CONVERT(DATE, DATEADD(DAY, ");
SqlStringBuilder.Append(" 0, ");
SqlStringBuilder.Append(" ( dbo.GetWorkDate('', ");
SqlStringBuilder.Append(" '') ))), 23) ");
SqlStringBuilder.Append(" + ' ' ");
SqlStringBuilder.Append(" + ( SELECT TOP 1 ");
SqlStringBuilder.Append(" start_time ");
SqlStringBuilder.Append(" FROM dbo.sys_shift ");
SqlStringBuilder.Append(" WHERE shift_id = @ShiftID ");
SqlStringBuilder.Append(" AND enabled = 'Y' ");
SqlStringBuilder.Append(" ) + ':00' ");
SqlStringBuilder.Append(" ) ");
SqlStringBuilder.Append(" AND ( SELECT CONVERT(VARCHAR(100), CONVERT(DATE, DATEADD(DAY, ");
SqlStringBuilder.Append(" " + BoardType + ", ");
SqlStringBuilder.Append(" ( dbo.GetWorkDate('', ");
SqlStringBuilder.Append(" '') ))), 23) ");
SqlStringBuilder.Append(" + ' ' ");
SqlStringBuilder.Append(" + ( SELECT TOP 1 ");
SqlStringBuilder.Append(" end_time ");
SqlStringBuilder.Append(" FROM dbo.sys_shift ");
SqlStringBuilder.Append(" WHERE shift_id = @ShiftID ");
SqlStringBuilder.Append(" AND enabled = 'Y' ");
SqlStringBuilder.Append(" ) + ':00' ");
SqlStringBuilder.Append(" ) ");
Params.Add("@ShiftID", ShiftID);
Params.Add("@terminal_id", terminal_id);
object obj = httpClient.GetScalar(SqlStringBuilder.ToString(), Params);
return obj == null ? "" : obj.ToString();
}
}
#endregion
#region poul
/// <summary>
/// 此条码是否是不良品
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public bool IsCurrentSN(string input)
{
lock (httpClient)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("SELECT * ");
querySen.AppendLine("FROM dbo.g_sn_status(NOLOCK) ");
querySen.AppendLine("WHERE serial_number = @input AND current_status=1 ");
Dictionary<string, object> ht = new Dictionary<string, object>(1);
ht.Add("@input", input);
var dtTemp = httpClient.GetEntityList(querySen.ToString(), ht);
return dtTemp == null || dtTemp.Count == 0 ? false : true;
}
}
#endregion
public string getPartLocation(string sn)
{
string sql = "SELECT b.part_location FROM dbo.g_sn_status a LEFT JOIN dbo.sys_part b ON a.part_id=b.part_id WHERE a.serial_number ='" + sn + "'";
object obj = httpClient.GetScalar(sql);
return obj != null && obj != DBNull.Value ? obj.ToString().ToUpper() : "";
}
#region 切换扭矩抢
/// <summary>
/// 查询是否存在
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public bool isExist(string ip, int terminal_id)
{
lock (httpClient)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("SELECT * FROM dbo.sys_terminal_torque WHERE ip=@ip AND terminal_id=@terminal_id ");
Dictionary<string, object> ht = new Dictionary<string, object>(2);
ht.Add("@ip", ip);
ht.Add("@terminal_id", terminal_id);
var dtTemp = httpClient.GetEntityList(querySen.ToString(), ht);
return dtTemp == null || dtTemp.Count == 0 ? false : true;
}
}
/// <summary>
/// 更新
/// </summary>
/// <param name="serial_number"></param>
/// <returns></returns>
public bool UpTorque(string IP, int terminal_id, string torqueFlag)
{
lock (httpClient)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("UPDATE dbo.sys_terminal_torque SET istorque_type='" + torqueFlag + "',update_ymd=dbo.get_ymd(), update_hms=dbo.get_hms() WHERE ip=@IP AND terminal_id=@terminal_id ");
Dictionary<string, object> ht = new Dictionary<string, object>(2);
ht.Add("@IP", IP);
ht.Add("@terminal_id", terminal_id);
return httpClient.Execute(querySen.ToString(), ht) > 0;
}
}
/// <summary>
/// 更新
/// </summary>
/// <param name="serial_number"></param>
/// <returns></returns>
public bool UpTorqueType(int terminal_id, string torqueFlag)
{
lock (httpClient)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("UPDATE dbo.sys_terminal_torque SET istorque_type='" + torqueFlag + "',update_ymd=dbo.get_ymd(), update_hms=dbo.get_hms() WHERE terminal_id=@terminal_id ");
Dictionary<string, object> ht = new Dictionary<string, object>(1);
ht.Add("@terminal_id", terminal_id);
return httpClient.Execute(querySen.ToString(), ht) > 0;
}
}
/// <summary>
/// 新增
/// </summary>
/// <param name="sn"></param>
/// <param name="operation"></param>
/// <param name="no"></param>
/// <param name="name"></param>
/// <param name="result"></param>
/// <returns></returns>
public bool InTorque(string ip, int terminal_id, string terminal_name, string torqueFlag)
{
string sql = "INSERT INTO dbo.sys_terminal_torque ( ip , terminal_id ,terminal_name , istorque_type , enabled ,guid)VALUES (@ip,@terminal_id ,@terminal_name ,'" + torqueFlag + "' , 'Y' , NEWID() )";
Dictionary<string, object> ht = new Dictionary<string, object>(3);
ht.Add("@ip", ip);
ht.Add("@terminal_id", terminal_id);
ht.Add("@terminal_name", terminal_name);
return httpClient.Execute(sql, ht) > 0;
}
/// <summary>
/// 更新sys_terminal_params
/// </summary>
/// <param name="serial_number"></param>
/// <returns></returns>
public bool UpTerminParam(string IP, int terminal_id)
{
lock (httpClient)
{
StringBuilder querySen = new StringBuilder(1024);
querySen.AppendLine("UPDATE dbo.sys_terminal_params SET param_value='" + IP + ",4545" + "' WHERE param_name='Torqur_IP' AND terminal_id=@terminal_id ");
Dictionary<string, object> ht = new Dictionary<string, object>(2);
ht.Add("@IP", IP);
ht.Add("@terminal_id", terminal_id);
return httpClient.Execute(querySen.ToString(), ht) > 0;
}
}
#endregion
#region sql格式化工具方法,后续拆除去作为独立工具类
/// <summary>
/// 获取更新 SQL 语句，用于事务处理
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="Cols">字段及值</param>
/// <param name="Where">Where 子语句（例如： where aa=11）</param>
/// <param name="ToLower">是否转换成小写</param>
/// <returns>UPDATE SQL</returns>
public string Update(string TableName, Dictionary<string, object> Cols, string Where, bool ToLower)
{
int Count = 0;
if (Cols.Count <= 0)
return string.Empty;
string Fields = " ";
foreach (var item in Cols)
{
if (Count != 0)
Fields += ",";
Fields += item.Key.ToString();
Fields += "=";
if (item.Value.GetType().ToString() == "System.String")
Fields += "'" + item.Value.ToString() + "'";
else
Fields += item.Value.ToString();
Count++;
}
Fields += " ";
string SqlString = "Update " + TableName + " Set " + Fields + " " + Where;
if (ToLower)
return SqlString.ToLower();
return SqlString;
}
#endregion
}
}
Reference in New Issue View Git Blame Copy Permalink