public static bool CapNhatNV(NhanVienDTO NV)
{
try
{
SqlConnection con = DataProvider.Connection();
SqlCommand cmd = new SqlCommand("sp_CapNhatNV", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@MaNV", SqlDbType.VarChar);
cmd.Parameters.Add("@Hoten", SqlDbType.NVarChar);
cmd.Parameters.Add("@Diachi", SqlDbType.NVarChar);
cmd.Parameters.Add("@CMND", SqlDbType.VarChar);
cmd.Parameters.Add("@Dienthoai", SqlDbType.VarChar);
cmd.Parameters.Add("@Khananglai", SqlDbType.Int);
cmd.Parameters.Add("@Username", SqlDbType.NVarChar);
cmd.Parameters.Add("@MaPQ", SqlDbType.Int);
cmd.Parameters.Add("@MaTo", SqlDbType.Int);
cmd.Parameters["@MaNV"].Value = NV.MaNV;
cmd.Parameters["@Hoten"].Value = NV.HoTen;
cmd.Parameters["@Diachi"].Value = NV.DiaChi;
cmd.Parameters["@CMND"].Value = NV.CMND;
cmd.Parameters["@Dienthoai"].Value = NV.DienThoai;
cmd.Parameters["@Khananglai"].Value = NV.KhaNangLai;
cmd.Parameters["@Username"].Value = NV.Username;
cmd.Parameters["@MaPQ"].Value = NV.MaPQ;
cmd.Parameters["@MaTo"].Value = NV.MaTo;
cmd.ExecuteNonQuery();
con.Close();
return(true);
}
catch
{
return(false);
}
}
public static bool CapNhatChuyenXe(ChuyenXeDTO C)
{
try
{
SqlConnection con = DataProvider.Connection();
SqlCommand cmd = new SqlCommand("sp_CapNhatChuyenXe", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@MaChuyen", SqlDbType.VarChar);
cmd.Parameters.Add("@HangXe", SqlDbType.NVarChar);
cmd.Parameters.Add("@GiaVe", SqlDbType.BigInt);
cmd.Parameters.Add("@MaTuyen", SqlDbType.VarChar);
cmd.Parameters["@MaChuyen"].Value = C.MaChuyen;
cmd.Parameters["@HangXe"].Value = C.HangXe;
cmd.Parameters["@GiaVe"].Value = C.GiaVe;
cmd.Parameters["@MaTuyen"].Value = C.MaTuyen;
cmd.ExecuteNonQuery();
con.Close();
return(true);
}
catch
{
return(false);
}
}
public static bool ThemPG(PhieuGiaoHangDTO PG)
{
try
{
SqlConnection con = DataProvider.Connection();
SqlCommand cmd = new SqlCommand("sp_ThemPG", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@mapg", SqlDbType.VarChar);
cmd.Parameters.Add("@ngaygiao", SqlDbType.Date);
cmd.Parameters.Add("@madh", SqlDbType.VarChar);
cmd.Parameters.Add("@mapgncc", SqlDbType.VarChar);
cmd.Parameters["@mapg"].Value = PG.MaPhieuGiaoHang;
cmd.Parameters["@ngaygiao"].Value = PG.NgayGiao;
cmd.Parameters["@madh"].Value = PG.MaDonDatHang;
cmd.Parameters["@mapgncc"].Value = PG.MaPhieuGiaoHangNhaCC;
cmd.ExecuteNonQuery();
con.Close();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public static bool ThemCTHD(ChiTietHoaDonDTO CTHD)
{
try
{
SqlConnection con = DataProvider.Connection();
SqlCommand cmd = new SqlCommand("sp_ThemCTHD", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@sohd", SqlDbType.NChar);
cmd.Parameters.Add("@masach", SqlDbType.NChar);
cmd.Parameters.Add("@soluongban", SqlDbType.Int);
cmd.Parameters.Add("@giaban", SqlDbType.BigInt);
cmd.Parameters.Add("@thanhtien", SqlDbType.BigInt);
cmd.Parameters["@sohd"].Value = CTHD.SoHD;
cmd.Parameters["@masach"].Value = CTHD.MaSach;
cmd.Parameters["@soluongban"].Value = CTHD.SoLuongBan;
cmd.Parameters["@giaban"].Value = CTHD.GiaBan;
cmd.Parameters["@thanhtien"].Value = CTHD.ThanhTien;
cmd.ExecuteNonQuery();
con.Close();
return(true);
}
catch
{
return(false);
}
}
public static bool ThemND(NguoiDungDTO ND)
{
try
{
SqlConnection con = DataProvider.Connection();
SqlCommand cmd = new SqlCommand("sp_ThemND", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@mand", SqlDbType.NChar);
cmd.Parameters.Add("@hoten", SqlDbType.NVarChar);
cmd.Parameters.Add("@username", SqlDbType.Char);
cmd.Parameters.Add("@pass", SqlDbType.Char);
cmd.Parameters.Add("@diachi", SqlDbType.NVarChar);
cmd.Parameters.Add("@cmnd", SqlDbType.NChar);
cmd.Parameters.Add("@phanquyen", SqlDbType.NChar);
cmd.Parameters.Add("@dienthoai", SqlDbType.NChar);
cmd.Parameters.Add("@gioitinh", SqlDbType.NVarChar);
cmd.Parameters["@mand"].Value = ND.MaND;
cmd.Parameters["@hoten"].Value = ND.HoTen;
cmd.Parameters["@username"].Value = ND.Username;
cmd.Parameters["@pass"].Value = ND.Password;
cmd.Parameters["@diachi"].Value = ND.DiaChi;
cmd.Parameters["@cmnd"].Value = ND.SoCMND;
cmd.Parameters["@phanquyen"].Value = ND.PhanQuyen;
cmd.Parameters["@dienthoai"].Value = ND.DienThoai;
cmd.Parameters["@gioitinh"].Value = ND.GioiTinh;
cmd.ExecuteNonQuery();
con.Close();
return(true);
}
catch
{
return(false);
}
}
public static bool SuaSach(SachDTO S)
{
try
{
SqlConnection con = DataProvider.Connection();
SqlCommand cmd = new SqlCommand("sp_SuaSach", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@masach", SqlDbType.NChar);
cmd.Parameters.Add("@tensach", SqlDbType.NVarChar);
cmd.Parameters.Add("@maTL", SqlDbType.NChar);
cmd.Parameters.Add("@maNXB", SqlDbType.NChar);
cmd.Parameters.Add("@maTG", SqlDbType.NChar);
cmd.Parameters["@masach"].Value = S.MaSach;
cmd.Parameters["@tensach"].Value = S.TenSach;
cmd.Parameters["@maTL"].Value = S.MaTL;
cmd.Parameters["@maNXB"].Value = S.MaNXB;
cmd.Parameters["@maTG"].Value = S.MaTG;
cmd.ExecuteNonQuery();
con.Close();
return(true);
}
catch
{
return(false);
}
}
public static bool ThemLichTrinh(LichTrinhDTO L)
{
try
{
SqlConnection con = DataProvider.Connection();
SqlCommand cmd = new SqlCommand("sp_ThemLichTrinh", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Thang", SqlDbType.Int);
cmd.Parameters.Add("@MaNV", SqlDbType.VarChar);
cmd.Parameters.Add("@GioDi", SqlDbType.DateTime);
cmd.Parameters.Add("@GioDen", SqlDbType.DateTime);
cmd.Parameters.Add("@NoiDi", SqlDbType.NVarChar);
cmd.Parameters.Add("@NoiDen", SqlDbType.NVarChar);
cmd.Parameters.Add("@MaChuyen", SqlDbType.VarChar);
cmd.Parameters["@Thang"].Value = L.Thang;
cmd.Parameters["@MaNV"].Value = L.MaNV;
cmd.Parameters["@GioDi"].Value = L.GioDi;
cmd.Parameters["@GioDen"].Value = L.GioDen;
cmd.Parameters["@NoiDi"].Value = L.NoiDi;
cmd.Parameters["@NoiDen"].Value = L.NoiDen;
cmd.Parameters["@MaChuyen"].Value = L.MaChuyen;
cmd.ExecuteNonQuery();
con.Close();
return(true);
}
catch
{
return(false);
}
}
public static bool ThemHD(HoaDonBanHangDTO HD)
{
try
{
SqlConnection con = DataProvider.Connection();
SqlCommand cmd = new SqlCommand("sp_ThemHD", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@mahd", SqlDbType.VarChar);
cmd.Parameters.Add("@ngaylap", SqlDbType.Date);
cmd.Parameters.Add("@makh", SqlDbType.VarChar);
cmd.Parameters.Add("@manv", SqlDbType.VarChar);
cmd.Parameters.Add("@tongtien", SqlDbType.Int);
cmd.Parameters["@mahd"].Value = HD.MaHoaDonBanHang;
cmd.Parameters["@ngaylap"].Value = HD.NgayLap;
cmd.Parameters["@makh"].Value = HD.MaKhachHang;
cmd.Parameters["@manv"].Value = HD.MaNhanVien;
cmd.Parameters["@tongtien"].Value = HD.TongTien;
cmd.ExecuteNonQuery();
con.Close();
return(true);
}
catch
{
return(false);
}
}
public static DataTable LoadDSNhanVien()
{
SqlConnection con = DataProvider.Connection();
DataTable dt = new DataTable();
string sql = @"Select * From NguoiDung";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(dt);
con.Close();
return(dt);
}
public static DataTable DangNhap(string Username)
{
SqlConnection con = DataProvider.Connection();
DataTable dt = new DataTable();
string sql = @"Select * From NguoiDung Where Username='******'";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(dt);
con.Close();
return(dt);
}
public static DataTable DanhSachTenSachTheoNXB(string maNXB)
{
SqlConnection con = DataProvider.Connection();
DataTable dt = new DataTable();
string sql = @"Select TenSach From Sach Where MaNXB='" + maNXB + "'";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(dt);
con.Close();
return(dt);
}
public static DataTable DanhSachSPTheoMa(string maSP)
{
SqlConnection con = DataProvider.Connection();
DataTable dt = new DataTable();
string sql = @"Select * From SanPham Where MaSanPham='" + maSP + "'";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(dt);
con.Close();
return(dt);
}
public static DataSet ThongKeTonKho()
{
SqlConnection con = DataProvider.Connection();
DataSet ds = new DataSet();
string sql = @"Select MaSach, TenSach,SoLuongTon From Sach";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(ds, "dtTonKho");
con.Close();
return(ds);
}
public static DataTable LoadChuyenXe()
{
SqlConnection con = DataProvider.Connection();
DataTable dt = new DataTable();
string sql = @"Select * From ChuyenXe";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(dt);
con = DataProvider.Disconnection();
return(dt);
}
public static DataTable DanhSachPG()
{
SqlConnection con = DataProvider.Connection();
DataTable dt = new DataTable();
string sql = @"Select * From PhieuGiaoHang";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(dt);
con.Close();
return(dt);
}
public static DataTable DanhSachPN()
{
SqlConnection con = DataProvider.Connection();
DataTable dt = new DataTable();
string sql = @"Select PN.SoPN, PN.NgayNhap, PN.TongTien, NXB.TenNXB From PhieuNhap PN join NhaXxuatBan NXB on PN.MaNXB=NXB.MaNXB";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(dt);
con.Close();
return(dt);
}
public static DataTable DanhSachSPTheoDDH(string maDH)
{
SqlConnection con = DataProvider.Connection();
DataTable dt = new DataTable();
string sql = @"Select S.MaSanPham From (DonDatHang D join ChiTietDonDatHang C on D.MaDonDatHang=C.MaDonDatHang) join SanPham S on C.MaSanPham=S.MaSanPham Where D.MaDonDatHang='" + maDH + "'";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(dt);
con.Close();
return(dt);
}
public static DataTable DanhSachTTPD()
{
SqlConnection con = DataProvider.Connection();
DataTable dt = new DataTable();
string sql = @"Select * From TrangThaiDonDatHang where MaTrangThaiDonDatHang between 1 and 3";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(dt);
con.Close();
return(dt);
}
public static DataTable LoadChuyenXeTheoTuyenDuong(string MaTuyen)
{
SqlConnection con = DataProvider.Connection();
DataTable dt = new DataTable();
string sql = @"Select * From ChuyenXe C join TuyenDuong T on C.MaTuyen=T.MaTuyen Where T.MaTuyen='" + MaTuyen + "'";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(dt);
con = DataProvider.Disconnection();
return(dt);
}
public static DataTable DanhSachNCC()
{
SqlConnection con = DataProvider.Connection();
DataTable dt = new DataTable();
string sql = @"Select *" +
" From NhaCungCap";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(dt);
con.Close();
return(dt);
}
public static DataTable LoadNVTrongTo(int MaTo)
{
SqlConnection con = DataProvider.Connection();
string sql = @"Select * From NhanVien Where MaTo='" + MaTo.ToString() + "'";
SqlCommand cmd = new SqlCommand(sql, con);
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
con = DataProvider.Disconnection();
return(dt);
}
public static DataTable LoadNVTheoKhaNang(long KhoangCach)
{
SqlConnection con = DataProvider.Connection();
string sql = @"Select * From NhanVien Where KhaNangLai >= " + KhoangCach;
SqlCommand cmd = new SqlCommand(sql, con);
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
con = DataProvider.Disconnection();
return(dt);
}
public static DataTable LoadTo()
{
SqlConnection con = DataProvider.Connection();
string sql = @"Select * From Nhom";
SqlCommand cmd = new SqlCommand(sql, con);
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
con = DataProvider.Disconnection();
return(dt);
}
public static DataTable LoadLichTrinh()
{
SqlConnection con = DataProvider.Connection();
DataTable dt = new DataTable();
string sql = @"Select L.MaLich,L.Thang,N.MaNV,L.GioDi,L.GioDen,L.NoiDi,L.NoiDen,C.MaChuyen,T.MaTuyen,T.KhoangCach " +
"From ((NhanVien N join LichTrinh L on N.MaNV=L.MaNV) join ChuyenXe C on L.MaChuyen=C.MaChuyen) join TuyenDuong T on C.MaTuyen=T.MaTuyen ";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(dt);
con = DataProvider.Disconnection();
return(dt);
}
public static DataTable LoadKhachHangTheoMa(string MaKH)
{
SqlConnection con = DataProvider.Connection();
DataTable dt = new DataTable();
string sql = @"select * " +
"from KhachHang " +
"where MaKhachHang ='" + MaKH + "'";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(dt);
con.Close();
return(dt);
}
public static DataTable DanhSachCTPD(string MaPD)
{
SqlConnection con = DataProvider.Connection();
DataTable dt = new DataTable();
string sql = @"Select SP.MaSanPham, SP.TenSanPham, SP.GiaMua, CT.SoLuong " +
"From (DonDatHang D join ChiTietDonDatHang CT on D.MaDonDatHang=CT.MaDonDatHang) join SanPham SP on CT.MaSanPham=SP.MaSanPham " +
"Where D.MaDonDatHang =" + "'" + MaPD + "'";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(dt);
con.Close();
return(dt);
}
public static DataTable DanhSachPD()
{
SqlConnection con = DataProvider.Connection();
DataTable dt = new DataTable();
string sql = @"Select D.MaDonDatHang, D.NgayLap,D.TongTien, T.TenTrangThai" +
" From DonDatHang D join TrangThaiDonDatHang T on D.MaTrangTrangThai = T.MaTrangThaiDonDatHang" +
" Where T.TenTrangThai= N'Chưa xác nhận' or T.TenTrangThai= N'Đã xác nhận'";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(dt);
con.Close();
return(dt);
}
public static DataTable TongSLHangDDH(string MaDDH)
{
SqlConnection con = DataProvider.Connection();
DataTable dt = new DataTable();
string sql = @"Select SUM(C.SoLuong) as 'TongSLHangDDH' " +
"From DonDatHang D join ChiTietDonDatHang C on D.MaDonDatHang=C.MaDonDatHang " +
"Where D.MaDonDatHang =" + "'" + MaDDH + "'";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(dt);
con.Close();
return(dt);
}
public static DataTable DanhSachPDCoTrangThaiDaXacNhanVaChuaNhanHangDu()
{
SqlConnection con = DataProvider.Connection();
DataTable dt = new DataTable();
string sql = @"Select D.MaDonDatHang" +
" From DonDatHang D join TrangThaiDonDatHang T on D.MaTrangTrangThai = T.MaTrangThaiDonDatHang" +
" Where T.TenTrangThai= N'Chưa nhận hàng đủ' or T.TenTrangThai= N'Đã xác nhận'";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(dt);
con.Close();
return(dt);
}
public static DataTable DanhSachDDHTheoMaPG(string MaPG)
{
SqlConnection con = DataProvider.Connection();
DataTable dt = new DataTable();
string sql = @"Select D.MaDonDatHang,D.NgayLap,D.TongTien, D.MaTrangTrangThai " +
"From (DonDatHang D join PhieuGiaoHang P on D.MaDonDatHang=P.MaDonDatHang) join TrangThaiDonDatHang T on D.MaTrangTrangThai=T.MaTrangThaiDonDatHang " +
"Where P.MaPhieuGiaoHang =" + "'" + MaPG + "' and (T.TenTrangThai=N'Chưa nhận hàng đủ' or T.TenTrangThai=N'Đã thanh toán' or T.TenTrangThai= N'Đã xác nhận')";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(dt);
con.Close();
return(dt);
}