/*
 * Copyright(C) 2010 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 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 General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 */

package com.aimluck.eip.enquete.util;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.apache.cayenne.access.DataContext;
import org.apache.cayenne.exp.Expression;
import org.apache.cayenne.exp.ExpressionFactory;
import org.apache.jetspeed.services.logging.JetspeedLogFactoryService;
import org.apache.jetspeed.services.logging.JetspeedLogger;
import org.apache.turbine.util.RunData;
import org.apache.velocity.context.Context;

import com.aimluck.commons.field.ALStringField;
import com.aimluck.eip.cayenne.om.portlet.AvzTEnquete;
import com.aimluck.eip.cayenne.om.portlet.AvzTEnqueteChoiceItem;
import com.aimluck.eip.cayenne.om.portlet.AvzTEnqueteItem;
import com.aimluck.eip.common.ALEipConstants;
import com.aimluck.eip.common.ALEipUser;
import com.aimluck.eip.common.ALPageNotFoundException;
import com.aimluck.eip.enquete.EnqueteChoiceItemRecordData;
import com.aimluck.eip.orm.Database;
import com.aimluck.eip.orm.DatabaseOrmService;
import com.aimluck.eip.orm.query.ResultList;
import com.aimluck.eip.orm.query.SelectQuery;
import com.aimluck.eip.util.ALEipUtils;

/**
 * Enqueteのユーティリティクラスです。 <BR>
 * 
 */
public class EnqueteUtils {

  /** logger */
  private static final JetspeedLogger logger =
    JetspeedLogFactoryService.getLogger(EnqueteUtils.class.getName());

  public static final String TARGET_GROUP_NAME = "target_group_name";

  /** 公開締切状態（未公開） */
  public static final String PUBLIC_STATUS_BEFORE = "0";

  /** 公開締切状態（実施中） */
  public static final String PUBLIC_STATUS_PRACTICE = "1";

  /** 公開締切状態（締切済） */
  public static final String PUBLIC_STATUS_AFTER = "2";

  /** 締切方法（手動締切） */
  public static final String DEADLINE_METHOD_HANDOPERATED = "0";

  /** 締切方法（自動締切） */
  public static final String DEADLINE_METHOD_AUTO = "1";

  /** 回答状態（未回答） */
  public static final String RESPONSE_STATUS_BEFORE = "0";

  /** 回答状態（回答済） */
  public static final String RESPONSE_STATUS_AFTER = "1";

  /** 選択肢状態（未選択） */
  public static final String STATUS_NO_SELECT = "0";

  /** 選択肢状態（選択） */
  public static final String STATUS_SELECT = "1";

  /** 選択方法(単一選択) */
  public static final String STATUS_ONE_SELECT = "0";

  /** 選択方法(複数選択) */
  public static final String STATUS_MULTI_SELECT = "1";

  /** 選択方法(フリーテキスト) */
  public static final String STATUS_FREE_TEXT = "2";

  /** 公開方法（無記名） */
  public static final String PUBLIC_METHOD_SECRET = "1";

  /** 公開方法（記名） */
  public static final String PUBLIC_METHOD_SIGNED = "0";

  /** 処理フラグ（集計画面） */
  public static final String FLAG_SUMMARY = "summary";

  /** 処理フラグ（CSV出力） */
  public static final String FLAG_CSV = "csv";

  /**
   * Enquete オブジェクトモデルを取得します。 <BR>
   * 
   * @param rundata
   * @param context
   * @return
   */
  public static AvzTEnquete getAvzTEnquete(RunData rundata, Context context)
      throws ALPageNotFoundException {
    String enqueteid =
      ALEipUtils.getTemp(rundata, context, ALEipConstants.ENTITY_ID);
    try {
      if (enqueteid == null || Integer.valueOf(enqueteid) == null) {
        // Enquete IDが空の場合
        logger.debug("[Enquete] Empty ID...");
        return null;
      }

      SelectQuery<AvzTEnquete> query = Database.query(AvzTEnquete.class);
      Expression exp =
        ExpressionFactory.matchDbExp(
          AvzTEnquete.ENQUETE_ID_PK_COLUMN,
          enqueteid);
      query.setQualifier(exp);
      ResultList<AvzTEnquete> enquetes = query.getResultList();

      if (enquetes == null || enquetes.size() == 0) {
        // 指定したEnquete IDのレコードが見つからない場合
        logger.debug("[Enquete] Not found ID...");
        return null;
      }

      return enquetes.get(0);

    } catch (Exception ex) {
      logger.error("Exception", ex);
      return null;
    }
  }

  /**
   * Enquete オブジェクトモデルを取得します。 (SummaryDetail用)<BR>
   * 
   * @param rundata
   * @param context
   * @param enqueteId
   * @return
   */
  public static AvzTEnquete getAvzTEnqueteSummaryDetail(RunData rundata,
      Context context, long enqueteId) throws ALPageNotFoundException {
    try {
      SelectQuery<AvzTEnquete> query = Database.query(AvzTEnquete.class);
      Expression exp =
        ExpressionFactory.matchDbExp(
          AvzTEnquete.ENQUETE_ID_PK_COLUMN,
          enqueteId);
      query.setQualifier(exp);
      ResultList<AvzTEnquete> enquetes = query.getResultList();

      if (enquetes == null || enquetes.size() == 0) {
        // 指定したEnquete IDのレコードが見つからない場合
        logger.debug("[Enquete] Not found ID...");
        return null;
      }

      return enquetes.get(0);

    } catch (Exception ex) {
      logger.error("Exception", ex);
      return null;
    }
  }

  /**
   * 
   * @param date
   * @return
   */
  public static boolean isEmptyDate(Date date) {
    if (date == null) {
      return false;
    }
    return date.getYear() == 8099;
    // Calendar.get(Calendar.YEAR)-1900
  }

  public static Date getEmptyDate() {
    Calendar cal = Calendar.getInstance();
    cal.set(9999, 11, 31);
    return cal.getTime();
  }

  public static ALStringField getChoiceItem(RunData rundata, Context context,
      int item_id, List users) throws ALPageNotFoundException {

    try {

      DataContext dataContext =
        DatabaseOrmService.getInstance().getDataContext();

      SelectQuery<AvzTEnqueteChoiceItem> choicequery =
        Database.query(AvzTEnqueteChoiceItem.class);

      Expression choiceexp =
        ExpressionFactory.matchDbExp(
          AvzTEnqueteChoiceItem.AVZ_TENQUETE_ITEM_PROPERTY
            + "."
            + AvzTEnqueteItem.ITEM_ID_PK_COLUMN,
          item_id);
      Expression choiceexp2 =
        ExpressionFactory.matchExp(
          AvzTEnqueteChoiceItem.ANSWER_ID_PROPERTY,
          users.get(0));

      choicequery.setQualifier(choiceexp.andExp(choiceexp2));
      choicequery.distinct(true);

      ResultList<AvzTEnqueteChoiceItem> choicelist =
        choicequery.getResultList();

      String choiceitemname = null;
      ALStringField choiceitem = new ALStringField();

      for (int k = 0; k < choicelist.size(); k++) {

        AvzTEnqueteChoiceItem choice = choicelist.get(k);

        if (k == 0) {
          choiceitemname = choice.getChoiceItemTitle();

        } else if (k > 0 && k < choicelist.size() - 1) {
          choiceitemname = choiceitemname + " , " + choice.getChoiceItemTitle();

        } else if (k == choicelist.size() - 1) {
          choiceitemname = choiceitemname + " , " + choice.getChoiceItemTitle();

        }

      }
      choiceitem.setValue(choiceitemname);
      return choiceitem;

    } catch (Exception ex) {
      logger.error("Exception", ex);
      return null;

    }

  }

  public static String insertChoiceItem(RunData rundata, Context context,
      List<String> msgList, DataContext dataContext,
      ALStringField[] choice_item_title, int j, AvzTEnquete enquete,
      AvzTEnqueteItem enqueteitem, List<ALEipUser> memberList) {

    String choiceItem =
      rundata.getParameters().getString("choice_item_title[" + j + "]");
    String choiceItemToken[] = choiceItem.split(",");

    int choiceItemSum = 0;
    for (int k = 0; k < memberList.size(); k++) {

      for (int l = 0; l < choiceItemToken.length; l++) {

        // 新規オブジェクトモデル
        AvzTEnqueteChoiceItem choiceitem =
          Database.create(AvzTEnqueteChoiceItem.class);

        choiceitem.setAvzTEnquete(enquete);
        choiceitem.setAvzTEnqueteItem(enqueteitem);
        // 回答ユーザー
        int memberId = (int) (memberList.get(k)).getUserId().getValue();
        choiceitem.setAnswerId(memberId);
        // 項目番号
        choiceitem.setItemNumber(j);
        // 選択項目番号
        choiceitem.setChoiceItemNumber(l + 1);
        // 選択肢名
        ALStringField splitItem = new ALStringField();
        splitItem.setValue(choiceItemToken[l]);
        choiceitem.setChoiceItemTitle(splitItem.getValue());

        // 状態(選択、未選択) ※作成時は未選択で登録
        choiceitem.setStatus(STATUS_NO_SELECT);
        // 回答状態 ※作成時は未回答で登録
        choiceitem.setResponseStatus(RESPONSE_STATUS_BEFORE);

        // 選択肢の項目数
        choiceItemSum = l + 1;
      }
    }

    EnqueteUtils.getItemSum(enqueteitem, choiceItemSum, dataContext);
    return null;
  }

  public static String insertFreeTextChoiceItem(RunData rundata,
      Context context, List<String> msgList, DataContext dataContext,
      ALStringField[] choice_item_title, int j, AvzTEnquete enquete,
      AvzTEnqueteItem enqueteitem, List<ALEipUser> memberList) {

    int choiceItemSum = 0;
    for (int k = 0; k < memberList.size(); k++) {

      // 新規オブジェクトモデル
      AvzTEnqueteChoiceItem choiceitem =
        Database.create(AvzTEnqueteChoiceItem.class);

      choiceitem.setAvzTEnquete(enquete);
      choiceitem.setAvzTEnqueteItem(enqueteitem);
      // 回答ユーザー
      int memberId = (int) (memberList.get(k)).getUserId().getValue();
      choiceitem.setAnswerId(memberId);
      // 項目番号
      choiceitem.setItemNumber(j);
      // 選択項目番号
      choiceitem.setChoiceItemNumber(1);
      // 選択肢名
      ALStringField splitItem = new ALStringField();
      splitItem.setValue("");
      choiceitem.setChoiceItemTitle(splitItem.getValue());

      // 状態(選択、未選択) ※作成時は未選択で登録
      choiceitem.setStatus(STATUS_NO_SELECT);
      // 回答状態 ※作成時は未回答で登録
      choiceitem.setResponseStatus(RESPONSE_STATUS_BEFORE);

      // 選択肢の項目数
      choiceItemSum = 1;
    }

    EnqueteUtils.getItemSum(enqueteitem, choiceItemSum, dataContext);
    return null;
  }

  public static List updateChoiceItem(RunData rundata, Context context,
      List<String> msgList, DataContext dataContext,
      ALStringField[] choice_item_title, int m, AvzTEnquete enquete,
      AvzTEnqueteItem enqueteitem, List<ALEipUser> memberList) {

    int itemNum = m + 1;
    String choiceItem =
      rundata.getParameters().getString("choice_item_title[" + itemNum + "]");
    String choiceItemToken[] = choiceItem.split(",");

    int choiceItemSum = 0;

    for (int k = 0; k < memberList.size(); k++) {

      for (int l = 0; l < choiceItemToken.length; l++) {

        // 新規オブジェクトモデル
        AvzTEnqueteChoiceItem choiceitem =
          Database.create(AvzTEnqueteChoiceItem.class);

        choiceitem.setAvzTEnquete(enquete);
        choiceitem.setAvzTEnqueteItem(enqueteitem);
        // 回答ユーザー
        int memberId = (int) (memberList.get(k)).getUserId().getValue();
        choiceitem.setAnswerId(memberId);
        // 項目番号
        choiceitem.setItemNumber(itemNum);
        // 選択項目番号
        choiceitem.setChoiceItemNumber(l + 1);
        // 選択肢名
        ALStringField splitItem = new ALStringField();
        splitItem.setValue(choiceItemToken[l]);
        choiceitem.setChoiceItemTitle(splitItem.getValue());
        // 状態(選択、未選択) ※作成時は未選択で登録
        choiceitem.setStatus(STATUS_NO_SELECT);
        // 回答状態 ※作成時は未回答で登録
        choiceitem.setResponseStatus(RESPONSE_STATUS_BEFORE);

        // 選択肢の項目数
        choiceItemSum = l + 1;
      }
    }
    EnqueteUtils.getItemSum(enqueteitem, choiceItemSum, dataContext);
    return memberList;
  }

  public static List updateFreeTextChoiceItem(RunData rundata, Context context,
      List<String> msgList, DataContext dataContext,
      ALStringField[] choice_item_title, int m, AvzTEnquete enquete,
      AvzTEnqueteItem enqueteitem, List<ALEipUser> memberList) {

    int itemNum = m + 1;
    // String choiceItem =
    // rundata.getParameters().getString("choice_item_title[" +itemNum+ "]");
    // String choiceItemToken[] = choiceItem.split(",");

    int choiceItemSum = 0;

    for (int k = 0; k < memberList.size(); k++) {

      // for (int l = 0; l < choiceItemToken.length; l++) {

      // 新規オブジェクトモデル
      AvzTEnqueteChoiceItem choiceitem =
        Database.create(AvzTEnqueteChoiceItem.class);

      choiceitem.setAvzTEnquete(enquete);
      choiceitem.setAvzTEnqueteItem(enqueteitem);
      // 回答ユーザー
      int memberId = (int) (memberList.get(k)).getUserId().getValue();
      choiceitem.setAnswerId(memberId);
      // 項目番号
      choiceitem.setItemNumber(itemNum);
      // 選択項目番号
      choiceitem.setChoiceItemNumber(1);
      // 選択肢名
      ALStringField splitItem = new ALStringField();
      splitItem.setValue("");
      choiceitem.setChoiceItemTitle(splitItem.getValue());
      // 状態(選択、未選択) ※作成時は未選択で登録
      choiceitem.setStatus(STATUS_NO_SELECT);
      // 回答状態 ※作成時は未回答で登録
      choiceitem.setResponseStatus(RESPONSE_STATUS_BEFORE);

      // 選択肢の項目数
      choiceItemSum = 1;

    }
    EnqueteUtils.getItemSum(enqueteitem, choiceItemSum, dataContext);
    return memberList;
  }

  public static void getItemSum(AvzTEnqueteItem enqueteitem, int choiceItemSum,
      DataContext dataContext) {
    enqueteitem.setChoiceItemNum(choiceItemSum);
  }

  public static List<EnqueteChoiceItemRecordData> getAnswerChoiceItem(
      RunData rundata, Context context, int item_id, int loginuserid)
      throws ALPageNotFoundException {

    try {

      DataContext dataContext =
        DatabaseOrmService.getInstance().getDataContext();

      SelectQuery<AvzTEnqueteChoiceItem> choicequery =
        Database.query(AvzTEnqueteChoiceItem.class);

      Expression choiceexp =
        ExpressionFactory.matchDbExp(
          AvzTEnqueteChoiceItem.AVZ_TENQUETE_ITEM_PROPERTY
            + "."
            + AvzTEnqueteItem.ITEM_ID_PK_COLUMN,
          item_id);
      Expression choiceexp2 =
        ExpressionFactory.matchExp(
          AvzTEnqueteChoiceItem.ANSWER_ID_PROPERTY,
          loginuserid);

      choicequery.setQualifier(choiceexp.andExp(choiceexp2));
      choicequery.distinct(true);

      ResultList<AvzTEnqueteChoiceItem> choicelist =
        choicequery.getResultList();

      List<EnqueteChoiceItemRecordData> choiceresults =
        new ArrayList<EnqueteChoiceItemRecordData>();

      for (Object r : choicelist) {
        AvzTEnqueteChoiceItem choiceitem = (AvzTEnqueteChoiceItem) r;
        EnqueteChoiceItemRecordData c = new EnqueteChoiceItemRecordData();
        ALStringField record = new ALStringField();
        record.setValue(choiceitem.getChoiceItemTitle());
        c.setChoiceItemTitle(record);
        c.setSelectStatus(choiceitem.getStatus());
        c.setResponse(choiceitem.getResponseStatus());

        choiceresults.add(c);

      }

      return choiceresults;

    } catch (Exception ex) {
      logger.error("Exception", ex);
      return null;

    }

  }

  public static List<EnqueteChoiceItemRecordData> getPracticePreview(
      RunData rundata, Context context, int item_id, int someAnswer)
      throws ALPageNotFoundException {

    try {

      DataContext dataContext =
        DatabaseOrmService.getInstance().getDataContext();

      SelectQuery<AvzTEnqueteChoiceItem> choicequery =
        Database.query(AvzTEnqueteChoiceItem.class);

      Expression choiceexp =
        ExpressionFactory.matchDbExp(
          AvzTEnqueteChoiceItem.AVZ_TENQUETE_ITEM_PROPERTY
            + "."
            + AvzTEnqueteItem.ITEM_ID_PK_COLUMN,
          item_id);
      Expression choiceexp2 =
        ExpressionFactory.matchExp(
          AvzTEnqueteChoiceItem.ANSWER_ID_PROPERTY,
          someAnswer);

      choicequery.setQualifier(choiceexp.andExp(choiceexp2));
      choicequery.distinct(true);

      ResultList<AvzTEnqueteChoiceItem> choicelist =
        choicequery.getResultList();

      List<EnqueteChoiceItemRecordData> choiceresults =
        new ArrayList<EnqueteChoiceItemRecordData>();

      for (Object r : choicelist) {
        AvzTEnqueteChoiceItem choiceitem = (AvzTEnqueteChoiceItem) r;
        EnqueteChoiceItemRecordData c = new EnqueteChoiceItemRecordData();
        ALStringField record = new ALStringField();
        record.setValue(choiceitem.getChoiceItemTitle());
        c.setChoiceItemTitle(record);
        c.setSelectStatus(choiceitem.getStatus());
        c.setResponse(choiceitem.getResponseStatus());

        choiceresults.add(c);

      }

      return choiceresults;

    } catch (Exception ex) {
      logger.error("Exception", ex);
      return null;

    }

  }

  public static List<EnqueteChoiceItemRecordData> getSummaryChoiceItem(
      RunData rundata, int item_id, int someAnswer, AvzTEnquete enquete,
      String flag) throws ALPageNotFoundException {

    try {

      DataContext dataContext =
        DatabaseOrmService.getInstance().getDataContext();

      SelectQuery<AvzTEnqueteChoiceItem> choicequery =
        Database.query(AvzTEnqueteChoiceItem.class);

      Expression choiceexp =
        ExpressionFactory.matchDbExp(
          AvzTEnqueteChoiceItem.AVZ_TENQUETE_ITEM_PROPERTY
            + "."
            + AvzTEnqueteItem.ITEM_ID_PK_COLUMN,
          item_id);

      Expression choiceexp2 =
        ExpressionFactory.matchExp(
          AvzTEnqueteChoiceItem.ANSWER_ID_PROPERTY,
          someAnswer);

      choicequery.setQualifier(choiceexp.andExp(choiceexp2));
      choicequery.distinct(true);
      choicequery
        .orderAscending(AvzTEnqueteChoiceItem.CHOICE_ITEM_NUMBER_PROPERTY);

      ResultList<AvzTEnqueteChoiceItem> choicelist =
        choicequery.getResultList();

      List<EnqueteChoiceItemRecordData> choiceresults =
        new ArrayList<EnqueteChoiceItemRecordData>();

      for (int j = 0; j < choicelist.size(); j++) {
        AvzTEnqueteChoiceItem choiceitem = choicelist.get(j);
        EnqueteChoiceItemRecordData c = new EnqueteChoiceItemRecordData();
        if (FLAG_CSV.equals(flag)) {
          c.setCsvChoiceItem(choiceitem.getChoiceItemTitle());
        } else {
          ALStringField record = new ALStringField();
          record.setValue(choiceitem.getChoiceItemTitle());
          c.setChoiceItemTitle(record);
        }
        c.setChoiceSelected(getSelectedSum(item_id, j));
        int selected = c.getChoiceSelected();
        c.setChoicePercnt(getTotalPercnt(item_id, selected, enquete));

        choiceresults.add(c);

      }

      return choiceresults;

    } catch (Exception ex) {
      logger.error("Exception", ex);
      return null;

    }

  }

  public static List<EnqueteChoiceItemRecordData> getSummaryChoiceItemFreeText(
      RunData rundata, String flag, int item_id, int someAnswer,
      AvzTEnquete enquete) throws ALPageNotFoundException {

    try {

      DataContext dataContext =
        DatabaseOrmService.getInstance().getDataContext();

      SelectQuery<AvzTEnqueteChoiceItem> choicequery =
        Database.query(AvzTEnqueteChoiceItem.class);

      Expression choiceexp =
        ExpressionFactory.matchDbExp(
          AvzTEnqueteChoiceItem.AVZ_TENQUETE_ITEM_PROPERTY
            + "."
            + AvzTEnqueteItem.ITEM_ID_PK_COLUMN,
          item_id);

      Expression choiceexp2 =
        ExpressionFactory.matchExp(
          AvzTEnqueteChoiceItem.STATUS_PROPERTY,
          STATUS_SELECT);

      choicequery.setQualifier(choiceexp.andExp(choiceexp2));
      choicequery.distinct(true);

      ResultList<AvzTEnqueteChoiceItem> choicelist =
        choicequery.getResultList();

      List<EnqueteChoiceItemRecordData> choiceresults =
        new ArrayList<EnqueteChoiceItemRecordData>();

      if (FLAG_SUMMARY.equals(flag)) {
        // アンケート集計画面の場合
        if (choicelist.size() == 0) {
          EnqueteChoiceItemRecordData c = new EnqueteChoiceItemRecordData();
          c.setChoiceSelected(getSelectedSum(item_id, 1));
          int selected = c.getChoiceSelected();
          c.setChoicePercnt(getTotalPercnt(item_id, selected, enquete));
          c.setFreeTextLength(choicelist.size());

          choiceresults.add(c);

        } else {

          for (int j = 0; j < choicelist.size(); j++) {
            AvzTEnqueteChoiceItem choiceitem = choicelist.get(j);
            EnqueteChoiceItemRecordData c = new EnqueteChoiceItemRecordData();
            ALStringField record = new ALStringField();
            if ("".equals(choiceitem.getChoiceItemTitle())) {
              record.setValue("記入なし");
            } else {
              record.setValue(choiceitem.getChoiceItemTitle());
            }
            c.setChoiceItemTitle(record);
            c.setChoiceSelected(getSelectedSum(item_id, j));
            int selected = c.getChoiceSelected();
            c.setChoicePercnt(getTotalPercnt(item_id, selected, enquete));
            c.setFreeTextLength(choicelist.size());

            choiceresults.add(c);

          }
        }
      } else if (FLAG_CSV.equals(flag)) {
        // "記入なし"カウント
        int noText = 0;
        if (choicelist.size() != 0) {
          for (int j = 0; j < choicelist.size(); j++) {
            AvzTEnqueteChoiceItem choiceitem = choicelist.get(j);
            EnqueteChoiceItemRecordData c = new EnqueteChoiceItemRecordData();

            if ("".equals(choiceitem.getChoiceItemTitle())) {
              noText++;
            } else {
              String title = choiceitem.getChoiceItemTitle();
              title = title.replaceAll("\"", "\"\"");
              c.setCsvChoiceItem(title);
              c.setChoiceSelected(1);
              choiceresults.add(c);
            }
          }
          if (noText != 0) {
            EnqueteChoiceItemRecordData c = new EnqueteChoiceItemRecordData();
            c.setCsvChoiceItem("記入なし");
            c.setChoiceSelected(noText);
            choiceresults.add(c);
          }
        } else {
          SelectQuery<AvzTEnqueteChoiceItem> query =
            Database.query(AvzTEnqueteChoiceItem.class);

          Expression exp =
            ExpressionFactory.matchDbExp(
              AvzTEnqueteChoiceItem.AVZ_TENQUETE_ITEM_PROPERTY
                + "."
                + AvzTEnqueteItem.ITEM_ID_PK_COLUMN,
              item_id);

          query.setQualifier(exp);
          query.distinct(true);

          ResultList<AvzTEnqueteChoiceItem> list = query.getResultList();

          AvzTEnqueteChoiceItem choice = list.get(0);
          EnqueteChoiceItemRecordData record =
            new EnqueteChoiceItemRecordData();
          // 選択項目タイトル
          record.setCsvChoiceItem(choice.getChoiceItemTitle());
          // 選択項目番号
          record.setChoiceItemNumber(choice.getChoiceItemNumber());
          // 回答者
          record.setChoiceSelected(0);
          choiceresults.add(record);
        }
      }

      return choiceresults;

    } catch (Exception ex) {
      logger.error("Exception", ex);
      return null;

    }

  }

  public static int getSelectedSum(int item_id, int j) {

    int selectedSum = 0;

    // 選択肢番号
    int choiceNum = j + 1;
    DataContext dataContext = DatabaseOrmService.getInstance().getDataContext();

    SelectQuery<AvzTEnqueteChoiceItem> choicequery =
      Database.query(AvzTEnqueteChoiceItem.class);

    Expression choiceexp =
      ExpressionFactory.matchDbExp(
        AvzTEnqueteChoiceItem.AVZ_TENQUETE_ITEM_PROPERTY
          + "."
          + AvzTEnqueteItem.ITEM_ID_PK_COLUMN,
        item_id);
    Expression choiceexp2 =
      ExpressionFactory.matchExp(
        AvzTEnqueteChoiceItem.STATUS_PROPERTY,
        STATUS_SELECT);
    Expression choiceexp3 =
      ExpressionFactory.matchExp(
        AvzTEnqueteChoiceItem.CHOICE_ITEM_NUMBER_PROPERTY,
        choiceNum);

    choicequery.setQualifier(choiceexp.andExp(choiceexp2).andExp(choiceexp3));

    ResultList<AvzTEnqueteChoiceItem> choicelist = choicequery.getResultList();

    selectedSum = choicelist.size();

    return selectedSum;
  }

  public static double getTotalPercnt(int item_id, int selected,
      AvzTEnquete enquete) {

    double totalPercnt = 0.0;
    // 選択された選択肢の総数をdouble型に
    double selectedSum = selected;

    // 同じ項目IDを持つ選択肢の総数を取得する
    DataContext dataContext = DatabaseOrmService.getInstance().getDataContext();

    SelectQuery<AvzTEnqueteChoiceItem> choicequery =
      Database.query(AvzTEnqueteChoiceItem.class);

    Expression choiceexp =
      ExpressionFactory.matchDbExp(
        AvzTEnqueteChoiceItem.AVZ_TENQUETE_ITEM_PROPERTY
          + "."
          + AvzTEnqueteItem.ITEM_ID_PK_COLUMN,
        item_id);
    Expression choiceexp2 =
      ExpressionFactory.matchExp(
        AvzTEnqueteChoiceItem.ENQUETE_ID_PROPERTY,
        enquete.getEnqueteId());

    choicequery.setQualifier(choiceexp.andExp(choiceexp2));
    ResultList<AvzTEnqueteChoiceItem> choicelist = choicequery.getResultList();

    // 項目ID毎の総数
    double Sum = choicelist.size();

    // 項目毎の選択肢数を取得
    SelectQuery<AvzTEnqueteItem> itemquery =
      Database.query(AvzTEnqueteItem.class);

    Expression itemexp =
      ExpressionFactory.matchDbExp(AvzTEnqueteItem.ITEM_ID_PK_COLUMN, item_id);

    itemquery.setQualifier(itemexp);
    ResultList<AvzTEnqueteItem> itemlist = itemquery.getResultList();

    double choiceSum = 0.0;
    for (int i = 0; i < itemlist.size(); i++) {
      AvzTEnqueteItem item = itemlist.get(i);
      item.getChoiceItemNum();
      // 項目毎の選択肢数
      choiceSum = item.getChoiceItemNum();
    }

    // 分母になる数
    // 項目÷選択肢数で回答メンバーの総数を取得
    double memberSum = Sum / choiceSum;

    totalPercnt = selectedSum / memberSum * 100;

    // 小数点第二位以下切捨て
    BigDecimal bi = new BigDecimal(String.valueOf(totalPercnt));
    totalPercnt = bi.setScale(1, BigDecimal.ROUND_DOWN).doubleValue();

    return totalPercnt;

  }

  public static ResultList<AvzTEnquete> setPublicStatus(
      ResultList<AvzTEnquete> list) {

    // 現在時刻を取得
    Date now = Calendar.getInstance().getTime();

    for (int i = 0; i < list.size(); i++) {

      AvzTEnquete enquete = list.get(i);

      if (DEADLINE_METHOD_HANDOPERATED.equals(enquete.getDeadlineMethod())) {
        continue;
      } else {

        // 締切時刻を取得する
        Date date = enquete.getDeadlineDate();

        // 締切時刻が現在時刻より前だった場合締切にする
        if (date.before(now)) {
          enquete.setPublicStatus(PUBLIC_STATUS_AFTER);
          enquete.setDeadlineDate(getEmptyDate());
          Database.commit();

        } else {
          continue;

        }
      }
    }
    return list;
  }

}
