public ActionResult Themmoi()
{
var dtDsTags = DBLibs.GetDataBy_DataAdapter($@"SELECT * FROM Tags", _cnn);
ViewBag.DsTags = dtDsTags != null ? dtDsTags : new DataTable();
return(View());
}
public ActionResult Create(int type = 0)
{
ViewBag.Title = "Thêm danh mục ";
var dtLoaiDm = DsLoaiDanhMuc();
foreach (DataRow dr in dtLoaiDm.Rows)
{
if (Request["t"].ToString() == dr["id"].ToString())
{
ViewBag.Title += dr["tieuDe"];
break;
}
}
var model = DanhMucChungServices.Instance.GetListDanhMucChung();
ViewBag.DsLoaiDanhMuc = dtLoaiDm;
//var dt = DsDanhMucCha();
//ViewBag.DsDanhMucCha = dt;
var sql = $@"SELECT * FROM dbo.DanhMucChung Where Type <> 0 AND LoaiDanhMuc=1 ORDER BY TenDanhMuc";
var dtCat = DBLibs.GetDataBy_DataAdapter(sql, _cnn);
ViewBag.DsCat = dtCat;
ViewBag.Images = $"{Server.MapPath(@"\")}UserData\\0\\ImgCat";
return(View(model));
}
public ActionResult Create(int type = 0)
{
#region Lấy ra các danh mục cho vào box chọn
var sql = "SELECT * FROM DanhMucChung WHERE LoaiDanhMuc IN (3,4,5)";
var dtDanhMuc = DBLibs.GetDataBy_DataAdapter(sql, _cnn);
var dtCoQuan = dtDanhMuc.Clone();
var dtLoaiVb = dtDanhMuc.Clone();
var dtLinhVuc = dtDanhMuc.Clone();
foreach (DataRow dr in dtDanhMuc.Rows)
{
if (dr["LoaiDanhMuc"].ToString() == "3")
{
dtCoQuan.ImportRow(dr);
}
else if (dr["LoaiDanhMuc"].ToString() == "4")
{
dtLoaiVb.ImportRow(dr);
}
else if (dr["LoaiDanhMuc"].ToString() == "5")
{
dtLinhVuc.ImportRow(dr);
}
}
ViewBag.DsCoQuanBh = dtCoQuan;
ViewBag.DsLoaiVanBan = dtLoaiVb;
ViewBag.DsLinhVuc = dtLinhVuc;
#endregion
return(View());
}
public ActionResult BaiGiangDaCham()
{
var dtBaiGiang = DBLibs.GetDataBy_DataAdapter($@"
SELECT
dt.TenDotThi,
bg.id,
bg.TenBaiGiang,
bg.Diem1,
bg.NgayChamDiem1,
bg.Diem2,
bg.NgayChamDiem2,
CASE WHEN bg.Diem1 > bg.Diem2 THEN bg.Diem1 - bg.Diem2 ELSE bg.Diem2 - bg.Diem1 END ChenhLech,
gv.TenGiaoVien,
gv1.TenGiaoVien TenGiamKhao1,
gv2.TenGiaoVien TenGiamKhao2,
gv1.id idGiamKhao1,
gv2.id idGiamKhao2
FROM dbo.BaiGiang bg
LEFT OUTER JOIN DotThi dt ON dt.id = bg.idDotThi
LEFT OUTER JOIN GiaoVien gv ON gv.id = bg.idGvienChinh
LEFT OUTER JOIN GiaoVien gv1 ON gv1.id = bg.idGiamKhao1
LEFT OUTER JOIN GiaoVien gv2 ON gv2.id = bg.idGiamKhao2
WHERE
submit_at IS NOT NULL
ORDER BY
CASE WHEN bg.Diem1 > bg.Diem2 THEN bg.Diem1 - bg.Diem2 ELSE bg.Diem2 - bg.Diem1 END DESC,
dt.TenDotThi, bg.submit_at DESC", _cnn);
ViewBag.DsBaiGiang = dtBaiGiang;
return(View());
}
public ActionResult Edit(int id = 0)
{
ViewBag.Title = "Sửa danh mục";
var dtLoaiDm = DsLoaiDanhMuc();
foreach (DataRow dr in dtLoaiDm.Rows)
{
if (id.ToString() == dr["id"].ToString())
{
ViewBag.Title += dr["tieuDe"];
break;
}
}
var sql = $@"SELECT * FROM dbo.DanhMucChung Where Type <> 0 AND LoaiDanhMuc=1 ORDER BY TenDanhMuc";
var dtCat = DBLibs.GetDataBy_DataAdapter(sql, _cnn);
ViewBag.DsCat = dtCat;
ViewBag.DsLoaiDanhMuc = dtLoaiDm;
#region Get item infos
var dtInfos = DBLibs.GetDataBy_DataAdapter($@"SELECT TOP 1 * FROM DanhMucChung WHERE id = {id}", _cnn);
ViewBag.ItemInfos = dtInfos;
#endregion
ViewBag.Type = Request["t"];
ViewBag.dmId = id;
var model = DanhMucChungServices.Instance.GetDanhMucChung(Request["t"].MapInt());
//ViewBag.DsDanhMucCha = DsDanhMucCha();
ViewBag.Images = $"{Server.MapPath(@"\")}UserData\\0\\ImgCat";
return(View(model));
}
public ActionResult Index()
{
var sql = $@"SELECT TOP 8 n.*, t.slug cates_slug FROM dbo.News n LEFT OUTER JOIN Tags t ON n.Cates = t.name WHERE n.Status = 1 AND n.show_athome = 1 ORDER BY n.PublishAt DESC, n.UpdatedAt DESC, n.CreatedAt DESC, n.id DESC";
var dtBaiVietTop = DBLibs.GetDataBy_DataAdapter(sql, _cnn);
var dtBvTop1 = dtBaiVietTop.Copy();
var dtBvTop2 = dtBaiVietTop.Copy();
//if (dtBaiVietTop != null && dtBaiVietTop.Rows.Count > 3)
//{
for (int i = 0; i < dtBaiVietTop.Rows.Count; i++)
{
if (i >= dtBaiVietTop.Rows.Count - 4)
{
dtBvTop2.ImportRow(dtBaiVietTop.Rows[i]);
}
else
{
dtBvTop1.ImportRow(dtBaiVietTop.Rows[i]);
}
}
ViewBag.DsBaiVietTop1 = dtBvTop1;
ViewBag.DsBaiVietTop2 = dtBvTop2; // Chỉ có 3 bài viết
//}
//else
//{
//}
//sql = $@"SELECT * FROM dbo.Tags";
//var dtTags = DBLibs.GetDataBy_DataAdapter(sql, _cnn);
//ViewBag.DsTags = dtTags;
return(View());
}
public ActionResult Edit(int id)
{
#region Get item infos
var dtInfos = DBLibs.GetDataBy_DataAdapter($@"SELECT TOP 1 * FROM BaiGiang WHERE id = {id}", _cnn);
ViewBag.Infos = dtInfos.Rows[0];
#endregion
#region Lấy danh sách các đợt thi còn hạn nộp
var dtDotThi = DBLibs.GetDataBy_DataAdapter($@"SELECT * FROM DotThi WHERE ThoiHanNop > '{DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")}' ORDER BY ThoiHanNop DESC", _cnn);
ViewBag.DotThi = dtDotThi;
#endregion
#region Lay danh sach file bai giang da duoc upload
var pathString = Path.Combine(Server.MapPath(@"\"), _diruploadpath.ToString(), SysBaseInfor.GetIdNguoiDung(), id.ToString());
var dtFiles = new DataTable();
dtFiles.Columns.Add("TenFile");
dtFiles.Columns.Add("FilePath");
if (Directory.Exists(pathString))
{
var dir = new DirectoryInfo(pathString);
FileInfo[] files = dir.GetFiles();
foreach (FileInfo f in files)
{
var dr = dtFiles.NewRow();
dr["TenFile"] = f.Name;
dr["FilePath"] = f.FullName.Replace(Server.MapPath(@"\"), "/");
dtFiles.Rows.Add(dr);
}
}
ViewBag.DsFile = dtFiles;
#endregion
ViewBag.IdDm = id;
return(View());
}
public ActionResult BaiGiangDaNop()
{
#region danh sach giam thi
var dtDsGiaoVien = DBLibs.GetDataBy_DataAdapter($@"
SELECT id, TenGiaoVien text FROM GiaoVien WHERE TrangThai = 9 ORDER BY TenGiaoVien
", _cnn);
ViewBag.DsGiaoVien = CLibs.ConvertDatatableToJson(dtDsGiaoVien);
#endregion
var dtBaiGiang = DBLibs.GetDataBy_DataAdapter($@"
SELECT
bg.*,
dt.TenDotThi,
gv.TenGiaoVien,
gv1.TenGiaoVien TenGiamKhao1,
gv2.TenGiaoVien TenGiamKhao2,
gv1.id idGiamKhao1,
gv2.id idGiamKhao2
FROM dbo.BaiGiang bg
LEFT OUTER JOIN DotThi dt ON dt.id = bg.idDotThi
LEFT OUTER JOIN GiaoVien gv ON gv.id = bg.idGvienChinh
LEFT OUTER JOIN GiaoVien gv1 ON gv1.id = bg.idGiamKhao1
LEFT OUTER JOIN GiaoVien gv2 ON gv2.id = bg.idGiamKhao2
WHERE
submit_at IS NOT NULL
ORDER BY dt.TenDotThi, bg.submit_at DESC", _cnn);
ViewBag.DsBaiGiang = dtBaiGiang;
return(View());
}
public ActionResult Edit(int id)
{
var sql = $@"SELECT * FROM dbo.Tags";
var dtTags = DBLibs.GetDataBy_DataAdapter(sql, _cnn);
ViewBag.DsTags = dtTags;
sql = $@"SELECT id, title FROM News WHERE id <> {id} AND id NOT IN (SELECT related_news FROM News WHERE id = {id}) AND PublishAt BETWEEN {CLibs.DatetimeToTimestampOrgin(DateTime.Now.AddMonths(-1))} AND {CLibs.DatetimeToTimestampOrgin(DateTime.Now)}";
var dtDsBaiViet = DBLibs.GetDataBy_DataAdapter(sql, _cnn);
ViewBag.DsBaiViet = dtDsBaiViet;
sql = $@"SELECT id, title FROM News WHERE id IN (SELECT related_news FROM News WHERE id = {id})";
var dtRelatedNews = DBLibs.GetDataBy_DataAdapter(sql, _cnn);
ViewBag.DsBaiVietLienQuan = dtRelatedNews;
#region Get item infos
var dtInfos = DBLibs.GetDataBy_DataAdapter($@"SELECT TOP 1 * FROM News WHERE id = {id}", _cnn);
ViewBag.Infos = dtInfos.Rows[0];
#endregion
ViewBag.Images = $"{Server.MapPath(@"\")}UserData\\{SysBaseInfor.GetIdNguoiDung()}\\{id}";
ViewBag.Cates = Request["_cates"];
ViewBag.Id = id;
return(View());
}
public ActionResult Items(string name, int status, string fromDate, string toDate)
{
string where = " Where Status=" + status;
if (!string.IsNullOrEmpty(name))
{
where += $" And Name like N'%{name}%'";
}
if (!string.IsNullOrEmpty(fromDate))
{
where += $" And FromDate >= '{fromDate}'";
}
if (!string.IsNullOrEmpty(toDate))
{
where += $" And FromDate <= '{toDate}'";
}
var sql = $@"SELECT * FROM dbo.Reservation {where} Order By STT ASC";
var data = DBLibs.GetDataBy_DataAdapter(sql, _cnn);
ViewBag.Data = data;
return(PartialView("Items"));
}
public static DataTable SelectTable(string sql)
{
DataTable dt = new DataTable();
try
{
return(DBLibs.GetDataBy_DataAdapter(sql, Cnn));
//SqlCommand cmd = new SqlCommand(sql, con);
//cmd.Connection = con;
//SqlDataAdapter da = new SqlDataAdapter(cmd);
//if (con.State == ConnectionState.Closed) { con.Open(); }
//DataSet ds = new DataSet();
//da.Fill(ds);
//dt = ds.Tables[0];
//return dt;
}
catch (Exception)
{
return(dt);
}
finally
{
con.Close();
}
}
public string GetCalendarn()
{
var dt = DBLibs.GetDataBy_DataAdapter("SELECT * FROM dbo.Reservation ORDER BY STT ASC", _cnn);
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
List <Dictionary <string, object> > rows = new List <Dictionary <string, object> >();
Dictionary <string, object> row;
foreach (DataRow dr in dt.Rows)
{
var setTime = dr["TimeSet"];
row = new Dictionary <string, object>();
foreach (DataColumn col in dt.Columns)
{
string dateTime = string.Format("MM/dd/yyyy {0} tt", setTime);
if (col.ColumnName.Equals("FromDate"))
{
row.Add(col.ColumnName, Convert.ToDateTime(dr[col]).ToString(dateTime));
}
else
{
row.Add(col.ColumnName, dr[col]);
}
}
rows.Add(row);
}
return(serializer.Serialize(rows));
}
public ActionResult Index()
{
var dtDotThi = DBLibs.GetDataBy_DataAdapter($@"SELECT * FROM dbo.DotThi ORDER BY ThoiHanNop DESC, ThoiHanCham DESC", _cnn);
ViewBag.DsDotThi = dtDotThi;
return(View());
}
// GET: QuanLyHinhAnh
public ActionResult Index()
{
var sql = $@"SELECT * FROM dbo.News ORDER BY PublishAt DESC, UpdatedAt DESC, CreatedAt DESC, id DESC";
var dtHinhAnh = DBLibs.GetDataBy_DataAdapter(sql, _cnn);
ViewBag.DsHinhAnh = dtHinhAnh;
return(View());
}
// GET: QuanLyLienHe
public ActionResult Index()
{
var sql = $@"SELECT * FROM dbo.Contact WHERE status= 1 ORDER BY Id";
var dtContact = DBLibs.GetDataBy_DataAdapter(sql, _cnn);
ViewBag.DsLienhe = dtContact;
return(View());
}
public ActionResult Edit(int id = 0)
{
var dtInfos = DBLibs.GetDataBy_DataAdapter($@"SELECT TOP 1 Id,Title,Col,Url,Status FROM link WHERE Id = {id} ORDER BY Id ", _cnn);
ViewBag.ItemInfos = dtInfos;
ViewBag.Id = id;
return(View());
}
// GET: QuanLyComment
public ActionResult Index()
{
var sql = $@"SELECT * FROM dbo.Comment ORDER BY Id ";
var data = DBLibs.GetDataBy_DataAdapter(sql, _cnn);
ViewBag.Data = data;
return(View());
}
// GET: Reservation
public ActionResult Index()
{
var dt = DBLibs.GetDataBy_DataAdapter("SELECT * FROM dbo.Reservation ORDER BY STT ASC", _cnn);
ViewBag.dt = dt;
return(View());
}
// GET: Links
public ActionResult Index()
{
var dt = DBLibs.GetDataBy_DataAdapter("SELECT TOP 1000 Id,Title,Col,Url,Status FROM link ORDER BY Id", _cnn);
ViewBag.dsLink = dt;
return(View());
}
public ActionResult Index()
{
var sql = $@"SELECT * FROM dbo.AspNetUsers";
var dtUser = DBLibs.GetDataBy_DataAdapter(sql, _cnn);
ViewBag.User = dtUser;
return(View());
}
public ActionResult EditUser(string id = "")
{
var sql = $@"SELECT TOP 1 * FROM dbo.AspNetUsers Where id = '{id}'";
var dtUser = DBLibs.GetDataBy_DataAdapter(sql, _cnn);
ViewBag.User = dtUser;
return(View());
}
// GET: QuanLyAnh
public ActionResult Index(int type = 1)
{
ViewBag.Type = type;
var sql = $@"SELECT * FROM dbo.Images Where page_kind_appear=" + type;
var dtImages = DBLibs.GetDataBy_DataAdapter(sql, _cnn);
DataTable dt_default = new DataTable();
dt_default.Columns.Add("id");
dt_default.Columns.Add("page_kind_appear");
dt_default.Columns.Add("position_inpage");
dt_default.Columns.Add("order_inpage");
dt_default.Columns.Add("type");
dt_default.Columns.Add("main_content");
dt_default.Columns.Add("alter_content");
dt_default.Columns.Add("url");
dt_default.Columns.Add("tooltip");
dt_default.Columns.Add("display_weekday");
dt_default.Columns.Add("display_time_on");
dt_default.Columns.Add("display_time_off");
dt_default.Columns.Add("created_at");
dt_default.Columns.Add("updated_at");
var dr_default = dt_default.NewRow();
dr_default["id"] = "0";
dr_default["page_kind_appear"] = "0";
dr_default["position_inpage"] = "0";
dr_default["order_inpage"] = "0";
dr_default["type"] = "0";
dr_default["main_content"] = "";
dr_default["alter_content"] = "";
dr_default["url"] = "";
dr_default["tooltip"] = "";
dr_default["display_weekday"] = "";
dr_default["display_time_on"] = "0";
dr_default["display_time_off"] = "0";
dr_default["created_at"] = "0";
dr_default["updated_at"] = "0";
dt_default.Rows.Add(dr_default);
#region tách ảnh nằm ở trang chủ và cột giữa position_inpage = 6 & page_kind_appear in (1,9)
try
{
var dtImage_at_Center_Home = dtImages.AsEnumerable()
.CopyToDataTable();
ViewBag.ImageHomeC = dtImage_at_Center_Home;
}
catch
{
ViewBag.ImageHomeC = dt_default;
}
ViewBag.Images = $"{Server.MapPath(@"\")}UserData\\0\\Images";
#endregion tách ảnh nằm ở trang chủ và cột giữa position_inpage = 6 & page_kind_appear in (1,9)
return(View());
}
private DataTable DsDanhMuc()
{
var where = "";
if (Request["t"] != null && Request["t"] != "")
{
where += "WHERE c.type = " + Request["t"];
}
return(DBLibs.GetDataBy_DataAdapter($@"SELECT c.*, ISNULL(p.name, '') tenCha FROM Tags c LEFT JOIN Tags p ON c.parent_id = p.id {where} ORDER BY c.type, c.col_athome, c.index_athome, c.name", _cnn));
}
private DataTable DsDanhMuc()
{
var where = "";
if (Request["t"] != null && Request["t"] != "")
{
where += "WHERE c.LoaiDanhMuc = " + Request["t"];
}
return(DBLibs.GetDataBy_DataAdapter($@"SELECT c.*, ISNULL(p.TenDanhMuc, '') tenCha FROM DanhMucChung c LEFT JOIN DanhMucChung p ON c.idDanhMucCha = p.id {where} ORDER BY p.SoThuTu, c.LoaiDanhMuc, c.SoThuTu", _cnn));
}
public ActionResult Edit(int id)
{
var sql = $@"SELECT * FROM dbo.Comment WHERE id = {id} ";
var data = DBLibs.GetDataBy_DataAdapter(sql, _cnn);
ViewBag.Data = data.Rows[0];
ViewBag.Id = id;
return(View());
}
public ActionResult Edit(int id = 0, int t = 0)
{
ViewBag.Title = "Sửa Tag ";
var dtLoaiDm = DsLoaiDanhMuc();
foreach (DataRow dr in dtLoaiDm.Rows)
{
if (t.ToString() == dr["id"].ToString())
{
ViewBag.Title += dr["name"];
break;
}
}
ViewBag.DsLoaiDanhMuc = dtLoaiDm;
#region Get item infos
var dtInfos = DBLibs.GetDataBy_DataAdapter($@"SELECT TOP 1 * FROM Tags WHERE id = {id}", _cnn);
ViewBag.Infos = dtInfos.Rows[0];
#endregion
#region Get Top 50 Newest
var sql = $@"
SELECT TOP 50 *
FROM dbo.News
WHERE
Tags = N'{dtInfos.Rows[0]["name"].MapStr().Replace("'", "''")}' OR
Tags LIKE N'%{dtInfos.Rows[0]["name"].MapStr().Replace("'", "''")},%' OR
Tags LIKE N'%,{dtInfos.Rows[0]["name"].MapStr().Replace("'", "''")}%' OR
Tags LIKE N'%,{dtInfos.Rows[0]["name"].MapStr().Replace("'", "''")},%'
ORDER BY UpdatedAt DESC";
ViewBag.Sql = sql;
var dtTopNewest = DBLibs.GetDataBy_DataAdapter(sql, _cnn);
ViewBag.TopNewest = dtTopNewest.Rows;
var dtTopHome = DBLibs.GetDataBy_DataAdapter($@"
SELECT TOP 10 *
FROM dbo.News
WHERE
show_athome = 1 AND
(
Tags = N'{dtInfos.Rows[0]["name"].MapStr().Replace("'", "''")}' OR
Tags LIKE N'%{dtInfos.Rows[0]["name"].MapStr().Replace("'", "''")},%' OR
Tags LIKE N'%,{dtInfos.Rows[0]["name"].MapStr().Replace("'", "''")}%' OR
Tags LIKE N'%,{dtInfos.Rows[0]["name"].MapStr().Replace("'", "''")},%'
)
ORDER BY UpdatedAt DESC", _cnn);
ViewBag.TopHome = dtTopHome.Rows;
#endregion
ViewBag.Type = Request["t"];
ViewBag.Id = id;
ViewBag.DsDanhMucCha = DsDanhMucCha();
return(View());
}
public ActionResult Edit(int id)
{
#region Get item infos
var dtInfos = DBLibs.GetDataBy_DataAdapter($@"SELECT TOP 1 * FROM DotThi WHERE id = {id}", _cnn);
ViewBag.Infos = dtInfos.Rows[0];
#endregion
ViewBag.IdDm = id;
return(View());
}
private DataTable DsLoaiDanhMuc()
{
return(DBLibs.GetDataBy_DataAdapter($@"
SELECT
tt.id, tt.name, COUNT(t.id) used
FROM dbo.TagTypes tt
LEFT OUTER JOIN dbo.Tags t ON tt.id = t.type
GROUP BY
tt.id, tt.name" , _cnn));
}
public ActionResult Edit(int id)
{
#region Get item infos
var dtInfos = DBLibs.GetDataBy_DataAdapter($@"SELECT TOP 1 * FROM TagTypes WHERE id = {id}", _cnn);
ViewBag.Infos = (dtInfos == null) ? new DataTable().Rows : dtInfos.Rows;
#endregion
ViewBag.Id = id;
return(View());
}
// GET: Homc_DuThi
public ActionResult Index(int page = 1, int pageSize = 20)
{
var dt = DBLibs.GetDataBy_DataAdapter(" SELECT TOP 1000 Id, Name, Phone,Email, Fax, City, [File] from dbo.homc_DuThi ORDER BY Id DESC", _cnn);
var dtNews = dt.AsEnumerable();
var dtNewsInfo = dtNews.ToPagedList(page, pageSize);
ViewBag.dsDuthi = dtNewsInfo;
return(View());
}