做动态报表

来源：互联网发布：php缓存编辑：程序博客网时间：2024/04/30 00:59

前台： <asp:Literal ID="Literal1" runat="server"></asp:Literal>

后台代码

int projectid = 0;
protected void Page_Load(object sender, EventArgs e)
{
projectid = Convert.ToInt32(Request.QueryString["project_id"]);
if (!IsPostBack)
{
this.txtCreatedt.Value = DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00";
this.txtCreatedto.Value = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string sql1 = "select id from tbNatureDefine where project_id=" + projectid + " and nature_name='售前'";// 查出售前的id

int prodt =Convert.ToInt32( SqlHelper.SqlHelper.ExecuteScalar(ConfigurationManager.ConnectionStrings["CommonEcrmDBConnectionString"].ToString(), CommandType.Text, sql1));
if (prodt!=0)
{
DataTable dtbypid = getprodateMethod(prodt);//查出pid=售前id 结果是产品，政策等

if (dtbypid.Rows.Count>=1)
{
StringBuilder htm = new StringBuilder();
htm.AppendLine("<table border='1'><tr><td colspan='30' align='center' >售前</tr><tr>");
htm.AppendLine("<td rowspan='2' align='center'>产品sku</td>");
htm.AppendLine("<td rowspan='2' align='center'>产品名称</td>");

foreach (DataRow item in dtbypid.Rows)
{
DataTable dtsob = getprodateMethod(Convert.ToInt32(item["id"]));//质量，颜色等的count

htm.AppendLine(string.Format("<td colspan='{0}' align='center'>{1}</td>",
dtsob.Rows.Count,
item["nature_name"].ToString()) );//添加列如产品，政策等
}

htm.AppendLine("</tr>");
htm.AppendLine("<tr>");

foreach (DataRow dritem in dtbypid.Rows)
{
DataTable dtsobitem = getprodateMethod(Convert.ToInt32(dritem["id"]));// 通过二级列名如产品查询三级列名结果是：质量，颜色等

foreach (DataRow thres in dtsobitem.Rows)
{

htm.AppendLine( string.Format("<td>{0}</td>",thres["nature_name"].ToString()));// 添加三级列名

}
}
htm.AppendLine("</tr>");

string skusql = @"select a.product_id as product_id , a.product_name as product_name from tbHistoryNatureMain as a
inner join tbCustContactHistory as b on a.history_id=b.id
inner join tbcustomer as c on b.cust_id=c.id
where b.project_id="+projectid+" and b.category='咨询'"+
" and (b.create_dt between '"+txtCreatedt.Value+"' and '"+txtCreatedto.Value+"')"+
"group by a.product_id,a.product_name"; //查出第一、二列 sku name 并根据此分组

DataTable dtsku = SqlHelper.SqlHelper.ExecuteDataset(ConfigurationManager.ConnectionStrings["CommonEcrmDBConnectionString"].ToString(), CommandType.Text, skusql).Tables[0];

foreach (DataRow drskuitem in dtsku.Rows)
{

htm.AppendLine("<tr>");//添加一二列如下：
htm.AppendLine(string.Format("<td>{0}</td>", drskuitem["product_id"].ToString()));
htm.AppendLine(string.Format("<td>{0}</td>", drskuitem["product_name"].ToString()));

string caseidsql = @"select a.id as id from tbHistoryNatureMain as a
inner join tbCustContactHistory as b on a.history_id=b.id
inner join tbcustomer as c on b.cust_id=c.id
where b.project_id=" + projectid + " and b.category='咨询'" +
" and a.product_id='"+ drskuitem["product_id"].ToString()+"'"+
" and a.product_name='"+drskuitem["product_name"].ToString()+"'"+
"group by a.id"; //根据一二列查出所包含的 id

DataTable dtcaseid = SqlHelper.SqlHelper.ExecuteDataset(ConfigurationManager.ConnectionStrings["CommonEcrmDBConnectionString"].ToString(), CommandType.Text, caseidsql).Tables[0];

string strsomecaseid=getstr(dtcaseid);// 循环出id 并拼接如 9975,6958，...

DataTable dtbypids = getprodateMethod(prodt);//查出产品，政策等

foreach (DataRow item in dtbypids.Rows)//循环产品，政策
{
DataTable dtsobitems = getprodateMethod(Convert.ToInt32(item["id"]));// 查出质量，颜色等

foreach (DataRow thresome in dtsobitems.Rows)// 循环。。。
{
// 根据条件查出count
string countsql = "select count(*) from v_tbNatureValue where category='咨询' and ppname='售前' and " +

" pname='" + item["nature_name"].ToString() + "' and nature_name='" + thresome["nature_name"].ToString() + "' and case_id in(" + strsomecaseid + ")";

int count = Convert.ToInt32(SqlHelper.SqlHelper.ExecuteScalar(ConfigurationManager.ConnectionStrings["CommonEcrmDBConnectionString"].ToString(), CommandType.Text, countsql));

int num = count == null ? 0 : count;
htm.AppendLine(
string.Format("<td align='center'>{0}</td>",//填入count
num));
}
}

htm.AppendLine("</tr>");

}

htm.AppendLine("</table>");
this.Literal1.Text = htm.ToString();

}
}



}

private string getstr(DataTable dtcaseid)
{
string strcaseid = "";
foreach (DataRow item in dtcaseid.Rows)
{
strcaseid += "'" + item["id"].ToString() + "',";
}

if (strcaseid != "")
{
strcaseid = strcaseid.Substring(0, strcaseid.Length - 1);
}
return strcaseid;
}


//根据pid 列名
private DataTable getprodateMethod(int id)
{
string sql3 = "select * from tbNatureDefine where pid=" + id + " and project_id=" + projectid;
DataTable dts = SqlHelper.SqlHelper.ExecuteDataset(ConfigurationManager.ConnectionStrings["CommonEcrmDBConnectionString"].ToString(), CommandType.Text, sql3).Tables[0];
return dts;

}