/// <summary>
/// 根据用户姓名,职称获取详情
/// </summary>
/// <param name="name">姓名</param>
/// <param name="positional">职称</param>
/// <param name="pageSize">条数</param>
/// <param name="curPage">页码</param>
/// <returns></returns>
public async Task <DicData> LoadUserByNameAndPositional(string name, string positional, int pageSize, int curPage)
{
DicData dicdata = new DicData();
using (var db = new OperationManagerDbContext())
{
// string sql = @"select u.* from [dbo].[User] u left join SYS_PositionalTitleType p on p.PttID=u.PttID where 1=1 ";
string sql = "SELECT u.UUID,u.SurnameChinese+u.NameChinese AS username,spt.PostTitleTypeName,sc.CollegeName ,uh.UploadIMG FROM dbo.[User] u LEFT JOIN dbo.SYS_PositionalTitleType spt ON u.PttID= spt.PttID LEFT JOIN dbo.SYS_College sc ON sc.CID= u.DeptID LEFT JOIN dbo.UserPersonalHomepage uh ON uh.UserID= u.UUID WHERE 1=1 ";
if (!string.IsNullOrEmpty(name))
{
sql += " and (u.SurnameChinese+u.NameChinese) like '%" + name + "%'";
}
if (!string.IsNullOrEmpty(positional))
{
sql += " and spt.PostTitleTypeName like '%" + positional + "%'";
}
List <UserOutput> list = db.Database.SqlQuery <UserOutput>(sql).Take(pageSize * curPage).Skip(pageSize * (curPage - 1)).ToList();
int count = db.Database.SqlQuery <UserOutput>(sql).Count();
dicdata.Data = list;
dicdata.TotalCount = count;
return(dicdata);
}
}
/// <summary>
/// 按找院系一级菜单查询文献
/// </summary>
/// <param name="departid"></param>
/// <param name="pageSize"></param>
/// <param name="curPage"></param>
/// <returns></returns>
public async Task <DicData> LoadProductionByDepartID(string departid, int pageSize, int curPage)
{
DicData dicdata = new DicData();
using (var db = new OperationManagerDbContext())
{
string sql = @" SELECT * from dbo.StaticProductions sp WHERE sp.ProductionID IN(
SELECT RU.ProductionID FROM dbo.SYS_College sysCollege
LEFT JOIN dbo.[User] U ON U.UnitID=sysCollege.CID
LEFT JOIN dbo.Relation_UserClaimWorks RU ON RU.SysUserID=U.UUID
WHERE U.UnitID='" + departid + "'AND RU.ProductionID IS NOT NULL AND RU.ProductionID<>''GROUP BY RU.ProductionID) ";
int count = db.Database.SqlQuery <StaticProductions>(sql).Count();
List <StaticProductions> list = db.Database.SqlQuery <StaticProductions>(sql).Take(pageSize * curPage).Skip(pageSize * (curPage - 1)).ToList();
dicdata.Data = list;
dicdata.TotalCount = count;
return(dicdata);
}
}
/// <summary>
/// SCI引用TOP100作者
/// </summary>
/// <param name="staryear">开始年份</param>
/// <param name="endyear">结束年份</param>
/// <param name="sciorcscd">sci 或者 cscd类型</param>
/// <param name="departid">机构id</param>
/// <param name="pageSize">条数</param>
/// <param name="curPage">页码</param>
/// <returns></returns>
public async Task <DicData> LoadUserCitationProductionsByContain(string staryear, string endyear, string sciorcscd, string departid, int pageSize, int curPage)
{
using (var db = new OperationManagerDbContext())
{
string sql = @"select top 100 sp.dataset as type,u.UUID as userid, u.SurnameChinese+u.NameChinese as username,COUNT(*)as count from UserCitationProductions uc left join [User] u
on uc.UserID=u.UUID left join StaticProductions sp on uc.ProductionID=sp.ProductionID
where 1=1 " ;
sql += " and year(sp.issued) between '" + staryear + "' and '" + endyear + "' ";
if (!string.IsNullOrEmpty(sciorcscd))
{
sql += " and sp.dataset like '%" + sciorcscd + "%'";
}
sql += " group by u.UUID, u.SurnameChinese+u.NameChinese,sp.dataset ";
DicData dicdata = new DicData();
List <UserCitationProductionsModel> list = await db.Database.SqlQuery <UserCitationProductionsModel>(sql).ToListAsync();
int count = list.Count;
List <UserCitationProductionsModel> lis = list.Take(pageSize * curPage).Skip(pageSize * (curPage - 1)).ToList();
dicdata.Data = lis;
dicdata.TotalCount = count;
return(dicdata);
}
}
/// <summary>
/// 根据用户ID查询期刊集合
/// </summary>
/// <param name="sysuserID"></param>
/// <returns></returns>
public async Task <DicData> LoadBySysUserID(Guid sysuserID, int pageSize, int curPage)
{
using (var db = new OperationManagerDbContext())
{
DicData dic = new DicData();
List <RelationUserAttentionPeriodicalModel> lis = await db.RelationUserAttentionPeriodical.AsNoTracking().OrderBy(c => c.CreateTime).Where(w => w.SysUserID == sysuserID).ToListAsync();
int count = lis.Count;
List <RelationUserAttentionPeriodicalModel> list = lis.Take(pageSize * curPage).Skip(pageSize * (curPage - 1)).ToList();
dic.Data = list;
dic.TotalCount = count;
return(dic);
}
}
public async Task <DicData> LoadProductionByMetaData(string columname, string[] metaDataValues, int pageSize, int curPage, bool isExact = true)
{
using (var db = new OperationManagerDbContext())
{
// db.User.GetScalar
//根据元数据KEY获取CODE
/* SYS_MetaData sys_MetaData = db.SYS_MetaData.AsNoTracking().Where(w => w.MetaDataCode == metaDataCode).FirstOrDefault();
*/
//string sql1 = @"SELECT distinct ps.* FROM Productions ps left join
// dbo.ProductionsField pf on ps.ProductionID = pf.ProductionID
// WHERE pf.ProductionID IN
// (SELECT DISTINCT ProductionID FROM dbo.ProductionsField WHERE MetaDataID = '" + sys_MetaData.MetaDataID + "' ";
string sql = @" SELECT * FROM StaticProductions WHERE 1=1 ";
if (metaDataValues.Length > 1)
{
foreach (var item in metaDataValues)
{
// inValues += "'" + item.ToString() + "',";
sql += " and " + columname + " like '%" + item + "%' ";
}
}
else
{
if (!isExact)
{
sql += " and " + columname + " = '" + metaDataValues[0].ToString() + "' ";
}
else
{
sql += " and " + columname + " like '%" + metaDataValues[0].ToString() + "%' ";
}
}
//通过查询获取文献 例如：作者、主题等
List <StaticProductions> list = db.Database.SqlQuery <StaticProductions>(sql).Take(pageSize * curPage).Skip(pageSize * (curPage - 1)).ToList();
DicData dic = new DicData();
dic.TotalCount = db.Database.SqlQuery <StaticProductions>(sql).Count();
dic.Data = list;
return(dic);
}
}
/// <summary>
/// 根据用户姓,拼音首字母获取用户详情
/// </summary>
/// <param name="phoneticize">姓的首字母A，B，C，D</param>
/// <param name="pageSize">条数</param>
/// <param name="curPage">页码</param>
/// <returns></returns>
public async Task <DicData> LoadUserBySurnamePhoneticize(string phoneticize, int pageSize, int curPage)
{
DicData dicdata = new DicData();
using (var db = new OperationManagerDbContext())
{
string sql = "SELECT u.UUID,u.SurnameChinese+u.NameChinese AS username,spt.PostTitleTypeName,sc.CollegeName ,uh.UploadIMG FROM dbo.[User] u LEFT JOIN dbo.SYS_PositionalTitleType spt ON u.PttID= spt.PttID LEFT JOIN dbo.SYS_College sc ON sc.CID= u.DeptID LEFT JOIN dbo.UserPersonalHomepage uh ON uh.UserID= u.UUID WHERE 1=1 ";
sql += @" and u.SurnamePhoneticize like '" + phoneticize + "%'";
var list = db.Database.SqlQuery <UserOutput>(sql).Take(pageSize * curPage).Skip(pageSize * (curPage - 1)).ToList();
int count = db.Database.SqlQuery <UserOutput>(sql).Count();
dicdata.Data = list;
dicdata.TotalCount = count;
return(dicdata);
}
}
/// <summary>
/// 根据用户ID查询产品集合
/// </summary>
/// <param name="sysuserID"></param>
/// <param name="pageSize"></param>
/// <param name="curPage"></param>
/// <returns></returns>
public async Task <DicData> LoadBySysUserID(Guid sysuserID, int pageSize, int curPage)
{
using (var db = new OperationManagerDbContext())
{
DicData dic = new DicData();
// int lis = db.RelationUserCollectProduct.AsNoTracking().OrderBy(c => c.CreateTime).Where(w => w.SysUserID == sysuserID).Count();
int lis = db.RelationUserCollectProduct.AsNoTracking().OrderBy(c => c.CreateTime).Where(w => w.SysUserID == sysuserID).Count();
List <RelationUserCollectProductModel> list = db.RelationUserCollectProduct.AsNoTracking().OrderBy(c => c.CreateTime).Where(w => w.SysUserID == sysuserID).Take(pageSize * curPage).Skip(pageSize * (curPage - 1)).ToList();
// int count = lis;
dic.Data = list;
dic.TotalCount = lis;
return(dic);
}
}
/// <summary>
/// 首页-根据院系ID 获取文献信息
/// </summary>
/// <param name="cid">院系ID</param>
/// <param name="pageSize">条数</param>
/// <param name="curPage">页码</param>
/// <returns>所有模板收录类别和该收录类别下的所有文献and数量</returns>
public async Task <DicData> GetProductionsByCID(string cid, int pageSize, int curPage)
{
using (var db = new OperationManagerDbContext())
{
string sql = @"SELECT * FROM dbo.StaticProductions WHERE ProductionID IN(
SELECT ProductionID FROM Relation_UserClaimWorks WHERE SysUserID IN
(SELECT UUID FROM dbo.[User] WHERE UnitID='" + cid + "'))";
List <StaticProductions> list = await db.Database.SqlQuery <StaticProductions>(sql).ToListAsync();
int count = list.Count;
List <StaticProductions> lis = list.Take(pageSize * curPage).Skip(pageSize * (curPage - 1)).ToList();
DicData dicdata = new DicData();
dicdata.Data = lis;
dicdata.TotalCount = count;
return(dicdata);
}
}
/// <summary>
/// 根据用户ID/名称/英文名称 查询用户作品认领
/// </summary>
/// <param name="sysuserID">用户ID</param>
/// <param name="chineseName">用户名称</param>
/// <param name="englishName">用户英文名称</param>
/// <param name="pageSize">页条数</param>
/// <param name="curPage">页数</param>
/// <returns></returns>
public async Task <DicData> LoadClaimWorksCombineBySysUserID(Guid sysuserID, string chineseName, string englishName, int pageSize, int curPage)
{
using (var db = new OperationManagerDbContext())
{
DicData dic = new DicData();
//sp.UserID IN (SELECT UUID FROM dbo.[User] WHERE REPLACE(SurnameChinese,' ','') + REPLACE(NameChinese,' ', '')='" + chineseName.Trim() + "' OR REPLACE(SurnamePhoneticize,' ', '')+REPLACE(NamePhoneticize, ' ', '')='" + englishName.Trim() + "')
//author LIKE '%zhujiu%' OR author LIKE '%朱九%'
string sql = @"SELECT DISTINCT sp.ProductionID, * FROM dbo.StaticProductions sp WHERE (author LIKE '%" + chineseName.Trim() + "%' OR author LIKE '%" + englishName.Trim() + "%' OR sp.UserID='" + sysuserID + "') AND sp.ProductionID NOT IN (select ProductionID from Relation_UserClaimWorks WHERE cast(SysUserID as varchar(36)) ='" + sysuserID + "' AND UserClaimWorksStatus <> '0')";
List <StaticProductions> lis = await db.Database.SqlQuery <StaticProductions>(sql).ToListAsync();
List <StaticProductions> list = lis.Take(pageSize * curPage).Skip(pageSize * (curPage - 1)).ToList();
int count = lis.Count;
dic.TotalCount = count;
dic.Data = list;
return(dic);
}
}
/// <summary>
/// 根据用户ID查询用户认领作品,未提交全文
/// </summary>
/// <param name="sysuserID"></param>
/// <param name="pageSize">条数</param>
/// <param name="curPage">页码</param>
/// <returns></returns>
public async Task <DicData> LoadBySysUserID(Guid sysuserID, int pageSize, int curPage)
{
using (var db = new OperationManagerDbContext())
{
DicData dic = new DicData();
string sql = @" select * from dbo.StaticProductions sp LEFT JOIN dbo.Comm_Attachment a ON sp.ProductionID=a.BusinessID
WHERE a.UUID IS NULL AND sp.UserID='" + sysuserID + "'";
List <StaticProductions> lis = await db.Database.SqlQuery <StaticProductions>(sql).ToListAsync();
List <StaticProductions> list = lis.Take(pageSize * curPage).Skip(pageSize * (curPage - 1)).ToList();
int count = lis.Count;
dic.TotalCount = count;
dic.Data = list;
return(dic);
}
}
/// <summary>
/// 根据用户ID查询用户认领作品
/// </summary>
/// <param name="sysuserID"></param>
/// <param name="userClaimWorksStatus">认领作品状态：待认领为0；未认领为1；已认领为2；拒绝认领为3；文件为完整上传4</param>
/// <param name="pageSize">条数</param>
/// <param name="curPage">页码</param>
/// <returns></returns>
public async Task <DicData> LoadBySysUserIDAndStatus(Guid sysuserID, int userClaimWorksStatus, int pageSize, int curPage)
{
using (var db = new OperationManagerDbContext())
{
DicData dic = new DicData();
string sql = @" select ru.* from Relation_UserClaimWorks ru inner join dbo.StaticProductions p
on cast(p.ProductionID as varchar(36)) =ru.ProductionID WHERE 1=1";
sql += " and cast(ru.SysUserID as varchar(36)) ='" + sysuserID + "' and ru.UserClaimWorksStatus='" + userClaimWorksStatus + "'";
List <RelationUserClaimWorksModel> lis = await db.Database.SqlQuery <RelationUserClaimWorksModel>(sql).ToListAsync();
List <RelationUserClaimWorksModel> list = lis.Take(pageSize * curPage).Skip(pageSize * (curPage - 1)).ToList();
int count = lis.Count;
dic.TotalCount = count;
dic.Data = list;
return(dic);
}
}
/// <summary>
/// 获取文献用户私信信息
/// </summary>
/// <param name="userid">用户id</param>
/// <param name="senduserid">发送用户id</param>
/// <returns></returns>
public async Task <DicData> GetUserPrivateLetterListByUserIDOrSendUserid(string userid, string senduserid, int pageSize, int curPage)
{
using (var db = new OperationManagerDbContext())
{
string sql = @" select up.UUID,up.UserID,up.SendUserID,up.Content,up.type,up.createtime,up.ValidStatus,
(select u.SurnameChinese+u.NameChinese from [User] u where u.UUID=up.UserID) as UserName,
(select u.UploadIMG from UserPersonalHomepage u where u.UserID=up.UserID) as UserUploadIMG,
(select u.SurnameChinese+u.NameChinese from [User] u where u.UUID=up.SendUserID) as SendUserName,
(select u.UploadIMG from UserPersonalHomepage u where u.UserID=up.SendUserID) as SendUserUploadIMG
from UserPrivateLetter up where 1=1 ";
if (!string.IsNullOrWhiteSpace(userid))
{
sql += " and up.UserID ='" + userid + "' ";
}
if (!string.IsNullOrWhiteSpace(senduserid))
{
sql += " and up.SendUserID='" + senduserid + "' ";
}
sql += " order by CreateTime desc ";
List <UserPrivateLetterModels> list = await db.Database.SqlQuery <UserPrivateLetterModels>(sql).ToListAsync();
DicData dicdata = new DicData();
int count = list.Count;
List <UserPrivateLetterModels> lis = list.Take(pageSize * curPage).Skip(pageSize * (curPage - 1)).ToList();
dicdata.Data = lis;
dicdata.TotalCount = count;
return(dicdata);
}
}
/// <summary>
/// 获取文献用户评论信息
/// </summary>
/// <param name="productionid">文献id</param>
/// <param name="type">评论类型0普通、1专家、2报错</param>
/// <returns></returns>
public async Task <DicData> LoadUserCommentListByUserIDAndType(string userid, string type, int pageSize, int curPage)
{
using (var db = new OperationManagerDbContext())
{
string sql = @" select uc.UUID,uc.Content,uc.CreateTime,uc.Level,uc.ParentID,uc.ProductionID
,uc.Type,uc.UserID,uc.ValidStatus,u.SurnameChinese+u.NameChinese as UserName,uph.UploadIMG as UploadIMG
from UserComment uc left join [User] u
on uc.UserID=u.UUID
left join UserPersonalHomepage uph on u.UUID=uph.UserID
WHERE 1=1 ";
if (!string.IsNullOrWhiteSpace(userid))
{
sql += " and uc.UserID ='" + userid + "' ";
}
if (!string.IsNullOrWhiteSpace(userid))
{
sql += " and uc.Type='" + type + "' ";
}
sql += " order by CreateTime desc ";
List <UserCommentModels> list = await db.Database.SqlQuery <UserCommentModels>(sql).ToListAsync();
DicData dicdata = new DicData();
int count = list.Count;
List <UserCommentModels> lis = list.Take(pageSize * curPage).Skip(pageSize * (curPage - 1)).ToList();
dicdata.Data = lis;
dicdata.TotalCount = count;
return(dicdata);
}
}
/// <summary>
/// SCI引用TOP100论文
/// </summary>
/// <param name="staryear">开始年份</param>
/// <param name="endyear">结束年份</param>
/// <param name="sciorcscd">sci 或者 cscd类型</param>
/// <param name="departid">机构id</param>
/// <param name="pageSize">条数</param>
/// <param name="curPage">页码</param>
/// <returns></returns>
public async Task <DicData> LoadCitationProductionsTop100(string staryear, string endyear, string sciorcscd, string departid, int pageSize, int curPage)
{
using (var db = new OperationManagerDbContext())
{
string sql = @"select top 100 sp.* from UserCitationProductions uc left join StaticProductions sp on uc.ProductionID=sp.ProductionID where 1=1 ";
sql += " and year(sp.issued) between '" + staryear + "' and '" + endyear + "' ";
if (!string.IsNullOrEmpty(sciorcscd))
{
sql += " and sp.dataset like '%" + sciorcscd + "%'";
}
DicData dicdata = new DicData();
List <StaticProductions> list = await db.Database.SqlQuery <StaticProductions>(sql).ToListAsync();
int count = list.Count;
List <StaticProductions> lis = list.Take(pageSize * curPage).Skip(pageSize * (curPage - 1)).ToList();
dicdata.Data = lis;
dicdata.TotalCount = count;
return(dicdata);
}
}
/// <summary>
/// 多条件查询文献
/// </summary>
/// <param name="diclist"></param>
/// <param name="pageSize"></param>
/// <param name="curPage"></param>
/// <param name="order"></param>
/// <param name="orderby"></param>
/// <returns></returns>
public async Task <DicData> LoadProductionByCondition(List <Dictionary <string, string> > diclist, string hasattachment, int pageSize, int curPage, string order, string orderby)
{
DicData dicdata = new DicData();
using (var db = new OperationManagerDbContext())
{
var q = diclist.GroupBy(x => x.Keys.FirstOrDefault()).Where(x => x.Count() >= 1).ToList();
List <Dictionary <string, string> > liststr = new List <Dictionary <string, string> >();
for (int i = 0; i < q.Count; i++)
{
string key = q[i].Key;
string var = "";
Dictionary <string, string> dic = new Dictionary <string, string>();
for (int j = 0; j < diclist.Count; j++)
{
string value = diclist[j].FirstOrDefault().Value;
if (key == diclist[j].FirstOrDefault().Key)
{
var += value + ",";
}
}
dic.Add(key, var);
liststr.Add(dic);
}
string sql = @"SELECT sp.* FROM StaticProductions sp WHERE 1=1 ";
string sqlcount = "SELECT count(-1) FROM StaticProductions sp WHERE 1=1 ";
string where = "";
string strorder = "";
for (int i = 0; i < liststr.Count; i++) //外循环是循环的次数
{
string key = liststr[i].FirstOrDefault().Key;
string value = liststr[i].FirstOrDefault().Value;
string[] arrayvalue = value.Split(',').Where(s => !string.IsNullOrEmpty(s)).ToArray();
if (arrayvalue.Length > 1)
{
where += " and";
for (int j = 0; j < arrayvalue.Length; j++)
{
if (j > 0)
{
where += " OR " + key + " like '%" + arrayvalue[j] + "%'";
}
else
{
where += " ( " + key + " like '%" + arrayvalue[j] + "%'";
}
}
where += " ) ";
}
else
{
where += " and " + key + " like '%" + arrayvalue[0] + "%'";
}
// string stror = "";
// where += stror;
}
if (hasattachment == "有")
{
where += " and ( sp.ProductionID in( SELECT DISTINCT BusinessID FROM dbo.Comm_Attachment ))";
}
if (hasattachment == "无")
{
where += " and ( sp.ProductionID not in( SELECT DISTINCT BusinessID FROM dbo.Comm_Attachment ))";
}
if (order.ToLower() == "citationnum")
{
strorder = " order by sp.citations " + orderby;
}
if (order.ToLower() == "createtime")
{
strorder = " order by sp.CreateTime " + orderby;
}
if (order.ToLower() == "title")
{
strorder = " order by sp.title " + orderby;
}
int count = db.Database.SqlQuery <int>(sqlcount + where).FirstOrDefault();
List <StaticProductions> list = db.Database.SqlQuery <StaticProductions>(sql + where + strorder).Take(pageSize * curPage).Skip(pageSize * (curPage - 1)).ToList();
dicdata.Data = list;
dicdata.TotalCount = count;
return(dicdata);
}
}
