import os
import sys
import shutil


sys.path.append("D:/tkProg/tkProg.main/tklib/python")


from tklib.tkutils import terminate, getarg, getintarg, getfloatarg
from tklib.tkutils import pint, pfloat
from tklib.tkparams import tkParams
from tklib.tkapplication import tkApplication
from tklib.tkexcel_db import tkExcelDB


app     = tkApplication()
cparams = tkParams()

cparams.infile1 = ""
cparams.infile2 = ""
cparams.outfile     = ''
cparams.irow_label1       = 1
cparams.icol_origin1      = 1
cparams.irow_data_origin1 = 2
cparams.irow_label2       = 1
cparams.icol_origin2      = 1
cparams.irow_data_origin2 = 2

cparams.key = '番号'

tmp_file = 'tmp.xlsx'
password = ""


cparams.outfile           = getarg   (1, cparams.outfile)
cparams.infile1           = getarg   (2, cparams.infile1)
cparams.irow_label1       = getintarg(3, cparams.irow_label1)
cparams.icol_origin1      = getintarg(4, cparams.icol_origin1)
cparams.irow_data_origin1 = getintarg(5, cparams.irow_data_origin1)
cparams.infile2           = getarg   (6, cparams.infile2)
cparams.irow_label2       = getintarg(7, cparams.irow_label2)
cparams.icol_origin2      = getintarg(8, cparams.icol_origin2)
cparams.irow_data_origin2 = getintarg(9, cparams.irow_data_origin2)
cparams.key               = getarg  (10, cparams.key)
password                  = getarg  (11, password)


if password is None:
    print("")
    password = input("Input password >>")

def clean_up(*files):
    print("")
    print("ファイルクリーンアップ")
    for f in files:
        if os.path.isfile(f):
            print(f"  {f} が存在ます。削除します")
            os.remove(f)

def merge():
    global app, cparams

# 一時ファイル、出力ファイルを削除
    clean_up(cparams.outfile, tmp_file)

    print("")
    print(f"入力ファイル1: {cparams.infile1}")
    print(f"  irow_label1      : {cparams.irow_label1}")
    print(f"  icol_origin1     : {cparams.icol_origin1}")
    print(f"  irow_data_origin1: {cparams.irow_data_origin1}")
    print(f"  キーラベル       : {cparams.key})")
    print(f"入力ファイル2: {cparams.infile2}")
    print(f"  irow_label2      : {cparams.irow_label2}")
    print(f"  icol_origin2     : {cparams.icol_origin2}")
    print(f"  irow_data_origin2: {cparams.irow_data_origin2}")
    print(f"出力ファイル: {cparams.outfile})")

# 入力ファイル読み込み
    print("")
    print(f"{cparams.infile1}読み込み")
    xls_in1 = tkExcelDB(cparams.infile1, table_name = None, password = password, tmp_file = tmp_file,
                        OpenFile = True, CloseFile = True, description = '入力ファイル1', IsPrint = True)
    if xls_in1.ws is None:
        print("")
        print(f"Error: Cannot read [{cparams.infile1}]")
        exit()

    print(f"{cparams.infile2}読み込み")
    xls_in2 = tkExcelDB(cparams.infile2, table_name = None, password = password, tmp_file = tmp_file,
                        OpenFile = True, CloseFile = True, data_only = True, description = '入力ファイル2', IsPrint = True)
    if xls_in2.ws is None:
        print("")
        print(f"Error: Cannot read [{cparams.infile2}]")
        exit()

# 出力ファイル作成
    print("")
    if not os.path.isfile(cparams.outfile):
        print(f"入力ファイル1 {cparams.infile1} を出力ファイル {cparams.outfile} にコピーします")
        shutil.copyfile(cparams.infile1, cparams.outfile)

        """
        print(f"出力ファイル {cparams.outfile} を作成します")
        xls_out = tkExcelDB(cparams.outfile, mode = 'w', OpenFile = True, CloseFile = True, data_only = True,
                        description = '出力ファイル', IsPrint = True)
        print(f"入力ファイル1 {cparams.infile1} のシートをコピーします")
        xls_out.copy_worksheet(ws_source = xls_in1.ws, title = None)
        """
    
    print(f"出力ファイル {cparams.outfile}を読み込みます")
    xls_out = tkExcelDB(cparams.outfile, table_name = None, password = password, tmp_file = tmp_file,
                        mode = 'r', OpenFile = True, CloseFile = True, data_only = True, description = '出力ファイル', IsPrint = True)

#入力ファイルからkeyのあるデータを抽出
    key_column_org1 = xls_in1.get_icolumn_from_label(cparams.key, column_org = cparams.icol_origin1, row_org = cparams.irow_label1, 
                        sheet = None)
    key_column_org2 = xls_in2.get_icolumn_from_label(cparams.key, column_org = cparams.icol_origin2, row_org = cparams.irow_label2, 
                        sheet = None)

    print("")
    xls_in1.print_inf()
    if key_column_org1 is None:
        print("")
        print(f"Error: [{cparams.key}]列が入力ファイル1 [{cparams.infile1}] {cparams.icol_origin1}列以降の{cparams.irow_label1}行目に見つかりません")
        print("")
        exit()
    print(f"  [{cparams.key}]列の番号: ", key_column_org1)
    xls_in2.print_inf()
    if key_column_org2 is None:
        print("")
        print(f"Error: [{cparams.key}]列が入力ファイル2 [{cparams.infile2}] {cparams.icol_origin2}列以降の{cparams.irow_label2}行目に見つかりません")
        print("")
        exit()
    print(f"  [{cparams.key}]列の番号: ", key_column_org2)
    xls_out.print_inf()

#入力ファイルから学籍番号のあるデータを抽出
    print("")
    print("入力ファイル2:")
    max_column = xls_in2.max_column()
    max_row    = xls_in2.max_row()
    key_column_org = xls_in2.get_icolumn_from_label(cparams.key, column_org = cparams.icol_origin2, row_org = cparams.irow_label2, 
                        sheet = None)
    print( "  最大列番号:", max_column)
    print( "  最大行番号:", max_row)

    condition =  f"'{cparams.key}' neq ''"
    print("")
    print(f"ファイル2から [{condition}] を満たすデータを抽出します")
    inf = xls_in2.select(condition, target_labels = None, 
                            key_column_org = cparams.icol_origin2, key_row_org = cparams.icol_origin2, 
                            target_row_org = cparams.irow_data_origin2,
                            ret_type = 'all', is_print = False)
    target_labels = inf["labels"]
    data_list     = inf["list"]
    irow_list     = inf["irows"]
    print("  取得列ラベル: ", target_labels)
#    print("  data_list=", data_list)

    print("")
    print(f"ファイル2 [{cparams.infile2}] の内容を出力ファイル [{cparams.outfile}] にコピーします")
#ラベルを出力ファイルにコピー
    max_column = xls_out.max_column()
    icol_offset = max_column + 2
    for i in range(len(target_labels)):
        irow_source = cparams.irow_label2
        icol_source = cparams.icol_origin2 + i
        irow_target = cparams.irow_label1
        icol_target = icol_offset + i

        xls_out.copy_cell_format(ws_source = xls_in2.ws, irow_source = irow_source, icol_source = icol_source, 
                               ws_target = None, irow_target = irow_target, icol_target = icol_target, 
                               format = 'value|fill|font|border')

#データを出力ファイルにコピー
    for idx in range(len(data_list)):
        id          = data_list[idx][0]
        irow_source = irow_list[idx]

        hit = xls_out.select_irows(f"'{cparams.key}' eq '{id}'", 
                    key_column_org = cparams.icol_origin1, key_row_org = cparams.icol_origin1, 
                    target_row_org = cparams.irow_data_origin1, first_hit_only = True)

        if hit is not None:
            irow = hit
        else:
            irow = xls_out.max_row() + 1

        for i in range(len(target_labels)):
            icol_source = i + 1
            icol_target = icol_offset + i
            irow_target = irow
            val = xls_in2.get(irow = irow_source, icol = icol_source)

            print(f"{idx+1}行[{target_labels[i]}]: {icol_source+1}列{irow_source+1}行の値 [{val}] を出力ファイル {irow_target}行{icol_target}列にコピー")
            xls_out.copy_cell_format(ws_source = xls_in2.ws, irow_source = irow_source, icol_source = icol_source, 
                                     ws_target = None,       irow_target = irow_target, icol_target = icol_target, 
                                     format = 'value|fill|font|border')

    print("")
    print(f"出力ファイル {cparams.outfile} に保存します")
    xls_out.save(path = None, workbook = None)


def main():
    merge()


if __name__ == "__main__":
    main()