既然是统计查询，第一肯定是要统计
public DataSet GetStiCount(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select TCSTM.CNAME as '区',TWORDS.[NAME] as '反映类型', sum(ALLCOUNT) as '受理案件数' ,sum( OCOUNT) as '已解决' , sum(NCOUNT) as '未解决' from ");
strSql.Append(" ( select CUSTOMERID,INFOTYPECODE,COUNT(*) AS ALLCOUNT,0 AS OCOUNT,0 AS NCOUNT ");
strSql.Append(" FROM T_CASEINFO ");
strSql.Append(" where 1=1" + strWhere);
strSql.Append(" GROUP BY INFOTYPECODE,CUSTOMERID ");
strSql.Append(" UNIon All ");
strSql.Append("SELECT CUSTOMERID,INFOTYPECODE,0 AS ALLCOUNT,COUNT(*) AS OCOUNT,0 AS NCOUNT ");
strSql.Append(" FROM T_CASEINFO ");
strSql.Append(" WHERE 1=1"+strWhere );
strSql.Append(" and TAG<>'3' OR (ISSOLVING<>0 AND ISSOLVING<>1 ) ");
strSql.Append(" GROUP BY INFOTYPECODE,CUSTOMERID");
strSql.Append(" Union All ");
strSql.Append(" SELECT CUSTOMERID,INFOTYPECODE,0 AS ALLCOUNT,0 AS OCOUNT,COUNT(*) AS NCOUNT ");
strSql.Append(" FROM T_CASEINFO ");
strSql.Append(" WHERE 1=1 "+ strWhere );
strSql .Append (" and TAG='3' AND (ISSOLVING=0 OR ISSOLVING=1) ");
strSql.Append(" GROUP BY INFOTYPECODE,CUSTOMERID ");
strSql.Append(" )T_CaseInfo ");
strSql.Append(" LEFT JOIN (SELECT CUSTOMERID,CNAME FROM T_CUSTOMER) TCSTM ON TCSTM.CUSTOMERID = T_CaseInfo.CUSTOMERID ");
strSql.Append("LEFT JOIN (SELECT CODE,[NAME] FROM T_WORDS)TWORDS ON T_caseinfo.INFOTYPECODE=TWORDS.CODE ");
strSql.Append(" GROUP BY TWORDS.[NAME],TCSTM.CNAME");
strSql.Append(" order by TCSTM.CNAME,TWORDS.[NAME] desc ");
DataTable dtb = new DataTable();
return DbHelperSQL.Query(strSql.ToString());
}
这是统计，第二是有条件查询。
private string GetQueryStr()
{
StringBuilder sb = new StringBuilder();
sb.Append(this.dllCstmName.SelectedIndex.ToString() == "0" ? "" : " and CustomerID='" + this.dllCstmName.SelectedItem.Value.Trim() + "'");
//上报时间
sb.Append("and ReportTime between '" + Convert.ToDateTime(InputSTime.Text).ToString("yyyy-MM-dd") + " 00:00:00'" + " and '" + Convert.ToDateTime(InputETime.Text).ToString("yyyy-MM-dd") + " 23:23:59'");
if (tree1.Value!="")
{
if (tree1.Value != "-01")
{
sb.Append(" and InfoTypeCode like'" + this.tree1.Value.Trim() + "%'");
}
}
sb.Append(this.dllSolvingUser.SelectedIndex.ToString() == "0" ? "" : " and SolvingUser='" + this.dllSolvingUser.SelectedItem.Value.Trim() + "'");
return sb.ToString();
}
这就是有条件查询。
private void BindList()
{
string SQLAccept = GetQueryStr();
DataSet dsAccept = objCase.GetStiCount(SQLAccept);
gv.DataSource = dsAccept;
gv.DataBind();
Session["TongJi"] = dsAccept;
RowSpanGrid(gv, new int[] { 0 });
if (dsAccept.Tables[0].Rows.Count <= 0)
{
this.tool.Visible = false;
}
else
{
this.tool.Visible = true;
}
}
这是因为统计，所以不想出现太多的客户名称
protected void RowSpanGrid(GridView gdv, int[] arr)
{
for (int cellNum = 0; cellNum < arr.Length; cellNum++)
{
//对列 cellNum 进行合并，从左向右，由上而下
int i = 0, rowSpanNum = 1;
while (i < gdv.Rows.Count - 1)
{
GridViewRow gvr = gdv.Rows[i];
for (++i; i < gdv.Rows.Count; i++)
{
GridViewRow gvrNext = gdv.Rows[i];
//if (gvr.Cells[cellNum].Text == gvrNext.Cells[cellNum].Text)
if (gvr.Cells[cellNum].Text == gvrNext.Cells[cellNum].Text
&& gvrNext.Cells[cellNum].Text.Trim().Replace(" ", "").Length > 0)
{
gvrNext.Cells[cellNum].Visible = false;
rowSpanNum++;
}
else
{
gvr.Cells[cellNum].RowSpan = rowSpanNum;
rowSpanNum = 1;
break;
}
if (i == gdv.Rows.Count - 1)
{
gvr.Cells[cellNum].RowSpan = rowSpanNum;
}
}
}
}
}
