private void FormTBPXemThongTin_Load(object sender, EventArgs e)
{
btnHuy.Enabled = false;
btnLuu.Enabled = false;
#region Load dữ liệu lên TB
SqlConnection myCon = new SqlConnection();
myCon.ConnectionString = ckn.chuoiKN();
myCon.Open();
SqlDataAdapter myAdapter = new SqlDataAdapter(nvbll.loadThongTinLenTB(FormDangNhap._tenDN), myCon);
DataTable myTable = new DataTable();
myAdapter.Fill(myTable);
//textBox1.Text = myTable.Rows[indexer]["username"].ToString();
//textBox2.Text = myTable.Rows[indexer]["password"].ToString();
tbMaNV.Text = myTable.Rows[0]["maNhanVien"].ToString().Trim();
tbTenNV.Text = myTable.Rows[0]["hoTen"].ToString().Trim();
dateTimePicker1.Text = myTable.Rows[0]["ngaySinh"].ToString();
cbbGioiTinh.Text = myTable.Rows[0]["gioiTinh"].ToString();
tbDiaChi.Text = myTable.Rows[0]["diaChiLienHe"].ToString();
tbSDT.Text = myTable.Rows[0]["soDienThoai"].ToString();
tbLoaiNV.Text = myTable.Rows[0]["loaiNhanVien"].ToString();
tbPhongBan.Text = myTable.Rows[0]["maPhongBan"].ToString();
tbTenPhongBan.Text = myTable.Rows[0]["tenPhongBan"].ToString().Trim();
myCon.Close();
#endregion
}
public void loadDataLuong(int nam, string maPDA = "PB005")
{
SqlConnection conn = new SqlConnection(ckn.chuoiKN());
try
{
conn.Open();
string sqlLoad = "select a.maNhanVien, a.hoTen, a.ngayBatDau,a.ngayKetThuc,COUNT(d.thang),sum(d.luongThang), d.daTraLuong from NhanVien a join ChamCong_PDA d on a.maNhanVien = d.maNhanVien join PhongBan c on a.maPhongBan = c.maPhongBan where d.nam = " + nam + " and c.maPhongBan ='" + maPDA + "' group by a.maNhanVien, a.hoTen ,a.ngayBatDau, a.ngayKetThuc, d.daTraLuong";
SqlDataAdapter dt = new SqlDataAdapter(sqlLoad, conn);
DataTable tb = new DataTable();
dt.Fill(tb);
dataGridView1.DataSource = tb;
conn.Close();
dataGridView1.Columns["Column2"].Visible = false;
}
catch (Exception ex)
{
MessageBox.Show("lỗi " + ex);
}
}
public void loadDataLuong()
{
SqlConnection conn = new SqlConnection(ckn.chuoiKN());
try
{
conn.Open();
string sqlLoad = "select a.maNhanVien, b.maPhanCong, a.hoTen, c.tenCongTrinh, b.ngayBatDauLam,b.ngayKetThuc,COUNT(d.thang),sum(d.luongThang), b.daTraLuong from dbo.NhanVien a join dbo.PhanCong b on a.maNhanVien = b.maNhanVien join dbo.CongTrinh c on b.maCongTrinh = c.maCongTrinh join dbo.ChamCong d on b.maPhanCong = d.maPhanCong join dbo.PhongBan e on a.maPhongBan=e.maPhongBan where c.tenCongTrinh = N'" + cbbTenCongTrinh.Text + "' and e.maPhongBan ='" + FormDangNhap._MaPB + "' group by b.maPhanCong,a.maNhanVien,a.hoTen ,b.ngayBatDauLam,b.ngayKetThuc, c.tenCongTrinh, b.daTraLuong";
SqlDataAdapter dt = new SqlDataAdapter(sqlLoad, conn);
DataTable tb = new DataTable();
dt.Fill(tb);
dataGridView1.DataSource = tb;
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("lỗi " + ex);
}
}
public void loadDataChamCong(int thang, int nam, string maPB = "PB005")
{
SqlConnection conn = new SqlConnection(ckn.chuoiKN());
try
{
conn.Open();
string sqlLoad = "select d.maChamCong, a.maNhanVien, a.hoTen,a.ngayBatDau, a.ngayKetThuc, d.soNgayLam, d.soNgayNghi, d.luongThang, d.D1, d.D2, d.D3, d.D4, d.D5, d.D6, d.D7, d.D8, d.D9, d.D10, d.D11, d.D12, d.D13, d.D14, d.D15, d.D16, d.D17, d.D18, d.D19, d.D20, d.D21, d.D22, d.D23, d.D24, d.D25, d.D26, d.D27, d.D28, d.D29, d.D30, d.D31 from NhanVien a join ChamCong_PDA d on a.maNhanVien = d.maNhanVien join PhongBan e on a.maPhongBan = e.maPhongBan where e.maPhongBan = '" + maPB + "' and d.thang = " + thang + " and d.nam = " + nam + " ";
SqlDataAdapter dt = new SqlDataAdapter(sqlLoad, conn);
DataTable tb = new DataTable();
dt.Fill(tb);
dataGridView1.DataSource = tb;
conn.Close();
}
catch (Exception e)
{
MessageBox.Show("lỗi " + e);
}
}
public void loadDataChamCong(string tenCT, int thang, int nam)
{
SqlConnection conn = new SqlConnection(ckn.chuoiKN());
try
{
conn.Open();
string sqlLoad = "select d.maChamCong, d.maPhanCong,a.maNhanVien, a.hoTen, a.soDienThoai, b.ngayBatDauLam, b.ngayKetThuc, d.soNgayLam, d.soNgayNghi, d.luongThang, d.D1, d.D2, d.D3, d.D4, d.D5, d.D6, d.D7, d.D8, d.D9, d.D10, d.D11, d.D12, d.D13, d.D14, d.D15, d.D16, d.D17, d.D18, d.D19, d.D20, d.D21, d.D22, d.D23, d.D24, d.D25, d.D26, d.D27, d.D28, d.D29, d.D30, d.D31 from NhanVien a join PhanCong b on a.maNhanVien=b.maNhanVien join CongTrinh c on b.maCongTrinh=c.maCongTrinh join ChamCong d on b.maPhanCong=d.maPhanCong join PhongBan e on a.maPhongBan=e.maPhongBan where c.trangThai = 'DXD' and d.thang= " + thang + "and d.nam = " + nam + " and c.tenCongTrinh= N'" + tenCT + "' and e.maPhongBan='" + FormDangNhap._MaPB + "' ";
SqlDataAdapter dt = new SqlDataAdapter(sqlLoad, conn);
DataTable tb = new DataTable();
dt.Fill(tb);
dataGridView1.DataSource = tb;
conn.Close();
}
catch (Exception e)
{
MessageBox.Show("lỗi " + e);
}
}
private void loadMaNV()
{
#region Lấy mã NV bằng Connection String
SqlConnection myCon = new SqlConnection();
myCon.ConnectionString = ckn.chuoiKN();
myCon.Open();//không có dòng này thì adapter sẽ tự open
string sqlMaPB = @"select top(1) *
from dbo.NhanVien
order by maNhanVien desc";
SqlDataAdapter myAdapter = new SqlDataAdapter(sqlMaPB, myCon);
DataTable myTable = new DataTable();
myAdapter.Fill(myTable);
_maTuTang = myTable.Rows[0]["maNhanVien"].ToString().Trim();
#endregion
}
private void tbTimKiemNV_TextChanged(object sender, EventArgs e)
{
if (_trangThaiCongTrinh == "TC")
{
if (cbbTK_NV.Text == "Theo Tên CT")
{
#region Tìm kiếm CT theo tên CT
SqlConnection myCon = new SqlConnection();
myCon.ConnectionString = ckn.chuoiKN();
myCon.Open();//không có dòng này thì adapter sẽ tự open
string sqlTimKiem = ctbll.timKiemCT_QLCT_TheoTen(tbTimKiemCT.Text, "DXD");
SqlDataAdapter myAdapter = new SqlDataAdapter(sqlTimKiem, myCon);
DataTable myTable = new DataTable();
myAdapter.Fill(myTable);
dataGridView1.DataSource = myTable;
myCon.Close();
#endregion
}
else if (cbbTK_NV.Text == "Theo Mã CT")
{
#region Tìm kiếm CT theo mã CT
SqlConnection myCon = new SqlConnection();
myCon.ConnectionString = ckn.chuoiKN();
myCon.Open();//không có dòng này thì adapter sẽ tự open
string sqlTimKiem = ctbll.timKiemCT_QLCT_TheoMa(tbTimKiemCT.Text, "DXD");
SqlDataAdapter myAdapter = new SqlDataAdapter(sqlTimKiem, myCon);
DataTable myTable = new DataTable();
myAdapter.Fill(myTable);
dataGridView1.DataSource = myTable;
myCon.Close();
#endregion
}
else if (cbbTK_NV.Text == "Theo Địa Điểm")
{
#region Tìm kiếm CT theo dia diẻm CT
SqlConnection myCon = new SqlConnection();
myCon.ConnectionString = ckn.chuoiKN();
myCon.Open();//không có dòng này thì adapter sẽ tự open
string sqlTimKiem = ctbll.timKiemCT_QLCT_TheoDiaDiem(tbTimKiemCT.Text, "DXD");
SqlDataAdapter myAdapter = new SqlDataAdapter(sqlTimKiem, myCon);
DataTable myTable = new DataTable();
myAdapter.Fill(myTable);
dataGridView1.DataSource = myTable;
myCon.Close();
#endregion
}
}
if (_trangThaiCongTrinh == "HT")
{
if (cbbTK_NV.Text == "Theo Tên CT")
{
#region Tìm kiếm CT theo tên CT
SqlConnection myCon = new SqlConnection();
myCon.ConnectionString = ckn.chuoiKN();
myCon.Open();//không có dòng này thì adapter sẽ tự open
string sqlTimKiem = ctbll.timKiemCT_QLCT_TheoTen(tbTimKiemCT.Text, "HT");
SqlDataAdapter myAdapter = new SqlDataAdapter(sqlTimKiem, myCon);
DataTable myTable = new DataTable();
myAdapter.Fill(myTable);
dataGridView1.DataSource = myTable;
myCon.Close();
#endregion
}
else if (cbbTK_NV.Text == "Theo Mã CT")
{
#region Tìm kiếm CT theo mã CT
SqlConnection myCon = new SqlConnection();
myCon.ConnectionString = ckn.chuoiKN();
myCon.Open();//không có dòng này thì adapter sẽ tự open
string sqlTimKiem = ctbll.timKiemCT_QLCT_TheoMa(tbTimKiemCT.Text, "HT");
SqlDataAdapter myAdapter = new SqlDataAdapter(sqlTimKiem, myCon);
DataTable myTable = new DataTable();
myAdapter.Fill(myTable);
dataGridView1.DataSource = myTable;
myCon.Close();
#endregion
}
else if (cbbTK_NV.Text == "Theo Địa Điểm")
{
#region Tìm kiếm CT theo dia diẻm CT
SqlConnection myCon = new SqlConnection();
myCon.ConnectionString = ckn.chuoiKN();
myCon.Open();//không có dòng này thì adapter sẽ tự open
string sqlTimKiem = ctbll.timKiemCT_QLCT_TheoDiaDiem(tbTimKiemCT.Text, "HT");
SqlDataAdapter myAdapter = new SqlDataAdapter(sqlTimKiem, myCon);
DataTable myTable = new DataTable();
myAdapter.Fill(myTable);
dataGridView1.DataSource = myTable;
myCon.Close();
#endregion
}
}
}
