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; }