using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;
namespace MainForm
{
///
/// 加工数据(上传加工数据 接口用 参数)
///
public class ProcessData // 加工数据
{
///
/// 主键
///
public string ID { get; set; }
///
/// 设备编号;以工位为单位,产线名称拼音-S数字
///
public string Equipment_code;
///
/// 测试项目;该工位的加工任务,如:装配
///
public string Testitem;
///
/// 车间订单号;车间工艺提供
///
public string Workorder_code;
///
/// 批次号;车间工艺提供
///
public string Batch_number;
///
/// 产品型号
///
public string Mtltmrk;
///
/// 产品序列号;车间工艺提供规则
///
public string Sn;
///
/// 加工数据
///
public List Parameter_values;
///
/// MES 接口数据
///
public string MESJson = string.Empty;
///
/// Iot 接口数据
///
public string IotJson = string.Empty;
///
/// 员工Id;操作员工编号,如:12345
///
public string Write_user="123456";
///
/// 测试时间;格式:yyyy-MM-dd HH:mm:ss.fff
///
public string Test_time;
///
/// 生产插入的语句
///
/// 是否上传
///
public string ToStringInsert(int upload)
{
string values = JsonConvert.SerializeObject(Parameter_values);
ID = Guid.NewGuid().ToString();
return string.Format(
"insert into ProcessData" +
"(" +
"ID," +
"Equipment_code," +
"Workorder_code," +
"Batch_number," +
"Sn,Testitem," +
"Parameter_values," +
"Write_user," +
"Test_time," +
"Upload" +
") " +
"values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')",
ID,
Equipment_code,
Workorder_code,
Batch_number,
Sn,
Testitem,
values,
Write_user,
Test_time,
upload);
}
///
/// 更新上传状态
///
/// 上传状态
/// Id
///
public static string ToStringUpdateStatusByID(int upload, string id)
{
return string.Format(
"update ProcessData" +
" set " +
" Upload = '{0}' " +
" where ID = '{1}'",
upload, id);
}
///
/// 更新接口数据
///
/// 更新接口数据
/// Id
///
public static string ToStringUpdateStationOut_body(string StationOut_body,string StationOutReturn_body, string id)
{
return string.Format(
"update ProcessData" +
" set " +
" StationOut_body = '{0}', " +
" StationOutReturn_body = '{1}' " +
" where ID = '{2}'",
StationOut_body, StationOutReturn_body, id);
}
///
/// 生产插入的语句
///
/// 是否上传
///
public string ToStringInsert(int mesUpload, int iotUpload)
{
string values = JsonConvert.SerializeObject(Parameter_values);
ID = Guid.NewGuid().ToString();
return string.Format(
"insert into ProcessData" +
"(" +
"ID," +
"Equipment_code," +
"Testitem," +
"Workorder_code," +
"Mtltmrk," +
"Sn," +
"Parameter_values," +
"MESJson," +
"IotJson," +
"Write_user," +
"Test_time," +
"MESUpload" +
"IotUpload" +
") " +
"values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}')",
ID,
Equipment_code,
Testitem,
Workorder_code,
Mtltmrk,
Sn,
values,
MESJson,
IotJson,
Write_user,
Test_time,
mesUpload,
iotUpload);
}
///
/// 更新MES、Iot上传状态
///
/// MES上传BodyJson
/// MES上传状态
/// Iot上传BodyJson
/// Iot上传状态
/// Id
///
public static string ToStringUpdateStatusByID(string mesJson, int mesUpload, string iotJson, int iotUpload, string id)
{
return string.Format(
"update ProcessData" +
" set " +
" MESJson = '{0}' " +
" MESUpload = '{1}' " +
" IotJson = '{2}' " +
" IotUpload = '{3}' " +
" where ID = '{4}'",
mesJson, mesUpload,
iotJson, iotUpload,
id);
}
///
/// 更新MES、Iot上传状态
///
/// MES上传状态
/// Iot上传状态
/// Id
///
public static string ToStringUpdateStatusByID(int mesUpload, int iotUpload, string id)
{
return string.Format(
"update ProcessData" +
" set " +
" MESUpload = '{1}' " +
" IotUpload = '{3}' " +
" where ID = '{4}'",
mesUpload, iotUpload,
id);
}
///
/// 更新MES上传状态
///
/// 上传状态
/// Id
///
public static string ToStringMESUpdateStatusByID(int mesUpload, string id)
{
return string.Format(
"update ProcessData" +
" set " +
" MESUpload = '{0}' " +
" where ID = '{1}'",
mesUpload, id);
}
///
/// 更新Iot上传状态
///
/// 上传状态
/// Id
///
public static string ToStringIotUpdateStatusByID(int iotUpload, string id)
{
return string.Format(
"update ProcessData" +
" set " +
" IotUpload = '{0}' " +
" where ID = '{1}'",
iotUpload, id);
}
public string ToStringQuery(string startDate, string endDate, string sendStatus)
{
switch(sendStatus)
{
case "All":
sendStatus = "All";
break;
case "已上传":
sendStatus = "1";
break;
case "未上传":
sendStatus = "0";
break;
}
StringBuilder strSQL = new StringBuilder();
strSQL.AppendFormat("select " +
"ID as 主键, " +
"Equipment_code 设备编号, " +
"Testitem 测试项目, " +
"Workorder_code 车间订单号, " +
"Mtltmrk 产品型号," +
"Sn 产品序列号," +
"Write_user 员工ID," +
"Test_time 测试时间, " +
"case when MESUpload = 1 then '已上传' else '未上传' end MES上传状态, " +
"case when IotUpload = 1 then '已上传' else '未上传' end Iot上传状态, " +
"Parameter_values 加工数据 " +
"MESJson MES接口数据 " +
"IotJson Iot接口数据 " +
"from ProcessData " +
"where convert(datetime,test_time,120) " +
"between convert(datetime, '{0}', 120) " +
"and convert(datetime, '{1}', 120) ",
startDate,
endDate);
if (Equipment_code != "All")
{
strSQL.AppendFormat("and Equipment_code='{0}' ", Equipment_code);
}
if (sendStatus != "All")
{
strSQL.AppendFormat("and upload='{0}' ", sendStatus);
}
if (Testitem != "All")
{
strSQL.AppendFormat("and Testitem='{0}' ", Testitem);
}
strSQL.Append(" Order By Test_time desc");
return strSQL.ToString();
}
public string ToStringQuery2(string startDate, string endDate, string sendStatus)
{
switch (sendStatus)
{
case "All":
sendStatus = "All";
break;
case "已上传":
sendStatus = "1";
break;
case "未上传":
sendStatus = "0";
break;
}
StringBuilder strSQL = new StringBuilder();
strSQL.AppendFormat("select " +
"ID as 主键, " +
"Equipment_code 设备编号, " +
"Testitem 测试项目, " +
"Workorder_code 车间订单号, " +
"Mtltmrk 产品型号," +
"Sn 产品序列号," +
"Parameter_values 加工数据, " +
"MESJson MES接口数据 " +
"IotJson Iot接口数据 " +
"Write_user 员工ID," +
"Test_time 测试时间, " +
"case when MESUpload = 1 then '已上传' else '未上传' end 上传状态 " +
"from ProcessData " +
"where convert(datetime,test_time,120) " +
"between convert(datetime, '{0}', 120) " +
"and convert(datetime, '{1}', 120) ",
startDate,
endDate);
if (sendStatus != "All")
{
strSQL.AppendFormat("and upload='{0}' ", sendStatus);
}
strSQL.Append(" Order By Test_time desc");
return strSQL.ToString();
}
public string ToStringQueryCount(string endDate, string sendStatus)
{
switch (sendStatus)
{
case "All":
sendStatus = "All";
break;
case "已上传":
sendStatus = "1";
break;
case "未上传":
sendStatus = "0";
break;
}
StringBuilder strSQL = new StringBuilder();
strSQL.AppendFormat("SELECT COUNT(*) FROM ProcessData " +
"where convert(datetime,test_time,120) < convert(datetime, '{0}', 120) ",
endDate);
if (sendStatus != "All")
{
strSQL.AppendFormat("and upload='{0}' ", sendStatus);
}
return strSQL.ToString();
}
}
///
/// 加工数据 内容
///
public class TestItem
{
///
/// 参数名称
///
public string Parameter_name;
///
/// 参数值
///
public string Parameter_value;
///
/// 参数单位
///
public string Parameter_unit;
}
}