using System;
using System.Collections.Generic;
using System.Data.SQLite;
using TSA_V.Common;

namespace DAL
{
    public class SQLite
    {
        private SQLiteConnection _con;

        public SQLite()
        {
        }

        public bool IsCon { private set; get; } = false;

        public bool Connect()
        {
            try
            {
                _con = new SQLiteConnection("data source='" + Environment.CurrentDirectory + "\\config\\database.db3';Version=3");
                _con.Open();
                IsCon = true;
                LogUtil.info("数据库连接成功");
                return true;
            }
            catch (Exception ex)
            {
                IsCon = false;
                LogUtil.error("数据库连接失败：" + ex.Message);
                return false;
            }
        }

        public void Close()
        {
            if (_con != null)
            {
                _con.Close();
                _con.Dispose();
                LogUtil.info("数据库关闭");
            }
        }

        //public bool QueryUserName(string username, string password)
        //{
        //    bool succeed = false;
        //    string sql = "SELECT * FROM Account WHERE Username='" + username + "' AND Password='" + password + "'";
        //    bool rtn = Select(sql, out string[][] data);
        //    if (rtn)
        //    {
        //        if (data.Length > 0)
        //            succeed = true;
        //    }
        //    return succeed;
        //}

        public bool QueryHistory(string barCode, string dateTimeFront, string dateTimeBack, out string[][] data)
        {
            string sql = "SELECT ID,ProName,ProType,BarCode,BoardWidth,BoardLength,AoiResult,UserName,CreateDate FROM OperateInfo WHERE 1=1";
            if (!string.IsNullOrEmpty(barCode))
                sql += " AND BarCode LIKE '%" + barCode + "%'";
            if (!string.IsNullOrEmpty(TSA_V.DeviceLibrary.DB.userName))
                sql += " AND UserName = '" + TSA_V.DeviceLibrary.DB.userName + "'";
            if (!string.IsNullOrEmpty(dateTimeFront))
                sql += " AND CreateDate >= '" + dateTimeFront + "'";
            if (!string.IsNullOrEmpty(dateTimeBack))
                sql += " AND CreateDate <= '" + dateTimeBack + "'";

            bool rtn = Select(sql, out data);
            return rtn;
        }

        public bool QueryPointInfo(string id, out string[][] data)
        {
            string sql = "SELECT ID,PartNum,PointName,CreateDate FROM PointInfo WHERE ID=" + id;
            bool rtn = Select(sql, out data);
            return rtn;
        }

        public bool AddHistory(TSA_V.DeviceLibrary.OpInfo info, out int id)
        {
            string sql;
            bool rtn;
            id = info.ID;

            //查询可写入的ID
            if (info.ID == 0)
            {
                sql = "SELECT MAX(ID) FROM PointInfo";
                rtn = Select(sql, out string[][] data);
                if (!rtn) return false;
                int.TryParse(data[0][0], out id);
                id++;
            }

            //添加操作信息记录
            string createDate = string.Format("{0:yyyy-MM-dd HH:mm:ss}", DateTime.Now);
            sql = "INSERT INTO OperateInfo(ID,ProName,ProType,BarCode,BoardWidth,BoardLength,AoiResult,UserName,CreateDate) " +
                "VALUES(" + id + ",'" + info.ProName + "','" + info.ProType + "','" + info.BarCode + "'," + info.BoardWidth + "," + 
                info.BoardLength + ",'" + info.AoiResult + "','" + TSA_V.DeviceLibrary.DB.userName + "','" + createDate + "')";
            rtn = Execute(sql);
            if (!rtn) return false;

            //添加操作点信息
            if (info.ID == 0)
            {
                SQLiteTransaction tr = _con.BeginTransaction();
                for (int i = 0; i < info.pointList.Count; i++)
                {
                    sql = "INSERT INTO PointInfo(ID,PartNum,PointName,CreateDate) " +
                        "VALUES(" + id + ",'" + info.pointList[i].PartNum + "','" + info.pointList[i].PointName + "','" + createDate + "')";
                    rtn = Execute(sql);
                    if (!rtn) break;
                }
                LogUtil.info("数据库Commit");
                if (rtn)
                    tr.Commit();
                else
                    tr.Rollback();
                         
                LogUtil.info("数据库 完成");
            }

            return rtn;
        }





        private bool Select(string sql, out string[][] data)
        {
            data = null;
            if (!IsCon) return false;
            List<string[]> content = new List<string[]>();

            try
            {
                SQLiteCommand cmd = new SQLiteCommand(_con) { CommandText = sql };
                SQLiteDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    string[] s = new string[dr.FieldCount];
                    for (int i = 0; i < s.Length; i++)
                        s[i] = dr[i].ToString();
                    content.Add(s);
                }
                dr.Close();
                cmd.Dispose();
                data = content.ToArray();
                LogUtil.info("数据库Select，SQL=" + sql);
                return true;
            }
            catch (Exception ex)
            {
                LogUtil.error("数据库Select：" + ex.Message);
                return false;
            }

        }

        private bool Execute(string sql)
        {
            if (!IsCon) return false;

            try
            {
                SQLiteCommand cmd = new SQLiteCommand(_con) { CommandText = sql };
                int n = cmd.ExecuteNonQuery();
                cmd.Dispose();
                LogUtil.info("数据库Execute，SQL=" + sql + "，修改了" + n + "行");
                return true;
            }
            catch (Exception ex)
            {
                LogUtil.error("数据库Execute：" + ex.Message);
                return false;
            }
        }

    }
}