# -*- coding: utf-8 -*-
import os
import pymssql
import pandas as pd
from utils.apiDoc import *
from utils.desHelper import des_decrypt

CurrentFile = os.path.abspath(__file__)
CurrentPath = os.path.dirname(CurrentFile)
FatherPath = os.path.dirname(CurrentPath)
cf = configparser.ConfigParser()
# 出于安全性考虑，这些配置信息都存放到doc下的配置文件里。
cf.read(os.path.join(FatherPath, './config/db.ini'))
ms_sql_con_dict = dict(cf.items('mssql-config'))


class HisMssqlConnect(object):
    def __init__(self):
        self.host = ms_sql_con_dict['host']
        self.user = ms_sql_con_dict['user']
        self.pwd = des_decrypt(ms_sql_con_dict['pwd']).decode('utf-8')
        self.db = ms_sql_con_dict['db']
        self.charset = ms_sql_con_dict['charset']

    def query(self, tableName, sql_str, cols=None):
        try:
            connect = pymssql.connect(host=self.host,
                                      user=self.user,
                                      password=self.pwd,
                                      database=self.db,
                                      charset=self.charset,
                                      )
            app.logger.info('>>> 数据库连接成功')
            try:
                sql_str = sql_str.encode('UTF-8')
                with connect.cursor() as cursor:
                    sql_str_cols = F"SELECT COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME= " \
                                   F"'{tableName}'"
                    cursor.execute(sql_str_cols)
                    default_cols = [col[0] for col in cursor.fetchall()]
                    cursor.execute(sql_str)
                    res = cursor.fetchall()
                    # res = []
                    # for item in iter(cursor.fetchall()):
                    #     itemList = []
                    #     for i in range(len(item)):
                    #         if isinstance(item[i], str):
                    #             itemList.append(item[i].encode('latin1').decode('gbk'))
                    #         else:
                    #             itemList.append(item[i])
                    #     res.append(tuple(itemList))
                    # res = tuple(res)
                    df = pd.DataFrame(res, columns=default_cols if cols is None else cols)
            except Exception as e:
                df = pd.DataFrame()
                app.logger.info(F'>>> 查询数据失败，查询语句为：{sql_str}；\n错误信息为：{e}')
            finally:
                connect.close()
        except Exception as e:
            df = pd.DataFrame()
            app.logger.info(F'>>> 数据库连接失败，请检查配置信息是否准确。错误信息为：{format(e)}')
        return df