xiaomiMes/MainForm/ClassFile/ProjectClass/StationIn.cs

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

namespace MainForm
{
    /// <summary>
    /// 进站数据
    /// </summary>
    public class StationIn
    {
        #region 变量
        /// <summary>
        /// 主键
        /// </summary>
        public string GUID { get; set; } = Guid.NewGuid().ToString();

        /// <summary>
        /// 车间订单号
        /// </summary>
        public string Workorder_code { get; set; } = string.Empty;

        /// <summary>
        /// 产品型号（物料号）
        /// </summary>
        public string Mtltmrk { get; set; } = string.Empty;

        /// <summary>
        /// 产品序列号
        /// </summary>
        public string Sn { get; set; } = string.Empty;

        /// <summary>
        /// 进站接口Json数据 - Body
        /// </summary>
        public string StationIn_body { get; set; } = string.Empty;

        /// <summary>
        /// 进站数据
        /// </summary>
        public List<TestItem> Parameter_values { get; set; } = new List<TestItem>();

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

        /// <summary>
        /// 进站时间
        /// </summary>
        public string Test_time { get; set; } = string.Empty;

        /// <summary>
        /// 上传
        /// </summary>
        public string Upload { get; set; } = string.Empty;
        #endregion 变量

        /// <summary>
        /// 生产插入的语句
        /// </summary>
        /// <param name="upload">上传状态</param>
        /// <returns></returns>
        public string ToStringInsert(int upload)
        {
            string values = JsonConvert.SerializeObject(Parameter_values);

            return string.Format(
                "insert into StationIn" +
                "(" +
                "GUID," +
                "Workorder_code," +
                "Mtltmrk," +
                "Sn," +
                "StationIn_body," +
                "Parameter_values," +
                "Write_user," +
                "Test_time," +
                "Upload" +
                ") " +
                "values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')",
                GUID,
                Workorder_code,
                Mtltmrk,
                Sn,
                StationIn_body,
                values,
                Write_user,
                Test_time,
                upload);
        }

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

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="startDate">开始日期</param>
        /// <param name="endDate">结束日期</param>
        /// <param name="upload">上传状态</param>
        /// <returns></returns>
        public string ToStringQuery(string startDate, string endDate, string upload)
        {
            switch (upload)
            {
                case "All":
                    upload = "All";
                    break;
                case "已上传":
                    upload = "1";
                    break;
                case "未上传":
                    upload = "0";
                    break;
            }

            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendFormat("select " +
                    "GUID as 主键," +
                    "Workorder_code 车间订单号," +
                    "Mtltmrk 物料号," +
                    "Sn 产品序列号," +
                    "StationIn_body 进站Body数据," +
                    "Parameter_values 进站数据, " +
                    "Write_user 员工ID," +
                    "Test_time 进站时间, " +
                    "case when Upload = 1 then '已上传' else '未上传' end 上传状态 " +
                    "from StationIn " +
                    "where convert(datetime, Test_time, 120) " +
                    "between convert(datetime, '{0}', 120) " +
                    "and     convert(datetime, '{1}', 120) ",
                    startDate,
                    endDate);
            if (upload != "All")
                strSQL.AppendFormat("and Upload='{0}' ", upload);

            strSQL.Append(" Order By Test_time desc");
            return strSQL.ToString();
        }

        /// <summary>
        /// 查询数据量
        /// </summary>
        /// <param name="endDate">结束日期</param>
        /// <param name="upload">上传状态</param>
        /// <returns></returns>
        public string ToStringQueryCount(string endDate, string upload)
        {
            switch (upload)
            {
                case "All":
                    upload = "All";
                    break;
                case "已上传":
                    upload = "1";
                    break;
                case "未上传":
                    upload = "0";
                    break;
            }

            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendFormat("SELECT COUNT(*) FROM StationIn " +
                    "where convert(datetime,test_time,120) < convert(datetime, '{0}', 120) ",
                    endDate);
            if (upload != "All")
            {
                strSQL.AppendFormat("and Upload='{0}' ", upload);
            }
            return strSQL.ToString();
        }
    }
}