using Newtonsoft.Json;
using NPOI.SS.Formula.Functions;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MainForm
{
    /// <summary>
    /// 设备点检数据
    /// </summary>
    public class OneCheckData  // 设备点检数据
    {
        /// <summary>
        /// 主键
        /// </summary>
        public string ID { get; set; }
        /// <summary>
        /// 产线编号
        /// </summary>
        public string Line_code { get; set; } = string.Empty;
        /// <summary>
        /// 产线名称
        /// </summary>
        public string Line_name { get; set; } = string.Empty;
        /// <summary>
        /// 设备编号
        /// </summary>
        public string Equipment_code { get; set; } = string.Empty;
        /// <summary>
        /// 设备名称
        /// </summary>
        public string Equipment_name { get; set; } = string.Empty;
        /// <summary>
        /// 车间订单号
        /// </summary>
        public string Workorder_code { get; set; } = string.Empty;
        /// <summary>
        /// 工序编号
        /// </summary>
        public string Procedure_code { get; set; } = string.Empty;
        /// <summary>
        /// 工序名称
        /// </summary>
        public string Procedure_name { get; set; } = string.Empty;
        /// <summary>
        /// 点检数据
        /// </summary>
        public List<OneCheckItem> Oneckeck_values = new List<OneCheckItem>();
        /// <summary>
        /// 员工ID
        /// </summary>
        public string Onecheck_empcode { get; set; } = string.Empty;
        /// <summary>
        /// 员工姓名
        /// </summary>
        public string Onecheck_empname { get; set; } = string.Empty;
        /// <summary>
        /// 点检时间
        /// </summary>
        public string Onecheck_time { get; set; } = string.Empty;

        #region 方法
        /// <summary>
        /// 插入数据的语句
        /// </summary>
        /// <param name="upload">是否上传</param>
        /// <returns></returns>
        public string ToStringInsert(int upload)
        {
            string values = JsonConvert.SerializeObject(Oneckeck_values);
            ID = Guid.NewGuid().ToString();

            return string.Format("insert into " +
                "OneCheckData(" +
                "ID," +
                "Line_code," +
                "Line_name," +
                "Equipment_code," +
                "Equipment_name," +
                "Workorder_code," +
                "Procedure_code," +
                "Procedure_name," +
                "Oneckeck_values," +
                "Onecheck_empcode," +
                "Onecheck_empname," +
                "Onecheck_time," +
                "Upload" +
                ") values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}')",
                ID,
                Line_code,
                Line_name,
                Equipment_code,
                Equipment_name,
                Workorder_code,
                Procedure_code,
                Procedure_name,
                values,
                Onecheck_empcode,
                Onecheck_empname,
                Onecheck_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 OneCheckData " +
                " set " +
                " Upload = '{0}' " +
                " where ID = '{1}'",
                upload, id);
        }

        /// <summary>
        /// 更新上传状态 - 批量
        /// </summary>
        /// <param name="upload">上传状态</param>
        /// <param name="id">Id</param>
        /// <returns></returns>
        public static string ToStringUpdateStatusByIDs(int upload, List<string> ids)
        {
            return string.Format(
                "update OneCheckData " +
                " set " +
                " Upload = '{0}' " +
                " where ID in ('{1}')",
                upload, string.Join("','", ids));
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="startDate">开始时间</param>
        /// <param name="endDate">结束时间</param>
        /// <param name="sendStatus"></param>
        /// <returns></returns>
        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 主键, " +
                "Line_code 产线编号," +
                "Line_name 产线名称," +
                "Equipment_code 设备编号, " +
                "Equipment_name 设备名称, " +
                "Workorder_code 车间订单号, " +
                "Procedure_code 工序编号, " +
                "Procedure_name 工序名称," +
                "Onecheck_empcode 员工ID," +
                "Onecheck_empname 员工姓名," +
                "Onecheck_time 点检时间, " +
                "case when Upload = 1 then '已上传' else '未上传' end 上传状态, " +
                "Oneckeck_values 点检数据 " +
                "from onecheckdata " +
                "where convert(datetime,Onecheck_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 (Procedure_name != "All")
            {
                strSQL.AppendFormat("and Procedure_name='{0}' ", Procedure_name);
            }
            strSQL.Append(" Order By Onecheck_time desc");

            return strSQL.ToString();
        }

        /// <summary>
        /// 查询今天的还未上传的点检数据 的语句
        /// </summary>
        /// <param name="procedure_code">工序编号</param>
        /// <param name="plcOrder">车间订单号</param>
        /// <returns></returns>
        public string ToQuerySQL_Today(string procedure_code,string plcOrder)
        {
            string dtNow = DateTime.Now.ToString("yyyy/MM/dd");
            string startDate = dtNow + " 00:00:00";
            string endDate = dtNow + " 23:59:59";

            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendFormat("select " +
                "ID, " +
                "Line_code, " +
                "Line_name, " +
                "Equipment_code, " +
                "Equipment_name, " +
                "Workorder_code, " +
                "Procedure_code, " +
                "Procedure_name, " +
                "Onecheck_empcode, " +
                "Onecheck_empname, " +
                "Onecheck_time, " +
                "Oneckeck_values " +
                "from onecheckdata " +
                "where convert(datetime,Onecheck_time,120) " +
                "between convert(datetime, '{0}', 120) " +
                "and     convert(datetime, '{1}', 120) " +
                "and Upload = '0' ",
                startDate,
                endDate
                );
            if (!string.IsNullOrEmpty(procedure_code))  // 工序编号
            {
                strSQL.AppendFormat("and Procedure_code='{0}' ", procedure_code);
            }
            if (!string.IsNullOrEmpty(plcOrder))  // 车间订单号
            {
                strSQL.AppendFormat("and Workorder_code='{0}' ", plcOrder);
            }
            strSQL.Append(" Order By Onecheck_time desc");

            return strSQL.ToString();
        }

        /// <summary>
        /// 清空 点检数据 By 工序号、订单号
        /// </summary>
        /// <param name="procedure_code">工序编号</param>
        /// <param name="plcOrder">车间订单号</param>
        /// <returns></returns>
        public static string ToDeteleByProcedurecodeAndPlcOrder(string procedure_code, string plcOrder)
        {
            string dtNow = DateTime.Now.ToString("yyyy/MM/dd");
            string startDate = dtNow + " 00:00:00";
            string endDate = dtNow + " 23:59:59";

            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendFormat("Delete " +
                "from onecheckdata " +
                "where convert(datetime,Onecheck_time,120) " +
                "between convert(datetime, '{0}', 120) " +
                "and     convert(datetime, '{1}', 120) " +
                "and Upload = '0' " +
                "and Procedure_code='{2}' " +
                "and Workorder_code='{3}' ",
                startDate,
                endDate,
                procedure_code,
                plcOrder
                );
            return strSQL.ToString();
        }
        #endregion
    }

    /// <summary>
    /// 点检明细数据
    /// </summary>
    public class OneCheckItem
    {
        public string Onecheck_name { get; set; } = string.Empty;
        public string Onecheck_content { get; set; } = string.Empty;
        public string Onecheck_result { get; set; } = string.Empty;
    }
}