public bool Update(TOClass c)
{
bool i = false;
try
{
string sql = "update tbl_class set class_day = '" + c.Class_day + "', class_timesPerWeek = " + c.Class_timePerWeek + ", class_hourStarts = '" + c.Class_hourStarts + "', class_hourEnds = '" + c.Class_hourEnds + "', class_duration = " + c.Class_duration + ", class_type = '" + c.Type + "', user_id = " + c.User_id + " where class_id = " + c.Class_id + ";";
con = ConnectionFactory.Connection();
con.Open();
MySqlCommand cmdUpdate = new MySqlCommand(sql, con);
cmdUpdate.ExecuteNonQuery();
i = true;
con.Close();
}
catch (MySqlException e)
{
throw new Exception(e.Message);
}
return(i);
}
public bool CheckCTR(string ctr)
{
bool i = false;
TOClass _class = new TOClass();
string check = "select * from tbl_student where student_ctr = '" + ctr + "';";
con = ConnectionFactory.Connection();
MySqlCommand cmdCheckCTR = new MySqlCommand(check, con);
con.Open();
MySqlDataReader dtReaderCheckCTR = cmdCheckCTR.ExecuteReader();
if (dtReaderCheckCTR.Read())
{
i = false;
}
else
{
i = true;
}
con.Close();
return(i);
}
public bool CheckVacancys(int id)
{
bool i = false;
TOClass _class = new TOClass();
string vacancy_count = "select class_vacancys from tbl_class where class_id = " + id + ";";
con = ConnectionFactory.Connection();
MySqlCommand cmdVacancyCount = new MySqlCommand(vacancy_count, con);
con.Open();
MySqlDataReader dtReaderVacancyCount = cmdVacancyCount.ExecuteReader();
if (dtReaderVacancyCount.Read())
{
_class.Class_vacancys = dtReaderVacancyCount.GetInt16("class_vacancys");
}
con.Close();
if (_class.Class_vacancys >= 1)
{
i = true;
}
else
{
i = false;
}
return(i);
}
public List <TOClass> LoadClasses()
{
List <TOClass> i = new List <TOClass>();
try
{
string sql = "select * from tbl_class;";
con = ConnectionFactory.Connection();
MySqlCommand cmd = new MySqlCommand(sql, con);
con.Open();
MySqlDataReader dtreader = cmd.ExecuteReader();
while (dtreader.Read())//If there's any data.
{
TOClass x = new TOClass();
x.Class_id = dtreader.GetInt16("class_id");
x.Class_day = dtreader.GetString("class_day");
x.Class_timePerWeek = dtreader.GetInt16("class_timesPerWeek");
x.Class_hourStarts = dtreader.GetString("class_hourStarts");
x.Class_hourEnds = dtreader.GetString("class_hourEnds");
x.Class_vacancys = dtreader.GetInt16("class_vacancys");
x.Class_duration = dtreader.GetInt16("class_duration");
x.User_id = dtreader.GetInt16("user_id");
x.Class_TotalHours = ("Das " + (dtreader.GetString("class_hourStarts")) + " às " + (dtreader.GetString("class_hourEnds")));
x.Type = dtreader.GetString("class_type");
MySqlConnection connection = new MySqlConnection();
connection = ConnectionFactory.Connection();
string getTeacherName = "select user_name from tbl_user where user_id = " + x.User_id + ";";
connection.Open();
MySqlCommand cmdGet = new MySqlCommand(getTeacherName, connection);
MySqlDataReader dtReaderGet = cmdGet.ExecuteReader();
if (dtReaderGet.Read())
{
x.Teacher_name = dtReaderGet.GetString("user_name");
}
connection.Close();
i.Add(x);
}
con.Close();
}
catch (MySqlException e)
{
throw new Exception(e.Message);
}
return(i);
}
public bool Registration(TOClass _class)
{
bool i = false;
try
{
string sql = @"insert into tbl_class(class_day, class_timesPerWeek,class_hourStarts, class_hourEnds, class_duration, class_vacancys, class_type, user_id) values('"
+ _class.Class_day + "', " + _class.Class_timePerWeek + ", '" + _class.Class_hourStarts + "', '" + _class.Class_hourEnds + "', " + _class.Class_duration + ", "
+ _class.Class_vacancys + ", '" + _class.Type + "', " + _class.User_id + ");";
con = ConnectionFactory.Connection();
MySqlCommand cmd = new MySqlCommand(sql, con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
i = true;
}
catch (MySqlException e)
{
throw new Exception(e.Message);
}
finally
{
con.Close();
}
return(i);
}
private void btnSaturdayVacancys_Click(object sender, RoutedEventArgs e)
{
DAOClass dao = new DAOClass();
if (dao.VacancyCount("sábado") == 0)
{
Xceed.Wpf.Toolkit.MessageBox.Show("Não há vagas disponíveis.", "MC Sales", MessageBoxButton.OK, MessageBoxImage.Information, MessageBoxResult.OK);
tblStudents.ItemsSource = null;
lblCount_Vacancy.Content = "0";
lblCount.Content = "10";
lblNoVancacys.Visibility = Visibility.Visible;
}
else
{
//class info.
_class = dao.LoadQuickMenu("sábado");
lblClass_Time.Content = _class.Class_TotalHours;
//teacher info.
TOUser u = new TOUser();
DAOUser daoU = new DAOUser();
u = daoU.Selection(_class.User_id);
lblTeacher_Name.Content = u.User_name;
btnViewCurrentClass.Visibility = Visibility.Visible;
btnResetTable.Visibility = Visibility.Visible;
LoadTable(_class.Class_id);
lblDay.Visibility = Visibility.Visible;
lblDay.Content = "Sábado";
}
}
public void LoadScreen()
{
DAOClass _class = new DAOClass();
TOClass c = new TOClass();
if (rdbtn_en.IsChecked == true)
{
c.Type = "Inglês";
tblClass.ItemsSource = _class.LoadClasses(c.Type);
}
else if (rdbtn_adm.IsChecked == true)
{
c.Type = "Administração";
tblClass.ItemsSource = _class.LoadClasses(c.Type);
}
else if (rdbtn_game.IsChecked == true)
{
c.Type = "Game";
tblClass.ItemsSource = _class.LoadClasses(c.Type);
}
else if (rdbtn_hard.IsChecked == true)
{
c.Type = "Hardware";
tblClass.ItemsSource = _class.LoadClasses(c.Type);
}
else if (rdbtn_info.IsChecked == true)
{
c.Type = "Informática";
tblClass.ItemsSource = _class.LoadClasses(c.Type);
}
else if (rdbtn_int.IsChecked == true)
{
c.Type = "Interativo";
tblClass.ItemsSource = _class.LoadClasses(c.Type);
}
else if (rdbtn_all.IsChecked == true)
{
tblClass.ItemsSource = _class.LoadClasses();
}
//loads the table.
tblClass.CanUserAddRows = false;
tblClass.IsReadOnly = true;
tblClass.AutoGenerateColumns = false;
}
public TOClass Selection(int id)
{
TOClass i = new TOClass();
try
{
string sql = "select * from tbl_class where class_id = " + id + ";";
con = ConnectionFactory.Connection();
MySqlCommand cmd = new MySqlCommand(sql, con);
con.Open();
MySqlDataReader dtreader = cmd.ExecuteReader();
while (dtreader.Read())//If there's any data.
{
i.Class_id = dtreader.GetInt16("class_id");
i.Class_day = dtreader.GetString("class_day");
i.Class_timePerWeek = dtreader.GetInt16("class_timesPerWeek");
i.Class_hourStarts = dtreader.GetString("class_hourStarts");
i.Class_hourEnds = dtreader.GetString("class_hourEnds");
i.Class_vacancys = dtreader.GetInt16("class_vacancys");
i.Class_duration = dtreader.GetInt16("class_duration");
i.User_id = dtreader.GetInt16("user_id");
i.Class_TotalHours = ("Das " + (dtreader.GetString("class_hourStarts")) + " às " + (dtreader.GetString("class_hourEnds")));
i.Type = dtreader.GetString("class_type");
}
con.Close();
}
catch (MySqlException e)
{
throw new Exception(e.Message);
}
return(i);
}
public bool Registration(TOStudent student, TOResponsable resp)
{
bool i = false;
TOClass _class = new TOClass();
try
{
if (resp != null)
{
string insert_resp = @"insert into tbl_resp(resp_name, resp_rg, resp_cpf, resp_birthDate, resp_sex,
resp_phone, resp_whatsApp, resp_cep, resp_state, resp_city, resp_block, resp_street,resp_number, resp_obs) values('"
+ resp.Resp_name + "', '" + resp.Resp_rg + "', '" + resp.Resp_cpf + "', '" + resp.Resp_birthDate + "', '" + resp.Resp_sex + "', '"
+ resp.Resp_phone + "', '" + resp.Resp_whatsApp + "', '" + resp.Resp_cep + "', '" + resp.Resp_state + "', '" + resp.Resp_city + "', '"
+ resp.Resp_block + "', '" + resp.Resp_street + "', '" + resp.Resp_number + "', '" + resp.Resp_obs + "');";
con = ConnectionFactory.Connection();
MySqlCommand cmdInsertResp = new MySqlCommand(insert_resp, con);
con.Open();
cmdInsertResp.ExecuteNonQuery();
con.Close();
string select_resp = "select resp_id from tbl_resp where resp_rg = '" + resp.Resp_rg + "';";
MySqlCommand cmdSelect = new MySqlCommand(select_resp, con);
con.Open();
MySqlDataReader reader = cmdSelect.ExecuteReader();
if (reader.Read())
{
resp.Resp_id = reader.GetInt16("resp_id");
}
con.Close();
string sql = @"insert into tbl_student(student_name, student_ctr, student_status, student_subscriptionDate,
student_rg, student_cpf, student_birthDate, student_sex, student_phone, student_whatsApp,
student_cep, student_state, student_city, student_block, student_street, student_number, student_obs,
class_id, resp_id) values('" + student.Student_name + "', '" + student.Student_ctr + "', 'Ativo', '" + student.Student_subscriptionDate
+ "', '" + student.Student_rg + "', '" + student.Student_cpf + "', '" + student.Student_birthDate + "', '" + student.Student_sex + "', '"
+ student.Student_phone + "', '" + student.Student_whatsApp + "', '" + student.Student_cep + "', '" + student.Student_state + "', '"
+ student.Student_city + "', '" + student.Student_block + "', '" + student.Student_street + "', '" + student.Student_number + "', '"
+ student.Student_obs + "', " + student.Class_id + ", " + resp.Resp_id + ");";
con = ConnectionFactory.Connection();
MySqlCommand cmd = new MySqlCommand(sql, con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
string select = "select * from tbl_student where student_ctr = '" + student.Student_ctr + "';";
con.Open();
MySqlCommand cmdSelectStudent = new MySqlCommand(select, con);
MySqlDataReader dtReaderSelect = cmdSelectStudent.ExecuteReader();
if (dtReaderSelect.Read())
{
i = true;
int v = 0;
string select_vacancys = "select class_vacancys from tbl_class where class_id = " + student.Class_id + ";";
MySqlConnection conSelect = ConnectionFactory.Connection();
conSelect.Open();
MySqlCommand cmdSelect_Vacancys = new MySqlCommand(select_vacancys, conSelect);
MySqlDataReader dtReaderVacancys = cmdSelect_Vacancys.ExecuteReader();
if (dtReaderVacancys.Read())
{
v = dtReaderVacancys.GetInt16("class_vacancys");
}
conSelect.Close();
string update = "update tbl_class set class_vacancys = " + (v - 1) + " where class_id = " + student.Class_id + ";";
conSelect.Open();
MySqlCommand cmdUpdate = new MySqlCommand(update, conSelect);
cmdUpdate.ExecuteNonQuery();
conSelect.Close();
}
else
{
i = false;
}
}
else
{
string sql = @"insert into tbl_student(student_name, student_ctr, student_status, student_subscriptionDate,
student_rg, student_cpf, student_birthDate, student_sex, student_phone, student_whatsApp,
student_cep, student_state, student_city, student_block, student_street, student_number, student_obs,
class_id, resp_id) values('" + student.Student_name + "', '" + student.Student_ctr + "', 'Ativo', '" + student.Student_subscriptionDate
+ "', '" + student.Student_rg + "', '" + student.Student_cpf + "', '" + student.Student_birthDate + "', '" + student.Student_sex + "', '"
+ student.Student_phone + "', '" + student.Student_whatsApp + "', '" + student.Student_cep + "', '" + student.Student_state + "', '"
+ student.Student_city + "', '" + student.Student_block + "', '" + student.Student_street + "', '" + student.Student_number + "', '"
+ student.Student_obs + "', " + student.Class_id + ", null);";
con = ConnectionFactory.Connection();
MySqlCommand cmd = new MySqlCommand(sql, con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
string select = "select * from tbl_student where student_ctr = '" + student.Student_ctr + "';";
con.Open();
MySqlCommand cmdSelect = new MySqlCommand(select, con);
MySqlDataReader dtReaderSelect = cmdSelect.ExecuteReader();
if (dtReaderSelect.Read())
{
i = true;
int v = 0;
string select_vacancys = "select class_vacancys from tbl_class where class_id = " + student.Class_id + ";";
MySqlConnection conSelect = ConnectionFactory.Connection();
conSelect.Open();
MySqlCommand cmdSelect_Vacancys = new MySqlCommand(select_vacancys, conSelect);
MySqlDataReader dtReaderVacancys = cmdSelect_Vacancys.ExecuteReader();
if (dtReaderVacancys.Read())
{
v = dtReaderVacancys.GetInt16("class_vacancys");
}
conSelect.Close();
string update = "update tbl_class set class_vacancys = " + (v - 1) + " where class_id = " + student.Class_id + ";";
conSelect.Open();
MySqlCommand cmdUpdate = new MySqlCommand(update, conSelect);
cmdUpdate.ExecuteNonQuery();
conSelect.Close();
}
else
{
i = false;
}
}
}
catch (MySqlException e)
{
throw new Exception(e.Message);
}
finally
{
con.Close();
}
return(i);
}
public bool Update(TOStudent student, TOResponsable resp)
{
bool i = false;
TOClass _class = new TOClass();
try
{
if (resp != null)
{
string sql = @"update tbl_student set student_name = '" + student.Student_name + "', student_ctr = '" + student.Student_ctr
+ "', student_status = '" + student.Student_state + "', student_rg = '" + student.Student_rg + "', student_cpf = '" + student.Student_cpf + "', student_birthDate = '"
+ student.Student_birthDate + "', student_sex = '" + student.Student_sex + "', student_phone = '" + student.Student_phone
+ "', student_whatsApp = '" + student.Student_whatsApp + "', student_cep = '" + student.Student_cep + "', student_state = '"
+ student.Student_state + "', student_city = '" + student.Student_city + "', student_block = '" + student.Student_block
+ "', student_street = '" + student.Student_street + "', student_number = '" + student.Student_number + "', student_obs = '"
+ student.Student_obs + "', class_id = " + student.Class_id + ", resp_id = " + student.Resp_id + " where student_id = " + student.Student_id + ";";
con = ConnectionFactory.Connection();
MySqlCommand cmd = new MySqlCommand(sql, con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
string select = "select * from tbl_student where student_ctr = '" + student.Student_ctr + "';";
con.Open();
MySqlCommand cmdSelectStudent = new MySqlCommand(select, con);
MySqlDataReader dtReaderSelect = cmdSelectStudent.ExecuteReader();
if (dtReaderSelect.Read())
{
i = true;
}
else
{
i = false;
}
con.Close();
string update_resp = @"update tbl_resp set resp_name = '" + resp.Resp_name + "', resp_rg = '" + resp.Resp_rg
+ "', resp_cpf = '" + resp.Resp_cpf + "', resp_birthDate = '" + resp.Resp_birthDate + "', resp_sex = '" + resp.Resp_sex
+ "', resp_phone = '" + resp.Resp_phone + "', resp_whatsApp = '" + resp.Resp_whatsApp + "', resp_cep = '" + resp.Resp_cep
+ "', resp_state = '" + resp.Resp_state + "', resp_city = '" + resp.Resp_city + "', resp_block = '" + resp.Resp_block
+ "', resp_street = '" + resp.Resp_street + "', resp_number = '" + resp.Resp_number + "', resp_obs = '" + resp.Resp_obs + "' where resp_id = " + resp.Resp_id + ";";
con = ConnectionFactory.Connection();
MySqlCommand cmdUpdateResp = new MySqlCommand(update_resp, con);
con.Open();
cmdUpdateResp.ExecuteNonQuery();
}
else
{
string sql = @"update tbl_student set student_name = '" + student.Student_name + "', student_ctr = '" + student.Student_ctr
+ "', student_status = '" + student.Student_state + "', student_subscriptionDate = '" + student.Student_subscriptionDate
+ "', student_rg = '" + student.Student_rg + "', student_cpf = '" + student.Student_cpf + "', student_birthDate = '"
+ student.Student_birthDate + "', student_sex = '" + student.Student_sex + "', student_phone = '" + student.Student_phone
+ "', student_whatsApp = '" + student.Student_whatsApp + "', student_cep = '" + student.Student_cep + "', student_state = '"
+ student.Student_state + "', student_city = '" + student.Student_city + "', student_block = '" + student.Student_block
+ "', student_street = '" + student.Student_street + "', student_number = '" + student.Student_number + "', student_obs = '"
+ student.Student_obs + "', class_id = " + student.Class_id + ", resp_id = null where student_id = " + student.Resp_id + ";";
con = ConnectionFactory.Connection();
MySqlCommand cmd = new MySqlCommand(sql, con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
string select = "select * from tbl_student where student_ctr = '" + student.Student_ctr + "';";
con.Open();
MySqlCommand cmdSelect = new MySqlCommand(select, con);
MySqlDataReader dtReaderSelect = cmdSelect.ExecuteReader();
if (dtReaderSelect.Read())
{
i = true;
}
else
{
i = false;
}
}
}
catch (MySqlException e)
{
throw new Exception(e.Message);
}
finally
{
con.Close();
}
return(i);
}
public REG_Class(int id)
{
op = 1;
InitializeComponent();
cbxTeacher.ItemsSource = LoadTeachers();
DAOClass d = new DAOClass();
to_class = d.Selection(id);
_numDuration.Text = to_class.Class_duration.ToString();
_numClass.Text = to_class.Class_timePerWeek.ToString();
_pickerStarts.Text = to_class.Class_hourStarts.ToString();
_pickerEnds.Text = to_class.Class_hourEnds.ToString();
//setting type comboBox.
if (to_class.Type == "Inglês")
{
cbxType.SelectedIndex = 0;
}
else if (to_class.Type == "Interativo")
{
cbxType.SelectedIndex = 1;
}
else if (to_class.Type == "Hardware")
{
cbxType.SelectedIndex = 2;
}
else if (to_class.Type == "Games")
{
cbxType.SelectedIndex = 3;
}
else if (to_class.Type == "Informática")
{
cbxType.SelectedIndex = 4;
}
else if (to_class.Type == "Administração")
{
cbxType.SelectedIndex = 5;
}
//setting day comboBox.
if (to_class.Class_day == "Segunda")
{
cbxDate.SelectedIndex = 0;
}
else if (to_class.Class_day == "Terça")
{
cbxDate.SelectedIndex = 1;
}
else if (to_class.Class_day == "Quarta")
{
cbxDate.SelectedIndex = 2;
}
else if (to_class.Class_day == "Quinta")
{
cbxDate.SelectedIndex = 3;
}
else if (to_class.Class_day == "Sexta")
{
cbxDate.SelectedIndex = 4;
}
else if (to_class.Class_day == "Sábado")
{
cbxDate.SelectedIndex = 5;
}
else if (to_class.Class_day == "Segunda e Quarta")
{
cbxDate.SelectedIndex = 6;
}
else if (to_class.Class_day == "Terça e Quinta")
{
cbxDate.SelectedIndex = 7;
}
lblDaysOfWeek.Content = cbxDate.SelectedValue;
cbxTeacher.SelectedItem = to_class.Teacher_name;
cbxDate.IsEnabled = false;
cbxTeacher.IsEnabled = false;
cbxType.IsEnabled = false;
_numDuration.IsEnabled = false;
_numClass.IsEnabled = false;
_pickerStarts.IsEnabled = false;
_pickerEnds.IsEnabled = false;
btnRegisterClass.Content = "Editar";
btnCancel.IsEnabled = false;
}
public TOClass LoadQuickMenu(string day)
{
List <TOClass> i = new List <TOClass>();
TOClass x = new TOClass();
int highestRate = 0;
try
{
string sql = "select class_vacancys from tbl_class where class_day = '" + day + "';";
con = ConnectionFactory.Connection();
MySqlCommand cmd = new MySqlCommand(sql, con);
con.Open();
MySqlDataReader dtreader = cmd.ExecuteReader();
while (dtreader.Read())
{
TOClass _class = new TOClass();
_class.Class_vacancys = dtreader.GetInt16("class_vacancys");
i.Add(_class);
}
try
{
highestRate = i.Max(r => r.Class_vacancys);
}
catch (Exception)
{
Xceed.Wpf.Toolkit.MessageBox.Show("Erro ao retornar resultados. Por favor, cheque as vagas manualmente.", "MC Sales", MessageBoxButton.OK, MessageBoxImage.Information, MessageBoxResult.OK);
}
con.Close();
string selectClass = "select * from tbl_class where class_vacancys = " + highestRate + " and class_day = '" + day + "';";;
con.Open();
MySqlCommand cmdSelectClass = new MySqlCommand(selectClass, con);
MySqlDataReader dtReaderSelectClass = cmdSelectClass.ExecuteReader();
if (dtReaderSelectClass.Read())
{
x.Class_id = dtReaderSelectClass.GetInt16("class_id");
x.Class_day = dtReaderSelectClass.GetString("class_day");
x.Class_timePerWeek = dtReaderSelectClass.GetInt16("class_timesPerWeek");
x.Class_hourStarts = dtReaderSelectClass.GetString("class_hourStarts");
x.Class_hourEnds = dtReaderSelectClass.GetString("class_hourEnds");
x.Class_vacancys = dtReaderSelectClass.GetInt16("class_vacancys");
x.Class_duration = dtReaderSelectClass.GetInt16("class_duration");
x.User_id = dtReaderSelectClass.GetInt16("user_id");
x.Class_TotalHours = ("Das " + (dtReaderSelectClass.GetString("class_hourStarts")) + " às " + (dtReaderSelectClass.GetString("class_hourEnds")));
x.Type = dtReaderSelectClass.GetString("class_type");
}
}
catch (MySqlException e)
{
throw new Exception(e.Message);
}
return(x);
}
