private void btnLogin_Click(object sender, EventArgs e)
{
Program.databaseUser = textBoxUser.Text;
Program.databasePassword = textBoxPassword.Text;
Classes.Connection connection = new Classes.Connection(Program.databaseUser, Program.databasePassword);
MySqlConnection c = connection.GetConnection();
try
{
c.Open(); //Tentativa de realizar uma conexão com os dados fornecidos
Forms.FormUserLogin nextScreen = new Forms.FormUserLogin();
this.Visible = false;
nextScreen.ShowDialog();
this.Close();
}
catch (MySqlException ex)
{
if (ex.Code == 0x0) //Se o try retornar uma exeção tratamos ela
{
MessageBox.Show("Ocorreu um erro \n Usuário ou Senha incoretos", "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
MessageBox.Show("Ocorreu um erro", "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
Console.WriteLine(ex); // Provavélmente o Usuario e/ou a senha estão incorretos
}
}
// SQL Methods
private void _load_schedules_to_datagridview(DataGridView dgv, string selectedDate)
{
Classes.Connection Connection = new Classes.Connection();
using (MySqlConnection conn = new MySqlConnection(Connection.connStr))
{
string stmt = "select `tbl_schedules`.`sched_id`, `tbl_schedules`.`sched_auto_departure`, `tbl_bus`.`bus_number` as `BUS NUMBER`, DATE_FORMAT(`tbl_schedules`.`sched_departure_time`, '%h:%i:%s %p') as `DEPARTURE TIME` from `tbl_schedules` left join `tbl_bus` on `tbl_schedules`.`sched_bus_id`=`tbl_bus`.`bus_id` where DATE_FORMAT(`sched_date`, '%c/%d/%Y')=@sched_date and `sched_is_active`=1 order by `sched_departure_time` ASC";
using (MySqlCommand cmd = new MySqlCommand(stmt, conn))
{
MySqlDataAdapter da = new MySqlDataAdapter();
DataTable dt = new DataTable();
cmd.Parameters.Add("@sched_date", MySqlDbType.VarChar).Value = selectedDate;
conn.Open();
da.SelectCommand = cmd;
conn.Close();
da.Fill(dt);
dgv.DataSource = dt;
// hide datagridview columns
Classes.Forms._hide_datagridview_column(this.dgv_schedules, new string[] { "sched_id", "sched_auto_departure" });
// call the format dgv method
this._format_datagridview(this.dgv_schedules);
}
}
}
// get datas
private object[] _get_datas(int sched_id)
{
Classes.Connection Connection = new Classes.Connection();
using (MySqlConnection conn = new MySqlConnection(Connection.connStr))
{
string stmt = "select `sched_bus_id`, DATE_FORMAT(`sched_date`, '%c/%d/%Y') as `sched_date`, DATE_FORMAT(`sched_departure_time`, '%h:%i:%s %p') as `sched_departure_time`, `sched_name`, `sched_description`, `sched_auto_departure` from `tbl_schedules` where `sched_id`=@sched_id and `sched_is_active`=1";
using (MySqlCommand cmd = new MySqlCommand(stmt, conn))
{
cmd.Parameters.Add("@sched_id", MySqlDbType.Int32).Value = sched_id;
conn.Open();
MySqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
object[] returnDatas = new object[6];
while (dr.Read())
{
returnDatas[0] = dr["sched_bus_id"];
returnDatas[1] = dr["sched_date"];
returnDatas[2] = dr["sched_departure_time"];
returnDatas[3] = dr["sched_name"];
returnDatas[4] = dr["sched_description"];
returnDatas[5] = dr["sched_auto_departure"];
}
return(returnDatas);
}
dr.Close();
conn.Close();
}
}
return(null);
}
private void _load_bus_numbers_to_combobox(ComboBox cmb)
{
Classes.Connection Connection = new Classes.Connection();
using (MySqlConnection conn = new MySqlConnection(Connection.connStr))
{
string stmt = "select `tbl_bus`.`bus_id`, `tbl_bus`.`bus_number` from `tbl_bus` where `tbl_bus`.`bus_is_active`=1 and `tbl_bus`.`bus_id` not in (select `tbl_schedules`.`sched_bus_id` from `tbl_schedules` where `tbl_schedules`.`sched_is_active`=1 and TIMESTAMP(`tbl_schedules`.`sched_date`, `tbl_schedules`.`sched_departure_time`)>=CURRENT_TIMESTAMP()) order by CAST(`bus_number` as SIGNED INTEGER) ASC";
using (MySqlCommand cmd = new MySqlCommand(stmt, conn))
{
Dictionary <int, string> listItems = new Dictionary <int, string>();
listItems.Add(0, "— Select Bus Number —");
conn.Open();
MySqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
listItems.Add(Convert.ToInt32(dr["bus_id"]), dr["bus_number"].ToString());
}
}
else
{
this.cb_bus_number.Enabled = false;
this.btn_save.Enabled = false;
}
dr.Close();
conn.Close();
// finalize the dictionary
cmb.DisplayMember = "Value";
cmb.ValueMember = "Key";
cmb.DataSource = new BindingSource(listItems, null);
}
}
}
// ---------------------------------------------------------------------------------------------------
// Load search/autocomplete textbox
private void _load_autocomplete(ToolStripTextBox txtbox)
{
Classes.Connection Connection = new Classes.Connection();
// init textbox autocomplete properties
this.toolStripTxt_search.AutoCompleteSource = AutoCompleteSource.CustomSource;
AutoCompleteStringCollection collection = new AutoCompleteStringCollection();
using (MySqlConnection conn = new MySqlConnection(Connection.connStr))
{
string stmt = "select `bus_number` from `tbl_bus` where `bus_is_active`=1 order by CAST(`bus_number` as SIGNED INTEGER) ASC";
using (MySqlCommand cmd = new MySqlCommand(stmt, conn))
{
conn.Open();
MySqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
collection.Add(dr["bus_number"].ToString());
}
}
dr.Close();
conn.Close();
}
}
// finally add to the textbox collection
txtbox.AutoCompleteCustomSource = collection;
}
// -----------------------------------------------------------------------------------------------
// get the data via class name
private object[] _get_class_info(string class_name)
{
Classes.Connection Connection = new Classes.Connection();
using (MySqlConnection conn = new MySqlConnection(Connection.connStr))
{
string stmt = "select `class_id`, `class_name`, `class_seat_count`, `class_has_aircon`, `class_remarks`, `class_edited_date` from `tbl_bus_class` where `class_name`=@class_name and `class_is_active`=1";
using (MySqlCommand cmd = new MySqlCommand(stmt, conn))
{
cmd.Parameters.Add("@class_name", MySqlDbType.VarChar).Value = class_name;
MySqlDataAdapter da = new MySqlDataAdapter();
DataSet ds = new DataSet();
conn.Open();
da.SelectCommand = cmd;
conn.Close();
da.Fill(ds, "tbl_bus_class");
if (ds.Tables[0].Rows.Count > 0)
{
DataRow row = ds.Tables[0].Rows[0];
object[] returnDatas = new object[6];
returnDatas[0] = row.ItemArray[0];
returnDatas[1] = row.ItemArray[1];
returnDatas[2] = row.ItemArray[2];
returnDatas[3] = row.ItemArray[3];
returnDatas[4] = row.ItemArray[4];
returnDatas[5] = row.ItemArray[5];
return(returnDatas);
}
return(null);
}
}
}
// get the data via class name
private object[] _get_bus_info(string bus_number)
{
Classes.Connection Connection = new Classes.Connection();
using (MySqlConnection conn = new MySqlConnection(Connection.connStr))
{
string stmt = "select `bus_id`, `bus_class_id`, `bus_number`, `bus_plate_number` from `tbl_bus` where `bus_number`=@bus_number";
using (MySqlCommand cmd = new MySqlCommand(stmt, conn))
{
cmd.Parameters.Add("@bus_number", MySqlDbType.VarChar).Value = bus_number;
MySqlDataAdapter da = new MySqlDataAdapter();
DataSet ds = new DataSet();
conn.Open();
da.SelectCommand = cmd;
conn.Close();
da.Fill(ds, "tbl_bus");
if (ds.Tables[0].Rows.Count > 0)
{
DataRow row = ds.Tables[0].Rows[0];
object[] returnDatas = new object[6];
returnDatas[0] = row.ItemArray[0];
returnDatas[1] = row.ItemArray[1];
returnDatas[2] = row.ItemArray[2];
returnDatas[3] = row.ItemArray[3];
return(returnDatas);
}
}
}
return(null);
}
// Load/Fill the combobox
private void _load_bus_class_to_combobox(ComboBox cmb)
{
Classes.Connection Connection = new Classes.Connection();
using (MySqlConnection conn = new MySqlConnection(Connection.connStr)) {
string stmt = "select `class_id`, `class_name` from `tbl_bus_class` where `class_is_active`=1 order by `class_name` ASC";
using (MySqlCommand cmd = new MySqlCommand(stmt, conn))
{
Dictionary <int, string> listItems = new Dictionary <int, string>();
listItems.Add(0, "— Select Bus Class —");
conn.Open();
MySqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
listItems.Add(Convert.ToInt32(dr["class_id"]), dr["class_name"].ToString());
}
}
dr.Close();
conn.Close();
cmb.DisplayMember = "Value";
cmb.ValueMember = "Key";
cmb.DataSource = new BindingSource(listItems, null);
}
}
}
// ------------------------------------------------------------------------
private void _load_datas(DataGridView dgv, string[] arrSearchKeys = null, string[] arrSearchValues = null)
{
Classes.Connection Connection = new Classes.Connection();
using (MySqlConnection conn = new MySqlConnection(Connection.connStr)) {
string stmtNormal = "select `class_id`, `class_name`, `class_seat_count`, `class_has_aircon`, `class_remarks` from `tbl_bus_class` where `class_is_active`=1";
string stmtSearch = "select `class_id`, `class_name`, `class_seat_count`, `class_has_aircon`, `class_remarks` from `tbl_bus_class` where `class_is_active`=1 and (";
if (!Classes.Datatypes.ArrayIsNullOrEmpty(arrSearchKeys) && !Classes.Datatypes.ArrayIsNullOrEmpty(arrSearchValues))
{
for (int i = 0; i < arrSearchKeys.Length; i++)
{
string commaStr = i >= arrSearchKeys.Length - 1 ? "" : " or ";
stmtSearch += string.Format("`{0}` like @{1}{2}", arrSearchKeys[i], arrSearchKeys[i], commaStr);
}
}
stmtSearch += ")";
string stmt = (!Classes.Datatypes.ArrayIsNullOrEmpty(arrSearchKeys) && !Classes.Datatypes.ArrayIsNullOrEmpty(arrSearchValues)) ? stmtSearch : stmtNormal;
using (MySqlCommand cmd = new MySqlCommand(stmt, conn))
{
MySqlDataAdapter da = new MySqlDataAdapter();
DataSet ds = new DataSet();
if (!Classes.Datatypes.ArrayIsNullOrEmpty(arrSearchKeys) && !Classes.Datatypes.ArrayIsNullOrEmpty(arrSearchValues))
{
for (int i = 0; i < arrSearchKeys.Length; i++)
{
cmd.Parameters.AddWithValue("@" + arrSearchKeys[i], "%" + arrSearchValues[i] + "%");
}
}
conn.Open();
da.SelectCommand = cmd;
conn.Close();
da.Fill(ds, "tbl_bus_class");
string[] arrColumns = new string[] { "ID", "CLASS NAME", "NUMBER OF SEATS", "AIR-CONDITIONED", "REMARKS" };
Classes.Forms._create_datagrid_columns(this.dgv_datas, arrColumns);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow row = ds.Tables[0].Rows[i];
string[] arrDatas = new string[5];
arrDatas[0] = row["class_id"].ToString();
arrDatas[1] = row["class_name"].ToString();
arrDatas[2] = row["class_seat_count"].ToString();
arrDatas[3] = row["class_has_aircon"].ToString() == "1" ? "YES" : "NO";
arrDatas[4] = row["class_remarks"].ToString();
Classes.Forms._add_rows(this.dgv_datas, arrDatas);
}
// hide columns
string[] arrHiddenColumns = new string[1] {
"ID"
};
Classes.Forms._hide_datagridview_column(this.dgv_datas, arrHiddenColumns);
}
}
}
private Classes.CurrentPlayer LoadPlayer(int _id)
{
Classes.Connection connection = new Classes.Connection();
if (connection.LoadPlayerFromDataBase(_id, out _thePlayer))
{
return(_thePlayer);
}
else
{
MessageBox.Show("Failed to load player");
return(null);
}
}
private void _load_datas_to_datagrid(DataGridView dgv, string[] arrSearchKeys = null, string[] arrSearchValues = null)
{
Classes.Connection Connection = new Classes.Connection();
using (MySqlConnection conn = new MySqlConnection(Connection.connStr))
{
string selected_fields = "`tbl_bus`.`bus_id`, `tbl_bus_class`.`class_name` as `BUS CLASS`, `tbl_bus`.`bus_number` as `BUS NUMBER`, `tbl_bus`.`bus_plate_number` as `PLATE NUMBER`, `tbl_bus`.`bus_created_date` AS `BUS CREATED DATE`";
string joinStmt = "left join `tbl_bus_class` on `tbl_bus`.`bus_class_id`=`tbl_bus_class`.`class_id`";
string stmtNormal = "select " + selected_fields + " from `tbl_bus` " + joinStmt + " where `bus_is_active`=1 order by CAST(`bus_number` as SIGNED INTEGER) ASC";
string stmtSearch = "select " + selected_fields + " from `tbl_bus` " + joinStmt + " where `bus_is_active`=1 and (";
if (!Classes.Datatypes.ArrayIsNullOrEmpty(arrSearchKeys) && !Classes.Datatypes.ArrayIsNullOrEmpty(arrSearchValues))
{
for (int i = 0; i < arrSearchKeys.Length; i++)
{
string commaStr = i >= arrSearchKeys.Length - 1 ? "" : " or ";
stmtSearch += string.Format("`{0}` like @{1}{2}", arrSearchKeys[i], arrSearchKeys[i], commaStr);
}
}
stmtSearch += ")order by CAST(`bus_number` as SIGNED INTEGER) ASC";
string stmt = (!Classes.Datatypes.ArrayIsNullOrEmpty(arrSearchKeys) && !Classes.Datatypes.ArrayIsNullOrEmpty(arrSearchValues)) ? stmtSearch : stmtNormal;
using (MySqlCommand cmd = new MySqlCommand(stmt, conn))
{
MySqlDataAdapter da = new MySqlDataAdapter();
DataTable dt = new DataTable();
if (!Classes.Datatypes.ArrayIsNullOrEmpty(arrSearchKeys) && !Classes.Datatypes.ArrayIsNullOrEmpty(arrSearchValues))
{
for (int i = 0; i < arrSearchKeys.Length; i++)
{
cmd.Parameters.AddWithValue("@" + arrSearchKeys[i], "%" + arrSearchValues[i] + "%");
}
}
conn.Open();
da.SelectCommand = cmd;
conn.Close();
da.Fill(dt);
dgv.DataSource = dt;
// hide some datagridview columns
Classes.Forms._hide_datagridview_column(this.dgv_datas, new string[1] {
"bus_id"
});
}
}
}
// remove data
private Boolean _remove_data(int sched_id)
{
Classes.Connection Connection = new Classes.Connection();
using (MySqlConnection conn = new MySqlConnection(Connection.connStr))
{
string stmt = "update `tbl_schedules` set `sched_is_active`=0 where `sched_id`=@sched_id";
using (MySqlCommand cmd = new MySqlCommand(stmt, conn))
{
cmd.Parameters.Add("@sched_id", MySqlDbType.Int32).Value = sched_id;
conn.Open();
int affectedRows = Convert.ToInt32(cmd.ExecuteNonQuery());
conn.Close();
if (affectedRows > 0)
{
return(true);
}
return(false);
}
}
}
// saving the bus schedule
private Boolean _save_bus_schedule(int schedule_id = 0)
{
Classes.Connection Connection = new Classes.Connection();
using (MySqlConnection conn = new MySqlConnection(Connection.connStr))
{
string stmtAdd = "insert into `tbl_schedules` (`sched_bus_id`, `sched_date`, `sched_departure_time`, `sched_name`, `sched_description`, `sched_created_by`, `sched_auto_departure`) values (@sched_bus_id, @sched_date, @sched_departure_time, @sched_name, @sched_description, @sched_created_by, @sched_auto_departure)";
string stmtUpdate = "update `tbl_schedules` set `sched_bus_id`=@sched_bus_id, `sched_date`=@sched_date, `sched_departure_time`=@sched_departure_time, `sched_name`=@sched_name, `sched_description`=@sched_description, `sched_auto_departure`=@sched_auto_departure, `sched_edited_by`=@sched_edited_by where `sched_id`=@sched_id and `sched_is_active`=1";
string stmt = schedule_id != 0 ? stmtUpdate : stmtAdd;
using (MySqlCommand cmd = new MySqlCommand(stmt, conn))
{
cmd.Parameters.Add("@sched_bus_id", MySqlDbType.Int32).Value = this.cb_bus_number.SelectedValue;
cmd.Parameters.Add("@sched_date", MySqlDbType.DateTime).Value = this.dtp_sched.Value;
cmd.Parameters.Add("@sched_departure_time", MySqlDbType.DateTime).Value = this.dtp_departure_time.Value;
cmd.Parameters.Add("@sched_name", MySqlDbType.Text).Value = this.txt_sched_name.Text;
cmd.Parameters.Add("@sched_description", MySqlDbType.Text).Value = this.txt_description.Text;
cmd.Parameters.Add("@sched_auto_departure", MySqlDbType.Bit).Value = this.chk_auto_departure.Checked;
if (0 != schedule_id)
{
cmd.Parameters.Add("@sched_edited_by", MySqlDbType.Int32).Value = Classes.Globals.user_control_id;
cmd.Parameters.Add("@sched_id", MySqlDbType.Int32).Value = schedule_id;
}
else
{
cmd.Parameters.Add("@sched_created_by", MySqlDbType.Int32).Value = Classes.Globals.user_control_id;
}
conn.Open();
int affectedRows = Convert.ToInt32(cmd.ExecuteNonQuery());
conn.Close();
if (affectedRows > 0)
{
return(true);
}
}
}
return(false);
}
// remove data
private Boolean _remove_data(int class_id)
{
Classes.Connection Connection = new Classes.Connection();
using (MySqlConnection conn = new MySqlConnection(Connection.connStr))
{
string stmt = "update `tbl_bus_class` set `class_is_active`=0, `class_edited_by`=@class_edited_by where `class_id`=@class_id";
using (MySqlCommand cmd = new MySqlCommand(stmt, conn))
{
cmd.Parameters.Add("@class_edited_by", MySqlDbType.Int32).Value = Classes.Globals.user_control_id;
cmd.Parameters.Add("@class_id", MySqlDbType.Int32).Value = class_id;
conn.Open();
int affectedRows = Convert.ToInt32(cmd.ExecuteNonQuery());
conn.Close();
if (affectedRows > 0)
{
return(true);
}
return(false);
}
}
}
private Boolean _save_data(int class_id = 0)
{
if (this._check_field_requirements() == true)
{
Classes.Connection Connection = new Classes.Connection();
using (MySqlConnection conn = new MySqlConnection(Connection.connStr))
{
string stmtInsert = "insert into `tbl_bus_class` (`class_name`, `class_seat_count`, `class_has_aircon`, `class_seat_price`, `class_remarks`, `class_created_by`) values (@class_name, @class_seat_count, @class_has_aircon, @class_seat_price, @class_remarks, @class_created_by)";
string stmtUpdate = "update `tbl_bus_class` set `class_name`=@class_name, `class_seat_count`=@class_seat_count, `class_has_aircon`=@class_has_aircon, `class_seat_price`=@class_seat_price, `class_remarks`=@class_remarks, `class_edited_by`=@class_edited_by where `class_id`=@class_id";
string stmt = class_id != 0 ? stmtUpdate : stmtInsert;
using (MySqlCommand cmd = new MySqlCommand(stmt, conn))
{
cmd.Parameters.Add("@class_name", MySqlDbType.VarChar).Value = this.txt_class_name.Text;
cmd.Parameters.Add("@class_seat_count", MySqlDbType.Int32).Value = this.txt_number_of_seats.Text;
cmd.Parameters.Add("@class_has_aircon", MySqlDbType.Bit).Value = this.has_aircon;
cmd.Parameters.Add("@class_seat_price", MySqlDbType.Double).Value = Convert.ToDouble(this.txt_seat_price.Text);
cmd.Parameters.Add("@class_remarks", MySqlDbType.Text).Value = this.txt_remarks.Text;
if (0 != class_id)
{
cmd.Parameters.Add("@class_edited_by", MySqlDbType.Int32).Value = Classes.Globals.user_control_id;
cmd.Parameters.Add("@class_id", MySqlDbType.Int32).Value = class_id;
}
else
{
cmd.Parameters.Add("@class_created_by", MySqlDbType.Int32).Value = Classes.Globals.user_control_id;
}
conn.Open();
int affectedRows = Convert.ToInt32(cmd.ExecuteNonQuery());
conn.Close();
if (affectedRows > 0)
{
return(true);
}
}
}
}
return(false);
}
// -------------------------------------- SQL STATEMENTS -----------------------------------------------
private Boolean _save_data(int bus_id = 0)
{
if (this._check_field_requirements() == true)
{
Classes.Connection Connection = new Classes.Connection();
using (MySqlConnection conn = new MySqlConnection(Connection.connStr))
{
string stmtInsert = "insert into `tbl_bus` (`bus_class_id`, `bus_number`, `bus_plate_number`, `bus_created_by`) values (@bus_class_id, @bus_number, @bus_plate_number, @bus_created_by)";
string stmtUpdate = "update `tbl_bus` set `bus_class_id`=@bus_class_id, `bus_number`=@bus_number, `bus_plate_number`=@bus_plate_number, `bus_edited_by`=@bus_edited_by where `bus_id`=@bus_id";
string stmt = bus_id != 0 ? stmtUpdate : stmtInsert;
using (MySqlCommand cmd = new MySqlCommand(stmt, conn))
{
cmd.Parameters.Add("@bus_class_id", MySqlDbType.VarChar).Value = this.cb_buss_class.SelectedValue;
cmd.Parameters.Add("@bus_number", MySqlDbType.VarChar).Value = this.txt_bus_number.Text;
cmd.Parameters.Add("@bus_plate_number", MySqlDbType.VarChar).Value = this.txt_plate_number.Text;
if (0 != bus_id)
{
cmd.Parameters.Add("@bus_edited_by", MySqlDbType.Int32).Value = Classes.Globals.user_control_id;
cmd.Parameters.Add("@bus_id", MySqlDbType.Int32).Value = bus_id;
}
else
{
cmd.Parameters.Add("@bus_created_by", MySqlDbType.Int32).Value = Classes.Globals.user_control_id;
}
conn.Open();
int affectedRows = Convert.ToInt32(cmd.ExecuteNonQuery());
conn.Close();
if (affectedRows > 0)
{
return(true);
}
}
}
}
return(false);
}
