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));
}
}
