private void button2_Click(object sender, EventArgs e)
{
try
{
HoTro ht = new HoTro();
string command = "select ProductId, MfgDate, ExpDate, ProdName, PurchasePrice, SalePrice,DisCount, BillSale.CreateDate"
+ " from product join ProductName"
+ " on Product.productNameid = ProductName.productNameid"
+ " join BillSale"
+ " on Product.BillSaleId = BillSale.BillSaleId";
SqlDataAdapter adapter = new SqlDataAdapter(command, ht.KetNoi());
BaoCao.DataSet2 dataset = new SSMP.BaoCao.DataSet2();
adapter.Fill(dataset, "DataTable1");
BaoCao.TestXuatCrystalReport report = new SSMP.BaoCao.TestXuatCrystalReport();
report.SetDataSource(dataset);
crystalReportViewer1.ReportSource = report;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
MessageBox.Show(ex.StackTrace);
}
}
private void btnDoiMatKhau_Click(object sender, EventArgs e)
{
//kiem tra mat khau cu khac rong
if (txtMatKhauCu.Text.Length == 0)
{
MessageBox.Show(this, "Mật khẩu cũ không được trống", "Thông báo", MessageBoxButtons.OK);
txtMatKhauCu.Focus();
txtMatKhauCu.SelectAll();
return;
}
//kiem tra mat khau moi khac rong
else if (txtMatKhauMoi.Text.Length == 0)
{
MessageBox.Show(this, "Mật khẩu mới không được trống", "Thông báo", MessageBoxButtons.OK);
txtMatKhauMoi.Focus();
txtMatKhauMoi.SelectAll();
return;
}
//kiem tra go lai mat khau giong mat khau
else if (!txtMatKhauMoi.Text.Equals(txtXacNhanLaiMatKhau.Text))
{
MessageBox.Show(this, "Mật khẩu mới không trùng nhau", "Thông báo", MessageBoxButtons.OK);
txtXacNhanLaiMatKhau.Focus();
txtXacNhanLaiMatKhau.SelectAll();
return;
}
else
{
try
{
int userId = DangNhap.idNguoiDung;
HoTro ht = new HoTro();
SqlConnection conn = ht.KetNoi() ;
conn.Open();
SqlCommand comm = new SqlCommand("select password from Users where UserId=" + userId, conn);
string oldPass = comm.ExecuteScalar().ToString();
if (!oldPass.Equals(txtMatKhauCu.Text))
{
MessageBox.Show(this, "Mật khẩu cũ không đúng !", "Lỗi nhập mật khẩu cũ", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtMatKhauCu.Focus();
txtMatKhauCu.SelectAll();
return;
}
else {
comm = new SqlCommand("update Users set Password='******' where userId = " + userId, conn);
comm.ExecuteNonQuery();
MessageBox.Show(this, "Bạn đã thay đổi mật khẩu thành công!", "Thông báo thay đổi mật khẩu thành công");
this.Dispose();
}
conn.Close();
}
catch (Exception ex) {
MessageBox.Show(ex.Message);
}
}
}
private void btnCapNhat_Click(object sender, EventArgs e)
{
try
{
if (dataGridViewDS.Rows.Count < 1) {
HoTro.baoLoi("Nhập dữ liệu để cập nhật");
return;
}
HoTro ht = new HoTro() ;
SqlConnection conn = ht.KetNoi();
conn.Open();
SqlCommand command = new SqlCommand();
int maNguoiDung = DangNhap.idNguoiDung;
for (int i = 0; i < dataGridViewDS.Rows.Count; i++)
{
DataGridViewRow row = dataGridViewDS.Rows[i];
// insert PhieuXuatKho
command = new SqlCommand("select CustomerId from Customer where CusTomerName=@Cusname", conn);
command.Parameters.Add("@CusNAme", row.Cells[1].Value.ToString());
int cusId = Int32.Parse(command.ExecuteScalar().ToString()) ;
command = new SqlCommand("insert into BillSale values(@ngaythang, @nguoiMua, @manguoiDung)");
command.Parameters.Add("@ngaythang", ht.toSQLDateTime(txtNgayThang.Text));
command.Parameters.Add("@nguoiMua", cusId);
command.Parameters.Add("@manguoiDung", maNguoiDung);
ht.CapNhatDuLieu(command);
// update Product
Int64 billSaleId = ht.LayGiaTriTruongKhoaVuaChen2("BillSale", "BillSaleId");
command = new SqlCommand("update Product set StatusId = 2, billSaleId=@billSaleId where ProductId = @id");
command.Parameters.Add("@id", Int64.Parse(row.Cells[0].Value.ToString()));
command.Parameters.Add("@billSaleId", billSaleId);
ht.CapNhatDuLieu(command);
}
HoTro.thongBao("Đã cập nhật !");
conn.Close();
dataGridViewDS.Rows.Clear();
}
catch (Exception ex)
{
//MessageBox.Show(ex.StackTrace);
HoTro.baoLoi(ex.Message);
}
}
private void taiCrystalReport()
{
try
{
string tuNgay, denNgay;
if (checkBoxNgay.Checked)
{
tuNgay = dateTimePicker1.Value.ToShortDateString();
denNgay = dateTimePicker2.Value.ToShortDateString();
}
else
{
tuNgay = "1/1/2009";
denNgay = DateTime.Now.ToShortDateString();
}
HoTro ht = new HoTro();
SqlConnection conn = ht.KetNoi();
string command = "select ProductId,convert(varchar,MfgDate,103) as 'MfgDate',convert(varchar,ExpDate,103) as 'ExpDate' , ProdName, PurchasePrice, SalePrice,DisCount,convert(varchar,BillSale.CreateDate,103) as 'CreateDate' "
+ " from product join ProductName"
+ " on Product.productNameid = ProductName.productNameid"
+ " join BillSale"
+ " on Product.BillSaleId = BillSale.BillSaleId"
+ " where BillSale.CreateDate >= '" + tuNgay + "' and BillSale.CreateDate <= '" + denNgay + "'";
SqlDataAdapter adapter = new SqlDataAdapter(command, conn);
BaoCao.DataSet2 dataset = new SSMP.BaoCao.DataSet2();
adapter.Fill(dataset, "DataTable1");
BaoCao.TestXuatCrystalReport report = new SSMP.BaoCao.TestXuatCrystalReport();
report.SetDataSource(dataset);
crystalReportViewer1.ReportSource = report;
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void buttonDelete_Click(object sender, EventArgs e)
{
try
{
// kiem tra xem co xoa dc ko
if (HoTro.confirm("Bạn có chắc muốn xóa hóa đơn này ?", "Xác nhận").Equals("No")) return;
Int64 maPhieu = Int64.Parse(dataGridViewDSPhieuBan.SelectedRows[0].Cells[0].Value.ToString());
HoTro ht = new HoTro();
SqlCommand com = new SqlCommand("update Product set BillSaleId=NULL, StatusId=1 where BillSaleId=" + maPhieu, ht.KetNoi());
ht.CapNhatDuLieu(com);
com = new SqlCommand("delete BillSale where BillSaleId=" + maPhieu, ht.KetNoi());
ht.CapNhatDuLieu(com);
taiDuLieu();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnDuaVaoPhieuXuat_Click(object sender, EventArgs e)
{
try
{
Int64.Parse(comboBoxId.Text);
Int32.Parse(comboBoxIdKh.Text);
HoTro ht = new HoTro();
SqlConnection conn = ht.KetNoi() ;
conn.Open() ;
SqlCommand command = new SqlCommand("select CustomerName from Customer where CustomerId = @khachhang", conn);
command.Parameters.Add("@khachhang", comboBoxIdKh.Text);
String khachHang = command.ExecuteScalar().ToString();
command = new SqlCommand("select StatusId from Product where ProductId = @id", conn);
command.Parameters.Add("@id", Int64.Parse(comboBoxId.Text));
int statusId = Int32.Parse(command.ExecuteScalar().ToString());
if (statusId != 1) {
HoTro.baoLoi("Sản phẩm này đã được bán");
conn.Close();
return;
}
foreach (DataGridViewRow row in dataGridViewDS.Rows) {
if (row.Cells[0].Value.ToString().Contains(comboBoxId.Text)) {
HoTro.baoLoi("Sản phẩm này đã có trong phiếu");
conn.Close();
return;
}
}
command = new SqlCommand("select ProdName from ProductName where ProductNameId = (select max(ProductNameId) " +
" from Product where ProductId = @id)", conn);
command.Parameters.Add("@id", Int64.Parse(comboBoxId.Text));
String tenSp = command.ExecuteScalar().ToString();
command = new SqlCommand("select UnitName from Units where UnitId = (select max (UnitId) from Product " +
"where ProductId = @id)", conn);
command.Parameters.Add("@id", comboBoxId.Text);
String donvi = command.ExecuteScalar().ToString();
command = new SqlCommand("select SalePrice from Product where ProductId = @id", conn);
command.Parameters.Add("@id", Int64.Parse(comboBoxId.Text));
String giaBan = command.ExecuteScalar().ToString();
if (giaBan.Length == 0)
{
HoTro.baoLoi("Sản phẩm này chưa được định giá bán.");
conn.Close();
return;
}
command = new SqlCommand("select Discount from Product where ProductId = @id", conn);
command.Parameters.Add("@id", Int64.Parse(comboBoxId.Text));
String giamGia = command.ExecuteScalar().ToString();
conn.Close();
String[] data = new String[] { comboBoxId.Text, khachHang, tenSp, donvi, giaBan, giamGia };
dataGridViewDS.Rows.Add(data);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
HoTro.baoLoi("Dữ liệu nhập sai !");
}
}
private void cboNhaSanXuat_SelectedIndexChanged(object sender, EventArgs e)
{
ht = new HoTro() ;
int manId = ht.LayVeKhoa("Manufacturer", "ManId", "ManName", cboNhaSanXuat.Text) ;
SqlConnection conn = ht.KetNoi();
try {
int countryId = 0;
string CauLenh = "select countryId from Manufacturer where manId= " + manId;
SqlCommand cmd = new SqlCommand(CauLenh, conn);
cmd.Connection.Open();
dr = cmd.ExecuteReader();
if (dr.Read())
{
cboNguonGoc.SelectedValue = (Object)dr.GetInt32(0);
}
} catch(Exception ex) {
MessageBox.Show(ex.Message) ;
conn.Close() ;
}
}
private void buttonDelete_Click(object sender, EventArgs e)
{
try
{
// kiem tra xem co xoa dc ko
if (HoTro.confirm("Bạn có chắc muốn xóa hóa đơn này ?", "Xác nhận").Equals("No")) return;
Int64 maPhieu = Int64.Parse(dataGridViewPhieuNhapHAng.SelectedRows[0].Cells[0].Value.ToString());
ht = new HoTro();
SqlCommand com = new SqlCommand("select * from Product where BillPurchaseId=" + maPhieu + " and BillSaleId is not NULL", ht.KetNoi());
com.Connection.Open();
if (com.ExecuteScalar() != null)
{
HoTro.baoLoi("Không xóa được hóa đơn mua này, có sản phẩm đã được bán");
return;
}
com.Connection.Close();
com = new SqlCommand("delete Product where BillPurchaseId=" + maPhieu, ht.KetNoi());
ht.CapNhatDuLieu(com);
com = new SqlCommand("delete BillPurchase where BillPurchaseId=" + maPhieu, ht.KetNoi());
ht.CapNhatDuLieu(com);
taiDuLieu();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}