# -*-coding:utf-8 -*- #
# ---------------------------------------------------------------------------
# ProjectName:   study_python
# FileName:      read_excel.py
# Author:       xxxxxxx
# Datetime:     2024/9/7 10:00
# Description:
# 命名规范：文件名全小写+下划线，类名大驼峰，方法和变量小写+下划线连接，
# 常量大写，变量和常量用名词，方法用动词
# ---------------------------------------------------------------------------
import openpyxl

from ApiAutoTese_3 import log
from ApiAutoTese_3.common.read_json import read_json
from ApiAutoTese_3.common.read_project_ini import Read_Project_Ini

from ApiAutoTese_3.common.read_user_ini import Read_User_Ini
from ApiAutoTese_3.data_config.settings import *


class Read_excel:
    def __init__(self,username):
        ini = Read_User_Ini(username)
        self.ini = Read_Project_Ini()

        file_path = ini.get_file_path('excel')
        table_name = ini.get_table_name('table_name')

        case_path = ini.get_file_path('case')
        expct_path = ini.get_file_path('expect')
        sql_path = ini.get_file_path('sql')

        self.case_dict = read_json(case_path)
        self.expect_dict = read_json(expct_path)
        self.sql_dict = read_json(sql_path)

        try:
            wb = openpyxl.load_workbook(file_path)
            self.ws = wb[table_name]
        except Exception as e:
            log.error(f'加载excel工作簿和工作表时出错，excel路径为{file_path}，工作表为{table_name},错误为{e}')

    def __get_excel_data(self,column,row):
        try:
            cell_value = self.ws[column+str(row)].value
        except Exception as e:
            log.error(f'获取单元格{column}{row}数据出错，错误为：{e}')
            raise e
        else:
            if cell_value is None:
                return None
            elif cell_value.strip():
                return cell_value.strip()

    def get_module(self,row):
        return self.__get_excel_data(MODULE,row)

    def get_api(self,row):
        return self.__get_excel_data(API,row)

    def get_url(self,row):
        path = self.__get_excel_data(URL,row)
        if path:
            return self.ini.get_host(HOST_KEY) + path

    def get_method(self,row):
        return self.__get_excel_data(REQ_METHOD,row)

    def get_mime(self,row):
        mime = self.__get_excel_data(MIME,row)
        if mime:
            return mime.lower()

    def get_case_data(self,row):
        case_key = self.__get_excel_data(CASE_DATA, row)
        if case_key:
            module_name = self.get_module(row)
            api_name = self.get_api(row)
            return self.case_dict[module_name][api_name][case_key]

    def get_expect_data(self,row):
        expect_key = self.__get_excel_data(EXPECT_DATA, row)
        if expect_key:
            module_name = self.get_module(row)
            api_name = self.get_api(row)
            return self.expect_dict[module_name][api_name][expect_key]

    def get_sql_data(self,row):
        sql_key = self.__get_excel_data(SQL_DATA, row)
        if sql_key:
            module_name = self.get_module(row)
            api_name = self.get_api(row)
            return self.sql_dict[module_name][api_name][sql_key]

    def get_sql_type(self,row):
        value = self.__get_excel_data(SQL_TYPE,row)
        if value:
            return value

    def get_updata(self,row):
        updata_key = self.__get_excel_data(UPDATE_KEY,row)
        if updata_key:
            return updata_key

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

    def case_level(self, row):
        """根号行号，获取用例的等级"""
        return self.__get_excel_data(LEVEL, row)

    def get_data(self):
        list_data = []
        for i in range(2,self.ws.max_row+1):
            module = self.get_module(i)
            api = self.get_api(i)
            url = self.get_url(i)
            method = self.get_method(i)
            mime = self.get_mime(i)
            case_data = self.get_case_data(i)
            expect_data = self.get_expect_data(i)
            sql_typ = self.get_sql_type(i)
            sql_data = self.get_sql_data(i)
            updata_key = self.get_updata(i)
            case_title = self.case_title(i)
            case_level = self.case_level(i)
            if url is not None and method is not None and expect_data is not None:
                list_data.append([module,api,url,method,mime,case_data,expect_data,sql_typ,sql_data,updata_key,case_title,case_level])
        return list_data

if __name__ == '__main__':
    excel = Read_excel('zys')
    print(excel.get_data())