# -*-coding:utf-8 -*- #
# ---------------------------------------------------------------------------
# ProjectName:   test64
# FileName:      read_excel.py
# Author:       lao_zhao
# Datetime:     2024/12/26 14:05
# Description:
# 
# ---------------------------------------------------------------------------
import openpyxl
from APIAutoTest_v3 import log_decorator
from APIAutoTest_v3.common.read_basic_ini import ReadBasicIni

from APIAutoTest_v3.common.read_json import read_json
from APIAutoTest_v3.common.read_user_ini import ReadUserIni


class ReadExcel:

    @log_decorator
    def __init__(self, username):
        """获取excel和所有json文件的路径,及excel工作表的名称,获取工作簿和工作表,获取所有json文件数据对应的python对象"""
        # 创建ReadBasicIni类对象,主要是用来获取被测系统的域名
        self.basic_ini = ReadBasicIni()
        # 创建ReadUserIni类对象-获取的是用户的数据
        self.ini = ReadUserIni(username)
        case_data_path = self.ini.get_file_path("case")
        expect_data_path = self.ini.get_file_path("expect")
        sql_data_path = self.ini.get_file_path("sql")
        excel_path = self.ini.get_file_path("excel")
        table_name = self.ini.get_table_name("name")

        wb = openpyxl.load_workbook(excel_path)
        self.ws = wb[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)

    @log_decorator
    def __get_cell_value(self, column, row):
        """获取指定单元格数据"""
        value = self.ws[column+str(row)].value
        if isinstance(value, str) and value.strip():
            return value.strip()

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

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

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

    @log_decorator
    def case_level(self, row):
        """根据行,获取用例的等级"""
        return self.__get_cell_value("e", row)

    @log_decorator
    def case_url(self, row):
        """根据行,获取用例的url"""
        path = self.__get_cell_value("f", row)
        if path:
            return self.basic_ini.get_host("bpm") + path

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

    @log_decorator
    def case_req(self, row):
        """根据行,获取用例的请求方法"""
        return self.__get_cell_value("h", row)

    @log_decorator
    def case_data(self, row):
        """根据行,获取用例数据"""
        case_data_key = self.__get_cell_value("i", 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("j", 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_data(self, row):
        """根据行,获取sql数据"""
        sql_data_key = self.__get_cell_value("l", 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 sql_type(self, row):
        """根据行,获取sql语句的类型"""
        sql_statement = self.__get_cell_value("k", row)
        if sql_statement:
            return sql_statement.lower()

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

    @log_decorator
    def get_data(self):
        """获取所有的测试数据,存放在二维列表中"""
        list_data = []
        for row in range(2, self.ws.max_row+1):
            url = self.case_url(row)
            mime = self.case_mime(row)
            req = self.case_req(row)
            case = self.case_data(row)
            expect = self.expect_data(row)
            sql_type = self.sql_type(row)
            sql = self.sql_data(row)
            update = self.update_key(row)
            module = self.module_name(row)
            api = self.api_name(row)
            title = self.case_title(row)
            level = self.case_level(row)
            if url is not None and req is not None and expect is not None:
                list_data.append([module, api, title, level, url, mime, req, case, expect, sql_type, sql, update])
        else:
            return list_data


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