<%@ page contentType="text/html;charset=gb2312" %>
<%@ page import="zlx.*,java.util.*,java.text.SimpleDateFormat,java.text.*,java.lang.*,java.sql.*"%>
<html>
<head>
<link href="<%=request.getContextPath()%>/css/client.css" rel="stylesheet" type="text/css">
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
</head>
<%!
//函数:保留2位的四舍五入函数
public Double round( double v , int scale )
{
String temp= "#0.";
for ( int i = 0 ;i < scale; i ++ ) {
temp+= "0";
}
return Double.valueOf(new java.text.DecimalFormat(temp).format(v));
}
public String getIntValue(double dvalue)
{
String svalue=String.valueOf(dvalue);
int len=svalue.length();
return svalue.substring(0,len-2);
}
public String getIntValueD(Double dvalue)
{
String svalue=String.valueOf(dvalue);
int len=svalue.length();
return svalue.substring(0,len-2);
}
//从vector中取得数据,可根据自己业务规则修改此参数
public int getField(Vector v, int col,String showtime)
{
int ret = 0;
if (v == null || v.isEmpty())
return ret;
try {
// ret = (null == ((Vector) v.get(row)).get(col) ? defaultValue : ((Vector) v.get(row)).get(col).toString());
ret=0;
for(int i=0;i<v.size();i++){
if(Integer.parseInt(((Vector)v.elementAt(i)).elementAt(1).toString())==col && ((Vector)v.elementAt(i)).elementAt(0).toString().substring(0,8).equals(showtime))
{
ret=Integer.parseInt(((Vector)v.elementAt(i)).elementAt(2).toString());
}}
// return ret;
}
catch (Exception e) {}
return ret;
}
public double getMoney(Vector v,Vector vprice,String time)
{
double money=0;
int number=0;
double price=0;
for(int i=0;i<v.size();i++)
{
number=Integer.parseInt(((Vector) v.elementAt(i)).elementAt(2).toString());
price=getPrice(vprice,((Vector) v.elementAt(i)).elementAt(3).toString());
if(((Vector) v.elementAt(i)).elementAt(0).toString().equalsIgnoreCase(time))
{
money=money + number * price;
}
}
return money;
}
//主要就是这个函数,用resultset作为输入参数,得到包含数据的vector
public Vector execute(ResultSet rs)
{
Vector vr = new Vector();
Vector v = new Vector();
// ResultSet rs;
// Statement stmt;
try
{
// stmt = conn.createStatement();
for(int ii=0; rs.next(); vr.add(v))
{
v = new Vector();
int i = 1;
do
{
try
{
Object o = rs.getObject(i);
v.add(o);
}
catch(Exception e)
{
break;
}
i++;
} while(true);
}
rs.close();
// stmt.close();
}
catch(Exception e)
{
}
return vr;
}
%>
<%
String sqldate="select distinct req_ymon from v_etl_day where to_date(req_ymon,'yyyy-mm-dd')>=to_date('" +
bdate + "','yyyy-mm-dd') and to_date(req_ymon,'yyyy-mm-dd')<=to_date('" +
edate + "','yyyy-mm-dd')";
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn= DriverManager.getConnection("jdbc:oracle:thin:@11.11.11.11:1521:sensky","11","weytega");
PreparedStatement pstmt = conn.prepareStatement(sqldate);
ResultSet rs=null;
Vector vdata=new Vector();
Vector vdate=new Vector();
Vector vprice=new Vector();
Vector v=new Vector();
String sqlprice="select trade_code,price from tbl_partnerstat_trade";
try{
pstmt= conn.prepareStatement(sqldate);
rs=pstmt.executeQuery(sqldate);
vdate=execute(rs);
rs.close();
pstmt= conn.prepareStatement(sqlprice);
rs=pstmt.executeQuery(sqlprice);
vprice=execute(rs);
}
catch(Exception e){
out.println(e.toString());
}
%>
<body>
<table width="1100" valign="top">
<tr valign="top"><td>
<div id="by" valign="top"><font size=3><B>包月报表---自<%=bdate%>到<%=edate%> </B></font>
<table width="98%" align="center" cellpadding="2" cellspacing="1" bgcolor="#333333" valign="top">
<tr bgcolor="#FFFFFF">
<td bgcolor="#b1e1fe"><strong>来源</strong></td>
<td bgcolor="#b1e1fe"><strong>统计日期</strong></td>
<td bgcolor="#b1e1fe"><strong>上期存量</strong></td>
<td bgcolor="#b1e1fe"><div align="center"></div>
<div align="center"><strong>订购人数</strong></div></td>
<td bgcolor="#b1e1fe"><strong>退订用户</strong></td>
<td bgcolor="#b1e1fe"><strong>本期存量</strong></td>
<td bgcolor="#b1e1fe"><div align="center"><strong>可收费</strong></div>
<div align="center"><strong> 用户</strong></div></td>
<td bgcolor="#b1e1fe"><strong>收入</strong></td>
<td bgcolor="#b1e1fe"><div align="center"><strong>运营商</strong></div>
<div align="center"><strong>比率</strong></div></td>
<td bgcolor="#b1e1fe"><div align="center"><strong>营业税</strong></div>
<div align="center"><strong>比率</strong></div></td>
<td bgcolor="#b1e1fe"><div align="center"><strong>分成</strong></div>
<div align="center"><strong>比率</strong></div></td>
<td bgcolor="#b1e1fe"><div align="center"><strong>应分成</strong></div>
<div align="center"><strong>收益</strong></div></td>
<td bgcolor="#b1e1fe"><div align="center"><strong>成功</strong></div>
<div align="center"><strong>收益</strong></div></td>
<td bgcolor="#b1e1fe"><strong>坏账率</strong></td>
<td bgcolor="#b1e1fe"><div align="center"><strong>ARPU</strong></div></td>
<td bgcolor="#b1e1fe"><div align="center"><strong>转定率</strong></div></td>
</tr>
<%
sql="select req_ymon,stat_id,sum(stat_value) from v_etl_day where from_id like '239%' and from_id<>'23999' and misc_serviceid in (select trade_code from tbl_partnerstat_trade where fee_type=1) group by stat_id, req_ymon ";
rs=pstmt.executeQuery(sql);
vdata=execute(rs);
rs.close();
sqldataprice="select req_ymon,stat_id,sum(stat_value),misc_serviceid from v_etl_day where from_id like '239%' and from_id<>'23999' and misc_serviceid in (select trade_code from tbl_partnerstat_trade where fee_type=1) and stat_id=21 group by stat_id, req_ymon ,misc_serviceid";
rs=pstmt.executeQuery(sqldataprice);
v=execute(rs);
rs.close();
int bonline[]=new int[vdate.size()];//本期存量
int btduser[]=new int[vdate.size()];//新增订购
int bfwuser[]=new int[vdate.size()];//访问人数
double bmoney[]=new double[vdate.size()];//收入
int bgetuser[]=new int[vdate.size()];//可收费用户
double bysy[]=new double[vdate.size()];//应收益
double bcsy[]=new double[vdate.size()];//成功收益
int bdguser[]=new int[vdate.size()];//订购人数
for(int ii=0;ii<vdate.size();ii++)
{
time=((Vector)vdate.elementAt(ii)).elementAt(0).toString();
bonline[ii]=getField(vdata,18,time);
btduser[ii]=getField(vdata,5,time);
bdguser[ii]=getField(vdata,4,time);
bgetuser[ii]=getField(vdata,21,time);
bfwuser[ii]=getField(vdata,1,time);
bmoney[ii]=getMoney(v,vprice,time);
bysy[ii]=getMoney(v,vprice,time) * 0.85 * 0.948 * 0.7;
bcsy[ii]=getMoney(v,vprice,time) * 0.85 * 0.948 * 0.7 * 0.93;
%>
<tr bgcolor="#FFFFFF">
<td bgcolor="#b1e1fe"><div align="center"><%if(ii==0){%>博动一<%}%></div></td>
<td align="left" bgcolor="#B1E1FE"><%=time%></td>
<td><%
if(ii>0)
out.println(bonline[ii-1]);
else
out.println("0");
%>
</td>
<td><%=bdguser[ii]%></td>
<td><%=btduser[ii]%></td>
<td><%=bonline[ii]%></td>
<td><%=bgetuser[ii]%></td>
<td><%=bmoney[ii]%></td>
<td>15%</td>
<td>5.2%</td>
<td>70%</td>
<td><%=bysy[ii]%></td>
<td><%=bcsy[ii]%></td>
<td>7%</td>
<td>0</td>
<td>0</td>
</tr>
<%
tbonline[0]= bonline[ii];
tbtduser[0]=tbtduser[0] + btduser[ii];
tbdguser[0]= tbdguser[0] + bdguser[ii];
tbgetuser[0]= tbgetuser[0] + bgetuser[ii];
tbfwuser[0]= tbfwuser[0] + bfwuser[ii];
tbmoney[0]= tbmoney[0] + bmoney[ii];
tbysy[0]=tbysy[0] + bysy[ii];
tbcsy[0]= tbcsy[0] + bcsy[ii];
}
%>
<tr bgcolor="#FFFFFF">
<td bgcolor="#b1e1fe"><div align="center">合计</div></td>
<td align="left" bgcolor="#B1E1FE"></td>
<td><%=tbonline[0]%>
</td>
<td><%=tbdguser[0]%></td>
<td><%=tbtduser[0]%></td>
<td><%=tbonline[0]%></td>
<td><%=tbgetuser[0]%></td>
<td><%=tbmoney[0]%></td>
<td></td>
<td></td>
<td></td>
<td><%=tbysy[0]%></td>
<td><%=tbcsy[0]%></td>
<td></td>
<td></td>
<td></td>
</tr>
//4
sql="select req_ymon,stat_id,sum(stat_value) from v_etl_day where from_id like '243%' and misc_serviceid in (select trade_code from tbl_partnerstat_trade where fee_type=1) group by stat_id, req_ymon ";
rs=pstmt.executeQuery(sql);
vdata=execute(rs);
rs.close();
sqldataprice="select req_ymon,stat_id,sum(stat_value),misc_serviceid from v_etl_day where from_id like '243%' and misc_serviceid in (select trade_code from tbl_partnerstat_trade where fee_type=1) and stat_id=21 group by stat_id, req_ymon ,misc_serviceid";
rs=pstmt.executeQuery(sqldataprice);
v=execute(rs);
rs.close();
int bonline4[]=new int[vdate.size()];//本期存量
int btduser4[]=new int[vdate.size()];//新增订购
int bfwuser4[]=new int[vdate.size()];//访问人数
double bmoney4[]=new double[vdate.size()];//收入
int bgetuser4[]=new int[vdate.size()];//可收费用户
double bysy4[]=new double[vdate.size()];//应收益
double bcsy4[]=new double[vdate.size()];//成功收益
int bdguser4[]=new int[vdate.size()];//订购人数
//例子
sqldataprice="select req_ymon,stat_id,sum(stat_value),misc_serviceid from v_etl_day where from_id like '244%' and misc_serviceid in (select trade_code from tbl_partnerstat_trade where fee_type=1) and stat_id=21 group by stat_id, req_ymon ,misc_serviceid";
rs=pstmt.executeQuery(sqldataprice);
v=execute(rs);
rs.close();
int bonline5[]=new int[vdate.size()];//本期存量
int btduser5[]=new int[vdate.size()];//新增订购
int bfwuser5[]=new int[vdate.size()];//访问人数
double bmoney5[]=new double[vdate.size()];//收入
int bgetuser5[]=new int[vdate.size()];//可收费用户
double bysy5[]=new double[vdate.size()];//应收益
double bcsy5[]=new double[vdate.size()];//成功收益
int bdguser5[]=new int[vdate.size()];//订购人数
for(int ii=0;ii<vdate.size();ii++)
{
//得到数据
time=((Vector)vdate.elementAt(ii)).elementAt(0).toString();
bonline5[ii]=getField(vdata,18,time);
btduser5[ii]=getField(vdata,5,time);
bdguser5[ii]=getField(vdata,4,time);
bgetuser5[ii]=getField(vdata,21,time);
bfwuser5[ii]=getField(vdata,1,time);
bmoney5[ii]=getMoney(v,vprice,time);
bysy5[ii]=getMoney(v,vprice,time) * 0.85 * 0.948 * 0.7;
bcsy5[ii]=getMoney(v,vprice,time) * 0.85 * 0.948 * 0.7 * 0.93;
%>>
sql="select req_ymon,stat_id,sum(stat_value) from v_etl_day where from_id like '245%' and misc_serviceid in (select trade_code from tbl_partnerstat_trade where fee_type=1) group by stat_id, req_ymon ";
rs=pstmt.executeQuery(sql);
vdata=execute(rs);
rs.close();
sqldataprice="select req_ymon,stat_id,sum(stat_value),misc_serviceid from v_etl_day where from_id like '245%' and misc_serviceid in (select trade_code from tbl_partnerstat_trade where fee_type=1) and stat_id=21 group by stat_id, req_ymon ,misc_serviceid";
rs=pstmt.executeQuery(sqldataprice);
v=execute(rs);
rs.close();
int bonline6[]=new int[vdate.size()];//本期存量
int btduser6[]=new int[vdate.size()];//新增订购
int bfwuser6[]=new int[vdate.size()];//访问人数
double bmoney6[]=new double[vdate.size()];//收入
int bgetuser6[]=new int[vdate.size()];//可收费用户
double bysy6[]=new double[vdate.size()];//应收益
double bcsy6[]=new double[vdate.size()];//成功收益
int bdguser6[]=new int[vdate.size()];//订购人数
for(int ii=0;ii<vdate.size();ii++)
{
time=((Vector)vdate.elementAt(ii)).elementAt(0).toString();
bonline6[ii]=getField(vdata,18,time);
btduser6[ii]=getField(vdata,5,time);
bdguser6[ii]=getField(vdata,4,time);
bgetuser6[ii]=getField(vdata,21,time);
bfwuser6[ii]=getField(vdata,1,time);
bmoney6[ii]=getMoney(v,vprice,time);
bysy6[ii]=getMoney(v,vprice,time) * 0.85 * 0.948 * 0.7;
bcsy6[ii]=getMoney(v,vprice,time) * 0.85 * 0.948 * 0.7 * 0.93;
%>
<tr bgcolor="#FFFFFF">
<td bgcolor="#b1e1fe"><div align="center"><%if(ii==0){%>博动六<%}%></div></td>
<td align="left" bgcolor="#B1E1FE"><%=time%></td>
<td><%
if(ii>0)
out.println(bonline6[ii-1]);
else
out.println("0");
%>
</td>
<td><%=bdguser6[ii]%></td>
<td><%=btduser6[ii]%></td>
<td><%=bonline6[ii]%></td>
<td><%=bgetuser6[ii]%></td>
<td><%=bmoney6[ii]%></td>
<td>15%</td>
<td>5.2%</td>
<td>70%</td>
<td><%=bysy6[ii]%></td>
<td><%=bcsy6[ii]%></td>
<td>7%</td>
<td></td>
<td></td>
</tr>
<%
tbonline[5]= bonline6[ii];
tbtduser[5]=tbtduser[5] + btduser6[ii];
tbdguser[5]= tbdguser[5] + bdguser6[ii];
tbgetuser[5]= tbgetuser[5] + bgetuser6[ii];
tbfwuser[5]= tbfwuser[5] + bfwuser6[ii];
tbmoney[5]= tbmoney[5] + bmoney6[ii];
tbysy[5]=tbysy[5] + bysy6[ii];
tbcsy[5]= tbcsy[5] + bcsy6[ii];
}
%>
<tr bgcolor="#FFFFFF">
<td bgcolor="#b1e1fe"><div align="center">合计</div></td>
<td align="left" bgcolor="#B1E1FE"></td>
<td><%=tbonline[5]%>
</td>
<td><%=tbdguser[5]%></td>
<td><%=tbtduser[5]%></td>
<td><%=tbonline[5]%></td>
<td><%=tbgetuser[5]%></td>
<td><%=tbmoney[5]%></td>
<td></td>
<td></td>
<td></td>
<td><%=tbysy[5]%></td>
<td><%=tbcsy[5]%></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr bgcolor="#FFFFFF">
<td bgcolor="#b1e1fe"><div align="center">总计</div></td>
<td align="left" bgcolor="#B1E1FE"></td>
<td><%=tbonline[1] + tbonline[2] + + tbonline[3] + tbonline[4] + tbonline[5] + tbonline[0]%></td>
<td><%=tbdguser[0] + tbdguser[1] + tbdguser[2] + tbdguser[3] + tbdguser[4] + tbdguser[5]%></td>
<td><%=tbtduser[0] + tbtduser[1] + tbtduser[2] + tbtduser[3] + tbtduser[4] + tbtduser[5]%></td>
<td><%=tbonline[0] + tbonline[1] + tbonline[2] + tbonline[3] + tbonline[4] + tbonline[5]%></td>
<td><%=tbgetuser[0] + tbgetuser[1] + tbgetuser[3] + tbgetuser[4] + tbgetuser[5] %></td>
<td><%=tbmoney[0] + tbmoney[1] + tbmoney[2] + tbmoney[3] + tbmoney[4] + tbmoney[5]%></td>
<td></td>
<td></td>
<td></td>
<td><%=tbysy[0] + tbysy[1] + tbysy[2] + tbysy[3] + tbysy[4] + tbysy[5]%></td>
<td><%=tbcsy[0] + tbcsy[1] + tbcsy[2] + tbcsy[3] + tbcsy[4] + tbcsy[5]%></td>
<td></td>
<td></td>
<td></td>
</tr>
</table>
</body>
</html>