public static void Them(LoaiVeDTO loaive)
{
// mở kết nối
SqlConnection connection = SQLConnectionData.HamKetNoi();
connection.Open();
// tạo câu lệnh Thêm
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
//command.CommandText = "INSERT INTO PHIM VALUES(@MALV,@TENLV,@LOAICHONGOI,@GIA,@MALP,@NOIDUNG,@NAMSX,@NUOCSX,@THOILUONG)";
command.CommandText = "EXEC ThemLoaiVe @MALV,@TENLV,@LOAICHONGOI,@GIA";
command.Parameters.Add("@MALV", SqlDbType.VarChar, 10);
command.Parameters.Add("@TENLV", SqlDbType.NVarChar, 100);
command.Parameters.Add("@LOAICHONGOI", SqlDbType.NVarChar, 100);
command.Parameters.Add("@GIA", SqlDbType.Money);
// gán giá trị
command.Parameters["@MALV"].Value = loaive.MaLV;
command.Parameters["@TENLV"].Value = loaive.TenLV;
command.Parameters["@LOAICHONGOI"].Value = loaive.LoaiChoNgoi;
command.Parameters["@GIA"].Value = loaive.Gia;
command.ExecuteNonQuery();
// đóng kết nối
connection.Close();
}
public static void Them(NguoiDungDTO nguoidung)
{
// mở kết nối
SqlConnection connection = SQLConnectionData.HamKetNoi();
connection.Open();
// tạo câu lệnh Thêm
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
//command.CommandText = "INSERT INTO NHANVIEN VALUES(@MANV,@HOTEN,@CHUCVU,@SDT,@GIOITINH,@NGAYSINH,@DIACHI,@LUONG,@NGAYVL)";
command.CommandText = "EXEC ThemNguoiDung @MAND,@HOTEN,@CHUCVU,@PHANQUYEN";
command.Parameters.Add("@MAND", SqlDbType.VarChar, 10);
command.Parameters.Add("@HOTEN", SqlDbType.NVarChar, 40);
command.Parameters.Add("@CHUCVU", SqlDbType.VarChar, 100);
command.Parameters.Add("@PHANQUYEN", SqlDbType.VarChar, 100);
// gán giá trị
command.Parameters["@MANV"].Value = nguoidung.MaND;
command.Parameters["@HOTEN"].Value = nguoidung.HoTen;
command.Parameters["@CHUCVU"].Value = nguoidung.ChucVu;
command.Parameters["@SDT"].Value = nguoidung.PhanQuyen;
command.ExecuteNonQuery();
// đóng kết nối
connection.Close();
}
public static void Xoa(string macc)
{
SqlConnection connection = SQLConnectionData.HamKetNoi();
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "EXEC XoaCaChieu '" + macc + "'";
command.ExecuteNonQuery();
connection.Close();
}
public static DataTable Loadds()
{
SqlConnection con = SQLConnectionData.KetnoiCSDL();
SqlCommand cmd = new SqlCommand("Loadds", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dtb = new DataTable();
da.Fill(dtb);
return(dtb);
}
public static int Phanquyen(string user, string pass)
{
using (var connection = SQLConnectionData.HamKetNoi())
{
connection.Open();
// using (SqlCommand cmd = new SqlCommand("EXEC Phanquyennhanvien '"+user+"','"+pass+"'", connection))
using (SqlCommand cmd = new SqlCommand("EXEC PhanQuyen '" + user + "','" + pass + "'", connection))
{
cmd.CommandType = CommandType.Text;
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
sda.Fill(ds);
string a = "";
foreach (DataRow row in ds.Tables[0].Rows)
{
//a = row["CHUCVU"].ToString();
a = row["PHANQUYEN"].ToString();
}
if (a == "Quản trị")
{
return(1);
}
// else if (a == "Nhân viên quản lý phim")
else if (a == "Quản lý phim, Quản lý loại phim")
{
return(2);
}
// else if (a == "Nhân viên quản lý phòng chiếu")
else if (a == "Quản lý phòng chiếu")
{
return(3);
}
// else if (a == "Nhân viên quản lý lịch chiếu")
else if (a == "Quản lý lịch chiếu")
{
return(4);
}
// else if (a == "Nhân viên bán vé")
else if (a == "Quản lý loại vé, Quản lý vé")
{
return(5);
}
return(0);
}
}
}
}
public static void Xoa(DateTime ngaychieu, string maphim, string macc, string mapc)
{
SqlConnection connection = SQLConnectionData.HamKetNoi();
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "EXEC XoaLichChieu " + "'" + ngaychieu + "','" + maphim + "','" + macc + "','" + mapc + "'";
command.ExecuteNonQuery();
connection.Close();
}
public static void Xoa(string maphim, string mapc, string malv)
{
SqlConnection connection = SQLConnectionData.HamKetNoi();
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "EXEC XoaVe " + "'" + maphim + "','" + mapc + "','" + malv + "'";
command.ExecuteNonQuery();
connection.Close();
}
public static void Sua(NhanVienDTO nhanvien)
{
// mở kết nối
SqlConnection connection = SQLConnectionData.HamKetNoi();
connection.Open();
// tạo câu lệnh Sửa
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "EXEC Suanhanvien @MANV,@HOTEN,@CHUCVU,@SDT,@GIOITINH,@NGAYSINH,@DIACHI,@LUONG,@NGAYVL";
//command.CommandText = "UPDATE NHANVIEN " +
// "SET MANV=@MANV ," +
// "HOTEN=@HOTEN," +
// "CHUCVU=@CHUCVU," +
// "SDT=@SDT," +
// "GIOITINH=@GIOITINH," +
// "NGAYSINH=@NGAYSINH," +
// "DIACHI=@DIACHI," +
// "LUONG=@LUONG," +
// "NGAYVL=@NGAYVL " +
// "WHERE MANV=@MANV";
command.Parameters.Add("@MANV", SqlDbType.VarChar, 10);
command.Parameters.Add("@HOTEN", SqlDbType.NVarChar, 40);
command.Parameters.Add("@CHUCVU", SqlDbType.NVarChar, 100);
command.Parameters.Add("@SDT", SqlDbType.VarChar, 20);
command.Parameters.Add("@GIOITINH", SqlDbType.NVarChar, 4);
command.Parameters.Add("@NGAYSINH", SqlDbType.DateTime);
command.Parameters.Add("@DIACHI", SqlDbType.NVarChar, 100);
command.Parameters.Add("@LUONG", SqlDbType.Money);
command.Parameters.Add("@NGAYVL", SqlDbType.DateTime);
// gán giá trị
command.Parameters["@MANV"].Value = nhanvien.MaNV;
command.Parameters["@HOTEN"].Value = nhanvien.HoTen;
command.Parameters["@CHUCVU"].Value = nhanvien.ChucVu;
command.Parameters["@SDT"].Value = nhanvien.SDT;
command.Parameters["@GIOITINH"].Value = nhanvien.GioiTinh;
command.Parameters["@NGAYSINH"].Value = nhanvien.NgaySinh;
command.Parameters["@DIACHI"].Value = nhanvien.DiaChi;
command.Parameters["@LUONG"].Value = nhanvien.Luong;
command.Parameters["@NGAYVL"].Value = nhanvien.NgayVL;
// thực hiện câu lệnh
command.ExecuteNonQuery();
// đóng kết nối
connection.Close();
}
public static DataTable Loadds(string _userPrimaryTemp)
{
SqlConnection con = SQLConnectionData.KetnoiCSDL();
SqlCommand cmd = new SqlCommand("Loadds_friend", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@UserPrimary", SqlDbType.NChar, 30);
cmd.Parameters["@UserPrimary"].Value = _userPrimaryTemp;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
}
public static void Delete_notice(string userPrimary, string userReference)
{
SqlConnection con = SQLConnectionData.KetnoiCSDL();
SqlCommand cmd = new SqlCommand("delete_notice", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@userPrimary", SqlDbType.NChar, 30);
cmd.Parameters.Add("@userReference", SqlDbType.NChar, 30);
cmd.Parameters["@userPrimary"].Value = userPrimary;
cmd.Parameters["@userReference"].Value = userReference;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
public static DataTable Kiemtra_dangki(string account, string username)
{
SqlConnection con = SQLConnectionData.KetnoiCSDL();
SqlCommand cmd = new SqlCommand("kiemtra_dangki", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@account", SqlDbType.NChar, 30);
cmd.Parameters.Add("@username", SqlDbType.NChar, 30);
cmd.Parameters["@account"].Value = account;
cmd.Parameters["@username"].Value = username;
SqlDataAdapter ds = new SqlDataAdapter(cmd);
DataTable dtb = new DataTable();
ds.Fill(dtb);
return(dtb);
}
public static DataTable Dangnhap(string account, string password)
{
SqlConnection con = SQLConnectionData.KetnoiCSDL();
SqlCommand cmd = new SqlCommand("dangnhap", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@account", SqlDbType.NChar, 30);
cmd.Parameters.Add("@password", SqlDbType.NChar, 30);
cmd.Parameters["@account"].Value = account;
cmd.Parameters["@password"].Value = password;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dtb = new DataTable();
da.Fill(dtb);
return(dtb);
}
public static void Xoa(string sohd)
{
SqlConnection connection = SQLConnectionData.HamKetNoi();
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "DELETE FROM HOADON " +
"WHERE SOHD=@SOHD";
command.Parameters.Add("@SOHD", SqlDbType.VarChar, 10);
// gán giá trị
command.Parameters["@SOHD"].Value = sohd;
command.ExecuteNonQuery();
connection.Close();
}
public static DataTable LichSuDatVe_LayDSGhe(string makh, string mave)
{
SqlConnection connection = SQLConnectionData.HamKetNoi();
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "EXEC LichSuDatVe_LayDSGhe '" + makh + "','" + mave + "'";
DataTable dataTable = new DataTable();
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataTable);
connection.Close();
return(dataTable);
}
public static DataTable HienLichChieuPhim_NgayChieu_MaCC_MaPC(DateTime ngaychieu, string macc, string mapc)
{
SqlConnection connection = SQLConnectionData.HamKetNoi();
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = " EXEC HienLichChieuPhim_NgayChieu_MaCC_MaPC '" + ngaychieu + "'" + ",'" + macc + "'" + ",'" + mapc + "'";
DataTable dataTable = new DataTable();
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataTable);
connection.Close();
return(dataTable);
}
public static void Xoa(string makh)
{
SqlConnection connection = SQLConnectionData.HamKetNoi();
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "EXEC Xoakhachhang '" + makh + "'";
//command.CommandText = "DELETE KH FROM KHACHHANGTHANTHIET KH " +
// "WHERE KH.MAKH='" + makh + "'";
//command.Parameters.Add("@MANV", SqlDbType.VarChar, 10);
// gán giá trị
//command.Parameters["@MANV"].Value = manv;
command.ExecuteNonQuery();
connection.Close();
}
//Load danh sách sv từ database
public static DataTable LoadDSPC()
{
SqlConnection connection = SQLConnectionData.HamKetNoi();
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "EXEC LoadDSPhongChieu ";
DataTable dataTable = new DataTable();
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataTable);
connection.Close();
return(dataTable);
}
public static void Xoa(string malp)
{
SqlConnection connection = SQLConnectionData.HamKetNoi();
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "EXEC Xoaloaiphim '" + malp + "'";
//command.CommandText = "DELETE NV FROM NHANVIEN NV " +
// "WHERE NV.MANV='" + manv + "'";
//command.Parameters.Add("@MANV", SqlDbType.VarChar, 10);
// gán giá trị
//command.Parameters["@MANV"].Value = manv;
command.ExecuteNonQuery();
connection.Close();
}
public static DataTable LoadDSHoaDon()
{
SqlConnection connection = SQLConnectionData.HamKetNoi();
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "LoadDSVe";
DataTable dataTable = new DataTable();
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataTable);
connection.Close();
return(dataTable);
}
public static DataTable LoadTheLoaiPhim(string maphim)
{
SqlConnection connection = SQLConnectionData.HamKetNoi();
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "EXEC LoadTheLoaiPhim '" + maphim + "'";
command.ExecuteNonQuery();
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
connection.Close();
return(dataTable);
}
public static int Phanquyen(string user, string pass)
{
using (var connection = SQLConnectionData.HamKetNoi())
{
connection.Open();
using (SqlCommand cmd = new SqlCommand("EXEC Phanquyenkhachhang '" + user + "', '" + pass + "'", connection))
{
cmd.CommandType = CommandType.Text;
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
sda.Fill(ds);
string a = "";
foreach (DataRow row in ds.Tables[0].Rows)
{
a = row["LOAIKH"].ToString();
}
/// quanly :1 nhanvien ban hang :2
if (a == "Đồng")
{
return(1);
}
else if (a == "Bạc")
{
return(2);
}
else if (a == "Vàng")
{
return(3);
}
else if (a == "Bạch kim")
{
return(4);
}
else if (a == "Kim cương")
{
return(5);
}
return(0);
}
}
}
}
public static void Sua(KhachHangDTO khachhang)
{
// mở kết nối
SqlConnection connection = SQLConnectionData.HamKetNoi();
connection.Open();
// tạo câu lệnh Sửa
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "EXEC Suakhachhang @MAKH,@HOTEN,@DIACHI,@NGAYSINH,@GIOITINH,@SDT,@LOAIKH,@NGAYDK";
//command.CommandText = "UPDATE KHACHHANGTHANTHIET " +
// "SET MAKH=@MAKH," +
// "HOTEN=@HOTEN," +
// "DIACHI=@DIACHI," +
// "NGAYSINH=@NGAYSINH," +
// "GIOITINH=@GIOITINH," +
// "SDT=@SDT," +
// "LOAIKH=@LOAIKH," +
// "NGAYDK=@NGAYDK" +
// " WHERE MAKH=@MAKH";
command.Parameters.Add("@MAKH", SqlDbType.VarChar, 10);
command.Parameters.Add("@HOTEN", SqlDbType.NVarChar, 40);
command.Parameters.Add("@DIACHI", SqlDbType.NVarChar, 100);
command.Parameters.Add("@NGAYSINH", SqlDbType.Date);
command.Parameters.Add("@GIOITINH", SqlDbType.NVarChar, 4);
command.Parameters.Add("@SDT", SqlDbType.VarChar, 20);
command.Parameters.Add("@LOAIKH", SqlDbType.NVarChar, 100);
command.Parameters.Add("@NGAYDK", SqlDbType.Date);
// gán giá trị
command.Parameters["@MAKH"].Value = khachhang.MaKH;
command.Parameters["@HOTEN"].Value = khachhang.HoTen;
command.Parameters["@DIACHI"].Value = khachhang.DiaChi;
command.Parameters["@NGAYSINH"].Value = khachhang.Ngaysinh;
command.Parameters["@GIOITINH"].Value = khachhang.GioiTinh;
command.Parameters["@SDT"].Value = khachhang.SDT;
command.Parameters["@LOAIKH"].Value = khachhang.LoaiKH;
command.Parameters["@NGAYDK"].Value = khachhang.Ngaydk;
command.ExecuteNonQuery();
// đóng kết nối
connection.Close();
}
public static DataTable LoadDSVe()
{
SqlConnection connection = SQLConnectionData.HamKetNoi();
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
//command.CommandText = "SELECT * " +
// "FROM LOAIVE ORDER BY STT ASC";
command.CommandText = "EXEC LoadDSVe";
DataTable dataTable = new DataTable();
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataTable);
connection.Close();
return(dataTable);
}
public static DataTable Load_message(int count, string userPrimary, string userReferences)
{
SqlConnection con = SQLConnectionData.KetnoiCSDL();
SqlCommand cmd = new SqlCommand("LoadMessage", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@count", SqlDbType.Int);
cmd.Parameters.Add("@userPrimary", SqlDbType.NChar, 30);
cmd.Parameters.Add("@userReferences", SqlDbType.NChar, 30);
cmd.Parameters["@count"].Value = count;
cmd.Parameters["@userPrimary"].Value = userPrimary;
cmd.Parameters["@userReferences"].Value = userReferences;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
}
public static DataTable LoadDSKH()
{
SqlConnection connection = SQLConnectionData.HamKetNoi();
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "SELECT * " +
"FROM KHACHHANGTHANTHIET ORDER BY STT ASC";
DataTable dataTable = new DataTable();
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataTable);
connection.Close();
return(dataTable);
}
public static DataTable DatVe_Phim_NgayChieu(string maphim, string ngaychieu)
{
//string date = ngaychieu.Day.ToString() + '/' + ngaychieu.Month.ToString() + '/' + ngaychieu.Year.ToString();
SqlConnection connection = SQLConnectionData.HamKetNoi();
connection.Open();
SqlCommand command = new SqlCommand("set dateformat dmy\n Exec DatVe_Phim_NgayChieu '" + maphim + "'" + ",'" + ngaychieu + "'", connection);
command.CommandType = CommandType.Text;
DataTable dataTable = new DataTable();
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataTable);
connection.Close();
return(dataTable);
}
public static DataTable DatVe_GetMALC(string maphim, string ngaychieu, string macc)
{
// string date = ngaychieu.Day.ToString() + '/' + ngaychieu.Month.ToString() + '/' + ngaychieu.Year.ToString();
SqlConnection connection = SQLConnectionData.HamKetNoi();
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "set dateformat dmy\n EXEC GetMALC '" + ngaychieu + "'" + ",'" + macc + "'" + ",'" + maphim + "'";
DataTable dataTable = new DataTable();
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataTable);
connection.Close();
return(dataTable);
}
public static DataTable DatVe_LoadDSChoNgoi(string maphim, string ngaychieu, string macc)
{
//string date = ngaychieu.Day.ToString() + '/' + ngaychieu.Month.ToString() + '/' + ngaychieu.Year.ToString();
SqlConnection connection = SQLConnectionData.HamKetNoi();
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "set dateformat dmy\n EXEC Load_DSGhe '" + ngaychieu + "'" + ",'" + macc + "'" + ",'" + maphim + "'";
// exec Load_DSGhe '02/01/2021','CC1','PHIM1'
DataTable dataTable = new DataTable();
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataTable);
connection.Close();
return(dataTable);
}
public static DataTable TimTheoMaKH(string makh)
{
SqlConnection connection = SQLConnectionData.HamKetNoi();
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "EXEC TimKH_theo_MAKH '" + makh + "'";
command.ExecuteNonQuery();
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
connection.Close();
return(dataTable);
}
public static DataTable TimTheoSoLuong(string soluong)
{
SqlConnection connection = SQLConnectionData.HamKetNoi();
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "EXEC Ve_TimTheoSoLuong '" + soluong + "'";
command.ExecuteNonQuery();
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
connection.Close();
return(dataTable);
}
