public static bool SuaBoPhan(BoPhanDTO BoPhanSua)
{
DataProvider p = new DataProvider();
p.Connect();
SqlParameter p0 = new SqlParameter("@MaBoPhan", SqlDbType.Int)
{
Value = BoPhanSua.MaBoPhan
};
SqlParameter p1 = new SqlParameter("@TenBoPhan", SqlDbType.NVarChar, 255)
{
Value = BoPhanSua.TenBoPhan
};
SqlParameter p2 = new SqlParameter("@GhiChu", SqlDbType.NVarChar, 255)
{
Value = BoPhanSua.GhiChu
};
SqlParameter p3 = new SqlParameter("@ConQuanLy", SqlDbType.Bit)
{
Value = BoPhanSua.ConQuanLy
};
int n = p.ExecuteNonQuery("sp_SuaBoPhan", CommandType.StoredProcedure, p0, p1, p2, p3);
p.Disconnect();
if (n == 0)
{
return(false);
}
return(true);
}
public static List <KhoHangDTO> LayDanhSachKhoHang()
{
List <KhoHangDTO> lst = new List <KhoHangDTO>();
DataProvider p = new DataProvider();
p.Connect();
SqlDataReader r = p.GetReader("sp_LayTatCaKhoHang", CommandType.StoredProcedure);
KhoHangDTO kh = null;
while ((r.Read()))
{
kh = new KhoHangDTO();
kh.MaKho = Convert.ToInt32(r[0]);
kh.TenKho = r[1].ToString();
kh.NVQuanLy = Convert.ToInt32(r[2]);
kh.NguoiLienHe = r[3].ToString();
kh.DiaChi = r[4].ToString();
kh.DienThoai = r[5].ToString();
kh.Fax = r[6].ToString();
kh.Email = r[7].ToString();
kh.DienGiai = r[8].ToString();
kh.ConQuanLy = Convert.ToBoolean(r[9]);
lst.Add(kh);
}
p.Disconnect();
return(lst);
}
public static void ThemNhatKy(Log_DTO nk)
{
DataProvider p = new DataProvider();
p.Connect();
SqlParameter[] para = new SqlParameter[] {
new SqlParameter("@tennd", SqlDbType.VarChar, 50)
{
Value = nk.TenND
},
new SqlParameter("@chucnang", SqlDbType.NVarChar, 255)
{
Value = nk.ChucNang
},
new SqlParameter("@hanhdong", SqlDbType.NVarChar, 255)
{
Value = nk.HanhDong
},
new SqlParameter("@thoigian", SqlDbType.VarChar, 50)
{
Value = nk.ThoiGian
}
};
int n = p.ExecuteNonQuery("sp_ThemNhatKy", CommandType.StoredProcedure, para);
p.Disconnect();
}
public static List <BoPhanDTO> LayToanBoBoPhan()
{
List <BoPhanDTO> lst = new List <BoPhanDTO>();
DataProvider p = new DataProvider();
p.Connect();
SqlDataReader r = p.GetReader("sp_LayTatCaBoPhan", CommandType.StoredProcedure);
BoPhanDTO bp;
while (r.Read())
{
bp = new BoPhanDTO();
bp.MaBoPhan = Convert.ToInt32(r[0]);
bp.TenBoPhan = r[1].ToString();
bp.GhiChu = r[2].ToString();
bp.ConQuanLy = Convert.ToBoolean(r[3]);
lst.Add(bp);
}
p.Disconnect();
return(lst);
}
public static List <NhanVienDTO> LayToanBoNhanVien()
{
List <NhanVienDTO> lst = new List <NhanVienDTO>();
DataProvider p = new DataProvider();
p.Connect();
SqlDataReader r = p.GetReader("sp_LayTatCaNhanVien", CommandType.StoredProcedure);
NhanVienDTO nv = null;
while ((r.Read()))
{
nv = new NhanVienDTO();
nv.MaNhanVien = Convert.ToInt32(r[0]);
nv.HoTen = r[1].ToString();
nv.ChucVu = r[2].ToString();
nv.DiaChi = r[3].ToString();
nv.Email = r[4].ToString();
nv.DienThoai = r[5].ToString();
nv.DiDong = r[6].ToString();
// kiểm tra xem có dữ liệu không?
if (r[7].ToString() != null)
{
nv.BoPhan = Convert.ToInt32(r[7]);
}
if (r[8].ToString() != null)
{
nv.QuanLy = Convert.ToInt32(r[8]);
}
nv.ConQuanLy = Convert.ToBoolean(r[9]);
lst.Add(nv);
}
p.Disconnect();
return(lst);
}
public static bool KiemTraDangNhap(string tk, string mk)
{
DataProvider p = new DataProvider();
p.Connect();
SqlParameter p1 = new SqlParameter("@tk", SqlDbType.VarChar, 50)
{
Value = tk
};
SqlParameter p2 = new SqlParameter("@mk", SqlDbType.VarChar, 50)
{
Value = mk
};
SqlParameter p3 = new SqlParameter("@check", SqlDbType.Bit);
p3.Direction = ParameterDirection.Output;
p.ExecuteNonQuery("sp_KiemTraDangNhap", CommandType.StoredProcedure, p1, p2, p3);
bool check = Convert.ToBoolean(p3.Value);
p.Disconnect();
if (check)
{
return(true);
}
else
{
return(false);
}
}
public int ThayDoiMatKhau(string a, string b, string c, string d)
{
DataProvider p = new DataProvider();
SqlConnection cn = null;
// int value = 0;
cn = DataProvider.Connection;
SqlCommand sqlComm = new SqlCommand();
sqlComm.Connection = cn;
sqlComm.CommandText = "ThayDoiMatKhau";
sqlComm.CommandType = CommandType.StoredProcedure;
sqlComm.Parameters.AddWithValue("ten", a);
sqlComm.Parameters.AddWithValue("matkhaucu", b);
sqlComm.Parameters.AddWithValue("matkhaumoi", c);
sqlComm.Parameters.AddWithValue("xacnhanmatkhaumoi", d);
SqlParameter avgPriceParam = new SqlParameter("@a", SqlDbType.Int);
avgPriceParam.Direction = ParameterDirection.Output;
sqlComm.Parameters.Add(avgPriceParam);
p.Connect();
SqlDataReader reader = sqlComm.ExecuteReader();
int avgPrice = int.Parse(avgPriceParam.Value.ToString());
return(avgPrice);
}
public static HangHoaDTO LayThongTinHangHoaCanMuaTheoMa(int MaHangHoa)
{
HangHoaDTO hh = new HangHoaDTO();
DataProvider p = new DataProvider();
p.Connect();
SqlParameter p1 = new SqlParameter("@MaHangHoa", SqlDbType.Int)
{
Value = MaHangHoa
};
SqlDataReader r = p.GetReader("sp_LayThongTinHangHoaCanMuaTheoMa", CommandType.StoredProcedure, p1);
while ((r.Read()))
{
hh.MaHangHoa = Convert.ToInt32(r[0]);
hh.TenHangHoa = r[1].ToString();
hh.DonVi = Convert.ToInt32(r[2]);
hh.GiaMua = float.Parse(r[3].ToString());
}
p.Disconnect();
return(hh);
}
public static List <TaiKhoan_DTO> LayHetNguoiDung()
{
List <TaiKhoan_DTO> lst = new List <TaiKhoan_DTO>();
DataProvider p = new DataProvider();
p.Connect();
SqlDataReader r = p.GetReader("sp_LayHetNguoiDung", CommandType.StoredProcedure);
TaiKhoan_DTO nd = null;
while ((r.Read()))
{
nd = new TaiKhoan_DTO();
nd.MaNguoiDung = Convert.ToInt32(r[0]);
nd.Ten = r[1].ToString();
nd.TaiKhoan = r[2].ToString();
nd.MatKhau = r[3].ToString();
nd.XacNhan = r[4].ToString();
nd.MaVaiTro = Convert.ToInt32(r[5]);
nd.GhiChu = r[6].ToString();
lst.Add(nd);
}
p.Disconnect();
return(lst);
}
public static void SuaNhomHang(NhomHangDTO nh)
{
DataProvider p = new DataProvider();
p.Connect();
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@manh", SqlDbType.Int)
{
Value = nh.MaNhomHang
},
new SqlParameter("@tennh", SqlDbType.NVarChar, 50)
{
Value = nh.TenNhomHang
},
new SqlParameter("@ghichu", SqlDbType.NVarChar, 255)
{
Value = nh.GhiChu
},
new SqlParameter("@conquanly", SqlDbType.Bit)
{
Value = nh.ConQuanLy
}
};
p.ExecuteNonQuery("sp_SuaNhomHang", CommandType.StoredProcedure, para);
p.Disconnect();
}
public static List <HangHoaDTO> _LayTatCaHangHoa()
{
List <HangHoaDTO> lst = new List <HangHoaDTO>();
DataProvider p = new DataProvider();
p.Connect();
SqlDataReader r = p.GetReader("LayTatCaHangHoa", CommandType.StoredProcedure);
HangHoaDTO hh = null;
while ((r.Read()))
{
hh = new HangHoaDTO();
hh.MaHangHoa = Convert.ToInt32(r[0]);
hh.TenHangHoa = r[1].ToString();
hh.LoaiHangHoa = Convert.ToInt32(r[2]);
hh.KhoMacDinh = Convert.ToInt32(r[3]);
hh.PhanLoai = Convert.ToInt32(r[4]);
hh.DonVi = Convert.ToInt32(r[5]);
hh.XuatXu = r[6].ToString();
hh.TonKhoToiThieu = Convert.ToInt32(r[7]);
hh.TonKho = Convert.ToInt32(r[8]);
hh.NhaCungCap = Convert.ToInt32(r[9]);
hh.GiaMua = Convert.ToDouble(r[10]);
hh.GiaBan = Convert.ToDouble(r[11]);
hh.ConQuanLy = Convert.ToBoolean(r[12]);
lst.Add(hh);
}
p.Disconnect();
return(lst);
}
public static int SuaKhuVuc(KhuVucDTO kv)
{
DataProvider p = new DataProvider();
p.Connect();
SqlParameter[] para = new SqlParameter[] {
new SqlParameter("@makv", SqlDbType.Int)
{
Value = kv.MaKhuVuc
},
new SqlParameter("@tenkv", SqlDbType.NVarChar, 255)
{
Value = kv.TenKhuVuc
},
new SqlParameter("@ghichu", SqlDbType.NVarChar, 1000)
{
Value = kv.GhiChu
},
new SqlParameter("@conquanly", SqlDbType.Bit)
{
Value = kv.ConQuanLy
}
};
int n = p.ExecuteNonQuery("sp_SuaKhuVuc", CommandType.StoredProcedure, para);
p.Disconnect();
return(n);
}
public static bool SuaDonVi(DonViTinhDTO dv)
{
DataProvider p = new DataProvider();
p.Connect();
SqlParameter p0 = new SqlParameter("@MaDV", SqlDbType.Int)
{
Value = dv.MaDV
};
SqlParameter p1 = new SqlParameter("@TenDonVi", SqlDbType.NVarChar, 255)
{
Value = dv.TenDonVi
};
SqlParameter p2 = new SqlParameter("@GhiChu", SqlDbType.NVarChar, 255)
{
Value = dv.GhiChu
};
SqlParameter p3 = new SqlParameter("@ConQuanLy", SqlDbType.Bit)
{
Value = dv.ConQuanLy
};
int n = p.ExecuteNonQuery("sp_SuaDonVi", CommandType.StoredProcedure, p0, p1, p2, p3);
p.Disconnect();
if (n == 0)
{
return(false);
}
return(true);
}
public static int SuaTyGia(TyGiaDTO tg)
{
DataProvider p = new DataProvider();
p.Connect();
SqlParameter[] para = new SqlParameter[] {
new SqlParameter("@matg", SqlDbType.Int)
{
Value = tg.MaTyGia
},
new SqlParameter("@tentg", SqlDbType.NVarChar, 50)
{
Value = tg.TenTyGia
},
new SqlParameter("@tygiaquydoi", SqlDbType.Float)
{
Value = tg.TyGiaQuyDoi
},
new SqlParameter("@conquanly", SqlDbType.Bit)
{
Value = tg.ConQuanLy
}
};
int n = p.ExecuteNonQuery("sp_SuaTyGia", CommandType.StoredProcedure, para);
p.Disconnect();
return(n);
}
public static bool SuaNhanVien(NhanVienDTO nv)
{
DataProvider p = new DataProvider();
p.Connect();
SqlParameter p0 = new SqlParameter("@MaNhanVien", SqlDbType.Int)
{
Value = nv.MaNhanVien
};
SqlParameter p1 = new SqlParameter("@HoTen", SqlDbType.NVarChar, 255)
{
Value = nv.HoTen
};
SqlParameter p2 = new SqlParameter("@ChucVu", SqlDbType.NVarChar, 255)
{
Value = nv.ChucVu
};
SqlParameter p3 = new SqlParameter("@DiaChi", SqlDbType.NVarChar, 255)
{
Value = nv.DiaChi
};
SqlParameter p4 = new SqlParameter("@Email", SqlDbType.Char, 255)
{
Value = nv.Email
};
SqlParameter p5 = new SqlParameter("@DienThoai", SqlDbType.Char, 20)
{
Value = nv.DienThoai
};
SqlParameter p6 = new SqlParameter("@DiDong", SqlDbType.NVarChar, 20)
{
Value = nv.DiDong
};
SqlParameter p7 = new SqlParameter("@BoPhan", SqlDbType.Int)
{
Value = nv.BoPhan
};
SqlParameter p8 = new SqlParameter("@QuanLy", SqlDbType.Int)
{
Value = nv.QuanLy
};
SqlParameter p9 = new SqlParameter("@ConQuanLy", SqlDbType.Bit)
{
Value = nv.ConQuanLy
};
int n = p.ExecuteNonQuery("sp_SuaNhanVien", CommandType.StoredProcedure, p0, p1, p2, p3, p4, p5, p6, p7,
p8, p9);
p.Disconnect();
if (n == 0)
{
return(false);
}
return(true);
}
public static DataTable XemTonKho()
{
DataProvider p = new DataProvider();
p.Connect();
DataTable dt = p.SL("sp_XemTonKho", CommandType.StoredProcedure);
p.Disconnect();
return(dt);
}
public static void XoaKhachHang(KhachHangDTO khXoa)
{
DataProvider p = new DataProvider();
p.Connect();
string sql = string.Format("delete KHACHHANG where MaKH = {0}", khXoa.MaKH);
p.ExecuteNonQuery(sql, CommandType.Text);
p.Disconnect();
}
public static void ThemKhachHang(KhachHangDTO kh)
{
DataProvider p = new DataProvider();
p.Connect();
string sql = string.Format("insert KHACHHANG values ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}','{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}','{17}', '{18}')", kh.HoTen, kh.DiaChi, kh.MaSoThue, kh.SoDiDong, kh.SoDienThoaiBan, kh.Email, kh.Fax, kh.Website, kh.SoTaiKhoan,
kh.NganHang, kh.GioiHanNo, kh.ChietKhau, kh.NguoiLienHe, kh.NoHienTai, kh.NickYahoo, kh.LoaiKhachHang, kh.KhuVuc, kh.NickSkype, kh.ConQuanLy);
p.ExecuteNonQuery(sql, CommandType.Text);
p.Disconnect();
}
public static bool ThemKhoHang(KhoHangDTO kh)
{
DataProvider p = new DataProvider();
p.Connect();
SqlParameter p1 = new SqlParameter("@TenKho", SqlDbType.NVarChar, 255)
{
Value = kh.TenKho
};
SqlParameter p2 = new SqlParameter("@NVQuanLy", SqlDbType.Int)
{
Value = kh.NVQuanLy
};
SqlParameter p3 = new SqlParameter("@NguoiLienHe", SqlDbType.NVarChar, 255)
{
Value = kh.NguoiLienHe
};
SqlParameter p4 = new SqlParameter("@DiaChi", SqlDbType.NVarChar, 255)
{
Value = kh.DiaChi
};
SqlParameter p5 = new SqlParameter("@DienThoai", SqlDbType.Char, 20)
{
Value = kh.DienThoai
};
SqlParameter p6 = new SqlParameter("@Fax", SqlDbType.Char, 20)
{
Value = kh.Fax
};
SqlParameter p7 = new SqlParameter("@Email", SqlDbType.Char, 255)
{
Value = kh.Email
};
SqlParameter p8 = new SqlParameter("@DienGiai", SqlDbType.NVarChar, 255)
{
Value = kh.DienGiai
};
SqlParameter p9 = new SqlParameter("@ConQuanLy", SqlDbType.Bit)
{
Value = kh.ConQuanLy
};
int n = p.ExecuteNonQuery("sp_ThemKhoHang", CommandType.StoredProcedure, p1, p2, p3, p4, p5, p6, p7, p8,
p9);
p.Disconnect();
if (n == 0)
{
return(false);
}
return(true);
}
public static void XoaNhomHang(int index)
{
DataProvider p = new DataProvider();
p.Connect();
SqlParameter para = new SqlParameter("@manh", SqlDbType.Int)
{
Value = index
};
p.ExecuteNonQuery("sp_XoaNhomHang", CommandType.StoredProcedure, para);
p.Disconnect();
}
public static void SuaKhachHang(KhachHangDTO kh)
{
DataProvider p = new DataProvider();
p.Connect();
string sql = string.Format("update KHACHHANG set HoTen = '{0}', DiaChi = '{1}', MaSoThue = '{2}', SoDiDong = '{3}', SoDienThoaiBan = '{4}', Email = '{5}', Fax = '{6}', Website = '{7}', SoTaiKhoan = '{8}', NganHang = '{9}', GioiHanNo = '{10}', ChietKhau = '{11}', NguoiLenHe = '{12}', NoHienTai = '{13}', NickYahoo = '{14}', LoaiKhachHang = '{15}', KhuVuc = '{16}', NickSkype = '{17}', ConQuanLy = '{18}' where MaKH = '{19}'", kh.HoTen, kh.DiaChi, kh.MaSoThue, kh.SoDiDong, kh.SoDienThoaiBan, kh.Email, kh.Fax, kh.Website, kh.SoTaiKhoan,
kh.NganHang, kh.GioiHanNo, kh.ChietKhau, kh.NguoiLienHe, kh.NoHienTai, kh.NickYahoo, kh.LoaiKhachHang, kh.KhuVuc, kh.NickSkype, kh.ConQuanLy,
kh.MaKH);
p.ExecuteNonQuery(sql, CommandType.Text);
p.Disconnect();
}
public static void XoaKhoHang(KhoHangDTO khXoa)
{
DataProvider p = new DataProvider();
p.Connect();
p.ExecuteNonQuery("sp_XoaKhoHang", CommandType.StoredProcedure, new SqlParameter("@MaKho", SqlDbType.Int)
{
Value = khXoa.MaKho
});
p.Disconnect();
}
public static int XoaKhuVuc(int makv)
{
DataProvider p = new DataProvider();
p.Connect();
SqlParameter[] para = new SqlParameter[] { new SqlParameter("@makv", SqlDbType.Int)
{
Value = makv
} };
int n = p.ExecuteNonQuery("sp_XoaKhuVuc", CommandType.StoredProcedure, para);
p.Disconnect();
return(n);
}
public static int LayMaKhoMoiNhat()
{
DataProvider p = new DataProvider();
p.Connect();
SqlParameter pMaKho = new SqlParameter("@MaKho", SqlDbType.Int);
pMaKho.Direction = ParameterDirection.Output;
p.ExecuteNonQuery("sp_LayMaKhoMoiNhat", CommandType.StoredProcedure, pMaKho);
p.Disconnect();
return(Convert.ToInt32(pMaKho.Value));
}
public static DataTable XemTonKhoTheoKho(int MaKho)
{
DataProvider p = new DataProvider();
p.Connect();
SqlParameter p1 = new SqlParameter("@MaKho", SqlDbType.Int)
{
Value = MaKho
};
DataTable dt = p.SL("sp_XemTonKhoTheoKho", CommandType.StoredProcedure, p1);
p.Disconnect();
return(dt);
}
public static int XoaNhaCungCap(int mancc)
{
DataProvider p = new DataProvider();
p.Connect();
SqlParameter[] para = new SqlParameter[] { new SqlParameter("@mancc", SqlDbType.Int)
{
Value = mancc
} };
int kq = p.ExecuteNonQuery("sp_XoaNhaCungCap", CommandType.StoredProcedure, para);
p.Disconnect();
return(kq);
}
public static bool XoaTatCa()
{
DataProvider p = new DataProvider();
p.Connect();
int n = p.ExecuteNonQuery("sp_XoaHetNhatKy", CommandType.StoredProcedure);
if (n == 0)
{
p.Disconnect();
return(false);
}
p.Disconnect();
return(true);
}
public static bool XoaNhanVien(NhanVienDTO nv)
{
DataProvider p = new DataProvider();
p.Connect();
SqlParameter p0 = new SqlParameter("@MaNhanVien", SqlDbType.Int)
{
Value = nv.MaNhanVien
};
int n = p.ExecuteNonQuery("sp_XoaNhanVien", CommandType.StoredProcedure, p0);
p.Disconnect();
if (n == 0)
{
return(false);
}
return(true);
}
public static bool XoaDonVi(DonViTinhDTO dv)
{
DataProvider p = new DataProvider();
p.Connect();
SqlParameter p0 = new SqlParameter("@MaDV", SqlDbType.Int)
{
Value = dv.MaDV
};
int n = p.ExecuteNonQuery("sp_XoaDonVi", CommandType.StoredProcedure, p0);
p.Disconnect();
if (n == 0)
{
return(false);
}
return(true);
}
public static bool XoaHangHoa(HangHoaDTO hh)
{
DataProvider p = new DataProvider();
p.Connect();
SqlParameter p0 = new SqlParameter("@MaHangHoa", SqlDbType.Int)
{
Value = hh.MaHangHoa
};
int n = p.ExecuteNonQuery("sp_XoaHangHoa", CommandType.StoredProcedure, p0);
p.Disconnect();
if (n == 0)
{
return(false);
}
return(true);
}