import pymysql

from common.fileUtls import FileUtils


def get_db():
    # 打开数据库连接
    db = pymysql.connect(host="10.17.65.108", user="root", password="Cmic.2023", database="spd3_herp_test2",
                                     charset="utf8")
    return db


def get_sql_conn():
    """
    获取数据库连接
    """
    db = get_db()
    cursor = db.cursor()
    return db, cursor


def get_index_dict(cursor):
    """
    获取数据库对应表中的字段名
    """
    index_dict = dict()
    index = 0
    for desc in cursor.description:
        index_dict[desc[0]] = index
        index = index + 1
    return index_dict


def get_dict_data_sql(cursor, sql):
    """
    运行sql语句，获取结果，并根据表中字段名，转化成dict格式（默认是tuple格式）
    """
    cursor.execute(sql)
    data = cursor.fetchall()
    index_dict = get_index_dict(cursor)
    res = []
    for datai in data:
        resi = dict()
        for indexi in index_dict:
            resi[indexi] = datai[index_dict[indexi]]
        res.append(resi)
    return res


def main2(hos_goods_id):
    db, cursor = get_sql_conn()
    sql = """SELECT hos_goods_code,
                    hos_id,
                    prov_id,
                    prov_hos_goods_id,
                    srv_id,
                    mdm_goods_code,
                    mdm_goods_spec_code,
                    spd_goods_code,
                    goods_name,
                    goods_general_name,
                    goods_mfrs_id,
                    goods_mfrs_name,
                    goods_reg_cert,
                    goods_agent_mfrs_id,
                    brand,
                    short_pinyin,
                    kind_sixtyeight_code,
                    unit,
                    goods_spec,
                    goods_package,
                    bar_code_mng,
                    unique_code_strategy,
                    pur_mode,
                    sub_pur_mode,
                    erp_code,
                    herp_code,
                    hrp,
                    price,
                    abroad_flag,
                    made,
                    property,
                    mgr_level,
                    goods_desc,
                    charge_flag,
                    focus_control_type,
                    temp_flag,
                    official_status,
                    focus_control,
                    storage_conditions,
                    transport_conditions,
                    into_cost_flag,
                    rfid_flag,
                    purchase_flag,
                    tb_status,
                    version,
                    create_user,
                    create_time,
                    last_modified_user,
                    sterilize_flag,
                    multi_charge_flag,
                    use_frequency,
                    charge_unit FROM  	mcms_goods_info WHERE 	id ='%s';""" % hos_goods_id
    res_dict = get_dict_data_sql(cursor, sql)
    print('res_dict',res_dict)
    return res_dict
def check_mcms_dept_buy_car_sql(sql):
    db, cursor = get_sql_conn()
    res_dict = get_dict_data_sql(cursor, sql)
    print('res_dict', res_dict)
    cursor.close()
    return res_dict
def check_mcms_dept_buy_sql(sql):
    db, cursor = get_sql_conn()
    res_dict = get_dict_data_sql(cursor, sql)
    print('res_dict', res_dict)
    cursor.close()
    return res_dict
def check_mcms_pur_plan_sql(sql):
    db, cursor = get_sql_conn()
    res_dict = get_dict_data_sql(cursor, sql)
    print('res_dict', res_dict)
    cursor.close()
    return res_dict
def get_create_time(sql):
    db, cursor = get_sql_conn()
    cursor.execute(sql)
    results = cursor.fetchone()
    str1 = str(results)
    new_str1 = str1.replace(',)', '')
    new_str1 = new_str1.replace('(date', 'date')
    # new_str1 = new_str1.replace(',', '')
    # print(new_str1)
    cursor.close()
    return new_str1
def get_expdt_date(sql):
    db, cursor = get_sql_conn()
    cursor.execute(sql)
    results = cursor.fetchone()
    str1 = str(results)
    new_str1 = str1.replace(',)', '')
    new_str1 = new_str1.replace('(date', 'date')
    # new_str1 = new_str1.replace(',', '')
    # print(new_str1)
    cursor.close()
    return new_str1
def get_id(sql):
    db, cursor = get_sql_conn()
    cursor.execute(sql)
    results = cursor.fetchone()
    str1 = str(results)
    new_str1 = str1.replace(',)', '')
    new_str1 = new_str1.replace('(', '')
    new_str1 = new_str1.replace(',', '')
    new_str1 = new_str1.replace("'", '')
    # print(new_str1)
    cursor.close()
    return new_str1
def get_dept_buy_id(sql):
    db, cursor = get_sql_conn()
    cursor.execute(sql)
    results = cursor.fetchall()
    cursor.close()
    return results
def process_tuple(tuple1):
    str1 = str(tuple1)
    new_str1 = str1.replace(',)', '')
    new_str1 = new_str1.replace('(', '')
    new_str1 = new_str1.replace(',', '')
    new_str1 = new_str1.replace("'", '')
    return new_str1

def get_mdm_goods_code(sql):
    db, cursor = get_sql_conn()
    cursor.execute(sql)
    results = cursor.fetchall()
    cursor.close()
    return results
def check_in_check_mcms_purchase(sql):
    db, cursor = get_sql_conn()
    cursor.execute(sql)
    results = cursor.fetchone()
    str1 = str(results)
    new_str1 = str1.replace(',)', '')
    new_str1 = new_str1.replace('(', '')
    new_str1 = new_str1.replace(',', '')
    new_str1 = new_str1.replace("'", '')
    # print(new_str1)
    cursor.close()
    return new_str1
# hos_goods_id = 'h034700004220'
# main2(hos_goods_id)