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