public static List <PhieuNhanDTO> LoadPhieuNhan()
{
string sQuery = @"SELECT * FROM PhieuNhan, DonViNhan WHERE PhieuNhan.MaDVN = DonViNhan.MaDVN";
con = DataProvider.Connect();
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <PhieuNhanDTO> lstpn = new List <PhieuNhanDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
PhieuNhanDTO pn = new PhieuNhanDTO();
pn.IMaPN = int.Parse(dt.Rows[i]["MaPN"].ToString());
pn.DtNgayNhan = DateTime.Parse(dt.Rows[i]["NgayNhan"].ToString());
pn.SNguoiNhan = dt.Rows[i]["NguoiNhan"].ToString();
pn.IMaDVN = int.Parse(dt.Rows[i]["MaDVN"].ToString());
pn.STenDVN = dt.Rows[i]["TenDVN"].ToString();
lstpn.Add(pn);
}
DataProvider.CloseConnect(con);
return(lstpn);
}
// Load Sinh Vien
public static List <HocSinhDTO> LoadSinhVien()
{
// Khai báo truy vấn
string sQuery = "SELECT * FROM HocSinh, Lop WHERE HocSinh.MaLop = Lop.MaLop";
// Tạo đối tượng kết nối
con = DataProvider.Connect();
// Truy vấn
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
// Tạo list DTOSinhVien
List <HocSinhDTO> lstsv = new List <HocSinhDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
HocSinhDTO sv = new HocSinhDTO();
sv.IMaHS = int.Parse(dt.Rows[i]["MaHS"].ToString());
sv.SHoTen = dt.Rows[i]["Hoten"].ToString();
sv.SGioiTinh = dt.Rows[i]["GioiTinh"].ToString();
sv.DtNgaySinh = DateTime.Parse(dt.Rows[i]["NgaySinh"].ToString());
sv.SDiaChi = dt.Rows[i]["DiaChi"].ToString();
sv.IMaLop = int.Parse(dt.Rows[i]["MaLop"].ToString());
sv.STenLop = dt.Rows[i]["TenLop"].ToString();
lstsv.Add(sv);
}
// Đóng kết nối
DataProvider.CloseConnect(con);
return(lstsv);
}
public static List <LoaiCongVanDTO> LoadLoaiCongVan()
{
// Khai báo truy vấn
string sQuery = @"SELECT * FROM LoaiCongVan";
// Tạo đối tượng kết nối
con = DataProvider.Connect();
// Truy vấn
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
// Tạo list DTOSinhVien
List <LoaiCongVanDTO> lstlcv = new List <LoaiCongVanDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
LoaiCongVanDTO lcv = new LoaiCongVanDTO();
lcv.IMaLCV = int.Parse(dt.Rows[i]["MaLCV"].ToString());
lcv.STenLCV = dt.Rows[i]["TenLCV"].ToString();
lstlcv.Add(lcv);
}
// Đóng kết nối
DataProvider.CloseConnect(con);
return(lstlcv);
}
public static List <MonHocDTO> LoadMonHoc()
{
string sQuery = string.Format(
@"SELECT * FROM MonHoc mh
INNER JOIN HocKy hk
ON hk.MaHK = mh.MaHK
INNER JOIN Khoi k
ON k.MaKhoi = mh.MaKhoi"
);
con = DataProvider.Connect();
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <MonHocDTO> lstmh = new List <MonHocDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
MonHocDTO monhocDTO = new MonHocDTO();
monhocDTO.IMaMH = int.Parse(dt.Rows[i]["MaMH"].ToString());
monhocDTO.STenMH = dt.Rows[i]["TenMH"].ToString();
monhocDTO.ISoTiet = int.Parse(dt.Rows[i]["SoTiet"].ToString());
monhocDTO.IMaHK = int.Parse(dt.Rows[i]["MaHK"].ToString());
monhocDTO.STenHK = dt.Rows[i]["TenHK"].ToString();
monhocDTO.IMaKhoi = int.Parse(dt.Rows[i]["MaKhoi"].ToString());
monhocDTO.STenKhoi = dt.Rows[i]["TenKhoi"].ToString();
lstmh.Add(monhocDTO);
}
// Đóng kết nối
DataProvider.CloseConnect(con);
return(lstmh);
}
public List <qlbhDTO> GetNhanVien(string sql)
{
List <qlbhDTO> list = new List <qlbhDTO>();
int maLoaiSp;
string tenLoaiSp;
dp.Connect();
try
{
SqlDataReader dr = dp.executeReader(sql);
while (dr.Read())
{
maLoaiSp = dr.GetInt32(0);
tenLoaiSp = dr.GetString(1);
qlbhDTO ql = new qlbhDTO(maLoaiSp, tenLoaiSp);//-> gọi DTO, DTO lấy dữ liệu từ Data
list.Add(ql);
}
dr.Close();
return(list);// trả về List đã có dữ liệu
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dp.DisConnect();
}
}
public static List <VanThuDTO> LoadVanThu()
{
string sQuery = @"SELECT *
FROM VanThu vt
INNER JOIN CoQuan cq
ON cq.MaCQ = vt.MaCQ
INNER JOIN LoaiCongVan lcv
ON lcv.MaLCV = vt.MaLCV" ;
con = DataProvider.Connect();
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <VanThuDTO> lstvt = new List <VanThuDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
VanThuDTO vt = new VanThuDTO();
vt.IMaVT = int.Parse(dt.Rows[i]["MaVT"].ToString());
vt.STieuDe = dt.Rows[i]["TieuDe"].ToString();
vt.IMaCQ = int.Parse(dt.Rows[i]["MaCQ"].ToString());
vt.STenCQ = dt.Rows[i]["TenCQ"].ToString();
vt.IMaLCV = int.Parse(dt.Rows[i]["MaLCV"].ToString());
vt.STenLCV = dt.Rows[i]["TenLCV"].ToString();
lstvt.Add(vt);
}
DataProvider.CloseConnect(con);
return(lstvt);
}
public static List <NienKhoaDTO> LoadcbNienKhoa()
{
string sQuery = @"SELECT * FROM NienKhoa";
con = DataProvider.Connect();
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
// Tạo list Niên Khóa
List <NienKhoaDTO> lstNienKhoa = new List <NienKhoaDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
NienKhoaDTO nienkhoa = new NienKhoaDTO();
nienkhoa.IMaNienKhoa = int.Parse(dt.Rows[i]["MaNienKhoa"].ToString());
nienkhoa.STenNienKhoa = dt.Rows[i]["TenNienKhoa"].ToString();
//nienkhoa.SGhiChu = dt.Rows[i]["GhiChu"].ToString();
lstNienKhoa.Add(nienkhoa);
}
DataProvider.CloseConnect(con);
return(lstNienKhoa);
}
public static List <PhieuGuiDTO> LoadPhieuGui()
{
string sQuery = @"SELECT * FROM PhieuGui, CoQuan WHERE PhieuGui.MaCQ = CoQuan.MaCQ";
con = DataProvider.Connect();
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <PhieuGuiDTO> lstpg = new List <PhieuGuiDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
PhieuGuiDTO pg = new PhieuGuiDTO();
pg.IMaPG = int.Parse(dt.Rows[i]["MaPG"].ToString());
pg.DtNgayGui = DateTime.Parse(dt.Rows[i]["NgayGui"].ToString());
pg.SNguoiGui = dt.Rows[i]["NguoiGui"].ToString();
pg.IMaCQ = int.Parse(dt.Rows[i]["MaCQ"].ToString());
pg.STenCQ = dt.Rows[i]["TenCQ"].ToString();
lstpg.Add(pg);
}
DataProvider.CloseConnect(con);
return(lstpg);
}
public static List <VanThuDTO> TimKiemvanThu(string key)
{
string sQuery = string.Format("SELECT * FROM VanThu WHERE TieuDe like N'%{0}%'", key);
con = DataProvider.Connect();
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <VanThuDTO> lstvt = new List <VanThuDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
VanThuDTO vt = new VanThuDTO();
vt.IMaVT = int.Parse(dt.Rows[i]["MaVT"].ToString());
vt.STieuDe = dt.Rows[i]["TieuDe"].ToString();
vt.IMaCQ = int.Parse(dt.Rows[i]["MaCQ"].ToString());
vt.IMaLCV = int.Parse(dt.Rows[i]["MaLCV"].ToString());
lstvt.Add(vt);
}
// Đóng kết nối
DataProvider.CloseConnect(con);
return(lstvt);
}
public static List <NienKhoaDTO> LoadNienKhoa()
{
// Khai báo truy vấn
string sQuery = @"SELECT * FROM NienKhoa ";
// Tạo đối tượng kết nối
con = DataProvider.Connect();
// Truy vấn
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
// Tạo list DTOSinhVien
List <NienKhoaDTO> lstnienkhoa = new List <NienKhoaDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
NienKhoaDTO nienkhoa = new NienKhoaDTO();
nienkhoa.IMaNienKhoa = int.Parse(dt.Rows[i]["MaNienKhoa"].ToString());
nienkhoa.STenNienKhoa = dt.Rows[i]["TenNienKhoa"].ToString();
nienkhoa.SGhiChu = dt.Rows[i]["GhiChu"].ToString();
lstnienkhoa.Add(nienkhoa);
}
// Đóng kết nối
DataProvider.CloseConnect(con);
return(lstnienkhoa);
}
/// <summary>
/// Load combobox
/// </summary>
/// <returns> list Lớp</returns>
public static List <LopDTO> LoadcbLop()
{
// Khai báo truy vấn.
string sQuery = string.Format(@"SELECT * FROM Lop");
// Mở kết nối
con = DataProvider.Connect();
// Truy vấn
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
// Tạo list LopDTO
List <LopDTO> lstLop = new List <LopDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
LopDTO lop = new LopDTO();
lop.IMaLop = int.Parse(dt.Rows[i]["MaLop"].ToString());
lop.STenLop = dt.Rows[i]["TenLop"].ToString();
//lop.ISiSo = int.Parse(dt.Rows[i]["SiSo"].ToString());
lstLop.Add(lop);
}
// Đóng kết nối
DataProvider.CloseConnect(con);
return(lstLop);
}
// Load Cơ Quan
public static List <CoQuanDTO> LoadCoQuan()
{
// Khai báo truy vấn
string sQuery = @"SELECT * FROM CoQuan";
// Tạo đối tượng kết nối
con = DataProvider.Connect();
// Truy vấn
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
// Tạo list DTOSinhVien
List <CoQuanDTO> lstcq = new List <CoQuanDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
CoQuanDTO cq = new CoQuanDTO();
cq.IMaCQ = int.Parse(dt.Rows[i]["MaCQ"].ToString());
cq.STenCQ = dt.Rows[i]["TenCQ"].ToString();
cq.SDiaChi = dt.Rows[i]["DiaChi"].ToString();
cq.ISDT = int.Parse(dt.Rows[i]["SDT"].ToString());
cq.SNguoiDungDau = dt.Rows[i]["NguoiDungDau"].ToString();
lstcq.Add(cq);
}
// Đóng kết nối
DataProvider.CloseConnect(con);
return(lstcq);
}
public static List <CoQuanDTO> LoadcbCQ()
{
// Khai báo truy vấn.
string sQuery = @"SELECT * FROM CoQuan";
// Mở kết nối
con = DataProvider.Connect();
// Truy vấn
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
// Tạo list LopDTO
List <CoQuanDTO> lstCQ = new List <CoQuanDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
CoQuanDTO cq = new CoQuanDTO();
cq.IMaCQ = int.Parse(dt.Rows[i]["MaCQ"].ToString());
cq.STenCQ = dt.Rows[i]["TenCQ"].ToString();
lstCQ.Add(cq);
}
// Đóng kết nối
DataProvider.CloseConnect(con);
return(lstCQ);
}
public static List <DonViNhanDTO> LoadDonViNhan()
{
string sQuery = @"SELECT * FROM DonViNhan, CoQuan WHERE DonViNhan.MaCQ = CoQuan.MaCQ";
con = DataProvider.Connect();
DataTable dt = DataProvider.LayDataTable(sQuery, con);
if (dt.Rows.Count == 0)
{
return(null);
}
List <DonViNhanDTO> lstdvn = new List <DonViNhanDTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
DonViNhanDTO dvn = new DonViNhanDTO();
dvn.IMaDVN = int.Parse(dt.Rows[i]["MaDVN"].ToString());
dvn.STenDVN = dt.Rows[i]["TenDVN"].ToString();
dvn.SDiaChi = dt.Rows[i]["DiaChi"].ToString();
dvn.ISDT = int.Parse(dt.Rows[i]["SDT"].ToString());
dvn.DtNgayNhan = DateTime.Parse(dt.Rows[i]["NgayNhan"].ToString());
dvn.IMaCQ = int.Parse(dt.Rows[i]["MaCQ"].ToString());
dvn.STenCQ = dt.Rows[i]["TenCQ"].ToString();
lstdvn.Add(dvn);
}
DataProvider.CloseConnect(con);
return(lstdvn);
}
public static bool SuaPhieuGui(PhieuGuiDTO pgDTO)
{
string sQuery = string.Format("UPDATE PhieuGui SET NgayGui=N'{0}', NguoiGui=N'{1}', MaCQ={2} WHERE MaPG={3}", pgDTO.DtNgayGui, pgDTO.SNguoiGui, pgDTO.IMaCQ, pgDTO.IMaPG);
con = DataProvider.Connect();
try
{
DataProvider.ExecuteQueriesNonQuery(sQuery, con);
DataProvider.CloseConnect(con);
return(true);
}
catch (Exception ex) { DataProvider.CloseConnect(con); return(false); }
}
public static bool XoaPhieuGui(PhieuGuiDTO pgDTO)
{
string sQuery = string.Format("DELETE FROM PhieuGui WHERE MapG={0}", pgDTO.IMaPG);
con = DataProvider.Connect();
try
{
DataProvider.ExecuteQueriesNonQuery(sQuery, con);
DataProvider.CloseConnect(con);
return(true);
}
catch (Exception ex) { DataProvider.CloseConnect(con); return(false); }
}
public static bool ThemPhieuGui(PhieuGuiDTO pgDTO)
{
string sQuery = string.Format(@"INSERT INTO PhieuGui VALUES (N'{0}', N'{1}', {2})", pgDTO.DtNgayGui, pgDTO.SNguoiGui, pgDTO.IMaCQ);
con = DataProvider.Connect();
try
{
DataProvider.ExecuteQueriesNonQuery(sQuery, con);
DataProvider.CloseConnect(con);
return(true);
}
catch (Exception ex) { DataProvider.CloseConnect(con); return(false); }
}
public static bool XoaDonViNhan(DonViNhanDTO dvnDTO)
{
string sQuery = string.Format("DELETE FROM DonViNhan WHERE MaDVN={0}", dvnDTO.IMaDVN);
con = DataProvider.Connect();
try
{
DataProvider.ExecuteQueriesNonQuery(sQuery, con);
DataProvider.CloseConnect(con);
return(true);
}
catch (Exception ex) { DataProvider.CloseConnect(con); return(false); }
}
public static bool XoaHocKy(HocKyDTO hkDTO)
{
string sQuery = string.Format("DELETE HocKy WHERE MaHK={0}", hkDTO.IMaHK);
con = DataProvider.Connect();
try
{
DataProvider.ExecuteQueriesNonQuery(sQuery, con);
DataProvider.CloseConnect(con);
return(true);
}
catch (Exception ex) { DataProvider.CloseConnect(con); return(false); }
}
public static bool SuaHocKy(HocKyDTO hkDTO)
{
string sQuery = string.Format("UPDATE HocKy SET TenHK=N'{0}', GhiChu=N'{1}' WHERE MaHK={2}", hkDTO.STenHK, hkDTO.SGhiChu, hkDTO.IMaHK);
con = DataProvider.Connect();
try
{
DataProvider.ExecuteQueriesNonQuery(sQuery, con);
DataProvider.CloseConnect(con);
return(true);
}
catch (Exception ex) { DataProvider.CloseConnect(con); return(false); }
}
public static bool ThemHocKy(HocKyDTO hkDTO)
{
string sQuery = string.Format("INSERT INTO HocKy VALUES (N'{0}', N'{1}')", hkDTO.STenHK, hkDTO.SGhiChu);
con = DataProvider.Connect();
try
{
DataProvider.ExecuteQueriesNonQuery(sQuery, con);
DataProvider.CloseConnect(con);
return(true);
}
catch (Exception ex) { DataProvider.CloseConnect(con); return(false); }
}
public static bool XoaVanThu(VanThuDTO vtDTO)
{
string sQuery = string.Format("DELETE FROM VanThu WHERE MaVT={0}", vtDTO.IMaVT);
con = DataProvider.Connect();
try
{
DataProvider.ExecuteQueriesNonQuery(sQuery, con);
DataProvider.CloseConnect(con);
return(true);
}
catch (Exception ex) { DataProvider.CloseConnect(con); return(false); }
}
public static bool ThemDonViNhan(DonViNhanDTO dvnDTO)
{
string sQuery = string.Format("INSERT INTO DonViNhan VALUES (N'{0}', N'{1}', {2}, N'{3}', '{4}')", dvnDTO.STenDVN, dvnDTO.SDiaChi, dvnDTO.ISDT, dvnDTO.DtNgayNhan, dvnDTO.IMaCQ);
con = DataProvider.Connect();
try
{
DataProvider.ExecuteQueriesNonQuery(sQuery, con);
DataProvider.CloseConnect(con);
return(true);
}
catch (Exception ex) { DataProvider.CloseConnect(con); return(false); }
}
public static bool SuaVanThu(VanThuDTO vtDTO)
{
string sQuery = string.Format("UPDATE VanThu SET TieuDe=N'{0}', MaCQ={1}, MaLCV={2} WHERE MaVT={3}", vtDTO.STieuDe, vtDTO.IMaCQ, vtDTO.IMaLCV, vtDTO.IMaVT);
con = DataProvider.Connect();
try
{
DataProvider.ExecuteQueriesNonQuery(sQuery, con);
DataProvider.CloseConnect(con);
return(true);
}
catch (Exception ex) { DataProvider.CloseConnect(con); return(false); }
}
public static bool SuaDonViNhan(DonViNhanDTO dvnDTO)
{
string sQuery = string.Format("UPDATE DonViNhan SET TenDVN=N'{0}', DiaChi=N'{1}', SDT={2}, NgayNhan=N'{3}', MaCQ={4} WHERE MaDVN={5}", dvnDTO.STenDVN, dvnDTO.SDiaChi, dvnDTO.ISDT, dvnDTO.DtNgayNhan, dvnDTO.IMaCQ, dvnDTO.IMaDVN);
con = DataProvider.Connect();
try
{
DataProvider.ExecuteQueriesNonQuery(sQuery, con);
DataProvider.CloseConnect(con);
return(true);
}
catch (Exception ex) { DataProvider.CloseConnect(con); return(false); }
}
public static bool ThemVanThu(VanThuDTO vtDTO)
{
string sQuery = string.Format("INSERT INTO VanThu VALUES (N'{0}', {1}, {2})", vtDTO.STieuDe, vtDTO.IMaCQ, vtDTO.IMaLCV);
con = DataProvider.Connect();
try
{
DataProvider.ExecuteQueriesNonQuery(sQuery, con);
DataProvider.CloseConnect(con);
return(true);
}
catch (Exception ex) { DataProvider.CloseConnect(con); return(false); }
}
public static bool XoaMonHoc(MonHocDTO monhocDTO)
{
string sQuery = string.Format(@"DELETE MonHoc WHERE MaMH={0}", monhocDTO.IMaMH);
con = DataProvider.Connect();
try
{
DataProvider.ExecuteQueriesNonQuery(sQuery, con);
DataProvider.CloseConnect(con);
return(true);
}
catch (Exception ex) { DataProvider.CloseConnect(con); return(false); }
}
public static bool SuaMonHoc(MonHocDTO monhocDTO)
{
string sQuery = string.Format(@"UPDATE MonHoc SET TenMH=N'{0}', SoTiet={1}, MaHK={2}, MaKhoi={3} WHERE MaMH={4}", monhocDTO.STenMH, monhocDTO.ISoTiet, monhocDTO.IMaHK, monhocDTO.IMaKhoi, monhocDTO.IMaMH);
con = DataProvider.Connect();
try
{
DataProvider.ExecuteQueriesNonQuery(sQuery, con);
DataProvider.CloseConnect(con);
return(true);
}
catch (Exception ex) { DataProvider.CloseConnect(con); return(false); }
}
// Thêm loại công văn
public static bool ThemLoaiCongVan(LoaiCongVanDTO lcvDTO)
{
string sQuery = string.Format("INSERT INTO LoaiCongVan VALUES (N'{0}')", lcvDTO.STenLCV);
con = DataProvider.Connect();
try
{
DataProvider.ExecuteQueriesNonQuery(sQuery, con);
DataProvider.CloseConnect(con);
return(true);
}
catch (Exception ex) { DataProvider.CloseConnect(con); return(false); }
}
public static bool SuaPhieuNhan(PhieuNhanDTO pnDTO)
{
string sQuery = string.Format("UPDATE PhieuNhan SET NgayNhan=N'{0}', NguoiNhan=N'{1}', MaDVN={2} WHERE MaPN={3}", pnDTO.DtNgayNhan, pnDTO.SNguoiNhan, pnDTO.IMaDVN, pnDTO.IMaPN);
con = DataProvider.Connect();
try
{
DataProvider.ExecuteQueriesNonQuery(sQuery, con);
DataProvider.CloseConnect(con);
return(true);
}
catch (Exception ex) { DataProvider.CloseConnect(con); return(false); }
}