private void refreshDataGridView()
{
myDatabase db = new myDatabase();
dgvMuontra.DataSource = db.getData("Select * from MuonTraSach");
dgvTraSach.DataSource = db.getData("Select * from MuonTraSach");
}
private void btnLuu_Click(object sender, EventArgs e)
{
if (btnThem.Enabled == true)
{
myDatabase db = new myDatabase();
string maDG = txtMaDG.Text;
string tenDG = txtTenDG.Text;
string diaChi = txtDiaChi.Text;
string ngayMuon = Convert.ToDateTime(dtNgayMuon.Text).ToShortDateString();
if (radNam.Checked)
{
string gioiTinh = "Nam";
string sql = string.Format("Insert into NguoiMuon values('{0}',N'{1}',N'{2}','{3}',N'{4}')", maDG, tenDG, gioiTinh, ngayMuon, diaChi);
db.getData(sql);
}
else
{
string gioiTinh = "Nữ";
string sql = string.Format("Insert into NguoiMuon values('{0}',N'{1}',N'{2}','{3}',N'{4}')", maDG, tenDG, gioiTinh, ngayMuon, diaChi);
db.getData(sql);
}
refreshDataGridView();
}
if (btnSua.Enabled == true)
{
sualuu();
}
}
public void loadcombobox()
{
myDatabase db = new myDatabase();
cbLoaiSach.DataSource = db.getData("Select * from LoaiSach");
cbLoaiSach.DisplayMember = "TenLoai";
cbLoaiSach.ValueMember = "MaLoaiSach";
}
private void cbLoaiSach_SelectedIndexChanged(object sender, EventArgs e)
{
myDatabase db = new myDatabase();
String maloai = cbLoaiSach.SelectedValue.ToString();
String sql = String.Format("Select * from Sach where MaLoaiSach='{0}'", maloai);
dgvSach.DataSource = db.getData(sql);
}
private void btnXoa_Click(object sender, EventArgs e)
{
myDatabase db = new myDatabase();
string Maloaisach = txtMaLoaiSach.Text;
string sql = string.Format("Delete LoaiSach Where MaLoaiSach='{0}'", Maloaisach);
db.getData(sql);
refreshDataGridView();
}
private void btnXoa_Click(object sender, EventArgs e)
{
myDatabase db = new myDatabase();
string MaDG = txtMaDG.Text;
string sql = string.Format("Delete NguoiMuon Where MaDG='{0}'", MaDG);
db.getData(sql);
refreshDataGridView();
}
private void txttimkiem_TextChanged(object sender, EventArgs e)
{
myDatabase db = new myDatabase();
string timkiem = txttimkiem.Text;
string sql = string.Format("Select * from NguoiMuon where TenDG like N'%{0}%' or MaDG like '%{0}%'", timkiem);
db.getData(sql);
refreshDataGridView1(sql);
}
private void btnXoaSach_Click(object sender, EventArgs e)
{
myDatabase db = new myDatabase();
String Masach = txtMaSach.Text;
String sql = String.Format("Delete Sach Where MaSach='{0}'", Masach);
db.getData(sql);
refreshDataGridView1();
}
private void sualuu()
{
myDatabase db = new myDatabase();
String maLS = txtMaLoaiSach.Text;
String tenLS = txtTenLoaiSach.Text;
String Kieusach = txtKieuSach.Text;
String sql = String.Format("Update LoaiSach Set TenLoai=N'{0}',KieuSach=N'{1}' Where MaLoaiSach='{2}'", tenLS, Kieusach, maLS);
db.getData(sql);
refreshDataGridView();
}
private void btnLuu_Click(object sender, EventArgs e)
{
myDatabase db = new myDatabase();
SqlConnection con = new SqlConnection(db.conSt);
String MaDG = cbMaDG_TraSach.SelectedValue.ToString();
String Masach = txtMaSach_TraSach.Text;
String ngaytra = Convert.ToDateTime(dtNgayTra.Text).ToShortDateString();
String sql = String.Format("UPDATE MuonTraSach SET NgayTra='{0}' WHERE MaDG='{1}'AND MaSach='{2}'", ngaytra, MaDG, Masach);
db.getData(sql);
refreshDataGridView();
}
private void Loadcombox()
{
myDatabase db = new myDatabase();
cbMaLoaiSach.DataSource = db.getData("Select * From LoaiSach");
cbMaLoaiSach.DisplayMember = "TenLoai";
cbMaLoaiSach.ValueMember = "MaLoaiSach";
cbMaTacGia.DataSource = db.getData("Select * from TacGia");
cbMaTacGia.DisplayMember = "TenTG";
cbMaTacGia.ValueMember = "MaTG";
}
private void sualuu()
{
myDatabase db = new myDatabase();
String maDG = txtMaDG.Text;
String tenDG = txtTenDG.Text;
String diaChi = txtDiaChi.Text;
String ngayMuon = Convert.ToDateTime(dtNgayMuon.Text).ToShortDateString();
String sql = String.Format("Update NguoiMuon Set TenDG=N'{0}',NgayMuon='{1}',DiaChi=N'{2}' Where MaDG='{3}'", tenDG, ngayMuon, diaChi, maDG);
db.getData(sql);
refreshDataGridView();
}
private void Suasach()
{
myDatabase db = new myDatabase();
String masach = txtMaSach.Text;
String tensach = txtTenSach.Text;
String maloai = cbMaLoaiSach.SelectedValue.ToString();
int soluong = Convert.ToInt32(txtSoLuong.Text);
String matg = cbMaTacGia.SelectedValue.ToString();
String sql = String.Format("Update Sach Set TenSach=N'{0}',MaLoaiSach=N'{1}',SoLuong={2},MaTG=N'{3}' where MaSach='{4}'", tensach, maloai, soluong, matg, masach);
db.getData(sql);
refreshDataGridView1();
}
private void btnChoMuon_Click(object sender, EventArgs e)
{
myDatabase db = new myDatabase();
SqlConnection con = new SqlConnection(db.conSt);
String madg = cbDocGia.SelectedValue.ToString();
String masach = txtMaSach.Text;
int soluong = Convert.ToInt32(txtSoLuong.Text);
String ngaymuon = Convert.ToDateTime(dtMuon.Text).ToShortDateString();
String ngayhentra = Convert.ToDateTime(dtTra.Text).ToShortDateString();
String sql = String.Format("Insert into MuonTraSach(MaDG,MaSach,SoLuong,NgayMuon,NgayHenTra) values('{0}','{1}',{2},'{3}','{4}')", madg, masach, soluong, ngaymuon, ngayhentra);
db.getData(sql);
refreshDataGridView();
}
private void sualuu()
{
try
{
myDatabase db = new myDatabase();
SqlConnection con = new SqlConnection(db.conSt);
String sql = "Update TacGia SET TenTG=N'" + txtTenTG.Text + "',DiaChi=N'" + txtDiaChi.Text + "'where MaTG='" + txtMaTG.Text + "'";
db.getData(sql);
}
catch
{
MessageBox.Show("Lỗi không sửa được", "Error");
}
refreshDataGridView();
}
private void Loadcombox()
{
myDatabase db = new myDatabase();
cbMaSach.DataSource = db.getData("Select * From Sach");
cbMaSach.DisplayMember = "TenSach";
cbMaSach.ValueMember = "MaSach";
cbDocGia.DataSource = db.getData("Select * from NguoiMuon");
cbDocGia.DisplayMember = "TenDG";
cbDocGia.ValueMember = "MaDG";
cbMaDG_TraSach.DataSource = db.getData("Select * from MuonTraSach");
cbMaDG_TraSach.DisplayMember = "MaDG";
cbMaDG_TraSach.ValueMember = "MaDG";
}
private void btnLuu_Click(object sender, EventArgs e)
{
if (btnThem.Enabled == true)
{
myDatabase db = new myDatabase();
String maloaisach = txtMaLoaiSach.Text;
String tenloaisach = txtTenLoaiSach.Text;
String kieusach = txtKieuSach.Text;
String sql = String.Format("Insert into LoaiSach values('{0}',N'{1}',N'{2}')", maloaisach, tenloaisach, kieusach);
db.getData(sql);
refreshDataGridView();
}
if (btnSua.Enabled == true)
{
sualuu();
}
}
private void cbMaSach_SelectedIndexChanged(object sender, EventArgs e)
{
myDatabase db = new myDatabase();
SqlConnection myConnection = new SqlConnection(db.conSt);
string masach = cbMaSach.SelectedValue.ToString();
string sql = string.Format("Select MaSach, MaLoaiSach, SoLuong, MaTG From Sach where MaSach=N'{0}'", masach);
SqlDataAdapter da = new SqlDataAdapter(sql, myConnection);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataRow drw in dt.Rows)
{
txtMaSach1.Text = drw["MaSach"].ToString();
labMaLoai.Text = drw["MaLoaiSach"].ToString();
labSoLuong.Text = drw["SoLuong"].ToString();
lbMaTG.Text = drw["MaTG"].ToString();
}
}
private void btnXoa_Click(object sender, EventArgs e)
{
try
{
myDatabase db = new myDatabase();
SqlConnection con = new SqlConnection(db.conSt);
String sql = "Delete TacGia where MaTG='" + txtMaTG.Text + "'";
db.getData(sql);
txtMaTG.Text = "";
txtTenTG.Text = "";
txtDiaChi.Text = "";
txtMaTG.Focus();
refreshDataGridView();
}
catch
{
MessageBox.Show("Lỗi, Không xóa được");
}
}
private void btnLuuSach_Click(object sender, EventArgs e)
{
if (btnThemSach.Enabled == true)
{
myDatabase db = new myDatabase();
String masach = txtMaSach.Text;
String tensach = txtTenSach.Text;
String maloai = cbMaLoaiSach.SelectedValue.ToString();
int soluong = Convert.ToInt32(txtSoLuong.Text);
String maTg = cbMaTacGia.SelectedValue.ToString();
String sql = String.Format("Insert into Sach values('{0}',N'{1}','{2}',{3},'{4}')", masach, tensach, maloai, soluong, maTg);
db.getData(sql);
refreshDataGridView1();
}
if (btnSuaSach.Enabled == true)
{
Suasach();
grbChiTietSach.Enabled = true;
}
}
private void sualuu()
{
myDatabase db = new myDatabase();
string maDG = txtMaDG.Text;
string tenDG = txtTenDG.Text;
string diaChi = txtDiaChi.Text;
string ngayMuon = Convert.ToDateTime(dtNgayMuon.Text).ToShortDateString();
if (radNam.Checked)
{
string gt = "Nam";
string sql = string.Format("Update NguoiMuon Set TenDG=N'{0}',NgayMuon='{1}',DiaChi=N'{2}',GioiTinh=N'{3}' Where MaDG='{4}'", tenDG, ngayMuon, diaChi, gt, maDG);
db.getData(sql);
}
else
{
string gt = "Nữ";
string sql = string.Format("Update NguoiMuon Set TenDG=N'{0}',NgayMuon='{1}',DiaChi=N'{2}',GioiTinh=N'{3}' Where MaDG='{4}'", tenDG, ngayMuon, diaChi, gt, maDG);
db.getData(sql);
}
refreshDataGridView();
}
private void btnLuu_Click(object sender, EventArgs e)
{
if (btnThem.Enabled == true)
{
int count = 0;
count = dgvTacgia.Rows.Count;
string chuoi = "";
int chuoi2 = 0;
chuoi = Convert.ToString(dgvTacgia.Rows[count - 2].Cells[0].Value);
chuoi2 = Convert.ToInt32((chuoi.Remove(0, 2)));//Loại bỏ ký tự mã tác giả DG
if (chuoi2 + 1 < 10)
{
txtMaTG.Text = "TG00" + (chuoi2 + 1).ToString();
}
else
if (chuoi2 + 1 < 100)
{
txtMaTG.Text = "TG0" + (chuoi2 + 1).ToString();
}
try
{
myDatabase db = new myDatabase();
SqlConnection con = new SqlConnection(db.conSt);
String sql = "INSERT INTO TacGia values('" + txtMaTG.Text + "',N'" + txtTenTG.Text + "',N'" + txtDiaChi.Text + "')";
dgvTacgia.DataSource = db.getData(sql);
}
catch
{
MessageBox.Show("Lỗi không thêm được", "Error");
}
refreshDataGridView();
}
if (btnSua.Enabled == true)
{
sualuu();
}
}
private void btnChoMuon_Click(object sender, EventArgs e)
{
myDatabase db = new myDatabase();
SqlConnection con = new SqlConnection(db.conSt);
string madg = cbDocGia.SelectedValue.ToString();
string masach = txtMaSach.Text;
int soluong = Convert.ToInt32(txtSoLuong.Text);
int soluong1 = Convert.ToInt32(labSoLuong.Text);
string ngaymuon = Convert.ToDateTime(dtMuon.Text).ToShortDateString();
string ngayhentra = Convert.ToDateTime(dtTra.Text).ToShortDateString();
if (soluong1 >= soluong)
{
string sql = string.Format("Insert into MuonTraSach(MaDG,MaSach,SoLuong,NgayMuon,NgayHenTra) values('{0}','{1}',{2},'{3}','{4}')", madg, masach, soluong, ngaymuon, ngayhentra);
db.getData(sql);
string sql1 = string.Format("UPDATE Sach SET SoLuong= SoLuong- {0} WHERE MaSach='{1}'", soluong, masach);
db.getData(sql1);
refreshDataGridView();
}
else
{
MessageBox.Show("lỗi, Sách trong kho không đủ");
}
}
private void btnTimKiem_Click(object sender, EventArgs e)
{
myDatabase db = new myDatabase();
if (btnTimKiem.Text == "Tìm Kiếm")
{
if (txtTimSach.Text == "")
{
MessageBox.Show("Vui lòng nhập thông tin cần tìm", "Thông Báo", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txtTimSach.Focus();
}
else
{
if (radMasach.Checked == false && radTensach.Checked == false)
{
MessageBox.Show("Vui lòng lựa chọn tìm kiếm theo mã sách hoặc tên sách", "Thông Báo", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
else
{
btnTimKiem.Text = "Thử lại";
if (radMasach.Checked == true)
{
SqlConnection myConnection = new SqlConnection(db.conSt);
string timkiem = txtTimSach.Text;
string sql = string.Format("Select * from Sach where MaSach='{0}'", timkiem);
SqlDataAdapter da = new SqlDataAdapter(sql, myConnection);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataRow drw in dt.Rows)
{
txtMaSach.Text = drw["MaSach"].ToString();
txtTenSach.Text = drw["TenSach"].ToString();
txtSoLuong.Text = drw["SoLuong"].ToString();
txtMaTacGia.Text = drw["MaTG"].ToString();
txtMaLoai.Text = drw["MaLoaiSach"].ToString();
}
}
else if (radTensach.Checked == true)
{
SqlConnection myConnection = new SqlConnection(db.conSt);
string timkiem = txtTimSach.Text;
string sql = string.Format("Select * from Sach where TenSach=N'{0}'", timkiem);
SqlDataAdapter da = new SqlDataAdapter(sql, myConnection);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataRow drw in dt.Rows)
{
txtMaSach.Text = drw["MaSach"].ToString();
txtTenSach.Text = drw["TenSach"].ToString();
txtSoLuong.Text = drw["SoLuong"].ToString();
txtMaTacGia.Text = drw["MaTG"].ToString();
txtMaLoai.Text = drw["MaLoaiSach"].ToString();
}
}
}
}
}
else
{
btnTimKiem.Text = "Tìm Kiếm";
txtTimSach.Enabled = true;
txtTimSach.Text = "";
txtTimSach.Focus();
txtMaSach.Text = "";
txtTenSach.Text = "";
txtSoLuong.Text = "";
txtMaTacGia.Text = "";
txtMaLoai.Text = "";
}
}
private void refreshDataGridView()
{
myDatabase db = new myDatabase();
dgvTacgia.DataSource = db.getData("Select * from TacGia");
}
private void refreshDataGridView()
{
myDatabase db = new myDatabase();
dgvLoaiSach.DataSource = db.getData("Select * from LoaiSach");
}
private void refreshDataGridView1(string sql)
{
myDatabase db = new myDatabase();
dgvMuontra.DataSource = db.getData(sql);
}
private void refreshDataGridView()
{
myDatabase db = new myDatabase();
dgvDocGia.DataSource = db.getData("Select * from NguoiMuon");
}
private void btnIn_Click(object sender, EventArgs e)
{
myDatabase db = new myDatabase();
SqlConnection con = new SqlConnection(db.conSt);
string madg = cbDocGia.Text;
string sql = string.Format("Select * from MuonTraSach where MaDG='{0}'", madg);
db.getData(sql);
refreshDataGridView1(sql);
DataTable dt = (DataTable)dgvMuontra.DataSource;
Excel.Application exApp;
Excel.Workbook exBook;
Excel.Worksheet exSheet;
Excel.Range exRange;
exApp = new Excel.Application();
exBook = (Excel.Workbook)(exApp.Workbooks.Add(true));
exSheet = (Excel.Worksheet)exBook.ActiveSheet;
int iRow = 7;
for (int i = 0; i < dt.Rows.Count; i++)
{
exSheet.Cells[iRow, 2] = dt.Rows[i]["MaDG"].ToString();
exSheet.Cells[iRow, 3] = dt.Rows[i]["MaSach"].ToString();
exSheet.Cells[iRow, 4] = dt.Rows[i]["SoLuong"].ToString();
exSheet.Cells[iRow, 5] = dt.Rows[i]["NgayMuon"].ToString();
exSheet.Cells[iRow, 6] = dt.Rows[i]["NgayHenTra"].ToString();
iRow++;
}
exRange = exSheet.get_Range("B6", "F" + (iRow - 1).ToString());
exRange.Borders.Color = Color.Black.ToArgb();
exRange.Font.Size = "18";
Excel.Range cl1 = exSheet.get_Range("B6", "B6");
cl1.Value = "Mã độc giả";
cl1.ColumnWidth = "15";
Excel.Range cl2 = exSheet.get_Range("C6", "C6");
cl2.Value = "Mã sách";
cl2.ColumnWidth = "20";
Excel.Range cl3 = exSheet.get_Range("D6", "D6");
cl3.Value = "Số lượng";
cl3.ColumnWidth = "15";
Excel.Range cl4 = exSheet.get_Range("E6", "E6");
cl4.Value = "Ngày mượn";
cl4.ColumnWidth = "30";
Excel.Range cl5 = exSheet.get_Range("F6", "F6");
cl5.Value = "Ngày hẹn trả";
cl5.ColumnWidth = "30";
Excel.Range cl6 = exSheet.get_Range("B3", "F4");
cl6.Merge(false);
cl6.HorizontalAlignment = 3;
cl6.VerticalAlignment = 3;
cl6.FormulaR1C1 = "PHIẾU MƯỢN SÁCH";
cl6.Font.Size = "20";
exApp.UserControl = false;
exApp.Visible = true;
}