yjfc_xinxi/Report_people.aspx.cs

微信扫码关注公众号，代码整理好后第一时间通知你。

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
namespace yjfc_xinxi
{
    public partial class Report_people : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
           // string sql = "select c.usertruename as 姓名,count(case c.jifenyuanyin when '提交' then c.jifen else null end) as [提交数量],count(case c.jifenyuanyin when '房产之窗采用' then c.jifen else null end) as [房产之窗采用数量],count(case c.jifenyuanyin when '房产之窗简讯采用' then c.jifen else null end) as [房房产之窗简讯采用数量],count(case c.jifenyuanyin when '房产之窗评论采用' then c.jifen else null end) as [房房产之窗评论采用],count(case c.jifenyuanyin when '市级采用' then c.jifen else null end) as [市级采用],count(case c.jifenyuanyin when '市级采用简讯' then c.jifen else null end) as [市级采用简讯],count(case c.jifenyuanyin when '市级约稿采用' then c.jifen else null end) as [市级约稿采用],count(case c.jifenyuanyin when '州级采用' then c.jifen else null end) as [州级采用数量],count(case c.jifenyuanyin when '省级采用' then c.jifen else null end) as [省级采用],count(case c.jifenyuanyin when '国家采用' then c.jifen else null end) as [国家采用数量],count(case c.jifenyuanyin when '市级网站、媒体采用' then c.jifen else null end) as [市级网站、媒体采用数量],count(case c.jifenyuanyin when '州级网站、媒体采用' then c.jifen else null end) as [州级网站、媒体采用数量],count(case c.jifenyuanyin when '省级网站、媒体采用' then c.jifen else null end) as [省级网站、媒体采用],count(case c.jifenyuanyin when '州级网站、媒体采用' then c.jifen else null end) as [州级网站、媒体采用数量],count(case c.jifenyuanyin when '市领导批示' then c.jifen else null end) as [市领导批示数量],count(case c.jifenyuanyin when '州领导批示' then c.jifen else null end) as [州领导批示数量],count(case c.jifenyuanyin when '省领导批示' then c.jifen else null end) as [省领导批示数量] from (select xinxi_jifen.*,xinxi_xinxi.issubmit,xinxi_xinxi.isused,xinxi_xinxi.xinxititle, xinxi_xinxi.xinxiid,xinxi_users.usertruename,xinxi_organize.organizename,xinxi_xinxi.xinxitime,xinxi_xinxi.xinxi_usetime from xinxi_users left outer join xinxi_xinxi on xinxi_xinxi.xinxiauthor=xinxi_users.userid left outer join xinxi_organize on xinxi_users.organizeid=xinxi_organize.organizeid left outer join xinxi_jifen on xinxi_xinxi.xinxijifenid=xinxi_jifen.jifenid where xinxi_xinxi.xinxititle is not null and xinxi_xinxi.xinxi_usetime>='2013-01-01' and xinxi_xinxi.xinxi_usetime <='2014-01-01') as c group by c.usertruename";
           // string sql = "select c.organizename as 部门, c.usertruename as 姓名,count(c.usertruename) as 数量,count(case c.jifenjb when '房产之窗' then c.jifen else null end) as [房产之窗采用数量(条)],sum(case c.jifenjb when '房产之窗' then c.jifen else null end) as [房产之窗采用总积分(分)],sum(case c.jifenjb when '房产之窗' then c.gaochou else null end) as [房产之窗采用总稿酬(元)],count(case c.jifenjb when '市级正文' then c.jifen else null end) as [市级正文采用数量(条)],sum(case c.jifenjb when '市级正文' then c.jifen else null end) as [市级正文采用总积分(分)],sum(case c.jifenjb when '市级正文' then c.gaochou else null end) as [市级正文采用总稿酬(元)],count(case c.jifenjb when '省级' then c.jifen else null end) as [省采用数量(条)],sum(case c.jifenjb when '省级' then c.jifen else null end) as [省采用总积分(分)],sum(case c.jifenjb when '省级' then c.gaochou else null end) as [省采用总稿酬(元)],count(case c.jifenjb when '州级' then c.jifen else null end) as [州级采用数量(条)],sum(case c.jifenjb when '州级' then c.jifen else null end) as [州级采用总积分(分)],sum(case c.jifenjb when '州级' then c.gaochou else null end) as [州级采用总稿酬(元)],count(case c.jifenjb when '国家级' then c.jifen else null end) as [国家级采用数量(条)],sum(case c.jifenjb when '国家级' then c.jifen else null end) as [国家级采用总积分(分)],sum(case c.jifenjb when '国家级' then c.gaochou else null end) as [国家级采用总稿酬(元)]from (select xinxi_jifen.*,xinxi_xinxi.issubmit,xinxi_xinxi.isused,xinxi_xinxi.xinxititle, xinxi_xinxi.xinxiid,xinxi_users.usertruename,xinxi_organize.organizename,xinxi_xinxi.xinxitime,xinxi_xinxi.xinxi_usetime from xinxi_users left outer join xinxi_xinxi on xinxi_xinxi.xinxiauthor=xinxi_users.userid left outer join xinxi_organize on xinxi_users.organizeid=xinxi_organize.organizeid left outer join xinxi_jifen on xinxi_xinxi.xinxijifenid=xinxi_jifen.jifenid where xinxi_xinxi.xinxititle is not null and xinxi_xinxi.xinxi_usetime>='2013-01-01' and xinxi_xinxi.xinxi_usetime <='2015-01-01') as c group by c.organizename,c.usertruename order by c.organizename";
            string sql="select c.organizename as 部门, c.usertruename as 信息员,count(c.usertruename) as 报数信息,count(case c.jifenjb when '房产之窗' then c.jifen else null end) as [房产之窗采用数量(条)],count(case c.jifenjb when '市级正文' then c.jifen else null end) as [市级正文采用数量(条)],count(case c.jifenjb when '省级' then c.jifen else null end) as [省采用数量(条)],count(case c.jifenjb when '州级' then c.jifen else null end) as [州级采用数量(条)],count(case c.jifenjb when '国家级' then c.jifen else null end) as [国家级采用数量(条)]from (select xinxi_jifen.*,xinxi_xinxi.issubmit,xinxi_xinxi.isused,xinxi_xinxi.xinxititle, xinxi_xinxi.xinxiid,xinxi_users.usertruename,xinxi_organize.organizename,xinxi_xinxi.xinxitime,xinxi_xinxi.xinxi_usetime from xinxi_users left outer join xinxi_xinxi on xinxi_xinxi.xinxiauthor=xinxi_users.userid left outer join xinxi_organize on xinxi_users.organizeid=xinxi_organize.organizeid left outer join xinxi_jifen on xinxi_xinxi.xinxijifenid=xinxi_jifen.jifenid where xinxi_xinxi.xinxititle is not null and xinxi_xinxi.xinxi_usetime>='2013-01-01' and xinxi_xinxi.xinxi_usetime <='2015-01-01') as c group by c.organizename,c.usertruename order by c.organizename";
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString);
            SqlDataAdapter adp = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            conn.Open();
            adp.Fill(ds);
            GridView1.DataSource = ds;
            GridView1.DataBind();
            //Repeater1.DataSource = ds;//设定DataList的数据源为PageDataSource
         
            //Repeater1.DataBind();//绑定DataList
            conn.Close();
        }
    }
}