private void btnthem_Click(object sender, EventArgs e)
{
try
{
if (cbxmdg.SelectedItem == null || cbxmacuonsach.SelectedItem == null)
throw new ArgumentNullException();
inf = new MUONSACH();
inf.madocgia = cbxmdg.SelectedItem.ToString();
inf.macuonsach = cbxmacuonsach.SelectedItem.ToString();
inf.ngaygiomuon = dtngaygiomuon.Value;
int result = data.themMuonTraSach(inf);
if (result == 0)
{
MessageBox.Show("Mượn sách vượt quá số lượng cho phép!");
}
else if (result == -1)
{
MessageBox.Show("Dữ liệu không hợp lệ!");
}
else
{
MessageBox.Show("Đã thêm!");
Close();
noticeBorrowBook(inf.macuonsach);
}
}
catch (ArgumentNullException)
{
MessageBox.Show("Dữ liệu không hợp lệ!");
}
}
private void btnluu_Click(object sender, EventArgs e)
{
try
{
THAMSO songaymuon = new THAMSO();
songaymuon.tenthamso = "songaymuon";
songaymuon = dataThamSo.timThamSo(songaymuon);
MUONSACH after = new MUONSACH();
if (cbxmdg.SelectedItem != null)
after.madocgia = cbxmdg.SelectedItem.ToString();
if (cbxmacuonsach.SelectedItem != null)
after.macuonsach = cbxmacuonsach.SelectedItem.ToString();
after.ngaygiomuon = dtngaygiomuon.Value;
after.ngayhethan = after.ngaygiomuon.Date.AddDays(Convert.ToInt32(songaymuon.giatri));
if (dtngaygiotra.Enabled == true)
after.ngaygiotra = dtngaygiotra.Value.Date;
data.suaMuonTraSach(temp, after);
MessageBox.Show("Đã lưu!");
noticeUpdateBackBook();
Close();
}
catch(SqlException)
{
MessageBox.Show("Dữ liệu không hợp lệ!");
}
}
public void suaMuonTraSach(MUONSACH before, MUONSACH after)
{
/*var temp = data.MUONSACHes.Find(before.madocgia, before.macuonsach, before.ngaygiomuon);
if (temp != null)
{
data.Entry(temp).CurrentValues.SetValues(after);
data.SaveChanges();
}*/
SqlCommand sql = new SqlCommand();
sql.CommandText = "update MUONSACH set madocgia = @mdg, macuonsach = @sach, ngaygiomuon = @muon, ngayhethan = @han, ngaygiotra = @tra where madocgia = @mdg1 and macuonsach = @sach1 and ngaygiomuon = @muon1";
sql.CommandType = CommandType.Text;
sql.Parameters.AddWithValue("@mdg", after.madocgia);
sql.Parameters.AddWithValue("@sach", after.macuonsach);
sql.Parameters.AddWithValue("@muon", after.ngaygiomuon);
sql.Parameters.AddWithValue("@han", after.ngayhethan);
if (after.ngaygiotra != null)
sql.Parameters.AddWithValue("@tra", after.ngaygiotra);
else
{
sql.Parameters.AddWithValue("@tra", DBNull.Value);
}
sql.Parameters.AddWithValue("@mdg1", before.madocgia);
sql.Parameters.AddWithValue("@sach1", before.macuonsach);
sql.Parameters.AddWithValue("@muon1", before.ngaygiomuon);
sql.Connection = DataProvider.con;
sql.ExecuteNonQuery();
}
public int themMuonTraSach(MUONSACH inf)
{
DataTable phieuMuon = timMuonTraSach(inf, 1);
int chuaTra = 0;
for (int i = 0; i < phieuMuon.Rows.Count; i++)
{
if (phieuMuon.Rows[i]["ngaygiotra"].ToString() == String.Empty)
chuaTra++;
}
THAMSO sosachmuon = new THAMSO();
sosachmuon.tenthamso = "sosachmuon";
sosachmuon = dataThamSo.timThamSo(sosachmuon);
THAMSO songaymuon = new THAMSO();
songaymuon.tenthamso = "songaymuon";
songaymuon = dataThamSo.timThamSo(songaymuon);
/*for (int i = 0; i < Form1.thamSo.Count; i++)
{
if (Form1.thamSo[i].tenthamso == "sosachmuon")
{
sosachmuon = Form1.thamSo[i];
break;
}
}
for (int i = 0; i < Form1.thamSo.Count; i++)
{
if (Form1.thamSo[i].tenthamso == "songaymuon")
{
songaymuon = Form1.thamSo[i];
break;
}
}*/
if (sosachmuon.tinhtrang == true)
{
if (chuaTra >= Convert.ToInt32(sosachmuon.giatri))
{
return 0;
}
}
DateTime hethan = new DateTime();
hethan = inf.ngaygiomuon.AddDays(Convert.ToInt32(songaymuon.giatri));
hethan = hethan.Date.AddHours(0).AddMinutes(0).AddSeconds(0);
/*inf.ngayhethan = inf.ngaygiomuon.AddDays(Convert.ToInt32(songaymuon.giatri));
inf.ngayhethan.*/
inf.ngayhethan = hethan;
try
{
data.MUONSACHes.Add(inf);
data.SaveChanges();
}
catch (DbUpdateException)
{
return -1;
}
return 1;
}
private void TraSach_Load(object sender, EventArgs e)
{
temp = data.timMuonTraSach(inf);
txtmdg.Text = temp.madocgia;
txtmacuonsach.Text = temp.macuonsach;
dtngaygiomuon.Value = temp.ngaygiomuon;
}
private void btntim_Click(object sender, EventArgs e)
{
if (rbmdg.Checked == true)
{
MUONSACH ms = new MUONSACH();
ms.madocgia = txtmdg.Text;
dgvmuonsach.DataSource = dataMuon.timMuonTraSach(ms, 1);
}
else if (rbmacuonsach.Checked == true)
{
MUONSACH ms = new MUONSACH();
ms.macuonsach = txtmcs.Text;
dgvmuonsach.DataSource = dataMuon.timMuonTraSach(ms, 2);
}
else if (rbngaymuon.Checked == true)
{
MUONSACH ms = new MUONSACH();
ms.ngaygiomuon = dtngaygiomuon.Value.Date;
dgvmuonsach.DataSource = dataMuon.timMuonTraSach(ms, 3);
}
}
private void SuaMuonTraSach_Load(object sender, EventArgs e)
{
temp = data.timMuonTraSach(inf);
Sach dataSach = new Sach();
DocGia dataDocGia = new DocGia();
DataTable dtDocGia = dataDocGia.loadDocGia();
DataTable dtCuonSach = dataSach.loadCuonSach();
for (int i = 0; i < dtDocGia.Rows.Count; i++)
{
//if (dtDocGia.Rows[i]["Tình trạng"].ToString() != String.Empty && (bool)dtDocGia.Rows[i]["Tình trạng"] == true)//== String.Empty
string mdg = dtDocGia.Rows[i]["Mã độc giả"].ToString();
cbxmdg.Items.Add(mdg);
if (temp.madocgia == mdg)
cbxmdg.SelectedIndex = i;
}
for (int i = 0; i < dtCuonSach.Rows.Count; i++)
{
string macuonsach = dtCuonSach.Rows[i]["Mã cuốn sách"].ToString();
cbxmacuonsach.Items.Add(macuonsach);
if (temp.macuonsach == macuonsach)
cbxmacuonsach.SelectedIndex = i;
}
dtngaygiomuon.Value = temp.ngaygiomuon;
if (temp.ngaygiotra != null)
{
dtngaygiotra.Value = (DateTime)temp.ngaygiotra;
}
else
{
dtngaygiotra.Enabled = false;
}
}
public void xoaMuonTraSach(MUONSACH inf)
{
data.xoaMuonTraSach(inf);
}
public DataTable timMuonTraSach(MUONSACH inf, int type)
{
return data.timMuonTraSach(inf, type);
}
public MUONSACH timMuonTraSach(MUONSACH inf)
{
return data.timMuonTraSach(inf);
}
public int themMuonTraSach(MUONSACH inf)
{
return data.themMuonTraSach(inf);
}
public void suaMuonTraSach(MUONSACH before, MUONSACH after)
{
data.suaMuonTraSach(before, after);
}
public void xoaMuonTraSach(MUONSACH inf)
{
data.MUONSACHes.Remove(inf);
data.SaveChanges();
}
public DataTable timMuonTraSach(MUONSACH inf, int type)
{
DataTable dt = new DataTable();
if (type == 1)//tìm theo mã độc giả
{
SqlDataAdapter da = new SqlDataAdapter("select* from MUONSACH where madocgia = @mdg", DataProvider.con);
da.SelectCommand.Parameters.AddWithValue("@mdg", inf.madocgia);
da.Fill(dt);
}
else if (type == 2)//tìm theo mã cuốn sách
{
SqlDataAdapter da = new SqlDataAdapter("select* from MUONSACH where macuonsach = @sach", DataProvider.con);
da.SelectCommand.Parameters.AddWithValue("@sach", inf.macuonsach);
da.Fill(dt);
}
else if (type == 3)//tìm theo ngày mượn
{
SqlDataAdapter da = new SqlDataAdapter("select* from MUONSACH where cast(ngaygiomuon AS DATE) = @ngay", DataProvider.con);
da.SelectCommand.Parameters.AddWithValue("@ngay", inf.ngaygiomuon.Date);
da.Fill(dt);
}
return dt;
}
public MUONSACH timMuonTraSach(MUONSACH inf)
{
return data.MUONSACHes.Find(inf.madocgia, inf.macuonsach, inf.ngaygiomuon);
}
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);
}