/// <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>
/// 作者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);
}
}
/// <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>
/// <returns></returns>
public List <YearAndCountOutPut> GetTongJiByNDLunWenShoulu(string departid, string staryear, string endyear, string indexed)
{
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 tt.indexed AS resulttype, CAST(YEAR(issued)AS NVARCHAR) AS year ,COUNT(-1) AS count FROM StaticProductions tt
WHERE YEAR(tt.issued) like '%" + list[i] + "%' AND tt.indexed LIKE'%" + indexed + "%' ";
if (!string.IsNullOrEmpty(departid))
{
// sql += " AND tt.jigouyuanxiCode='" + departid + "'";
}
sql += " group by year(tt.issued),tt.indexed ORDER BY year DESC ";
var listm = db.Database.SqlQuery <YearAndCount>(sql).ToList();;
// diclist.Add(list[i], listm);
op.Year = list[i];
op.CollegeResult = listm;
output.Add(op);
}
return(output);
}
}
