public bool SuaHangHoa(HangHoa_DTO hh)
{
OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "update HANGHOA set TEN_HANG= @TenHang, SL= @SoLuong, GHI_CHU= @GhiChu, ID=@ID, DON_GIA=@DonGia, HANG_SX=@HangSX where MA_HANG=@MaHang";
cmd.Connection = conn;
cmd.Parameters.Add("@MaHang", SqlDbType.VarChar).Value = hh.MaHang;
cmd.Parameters.Add("@TenHang", SqlDbType.NVarChar).Value = hh.TenHang;
cmd.Parameters.Add("@SoLuong", SqlDbType.Int).Value = hh.SoLuong;
cmd.Parameters.Add("@GhiChu", SqlDbType.NVarChar).Value = hh.GhiChu;
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = hh.ID;
cmd.Parameters.Add("@DonGia", SqlDbType.Int).Value = hh.DonGia;
cmd.Parameters.Add("@HangSX", SqlDbType.NVarChar).Value = hh.HangSX;
int kq = cmd.ExecuteNonQuery();
if (kq > 0)
{
return(true);
}
else
{
return(false);
}
}
private void ktSuahh()
{
try
{
HangHoa_DTO hh = new HangHoa_DTO();
hh.MaHang = txtMaHang.Text.Trim();
hh.TenHang = txtTenHang.Text.Trim();
hh.SoLuong = int.Parse(txtSoLuong.Text.ToString());
hh.GhiChu = txtGhiChu.Text.Trim();
hh.DonGia = int.Parse(txtDonGia.Text.Trim().ToString());
hh.HangSX = txtHangSX.Text.Trim();
if (hhBLL.SuaHangHoa(hh))
{
MessageBox.Show("Sửa thành công!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Sửa không thành công!", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
Load_HangHoa();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public bool ThemHangHoa(HangHoa_DTO hh)
{
OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert into HANGHOA values(@MaHang,@TenHang, @SoLuong, @GhiChu, @ID, @DonGia, @HangSX)";
cmd.Connection = conn;
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = hh.ID;
cmd.Parameters.Add("@MaHang", SqlDbType.VarChar).Value = hh.MaHang;
cmd.Parameters.Add("@TenHang", SqlDbType.NVarChar).Value = hh.TenHang;
cmd.Parameters.Add("@SoLuong", SqlDbType.Int).Value = hh.SoLuong;
cmd.Parameters.Add("@GhiChu", SqlDbType.NVarChar).Value = hh.GhiChu;
cmd.Parameters.Add("@DonGia", SqlDbType.Int).Value = hh.DonGia;
cmd.Parameters.Add("@HangSX", SqlDbType.NVarChar).Value = hh.HangSX;
int kq = cmd.ExecuteNonQuery();
if (kq > 0)
{
return(true);
}
else
{
return(false);
}
}
public List <HangHoa_DTO> autoDuLieu()
{
List <HangHoa_DTO> tthh = new List <HangHoa_DTO>();
HangHoa_DTO hh = new HangHoa_DTO();
OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from HANGHOA where MA_HANG='" + hh.MaHang + "'";
cmd.Connection = conn;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
hh.MaHang = reader.GetString(0);
hh.TenHang = reader.GetString(1);
tthh.Add(hh);
}
if (reader != null)
{
reader.Close();
}
return(tthh);
}
public List <HangHoa_DTO> ThongTinHangHoa()
{
List <HangHoa_DTO> tthh = new List <HangHoa_DTO>();
OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from HANGHOA order by SL ASC";
cmd.Connection = conn;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
HangHoa_DTO hh = new HangHoa_DTO();
hh.MaHang = reader.GetString(0);
hh.TenHang = reader.GetString(1);
hh.SoLuong = reader.GetInt32(2);
hh.GhiChu = reader.GetString(3);
hh.ID = reader.GetInt32(4);
hh.DonGia = reader.GetInt32(5);
hh.HangSX = reader.GetString(6);
tthh.Add(hh);
}
if (reader != null)
{
reader.Close();
}
return(tthh);
}
public bool CapNhatHangHoa(HangHoa_DTO hh)
{
SqlConnection conn = ConnectToSQL.HamKetNoi();
string str = "UPDATE HANGHOA SET Ten = @Ten, Dongia = @Dongia, Soluong = @Soluong WHERE Id = @Id";
SqlCommand cmd = new SqlCommand(str, conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@Id", SqlDbType.NChar, 10);
cmd.Parameters.Add("@Ten", SqlDbType.NVarChar, 50);
cmd.Parameters.Add("@Dongia", SqlDbType.Float);
cmd.Parameters.Add("@Soluong", SqlDbType.Int);
cmd.Parameters["@Id"].Value = hh.Id;
cmd.Parameters["@Ten"].Value = hh.Ten;
cmd.Parameters["@Dongia"].Value = hh.Dongia;
cmd.Parameters["@Soluong"].Value = hh.Soluong;
conn.Open();
int t = cmd.ExecuteNonQuery();
if (t < 1)
{
return(false);
}
conn.Close();
return(true);
}
private void btnCapNhat_Click(object sender, EventArgs e)
{
//lay du lieu tu form
string id = txtIDSua.Text;
string ten = txtTenSua.Text;
float dongia = float.Parse(txtDonGiaSua.Text);
int soluong = int.Parse(txtSoLuongSua.Text);
//thao tac them
HangHoa_DTO hh = new HangHoa_DTO(id, ten, dongia, soluong);
HangHoa_BUS t = new HangHoa_BUS();
if (!t.CapNhatHangHoa(hh))
{
MessageBox.Show("Cập nhật không thành công. Vui lòng thử lại!.");
}
//hien thi lai form
dgvSua.DataSource = t.LoadHangHoa();
bingdingSua();
//hien thi lai tab ds
dgvDS.DataSource = t.LoadHangHoa();
//hien thi lai tab them
dgvThem.DataSource = t.LoadHangHoa();
//hien thi lai tab xoa
dgvXoa.DataSource = t.LoadHangHoa();
bingdingXoa();
}
public List <HangHoa_DTO> TimKiemHangHoa(string tenhang)
{
List <HangHoa_DTO> dsHangHoa = new List <HangHoa_DTO>();
OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from HANGHOA where TEN_HANG like N'%'+@TenHang+'%'";
cmd.Connection = conn;
cmd.Parameters.Add("@TenHang", SqlDbType.NVarChar).Value = tenhang;
//cmd.Parameters.Add("@MaHang", SqlDbType.VarChar).Value = mahang;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
HangHoa_DTO hh = new HangHoa_DTO();
hh.MaHang = reader.GetString(0);
hh.TenHang = reader.GetString(1);
hh.SoLuong = reader.GetInt32(2);
hh.GhiChu = reader.GetString(3);
hh.ID = reader.GetInt32(4);
hh.DonGia = reader.GetInt32(5);
hh.HangSX = reader.GetString(6);
dsHangHoa.Add(hh);
}
reader.Close();
return(dsHangHoa);
}
void hiemThiThongTinHangHoa(HangHoa_DTO hangHoa)
{
txtTenHang.Text = hangHoa.TenHH;
txtGia.Text = hangHoa.DonGiaBan.ToString();
numberSoLuong.Value = 0;
txtThanhTien.Text = "0";
}
private void cbBMaHang_SelectionChangeCommitted(object sender, EventArgs e)
{
if (cbBMaHang.SelectedItem != null)
{
HangHoa_DTO hangHoa_DTO = (HangHoa_DTO)cbBMaHang.SelectedItem;
hiemThiThongTinHangHoa(hangHoa_DTO);
}
}
public ActionResult <IEnumerable <HangHoa> > Get()
{
try
{
HangHoa_DTO mysqlGet = new HangHoa_DTO();
return(mysqlGet.getHangHoa());
}
catch (Exception)
{
throw;
}
}
public List <HangHoa_DTO> SearchHH(string str)
{
List <HangHoa_DTO> HHlist = new List <HangHoa_DTO>();
DataTable data = DataProvider.Instance.ExecuteQuery("EXEC USP_SearchHangHoa @search ", new object[] { str });
foreach (DataRow item in data.Rows)
{
HangHoa_DTO HH = new HangHoa_DTO(item);
HHlist.Add(HH);
}
return(HHlist);
}
public ActionResult <HangHoa> Get(int id)
{
try
{
HangHoa_DTO mysqlGet = new HangHoa_DTO();
return(mysqlGet.getHangHoa(id));
}
catch (Exception)
{
throw;
}
}
public List <HangHoa_DTO> GetDSHH()
{
List <HangHoa_DTO> list = new List <HangHoa_DTO>();
DataTable data = DataProvider.Instance.ExecuteQuery("dbo.USP_GetallHangHoa");
foreach (DataRow item in data.Rows)
{
HangHoa_DTO HH = new HangHoa_DTO(item);
list.Add(HH);
}
return(list);
}
public List <HangHoa_DTO> LoadCusByCateloryID(int id)
{
List <HangHoa_DTO> listHH = new List <HangHoa_DTO>();
string query = "select * from HangHoa where MaDSHH =" + id;
DataTable data = DataProvider.Instace.ExcuteQuey(query);
foreach (DataRow item in data.Rows)
{
HangHoa_DTO hangHoa = new HangHoa_DTO(item);
listHH.Add(hangHoa);
}
return(listHH);
}
public string Put(HangHoa hh)
{
try
{
HangHoa_DTO mysqlGet = new HangHoa_DTO();
return(mysqlGet.editHangHoa(hh) == 0 ? "Không thành công" : "Thành công");
}
catch (Exception ex)
{
return(ex.Message);
throw;
}
}
public string Delete(int id)
{
try
{
HangHoa_DTO mysqlGet = new HangHoa_DTO();
return(mysqlGet.delHangHoa(id) == 0 ? "Không thành công" : "Thành công");
}
catch (Exception ex)
{
return(ex.Message);
throw;
}
}
public bool ThemHangHoa(HangHoa_DTO hh)
{
//kiem tra
SqlConnection conn = ConnectToSQL.HamKetNoi();
string str = "SELECT Id FROM HANGHOA WHERE Id = @Id";
SqlCommand cmd = new SqlCommand(str, conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@Id", SqlDbType.NChar, 10);
cmd.Parameters["@Id"].Value = hh.Id;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
int count = 0;
if (reader.Read())
{
count++;
string s = reader.GetString(0);
}
reader.Close();
if (count == 1)
{
return(false);
}
SqlConnection conn1 = ConnectToSQL.HamKetNoi();
string str1 = "INSERT INTO HANGHOA(Id, Ten, Dongia, Soluong) VALUES (@Id, @Ten, @Dongia, @Soluong)";
SqlCommand cmd1 = new SqlCommand(str1, conn);
cmd1.CommandType = CommandType.Text;
cmd1.Parameters.Add("@Id", SqlDbType.NChar, 10);
cmd1.Parameters.Add("@Ten", SqlDbType.NVarChar, 50);
cmd1.Parameters.Add("@Dongia", SqlDbType.Float);
cmd1.Parameters.Add("@Soluong", SqlDbType.Int);
cmd1.Parameters["@Id"].Value = hh.Id;
cmd1.Parameters["@Ten"].Value = hh.Ten;
cmd1.Parameters["@Dongia"].Value = hh.Dongia;
cmd1.Parameters["@Soluong"].Value = hh.Soluong;
conn1.Open();
cmd1.ExecuteNonQuery();
conn1.Close();
return(true);
}
private void btnThem_Click(object sender, EventArgs e)
{
string id = "";
string ten = "";
float dongia = 0;
int soluong = 0;
//kiem tra
if (txtIDThem.Text == "" || txtTenThem.Text == "" || txtDonGiaThem.Text == "" || txtSoLuongThem.Text == "")
{
MessageBox.Show("Bạn chưa nhập đủ dữ liệu!");
}
//lay du lieu tu form
else
{
id = txtIDThem.Text;
ten = txtTenThem.Text;
dongia = float.Parse(txtDonGiaThem.Text);
soluong = int.Parse(txtSoLuongThem.Text);
//thao tac them
HangHoa_DTO hh = new HangHoa_DTO(id, ten, dongia, soluong);
HangHoa_BUS t = new HangHoa_BUS();
if (!t.ThemHangHoa(hh))
{
MessageBox.Show("Thêm không thành công. Vui lòng thử lại!.");
}
//hien thi lai form
dgvThem.DataSource = t.LoadHangHoa();
//hien thi lai tab ds
dgvDS.DataSource = t.LoadHangHoa();
//hien thi lai tab xoa
dgvXoa.DataSource = t.LoadHangHoa();
bingdingXoa();
//hien thi lai tab sua
dgvSua.DataSource = t.LoadHangHoa();
bingdingSua();
//them vao bao cao chi
DateTime ngay = DateTime.Now.Date;
Baocaochi_DTO bc = new Baocaochi_DTO(ngay, "Hang hoa", float.Parse(txtDonGiaThem.Text) * int.Parse(txtSoLuongThem.Text));
Baocaochi_BUS baocaochi = new Baocaochi_BUS();
baocaochi.ThemBaoCao(bc);
}
}
public bool XoaHangHoa(HangHoa_DTO hh)
{
SqlConnection conn = ConnectToSQL.HamKetNoi();
string str = "DELETE FROM HANGHOA WHERE Id = @Id";
SqlCommand cmd = new SqlCommand(str, conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@Id", System.Data.SqlDbType.NChar, 10);
cmd.Parameters["@Id"].Value = hh.Id;
conn.Open();
int t = cmd.ExecuteNonQuery();
if (t < 1)
{
return(false);
}
conn.Close();
return(true);
}
public bool ThemHangHoa(HangHoa_DTO hh)
{
HangHoa_DAO t = new HangHoa_DAO();
return(t.ThemHangHoa(hh));
}
public bool XoaHangHoa(HangHoa_DTO hh)
{
HangHoa_DAO t = new HangHoa_DAO();
return(t.XoaHangHoa(hh));
}
public bool CapNhatHangHoa(HangHoa_DTO hh)
{
HangHoa_DAO t = new HangHoa_DAO();
return(t.CapNhatHangHoa(hh));
}
public List <HangHoa_DTO> LayDanhSachTheoMa(string maHH)
{
List <HangHoa_DTO> dsHH = new List <HangHoa_DTO>();
// 1. Tạo đối tượng kết nối
SqlConnection conn = DataProvider.TaoKetNoi();
SqlDataReader dr = null;
try
{
// 2. mở kết nối
// 3. tạo đối tượng command
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM [dbo].[HANGHOA] WHERE TRANGTHAI = 1 AND MAHH='" + maHH + "'";
cmd.Connection = conn;
// 4. thực thi cmd và xử lý kết quả
dr = cmd.ExecuteReader();
while (dr.Read())
{
HangHoa_DTO hH = new HangHoa_DTO();
// đọc từng dòng dữ liệu
if (!dr.IsDBNull(0))
{
hH.MaHH = (string)dr[0];
}
if (!dr.IsDBNull(1))
{
hH.TenHH = (string)dr[1];
}
if (!dr.IsDBNull(2))
{
hH.SoLuong = (int)dr[2];
}
if (!dr.IsDBNull(3))
{
hH.DonGiaNhap = long.Parse(dr[3].ToString());
}
if (!dr.IsDBNull(4))
{
hH.DonGiaBan = long.Parse(dr[4].ToString());
}
if (!dr.IsDBNull(5))
{
hH.MaLoaiHH = (string)dr[5];
}
if (!dr.IsDBNull(6))
{
hH.MaNCC = (string)dr[6];
}
if (!dr.IsDBNull(7))
{
dsHH.Add(hH);
}
}
}
finally
{
dr.Close();
// 5. đóng kết nối
conn.Close();
}
return(dsHH);
}
public bool ThemHangHoa(HangHoa_DTO hh)
{
return(hhDAL.ThemHangHoa(hh));
}
public bool SuaHangHoa(HangHoa_DTO hh)
{
return(hhDAL.SuaHangHoa(hh));
}
