/// <summary>
/// Sửa nhanh tên
/// </summary>
/// <param name="title"></param>
/// <param name="id"></param>
/// <returns></returns>
public JsonResult UpdateTooltip(string tooltip = "", int id = 0)
{
var stt = false;
var msg = "";
#region Update Tên
var eff = DBLibs.ExecuteNonQuery($@"
UPDATE Images
SET
tooltip = N'{tooltip.Replace("'", "''").Trim()}'
WHERE
id = {id}", _cnn);
if (eff > 0)
{
stt = true;
}
else
{
msg = "Không cập nhật dữ liệu được!";
}
#endregion
return(Json(new
{
status = stt,
message = msg
}));
}
public JsonResult UpdateTenDanhMuc(string tenDm = "", int id = 0)
{
var stt = false;
var msg = "";
#region Update danh muc
var eff = DBLibs.ExecuteNonQuery($@"
UPDATE DanhMucChung
SET
TenDanhMuc = N'{tenDm.Replace("'", "''").Trim()}'
WHERE
id = {id}", _cnn);
if (eff > 0)
{
stt = true;
}
else
{
msg = "Không cập nhật dữ liệu được!";
}
#endregion
return(Json(new
{
status = stt,
message = msg
}));
}
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 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 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 JsonResult Delete(string id = "0", int albumId = 0)
{
var stt = false;
var msg = "";
if (id != "" && id != "0" && int.Parse(id) > 0)
{
string strDelete = $"DELETE dbo.GroupAlbumImg WHERE ImagId = {id}";
var efd = DBLibs.ExecuteNonQuery(strDelete, _cnn);
var sql = $"DELETE dbo.Images WHERE id = {id}";
var eff = DBLibs.ExecuteNonQuery(sql, _cnn);
if (eff > 0)
{
stt = true;
}
else
{
msg = "Không cập nhật dữ liệu được!";
}
}
else
{
msg = "ID rỗng hoặc = 0";
}
return(Json(new
{
status = stt,
message = msg
}, JsonRequestBehavior.AllowGet));
}
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 JsonResult ChangeCols(
int id = 0
)
{
var st1 = false;
var msg = "không thay đổi được";
try {
var sql = DBLibs.ExecuteNonQuery($@"
UPDATE dbo.ConfigColumns SET status = 0
UPDATE dbo.ConfigColumns
SET
status = 1;
WHERE id = {id}", _cnn);
}
catch (Exception ex)
{
msg = ex.Message;
}
return(Json(new
{
status = st1,
mess = msg
}, JsonRequestBehavior.AllowGet));
}
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 JsonResult SaveSort(
string values
)
{
values = values.TrimEnd(';');
string[] arrId = values.Split(';');
foreach (var item in arrId)
{
int id = Convert.ToInt32(item.Split(':')[0]);
int stt = Convert.ToInt32(item.Split(':')[1]);
if (id > 0)
{
var sql = $@"
UPDATE dbo.Reservation
SET
STT = {stt}
WHERE
id = {id}";
var eff = DBLibs.ExecuteNonQuery(sql, _cnn);
}
}
return(Json(new
{
}, JsonRequestBehavior.AllowGet));
}
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)
{
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 JsonResult UpdateDisplayName(string name = "", string id = "")
{
var stt = false;
var msg = "";
#region Update Tiêu đề
var eff = DBLibs.ExecuteNonQuery($@"
UPDATE AspNetUsers
SET
DisplayName = N'{name.Replace("'", "''").Trim()}'
WHERE
id = {id}", _cnn);
if (eff > 0)
{
stt = true;
}
else
{
msg = "Không cập nhật dữ liệu được!";
}
#endregion
return(Json(new
{
status = stt,
message = msg
}));
}
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 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 ActionResult Sua(FormCollection frm, int id)
{
var exited = DBLibs.ExecuteScalar($"SELECT COUNT(*) FROM News WHERE id <> {id} AND (slug = N'{frm["slug"]}' OR title = N'{frm["title"]}')", _cnn);
if (exited != null && int.Parse(exited.ToString()) > 0)
{
return(Json(new
{
status = false,
message = "Đã có bài viết có cùng tiêu đề hoặc url!"
}));
}
var eff = DBLibs.ExecuteNonQuery($@"
UPDATE News
SET
Slug = '{frm["slug"].Trim()}' , -- Slug - varchar(250)
Title = N'{frm["title"].Trim()}' , -- Title - nvarchar(200)
Thumbnail = N'{frm["url"]}' , -- Thumbnail - nvarchar(500)
Summary = N'{frm["noidung"].Trim()}' , -- Summary - nvarchar(1000)
FullContent = N'{frm["sapo"].Replace("'", "''").Trim()}' , -- FullContent - ntext
related_news = N'' , -- related_news - nvarchar(45)
Cates = N'{frm["cates"].Replace("'", "''").Trim()}' , -- Cates - nvarchar(1000)
Tags = N'{frm["tag"].Replace("'", "''").Trim()}' , -- Tags - nvarchar(1000)
Status = 1, -- Status - tinyint
UpdatedAt = {CLibs.DatetimeToTimestampOrgin(DateTime.Now)} , -- UpdatedAt - int
PublishAt = {CLibs.DatetimeToTimestampOrgin(DateTime.Now)} , -- PublishAt - int
UpdatedBy = 0 , -- UpdatedBy - int
meta_keywords = N'{frm["meta_keywords"].Replace("'", "''").Trim()}' , -- meta_keywords - nvarchar(50)
meta_descriptions = N'{frm["meta_descriptions"].Replace("'", "''").Trim()}' -- meta_descriptions - nvarchar(550)
WHERE
id = {id}", _cnn);
return(RedirectToAction("Index"));
}
public ActionResult Themmoi()
{
var dtDsTags = DBLibs.GetDataBy_DataAdapter($@"SELECT * FROM Tags", _cnn);
ViewBag.DsTags = dtDsTags != null ? dtDsTags : new DataTable();
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 static DataTable SelectTableSP(string storeProcedure)
{
DataTable dt = new DataTable();
try
{
return(DBLibs.ExecuteStoreProcedure_Select(storeProcedure, new Hashtable(), Cnn));
//SqlCommand cmd = new SqlCommand(storeProcedure, con);
//cmd.Connection = con;
//if (con.State == ConnectionState.Closed) { con.Open(); }
//cmd.CommandType = CommandType.StoredProcedure;
//cmd.CommandText = storeProcedure;
//SqlDataAdapter da = new SqlDataAdapter(cmd);
//DataSet ds = new DataSet();
//da.Fill(ds);
//dt = ds.Tables[0];
//return dt;
}
catch (Exception)
{
return(dt);
}
finally
{
con.Close();
}
}
public JsonResult UpdateOneCol(int id = 0, string _col_name = "", string _value = "")
{
var stt = false;
var msg = "";
var sql = "";
if (int.TryParse(_value, out int r))
{
sql = $"UPDATE TaiLieu SET {_col_name} = {_value} WHERE id = {id}";
}
else
{
sql = $"UPDATE TaiLieu SET {_col_name} = '{_value.Replace("'", "''")}' WHERE id = {id}";
}
var eff = DBLibs.ExecuteNonQuery(sql, _cnn);
if (eff > 0)
{
stt = true;
}
else
{
msg = "Không cập nhật dữ liệu được!";
}
return(Json(new
{
status = stt,
message = msg
}));
}
public JsonResult ChangeStatus(int id = 0, int _status = 0)
{
var status = false;
var message = "";
if (_status > 1)
{
message = "Status chỉ có thể là 0 hoặc 1";
return(Json(new
{
status,
message
}, JsonRequestBehavior.AllowGet));
}
if (id > 0)
{
var sql = $@"UPDATE dbo.Link SET Status = {_status} WHERE Id = {id}";
var eff = DBLibs.ExecuteNonQuery(sql, _cnn);
if (eff > 0)
{
status = true;
}
}
//ViewBag.Type = Request["t"];
return(Json(new
{
status,
message
}, JsonRequestBehavior.AllowGet));
}
public JsonResult Delete(string id = "0")
{
var stt = false;
var msg = "";
if (id != "" && id != "0" && int.Parse(id) > 0)
{
var sql = $"DELETE dbo.Reservation WHERE id = {id}";
var eff = DBLibs.ExecuteNonQuery(sql, _cnn);
if (eff > 0)
{
stt = true;
}
else
{
msg = "Không xóa dữ liệu được!";
}
}
else
{
msg = "ID rỗng hoặc = 0";
}
return(Json(new
{
status = stt,
message = msg
}, JsonRequestBehavior.AllowGet));
}
// 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 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: Reservation
public ActionResult Index()
{
var dt = DBLibs.GetDataBy_DataAdapter("SELECT * FROM dbo.Reservation ORDER BY STT ASC", _cnn);
ViewBag.dt = dt;
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());
}
public ActionResult Index()
{
var dt = DBLibs.ExecuteStoreProcedure_Select("sp_gianhang_list", new Hashtable(), _cnn);
ViewBag.DsGianHang = dt;
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 Index()
{
var sql = $@"SELECT * FROM dbo.AspNetUsers";
var dtUser = DBLibs.GetDataBy_DataAdapter(sql, _cnn);
ViewBag.User = dtUser;
return(View());
}
