using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;

namespace M423project
{
    public class SpotCheckData
    {
        private SqlConnection connection;

        public SpotCheckData()
        {
            string connStr = ConfigurationManager.AppSettings["connectionString"];
            connection = new SqlConnection(connStr);
            try
            {
                connection.Open();
            }
            catch (Exception)
            {
                CommonUtil.WriteLog(LogType.Err, "连接数据库失败！");
            }
        }

        public DataTable GetTodayFinishedSpot()
        {
            DataTable dt = new DataTable();
            string sql = @"select * from SpotCheckDetail
                        where DATEDIFF(DAY, PickTime, GETDATE())= 0";
            SqlDataReader dr = null;
            using (SqlCommand cmd = new SqlCommand(sql, connection))
            {
                try
                {
                    dr = cmd.ExecuteReader();
                    dt.Load(dr);
                }
                catch (Exception e)
                {
                    CommonUtil.WriteLog(LogType.Inf, string.Format("获取点检数据数据时出现异常：{0}", e.Message));
                }
                finally
                {
                    if (dr != null)
                        dr.Close();
                }
            }
            return dt;
        }

        public DataTable GetTodayCheckPlan()
        {
            DataTable dt = new DataTable();
            string sql = @"select * from SpotCheck
                        where DATEDIFF(DAY,BeginTime,GETDATE())=0";
            SqlDataReader dr = null;
            using (SqlCommand cmd = new SqlCommand(sql, connection))
            {
                try
                {
                    dr = cmd.ExecuteReader();
                    dt.Load(dr);
                }
                catch (Exception e)
                {
                    CommonUtil.WriteLog(LogType.Inf, string.Format("获取执行中的点检计划数据时出现异常：{0}", e.Message));
                }
                finally
                {
                    if (dr != null)
                        dr.Close();
                }
            }
            return dt;
        }

        public bool IsSpotCheck()
        {
            try
            {
                int count = 0;
                StringBuilder sb = new StringBuilder();
                sb.AppendFormat(@"select COUNT(ID) from SpotCheck
                                where DATEDIFF(DAY,BeginTime,GETDATE())=0");
                using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
                {
                    count = Convert.ToInt32(cmd.ExecuteScalar());
                }
                return count > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public DataTable GetTodaySpotCheck()
        {
            DataTable dt = new DataTable();
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@"select * from SpotCheck
                            where DATEDIFF(DAY,BeginTime,GETDATE())=0");
            SqlDataReader dr = null;
            using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
            {
                try
                {
                    dr = cmd.ExecuteReader();
                    dt.Load(dr);
                }
                catch (Exception e)
                {
                    CommonUtil.WriteLog(LogType.Inf, string.Format("获取抽檢数据时出现异常：{0}", e.Message));
                }
                finally
                {
                    if (dr != null)
                        dr.Close();
                }
            }
            return dt;
        }

        public void SaveSpotCheck(SpotCheck spotCheck)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendFormat(@"insert into SpotCheck
                                (BeginTime,TotalQty,FinishedQty,PickCount,LastBreakProductQty,LastProductQty)
                                values(GETDATE(),{0},{1},{2},{3},{4})", spotCheck.TotalQty, spotCheck.FinishedQty, spotCheck.PickCount,spotCheck.LastBreakProductQty,spotCheck.LastProductQty);
                using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                CommonUtil.WriteLog(LogType.Err, ex.ToString());
            }
        }

        public void SaveSpotCheckDetail(SpotCheckDetail spotCheckDetail)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendFormat(@"insert into SpotCheckDetail
                                (PickTime,ProductNo,ProductHeight,ProductLength,ProductWidth,Type)
                                values(GETDATE(),'{0}','{1}','{2}','{3}',N'{4}')", spotCheckDetail.ProductNo,
                                spotCheckDetail.ProductHeight, spotCheckDetail.ProductLength,
                                spotCheckDetail.ProductWidth, spotCheckDetail.Type);
                using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                CommonUtil.WriteLog(LogType.Err, ex.ToString());
            }
        }

        public void UpdateFinishedQty()
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendFormat(@"update SpotCheck
                                set FinishedQty=FinishedQty+1
                                where DATEDIFF(DAY,BeginTime,GETDATE())=0");
                using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                CommonUtil.WriteLog(LogType.Err, ex.ToString());
            }
        }

        public void UpdateLastBreakProductQty(int qty)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendFormat(@"update SpotCheck
                                set LastBreakProductQty={0}
                                where DATEDIFF(DAY,BeginTime,GETDATE())=0",qty);
                using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                CommonUtil.WriteLog(LogType.Err, ex.ToString());
            }
        }

        public void UpdateLastProductQty(int qty)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendFormat(@"update SpotCheck
                                set LastProductQty={0}
                                where DATEDIFF(DAY,BeginTime,GETDATE())=0", qty);
                using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                CommonUtil.WriteLog(LogType.Err, ex.ToString());
            }
        }

        public void SaveCheckToCsv(SpotCheckDetail detail)
        {
            string fileName = @"E:\CheckReport\CheckReport" + DateTime.Now.ToString("yyyyMMdd") + ".csv";
            bool isNew = !File.Exists(fileName);
            using (FileStream myStream = new FileStream(fileName, FileMode.OpenOrCreate))
            {
                myStream.Seek(0, SeekOrigin.End);
                using (StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.UTF8))
                {
                    try
                    {
                        if (isNew)
                        {
                            sw.WriteLine("產品條碼,拾取時間,長度,寬度,高度");
                        }
                        sw.WriteLine(
                            string.Format("{0},{1},{2},{3},{4}",
                            detail.ProductNo, detail.PickTime, detail.ProductLength, detail.ProductWidth, detail.ProductHeight
                            ));
                    }
                    catch (Exception e)
                    {
                        CommonUtil.WriteLog(LogType.Inf, string.Format("记录抽检结果出现异常：{0}", e.Message));
                    }
                }
            }
        }

        public void ClearTodayCheck()
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendFormat(@"delete from SpotCheck
                                where DATEDIFF(DAY,BeginTime,GETDATE())=0");
                sb.AppendFormat(@"delete from SpotCheckDetail
                                where DATEDIFF(DAY,PickTime,GETDATE())=0");
                using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                CommonUtil.WriteLog(LogType.Err, ex.ToString());
            }
        }
    }

    public class SpotCheck
    {
        /// <summary>
        /// 抽检开始时间
        /// </summary>
        public DateTime BeginTime { get; set; }

        /// <summary>
        /// 总抽检数
        /// </summary>
        public int TotalQty { get; set; }

        /// <summary>
        /// 已完成抽检数
        /// </summary>
        public int FinishedQty { get; set; }

        /// <summary>
        /// 抽检跨度(每隔多少个抽检下一个)
        /// </summary>
        public int PickCount { get; set; }

        /// <summary>
        /// 上一次程序关闭时记录下的界面生产数量
        /// </summary>
        public int LastBreakProductQty { get; set; }

        /// <summary>
        /// 上一次拾取时记录下的界面生产数量
        /// </summary>
        public int LastProductQty { get; set; }
    }

    public class SpotCheckDetail
    {
        /// <summary>
        /// 产品条码
        /// </summary>
        public string ProductNo { get; set; }

        /// <summary>
        /// 拾取时间
        /// </summary>
        public DateTime PickTime { get; set; }

        public double ProductHeight { get; set; }

        public double ProductLength { get; set; }

        public double ProductWidth { get; set; }

        /// <summary>
        /// 拾取类型(自动拾取、手动拾取)
        /// </summary>
        public string Type { get; set; }

    }
}