using ADOX;
using System;
using System.Data.OleDb;
using System.IO;

namespace SLC1_N
{
    class NGDatabase
    {
        /// <summary>
        /// 判断是否有数据库，没有就新建
        /// </summary>
        public static void AddDatabase()
        {
            //判断所选路径是否有文件
            string filepath = System.Environment.CurrentDirectory + "\\Config\\NGCode\\CheckCode.mdb";
            if (!File.Exists(filepath))
            {
                string constr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + System.Environment.CurrentDirectory + "\\Config\\NGCode\\CheckCode.mdb;";
                OleDbConnection con = new OleDbConnection(constr);

                string filepath2 = System.Environment.CurrentDirectory + "\\Config\\NGCode\\";
                Directory.CreateDirectory(filepath2);//新建文件夹

                Catalog Product = new Catalog();
                Product.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.Environment.CurrentDirectory + "\\Config\\NGCode\\CheckCode.mdb;Jet OLEDB:Engine Type=5;");

                con.Open();

                string sql = "CREATE TABLE NGCode ([条码] VarChar(50),[NG次数] VarChar(20))";
                OleDbCommand cmd = new OleDbCommand(sql, con);

                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
        /// <summary>
        /// NG的条码，添加进数据库里
        /// </summary>
        /// <param name="NGCode"></param>
        public static int ADDDATA(string NGCode)
        {
            //Boolean add = true;
            //List<string> code = new List<string>(); 
            //string filepath = System.Environment.CurrentDirectory + "\\Config\\NGCode\\NGCode.mdb";

            string constr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + System.Environment.CurrentDirectory + "\\Config\\NGCode\\CheckCode.mdb;";
            OleDbConnection con = new OleDbConnection(constr);
            con.Open();

            //string sql2 = "SELECT * FROM NGCode";
            string sql2 = "SELECT * FROM NGCode WHERE 条码='" + NGCode + "'";

            OleDbCommand cmd2 = new OleDbCommand(sql2, con);
            OleDbDataReader ngcodeinform = cmd2.ExecuteReader();
            string ngnum = null;
            while (ngcodeinform.Read())
            {
                //Account.Text = Convert.ToString(ngcodeinform["条码"]);
                ngnum = Convert.ToString(ngcodeinform["NG次数"]);
            }

            ngcodeinform.Close();
            con.Close();
            if (String.IsNullOrEmpty(ngnum))
            {
                con.Open();
                string sql3 = " INSERT INTO NGCode(条码, NG次数) VALUES('" + NGCode + "', '" + 1 + "')";
                OleDbCommand cmd3 = new OleDbCommand(sql3, con);
                cmd3.ExecuteNonQuery();
                con.Close();
                return 1;
            }
            else
            {
                int num = Convert.ToInt32(ngnum);
                if (num == 1)
                {
                    con.Open();
                    string sql4 = " UPDATE NGCode SET NG次数='" + 2 + "' where 条码='" + NGCode + "'";
                    OleDbCommand cmd4 = new OleDbCommand(sql4, con);
                    cmd4.ExecuteNonQuery();
                    con.Close();
                    return 2;
                }
                else if (num == 2)
                {
                    con.Open();
                    string sql5 = "DELETE FROM NGCode WHERE 条码='" + NGCode + "'";
                    OleDbCommand cmd5 = new OleDbCommand(sql5, con);
                    cmd5.ExecuteNonQuery();
                    con.Close();
                    return 3;
                }
                else
                {
                    return num;
                }
            }
            //OleDbDataReader codeinform = cmd2.ExecuteReader();

            ////下移游标，读取一行，如果没有数据了则返回false
            //while (codeinform.Read())
            //{
            //    code.Add(Convert.ToString(codeinform["条码"]));
            //}
            //codeinform.Close();
            //con.Close();
            ////遍历账户，查看是否有重复的账号
            //foreach (string ng in code)
            //{
            //    if (NGCode == ng)
            //    {
            //        //add = false;
            //        CheckData();
            //        return;
            //    }
            //}

            //if (add is true)
            //{

            ////string filepath = System.Environment.CurrentDirectory + "\\Config\\NGCode.mdb";
            //string constr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + System.Environment.CurrentDirectory + "\\Config\\Users\\UsersInfo.mdb;";
            //OleDbConnection con = new OleDbConnection(constr);


            //}
        }

        /// <summary>
        /// OK的条码，判断是否在数据库中有NG记录
        /// </summary>
        public static bool SelectData(string Code)
        {
            string constr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + System.Environment.CurrentDirectory + "\\Config\\NGCode\\CheckCode.mdb;";
            OleDbConnection con = new OleDbConnection(constr);
            con.Open();

            //string sql2 = "SELECT * FROM NGCode";
            string sql2 = "SELECT * FROM NGCode WHERE 条码='" + Code + "'";

            OleDbCommand cmd2 = new OleDbCommand(sql2, con);
            OleDbDataReader ngcodeinform = cmd2.ExecuteReader();
            string ngnum = null;
            while (ngcodeinform.Read())
            {
                //Account.Text = Convert.ToString(ngcodeinform["条码"]);
                ngnum = Convert.ToString(ngcodeinform["NG次数"]);
            }

            ngcodeinform.Close();
            con.Close();
            if (String.IsNullOrEmpty(ngnum))
            {
                //con.Open();
                //string sql3 = " INSERT INTO NGCode(条码, NG次数) VALUES('" + Code + "', " + 1 + ")";
                //OleDbCommand cmd3 = new OleDbCommand(sql3, con);
                //cmd3.ExecuteNonQuery();
                //con.Close();
                return true;
            }
            else
            {
                int num = Convert.ToInt32(ngnum);
                if (num > 0)
                {
                    con.Open();
                    string sql4 = "DELETE FROM NGCode WHERE 条码='" + Code + "'";
                    OleDbCommand cmd4 = new OleDbCommand(sql4, con);
                    cmd4.ExecuteNonQuery();
                    con.Close();
                    return false;

                }
                else
                {
                    return true;
                }
            }
        }
    }
}