/*
 * 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.security.TurbineUser;
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 ALUserDirectSelectQuery extends AbstractQuery<TurbineUser> {

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

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

  /** ページＮｏ */
  protected int page = 1;

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

  /** WHERE句 */
  protected String where = "";

  /** 条件（グループ名/グループ送信ＩＤ） */
  protected String groupName = "";

  /** 条件（グループ名(s)） */
  protected List<String> groupNames = new ArrayList<String>();

  /** 役職ID */
  protected String positionId;

  /** 役割ID */
  protected String roleId;

  /** 複数グループ名指定か？ */
  protected boolean isIn;

  /** グループ送信指定の検索か？ */
  protected boolean isGroupSend;

  /** 全件検索か？ */
  protected boolean isAll;

  /** 無効も含むか？ */
  protected boolean isContainDisabled;

  /** 管理者権限を持つユーザーだけを表示するモード */
  protected boolean isAdminOnly;

  /** 検索用SQLクエリー */
  private static String searchQuery =
    "SELECT DISTINCT "
      + "  B.USER_ID, B.LOGIN_NAME, B.DISPLAY_NAME, B.FIRST_NAME, B.LAST_NAME, D.POSITION, B.EMAIL "
      + "FROM turbine_user_group_role as A "
      + "INNER JOIN turbine_user as B "
      + "  on A.USER_ID = B.USER_ID "
      + "INNER JOIN turbine_group as C "
      + "  on A.GROUP_ID = C.GROUP_ID "
      + "INNER JOIN eip_m_user_position as D "
      + " on A.USER_ID = D.USER_ID "
      + "LEFT JOIN turbine_user_group_role as E "
      + " on B.USER_ID = E.USER_ID ";

  /** カウント用SQLクエリー */
  private static String countQuery =
    "SELECT COUNT(DISTINCT B.USER_ID) AS C "
      + "FROM turbine_user_group_role as A "
      + "INNER JOIN turbine_user as B "
      + "  on A.USER_ID = B.USER_ID "
      + "INNER JOIN turbine_group as C "
      + "  on A.GROUP_ID = C.GROUP_ID "
      + "INNER JOIN eip_m_user_position as D "
      + " on A.USER_ID = D.USER_ID "
      + "LEFT JOIN turbine_user_group_role as E "
      + " on B.USER_ID = E.USER_ID ";

  /** 役割絞込み用結合記述 */
  private static String roleFrom =
    "LEFT JOIN avz_m_user_role as Y on A.USER_ID = Y.USER_ID ";

  /** グループ送信絞込み用結合記述 */
  private static String groupSendFrom =
    "LEFT JOIN avz_m_user_groupsend as Z on A.USER_ID = Z.USER_ID ";

  /** 共通WHERE句 */
  private static String whereQuery =
    " WHERE B.USER_ID > 3 AND B.DISABLED = 'F' ";

  /** WHERE句（無効ユーザーも取得する） */
  private static String whereQueryWithDisabled =
    " WHERE B.USER_ID > 3 AND (B.DISABLED = 'F' OR B.DISABLED = 'N')";

  /** 複数グループ名指定用条件 */
  private static String groupQuery =
    " AND C.GROUP_NAME IN ( #bind($groupname 'VARCHAR') ) ";

  /** 役職指定用条件 */
  private static String positionQuery =
    " AND E.POSITION_ID = #bind($positionId 'INTEGER') ";

  /** 役割指定用条件 */
  private static String roleQuery =
    " AND Y.ROLE_ID = #bind($roleId 'INTEGER') ";

  /** グループ送信ID指定用条件 */
  private static String groupSendQuery =
    " AND Z.GROUPSEND_ID = #bind($groupname 'INTEGER') ";

  /** ORDER BY句 */
  private static String orderQuery = " ORDER BY B.DISPLAY_NAME, D.POSITION";

  /** アカウント情報管理の管理ユーザー絞込み用条件 */
  private static String adminQuery =
    " AND A.USER_ID IN (SELECT DISTINCT t0.USER_ID "
      + " FROM turbine_user_group_role as t0 "
      + " INNER JOIN turbine_group as t1 ON t0.GROUP_ID = t1.GROUP_ID "
      + " INNER JOIN turbine_role as t2 ON t0.ROLE_ID = t2.ROLE_ID "
      + " WHERE t2.ROLE_NAME = 'admin' AND t1.GROUP_NAME = 'LoginUser' AND t0.USER_ID <> 1)";

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

  }

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

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

    // FROM句構築
    if (isGroupSend) {
      bufSql.append(groupSendFrom);
    }

    if (roleId != null) {
      // 役職テーブル付加
      bufSql.append(roleFrom);
    }

    // WHERE句構築
    if (isContainDisabled) {
      bufSql.append(whereQueryWithDisabled);
    } else {
      bufSql.append(whereQuery);
    }

    if (!isAll) {
      if (isGroupSend) {
        bufSql.append(groupSendQuery);
      } else {
        bufSql.append(groupQuery);
      }
    }

    if (positionId != null) {
      // 役職条件付加
      bufSql.append(positionQuery);
    }

    if (roleId != null) {
      // 役職条件付加
      bufSql.append(roleQuery);
    }

    if (isAdminOnly) {
      bufSql.append(adminQuery);
    }

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

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

    // グループ名条件をバインド
    if (!isAll) {
      if (isIn) {
        query.param("groupname", groupNames);
      } else {
        query.param("groupname", groupName);
      }
    }

    // 役職条件をバインド
    if (positionId != null) {
      query.param("positionId", positionId);
    }

    // 役職条件をバインド
    if (roleId != null) {
      query.param("roleId", roleId);
    }

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

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

  /**
   * 結果リスト取得
   * 
   * @return ユーザー情報リスト
   */
  public ResultList<TurbineUser> 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<TurbineUser> fetchList = fetchList();
    return new ResultList<TurbineUser>(fetchList, page, pageSize, totalCount);
  }

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

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

    // FROM句構築
    if (isGroupSend) {
      bufSql.append(groupSendFrom);
    }

    if (roleId != null) {
      // 役職テーブル付加
      bufSql.append(roleFrom);
    }

    // WHERE句構築
    if (isContainDisabled) {
      bufSql.append(whereQueryWithDisabled);
    } else {
      bufSql.append(whereQuery);
    }

    // WHERE句構築
    if (!isAll) {
      if (isGroupSend) {
        bufSql.append(groupSendQuery);
      } else {
        bufSql.append(groupQuery);
      }
    }

    if (positionId != null) {
      // 役職条件付加
      bufSql.append(positionQuery);
    }

    if (roleId != null) {
      // 役職条件付加
      bufSql.append(roleQuery);
    }

    if (isAdminOnly) {
      bufSql.append(adminQuery);
    }

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

    // グループ名条件を付加する。
    if (!isAll) {
      if (isIn) {
        query.param("groupname", groupNames);
      } else {
        query.param("groupname", groupName);
      }
    }

    // 役職条件をバインド
    if (positionId != null) {
      query.param("positionId", positionId);
    }

    // 役職条件をバインド
    if (roleId != null) {
      query.param("roleId", roleId);
    }

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

  public void page(int page) {
    this.page = page;
  }

  public void limit(int limit) {
    this.limit = limit;
  }

  public void offset(int offset) {
    this.offset = offset;
  }

  public void setGroupName(String s) {
    isIn = false;
    isGroupSend = false;
    this.groupName = s;
  }

  public void setGroupNames(List<String> s) {
    isIn = true;
    isGroupSend = false;
    this.groupNames = s;
  }

  public void setPositionId(String s) {
    positionId = s;
  }

  public void setRoleId(String s) {
    roleId = s;
  }

  public void setGroupSendId(String s) {
    isIn = false;
    isGroupSend = true;
    groupName = s;
  }

  public void setAdminOnly(boolean b) {
    isAdminOnly = b;
  }

  /**
   * 全件検索か？
   * 
   * @param b
   *            全件検索である:true でない：false
   * 
   */
  public void setAll(boolean b) {
    isAll = b;
  }

  /**
   * 無効も含むか？
   * 
   * @param isContainDisabled
   *            含む：true 含まない：false
   */
  public void setContainDisabled(boolean isContainDisabled) {
    this.isContainDisabled = isContainDisabled;
  }

}
