将数据取到内存,减少对数据库的访问次数,加快速度

来源:互联网 发布:python turtle 国旗 编辑:程序博客网 时间:2022/05/28 06:52

<%@ 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>

 

原创粉丝点击