/*  OpenOlap viewer
 *  パッケージ名：openolap.viewer.dao
 *  ファイル：PostgresColorDAO.java
 *  説明：
 *
 *  作成日: 2004/01/15
 */
package openolap.viewer.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.TreeMap;

import javax.servlet.http.HttpServletRequest;

import openolap.viewer.Color;
import openolap.viewer.Report;
import openolap.viewer.common.CommonSettings;
import openolap.viewer.common.CommonUtils;
import openolap.viewer.common.StringUtil;
import openolap.viewer.controller.RequestHelper;

/**
 *  クラス：PostgresColorDAO
 *  説明：
 */
public class PostgresColorDAO implements ColorDAO {

	Connection conn = null;

	// ********** コンストラクタ **********
	PostgresColorDAO(Connection conn) {
		this.conn = conn;
	}

	// ********** メソッド **********

	public void applyColor(Report report, Connection conn) throws SQLException {

		ArrayList colorList = new ArrayList();
		
		// SQL生成
		String SQL = "";
		SQL += "select ";
		SQL += "    edge_id_combo, ";
		SQL += "    edge_mem_key1, ";
		SQL += "    edge_mem_key2, ";
		SQL += "    edge_mem_key3, ";
		SQL += "    edge_mem_key4, ";
		SQL += "    edge_mem_key5, ";
		SQL += "    edge_mem_key6, ";
		SQL += "    headerFLG, ";
		SQL += "    html_color ";
		SQL += "from ";
		SQL += "    oo_v_color ";
		SQL += "where ";
		SQL += "    report_id=" + report.getReportID() + " ";
		SQL += "order by ";
		SQL += "    edge_id_combo";
//System.out.println("apply Color SQL:" + SQL);					
		
		// SQL実行
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(SQL);

			while ( rs.next() ) {

				// 色づけされたセルの座標を表すTreeMapを作成
				ArrayList axisIdList = StringUtil.splitString(rs.getString("edge_id_combo"),",");
				TreeMap axisIDAndMemberKeyMap = new TreeMap();

				Iterator axisIdIt = axisIdList.iterator();
				int i = 0;
				while (axisIdIt.hasNext()) {
					String axisId = (String) axisIdIt.next();									// 軸ID
					String axisMemberKey = rs.getString("edge_mem_key"+Integer.toString(i+1));	// 上記軸内のメンバーKey
					axisIDAndMemberKeyMap.put(Integer.decode(axisId),axisMemberKey);
					i++;
				}
	
				// Colorオブジェクトを生成
				Color color = new Color( axisIDAndMemberKeyMap, 		// axisIDAndMemberKeyMap 
										  CommonUtils.FLGTobool(rs.getString("headerFLG")), // isHeader
										  rs.getString("html_color"));	// HTMLColor

				// 作成したColorオブジェクトをArrayListに追加
				colorList.add(color);

			}	
	
		} catch (SQLException e) {
			throw e;
		} catch (Exception e){
			e.printStackTrace();
		} finally {
			try {
				if (rs != null){
					rs.close();
				}
			} catch (SQLException e) {
				throw e;
			} finally {
				try {
					if (stmt != null){
						stmt.close();
					}
				} catch (SQLException e) {
					throw e;
				}
			}
		}
		
		// レポートオブジェクトにColorオブジェクトの集合を登録
		report.addColor(colorList);
		
		
	}

	public void registColor(RequestHelper helper, CommonSettings commonSettings) {

		// クライアントから受信した情報を取得
		HttpServletRequest request = helper.getRequest();
		String dtColorInfoListString = (String)request.getParameter("dtColorInfo");
		String hdrColorInfoListString = (String)request.getParameter("hdrColorInfo");

//System.out.println("dtColorInfoListString:" + dtColorInfoListString);
//System.out.println("hdrColorInfoListString:" + hdrColorInfoListString);

	  	Report report = (Report)request.getSession().getAttribute("report");

		// 色情報をクリア
		report.clearColorList();

		// データテーブルの色情報をReportオブジェクトに追加
		ArrayList dtColorInfoList = changeColorList(dtColorInfoListString, false);
		report.addColor(dtColorInfoList);

		// ヘッダの色情報をReportオブジェクトに追加
		ArrayList hdrColorInfoList = changeColorList(hdrColorInfoListString, true);
		report.addColor(hdrColorInfoList);

	}


	public void saveColor(Report report, Connection conn) throws SQLException {

		// 既存の色設定は全て削除してからinsert
		// delete
		this.deleteColor(report, conn);


		Iterator it = report.getColorList().iterator();
		while (it.hasNext()) {
			Color color = (Color) it.next();
			String edgeIdCombo = "";
			String[] edgeMemKey = new String[6];

			TreeMap axisIDAndMemberKeyTree = color.getAxisIDAndMemberKeyMap();
			Iterator keyIt = axisIDAndMemberKeyTree.keySet().iterator();
			int i = 0;
			while (keyIt.hasNext()) {
				if(i>0){
					edgeIdCombo += ",";
				}
				Integer key = (Integer) keyIt.next();
				edgeIdCombo += key.toString();
//System.out.println("key,value" + key + "," + axisHash.get(key));
				edgeMemKey[i] = (String)axisIDAndMemberKeyTree.get(key);
				i++;
			}

			Statement stmt = conn.createStatement();			
			try {
				// insert
				String SQL = "";
				SQL =  "";
				SQL += "INSERT INTO oo_v_color ";
				SQL += "       (report_id, edge_id_combo,";
				for (int j = 0; j < edgeMemKey.length; j++) {
					SQL +=            "edge_mem_key" + (j+1) +",";
				}
				SQL += "       headerflg, html_color) ";
				SQL += "values ( ";
				SQL +=                report.getReportID() + ", ";
				SQL +=          "'" + edgeIdCombo + "', ";
				
				for (int j = 0; j < edgeMemKey.length; j++) {
					SQL +=            edgeMemKey[j] + ", ";
				}
	
				SQL +=          "'" + CommonUtils.boolToFLG(color.isHeader()) + "', ";
				SQL +=          "'" + color.getHtmlColor() + "'"; 
	            SQL +=        ")";
//System.out.println("saveColor:" + SQL);
	
				int insertCount = stmt.executeUpdate(SQL);
				if (insertCount != 1) {
					throw new IllegalStateException();
				}

			} catch (IllegalStateException e) {
				throw e;
			} catch (SQLException e) {
				throw e;
			} catch (Exception e) { 
				e.printStackTrace();
			} finally {
				if (stmt != null) {
					stmt.close();
				}
			} 
		}
	}



	public void deleteColor(Report report, Connection conn) throws SQLException {

		String SQL = "";
		SQL =  "";
		SQL += "delete from oo_v_color ";
		SQL += "where ";
		SQL += "    report_id=" + report.getReportID();

		Statement stmt = conn.createStatement();
		try {
			stmt.executeUpdate(SQL);
		} catch (SQLException e) {
			throw e;
		} catch (Exception e) { 
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				stmt.close();
			}
		} 

	}

	// ********** privateメソッド **********

	// カラー情報文字列より、カラーオブジェクトのArrayListを生成する

	private ArrayList changeColorList(String sourceString, boolean isHeader) {

		ArrayList colorList = new ArrayList();	// Colorオブジェクトのリスト
		Color color = null;

		// データテーブルセルの色設定を追加
		ArrayList colorInfoList = StringUtil.splitString(sourceString, ",");	// 「ID.Key:・・・:ID.Key;color」リスト
		Iterator colorInfoIt = colorInfoList.iterator();


		int i = 0;		
		while (colorInfoIt.hasNext()) {
			TreeMap idKeyList = new TreeMap();	// 軸ID(Key)と軸メンバKey(Value)

			String colorInfo = (String) colorInfoIt.next();						// 「ID.Key:・・・:ID.Key;color」文字列
			ArrayList comboKeyColorList = StringUtil.splitString(colorInfo,";");	// 「ID.Key:・・・:ID.Key」,「color」リスト

			String comboKeyListString = (String) comboKeyColorList.get(0);	// 「ID.Key:・・・:ID.Key」文字列
			String colorString = (String) comboKeyColorList.get(1);			// 「color」文字列

			ArrayList comboKeyList = StringUtil.splitString(comboKeyListString, ":");	// 「ID.Key」のリスト
			Iterator comboKeyIte = comboKeyList.iterator();

			while (comboKeyIte.hasNext()) {
				String comboKeyString = (String) comboKeyIte.next();
				
				ArrayList axisIDAndKey = StringUtil.splitString(comboKeyString, ".");
				
				idKeyList.put(Integer.decode((String)axisIDAndKey.get(0)), (String)axisIDAndKey.get(1));	// 軸ID,メンバーキー

			}

			color = new Color( idKeyList, 			// axisIDAndMemberKeyMap 
							    isHeader, 			// isHeader
							    colorString);		// HTMLColor

			colorList.add(color);
			i++;
		}

//System.out.println("size:" + colorList.size());
//Iterator it = colorList.iterator();
//while (it.hasNext()) {
//	Color oColor = (Color) it.next();
//
//	TreeMap comboKeyHash = oColor.getAxisIDAndMemberKeyMap();
//	Iterator comboKeyIt = comboKeyHash.keySet().iterator();
//	while (comboKeyIt.hasNext()) {
//		String key = (String) comboKeyIt.next();
//		System.out.println("key,value:" + key + "," + comboKeyHash.get(key));
//	}
//	System.out.println("color:" + oColor.getHtmlColor());
//}

		return colorList;

	}

}
