from cms.common import util
from dataAccess import dataAccess


def init_proc(check_kbn, check_period):
    db_access = dataAccess.DBAccess()

    # チェックリストの取得
    check_list_kbn = db_access.select_rows(
        "select"
        " kbnKey"
        " , name"
        " , case " + str(check_kbn) +
        "     when kbnKey then 'selected'"
        "                 else ''"
        "    end as selectedValue"
        " from m_kbnmeisyo"
        " where kbn = 1")

    # チェック期間の取得
    if check_period == "":
        now_date = util.now_datetime().strftime('%Y/%m/%d')
    else:
        now_date = check_period

    if check_kbn == 0:
        check_kbn = check_list_kbn[0]["kbnKey"]

    check_period_list = db_access.select_rows(
        "select"
        "  DATE_FORMAT(stDate, '%Y/%m/%d') as value"
        "  , case DATE_FORMAT(stDate, '%m')"
        "    when DATE_FORMAT(edDate, '%m') "
        "      then CONCAT(DATE_FORMAT(stDate, '%Y'), '年', DATE_FORMAT(stDate, '%m'), '月')"
        "      else CONCAT(DATE_FORMAT(stDate, '%Y'), '年', DATE_FORMAT(stDate, '%m'), '月 ～'"
        "                , DATE_FORMAT(edDate, '%Y'), '年', DATE_FORMAT(edDate, '%m'), '月')"
        "    end as dispValue"
        "  , case DATE_FORMAT(stDate, '%Y/%m/%d')"
        "      when '" + now_date + "' then 'selected'"
        "                              else ''"
        "    end as selectedValue"
        " From m_checkperiod"
        " where checkKbn = " + str(check_kbn) +
        " order by stDate desc")
    if check_period == "":
        now_date = check_period_list[0]["value"]

    # 回答の取得
    row_answer_list = db_access.select_rows(
        "select answerCode, answerNm "
        " from m_checkanswer "
        " where checkKbn = " + str(check_kbn) +
        " order by answerCode")

    # 対象者＆チェック状態の取得
    user_check_result_list = db_access.select_rows(
        "select syainName, SYAINCODE"
        "  , ("
        "     select count(*)"
        "     from t_checkresult tcr"
        "     where  ms.syainCode = tcr.userId"
        "     and tcr.checkKbn = " + str(check_kbn) +
        "     and tcr.implementationDate = '" + now_date + "'"
        "    ) as resultCnt"
    #    "  , row_number() OVER (ORDER BY SYAINCODE) - 1 as rowNumber"
        " From m_syain ms"
        " where GroupCode in ('06','07')"
        " order by SYAINCODE")

    check_result_count_list = db_access.select_rows(
        "select"
        "  userId"
        "  , answerCode"
        "  , count(*) as CNT"
        " from t_checkresult"
        " where checkKbn = " + str(check_kbn) +
        "     and implementationDate = '" + now_date + "'"
        " group by userId, checkKbn, answerCode")

    row_no = 0
    check_count_list = []
    user_check_result_list_mod = []
    for row_res in user_check_result_list:
        for row_ans in row_answer_list:
            check_count = 0
            for row_cnt in check_result_count_list:
                if row_res["SYAINCODE"] == row_cnt["userId"] and row_ans["answerCode"] == row_cnt["answerCode"]:
                    check_count = row_cnt["CNT"]
                    break

            check_count_list.append(check_count)

        row_in = {
            'syainName': row_res["syainName"],
            'syainCode': row_res["SYAINCODE"],
            'resultCnt': row_res["resultCnt"],
            'rowNumber': row_no,
            'checkCountList': check_count_list,
        }
        user_check_result_list_mod.append(row_in)
        check_count_list = []
        row_no = row_no + 1

    # 確定状態の取得
    kakutei = db_access.select_row(
        "select kakuteiFlg"
        " from m_checkperiod"
        " where checkKbn = " + str(check_kbn) +
        " and stDate = '" + now_date + "'")

    return check_list_kbn, check_period_list, user_check_result_list_mod, check_kbn, now_date,row_answer_list, kakutei["kakuteiFlg"]


def regist_kakutei_flg(check_kbn, now_date):
    db_access = dataAccess.DBAccess()

    db_access.regist_db(
        "update m_checkperiod "
        " set kakuteiFlg ="
        "   case kakuteiFlg when 0 then 1 else 0 end"
        " where checkKbn = " + str(check_kbn) +
        " and   stDate   = '" + now_date + "'")

    db_access.commit()

    # 確定状態の取得
    kakutei = db_access.select_row(
        "select kakuteiFlg"
        " from m_checkperiod"
        " where checkKbn = " + str(check_kbn) +
        " and stDate = '" + now_date + "'")

    if kakutei.kakuteiFlg == 1:
        return "確定が完了しました。"
    else:
        return "確定解除が完了しました。"