public bool DeleteById(int id)
{
string sql = " update attendance set deleted=1 where id = @id;";
MySQLConnection sqlHelp = new MySQLConnection();
MySqlParameter parameter = new MySqlParameter("id", id);
if (sqlHelp.ExecuteNonQuery(sql, parameter))
{
return(true);
}
return(false);
}
public bool UpdateById(int id, Attendence att)
{
string sql = "update attendance set employee_id = @employeeId,employee_name=@employeeName,status=@status,late_time=@lateTime,AReason=@aReason where id = @id;";
MySQLConnection sqlHelp = new MySQLConnection();
MySqlParameter parameter1 = new MySqlParameter("employeeId", sqlHelp.SqlNull(att.employeeId));
MySqlParameter parameter2 = new MySqlParameter("employeeName", sqlHelp.SqlNull(att.employeeName));
MySqlParameter parameter3 = new MySqlParameter("status", sqlHelp.SqlNull(att.status));
MySqlParameter parameter4 = new MySqlParameter("lateTime", sqlHelp.SqlNull(att.lateTime));
MySqlParameter parameter5 = new MySqlParameter("aReason", sqlHelp.SqlNull(att.aReason));
if (sqlHelp.ExecuteNonQuery(sql, parameter1, parameter2, parameter3, parameter4, parameter5))
{
return(true);
}
return(false);
}
public bool AddAttendance(Attendence att)
{
string sql = " insert into attendance (employee_id,employee_name,status,late_time,AReason) values(@employeeId,@employeeName,@status,@lateTime,@aReason);";
MySQLConnection sqlHelp = new MySQLConnection();
MySqlParameter parameter1 = new MySqlParameter("employeeId", sqlHelp.SqlNull(att.employeeId));
MySqlParameter parameter2 = new MySqlParameter("employeeName", sqlHelp.SqlNull(att.employeeName));
MySqlParameter parameter3 = new MySqlParameter("status", sqlHelp.SqlNull(att.status));
MySqlParameter parameter4 = new MySqlParameter("lateTime", sqlHelp.SqlNull(att.lateTime));
MySqlParameter parameter5 = new MySqlParameter("aReason", sqlHelp.SqlNull(att.aReason));
if (sqlHelp.ExecuteNonQuery(sql, parameter1, parameter2, parameter3, parameter4, parameter5))
{
return(true);
}
return(false);
}
private void btn_search_Click(object sender, EventArgs e)
{
string sql;
string type;
if (!IsNumberic(txt_id.Text) && txt_id.Text != "")
{
MessageBox.Show("ID must be a number");
}
else
{
if (comboB_type.Text == "All")
{
type = "";
}
else
{
type = comboB_type.Text;
}
if (txt_id.Text == "")
{
sql = "select * from stock where itemID like '%" + txt_id.Text + "%' and itemName like '%" + txt_name.Text + "%' and type like '%" + type + "%'";
}
else
{
sql = "select * from stock where itemID = '" + txt_id.Text + "' and itemName like '%" + txt_name.Text + "%' and type like '%" + type + "%'";
}
if (local)
{
DataSet ds = GetDataSet(sql);
this.dataGridView1.DataSource = ds;
this.dataGridView1.DataMember = "tb";
ColumnOrder();
}
else
{
DataSet ds = MySQLConnection.GetDataSetValue(sql, "tb");
this.dataGridView1.DataSource = ds;
this.dataGridView1.DataMember = "tb";
ColumnOrder();
}
}
isSearchOrAddIsClick = true;
}
private void InitialGrid()
{
DataSet ds;
if (local)
{
ds = GetDataSet("select * from stock");
}
else
{
ds = MySQLConnection.GetDataSetValue("select * from stock", "tb");
}
this.dataGridView1.DataSource = ds;
this.dataGridView1.DataMember = "tb";
ColumnOrder();
style();
}
private void deleteData(int id)
{
try
{
string DeleteSql = "Delete from stock where itemID = '" + id + "'";
if (local)
{
SqlConnection sqlcon = new SqlConnection(strcon);
sqlcon.Open();
SqlCommand com = new SqlCommand(DeleteSql, sqlcon);
com.ExecuteNonQuery();
SqlDataAdapter ada = new SqlDataAdapter("select * from stock", sqlcon);
DataSet ds = new DataSet();
ada.Fill(ds, "stock");
sqlcon.Close();
}
else
{
MySqlConnection MySqlCon = new MySqlConnection(MySQLConnection.SqlCon);
MySqlCon.Open();
MySqlCommand com = new MySqlCommand(DeleteSql, MySqlCon);
com.ExecuteNonQuery();
MySqlDataAdapter ada = new MySqlDataAdapter("select * from stock", MySqlCon);
DataSet ds = new DataSet();
ada.Fill(ds, "stock");
MySQLConnection.CloseConn();
}
MySqlConnection MySqlCon1 = new MySqlConnection(MySQLConnection.SqlCon);
MySqlCon1.Open();
MySqlCommand com1 = new MySqlCommand("ALTER TABLE stock AUTO_INCREMENT = 1;", MySqlCon1);
com1.ExecuteNonQuery();
MySqlDataAdapter ada1 = new MySqlDataAdapter("select * from stock", MySqlCon1);
DataSet ds1 = new DataSet();
ada1.Fill(ds1, "stock");
MySQLConnection.CloseConn();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void updateData(int id, string name, int safe, int qty)
{
try
{
string UpdateSql = "update stock" +
" set itemName = '" + name + "'," +
"itemSafeStock = '" + safe + "'," +
"quantity = '" + qty + "'" +
"where itemID = '" + id + "'";
if (local)
{
SqlConnection sqlcon = new SqlConnection(strcon);
sqlcon.Open();
SqlCommand com = new SqlCommand(UpdateSql, sqlcon);
com.ExecuteNonQuery();
SqlDataAdapter ada = new SqlDataAdapter("select * from stock", sqlcon);
DataSet ds = new DataSet();
ada.Fill(ds, "stock");
sqlcon.Close();
}
else
{
MySqlConnection MySqlCon = new MySqlConnection(MySQLConnection.SqlCon);
MySqlCon.Open();
MySqlCommand com = new MySqlCommand(UpdateSql, MySqlCon);
com.ExecuteNonQuery();
MySqlDataAdapter ada = new MySqlDataAdapter("select * from stock", MySqlCon);
DataSet ds = new DataSet();
ada.Fill(ds, "stock");
MySQLConnection.CloseConn();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void InitTree(TreeNodeCollection Nds, string parentId)
{
DataView dv = new DataView();
TreeNode tmpNd;
string intId;
if (local)
{
DataSet ds = GetDataSet("select * from staff_info");
dv.Table = ds.Tables[0];
}
else
{
DataTable dt = MySQLConnection.GetDataTableValue("select * from staff_info");
dt.TableName = "staff_info";
dv.Table = dt;
}
dv.RowFilter = "parent_id='" + parentId + "'";
foreach (DataRowView drv in dv)
{
tmpNd = new TreeNode();
tmpNd.Tag = drv["id"].ToString();
tmpNd.Text = drv["last_name"].ToString();
if (drv["parent_id"].ToString() == "1") //10 means admin
{
tmpNd.ImageIndex = tmpNd.SelectedImageIndex = 0;
}
else
{
tmpNd.ImageIndex = tmpNd.SelectedImageIndex = 1;
}
Nds.Add(tmpNd);
intId = drv["parent_id"].ToString();
InitTree(tmpNd.Nodes, tmpNd.Tag.ToString());
}
}
private void deleteData()
{
try
{
string DeleteSql = "Delete from staff_info where id = '" + treeView1.SelectedNode.Tag + "'";
if (local)
{
SqlConnection sqlcon = new SqlConnection(strcon);
sqlcon.Open();
SqlCommand com = new SqlCommand(DeleteSql, sqlcon);
com.ExecuteNonQuery();
SqlDataAdapter ada = new SqlDataAdapter("select * from staff_info", sqlcon);
DataSet ds = new DataSet();
ada.Fill(ds, "staff_info");
sqlcon.Close();
}
else
{
MySqlConnection MySqlCon = new MySqlConnection(MySQLConnection.SqlCon);
MySqlCon.Open();
MySqlCommand com = new MySqlCommand(DeleteSql, MySqlCon);
com.ExecuteNonQuery();
MySqlDataAdapter ada = new MySqlDataAdapter("select * from staff_info", MySqlCon);
DataSet ds = new DataSet();
ada.Fill(ds, "staff_info");
MySQLConnection.CloseConn();
}
treeView1.Nodes.Clear();
InitTree(this.treeView1.Nodes, "0");
treeView1.ExpandAll();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void insertData()
{
try
{
if (!IsNumberic(txt_safe.Text))
{
MessageBox.Show("Safe stock must be a number");
return;
}
if (!IsNumberic(txt_qty.Text))
{
MessageBox.Show("Qty must be be a number");
return;
}
if (comboB_type1.Text == "")
{
MessageBox.Show("Must select the type of item");
return;
}
else
{
string InsertSql = "insert into stock(itemName,itemSafeStock,quantity,type) " +
"values ('" + txt_name1.Text + "'" +
",'" + txt_safe.Text + "'" +
",'" + txt_qty.Text + "'" +
",'" + comboB_type1.Text + "')";
if (local)
{
SqlConnection sqlcon = new SqlConnection(strcon);
sqlcon.Open();
SqlCommand com = new SqlCommand(InsertSql, sqlcon);
com.ExecuteNonQuery();
SqlDataAdapter ada = new SqlDataAdapter("select * from stock", sqlcon);
DataSet ds = new DataSet();
ada.Fill(ds, "stock");
sqlcon.Close();
}
else
{
MySqlConnection MySqlCon = new MySqlConnection(MySQLConnection.SqlCon);
MySqlCon.Open();
MySqlCommand com = new MySqlCommand(InsertSql, MySqlCon);
com.ExecuteNonQuery();
MySqlDataAdapter ada = new MySqlDataAdapter("select * from stock", MySqlCon);
DataSet ds = new DataSet();
ada.Fill(ds, "stock");
MySQLConnection.CloseConn();
}
MessageBox.Show("successful");
txt_name1.Text = "";
txt_safe.Text = "";
txt_qty.Text = "";
comboB_type1.Text = "";
InitialGrid();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
{
if (e.Action == TreeViewAction.ByMouse)
{
DataView dv = new DataView();
if (local)
{
DataSet ds = GetDataSet("select * from staff_info where id = '" + e.Node.Tag + "'");
dv.Table = ds.Tables[0];
}
else
{
DataTable dt = MySQLConnection.GetDataTableValue("select * from staff_info where id = '" + e.Node.Tag + "'");
dt.TableName = "staff_info";
dv.Table = dt;
}
DataRowView drv = dv[0];
if (e.Node.Parent != null)
{
txt_name.Text = drv["last_name"].ToString();
if (drv["gender"].ToString() == "male")
{
comBox_gender.SelectedIndex = 0;
}
else
{
comBox_gender.SelectedIndex = 1;
}
txt_SSN.Text = drv["ssn"].ToString();
if (drv["parent_id"].ToString() == "1")
{
cmb_position.SelectedIndex = 0;
}
if (drv["parent_id"].ToString() == "2")
{
cmb_position.SelectedIndex = 1;
}
if (drv["parent_id"].ToString() == "3")
{
cmb_position.SelectedIndex = 2;
}
if (drv["parent_id"].ToString() == "4")
{
cmb_position.SelectedIndex = 3;
}
txt_Phone.Text = drv["phone"].ToString();
txt_Address.Text = drv["address_id"].ToString();
//txt_Birthday.Text = drv["Birthday"].ToString();
txt_Email.Text = drv["email"].ToString();
toolStripButton2.Enabled = true;
btn_del.Enabled = true;
}
else
{
toolStripButton2.Enabled = false;
btn_del.Enabled = false;
}
}
}
private void updateData()
{
int parentID = 0;
if (cmb_position.SelectedIndex == 0)
{
parentID = 1;
}
if (cmb_position.SelectedIndex == 1)
{
parentID = 2;
}
if (cmb_position.SelectedIndex == 2)
{
parentID = 3;
}
if (cmb_position.SelectedIndex == 3)
{
parentID = 4;
}
if (!IsEmail(txt_Email.Text))
{
MessageBox.Show("the email address is invalid");
return;
}
else
{
string UpdateSql = "update staff_info" +
" set last_name = '" + txt_name.Text + "'," +
"address_id = '" + txt_Address.Text + "'," +
"phone = '" + txt_Phone.Text + "'," +
"parent_id = '" + parentID + "'," +
"gender = '" + comBox_gender.Text + "'," +
"birthday = '" + dateTimePicker1.Value.Date.ToString("yyyy-MM-dd HH:mm") + "'," +
"ssn = '" + txt_SSN.Text + "'," +
"email = '" + txt_Email.Text + "' " +
"where id = '" + treeView1.SelectedNode.Tag + "'";
try
{
if (local)
{
SqlConnection sqlcon = new SqlConnection(strcon);
sqlcon.Open();
SqlCommand com = new SqlCommand(UpdateSql, sqlcon);
com.ExecuteNonQuery();
SqlDataAdapter ada = new SqlDataAdapter("select * from staff_info", sqlcon);
DataSet ds = new DataSet();
ada.Fill(ds, "staff_info");
sqlcon.Close();
}
else
{
MySqlConnection MySqlCon = new MySqlConnection(MySQLConnection.SqlCon);
MySqlCon.Open();
MySqlCommand com = new MySqlCommand(UpdateSql, MySqlCon);
com.ExecuteNonQuery();
MySqlDataAdapter ada = new MySqlDataAdapter("select * from staff_info", MySqlCon);
DataSet ds = new DataSet();
ada.Fill(ds, "staff_info");
MySQLConnection.CloseConn();
}
MessageBox.Show("successful");
treeView1.Nodes.Clear();
InitTree(this.treeView1.Nodes, "0");
treeView1.ExpandAll();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
toolStripButton1.Text = "Add";
toolStripButton1.Image = imageList1.Images[2];
toolStripButton1.Enabled = true;
initial();
}
}