private void btnTimKiem_Click(object sender, EventArgs e)
{
string sql;
if ((txtMaKH.Text == "") && (txtTenKH.Text == "") &&
(txtSDT.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 * FROM tbKhachHang WHERE 1=1";
if (txtMaKH.Text != "")
{
sql = sql + " AND MaKH Like N'%" + txtMaKH.Text + "%'";
}
if (txtTenKH.Text != "")
{
sql = sql + " AND TenKhachHang Like N'%" + txtTenKH.Text + "%'";
}
if (txtSDT.Text != "")
{
sql = sql + " AND SDT Like N'%" + txtSDT.Text + "%'";
}
tbKH = ConnectToSQL.GetData(sql);
if (tbKH.Rows.Count == 0)
{
MessageBox.Show("Không có dữ liệu thỏa mãn điều kiện!!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Có " + tbKH.Rows.Count + " dữ liệu thỏa mãn điều kiện!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
dtGVKhachHang.DataSource = tbKH;
LoadDataGridView();
}
public void LoadData()
{
string sql = "select * from tbKhachHang";
tblKhachHang = ConnectToSQL.GetData(sql);
dgvKhachHang.DataSource = tblKhachHang;
dgvKhachHang.AllowUserToAddRows = false;
dgvKhachHang.EditMode = DataGridViewEditMode.EditProgrammatically;
}
public void LoadData()
{
string query = "SELECT * FROM tbSanPham";
tblSanPham = ConnectToSQL.GetData(query);
menuList.DataSource = tblSanPham;
dtgvSanPham.AllowUserToAddRows = false;
dtgvSanPham.EditMode = DataGridViewEditMode.EditProgrammatically;
}
public void SearchFoodByName(string name)
{
string query = string.Format("SELECT * FROM tbSanPham WHERE dbo.convertToUnsign(TenSP) LIKE N'%' + dbo.GetUnsignString(N'{0}') +'%'", name);
tblSanPham = ConnectToSQL.GetData(query);
menuList.DataSource = tblSanPham;
dtgvSanPham.AllowUserToAddRows = false;
dtgvSanPham.EditMode = DataGridViewEditMode.EditProgrammatically;
}
public void LoadData()
{
string sql = "select MaNV, TenNhanVien, GioiTinh, DiaChi, SDT, NgaySinh, MatKhau from tbNhanVien";
tblNhanVien = ConnectToSQL.GetData(sql);
dgvNhanVien.DataSource = tblNhanVien;
dgvNhanVien.AllowUserToAddRows = false;
dgvNhanVien.EditMode = DataGridViewEditMode.EditProgrammatically;
dgvNhanVien.Columns["MatKhau"].Visible = false;
}
private void btnTimKiem_Click(object sender, EventArgs e)
{
string sql;
if ((txtMaHD.Text == "") && (txtThang.Text == "") && (txtNam.Text == "") &&
(txtMaNV.Text == "") && (txtMaKH.Text == "") &&
(txtTongTien.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 * FROM tbHoaDon WHERE 1=1";
if (txtMaHD.Text != "")
{
sql = sql + " AND MaHD Like N'%" + txtMaHD.Text + "%'";
}
if (txtThang.Text != "")
{
sql = sql + " AND MONTH(NgayLap) =" + txtThang.Text;
}
if (txtNam.Text != "")
{
sql = sql + " AND YEAR(NgayLap) =" + txtNam.Text;
}
if (txtMaNV.Text != "")
{
sql = sql + " AND MaNV Like N'%" + txtMaNV.Text + "%'";
}
if (txtMaKH.Text != "")
{
sql = sql + " AND MaKH Like N'%" + txtMaKH.Text + "%'";
}
if (txtTongTien.Text != "")
{
sql = sql + " AND TongThanhTien <=" + txtTongTien.Text;
}
tbHD = ConnectToSQL.GetData(sql);
if (tbHD.Rows.Count == 0)
{
MessageBox.Show("Không có dữ liệu thỏa mãn điều kiện!!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Có " + tbHD.Rows.Count + " dữ liệu thỏa mãn điều kiện!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
dtGVHoaDon.DataSource = tbHD;
LoadDataGridView();
}
private void btnDangNhap_Click(object sender, EventArgs e)
{
DataTable table = new DataTable();
table = ConnectToSQL.GetData("select * from tbNhanVien where MaNV = '" + txtUsername.Text + "' and MatKhau = '" + txtPassword.Text + "'");
if (table.Rows.Count > 0)
{
MessageBox.Show("Đăng nhập thành công!", "Chúc mừng", MessageBoxButtons.OK, MessageBoxIcon.Information);
// Chuyển sang FormMain
FormMain frmMain = new FormMain(table.Rows[0][0].ToString(), table.Rows[0][6].ToString());
this.Hide();
frmMain.ShowDialog();
}
else
{
MessageBox.Show("Đăng nhập thất bại!", "Xin lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void LoadDataGridView()
{
string sql;
sql = "SELECT A.MaSP, B.TenSP, A.SoLuong, B.Gia, A.GiamGia, A.TongTien FROM tbCTHD AS A, tbSanPham AS B WHERE A.MaHD = '" + txtMaHD.Text + "' AND A.MaSP=B.MaSP";
tbHD = ConnectToSQL.GetData(sql);
dGVCTHD.DataSource = tbHD;
dGVCTHD.Columns[0].HeaderText = "Mã hàng";
dGVCTHD.Columns[1].HeaderText = "Tên hàng";
dGVCTHD.Columns[2].HeaderText = "Số lượng";
dGVCTHD.Columns[3].HeaderText = "Đơn giá";
dGVCTHD.Columns[4].HeaderText = "Giảm giá %";
dGVCTHD.Columns[5].HeaderText = "Thành tiền";
dGVCTHD.Columns[0].Width = 80;
dGVCTHD.Columns[1].Width = 130;
dGVCTHD.Columns[2].Width = 80;
dGVCTHD.Columns[3].Width = 90;
dGVCTHD.Columns[4].Width = 90;
dGVCTHD.Columns[5].Width = 90;
dGVCTHD.AllowUserToAddRows = false;
dGVCTHD.EditMode = DataGridViewEditMode.EditProgrammatically;
}
private void btnInHoaDon_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 tbThongTinChung, tbThongTinHang;
exBook = exApp.Workbooks.Add(COMExcel.XlWBATemplate.xlWBATWorksheet);
exSheet = exBook.Worksheets[1];
// Định dạng chung
exRange = exSheet.Cells[1, 1];
exRange.Range["A1:Z200"].Font.Name = "Times new roman";
exRange.Range["A2:B3"].Font.Size = 10;
exRange.Range["A1:B3"].Font.Bold = true;
exRange.Range["A1:A1"].Font.Size = 20;
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 = "Smart";
exRange.Range["A2:B2"].MergeCells = true;
exRange.Range["A2:B2"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["A2:B2"].Value = "Lê Văn Lương";
exRange.Range["C2:E2"].Font.Size = 20;
exRange.Range["C2:E2"].Font.Bold = true;
exRange.Range["C2:E2"].MergeCells = true;
exRange.Range["C2:E2"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["C2:E2"].Value = "HÓA ĐƠN BÁN";
exRange.Range["A3:B3"].MergeCells = true;
exRange.Range["A3:B3"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["A3:B3"].Value = "Điện thoại: (+84)456789012";
// Biểu diễn thông tin chung của hóa đơn bán
sql = "SELECT A.MaHD, A.NgayLap, A.TongThanhTien, B.TenKhachHang, B.DiaChi, B.SDT, C.TenNhanVien FROM tbHoaDon AS A, tbKhachHang AS B, tbNhanVien AS C WHERE A.MaHD = N'" + txtMaHD.Text + "' AND A.MaKH = B.MaKH AND A.MaNV = C.MaNV";
tbThongTinChung = ConnectToSQL.GetData(sql);
exRange.Range["B6:C9"].Font.Size = 12;
exRange.Range["B6:B6"].Value = "Mã hóa đơn:";
exRange.Range["C6:E6"].MergeCells = true;
exRange.Range["C6:E6"].Value = tbThongTinChung.Rows[0][0].ToString();
exRange.Range["B7:B7"].Value = "Khách hàng:";
exRange.Range["C7:E7"].MergeCells = true;
exRange.Range["C7:E7"].Value = tbThongTinChung.Rows[0][3].ToString();
exRange.Range["B8:B8"].Value = "Địa chỉ:";
exRange.Range["C8:E8"].MergeCells = true;
exRange.Range["C8:E8"].Value = tbThongTinChung.Rows[0][4].ToString();
exRange.Range["B9:B9"].Value = "Điện thoại:";
exRange.Range["C9:E9"].MergeCells = true;
exRange.Range["C9:E9"].Value = tbThongTinChung.Rows[0][5].ToString();
//Lấy thông tin các sản phẩm
sql = "SELECT B.MaSP,B.TenSP, A.SoLuong, B.Gia, A.GiamGia, A.TongTien " + "FROM tbCTHD AS A , tbSanPham AS B WHERE A.MaHD = N'" + txtMaHD.Text + "' AND A.MaSP = B.MaSP";
tbThongTinHang = ConnectToSQL.GetData(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 = "Mã SP";
exRange.Range["C11:C11"].Value = "Tên SP";
exRange.Range["D11:D11"].Value = "Số lượng";
exRange.Range["E11:E11"].Value = "Đơn giá";
exRange.Range["F11:F11"].Value = "Giảm giá";
exRange.Range["G11:G11"].Value = "Thành tiền";
for (hang = 0; hang < tbThongTinHang.Rows.Count; hang++)
{
//Điền số thứ tự
exSheet.Cells[1][hang + 12] = hang + 1;
for (cot = 0; cot < tbThongTinHang.Columns.Count; cot++)
//Điền thông tin sản phẩm
{
exSheet.Cells[cot + 2][hang + 12] = tbThongTinHang.Rows[hang][cot].ToString();
if (cot == 4)
{
exSheet.Cells[cot + 2][hang + 12] = tbThongTinHang.Rows[hang][cot].ToString() + "%";
}
}
}
exRange = exSheet.Cells[cot][hang + 14];
exRange.Font.Bold = true;
exRange.Value2 = "Tổng TT:";
exRange = exSheet.Cells[cot + 1][hang + 14];
exRange.Font.Bold = true;
exRange.Value2 = tbThongTinChung.Rows[0][2].ToString();
exRange = exSheet.Cells[4][hang + 17];
exRange.Range["A1:C1"].MergeCells = true;
exRange.Range["A1:C1"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
DateTime d = Convert.ToDateTime(tbThongTinChung.Rows[0][1]);
exRange.Range["A1:C1"].Value = "Nhà Bè, ngày " + d.Day + " tháng " + d.Month + " năm " + d.Year;
exRange.Range["A2:C2"].MergeCells = 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"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["A6:C6"].Value = tbThongTinChung.Rows[0][6];
exSheet.Name = "Hóa đơn";
exApp.Visible = true;
}