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; } }