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 ShenQI {
	public ShenQI() {

	}

	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 Map<String, String> funcList = new HashMap<String, String>();
	static ZillionAgent agent;
	static String building = "1101010001";

	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 = new ZillionAgent(configuration);
			agent.Start();
			Thread.sleep(10L);
			// 创建HSSFWorkbook对象(excel的文档对象)

			GetPointList(agent, "db_time_data", "dy_pointlist", building);

			// String timefrom = "20171001000000";
			// String timeto = "20171101000000";
			// export2(timefrom, timeto);
			// timefrom = "20171101000000";
			// timeto = "20171201000000";
			// export2(timefrom, timeto);
			// timefrom = "20180301000000";
			// timeto = "20180401000000";
			// export2(timefrom, timeto);
			// timefrom = "20180701000000";
			// timeto = "20180801000000";
			// export2(timefrom, timeto);
			String timefrom = "20171001000000";
			String timeto = "20180814000000";
			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()));
			}
			// export2(timefrom, timeto);

		} catch (Exception e) {
			// TODO Auto-generated catch block
			LogUtil.error("Main: " + e.toString());
		}
	}

	private static void export2(String timefrom, String timeto) {
		// TODO Auto-generated method stub

		try {
			LogUtil.info("get data begin...");
			LogUtil.info("from " + Constant.parse(timefrom) + " to " + Constant.parse(timeto));

			HSSFWorkbook wb = new HSSFWorkbook();

			for (Entry<String, String> m : meterList.entrySet()) {
				LogUtil.info("create sheet " + m.getKey());
				HSSFSheet sheet = wb.createSheet(m.getKey());
				// 抬头
				HSSFRow row = sheet.createRow(0);
				row.createCell(1).setCellValue("meter");
				row.createCell(2).setCellValue("funcid");
				row.createCell(3).setCellValue("receivetime");
				row.createCell(4).setCellValue("data");
				int r = 0;
				for (Entry<String, String> funcid : funcList.entrySet()) {

					try {
						Map<String, Double> data = new HashMap<String, Double>();
						data = GetFJD(agent, "db_time_data", "fjd_0_near_15min", building, m.getKey(),
								Long.parseLong(funcid.getKey()), timefrom, timeto);
						// 数据
						for (Entry<String, Double> d : data.entrySet()) {
							r++;
							row = sheet.createRow(r);
							row.createCell(1).setCellValue(m.getKey());
							row.createCell(2).setCellValue(funcid.getKey());
							row.createCell(3).setCellValue(d.getKey());
							row.createCell(4).setCellValue(d.getValue());
						}
						Thread.sleep(10L);
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			}
			LogUtil.info("get data end...");
			LogUtil.info("insert excel start...");

			// 输出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 export(String timefrom, String timeto) {
		// TODO Auto-generated method stub

		try {
			LogUtil.info("get data begin...");
			LogUtil.info("from " + Constant.parse(timefrom) + " to " + Constant.parse(timeto));

			HSSFWorkbook wb = new HSSFWorkbook();
			for (Entry<String, String> m : meterList.entrySet()) {
				for (Entry<String, String> funcid : funcList.entrySet()) {

					// 建立新的sheet对象（excel的表单）
					// LogUtil.info("create sheet " + timefrom.substring(0,
					// 8)+"~"+ timeto.substring(0, 8));
					// HSSFSheet sheet = wb.createSheet(timefrom.substring(0,
					// 8)+"~"+ timeto.substring(0, 8));
					LogUtil.info("create sheet " + m.getKey() + "-" + funcid.getKey());
					HSSFSheet sheet = wb.createSheet(m.getKey() + "-" + funcid.getKey());
					// 抬头
					HSSFRow row = sheet.createRow(1);
					row.createCell(1).setCellValue("meter");
					row.createCell(2).setCellValue("funcid");
					row.createCell(3).setCellValue("receivetime");
					row.createCell(4).setCellValue("data");
					int r = 0;

					try {
						Map<String, Double> data = new HashMap<String, Double>();
						data = GetOriginal(agent, "db_time_data", "original_month", building, m.getKey(),
								Long.parseLong(funcid.getKey()), timefrom, timeto);
						// 数据
						for (Entry<String, Double> d : data.entrySet()) {

							r++;
							row = sheet.createRow(r);
							row.createCell(1).setCellValue(m.getKey());
							row.createCell(2).setCellValue(funcid.getKey());
							row.createCell(3).setCellValue(d.getKey());
							row.createCell(4).setCellValue(d.getValue());
						}
						Thread.sleep(10L);
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			}
			LogUtil.info("get data end...");
			LogUtil.info("insert excel start...");

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

	private static Map<String, Double> GetFJD(ZillionAgent agent, String db, String tb, String building, String meter,
			long funcid, String query_from, String query_to) throws Exception {
		Map<String, Double> result = new HashMap<String, Double>();
		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");
			result.put(receivetime, data);
			// dataList.get(meter).get(String.valueOf(funcid)).put(receivetime,
			// data);
			// LogUtil.info(("receivetime " + receivetime + " data:" + data));
		}
		return result;
	}

	private static Map<String, Double> GetOriginal(ZillionAgent agent, String db, String tb, String building,
			String meter, long funcid, String query_from, String query_to) throws Exception {
		Map<String, Double> result = new HashMap<String, Double>();
		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("receivetime", 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("GetOriginal" + " " + 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("receivetime");
			Double data = (Double) dataItem.get("data");
			result.put(receivetime, data);
			// dataList.get(meter).get(String.valueOf(funcid)).put(receivetime,
			// data);
			// LogUtil.info(("receivetime " + receivetime + " data:" + data));
		}
		return result;
	}

	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);

			meterList.put(String.valueOf(dataItem.get("meter")), null);
			funcList.put(String.valueOf(dataItem.get("funcid")), 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;
	}
}
