/*  OpenOlap viewer
 *  パッケージ名：openolap.viewer.dao
 *  ファイル：PostgresReportDAO.java
 *  説明：
 *
 *  作成日: 2004/01/07
 */
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.HashMap;
import java.util.Iterator;

import javax.servlet.http.HttpServletRequest;

import openolap.viewer.Axis;
import openolap.viewer.Cube;
import openolap.viewer.Edge;
import openolap.viewer.Measure;
import openolap.viewer.Report;
import openolap.viewer.common.CommonSettings;
import openolap.viewer.common.Constants;
import openolap.viewer.common.StringUtil;
import openolap.viewer.controller.RequestHelper;


/**
 *  クラス：PostgresReportDAO
 *  説明：
 */
public class PostgresReportDAO implements ReportDAO {

	Connection conn = null;
	DAOFactory daoFactory = DAOFactory.getDAOFactory();

	// ********** コンストラクタ **********

	PostgresReportDAO(Connection conn) {
		this.conn = conn;
	}


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

	public Report getInitialReport(String cubeSeq, CommonSettings commonSettings) throws SQLException {

		String reportId = getInitialReportID(conn);
		String reportName = Report.getInitialReportName();
		Report report = getReport(reportId, reportName, cubeSeq, commonSettings);
		
		report.setNewReport(true);// 新規レポートとして設定
		
		return report;
	}

	public Report getReport(String reportId, String reportName, String cubeSeq, CommonSettings commonSettings) throws SQLException {
		
		DimensionDAO dimDAO = daoFactory.getDimensionDAO(conn);
		ArrayList dimList = dimDAO.selectDimensions(cubeSeq);					// Dimension

		MeasureDAO measureDAO = daoFactory.getMeasureDAO(conn);
		Measure measure = measureDAO.findMeasureWithMember(cubeSeq, commonSettings);	// Measure

		ArrayList edgeList = Report.initializeEdge(dimList, measure);			// Edge List

		CubeDAO cubeDAO = daoFactory.getCubeDAO(conn);
		Cube cube = cubeDAO.getCubeByID(cubeSeq);

		ReportDAO reportDAO = daoFactory.getReportDAO(conn);
		Report report = new Report(reportId,									// reportID
									reportName,									// reportName
									cube,										// cube
									edgeList,									// edgeList
									new ArrayList(),							// colorList
									Report.investigateTimeDimension(edgeList));	// hasTimeDim

		return report;
	}




	// 既存のレポートを取得
	public Report getExistingReport(String reportId, CommonSettings commonSettings) throws SQLException {

		// 初期状態のレポート設定を取得
		ReportInfo reportInfo = this.getReportInfo(reportId);
		// キューブが存在しない場合、nullを戻す
		if(!this.isCubeExist((String) reportInfo.getCubeSeq())) {
			return null;
		}

		Report report = getReport(reportId, (String) reportInfo.getReportName(), (String) reportInfo.getCubeSeq(), commonSettings);

		// Reportオブジェクトに格納先フォルダのIDを保存
		report.setParentID(((String) reportInfo.getParentID()));

		// キューブ構成が変更されている場合、既存のレポート情報を削除し、新たなレポート情報を保存する
		if ( isCubeChanged(reportId, (String) reportInfo.getCubeSeq()) ) {
//System.out.println("キューブ構成が変更されている");

			// 既存レポート情報クリア
			DAOFactory daoFactory = DAOFactory.getDAOFactory();
			daoFactory.getAxisDAO(conn).deleteAxes(report,conn);// 軸
			daoFactory.getAxisMemberDAO(conn).deleteAxisMember(report,conn);// 軸メンバ
			daoFactory.getColorDAO(conn).deleteColor(report, conn); // 色

			// レポート情報保存
			this.saveReport(report, conn);

		} else { // キューブ構成に変更無しの場合、軸情報・色情報を更新する

			// 軸情報を更新
			AxisDAO axisDAO = daoFactory.getAxisDAO(conn);
			axisDAO.applyAxis(report, commonSettings, conn);
		
			// 色情報を更新
			ColorDAO colorDAO = daoFactory.getColorDAO(conn);
			colorDAO.applyColor(report, conn);
			
		}

		return report;
	}

	// 未使用のレポートIDを取得
	public String getInitialReportID(Connection conn) throws SQLException {

		Statement stmt = null;
		ResultSet rs = null;

		String new_rep_id = null;
		String SQL = "";
		SQL += "SELECT ";
		SQL += "    nextval('report_id') as new_rep_id";

		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(SQL);
			while(rs.next()){
				new_rep_id = Integer.toString(rs.getInt("new_rep_id"));			
			}

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

		return new_rep_id;

	}

	public void registReport(RequestHelper helper, CommonSettings commonSettings) {
		HttpServletRequest request = helper.getRequest();
		String reportName = (String)request.getParameter("reportName");
		String folderID = (String)request.getParameter("folderID");

		Report report = (Report)helper.getRequest().getSession().getAttribute("report");	

		if ((reportName == null) || (folderID == null) ) {
			throw new IllegalStateException();
		}
					
		if (( request.getAttribute("mode") != null ) && 
		    ( request.getAttribute("mode").equals("saveNewReport"))) {
			report.setReportName(reportName);
			report.setParentID(folderID);		
		}

	}


	public void registAxisPosition(RequestHelper helper, CommonSettings commonSettings){
		
		HttpServletRequest request = helper.getRequest();
		String defaultMembers = (String)request.getParameter("defaultMembers");
		Report report = (Report)helper.getRequest().getSession().getAttribute("report");


		// クライアントから軸の配置情報を取得(カンマ区切りの軸ID)
		ArrayList colAxesIDListFromClient = StringUtil.splitString((String)request.getParameter("colItems"), ",");
		ArrayList rowAxesIDListFromClient = StringUtil.splitString((String)request.getParameter("rowItems"), ",");
		ArrayList pageAxesIDListFromClient = StringUtil.splitString((String)request.getParameter("pageItems"), ",");

		registAxisPosition(colAxesIDListFromClient, rowAxesIDListFromClient, pageAxesIDListFromClient, report);

	}

	//レポートの持つ、軸のエッジ配置情報を更新
	public void registAxisPosition(ArrayList colItemList, ArrayList rowItemList, ArrayList pageItemList, Report report){

		// 既存の軸をHashMapに退避
		HashMap axisMap = new HashMap();
		Iterator it = report.getEdgeList().iterator();
		while (it.hasNext()) {
			Edge edge = (Edge) it.next();
			Iterator it2 = edge.getAxisList().iterator();
			while (it2.hasNext()) {
				Axis axis = (Axis) it2.next();
				axisMap.put(axis.getId(),axis);
			}
		}

		// Colの配置情報を更新
		ArrayList colAxesListFromModel = report.getEdgeByType(Constants.Col).getAxisList();
		replaceAxisList(colItemList, colAxesListFromModel, axisMap);

		// Rowの配置情報を更新
		ArrayList rowAxesListFromModel = report.getEdgeByType(Constants.Row).getAxisList();
		replaceAxisList(rowItemList, rowAxesListFromModel, axisMap);

		// Pageの配置情報を更新
		ArrayList pageAxesListFromModel = report.getEdgeByType(Constants.Page).getAxisList();
		replaceAxisList(pageItemList, pageAxesListFromModel, axisMap);
		
	}

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

		String SQL = "";

		SQL =  "";
		SQL += "UPDATE oo_v_report set";	
		SQL += "    report_name='" + report.getReportName() + "', ";
		SQL += "    update_date=now(), ";
		SQL += "    par_id=" + report.getParentID() + " ";
		SQL += "WHERE ";
		SQL += "    report_id=" + report.getReportID();
//System.out.println("update report:" + SQL);
		Statement stmt = conn.createStatement();
		int updateCount = stmt.executeUpdate(SQL);


		try {
			// update件数が0の場合、新たにレコード作成
			if (updateCount == 0) {
				SQL =  "";
				SQL += "INSERT INTO oo_v_report ";
				SQL += "       (report_id, par_id, report_name, cube_seq, update_date, kind_flg) ";
				SQL += "values ( "  + report.getReportID() + ", ";
				SQL +=                report.getParentID() + ", ";
				SQL +=          "'" + report.getReportName() + "', ";
				SQL +=                report.getCube().getCubeSeq() + ", ";
				SQL +=                "now(), ";
				SQL +=                "'R' )";
//System.out.println("saveReport:" + SQL);
	
				int count = stmt.executeUpdate(SQL);
				if (count != 1) {
					throw new IllegalStateException();
				}
			}		
	
			// 軸情報の保存
			DAOFactory daoFactory = DAOFactory.getDAOFactory();
			AxisDAO axisDAO = daoFactory.getAxisDAO(conn);
			axisDAO.saveAxis(report, conn);
	
			// 色情報の保存
			ColorDAO colorDAO = daoFactory.getColorDAO(conn);
			colorDAO.saveColor(report, conn);

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


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

		// レポートオブジェクト自体を削除
		String SQL = "";
		SQL =  "";
		SQL += "delete from oo_v_report ";
		SQL += "where ";
		SQL += "    report_id=" + report.getReportID();

		Statement stmt = null;

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

		// 軸情報の削除
		DAOFactory daoFactory = DAOFactory.getDAOFactory();
		AxisDAO axisDAO = daoFactory.getAxisDAO(conn);
		axisDAO.deleteAxes(report, conn);

		// 色情報の削除
		ColorDAO colorDAO = daoFactory.getColorDAO(conn);
		colorDAO.deleteColor(report, conn);

	}


	// ********** 内部クラス **********

	// レポート情報を受け渡すための内部クラス
	class ReportInfo {
		
		String reportName = null;
		String cubeSeq = null;
		String parentID = null;
		

		public void setReportName(String reportName){
			this.reportName = reportName;
		}
		public void setCubeSeq(String cubeSeq){
			this.cubeSeq = cubeSeq;
		}
		public void setParentID(String parentID){
			this.parentID = parentID;
		}
		public String getReportName() {
			return this.reportName;
		}
		public String getCubeSeq() {
			return this.cubeSeq;		
		}
		public String getParentID() {
			return this.parentID;
		}
		
	}

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

	private ReportInfo getReportInfo(String reportId) throws SQLException {

		String reportName = null;
		String cubeSeq = null;
		String parentID = null;

		String SQL = "";
		SQL += "select ";
		SQL += "    report_name,";	// レポート名
		SQL += "    cube_seq, ";	// CubeSeq
		SQL += "    par_id ";		// レポート格納先のフォルダ情報
		SQL += "from ";
		SQL += "    oo_v_report ";
		SQL += "where ";
		SQL += "    report_id=" + reportId;
//System.out.println("select report SQL:" + SQL);

		Statement stmt = null;
		ResultSet rs = null;


		ReportInfo reportInfo = null;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(SQL);
	
			while ( rs.next() ) {
				reportName = rs.getString("report_name");
				cubeSeq = rs.getString("cube_seq");
				parentID = rs.getString("par_id");

			}

			reportInfo = new ReportInfo();
			reportInfo.setReportName(reportName);
			reportInfo.setCubeSeq(cubeSeq);
			reportInfo.setParentID(parentID);

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

		return reportInfo;

	}


	// レポートを構成するキューブが削除されていないかをチェックする
	private boolean isCubeExist(String cubeSeq) throws SQLException {
		
		String SQL = "";
		SQL += "select  ";
		SQL += "	cube_seq ";
		SQL += "from ";
		SQL += "    oo_info_cube ";
		SQL += "where cube_seq=" + cubeSeq ;

		Statement stmt = null;
		ResultSet rs = null;
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(SQL);

			int i = 0;
			while(rs.next()){
				i++;
				if ( i>=1 ) {	// キューブ構成が変更されている
					return true;
				}
			}

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

		return false;	// レポートを構成するキューブが削除された
	}


	// レポートに保存された状態からキューブ構成が変更されたかどうかをチェックする
	private boolean isCubeChanged(String reportId, String cubeSeq) throws SQLException {
		
		String SQL = "";
		SQL += "select  ";
		SQL += "    distinct r.cube_seq,d.dimension_seq ";
		SQL += "from  ";
		SQL += "    oo_v_report r,oo_v_axis d ";
		SQL += "where  ";
		SQL += "    r.report_id=d.report_id and  ";
		SQL += "    r.report_id=" + reportId + " and  ";
		SQL += "    d.dimension_seq!=0 ";	// メジャーではない
		SQL += "except ";
		SQL += "select ";
		SQL += "    cube_seq,dimension_seq  ";
		SQL += "from  ";
		SQL += "    oo_info_dim ";
		SQL += "where  ";
		SQL += "    cube_seq=" + cubeSeq;
//System.out.println("cubeChangeSQL:" + SQL);

		String SQL2 = "";
		SQL2 += "select ";
		SQL2 += "    cube_seq,dimension_seq  ";
		SQL2 += "from  ";
		SQL2 += "    oo_info_dim ";
		SQL2 += "where  ";
		SQL2 += "    cube_seq=" + cubeSeq;
		SQL2 += "except ";
		SQL2 += "select  ";
		SQL2 += "    distinct r.cube_seq,d.dimension_seq ";
		SQL2 += "from  ";
		SQL2 += "    oo_v_report r,oo_v_axis d ";
		SQL2 += "where  ";
		SQL2 += "    r.report_id=d.report_id and  ";
		SQL2 += "    r.report_id=" + reportId + " and  ";
		SQL2 += "    d.dimension_seq!=0 ";	// メジャーではない
//System.out.println("cubeChangeSQL2:" + SQL2);

		
		Statement stmt = null;
		ResultSet rs = null;
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(SQL);

			int i = 0;
			while(rs.next()){
				i++;
				if ( i>1 ) {	// キューブ構成が変更されている
					return true;
				}
			}

			stmt = conn.createStatement();
			rs = stmt.executeQuery(SQL2);
			i = 0;
			while(rs.next()){
				i++;
				if ( i>1 ) {	// キューブ構成が変更されている
					return true;
				}
			}

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

		return false;	// キューブ構成が変更されていない
	}


	private void replaceAxisList(ArrayList edgeAxesIDListFromClient, ArrayList edgeAxesListFromModel, HashMap axisMap) {

		edgeAxesListFromModel.clear();							// エッジをクリア

		Iterator it = edgeAxesIDListFromClient.iterator();		// エッジに追加
		while(it.hasNext()){
			edgeAxesListFromModel.add(axisMap.get(it.next()));
		}

	}

}
