public void NhapDuLieu()
{
OpenFileDialog openFile = new OpenFileDialog();
openFile.Filter = "Excel file (*.xls; *.xlsx; *.xlsm; *.xlsb)|*.xls";
openFile.ShowDialog();
if (openFile.FileName == String.Empty)
return;
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Worksheet sheetDocGia, sheetHocSinh, sheetNhanVien, sheetViPham;
Excel.Worksheet sheetTuaSach, sheetDauSach, sheetCuonSach;
Excel.Worksheet sheetMuonSach;
Excel.Worksheet sheetDkChoMuon;
Excel.Range rangeDocGia;
string str;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(openFile.FileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
//xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
sheetDocGia = xlWorkBook.Sheets["Độc giả"] as Excel.Worksheet;
sheetHocSinh = xlWorkBook.Sheets["Học sinh"] as Excel.Worksheet;
sheetNhanVien = xlWorkBook.Sheets["Nhân viên"] as Excel.Worksheet;
sheetViPham = xlWorkBook.Sheets["Vi phạm"] as Excel.Worksheet;
sheetTuaSach = xlWorkBook.Sheets["Tựa sách"] as Excel.Worksheet;
sheetDauSach = xlWorkBook.Sheets["Đầu sách"] as Excel.Worksheet;
sheetCuonSach = xlWorkBook.Sheets["Cuốn sách"] as Excel.Worksheet;
sheetMuonSach = xlWorkBook.Sheets["Mượn sách"] as Excel.Worksheet;
sheetDkChoMuon = xlWorkBook.Sheets["ĐK chờ mượn"] as Excel.Worksheet;
//sheetDocGia = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
/*sheetHocSinh = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);
sheetNhanVien = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(3);
sheetViPham = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(4);
sheetTuaSach = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(5);
sheetDauSach = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(6);
sheetCuonSach = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(7);
sheetMuonSach = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(8);
sheetDkChoMuon = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(9);*/
//range = xlWorkSheet.UsedRange;
//for (int rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
//{
// for (int cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
// {
// str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
// MessageBox.Show(str);
// }
//}
//rangeDocGia = sheetDocGia.UsedRange;
//if ((sheetDocGia.Cells[2, 6] as Excel.Range).Value == false)
// MessageBox.Show("false");
//MessageBox.Show(Convert.ToString((sheetDocGia.Cells[2,6] as Excel.Range).Value));
DangKyCho dataDkCho = new DangKyCho();
MuonTraSach dataMuonSach = new MuonTraSach();
Sach dataSach = new Sach();
DocGia dataDocGia = new DocGia();
ViPham dataViPham = new ViPham();
dataDkCho.xoaTatCa();
dataMuonSach.xoaTatCa();
dataSach.xoaTatCa();
dataViPham.xoaTatCa();
dataDocGia.xoaTatCa();
//Excel.Range rangeDkChoMuon = sheetDkChoMuon.UsedRange;
DOCGIA dg;
for (int i = 2; i <= sheetDocGia.UsedRange.Rows.Count; i++)
{
dg = new DOCGIA();
dg.madocgia = Convert.ToString((sheetDocGia.Cells[i, 1] as Excel.Range).Value);
dg.hoten = Convert.ToString((sheetDocGia.Cells[i, 2] as Excel.Range).Value);
dg.gioitinh = Convert.ToString((sheetDocGia.Cells[i, 3] as Excel.Range).Value);
String date = Convert.ToString((sheetDocGia.Cells[i, 4] as Excel.Range).Value);
if (date != null)
dg.ngaysinh = DateTime.Parse(date);
String date1 = Convert.ToString((sheetDocGia.Cells[i, 5] as Excel.Range).Value);
if (date1 != null)
dg.ngaylap = DateTime.Parse(date1);
dg.tinhtrang = (bool)(sheetDocGia.Cells[i, 6] as Excel.Range).Value;
dataDocGia.themDocGia(dg);
}
HOCSINH hs;
for (int i = 2; i <= sheetHocSinh.UsedRange.Rows.Count; i++)
{
hs = new HOCSINH();
hs.madocgia = Convert.ToString((sheetHocSinh.Cells[i, 1] as Excel.Range).Value);
hs.lop = Convert.ToString((sheetHocSinh.Cells[i, 2] as Excel.Range).Value);
dataDocGia.themHocSinh(hs);
}
NHANVIEN nv;
for (int i = 2; i <= sheetNhanVien.UsedRange.Rows.Count; i++)
{
nv = new NHANVIEN();
nv.madocgia = Convert.ToString((sheetNhanVien.Cells[i, 1] as Excel.Range).Value);
dataDocGia.themNhanVien(nv);
}
VIPHAM vp;
for (int i = 2; i <= sheetViPham.UsedRange.Rows.Count; i++)
{
vp = new VIPHAM();
vp.madocgia = Convert.ToString((sheetViPham.Cells[i, 1] as Excel.Range).Value);
vp.vipham1 = Convert.ToInt32((sheetViPham.Cells[i, 2] as Excel.Range).Value);
String date = Convert.ToString((sheetViPham.Cells[i, 3] as Excel.Range).Value);
if (date != null)
vp.ngayhethan = DateTime.Parse(date);
dataViPham.themViPham(vp);
}
TUASACH ts;
for (int i = 2; i <= sheetTuaSach.UsedRange.Rows.Count; i++)
{
ts = new TUASACH();
ts.matuasach = Convert.ToString((sheetTuaSach.Cells[i, 1] as Excel.Range).Value);
ts.tentuasach = Convert.ToString((sheetTuaSach.Cells[i, 2] as Excel.Range).Value);
ts.tacgia = Convert.ToString((sheetTuaSach.Cells[i, 3] as Excel.Range).Value);
ts.gioithieu = Convert.ToString((sheetTuaSach.Cells[i, 4] as Excel.Range).Value);
dataSach.themTuaSach(ts);
}
DAUSACH ds;
for (int i = 2; i <= sheetDauSach.UsedRange.Rows.Count; i++)
{
ds = new DAUSACH();
ds.madausach = Convert.ToString((sheetDauSach.Cells[i, 1] as Excel.Range).Value);
ds.matuasach = Convert.ToString((sheetDauSach.Cells[i, 2] as Excel.Range).Value);
ds.ngonngu = Convert.ToString((sheetDauSach.Cells[i, 3] as Excel.Range).Value);
ds.tinhtrang = (bool)(sheetDauSach.Cells[i, 4] as Excel.Range).Value;
dataSach.themDauSach(ds);
}
CUONSACH cs;
for (int i = 2; i <= sheetCuonSach.UsedRange.Rows.Count; i++)
{
cs = new CUONSACH();
cs.macuonsach = Convert.ToString((sheetCuonSach.Cells[i, 1] as Excel.Range).Value);
cs.madausach = Convert.ToString((sheetCuonSach.Cells[i, 2] as Excel.Range).Value);
cs.tinhtrang = (bool)(sheetCuonSach.Cells[i, 3] as Excel.Range).Value;
dataSach.themCuonSach(cs);
}
DKCHOMUON dk;
for (int i = 2; i <= sheetDkChoMuon.UsedRange.Rows.Count; i++)
{
dk = new DKCHOMUON();
dk.madocgia = Convert.ToString((sheetDkChoMuon.Cells[i, 1] as Excel.Range).Value);
dk.madausach = Convert.ToString((sheetDkChoMuon.Cells[i, 2] as Excel.Range).Value);
String date = Convert.ToString((sheetDkChoMuon.Cells[i, 3] as Excel.Range).Value);
if (date != null)
dk.ngaygiodk = DateTime.Parse(date);
dk.tinhtrang = (bool)(sheetDkChoMuon.Cells[i, 4] as Excel.Range).Value;
dataDkCho.themDangKyCho(dk);
}
MUONSACH ms;
for (int i = 2; i <= sheetMuonSach.UsedRange.Rows.Count; i++)
{
ms = new MUONSACH();
ms.madocgia = Convert.ToString((sheetMuonSach.Cells[i, 1] as Excel.Range).Value);
ms.macuonsach = Convert.ToString((sheetMuonSach.Cells[i, 2] as Excel.Range).Value);
String date = Convert.ToString((sheetMuonSach.Cells[i, 3] as Excel.Range).Value);
if (date != null)
ms.ngaygiomuon = DateTime.Parse(date);
String date1 = Convert.ToString((sheetMuonSach.Cells[i, 4] as Excel.Range).Value);
if (date1 != null)
ms.ngayhethan = DateTime.Parse(date1);
String date2 = Convert.ToString((sheetMuonSach.Cells[i, 5] as Excel.Range).Value);
if (date2 != null)
ms.ngaygiotra = DateTime.Parse(date2);
dataMuonSach.themMuonTraSach(ms);
}
xlWorkBook.Close(true, null, null);
xlApp.Quit();
MessageBox.Show("Hoàn thành!");
noticeImport();
//releaseObject(xlWorkSheet);
//releaseObject(xlWorkBook);
//releaseObject(xlApp);
}
//cập nhật bảng vi phạm
void updateViPham(string madocgia)
{
ViPham dataViPham = new ViPham();
ThamSo dataThamSo = new ThamSo();
VIPHAM inf = new VIPHAM();
THAMSO songaykhoathe;
THAMSO solantrehen = new THAMSO();
inf.madocgia = madocgia;
inf = dataViPham.timViPham(inf);
solantrehen = new THAMSO();
solantrehen.tenthamso = "solantrehen";
solantrehen = dataThamSo.timThamSo(solantrehen);
inf.vipham1++;
if (solantrehen.tinhtrang == true)
{
if (inf.vipham1 % Convert.ToInt32(solantrehen.giatri) == 0)
{
khoaThe(madocgia);
songaykhoathe = new THAMSO();
songaykhoathe.tenthamso = "songaykhoathe";
songaykhoathe = dataThamSo.timThamSo(songaykhoathe);
inf.ngayhethan = DateTime.Now.Date.AddDays(Convert.ToInt32(songaykhoathe.giatri));
}
}
dataViPham.suaViPham(inf);
//updateDocGia();
dgvdocgia.DataSource = dataDocGia.loadDocGia();
dgvhocsinh.DataSource = dataDocGia.loadHocSinh();
dgvnhanvien.DataSource = dataDocGia.loadNhanVien();
dgvvipham.DataSource = dataViPham.loadViPham();
}
private void btndelalldg_Click(object sender, EventArgs e)
{
try
{
DialogResult delete = MessageBox.Show("Bạn có thực muốn xóa hết dữ liệu này?", "Exit", MessageBoxButtons.YesNo);
if (delete == DialogResult.No)
return;
dataDocGia.xoaTatCa();
ViPham dataViPham = new ViPham();
dataViPham.xoaTatCa();
updateDgvDocGia();
}
catch (SqlException)
{
MessageBox.Show("Có ràng buộc dữ liệu, không thể xóa!");
}
}
//load lại DataGridView vi phạm
void updateDgvViPham()
{
ViPham dataViPham = new ViPham();
dgvvipham.DataSource = dataViPham.loadViPham();
}
//load lại DataGridView độc giả
void updateDgvDocGia()
{
ViPham dataViPham = new ViPham();
dgvdocgia.DataSource = dataDocGia.loadDocGia();
dgvhocsinh.DataSource = dataDocGia.loadHocSinh();
dgvnhanvien.DataSource = dataDocGia.loadNhanVien();
dgvvipham.DataSource = dataViPham.loadViPham();
//dgvdocgia.Columns[0].ValueType = typeof(Int32);
//dgvdocgia.Sort(dgvdocgia.Columns[0], System.ComponentModel.ListSortDirection.Ascending);
}
//kiểm tra phục hồi thẻ độc giả đã gia hạn
void ktPhucHoiThe()
{
ViPham dataViPham = new ViPham();
ThamSo dataThamSo = new ThamSo();
DataTable dt = dataDocGia.loadDocGia();
THAMSO handungthe = new THAMSO();
handungthe.tenthamso = "handungthe";
handungthe = dataThamSo.timThamSo(handungthe);
DateTime ngaydk;
DateTime ngayhethan;
DOCGIA dg;
DataTable vp;
for (int i = 0; i < dt.Rows.Count; i++)
{
vp = dataViPham.timViPham(dt.Rows[i]["Mã độc giả"].ToString());
ngaydk = new DateTime();
ngaydk = (DateTime)dt.Rows[i]["Ngày lập"];
ngayhethan = new DateTime();
ngayhethan = ngaydk.AddMonths(Convert.ToInt32(handungthe.giatri));
if (ngayhethan.Date >= DateTime.Now.Date && (vp.Rows[0]["ngayhethan"].ToString() == String.Empty || (DateTime)vp.Rows[0]["ngayhethan"] < DateTime.Now)) //mở thẻ
{
//dg = new DOCGIA();
string madocgia = dt.Rows[i]["Mã độc giả"].ToString();
//dg.madocgia = dt.Rows[i]["Mã độc giả"].ToString();
dg = dataDocGia.timDocGia(madocgia);
if (dg.tinhtrang != true)
{
dg.tinhtrang = true;
dataDocGia.suaDocGia(dg);
}
}
}
}
//kiểm tra phục hồi thẻ độc giả hết hạn phạt
void ktHetHanPhat()
{
ViPham dataViPham = new ViPham();
DataTable dt = dataViPham.loadViPham();
DateTime ngayhethan;
DOCGIA dg;
VIPHAM vp;
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["Ngày hết hạn phạt"].ToString() != String.Empty)
{
ngayhethan = new DateTime();
ngayhethan = (DateTime)dt.Rows[i]["Ngày hết hạn phạt"];
if (ngayhethan.Date < DateTime.Now.Date)
{
//dg = new DOCGIA();
string madocgia = dt.Rows[i]["Mã độc giả"].ToString();
//dg.madocgia = dt.Rows[i]["Mã độc giả"].ToString();
dg = dataDocGia.timDocGia(madocgia);
dg.tinhtrang = true;
dataDocGia.suaDocGia(dg);
vp = new VIPHAM();
vp.madocgia = dt.Rows[i]["Mã độc giả"].ToString();
vp = dataViPham.timViPham(vp);
vp.ngayhethan = null;
dataViPham.suaViPham(vp);
}
}
}
}
private void btnxoadg_Click(object sender, EventArgs e)
{
try
{
DialogResult delete = MessageBox.Show("Do you really want to delete?", "Exit", MessageBoxButtons.YesNo);
if (delete == DialogResult.No)
return;
if (tcdocgia.SelectedTabIndex == 0)//xóa theo tab độc giả
{
if (dgvdocgia.SelectedRows.Count == 0)
return;
string madocgia = dgvdocgia.SelectedRows[0].Cells[0].Value.ToString();
dataDocGia.xoaDocGia(madocgia);
dataDocGia.xoaNhanVien(madocgia);
dataDocGia.xoaHocSinh(madocgia);
ViPham dataViPham = new ViPham();
dataViPham.xoaViPham(madocgia);
updateDgvDocGia();
}
else if (tcdocgia.SelectedTabIndex == 1)//xóa theo tab học sinh
{
if (dgvhocsinh.SelectedRows.Count == 0)
return;
string madocgia = dgvhocsinh.SelectedRows[0].Cells[0].Value.ToString();
dataDocGia.xoaDocGia(madocgia);
dataDocGia.xoaHocSinh(madocgia);
ViPham dataViPham = new ViPham();
dataViPham.xoaViPham(madocgia);
updateDgvDocGia();
}
else if (tcdocgia.SelectedTabIndex == 2)//xóa theo tab nhân viên
{
if (dgvnhanvien.SelectedRows.Count == 0)
return;
string madocgia = dgvnhanvien.SelectedRows[0].Cells[0].Value.ToString();
dataDocGia.xoaDocGia(madocgia);
dataDocGia.xoaNhanVien(madocgia);
ViPham dataViPham = new ViPham();
dataViPham.xoaViPham(madocgia);
updateDgvDocGia();
}
}
catch (SqlException)
{
MessageBox.Show("Có liên kết dữ liệu, không thể xóa!");
}
/*catch (System.Data.Entity.Infrastructure.DbUpdateException)
{
MessageBox.Show("Có liên kết dữ liệu, không thể xóa!");
}*/
}
