2016-09-18 Text，2016-09-18text

2016-09-18 Text，2016-09-18text

public void getHighConsMeterData(String jssj) throws Exception{
		
		 HashMap<String, String> map = new HashMap<String, String>();
		 
		 map.put("1"   ,"'9010'" ); // 正向有功總
		 map.put("9"   ,"'9011'" ); // 正向有功總尖
		 map.put("10"  ,"'9012'" ); // 正向有功總峰
		 map.put("12"  ,"'9013'" ); // 正向有功總谷
		 map.put("11"  ,"'9014'" ); // 正向有功總平

		 map.put("5"   ,"'9020'" ); // 反向有功總
		 map.put("13"  ,"'9021'" ); // 反向有功總尖
		 map.put("14"  ,"'9022'" ); // 反向有功總峰
		 map.put("16"  ,"'9023'" ); // 反向有功總平
		 map.put("15"  ,"'9024'" ); // 反向有功總谷

		 map.put("2"   ,"'9110'" ); // 正向無功總
		 map.put("6"   ,"'9120'" ); // 反向無功總
		 map.put("3"   ,"'9130'" ); // 無功(Q1象限)
		 map.put("4"   ,"'9140'" ); // 無功(Q2象限)
		 map.put("7"   ,"'9150'" ); // 無功(Q3象限)
		 map.put("8"   ,"'9160'" ); // 無功(Q4象限)

			Date datetmp = new Date();
		int hour = datetmp.getHours();
		String datatime = jssj;
		String columnName ="r1"; 
		// 判斷時間
		if(hour<12){
			datatime = jssj + " 000000";
			columnName ="r1";
		}
		else{
			datatime = jssj + " 120000";
			columnName ="r49";
		}
		
		 hql.setHql(" drop table if exists prepay_high_cons_upload_data ");
		 hql.executeUpdate();
		 
		 StringBuffer sb = new StringBuffer();
		 sb.append(" create table prepay_high_cons_upload_data row format delimited fields terminated by ?  as   ");
		 sb.append(" select coll_obj_id,                                           ");
		 sb.append("        org_no,                                                ");
		 sb.append("        coll_item_code,                                        ");
		 sb.append("        '"+ datatime +"' data_time,                            ");
		 sb.append("        data_value,                                            ");
		 sb.append("        '"+ jssj +"' write_time				                   ");
		 sb.append("  from (				                                       ");

		 Iterator iter = map.entrySet().iterator();
		 while (iter.hasNext()) {
			 
		     Map.Entry entry = (Map.Entry) iter.next();
		     String key = (String) entry.getKey();
		     String val = (String) entry.getValue();
		    if(iter.hasNext()){
				
				sb.append(" select pc.coll_obj_id,                                        ");
				sb.append("        pc.org_no,                                             ");
				sb.append("        "+val+" coll_item_code,                                ");
				sb.append("        em.data_date data_time,                                ");
				sb.append("        COALESCE(em."+ columnName +",em.r1) data_value         ");
				sb.append("   from e_mp_read_curve_prepay em                              ");
				sb.append("   join prepay_collobjid_data_usual pc on pc.sp_id = em.id     ");
				sb.append("  where pc.cons_sort_code != '02'                              ");
		        sb.append("    and pc.cons_sort_code != '03'                              ");
				sb.append("    and em."+columnName+" is not null                          ");
				sb.append("    and em.data_type = '"+ key +"'                             ");
				sb.append(" union all                                                     ");
		   	 }
		     else{
		    	 
			    sb.append(" select pc.coll_obj_id,                                        ");
			    sb.append("        pc.org_no,                                             ");
			    sb.append("        "+val+" coll_item_code,                                ");
			    sb.append("        em.data_date data_time,                                ");
				sb.append("        COALESCE(em."+ columnName +",em.r1) data_value         ");
			    sb.append("   from e_mp_read_curve_prepay em                              ");
			    sb.append("   join prepay_collobjid_data_usual pc on pc.sp_id = em.id     ");
			    sb.append("  where pc.cons_sort_code != '02'                              ");
		        sb.append("    and pc.cons_sort_code != '03'                              ");
			    sb.append("    and em."+columnName+" is not null                          ");
			    sb.append("    and em.data_date = '"+ jssj +"'                            ");
			    sb.append("    and em.data_type = '"+ key +"'                             ");
		    }
	     }
		 sb.append("    ) a                                                               ");
		
		hql.setHql(sb.toString());
		hql.setString(1, Constants.SEPARATOR);
		hql.executeUpdate();
	}