﻿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;

namespace MyReg
{
    public partial class NewPatient : Form
    {
        public OleDbConnection myConn;
        public string patientfam ;
        public string patientnam;
        public string patientlnam ;
        public string p_dat1;
        public string p_dat2;
        
        public NewPatient(string data)
        {
            
            InitializeComponent();
            label5.Visible = false;
            //label6.Text = data;
            
            string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=registration.mdb";

            try
            {
               myConn = new OleDbConnection(@ConnStr);
                //myConn.ConnectionString = ConnStr;
                myConn.Open();
                string queryDataGrid = "SELECT Patients.id_patient, Patients.fname_patient, Patients.sname_patient, Patients.lname_patient, Patients.birthdate, Patients.workplace_patient, Patients.post_patient, Patients.telefone_patient, ExplanatorySurvey.[flyurografia_onko_result], ExplanatorySurvey.[flyurografia_onko_date] FROM Patients INNER JOIN ExplanatorySurvey ON Patients.id_explanatorysurvey = ExplanatorySurvey.[id_explanatorysurvey]";

                OleDbCommand myquery2 = new OleDbCommand();
                DataTable datat = null;
                myquery2.Connection = null;
                OleDbDataAdapter adapter1 = null;
                myquery2.CommandText = "SELECT Receptionists.id_receptionist, Receptionists.id_receptionist, Receptionists.fname_receptionist, Receptionists.sname_receptionist, Receptionists.lname_receptionist FROM Receptionists INNER JOIN LogPass ON Receptionists.id_receptionist = LogPass.[user] where LogPass.login='" + data + "'";
                myquery2.Connection = myConn;
                datat = new DataTable();
                adapter1 = new OleDbDataAdapter(myquery2);
                adapter1.Fill(datat);
                DataRowCollection drct = datat.Rows;
                p_dat1 = datat.Rows[0]["id_receptionist"].ToString();
                label6.Text = datat.Rows[0]["fname_receptionist"].ToString() + " " + datat.Rows[0]["sname_receptionist"].ToString();
                
                FillDataGrid(queryDataGrid);
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return;
            }
        }
        public void FillDataGrid(string queryDataGrid)
        {
            OleDbCommand myQuery = new OleDbCommand();
            DataTable data = null;
            dataGridView1.DataSource = null;
            myQuery.Connection = null;
            OleDbDataAdapter adapter = null;

            dataGridView1.Columns.Clear();

            myQuery.CommandText = queryDataGrid;
            myQuery.Connection = myConn;
            data = new DataTable();
            adapter = new OleDbDataAdapter(myQuery);
            adapter.Fill(data);
            //p_dat2 = data.Rows[0]["id_patient"].ToString();
            if (data.Rows.Count == 0)
            {
                label5.Visible = true;
                label5.Text = "Результаты не найдены";
            }
            else
            {
                dataGridView1.DataSource = data;
                dataGridView1.Columns[0].Visible = false;
                dataGridView1.Columns[1].HeaderText = "Фамилия";
                dataGridView1.Columns[2].HeaderText = "Имя";
                dataGridView1.Columns[3].HeaderText = "Отчество";
                dataGridView1.Columns[4].HeaderText = "Дата рождения";
                dataGridView1.Columns[5].HeaderText = "Место работы";
                dataGridView1.Columns[6].HeaderText = "Должность";
                dataGridView1.Columns[7].HeaderText = "Телефон";
                dataGridView1.Columns[8].HeaderText = "Флюрография/Онко результат";
                dataGridView1.Columns[9].HeaderText = "Флюрография/Онко дата";
                dataGridView1.Columns[1].Width = 100;
                dataGridView1.Columns[2].Width = 100;
                dataGridView1.Columns[3].Width = 100;
                dataGridView1.Columns[4].Width = 100;
                dataGridView1.Columns[5].Width = 100;
                dataGridView1.Columns[6].Width = 100;
                dataGridView1.Columns[7].Width = 100;
                dataGridView1.Columns[8].Width = 100;
                dataGridView1.Columns[9].Width = 100;
            }
        }

        private void OnClosing(object sender, FormClosedEventArgs e) 
        { 
            //if (Application.OpenForms.Count == 0) 
                Application.Exit(); 
        }



        private void button1_Click(object sender, EventArgs e)
        {
           
        }

        private void button4_Click(object sender, EventArgs e)
        {
             patientfam = textBox1.Text;
             patientnam = textBox2.Text;
             patientlnam = textBox3.Text;
             string query = "SELECT Patients.id_patient, Patients.fname_patient, Patients.sname_patient, Patients.lname_patient, Patients.birthdate,  Patients.workplace_patient, Patients.post_patient, Patients.telefone_patient, ExplanatorySurvey.[flyurografia_onko_result], ExplanatorySurvey.[flyurografia_onko_date] FROM Patients INNER JOIN ExplanatorySurvey ON Patients.id_explanatorysurvey = ExplanatorySurvey.[id_explanatorysurvey] where Patients.fname_patient='" + patientfam + "' and Patients.sname_patient='" + patientnam + "' and Patients.lname_patient='" + patientlnam + "'";
            FillDataGrid(query);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            Add_new_patient adding = new Add_new_patient(textBox1.Text, textBox2.Text, textBox3.Text);
            if (adding.ShowDialog() == DialogResult.OK)
            {
                if (((myConn.State & System.Data.ConnectionState.Open) 
                        != System.Data.ConnectionState.Open)) {
                myConn.Open();
            }
            int val = 0;
                //OleDbCommand cmd = myConn.CreateCommand();
                //cmd.CommandText = "insert into ExplanatorySurvey ([id_explanatorysurvey],[flyurografia_onko_date],[flyurografia_onko_result]) values (AUTO_INCREMENT,'" + adding.fluonkodate + "','" + adding.fluonkoresult + "');";
            OleDbCommand cmd = new OleDbCommand("insert into ExplanatorySurvey ([flyurografia_onko_date],[flyurografia_onko_result]) VALUES ('" + adding.fluonkodate + "','" + adding.fluonkoresult + "')", myConn);
            //cmd.CommandType = CommandType.Text;
            val = cmd.ExecuteNonQuery();
            
            myConn.Close();
            myConn.Open();
            DataSet ds = new DataSet();
            DateTime t = Convert.ToDateTime(adding.fluonkodate);
            String sss = t.ToShortDateString();
            String query = "SELECT *  FROM ExplanatorySurvey where flyurografia_onko_date = Format('{0}', 'dd-mm-yy')  AND  flyurografia_onko_result='{1}';";
            OleDbCommand adapter4 = new OleDbCommand(String.Format(query, sss, adding.fluonkoresult), myConn);
            OleDbDataReader dataReader = adapter4.ExecuteReader();
            Int32 id = -1;
            if (dataReader.Read())
            {
                int idIndex = dataReader.GetOrdinal("id_explanatorysurvey");
                id = dataReader.GetInt32(idIndex);
            }
                   
            
            OleDbCommand cmd1 = new OleDbCommand("insert into Patients ([fname_patient],[sname_patient],[lname_patient],[birthdate],[workplace_patient],[post_patient],[telefone_patient],[id_attendingdoctor],[id_tretmentreception],[id_explanatorysurvey]) VALUES ('" + adding.fname + "','" + adding.sname + "','" + adding.lname + "','" + adding.birth + "','" + adding.workplace + "','" + adding.post + "','" + adding.tel + "',0,0," + id.ToString() + ")", myConn);
            //cmd.CommandType = CommandType.Text;
            val=cmd1.ExecuteNonQuery();

}
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if ((textBox1.Text == "") || (textBox2.Text == "") || (textBox3.Text==""))
            {
                MessageBox.Show("Сначала введите все значения в поля ввода!", "");
            }
            else
            {
                String query = "SELECT id_explanatorysurvey  FROM Patients WHERE fname_patient='" + textBox1.Text + "' and sname_patient='" + textBox2.Text + "' and lname_patient='" + textBox3.Text + "'";
                OleDbCommand adapter5 = new OleDbCommand(query, myConn);
                OleDbDataReader dataReader = adapter5.ExecuteReader();
                Int32 id = -1;
                if (dataReader.Read())
                {
                    int idIndex = dataReader.GetOrdinal("id_explanatorysurvey");
                    id = dataReader.GetInt32(idIndex);
                };

                int val1 = 0;
                //OleDbCommand cmd = myConn.CreateCommand();
                //cmd.CommandText = "insert into ExplanatorySurvey ([id_explanatorysurvey],[flyurografia_onko_date],[flyurografia_onko_result]) values (AUTO_INCREMENT,'" + adding.fluonkodate + "','" + adding.fluonkoresult + "');";
                OleDbCommand cmd2 = new OleDbCommand("DELETE FROM Patients WHERE fname_patient='" + textBox1.Text + "' and sname_patient='" + textBox2.Text + "' and lname_patient='" + textBox3.Text + "'", myConn);
                //cmd.CommandType = CommandType.Text;
                val1 = cmd2.ExecuteNonQuery();







                OleDbCommand cmd3 = new OleDbCommand("DELETE FROM ExplanatorySurvey WHERE id_explanatorysurvey=" + id.ToString(), myConn);
                //cmd.CommandType = CommandType.Text;
                val1 = cmd3.ExecuteNonQuery();
                MessageBox.Show("Запись удалена!", "");
            }
            //myConn.Close();
            //myConn.Open();
        }

        private void button5_Click(object sender, EventArgs e)
        {
            if ((textBox1.Text == "") || (textBox2.Text == "") || (textBox3.Text==""))
            {
                MessageBox.Show("Сначала введите все значения в поля ввода!", "Ок");
            }
            else
            {
                string query = "SELECT id_patient FROM Patients where fname_patient='" + textBox1.Text + "' and sname_patient='" + textBox2.Text + "' and lname_patient='" + textBox3.Text + "'";
                OleDbCommand myQuery6 = new OleDbCommand();
                DataTable data6 = null;
                myQuery6.Connection = null;
                OleDbDataAdapter adapter6 = null;
                myQuery6.CommandText = query;
                myQuery6.Connection = myConn;
                data6 = new DataTable();
                adapter6 = new OleDbDataAdapter(myQuery6);
                adapter6.Fill(data6);
                p_dat2 = data6.Rows[0]["id_patient"].ToString();

                NaznDoctor nf = new NaznDoctor(p_dat1, p_dat2);

                if (nf.ShowDialog() == DialogResult.OK)
                {
                }
            }
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

    }
}