import pymysql

from air_case.public1.public1.public1 import *
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()
    db.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()
    db.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()
    db.close()
    return res_dict


def check_mcms_pur_sql(sql):
    db, cursor = get_sql_conn()
    res_dict = get_dict_data_sql(cursor, sql)
    print('res_dict', res_dict)
    cursor.close()
    db.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()
    db.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()
    db.close()
    return new_str1


def get_expdt_date1(sql):
    db, cursor = get_sql_conn()
    cursor.execute(sql)
    results = cursor.fetchone()
    cursor.close()
    db.close()
    return results


def get_account_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('(', '')
    new_str1 = new_str1.replace(',', '')
    new_str1 = new_str1.replace("'", '')
    # print(new_str1)
    cursor.close()
    db.close()
    return new_str1


def set_goods_coding_type(branch_id):  # 设置科室产品为赋码管理0为赋码管理1是非赋码
    db, cursor = get_sql_conn()
    sql = "UPDATE mcms_dept_goods_info SET coding_type = 0 WHERE branch_id=%s;"
    cursor.execute(sql, (branch_id))
    db.commit()
    cursor.close()
    db.close()


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()
    db.close()
    return new_str1


def get_DRId(branch_id, type):
    db, cursor = get_sql_conn()
    cursor.execute(
        "select id from  mcms_psi_dept where target_branch_id='%s' and bill_mode='%s' and stock_kind='CK_LYTK' order by   CREATE_TIME desc LIMIT 1;"%(branch_id,type))
    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()
    db.close()
    return new_str1
def get_DN_no(branch_id, type):
    DR_id=get_DRId(branch_id, type)
    db, cursor = get_sql_conn()
    cursor.execute(
        "select order_no from  mcms_psi_dept where id='%s';" % (
        DR_id))
    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()
    db.close()
    return  new_str1
def get_login_user_uxid_bydb(username):
    db, cursor = get_sql_conn()
    cursor.execute("select id from sys_user where user_code='%s'" % username)
    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()
    db.close()
    return new_str1


def get_dept_buy_id(sql):
    db, cursor = get_sql_conn()
    cursor.execute(sql)
    results = cursor.fetchall()
    cursor.close()
    db.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_code1(hos_goods_id):
    db, cursor = get_sql_conn()
    cursor.execute("select mdm_goods_code from mcms_goods_info where id='%s'" % hos_goods_id)
    results = cursor.fetchall()
    cursor.close()
    db.close()
    return process_tuple(results)


def get_goods_code1(hos_goods_id):
    db, cursor = get_sql_conn()
    cursor.execute("select hos_goods_code from mcms_goods_info where id='%s'" % hos_goods_id)
    results = cursor.fetchall()
    cursor.close()
    db.close()
    return process_tuple(results)


def get_goods_name(hos_goods_id):
    db, cursor = get_sql_conn()
    cursor.execute("select goods_name from mcms_goods_info where id='%s'" % hos_goods_id)
    results = cursor.fetchall()
    cursor.close()
    db.close()
    return process_tuple(results)


def get_goods_gg(hos_goods_id):
    db, cursor = get_sql_conn()
    cursor.execute("select goods_spec from mcms_goods_info where id='%s'" % hos_goods_id)
    results = cursor.fetchall()
    cursor.close()
    db.close()
    return process_tuple(results)


def get_supply_id(hos_goods_id):
    db, cursor = get_sql_conn()
    cursor.execute("select id from supply_prov_hos_goods  where hos_goods_id='%s'" % hos_goods_id)
    results = cursor.fetchall()
    cursor.close()
    db.close()
    return process_tuple(results)


def get_pkg_def_id(hos_goods_id):
    db, cursor = get_sql_conn()
    cursor.execute("select pkg_def_id from mcms_pkg where hos_goods_id='%s'" % hos_goods_id)
    results = cursor.fetchone()
    cursor.close()
    db.close()
    return process_tuple(results)


def get_pkg_def_Name(hos_goods_id):
    db, cursor = get_sql_conn()
    cursor.execute("select pkg_def_name from mcms_pkg where hos_goods_id='%s'" % hos_goods_id)
    results = cursor.fetchone()
    cursor.close()
    db.close()
    return process_tuple(results)


def get_goods_name1(hos_goods_id):
    db, cursor = get_sql_conn()
    cursor.execute("select goods_name from mcms_goods_info where id='%s'" % hos_goods_id)
    results = cursor.fetchall()
    cursor.close()
    db.close()
    return process_tuple(results)


def get_mdm_goods_code(sql):
    db, cursor = get_sql_conn()
    cursor.execute(sql)
    results = cursor.fetchall()
    cursor.close()
    db.close()
    return results


def get_goods_mfrs_id(hos_goods_id):
    db, cursor = get_sql_conn()
    cursor.execute("select goods_mfrs_id from mcms_goods_info where id='%s'" % hos_goods_id)
    results = cursor.fetchall()
    cursor.close()
    db.close()
    return process_tuple(results)


def check_source_id(hos_goods_id, source_id):
    db, cursor = get_sql_conn()
    cursor.execute("select hos_goods_id from mcms_pur_plan_detail where id='%s'" % (source_id))
    results1 = cursor.fetchall()
    cursor.close()
    db.close()
    hos_goods_id2 = process_tuple(results1)
    if hos_goods_id2 == hos_goods_id:
        return True
    else:
        return False


def check_batch_id(source_batch_id):
    db, cursor = get_sql_conn()
    cursor.execute("select id from mcms_hos_batch where id='%s'" % (source_batch_id))
    results1 = cursor.fetchall()
    cursor.close()
    db.close()
    batch_id = process_tuple(results1)
    if batch_id != None:
        return True
    else:
        return False


def check_barcode_id(barcode_id):
    db, cursor = get_sql_conn()
    cursor.execute("select id from hdi_barcode where id='%s'" % (barcode_id))
    results1 = cursor.fetchall()
    cursor.close()
    db.close()
    batch_id = process_tuple(results1)
    if batch_id != None:
        return True
    else:
        return False


def get_PICK_id(branch_id, bill_mode):
    sql = "select id from  mcms_pick  where branch_id='%s' and bill_mode='%s' order by   CREATE_TIME desc LIMIT 1;" % (
        branch_id, bill_mode)
    print(sql)
    return process_tuple(get_dept_buy_id(sql))


def get_pick_detail_id(branch_id, bill_mode):
    main_id = get_PICK_id(branch_id, bill_mode)
    sql = "select id from  mcms_pick_detail  where pid='%s';" % (
        main_id)
    print(sql)
    return process_tuple(get_dept_buy_id(sql))


def get_PICK_order_no(branch_id, bill_mode):
    PICK_id = get_PICK_id(branch_id, bill_mode)
    sql = "select order_no from  mcms_pick  where id='%s' order by   CREATE_TIME desc LIMIT 1;" % (
        PICK_id)
    print(sql)
    return process_tuple(get_dept_buy_id(sql))


def get_buyBillId(branch_id, bill_mode):
    sql = "select id from  spd3_herp_test2.mcms_dept_buy where branch_id='%s' and bill_mode='%s' order by create_time desc  LIMIT 1;" % (
        branch_id, bill_mode)
    print(sql)
    return process_tuple(get_dept_buy_id(sql))


def get_buyOrderNo(branch_id, bill_mode):
    sql = "select order_no from  spd3_herp_test2.mcms_dept_buy where branch_id='%s' and bill_mode='%s' order by create_time desc  LIMIT 1;" % (
        branch_id, bill_mode)
    print(sql)
    return process_tuple(get_dept_buy_id(sql))


def get_purOrderNo(branch_id, bill_mode):
    sql = "select id from  mcms_purchase where branch_id='%s' and bill_mode='%s' order by create_time desc  LIMIT 1;" % (
        branch_id, bill_mode)
    # print(sql)
    id1 = process_tuple(get_dept_buy_id(sql))
    sql = "select order_no from  mcms_purchase where branch_id='%s' and bill_mode='%s'and id='%s' order by create_time desc LIMIT 1;" % (
        branch_id, bill_mode, id1)
    print(sql)
    return process_tuple(get_dept_buy_id(sql))


def get_purBillId(branch_id, bill_mode):
    sql = "select id from  mcms_purchase where branch_id='%s' and bill_mode='%s' order by create_time desc  LIMIT 1;" % (
        branch_id, bill_mode)
    # print(sql)
    return process_tuple(get_dept_buy_id(sql))


def get_planBillId(hos_goods_id):
    planBillId = process_tuple(
        get_id(
            "select pid from spd3_herp_test2.mcms_pur_plan_detail  where hos_goods_id='%s' order by create_time desc  LIMIT 1;" % (
                hos_goods_id)))
    return planBillId


def get_planOrderNo(hos_goods_id):
    return process_tuple(
        get_id(
            "select order_no from spd3_herp_test2.mcms_pur_plan_detail  where hos_goods_id='%s' order by create_time desc  LIMIT 1;" % (
                hos_goods_id)))


def get_ext_info(branch_id, bill_mode):
    distr_main_key = get_id(
        "select id from hdi_distr a where branch_id = '%s' and bill_mode='%s' order by create_time desc LIMIT 1;" % (
            branch_id, bill_mode))
    ext_info = get_id(
        "select ext_info from hdi_distr a where id = '%s' and bill_mode='%s' order by create_time desc LIMIT 1;" % (
            distr_main_key, bill_mode))
    # print(bill_relation_json)
    return ext_info


def get_purBillDate(branch_id, bill_mode):
    distr_main_key = get_id(
        "select id from hdi_distr a where branch_id = '%s' and bill_mode='%s' order by create_time desc LIMIT 1;" % (
            branch_id, bill_mode))
    ext_info = get_id(
        "select ext_info from hdi_distr a where id = '%s' and bill_mode='%s' order by create_time desc LIMIT 1;" % (
            distr_main_key, bill_mode))
    # print(bill_relation_json)
    return ext_info


def get_purBillDate2(branch_id, bill_mode):
    main_key = get_id(
        "select id from mcms_psi a where target_branch_id = '%s' and bill_mode='%s' order by create_time desc LIMIT 1;" % (
            branch_id, bill_mode))
    bill_relation_json = get_id(
        "select bill_relation_json from mcms_psi a where id = '%s' and bill_mode='%s' order by create_time desc LIMIT 1;" % (
            main_key, bill_mode))
    # print(bill_relation_json)
    return bill_relation_json


def get_mcms_chek_purBillDate(branch_id, bill_mode):
    main_key = get_id(
        "select id from mcms_check a where branch_id = '%s' and bill_mode='%s' order by create_time desc LIMIT 1;" % (
            branch_id, bill_mode))
    bill_relation_json = get_id(
        "select bill_relation_json from mcms_check a where id = '%s' and bill_mode='%s' order by create_time desc LIMIT 1;" % (
            main_key, bill_mode))
    # print(bill_relation_json)
    return bill_relation_json


def get_distrBillId(branch_id, bill_mode):
    distr_main_key = get_id(
        "select id from hdi_distr a where branch_id = '%s' and bill_mode='%s' order by create_time desc LIMIT 1;" % (
            branch_id, bill_mode))
    return distr_main_key


def get_distr_detail_id(branch_id, bill_mode):
    distr_main_key = get_distrBillId(branch_id, bill_mode)
    distr_detail_id = get_id(
        "select id from hdi_distr_detail a where pid='%s' order by create_time desc LIMIT 1;" % (
            distr_main_key))
    return distr_detail_id


def get_distrOrderNo(branch_id, bill_mode):
    # 查询配送单order_no
    distr_order_no = get_id(
        "select order_no from hdi_distr a where id = '%s' and bill_mode='%s' order by create_time desc LIMIT 1;" % (
            get_distrBillId(branch_id, bill_mode), bill_mode))
    return distr_order_no


def get_checkBillId(branch_id, bill_mode):
    checkBillId = get_id(
        "select id from  mcms_check where branch_id='%s' and bill_mode='%s' order by create_time desc LIMIT 1;" % (
            branch_id, bill_mode))
    return checkBillId


def get_checkOrderNo(branch_id, bill_mode):
    checkOrderNo = get_id(
        "select order_no from mcms_check a where id = '%s' and bill_mode='%s' order by create_time desc LIMIT 1;" % (
            get_checkBillId(branch_id, bill_mode), bill_mode))
    return checkOrderNo


def get_psiInBillId(branch_id, bill_mode):
    psiInBillId = get_id(
        "select id from  mcms_psi where target_branch_id='%s' and bill_mode='%s' and stock_kind='RK_YS' order by create_time desc LIMIT 1;" % (
            branch_id, bill_mode))
    return psiInBillId


def get_psiInOrderNo(branch_id, bill_mode):
    psiInOrderNo = get_id(
        "select order_no from mcms_psi a where id = '%s' and bill_mode='%s' order by create_time desc LIMIT 1;" % (
            get_psiInBillId(branch_id, bill_mode), bill_mode))
    return psiInOrderNo


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()
    db.close()
    return new_str1


def process_date(str1):
    # str1 = "datetime.datetime(2024, 9, 17, 15, 32, 58)"
    str2 = str1.replace('datetime.datetime(', "")
    str3 = str2.replace(')', "")
    result = str3.split(",")
    result.pop(3)
    result.pop(4)
    result.pop(3)
    print(result)
    b = [int(x) for x in result]
    print(type(b), b)
    date_list = b
    #
    from datetime import datetime

    # # 假设我们有一个列表，包含年、月、日
    # date_list = [2023, 3, 14]
    # print(type(date_list),date_list)
    #
    # # 使用列表元素构造字符串
    date_string = '-'.join(map(str, date_list))
    #
    # # 使用datetime.strptime将字符串转换为日期
    date = datetime.strptime(date_string, '%Y-%m-%d').date()

    #
    print(date)  # 输出: 2023-03-14
    return date


def get_last_auditor(order_no):
    db, cursor = get_sql_conn()
    cursor.execute("select last_auditor from mcms_pur_plan  where order_no='%s';" % order_no)
    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()
    db.close()
    return new_str1
# hos_goods_id = 'h034700004220'
# main2(hos_goods_id)
# try:
#     set_goods_coding_type('h0347-3761')
# except Exception as e:
#     # 打印错误信息
#     print(f"发生错误: {e}")