import time
from os import path

import pytest

from src.aws.s3 import VjskReceiveBucket
from src.batch.common.batch_context import BatchContext
from src.batch.vjsk.vjsk_data_load_manager import VjskDataLoadManager
# from src.batch.vjsk.vjsk_data_load_manager import VjskDataLoadManager
from src.batch.vjsk.vjsk_importer import (_check_received_files,
                                          _import_file_to_db)
from src.db.database import Database
from src.error.exceptions import BatchOperationException
# from tests.testing_vjsk_utility import create_vjsk_assertion_dictionary
from tests.testing_vjsk_utility import (assert_table_results,
                                        create_vjsk_assertion_list)


class TestImportFileToDb:
    db: Database
    batch_context: BatchContext
    test_file_path_import_all: str
    test_file_path_load_individual: str
    test_file_path_unzip_error: str

    @pytest.fixture(autouse=True, scope='function')
    def pre_test(self, database: Database):
        """テスト実行前後処理"""
        # setup
        self.test_file_path_import_all = path.join(path.dirname(__file__), "testdata", "TestImportFileToDb")
        self.test_file_path_load_individual = path.join(path.dirname(__file__), "testdata")
        self.test_file_path_unzip_error = path.join(path.dirname(__file__), "testdata", "UnzipError")

        self.batch_context = BatchContext.get_instance()

        self.db = database
        self.db.connect()
        self.db.execute("set sql_mode = 'TRADITIONAL';")
        # self.db.begin()

        # testing
        yield

        # teardown
        # self.db.rollback()
        self.db.disconnect()

    def test_import_file_to_db_ok(self, s3_client, bucket_name, receive_folder, mapper):
        """
        観点
            正常系 : すべての受領データをデータベースに登録できる
        期待値
            例外が発生しない
        """
        # setup - 卸在庫データ取込対象日
        self.batch_context.is_vjsk_stock_import_day = True

        # setup - S3受領バケットの内容をすべて削除する
        vjsk_recv_bucket = VjskReceiveBucket()
        s3_files = vjsk_recv_bucket.get_s3_file_list()
        for file_obj in s3_files:
            s3_client.delete_object(Bucket=bucket_name, Key=file_obj.get("filename"))

        # setup - テスト用受領ファイルをS3受領バケットにupload
        test_files = [
            "stock_slip_data_202304270000.gz",
            "slip_data_202304270000.gz",
            "org_cnv_mst_202304270000.gz",
            "vop_hco_merge_202304270000.gz",
            "whs_mst_202304270000.gz",
            "hld_mst_202304270000.gz",
            "fcl_mst_202304270000.gz",
            "mkr_org_horizon_202304270000.gz",
            "tran_kbn_mst_202304270000.gz",
            "phm_prd_mst_202304270000.gz",
            "phm_price_mst_202304270000.gz",
            "whs_customer_mst_202304270000.gz",
            "mdb_conv_mst_202304270000.gz",
            "bio_slip_data_202304270000.gz",
            "lot_num_mst_202304270000.gz"
        ]
        for test_file in test_files:
            file_name = path.join(self.test_file_path_import_all, test_file)
            key = f"{receive_folder}/{test_file}"
            s3_client.upload_file(file_name, bucket_name, key)

        self.db.execute(f"truncate table {mapper.get_src_table(mapper.CONDKEY_STOCK_SLIP_DATA)}")
        self.db.execute(f"truncate table {mapper.get_src_table(mapper.CONDKEY_SLIP_DATA)}")
        self.db.execute(f"truncate table {mapper.get_src_table(mapper.CONDKEY_ORG_CNV_MST)}")
        self.db.execute(f"truncate table {mapper.get_src_table(mapper.CONDKEY_VOP_HCO_MERGE)}")
        self.db.execute(f"truncate table {mapper.get_src_table(mapper.CONDKEY_WHS_MST)}")
        self.db.execute(f"truncate table {mapper.get_src_table(mapper.CONDKEY_HLD_MST)}")
        self.db.execute(f"truncate table {mapper.get_src_table(mapper.CONDKEY_FCL_MST)}")
        self.db.execute(f"truncate table {mapper.get_src_table(mapper.CONDKEY_MKR_ORG_HORIZON)}")
        self.db.execute(f"truncate table {mapper.get_src_table(mapper.CONDKEY_TRAN_KBN_MST)}")
        self.db.execute(f"truncate table {mapper.get_src_table(mapper.CONDKEY_PHM_PRD_MST)}")
        self.db.execute(f"truncate table {mapper.get_src_table(mapper.CONDKEY_PHM_PRICE_MST)}")
        self.db.execute(f"truncate table {mapper.get_src_table(mapper.CONDKEY_WHS_CUSTOMER_MST)}")
        self.db.execute(f"truncate table {mapper.get_src_table(mapper.CONDKEY_MDB_CONV_MST)}")
        self.db.execute(f"truncate table {mapper.get_src_table(mapper.CONDKEY_BIO_SLIP_DATA)}")
        self.db.execute(f"truncate table {mapper.get_src_table(mapper.CONDKEY_LOT_NUM_MST)}")

        # assertion
        received_s3_files = _check_received_files()
        _import_file_to_db(received_s3_files)

        # 検証
        condkey = mapper.CONDKEY_STOCK_SLIP_DATA
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_org_table(condkey)}"))
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_src_table(condkey)}"))
        condkey = mapper.CONDKEY_SLIP_DATA
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_org_table(condkey)}"))
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_src_table(condkey)}"))
        condkey = mapper.CONDKEY_ORG_CNV_MST
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_org_table(condkey)}"))
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_src_table(condkey)}"))
        condkey = mapper.CONDKEY_VOP_HCO_MERGE
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_org_table(condkey)}"))
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_src_table(condkey)}"))
        condkey = mapper.CONDKEY_WHS_MST
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_org_table(condkey)}"))
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_src_table(condkey)}"))
        condkey = mapper.CONDKEY_HLD_MST
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_org_table(condkey)}"))
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_src_table(condkey)}"))
        condkey = mapper.CONDKEY_FCL_MST
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_org_table(condkey)}"))
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_src_table(condkey)}"))
        condkey = mapper.CONDKEY_MKR_ORG_HORIZON
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_org_table(condkey)}"))
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_src_table(condkey)}"))
        condkey = mapper.CONDKEY_TRAN_KBN_MST
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_org_table(condkey)}"))
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_src_table(condkey)}"))
        condkey = mapper.CONDKEY_PHM_PRD_MST
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_org_table(condkey)}"))
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_src_table(condkey)}"))
        condkey = mapper.CONDKEY_PHM_PRICE_MST
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_org_table(condkey)}"))
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_src_table(condkey)}"))
        condkey = mapper.CONDKEY_WHS_CUSTOMER_MST
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_org_table(condkey)}"))
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_src_table(condkey)}"))
        condkey = mapper.CONDKEY_MDB_CONV_MST
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_org_table(condkey)}"))
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_src_table(condkey)}"))
        condkey = mapper.CONDKEY_BIO_SLIP_DATA
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_org_table(condkey)}"))
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_src_table(condkey)}"))
        condkey = mapper.CONDKEY_LOT_NUM_MST
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_org_table(condkey)}"))
        assert 10 == len(self.db.execute_select(f"select * from {mapper.get_src_table(condkey)}"))

        # teardown
        for test_file in test_files:
            key = f"{receive_folder}/{test_file}"
            s3_client.delete_object(Bucket=bucket_name, Key=key)

    def test_load_01_stock_slip_data_ok(self, mapper):
        table_name_org = mapper.get_org_table(mapper.CONDKEY_STOCK_SLIP_DATA)
        table_name_src = mapper.get_src_table(mapper.CONDKEY_STOCK_SLIP_DATA)

        # setup
        self.batch_context.is_vjsk_stock_import_day = True
        self.db.execute(f"truncate table {table_name_src}")

        # assertion1 (insert 4row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_STOCK_SLIP_DATA,
            "src_file_path": path.join(self.test_file_path_load_individual, "stock_slip_data_202304280000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果を取得
        result_src = self.db.execute_select(f"select * from {table_name_src}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # assertion2 (update 2row +insert 2row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_STOCK_SLIP_DATA,
            "src_file_path": path.join(self.test_file_path_load_individual, "stock_slip_data_202304290000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果(orgテーブル結果のPK値で一致するもの)を取得
        result_src = self.db.execute_select(
            f"select * from {table_name_src} s inner join {table_name_org} o on (s.slip_mgt_num = o.slip_mgt_num)")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # srcテーブル結果のレコード件数 (insert 4row + update 2row + insert 2row = 6row)
        result_src_count = self.db.execute_select(f"select count(*) from {table_name_src} ")
        assert result_src_count[0]['count(*)'] == 6

        # teardown

    def test_load_02_slip_data_ok(self, mapper):
        table_name_org = mapper.get_org_table(mapper.CONDKEY_SLIP_DATA)
        table_name_src = mapper.get_src_table(mapper.CONDKEY_SLIP_DATA)

        # setup
        self.batch_context.is_vjsk_stock_import_day = True
        self.db.execute(f"truncate table {table_name_src}")

        # assertion1 (insert 4row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_SLIP_DATA,
            "src_file_path": path.join(self.test_file_path_load_individual, "slip_data_202304280000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果を取得
        result_src = self.db.execute_select(f"select * from {table_name_src}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # assertion2 (update 2row +insert 2row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_SLIP_DATA,
            "src_file_path": path.join(self.test_file_path_load_individual, "slip_data_202304290000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果(orgテーブル結果のPK値で一致するもの)を取得
        result_src = self.db.execute_select(
            f"select * from {table_name_src} s inner join {table_name_org} o on (s.slip_mgt_num = o.slip_mgt_num)")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # srcテーブル結果のレコード件数 (insert 4row + update 2row + insert 2row = 6row)
        result_src_count = self.db.execute_select(f"select count(*) from {table_name_src} ")
        assert result_src_count[0]['count(*)'] == 6

        # teardown

    def test_load_03_org_cnv_mst_ok(self, mapper):
        table_name_org = mapper.get_org_table(mapper.CONDKEY_ORG_CNV_MST)
        table_name_src = mapper.get_src_table(mapper.CONDKEY_ORG_CNV_MST)

        # setup
        self.batch_context.is_vjsk_stock_import_day = True
        self.db.execute(f"truncate table {table_name_src}")

        # assertion1 (insert 4row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_ORG_CNV_MST,
            "src_file_path": path.join(self.test_file_path_load_individual, "org_cnv_mst_202304280000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果を取得
        result_src = self.db.execute_select(f"select * from {table_name_src}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # assertion2 (update 2row +insert 2row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_ORG_CNV_MST,
            "src_file_path": path.join(self.test_file_path_load_individual, "org_cnv_mst_202304290000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果(orgテーブル結果のPK値で一致するもの)を取得
        result_src = self.db.execute_select(
            f"select * from {table_name_src} s inner join {table_name_org} o on (s.whs_cd = o.whs_cd and s.whs_sub_cd = o.whs_sub_cd and s.org_cd = o.org_cd and s.sub_num = o.sub_num)")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # srcテーブル結果のレコード件数 (insert 4row + update 2row + insert 2row = 6row)
        result_src_count = self.db.execute_select(f"select count(*) from {table_name_src} ")
        assert result_src_count[0]['count(*)'] == 6

        # teardown

    def test_load_04_vop_hco_merge_ok(self, mapper):
        table_name_org = mapper.get_org_table(mapper.CONDKEY_VOP_HCO_MERGE)
        table_name_src = mapper.get_src_table(mapper.CONDKEY_VOP_HCO_MERGE)

        # setup
        self.batch_context.is_vjsk_stock_import_day = True
        self.db.execute(f"truncate table {table_name_src}")

        # assertion1 (insert 4row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_VOP_HCO_MERGE,
            "src_file_path": path.join(self.test_file_path_load_individual, "vop_hco_merge_202304280000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果を取得
        result_src = self.db.execute_select(f"select * from {table_name_src}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # assertion2 (update 2row +insert 2row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_VOP_HCO_MERGE,
            "src_file_path": path.join(self.test_file_path_load_individual, "vop_hco_merge_202304290000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果(orgテーブル結果のPK値で一致するもの)を取得
        result_src = self.db.execute_select(
            f"select * from {table_name_src} s inner join {table_name_org} o on (s.v_inst_cd = o.v_inst_cd and s.apply_dt = o.apply_dt)")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # srcテーブル結果のレコード件数 (insert 4row + update 2row + insert 2row = 6row)
        result_src_count = self.db.execute_select(f"select count(*) from {table_name_src} ")
        assert result_src_count[0]['count(*)'] == 6

        # teardown

    def test_load_05_whs_mst_ok(self, mapper):
        table_name_org = mapper.get_org_table(mapper.CONDKEY_WHS_MST)
        table_name_src = mapper.get_src_table(mapper.CONDKEY_WHS_MST)

        # setup
        self.batch_context.is_vjsk_stock_import_day = True
        self.db.execute(f"truncate table {table_name_src}")

        # assertion1 (insert 4row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_WHS_MST,
            "src_file_path": path.join(self.test_file_path_load_individual, "whs_mst_202304280000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果を取得
        result_src = self.db.execute_select(f"select * from {table_name_src}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # assertion2 (update 2row +insert 2row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_WHS_MST,
            "src_file_path": path.join(self.test_file_path_load_individual, "whs_mst_202304290000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果(orgテーブル結果のPK値で一致するもの)を取得
        result_src = self.db.execute_select(
            f"select * from {table_name_src} s inner join {table_name_org} o on (s.v_whs_cd = o.v_whs_cd and s.sub_num = o.sub_num)")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # srcテーブル結果のレコード件数 (insert 4row + update 2row + insert 2row = 6row)
        result_src_count = self.db.execute_select(f"select count(*) from {table_name_src} ")
        assert result_src_count[0]['count(*)'] == 6

        # teardown

    def test_load_06_hld_mst_ok(self, mapper):
        table_name_org = mapper.get_org_table(mapper.CONDKEY_HLD_MST)
        table_name_src = mapper.get_src_table(mapper.CONDKEY_HLD_MST)

        # setup
        self.batch_context.is_vjsk_stock_import_day = True
        self.db.execute(f"truncate table {table_name_src}")

        # assertion1 (insert 4row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_HLD_MST,
            "src_file_path": path.join(self.test_file_path_load_individual, "hld_mst_202304280000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果を取得
        result_src = self.db.execute_select(f"select * from {table_name_src}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # assertion2 (update 2row +insert 2row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_HLD_MST,
            "src_file_path": path.join(self.test_file_path_load_individual, "hld_mst_202304290000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果(orgテーブル結果のPK値で一致するもの)を取得
        result_src = self.db.execute_select(
            f"select * from {table_name_src} s inner join {table_name_org} o on (s.v_hld_cd = o.v_hld_cd and s.sub_num = o.sub_num)")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # srcテーブル結果のレコード件数 (insert 4row + update 2row + insert 2row = 6row)
        result_src_count = self.db.execute_select(f"select count(*) from {table_name_src} ")
        assert result_src_count[0]['count(*)'] == 6

        # teardown

    def test_load_07_fcl_mst_ok(self, mapper):
        table_name_org = mapper.get_org_table(mapper.CONDKEY_FCL_MST)
        table_name_src = mapper.get_src_table(mapper.CONDKEY_FCL_MST)

        # setup
        self.batch_context.is_vjsk_stock_import_day = True
        self.db.execute(f"truncate table {table_name_src}")

        # assertion1 (insert 4row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_FCL_MST,
            "src_file_path": path.join(self.test_file_path_load_individual, "fcl_mst_202304280000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        force_cast_to_str_columns = ['closed_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns, force_cast_to_str_columns)
        # srcテーブル結果を取得
        result_src = self.db.execute_select(f"select * from {table_name_src}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        force_cast_to_str_columns = ['closed_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns, force_cast_to_str_columns)

        # assertion2 (update 2row +insert 2row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_FCL_MST,
            "src_file_path": path.join(self.test_file_path_load_individual, "fcl_mst_202304290000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        force_cast_to_str_columns = ['closed_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns, force_cast_to_str_columns)
        # srcテーブル結果(orgテーブル結果のPK値で一致するもの)を取得
        result_src = self.db.execute_select(
            f"select * from {table_name_src} s inner join {table_name_org} o on (s.v_inst_cd = o.v_inst_cd and s.sub_num = o.sub_num)")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        force_cast_to_str_columns = ['closed_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns, force_cast_to_str_columns)

        # srcテーブル結果のレコード件数 (insert 4row + update 2row + insert 2row = 6row)
        result_src_count = self.db.execute_select(f"select count(*) from {table_name_src} ")
        assert result_src_count[0]['count(*)'] == 6

        # teardown

    def test_load_08_mkr_org_horizon_ok(self, mapper):
        table_name_org = mapper.get_org_table(mapper.CONDKEY_MKR_ORG_HORIZON)
        table_name_src = mapper.get_src_table(mapper.CONDKEY_MKR_ORG_HORIZON)

        # setup
        self.batch_context.is_vjsk_stock_import_day = True
        self.db.execute(f"truncate table {table_name_src}")

        # assertion1 (insert 4row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_MKR_ORG_HORIZON,
            "src_file_path": path.join(self.test_file_path_load_individual, "mkr_org_horizon_202304280000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果を取得
        result_src = self.db.execute_select(f"select * from {table_name_src}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # assertion2 (update 0row +insert 4row) ※PK項目がないテーブルなのですべてinsertになる

        assetion1_done_dt = self.db.execute_select("select SYSDATE()")[0]["SYSDATE()"]

        # assertion2でinsertされたレコードをdwh_upd_dtで判断するため、assertion1からの実行間隔を明確に空けるためにスリープを挟む
        time.sleep(3)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_MKR_ORG_HORIZON,
            "src_file_path": path.join(self.test_file_path_load_individual, "mkr_org_horizon_202304290000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果(orgテーブル結果のPK値で一致するもの)を取得
        result_src = self.db.execute_select(
            f"select * from {table_name_src} where dwh_upd_dt > :dt_value", {"dt_value": assetion1_done_dt})
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # srcテーブル結果のレコード件数 (insert 4row + update 0row + insert 4row = 8row) ※PK項目がないテーブルなのですべてinsertになる
        result_src_count = self.db.execute_select(f"select count(*) from {table_name_src} ")
        assert result_src_count[0]['count(*)'] == 8

        # teardown

    def test_load_09_tran_kbn_mst_ok(self, mapper):
        table_name_org = mapper.get_org_table(mapper.CONDKEY_TRAN_KBN_MST)
        table_name_src = mapper.get_src_table(mapper.CONDKEY_TRAN_KBN_MST)

        # setup
        self.batch_context.is_vjsk_stock_import_day = True
        self.db.execute(f"truncate table {table_name_src}")

        # assertion1 (insert 4row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_TRAN_KBN_MST,
            "src_file_path": path.join(self.test_file_path_load_individual, "tran_kbn_mst_202304280000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果を取得
        result_src = self.db.execute_select(f"select * from {table_name_src}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # assertion2 (update 2row +insert 2row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_TRAN_KBN_MST,
            "src_file_path": path.join(self.test_file_path_load_individual, "tran_kbn_mst_202304290000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果(orgテーブル結果のPK値で一致するもの)を取得
        result_src = self.db.execute_select(
            f"select * from {table_name_src} s inner join {table_name_org} o on (s.v_tran_cd = o.v_tran_cd and s.sub_num = o.sub_num)")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # srcテーブル結果のレコード件数 (insert 4row + update 2row + insert 2row = 6row)
        result_src_count = self.db.execute_select(f"select count(*) from {table_name_src} ")
        assert result_src_count[0]['count(*)'] == 6

        # teardown

    def test_load_10_phm_prd_mst_ok(self, mapper):
        table_name_org = mapper.get_org_table(mapper.CONDKEY_PHM_PRD_MST)
        table_name_src = mapper.get_src_table(mapper.CONDKEY_PHM_PRD_MST)

        # setup
        self.batch_context.is_vjsk_stock_import_day = True
        self.db.execute(f"truncate table {table_name_src}")

        # assertion1 (insert 4row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_PHM_PRD_MST,
            "src_file_path": path.join(self.test_file_path_load_individual, "phm_prd_mst_202304280000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果を取得
        result_src = self.db.execute_select(f"select * from {table_name_src}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # assertion2 (update 2row +insert 2row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_PHM_PRD_MST,
            "src_file_path": path.join(self.test_file_path_load_individual, "phm_prd_mst_202304290000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果(orgテーブル結果のPK値で一致するもの)を取得
        result_src = self.db.execute_select(
            f"select * from {table_name_src} s inner join {table_name_org} o on (s.prd_cd = o.prd_cd and s.sub_num = o.sub_num)")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # srcテーブル結果のレコード件数 (insert 4row + update 2row + insert 2row = 6row)
        result_src_count = self.db.execute_select(f"select count(*) from {table_name_src} ")
        assert result_src_count[0]['count(*)'] == 6

        # teardown

    def test_load_11_phm_price_mst_ok(self, mapper):
        table_name_org = mapper.get_org_table(mapper.CONDKEY_PHM_PRICE_MST)
        table_name_src = mapper.get_src_table(mapper.CONDKEY_PHM_PRICE_MST)

        # setup
        self.batch_context.is_vjsk_stock_import_day = True
        self.db.execute(f"truncate table {table_name_src}")

        # assertion1 (insert 4row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_PHM_PRICE_MST,
            "src_file_path": path.join(self.test_file_path_load_individual, "phm_price_mst_202304280000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果を取得
        result_src = self.db.execute_select(f"select * from {table_name_src}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # assertion2 (update 2row +insert 2row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_PHM_PRICE_MST,
            "src_file_path": path.join(self.test_file_path_load_individual, "phm_price_mst_202304290000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果(orgテーブル結果のPK値で一致するもの)を取得
        result_src = self.db.execute_select(
            f"select * from {table_name_src} s inner join {table_name_org} o on (s.phm_prd_cd = o.phm_prd_cd and s.phm_price_kind = o.phm_price_kind and s.sub_num = o.sub_num)")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # srcテーブル結果のレコード件数 (insert 4row + update 2row + insert 2row = 6row)
        result_src_count = self.db.execute_select(f"select count(*) from {table_name_src} ")
        assert result_src_count[0]['count(*)'] == 6

        # teardown

    def test_load_12_whs_customer_mst_ok(self, mapper):
        table_name_org = mapper.get_org_table(mapper.CONDKEY_WHS_CUSTOMER_MST)
        table_name_src = mapper.get_src_table(mapper.CONDKEY_WHS_CUSTOMER_MST)

        # setup
        self.batch_context.is_vjsk_stock_import_day = True
        self.db.execute(f"truncate table {table_name_src}")

        # assertion1 (insert 4row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_WHS_CUSTOMER_MST,
            "src_file_path": path.join(self.test_file_path_load_individual, "whs_customer_mst_202304280000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果を取得
        result_src = self.db.execute_select(f"select * from {table_name_src}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # assertion2 (update 2row +insert 2row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_WHS_CUSTOMER_MST,
            "src_file_path": path.join(self.test_file_path_load_individual, "whs_customer_mst_202304290000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果(orgテーブル結果のPK値で一致するもの)を取得
        result_src = self.db.execute_select(
            f"select * from {table_name_src} s inner join {table_name_org} o on (s.whs_cd = o.whs_cd and s.whs_sub_cd = o.whs_sub_cd and s.customer_cd = o.customer_cd and s.sub_num = o.sub_num)")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # srcテーブル結果のレコード件数 (insert 4row + update 2row + insert 2row = 6row)
        result_src_count = self.db.execute_select(f"select count(*) from {table_name_src} ")
        assert result_src_count[0]['count(*)'] == 6

        # teardown

    def test_load_13_mdb_conv_mst_ok(self, mapper):
        table_name_org = mapper.get_org_table(mapper.CONDKEY_MDB_CONV_MST)
        table_name_src = mapper.get_src_table(mapper.CONDKEY_MDB_CONV_MST)

        # setup
        self.batch_context.is_vjsk_stock_import_day = True
        self.db.execute(f"truncate table {table_name_src}")

        # assertion1 (insert 4row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_MDB_CONV_MST,
            "src_file_path": path.join(self.test_file_path_load_individual, "mdb_conv_mst_202304280000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果を取得
        result_src = self.db.execute_select(f"select * from {table_name_src}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # assertion2 (update 2row +insert 2row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_MDB_CONV_MST,
            "src_file_path": path.join(self.test_file_path_load_individual, "mdb_conv_mst_202304290000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果(orgテーブル結果のPK値で一致するもの)を取得
        result_src = self.db.execute_select(
            f"select * from {table_name_src} s inner join {table_name_org} o on (s.hco_vid_v = o.hco_vid_v and s.sub_num = o.sub_num)")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # srcテーブル結果のレコード件数 (insert 4row + update 2row + insert 2row = 6row)
        result_src_count = self.db.execute_select(f"select count(*) from {table_name_src} ")
        assert result_src_count[0]['count(*)'] == 6

        # teardown

    def test_load_14_bio_slip_data_ok(self, mapper):
        table_name_org = mapper.get_org_table(mapper.CONDKEY_BIO_SLIP_DATA)
        table_name_src = mapper.get_src_table(mapper.CONDKEY_BIO_SLIP_DATA)

        # setup
        self.batch_context.is_vjsk_stock_import_day = True
        self.db.execute(f"truncate table {table_name_src}")

        # assertion1 (insert 4row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_BIO_SLIP_DATA,
            "src_file_path": path.join(self.test_file_path_load_individual, "bio_slip_data_202304280000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果を取得
        result_src = self.db.execute_select(f"select * from {table_name_src}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # assertion2 (update 2row +insert 2row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_BIO_SLIP_DATA,
            "src_file_path": path.join(self.test_file_path_load_individual, "bio_slip_data_202304290000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果(orgテーブル結果のPK値で一致するもの)を取得
        result_src = self.db.execute_select(
            f"select * from {table_name_src} s inner join {table_name_org} o on (s.slip_mgt_num = o.slip_mgt_num)")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # srcテーブル結果のレコード件数 (insert 4row + update 2row + insert 2row = 6row)
        result_src_count = self.db.execute_select(f"select count(*) from {table_name_src} ")
        assert result_src_count[0]['count(*)'] == 6

        # teardown

    def test_load_15_lot_num_mst_ok(self, mapper):
        table_name_org = mapper.get_org_table(mapper.CONDKEY_LOT_NUM_MST)
        table_name_src = mapper.get_src_table(mapper.CONDKEY_LOT_NUM_MST)

        # setup
        self.batch_context.is_vjsk_stock_import_day = True
        self.db.execute(f"truncate table {table_name_src}")

        # assertion1 (insert 4row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_LOT_NUM_MST,
            "src_file_path": path.join(self.test_file_path_load_individual, "lot_num_mst_202304280000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果を取得
        result_src = self.db.execute_select(f"select * from {table_name_src}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # assertion2 (update 2row +insert 2row)

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_LOT_NUM_MST,
            "src_file_path": path.join(self.test_file_path_load_individual, "lot_num_mst_202304290000.tsv")
        }
        VjskDataLoadManager.load(target_dict)

        # 期待値データファイル読み込み
        assert_list = create_vjsk_assertion_list(target_dict["src_file_path"])
        # orgテーブル結果を取得
        result_org = self.db.execute_select(f"select * from {table_name_org}")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # orgテーブル結果が期待値通りかを突合
        assert_table_results(result_org, assert_list, ignore_columns)
        # srcテーブル結果(orgテーブル結果のPK値で一致するもの)を取得
        result_src = self.db.execute_select(
            f"select * from {table_name_src} s inner join {table_name_org} o on (s.ser_num = o.ser_num and s.lot_num = o.lot_num)")
        # 突合から除外する項目
        ignore_columns = ['dwh_upd_dt']
        # srcテーブル結果が期待値通りかを突合
        assert_table_results(result_src, assert_list, ignore_columns)

        # srcテーブル結果のレコード件数 (insert 4row + update 2row + insert 2row = 6row)
        result_src_count = self.db.execute_select(f"select count(*) from {table_name_src} ")
        assert result_src_count[0]['count(*)'] == 6

        # teardown

    def test_unzip_to_error(self, s3_client, bucket_name, receive_folder, mapper):
        """
        観点
            異常系 : gzファイルが解凍できない
        期待値
            例外が発生する
        """
        # setup - 卸在庫データ取込対象日
        self.batch_context.is_vjsk_stock_import_day = True

        # setup - S3受領バケットの内容をすべて削除する
        vjsk_recv_bucket = VjskReceiveBucket()
        s3_files = vjsk_recv_bucket.get_s3_file_list()
        for file_obj in s3_files:
            s3_client.delete_object(Bucket=bucket_name, Key=file_obj.get("filename"))

        # setup - テスト用受領ファイルをS3受領バケットにupload
        # ※.gzだが、7zipで圧縮してあるので、解凍に失敗するのが期待値
        test_files = [
            "stock_slip_data_202304270000.gz",
            "slip_data_202304270000.gz",
            "org_cnv_mst_202304270000.gz",
            "vop_hco_merge_202304270000.gz",
            "whs_mst_202304270000.gz",
            "hld_mst_202304270000.gz",
            "fcl_mst_202304270000.gz",
            "mkr_org_horizon_202304270000.gz",
            "tran_kbn_mst_202304270000.gz",
            "phm_prd_mst_202304270000.gz",
            "phm_price_mst_202304270000.gz",
            "whs_customer_mst_202304270000.gz",
            "mdb_conv_mst_202304270000.gz",
            "bio_slip_data_202304270000.gz",
            "lot_num_mst_202304270000.gz"
        ]
        for test_file in test_files:
            file_name = path.join(self.test_file_path_unzip_error, test_file)
            key = f"{receive_folder}/{test_file}"
            s3_client.upload_file(file_name, bucket_name, key)

        # assertion
        received_s3_files = _check_received_files()
        with pytest.raises(Exception) as e:
            _import_file_to_db(received_s3_files)

        # 検証
        assert str(e.value) == "file could not be opened successfully"

        # teardown
        for test_file in test_files:
            key = f"{receive_folder}/{test_file}"
            s3_client.delete_object(Bucket=bucket_name, Key=key)

    def test_load_data_error(self, mapper):
        """
        観点
            異常系 : 日付型矛盾のデータ ※製品価格マスタファイルで確認
        期待値
            例外が発生する
        """

        # setup
        self.batch_context.is_vjsk_stock_import_day = True

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_PHM_PRICE_MST,
            "src_file_path": path.join(self.test_file_path_load_individual, "phm_price_mst_dataerror.tsv")
        }

        with pytest.raises(BatchOperationException) as e:
            VjskDataLoadManager.load(target_dict)

        # 検証
        assert str(e.value).startswith("SQL Error:") > 0

        # teardown

    def test_load_format_error(self, mapper):
        """
        観点
            異常系 : tsvファイルが途中で欠落している
        期待値
            例外が発生する
        """

        # setup
        self.batch_context.is_vjsk_stock_import_day = True

        # 処理実行
        target_dict = {
            "condkey": mapper.CONDKEY_PHM_PRICE_MST,
            "src_file_path": path.join(self.test_file_path_load_individual, "phm_price_mst_formaterror.tsv")
        }

        with pytest.raises(BatchOperationException) as e:
            VjskDataLoadManager.load(target_dict)

        # 検証
        assert str(e.value).startswith("LOAD文実行時にWARNINGが発生しました。") > 0

        # teardown

    def test_s3backup_ok(self, s3_client, bucket_name, receive_folder, mapper):
        """
        観点
            正常系 : S3受領ファイルのバックアップフォルダ移動が完了する
        期待値
            例外が発生する
        """
        # setup - 卸在庫データ取込対象日
        self.batch_context.is_vjsk_stock_import_day = True

        # setup - S3受領バケットの内容をすべて削除する
        vjsk_recv_bucket = VjskReceiveBucket()
        s3_files = vjsk_recv_bucket.get_s3_file_list()
        for file_obj in s3_files:
            s3_client.delete_object(Bucket=bucket_name, Key=file_obj.get("filename"))

        # setup - テスト用受領ファイルをS3受領バケットにupload
        # ※.gzだが、7zipで圧縮してあるので、解凍に失敗するのが期待値
        test_files = [
            "stock_slip_data_202304270000.gz",
            "slip_data_202304270000.gz",
            "org_cnv_mst_202304270000.gz",
            "vop_hco_merge_202304270000.gz",
            "whs_mst_202304270000.gz",
            "hld_mst_202304270000.gz",
            "fcl_mst_202304270000.gz",
            "mkr_org_horizon_202304270000.gz",
            "tran_kbn_mst_202304270000.gz",
            "phm_prd_mst_202304270000.gz",
            "phm_price_mst_202304270000.gz",
            "whs_customer_mst_202304270000.gz",
            "mdb_conv_mst_202304270000.gz",
            "bio_slip_data_202304270000.gz",
            "lot_num_mst_202304270000.gz"
        ]
        for test_file in test_files:
            file_name = path.join(self.test_file_path_import_all, test_file)
            key = f"{receive_folder}/{test_file}"
            s3_client.upload_file(file_name, bucket_name, key)

        # assertion
        received_s3_files = _check_received_files()
        vjsk_recv_bucket.backup_dat_file(received_s3_files, "test")

        # 検証

        # teardown
        for test_file in test_files:
            key = f"{receive_folder}/{test_file}"
            s3_client.delete_object(Bucket=bucket_name, Key=key)

    def test_s3backup_to_error(self, s3_client, bucket_name, receive_folder, mapper):
        """
        観点
            異常系 : S3受領ファイルのバックアップフォルダ移動ができない
        期待値
            例外が発生する
        """
        # setup - 卸在庫データ取込対象日
        self.batch_context.is_vjsk_stock_import_day = True

        # setup - S3受領バケットの内容をすべて削除する
        vjsk_recv_bucket = VjskReceiveBucket()
        s3_files = vjsk_recv_bucket.get_s3_file_list()
        for file_obj in s3_files:
            s3_client.delete_object(Bucket=bucket_name, Key=file_obj.get("filename"))

        # setup

        # assertion
        with pytest.raises(Exception) as e:
            # 有りもしないファイルをバックアップフォルダにコピーさせてコケさせる
            received_s3_files = []
            received_s3_files.append({"filename": "dummy.dummy"})
            vjsk_recv_bucket.backup_dat_file(received_s3_files, "test")

        # 検証
        assert str(e.value) == "An error occurred (404) when calling the HeadObject operation: Not Found"

        # teardown
