# -*-coding:utf-8 -*- #
# ---------------------------------------------------------------------------
# ProjectName:   test61
# FileName:      read_excel.py
# Author:       lao_zhao
# Datetime:     2024/7/12 14:15
# Description:
# 
# ---------------------------------------------------------------------------
import openpyxl

from APIAutoTest_v3_1 import log_decorator
from APIAutoTest_v3_1.common.read_basic_ini import ReadBasicIni
from APIAutoTest_v3_1.common.read_ini import ReadIni
from APIAutoTest_v3_1.common.read_json import read_json
from APIAutoTest_v3_1.data_config.settings import *


class ReadExcel:
    @log_decorator
    def __init__(self, username):
        """获取数据配置层中，除了ini文件以外所有文件的路径"""
        self.ini = ReadIni(username)
        case_data_path = self.ini.get_file_path(CASE_FILE)
        expect_data_path = self.ini.get_file_path(EXPECT_FILE)
        sql_data_path = self.ini.get_file_path(SQL_FILE)

        excel_path = self.ini.get_file_path(EXCEL_FILE)
        table_name = self.ini.get_table_name(TABLE_NAME)

        self.case_data_dict = read_json(case_data_path)
        self.expect_data_dict = read_json(expect_data_path)
        self.sql_data_dict = read_json(sql_data_path)

        try:
            wb = openpyxl.load_workbook(excel_path)
            self.ws = wb[table_name]
        except Exception as e:
            # log.error(f"获取excel工作簿或工作表时报错，错误为:{e}")
            raise e

    @log_decorator
    def __get_cell_value(self, column: str, row: int) -> str:
        """获取指定单元格数据"""
        try:
            value = self.ws[column+str(row)].value
        except Exception as e:
            raise e
        else:
            if value is None:
                return None
            elif value.strip():
                return value.strip()

    @log_decorator
    def module_name(self, row):
        """根据行，获取模块名称"""
        return self.__get_cell_value(MODULE, row)

    @log_decorator
    def api_name(self, row):
        """根据行，获取接口名称"""
        return self.__get_cell_value(API, row)

    @log_decorator
    def req_method(self, row):
        """根据行，获取请求方法"""
        return self.__get_cell_value(METHOD, row)

    @log_decorator
    def req_url(self, row):
        """根据行，获取请求的url"""
        host = ReadBasicIni().get_url(HOST)
        path = self.__get_cell_value(PATH, row)
        if path:
            return host + path

    @log_decorator
    def case_mime(self, row):
        """根据行，获取用例的媒体类型"""
        value = self.__get_cell_value(MIME, row)
        if value:
            return value.lower()

    @log_decorator
    def case_data(self, row):
        """根据行，获取用例数据"""
        case_data_key = self.__get_cell_value(CASE, row)
        if case_data_key:
            module_name = self.module_name(row)
            api_name = self.api_name(row)
            return self.case_data_dict[module_name][api_name][case_data_key]

    @log_decorator
    def expect_data(self, row):
        """根据行，获取期望数据"""
        expect_data_key = self.__get_cell_value(EXPECT, row)
        if expect_data_key:
            module_name = self.module_name(row)
            api_name = self.api_name(row)
            return self.expect_data_dict[module_name][api_name][expect_data_key]

    @log_decorator
    def sql_type(self, row):
        """根据行，获取sql语句的类型"""
        value = self.__get_cell_value(SQLTYPE, row)
        if value:
            return value.lower()

    @log_decorator
    def sql_data(self, row):
        """根据行，获取期望数据"""
        sql_data_key = self.__get_cell_value(SQLDATA, row)
        if sql_data_key:
            module_name = self.module_name(row)
            api_name = self.api_name(row)
            return self.sql_data_dict[module_name][api_name][sql_data_key]

    @log_decorator
    def update_key(self, row):
        """根据行，获取更新的key"""
        return self.__get_cell_value(UPDATEKEY, row)

    @log_decorator
    def case_title(self, row):
        """根据行，获取用例的标题"""
        return self.__get_cell_value(TITLE, row)

    @log_decorator
    def case_level(self, row):
        """根据行，获取用例的标题"""
        return self.__get_cell_value(LEVEL, row)

    @log_decorator
    def get_data(self):
        """将测试使用的数据存放在一个二维列表中"""
        data_list = []
        for row in range(2, self.ws.max_row+1):
            module_name = self.module_name(row)
            api_name = self.api_name(row)
            case_title = self.case_title(row)
            level = self.case_level(row)
            case_method = self.req_method(row)
            case_url = self.req_url(row)
            case_mime = self.case_mime(row)
            case_data = self.case_data(row)
            expect_data = self.expect_data(row)
            sql_type = self.sql_type(row)
            sql_data = self.sql_data(row)
            update_key = self.update_key(row)
            if case_method is not None and case_url is not None:
                data_list.append([module_name, api_name, level, case_title, case_method, case_url, case_mime, case_data, expect_data, sql_type, sql_data, update_key])
        else:
            return data_list


if __name__ == '__main__':
    excel = ReadExcel("lao_zhang")
    print(excel.get_data())