public Show_Schedualing_lecturer(Lecturer lec)
        {
            InitializeComponent();
            this.lec  = lec;
            name.Text = "Hello " + lec.getFirstName().ToString() + "!";
            SqlDataAdapter sda  = new SqlDataAdapter("SELECT ID, day, courseName, class, timeStart, timeEnd FROM schedualing WHERE ID LIKE'" + lec.getID().ToString() + "%'", cn);
            DataTable      data = new DataTable();

            sda.Fill(data);
            dataGridView1.DataSource = data;
        }
        //insert user to the table sched1_lecturer_A - lecturer that doesn't exsit in the table
        public bool insertToSchecd1(Lecturer lect)
        {
            string constring = "Data Source=p17server.database.windows.net;Initial Catalog=P17DATABASE;Persist Security Info=True;User ID=P17;Password=Hadas@2017";

            int index;
            int nextIndex = 0;

            try
            {
                string q = "select MAX(num) from sched1_lecturer_A";
                cn  = new SqlConnection(constring);
                cmd = new SqlCommand(q, cn);
                cn.Open();
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    index     = Int32.Parse(dr[0].ToString()) + 1;
                    nextIndex = index; //in order to get index out of the red() loop
                }
                dr.Close();
                cn.Close();
            }
            catch (Exception exe) { MessageBox.Show(exe.Message); }

            try
            {
                cn = new SqlConnection(constring);
                cn.Open();

                string q = "insert into [sched1_lecturer_A]  (Num,ID, lecturer,day) values(@Num , @ID, @lecturer , @Day );";
                cmd = new SqlCommand(q, cn);
                cmd.Parameters.AddWithValue("@Num", nextIndex);
                cmd.Parameters.AddWithValue("@ID", lect.getID());
                cmd.Parameters.AddWithValue("@lecturer", lect.mergetoOne(lect.getFirstName(), lect.getLastName()));
                cmd.Parameters.AddWithValue("@Day", this.course.getDayL());

                //cmd.CommandText = "SELECT SCOPE_IDENTITY()";
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                //dr = cmd.ExecuteReader();

                cn.Close();
                dr.Close();
                return(true);
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }

            return(false);
        }
        private void button_SUBMIT_Click(object sender, EventArgs e)
        {
            bool alreadySigned = false;

            if (String.IsNullOrEmpty(textBox_hoursAmount.Text))
            {
                MessageBox.Show("You dont enter your work hours amount");
                this.Hide();
                A_Insert_work_hours_for_Lec.CURRENTA_Insert_work_hours_for_Lec.Show();
            }

            SqlConnection cn = new SqlConnection("Data Source=p17server.database.windows.net;Initial Catalog=P17DATABASE;Persist Security Info=True;User ID=P17;Password=Hadas@2017");

            cn.Open();
            SqlCommand cmd0 = new SqlCommand("SELECT MAX (Num) FROM staffHours", cn);

            dr = cmd0.ExecuteReader();
            while (dr.Read())
            {
                //getting the last Num in table - in order to know to set next primary key

                followNum = dr[0].ToString();

                index     = Int32.Parse(followNum) + 1;
                nextIndex = index; //in order to get the number outside the loop
            }
            dr.Close();

            List <String> dept = new List <string>();

            SqlCommand cmd1 = new SqlCommand("SELECT * FROM UsersDepartment", cn);

            dr = cmd1.ExecuteReader();
            while (dr.Read())
            {
                if (dr["ID"].ToString() == lec.getID().ToString())
                {
                    dept.Add(dr["IDdepartment"].ToString());
                }
            }
            dr.Close();


            if (dept.Count() == 0)
            {
                MessageBox.Show("You'r not associated with a department \n You'r not allowed to insert work hours");
                textBox_hoursAmount.Clear();
            }
            else
            {
                SqlCommand cmd2 = new SqlCommand("SELECT * FROM staffHours", cn);
                dr = cmd2.ExecuteReader();
                while (dr.Read())
                {
                    if (dr["ID"].ToString() == lec.getID().ToString())
                    {
                        if (Int32.Parse(dr["month"].ToString()) == numericUpDown_month.Value)
                        {
                            if (Int32.Parse(dr["year"].ToString()) == numericUpDown_year.Value)
                            {
                                alreadySigned = true;
                                break;
                            }
                        }
                    }
                }
                dr.Close();

                if (!alreadySigned)
                {
                    cmd = new SqlCommand("INSERT INTO staffHours(Num,ID,name,Type,IDdepartment,month,year,hoursAmount) VALUES (@Num,@ID,@name,@Type,@IDdepartment,@month,@year,@hoursAmount)", cn);
                    cmd.Parameters.AddWithValue("@Num", nextIndex);
                    cmd.Parameters.AddWithValue("@ID", lec.getID().ToString());
                    cmd.Parameters.AddWithValue("@name", lec.getFirstName().ToString() + " " + lec.getLastName().ToString());
                    cmd.Parameters.AddWithValue("@Type", 2);
                    cmd.Parameters.AddWithValue("@IDdepartment", dept[0]);
                    cmd.Parameters.AddWithValue("@month", numericUpDown_month.Value);
                    cmd.Parameters.AddWithValue("@year", numericUpDown_year.Value);
                    cmd.Parameters.AddWithValue("@hoursAmount", textBox_hoursAmount.Text);

                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();


                    MessageBox.Show("Your work hours added succesfully!");
                    this.Close();
                    A_LecturerMenu.CIRRENTA_LecturerMenu.Show();
                }

                else
                {
                    if (alreadySigned)
                    {
                        MessageBox.Show("You have already enterd your hours for: " + numericUpDown_month.Value + " - " + numericUpDown_year.Value);
                    }
                    textBox_hoursAmount.Clear();
                    MessageBox.Show(" *** PAY ATTENTION: insertion work hours FAILED! ***");
                }
            }



            cn.Close();
        }
 public A_Insert_work_hours_for_Lec(Lecturer lec)
 {
     InitializeComponent();
     this.lec  = lec;
     name.Text = "Hello " + lec.getFirstName().ToString() + "!";
 }
        // to check the available hours of the lecturer
        public void findFreeHours(Lecturer lec)
        {
            string constring = "Data Source=p17server.database.windows.net;Initial Catalog=P17DATABASE;Persist Security Info=True;User ID=P17;Password=Hadas@2017";
            string Query     = "SELECT * from sched1_lecturer_A WHERE lecturer ='" + lec.mergetoOne(lec.getFirstName(), lec.getLastName()) + "' and day = '" + this.comboBox3.SelectedItem + "'";

            cn  = new SqlConnection(constring);
            cmd = new SqlCommand(Query, cn);
            cn.Open();
            dr = cmd.ExecuteReader();

            List <int> availableHours = new List <int>();

            while (dr.Read())
            {
                if (dr["8"].ToString() == "")
                {
                    availableHours.Add(8);
                }
                if (dr["9"].ToString() == "")
                {
                    availableHours.Add(9);
                }
                if (dr["10"].ToString() == "")
                {
                    availableHours.Add(10);
                }
                if (dr["11"].ToString() == "")
                {
                    availableHours.Add(11);
                }
                if (dr["12"].ToString() == "")
                {
                    availableHours.Add(12);
                }
                if (dr["13"].ToString() == "")
                {
                    availableHours.Add(13);
                }
                if (dr["14"].ToString() == "")
                {
                    availableHours.Add(14);
                }
                if (dr["15"].ToString() == "")
                {
                    availableHours.Add(15);
                }
                if (dr["16"].ToString() == "")
                {
                    availableHours.Add(16);
                }
                if (dr["17"].ToString() == "")
                {
                    availableHours.Add(17);
                }
                if (dr["18"].ToString() == "")
                {
                    availableHours.Add(18);
                }
                if (dr["19"].ToString() == "")
                {
                    availableHours.Add(19);
                }
                if (dr["20"].ToString() == "")
                {
                    availableHours.Add(20);
                }
                if (dr["21"].ToString() == "")
                {
                    availableHours.Add(21);
                }
                break;
            }
            dr.Close();
            cn.Close();
            //int flag = 0;

            //List<int> lectHours = new List<int>();

            for (int i = 0; i < availableHours.Count() - 1; i++)
            {
                if (availableHours[i] == availableHours[i + 1] - 1)
                {
                    lectHours.Add(availableHours[i]);
                    if (lectHours.Contains(availableHours[i + 1]))
                    {
                        continue;
                    }
                    else
                    {
                        lectHours.Add(availableHours[i + 1]);
                    }
                    //flag++;
                    this.countHours++;
                }
            }
        }
        private void button1_Click(object sender, EventArgs e)
        {
            //to clear the data grid
            this.dataGridView1.DataSource = null;
            this.dataGridView1.Rows.Clear();
            try
            {
                cn.Open();
                cmd = new SqlCommand("select U.ID , U.FirstName , U.LastName , U.Departments from Users U where U.ID = '" + this.comboBox1.Text + "'", cn);
                SqlDataReader dr;
                dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    // keep the details of the Lecturer and the department
                    this.lec.setID(Int32.Parse(dr["ID"].ToString()));
                    this.lec.setFirstName(dr["FirstName"].ToString());
                    this.lec.setLastName(dr["LastName"].ToString());
                    this.lec.setdeparName(dr["Departments"].ToString());
                }

                dr.Close();
                cn.Close();
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }

            // now we check the courses of the Lecturer in StaffCourses
            int           sizeArr = 0;
            List <string> list    = new List <string>();

            try
            {
                cn.Open();
                cmd = new SqlCommand("select C.Name from Users U ,UsersDepartment UD ,Departments D ,Courses C,StaffCourses SC where U.ID=UD.ID and UD.IDdepartment=D.IDdepartment and U.ID=SC.ID and C.IDcourse=SC.IDcourse and U.ID ='" + this.lec.getID() + "' and Type = 2", cn);
                SqlDataReader dr;
                dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    // check how many courses
                    sizeArr++;
                    list.Add(dr["Name"].ToString());
                }

                dr.Close();
                cn.Close();
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }


            data = new DataTable();
            // for fill the table
            SqlDataAdapter adapter1 = new SqlDataAdapter(cmd);

            adapter1.Fill(data);   // fill the new data table
                                   //to show specific course with the detail that secretary choose
            DataView dav = new DataView(data);

            //dav.RowFilter = "Convert(ID, 'System.String') LIKE '%" + this.lec.getID() + "%'";
            //dataGridView1.DataSource = dav;

            if (list.Count == 0)
            {
                dataGridView1.Rows.Add(lec.getID().ToString(), lec.getFirstName(), lec.getLastName(), lec.getdeparName(), "");
            }
            else
            {
                for (int i = 0; i < list.Count; i++)
                {
                    dataGridView1.Rows.Add(lec.getID().ToString(), lec.getFirstName(), lec.getLastName(), lec.getdeparName(), list[i]);
                }
            }
        }