from   logging             import getLogger, config, DEBUG, INFO, WARN, ERROR, CRITICAL, FATAL
import ftplib            
import json
import os
import sys
import codecs as cd
import math
import locale
import errno
import datetime
import csvExcelConst
import unicodedata
import configparser
from   csvExcelImportXml import csv_excel_import_xml
import pandas as pd 
import csvExcelUtil as pu
import openpyxl     as px
import traceback
#! from   openpyxl.styles.fonts    import Font
#! from   openpyxl.worksheet.table import Table, TableStyleInfo
import csvExcelUtil  as pu
from   csvExcelPivot import csv_excel_pivot as pv
from   csvExcelPivot import graph_data      as graph_data
from   csvExcelUtil  import send_mail_data  as sd

# ---------------------------------------------------------------
# サブルーチン
# ---------------------------------------------------------------
def exit_proc(rtn_code: int, message_str, status_file_name:str, output_file_name:str):
    
    with open(status_file_name, 'w') as f:  
        f.write(f"RETURN_CODE {rtn_code} {output_file_name} {message_str}\n") 
        
    sys.exit(rtn_code)

# ---------------------------------------------------------------
# メインルーチン
# ---------------------------------------------------------------
if __name__ == '__main__':

    locale.setlocale(locale.LC_TIME, 'JA_JP.UTF-8')     # ← ja_JP.UTF-8 だとUnSupportになるので注意する事
    execute_dir = os.path.dirname(__file__)             # プログラム実行ディクトレイーの取得
    # =================================
    # 構成ファイルの読込み
    # ---------------------------------
    config_ini_path = f"{execute_dir}/config.ini"
    if  not os.path.exists(config_ini_path):
        print(f"Not Find File = {config_ini_path}")
        raise FileNotFoundError(errno.ENOENT, os.strerror(errno.ENOENT), config_ini_path)
    
    config_ini      = configparser.ConfigParser()
    config_ini.read(config_ini_path, encoding='utf-8')

    conf_default        = config_ini['DEFAULT']
    config_email        = config_ini['MAIL']
    config_ftp          = config_ini['FTP']
    log_conf_file       = conf_default['LOG_CONF']
    log_conf_level      = conf_default['LOG_LEVEL']

    # =================================
    # Logger 設定
    # ---------------------------------
    with open(f"{execute_dir}/{log_conf_file}", "r") as f:
        log_conf = json.load(f)
        config.dictConfig(log_conf)
        _EXEC_FILE_NAME = os.path.basename(__file__)[:-3]

    log = getLogger(_EXEC_FILE_NAME)

    if   log_conf_level.casefold() == "DEBUG".casefold():
         log.setLevel(DEBUG)
    elif log_conf_level.casefold() == "FATAL".casefold():
         log.setLevel(FATAL)
    elif log_conf_level.casefold() == "ERROR".casefold():
         log.setLevel(ERROR)
    elif log_conf_level.casefold() == "WARN".casefold():
         log.setLevel(WARN)
    elif log_conf_level.casefold() == "INFO".casefold():
         log.setLevel(INFO)
    elif log_conf_level.casefold() == "CRITICAL".casefold():
         log.setLevel(CRITICAL)

    args = sys.argv
    
    csv_file_name         = args[1]
    status_file_name      = args[2]
    dict_bnd              = {}
    def_head              = {}
    def_item              = {}
    bnd_mode              = False
    output_base_file_name = ""

    csv_util = pu.csv_excel_util(log)
    csv_imp  = csv_excel_import_xml(log)
    
    # ==================================================== 
    # 拡張子のチェック（拡張子は、BNDもしくはCSVのみ有効
    # ---------------------------------------------------- 
    if  not csv_file_name.casefold().endswith(".CSV".casefold()) and not csv_file_name.casefold().endswith(".BND".casefold()):
        err_msg  = f"ファイル名パラメータの拡張子が、BNDもしくはCSVでありません【{csv_file_name}】"
        rtn_code = 1
        log.log(ERROR, err_msg)
        
        exit_proc(rtn_code, err_msg, status_file_name, "NONE")
        
    # ========================== 
    # ファイルの存在確認
    # -------------------------- 
    if  csv_file_name.casefold().endswith(".BND".casefold()):
        bnd_mode = True

        #
        # BNDファイルの存在チェックとオブジェクトの取得
        rtn_flag, dict_bnd, err_msg = csv_util.check_bnd_files(csv_file_name) 
        if  rtn_flag == False:
            rtn_code = 2
            exit_proc(rtn_code, err_msg, "NONE")
        
        output_base_file_name =dict_bnd[csvExcelConst.BND_OUTPUT_FILE]
        log.log(DEBUG, dict_bnd)
    else:
        #
        # CSVファイル名からDEFファイル名を生成する（拡張子の変更 .CSV -> .DEF )
        def_file_name = csv_util.get_def_file_name(csv_file_name)
        log.log(DEBUG, f"log File Name = {def_file_name}")
        #
        # CSVファイル及びDEFファイルの存在チェック
        rtn_flag, err_msg = csv_util.check_csv_def_files(csv_file_name, def_file_name)
        if  rtn_flag == False:
            rtn_code = 3
            exit_proc(rtn_code, err_msg, status_file_name, "NONE")
        
        #
        # BNDファイルでない時はdef_head及びdef_itemオブジェクトを取得し、BNDオブジェクトにセットする
        def_head, def_item          = csv_imp.get_def(def_file_name)
        def_file                    = def_head[csvExcelConst.FILE]
        output_base_file_name       = def_file[csvExcelConst.OUTPUT_FILE]

        dict_mem = {}
        dict_mem[csvExcelConst.BND_CSV_FILE]    = csv_file_name
        dict_mem[csvExcelConst.BND_DEF_FILE]    = def_file_name
        dict_mem[csvExcelConst.BND_DEF_HEAD]    = def_head
        dict_mem[csvExcelConst.BND_DEF_ITEM]    = def_item
        dict_mem[csvExcelConst.BND_SHEET_NAME]  = output_base_file_name     # シート名はファイル名（拡張子なし）に設定する
        dict_bnd[csvExcelConst.BND_BIND]        = {'000': dict_mem}
        dict_bnd[csvExcelConst.BND_OUTPUT_FILE] = def_file[csvExcelConst.OUTPUT_FILE]

        if  csvExcelConst.MAIL in def_head:
            dict_bnd[csvExcelConst.MAIL]            = def_head[csvExcelConst.MAIL]
        
        log.log(INFO, f"CSVファイル名＝{csv_file_name}、defファイル名＝{def_file_name}")

    # =========================================================
    # Excelファイル出力
    # ---------------------------------------------------------
    output_file_name = output_base_file_name + "_" +  csv_util.get_timestamp_string()  + ".xlsx"

    log.log(INFO, f"出力ファイル名＝{output_file_name}、ベース名＝{output_base_file_name}")

    log.log(DEBUG, dict_bnd)

    with pd.ExcelWriter(output_file_name) as writer:
    
        for bnd in dict_bnd[csvExcelConst.BND_BIND]:
            dict_mem  = dict_bnd[csvExcelConst.BND_BIND].get(bnd)
            tmp_sheet = dict_mem[csvExcelConst.BND_SHEET_NAME]
            bnd_sheet = csv_util.get_sheet_name_with_maxlen(tmp_sheet)  # シート名に許される最大文字数の名称を取得する
            len_sheet = len(bnd_sheet)
            bnd_csv   = dict_mem[csvExcelConst.BND_CSV_FILE]
            bnd_def   = csv_util.get_def_file_name(bnd_csv)

            log.log(DEBUG,f"No = {bnd}, ＣＳＶ＝{bnd_csv}、ＤＥＦ＝{bnd_def}、シート名「{bnd_sheet}」{len_sheet}、bndMode = {bnd_mode}")

            if  bnd_mode == True:
                def_head, def_item = csv_imp.get_def(bnd_def)
                dict_mem[csvExcelConst.BND_DEF_HEAD]    = def_head
                dict_mem[csvExcelConst.BND_DEF_ITEM]    = def_item
                sheet_name        = bnd_sheet 
            else:
                sheet_name        = output_base_file_name
            
            # =========================================================
            # ＣＳＶファイルを読込む
            # ---------------------------------------------------------
            log.log(DEBUG, f"ＣＳＶファイル「{bnd_csv}」ロード開始")
            
            csv_data = csv_util.load_csv_file(bnd_csv)
            
            log.log(DEBUG, f"ＣＳＶファイル「{bnd_csv}」ロード終了")
            
            # =========================================================
            # 読込んだＣＳＶファイルをＥｘｃｅｌファイルへ出力する
            # ---------------------------------------------------------
            num_rows, num_cols = csv_data.shape  # データの行数及び列数を取得する
            dict_mem[csvExcelConst.BND_CSV_ROWS] = num_rows
            dict_mem[csvExcelConst.BND_CSV_COLS] = num_cols
            
            win_lock_row = int(def_item[csvExcelConst.WINDOW_LOCK_ROW]) - 1 # Window枠の固定　行位置
            win_lock_col = int(def_item[csvExcelConst.WINDOW_LOCK_COL]) - 1 # Window枠の固定　列位置
            
            log.log(DEBUG,  f"行数＝{num_rows}、列数＝{num_cols}、winLockRow＝{win_lock_row}、winLockCol＝{win_lock_col}")

            log.log(DEBUG, f"ＣＳＶファイル「{bnd_csv}」ＥＸＣＥＬ出力開始　シート名「{sheet_name}」、シート名長さ「{len_sheet}」")
            csv_data.to_excel(writer, sheet_name=sheet_name, index=False, freeze_panes=(win_lock_row, win_lock_col))
            log.log(DEBUG, f"ＣＳＶファイル「{bnd_csv}」ＥＸＣＥＬ出力終了")
        
    log.log(DEBUG, dict_bnd)
    # =========================================================
    # Ｅｘｃｅｌの各種設定
    # ---------------------------------------------------------
    wb = csv_util.load_excel_file(output_file_name)
    
    for bnd in dict_bnd[csvExcelConst.BND_BIND]:
        dict_mem   = dict_bnd[csvExcelConst.BND_BIND].get(bnd)
        tmp_sheet  = dict_mem[csvExcelConst.BND_SHEET_NAME]
        bnd_sheet  = csv_util.get_sheet_name_with_maxlen(tmp_sheet)  # シート名に許される最大文字数までの文字列を取得する
        len_sheet  = len(bnd_sheet)
        bnd_def    = dict_mem[csvExcelConst.BND_DEF_FILE]
        
        num_rows   = dict_mem[csvExcelConst.BND_CSV_ROWS] 
        num_cols   = dict_mem[csvExcelConst.BND_CSV_COLS]   
        
        log.log(DEBUG, f"DEFファイル名「{bnd_def}」、シート名「{bnd_sheet}」,{len_sheet}、データ行数「{num_rows}」、データ列数「{num_cols}」")
        ws = wb[bnd_sheet]
        
        if  bnd_mode == True:
            def_head, def_item   = csv_imp.get_def(bnd_def)
            def_file             = def_head[csvExcelConst.FILE]
        # =========================================================
        # Ｅｘｃｅｌのフォント設定
        # ---------------------------------------------------------
        font_name = 'メイリオ'
        font_size = 11
        
        log.log(DEBUG, f"font={csvExcelConst.FONT_NAME}、fontSize={csvExcelConst.FONT_SIZE}")

        if  csvExcelConst.FONT_NAME in def_file.keys():
            fontName = def_file[csvExcelConst.FONT_NAME]
        
        if  csvExcelConst.FONT_SIZE in def_file.keys():
            fontSize = int(def_file[csvExcelConst.FONT_SIZE])

        csv_util.set_excel_font(ws, font_name, font_size)
        
        # ================================================================
        # Ｅｘｃｅｌ列の表示形式設定(表示形式は列での一括設定は出来ない)
        # ----------------------------------------------------------------
        #! log.log(DEBUG, f"DEFファイル名「{bnd_def}」、シート名「{bnd_sheet}」,{len_sheet}、データ行数「{num_rows}」、データ列数「{num_cols}」")
        
        csv_util.set_excel_cell_edit_format(ws, def_item, num_rows)
                
        # =========================================================
        # Ｅｘｃｅｌの列幅を自動設定
        # ---------------------------------------------------------
        csv_util.set_excel_cell_width(ws)

        # =========================================================
        # Ｅｘｃｅｌにテーブルを設定
        # ---------------------------------------------------------
        csv_util.set_excel_table(bnd, ws, def_file, num_rows, num_cols)
        
    # =============================================================
    # Ｅｘｃｅｌファイルの上書き
    # -------------------------------------------------------------
    wb.save(output_file_name)   # wb.save(ファイル名) と記述すること！！  wb.saveだとプロパティ扱いされてファイルが保存されない。

    if  bnd_mode == False :
        # =================================
        # ピボット集計
        # ---------------------------------
        if csvExcelConst.PIVOT in def_head: 
    
            excel_df = pd.read_excel(output_file_name, sheet_name=0, header=0, index_col=0)
            log.log(DEBUG, f"カラムリスト＝{excel_df.columns.to_list()}")
            
            pivot = pv(log)
            log.log(DEBUG, f"def head = {def_head}")

            def_pivot = def_head[csvExcelConst.PIVOT]
            
            log.log(DEBUG, f"PIVOT = {def_pivot}")

            if  csvExcelConst.PIVOT_CONVERT_YM in def_pivot:
                def_ym = def_pivot[csvExcelConst.PIVOT_CONVERT_YM]
                for def_ym_mem in def_ym:
                    src_field = def_ym_mem[csvExcelConst.PIVOT_CONVERT_YM_SRC]
                    tar_field = def_ym_mem[csvExcelConst.PIVOT_CONVERT_YM_TARGET]

                    #! log.log(DEBUG, f"insert field src={src_field}, target = {tar_field}")

                    pivot.insert_ym(excel_df, src_field, tar_field, 100)        # 年月日を年月に変換
            
            summary_sheet_name  = def_pivot[csvExcelConst.PIVOT_SHEET_NAME]
            pivot_column_x      = def_pivot[csvExcelConst.PIVOT_COLUMN_X]
            pivot_column_y      = def_pivot[csvExcelConst.PIVOT_COLUMN_Y]
            pivot_column_sum    = def_pivot[csvExcelConst.PIVOT_COLUMN_SUM]
            pivot_sum_type      = def_pivot[csvExcelConst.PIVOT_TYPE]
            pivot_total_name    = def_pivot[csvExcelConst.PIVOT_TOTAL_NAME] # 合計出力項目名
            pivot_total_flag    = False                                     # 合計出力 False:しない  True:する
            pivot_cumulative    = True                                      # 累計集計 False:しない  True:する

            if  def_pivot[csvExcelConst.PIVOT_TOTAL].casefold() == 'True'.casefold():
                pivot_total_flag = True
            
            if  def_pivot[csvExcelConst.PIVOT_CUMULATIVE_SUM].casefold() == 'True'.casefold():
                pivot_cumulative = True

            log.log(DEBUG, f"pivot x={pivot_column_x}, y={pivot_column_y}, sum={pivot_column_sum}, type={pivot_sum_type}, totalName={pivot_total_name}, total_flag={pivot_total_flag}, cumulativ={pivot_cumulative}")
            sales = pivot.create_pivot_table(excel_df, pivot_column_x, pivot_column_y, pivot_column_sum, pivot_sum_type, pivot_total_flag, '', pivot_cumulative)
            
            with pd.ExcelWriter(output_file_name, mode="a") as writer:
                sales.to_excel(writer,    sheet_name=summary_sheet_name, startrow=0)     # 行はゼロから始まる

            log.log(DEBUG, "excel pivot out end")

    # =================================
    # グラフ描画
    # ---------------------------------
        if  csvExcelConst.GRAPH in def_head: 
            def_graph = def_head[csvExcelConst.GRAPH]

            log.log(DEBUG, "def_graph = " + json.dumps(def_graph))

            wb = px.load_workbook(output_file_name)
            
            log.log(DEBUG, "excel reload end")
            ws = wb[summary_sheet_name]
            csv_util.set_excel_cell_edit_format2(ws, "#,##0;[赤]-#,##0", 2, 2)    # 数字領域をカンマ編集表示にする
            csv_util.set_excel_cell_width(ws)                                    # セル幅の調整

            # グラフ描画データ設定
            gd = graph_data()

            gd.ws               = ws
            gd.graph_type       = def_graph[csvExcelConst.GRAPH_TYPE]
            gd.graph_title      = def_graph[csvExcelConst.GRAPH_TITLE]
            gd.x_axis_title     = def_graph[csvExcelConst.GRAPH_X_AXIS]
            gd.y_axis_title     = def_graph[csvExcelConst.GRAPH_Y_AXIS]
            gd.size_x           = int(def_graph[csvExcelConst.GRAPH_X_SIZE])
            gd.size_y           = int(def_graph[csvExcelConst.GRAPH_Y_SIZE])
            gd.graph_group      = def_graph[csvExcelConst.GRAPH_GROUP]
            gd.graph_style      = def_graph[csvExcelConst.GRAPH_STYLE]     # 青系
            gd.graph_position   = def_graph[csvExcelConst.GRAPH_POSITION]
            gd.y_axis_units     = def_graph[csvExcelConst.GRAPH_Y_UNITS]   # 1,000単位  

            # グラフデータのデータ領域

            log.log(DEBUG,f"シート「{summary_sheet_name}」 最大行数＝{ws.max_row}、最大列数＝{ws.max_column}")
            gd.data_min_row     = 1
            gd.data_min_col     = 2
            gd.data_max_row     = ws.max_row        # ワークシートの最大行数
            gd.data_max_col     = ws.max_column     # ワークシートの最大列数

            # グラフデータのカテゴライズ領域
            gd.category_min_row = 2
            gd.category_min_col = 1
            gd.category_max_row = ws.max_row
            gd.category_max_col = 1

            pivot.create_graph(gd)

            # ===================================
            # グラフ描画済みのEXCELファイル保存
            # -----------------------------------
            wb.active = ws      # アクティブシートの変更
            wb.save(output_file_name)

    # =================================
    # メールの送信
    # ---------------------------------
    mail_send:str           = config_email['MailSend']
    mail_system_name:str    = config_email['MailSystem']
    mail_body_str:str       = config_email['BODY_TEXT_FILE']
    
    if  mail_send.casefold() == 'True'.casefold() and csvExcelConst.MAIL in def_head:
        req_data:dict           = dict_bnd[csvExcelConst.MAIL]
        log.log(DEBUG, "メール送信要求データ＝" + json.dumps(req_data))
        try:
            send_mail_data = sd(config_email)
            send_mail_data.config_mail  =   config_email
            
            #メール本文を読み込む
            text = open(f"{execute_dir}/{mail_body_str}", encoding="cp932")
            body_temp = text.read()
            text.close

            # 定型文字列の置換
            body:[str] = [mail_system_name, req_data[csvExcelConst.MAIL_REQUEST_TIME],  datetime.datetime.now()]
            #メール本文の追加
            body_text:str = body_temp.format(
                systemName=body[0],
                requestDateTime=body[1],
                sendDateTime=body[2]
            )
            #
            # Toアドレスのセット（不要な空白を除去する為、一度splitで配列に変換し、メール送信にて再度カンマ区切り文字列に戻す）
            send_mail_data.to_addr  = req_data[csvExcelConst.MAIL_TO_ADDRESS].split(",")
            #
            # Ccアドレスのセット（不要な空白を除去する為、一度splitで配列に変換し、メール送信にて再度カンマ区切り文字列に戻す）
            if  (req_data.get(csvExcelConst.MAIL_CC_ADDRESS) is not None):
                if  len(req_data[csvExcelConst.MAIL_CC_ADDRESS]) > 0:
                    send_mail_data.cc_addr = req_data[csvExcelConst.MAIL_CC_ADDRESS].split(",")
            #
            # Bccアドレスのセット（不要な空白を除去する為、一度splitで配列に変換し、メール送信にて再度カンマ区切り文字列に戻す）
            if  (req_data.get(csvExcelConst.MAIL_BCC_ADDRESS) is not None):
                if  len(req_data[csvExcelConst.MAIL_BCC_ADDRESS]) > 0:
                    send_mail_data.bcc_addr = req_data[csvExcelConst.MAIL_BCC_ADDRESS].split(",")
            
            send_mail_data.subject  = req_data[csvExcelConst.MAIL_SUBJECT]
            send_mail_data.body     = body_text
            send_mail_data.attachment_file = [output_file_name]

            successFlag, mailErrMsg =  csv_util.send_mail(send_mail_data)  
            if  successFlag == False:
                err_msg = f"メール送信エラー＝{mailErrMsg}"
                log.log(ERROR, err_msg)
                rtn_code = 10
                exit_proc(rtn_code, err_msg, status_file_name, output_file_name)
                
        except Exception as e:
            err_msg = f"メール送信処理にてエラーが発生しました。"
            log.log(ERROR, err_msg)
            log.log(ERROR, traceback.format_exc())
            rtn_code = 10
            exit_proc(rtn_code, err_msg, status_file_name, output_file_name)
            
    # =================================
    # ＦＴＰ送信
    # ---------------------------------
    ftp_send:str           = config_ftp['FtpSend']
    if  ftp_send.casefold() == 'True'.casefold() and csvExcelConst.FTP in def_head:

        try:
            def_ftp:dict        = def_head[csvExcelConst.FTP]

            log.log(DEBUG, f"def_ftp = {def_ftp}")
            ftp_directory:str   = ""
            if  csvExcelConst.FTP_FOLDER in def_ftp:
                ftp_directory:str   = def_ftp[csvExcelConst.FTP_FOLDER]

            ftp_server:str      = config_ftp['FtpServer']
            ftp_user_id:str     = config_ftp['FtpUser']
            ftp_user_pass:str   = config_ftp['FtpPassword']
            ftp_passive:str     = config_ftp['FtpPassive']
            ftp_debug_level:str = config_ftp['DEBUG_LEVEL']

            log.log(DEBUG, f"FTP サーバー＝{ftp_server}")
            log.log(DEBUG, f"FTP ユーザーＩＤ＝{ftp_user_id}")
            log.log(DEBUG, f"FTP ユーザーパスワード＝{ftp_user_pass}")
            log.log(DEBUG, f"FTP パッシッブモード＝{ftp_passive}")
            log.log(DEBUG, f"FTP デバッグレベル＝{ftp_debug_level}")
            log.log(DEBUG, f"FTP ディレクトリー＝{ftp_directory}")
            
            ftp = ftplib.FTP(ftp_server)
            log.log(DEBUG, "ＦＴＰ接続終了")

            # ログレベルの設定
            if  ftp_debug_level is not None:
                ftp.set_debuglevel(int(ftp_debug_level)) 
                log.log(DEBUG, "ＦＴＰデバッグレベル設定終了")

            # パッシッブモードの設定
            if  ftp_passive.casefold() == 'True'.casefold():
                ftp.set_pasv(True)
            if  ftp_passive.casefold() == 'False'.casefold():
                ftp.set_pasv(False)
            
            log.log(DEBUG, f"ＦＴＰパッシブモード設定終了 {ftp_passive}")

            ftp.login(ftp_user_id, ftp_user_pass)
            log.log(DEBUG, "ＦＴＰログイン終了")
            
            if  ftp_directory != "":
                ftp.cwd(ftp_directory)
                log.log(DEBUG, "ＦＴＰディレクトリー変更終了")

            f = open(output_file_name, "rb")
            ftp.storbinary('STOR {}'.format(output_file_name), f)
            log.log(DEBUG, "ＦＴＰ  ファイル送信終了")

            f.close()
            ftp.quit()
            log.log(DEBUG, "ＦＴＰクローズ終了")
        except Exception as e:
            err_msg = "FTP送信処理にてエラーが発生しました。"
            log.log(ERROR, err_msg)
            log.log(ERROR, traceback.format_exc())

            rtn_code = 20
            exit_proc(rtn_code, err_msg, status_file_name, output_file_name)
            

    # =================================
    # 正常終了
    # ---------------------------------
    log.log(DEBUG,  "=================================================")
    log.log(DEBUG, f"|         End of csvExcel  ({output_file_name})")    
    log.log(DEBUG,  "=================================================")
    
    # 正常終了  終了コード＝０を返す
    rtn_code = 0
    err_msg  = "SUCCESSFUL"
    exit_proc(rtn_code, err_msg, status_file_name, output_file_name)
    