//
public static DataTable LoadLichTrinhThang(int Thang)
{
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 " +
"Where L.Thang = " + Thang.ToString();
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(dt);
con = DataProvider.Disconnection();
return(dt);
}
public static DataTable LoadLichTrinhCaNhan(string MaNV)
{
SqlConnection con = DataProvider.Connection();
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand("sp_LichTrinhCN", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@MaNV", SqlDbType.VarChar);
cmd.Parameters["@MaNV"].Value = MaNV;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
con = DataProvider.Disconnection();
return(dt);
}
public static long LayKhoangCach(string MaTuyen)
{
long KQ = 0;
SqlConnection con = DataProvider.Connection();
DataTable dt = new DataTable();
string sql = @"Select KhoangCach From TuyenDuong Where MaTuyen='" + MaTuyen + "'";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
KQ = Int64.Parse(row["KhoangCach"].ToString());
}
con = DataProvider.Disconnection();
return(KQ);
}
public static string LayTenND(string Username)
{
string TenND = "";
SqlConnection con = DataProvider.Connection();
string sql = @"Select HoTen From NhanVien Where Username ='******'";
SqlCommand cmd = new SqlCommand(sql, con);
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
TenND = row["HoTen"].ToString();
}
con = DataProvider.Disconnection();
return(TenND);
}
public static string LayPQ(string Username)
{
string TenPQ = "";
SqlConnection con = DataProvider.Connection();
string sql = @"Select P.TenPQ From NhanVien N join PhanQuyen P on N.MaPQ=P.MaPQ Where N.Username ='******'";
SqlCommand cmd = new SqlCommand(sql, con);
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
TenPQ = row["TenPQ"].ToString();
}
con = DataProvider.Disconnection();
return(TenPQ);
}
public static string LayMatKhau(string Username)
{
string MatKhau = "";
SqlConnection con = DataProvider.Connection();
string sql = @"Select Password From NhanVien Where Username ='******'";
SqlCommand cmd = new SqlCommand(sql, con);
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
MatKhau = row["Password"].ToString();
}
con = DataProvider.Disconnection();
return(MatKhau);
}
public static string LayTinhTrang(string Username)
{
SqlConnection con = DataProvider.Connection();
SqlCommand cmd = new SqlCommand("sp_LayTinhTrang", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@username", SqlDbType.NVarChar);
cmd.Parameters.Add("@tenTT", SqlDbType.NVarChar);
cmd.Parameters["@tenTT"].Direction = ParameterDirection.Output;
cmd.Parameters["@tenTT"].Size = 50;
cmd.Parameters["@username"].Value = Username;
cmd.ExecuteNonQuery();
string TinhTrang = cmd.Parameters["@tenTT"].Value.ToString();
con = DataProvider.Disconnection();
return(TinhTrang);
}
public static NhanVienDTO LoadThongTinTaiXe(string Username)
{
NhanVienDTO NV = new NhanVienDTO();
SqlConnection con = DataProvider.Connection();
string sql = @"Select * From NhanVien Where Username ='******'";
SqlCommand cmd = new SqlCommand(sql, con);
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
NV.MaNV = row["MaNV"].ToString();
NV.HoTen = row["HoTen"].ToString();
NV.DiaChi = row["DiaChi"].ToString();
NV.CMND = row["CMND"].ToString();
NV.DienThoai = row["DienThoai"].ToString();
if (row["KhaNangLai"].ToString() == "")
{
NV.KhaNangLai = 0;
}
else
{
NV.KhaNangLai = Int32.Parse(row["KhaNangLai"].ToString());
}
NV.Username = row["Username"].ToString();
NV.Password = row["Password"].ToString();
if (row["MaTo"].ToString() == "")
{
NV.MaTo = 0;
}
else
{
NV.MaTo = Int32.Parse(row["MaTo"].ToString());
}
}
con = DataProvider.Disconnection();
return(NV);
}
public static bool DangNhap(NhanVienDTO NV)
{
bool KQ = new bool();
SqlConnection con = DataProvider.Connection();
try
{
SqlCommand cmd = new SqlCommand("sp_DangNhap", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Username", SqlDbType.NVarChar);
cmd.Parameters.Add("@Password", SqlDbType.VarChar);
cmd.Parameters.Add("@Trave", SqlDbType.Int);
cmd.Parameters["@Trave"].Direction = ParameterDirection.Output;
cmd.Parameters["@Username"].Value = NV.Username;
cmd.Parameters["@Password"].Value = NV.Password;
cmd.ExecuteNonQuery();
string Trave = cmd.Parameters["@Trave"].Value.ToString();
if (Trave == "0")
{
KQ = false;
}
else if (Trave == "1")
{
KQ = true;
}
}
catch
{
KQ = false;
}
finally
{
con = DataProvider.Disconnection();
}
return(KQ);
}
