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