package sqlquery;

import baseclass.PaoCust;
import baseclass.tools.Json;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONException;
import com.alibaba.fastjson.JSONObject;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.XMLWriter;
import p2.pao.DBShortcutProvider;
import p2.pao.PaoRemote;
import p2.pao.PaoSet;
import p2.pao.PaoSetRemote;
import p2.security.UserInfo;
import p2.util.P2AppException;
import p2.util.P2Exception;

import java.io.IOException;
import java.io.StringWriter;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
public class sqlquery extends PaoCust {
    String[] fieldnames = null;
    String[] field = {"recversion", "rmkenable"};
	HashMap<String, String> parammap = new HashMap<String, String>();

	public sqlquery(PaoSet arg0) {
		super(arg0);
	}

	@Override
	public void init() throws P2Exception {
		super.init();
		 
		if(!toBeAdded()){
			setFieldFlag("SQLNAME", READONLY,true);
			if ("2,4".contains(getString("FTYPE"))) {
				setFieldFlag("DATATYPE", READONLY, true);
			}
		}
	}

	/**
	 * 查询测试
	 * 
	 * @throws P2Exception
	 */
	public void check() throws P2Exception {
		try {
			setValue("show", "<xmp>" + runsql(false) + "</xmp>");
		} catch (IOException e) {
			e.printStackTrace();
			throw new P2AppException("", e.getMessage());
		} catch (JSONException e) {
			e.printStackTrace();
			throw new P2AppException("", e.getMessage());
		}
		setValue("dataurl", "", 11L);
		setValue("dataurl", createXmlUrl());
	}

	public String createXmlUrl() throws P2Exception {
		
		String url = "http://" + "192.168.1.55" + ":8001/rest/sqlquerydata";
		PaoSetRemote FINEREPORTPARAM = getPaoSet("SQLQUERYPARAM");
		FINEREPORTPARAM.setOrderBy("frownum");
		FINEREPORTPARAM.reset();
		String param = "";
		int i = 0;
		while (FINEREPORTPARAM.getPao(i) != null) {
			String paramname = FINEREPORTPARAM.getPao(i).getString("PARAMNAME");// 参数名称
			if ("".equals(param)) {
				param = param + paramname + "=${" + paramname + "}";
			} else {
				param = param + ";;;;" + paramname + "=${" + paramname + "}";
			}
			i++;
		}
		if (i > 0) {
			param = "?param=%22" + "SQLNAME=" + getString("SQLNAME") + ";;;;"
					+ param + "%22";
		} else {
			param = "?param=%22" + "SQLNAME=" + getString("SQLNAME") + "%22";
		}
		return url + param;
	}

	public String runsql(boolean isformal) throws P2Exception, IOException,
			JSONException {
		String sql = getString("sql");
		PaoSetRemote FINEREPORTPARAM = getPaoSet("SQLQUERYPARAM");
		FINEREPORTPARAM.setOrderBy("frownum");
		FINEREPORTPARAM.reset();

		if (isformal) {
			int flag = 0;
			while (sql.contains("?")) {
				if (FINEREPORTPARAM.getPao(flag) != null) {
					String PARAMNAME = FINEREPORTPARAM.getPao(flag).getString(
							"PARAMNAME");
					String PARAMVALUE = parammap.get(PARAMNAME);
					sql = sql.replaceFirst("\\?", "'" + PARAMVALUE + "'");
					flag++;
				}
			}
		} else {
			int flag = 0;
			while (sql.contains("?")) {
				if (FINEREPORTPARAM.getPao(flag) != null) {
					String PARAMVALUE = FINEREPORTPARAM.getPao(flag).getString(
							"PARAMVALUE");
					sql = sql.replaceFirst("\\?", "'" + PARAMVALUE + "'");
					flag++;
				} else {
					throw new P2AppException("", "参数设置不匹配");
				}
			}
		}

		ArrayList<HashMap<String, Object>> list = runQuerySQL(sql);

		String datatype = getString("datatype");
		if ("XML".equals(datatype)) {
			return getXML(list, isformal);
		} else if ("JSON".equals(datatype)) {
			if ("3".equals(getString("ftype"))) {
				return getFlexigridJSON(list, isformal);
			} else if ("4".equals(getString("ftype"))) {
				return getJSON(list, isformal);
			} else {
				return getJSON(list, isformal);
			}
		}
		return "";
	}

	public String getXML(ArrayList<HashMap<String, Object>> list,
			boolean isformal) throws IOException {
		Document document = DocumentHelper.createDocument();
		Element root = document.addElement("root");
		if (list.size() > 0) {
			for (int i = 0; i < list.size() && (isformal || i < 1); i++) {
				HashMap<String, Object> map = list.get(i);
				Element row = root.addElement("row");
				for (String fieldname : fieldnames) {
					if (Arrays.asList(field).contains(fieldname)) {
						continue;
					}
					Element filed = row.addElement(fieldname);

					if (map.get(fieldname) == null) {
						filed.setText("");
					} else {
						filed.setText(map.get(fieldname).toString());
					}
				}
			}
		} else {
			Element row = root.addElement("row");
			for (String fieldname : fieldnames) {
				if (Arrays.asList(field).contains(fieldname)) {
					continue;
				}
				row.addElement(fieldname);
			}
		}
		OutputFormat format = OutputFormat.createPrettyPrint();
		StringWriter writer = new StringWriter();
		// 创建一个文件输出流
		XMLWriter xmlwriter = new XMLWriter(writer, format);
		xmlwriter.write(document);
		String returnValue = writer.toString();
		writer.close();
		return returnValue;

	}

	public String getJSON(ArrayList<HashMap<String, Object>> list,
			boolean isformal) throws IOException, JSONException {
		JSONArray array = new JSONArray();
		if (list.size() > 0) {
			for (int i = 0; i < list.size() && (isformal || i < 1); i++) {
				HashMap<String, Object> map = list.get(i);
				JSONObject object = new JSONObject();
				for (String fieldname : fieldnames) {
					if (Arrays.asList(field).contains(fieldname)) {
						continue;
					}
					if (map.get(fieldname) == null) {
						object.put(fieldname, "");
					} else {
						object.put(fieldname, map.get(fieldname));
					}
				}
				array.add(object);
			}
		} else {
			JSONObject object = new JSONObject();
			for (String fieldname : fieldnames) {
				if (Arrays.asList(field).contains(fieldname)) {
					continue;
				}
				object.put(fieldname, "");
			}
			array.add(object);
		}
		return Json.formatJson(array.toString());
	}

	public String getFlexigridJSON(ArrayList<HashMap<String, Object>> list,
			boolean isformal) throws IOException, JSONException {
		JSONObject jsonObject = new JSONObject();
		jsonObject.put("page", 1);
		jsonObject.put("total", list.size());

		JSONArray rows = new JSONArray();

		for (int i = 0; i < list.size() && (isformal || i < 1); i++) {
			HashMap<String, Object> map = list.get(i);
			JSONObject row = new JSONObject();
			row.put("id", "row" + (i + 1));

			JSONArray cell = new JSONArray();

			for (String fieldname : fieldnames) {
				if (Arrays.asList(field).contains(fieldname)) {
					continue;
				}
				if (map.get(fieldname) == null) {
					cell.add("");
				} else {
					cell.add(map.get(fieldname));
				}
			}
			row.put("cell", cell);
			rows.add(row);
		}
		jsonObject.put("rows", rows);
		return  Json.formatJson(jsonObject.toString());
	}

	@Override
	public ArrayList<HashMap<String, Object>> runQuerySQL(String sqlFormat)
			throws P2Exception {
		UserInfo useInfo = getUserInfo();
		DBShortcutProvider dbShortcut = new DBShortcutProvider();
		dbShortcut.connect(useInfo.getConnectionKey());
		ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
		try {
			ResultSet rs = dbShortcut.executeQuery(sqlFormat);
			ResultSetMetaData metaData = rs.getMetaData();// 栏位名称
			int count = metaData.getColumnCount();// 栏位数
			fieldnames = new String[count];
			for (int i = 1; i <= count; i++) {
				fieldnames[i - 1] = metaData.getColumnLabel(i);
			}
			while (rs.next()) {
				HashMap<String, Object> map = new HashMap<String, Object>();
				for (String fieldname : fieldnames) {
					map.put(fieldname, rs.getObject(fieldname));
				}
				list.add(map);
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				dbShortcut.commit();
				dbShortcut.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	/**
	 * 参数生成
	 * 
	 * @throws P2Exception
	 */
	public void createparam() throws P2Exception {
		String sql = getString("sql");
		int count = 0;
		while (sql.contains("?")) {
			sql = sql.replaceFirst("\\?", "");
			count = count + 1;
		}
		PaoSetRemote paoSet = getPaoSet("SQLQUERYPARAM");
		if (count != paoSet.count()) {
			paoSet.deleteAll();
			paoSet.save();
			while (count > 0) {
				PaoRemote pao = paoSet.addAtEnd();
				pao.setValue("frownum", paoSet.count());
				pao.setValue("OWNERID", getUniqueIDValue());
				pao.setValue("PARAMNAME", "PARAM" + paoSet.count());
				paoSet.save();
				count--;
			}
		}
	}

	@Override
	public void fieldAction(Object paoField, String fieldname)
			throws P2Exception {
		super.fieldAction(paoField, fieldname);
		if ("DATAURL".equals(fieldname)) {
			/*String HOSTINTRANETPORT = getSysVarsByUser()
					.getString("HOSTINTRANETPORT");*/
			String dataurl = getString("DATAURL");
			if ("4".equals(getString("ftype"))) {// datatable表格展示
				String url = "http://" + "192.168.1.55" + ":"
						+ "8088"
						+ "/meida/datatables/datatables.html?url=" + dataurl;
				getPaoField("DATATABLEIFRAMEURL").setValue(url);
			} else if ("2".equals(getString("ftype"))) {// 帆软报表
				String url = getSysVarsByUser().getString("FINEREPORTURL");
				getPaoField("DATATABLEIFRAMEURL").setValue(
						url + "?reportlet=" + getSite() + "/"
								+ getString("SQLNAME") + ".cpt");
			}
		} else if ("FTYPE".equals(fieldname)) {
			if ("2".equals(getString("ftype"))) {
				setValue("DATATYPE", "XML", 11L);
			} else if ("4".equals(getString("ftype"))) {
				setValue("DATATYPE", "JSON", 11L);
			}
		}

	}

}
