Beispiel #1
0
        //insert user to the table sched2_TA_A - lecturer that doesn't exsit in the table
        public bool insertToSchecd2(TeachingAssitant ta)
        {
            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 sched2_TA_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 [sched2_TA_A]  (Num,ID, TA,day) values(@Num , @ID, @TA , @day );";
                cmd = new SqlCommand(q, cn);
                cmd.Parameters.AddWithValue("@Num", nextIndex);
                cmd.Parameters.AddWithValue("@ID", ta.getID());
                cmd.Parameters.AddWithValue("@TA", ta.mergetoOne(ta.getFirstName(), ta.getLastName()));
                cmd.Parameters.AddWithValue("@day", this.course.getDayL());

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

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

            return(false);
        }
Beispiel #2
0
        // to check the available hours of the TA and save then in variable List taHours
        public void findFreeHours(TeachingAssitant ta)
        {
            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 sched2_TA_A WHERE TA ='" + ta.mergetoOne(ta.getFirstName(), ta.getLastName()) + "' and day = '" + this.cmbDay.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)
                {
                    taHours.Add(availableHours[i]);
                    if (taHours.Contains(availableHours[i + 1]))
                    {
                        continue;
                    }
                    else
                    {
                        taHours.Add(availableHours[i + 1]);
                    }
                    //flag++;
                    this.countHours++;
                }
            }
        }
        private void button1_search_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.ta.setID(Int32.Parse(dr["ID"].ToString()));
                    this.ta.setFirstName(dr["FirstName"].ToString());
                    this.ta.setLastName(dr["LastName"].ToString());
                    this.ta.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
            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.ta.getID() + "' and Type = 3", cn);
                SqlDataReader dr;
                dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    // check how many courses
                    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(ta.getID().ToString(), ta.getFirstName(), ta.getLastName(), ta.getdeparName(), "");
            }
            else
            {
                for (int i = 0; i < list.Count; i++)
                {
                    dataGridView1.Rows.Add(ta.getID().ToString(), ta.getFirstName(), ta.getLastName(), ta.getdeparName(), list[i]);
                }
            }
        }
        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_TA.currentA_Insert_work_hours_for_TA.Show(); //form = new A_Insert_work_hours_for_TA(ta);
                // form.ShowDialog();
            }

            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() == ta.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() == ta.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", ta.getID().ToString());
                    cmd.Parameters.AddWithValue("@name", ta.getFirstName().ToString() + " " + ta.getLastName().ToString());
                    cmd.Parameters.AddWithValue("@Type", 3);
                    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_TeachingAssistant back = new A_TeachingAssistant(ta);
                    this.Hide();
                    back.ShowDialog();
                }



                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();
        }