/// <summary>
/// 年度成果院所排名---//--年度成果科系排名
/// </summary>
/// <param name="collegetype"></param>
/// <returns></returns>
public List <OutPutReport> GetStatisticalByStarYearAndEndYear(string collegetype, string top, int pageSize, int curPage)
{
using (var db = new OperationManagerDbContext())
{
string sqlstr = @"SELECT DISTINCT CAST(YEAR(issued)AS NVARCHAR) AS year FROM dbo.StaticProductions ORDER BY year desc";
List <string> list = db.Database.SqlQuery <string>(sqlstr).Take(pageSize * curPage).Skip(pageSize * (curPage - 1)).ToList();
List <OutPutReport> list_outputReport = new List <OutPutReport>();
// Dictionary<string, List<SysCollegeResult>> diclist = new Dictionary<string, List<SysCollegeResult>>();
for (int i = 0; i < list.Count; i++)
{
OutPutReport outPutReport = new OutPutReport();
outPutReport.Year = Convert.ToInt32(list[i]);
string sql = @"SELECT top " + top + " sc.CollegeName as Year,cast(year(sp.issued) as varchar) as Resulttype,COUNT(-1) AS count FROM dbo.SYS_College sc LEFT join dbo.StaticProductions sp ON sc.CID=sp.jigouyuanxiCode WHERE sc.CollegeType=" + collegetype + " and year(sp.issued) like '%" + list[i] + "%' GROUP BY year(sp.issued),sc.CollegeName ORDER BY count DESC";
List <SysCollegeResult> lis = db.Database.SqlQuery <SysCollegeResult>(sql).ToList();
if (lis.Count > 0)
{
outPutReport.CollegeResult = lis;
// listAll.Add(lis);
//diclist.Add(list[i], lis);
}
list_outputReport.Add(outPutReport);
}
return(list_outputReport);
}
}
/// <summary>
/// 研究单元-是否有全文
///获取所有收录类别下的是否有全文
/// </summary>
/// <returns>所有收录类别，是否有全文</returns>
public List <TypeNameCount> GetSYS_AttachmentListAndCount()
{
var hasnum = 0;
var nonum = 0;
string sqlhave = @"SELECT COUNT(*) FROM dbo.StaticProductions WHERE ProductionID IN (SELECT BusinessID FROM dbo.Comm_Attachment) ";
string sqlnot = @"SELECT COUNT(*) FROM dbo.StaticProductions WHERE ProductionID not IN (SELECT BusinessID FROM dbo.Comm_Attachment) ";
using (var db = new OperationManagerDbContext())
{
hasnum = db.Database.SqlQuery <int>(sqlhave).FirstOrDefault();
nonum = db.Database.SqlQuery <int>(sqlnot).FirstOrDefault();
}
List <TypeNameCount> dicList = new List <TypeNameCount>();
TypeNameCount dic = new TypeNameCount();
dic.TypeID = "CommAttachmentTypeID";
dic.TypeName = "有";
dic.TypeCount = hasnum;
TypeNameCount dic1 = new TypeNameCount();
dic1.TypeID = "CommAttachmentTypeID";
dic1.TypeName = "无";
dic1.TypeCount = nonum;
dicList.Add(dic);
dicList.Add(dic1);
return(dicList);
}
/// <summary>
/// 获取单个模板内的属性
/// </summary>
/// <param name="metaDataID">属性ID</param>
/// <returns></returns>
public async Task <SYS_TemplateField> GetByMetaDataIDAsync(string metaDataID)
{
using (var db = new OperationManagerDbContext())
{
return(await db.SYS_TemplateField.AsNoTracking().Where(w => w.MetaDataID.ToString() == metaDataID).FirstOrDefaultAsync());
}
}
/// <summary>
/// 研究单元-内容类型
///获取所有模板的内容类型,该内容类型下的所有文献数量
/// </summary>
/// <param name="userID">userID,登陆用户ID</param>
/// <returns>所有模板内容类型和该内容类型下的所有文献and数量</returns>
public async Task <List <TypeNameCount> > GetSYS_TemplateContentTypeListAndCountAsync(Guid userID)
{
using (var db = new OperationManagerDbContext())
{
SYS_TemplateAdapter sys_TemplateAdapter = new SYS_TemplateAdapter();
List <SYS_Template> list = await db.SYS_Template.AsNoTracking().ToListAsync();
List <TypeNameCount> dicList = new List <TypeNameCount>();
ProductionsAdapter prad = new ProductionsAdapter();
int lic = 0;
foreach (var sale in list)
{
TypeNameCount dic = new TypeNameCount();
lic = prad.LoadProductionByTempleteIDAndUserID(sale.TemplateID, userID).Count;
// dic.TypeID = sale.TemplateID.ToString();
dic.TypeID = "TemplateID";
dic.TypeName = sale.ContentType;
dic.TypeCount = lic;
dicList.Add(dic);
}
return(dicList);
}
}
/// <summary>
/// 研究单元-收录类别
///获取所有收录类别下的所有文献数量
/// </summary>
/// <returns>所有收录类别，收录类别下的文献数量</returns>
public List <TypeNameCount> GetSYS_IndexedTypeListAndCount()
{
ProductionsFieldAdapter productionsFieldAdapter = new ProductionsFieldAdapter();
string sql = @"SELECT DISTINCT indexed FROM dbo.StaticProductions WHERE indexed IS NOT NULL AND indexed<>''";
List <TypeNameCount> dicList = new List <TypeNameCount>();
using (var db = new OperationManagerDbContext())
{
List <string> strlist = db.Database.SqlQuery <string>(sql).ToList();
int lif = 0;
for (int i = 0; i < strlist.Count; i++)
{
string indexsql = @"SELECT COUNT(*) FROM dbo.StaticProductions WHERE indexed='" + strlist[i] + "'";
lif = db.Database.SqlQuery <int>(indexsql).FirstOrDefault();
TypeNameCount typeNameCount = new TypeNameCount();
typeNameCount.TypeID = "indexed";//收录类别
typeNameCount.TypeName = strlist[i];
typeNameCount.TypeCount = lif;
dicList.Add(typeNameCount);
}
}
return(dicList);
}
/// <summary>
/// 根据UserID查询大表数据
/// </summary>
/// <param name="userID"></param>
/// <returns></returns>
public async Task <List <StaticProductions> > GetByUserID(Guid userID)
{
using (var db = new OperationManagerDbContext())
{
return(await db.StaticProductions.Where(w => w.UserID == userID).ToListAsync());
}
}
public async Task <IHttpActionResult> GetSYS_TemplateContentTypeListAndCountByTime(string startime, string endtime)
{
using (var db = new OperationManagerDbContext())
{
List <SYS_Template> list = await db.SYS_Template.ToListAsync();
List <Dictionary <String, Object> > dicList = new List <Dictionary <string, object> >();
foreach (var sale in list)
{
Dictionary <String, Object> dic = new Dictionary <string, object>();
List <StaticProductions> lic = await productionsFieldAdapter.LoadContentListByTimeAndUserID(sale.TemplateID, startime, endtime);
dic.Add("ContentTypeID", "TemplateID");
dic.Add("ContentTypeValue", sale.TemplateID);
dic.Add("ContentType", sale.ContentType);
dic.Add("ContentTypeCount", lic.Count);
dicList.Add(dic);
}
return(Json(dicList));
}
}
public async Task <RelationUserCollectThemeModel> LoadBySysUserIDAndThemeID(Guid sysuserID, Guid themeID)
{
using (var db = new OperationManagerDbContext())
{
return(db.RelationUserCollectTheme.AsNoTracking().OrderBy(c => c.CreateTime).Where(w => w.SysUserID == sysuserID && w.ThemeID == themeID).ToList().FirstOrDefault());
}
}
/// <summary>
/// 根据用户ID查询别名
/// </summary>
/// <param name="userID"></param>
/// <returns></returns>
public UserAliasModel LoadByUserID(Guid userID)
{
using (var db = new OperationManagerDbContext())
{
return(db.UserAlia.Where(w => w.UserID == userID).FirstOrDefault());
}
}
public List <Dictionary <String, Object> > LoadProductionYearListAndCountByUserID(string username)
{
using (var db = new OperationManagerDbContext())
{
string sql = @" select cast( year(tt.issued)as NVARCHAR) as year ,count(*) as count from StaticProductions tt
WHERE tt.author LIKE '%" + username + "%' group by year(tt.issued) ORDER BY YEAR desc ";
List <Dictionary <String, Object> > dicList = new List <Dictionary <string, object> >();
var list = db.Database.SqlQuery <YearAndCount>(sql).ToList();
foreach (var sale in list)
{
Dictionary <String, Object> dic = new Dictionary <string, object>();
if (string.IsNullOrEmpty(sale.Year + ""))
{
dic.Add("ContentYear", "其它");
dic.Add("ContentYearCount", sale.Count);
dic.Add("ContentYearID", "issued");
}
else
{
dic.Add("ContentYear", sale.Year);
dic.Add("ContentYearCount", sale.Count);
dic.Add("ContentYearID", "issued");
}
dicList.Add(dic);
}
return(dicList);
}
}
public async Task <List <Dictionary <String, Object> > > LoadContentListCount(string username)
{
using (var db = new OperationManagerDbContext())
{
string sqltem = @"SELECT * FROM dbo.SYS_Template ";
List <Dictionary <String, Object> > listdic = new List <Dictionary <string, object> >();
List <SYS_Template> list = db.Database.SqlQuery <SYS_Template>(sqltem).ToList();
for (int i = 0; i < list.Count; i++)
{
string sql = @"select count(-1) from StaticProductions p
where p.TemplateID='" + list[i].TemplateID + "' and p.author LIKE '%" + username + "%'";
var count = db.Database.SqlQuery <int>(sql).FirstOrDefault();
Dictionary <String, Object> dic = new Dictionary <string, object>();
dic.Add("IndexedTypeID", "TemplateID");
dic.Add("IndexedTypeValue", list[i].TemplateID);
dic.Add("IndexedType", list[i].ContentType);
dic.Add("IndexedTypeCount", count);
listdic.Add(dic);
}
return(listdic);
}
}
/// <summary>
/// 个人详细信息-收录类别
///获取个人所有模板的收录类别,该收录类别的所有文献数量
/// </summary>
/// <param name="username">作者的名字</param>
/// <returns>所有模板收录类别和该收录类别下的所有文献and数量</returns>
//E134A22A-4187-4318-BB70-BCC66711ED1D 收录类型
//50883877-E367-4D5B-85FD-5F15A5B2E789 作者
public List <Dictionary <String, Object> > GetUserIndexedTypeListAndCount(string username)
{
SYS_TemplateFieldAdapter sys_TemplateFieldModelAdapter = new SYS_TemplateFieldAdapter();
ProductionsFieldAdapter productionsFieldAdapter = new ProductionsFieldAdapter();
ProductionsAdapter productionsAdapter = new ProductionsAdapter();
string IndexedTypesql = @"SELECT DISTINCT indexed FROM dbo.StaticProductions WHERE indexed IS NOT NULL AND indexed<>''";
using (var db = new OperationManagerDbContext())
{
List <string> IndexedType = db.Database.SqlQuery <string>(IndexedTypesql).ToList();
List <Dictionary <String, Object> > dicList = new List <Dictionary <string, object> >();
for (int i = 0; i < IndexedType.Count; i++)
{
int count = 0;
string sql = @" SELECT count(*) FROM StaticProductions WHERE indexed = '" + IndexedType[i] + "' and author LIKE'%" + username + "%'";
count = db.Database.SqlQuery <int>(sql).FirstOrDefault();
Dictionary <String, Object> dic = new Dictionary <string, object>();
dic.Add("IndexedTypeID", "indexed");
dic.Add("IndexedType", IndexedType[i]);
dic.Add("IndexedTypeCount", count);
dicList.Add(dic);
}
return(dicList);
}
}
/// <summary>
/// 年度成果类型分布
/// </summary>
/// <returns></returns>
public List <YearAndCountOutPut> GetStatisticalByNianDuChengGuoLeiXingFenBu(string staryear, string endyear)
{
using (var db = new OperationManagerDbContext())
{
string strsql = @" SELECT DISTINCT CAST(YEAR(issued)AS NVARCHAR) AS year FROM dbo.StaticProductions WHERE YEAR(issued) BETWEEN '" + staryear + "' AND '" + endyear + "' ORDER BY year desc";
List <string> list = db.Database.SqlQuery <string>(strsql).ToList();
//Dictionary<string, List<YearAndCount>> diclist = new Dictionary<string, List<YearAndCount>>();
List <YearAndCountOutPut> output = new List <YearAndCountOutPut>();
for (int i = 0; i < list.Count; i++)
{
YearAndCountOutPut op = new YearAndCountOutPut();
string sql = @" select resulttype,CAST(YEAR(issued)AS NVARCHAR) as year ,count(*) as count from StaticProductions sp
where year(sp.issued) like '%" + list[i] + "%' group by resulttype,year(sp.issued)";
List <YearAndCount> listm = db.Database.SqlQuery <YearAndCount>(sql).ToList();
op.Year = list[i];
op.CollegeResult = listm;
output.Add(op);
// diclist.Add(list[i], listm);
}
return(output);
}
}
/// <summary>
/// 年度论文院所收录情况排名---/年度论文科系收录情况排名
/// </summary>
/// <param name="collegetype">院系类型 1 院系，2科系</param>
/// <returns></returns>
public List <YearAndCountOutPut> GetTongJiByNDChengGuoYuanSuoPaiMing(string collegetype, string top, int pageSize, int curPage)
{
using (var db = new OperationManagerDbContext())
{
string sqlstr = @"SELECT DISTINCT CAST(YEAR(issued)AS NVARCHAR) AS year FROM dbo.StaticProductions ORDER BY year desc";
List <string> list = db.Database.SqlQuery <string>(sqlstr).Take(pageSize * curPage).Skip(pageSize * (curPage - 1)).ToList();
List <YearAndCountOutPut> list_outputReport = new List <YearAndCountOutPut>();
// Dictionary<string, List<SysCollegeResult>> diclist = new Dictionary<string, List<SysCollegeResult>>();
for (int i = 0; i < list.Count; i++)
{
YearAndCountOutPut report = new YearAndCountOutPut();
string sql = @" SELECT TOP " + top + " sc.CID,sc.CollegeName as Year,COUNT(*) AS count FROM dbo.SYS_College sc LEFT JOIN dbo.[User] u ON (u.UnitID=sc.CID OR u.DeptID=sc.CID) LEFT JOIN dbo.Relation_UserClaimWorks ruw ON ruw.SysUserID = u.UUID LEFT join dbo.StaticProductions sp ON ruw.ProductionID = sp.ProductionID where sc.CollegeType=" + collegetype + " and year(sp.issued) like '%" + list[i] + "%' GROUP BY sc.CID,sc.CollegeName ORDER BY sc.CID ASC , count DESC";
List <YearAndCount> lis = db.Database.SqlQuery <YearAndCount>(sql).ToList();
report.Year = list[i];
if (lis.Count > 0)
{
report.CollegeResult = lis;
//diclist.Add(list[i], lis);
}
list_outputReport.Add(report);
}
return(list_outputReport);
}
}
/// <summary>
///
/// </summary>
/// <param name="templeteid"></param>
/// <param name="userid"></param>
/// <returns></returns>
public List <Production> LoadProductionByTempleteIDAndUserID(Guid templeteid, Guid userid)
{
using (var db = new OperationManagerDbContext())
{
return(db.Production.AsNoTracking().OrderBy(o => o.CreateTime).Where(o => o.TemplateID == templeteid).ToList());;
}
}
public async Task <IHttpActionResult> GetIndexUserList()
{
using (var db = new OperationManagerDbContext())
{
// string sql = @"
//SELECT TOP 8 u.UUID,SurnameChinese,NameChinese,
//(SELECT COUNT(-1) FROM (
//SELECT s.ProductionID FROM dbo.StaticProductions s WHERE s.UserID=u.UUID
// UNION
// SELECT ruc.ProductionID FROM Relation_UserClaimWorks AS ruc
// WHERE ruc.SysUserID=u.UUID
// )t
//) totalcount,up.UploadIMG
//FROM dbo.[User] u LEFT JOIN dbo.UserPersonalHomepage up ON up.UserID = u.UUID
//WHERE IsRecommend =1 ORDER BY OrderBy";
string sql = @"
SELECT TOP 8 u.UUID,SurnameChinese,NameChinese,
(SELECT COUNT(-1) FROM (
SELECT s.ProductionID FROM dbo.StaticProductions s WHERE CHARINDEX(REPLACE(u.SurnameChinese+u.NameChinese,' ',''),s.author)>0
)t
) totalcount,up.UploadIMG
FROM dbo.[User] u LEFT JOIN dbo.UserPersonalHomepage up ON up.UserID = u.UUID
WHERE IsRecommend =1 ORDER BY OrderBy";
List <IndexUser> list = await db.Database.SqlQuery <IndexUser>(sql).ToListAsync();
return(Json(list));
}
}
/// <summary>
/// 根据文献ID查询大表数据
/// </summary>
/// <param name="ProductionID"></param>
/// <returns></returns>
public StaticProductions GetProductionsByID(Guid ProductionID)
{
using (var db = new OperationManagerDbContext())
{
return(db.StaticProductions.Where(w => w.ProductionID == ProductionID).FirstOrDefault());
}
}
public async Task <int> LoadBySysUserIDAndStatusCount(Guid sysuserID, int UserClaimWorksStatus)
{
using (var db = new OperationManagerDbContext())
{
return(db.RelationUserClaimWorks.AsNoTracking().OrderBy(c => c.SysUserID).Where(w => w.SysUserID == sysuserID && w.UserClaimWorksStatus == UserClaimWorksStatus).ToListAsync().Result.Count);
}
}
public async Task <int> LoadBySysUserIDCount(Guid sysuserID)
{
using (var db = new OperationManagerDbContext())
{
return(db.RelationUserCollectPeriodical.OrderBy(c => c.CreateTime).Where(w => w.SysUserID == sysuserID).ToList().Count);
}
}
public async Task <int> LoadBySysUserIDCount(Guid sysuserID)
{
using (var db = new OperationManagerDbContext())
{
return(db.RelationUserAttentionScholar.AsNoTracking().OrderBy(c => c.CreateTime).Where(w => w.SysUserID == sysuserID).ToList().Count);
}
}
/// <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.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;
// }
//}
//根据用户id 和文献id获取 关注信息
public async Task <RelationUserAttentionPeriodicalModel> LoadBySysUserIDAndProdctionID(Guid sysuserID, Guid productionid)
{
using (var db = new OperationManagerDbContext())
{
return(db.RelationUserAttentionPeriodical.AsNoTracking().OrderBy(c => c.CreateTime).Where(w => w.SysUserID == sysuserID && w.PeriodicalID == productionid).ToList().FirstOrDefault());
}
}
//根据用户id 和学者id 获取关注学者信息
public async Task <RelationUserAttentionScholarModel> LoadBySysUserIDAndScholarID(Guid sysuserID, Guid scholarID)
{
using (var db = new OperationManagerDbContext())
{
return(db.RelationUserAttentionScholar.AsNoTracking().OrderBy(c => c.CreateTime).Where(w => w.SysUserID == sysuserID && w.ScholarID == scholarID).ToList().FirstOrDefault());
}
}
/// <summary>
/// 研究单元-语言
///获取所有语言下的所有文献数量
/// </summary>
/// <returns>所有语言，所有语言下的文献数量</returns>
public async Task <List <TypeNameCount> > GetSYS_LanguageListAndCount()
{
SYS_TemplateFieldAdapter sys_TemplateFieldModelAdapter = new SYS_TemplateFieldAdapter();
ProductionsFieldAdapter productionsFieldAdapter = new ProductionsFieldAdapter();
string sql = @"SELECT DISTINCT Iso FROM dbo.StaticProductions WHERE iso IS NOT NULL AND iso<>''";
using (var db = new OperationManagerDbContext())
{
List <string> strlist = db.Database.SqlQuery <string>(sql).ToList();
List <TypeNameCount> dicList = new List <TypeNameCount>();
int lif = 0;
for (int i = 0; i < strlist.Count; i++)
{
lif = productionsFieldAdapter.LoadByDefaultText("iso", strlist[i]);
TypeNameCount dic = new TypeNameCount();
// dic.TypeID = "8BC5C3A3-6565-45F8-B77A-891077D4E2A5";
dic.TypeID = "iso";
dic.TypeName = strlist[i];
dic.TypeCount = lif;
dicList.Add(dic);
}
return(dicList);
}
}
/// <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>
/// 获取模板数据集合
/// </summary>
/// <returns></returns>
public List <SYS_TemplateField> GetSYS_TemplateFieldList()
{
using (var db = new OperationManagerDbContext())
{
return(base.GetAll().ToList());
}
}
/// <summary>
/// 研究单元-发表日期
/// 获取所有年份下的所有文献数量
/// </summary>
/// <param name="userid">userid,登陆用户ID</param>
/// <returns>所有年份，所有年份下的文献and数量</returns>
public async Task <List <TypeNameCount> > LoadProductionYearListAndCountByUserID()
{
using (var db = new OperationManagerDbContext())
{
string sql = @"select year(issued) as year ,count(*) as count from StaticProductions where issued is not null and issued <>'' group by year(issued) order by year desc";
List <TypeNameCount> dicList = new List <TypeNameCount>();
var list = await db.Database.SqlQuery <YearAndCount>(sql).ToListAsync();
foreach (var sale in list)
{
TypeNameCount dic = new TypeNameCount();
dic.TypeName = sale.Year.ToString();
dic.TypeCount = sale.Count;
// dic.TypeID = "6A94CC2F-545D-4F80-8843-CA037B84C24F";
dic.TypeID = "issued";
if (sale.Year.ToString() == "1900")
{
dic.TypeName = "其它";
}
dicList.Add(dic);
}
return(dicList);
}
}
/// <summary>
/// 获取单个模板
/// </summary>
/// <param name="templateID">模板ID</param>
/// <returns></returns>
public List <SYS_TemplateField> GetSYS_TemplateField(Guid?templateID)
{
using (var db = new OperationManagerDbContext())
{
return(base.GetAll().Where(w => w.TemplateID == templateID).ToList());
}
}
/// <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>
/// 研究单元-内容类型
///获取所有模板的内容类型,该内容类型下的所有文献数量
/// </summary>
/// <param name="userID">userID,登陆用户ID</param>
/// <returns>所有模板内容类型和该内容类型下的所有文献and数量</returns>
public async Task <List <TypeNameCount> > GetSYS_TemplateContentTypeListAndCount()
{
using (var db = new OperationManagerDbContext())
{
SYS_TemplateAdapter sys_TemplateAdapter = new SYS_TemplateAdapter();
List <TypeNameCount> dicList = new List <TypeNameCount>();
ProductionsAdapter prad = new ProductionsAdapter();
List <SYS_Template> list = await db.SYS_Template.ToListAsync();
int lic;
foreach (var sale in list)
{
if (sale.TemplateID.ToString().Length > 0)
{
TypeNameCount dic = new TypeNameCount();
lic = db.StaticProductions.Where(w => w.TemplateID == sale.TemplateID).Count();
// dic.TypeID = sale.TemplateID.ToString();
dic.TypeID = "TemplateID";
dic.TypeValue = sale.TemplateID.ToString();
dic.TypeName = sale.ContentType;
dic.TypeCount = lic;
// dic.Add("ContentTypeList", lic);
dicList.Add(dic);
}
}
return(dicList);
}
}
/// <summary>
/// 作者SCI发文量TOP100/作者CSCD发文量TOP100
/// </summary>
/// <returns></returns>
public List <YearAndCountOutPut> GetStatisticalByZuoZheSCIOrCSCDTop100(string staryear, string endyear, string sciorcscd)
{
using (var db = new OperationManagerDbContext())
{
string sql = @" select top 100 us.SurnameChinese+us.NameChinese as year,count(*)as count from Relation_UserClaimWorks u
left join StaticProductions sp on u.ProductionID=sp.ProductionID
left join [User] us on us.uuid=u.SysUserID
where UserClaimWorksStatus=2
and year(sp.issued) between '" + staryear + "' and '" + endyear + "' and sp.dataset like '%" + sciorcscd + "%' group by us.SurnameChinese+us.NameChinese order by count desc";
List <YearAndCount> list = db.Database.SqlQuery <YearAndCount>(sql).ToList();
List <YearAndCountOutPut> output = new List <YearAndCountOutPut>();
for (int i = 0; i < list.Count; i++)
{
YearAndCountOutPut op = new YearAndCountOutPut();
op.Year = list[i].Year;
YearAndCount ya = list[i];
op.CollegeResult = new List <YearAndCount>();
op.CollegeResult.Add(ya);
output.Add(op);
}
return(output);
}
}
