package com.saga.test;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.hadoop.conf.Configuration;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.zillion.util.log.LogUtil;

import com.saga.util.Constant;
import com.sagacloud.json.JSONArray;
import com.sagacloud.json.JSONObject;
import com.zillion.database.agent.ZillionAgent;

public class Sunyifu {
	static Map<String, String> meterList = new HashMap<String, String>();
	static Map<String, Map<String, Map<String, Double>>> dataList = new HashMap<String, Map<String, Map<String, Double>>>();
	static long[] funcs = { 11011, 11012, 11013, 11014 };
	static ZillionAgent agent;

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		try {
			String resource = "/hbase-site.xml";
			Configuration configuration = new Configuration();

			configuration.addResource(new FileInputStream(System.getProperty("user.dir") + resource));
			String zookeeper_connectionAddress = "zookeeper1:2181,zookeeper2:2181,zookeeper3:2181";
			int zookeeper_sessionTimeout = 300000;
			agent = new ZillionAgent(configuration, zookeeper_connectionAddress, zookeeper_sessionTimeout);
			agent.Start();
			Thread.sleep(10L);
			// for (int i = 0; i < 20; i++) {
			// Set_buildingcomputetime(agent, "db_time_data",
			// "fjd_0_buildingcomputetime", "1101080002",
			// "20170701000000");
			// Thread.sleep(1L);
			// }
			// Thread.sleep(Constant.Millisecond.MINUTE_30);
			String timefrom = "20170701000000";
			String timeto = "20180901000000";
			// long[] funcs = { 11011, 11012, 11013, 11014 };
			GetPointList(agent, "db_time_data", "dy_pointlist", "1101080002");

			Calendar calendarFrom = Calendar.getInstance();
			Calendar calendarTo = Calendar.getInstance();
			Date from = Constant.parse(timefrom);
			Date to = Constant.parse(timeto);
			calendarFrom.setTime(from);
			calendarTo.setTime(to);
			while (calendarFrom.before(calendarTo)) {
				Date datetmp = calendarFrom.getTime();
				calendarFrom.add(Calendar.MONTH, 1);

				export2(Constant.format(datetmp), Constant.format(calendarFrom.getTime()));
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			LogUtil.error("Main: " + e.toString());
		}
	}

	private static void export2(String timefrom, String timeto) {
		try {
			LogUtil.info("get data begin...");
			for (Entry<String, String> m : meterList.entrySet()) {
				dataList.put(m.getKey(), new HashMap<String, Map<String, Double>>());
				for (long funcid : funcs) {
					dataList.get(m.getKey()).put(String.valueOf(funcid), new HashMap<String, Double>());
					try {
						GetFJD(agent, "db_time_data", "fjd_0_near_15min", "1101080002", m.getKey(), funcid, timefrom,
								timeto);
						Thread.sleep(10L);
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			}
			LogUtil.info("get data end...");
			LogUtil.info("insert excel start...");
			Calendar calendarFrom = Calendar.getInstance();
			Calendar calendarTo = Calendar.getInstance();
			Date from = Constant.parse(timefrom);
			Date to = Constant.parse(timeto);
			calendarFrom.setTime(from);
			calendarTo.setTime(to);

			// 创建HSSFWorkbook对象(excel的文档对象)
			HSSFWorkbook wb = new HSSFWorkbook();
			for (long funcid : funcs) {
				// 建立新的sheet对象（excel的表单）
				LogUtil.info("create sheet " + funcid);
				HSSFSheet sheet = wb.createSheet(String.valueOf(funcid));
				int col = 0;
				// 在sheet里创建第二行
				HSSFRow row = sheet.createRow(1);
				for (Entry<String, String> m : meterList.entrySet()) {
					col++;

					// 创建单元格并设置单元格内容
					row.createCell(col).setCellValue(m.getKey());

				}
				calendarFrom.setTime(from);
				int r = 1;
				while (calendarFrom.before(calendarTo)) {
					col = 0;
					r++;
					row = sheet.createRow(r);
					row.createCell(col).setCellValue(Constant.format(calendarFrom.getTime()));
					for (Entry<String, String> m : meterList.entrySet()) {
						col++;
						if (dataList.get(m.getKey()).get(String.valueOf(funcid))
								.get(Constant.format(calendarFrom.getTime())) != null) {
							row.createCell(col).setCellValue(dataList.get(m.getKey()).get(String.valueOf(funcid))
									.get(Constant.format(calendarFrom.getTime())));
						}
					}
					calendarFrom.add(Calendar.MINUTE, 15);
				}
			}

			// 输出Excel文件
			OutputStream output = new FileOutputStream("d:/data" + timefrom.substring(0, 6) + ".xls");
			wb.write(output);
			output.close();
			LogUtil.info("insert excel end...");
		} catch (Exception e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
	}

	private static void GetFJD(ZillionAgent agent, String db, String tb, String building, String meter, long funcid,
			String query_from, String query_to) throws Exception {

		JSONObject Criteria = new JSONObject();
		Criteria.put("building", building);
		Criteria.put("meter", meter);
		Criteria.put("funcid", funcid);
		JSONObject range = new JSONObject();
		range.put("$gte", query_from);
		range.put("$lte", query_to);
		Criteria.put("data_time", range);
		JSONObject wrapperObject = new JSONObject();
		wrapperObject.put("QueryType", "select");
		wrapperObject.put("Database", db);
		wrapperObject.put("Datatable", tb);
		wrapperObject.put("Criteria", Criteria);
		JSONObject queryResult = agent.Query(wrapperObject);
		JSONArray resultContent = (JSONArray) queryResult.get("Content");

		LogUtil.info("GetFJD" + " " + db + " " + tb + " " + building + " " + meter + " " + funcid + " " + query_from
				+ " " + query_to);
		for (int i = 0; i < resultContent.size(); i++) {
			JSONObject dataItem = (JSONObject) resultContent.get(i);

			String receivetime = (String) dataItem.get("data_time");

			Double data = (Double) dataItem.get("data_value");
			dataList.get(meter).get(String.valueOf(funcid)).put(receivetime, data);
			// LogUtil.info(("receivetime " + receivetime + " data:" + data));
		}
	}

	private static void GetPointList(ZillionAgent agent, String db, String tb, String building) throws Exception {

		JSONObject Criteria = new JSONObject();
		Criteria.put("building", building);

		JSONObject wrapperObject = new JSONObject();
		wrapperObject.put("QueryType", "select");
		wrapperObject.put("Database", db);
		wrapperObject.put("Datatable", tb);
		wrapperObject.put("Criteria", Criteria);
		JSONObject queryResult = agent.Query(wrapperObject);
		JSONArray resultContent = (JSONArray) queryResult.get("Content");

		for (int i = 0; i < resultContent.size(); i++) {
			JSONObject dataItem = (JSONObject) resultContent.get(i);
			if (in(funcs, Long.parseLong(String.valueOf(dataItem.get("funcid"))))) {
				meterList.put(String.valueOf(dataItem.get("meter")), null);
			}
		}
	}

	private static void Set_buildingcomputetime(ZillionAgent agent, String db, String tb, String building,
			String compute_time) throws Exception {
		JSONObject Criteria = new JSONObject();
		Criteria.put("building", building);
		JSONObject Set = new JSONObject();
		Set.put("compute_time", compute_time);
		JSONObject wrapperObject = new JSONObject();
		wrapperObject.put("QueryType", "update");
		wrapperObject.put("Database", db);
		wrapperObject.put("Datatable", tb);
		wrapperObject.put("Criteria", Criteria);
		wrapperObject.put("Set", Set);
		agent.Query(wrapperObject);
		LogUtil.info(wrapperObject.toFormatString());
	}

	private static boolean in(long[] funcs, long funcid) {
		for (long func : funcs) {
			if (func == funcid) {
				return true;
			}
		}
		return false;
	}
}
