package smartbi.demo.javaquery;

import java.io.BufferedReader;
import java.io.Reader;
import java.security.InvalidParameterException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collections;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import org.apache.log4j.Logger;
import smartbi.connectionpool.ConnectionPool;
import smartbi.freequery.metadata.IJavaQueryData;
import smartbi.freequery.metadata.JavaQueryConfig;
import smartbi.freequery.metadata.JavaQueryOutputField;
import smartbi.freequery.metadata.JavaQueryParameter;
import smartbi.freequery.querydata.CellData;
import smartbi.freequery.querydata.GridData;
import smartbi.net.sf.json.JSONObject;
import smartbi.util.DbUtil;
import smartbi.util.StringUtil;
import smartbi.util.ValueType;

public class CellStringSplitToRows implements IJavaQueryData {
	public static final String CN_DS_ID = "DS_ID"; // config name
	public static final String CN_QUERY_SQL = "QUERY_SQL"; // config name
	public static final String CN_IGNORE_EMPTY_VALUE = "IGNORE_EMPTY_VALUE"; // config name
	public static final String SPLIT_DELIMITER = ",";
	protected static final String ID_OUTPUT_FIELD_1 = "VALUES";
	protected static final String NAME_OUTPUT_FIELD_1 = "values";
	protected static final String ID_PARAMETER_1 = "SQL_PARAMETER_VALUES";
	protected static final String NAME_PARAMETER_1 = "sqlParameterValues";
	protected static final boolean USE_CACHE_DATA = false;
	protected static final Logger LOG = Logger.getLogger(CellStringSplitToRows.class);
	protected Map<String, JavaQueryConfig> configs = new LinkedHashMap<String, JavaQueryConfig>();
	protected List<JavaQueryOutputField> outputFields;
	protected List<JavaQueryParameter> parameters;
	protected String sqlParameterValues;
	protected List<String> dataStore;

	public CellStringSplitToRows() {
		String sql = "select STAT_VALUE from QAR.HB_MONITOR_MAN where COND_ID=? and FLIGHT_DATE=?"
				+ " and TO_AIRPORT_ID=? and LD_AIRPORT_ID=? and FLIGHT_NO=? and FLIGHT_SEGMENT=?";
		addConfig(CN_DS_ID, "数据源ID", "执行SQL所对应的数据源ID", "DS.ODS", true);
		addConfig(CN_QUERY_SQL, "查询SQL", "获取待分隔字符串的查询SQL", sql, true);
		addConfig(CN_IGNORE_EMPTY_VALUE, "忽略空值", "分隔字符串后空白值是否忽略，需要忽略填"true"否则填"false"", "true",
				false);
	}

	/**
	 * 添加一个配置项
	 * 
	 * @param name
	 *            名称
	 * @param alias
	 *            别名
	 * @param desc
	 *            描述
	 * @param defaultValue
	 *            默认值
	 * @param notNull
	 *            是否允许为空
	 */
	private void addConfig(String name, String alias, String desc, String defaultValue,
			boolean notNull) {
		JavaQueryConfig p = new JavaQueryConfig();
		p.setName(name);
		p.setAlias(alias);
		p.setDesc(desc);
		p.setValue(defaultValue);
		p.setNotNull(notNull);
		configs.put(name, p);
	}

	/**
	 * 从保存的字符串中恢复配置信息
	 * 
	 * @param configs
	 *            配置字符串
	 */
	public void loadConfigs(String configStr) {
		if (StringUtil.isNullOrEmpty(configStr)) {
			return;
		}
		JSONObject obj = JSONObject.fromString(configStr);
		configs.get(CN_DS_ID).setValue(obj.has(CN_DS_ID) ? obj.getString(CN_DS_ID) : null);
		configs.get(CN_QUERY_SQL).setValue(
				obj.has(CN_QUERY_SQL) ? obj.getString(CN_QUERY_SQL) : null);
		configs.get(CN_IGNORE_EMPTY_VALUE).setValue(
				obj.has(CN_IGNORE_EMPTY_VALUE) ? obj.getString(CN_IGNORE_EMPTY_VALUE) : null);
	}

	/**
	 * 保存配置信息
	 * 
	 * @return 返回配置字符串
	 */
	public String saveConfigs() {
		JSONObject json = new JSONObject();
		for (JavaQueryConfig config : configs.values()) {
			json.put(config.getName(), config.getValue());
		}
		return json.toString();
	}

	/**
	 * 获取Java查询需要的配置信息
	 */
	public List<JavaQueryConfig> getConfigs() {
		return new ArrayList<JavaQueryConfig>(configs.values());
	}

	/**
	 * 设置配置信息
	 * 
	 * @param key
	 *            名称
	 * @param value
	 *            配置值
	 */
	public void setConfigValue(String key, String value) {
		configs.get(key).setValue(value);
	}

	/**
	 * 设置配置信息
	 */
	public void setConfigValues(Map<String, String> configValues) {
		for (Entry<String, String> config : configValues.entrySet()) {
			configs.get(config.getKey()).setValue(config.getValue());
		}
	}

	/**
	 * 根据配置信息初始化Java查询对象
	 */
	public void init() {
		initOutputFields();
		initParameters();
	}

	/**
	 * 初始化输出字段
	 */
	protected void initOutputFields() {
		outputFields = new ArrayList<JavaQueryOutputField>();
		outputFields.add(new JavaQueryOutputField(ID_OUTPUT_FIELD_1, NAME_OUTPUT_FIELD_1, "values",
				"", ValueType.STRING, ""));
	}

	/**
	 * 初始化查询参数
	 */
	protected void initParameters() {
		parameters = new ArrayList<JavaQueryParameter>();
		parameters.add(new JavaQueryParameter(ID_PARAMETER_1, NAME_PARAMETER_1, "SQL参数值",
				"执行查询SQL所需参数值，值与值之间英文逗号(,)分隔", ValueType.STRING, true));
	}

	/**
	 * 返回参数对象
	 */
	public List<JavaQueryParameter> getParameters() {
		return parameters;
	}

	/**
	 * 返回Java查询的输出字段
	 */
	public List<JavaQueryOutputField> getOutputFields() {
		return outputFields;
	}

	/**
	 * 设置参数值
	 */
	public void setParameterValue(String id, String value, String displayValue) {
		if (ID_PARAMETER_1.equals(id)) {
			sqlParameterValues = value == null ? null : value.replaceAll("^\\'|\\'$", "");
			dataStore = null; // reset
		}
	}

	/**
	 * 返回总行数，返回Integer.MAX_VALUE表示未知总行数
	 */
	public int getRowCount() {
		if (dataStore != null) {
			return dataStore.size();
		} else {
			return Integer.MAX_VALUE;
		}
	}

	/**
	 * 关闭Java查询对象，关闭必要的资源
	 */
	public void close() {
		dataStore = null;
	}

	public synchronized List<String> getValues() throws Exception {
		if (dataStore == null || !USE_CACHE_DATA) {
			Connection conn = null;
			PreparedStatement prep = null;
			ResultSet rs = null;
			try {
				String dsId = configs.get(CN_DS_ID).getValue();
				String sql = configs.get(CN_QUERY_SQL).getValue();
				boolean ignoreEmptyValue = !"false".equals(configs.get(CN_IGNORE_EMPTY_VALUE)
						.getValue());
				conn = ConnectionPool.getInstance().getConnection(dsId);
				prep = conn.prepareStatement(sql);
				if (!StringUtil.isNullOrEmpty(sqlParameterValues)) {
					int count = sql.length() - sql.replaceAll("\\?", "").length();
					String[] values = sqlParameterValues.split(",");
					for (int i = 0, len = Math.min(count, values.length); i < len; i++) {
						prep.setString(i + 1, values[i]);
					}
				}
				LOG.debug(sql + "; params=" + sqlParameterValues);
				rs = prep.executeQuery();
				List<String> list = new ArrayList<String>();
				while (rs.next()) {
					Reader reader = rs.getCharacterStream(1);
					BufferedReader br = new BufferedReader(reader);
					String line;
					while ((line = br.readLine()) != null) {
						String[] items = line.split(SPLIT_DELIMITER);
						for (String item : items) {
							if (ignoreEmptyValue && StringUtil.isNullOrEmpty(item)) {
								continue;
							}
							list.add(ignoreEmptyValue ? item.trim() : item);
						}
					}
				}
				dataStore = Collections.unmodifiableList(list);
			} catch (Exception e) {
				LOG.error(e.getMessage(), e);
				throw e;
			} finally {
				DbUtil.closeDBObject(rs, prep, conn);
			}
		}
		return dataStore;
	}

	/**
	 * 检测输入参数是否合法
	 * 
	 * @param from
	 * @param count
	 */
	protected void assertGetDataParams(int from, int count) {
		if (from < 0 || count < 1) {
			throw new InvalidParameterException(
					"parameter [from] must greater -1 and [count] must greater 0. current from="
							+ from + ", count=" + count);
		}
	}

	/**
	 * 获取指定行的数据
	 */
	public GridData getGridData(int from, int count) {
		assertGetDataParams(from, count);
		List<String> values = null;
		try {
			values = getValues();
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage(), e);
		}
		List<List<CellData>> datas = new ArrayList<List<CellData>>();
		if (values != null && values.size() > 0) {
			int toIndex = Math.min(values.size(), from + count);
			for (String value : values.subList(from, toIndex)) {
				List<CellData> rowData = new ArrayList<CellData>();
				CellData cellData = new CellData();
				cellData.setStringValue(value);
				Double d = 0.0;
				try {
					d = Double.parseDouble(value);
				} catch (NumberFormatException e) {
				}
				cellData.setDoubleValue(d.doubleValue());
				rowData.add(cellData);
				datas.add(rowData);
			}
		}
		GridData grid = new GridData();
		List<String> headers = new ArrayList<String>();
		for (JavaQueryOutputField f : getOutputFields()) {
			headers.add(f.getName());
		}
		grid.setStringHeaders(headers);
		grid.setData(datas);
		return grid;
	}

}
 

package smartbi.demo.javaquery;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import org.apache.commons.math3.distribution.NormalDistribution;
import org.apache.commons.math3.stat.descriptive.DescriptiveStatistics;
import smartbi.freequery.metadata.JavaQueryOutputField;
import smartbi.freequery.querydata.CellData;
import smartbi.freequery.querydata.GridData;
import smartbi.util.ValueType;

/**
 * 通过CellStringSplitToRows的结果集计算正态分布的分组值等<br>
 * 分组数可认为是x轴刻度数量，分组中的值可认为是x轴刻度值(一系列等差的值)
 */
public class NormalDistributionGroup extends CellStringSplitToRows {
	/**
	 * 初始化输出字段
	 */
	protected void initOutputFields() {
		outputFields = new ArrayList<JavaQueryOutputField>();
		outputFields.add(new JavaQueryOutputField("ORDER_NUMBER", "orderNumber", "序号", "",
				ValueType.INTEGER, ""));
		outputFields.add(new JavaQueryOutputField("GROUP_VALUE", "groupValue", "分组值", "",
				ValueType.DOUBLE, ""));
		outputFields.add(new JavaQueryOutputField("FREQUENCY", "frequency", "频率", "",
				ValueType.INTEGER, ""));
		outputFields.add(new JavaQueryOutputField("PROBABILITY_DENSITY", "probabilityDensity",
				"概率密度", "", ValueType.DOUBLE, ""));
	}

	/**
	 * 获取指定行的数据
	 */
	public GridData getGridData(int from, int count) {
		long t1 = System.currentTimeMillis();
		assertGetDataParams(from, count);
		List<String> strValues = null;
		try {
			strValues = getValues();
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage(), e);
		}
		List<Double> values = new ArrayList<Double>();
		DescriptiveStatistics ds = new DescriptiveStatistics();
		for (String strValue : strValues) {
			Double v = 0.0;
			try {
				v = Double.parseDouble(strValue);
			} catch (NumberFormatException e) {
			}
			ds.addValue(v);
			values.add(v);
		}
		List<List<CellData>> datas = new ArrayList<List<CellData>>();
		if (values.size() > 0) {
			Collections.sort(values, new Comparator<Double>() {
				public int compare(Double o1, Double o2) {
					return o1.compareTo(o2); /* order by asc */
				}
			});
			double max = ds.getMax(); // 最大值
			double min = ds.getMin(); // 最小值
			double offset = max - min; // 极差 = 最大值 - 最小值
			Double gc = Math.sqrt(ds.getN());
			long groupCount = gc.longValue(); // 分组数 = ROUNDUP( SQRT(values.size()), 0 )
			if (gc - groupCount > 0.000001) {
				++groupCount;
			}
			double groupStep = offset * 1.0 / groupCount; // 分组组距 = 极差 / 分组数
			groupCount += 2; // 在x轴上加一个比最小值(min)小的值和一个比最大值(max)大的值
			Double firstValue = min - groupStep / 3; // 比最小值(min)小的值
			double mean = ds.getMean(); // 平均值(average)
			double sd = ds.getStandardDeviation(); // 标准偏差
			NormalDistribution nd = new NormalDistribution(mean, sd);
			for (int i = 0, lastJ = 0; i < groupCount; i++) {
				int orderNumber = i + 1;
				Double groupValue = firstValue + groupStep * i;
				int freq = 0;
				for (int j = lastJ; j < values.size(); j++) {
					if (values.get(j) <= groupValue) {
						++freq;
					} else {
						lastJ = j;
						break;
					}
				}
				//
				List<CellData> rowData = new ArrayList<CellData>();
				CellData firstCellData = new CellData();
				firstCellData.setIntValue(orderNumber); // 序号
				rowData.add(firstCellData);
				//
				CellData secondCellData = new CellData();
				secondCellData.setDoubleValue(groupValue); // 分组值
				rowData.add(secondCellData);
				//
				CellData thirdCellData = new CellData();
				thirdCellData.setIntValue(freq); // 频率
				rowData.add(thirdCellData);
				//
				CellData fourthCellData = new CellData();
				fourthCellData.setDoubleValue(nd.density(groupValue)); // 概率密度
				rowData.add(fourthCellData);
				//
				datas.add(rowData);
			}
		}
		GridData grid = new GridData();
		List<String> headers = new ArrayList<String>();
		for (JavaQueryOutputField f : getOutputFields()) {
			headers.add(f.getName());
		}
		grid.setStringHeaders(headers);
		grid.setData(datas);
		long t2 = System.currentTimeMillis();
		LOG.debug("calc normal distribution values: " + (t2 - t1) + "ms.");
		return grid;
	}

}