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