private void loadDataToGridview()
{
string sql = "Select * From LinhVuc";
LinhVuc = DAO.GetDataToTable(sql);
dataGridView_LinhVuc.DataSource = LinhVuc;
}
private void LoadDataToGrivew()
{
string sql = "SELECT * FROM NhaXuatBan";
NXB = DAO.GetDataToTable(sql);
dataGridView1.DataSource = NXB;
}
private void btnHienThi_Click(object sender, EventArgs e)
{
//Xuất báo cáo
DAO.OpenConnection();
string sql = "SELECT * FROM KhoSach WHERE NOT EXISTS (SELECT * FROM ChiTietHDB INNER JOIN HoaDonBan ON ChiTietHDB.SoHDB = HoaDonBan.SoHDB WHERE 1=1";
if (cboQuy.Text == "")
{
MessageBox.Show("Bạn phải chọn quý!");
}
if (cboQuy.Text == "1")
{
sql = sql + "AND KhoSach.MaSach = ChiTietHDB.MaSach AND NgayBan >= '2020-01-01' AND NgayBan <= '2020-03-31')";
}
if (cboQuy.Text == "2")
{
sql = sql + "AND KhoSach.MaSach = ChiTietHDB.MaSach AND NgayBan >= '2020-04-01' AND NgayBan <= '2020-06-30')";
}
if (cboQuy.Text == "3")
{
sql = sql + "AND KhoSach.MaSach = ChiTietHDB.MaSach AND NgayBan >= '2020-07-01' AND NgayBan <= '2020-09-30')";
}
if (cboQuy.Text == "4")
{
sql = sql + "AND KhoSach.MaSach = ChiTietHDB.MaSach AND NgayBan >= '2020-10-01' AND NgayBan <= '2020-12-31')";
}
BC6 = DAO.GetDataToTable(sql);
dataGridViewBC6.DataSource = BC6;
LoadDataGridView();
DAO.CloseConnetion();
}
//Tìm kiếm
private void button6_Click(object sender, EventArgs e)
{
string sql;
if (txtTNN.Text == "")
{
MessageBox.Show("Bạn cần nhập điều kiện tìm kiếm", "Yêu cầu",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
sql = "SELECT * FROM NgonNgu WHERE 1=1";
if (txtTNN.Text != "")
{
sql = sql + "AND TenNgonNgu LIKE N'%" + txtTNN.Text + "%'";
}
NgonNgu = DAO.GetDataToTable(sql);
if (NgonNgu.Rows.Count == 0)
{
MessageBox.Show("Không có bản ghi nào thỏa mãn", "Thông báo",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
MessageBox.Show("Có " + NgonNgu.Rows.Count + " bản ghi thỏa mãn", "Thông báo",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
dataGridView1.DataSource = NgonNgu;
}
private void btnHienThi_Click(object sender, EventArgs e)
{
DAO.OpenConnection();
string sql = "SELECT a.MaSach,TenSach,SoLuong,DonGiaNhap,DonGiaBan,MaLoaiSach,MaTG,MaNXB,MaLinhVuc,MaNgonNgu,Anh,SoTrang" +
"FROM KhoSach a join ChiTietHDB b on a.MaSach =b.MaSach" +
"FROM ChiTietHDB b join HoaDonBan c on b.SoHDB =c.SoHDB" +
"WHERE NOT EXISTS (select c.SoHDB,NgayBan AS c WHERE NgayBan LIKE '%" + cboQuy.Text + "%')";
if (cboQuy.Text == "")
{
MessageBox.Show("Bạn phải chọn quý!");
}
if (cboQuy.Text == "1")
{
sql = sql + "AND NgayBan >= '2020-01-01' AND NgayBan <= '2020-03-31'";
}
if (cboQuy.Text == "2")
{
sql = sql + "AND NgayBan >= '2020-04-01' AND NgayBan <= '2020-06-30'";
}
if (cboQuy.Text == "3")
{
sql = sql + "AND NgayBan >= '2020-07-01' AND NgayBan <= '2020-09-30'";
}
if (cboQuy.Text == "4")
{
sql = sql + "AND NgayBan >= '2020-10-01' AND NgayBan <= '2020-12-31'";
}
tblBC6 = DAO.GetDataToTable(sql);
DAO.RunSql(sql);
DAO.CloseConnetion();
}
public void LoadDataToGridview()
{
string sql = "SELECT * FROM Khosach";
KhoSach = DAO.GetDataToTable(sql);
dataGridView1.DataSource = KhoSach;
}
private void btnHienThi_Click(object sender, EventArgs e)
{
//Xuất báo cáo
DAO.OpenConnection();
string sql = "SELECT SoHDB, TongTien FROM HoaDonBan WHERE 1=1";
if (cboQuy.Text == "")
{
MessageBox.Show("Bạn phải chọn quý!");
}
if (cboQuy.Text == "1")
{
sql = sql + "AND NgayBan >= '2020-01-01' AND NgayBan <= '2020-03-31'";
}
if (cboQuy.Text == "2")
{
sql = sql + "AND NgayBan >= '2020-04-01' AND NgayBan <= '2020-06-30'";
}
if (cboQuy.Text == "3")
{
sql = sql + "AND NgayBan >= '2020-07-01' AND NgayBan <= '2020-09-30'";
}
if (cboQuy.Text == "4")
{
sql = sql + "AND NgayBan >= '2020-10-01' AND NgayBan <= '2020-12-31'";
}
BC8 = DAO.GetDataToTable(sql);
dataGridViewC8.DataSource = BC8;
dataGridViewC8.Columns[0].HeaderText = "Số hóa đơn bán";
dataGridViewC8.Columns[0].Width = 160;
dataGridViewC8.Columns[1].HeaderText = "Tổng tiền";
dataGridViewC8.Columns[1].Width = 150;
LoadDataGridView();
DAO.CloseConnetion();
}
private void btnhienthi_Click(object sender, EventArgs e)
{
DAO.OpenConnection();
//xuất báo cáo
if (cmbthang.Text == "")
{
MessageBox.Show("Hãy nhập đủ điều kiện!!!", "Yêu cầu ...", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
string sql = "SELECT * FROM KhachHang WHERE NOT EXISTS (SELECT * FROM HoaDonBan WHERE KhachHang.MaKhach = HoaDonBan.MaKhach AND MONTH (NgayBan) = " + Convert.ToInt32(cmbthang.Text) + ")";
BC9 = DAO.GetDataToTable(sql);
DAO.RunSql(sql);
dataGridView1.DataSource = BC9;
dataGridView1.Columns[0].HeaderText = "Mã Khách";
dataGridView1.Columns[0].Width = 60;
dataGridView1.Columns[1].HeaderText = "Tên Khách";
dataGridView1.Columns[1].Width = 170;
dataGridView1.Columns[2].HeaderText = "Địa Chỉ";
dataGridView1.Columns[2].Width = 120;
dataGridView1.Columns[3].HeaderText = "Điện Thoại";
dataGridView1.Columns[3].Width = 110;
LoadDataGridView();
DAO.CloseConnetion();
}
public void LoadDataToGridview()
{
string sql = "SELECT * FROM NgonNgu";
NgonNgu = DAO.GetDataToTable(sql);
dataGridView1.DataSource = NgonNgu;
}
private void btntimkiem_Click(object sender, EventArgs e)
{
string sql;
if ((txtMaHang.Text == "") && (txtNgayNhap.Text == ""))
{
MessageBox.Show("Hãy nhập một điều kiện tìm kiếm!!!", "Yêu cầu...", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
sql = "select a.SoHDN,a.NgayNhap,a.MaNV,a.MaNhaCC,a.TongTien from HoaDonNhap a join ChiTietHDN b on a.SoHDN=b.SoHDN where 1=1";
if (txtMaHang.Text != "")
{
sql = sql + " AND b.MaSach Like '%" + txtMaHang.Text + "%'";
}
if (txtNgayNhap.Text != "")
{
sql = sql + " AND a.NgayNhap Like '%" + txtNgayNhap.Text + "%'";
}
TimHDN = DAO.GetDataToTable(sql);
if (TimHDN.Rows.Count == 0)
{
MessageBox.Show("Không có bản ghi nào thỏa mãn điều kiện!!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Có " + TimHDN.Rows.Count + " bản ghi thỏa mãn điều kiện!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
dataGridView1.DataSource = TimHDN;
LoadDataGridView();
}
private void btnTongQuy_Click(object sender, EventArgs e)
{
//Tính tổng tiền bán được trong 1 quý
DAO.OpenConnection();
string sql = "SELECT SUM (TongTien) FROM HoaDonBan WHERE 1=1";
if (cboQuy.Text == "")
{
MessageBox.Show("Bạn phải chọn quý!");
}
if (cboQuy.Text == "1")
{
sql = sql + "AND NgayBan >= '2020-01-01' AND NgayBan <= '2020-03-31'";
}
if (cboQuy.Text == "2")
{
sql = sql + "AND NgayBan >= '2020-04-01' AND NgayBan <= '2020-06-30'";
}
if (cboQuy.Text == "3")
{
sql = sql + "AND NgayBan >= '2020-07-01' AND NgayBan <= '2020-09-30'";
}
if (cboQuy.Text == "4")
{
sql = sql + "AND NgayBan >= '2020-10-01' AND NgayBan <= '2020-12-31'";
}
BC8 = DAO.GetDataToTable(sql);
dataGridView2.DataSource = BC8;
dataGridView2.Columns[0].HeaderText = "Tổng quý";
dataGridView2.Columns[0].Width = 120;
LoadDataGridView();
DAO.CloseConnetion();
}
public void LoadDataToGridview()
{
string sql = "SELECT * FROM KhachHang";
KhachHang = DAO.GetDataToTable(sql);
dataGridView1.DataSource = KhachHang;
}
public void LoadDataToGridview()
{
string sql = "SELECT * FROM CongViec";
CongViec = DAO.GetDataToTable(sql);
dataGridView1.DataSource = CongViec;
}
public void LoadDataToGridview()
{
string sql = "SELECT * FROM NhanVien";
NhanVien = DAO.GetDataToTable(sql);
dataGridView1.DataSource = NhanVien;
}
private void loadDataToGridview()
{
string sql = "Select * From NhaCungCap";
NhaCungCap = DAO.GetDataToTable(sql);
dataGridView1.DataSource = NhaCungCap;
}
private void btnTimKiem_Click(object sender, EventArgs e)
{
string sql;
if (txtTTG.Text == "")
{
MessageBox.Show("Bạn cần nhập điều kiện tìm kiếm", "Yêu cầu",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
sql = "select * from TacGia where 1=1";
if (txtTTG.Text != "")
{
sql = sql + "and TenTacGia like N'%" + txtTTG.Text + "%'";
}
TacGia = DAO.GetDataToTable(sql);
if (TacGia.Rows.Count == 0)
{
MessageBox.Show("Không có bản ghi nào thỏa mãn", "Thông báo",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
MessageBox.Show("Có " + TacGia.Rows.Count + " bản ghi thỏa mãn", "Thông báo",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
dataGridView1.DataSource = TacGia;
}
public void LoadDataToGridview()
{
DAO.OpenConnection();
string sql = "SELECT * FROM CongViec";
CongViec = DAO.GetDataToTable(sql);
dataGridView1.DataSource = CongViec;
DAO.CloseConnetion();
}
public void LoadDataToGridview()
{
string sql = "SELECT * FROM TacGia";
TacGia = DAO.GetDataToTable(sql);
dataGridView1.DataSource = TacGia;
DAO.FillDataToCombo("SELECT GioiTinh FROM TacGia", cboGT, "GioiTinh", "GioiTinh");
cboGT.SelectedIndex = -1;
}
public void LoadDataToGridview()
{
DAO.OpenConnection();
string sql = "SELECT MaLanMat, MaSach, NgayMat, SoLuongMat FROM MatSach";
MatSach = DAO.GetDataToTable(sql);
dataGridView1.DataSource = MatSach;
DAO.CloseConnetion();
}
public void LoadDataToGridview()
{
DAO.OpenConnection();
string sql = "SELECT * FROM Khosach";
KhoSach = DAO.GetDataToTable(sql);
dataGridView1.DataSource = KhoSach;
DAO.CloseConnetion();
}
public void LoadDataToGridview()
{
DAO.OpenConnection();
string sql = "SELECT * FROM NgonNgu";
NgonNgu = DAO.GetDataToTable(sql);
dataGridView1.DataSource = NgonNgu;
DAO.CloseConnetion();
}
public void LoadDataToGridview()
{
DAO.OpenConnection();
string sql = "SELECT * FROM TacGia";
TacGia = DAO.GetDataToTable(sql);
dataGridView1.DataSource = TacGia;
DAO.CloseConnetion();
}
private void FrmBC6_Load(object sender, EventArgs e)
{
DAO.OpenConnection();
btnIn.Enabled = true;
btnHienThi.Enabled = true;
btnThoat.Enabled = true;
string sql = "SELECT MaSach,TenSach,SoLuong,DonGiaNhap,DonGiaBan,MaLoaiSach,MaTG,MaNXB,MaLinhVuc,MaNgonNgu,Anh,SoTrang FROM KhoSach";
tblBC6 = DAO.GetDataToTable(sql);
dataGridView1.DataSource = tblBC6;
dataGridView1.AllowUserToAddRows = false;
dataGridView1.EditMode = DataGridViewEditMode.EditProgrammatically;
DAO.CloseConnetion();
}
private void FrmBC7_Load(object sender, EventArgs e)
{
DAO.OpenConnection();
btnIn.Enabled = true;
btnHienThi.Enabled = true;
btnThoat.Enabled = true;
string sql = "SELECT SoHDN,MaNV,NgayNhap,MaNCC,TongTien FROM HoaDonNhap";
tblBC7 = DAO.GetDataToTable(sql);
dataGridView1.DataSource = tblBC7;
dataGridView1.AllowUserToAddRows = false;
dataGridView1.EditMode = DataGridViewEditMode.EditProgrammatically;
DAO.CloseConnetion();
}
private void btnHienThi_Click(object sender, EventArgs e)
{
if (cboNam.Text == "")
{
MessageBox.Show("Hãy nhập đủ điều kiện!!!", "Yêu cầu ...", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
loadGridbyngaythang();
string sql = "SELECT SoHDN,MaNV,NgayNhap,MaNCC,TongTien FROM HoaDonNhap WHERE NgayNhap LIKE '%" + cboNam.Text + "%')";
tblBC7 = DAO.GetDataToTable(sql);
DAO.RunSql(sql);
}
private void btnHXuat_Click(object sender, EventArgs e)
{
DAO.OpenConnection();
string sql = "SELECT TOP (5)* FROM HoaDonNhap WHERE NgayNhap LIKE '%" + txtNam.Text + "%' ORDER BY TongTien DESC";
BC7 = DAO.GetDataToTable(sql);
dataGridViewBC7.DataSource = BC7;
dataGridViewBC7.Columns[0].HeaderText = "Số HĐN";
dataGridViewBC7.Columns[0].Width = 145;
dataGridViewBC7.Columns[1].HeaderText = "Ngày nhập";
dataGridViewBC7.Columns[1].Width = 100;
dataGridViewBC7.Columns[2].HeaderText = "Mã NCC";
dataGridViewBC7.Columns[2].Width = 80;
dataGridViewBC7.Columns[3].HeaderText = "Mã NV";
dataGridViewBC7.Columns[3].Width = 75;
dataGridViewBC7.Columns[4].HeaderText = "Tổng tiền";
dataGridViewBC7.Columns[4].Width = 80;
LoadDataGridView();
DAO.CloseConnetion();
}
private void Load_DataGridViewChitiet()
{
string sql;
sql = "SELECT a.Masach, b.Tensach, a.SoLuong, b.DonGiaBan, a.KhuyenMai, a.ThanhTien FROM ChiTietHDB AS a, KhoSach AS b WHERE a.SoHDB = N'" + txtMHB.Text + "' AND a.Masach=b.Masach";
ChiTietHDB = DAO.GetDataToTable(sql);
gridviewHDB.DataSource = ChiTietHDB;
gridviewHDB.Columns[0].HeaderText = "Mã sách";
gridviewHDB.Columns[1].HeaderText = "Tên sách";
gridviewHDB.Columns[2].HeaderText = "Số lượng";
gridviewHDB.Columns[3].HeaderText = "Đơn giá";
gridviewHDB.Columns[4].HeaderText = "Khuyến mại %";
gridviewHDB.Columns[5].HeaderText = "Thành tiền";
gridviewHDB.Columns[0].Width = 80;
gridviewHDB.Columns[1].Width = 100;
gridviewHDB.Columns[2].Width = 80;
gridviewHDB.Columns[3].Width = 90;
gridviewHDB.Columns[4].Width = 90;
gridviewHDB.Columns[5].Width = 90;
gridviewHDB.AllowUserToAddRows = false;
gridviewHDB.EditMode = DataGridViewEditMode.EditProgrammatically;
}
private void btntimkiem_Click(object sender, EventArgs e)
{
string sql;
if ((txtTenSach.Text == "") && (txtTenNXB.Text == "") && (txtTenLoaiSach.Text == ""))
{
MessageBox.Show("Hãy nhập một điều kiện tìm kiếm!!!", "Yêu cầu...", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
sql = "select a.MaSach,a.TenSach,a.SoLuong,a.DonGiaNhap,a.DonGiaBan,a.MaLoaiSach,a.MaTG,a.MaNXB,a.MaLinhVuc,a.MaNgonNgu,a.Anh,a.SoTrang" +
" from KhoSach a join LoaiSach b on a.MaLoaiSach=b.MaloaiSach join NhaXuatBan c on a.MaNXB=c.MaNXB where 1=1";
if (txtTenSach.Text != "")
{
sql = sql + " AND a.TenSach Like '%" + txtTenSach.Text + "%'";
}
if (txtTenLoaiSach.Text != "")
{
sql = sql + " AND b.TenLoaiSach Like '%" + txtTenLoaiSach.Text + "%'";
}
if (txtTenNXB.Text != "")
{
sql = sql + " AND c.TenNXB Like '%" + txtTenNXB.Text + "%'";
}
KhoSach = DAO.GetDataToTable(sql);
if (KhoSach.Rows.Count == 0)
{
MessageBox.Show("Không có bản ghi nào thỏa mãn điều kiện!!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Có " + KhoSach.Rows.Count + " bản ghi thỏa mãn điều kiện!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
dataGridView1.DataSource = KhoSach;
LoadDataGridView();
}
private void btnIn_Click(object sender, EventArgs e)
{
COMExcel.Application exApp = new COMExcel.Application();
COMExcel.Workbook exBook;
COMExcel.Worksheet exSheet;
COMExcel.Range exRange;
string sql;
int hang = 0, cot = 0;
DataTable danhsach;
exBook = exApp.Workbooks.Add(COMExcel.XlWBATemplate.xlWBATWorksheet);
exSheet = exBook.Worksheets[1];
exRange = exSheet.Cells[1, 1];
exRange.Range["A1:Z300"].Font.Name = "Times new roman";
exRange.Range["A1:B3"].Font.Size = 10;
exRange.Range["A1:B3"].Font.Bold = true;
exRange.Range["A1:B3"].Font.ColorIndex = 5;
exRange.Range["A1:A1"].ColumnWidth = 10;
exRange.Range["B1:B1"].ColumnWidth = 20;
exRange.Range["A1:B1"].MergeCells = true;
exRange.Range["A1:B1"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["A1:B1"].Value = "CỔ PHONG BOOKS";
exRange.Range["A2:B2"].MergeCells = true;
exRange.Range["A2:B2"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["A2:B2"].Value = "Cầu Giấy - Hà Nội";
exRange.Range["A3:B3"].MergeCells = true;
exRange.Range["A3:B3"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["A3:B3"].Value = "Điện thoại: (04)37562222";
exRange.Range["C2:G2"].Font.Size = 16;
exRange.Range["C2:G2"].Font.Bold = true;
exRange.Range["C2:G2"].Font.ColorIndex = 3;
exRange.Range["C2:G2"].MergeCells = true;
exRange.Range["C2:G2"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["C2:G2"].Value = "Báo cáo danh sách hóa đơn và tổng tiền theo quý";
sql = "SELECT * FROM KhoSach WHERE NOT EXISTS (SELECT * FROM ChiTietHDB INNER JOIN HoaDonBan ON ChiTietHDB.SoHDB = HoaDonBan.SoHDB WHERE 1=1";
if (cboQuy.Text == "")
{
MessageBox.Show("Bạn phải chọn quý!");
}
if (cboQuy.Text == "1")
{
sql = sql + "AND KhoSach.MaSach = ChiTietHDB.MaSach AND NgayBan >= '2020-01-01' AND NgayBan <= '2020-03-31')";
}
if (cboQuy.Text == "2")
{
sql = sql + "AND KhoSach.MaSach = ChiTietHDB.MaSach AND NgayBan >= '2020-04-01' AND NgayBan <= '2020-06-30')";
}
if (cboQuy.Text == "3")
{
sql = sql + "AND KhoSach.MaSach = ChiTietHDB.MaSach AND NgayBan >= '2020-07-01' AND NgayBan <= '2020-09-30')";
}
if (cboQuy.Text == "4")
{
sql = sql + "AND KhoSach.MaSach = ChiTietHDB.MaSach AND NgayBan >= '2020-10-01' AND NgayBan <= '2020-12-31')";
}
danhsach = DAO.GetDataToTable(sql);
exRange.Range["B5:N5"].Font.Bold = true;
exRange.Range["B5:G5"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["B5:B5"].ColumnWidth = 15;
exRange.Range["C5:C5"].ColumnWidth = 10;
exRange.Range["D5:D5"].ColumnWidth = 20;
exRange.Range["E5:E5"].ColumnWidth = 10;
exRange.Range["F5:F5"].ColumnWidth = 15;
exRange.Range["G5:G5"].ColumnWidth = 15;
exRange.Range["H5:H5"].ColumnWidth = 15;
exRange.Range["I5:I5"].ColumnWidth = 10;
exRange.Range["J5:J5"].ColumnWidth = 10;
exRange.Range["K5:K5"].ColumnWidth = 10;
exRange.Range["L5:L5"].ColumnWidth = 10;
exRange.Range["M5:M5"].ColumnWidth = 12;
exRange.Range["N5:N5"].ColumnWidth = 8;
exRange.Range["B5:B5"].Value = "STT";
exRange.Range["C5:C5"].Value = "Mã sách";
exRange.Range["D5:D5"].Value = "Tên sách";
exRange.Range["E5:E5"].Value = "Số lượng";
exRange.Range["F5:F5"].Value = "Đơn giá nhập";
exRange.Range["G5:G5"].Value = "Đơn giá bán";
exRange.Range["H5:H5"].Value = "Mã loại sách";
exRange.Range["I5:I5"].Value = "Mã TG";
exRange.Range["J5:J5"].Value = "Mã NXB";
exRange.Range["K5:K5"].Value = "Mã lĩnh vực";
exRange.Range["L5:L5"].Value = "Mã NN";
exRange.Range["M5:M5"].Value = "Ảnh";
exRange.Range["N5:N5"].Value = "Số trang";
for (hang = 0; hang < danhsach.Rows.Count; hang++)
{
exSheet.Cells[2][hang + 6] = hang + 1;
for (cot = 0; cot < danhsach.Columns.Count; cot++)
{
exSheet.Cells[cot + 3][hang + 6] = danhsach.Rows[hang][cot].ToString();
}
}
exRange = exSheet.Cells[2][hang + 8];
exRange.Range["L1:N1"].MergeCells = true;
exRange.Range["L1:N1"].Font.Italic = true;
exRange.Range["L1:N1"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["L1:N1"].Value = "Hà Nội, " + DateTime.Now.ToShortDateString();
exSheet.Name = "Báo cáo";
exApp.Visible = true;
}
private void btnIn_Click(object sender, EventArgs e)
{
COMExcel.Application exApp = new COMExcel.Application();
COMExcel.Workbook exBook; //Trong 1 chương trình Excel có nhiều Workbook
COMExcel.Worksheet exSheet; //Trong 1 Workbook có nhiều Worksheet
COMExcel.Range exRange;
string sql;
int hang = 0, cot = 0;
DataTable tblThongtinHD, tblThongtinHang;
exBook = exApp.Workbooks.Add(COMExcel.XlWBATemplate.xlWBATWorksheet);
exSheet = exBook.Worksheets[1];
// Định dạng chung
exRange = exSheet.Cells[1, 1];
exRange.Range["A1:B3"].Font.Size = 10;
exRange.Range["A1:B3"].Font.Name = "Times new roman";
exRange.Range["A1:B3"].Font.Bold = true;
exRange.Range["A1:B3"].Font.ColorIndex = 5; //Màu xanh da trời
exRange.Range["A1:A1"].ColumnWidth = 7;
exRange.Range["B1:B1"].ColumnWidth = 15;
exRange.Range["A1:B1"].MergeCells = true;
exRange.Range["A1:B1"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["A1:B1"].Value = "CỔ PHONG BOOKS";
exRange.Range["A2:B2"].MergeCells = true;
exRange.Range["A2:B2"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["A2:B2"].Value = "Cầu Giấy - Hà Nội";
exRange.Range["A3:B3"].MergeCells = true;
exRange.Range["A3:B3"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["A3:B3"].Value = "Điện thoại: (04)37562222";
exRange.Range["C2:E2"].Font.Size = 16;
exRange.Range["C2:E2"].Font.Name = "Times new roman";
exRange.Range["C2:E2"].Font.Bold = true;
exRange.Range["C2:E2"].Font.ColorIndex = 3; //Màu đỏ
exRange.Range["C2:E2"].MergeCells = true;
exRange.Range["C2:E2"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["C2:E2"].Value = "HÓA ĐƠN BÁN";
// Biểu diễn thông tin chung của hóa đơn bán
sql = "SELECT a.SoHDB, a.NgayBan, a.TongTien, b.TenKhach, b.DiaChi, b.DienThoai, c.TenNV FROM HoaDonBan AS a, KhachHang AS b, NhanVien AS c WHERE a.SoHDB = N'" + txtMHB.Text + "' AND a.MaKhach = b.MaKhach AND a.MaNV =c.MaNV";
tblThongtinHD = DAO.GetDataToTable(sql);
exRange.Range["B6:C9"].Font.Size = 12;
exRange.Range["B6:C9"].Font.Name = "Times new roman";
exRange.Range["B6:B6"].Value = "Mã hóa đơn:";
exRange.Range["C6:E6"].MergeCells = true;
exRange.Range["C6:E6"].Value = tblThongtinHD.Rows[0][0].ToString();
exRange.Range["B7:B7"].Value = "Khách hàng:";
exRange.Range["C7:E7"].MergeCells = true;
exRange.Range["C7:E7"].Value = tblThongtinHD.Rows[0][3].ToString();
exRange.Range["B8:B8"].Value = "Địa chỉ:";
exRange.Range["C8:E8"].MergeCells = true;
exRange.Range["C8:E8"].Value = tblThongtinHD.Rows[0][4].ToString();
exRange.Range["B9:B9"].Value = "Điện thoại:";
exRange.Range["C9:E9"].MergeCells = true;
exRange.Range["C9:E9"].Value = tblThongtinHD.Rows[0][5].ToString();
//Lấy thông tin các mặt hàng
sql = "SELECT b.TenSach, a.SoLuong, b.DonGiaBan, a.KhuyenMai, a.ThanhTien " +
"FROM ChiTietHDB AS a , KhoSach AS b WHERE a.SoHDB = N'" +
txtMHB.Text + "' AND a.MaSach = b.MaSach";
tblThongtinHang = DAO.GetDataToTable(sql);
//Tạo dòng tiêu đề bảng
exRange.Range["A11:F11"].Font.Bold = true;
exRange.Range["A11:F11"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["C11:F11"].ColumnWidth = 12;
exRange.Range["A11:A11"].Value = "STT";
exRange.Range["B11:B11"].Value = "Tên hàng";
exRange.Range["C11:C11"].Value = "Số lượng";
exRange.Range["D11:D11"].Value = "Đơn giá";
exRange.Range["E11:E11"].Value = "Giảm giá";
exRange.Range["F11:F11"].Value = "Thành tiền";
for (hang = 0; hang <= tblThongtinHang.Rows.Count - 1; hang++)
{
//Điền số thứ tự vào cột 1 từ dòng 12
exSheet.Cells[1][hang + 12] = hang + 1;
for (cot = 0; cot <= tblThongtinHang.Columns.Count - 1; cot++)
{
//Điền thông tin hàng từ cột thứ 2, dòng 12
exSheet.Cells[cot + 2][hang + 12] = tblThongtinHang.Rows[hang][cot].ToString();
}
}
exRange = exSheet.Cells[cot][hang + 14];
exRange.Font.Bold = true;
exRange.Value2 = "Tổng tiền:";
exRange = exSheet.Cells[cot + 1][hang + 14];
exRange.Font.Bold = true;
exRange.Value2 = tblThongtinHD.Rows[0][2].ToString();
exRange = exSheet.Cells[1][hang + 15]; //Ô A1
exRange.Range["A1:F1"].MergeCells = true;
exRange.Range["A1:F1"].Font.Bold = true;
exRange.Range["A1:F1"].Font.Italic = true;
exRange.Range["A1:F1"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignRight;
exRange.Range["A1:F1"].Value = "Bằng chữ: " + DAO.ChuyenSoSangChu(tblThongtinHD.Rows[0][2].ToString());
exRange = exSheet.Cells[4][hang + 17]; //Ô A1
exRange.Range["A1:C1"].MergeCells = true;
exRange.Range["A1:C1"].Font.Italic = true;
exRange.Range["A1:C1"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
DateTime d = Convert.ToDateTime(tblThongtinHD.Rows[0][1]);
exRange.Range["A1:C1"].Value = "Hà Nội, ngày " + d.Day + " tháng " + d.Month + " năm " + d.Year;
exRange.Range["A2:C2"].MergeCells = true;
exRange.Range["A2:C2"].Font.Italic = true;
exRange.Range["A2:C2"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["A2:C2"].Value = "Nhân viên bán hàng";
exRange.Range["A6:C6"].MergeCells = true;
exRange.Range["A6:C6"].Font.Italic = true;
exRange.Range["A6:C6"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["A6:C6"].Value = tblThongtinHD.Rows[0][6];
exSheet.Name = "Hóa đơn nhập";
exApp.Visible = true;
}
