public static void Delete_KH(KhachHangDTO kh)
{
try
{
HoaDonDTO hd = new HoaDonDTO();
PhieuDatHangDTO pdh = new PhieuDatHangDTO();
List <string> ds = new List <string>();
string delete_kh = string.Format("DELETE FROM db_khach_hang WHERE ma_kh='{0}'", kh.ma_kh);
string delete_hd_in_kh = string.Format("SELECT ma_hd FROM db_hoa_don WHERE ma_kh = '{0}'", kh.ma_kh);
string delete_pdh_in_kh = string.Format("SELECT ma_pdh FROM db_phieu_dat_hang WHERE ma_kh = '{0}'", kh.ma_kh);
ds = Xoa_thong_tin(delete_hd_in_kh);
for (int i = 0; i < ds.Count; i++)
{
hd.ma_hd = ds[0];
ChiTietHoaDonDAO.delete_SPHD(hd);
HoaDonDAO.Delete_HD(hd);
}
ds = Xoa_thong_tin(delete_pdh_in_kh);
for (int i = 0; i < ds.Count; i++)
{
pdh.ma_pdh = ds[0];
ChiTietPhieuDatHangDAO.delete_SPPDH(pdh);
PhieuDatHangDAO.Delete_PDH(pdh);
}
cnn = DataProvider.ConnectData();
DataProvider.Execute(cnn, delete_kh);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public static void Dang_xuat(TaiKhoanDTO tk)
{
cnn = DataProvider.ConnectData();
string select = string.Format("UPDATE db_taikhoan SET trang_thai = false WHERE username='******'", tk.username);
DataProvider.Execute(cnn, select);
cnn.Close();
}
/// <summary>
/// thêm 1 tài khoản vào csdl
/// </summary>
/// <param name="tk"></param>
public static void Insert_TK(TaiKhoanDTO tk)
{
try
{
string Insert = string.Format("INSERT INTO db_taikhoan(username,pass,ma_cv,trang_thai) VALUES ('{0}','{1}','{2}','false');", tk.username, tk.password, tk.ma_cv);
cnn = DataProvider.ConnectData();
DataProvider.Execute(cnn, Insert);
cnn.Close();
}catch (Exception ex)
{
throw ex;
}
}
public static void update_PN(PhieuNhapDTO pn)
{
try
{
cnn = DataProvider.ConnectData();
string update = string.Format("UPDATE db_phieu_nhap SET tong_tien='{1}' WHERE ma_phieu_nhap='{0}';", pn.ma_phieu_nhap, pn.tongtien);
DataProvider.Execute(cnn, update);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// xoa thong tin 1 phieu nhap
/// </summary>
/// <param name="pn"></param>
public static void Delete_PN(PhieuNhapDTO pn)
{
try
{
cnn = DataProvider.ConnectData();
string select = string.Format("delete from db_chi_tiet_pn where ma_phieu_nhap='{0}';delete from db_phieu_nhap where ma_phieu_nhap='{0}';", pn.ma_phieu_nhap);
DataProvider.Execute(cnn, select);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public static void update_hd(HoaDonDTO hd)
{
try
{
cnn = DataProvider.ConnectData();
string update = string.Format("UPDATE db_hoa_don SET tonggiatri = '{1}' WHERE ma_hd='{0}'", hd.ma_hd, hd.tonggiatri);
DataProvider.Execute(cnn, update);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// xóa 1 phiếu đặt hàng
/// </summary>
/// <param name="pdh"></param>
public static void Delete_PDH(PhieuDatHangDTO pdh)
{
try
{
cnn = DataProvider.ConnectData();
string delete = string.Format("DELETE FROM chi_tiet_pdh WHERE ma_pdh = '{0}';DELETE FROM db_phieu_dat_hang WHERE ma_pdh = '{0}';", pdh.ma_pdh);
DataProvider.Execute(cnn, delete);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public static void Update_KH(KhachHangDTO kh)
{
try
{
cnn = DataProvider.ConnectData();
string insert = string.Format("UPDATE db_khach_hang SET ten_kh='{1}',diachi='{2}',sdt='{3}'WHERE ma_kh='{0}';", kh.ma_kh, kh.ten_kh, kh.diachi, kh.sdt);
DataProvider.Execute(cnn, insert);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public static void Insert_KH(KhachHangDTO kh)
{
try
{
cnn = DataProvider.ConnectData();
string insert = string.Format("insert into db_khach_hang(ma_kh,ten_kh,diachi,sdt) value ('{0}','{1}', '{2}','{3}');", kh.ma_kh, kh.ten_kh, kh.diachi, kh.sdt);
DataProvider.Execute(cnn, insert);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// them ncc
/// </summary>
/// <param name="ncc"></param>
public static void Insert_NCC(NhaCCDTO ncc)
{
try
{
cnn = DataProvider.ConnectData();
string insert = string.Format("insert into db_nha_cung_cap(ma_ncc,ten_ncc,sdt_ncc,email) values ('{0}','{1}','{2}','{3}')", ncc.ma_ncc, ncc.ten_ncc, ncc.sdt_ncc, ncc.email);
DataProvider.Execute(cnn, insert);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public static void Insert_PDH(PhieuDatHangDTO pdh)
{
try
{
cnn = DataProvider.ConnectData();
string insert = string.Format("insert into db_phieu_dat_hang(ma_pdh,ma_nv,ma_kh,ngay_lap) values ('{0}','{1}','{2}','{3}/{4}/{5}');", pdh.ma_pdh, pdh.ma_nv, pdh.ma_kh, pdh.year, pdh.month, pdh.day);
DataProvider.Execute(cnn, insert);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public static void Delete_LSP(LoaiSanPhamDTO loaisp)
{
try
{
cnn = DataProvider.ConnectData();
string update = string.Format("DELETE FROM db_nhom_sp WHERE ma_loai='{0}';", loaisp.ma_loai, loaisp.ten_loai_sp);
DataProvider.Execute(cnn, update);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// delete NCC
/// </summary>
/// <param name="ncc"></param>
public static void Delete_NCC(NhaCCDTO ncc)
{
try
{
cnn = DataProvider.ConnectData();
string insert = string.Format("DELETE FROM db_nha_cung_cap WHERE ma_ncc='{0}'", ncc.ma_ncc);
DataProvider.Execute(cnn, insert);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// update nha cung cap
/// </summary>
/// <param name="ncc"></param>
public static void Update_NCC(NhaCCDTO ncc)
{
try
{
cnn = DataProvider.ConnectData();
string insert = string.Format("UPDATE db_nha_cung_cap SET ten_ncc='{1}',sdt_ncc='{2}',email='{3}' WHERE ma_ncc='{0}'", ncc.ma_ncc, ncc.ten_ncc, ncc.sdt_ncc, ncc.email);
DataProvider.Execute(cnn, insert);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// sữa thông tin chi tiết hóa đơn
/// </summary>
/// <param name="cthd"></param>
public static void Update_CTHD(ChiTietHoaDonDTO cthd)
{
try
{
cnn = DataProvider.ConnectData();
string update = string.Format(" UPDATE chi_tiet_hd SET ma_sp = '{2}', so_luong='{3}' WHERE ma_hd = '{1}' and id = '{0}'; UPDATE chi_tiet_hd SET thanh_tien='{4}' WHERE id = '{0}'; ", cthd.id, cthd.ma_hd, cthd.ma_sp, cthd.soluong, cthd.thanhtien);
DataProvider.Execute(cnn, update);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public static void Insert_CTPDH(ChiTietPDHDTO ctpdh)
{
try
{
cnn = DataProvider.ConnectData();
string insert = string.Format("insert into chi_tiet_pdh(ma_sp,ma_pdh,soluong,thanhtien) values('{0}','{1}','{2}','{3}');", ctpdh.ma_sp, ctpdh.ma_pdh, ctpdh.soluong, ctpdh.thanhtien);
DataProvider.Execute(cnn, insert);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// delete nhan vien
/// </summary>
/// <param name="nv"></param>
public static void Delete_NV(NhanVienDTO nv)
{
try
{
string delete = string.Format("DELETE FROM db_nhanvien where ma_nv ='{0}'", nv.ma_nv);
cnn = DataProvider.ConnectData();
DataProvider.Execute(cnn, delete);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// them vao csdl
/// </summary>
/// <param name="loaisp"></param>
public static void Insert_LSP(LoaiSanPhamDTO loaisp)
{
try
{
cnn = DataProvider.ConnectData();
string insert = string.Format("insert into db_nhom_sp values ('{0}','{1}');", loaisp.ma_loai, loaisp.ten_loai_sp);
DataProvider.Execute(cnn, insert);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// sửa chi tiết phiếu đặt hàng
/// </summary>
/// <param name="ctpdh"></param>
public static void Update_CTPDH(ChiTietPDHDTO ctpdh)
{
try
{
cnn = DataProvider.ConnectData();
string update = string.Format("UPDATE chi_tiet_pdh SET ma_sp='{1}',soluong={2},thanhtien='{3}' WHERE id = '{0}'", ctpdh.id, ctpdh.ma_sp, ctpdh.soluong, ctpdh.thanhtien);
DataProvider.Execute(cnn, update);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public static void insert_CTPN(ChiTietPNDTO ctpn)
{
try
{
cnn = DataProvider.ConnectData();
string insert = string.Format("insert into db_chi_tiet_pn(ma_sp,ma_phieu_nhap,so_luong,gianhap,tong) values('{0}','{1}','{2}','{3}','{4}');",
ctpn.ma_sp, ctpn.ma_phieu_nhap, ctpn.soluong, ctpn.gianhap, ctpn.tong);
DataProvider.Execute(cnn, insert);
cnn.Close();
}catch (Exception ex)
{
throw ex;
}
}
public static void update_Gia(PhieuDatHangDTO pdh)
{
try
{
cnn = DataProvider.ConnectData();
string update_gia = string.Format("UPDATE db_phieu_dat_hang SET tonggiatri='{0}' WHERE ma_pdh='{1}'", pdh.tonggiatri, pdh.ma_pdh);
DataProvider.Execute(cnn, update_gia);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public static void Delete_HD(HoaDonDTO hd)
{
try
{
cnn = DataProvider.ConnectData();
string delete = string.Format("DELETE FROM chi_tiet_hd WHERE ma_hd = '{0}'; DELETE FROM db_hoa_don WHERE ma_hd = '{0}'; ", hd.ma_hd);
DataProvider.Execute(cnn, delete);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// delete du lieu
/// </summary>
/// <param name="sp"></param>
public static void Delete_SP(SanPhamDTO sp)
{
try
{
cnn = DataProvider.ConnectData();
string insert = string.Format("DELETE FROM `db_sanpham` WHERE ma_sp='{0}' ;", sp.ma_sp);
DataProvider.Execute(cnn, insert);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// thêm danh 1 phiếu nhập
/// </summary>
/// <param name="pn"></param>
public static void insert_PN(PhieuNhapDTO pn)
{
try
{
cnn = DataProvider.ConnectData();
string insert = string.Format("insert into db_phieu_nhap(ma_phieu_nhap,ma_nv,ma_ncc,ngay_lap_pn,tong_tien) values ('{0}','{1}','{2}','{3}/{4}/{5}','{6}');",
pn.ma_phieu_nhap, pn.ma_nv, pn.ma_ncc, pn.year, pn.month, pn.day, pn.tongtien);
DataProvider.Execute(cnn, insert);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public static void insert_CTHD(ChiTietHoaDonDTO cthd)
{
try
{
cnn = DataProvider.ConnectData();
string insert = string.Format("insert into chi_tiet_hd(ma_hd,ma_sp,so_luong,thanh_tien) values ('{0}','{1}','{2}','{3}');",
cthd.ma_hd, cthd.ma_sp, cthd.soluong, cthd.thanhtien);
DataProvider.Execute(cnn, insert);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// update nhan viên
/// </summary>
/// <param name="nv"></param>
public static void Update_NV(NhanVienDTO nv)
{
try
{
string update = string.Format("UPDATE db_nhanvien SET ten_nv='{0}',ngay_sinh='{1}/{2}/{3}',gioi_tinh='{4}',sdt_nv='{5}',dia_chi_nv='{6}',email='{7}',ma_cv='{8}' WHERE ma_nv='{9}'",
nv.ten_nv, nv.year, nv.month, nv.day, nv.gioi_tinh, nv.sdt_nv, nv.dia_chi_nv, nv.email, nv.ma_cv, nv.ma_nv);
cnn = DataProvider.ConnectData();
DataProvider.Execute(cnn, update);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Insert dữ liệu
/// </summary>
/// <param name="nv">1 đối tượng nhân viên trong bảng nhân viên</param>
/// <returns>kết quả thành công hay không</returns>
public static void Insert_NV(NhanVienDTO nv)
{
try
{
string insert = string.Format("insert into db_nhanvien(ma_nv,ten_nv,ngay_sinh,gioi_tinh,sdt_nv,dia_chi_nv,email,ma_cv) values ('{0}','{1}','{2}/{3}/{4}','{5}','{6}','{7}','{8}','{9}');",
nv.ma_nv, nv.ten_nv, nv.year, nv.month, nv.day, nv.gioi_tinh, nv.sdt_nv, nv.dia_chi_nv, nv.email, nv.ma_cv);
cnn = DataProvider.ConnectData();
DataProvider.Execute(cnn, insert);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public static void insert_HD(HoaDonDTO hd)
{
try
{
cnn = DataProvider.ConnectData();
string insert = string.Format("insert into db_hoa_don(ma_hd,ma_nv,ma_kh,ngay_lap,da_thanh_toan,con_lai,tonggiatri,thanh_toan) values ('{0}','{1}','{2}','{3}/{4}/{5}','{6}','{7}','{8}',{9});",
hd.ma_hd, hd.ma_nv, hd.ma_kh, hd.year, hd.month, hd.day, hd.dathanhtoan, hd.conlai, hd.tonggiatri, hd.thanhtoan);
DataProvider.Execute(cnn, insert);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// them vao csdl
/// </summary>
/// <param name="sp"></param>
public static void Insert_SP(SanPhamDTO sp)
{
try
{
string insert = string.Format("insert into db_sanpham(ma_sp,ma_loai,ten_sp,don_vi_tinh,gia_sp,thoi_gian_bh,soluong,hang_san_xuat) values ('{0}','{1}','{2}','{3}',{4},{5},{6},'{7}');",
sp.ma_sp, sp.ma_loai, sp.ten_sp, sp.don_vi_tinh, sp.gia, sp.thoi_gian_bh, sp.soluong, sp.hang_san_xuat);
cnn = DataProvider.ConnectData();
DataProvider.Execute(cnn, insert);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public static void Update_CTPNSP(ChiTietPNDTO ctpn)
{
try
{
cnn = DataProvider.ConnectData();
string update = string.Format("UPDATE db_chi_tiet_pn SET ma_sp='{1}',so_luong='{2}',gianhap='{3}',tong='{4}' WHERE id='{0}'",
ctpn.id, ctpn.ma_sp, ctpn.soluong, ctpn.gianhap, ctpn.tong);
DataProvider.Execute(cnn, update);
cnn.Close();
}
catch (Exception ex)
{
throw ex;
}
}