/*
* Copyright 2010-2011 Zhou Danyong.RDC.HuaXin
* under of the HZGJGX Project
*    
* You can redistribute this software and/or modify it under the
* terms of the HZGJGX project as published on the inner team (HZGJGX).
*    
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
* "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
* LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
* A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
* OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
* SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
* LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
* DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
* THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
* OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*    
*/
package com.huaxin.hzgjgx.assessment.gjgxfx;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;

import com.huaxin.common.DataProcessUtil;
import com.huaxin.common.QueryUtil;
import com.huaxin.hzgjgx.util.DataHandler;
import com.huaxin.hzgjgx.util.DateFilte;
import com.huaxin.hzgjgx.util.DateFilteImpl;


/**
* Export share corps info of those sharable sites.
*    
* @author Daniel Zhou 2010-04-06 15:00
* @version 0.1
* @edit Daniel Zhou 2010-04-07 09:40
*/
@SuppressWarnings( "unchecked")
public class GjGxTotal {
    
   // Initialization
  QueryUtil queryUtil = new QueryUtil();
  DateFilte dateFilte = new DateFilteImpl();
  DataProcessUtil dpu = new DataProcessUtil();
  StringBuffer sql = new StringBuffer();
    
   /**
    *    
    * @param ht
    * @return
    */
   public ArrayList getSearchList(Hashtable ht){
     //make sure date is validate
    String toDate ="";
     if(ht.get( "ToDate")== null) {
      toDate = DataHandler.getCurrentYearAndMonth();
    } else{
      toDate = ht.get( "ToDate").toString();
    }
    
     //Get SQL
    getSQL(ht, toDate);
    
     //query
    ArrayList list = null;
     try{
      list = queryUtil.getDataBySQL(sql.toString());
    } catch(Exception e){
      e.printStackTrace();
    }
    
     //release
    toDate= null;
    sql = null;queryUtil= null;dpu= null;dateFilte= null;
    
     //sum up
    list = sumUpTheList(list);
    
     //format date
    list = formatList(list);
    
     //back
     return list;
  }
    
   public ArrayList getSearchListAfterFormat(Hashtable ht){
    
    ArrayList fList = null;
    GjGxTotal inst = new GjGxTotal();
    fList = inst.getSearchList(ht);
     //format date
    fList = formatList(fList);
     //back
     return fList;
  }
    
   /**
    * Format data in list as add '%'
    *    
    * @param list
    * @return ArrayList
    */
   private ArrayList formatList(ArrayList list) {
    ArrayList newList =     new ArrayList();
     for (Iterator iterator = list.iterator(); iterator.hasNext();) {
      HashMap tmpMap = (HashMap)iterator.next();
      tmpMap.put( "GJGX_COLL_GJL", DataHandler.round(Double.parseDouble(tmpMap.get( "GJGX_COLL_GJL").toString())*100,2)+ "%");
      tmpMap.put( "GJGX_COLL_GXL", DataHandler.round(Double.parseDouble(tmpMap.get( "GJGX_COLL_GXL").toString())*100,2)+ "%");
      newList.add(tmpMap);
      tmpMap = null;
    }
    
     return newList;
  }

   /**
    * Sum up the list
    *    
    * @param list
    * @return
    */
   private ArrayList sumUpTheList(ArrayList list) {
     //initialization
    ArrayList newList =     new ArrayList();
    newList = list;
    HashMap map =     new HashMap();
    Double GJGX_COLL_ZJZYXJ=0.0;Double GJGX_COLL_ZJZYQZ=0.0; Double GJGX_COLL_GJSS=0.0;
    Double GJGX_COLL_GJL=0.0;Double GJGX_COLL_SDLLJ=0.0;Double GJGX_COLL_MZLLJ=0.0;
    Double GJGX_COLL_GXL=0.0;Double GJGX_COLL_YYSSXJ=0.0;Double GJGX_COLL_YYSSGX=0.0;

     //Sum up
     for (Iterator iterator = list.iterator(); iterator.hasNext();) {
      HashMap mapTmp = (HashMap) iterator.next();
      GJGX_COLL_ZJZYXJ +=Double.parseDouble( "0"+mapTmp.get( "GJGX_COLL_ZJZYXJ").toString());
      GJGX_COLL_ZJZYQZ +=Double.parseDouble( "0"+mapTmp.get( "GJGX_COLL_ZJZYQZ").toString());
      GJGX_COLL_GJSS +=Double.parseDouble( "0"+mapTmp.get( "GJGX_COLL_GJSS").toString()); //3
      GJGX_COLL_GJL +=Double.parseDouble( "0"+mapTmp.get( "GJGX_COLL_GJL").toString());
      GJGX_COLL_SDLLJ +=Double.parseDouble( "0"+mapTmp.get( "GJGX_COLL_SDLLJ").toString());
      GJGX_COLL_MZLLJ +=Double.parseDouble( "0"+mapTmp.get( "GJGX_COLL_MZLLJ").toString()); //6
      GJGX_COLL_GXL +=Double.parseDouble( "0"+mapTmp.get( "GJGX_COLL_GXL").toString());
      GJGX_COLL_YYSSXJ +=Double.parseDouble( "0"+mapTmp.get( "GJGX_COLL_YYSSXJ").toString());
      GJGX_COLL_YYSSGX +=Double.parseDouble( "0"+mapTmp.get( "GJGX_COLL_YYSSGX").toString()); //9
    }
    
     //set value
    map.put( "rn", "911");
    map.put( "YYS_TYPE_NAME", "合计");
    map.put( "GJGX_COLL_ZJZYXJ",DataHandler.round(GJGX_COLL_ZJZYXJ,2)+"");
    map.put( "GJGX_COLL_ZJZYQZ",DataHandler.round(GJGX_COLL_ZJZYQZ,2)+"");
    map.put( "GJGX_COLL_GJSS",DataHandler.round(GJGX_COLL_GJSS,2)+"");
    map.put( "GJGX_COLL_GJL",DataHandler.round(GJGX_COLL_GJL,2)+"");
    map.put( "GJGX_COLL_SDLLJ",DataHandler.round(GJGX_COLL_SDLLJ,2)+"");
    map.put( "GJGX_COLL_MZLLJ",DataHandler.round(GJGX_COLL_MZLLJ,2)+"");
    map.put( "GJGX_COLL_GXL",DataHandler.round(GJGX_COLL_GXL,2)+"");
    map.put( "GJGX_COLL_YYSSXJ",DataHandler.round(GJGX_COLL_YYSSXJ,2)+"");
    map.put( "GJGX_COLL_YYSSGX",DataHandler.round(GJGX_COLL_YYSSGX,2)+"");
    
     //add and back
    newList.add(map);
    map= null;
     return newList;
  }

   /**
    * Set SQL by conditions.
    *    
    * @param ht
    * @param toDate
    */
   private void getSQL(Hashtable ht, String toDate) {
    
     //Read direct    
     if(ht.get( "StatisticWay").toString().equals( "1")){
       //outer
      sql.append( " SELECT tt.*, ROWNUM rn FROM ( ");
       //core SQL
      sql.append( " Select T.YYS_TYPE_NAME,T.YYS_TYPE, ");
      sql.append( " SUM(t.GJGX_COLL_ZJZYXJ) AS GJGX_COLL_ZJZYXJ , ");
      sql.append( " SUM(t.GJGX_COLL_ZJZYQZ) AS GJGX_COLL_ZJZYQZ , ");
      sql.append( " SUM(t.GJGX_COLL_GJSS) AS GJGX_COLL_GJSS , "); //3
      sql.append( " SUM(t.GJGX_COLL_GJL) AS GJGX_COLL_GJL , ");
      sql.append( " SUM(t.GJGX_COLL_SDLLJ) AS GJGX_COLL_SDLLJ , ");
      sql.append( " SUM(t.GJGX_COLL_MZLLJ) AS GJGX_COLL_MZLLJ , "); //6
      sql.append( " SUM(t.GJGX_COLL_GXL) AS GJGX_COLL_GXL , ");
      sql.append( " SUM(t.GJGX_COLL_YYSSXJ) AS GJGX_COLL_YYSSXJ , ");
      sql.append( " SUM(t.GJGX_COLL_YYSSGX) AS GJGX_COLL_YYSSGX    "); //9
      sql.append( " from v_gjgx_hz t ");
      sql.append( " where    t.RES_TYPE='" +ht.get( "ReportTypeFour")+ "'");
      sql.append( " and    t.GJGX_XXTB_DY_YM <='" +toDate+ "' ");
      sql.append( " GROUP BY T.YYS_TYPE ");
      sql.append( " order by    t.YYS_TYPE asc ");
       //outer end
      sql.append( " ) tt ");
    }
    
     //Statistics by hand
     if(ht.get( "StatisticWay").toString().equals( "2")){
       //release    
      sql.delete(0, sql.length());
       //initialization
       int type = Integer.parseInt(ht.get( "ReportTypeThree").toString());
      String comEndSQL = " GROUP BY T.YYS_TYPE    order by    t.YYS_TYPE asc ";
        
       //outer
      sql.append( " SELECT ttt.*, ROWNUM rn FROM ( ");
        
       //construct SQL
       switch (type) {
       //1.铁塔设施共建共享汇总表
       case 1:
         //core SQL
        sql.append( " Select tt.YYS_TYPE, ");
        sql.append( " (select organizationname from v_org where organizationid=tt.YYS_TYPE ) as YYS_TYPE_NAME, ");
        sql.append( " SUM(tt.GJGX_COLL_ZJZYXJ) AS GJGX_COLL_ZJZYXJ , ");
        sql.append( " SUM(tt.GJGX_COLL_ZJZYQZ) AS GJGX_COLL_ZJZYQZ , ");
        sql.append( " SUM(tt.GJGX_COLL_GJSS) AS GJGX_COLL_GJSS , "); //3
        sql.append( " SUM(tt.GJGX_COLL_GJL) AS GJGX_COLL_GJL , ");
        sql.append( " SUM(tt.GJGX_COLL_SDLLJ) AS GJGX_COLL_SDLLJ , ");
        sql.append( " SUM(tt.GJGX_COLL_MZLLJ) AS GJGX_COLL_MZLLJ , "); //6
        sql.append( " SUM(tt.GJGX_COLL_GXL) AS GJGX_COLL_GXL , ");
        sql.append( " SUM(tt.GJGX_COLL_YYSSXJ) AS GJGX_COLL_YYSSXJ , ");
        sql.append( " SUM(tt.GJGX_COLL_YYSSGX) AS GJGX_COLL_YYSSGX    "); //9
        sql.append( " from    ");
        
        
        sql.append( " (select t1.YYS_TYPE,t1.GJGX_COLL_ZJZYXJ,t2.GJGX_COLL_ZJZYQZ,t3.GJGX_COLL_GJSS,");
        sql.append( " to_char((ROUND(((GJGX_COLL_ZJZYQZ+GJGX_COLL_GJSS) / (GJGX_COLL_ZJZYXJ+GJGX_COLL_GJSS)) * 100, 1)),'fm990.00') as GJGX_COLL_GJL, ");
        sql.append( " t5.GJGX_COLL_SDLLJ,t6.GJGX_COLL_MZLLJ,");
        sql.append( " to_char((ROUND((GJGX_COLL_MZLLJ / GJGX_COLL_SDLLJ) * 100, 1)),'fm990.00') as GJGX_COLL_GXL, ");
        sql.append( " t8.GJGX_COLL_YYSSXJ,t9.GJGX_COLL_YYSSGX from    ");
         //1.自建自用
        sql.append( " ( ");
        sql.append( " select count(t.BO_BTS_YJ_ID) as GJGX_COLL_ZJZYXJ, t.YYS_TYPE from BO_BTS_YJ t where ");
        sql.append( " t.BTS_YJ_JSSJ<='" +toDate+ "' and (t.BO_TWLX_ID='1' or    t.BO_TWLX_ID='2' or    t.BO_TWLX_ID='3') and ( t.BTS_YJ_ZYPTSL1='0' ");
        sql.append( " and t.BTS_YJ_ZYPTSL2='0' and t.BTS_YJ_ZYPTSL3='0') ");
        sql.append(comEndSQL);
        sql.append( " ) t1, ");
        
         //2.自建自用中自建预留
        sql.append( " ( ");
        sql.append( " select count(t.BO_BTS_YJ_ID) as GJGX_COLL_ZJZYQZ, t.YYS_TYPE from BO_BTS_YJ t where ");
        sql.append( " t.BTS_YJ_JSSJ<='" +toDate+ "' and (t.BO_TWLX_ID='1' or    t.BO_TWLX_ID='2' or    t.BO_TWLX_ID='3') and ( t.BTS_YJ_ZYPTSL1='0' ");
        sql.append( " and t.BTS_YJ_ZYPTSL2='0' and t.BTS_YJ_ZYPTSL3='0') and t.BUILD_SELF_TYPE='1' ");
        sql.append(comEndSQL);
        sql.append( " ) t2, ");
        
         //3.共建设施
        sql.append( " ( ");
        sql.append( " select count(t.BO_BTS_YJ_ID) as GJGX_COLL_GJSS, t.YYS_TYPE from BO_BTS_YJ t where ");
        sql.append( " t.BTS_YJ_JSSJ<='" +toDate+ "' and (t.BO_TWLX_ID='1' or    t.BO_TWLX_ID='2' or    t.BO_TWLX_ID='3') ");
        sql.append( " and t.SHARE_TYPE='1' and t.BTS_YJ_ZYPTSL3='0' and ( t.BTS_YJ_ZYPTSL1>'0' and t.BTS_YJ_ZYPTSL2>'0' and t.BTS_YJ_ZYPTSL3>'0')");
        sql.append(comEndSQL);
        sql.append( " ) t3, ");
        
         //4.共建率
         //have done with outer:
         /*
         * 'to_char((ROUND(((GJGX_COLL_ZJZYQZ+GJGX_COLL_GJSS) / (GJGX_COLL_ZJZYXJ+GJGX_COLL_GJSS)) * 100, 1)),'fm990.00') as GJGX_COLL_GJL'
         */
        
         //5.收到另两家企业的共享需求
        sql.append( " ( ");
        sql.append( " select sum(t.csize) as GJGX_COLL_SDLLJ, t.SQF_YYS_TYPE as YYS_TYPE from ( ");
        sql.append( " select x.SQF_YYS_TYPE, count(1) csize, x.CQF_YYS_TYPE from v_gxmx_tt x where x.XQTCDATE <'" +toDate+ "' and x.TRCTAG<>'9' " +
             " and x.XQTCDATE between '"+ toDate.substring(0,4)+ "年"+ "01月"+ "' and '"+ toDate.substring(0,4)+ "年"+ "12月"+ "' " +
             " group by x.SQF_YYS_TYPE, x.CQF_YYS_TYPE ) t ");
        sql.append( " GROUP BY T.SQF_YYS_TYPE    order by    t.SQF_YYS_TYPE asc ");
        sql.append( " ) t5, ");
        
         //6.满足另两家电信企业的共享需求
        sql.append( " ( ");
        sql.append( " select sum(t.csize) as GJGX_COLL_MZLLJ, t.CQF_YYS_TYPE as YYS_TYPE from ( ");
        sql.append( " select x.SQF_YYS_TYPE, nvl(sum(x.GL_ZYCD),0) csize, x.CQF_YYS_TYPE from v_gxmx_tt x where x.enddate <'" +toDate+ "' and x.TRCTAG='7' " +
             " and x.enddate between '"+ toDate.substring(0,4)+ "年"+ "01月"+ "' and '"+ toDate.substring(0,4)+ "年"+ "12月"+ "' " +
             " group by x.CQF_YYS_TYPE, x.SQF_YYS_TYPE ) t ");
        sql.append( " GROUP BY T.CQF_YYS_TYPE    order by    t.CQF_YYS_TYPE asc ");
        sql.append( " ) t6, ");
        
         //7.共享率
         //math （满足另两家电信企业的共享需求/收到另两家企业的共享需求）
         /*
         * to_char((ROUND((GJGX_COLL_MZLLJ / GJGX_COLL_SDLLJ) * 100, 1)),'fm990.00') as GJGX_COLL_GXL,
         */
        
         //8.已有设施
        sql.append( " ( ");
        sql.append( " select count(t.BO_BTS_YJ_ID) as GJGX_COLL_YYSSXJ, t.YYS_TYPE from BO_BTS_YJ t where ");
        sql.append( " t.BTS_YJ_JSSJ<='" +toDate+ "' and (t.BO_TWLX_ID='1' or    t.BO_TWLX_ID='2' or    t.BO_TWLX_ID='3')");
        sql.append(comEndSQL);
        sql.append( " ) t8, ");
        
         //9.已有设施中共享
        sql.append( " ( ");
        sql.append( " select count(t.BO_BTS_YJ_ID) as GJGX_COLL_YYSSGX, t.YYS_TYPE from BO_BTS_YJ t where ");
        sql.append( "        (    t.SHARE_TYPE='2' and t.BTS_YJ_JSSJ<='" +toDate+ "' and (t.BO_TWLX_ID='1' or    t.BO_TWLX_ID='2' or    t.BO_TWLX_ID='3') " +
             "and t.BTS_YJ_ZYPTSL1>'0' and t.BTS_YJ_ZYPTSL2>'0' and t.BTS_YJ_ZYPTSL3>'0' " +
             "and t.QDXY_DATE1<='" +toDate+ "' and t.QDXY_DATE2<='" +toDate+ "' and t.QDXY_DATE3<='" +toDate+ "' )    ");
        sql.append( " or (    t.SHARE_TYPE='1' and t.BTS_YJ_JSSJ<='"+ toDate.substring(0,4)+ "年"+ "01月"+ "' " +
             "and ( t.BTS_YJ_ZYPTSL1>'0' and t.BTS_YJ_ZYPTSL2>'0' and t.BTS_YJ_ZYPTSL3>'0')" +
             "and t.QDXY_DATE1<='" +toDate+ "' and t.QDXY_DATE2<='" +toDate+ "' and t.QDXY_DATE3<='" +toDate+ "' )    ");
        sql.append(comEndSQL);
        sql.append( " ) t9 ");
        
        sql.append( " where t1.YYS_TYPE=t2.YYS_TYPE and t2.YYS_TYPE=t3.YYS_TYPE " +
             " and t3.YYS_TYPE=t5.YYS_TYPE and t5.YYS_TYPE=t6.YYS_TYPE and t8.YYS_TYPE=t9.YYS_TYPE ");
        sql.append( "    ) tt    ");
        sql.append( "    GROUP BY TT.YYS_TYPE ORDER BY TT.YYS_TYPE ASC    ");
         break;
        
       //2.杆路设施共建共享汇总表    
       case 2:
         //core SQL
        sql.append( " Select tt.YYS_TYPE, ");
        sql.append( " (select organizationname from v_org where organizationid=tt.YYS_TYPE ) as YYS_TYPE_NAME, ");
        sql.append( " SUM(tt.GJGX_COLL_ZJZYXJ) AS GJGX_COLL_ZJZYXJ , ");
        sql.append( " SUM(tt.GJGX_COLL_ZJZYQZ) AS GJGX_COLL_ZJZYQZ , ");
        sql.append( " SUM(tt.GJGX_COLL_GJSS) AS GJGX_COLL_GJSS , "); //3
        sql.append( " SUM(tt.GJGX_COLL_GJL) AS GJGX_COLL_GJL , ");
        sql.append( " SUM(tt.GJGX_COLL_SDLLJ) AS GJGX_COLL_SDLLJ , ");
        sql.append( " SUM(tt.GJGX_COLL_MZLLJ) AS GJGX_COLL_MZLLJ , "); //6
        sql.append( " SUM(tt.GJGX_COLL_GXL) AS GJGX_COLL_GXL , ");
        sql.append( " SUM(tt.GJGX_COLL_YYSSXJ) AS GJGX_COLL_YYSSXJ , ");
        sql.append( " SUM(tt.GJGX_COLL_YYSSGX) AS GJGX_COLL_YYSSGX    "); //9
        sql.append( " from    ");
        
        
        sql.append( " (select t1.YYS_TYPE,t1.GJGX_COLL_ZJZYXJ,t2.GJGX_COLL_ZJZYQZ,t3.GJGX_COLL_GJSS,");
        sql.append( " to_char((ROUND(((GJGX_COLL_ZJZYQZ+GJGX_COLL_GJSS) / (GJGX_COLL_ZJZYXJ+GJGX_COLL_GJSS)) * 100, 1)),'fm990.00') as GJGX_COLL_GJL, ");
        sql.append( " t5.GJGX_COLL_SDLLJ,t6.GJGX_COLL_MZLLJ,");
        sql.append( " to_char((ROUND((GJGX_COLL_MZLLJ / GJGX_COLL_SDLLJ) * 100, 1)),'fm990.00') as GJGX_COLL_GXL, ");
        sql.append( " t8.GJGX_COLL_YYSSXJ,t9.GJGX_COLL_YYSSGX from    ");
        
         //1.自建自用
        sql.append( " ( ");
        sql.append( " select count(t.BO_BTS_YJ_ID) as GJGX_COLL_ZJZYXJ, t.YYS_TYPE from BO_BTS_YJ t where ");
        sql.append( " t.BTS_YJ_JSSJ<='" +toDate+ "' and (t.BO_TWLX_ID='1' or    t.BO_TWLX_ID='2' or    t.BO_TWLX_ID='3') and ( t.BTS_YJ_ZYPTSL1='0' ");
        sql.append( " and t.BTS_YJ_ZYPTSL2='0' and t.BTS_YJ_ZYPTSL3='0') ");
        sql.append(comEndSQL);
        sql.append( " ) t1, ");
        
         //2.自建自用中自建预留
        sql.append( " ( ");
        sql.append( " select count(t.BO_BTS_YJ_ID) as GJGX_COLL_ZJZYQZ, t.YYS_TYPE from BO_BTS_YJ t where ");
        sql.append( " t.BTS_YJ_JSSJ<='" +toDate+ "' and (t.BO_TWLX_ID='1' or    t.BO_TWLX_ID='2' or    t.BO_TWLX_ID='3') and ( t.BTS_YJ_ZYPTSL1='0' ");
        sql.append( " and t.BTS_YJ_ZYPTSL2='0' and t.BTS_YJ_ZYPTSL3='0') and t.BUILD_SELF_TYPE='1' ");
        sql.append(comEndSQL);
        sql.append( " ) t2, ");
        
         //3.共建设施
        sql.append( " ( ");
        sql.append( " select count(t.BO_BTS_YJ_ID) as GJGX_COLL_GJSS, t.YYS_TYPE from BO_BTS_YJ t where ");
        sql.append( " t.BTS_YJ_JSSJ<='" +toDate+ "' and (t.BO_TWLX_ID='1' or    t.BO_TWLX_ID='2' or    t.BO_TWLX_ID='3') ");
        sql.append( " and t.SHARE_TYPE='1' and t.BTS_YJ_ZYPTSL3='0' and ( t.BTS_YJ_ZYPTSL1>'0' and t.BTS_YJ_ZYPTSL2>'0' and t.BTS_YJ_ZYPTSL3>'0')");
        sql.append(comEndSQL);
        sql.append( " ) t3, ");
        
         //4.共建率
         //Done by math （自建自用中自建预留+共建设施）/（自建自用+共建设施）
        
         //5.收到另两家企业的共享需求
        sql.append( " ( ");
        sql.append( " select sum(t.csize) as GJGX_COLL_SDLLJ, t.SQF_YYS_TYPE as YYS_TYPE from ( ");
        sql.append( " select x.SQF_YYS_TYPE, nvl(sum(x.GL_ZYCD),0) csize, x.CQF_YYS_TYPE from v_gxmx_bts x where x.SS_TYPE = '3' and x.XQTCDATE <'" +toDate+ "' and x.TRCTAG<>'9' " +
             " and x.XQTCDATE between '"+ toDate.substring(0,4)+ "年"+ "01月"+ "' and '"+ toDate.substring(0,4)+ "年"+ "12月"+ "' " +
             " group by x.SQF_YYS_TYPE, x.CQF_YYS_TYPE ) t ");
        sql.append( " GROUP BY T.SQF_YYS_TYPE    order by    t.SQF_YYS_TYPE asc ");
        sql.append( " ) t5, ");

         //6.满足另两家电信企业的共享需求
        sql.append( " ( ");
        sql.append( " select sum(t.csize) as GJGX_COLL_MZLLJ, t.CQF_YYS_TYPE as YYS_TYPE from ( ");
        sql.append( " select x.SQF_YYS_TYPE, nvl(sum(g.bts_yj_zygdcd1),0) csize, x.CQF_YYS_TYPE from v_gxmx_tt x    inner join bo_gxxx g on x.BO_GXGL_ID = g.bo_gxgl_id     where x.enddate <'" +toDate+ "' and x.TRCTAG='7' and x.SS_TYPE = '3'    " +
             " and x.enddate between '"+ toDate.substring(0,4)+ "年"+ "01月"+ "' and '"+ toDate.substring(0,4)+ "年"+ "12月"+ "' " +
             " group by x.CQF_YYS_TYPE, x.SQF_YYS_TYPE ) t ");
        sql.append( " GROUP BY T.CQF_YYS_TYPE    order by    t.CQF_YYS_TYPE asc ");
        sql.append( " ) t6, ");
        
         //7.共享率
         //Done by math （满足另两家电信企业的共享需求/收到另两家企业的共享需求）
        
         //8.已有设施
        sql.append( " ( ");
        sql.append( " select count(t.BO_BTS_YJ_ID) as GJGX_COLL_YYSSXJ, t.YYS_TYPE from BO_BTS_YJ t where ");
        sql.append( " t.BTS_YJ_JSSJ<='" +toDate+ "' and (t.BO_TWLX_ID='1' or    t.BO_TWLX_ID='2' or    t.BO_TWLX_ID='3')");
        sql.append(comEndSQL);
        sql.append( " ) t8, ");
        
         //9.已有设施中共享
        sql.append( " ( ");
        sql.append( " select count(t.BO_BTS_YJ_ID) as GJGX_COLL_YYSSGX, t.YYS_TYPE from BO_BTS_YJ t where ");
        sql.append( "        (    t.SHARE_TYPE='2' and t.BTS_YJ_JSSJ<='" +toDate+ "' and (t.BO_TWLX_ID='1' or    t.BO_TWLX_ID='2' or    t.BO_TWLX_ID='3') " +
             "and t.BTS_YJ_ZYPTSL1>'0' and t.BTS_YJ_ZYPTSL2>'0' and t.BTS_YJ_ZYPTSL3>'0' " +
             "and t.QDXY_DATE1<='" +toDate+ "' or t.QDXY_DATE2<='" +toDate+ "' or t.QDXY_DATE3<='" +toDate+ "' )    ");
        sql.append( " or (    t.SHARE_TYPE='1' and t.BTS_YJ_JSSJ<='"+ toDate.substring(0,4)+ "年"+ "01月"+ "' " +
             "and ( t.BTS_YJ_ZYPTSL1>'0' and t.BTS_YJ_ZYPTSL2>'0' and t.BTS_YJ_ZYPTSL3>'0')" +
             "and t.QDXY_DATE1<='" +toDate+ "' or t.QDXY_DATE2<='" +toDate+ "' or t.QDXY_DATE3<='" +toDate+ "' )    ");
        sql.append(comEndSQL);
        sql.append( " ) t9 ");
        
        sql.append( " where t1.YYS_TYPE=t2.YYS_TYPE and t2.YYS_TYPE=t3.YYS_TYPE and t8.YYS_TYPE=t9.YYS_TYPE ");
        sql.append( " and t3.YYS_TYPE=t5.YYS_TYPE ");
        sql.append( " and t5.YYS_TYPE=t6.YYS_TYPE ");
        sql.append( "    ) tt    ");
        sql.append( "    GROUP BY TT.YYS_TYPE ORDER BY TT.YYS_TYPE ASC    ");
         break;
        
       //3.基站设施共建共享汇总表    
       case 3:
         //core SQL
        sql.append( " Select tt.YYS_TYPE, ");
        sql.append( " (select organizationname from v_org where organizationid=tt.YYS_TYPE ) as YYS_TYPE_NAME, ");
        sql.append( " SUM(tt.GJGX_COLL_ZJZYXJ) AS GJGX_COLL_ZJZYXJ , ");
        sql.append( " SUM(tt.GJGX_COLL_ZJZYQZ) AS GJGX_COLL_ZJZYQZ , ");
        sql.append( " SUM(tt.GJGX_COLL_GJSS) AS GJGX_COLL_GJSS , "); //3
        sql.append( " SUM(tt.GJGX_COLL_GJL) AS GJGX_COLL_GJL , ");
        sql.append( " SUM(tt.GJGX_COLL_SDLLJ) AS GJGX_COLL_SDLLJ , ");
        sql.append( " SUM(tt.GJGX_COLL_MZLLJ) AS GJGX_COLL_MZLLJ , "); //6
        sql.append( " SUM(tt.GJGX_COLL_GXL) AS GJGX_COLL_GXL , ");
        sql.append( " SUM(tt.GJGX_COLL_YYSSXJ) AS GJGX_COLL_YYSSXJ , ");
        sql.append( " SUM(tt.GJGX_COLL_YYSSGX) AS GJGX_COLL_YYSSGX    "); //9
        sql.append( " from    ");
        
        
        sql.append( " (select t1.YYS_TYPE,t1.GJGX_COLL_ZJZYXJ,t2.GJGX_COLL_ZJZYQZ,t3.GJGX_COLL_GJSS,");
        sql.append( " to_char((ROUND(((GJGX_COLL_ZJZYQZ+GJGX_COLL_GJSS) / (GJGX_COLL_ZJZYXJ+GJGX_COLL_GJSS)) * 100, 1)),'fm990.00') as GJGX_COLL_GJL, ");
        sql.append( " t5.GJGX_COLL_SDLLJ,t6.GJGX_COLL_MZLLJ,");
        sql.append( " to_char((ROUND((GJGX_COLL_MZLLJ / GJGX_COLL_SDLLJ) * 100, 1)),'fm990.00') as GJGX_COLL_GXL, ");
        sql.append( " t8.GJGX_COLL_YYSSXJ,t9.GJGX_COLL_YYSSGX from    ");
        
         //1.自建自用
        sql.append( " ( ");
        sql.append( " select count(t.BO_BTS_YJ_ID) as GJGX_COLL_ZJZYXJ, t.YYS_TYPE from BO_BTS_YJ t where ");
        sql.append( " t.BTS_YJ_JSSJ<='" +toDate+ "' and (t.BTS_YJ_ZYJFMJ1='0' and    t.BTS_YJ_ZYJFMJ2='0' and    t.BTS_YJ_ZYJFMJ3='0') and ( t.BTS_YJ_ZYPTSL1='0' ");
        sql.append( " and t.BTS_YJ_ZYPTSL2='0' and t.BTS_YJ_ZYPTSL3='0') and ( t.IS_ZYSD1='2' and    t.IS_ZYSD2='2' and t.IS_ZYSD3='2' ) ");
        sql.append(comEndSQL);
        sql.append( " ) t1, ");
        
         //2.自建自用中自建预留
        sql.append( " ( ");
        sql.append( " select count(t.BO_BTS_YJ_ID) as GJGX_COLL_ZJZYQZ, t.YYS_TYPE from BO_BTS_YJ t where ");
        sql.append( " t.BTS_YJ_JSSJ<='" +toDate+ "' and t.BUILD_SELF_TYPE='1' and (t.BTS_YJ_ZYJFMJ1='0' and    t.BTS_YJ_ZYJFMJ2='0' and    t.BTS_YJ_ZYJFMJ3='0') and ( t.BTS_YJ_ZYPTSL1='0' ");
        sql.append( " and t.BTS_YJ_ZYPTSL2='0' and t.BTS_YJ_ZYPTSL3='0') and ( t.IS_ZYSD1='2' and    t.IS_ZYSD2='2' and t.IS_ZYSD3='2' ) ");
        sql.append(comEndSQL);
        sql.append( " ) t2, ");
        
         //3.共建设施
        sql.append( " ( ");
        sql.append( " select count(t.BO_BTS_YJ_ID) as GJGX_COLL_GJSS, t.YYS_TYPE from BO_BTS_YJ t where ");
        sql.append( " t.BTS_YJ_JSSJ<='" +toDate+ "' " +
             " and t.QDXY_DATE1<='" +toDate+ "' or t.QDXY_DATE2<='" +toDate+ "' or t.QDXY_DATE3<='" +toDate+ "' ");
        sql.append( " and t.SHARE_TYPE='1' ");
        sql.append(comEndSQL);
        sql.append( " ) t3, ");
        
         //4.共建率
         //have done with outer:
         /*
         * 'to_char((ROUND(((GJGX_COLL_ZJZYQZ+GJGX_COLL_GJSS) / (GJGX_COLL_ZJZYXJ+GJGX_COLL_GJSS)) * 100, 1)),'fm990.00') as GJGX_COLL_GJL'
         */
        
         //5.收到另两家企业的共享需求
        sql.append( " ( ");
        sql.append( " select sum(t.csize) as GJGX_COLL_SDLLJ, t.SQF_YYS_TYPE as YYS_TYPE from ( ");
        sql.append( " select x.SQF_YYS_TYPE, count(1) csize, x.CQF_YYS_TYPE from v_gxmx_bts x where x.XQTCDATE <'" +toDate+ "' and x.TRCTAG<>'9' and x.SS_TYPE = '1'    " +
             " and x.XQTCDATE between '"+ toDate.substring(0,4)+ "年"+ "01月"+ "' and '"+ toDate.substring(0,4)+ "年"+ "12月"+ "' " +
             " group by x.SQF_YYS_TYPE, x.CQF_YYS_TYPE ) t ");
        sql.append( " GROUP BY T.SQF_YYS_TYPE    order by    t.SQF_YYS_TYPE asc ");
        sql.append( " ) t5, ");
        
         //6.满足另两家电信企业的共享需求
        sql.append( " ( ");
        sql.append( " select sum(t.csize) as GJGX_COLL_MZLLJ, t.CQF_YYS_TYPE as YYS_TYPE from ( ");
        sql.append( " select x.SQF_YYS_TYPE, count(1) csize, x.CQF_YYS_TYPE from v_gxmx_tt x where x.enddate <'" +toDate+ "' and x.TRCTAG='7' and x.SS_TYPE = '1' " +
             " and x.enddate between '"+ toDate.substring(0,4)+ "年"+ "01月"+ "' and '"+ toDate.substring(0,4)+ "年"+ "12月"+ "' " +
             " group by x.CQF_YYS_TYPE, x.SQF_YYS_TYPE ) t ");
        sql.append( " GROUP BY T.CQF_YYS_TYPE    order by    t.CQF_YYS_TYPE asc ");
        sql.append( " ) t6, ");
        
         //7.共享率
         //math （满足另两家电信企业的共享需求/收到另两家企业的共享需求）
         /*
         * to_char((ROUND((GJGX_COLL_MZLLJ / GJGX_COLL_SDLLJ) * 100, 1)),'fm990.00') as GJGX_COLL_GXL,
         */
        
         //8.已有设施
        sql.append( " ( ");
        sql.append( " select count(t.BO_BTS_YJ_ID) as GJGX_COLL_YYSSXJ, t.YYS_TYPE from BO_BTS_YJ t where ");
        sql.append( " t.BTS_YJ_JSSJ<='" +toDate+ "'");
        sql.append(comEndSQL);
        sql.append( " ) t8, ");
        
         //9.已有设施中共享
        sql.append( " ( ");
        sql.append( " select count(t.BO_BTS_YJ_ID) as GJGX_COLL_YYSSGX, t.YYS_TYPE from BO_BTS_YJ t where ");
        sql.append( "         (    t.SHARE_TYPE='2' and t.BTS_YJ_JSSJ<='" +toDate+ "' )    ");
        sql.append( " and (    t.SHARE_TYPE='1' and t.BTS_YJ_JSSJ<='"+ toDate.substring(0,4)+ "年"+ "01月"+ "' )    ");
        sql.append(comEndSQL);
        sql.append( " ) t9 ");
        
        sql.append( " where t1.YYS_TYPE=t2.YYS_TYPE and t2.YYS_TYPE=t3.YYS_TYPE and t8.YYS_TYPE=t9.YYS_TYPE ");
        sql.append( " and t3.YYS_TYPE=t5.YYS_TYPE ");
        sql.append( " and t5.YYS_TYPE=t6.YYS_TYPE ");
        sql.append( "    ) tt    ");
        sql.append( "    GROUP BY TT.YYS_TYPE ORDER BY TT.YYS_TYPE ASC    ");
         break;
      }
        
       //outer end
      sql.append( " ) ttt ");
    }
  }

    
    
}
 
疑问描述：
ArrayList在多次操作时候，后面的操作会影响前面的操作，曾试试非迭代器置值、更换HashMap、浅克隆List或者Map，结果均不能解决。