/*
 * Aipo is a groupware program developed by Aimluck,Inc.
 * http://aipostyle.com/
 *
 * Copyright(C) 2011 avanza Co.,Ltd. All rights reserved.
 * http://www.avnz.co.jp/
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

package com.aimluck.eip.query;

import java.util.ArrayList;
import java.util.List;

import org.apache.cayenne.DataRow;

import com.aimluck.eip.cayenne.om.portlet.EipTMail;
import com.aimluck.eip.orm.query.AbstractQuery;
import com.aimluck.eip.orm.query.ResultList;
import com.aimluck.eip.orm.query.SQLTemplate;
import com.aimluck.eip.util.ALEipUtils;

/**
 * 受信メール情報検索用共有SelectQuery
 */
public class ALEipTMailDirectSelectQuery extends AbstractQuery<EipTMail> {

  /** オフセット */
  protected int offset = 0;

  /** リミット */
  protected int limit = 1000;

  /** ページNo */
  protected int page = 1;

  /** 取得件数 */
  protected int totalCount = 0;

  /** ユーザーID */
  protected int userId;

  /** アカウントID */
  protected int accountId;

  /** メール本文検索か？ */
  protected boolean isSearchWord = false;

  /** メール本文検索語 */
  private String textSearchWord;

  /** メール件名検索か？ */
  protected boolean isSearchSubjectWord = false;

  /** メール件名検索語 */
  private String subjectSearchWord;

  /** メール差出人検索か？ */
  protected boolean isSearchAddressWord = false;

  /** メール差出人検索語 */
  private String addressSearchWord;

  /** 子フォルダありか？ */
  protected boolean isInChildFolder;

  /** 条件（子フォルダID(s)） */
  protected List<String> childFolderIds = new ArrayList<String>();

  /** 条件（現在処理中のフォルダID） */
  protected String folderId;

  /** 検索用SQLクエリー */
  private static String searchQuery =
    "SELECT "
      + "  A.MAIL_ID,  A.READ_FLG, A.SUBJECT, A.PERSON, A.EVENT_DATE, A.FILE_VOLUME, A.HAS_FILES, A.FILE_PATH, A.REPLY_FORWARD "
      + "FROM eip_t_mail as A ";

  /** 共通WHERE句 */
  private static String whereQuery = " WHERE A.USER_ID = #bind($userId 'INTEGER') AND A.ACCOUNT_ID = #bind($accountId 'INTEGER') ";

  /** カウント用SQLクエリー */
  private static String countQuery = "SELECT COUNT (A.MAIL_ID) AS C FROM eip_t_mail as A ";

  /** メール本文指定用条件 */
  private static String searchWordQuery = " AND A.TEXT_FOR_SEARCH %% " + "#bind($text_search_word 'VARCHAR') ";

  /** メール件名指定用条件 */
  private static String searchSubjectWordQuery = " AND A.SUBJECT_FOR_SEARCH %% " + "#bind($subject_search_word 'VARCHAR') ";

  /** メール差出人指定用条件 */
  private static String searchAddressWordQuery = " AND A.ADDRESS_FOR_SEARCH %% " + "#bind($address_search_word 'VARCHAR') ";

  /** 子フォルダ指定用条件 */
  private static String foldersQuery = " AND A.FOLDER_ID IN ( #bind($folderId 'INTEGER') ) ";

  /** 現在処理中のフォルダ用条件 */
  private static String folderQuery = " AND A.FOLDER_ID = #bind($folderId 'INTEGER') ";

  /** ORDER BY句 */
  private String orderQuery = "";

  /**
   * コンストラクタ
   */
  public ALEipTMailDirectSelectQuery() {
    super(EipTMail.class);

  }

  /**
   * データ取得
   * 
   * @return ユーザー情報リスト
   */
  @SuppressWarnings("unchecked")
  public List<EipTMail> fetchList() {

    StringBuffer bufSql = new StringBuffer();
    bufSql.append(searchQuery);

    // 共通WHERE句構築
    bufSql.append(whereQuery);
    // WHERE句構築
    if (isSearchWord) {
      bufSql.append(searchWordQuery);
    }
    if (isSearchSubjectWord) {
      bufSql.append(searchSubjectWordQuery);
    }
    if (isSearchAddressWord) {
      bufSql.append(searchAddressWordQuery);
    }
    if (isInChildFolder) {
      bufSql.append(foldersQuery);
    } else {
      bufSql.append(folderQuery);
    }

    bufSql.append(orderQuery);
    bufSql.append(" LIMIT ");
    bufSql.append(limit);
    bufSql.append(" OFFSET ");
    bufSql.append(offset);

    SQLTemplate<EipTMail> query = new SQLTemplate<EipTMail>(EipTMail.class, bufSql.toString());

    // 条件をバインド
    query.param("userId", userId);
    query.param("accountId", accountId);
    query.param("text_search_word", textSearchWord);
    query.param("subject_search_word", subjectSearchWord);
    query.param("address_search_word", addressSearchWord);

    if (isInChildFolder) {
      query.param("folderId", childFolderIds);
    } else {
      query.param("folderId", folderId);
    }

    List<DataRow> dataRows = query.fetchListAsDataRow();
    List<EipTMail> results = new ArrayList<EipTMail>();

    for (DataRow dataRow : dataRows) {
      EipTMail model = newInstanceFromRowData(dataRow, EipTMail.class);
      if (model != null) {
        results.add(model);
      }
    }
    return results;
  }

  /**
   * 結果リスト取得
   * 
   * @return 受信メール情報リスト
   */
  public ResultList<EipTMail> getResultList() {

    int totalCount = getCount();
    int pageSize = limit;
    if (pageSize > 0) {
      int num = ((int) (Math.ceil(totalCount / (double) pageSize)));
      if ((num > 0) && (num < page)) {
        page = num;
      }
      offset = pageSize * (page - 1);
    } else {
      page = 1;
    }
    List<EipTMail> fetchList = fetchList();
    return new ResultList<EipTMail>(fetchList, page, pageSize, totalCount);
  }

  /**
   * 結果件数取得
   * 
   * @return 結果件数
   */
  public int getCount() {

    StringBuffer bufSql = new StringBuffer();
    bufSql.append(countQuery);

    // 共通WHERE句構築
    bufSql.append(whereQuery);
    // WHERE句構築
    if (isSearchWord) {
      bufSql.append(searchWordQuery);
    }
    if (isSearchSubjectWord) {
      bufSql.append(searchSubjectWordQuery);
    }
    if (isSearchAddressWord) {
      bufSql.append(searchAddressWordQuery);
    }
    if (isInChildFolder) {
      bufSql.append(foldersQuery);
    } else {
      bufSql.append(folderQuery);
    }

    SQLTemplate<EipTMail> query = new SQLTemplate<EipTMail>(EipTMail.class, bufSql.toString());

    // 条件をバインド
    query.param("userId", userId);
    query.param("accountId", accountId);
    query.param("text_search_word", textSearchWord);
    query.param("subject_search_word", subjectSearchWord);
    query.param("address_search_word", addressSearchWord);

    if (isInChildFolder) {
      query.param("folderId", childFolderIds);
    } else {
      query.param("folderId", folderId);
    }

    List<DataRow> rows = query.fetchListAsDataRow();
    DataRow dataRow = rows.get(0);
    return ((Long) ALEipUtils.getObjFromDataRow(dataRow, "C")).intValue();
  }

  /**
   * ページNo
   * 
   * @param page
   *            ページNo
   */
  public void page(int page) {
    this.page = page;
  }

  /**
   * 限度数
   * 
   * @param limit
   *            limit
   */
  public void limit(int limit) {
    this.limit = limit;
  }

  /**
   * 開始位置を飛ばす行数
   * 
   * @param offset
   *            offset
   */
  public void offset(int offset) {
    this.offset = offset;
  }

  /**
   * ユーザーID
   * 
   * @param s
   *            ユーザーID
   */
  public void setUserId(int s) {
    userId = s;
  }

  /**
   * アカウントID
   * 
   * @param s
   *            アカウントID
   */
  public void setAccountId(int s) {
    accountId = s;
  }

  /**
   * メール本文検索クエリーを生成
   * 
   */
  public void setSearchWordQuery(String s) {
    isSearchWord = true;
    this.textSearchWord = "*D+ " + s;
  }

  /**
   * メール件名検索クエリーを生成
   * 
   */
  public void setSearchSubjectWordQuery(String s) {
    isSearchSubjectWord = true;
    this.subjectSearchWord = "*D+ " + s;
  }

  /**
   * メール差出人検索クエリーを生成
   * 
   */
  public void setSearchAddressWordQuery(String s) {
    isSearchAddressWord = true;
    this.addressSearchWord = "*D+ " + s;
  }

  // add start 運用フェーズ課題・障害台帳No.１３３
  /**
   * メール差出人検索クエリーを生成
   * 
   */
  public void setSearchAddressWordQuery(List<String> words) {
    isSearchAddressWord = true;
    if (words.size() > 0) {
      this.addressSearchWord = "*DOR ";
      int cnt = 0;
      for (String word : words) {
        if (cnt == 0) {
          word = "\"" + word.replace("\"", "\\\"") + "\"";
        }
        this.addressSearchWord = this.addressSearchWord + word + " ";
        cnt++;
      }
    }/*
       * else if (words.size() == 1) { this.addressSearchWord = "*D+ " +
       * words.get(0); }
       */
  }

  // add end

  /**
   * 検索対象フォルダ
   * 
   */
  public void setChildFolderIds(List<String> s) {
    isInChildFolder = true;
    this.childFolderIds = s;
  }

  /**
   * 検索対象フォルダ
   * 
   */
  public void setFolderId(String s) {
    isInChildFolder = false;
    this.folderId = s;
  }

  /**
   * ORDER BY 句生成(DESC)
   * 
   */
  public void orderDesending(String s) {
    this.orderQuery = " ORDER BY " + s + " DESC ";
  }

  /**
   * ORDER BY 句生成(ASC)
   * 
   */
  public void orderAscending(String s) {
    this.orderQuery = " ORDER BY " + s + " ASC ";
  }

}
