package com.bus.dao.impl;

import java.io.UnsupportedEncodingException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;

import com.bus.dao.IBusOrgDao;
import com.bus.model.BusOrg;
import com.bus.model.temp.BusOrgTemp;
import com.core.dao.impl.GenericDaoImpl;

/**
 * 数据导入文件持久层接口类
 * 
 * @author lha
 * 
 */
public class BusOrgDaoImpl extends GenericDaoImpl<BusOrg, Long>
		implements IBusOrgDao {
	
	@Autowired
	private SessionFactory sessionFactory;
	@Autowired
	private org.springframework.jdbc.core.JdbcTemplate jdbcTemplate;

	@Override
	public String getMatYearFromBusOrg() {
		String hql = "select max(b.year) from BusOrg b where b.importStatus='normal' and b.isPlanUser='yes'";
		String year = (String)sessionFactory.getCurrentSession().createQuery(hql).uniqueResult();
		if(null == year){
			SimpleDateFormat f = new SimpleDateFormat("yyyy");
			Date d = new Date();
			year= f.format(d);
		}
		return year;
	}

/*	@Override
	public List<BusOrgTemp> getCountInitDate(String year) {
		String sql = "SELECT b.`org_id`,b.`year`,b.org_number FROM `bus_org` AS b WHERE b.`import_status`='normal' AND b.`year`='"+year+"' and b.is_reply='no'";	
		RowMapper<BusOrgTemp> rowMapper = new RowMapper<BusOrgTemp>(){

			@Override
			public BusOrgTemp mapRow(ResultSet rs, int arg1)
					throws SQLException {
				BusOrgTemp busOrgTemp= new BusOrgTemp(rs.getLong("org_id"), rs.getString("year"), rs.getInt("org_number"));				
				return busOrgTemp;
			}			
		};		
		List<BusOrgTemp> list = jdbcTemplate.query(sql, rowMapper);
		String sql = "SELECT count(*) FROM `bus_org` AS b WHERE b.`import_status`='normal' AND b.`year`=? and b.is_reply='no'";
		Long count = super.getCountBySql(sql, year);
		return list;
	}*/

	@Override
	public void updateCountYearInitDate(Long userId,Date updateTime,String userName,String year,Date initStartTime,Date initEndTime,
			  Date bMonthStartTime,Date bMonthEndTime,Date eMonthStartTime,Date eMonthEndTime) {
		//先将int值转为float否则会发生精度丢失
		//Float v = (float) (1+GR/100);
		//通过sql查询增长比例
		String v = " (select 1+(growth_ratio/100) from base_org_type o where o.org_type_id = b.org_type_id) ";
		String s = " (select count(1) from bus_customer_meter c where c.`org_id` = b.`org_id` and c.`year` = b.`year`) ";
		String yearSql = "ROUND(SUM(bm.`water`)/count(bm.`water`) * 12 * "+s+" * "+v+")";
		String quarterSql = "ROUND("+yearSql+"/4)";
		String montherSql = "ROUND("+yearSql+"/12)";
		String sql = " INSERT INTO `bus_annual_plan_for_water`                                                           "
                   + " (                                                                                                 " 
                   + "    org_id,pri_year_usewater,plan_of_water,reply_of_water,                      					"
                   + "    first_quarter_plan,first_quarter_reply,second_quarter_plan,second_quarter_reply,               "
                   + "    third_quarter_plan,third_quarter_reply,fourth_quarter_plan,fourth_quarter_reply,                                  "
                   + "    operator_id,fill_time,user_name,is_cancel,                                                      "
				   + "    jan_plan,feb_plan,mar_plan,apr_plan,may_plan,jun_plan,jul_plan,aug_plan,sept_plan,oct_plan,nov_plan,dec_plan, "
				   + "    jan_reply,feb_reply,mar_reply,apr_reply,may_reply,jun_reply,jul_reply,aug_reply,sept_reply,oct_reply,nov_reply,dec_reply "
                   + " )                                                                                                 " 
                   + " (                                                                                                 "
				   + " SELECT b.`org_id`,SUM(bm.`water`),"+yearSql+", "+yearSql+",                                  "
				   + "       "+quarterSql+", "+quarterSql+","+quarterSql+","+quarterSql+",                              "
                   + "        "+quarterSql+", "+quarterSql+","+quarterSql+","+quarterSql+",                             "
                   + "        ?,?,?,'yes',                  "
				   + "        "+montherSql+","+montherSql+","+montherSql+","+montherSql+","+montherSql+","+montherSql+","
					+ "        "+montherSql+","+montherSql+","+montherSql+","+montherSql+","+montherSql+","+montherSql+","
					+ "        "+montherSql+","+montherSql+","+montherSql+","+montherSql+","+montherSql+","+montherSql+","
					+ "        "+montherSql+","+montherSql+","+montherSql+","+montherSql+","+montherSql+","+montherSql
                   + " FROM `bus_org` b JOIN `bus_meter_read_info` bm ON b.`org_number`=bm.`org_number`                  " 
                   + " WHERE b.`is_plan_user`='yes' AND b.`import_status`='normal' AND b.`year`=? AND bm.`price_month`>=? AND bm.`price_month`<?        "
                   + " AND NOT EXISTS                                                                                    "
                   + "    (                                                                                              " 
                   + "      SELECT w.`org_id` FROM `bus_annual_plan_for_water` w WHERE b.`org_id`=w.`org_id`             " 
                   + "     )                                                                                             "
//                   + " AND EXISTS                                                                                        " 
//                   + "    (                                                                                              " 
//                   + "      SELECT bmr.`info_id` FROM `bus_meter_read_info` bmr WHERE b.`org_number`=bmr.`org_number`    " 
//                   + "      AND bmr.`import_status`='normal' AND bmr.`price_month`>=? AND bmr.`price_month`<?            " 
//                   + "    )                                                                                              "
//                   + " AND EXISTS                                                                                        " 
//                   + "    (                                                                                              " 
//                   + "      SELECT bmri.`info_id` FROM `bus_meter_read_info` bmri WHERE b.`org_number`=bmri.`org_number` " 
//                   + "      AND bmri.`import_status`='normal' AND bmri.`price_month`>=? AND bmri.`price_month`<?         " 
//                   + "     )                                                                                             "
                   + " GROUP BY b.`org_number`                                                                           "
                   + " )";
		
		System.out.println("sql======================================"+sql);
		//return;
//		 jdbcTemplate.update(sql, userId,updateTime,userName,year,initStartTime,initEndTime,bMonthStartTime,bMonthEndTime,eMonthStartTime,eMonthEndTime);
		 jdbcTemplate.update(sql, userId,updateTime,userName,year,initStartTime,initEndTime);
		
	}

	@Override
	public void updateYearneedInitDate(String initYear,Date initStartTime,Date initEndTime,
			  Date bMonthStartTime,Date bMonthEndTime,Date eMonthStartTime,Date eMonthEndTime) {
		//初值化计划户
		int nextyear = Integer.parseInt(initYear)+1;
		//添加条件： AND bo.`import_status`='normal' 了必须取未删除的单位信息，
		//李周芳 2016-02-19
		//把所有上一年用户都初值化到今年来
		String sql1 =" INSERT INTO `bus_org` (org_name,org_number,address,is_plan_user,`year`,industry_number,`code`,email,bank,bank_account, "
                    +" remark,org_type_id,`area`,phone,import_status,user_type,postal_address)                                                               "    
                    +" SELECT b.`org_name`,b.`org_number`,b.`address`,b.`is_plan_user`,?,b.`industry_number`,b.`code`,                        "
                    +" b.`email`,b.`bank`,b.`bank_account`,b.`remark`,b.`org_type_id`,b.`area`,b.`phone`,b.`import_status`,b.`user_type`,b.`postal_address`      "
                    +" FROM `bus_org` b WHERE b.`year`=? AND b.`import_status`='normal'                                                       "
                    +" AND NOT EXISTS (                                                                                                       "
                    +" SELECT bo.`org_id` FROM `bus_org` bo WHERE bo.`org_number`=b.`org_number` AND bo.`year`=?   AND bo.`import_status`='normal'                            "
                    +" )";
		int a = jdbcTemplate.update(sql1, nextyear,initYear,nextyear);
		
		String sql2 ="UPDATE bus_org SET postal_address=address WHERE (postal_address ='' or postal_address is null) and year=?";
		int c = jdbcTemplate.update(sql2, nextyear);
	/*	//把所有没有年度计划的计划户变为非计划户
		String sql3 =" UPDATE `bus_org` bu SET bu.`is_plan_user`='no' WHERE bu.`year`=? AND bu.`is_plan_user`='yes' AND bu.`import_status`='normal'            "
                    +" AND NOT EXISTS ( SELECT w.`org_id` FROM `bus_annual_plan_for_water` w WHERE bu.`org_id` = w.`org_id`)    ";
		int b = jdbcTemplate.update(sql3, nextyear);*/
		
		/*//把初值化数据标记计划户		
		String sql =" UPDATE `bus_org` bus SET bus.`is_plan_user`='yes' WHERE bus.year=? and exists                                " 
		           +" (                                                                                                            "
                   +"      SELECT te.org_id FROM                                                                                   "
                   +"      (                                                                                                       "
		           +"          SELECT b.`org_id`,b.year,b.`org_number`,SUM(bm.`water`) AS sumWater FROM bus_org b                  "
		           +"          JOIN `bus_meter_read_info` bm ON b.`org_number`=bm.`org_number`                                     "
			       +"          WHERE b.`year`=? AND b.`import_status`='normal' AND bm.`import_status`='normal'                     "
			       +"          AND (bm.`price_month`>= ? AND bm.`price_month`<?)                                                   "
			       +"          and not exists                                                                                      "
			       +"              (                                                                                               " 
			       +"                  SELECT w.`org_id` FROM `bus_annual_plan_for_water` w WHERE b.`org_id`=w.`org_id`            " 
			       +"              )                                                                                               "
			       +"          AND EXISTS                                                                                          "
			       +"              (                                                                                               " 
			       +"                  SELECT bmr.`org_id` FROM `bus_meter_read_info` bmr WHERE bmr.`org_number`=b.`org_number`    "
			       +"                  AND (bmr.`price_month`>= ? AND bmr.`price_month`<? ) AND bmr.`import_status`='normal'       "
			       +"              )                                                                                               "
			       +"          AND EXISTS                                                                                          " 
			       +"              (                                                                                               " 
			       +"                  SELECT bmri.`org_id` FROM `bus_meter_read_info` bmri WHERE bmri.`org_number`=b.`org_number` "
			       +"                  AND (bmri.`price_month`>= ? AND bmri.`price_month`<? ) AND bmri.`import_status`='normal'    "
			       +"               )                                                                                              "
			       +"          GROUP BY b.`org_number` HAVING sumWater>=?                                                          "
			       +"      ) as te where bus.org_number=te.org_number                                                              "
			       +" )";
		int c = jdbcTemplate.update(sql, new Object[]{nextyear,initYear,initStartTime,initEndTime,bMonthStartTime,bMonthEndTime,eMonthStartTime,eMonthEndTime,checkWater});		*/
		
		
		/*// 把水表号、客户号插入水表客户表
		String sql2 ="  INSERT INTO `bus_customer_meter` (`year`,address,org_id,customer_Number,meter_Number)                                   "
					+"	  SELECT ?,e.address,e.org_id,e.customer_Number,e.meter_Number FROM                                                     "
					+"	  (SELECT c.address,c.customer_Number,c.meter_Number,rg.`org_id` FROM                                                   " 
					+"	       (SELECT a.address,a.customer_Number,a.meter_Number,bo.`org_number` FROM                                          "
					+"	           (SELECT  bm.`address`,bm.`org_id` AS oId,bm.`customer_Number`,bm.`meter_Number` FROM `bus_customer_meter` bm "
					+"		        WHERE bm.`org_id` IN                                                                                        " 
					+"		      (                                                                                                             "
					+"		         SELECT b.`org_id` FROM `bus_org` b WHERE b.`org_number` IN                                                 " 
					+"		         (SELECT o.`org_number` FROM `bus_org` o WHERE o.`year`=? AND o.`import_status`='normal')                       " 
					+"		         AND b.`year`=? AND b.`import_status`='normal'                                                              "
					+"		      )                                                                                                             "
					+"		    ) a LEFT JOIN `bus_org` bo ON a.oId = bo.`org_id`                                                               "
					+"	       ) c                                                                                                              " 
					+"	   LEFT JOIN `bus_org` rg ON c.org_number= rg.org_number                                                                "
					+"	   WHERE rg.year=? AND rg.import_status='normal'                                                                            "
					+"	   ) e                                                                                                                  "
					+"	   WHERE NOT EXISTS                                                                                                     "
					+"	   (                                                                                                                    "
					+"	    SELECT mcb.`meter_id` FROM `bus_customer_meter` mcb WHERE mcb.`year`=?                                              "
					+"	    AND mcb.`org_id`=e.org_id AND mcb.`meter_Number`=e.meter_Number                                                     "
					+"	   )";
		int d = jdbcTemplate.update(sql2, nextyear,nextyear,initYear,nextyear,nextyear);*/
		
	}

	@Override
	public void updateInitbusQuarterlyAssessment(int assessmentQuarter,
			String year, Date initStartTime, Date initEndTime,
			Date bMonthStartTime, Date bMonthEndTime, Date eMonthStartTime,
			Date eMonthEndTime) {
//		Float v = (float) (1+GR/100);
		//通过sql查询增长比例
		String v = " (select 1+(growth_ratio/100) from base_org_type o where o.org_type_id = b.org_type_id) ";
        String s = " (select count(1) from bus_customer_meter c where c.`org_id` = b.`org_id` and c.`year` = b.`year`) ";
		String yearSql = "ROUND(SUM(bm.`water`)/count(bm.`water`) * 12 * "+s+" * "+v+")";
		String quarterSql = "ROUND("+yearSql+"/4)";
		String montherSql = "ROUND("+yearSql+"/12)";


		String sql =" INSERT INTO `bus_quarterly_assessment`                                                                   "
				   +" (                                                                                                        " 
				   +"     assessment_quarter,water_planning_index,org_id,                                                      "
				   +"     is_write_off,bank,bank_account                                                                       " 
				   +" )                                                                                                        " 
				   +" (                                                                                                        " 
				   +"     SELECT ?,"+quarterSql+",b.`org_id`,'no',b.bank,b.bank_account FROM `bus_org` b       "
				   +"     JOIN `bus_meter_read_info` bm ON b.`org_number`=bm.`org_number`                                      " 
                   +"     WHERE b.`is_plan_user`='yes' AND b.`year`=? AND bm.`price_month`>=? AND bm.`price_month`<?           "
                   +"     AND NOT EXISTS                                                                                       "
                   +"        (                                                                                                 " 
                   +"             SELECT bqa.`assessment_id` FROM `bus_quarterly_assessment` bqa                               " 
                   +"             WHERE bqa.`org_id`=b.`org_id` AND bqa.`assessment_quarter`=?                                 " 
                   +"         )                                                                                                "
//                   +"     AND EXISTS                                                                                           " 
//                   +"        (                                                                                                 " 
//                   +"             SELECT bmr.`info_id` FROM `bus_meter_read_info` bmr WHERE b.`org_number`=bmr.`org_number`    " 
//                   +"             AND bmr.`import_status`='normal' AND bmr.`price_month`>=? AND bmr.`price_month`<?            " 
//                   +"         )                                                                                                "
//                   +"     AND EXISTS                                                                                           " 
//                   +"        (                                                                                                 " 
//                   +"             SELECT bmri.`info_id` FROM `bus_meter_read_info` bmri WHERE b.`org_number`=bmri.`org_number` " 
//                   +"             AND bmri.`import_status`='normal' AND bmri.`price_month`>=? AND bmri.`price_month`<?         " 
//                   +"         )                                                                                                "
                   +" GROUP BY b.`org_number`                                                                                  " 
                   +" ) ";
	  jdbcTemplate.update(sql, assessmentQuarter,year,initStartTime,initEndTime,assessmentQuarter);
	  //jdbcTemplate.update(sql, assessmentQuarter,year,initStartTime,initEndTime,assessmentQuarter,bMonthStartTime,bMonthEndTime,eMonthStartTime,eMonthEndTime);
		
	}

	@Override
	public void updateInitbusMonthAssessment(int assessmentMonth,
			String year, Date initStartTime, Date initEndTime,
			Date bMonthStartTime, Date bMonthEndTime, Date eMonthStartTime,
			Date eMonthEndTime) {
		//通过sql查询增长比例
		String v = " (select 1+(growth_ratio/100) from base_org_type o where o.org_type_id = b.org_type_id ) ";
        String s = " (select count(1) from bus_customer_meter c where c.`org_id` = b.`org_id` and c.`year` = b.`year`) ";
		String yearSql = "ROUND(SUM(bm.`water`)/count(bm.`water`) * 12 * "+s+" * "+v+")";
		String quarterSql = "ROUND("+yearSql+"/4)";
		String montherSql = "ROUND("+yearSql+"/12)";
		String sql =" INSERT INTO `bus_month_assessment`                                                                   "
				+" (                                                                                                        "
				+"     assessment_month,water_planning_index,org_id,                                                      "
				+"     is_write_off,bank,bank_account                                                                       "
				+" )                                                                                                        "
				+" (                                                                                                        "
				+"     SELECT ?,"+montherSql+",b.`org_id`,'no',b.bank,b.bank_account FROM `bus_org` b       "
				+"     JOIN `bus_meter_read_info` bm ON b.`org_number`=bm.`org_number`                                      "
				+"     WHERE b.`is_plan_user`='yes' AND b.`year`=? AND bm.`price_month`>=? AND bm.`price_month`<?           "
				+"     AND NOT EXISTS                                                                                       "
				+"        (                                                                                                 "
				+"             SELECT bqa.`assessment_mon_id` FROM `bus_month_assessment` bqa                               "
				+"             WHERE bqa.`org_id`=b.`org_id` AND bqa.`assessment_month`=?                                 "
				+"         )                                                                                                "
//                   +"     AND EXISTS                                                                                           "
//                   +"        (                                                                                                 "
//                   +"             SELECT bmr.`info_id` FROM `bus_meter_read_info` bmr WHERE b.`org_number`=bmr.`org_number`    "
//                   +"             AND bmr.`import_status`='normal' AND bmr.`price_month`>=? AND bmr.`price_month`<?            "
//                   +"         )                                                                                                "
//                   +"     AND EXISTS                                                                                           "
//                   +"        (                                                                                                 "
//                   +"             SELECT bmri.`info_id` FROM `bus_meter_read_info` bmri WHERE b.`org_number`=bmri.`org_number` "
//                   +"             AND bmri.`import_status`='normal' AND bmri.`price_month`>=? AND bmri.`price_month`<?         "
//                   +"         )                                                                                                "
				+" GROUP BY b.`org_number`                                                                                  "
				+" ) ";
		jdbcTemplate.update(sql, assessmentMonth,year,initStartTime,initEndTime,assessmentMonth);
	}

	@Override
	public void updateInitbusHalfYearAssessment(int assessmentQuarter,
			String year,Date initStartTime,Date initEndTime,
			Date bMonthStartTime,Date bMonthEndTime,Date eMonthStartTime,
			Date eMonthEndTime,Float GR){

	}

	@Override
	public List<BusOrgTemp> getPlanYearCountData(String orgNumber,String orgName,String year,
			String busOrgCustomerNumber,String address,String orgTypeId,String userType) throws UnsupportedEncodingException {
		    String sql = " SELECT b.`org_id`,b.`org_number`,b.`org_name`,b.org_type_id,(select org_name from base_org_type o where o.org_type_id = b.org_type_id) org_type_name,bw.`plan_of_water`,bw.`reply_of_water` "
                       + " FROM `bus_org` b  JOIN `bus_annual_plan_for_water` bw ON b.`org_id`=bw.`org_id`                    "
                       + " WHERE b.`year`='"+year+"' AND bw.`is_cancel`='yes'";
			      if(!orgNumber.equals("")){
			      	   sql +=" and b.org_number="+orgNumber;
			      }
			      if(!orgName.equals("")){
			      	   orgName = new String(orgName.getBytes("iso8859-1"),"UTF-8");
			      	   sql +=" and b.org_name='"+orgName+"'";
			      }
			      if(orgTypeId!=null && !orgTypeId.equals("")){
					  orgTypeId = new String(orgTypeId.getBytes("iso8859-1"),"UTF-8");
						sql +=" and b.org_type_id="+orgTypeId+"";
					}
			      if(!busOrgCustomerNumber.equals("")){
			      	   sql +=" and b.org_id in (SELECT bcm.`org_id` FROM `bus_customer_meter` bcm WHERE bcm.`customer_Number`="+busOrgCustomerNumber+")";
			      }
			      if(!address.equals("")){
			      	   address = new String(address.getBytes("iso8859-1"),"UTF-8");
			      	   sql +=" and b.address='"+address+"'";
			      }
			      if(!userType.equals("")){
			      	   sql +=" and b.user_type ="+userType;
			      }
             RowMapper<BusOrgTemp> rowMapper = new RowMapper<BusOrgTemp>() {

				@Override
				public BusOrgTemp mapRow(ResultSet rs, int arg1)
						throws SQLException {
					        BusOrgTemp usOrgTemp = new BusOrgTemp(rs.getLong("org_id"),rs.getInt("org_number"),
							rs.getString("org_name"),rs.getLong("org_type_id"),rs.getString("org_type_name"),rs.getFloat("plan_of_water"),rs.getFloat("reply_of_water"));
					    return usOrgTemp;
				}
			
             };
             
             List<BusOrgTemp> list = jdbcTemplate.query(sql, rowMapper);
             
             return list;
			     
	}

	@Override
	public String getMaxUserPlanYear() {
		String hql = "select max(b.year) from BusOrg b where b.importStatus='normal' and b.isPlanUser='no'";
		String year = (String)sessionFactory.getCurrentSession().createQuery(hql).uniqueResult();
		if(null == year){
			SimpleDateFormat f = new SimpleDateFormat("yyyy");
			Date d = new Date();
			year= f.format(d);

		}
		return year;
	}

	@Override
	public void initOneData(Long orgId,int nextYear,String type) {
		String sql =" INSERT INTO `bus_org` (org_name,org_number,address,is_plan_user,`year`,industry_number,`code`,email,bank,bank_account,"
                   +" remark,org_type_id,`area`,phone,import_status,user_type,postal_address)                                                              "
				   +" SELECT bus.`org_name`,bus.`org_number`,bus.`address`,?,?,bus.`industry_number`,bus.`code`,bus.`email`,bus.`bank`, "
                   +" bus.`bank_account`,bus.`remark`,bus.`org_type_id`,bus.`area`,bus.`phone`,bus.`import_status`,bus.`user_type`,bus.`postal_address`          "
		           +" FROM `bus_org` bus WHERE bus.`org_id`=?";
		jdbcTemplate.update(sql,type,nextYear,orgId);
		
		//客户水表要初值化
		String sql2=" INSERT INTO `bus_customer_meter` (`year`,address,org_id,customer_Number,meter_Number)               "
                   +" (SELECT ?,t.address,o.`org_id`,t.customer_Number,t.meter_Number FROM `bus_org` o RIGHT JOIN (       "
                   +" SELECT b.`org_number`,bm.`address`,bm.`customer_Number`,bm.`meter_Number` FROM `bus_org` b          "
                   +" RIGHT JOIN `bus_customer_meter` bm ON b.`org_id`=bm.`org_id` WHERE bm.`org_id`=?) AS t         "
                   +" ON o.`org_number` = t.org_number WHERE o.year=? )";
		jdbcTemplate.update(sql2,nextYear,orgId,nextYear);
	}

	@Override
	public void updateIsplanYear(String year) {
		int nextyear = Integer.parseInt(year)+1;
		//把所有没有年度计划的计划户变为非计划户
		String sql3 =" UPDATE `bus_org` bu SET bu.`is_plan_user`='no' WHERE bu.`year`=? AND bu.`is_plan_user`='yes' AND bu.`import_status`='normal'            "
                    +" AND NOT EXISTS ( SELECT w.`org_id` FROM `bus_annual_plan_for_water` w WHERE bu.`org_id` = w.`org_id`)    ";
		jdbcTemplate.update(sql3, nextyear);
	}

	@Override
	public void updateSetplanYear(String initYear, Date initStartTime,
			Date initEndTime, Date bMonthStartTime, Date bMonthEndTime,
			Date eMonthStartTime, Date eMonthEndTime, Float checkWater) {
		int nextyear = Integer.parseInt(initYear)+1;
		//把初值化数据标记计划户		
		//去掉年头年尾必须有水量的要求
				String sql =" UPDATE `bus_org` bus SET bus.`is_plan_user`='yes' WHERE bus.year=? AND EXISTS ("
						   +" SELECT te.org_id FROM (SELECT b.`org_id`,b.year,b.`org_number`,SUM(bm.`water`) AS sumWater FROM bus_org b"                  
					       +" JOIN `bus_meter_read_info` bm ON b.`org_number`=bm.`org_number`"                                     
						   +" WHERE b.`year`=? AND b.`import_status`='normal' AND bm.`import_status`='normal'"                     
						   +" AND (bm.`price_month`>= ? AND bm.`price_month`<?)"                                                                                                                                                 
//						   +" AND EXISTS"                                                                                          
//						   +"  ("                                                                                                
//						   +" SELECT bmr.`org_id` FROM `bus_meter_read_info` bmr WHERE bmr.`org_number`=b.`org_number`"    
//						   +"   AND (bmr.`price_month`>= ? AND bmr.`price_month`<? ) AND bmr.`import_status`='normal'"       
//						   +" ) "                                                                                              
//						   +" AND EXISTS "                                                                                         
//						   +" ("                                                                                               
//						   +" SELECT bmri.`org_id` FROM `bus_meter_read_info` bmri WHERE bmri.`org_number`=b.`org_number`" 
//						   +" AND (bmri.`price_month`>= ? AND bmri.`price_month`<? ) AND bmri.`import_status`='normal'"    
//						   +"   )" 
						   +" GROUP BY b.`org_number` HAVING sumWater>=?) te WHERE bus.`org_number`=te.org_number)";
				 jdbcTemplate.update(sql,nextyear,initYear,initStartTime,initEndTime,checkWater);	
				 
				//月平均用水量超过1000吨的也算
				String sql1 =" UPDATE `bus_org` bus SET bus.`is_plan_user`='yes' WHERE bus.year=? AND EXISTS ("
						   +" SELECT te.org_id FROM (SELECT b.`org_id`,b.year,b.`org_number`,SUM(bm.`water`) AS sumWater,COUNT(meter_Number) countMeter FROM bus_org b"                  
					       +" JOIN `bus_meter_read_info` bm ON b.`org_number`=bm.`org_number`"                                     
						   +" WHERE b.`year`=? AND b.`import_status`='normal' AND bm.`import_status`='normal'"                     
						   +" AND (bm.`price_month`>= ? AND bm.`price_month`<?)"                                                                                                                                                 
						   +" GROUP BY b.`org_number` HAVING sumWater/countMeter>=1000) te WHERE bus.`org_number`=te.org_number)";
				 jdbcTemplate.update(sql1,nextyear,initYear,initStartTime,initEndTime);					 
	}

	@Override
	public void updateCosMeter(String initYear) {
		int nextyear = Integer.parseInt(initYear)+1;

		// 把水表号、客户号插入水表客户表
		String sql2 ="  INSERT INTO `bus_customer_meter` (`year`,address,org_id,customer_Number,meter_Number)                                   "
					+"	  SELECT ?,e.address,e.org_id,e.customer_Number,e.meter_Number FROM                                                     "
					+"	  (SELECT c.address,c.customer_Number,c.meter_Number,rg.`org_id` FROM                                                   " 
					+"	       (SELECT a.address,a.customer_Number,a.meter_Number,bo.`org_number` FROM                                          "
					+"	           (SELECT  bm.`address`,bm.`org_id` AS oId,bm.`customer_Number`,bm.`meter_Number` FROM `bus_customer_meter` bm "
					+"		        WHERE bm.`org_id` IN                                                                                        " 
					+"		      (                                                                                                             "
					+"		         SELECT b.`org_id` FROM `bus_org` b WHERE b.`org_number` IN                                                 " 
					+"		         (SELECT o.`org_number` FROM `bus_org` o WHERE o.`year`=? AND o.`import_status`='normal')                       " 
					+"		         AND b.`year`=? AND b.`import_status`='normal'                                                              "
					+"		      )                                                                                                             "
					+"		    ) a LEFT JOIN `bus_org` bo ON a.oId = bo.`org_id`                                                               "
					+"	       ) c                                                                                                              " 
					+"	   LEFT JOIN `bus_org` rg ON c.org_number= rg.org_number                                                                "
					+"	   WHERE rg.year=? AND rg.import_status='normal'                                                                            "
					+"	   ) e                                                                                                                  "
					+"	   WHERE NOT EXISTS                                                                                                     "
					+"	   (                                                                                                                    "
					+"	    SELECT mcb.`meter_id` FROM `bus_customer_meter` mcb WHERE mcb.`year`=?                                              "
					+"	    AND mcb.`org_id`=e.org_id AND mcb.`meter_Number`=e.meter_Number                                                     "
					+"	   )";
		int d = jdbcTemplate.update(sql2, nextyear,nextyear,initYear,nextyear,nextyear);
	}

}