public bool them(TienCongDTO tc)
{
string query = string.Empty;
query += "INSERT INTO [dbo].[LOAITIENCONG]([MaLoaiTienCong],[TenLoaiTienCong],[TienCong])";
query += "VALUES (@MaLoaiTienCong,@TenLoaiTienCong,@TienCong)";
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@MaLoaiTienCong", tc.Maloaitiencong);
cmd.Parameters.AddWithValue("@TenLoaiTienCong", tc.Tenloaitiencong);
cmd.Parameters.AddWithValue("@TienCong", tc.Tiencong);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(false);
}
}
}
return(true);
}
private void bt_luu_Click(object sender, EventArgs e)
{
if (check_null())
{
ThayDoiQuyDinhDTO thaydoi = thaydoiBus.select();
int line = data_tiencong.RowCount;
if (line < thaydoi.SoLoaiTienCong1)
{
TienCongDTO tc = new TienCongDTO();
tc.Maloaitiencong = int.Parse(tb_matiencong.Text);
tc.Tenloaitiencong = tb_tenloaitc.Text;
tc.Tiencong = int.Parse(tb_tiencong.Text);
bool kq = tcBus.them(tc);
if (kq == false)
{
MessageBox.Show("Thêm tiền công thất bại. Vui lòng kiểm tra lại dữ liệu");
}
else
{
MessageBox.Show("Thêm tiền công thành công");
this.loadData_Vao_GridView();
}
CurrencyManager myCurrencyManager = (CurrencyManager)this.BindingContext[data_tiencong.DataSource];
myCurrencyManager.Refresh();
}
else
{
MessageBox.Show("Vượt quá số tiền công cho phép");
}
}
}
public bool them(TienCongDTO tc)
{
string query = string.Empty;
query += "INSERT INTO [TienCong] (tendichvu,matiencong,tiencong)";
query += "VALUES (@tendichvu,@matiencong,@tiencong)";
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@tendichvu", tc.Tendichvu);
cmd.Parameters.AddWithValue("@matiencong", tc.Matiencong);
cmd.Parameters.AddWithValue("@tiencong", tc.Tiencong);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
Console.WriteLine(ex);
throw;
}
}
}
return(true);
}
public bool xoa(TienCongDTO tc)
{
string query = string.Empty;
query += "DELETE FROM TienCong WHERE [matiencong] = @matiencong";
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@matiencong", tc.Matiencong);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
Console.WriteLine(ex);
throw;
}
}
}
return(true);
}
private void bt_xoa_Click(object sender, EventArgs e)
{
int currentRowIndex = data_tiencong.CurrentCellAddress.Y;// 'current row selected
//'Verify that indexing OK
if (-1 < currentRowIndex && currentRowIndex < data_tiencong.RowCount)
{
TienCongDTO tc = new TienCongDTO();
tc.Maloaitiencong = int.Parse(tb_matiencong.Text);
//2. Kiểm tra data hợp lệ or not
//3. Thêm vào DB
bool kq = tcBus.xoa(tc);
if (kq == false)
{
MessageBox.Show("Xóa tiền công thất bại. Vui lòng kiểm tra lại dũ liệu");
}
else
{
MessageBox.Show("Xóa tiền công thành công");
this.loadData_Vao_GridView();
tb_matiencong.Clear();
tb_tenloaitc.Clear();
tb_tiencong.Clear();
}
}
CurrencyManager myCurrencyManager = (CurrencyManager)this.BindingContext[data_tiencong.DataSource];
myCurrencyManager.Refresh();
}
private void btnThem_Click(object sender, EventArgs e)
{
if (IsNumber(tbSoTienCong.Text) == false)
{
MessageBox.Show("Số tiền công là số.Mời nhập lại.", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (!fMainForm.cNullTB(tbMaTienCong.Text) && !fMainForm.cNullTB(tbTenTienCong.Text))
{
if (!TienCongBUS.cPrimaryKey(tbMaTienCong.Text.Trim()))
{
string MaTienCong = tbMaTienCong.Text.Trim();
string TenTienCong = tbTenTienCong.Text;
string soTienCong = tbSoTienCong.Text;
decimal SoTienCong = decimal.Parse(soTienCong);
TienCongDTO tienCong = new TienCongDTO(MaTienCong, TenTienCong, SoTienCong);
TienCongBUS.addSalary(tienCong);
dtgvTienCong.DataSource = TienCongBUS.loadAllSalary();
}
else
{
MessageBox.Show("Dữ liệu vừa nhập vào không hợp lệ, do bị trùng khóa chính.");
}
}
else
{
MessageBox.Show("Bạn chưa nhập vào đủ dữ liệu xin vui lòng nhập lại.");
}
RefreshGUI();
}
public bool xoa(TienCongDTO tc)
{
string query = string.Empty;
query += "DELETE FROM [dbo].[LOAITIENCONG] WHERE [MaLoaiTienCong] = @MaLoaiTienCong";
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@MaLoaiTienCong", tc.Maloaitiencong);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(false);
}
}
}
return(true);
}
public List <TienCongDTO> TimKiem(string Key)
{
string query = string.Empty;
query += "SELECT [tendichvu], [tiencong], [matiencong]";
query += "FROM [TienCong]";
query += " WHERE ([tendichvu] LIKE CONCAT('%',@Key,'%'))";
query += " OR ([matiencong] LIKE CONCAT('%',@Key,'%'))";
query += " OR ([tiencong] LIKE CONCAT('%',@Key,'%'))";
List <TienCongDTO> lsTimKiem = new List <TienCongDTO>();
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@Key", Key);
try
{
con.Open();
SqlDataReader reader = null;
reader = cmd.ExecuteReader();
if (reader.HasRows == true)
{
while (reader.Read())
{
TienCongDTO pt = new TienCongDTO();
pt.Tendichvu = reader["tendichvu"].ToString();
pt.Matiencong = int.Parse(reader["matiencong"].ToString());
pt.Tiencong = Decimal.Parse(reader["tiencong"].ToString());
lsTimKiem.Add(pt);
}
}
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
Console.WriteLine(ex);
throw;
}
}
}
return(lsTimKiem);
}
public List <TienCongDTO> select()
{
string query = string.Empty;
query += "SELECT [MaLoaiTienCong],[TenLoaiTienCong],[TienCong]";
query += " FROM [dbo].[LOAITIENCONG]";
List <TienCongDTO> lsTienCong = new List <TienCongDTO>();
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
try
{
con.Open();
SqlDataReader reader = null;
reader = cmd.ExecuteReader();
if (reader.HasRows == true)
{
while (reader.Read())
{
TienCongDTO tc = new TienCongDTO();
tc.Maloaitiencong = int.Parse(reader["MaLoaiTienCong"].ToString());
tc.Tenloaitiencong = reader["TenLoaiTienCong"].ToString();
tc.Tiencong = int.Parse(reader["TienCong"].ToString());
lsTienCong.Add(tc);
}
}
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(null);
}
}
}
return(lsTienCong);
}
public List <TienCongDTO> selecttendichvu()
{
string query = string.Empty;
query += "SELECT [tendichvu]";
query += "FROM [TienCong]";
List <TienCongDTO> lstendichvu = new List <TienCongDTO>();
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
try
{
con.Open();
SqlDataReader reader = null;
reader = cmd.ExecuteReader();
if (reader.HasRows == true)
{
while (reader.Read())
{
TienCongDTO pt = new TienCongDTO();
pt.Tendichvu = reader["tendichvu"].ToString();
lstendichvu.Add(pt);
}
}
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
Console.WriteLine(ex);
throw;
}
}
}
return(lstendichvu);
}
private void LapPhieuSuaChua_Load(object sender, EventArgs e)
{
PhuTungDTO pt = new PhuTungDTO();
ptBus = new PhuTungBUS();
List <PhuTungDTO> ListTenPhuTung = ptBus.selectTenPhuTung();
if (ListTenPhuTung == null)
{
MessageBox.Show("Có lỗi khi lấy thông tin từ table PhuTung");
return;
}
cbPhuTung.DataSource = ListTenPhuTung;
TienCongDTO tc = new TienCongDTO();
tcBus = new TienCongBUS();
List <TienCongDTO> Listtendichvu = tcBus.selecttendichvu();
if (Listtendichvu == null)
{
MessageBox.Show("Có lỗi khi lấy thông tin từ table TienCong");
return;
}
cbDV.DataSource = Listtendichvu;
TiepNhanDTO tn = new TiepNhanDTO();
tnBus = new TiepNhanBUS();
List <TiepNhanDTO> ListBienSo = tnBus.selectBS();
if (ListBienSo == null)
{
MessageBox.Show("Có lỗi khi lấy thông tin từ table PhieuSuaChua");
return;
}
cbBS.DataSource = ListBienSo;
ngaysuachua.Format = DateTimePickerFormat.Custom;
ngaysuachua.CustomFormat = "yyyy/MM/dd";
txtThanhtien.Enabled = false;
//txtsoluong.Text = "1";
//thtien = Decimal.Parse(cbGTC.Text) + Decimal.Parse(lbPhuTung.Text) * int.Parse(txtsoluong.Text);
//txtThanhtien.Text = thtien.ToString();
}
private void button_them_Click(object sender, EventArgs e)
{
TienCongDTO tc = new TienCongDTO();
tc.Matiencong = int.Parse(txttmtc.Text);
tc.Tendichvu = txttdv.Text;
tc.Tiencong = Decimal.Parse(txttc.Text);
bool kq = tcBus.them(tc);
if (kq == false)
{
MessageBox.Show("Thêm thông tin thất bại. Vui lòng kiểm tra lại dữ liệu");
}
else
{
MessageBox.Show("Thêm thông tin thành công");
}
this.loadData_Vao_GridView();
txttmtc.Text = "";
txttdv.Text = "";
txttc.Text = "";
}
public static void InsertSalary(TienCongDTO tienCong)
{
MySqlConnection Conn = DatabaseConnectionDAO.connectionDatabase();
MySqlCommand cmd = new MySqlCommand("InsertSalary", Conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@_MaTienCong", MySqlDbType.VarChar, 10);
cmd.Parameters.Add("@_TenTienCong", MySqlDbType.VarChar, 100);
cmd.Parameters.Add("@_SoTienCong", MySqlDbType.Decimal);
cmd.Parameters["@_MaTienCong"].Value = tienCong.maTienCong;
cmd.Parameters["@_TenTienCong"].Value = tienCong.tenTienCong;
cmd.Parameters["@_SoTienCong"].Value = tienCong.soTienCong;
Conn.Open();
cmd.ExecuteNonQuery();
Conn.Close();
}
private void button_xoa_Click(object sender, EventArgs e)
{
DialogResult = MessageBox.Show("Bạn có chắc chắn muốn xóa ?", "Thông báo", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (DialogResult == DialogResult.Yes)
{
TienCongDTO tc = new TienCongDTO();
tc.Matiencong = int.Parse(txttmtc.Text);
bool kq = tcBus.xoa(tc);
if (kq == false)
{
MessageBox.Show("Xóa thông tin thất bại. Vui lòng kiểm tra lại dữ liệu");
}
else
{
MessageBox.Show("Xóa thông tin thành công");
}
this.loadData_Vao_GridView();
txttmtc.Text = "";
txttdv.Text = "";
txttc.Text = "";
}
}
private void bt_sua_Click(object sender, EventArgs e)
{
if (check_null())
{
TienCongDTO tc = new TienCongDTO();
tc.Maloaitiencong = int.Parse(tb_matiencong.Text);
tc.Tenloaitiencong = tb_tenloaitc.Text;
tc.Tiencong = int.Parse(tb_tiencong.Text);
bool kq = tcBus.sua(tc);
if (kq == false)
{
MessageBox.Show("Sửa tiền công thất bại. Vui lòng kiểm tra lại dữ liệu");
}
else
{
MessageBox.Show("Sửa tiền công thành công");
this.loadData_Vao_GridView();
}
CurrencyManager myCurrencyManager = (CurrencyManager)this.BindingContext[data_tiencong.DataSource];
myCurrencyManager.Refresh();
}
}
public bool sua(TienCongDTO tc)
{
bool re = tcDal.sua(tc);
return(re);
}
public bool them(TienCongDTO tc)
{
bool re = tcDal.them(tc);
return(re);
}
public bool xoa(TienCongDTO tc)
{
bool re = tcDao.xoa(tc);
return(re);
}
public static void addSalary(TienCongDTO tienCong)
{
TienCongDAO.InsertSalary(tienCong);
}
