xiaomiMes/MainForm/ClassFile/ProjectClass/ProcessData.cs

using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;

namespace MainForm
{
    /// <summary>
    /// 加工数据(上传加工数据 接口用 参数)
    /// </summary>
    public class ProcessData  // 加工数据
    {
        /// <summary>
        /// 主键
        /// </summary>
        public string ID { get; set; }

        /// <summary>
        /// 设备编号；以工位为单位，产线名称拼音-S数字
        /// </summary>
        public string Equipment_code;
        /// <summary>
        /// 测试项目;该工位的加工任务，如：装配
        /// </summary>
        public string Testitem;

        /// <summary>
        /// 车间订单号;车间工艺提供
        /// </summary>
        public string Workorder_code;

        /// <summary>
        /// 批次号;车间工艺提供
        /// </summary>
        public string Batch_number;

        /// <summary>
        /// 产品型号
        /// </summary>
        public string Mtltmrk;

        /// <summary>
        /// 产品序列号;车间工艺提供规则
        /// </summary>
        public string Sn;

        /// <summary>
        /// 加工数据
        /// </summary>
        public List<TestItem> Parameter_values;

        /// <summary>
        /// MES 接口数据
        /// </summary>
        public string MESJson = string.Empty;
        /// <summary>
        /// Iot 接口数据
        /// </summary>
        public string IotJson = string.Empty;

        /// <summary>
        /// 员工Id;操作员工编号，如：12345
        /// </summary>
        public string Write_user="123456";

        /// <summary>
        /// 测试时间;格式：yyyy-MM-dd HH:mm:ss.fff
        /// </summary>
        public string Test_time;


        /// <summary>
        /// 生产插入的语句
        /// </summary>
        /// <param name="upload">是否上传</param>
        /// <returns></returns>
        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);
        }

        /// <summary>
        /// 更新上传状态
        /// </summary>
        /// <param name="upload">上传状态</param>
        /// <param name="id">Id</param>
        /// <returns></returns>
        public static string ToStringUpdateStatusByID(int upload, string id)
        {
            return string.Format(
                "update ProcessData" +
                " set " +
                " Upload = '{0}' " +
                " where ID = '{1}'",
                upload, id);
        }

        /// <summary>
        /// 更新接口数据
        /// </summary>
        /// <param name="upload">更新接口数据</param>
        /// <param name="id">Id</param>
        /// <returns></returns>
        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);
        }


        /// <summary>
        /// 生产插入的语句
        /// </summary>
        /// <param name="upload">是否上传</param>
        /// <returns></returns>
        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);
        }

        /// <summary>
        /// 更新MES、Iot上传状态
        /// </summary>
        /// <param name="mesJson">MES上传BodyJson</param>
        /// <param name="mesUpload">MES上传状态</param>
        /// <param name="iotJson">Iot上传BodyJson</param>
        /// <param name="iotUpload">Iot上传状态</param>
        /// <param name="id">Id</param>
        /// <returns></returns>
        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);
        }

        /// <summary>
        /// 更新MES、Iot上传状态
        /// </summary>
        /// <param name="mesUpload">MES上传状态</param>
        /// <param name="iotUpload">Iot上传状态</param>
        /// <param name="id">Id</param>
        /// <returns></returns>
        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);
        }

        /// <summary>
        /// 更新MES上传状态
        /// </summary>
        /// <param name="mesUpload">上传状态</param>
        /// <param name="id">Id</param>
        /// <returns></returns>
        public static string ToStringMESUpdateStatusByID(int mesUpload, string id)
        {
            return string.Format(
                "update ProcessData" +
                " set " +
                " MESUpload = '{0}' " +
                " where ID = '{1}'",
                mesUpload, id);
        }

        /// <summary>
        /// 更新Iot上传状态
        /// </summary>
        /// <param name="upload">上传状态</param>
        /// <param name="id">Id</param>
        /// <returns></returns>
        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();
        }
    }

    /// <summary>
    /// 加工数据 内容
    /// </summary>
    public class TestItem
    {
        /// <summary>
        /// 参数名称
        /// </summary>
        public string Parameter_name;

        /// <summary>
        /// 参数值
        /// </summary>
        public string Parameter_value;

        /// <summary>
        /// 参数单位
        /// </summary>
        public string Parameter_unit;
    }
}