public void Update_Tuyen(DTO.Tuyen tuyen)
{
Provider p = new Provider();
try
{
p.Connect();
string strSql = "sp_UpdateTuyen";
p.ExecuteNonQuery(CommandType.StoredProcedure, strSql,
new SqlParameter {
ParameterName = "@ID", Value = tuyen.ID
},
new SqlParameter {
ParameterName = "@KhoangCach", Value = tuyen.KhoangCach
},
new SqlParameter {
ParameterName = "@ThoiGianChay", Value = tuyen.ThoiGian
},
new SqlParameter {
ParameterName = "@IdTram1", Value = tuyen.IDTram1
},
new SqlParameter {
ParameterName = "@IdTram2", Value = tuyen.IDTram2
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
p.Disconnect();
}
}
public int FindKhoangCach(int id)
{
Provider p = new Provider();
try
{
p.Connect();
string strSql = "sp_FindKMByTuyen";
int flag = 0;
DataTable dt = p.Select(CommandType.StoredProcedure, strSql,
new SqlParameter {
ParameterName = "@ID", Value = id
});
foreach (DataRow row in dt.Rows)
{
flag = Convert.ToInt32(row["KhoangCach"]);
}
return(flag);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
p.Disconnect();
}
}
public int Find_MaTuyenByTram(int ID1, int ID2)
{
Provider p = new Provider();
try
{
p.Connect();
string strSql = "sp_FindTuyenByTram";
int flag = 0;
DataTable dt = p.Select(CommandType.StoredProcedure, strSql,
new SqlParameter {
ParameterName = "@ID1", Value = ID1
},
new SqlParameter {
ParameterName = "@ID2", Value = ID2
}
);
foreach (DataRow row in dt.Rows)
{
flag = Convert.ToInt32(row["ID_Tuyen"]);
}
return(flag);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
p.Disconnect();
}
}
public int Find_MaTuyen()
{
Provider p = new Provider();
try
{
p.Connect();
string strSql = "sp_FillIDTuyen";
int flag = 0;
DataTable dt = p.Select(CommandType.StoredProcedure, strSql);
foreach (DataRow row in dt.Rows)
{
flag = Convert.ToInt32(row["ID_Tuyen"]);
}
return(flag);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
p.Disconnect();
}
}
public int FindOneUser(UsersDTO User)
{
Provider provider = new Provider();
try
{
string strSql = "SELECT * FROM Users WHERE User_kyDanh = @kyDanh and User_matKhau = @matKhau";
provider.Connect();;
DataTable dt = provider.Select(CommandType.Text, strSql,
new SqlParameter {
ParameterName = "@kyDanh", Value = User.User_kyDanh
},
new SqlParameter {
ParameterName = "@matKhau", Value = User.User_matKhau
}
);
return(dt.Rows.Count);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public int FindIdXeByName(string tenXe)
{
Provider p = new Provider();
try
{
p.Connect();
int flag = 0;
string strSql = "sp_FindIdXeyName";
DataTable dt = p.Select(CommandType.StoredProcedure, strSql,
new SqlParameter {
ParameterName = "@TenXe", Value = tenXe
});
foreach (DataRow row in dt.Rows)
{
flag = Convert.ToInt32(row["XeID"]);
}
return(flag);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
p.Disconnect();
}
}
public bool DangNhap(string tenDangNhap, string matKhau)
{
byte[] temp = ASCIIEncoding.ASCII.GetBytes(matKhau);
byte[] hasData = new MD5CryptoServiceProvider().ComputeHash(temp);
string hasPass = "";
foreach (byte item in hasData)
{
hasPass += item;
}
string sql = "sp_Login";
provider.Connect();
DataTable result = provider.Select2(CommandType.StoredProcedure, sql,
new SqlParameter {
ParameterName = "user", Value = tenDangNhap
},
new SqlParameter {
ParameterName = "pass", Value = hasPass
});
provider.DisConnect();
return(result.Rows.Count > 0);
}
public static bool WasExistBookId(string bookId)
{
provider.Connect();
try
{
string cmd = "SELECT * FROM SACH WHERE MaSach = '" + bookId + "'";
var dt = provider.SELECT(System.Data.CommandType.Text, cmd);
return(dt.Rows.Count != 0);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public DataTable LayDanhSachBangCap()
{
try
{
provider.Connect();
string query = "SELECT * FROM BangCap";
DataTable table = provider.SELECT(CommandType.Text, query);
return(table);
}
catch (Exception ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public DataTable LayDSDocGiaNo()
{
DataTable dt = new DataTable();
try
{
pro.Connect();
string strSql = "SELECT pm.MaDG, pt.MaPT, pt.TienNo FROM PHIEUTRASACH pt INNER JOIN PHIEUMUONSACH pm ON pt.MaPM=pm.MaPM WHERE pt.TienNo > 0";
dt = pro.SELECT(CommandType.Text, strSql);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
pro.DisConnect();
}
return(dt);
}
public DataTable MaGhiNhanSachMat()
{
DataTable dt = new DataTable();
try
{
pro.Connect();
string strSql = "SELECT MaGNMatSach FROM MATSACH ORDER BY MaGNMatSach DESC";
dt = pro.SELECT(CommandType.Text, strSql);
return(dt);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
pro.DisConnect();
}
}
public DataTable LayThongTinBaoCaoDocGiaNoTienPhat(string NgayBaoCao)
{
try
{
string strSQL = "SELECT DG.HoTenDG, PTTP.ConLai FROM PHIEUTHUTIENPHAT PTTP INNER JOIN PHIEUTRASACH PTS INNER JOIN PHIEUMUONSACH PMS JOIN DOCGIA DG ON DG.MaDocGia = PMS.MaDG ON PMS.MaPM = PTS.MaPM ON PTS.MaPT = PTTP.MaPT " +
"WHERE PTTP.ConLai > 0 AND PTTP.NgayThu <= @NgayBaoCao";
provider.Connect();
DataTable dtBaoCao = provider.SELECT(CommandType.Text, strSQL,
new SqlParameter {
ParameterName = "@NgayBaoCao", Value = NgayBaoCao
});
return(dtBaoCao);
}
catch (Exception ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public static Results Sua_Menu_1(Menu_DTO Menu_DTO)
{
//try
//{
// string sQuery2 = string.Format("Update MENU set Ten_SP = N'{0}', Loai_SP =N'{1}', DVT = N'{2}', Don_Gia = {3} where Ma_SP = '{4}'", Menu_DTO.TenSP, Menu_DTO.LoaiSP, Menu_DTO.DVT, Menu_DTO.DonGia, Menu_DTO.MaSP);
// sprovider = new Provider();
// var u = sprovider.ExcuteData(sQuery2);
// return true;
// conn.Close();
//}
//catch (Exception Ex)
//{
// return false;
// throw Ex;
//}
Results re = new Results();
try
{
//string sQuery = string.Format("exec proc_UpdateNV @TenNV=N'{0}',@GT='{1}',@DiaChi=N'{2}',@SDT='{3}',@Email='{4}',@Ngay_Sinh ='{5}',@Chuc_Vu=N'{6}',@NVL='{7}',@Luong={8},@MaNV='{9}'", nv.TenNV, nv.GioiTinh, nv.DiaChi, nv.SDT, nv.Email, nv.NgaySinh, nv.ChucVu, nv.NgayVaoLam, nv.Luong, nv.MaNV);
conn = Provider.Connect();
SqlCommand cmd = new SqlCommand("proc_UpdateMENU", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Ma_SP", Menu_DTO.MaSP);
cmd.Parameters.AddWithValue("@Ten_SP", Menu_DTO.TenSP);
cmd.Parameters.AddWithValue("@Loai_SP", Menu_DTO.LoaiSP);
cmd.Parameters.AddWithValue("@DVT", Menu_DTO.DVT);
cmd.Parameters.AddWithValue("@Don_Gia", Menu_DTO.DonGia);
cmd.Parameters.AddWithValue("@resutsID", re.ResultID);
cmd.Parameters.AddWithValue("@Message", re.Message);
cmd.Parameters["@resutsID"].Direction = ParameterDirection.Output;
cmd.Parameters["@Message"].Direction = ParameterDirection.Output;
cmd.Parameters["@Message"].Size = 50;
conn.Open();
var u = cmd.ExecuteNonQuery();
re.ResultID = int.Parse(cmd.Parameters["@resutsID"].Value.ToString());
re.Message = cmd.Parameters["@Message"].Value.ToString();
}
catch (Exception ex)
{
re.ResultID = -1;
re.Message = ex.Message;
}
return(re);
}
public static Results Sua_Ban(Ban_DTO Ban_DTO)
{
//try
//{
// string sQuery2 = string.Format("Update Ban set Ten_Ban = N'{0}', Ma_KV =N'{1}', Tinh_Trang = '{2}', So_TT = {3} where Ma_Ban = '{4}'", Ban_DTO.Tenn_Ban, Ban_DTO.Ma_KV, Ban_DTO.Tinh_Trang, Ban_DTO.So, Ban_DTO.Ma_Ban);
// sprovider = new Provider();
// var u = sprovider.ExcuteData(sQuery2);
// return true;
// conn.Close();
//}
//catch (Exception Ex)
//{
// return false;
//}
Results re = new Results();
try
{
//string sQuery = string.Format("exec proc_UpdateNV @TenNV=N'{0}',@GT='{1}',@DiaChi=N'{2}',@SDT='{3}',@Email='{4}',@Ngay_Sinh ='{5}',@Chuc_Vu=N'{6}',@NVL='{7}',@Luong={8},@MaNV='{9}'", nv.TenNV, nv.GioiTinh, nv.DiaChi, nv.SDT, nv.Email, nv.NgaySinh, nv.ChucVu, nv.NgayVaoLam, nv.Luong, nv.MaNV);
conn = Provider.Connect();
SqlCommand cmd = new SqlCommand("proc_UpdateBan", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Ma_Ban", Ban_DTO.Ma_Ban);
cmd.Parameters.AddWithValue("@Ten_Ban", Ban_DTO.Tenn_Ban);
cmd.Parameters.AddWithValue("@Ma_KV", Ban_DTO.Ma_KV);
cmd.Parameters.AddWithValue("@Tinh_Trang", Ban_DTO.Tinh_Trang);
cmd.Parameters.AddWithValue("@So_TT", Ban_DTO.So);
cmd.Parameters.AddWithValue("@resutsID", re.ResultID);
cmd.Parameters.AddWithValue("@Message", re.Message);
cmd.Parameters["@resutsID"].Direction = ParameterDirection.Output;
cmd.Parameters["@Message"].Direction = ParameterDirection.Output;
cmd.Parameters["@Message"].Size = 50;
conn.Open();
var u = cmd.ExecuteNonQuery();
re.ResultID = int.Parse(cmd.Parameters["@resutsID"].Value.ToString());
re.Message = cmd.Parameters["@Message"].Value.ToString();
}
catch (Exception ex)
{
re.ResultID = -1;
re.Message = ex.Message;
}
return(re);
}
public void RollBAck()
{
Provider p = new Provider();
try
{
p.Connect();
string strSql = "SP_ROOLBACK";
p.ExecuteNonQuery(CommandType.StoredProcedure, strSql);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
p.Disconnect();
}
}
public int updateProduct(SanPham sp)
{
Provider provider = new Provider();
int nRow = 0;
try
{
string SqlQuery = "UPDATE SanPham SET TenSP = @TenSP, GiaTien = @GiaTien, SoLuong = @SoLuong, DonViTinh = @DonViTinh WHERE MaSP = @MaSP";
provider.Connect();
nRow = provider.ExeCuteNonQuery(CommandType.Text, SqlQuery,
new SqlParameter {
ParameterName = "@MaSP", Value = sp.MaSP
},
new SqlParameter {
ParameterName = "@TenSP", Value = sp.TenSP
},
new SqlParameter {
ParameterName = "@GiaTien", Value = sp.GiaTien
},
new SqlParameter {
ParameterName = "@SoLuong", Value = sp.SoLuong
},
new SqlParameter {
ParameterName = "@DonViTinh", Value = sp.DonVi
}
);
if (nRow > 0)
{
return(1);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
return(nRow);
}
public DataTable loadAllUnitProduct()
{
Provider provider = new Provider();
try
{
string strSQL = "SELECT * FROM DonViTinh";
provider.Connect();
DataTable dt = provider.Select(CommandType.Text, strSQL);
return(dt);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public DataTable LayTatCaUser()
{
Provider provider = new Provider();
try
{
string strSql = "SELECT * FROM SanPham";
provider.Connect();;
DataTable dt = provider.Select(CommandType.Text, strSql);
return(dt);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public DataTable Fill_CbbTram()
{
Provider p = new Provider();
try
{
p.Connect();
string strSql = "sp_FillCBB_Tram";
DataTable dt = p.Select(CommandType.StoredProcedure, strSql);
return(dt);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
p.Disconnect();
}
}
public DataTable loadAllProduct()
{
Provider provider = new Provider();
try
{
string strSQL = "SELECT SP.MaSP, SP.TenSP, SP.GiaTien, SP.SoLuong, DV.TenLoai FROM SanPham SP JOIN DonViTinh DV ON SP.DonViTinh = DV.Loai";
provider.Connect();
DataTable dt = provider.Select(CommandType.Text, strSQL);
return(dt);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public static Results Xoa_Menu_1(Menu_DTO Menu_DTO)
{
Results re = new Results();
try
{
conn = Provider.Connect();
SqlCommand cmd = new SqlCommand("proc_XoaMENU", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@psMaSP", Menu_DTO.MaSP);
cmd.Parameters.AddWithValue("@pResultCode", re.ResultID);
cmd.Parameters.AddWithValue("@pResultMessage", re.Message);
cmd.Parameters["@pResultCode"].Direction = ParameterDirection.Output;
cmd.Parameters["@pResultMessage"].Direction = ParameterDirection.Output;
cmd.Parameters["@pResultMessage"].Size = 50;
conn.Open();
var u = cmd.ExecuteNonQuery();
re.ResultID = int.Parse(cmd.Parameters["@pResultCode"].Value.ToString());
re.Message = cmd.Parameters["@pResultMessage"].Value.ToString();
}
catch (Exception ex)
{
re.ResultID = -1;
re.Message = ex.Message;
}
return(re);
//try
//{
// string sQuery2 = string.Format("Delete From MENU where Ma_SP = '{0}'", Menu_DTO.MaSP);
// sprovider = new Provider();
// var u = sprovider.ExcuteData(sQuery2);
// return true;
// conn.Close();
//}
//catch (Exception Ex)
//{
// return false;
// throw Ex;
//}
}
public void UpdateChuyen(DTO.Chuyen chuyen)
{
Provider p = new Provider();
try
{
p.Connect();
string strSql = "sp_UpdateChuyen";
p.ExecuteNonQuery(CommandType.StoredProcedure, strSql,
new SqlParameter {
ParameterName = "@ID", Value = chuyen.ID
},
new SqlParameter {
ParameterName = "@IdTuyen", Value = chuyen.IDTuyen
},
new SqlParameter {
ParameterName = "@NgayKhoiHanh", Value = chuyen.NgayKhoiHanh
},
new SqlParameter {
ParameterName = "@GioKhoiHanh", Value = chuyen.GioKhoiHanh
},
new SqlParameter {
ParameterName = "@GhiChu", Value = chuyen.GhiChi
},
new SqlParameter {
ParameterName = "@IdXe", Value = chuyen.IdXe
},
new SqlParameter {
ParameterName = "@IDTaiXe", Value = chuyen.IDTaiXe
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
p.Disconnect();
}
}
public int addProduct(SanPham sp)
{
Provider provider = new Provider();
int nRow = 0;
try
{
string strSQL = "INSERT INTO SanPham VALUES(@MaSP, @TenSP, @GiaTien, @SoLuong, @DonVi)";
provider.Connect();
nRow = provider.ExeCuteNonQuery(CommandType.Text, strSQL,
new SqlParameter {
ParameterName = "@MaSP", Value = sp.MaSP
},
new SqlParameter {
ParameterName = "@TenSP", Value = sp.TenSP
},
new SqlParameter {
ParameterName = "@GiaTien", Value = sp.GiaTien
},
new SqlParameter {
ParameterName = "@SoLuong", Value = sp.SoLuong
},
new SqlParameter {
ParameterName = "@DonVi", Value = sp.DonVi
}
);
if (nRow > 0)
{
return(1);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
return(nRow);
}
public static Results Delete_NV(NhanVien_DTO nv)
{
//try
//{
// string sQuery = string.Format("exec proc_DeleteNV @MaNV='{0}'", nv.MaNV);
// sprovider = new Provider();
// var u = sprovider.ExcuteData(sQuery);
// return true;
//}
//catch
//{
// return false;
// throw;
//}
Results re = new Results();
try
{
conn = Provider.Connect();
SqlCommand cmd = new SqlCommand("usp_USER_DeleteUser", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@psMaNV", nv.MaNV);
cmd.Parameters.AddWithValue("@pResultCode", re.ResultID);
cmd.Parameters.AddWithValue("@pResultMessage", re.Message);
cmd.Parameters["@pResultCode"].Direction = ParameterDirection.Output;
cmd.Parameters["@pResultMessage"].Direction = ParameterDirection.Output;
cmd.Parameters["@pResultMessage"].Size = 50;
conn.Open();
var u = cmd.ExecuteNonQuery();
re.ResultID = int.Parse(cmd.Parameters["@pResultCode"].Value.ToString());
re.Message = cmd.Parameters["@pResultMessage"].Value.ToString();
}
catch (Exception ex)
{
re.ResultID = -1;
re.Message = ex.Message;
}
return(re);
}
public void XoaQuanLyThanhLySach(string maQuanLyThanhLySach)
{
Provider provider = new Provider();
try
{
provider.Connect();
string query = "DELETE LyDoThanhLy WHERE MaLyDoThanhLy = @MaLyDoThanhLy";
provider.ExecuteNonQuery(CommandType.Text, query,
new SqlParameter {
ParameterName = "@MaLyDoThanhLy", Value = maQuanLyThanhLySach
});
}
catch (Exception ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public void RollBackVeDat(int IDVe)
{
Provider p = new Provider();
try
{
p.Connect();
string strSql = "SP_ROOLBACKVeDat";
p.ExecuteNonQuery(CommandType.StoredProcedure, strSql,
new SqlParameter {
ParameterName = "@IDVe", Value = IDVe
});
}
catch (SqlException ex)
{
throw ex;
}
finally
{
p.Disconnect();
}
}
public void ThanhToan(int idKhach)
{
Provider p = new Provider();
try
{
p.Connect();
string strSql = "sp_ThanhToan";
p.ExecuteNonQuery(CommandType.StoredProcedure, strSql,
new SqlParameter {
ParameterName = "@IdKH", Value = idKhach
});
}
catch (SqlException ex)
{
throw ex;
}
finally
{
p.Disconnect();
}
}
public void XoaVe(int IdChuyen)
{
Provider p = new Provider();
try
{
p.Connect();
string strSql = "sp_XoaVe";
p.ExecuteNonQuery(CommandType.StoredProcedure, strSql,
new SqlParameter {
ParameterName = "@IDChuyen", Value = IdChuyen
});
}
catch (SqlException ex)
{
throw ex;
}
finally
{
p.Disconnect();
}
}
public void ThayTheTam(int IDVe)
{
Provider p = new Provider();
try
{
p.Connect();
string strSql = "sp_ThemTempVe";
p.ExecuteNonQuery(CommandType.StoredProcedure, strSql,
new SqlParameter {
ParameterName = "@IdVe", Value = IDVe
});
}
catch (SqlException ex)
{
throw ex;
}
finally
{
p.Disconnect();
}
}
public static bool checklogin(UserDTO UserDTO)
{
try
{
//conn = new SqlConnection(@"Data Source=NTDPC\SQLEXPRESS;Initial Catalog=Cafe_New_1;Integrated Security=True");
string Query = "usp_USER_CheckUser @psUsername, @psPassword";
conn = Provider.Connect();
conn.Open();
SqlCommand cmd = new SqlCommand(Query, conn);
SqlParameter User = new SqlParameter("@psUsername", SqlDbType.Char);
User.Value = UserDTO.manv;
cmd.Parameters.Add(User);
SqlParameter Pass = new SqlParameter("@psPassword", SqlDbType.Char);
Pass.Value = UserDTO.pass;
cmd.Parameters.Add(Pass);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
UserDTO.chucvu = dt.Rows[i]["Chuc_Vu"].ToString();
}
return(true);
}
catch (Exception ex)
{
//throw;
return(false);
}
finally
{
conn.Close();
}
}