using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO.Ports;

namespace DB
{

    public partial class Form1 : Form
    {
        OleDbConnection conn;
        OleDbCommand command;

        bool addMode = false;

        private System.Timers.Timer timer1;
        private StringBuilder receivedB = new StringBuilder();

        public Form1()
        {
            InitializeComponent();
            // open the serial port for communication
            serialPort1.Open();
            timer1 = new System.Timers.Timer(200);
            timer1.Elapsed += new System.Timers.ElapsedEventHandler(timer1_Elapsed);
            GC.KeepAlive(timer1);
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            // connect to the database
            string conString =
               @"Provider=Microsoft.JET.OLEDB.4.0;"
               + @"data source=c:\db\test.mdb";

            // create an open the connection          
            conn = new OleDbConnection(conString);
            command = conn.CreateCommand();

            this.Invoke(new EventHandler(populate_datagrid));
        }

        private void populate_datagrid(object sender, EventArgs e)
        {
            // clear the grids data source
            dataGridView1.DataSource = null;

            // create new dataset
            DataSet ds = new DataSet();

            OleDbDataAdapter adapter;

            // open the connection
            conn.Open();

            // run the query
            command.CommandText = "select id,name,inout  from badges";
            adapter = new OleDbDataAdapter(command);
            adapter.Fill(ds);

            // close the connection
            conn.Close();

            // set the grid's data source
            dataGridView1.DataSource = ds.Tables[0];
            //dataGridView1.Columns[0].HeaderText = "ID";
            dataGridView1.Columns[0].Width = 100;
            dataGridView1.Columns[1].Width = 198;
            dataGridView1.Columns[2].Width = 30;
        }

        // color each row, red if checked out, green if checked in
        private void dataGridView1_RowsAdded(object sender, DataGridViewRowsAddedEventArgs e)
        {
            string inout;

            if (dataGridView1.RowCount != 0)
            {
                foreach (DataGridViewRow r1 in dataGridView1.Rows)
                {
                    inout = r1.Cells[2].Value.ToString();

                    foreach (DataGridViewCell cell in r1.Cells)
                    {
                        if (inout == "True")
                            // green
                            cell.Style.BackColor = Color.FromArgb(223,255,217);
                        else
                            // red
                            cell.Style.BackColor = Color.FromArgb(255,217,217);

                        cell.Style.Font = new Font("Tahoma", 8, FontStyle.Bold);
                    }
                }
            }
            dataGridView1.Invalidate(); 
        }

        private void serialPort1_DataReceived(object sender, SerialDataReceivedEventArgs e)
        {
            this.Invoke(new EventHandler(AddReceive));
        }

        // read bytes off the serial port, stick them in the stringbuilder object
        private void AddReceive(object s, EventArgs e)
        {
            string st = serialPort1.ReadExisting();
            receivedB.Append(st);

            timer1.Interval = 250;
            timer1.Start();
        }

        void timer1_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
        {
            timer1.Stop();
            this.Invoke(new EventHandler(ReadData));
        }

        // write a line to the debug window
        private void write_DebugWindow(string data, bool startblock)
        {
            if (startblock)
            {
                richTextBox1.Text = "------------\n" + richTextBox1.Text;
            }
            richTextBox1.Text = data + "\n" + richTextBox1.Text;
        }

        // when the read timer expires, stick the received data into a string and process it
        private void ReadData(object s, EventArgs e)
        {
            string receivedData = receivedB.ToString();
            int j = receivedB.Length;
            receivedB.Remove(0,j);

            write_DebugWindow("Scanned: " + receivedData,true);

            if (addMode)
            {
                Form2.setBadgeID(receivedData);
            }
            else
            {

                conn.Open();

                OleDbCommand update_inout;
                update_inout = conn.CreateCommand();

                command.CommandText = "select name,inout from badges where id=\"" + receivedData + "\"";
                string name = (string)command.ExecuteScalar();
                OleDbDataReader reader = command.ExecuteReader();
                reader.Read();

                if (name != null)
                {
                    string inout = reader[1].ToString();
                    write_DebugWindow("Badge " + reader[0], false);
                    if (inout == "True")
                        inout = "False";
                    else
                        inout = "True";

                    update_inout.CommandText = "update badges set inout=" + inout + " where id=\"" + receivedData + "\"";
                    update_inout.ExecuteNonQuery();
                }
                else
                {
                    write_DebugWindow("Badge not found", false);
                }

                reader.Close();
                conn.Close();
                this.Invoke(new EventHandler(populate_datagrid));
            }
        }

        private void change_addmode(object sender, EventArgs e)
        {
            addMode = false;
            write_DebugWindow("Leaving add mode",true);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            this.Invoke(new EventHandler(populate_datagrid));
        }

        private void button3_Click(object sender, EventArgs e)
        {
            int open = 0;
            foreach (Form a in Application.OpenForms)
            {
                if (a is Form2)
                {
                    open = 1;
                    break;
                }
            }
            if (open == 0)
            {
                write_DebugWindow("Entering add mode",true);
                Form2 frm = new Form2();
                addMode = true;
                frm.Closing += new CancelEventHandler(populate_datagrid);
                frm.Closing += new CancelEventHandler(change_addmode);
                frm.Show();
            }
        }

        private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            // grab cell information
            string newdata = dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
            string colid = dataGridView1.Rows[e.RowIndex].Cells["ID"].Value.ToString();
            string colname = dataGridView1.Columns[e.ColumnIndex].Name.ToString();
            string name = dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString();

            //ds = new DataSet();

            // connect to db
            conn.Open();
            // if the column modified is inout remove quotes
            if (colname == "inout")
                command.CommandText = "update badges set " + colname + "=" + newdata + " where id=\"" + colid + "\"";
            else if (colname == "id")
                command.CommandText = "update badges set id=\"" + newdata + "\" where name=\"" + name + "\"";
            else
                command.CommandText = "update badges set " + colname + "=\"" + newdata + "\" where id=\"" + colid + "\"";

            write_DebugWindow("Modified badge " + colid,true);

            // execute the db update
            command.ExecuteNonQuery();

            conn.Close();

            this.Invoke(new EventHandler(populate_datagrid));
        }

        private void button1_Click(object sender, EventArgs e)
        {
            int rowsdeleted = 0;
            conn.Open();

            foreach (DataGridViewRow rows in dataGridView1.Rows) {
                if (rows.Selected)
                {
                    command.CommandText = "delete from badges where id=\"" + rows.Cells[0].Value.ToString() + "\"";
                    command.ExecuteNonQuery();
                    rowsdeleted += 1;
                }
            }

            write_DebugWindow("Deleted " + rowsdeleted + " badges",true);

            conn.Close();

            this.Invoke(new EventHandler(populate_datagrid));
        }
    }
}