本文展示了如何在ASP.NET中使用DataGrid控件进行数据展示，并提供了编辑功能。用户可以通过下拉列表选择查询时段，点击查询按钮获取数据。数据显示包括商家名称、市场负责人、排期名称等信息，以及各项统计数据。每个数据项还提供了编辑功能，用户可以编辑备注并保存或取消更改。
1、数据空间里的操作
截图
点击编辑
出现输入框 取消和确定
前台代码
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Ad_EffectDetail.aspx.cs" Inherits="Admin_Ad_EffectDetail" %>
<HTML>
<HEAD>
<title>创意效果</title>
<LINK href="../Style.css" type="text/css" rel="stylesheet">
</HEAD>
<body leftMargin="0" topMargin="0" rightMargin="0"
MS_POSITIONING="GridLayout" align="center">
<form id="Form1" method="post" runat="server">
<TABLE id="Table1" style="WIDTH: 70%" cellSpacing="0" cellPadding="0" border="0" align="center">
<TR>
<td align="center"> <FONT class="title">创意效果</FONT></td>
</TR>
<TR>
<td> <FONT class="title">查询时段：</FONT>
<asp:DropDownList ID="ddlDate" runat="server">
<asp:ListItem Text="前7天" Value="7" Selected="True"></asp:ListItem>
<asp:ListItem Text="前40天" Value="40"></asp:ListItem>
</asp:DropDownList>
<asp:Button ID="btnQuery" runat="server" Text="查询" /></td>
</TR>
<tr>
<td align="center">
<table cellspacing="0" cellpadding="0" rules="all" bordercolor="#DDE1EC" border="1" id="Table3" style="border-color:#DDE1EC;border-width:1px;border-style:solid;width:100%;border-collapse:collapse;">
<tr align="center" style="background-color:#ffffff;height:27px;">
<td style="font-weight:bold;">商家名称</td>
<td> <%=__HostName %></td>
</tr>
<tr align="center" style="background-color:#ffffff;height:27px;">
<td style="font-weight:bold;">市场负责</td>
<td> <%=__SalerName %></td>
</tr>
<tr align="center" style="background-color:#ffffff;height:27px;">
<td style="font-weight:bold;">排期名称</td>
<td> <%=__ArrangeName %></td>
</tr>
<tr align="center" style="background-color:#ffffff;height:27px;">
<td style="font-weight:bold;">支付形式</td>
<td> 联盟：<%=__ArrangePriceIn %>元， 站长：<%=__ArrangePriceOut %>元</td>
</tr>
</table>
<asp:datagrid id="dgShow" AllowSorting="True" Width="100%" BorderWidth="1px" CellPadding="0" AutoGenerateColumns="False"
ShowFooter="true" BorderColor="#dde1ec" CellSpacing="0" Runat="server" OnItemDataBound="dgShow_ItemDataBound">
<FooterStyle Font-Bold="True" HorizontalAlign="Center" VerticalAlign="Middle" BackColor="Silver"></FooterStyle>
<ItemStyle HorizontalAlign="Center" Height="25px" BackColor="White"></ItemStyle>
<HeaderStyle HorizontalAlign="Center" Height="27px" BackColor="#DDE1EC"></HeaderStyle>
<AlternatingItemStyle HorizontalAlign="Center" BackColor=''></AlternatingItemStyle>
<SelectedItemStyle HorizontalAlign="Center"></SelectedItemStyle>
<Columns>
<asp:BoundColumn DataField="accesstime" HeaderText="日期" ItemStyle-HorizontalAlign="Center" DataFormatString="{0:yyyy年MM月dd日}"></asp:BoundColumn>
<asp:BoundColumn DataField="clickrate" HeaderText="点击率" ItemStyle-HorizontalAlign="Center"></asp:BoundColumn>
<asp:BoundColumn DataField="regrate" HeaderText="注册率" ItemStyle-HorizontalAlign="Center"></asp:BoundColumn>
<asp:BoundColumn DataField="regcnt" HeaderText="注册数" ItemStyle-HorizontalAlign="Center"></asp:BoundColumn>
<asp:BoundColumn DataField="regcost" HeaderText="注册成本" ItemStyle-HorizontalAlign="Center"></asp:BoundColumn>
<asp:BoundColumn DataField="amount" HeaderText="日消耗" ItemStyle-HorizontalAlign="Center"></asp:BoundColumn>
<asp:TemplateColumn HeaderText="备注" ItemStyle-Width="300">
<ItemTemplate>
<%#DataBinder.Eval(Container.DataItem, "logcontent").ToString()%>
<span id="spanEdit<%#Convert.ToDateTime(DataBinder.Eval(Container.DataItem, "accesstime")).Day.ToString()%>" style="display:none">
<input id="logcontent<%#Convert.ToDateTime(DataBinder.Eval(Container.DataItem, "accesstime")).Day.ToString()%>" type="text" style="width:180px" />
<input type="button" value="确定" onclick="location.href='?adid=<%#DataBinder.Eval(Container.DataItem, "adid").ToString()%>&accesstime=<%#DataBinder.Eval(Container.DataItem, "accesstime","{0:yyyy-MM-dd}").ToString()%>&logcontent='+escape(document.getElementById('logcontent<%#Convert.ToDateTime(DataBinder.Eval(Container.DataItem, "accesstime")).Day.ToString()%>').value)" />
<input type="button" value="取消" onclick="document.getElementById('spanEdit<%#Convert.ToDateTime(DataBinder.Eval(Container.DataItem, "accesstime")).Day.ToString()%>').style.display='none';document.getElementById('spanShow<%#Convert.ToDateTime(DataBinder.Eval(Container.DataItem, "accesstime")).Day.ToString()%>').style.display='block';" />
</span>
<span id="spanShow<%#Convert.ToDateTime(DataBinder.Eval(Container.DataItem, "accesstime")).Day.ToString()%>" style="display:block">
<a href="#" onclick="document.getElementById('spanEdit<%#Convert.ToDateTime(DataBinder.Eval(Container.DataItem, "accesstime")).Day.ToString()%>').style.display='block';document.getElementById('spanShow<%#Convert.ToDateTime(DataBinder.Eval(Container.DataItem, "accesstime")).Day.ToString()%>').style.display='none';">编辑</a>
</span>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:datagrid>
</td>
</tr>
</TABLE>
</form>
</body>
</HTML>
后台代码：
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using DataOperation;
using UnionLib;
public partial class Admin_Ad_EffectDetail : UnionLib.UnionPage
{
public string __HostName = string.Empty;
public string __SalerName = string.Empty;
public string __ArrangeName = string.Empty;
public string __ArrangePriceIn = string.Empty;
public string __ArrangePriceOut = string.Empty;
protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString["adid"] == null)
{
return;
}
else
{
string strAdid = Request.QueryString["adid"].ToString();
if (Request.QueryString["accesstime"] != null && Request.QueryString["logcontent"] != null)
{
string strAccesstime = Request.QueryString["accesstime"].ToString();
string strLogcontent = Request.QueryString["logcontent"].ToString();
// 插入日志
UnionLib.Common com = new UnionLib.Common();
string strSql = string.Empty;
strSql = @"insert into ad_adlog (adid,logtime,logtype,logcontent)
(select @adid,to_date('@accesstime','yyyy-mm-dd'),13,'@logcontent' from dual)";
strSql = strSql.Replace("\r\n", " ");
strSql = strSql.Replace("@adid", strAdid);
strSql = strSql.Replace("@accesstime", strAccesstime);
strSql = strSql.Replace("@logcontent", strLogcontent);
com.ExeSql(strSql);
Response.Redirect("ad_effectdetail.aspx?adid=" + strAdid);
}
else
{
ShowArrangeInfo(strAdid);
ShowArrangeDetail(strAdid, ddlDate.SelectedValue);
}
}
}
protected void ShowArrangeInfo(string strAdid)
{
UnionLib.Common com = new UnionLib.Common();
DataTable dt = new DataTable();
string strSql = string.Empty;
strSql = @"select a.arrid,a.arrname,nvl(c.company,' ') company,nvl(d.salername,' ') salername,
max(replace(f.inpaymethod,'/1000')) inpaymethod,max(replace(f.outpaymethod,'/1000')) outpaymethod
from ad_adarrange a,ad_hostuser b,ad_host c,ad_union_saler d,ad_adset e,ad_statkind f
where a.hostid = b.hostid and b.hostid = c.hostid and b.saler = d.salerid
and a.arrid = e.arrid and e.adid = f.adid and e.adid=@adid
group by a.arrid,a.arrname,c.company,d.salername";
strSql = strSql.Replace("\r\n", " ");
strSql = strSql.Replace("@adid", strAdid);
dt = com.GetDataTable(strSql);
__HostName = dt.Rows[0]["company"].ToString();
__SalerName = dt.Rows[0]["salername"].ToString();
__ArrangeName = dt.Rows[0]["arrname"].ToString();
__ArrangePriceIn = dt.Rows[0]["inpaymethod"].ToString();
__ArrangePriceOut = dt.Rows[0]["outpaymethod"].ToString();
}
protected void ShowArrangeDetail(string strAdid,string strDate)
{
UnionLib.Common com = new UnionLib.Common();
DataTable dt = new DataTable();
string strSql = string.Empty;
strSql = @"select a.adid,a.title,a.accesstime,
decode(d.shapeid,1,
decode(decode(a.arrivalcnt,0,0,a.clickcnt),0,'-',to_char(round(a.clickcnt/a.arrivalcnt,4)*100,'fm99990.00')||'%'),
2,decode(decode(a.showcnt,0,0,a.windowcnt),0,'-',to_char(round(a.windowcnt/a.showcnt,4)*100,'fm99990.00')||'%'),
3,decode(decode(a.showcnt,0,0,a.windowcnt),0,'-',to_char(round(a.windowcnt/a.showcnt,4)*100,'fm99990.00')||'%'),
decode(c.arrtype,4,decode(decode(a.arrivalcnt,0,0,a.clickcnt),0,'-',to_char(round(a.clickcnt/a.arrivalcnt,4)*100,'fm99990.00')||'%'),
decode(decode(a.windowcnt,0,0,a.arrivalcnt),0,'-',to_char(round(a.arrivalcnt/a.windowcnt,4)*100,'fm99990.00')||'%'))
) clickrate,
decode(decode(a.arrivalcnt,0,0,a.regcnt),0,'-',to_char(round(a.regcnt/a.arrivalcnt,4)*100,'fm99990.00')||'%') regrate,
decode(a.regcnt,0,'-',to_char(round(a.amount/a.regcnt,2),'fm99990.00')) regcost,
a.regcnt,
to_char(a.amount,'fm99990.00') amount,
nvl(b.logcontent,' ') logcontent
from ad_adeffect a,
(
select adid,logtime,ltrim(max(sys_connect_by_path(logcontent,',')),',') logcontent
from
(
select a.adid,to_date(to_char(b.logtime,'yyyy-mm-dd'),'yyyy-mm-dd') logtime,b.logcontent,
(row_number() over(partition by a.arrid,to_date(to_char(b.logtime,'yyyy-mm-dd'),'yyyy-mm-dd') order by a.arrid,b.logtime desc)) numid
from ad_adset a,ad_adlog b
where a.adid = b.adid and b.logtype=13 and b.logtime>=trunc(sysdate-@date)
)
connect by adid = prior adid and logtime = prior logtime and numid - 1 = prior numid
group by adid,logtime
) b,ad_adarrange c,
(
select a.shapeid,a.shapename,b.typeid,b.name from ad_shape a,ad_adtypeput b,ad_shaperelation c
where a.shapeid=c.shapeid
and c.adtypeid=b.typeid
) d
where a.accesstime>=trunc(sysdate-@date) and a.adid=@adid
and a.adtype=d.typeid
and a.adid = b.adid(+) and a.arrid=c.arrid(+)
and a.accesstime = b.logtime(+)
order by a.accesstime";
strSql = strSql.Replace("\r\n", " ");
strSql = strSql.Replace("@adid", strAdid);
strSql = strSql.Replace("@date", strDate);
dt = com.GetDataTable(strSql);
// 给数据集赋值
this.dgShow.DataSource = dt;
this.dgShow.DataBind();
}
decimal avg_clickrate = 0, avg_regrate = 0, avg_regcnt = 0, avg_regcost = 0, avg_amount = 0;
int records_clickrate = 0, records_regrate = 0, records_regcnt = 0, records_regcost = 0, records_amount = 0;
protected void dgShow_ItemDataBound(object sender, DataGridItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
try
{
avg_clickrate += Convert.ToDecimal(e.Item.Cells[1].Text.Replace("%", ""));
records_clickrate++;
}
catch
{
avg_clickrate += 0;
}
try
{
avg_regrate += Convert.ToDecimal(e.Item.Cells[2].Text.Replace("%", ""));
records_regrate++;
}
catch
{
avg_regrate += 0;
}
try
{
avg_regcnt += Convert.ToDecimal(e.Item.Cells[3].Text);
records_regcnt++;
}
catch
{
avg_regcnt += 0;
}
try
{
avg_regcost += Convert.ToDecimal(e.Item.Cells[4].Text);
records_regcost++;
}
catch
{
avg_regcost += 0;
}
try
{
avg_amount += Convert.ToDecimal(e.Item.Cells[5].Text);
records_amount++;
}
catch
{
avg_amount += 0;
}
}
if (e.Item.ItemType == ListItemType.Footer)
{
e.Item.Cells[0].Text = "日均";
if (records_clickrate > 0)
{
e.Item.Cells[1].Text = Convert.ToDecimal(avg_clickrate / records_clickrate).ToString("0.000") + "%";
}
if (records_regrate > 0)
{
e.Item.Cells[2].Text = Convert.ToDecimal(avg_regrate / records_regrate).ToString("0.000") + "%";
}
if (records_regcnt > 0)
{
e.Item.Cells[3].Text = Convert.ToDecimal(avg_regcnt / records_regcnt).ToString("0.000");
}
if (records_regcost > 0)
{
e.Item.Cells[4].Text = Convert.ToDecimal(avg_regcost / records_regcost).ToString("0.000");
}
if (records_amount > 0)
{
e.Item.Cells[5].Text = Convert.ToDecimal(avg_amount / records_amount).ToString("0.000");
}
}
}
}
截图
点击编辑
出现输入框 取消和确定
前台代码
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Ad_EffectDetail.aspx.cs" Inherits="Admin_Ad_EffectDetail" %>
<HTML>
<HEAD>
<title>创意效果</title>
<LINK href="../Style.css" type="text/css" rel="stylesheet">
</HEAD>
<body leftMargin="0" topMargin="0" rightMargin="0"
MS_POSITIONING="GridLayout" align="center">
<form id="Form1" method="post" runat="server">
<TABLE id="Table1" style="WIDTH: 70%" cellSpacing="0" cellPadding="0" border="0" align="center">
<TR>
<td align="center"> <FONT class="title">创意效果</FONT></td>
</TR>
<TR>
<td> <FONT class="title">查询时段：</FONT>
<asp:DropDownList ID="ddlDate" runat="server">
<asp:ListItem Text="前7天" Value="7" Selected="True"></asp:ListItem>
<asp:ListItem Text="前40天" Value="40"></asp:ListItem>
</asp:DropDownList>
<asp:Button ID="btnQuery" runat="server" Text="查询" /></td>
</TR>
<tr>
<td align="center">
<table cellspacing="0" cellpadding="0" rules="all" bordercolor="#DDE1EC" border="1" id="Table3" style="border-color:#DDE1EC;border-width:1px;border-style:solid;width:100%;border-collapse:collapse;">
<tr align="center" style="background-color:#ffffff;height:27px;">
<td style="font-weight:bold;">商家名称</td>
<td> <%=__HostName %></td>
</tr>
<tr align="center" style="background-color:#ffffff;height:27px;">
<td style="font-weight:bold;">市场负责</td>
<td> <%=__SalerName %></td>
</tr>
<tr align="center" style="background-color:#ffffff;height:27px;">
<td style="font-weight:bold;">排期名称</td>
<td> <%=__ArrangeName %></td>
</tr>
<tr align="center" style="background-color:#ffffff;height:27px;">
<td style="font-weight:bold;">支付形式</td>
<td> 联盟：<%=__ArrangePriceIn %>元， 站长：<%=__ArrangePriceOut %>元</td>
</tr>
</table>
<asp:datagrid id="dgShow" AllowSorting="True" Width="100%" BorderWidth="1px" CellPadding="0" AutoGenerateColumns="False"
ShowFooter="true" BorderColor="#dde1ec" CellSpacing="0" Runat="server" OnItemDataBound="dgShow_ItemDataBound">
<FooterStyle Font-Bold="True" HorizontalAlign="Center" VerticalAlign="Middle" BackColor="Silver"></FooterStyle>
<ItemStyle HorizontalAlign="Center" Height="25px" BackColor="White"></ItemStyle>
<HeaderStyle HorizontalAlign="Center" Height="27px" BackColor="#DDE1EC"></HeaderStyle>
<AlternatingItemStyle HorizontalAlign="Center" BackColor=''></AlternatingItemStyle>
<SelectedItemStyle HorizontalAlign="Center"></SelectedItemStyle>
<Columns>
<asp:BoundColumn DataField="accesstime" HeaderText="日期" ItemStyle-HorizontalAlign="Center" DataFormatString="{0:yyyy年MM月dd日}"></asp:BoundColumn>
<asp:BoundColumn DataField="clickrate" HeaderText="点击率" ItemStyle-HorizontalAlign="Center"></asp:BoundColumn>
<asp:BoundColumn DataField="regrate" HeaderText="注册率" ItemStyle-HorizontalAlign="Center"></asp:BoundColumn>
<asp:BoundColumn DataField="regcnt" HeaderText="注册数" ItemStyle-HorizontalAlign="Center"></asp:BoundColumn>
<asp:BoundColumn DataField="regcost" HeaderText="注册成本" ItemStyle-HorizontalAlign="Center"></asp:BoundColumn>
<asp:BoundColumn DataField="amount" HeaderText="日消耗" ItemStyle-HorizontalAlign="Center"></asp:BoundColumn>
<asp:TemplateColumn HeaderText="备注" ItemStyle-Width="300">
<ItemTemplate>
<%#DataBinder.Eval(Container.DataItem, "logcontent").ToString()%>
<span id="spanEdit<%#Convert.ToDateTime(DataBinder.Eval(Container.DataItem, "accesstime")).Day.ToString()%>" style="display:none">
<input id="logcontent<%#Convert.ToDateTime(DataBinder.Eval(Container.DataItem, "accesstime")).Day.ToString()%>" type="text" style="width:180px" />
<input type="button" value="确定" onclick="location.href='?adid=<%#DataBinder.Eval(Container.DataItem, "adid").ToString()%>&accesstime=<%#DataBinder.Eval(Container.DataItem, "accesstime","{0:yyyy-MM-dd}").ToString()%>&logcontent='+escape(document.getElementById('logcontent<%#Convert.ToDateTime(DataBinder.Eval(Container.DataItem, "accesstime")).Day.ToString()%>').value)" />
<input type="button" value="取消" onclick="document.getElementById('spanEdit<%#Convert.ToDateTime(DataBinder.Eval(Container.DataItem, "accesstime")).Day.ToString()%>').style.display='none';document.getElementById('spanShow<%#Convert.ToDateTime(DataBinder.Eval(Container.DataItem, "accesstime")).Day.ToString()%>').style.display='block';" />
</span>
<span id="spanShow<%#Convert.ToDateTime(DataBinder.Eval(Container.DataItem, "accesstime")).Day.ToString()%>" style="display:block">
<a href="#" onclick="document.getElementById('spanEdit<%#Convert.ToDateTime(DataBinder.Eval(Container.DataItem, "accesstime")).Day.ToString()%>').style.display='block';document.getElementById('spanShow<%#Convert.ToDateTime(DataBinder.Eval(Container.DataItem, "accesstime")).Day.ToString()%>').style.display='none';">编辑</a>
</span>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:datagrid>
</td>
</tr>
</TABLE>
</form>
</body>
</HTML>
后台代码：
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using DataOperation;
using UnionLib;
public partial class Admin_Ad_EffectDetail : UnionLib.UnionPage
{
public string __HostName = string.Empty;
public string __SalerName = string.Empty;
public string __ArrangeName = string.Empty;
public string __ArrangePriceIn = string.Empty;
public string __ArrangePriceOut = string.Empty;
protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString["adid"] == null)
{
return;
}
else
{
string strAdid = Request.QueryString["adid"].ToString();
if (Request.QueryString["accesstime"] != null && Request.QueryString["logcontent"] != null)
{
string strAccesstime = Request.QueryString["accesstime"].ToString();
string strLogcontent = Request.QueryString["logcontent"].ToString();
// 插入日志
UnionLib.Common com = new UnionLib.Common();
string strSql = string.Empty;
strSql = @"insert into ad_adlog (adid,logtime,logtype,logcontent)
(select @adid,to_date('@accesstime','yyyy-mm-dd'),13,'@logcontent' from dual)";
strSql = strSql.Replace("\r\n", " ");
strSql = strSql.Replace("@adid", strAdid);
strSql = strSql.Replace("@accesstime", strAccesstime);
strSql = strSql.Replace("@logcontent", strLogcontent);
com.ExeSql(strSql);
Response.Redirect("ad_effectdetail.aspx?adid=" + strAdid);
}
else
{
ShowArrangeInfo(strAdid);
ShowArrangeDetail(strAdid, ddlDate.SelectedValue);
}
}
}
protected void ShowArrangeInfo(string strAdid)
{
UnionLib.Common com = new UnionLib.Common();
DataTable dt = new DataTable();
string strSql = string.Empty;
strSql = @"select a.arrid,a.arrname,nvl(c.company,' ') company,nvl(d.salername,' ') salername,
max(replace(f.inpaymethod,'/1000')) inpaymethod,max(replace(f.outpaymethod,'/1000')) outpaymethod
from ad_adarrange a,ad_hostuser b,ad_host c,ad_union_saler d,ad_adset e,ad_statkind f
where a.hostid = b.hostid and b.hostid = c.hostid and b.saler = d.salerid
and a.arrid = e.arrid and e.adid = f.adid and e.adid=@adid
group by a.arrid,a.arrname,c.company,d.salername";
strSql = strSql.Replace("\r\n", " ");
strSql = strSql.Replace("@adid", strAdid);
dt = com.GetDataTable(strSql);
__HostName = dt.Rows[0]["company"].ToString();
__SalerName = dt.Rows[0]["salername"].ToString();
__ArrangeName = dt.Rows[0]["arrname"].ToString();
__ArrangePriceIn = dt.Rows[0]["inpaymethod"].ToString();
__ArrangePriceOut = dt.Rows[0]["outpaymethod"].ToString();
}
protected void ShowArrangeDetail(string strAdid,string strDate)
{
UnionLib.Common com = new UnionLib.Common();
DataTable dt = new DataTable();
string strSql = string.Empty;
strSql = @"select a.adid,a.title,a.accesstime,
decode(d.shapeid,1,
decode(decode(a.arrivalcnt,0,0,a.clickcnt),0,'-',to_char(round(a.clickcnt/a.arrivalcnt,4)*100,'fm99990.00')||'%'),
2,decode(decode(a.showcnt,0,0,a.windowcnt),0,'-',to_char(round(a.windowcnt/a.showcnt,4)*100,'fm99990.00')||'%'),
3,decode(decode(a.showcnt,0,0,a.windowcnt),0,'-',to_char(round(a.windowcnt/a.showcnt,4)*100,'fm99990.00')||'%'),
decode(c.arrtype,4,decode(decode(a.arrivalcnt,0,0,a.clickcnt),0,'-',to_char(round(a.clickcnt/a.arrivalcnt,4)*100,'fm99990.00')||'%'),
decode(decode(a.windowcnt,0,0,a.arrivalcnt),0,'-',to_char(round(a.arrivalcnt/a.windowcnt,4)*100,'fm99990.00')||'%'))
) clickrate,
decode(decode(a.arrivalcnt,0,0,a.regcnt),0,'-',to_char(round(a.regcnt/a.arrivalcnt,4)*100,'fm99990.00')||'%') regrate,
decode(a.regcnt,0,'-',to_char(round(a.amount/a.regcnt,2),'fm99990.00')) regcost,
a.regcnt,
to_char(a.amount,'fm99990.00') amount,
nvl(b.logcontent,' ') logcontent
from ad_adeffect a,
(
select adid,logtime,ltrim(max(sys_connect_by_path(logcontent,',')),',') logcontent
from
(
select a.adid,to_date(to_char(b.logtime,'yyyy-mm-dd'),'yyyy-mm-dd') logtime,b.logcontent,
(row_number() over(partition by a.arrid,to_date(to_char(b.logtime,'yyyy-mm-dd'),'yyyy-mm-dd') order by a.arrid,b.logtime desc)) numid
from ad_adset a,ad_adlog b
where a.adid = b.adid and b.logtype=13 and b.logtime>=trunc(sysdate-@date)
)
connect by adid = prior adid and logtime = prior logtime and numid - 1 = prior numid
group by adid,logtime
) b,ad_adarrange c,
(
select a.shapeid,a.shapename,b.typeid,b.name from ad_shape a,ad_adtypeput b,ad_shaperelation c
where a.shapeid=c.shapeid
and c.adtypeid=b.typeid
) d
where a.accesstime>=trunc(sysdate-@date) and a.adid=@adid
and a.adtype=d.typeid
and a.adid = b.adid(+) and a.arrid=c.arrid(+)
and a.accesstime = b.logtime(+)
order by a.accesstime";
strSql = strSql.Replace("\r\n", " ");
strSql = strSql.Replace("@adid", strAdid);
strSql = strSql.Replace("@date", strDate);
dt = com.GetDataTable(strSql);
// 给数据集赋值
this.dgShow.DataSource = dt;
this.dgShow.DataBind();
}
decimal avg_clickrate = 0, avg_regrate = 0, avg_regcnt = 0, avg_regcost = 0, avg_amount = 0;
int records_clickrate = 0, records_regrate = 0, records_regcnt = 0, records_regcost = 0, records_amount = 0;
protected void dgShow_ItemDataBound(object sender, DataGridItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
try
{
avg_clickrate += Convert.ToDecimal(e.Item.Cells[1].Text.Replace("%", ""));
records_clickrate++;
}
catch
{
avg_clickrate += 0;
}
try
{
avg_regrate += Convert.ToDecimal(e.Item.Cells[2].Text.Replace("%", ""));
records_regrate++;
}
catch
{
avg_regrate += 0;
}
try
{
avg_regcnt += Convert.ToDecimal(e.Item.Cells[3].Text);
records_regcnt++;
}
catch
{
avg_regcnt += 0;
}
try
{
avg_regcost += Convert.ToDecimal(e.Item.Cells[4].Text);
records_regcost++;
}
catch
{
avg_regcost += 0;
}
try
{
avg_amount += Convert.ToDecimal(e.Item.Cells[5].Text);
records_amount++;
}
catch
{
avg_amount += 0;
}
}
if (e.Item.ItemType == ListItemType.Footer)
{
e.Item.Cells[0].Text = "日均";
if (records_clickrate > 0)
{
e.Item.Cells[1].Text = Convert.ToDecimal(avg_clickrate / records_clickrate).ToString("0.000") + "%";
}
if (records_regrate > 0)
{
e.Item.Cells[2].Text = Convert.ToDecimal(avg_regrate / records_regrate).ToString("0.000") + "%";
}
if (records_regcnt > 0)
{
e.Item.Cells[3].Text = Convert.ToDecimal(avg_regcnt / records_regcnt).ToString("0.000");
}
if (records_regcost > 0)
{
e.Item.Cells[4].Text = Convert.ToDecimal(avg_regcost / records_regcost).ToString("0.000");
}
if (records_amount > 0)
{
e.Item.Cells[5].Text = Convert.ToDecimal(avg_amount / records_amount).ToString("0.000");
}
}
}
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
