public static void UpdateHopDongTimeOut()
{
DataTable tb = new DataTable();
string date = DateTime.Today.ToString("yyyy-MM-dd");
string query = "Select MaHD from HopDong where MaTTHD <>3 and NgayHetHan<'" + date + "' and MaHD not in (select hdhh.MaHD from HopDongHetHan hdhh)";
//string query = @"Update HopDong Set MaTTHD = 3 Where MaNV = '" + hd.MaNV + "' AND MaTTHD=1";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
tb = dataProvider.ExecuteQuery_DataTble(query);
if (tb.Rows.Count > 0)
{
query = "";
foreach (DataRow row in tb.Rows)
{
query += @"INSERT INTO HopDongHetHan (MaHD, TinhTrangGiaiQuyet) VALUES('" + row[0].ToString() + "', 0); ";
}
dataProvider.ExecuteUpdateQuery(query); query = "";
foreach (DataRow row in tb.Rows)
{
query += @"UPDATE HopDong SET HopDong.MaTTHD=2 Where MaHD= '" + row[0].ToString() + "' ";
}
dataProvider.ExecuteUpdateQuery(query);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static void AddCTCC(DataTable dt, string macc)
{
string query = "";
foreach (DataRow row in dt.Rows)
{
query += " INSERT INTO ChiTietChamCong(MaChamCong, MaNV, NgayCong, TrangThai) VALUES('" + macc + "', '" + row["MaNV"].ToString() + "', " + row["NgayCong"].ToString() + ", 0) ";
}
DataTable tb = DAO.ChamCongMacDinhDAO.GetDefault();
string query2 = "";
foreach (DataRow row in tb.Rows)
{
query2 += " INSERT INTO ChiTietChamCong(MaChamCong, MaNV, NgayCong, TrangThai) VALUES('" + macc + "', '" + row[0].ToString() + "', 35, 0) ";
}
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
dataProvider.ExecuteUpdateQuery(query);
dataProvider.ExecuteUpdateQuery(query2);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dataProvider.disconnect();
}
}
public static void RemoveDefault(List <string> listNV)
{
bool isExists = false;
string query = "DELETE From ChamCongMacDinh Where MaNV in (";
foreach (string str in listNV)
{
if (isExists)
{
query += ",'" + str + "'";
}
else
{
query += "'" + str + "'";
isExists = true;
}
}
query += ")";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
dataProvider.ExecuteUpdateQuery(query);
}
catch (Exception ex)
{
MessageBox.Show(ex.StackTrace);
}
finally
{
dataProvider.disconnect();
}
}
public static void UnLayoffNhanVien(DataTable dt)
{
string query = @"UPDATE HopDong SET HopDong.MaTTHD=1 ";
bool IsExists = false;
foreach (DataRow row in dt.Rows)
{
if (IsExists == false)
{
query += " WHERE HopDong.MaNV IN ('" + row[0].ToString() + "' ";
IsExists = true;
}
else
{
query += " , '" + row[0].ToString() + "' ";
}
}
query += ") AND MaHD=(select TOP 1 hd2.MaHD from HopDong hd2 where hd2.MaNV=HopDong.MaNV order by hd2.MaHD DESC)";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.ExecuteUpdateQuery(query);
MessageBox.Show("Hồi phục nhân viên thành công!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static void UnLayoffNhanVien(DataTable dt)
{
string query = @"UPDATE NhanVien SET MaTT=1 ";
bool IsExists = false;
foreach (DataRow row in dt.Rows)
{
if (IsExists == false)
{
query += " WHERE MaNV IN ('" + row[0].ToString() + "' ";
IsExists = true;
}
else
{
query += " , '" + row[0].ToString() + "' ";
}
}
query += ") ";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.ExecuteUpdateQuery(query);
DAO.HopDongDAO.UnLayoffNhanVien(dt);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static void AddNhanVienMulti(NhanVienDTO nv, ThongTinChiTietNhanVienDTO ttct)
{
string query = @"INSERT INTO NhanVien (MaNV, HoTen, NgayVaoLam, MaPB, LuongCB, HinhAnh, MaTT) VALUES( '" + nv.MaNV + "', N'" + nv.HoTen + "', '" + nv.NgayVaoLam.ToString("yyyy-MM-dd") + "', " + nv.MaPB + ", '" + nv.LuongCanBan + "', N'" + nv.HinhAnh + "', " + nv.MaTT.ToString() + "); ";
string query2 = @"INSERT INTO ThongTinChiTietNhanVien (MaNV, MaGT, CMND, NgaySinh, NoiSinh, DienThoai, SoNha, Duong, PhuongXa, QuanHuyen, TinhTP, QuocGia, MaDT, MaTG, SoTheATM, Email) VALUES( '" + nv.MaNV + "', '" + ttct.MaGT.ToString() + "', '" + ttct.CMND + "', '" + ttct.NgaySinh.ToString("yyyy-MM-dd") + "', N'" + ttct.NoiSinh + "', '" + ttct.DienThoai + "', '" + ttct.SoNha + "', N'" + ttct.Duong + "', N'" + ttct.PhuongXa + "', " + ttct.QuanHuyen.Trim() + ", " + ttct.TinhTP.Trim() + ", " + ttct.QuocGia.Trim() + ", " + ttct.MaDT.Trim() + ", " + ttct.MaTG.Trim() + ", '" + ttct.SoTheATM + "', N'" + ttct.Email + "' ); ";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.ExecuteUpdateQuery(query);
dataProvider.ExecuteUpdateQuery(query2);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static void SetIsUpdated(string username)
{
string query = @"Update NguoiDung Set IsUpdated='false'";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.ExecuteUpdateQuery(query);
query = @"Update NguoiDung Set IsUpdated='true' where Username='******'";
dataProvider.ExecuteUpdateQuery(query);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static void AddDefault(List <string> listNV)
{
string query = "";
foreach (string str in listNV)
{
query += " INSERT INTO ChamCongMacDinh(MaNV) VALUES('" + str + "') ";
}
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.connect();
dataProvider.ExecuteUpdateQuery(query);
}
catch (Exception ex)
{
MessageBox.Show(ex.StackTrace);
}
finally
{
dataProvider.disconnect();
}
}
public void deletingKH(string MAKH)
{
string sql = "Update KhachHang set Daxoa = 1 where @Makh = MaKH";
SqlParameter[] par = new SqlParameter[1];
par[0] = new SqlParameter("@Makh", MAKH);
DataProvider.ExecuteUpdateQuery(sql, par);
}
public static bool XoaLoaiSanPham(string maLoaiSP)
{
string query = "UPDATE tblLoaiSanPham SET TrangThai=0 WHERE MaLoaiSP=@MaLoaiSP";
SqlParameter[] parameter = new SqlParameter[1];
parameter[0] = new SqlParameter("@MaLoaiSP", maLoaiSP);
return(DataProvider.ExecuteUpdateQuery(query, parameter) == 1);
}
public static bool XoaSP(string id)
{
string query = "UPDATE dbo.SanPham SET TrangThai=0 WHERE MASP=@ID";
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@ID", id);
return(DataProvider.ExecuteUpdateQuery(query, param) == 1);
}
public static Boolean Delete(String maHD)
{
String query = "UPDATE HoaDon SET TrangThai = 0 WHERE MaHD = @MaHD";
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@MaHD", maHD);
return(DataProvider.ExecuteUpdateQuery(query, param) == 1);
}
public static int UpdateTotalVisitors(int i)
{
string query = "UPDATE TotalVisitors SET VisitorNumber = VisitorNumber + @i";
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@i", i);
return(DataProvider.ExecuteUpdateQuery(query, param));
}
public static bool Xoasp(string tensp)
{
string query = "UPDATE SanPham SET TrangThai = 0 WHERE TenSP = @tensp";
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@tensp", tensp);
return(DataProvider.ExecuteUpdateQuery(query, param) == 1);
}
public static void NewHopDong(HopDongDTO hd)
{
string query = @"Update HopDong Set MaTTHD = 3 Where MaNV = '" + hd.MaNV + "' AND MaTTHD=1";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.ExecuteUpdateQuery(query);
query = @"INSERT INTO HopDong (MaHD, MaNV, MaLoaiHD, NgayKyHD, NgayHetHan, MaTTHD) VALUES( '" + hd.MaHD + "', '" + hd.MaNV + "', " + hd.MaLoaiHD + ", '" + hd.NgayKyHD.ToString("yyyy-MM-dd") + "', '" + hd.NgayHetHan.ToString("yyyy-MM-dd") + "', '" + hd.MaTTHD + "' ); ";
dataProvider.ExecuteUpdateQuery(query);
MessageBox.Show("Lập hợp đồng thành công!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void DeleteStaff(string Manv)
{
string sql = "Update Nhanvien set Daxoa = 1 where @Manv = Manv";
SqlParameter[] par = new SqlParameter[1];
par[0] = new SqlParameter("@Manv", Manv);
DataProvider.ExecuteUpdateQuery(sql, par);
}
public static Boolean Delete(String tenTaiKhoan)
{
String query = "UPDATE TaiKhoan SET TrangThai = 0 WHERE TenTaiKhoan = @TenTaiKhoan";
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@TenTaiKhoan", tenTaiKhoan);
return(DataProvider.ExecuteUpdateQuery(query, param) == 1);
}
public static Boolean Delete(String maSP)
{
String query = "UPDATE SanPham SET TrangThai = 0 WHERE MaSP = @MaSP";
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@MaSP", maSP);
return(DataProvider.ExecuteUpdateQuery(query, param) == 1);
}
public static bool XoaMon(string maMon)
{
string query = "UPDATE tblMonAn SET TrangThai=0 WHERE MaMon=@maMon ";
SqlParameter[] parameter = new SqlParameter[1];
parameter[0] = new SqlParameter("@maMon", maMon);
return(DataProvider.ExecuteUpdateQuery(query, parameter) == 1);
}
public static bool LayTrangThai(SinhVienDTO sv)
{
string query = "UPDATE ThongTinSV SET TrangThai = 1 WHERE Ma_SV = @Ma_SV AND TrangThai = 0";
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@Ma_SV", sv.Ma_SV);
return(DataProvider.ExecuteUpdateQuery(query, param) == 1);
}
public static bool CapNhatSoSinhVienKhiLamMoi(LopHocDTO lh)
{
string query = "UPDATE LopHoc SET SoSinhVien = 0 WHERE Ma_Lop = @Ma_Lop ";
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@Ma_Lop", lh.Ma_Lop);
return(DataProvider.ExecuteUpdateQuery(query, param) == 1);
}
public static void ShutdownBoPhan(DTO.BoPhanDTO bp)
{
string query = @"UPDATE BoPhan SET MaTT=2 Where MaBP = '" + bp.MaBP + "'";
string query2 = @"UPDATE PhongBan SET MaTT=2 Where MaBP = '" + bp.MaBP + "'";
DataProvider dataProvider = new DataProvider();
try
{
dataProvider.ExecuteUpdateQuery(query);
dataProvider.ExecuteUpdateQuery(query2);
MessageBox.Show("Đã ngừng hoạt động bộ phận!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static bool XoaVideo(string maVideo)
{
string query = "UPDATE Video SET TrangThai = 0 WHERE MaVideo = @MaVideo";
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@TenTaiKhoan", maVideo);
return(DataProvider.ExecuteUpdateQuery(query, param) == 1);
}
public static bool XoaNguyenLieu(string maNL)
{
string query = "UPDATE tblNguyenLieu SET TrangThai = 0 WHERE MaNL = @MaNL";
SqlParameter[] pars = new SqlParameter[1];
pars[0] = new SqlParameter("@MaNL", maNL);
return(DataProvider.ExecuteUpdateQuery(query, pars) == 1);
}
//Cập nhật trạng thái của hóa đơn(0: Chờ xử lý - 1: Đã xử lý)
public static bool CapNhatTrangThaiHD(int maHD, int tThai)
{
string query = "UPDATE tblHDXuat SET TrangThai = @TrangThai WHERE MaHDXuat = @MaHDXuat";
SqlParameter[] pars = new SqlParameter[2];
pars[0] = new SqlParameter("@TrangThai", tThai);
pars[1] = new SqlParameter("@MaHDXuat", maHD);
return(DataProvider.ExecuteUpdateQuery(query, pars) == 1);
}
public static bool SuaSV(SinhVienDTO sv)
{
string query = "UPDATE ThongTinSV SET Ten_SV=@Ten_SV WHERE Ma_SV=@Ma_SV";
SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("@Ma_SV", sv.Ma_SV);
param[1] = new SqlParameter("@Ten_SV", sv.Ten_SV);
return(DataProvider.ExecuteUpdateQuery(query, param) == 1);
}
//0: đã hủy || 1: chưa hủy
public static bool CapNhatTrangThaiHuy(int maHDNhap, int tTai)
{
string query = "UPDATE tblHDNhap SET TonTai = @TonTai WHERE MaHDNhap = @MaHDNhap";
SqlParameter[] pars = new SqlParameter[2];
pars[0] = new SqlParameter("@MaHDNhap", maHDNhap);
pars[1] = new SqlParameter("@TonTai", tTai);
return(DataProvider.ExecuteUpdateQuery(query, pars) == 1);
}
public static bool CapNhatHDNhap(int maHD, int tTien)
{
string query = "UPDATE tblHDNhap SET TongTien = @TongTien WHERE MaHDNhap = @MaHDNhap";
SqlParameter[] pars = new SqlParameter[2];
pars[0] = new SqlParameter("@MaHDNhap", maHD);
pars[1] = new SqlParameter("@TongTien", tTien);
return(DataProvider.ExecuteUpdateQuery(query, pars) == 1);
}
public static bool CapNhatLSP(LoaiSanPhamDTO lsp, string id)
{
string query = "UPDATE dbo.LoaiSanPham SET TenLoaiSP=@TenLoaiSP WHERE MaLoaiSP=@ID";
SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("@TenLoaiSP", lsp.TenLoaiSP);
param[1] = new SqlParameter("@ID", id);
return(DataProvider.ExecuteUpdateQuery(query, param) == 1);
}
public static bool SuaMatKhau(string tenTK, string mKMoi)
{
string query = "UPDATE tblTaiKhoan SET MatKhau=@MatKhau WHERE TenTaiKhoan=@TenTaiKhoan";
SqlParameter[] parameter = new SqlParameter[2];
parameter[0] = new SqlParameter("@TenTaiKhoan", tenTK);
parameter[1] = new SqlParameter("@MatKhau", mKMoi);
return(DataProvider.ExecuteUpdateQuery(query, parameter) == 1);
}