JW/督导评语7.16/Count.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Count : System.Web.UI.Page
{
   LYS.UserData1 myud = new LYS.UserData1();
    //string QueryConditions = "";
    string SQL = "select ID,教师姓名,系部,课程名,督导姓名,听课日期 from TKJL";
    string TableName = "TKJL";
    string sql = "";
    protected void Page_Load(object sender, EventArgs e)
    {
        //tbEndTime.Value = DateTime.Now.ToShortDateString();
        //tbStartTime.Value = DateTime.Now.ToShortDateString();
        Session["SQL"] = "";
        HttpContext.Current.Session["TableName"] = "";
        HttpContext.Current.Session["SQL"] = SQL;
        HttpContext.Current.Session["TableName"] = TableName;

        if (!Page.IsPostBack)
        {
           // myud.BindFiledToDropDownList(QueryField, SQL, TableName);
        }
        //DateTime startTime = Convert.ToDateTime("2017-9-1");
        //DateTime endTime = Convert.ToDateTime("2017-9-11");
       // string sql = "select count(*) as  总计 from TKJL where 听课日期 >= #2017-9-1# and 听课日期 <=#2017-9-10#";  //OK


       //DateTime  time3 = DateTime.D dateTimePicker1.Value.Date.ToString("yyyy-MM-dd");
       //DateTime time4 = dateTimePicker2.Value.Date.ToString("yyyy-MM-dd");

       // checkCmd = "SELECT sum([单笔账单总计]) As 总计 FROM Account where 时间  between #" + time3 + "# and #" + time4 + "#";

        //Literal1.Text = myud.DispLineCount(sql).ToString();
      
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        Panel1.Visible = true;
        Literal2.Text = "《" + sDemp.Value + "》" + ReportName.Value + "督导统计报表";

        DateTime startTime = Convert.ToDateTime(tbStartTime.Value.ToString());
        DateTime endTime = Convert.ToDateTime(tbEndTime.Value.ToString());
        //2.督导听课
        if (sDemp.Value.Trim().ToString() == "全院")
        {
          //   sql = "select count(*) as  " + ReportName.Value + " from TKJL where 听课日期 >= '" + tbStartTime.Value + "' and 听课日期 <='" + tbEndTime.Value + "'";  //OK
            sql = "select count(*) from TKJL where 听课日期 >= '" + tbStartTime.Value + "' and 听课日期 <='" + tbEndTime.Value + "'";  //OK
        }
        else
        {
         //  sql = "select count(*) as  " + ReportName.Value + " from TKJL where 听课日期 >= '" + tbStartTime.Value + "' and 听课日期 <='" + tbEndTime.Value + "' and 系部='" + sDemp.Value.Trim().ToString() + "'";  //OK 
            sql = "select count(*)  from TKJL where 听课日期 >= '" + tbStartTime.Value + "' and 听课日期 <='" + tbEndTime.Value + "' and 系部='" + sDemp.Value.Trim().ToString() + "'";  //OK 
        }
        //string sql = "select count(*) as  " + ReportName.Value + " from TKJL where 听课日期 >= #" + tbStartTime.Value + "# and 听课日期 <=#" + tbEndTime.Value + "#";  //OK ACCESS
        XB.Text = sDemp.Value.ToString();
        DDTK.Text = myud.DispLineCountNum(sql).ToString();
        //1.督导会议
        if (sDemp.Value.Trim().ToString() == "全院")
        {
            sql = "select count(*)  from OthProg where 督导时间 >= '" + tbStartTime.Value + "' and 督导时间 <='" + tbEndTime.Value + "'  and 督导项目名 Like '%督导会议%'";  //OK
        }
        else
        {
            sql = "select count(*)  from OthProg where 督导时间 >= '" + tbStartTime.Value + "' and 督导时间 <='" + tbEndTime.Value + "' and 督导项目名 Like '%督导会议%' and 系部='" + sDemp.Value.Trim().ToString() + "'";  //OK 
        }
        DDHY.Text = myud.DispLineCountNum(sql).ToString();
        //3.评课次数
        //if (sDemp.Value.Trim().ToString() == "全院")
        //{
        //    sql = "select count(*)  from OthProg where 督导时间 >= '" + tbStartTime.Value + "' and 督导时间 <='" + tbEndTime.Value + "'  and 督导项目名 Like '%教学评课%'";  //OK
        //}
        //else
        //{
        //    sql = "select count(*)  from OthProg where 督导时间 >= '" + tbStartTime.Value + "' and 督导时间 <='" + tbEndTime.Value + "' and 督导项目名 Like '%教学评课%' and 系部='" + sDemp.Value.Trim().ToString() + "'";  //OK 
        //}

        if (sDemp.Value.Trim().ToString() == "全院")
            {
                sql = "select count(*)  from TKJL where 听课日期 >= '" + tbStartTime.Value + "' and 听课日期 <='" + tbEndTime.Value + "'  and 综合评价 IS NOT NULL";  //OK
            }
            else
            {
                sql = "select count(*)  from TKJL where 听课日期 >= '" + tbStartTime.Value + "' and 听课日期 <='" + tbEndTime.Value + "' and 综合评价 IS NOT NULL and 系部='" + sDemp.Value.Trim().ToString() + "'";  //OK 
            }
        DDPK.Text = myud.DispLineCountNum(sql).ToString();
        //4.督学情况
        if (sDemp.Value.Trim().ToString() == "全院")
        {
            sql = "select count(*)  from OthProg where 督导时间 >= '" + tbStartTime.Value + "' and 督导时间 <='" + tbEndTime.Value + "'  and 督导项目名 Like '%督学情况%'";  //OK
        }
        else
        {
            sql = "select count(*)  from OthProg where 督导时间 >= '" + tbStartTime.Value + "' and 督导时间 <='" + tbEndTime.Value + "' and 督导项目名 Like '%督学情况%' and 系部='" + sDemp.Value.Trim().ToString() + "'";  //OK 
        }
        DXCS.Text = myud.DispLineCountNum(sql).ToString();
        //5.教学检查
        if (sDemp.Value.Trim().ToString() == "全院")
        {
            sql = "select count(*)  from OthProg where 督导时间 >= '" + tbStartTime.Value + "' and 督导时间 <='" + tbEndTime.Value + "'  and 督导项目名 Like '%教学检查%'";  //OK
        }
        else
        {
            sql = "select count(*)  from OthProg where 督导时间 >= '" + tbStartTime.Value + "' and 督导时间 <='" + tbEndTime.Value + "' and 督导项目名 Like '%教学检查%' and 系部='" + sDemp.Value.Trim().ToString() + "'";  //OK 
        }
        JXJC.Text = myud.DispLineCountNum(sql).ToString();
        //7.项目评审
        if (sDemp.Value.Trim().ToString() == "全院")
        {
            sql = "select count(*)  from OthProg where 督导时间 >= '" + tbStartTime.Value + "' and 督导时间 <='" + tbEndTime.Value + "'  and 督导项目名 Like '%项目评审%'";  //OK
        }
        else
        {
            sql = "select count(*)  from OthProg where 督导时间 >= '" + tbStartTime.Value + "' and 督导时间 <='" + tbEndTime.Value + "' and 督导项目名 Like '%项目评审%' and 系部='" + sDemp.Value.Trim().ToString() + "'";  //OK 
        }
        XMPH.Text = myud.DispLineCountNum(sql).ToString();
        //8.培训交流
        if (sDemp.Value.Trim().ToString() == "全院")
        {
            sql = "select count(*)  from OthProg where 督导时间 >= '" + tbStartTime.Value + "' and 督导时间 <='" + tbEndTime.Value + "'  and 督导项目名 Like '%培训交流%'";  //OK
        }
        else
        {
            sql = "select count(*)  from OthProg where 督导时间 >= '" + tbStartTime.Value + "' and 督导时间 <='" + tbEndTime.Value + "' and 督导项目名 Like '%培训交流%' and 系部='" + sDemp.Value.Trim().ToString() + "'";  //OK 
        }
        PXJL.Text = myud.DispLineCountNum(sql).ToString();
        //9.其它
        if (sDemp.Value.Trim().ToString() == "全院")
        {
            sql = "select count(*)  from OthProg where 督导时间 >= '" + tbStartTime.Value + "' and 督导时间 <='" + tbEndTime.Value + "'  and 督导项目名 Like '%其它%'";  //OK
        }
        else
        {
            sql = "select count(*)  from OthProg where 督导时间 >= '" + tbStartTime.Value + "' and 督导时间 <='" + tbEndTime.Value + "' and 督导项目名 Like '%其它%' and 系部='" + sDemp.Value.Trim().ToString() + "'";  //OK 
        }
        Othe.Text = myud.DispLineCountNum(sql).ToString();
    }


    /// <summary>
    /// 以行显示，每一行下划线显示 显示统计结果(总数)
    /// </summary>
    /// <param name="SQL"></param>
    /// <returns></returns>
    //public StringBuilder DispLineCount(string SQL)
    //{  //调用
    //    //DateTime startTime = Convert.ToDateTime("2017-9-1");
    //    //DateTime endTime = Convert.ToDateTime("2017-9-11");
    //    //string sql = "select count(*) as  总计 from TKJL where 听课日期 >= #2017-9-1# and 听课日期 <=#2017-9-10#";  //OK
    //    StringBuilder sb = new StringBuilder();
    //    // sb.Append("\n <link href=\"css/global.css\"  rel=\"stylesheet\" type=\"text/css\" />");

    //    string sql = SQL;
    //    OleDbConnection dtConn = new OleDbConnection(connectionString);
    //    OleDbDataAdapter dtCmd = new OleDbDataAdapter(sql, dtConn);
    //    DataSet dtSet = new DataSet();
    //    dtCmd.Fill(dtSet);

    //    DataTableReader objreader = dtSet.CreateDataReader();
    //    int i = 0, j = 0;
    //    sb.Append("\n <table width=\"900\" align=\"center\" class=\"lh28 font_st\">");
    //    sb.Append("\n 					<tr style=\"background-image: url('images/list_line2-1.gif')\">");

    //    for (i = 0; i <= objreader.FieldCount - 1; i++)
    //    {
    //        sb.Append(" <td  align=\"center\" nowrap=\"nowrap\" style=\"background-color:#3F98E9; color:#FFFFFF; height:30px;\"><b>" + objreader.GetName(i) + "&nbsp;</b>");
    //    }
    //    sb.Append("\n </tr>");

    //    while (objreader.Read())
    //    {
    //        j = j + 1;
    //        sb.Append("\n 					<tr class=\"list_bline1\">");
    //        for (i = 0; i <= objreader.FieldCount - 1; i++)
    //        {
    //            sb.Append("\n  <td style=\"height:30px; border-bottom-width: 1px;	border-bottom-style: dotted;	border-bottom-color: #C1CCD5; \">" + objreader.GetValue(i).ToString() + "&nbsp;</td>");
    //        }
    //        sb.Append("\n </tr>");
    //    }
    //    sb.Append("</table>");
    //    return sb;
    //}
    protected void Button3_Click(object sender, EventArgs e)
    {
        HttpContext.Current.Response.Buffer = true;
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.Charset = "gb2312";
        HttpContext.Current.Response.ClearContent();
        HttpContext.Current.Response.ClearHeaders();
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
        HttpContext.Current.Response.ContentType = "application/ms-word";
        HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=fileDown.doc");
        //关闭控件的视图状态  ,如果仍然为true，RenderControl将启用页的跟踪功能，存储与控件有关的跟踪信息
        this.EnableViewState = false;
        //将要下载的页面输出到HtmlWriter  
        System.IO.StringWriter writer = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlWriter = new System.Web.UI.HtmlTextWriter(writer);
        this.RenderControl(htmlWriter);
        //提取要输出的内容  
        string pageHtml = writer.ToString();
        int startIndex = pageHtml.IndexOf("<div style=\"margin: 0 auto;\" id=\"mainContent\">");
        int endIndex = pageHtml.LastIndexOf("</div>");
        int lenth = endIndex - startIndex;
        pageHtml = pageHtml.Substring(startIndex, lenth);
        //输出
        HttpContext.Current.Response.Write(pageHtml.ToString());
        HttpContext.Current.Response.End();  
    }
}
//SQL
//string datemin=DateTime.Parse("2009年5月1日 00：00");
//string datemax=DateTime.Parse("20009年7月1日 12：12");
//然后写sql语句如下：
//string strsql="select * from T_Chart where F_Date>='"+datemin+"' and F_Date<='"+datemax+"'";