public BaseModel <object> DS_ThanhVien_Group(int id_group, [FromBody] QueryRequestParams p) { string Token = lc.GetHeader(Request); LoginData loginData = lc._GetInfoUser(Token); if (loginData == null) { return(JsonResultCommon.DangNhap()); } try { using (DpsConnection cnn = new DpsConnection(_config.ConnectionString)) { Dictionary <string, string> _sortableFields = new Dictionary <string, string> { { "Username", "Username" }, //{ "DonViTinh", "DonViTinh" } }; Panigator v_panigator = null; IDictionary <string, string> v_dic_keyFilter = new Dictionary <string, string> { { "Username", "Username" }, //{ "DonViTinh", "DonViTinh"}, }; string _select = "", _sqlQuery = "", v_str_paginate = "", _orderBy = "", _whereCondition = ""; SqlConditions _cond = new SqlConditions(); #region Filter, sort, paganitor //filter request have to right and enough , same in code if (p.Filter != null) { if (p.Filter.Count > 0) { var listKeySearch = p.Filter.Where(x => !v_dic_keyFilter.ContainsKey(x.Key)).Select(q => q.Key).ToList(); if (listKeySearch != null && listKeySearch.Count > 0) { return(JsonResultCommon.NotData()); } foreach (string _filter in p.Filter.Keys) { if (!string.IsNullOrEmpty(p.Filter[_filter])) { foreach (string vl in p.Filter.Values) { //_whereCondition += " AND " + v_dic_keyFilter[_filter] + " = @" + _filter; _whereCondition += " AND " + v_dic_keyFilter[_filter] + " LIKE'%"; _whereCondition += vl; _whereCondition += "%'"; _cond.Add(_filter, p.Filter[_filter]); } } } } } //sort column in datatable if (p.Sort != null) { if (!string.IsNullOrEmpty(p.Sort.ColumnName) && v_dic_keyFilter.ContainsKey(p.Sort.ColumnName)) { _orderBy = v_dic_keyFilter[p.Sort.ColumnName] + " " + (p.Sort.Direction.ToLower().Equals("asc") ? "asc" : "desc"); } } int page_index = 0; //set up panigator for datatable if (p.Panigator != null) { //v_str_paginate = $@" OFFSET @PageSize * (@PageNumber - 1) ROWS FETCH NEXT @PageSize ROWS ONLY;"; //// offset fetch là các tùy chọn của mệnh đề order by //_cond.Add("PageSize", p.Panigator.PageSize); //_cond.Add("PageNumber", p.Panigator.PageIndex); page_index = p.Panigator.PageIndex; } #endregion _sqlQuery = $@" select ID_GROUP, U.ID_USER, QUYEN_ADMIN , CREATE_DATE, IDNV, UserName, AVATAR from TBL_GROUPMEMBER AS G, TBL_Account AS U WHERE G.ID_USER = U.ID_USER and ID_GROUP=" + id_group + "" + _whereCondition + ""; DataTable _datatable = cnn.CreateDataTable(_sqlQuery, _cond); int _countRows = _datatable.Rows.Count; if (cnn.LastError != null || _datatable == null) { return(JsonResultCommon.NotData()); } if (_datatable.Rows.Count == 0) { return(JsonResultCommon.NotData()); } else { if (page_index == 0) { p.Panigator.PageSize = _countRows; } v_panigator = new Panigator(p.Panigator.PageIndex, p.Panigator.PageSize, _datatable.Rows.Count); } var _data = from r in _datatable.AsEnumerable().Skip((p.Panigator.PageIndex - 1) * p.Panigator.PageSize).Take(p.Panigator.PageSize).ToList() select new { Id_group = r["ID_GROUP"], id_user = r["ID_USER"], id_nv = r["IDNV"], Username = r["UserName"], quyen_group = r["QUYEN_ADMIN"], create_date = r["CREATE_DATE"], hinhanh = r["AVATAR"], Avatar = LiteController.genLinkAvatar(_config.LinkAPI, r["AVATAR"]), }; return(JsonResultCommon.ThanhCong(_data, v_panigator)); } } catch (Exception ex) { return(JsonResultCommon.Exception(ex)); } }
public object getDSBaiDangFlowTrangCaNhan(int id, [FromQuery] QueryParams_New query) { //string Token = lc.GetHeader(Request); //LoginData loginData = lc._GetInfoUser(Token); //if (loginData == null) // return JsonResultCommon.DangNhap(); PageModel pageModel = new PageModel(); string sql = ""; string sql_baidang = ""; SqlConditions Conds = new SqlConditions(); DataTable dt_baidang = new DataTable(); DataTable dt_user = new DataTable(); DataTable dt_like_baidang = new DataTable(); DataTable dt_like_comment = new DataTable(); DataTable dt_comment = new DataTable(); DataTable dt_user_comment = new DataTable(); DataTable dt = new DataTable(); DataTable dt_like = new DataTable(); DataTable dt_comment_parent = new DataTable(); DataTable dt_icon = new DataTable(); DataTable dt_baidang_like_user = new DataTable(); DataTable dt_comment_like_user = new DataTable(); DataTable dt_baidangcanhan = new DataTable(); DataTable dt_kt = new DataTable(); DataTable dt_group = new DataTable(); DataRow[] dr; // DataRow[] dr; try { sql_baidang = @" select ID_BAIDANG,CreatedDate from TBL_BAIDANG where CreatedBy=" + id + "UNION select ID_BAIDANG,CreatedDate from TBL_BaiDang_TRANGCANHAN where ID_USER="******" ORDER BY CreatedDate DESC"; sql = @" select ID_BAIDANG,ID_LOAIBAIDANG,TIEUDE,NOIDUNG_BAIDANG,TYPEPOST,CreatedDate,CreatedBy,UpdateDate,UpdateBy,ID_KHENTHUONG,ID_GROUP from TBL_BAIDANG ORDER BY ID_BAIDANG DESC"; using (DpsConnection cnn = new DpsConnection(_config.ConnectionString)) { //dt = cnn.CreateDataTable(sql, Conds); dt_baidangcanhan = cnn.CreateDataTable(sql_baidang, Conds); Thread.Sleep(500); dt = cnn.CreateDataTable(sql); dt_comment = cnn.CreateDataTable(@"select * from TBL_COMMENT"); dt_like = cnn.CreateDataTable(@"select * from TBL_COMMENT"); //dt_user_baidang = cnn.CreateDataTable(@"select * from TBL_USER"); dt_user = cnn.CreateDataTable(@"select * from TBL_Account"); dt_like_baidang = cnn.CreateDataTable(@" select l.ID_LIKE,likebd.ID_BAIDANG, l.title,count(likebd.ID_LIKE) as tong ,l.LINK_ICON from TBL_BAIDANG_LIKE as likebd ,TBL_LIKE as l where l.ID_LIKE=likebd.ID_LIKE and likebd.COL_DISABLED=0 group by l.title,likebd.ID_BAIDANG,l.ID_LIKE,l.LINK_ICON "); dt_user_comment = cnn.CreateDataTable(@"select * from TBL_Account"); dt_like_comment = cnn.CreateDataTable(@" select top 3 l.ID_LIKE,cmtlike.ID_COMMENT, l.title,count(cmtlike.ID_LIKE) as tong,l.LINK_ICON from TBL_COMMENT_LIKE as cmtlike ,TBL_LIKE as l where l.ID_LIKE=cmtlike.ID_LIKE and cmtlike.COL_DISABLED=0 group by l.title,cmtlike.ID_COMMENT,l.ID_LIKE,l.LINK_ICON order by tong desc "); dt_icon = cnn.CreateDataTable("select * from TBL_LIKE"); dt_baidang_like_user = cnn.CreateDataTable(@" select l.ID_LIKE,likebd.ID_BAIDANG, l.title,l.LINK_ICON,likebd.CREATE_BY,likebd.COL_DISABLED from TBL_BAIDANG_LIKE as likebd ,TBL_LIKE as l where l.ID_LIKE=likebd.ID_LIKE and likebd.COL_DISABLED=0 "); dt_comment_like_user = cnn.CreateDataTable(@" select l.ID_LIKE,like_cmt.ID_COMMENT, l.title,l.LINK_ICON,like_cmt.CREATE_BY from TBL_COMMENT_LIKE as like_cmt ,TBL_LIKE as l where l.ID_LIKE=like_cmt.ID_LIKE and like_cmt.COL_DISABLED=0 "); dt_comment_parent = cnn.CreateDataTable(@" SELECT * FROM TBL_COMMENT WHERE ID_COMMENT_PARENT !=0"); dt_kt = cnn.CreateDataTable(@" select * from TBL_KHENTHUONG "); dt_group = cnn.CreateDataTable(@" select ID_GROUP,TEN_GROUP from TBL_GROUP "); } int total = dt_baidangcanhan.Rows.Count; pageModel.TotalCount = total; pageModel.AllPage = (int)Math.Ceiling(total / (decimal)query.record); pageModel.Size = query.record; pageModel.Page = query.page; if (query.more) { query.page = 1; query.record = pageModel.TotalCount; } if (query.page <= pageModel.AllPage) { dt_baidangcanhan = dt_baidangcanhan.AsEnumerable().Skip((query.page - 1) * query.record).Take(query.record).CopyToDataTable(); } else { return(JsonResultCommon.NotData()); } //lay anh usser //anh usser cmt var data = from p in dt_baidangcanhan.AsEnumerable() select new { Id_baidang_canhan = p["ID_BAIDANG"], DataBaiDang = from r in dt.AsEnumerable() where r["ID_BAIDANG"].ToString().Equals(p["ID_BAIDANG"].ToString()) select new { Id_BaiDang = r["ID_BAIDANG"], Id_LoaiBaiDang = r["ID_LOAIBAIDANG"], title = r["TIEUDE"], NoiDung = r["NOIDUNG_BAIDANG"], CreatedDate = r["CreatedDate"], CreatedBy = r["CreatedBy"], // Id_Group = r["ID_GROUP"], hinhanh = r["TYPEPOST"], image = LiteController.genLinkImage_Post(_config.LinkAPI, r["TYPEPOST"]), //UpdateDate = r["UpdateDate"], //UpdateBy = r["UpdateBy"], /// id_khenthuong = r["ID_KHENTHUONG"] AllowEdit = r["CreatedBy"], // bai dang duoc like Group = from g in dt_group.AsEnumerable() where r["ID_GROUP"].Equals(g["ID_GROUP"]) select new { id_group = g["ID_GROUP"], ten_group = g["TEN_GROUP"], //icon = g["LINK_ICON"], }, KhenThuong = from kt in dt_kt.AsEnumerable() where r["ID_KHENTHUONG"].Equals(kt["ID_KHENTHUONG"]) select new { id_khenthuong = kt["ID_KHENTHUONG"], tieude_kt = kt["TIEUDE"], icon = kt["LINK_ICON"], }, Like = dt_baidang_like_user == null ? null : ( from lr in dt_baidang_like_user.AsEnumerable() where lr["ID_BAIDANG"].Equals(r["ID_BAIDANG"]) && lr["CREATE_BY"].Equals(id) //&& lr["CreatedBy"].Equals(r["CreatedBy"])) select new { CreateBy = lr["CREATE_BY"], ID_like = lr["ID_LIKE"], title = lr["title"], icon = lr["LINK_ICON"], //icon_app = lr["ICON_APP"] }).FirstOrDefault(), //Like = ttt, Like_BaiDang = from like_bd in dt_like_baidang.AsEnumerable() where like_bd["ID_BAIDANG"].ToString().Equals(r["ID_BAIDANG"].ToString()) select new { ID_like = like_bd["ID_LIKE"], title = like_bd["title"], icon = like_bd["LINK_ICON"], //icon_app= like_bd["ICON_APP"], tong = like_bd["tong"], }, User_DangBai = from user in dt_user.AsEnumerable() where user["ID_USER"].ToString().Equals(r["CreatedBy"].ToString()) select new { ID_user = user["ID_USER"], Username = user["UserName"], ID_NV = user["IDNV"], hinhanh = user["AVATAR"], avatar = LiteController.genLinkAvatar(_config.LinkAPI, user["AVATAR"]), }, // Trong bai dang co nhung commet nao Coment = from h in dt_comment.AsEnumerable() where h["ID_BAIDANG"].ToString().Equals(r["ID_BAIDANG"].ToString()) select new { id_cmt = h["ID_COMMENT"], Id_BaiDang = h["ID_BAIDANG"], NoiDung_cmt = h["NOIDUNG_COMMENT"], typepost = h["TYPEPOST"], // hinh_cmt = $"{link}/UploadedFiles/{h["TYPEPOST"]}", CreatedBy = h["CreatedBy_cmt"], CreatedDate = h["CreatedDate_cmt"], id_cmt_parent = h["ID_COMMENT_PARENT"], //Dan_Nhan = h["ID_DANNHAN"], //UpdatedDate = h["UpdatedDate_cmt"], //UpdatedBy = h["UpdatedBy_cmt"], AllowEdit = h["CreatedBy_cmt"], User_comment = from user in dt_user.AsEnumerable() where user["ID_USER"].ToString().Equals(h["CreatedBy_cmt"].ToString()) select new { ID_user = user["ID_USER"], Username = user["UserName"], ID_NV = user["IDNV"], hinhanh = user["AVATAR"], avatar = LiteController.genLinkAvatar(_config.LinkAPI, user["AVATAR"]), }, Like = ( from lr in dt_comment_like_user.AsEnumerable() where lr["ID_COMMENT"].Equals(h["ID_COMMENT"]) && lr["CREATE_BY"].Equals(id ) //&& lr["CreatedBy"].Equals(r["CreatedBy"])) select new { CreateBy = lr["CREATE_BY"], ID_like = lr["ID_LIKE"], title = lr["title"], icon = lr["LINK_ICON"], //icon_app = lr["ICON_APP"], }).FirstOrDefault(), Like_Comment = from like_cmt in dt_like_comment.AsEnumerable() where like_cmt["ID_COMMENT"].ToString().Equals(h["ID_COMMENT"].ToString()) select new { ID_like = like_cmt["ID_LIKE"], title = like_cmt["title"], icon = like_cmt["LINK_ICON"], //icon_app = like_cmt["ICON_APP"], tong = like_cmt["tong"], }, Comment_child = from like_cmt_chill in dt_comment_parent.AsEnumerable() where like_cmt_chill["ID_COMMENT_PARENT"].ToString().Equals(h["ID_COMMENT"].ToString()) select new { id_cmt = like_cmt_chill["ID_COMMENT"], //Id_BaiDang = like_cmt_chill["ID_BAIDANG"], NoiDung_cmt = like_cmt_chill["NOIDUNG_COMMENT"], TypePost = like_cmt_chill["TYPEPOST"], // hinh_cmt = $"{link}/UploadedFiles/{like_cmt_chill["TYPEPOST"]}", CreatedBy = like_cmt_chill["CreatedBy_cmt"], CreatedDate = like_cmt_chill["CreatedDate_cmt"], id_cmt_parent = like_cmt_chill["ID_COMMENT_PARENT"], //Dan_Nhan = h["ID_DANNHAN"], //UpdatedDate_cmt = like_cmt_chill["UpdatedDate_cmt"], //UpdatedBy_cmt = like_cmt_chill["UpdatedBy_cmt"], AllowEdit = like_cmt_chill["CreatedBy_cmt"], User_comment_child = from user in dt_user.AsEnumerable() where user["ID_USER"].ToString().Equals(like_cmt_chill["CreatedBy_cmt"].ToString()) select new { ID_user = user["ID_USER"], Username = user["UserName"], ID_NV = user["IDNV"], hinhanh = user["AVATAR"], avatar = LiteController.genLinkAvatar(_config.LinkAPI, user["AVATAR"]), }, Like_child = ( from lr in dt_comment_like_user.AsEnumerable() where lr["ID_COMMENT"].Equals(like_cmt_chill["ID_COMMENT"]) && lr["CREATE_BY"].Equals(id ) //&& lr["CreatedBy"].Equals(r["CreatedBy"])) select new { CreateBy = lr["CREATE_BY"], ID_like = lr["ID_LIKE"], title = lr["title"], icon = lr["LINK_ICON"], //icon_app = lr["ICON_APP"], }).FirstOrDefault(), Like_Comment_child = from like_cmt in dt_like_comment.AsEnumerable() where like_cmt["ID_COMMENT"].ToString().Equals(like_cmt_chill["ID_COMMENT"].ToString()) select new { ID_like = like_cmt["ID_LIKE"], title = like_cmt["title"], icon = like_cmt["LINK_ICON"], }, }, }, }, }; //CustemerID = loginData.IDKHDPS, // page = pageModel; return(JsonResultCommon.ThanhCong(data, pageModel)); // ; ; ; } catch (Exception ex) { return(JsonResultCommon.Exception(ex)); } }