# encoding: utf-8
import os
import random
import re
import time
from flask import Blueprint, json, request
import requests
from app.model.base import dbconn, dbconn2, dbconn3
from bs4 import BeautifulSoup
from http import HTTPStatus
from dashscope.audio.asr import Transcription
import json

bp = Blueprint('importdata', __name__, url_prefix='/importdata')


@bp.route('/audio_test', methods=['POST', 'GET'])
def audio_test():
    req = request.get_json()
    # 若没有将API Key配置到环境变量中，需将下面这行代码注释放开，并将apiKey替换为自己的API Key
    import dashscope
    dashscope.api_key = "sk-d73786459e4940cfba6bd0e48d8d28cd"

    transcribe_response = Transcription.async_call(
        model='paraformer-v2',
        file_urls=[req['file_url']],
        language_hints=['zh', 'en'],  # “language_hints”只支持paraformer-v2模型
        diarization_enabled=True,
        speaker_count=req['speaker_count']
    )

    while True:
        if transcribe_response.output.task_status == 'SUCCEEDED' or transcribe_response.output.task_status == 'FAILED':
            break
        transcribe_response = Transcription.fetch(task=transcribe_response.output.task_id)

    if transcribe_response.status_code == HTTPStatus.OK:
        print(json.dumps(transcribe_response.output, indent=4, ensure_ascii=False))
        print('transcription done!')
        
    return transcribe_response.output

@bp.route('/ai_test3')
def ai_test3():
    
    url = "http://47.94.203.143:9001/api_v2/ai_format"
    msg = '''```
发言人1:下午好，小公主叫什么名字？看起来有点心事。
发言人2:她叫绵绵，最近总用后腿蹬耳朵，毛都抓秃了。
发言人1:幼儿里有好多咖啡渣样的分泌物，这种情况持续多久了？洗澡时有进水吗？
发言人2:大概两周多，上周去宠物店美容的时候，就开始频繁的甩了个头，但是店员说用的是干燥箱。
发言人1:平时是散养还是关龙？家里有其他宠物吗？
发言人2:他在家都是自由活动的，家里面还有只布偶，不过那只最近眼睛发炎了。
发言人1:绵绵好乖，我们做个快速染色，两小只。平时会互相舔毛吗？最近换猫砂或清洁剂吗？
发言人2:他们俩的关系还是特别好的。猫砂从膨润土换成了豆腐砂，一个多月了。这和耳朵有关。
发言人1:您看这些椭圆形的包子，这是马拉色菌过度繁殖的表现，不过别担心。嗯，这个喷嚏频率高吗？
发言人2:对，最近打喷嚏的次数好像变多了，我还以为他是换季着凉了。
发言人1:可能需要补充做个孢疹病毒试纸检测。虽然疫苗齐全，但多猫环境容易。绵绵的鸳鸯眼真特别是天生异瞳吗？
发言人2:领养的时候，救助的人说这是虹膜异色症，当时就觉得这张眼睛比较好看。
发言人1:病毒检测阴性可以排除猫鼻支，综合来看是耳道真菌感染并轻微过敏，建议每周两次药浴，配合抗真菌第二剂。对了，暂时换回原来的猫砂会更稳妥。
发言人2:需要隔离吗？他们总睡同一个猫窝。
发言人1:最好分开睡垫。真菌孢子真菌孢子容易传通过织物传播。我给您开瓶环境消毒喷雾，记得重点处理抓板区域。呃，这是家里另外一位小祖宗吧，眼睛发炎的话可以顺带检查下。
发言人2:太好了，她叫雪球，我这就回去挂号，听到没回家不许再给姐姐舔耳朵了。
发言人1:下次带雪球来，记得提前近16小时，说不定要抽血检查过敏原。毕竟两位公主的排场可不能含糊。好玩。
```
'''
        
    params = {
        'msg': msg
    }
    reply = requests.post(url=url,json=params)
    
    content_json = json.loads(reply.content)
        
    return '1'

@bp.route('/ai_test2')
def ai_test2():
    
    url = "http://47.94.203.143:9001/api_v2/qwen3"
    msg = '''
现在有一份宠物医生与患病宠物的主人之间的对话，现有如下要求：
1.请判断出发言人1和发言人2的身份并替换掉原文中的名称；2.找出对话中不准确和不专业的单词并修改进原文，无需额外解释。
以下是对话内容：
```
发言人1:下午好，小公主叫什么名字？看起来有点心事。
发言人2:她叫绵绵，最近总用后腿蹬耳朵，毛都抓秃了。
发言人1:幼儿里有好多咖啡渣样的分泌物，这种情况持续多久了？洗澡时有进水吗？
发言人2:大概两周多，上周去宠物店美容的时候，就开始频繁的甩了个头，但是店员说用的是干燥箱。
发言人1:平时是散养还是关龙？家里有其他宠物吗？
发言人2:他在家都是自由活动的，家里面还有只布偶，不过那只最近眼睛发炎了。
发言人1:绵绵好乖，我们做个快速染色，两小只。平时会互相舔毛吗？最近换猫砂或清洁剂吗？
发言人2:他们俩的关系还是特别好的。猫砂从膨润土换成了豆腐砂，一个多月了。这和耳朵有关。
发言人1:您看这些椭圆形的包子，这是马拉色菌过度繁殖的表现，不过别担心。嗯，这个喷嚏频率高吗？
发言人2:对，最近打喷嚏的次数好像变多了，我还以为他是换季着凉了。
发言人1:可能需要补充做个孢疹病毒试纸检测。虽然疫苗齐全，但多猫环境容易。绵绵的鸳鸯眼真特别是天生异瞳吗？
发言人2:领养的时候，救助的人说这是虹膜异色症，当时就觉得这张眼睛比较好看。
发言人1:病毒检测阴性可以排除猫鼻支，综合来看是耳道真菌感染并轻微过敏，建议每周两次药浴，配合抗真菌第二剂。对了，暂时换回原来的猫砂会更稳妥。
发言人2:需要隔离吗？他们总睡同一个猫窝。
发言人1:最好分开睡垫。真菌孢子真菌孢子容易传通过织物传播。我给您开瓶环境消毒喷雾，记得重点处理抓板区域。呃，这是家里另外一位小祖宗吧，眼睛发炎的话可以顺带检查下。
发言人2:太好了，她叫雪球，我这就回去挂号，听到没回家不许再给姐姐舔耳朵了。
发言人1:下次带雪球来，记得提前近16小时，说不定要抽血检查过敏原。毕竟两位公主的排场可不能含糊。好玩。
```
'''
    i = 0
    while i < 200:
        params = {
            'msg': msg
        }
        reply = requests.post(url=url,json=params)
        
        content_json = json.loads(reply.content)

        i += 1

        data1 = {
            'api': "format6",
            'prompt': msg,
            'think': content_json['think'],
            'result': content_json['answer']
        }
        
        dbconn.insert(table='ai_test', data=data1)


    return '1'


@bp.route('/ai_test1')
def ai_test1():
    
    url = "http://47.94.203.143:9001/api_v2/qwen3"
    msg = '''
现在有一份宠物医生与患病宠物的主人之间的对话，现有如下要求：
请按照"KV"格式生成一条数据，其中K为【宠物名称，性别，年龄，主诉，病因，临床诊断，治疗方案，预后，医嘱】字段，V为对应的值，切每一组KV用【;】分隔，无需额外解释。
以下是对话内容：
```
医生：您好，请坐。宠物叫什么名字？
宠物主人：医生好，它叫球球，这两天一直拉肚子，精神状态也差。
医生：球球真乖。咱们先了解下情况，您发现它腹泻大概多久了？便便形态是水状还是糊状？
宠物主人：前天开始的，开始是软便，昨天变成了黄褐色的黏液状，今天早上还带有点血丝。
医生：最近有没有换过狗粮或者误食过骨头、塑料袋之类的东西？
宠物主人：上周换了新牌子的幼犬粮，不过按说明慢慢过渡的。对了，昨天他啃了我半块掉在地上的奶油蛋糕。
医生：突然的高脂肪食物可能刺激肠胃。体温量过吗？呕吐过吗？
宠物主人：今天早上用耳温计测试39.2度，然后呕吐两次胆汁样液体，现在连鸡肉干都不吃了。
医生：球球别怕，我们轻轻听一听。平时驱虫按时做吗？疫苗都打全了吗？
宠物主人：上个月刚去过去犬瘟热、细小病毒、腺病毒三联疫苗，今年加强过。然后会不会是细小？
医生：您观察的很仔细，腹部有明显压痛，不过咱们先别自己吓自己，我建议做个血常规和粪便PCR检测，这样能准确区分是细菌感染？寄生虫还是病毒性疾病？球球这名字真可爱，是有什么特别含义吗？
宠物主人：他小时候就喜欢追着网球跑，圆滚滚的像个小毛球。
医生：好消息，细小病毒检测阴性，白细胞升高明显。粪便里有大量致病性大肠杆菌，应该是饮食不当引发的急性肠炎。咱们先补充输液补充电解质，配合抗生素治疗。这两天胃肠道处方罐头，您看可以吗？
宠物主人：那太好了，那大概需要住几天？
医生：观察三天比较稳妥，我们24小时都有护理措施，护理到位了，回家可以准备些南瓜泥，对修复肠道粘膜有帮助。球球喜欢看窗外吗？我们住院部有全景玻璃房，输液时可以给他选个阳光充足的位置。
宠物主人：那太好了，他最喜欢晒太阳，麻烦您多费心了。
医生：好的。
```
'''
    i = 0
    while i < 51:
        params = {
            'msg': msg
        }
        reply = requests.post(url=url,json=params)
        
        content_json = json.loads(reply.content)

        i += 1

        data1 = {
            'api': "report3",
            'prompt': msg,
            'think': content_json['think'],
            'result': content_json['answer']
        }
        
        dbconn.insert(table='ai_test', data=data1)


    return '1'


@bp.route('/ai_test_format')
def ai_test_format():
    old_id = '0'
    i = 0
    size = 100
    while i <= 10:
        page = i * size
        
        sql_str = 'SELECT id, api, result ' \
                ' FROM ai_test ' \
                ' where api = "format6"  and id >' + old_id + \
                ' limit ' + str(page) + ', ' + str(size)

        rows = dbconn2.query(
            sql=sql_str
        )

        i += 1
        
        if len(rows) == 0:
            break

        for val1 in rows:
            
            s1 = val1['result'].strip('\n').strip('```')
            s1 = s1.strip('\n')
            # if len(s1) < 2 :
            #     s1 = val1['result'].strip('\n').split('；')
                
            is_restart = 0
                
            # for val2 in s1:
            #     if val2 == '':
            #         continue
                
            if '发言人' in s1:
                is_restart = 1
                
                    
            if is_restart == 0 :
                data1 = {
                    'old_id' : val1['id'],
                    'api': val1['api'],
                    'result' : s1,
                }
                
                dbconn.insert(table='ai_test_res', data=data1)

    return '1'


@bp.route('/ai_test_report1')
def ai_test_report1():
    old_id = '1'
    i = 0
    size = 100
    while i <= 10:
        page = i * size
        
        sql_str = 'SELECT id, api, result ' \
                ' FROM ai_test ' \
                ' where api = "report3"  and id >' + old_id + \
                ' limit ' + str(page) + ', ' + str(size)

        rows = dbconn2.query(
            sql=sql_str
        )

        i += 1
        
        if len(rows) == 0:
            break

        for val1 in rows:
            
            s1 = val1['result'].strip('\n').split(';')
            
            if len(s1) < 2 :
                s1 = val1['result'].strip('\n').split('；')
                
            pet_name = ''
            pet_sex = ''
            pet_age = ''
            chief_complaint = ''
            clinical = ''
            suspected_disease = ''
            prognosis = ''
            treatment = ''
            pet_name = ''
            doctors_advice = ''
            for val2 in s1:
                if val2 == '':
                    continue
                s2 = val2.strip().split(':')
                if len(s2) <= 1:
                    s2 = val2.strip().split('：')
                
                if s2[0] == '宠物名称':
                    pet_name = s2[1]
                    
                if s2[0] == '性别':
                    pet_sex = s2[1]
                    
                if s2[0] == '年龄':
                    pet_age = s2[1]
                    
                if s2[0] == '主诉':
                    chief_complaint = s2[1]
                    
                if s2[0] == '病因':
                    suspected_disease = s2[1]
                    
                if s2[0] == '临床诊断':
                    clinical = s2[1]
                    
                if s2[0] == '治疗方案':
                    treatment = s2[1]
                    
                if s2[0] == '预后':
                    prognosis = s2[1]
                    
                if s2[0] == '医嘱':
                    doctors_advice = s2[1]
                    
                    
            data1 = {
                'old_id' : val1['id'],
                'api': val1['api'],
                'pet_name' : pet_name,
                'pet_sex' : pet_sex,
                'pet_age' : pet_age,
                'chief_complaint' : chief_complaint,
                'suspected_disease' : suspected_disease,
                'clinical' : clinical,
                'treatment' : treatment,
                'prognosis' : prognosis,
                'doctors_advice' : doctors_advice,
            }
            
            dbconn.insert(table='ai_report', data=data1)

    return '1'


@bp.route('/data_ch_report')
def data_ch_report():
    old_id = '466342'
    i = 0
    size = 500
    while i <= 600:
        page = i * size
        
        sql_str = 'SELECT rp.id, rp.report_main_orgin_id, rp.species, rp.breed, rp.patient_gender_code, rp.weight, rp.is_sterilization, rp.chief_complaint'\
                ', rp.phys_examination, rp.suspected_disease, rp.treatment, rp.operation_record, rp.doctors_advice, rp.return_visit ' \
                ' FROM ch_report.ch_report_patient as rp ' \
                ' where rp.chief_complaint != ""  and rp.id >' + old_id + \
                ' limit ' + str(page) + ', ' + str(size)

        rows = dbconn2.query(
            sql=sql_str
        )

        i += 1
        print(i)
        
        if len(rows) == 0:
            break

        for val1 in rows:
            
            sql_str2 = 'SELECT re.diagnosis_cate_name, re.diagnosis_subject, re.diagnosis_summary' \
                ' FROM ch_report.ch_report_evalute as re ' \
                ' where re.report_main_orgin_id ="' + val1['report_main_orgin_id'] +'"'

            rows2 = dbconn2.query(
                sql=sql_str2
            )
            
            data1 = {
                'orgin_id': val1['report_main_orgin_id'],
                'species': val1['species'],
                'breed': val1['breed'],
                'patient_gender_code': val1['patient_gender_code'],
                'weight': val1['weight'],
                'is_sterilization': val1['is_sterilization'],
                'chief_complaint': val1['chief_complaint'],
                'phys_examination': val1['phys_examination'],
                'suspected_disease': val1['suspected_disease'],
                'treatment': val1['treatment'],
                'operation_record': val1['operation_record'],
                'doctors_advice': val1['doctors_advice'],
                'return_visit': val1['return_visit'],
                'old_id': val1['id'],
            }
            
            if len(rows2) >0 :
                data1['diagnosis_cate_name'] = rows2[0]['diagnosis_cate_name']
                data1['diagnosis_subject'] = rows2[0]['diagnosis_subject']
                data1['diagnosis_summary'] = rows2[0]['diagnosis_summary']
            
            dbconn.insert(table='ch_data4', data=data1)

    return '1'

@bp.route('/xd_cate_info_rpl')
def xd_cate_info_rpl():
    
    sql_str = 'SELECT id, content FROM xd_cate_info WHERE content like "%&nbsp;%" ' 

    rows = dbconn.query(
        sql=sql_str
    )

    for v in rows:
        
        content = v['content'].replace("&nbsp;","")
            
        data1 = {
            'content' : content
        }
        
        dbconn.update(table='xd_cate_info', data=data1,condition={"id":v['id']})
    
    # with open('./med_data2.jsonl', "w", encoding="utf-8") as file:
    #     for message in messages:
    #         file.write(json.dumps(message, ensure_ascii=False) + "\n")
    return '1'


@bp.route('/xd_data_res')
def xd_data_res():
    
    sql_str = 'SELECT xc.id, xc.name ' \
                ' FROM xd_cate_info as xci left join xd_cate as xc on xci.xd_cate_id = xc.id ' \
                ' WHERE xc.children = 4 ' \
                ' group by xd_cate_id '

    rows = dbconn.query(
        sql=sql_str
    )

    for v in rows:
            
        sql_str2 = 'SELECT title, content ' \
            ' FROM xd_cate_info ' \
            ' WHERE xd_cate_id = '+ str(v['id'])

        rows2 = dbconn.query(
            sql=sql_str2
        )
        
        content = ''
        for v2 in rows2:
            content += v2['title']+v2['content']+';'
            
        
        sql_str3 = 'SELECT xc2.name ' \
            ' FROM xd_cate as xc left join xd_cate as xc2 on xc.pid = xc2.id ' \
            ' WHERE xc.id = '+ str(v['id'])

        rows3 = dbconn.query(
            sql=sql_str3
        )
        
        name_p = ''
        if rows3[0]['name'] != '症状描述':
            name_p = rows3[0]['name']
            
        sql_str4 = 'SELECT xt.name, xt.introduce, xt.goods_name ' \
            ' FROM xd_cate_treatment as xct left join xd_treatment as xt on xt.id = xct.treatment_id ' \
            ' WHERE xct.cate_id = '+ str(v['id'])

        rows4 = dbconn.query(
            sql=sql_str4
        )
        
        treatment = ''
        for v4 in rows4:
            treatment += v4['name']+'：'+v4['introduce']+'\n治疗方案：'+v4['goods_name']+';'
            
        data1 = {
            'name': v['name'],
            'name_p': name_p,
            'content':content,
            'treatment':treatment
        }
        
        # messages.append(data1)
        
        dbconn.insert(table='xd_data_res', data=data1)
    
    # with open('./med_data2.jsonl', "w", encoding="utf-8") as file:
    #     for message in messages:
    #         file.write(json.dumps(message, ensure_ascii=False) + "\n")
    return '1'


@bp.route('/data_ill_res3')
def data_ill_res3():
    
        #' left join report_main_status as rms on rm.orgin_id=rms.report_main_orgin_id' \
    sql_str = 'SELECT kc.id, lcs.lab_category_id, kc.keyword, kc.clinical, GROUP_CONCAT(lc.name order by lc.name) as name' \
                ' FROM keyword_clinical as kc ' \
                ' left join lab_category_sympeons as lcs on kc.clinical = lcs.name ' \
                ' left join lab_category as lc on lc.id = lcs.lab_category_id ' \
                ' WHERE lcs.lab_category_id > 0 and kc.id > 0 group by kc.keyword, kc.clinical'

    rows = dbconn.query(
        sql=sql_str
    )

    for v in rows:
        
        chief_complaint = replace_self(v['keyword'])
        
        phys_examination = replace_self(v['clinical'])
        
        suspected_diseases = replace_self(v['name'])
    
            
        sql_str2 = 'SELECT illness_name, drug_name ' \
            ' FROM data_illness_durg ' \
            ' WHERE drug_name != "" and cate_id = '+ str(v['lab_category_id'])

        rows2 = dbconn.query(
            sql=sql_str2
        )
        
        treatment = ''
        for v2 in rows2:
            treatment += v2['illness_name']+':'+v2['drug_name']+';'
            
        
        sql_str3 = 'SELECT data ' \
            ' FROM data_illness_note ' \
            ' WHERE data != "" and cate_id = '+ str(v['lab_category_id'])

        rows3 = dbconn.query(
            sql=sql_str3
        )
        
        doctors_advice = ''
        for v3 in rows3:
            doctors_advice += v3['data']+';'
            
        
        data1 = {
            'chief_complaint': chief_complaint,
            'phys_examination': phys_examination,
            'suspected_disease': suspected_diseases,
            'treatment': treatment,
            'doctors_advice': doctors_advice,
            'old_id':v['id']
        }
        
        # messages.append(data1)
        
        dbconn.insert(table='ill_data_res3', data=data1)
    
    # with open('./med_data2.jsonl', "w", encoding="utf-8") as file:
    #     for message in messages:
    #         file.write(json.dumps(message, ensure_ascii=False) + "\n")
    return '1'


@bp.route('/data_ill_res2')
def data_ill_res2():
    
        #' left join report_main_status as rms on rm.orgin_id=rms.report_main_orgin_id' \
    sql_str = 'SELECT kc.id, lcs.lab_category_id, kc.keyword, kc.clinical, lc.name ' \
                ' FROM keyword_clinical as kc ' \
                ' left join lab_category_sympeons as lcs on kc.clinical = lcs.name ' \
                ' left join lab_category as lc on lc.id = lcs.lab_category_id ' \
                ' WHERE kc.id > 0'

    rows = dbconn.query(
        sql=sql_str
    )

    for v in rows:
        
        chief_complaint = replace_self(v['keyword'])
        
        phys_examination = replace_self(v['clinical'])
        
        suspected_diseases = replace_self(v['name'])
            
        sql_str2 = 'SELECT illness_name, drug_name ' \
            ' FROM data_illness_durg ' \
            ' WHERE drug_name != "" and cate_id = '+ str(v['lab_category_id'])

        rows2 = dbconn.query(
            sql=sql_str2
        )
        
        treatment = ''
        for v2 in rows2:
            treatment += v2['illness_name']+':'+v2['drug_name']+';'
            
        
        sql_str3 = 'SELECT data ' \
            ' FROM data_illness_note ' \
            ' WHERE data != "" and cate_id = '+ str(v['lab_category_id'])

        rows3 = dbconn.query(
            sql=sql_str3
        )
        
        doctors_advice = ''
        for v3 in rows3:
            doctors_advice += v3['data']+';'
            
        
        data1 = {
            'chief_complaint': chief_complaint,
            'phys_examination': phys_examination,
            'suspected_disease': suspected_diseases,
            'treatment': treatment,
            'doctors_advice': doctors_advice,
            'old_id':v['id']
        }
        
        # messages.append(data1)
        
        dbconn.insert(table='ill_data_res2', data=data1)
    
    # with open('./med_data2.jsonl', "w", encoding="utf-8") as file:
    #     for message in messages:
    #         file.write(json.dumps(message, ensure_ascii=False) + "\n")
    return '1'


@bp.route('/data_ill_res')
def data_ill_res():
    
        #' left join report_main_status as rms on rm.orgin_id=rms.report_main_orgin_id' \
    sql_str = 'SELECT id, cate_id, name, data ' \
                ' FROM data_illness_symptoms ' \
                ' WHERE data != "" and id > 0'

    rows = dbconn.query(
        sql=sql_str
    )

    for v in rows:
        
        chief_complaint = replace_self(v['data'])
        
        suspected_diseases = replace_self(v['name'])
            
        sql_str2 = 'SELECT illness_name, drug_name ' \
            ' FROM data_illness_durg ' \
            ' WHERE drug_name != "" and cate_id = '+ str(v['cate_id'])

        rows2 = dbconn.query(
            sql=sql_str2
        )
        
        treatment = ''
        for v2 in rows2:
            treatment += v2['illness_name']+':'+v2['drug_name']+';'
            
        
        sql_str3 = 'SELECT data ' \
            ' FROM data_illness_note ' \
            ' WHERE data != "" and cate_id = '+ str(v['cate_id'])

        rows3 = dbconn.query(
            sql=sql_str3
        )
        
        doctors_advice = ''
        for v3 in rows3:
            doctors_advice += v3['data']+';'
            
        
        data1 = {
            'chief_complaint': chief_complaint,
            'suspected_disease': suspected_diseases,
            'treatment': treatment,
            'doctors_advice': doctors_advice,
            'old_id':v['id']
        }
        
        # messages.append(data1)
        
        dbconn.insert(table='ill_data_res', data=data1)
    
    # with open('./med_data2.jsonl', "w", encoding="utf-8") as file:
    #     for message in messages:
    #         file.write(json.dumps(message, ensure_ascii=False) + "\n")
    return '1'


@bp.route('/new_tem')
def new_tem():
    i = 0
    size = 100
    while i <= 10:
        page = i * size
        i += 1
        
        sql_str = 'select `tem_name`, `describe`, `diagnosis` from medical_platform.dcm_tem ' \
                ' where tem_type = 1 and lang = 1 and id >92' \
                ' limit ' + str(page) + ', ' + str(size)

        rows = dbconn3.query(
            sql=sql_str
        )
        
        if len(rows) == 0:
            break
        
        for val in rows:
            
            data1 = {
                'tem_name': Translate(val['tem_name']),
                'describe': Translate(val['describe']),
                'diagnosis': Translate(val['diagnosis']),
                'user_id': 1,
                'lang': 2,
                'tem_type': 1,
            }
                
            dbconn.insert(table='medical_platform.ill_data_res', data=data1)
        
    return '1'

def Translate(data):
    
    if data == "":
        return ""
    
    source_lang ="zh"
    target_lang ="cht"
    trans_type ="y"
    
    TranslateToken = "JSY5jBTdd73iOO/6oFYyy/eGmVwip0NwrvLFeGvYzJy7PWpCJTn8euCYEvjZK3Xl"
        
    url = 'https://translate.wuyoufanyi.com/api/Translate'

    headers = {
        "content-type": "application/json",
        "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:123.0) Gecko/20100101 Firefox/123.0",
        "Authorization":"Bearer "+TranslateToken
    }

    params = {"text":data, "source_lang": source_lang, "target_lang":target_lang,"trans_type":trans_type}

    result = requests.request('POST', url=url, headers=headers, json=params)

    rows = json.loads(result.text)
    
    return rows['data']


@bp.route('/data_getimage')
def data_getimage():
    i = 0
    size = 100
    
    f = open('test.jsonl', 'a', encoding='UTF-8')
    
    while i <= 500:
        page = i * size
        i += 1
        
        sql_str = 'select `study_instance_uid`, `describe`, `diagnosis`, `images_png`, `images_jpeg` from vpet_data ' \
                ' limit ' + str(page) + ', ' + str(size)

        rows = dbconn.query(
            sql=sql_str
        )
        
        if len(rows) == 0:
            break
        
        for val in rows:
            
            data1 = {
                'study_instance_uid': val['study_instance_uid'],
                'describe': val['describe'].strip(' '),
                'diagnosis': val['diagnosis'].strip(' '),
                'images_png': val['images_png'],
                'images_jpeg': val['images_jpeg']
            }
            f.write('\n'+json.dumps(data1))
            
    
    f.close()
        
    return '1'

@bp.route('/data_vpet')
def data_vpet():
    i = 0
    size = 100
    while i <= 100:
        page = i * size
        i += 1
        
        sql_str = 'select di.id, di.study_instance_uid, dir.describe, dir.diagnosis, di.chief_complaint,'\
                ' di.pet_species, di.pet_breed, di.pet_sex, di.pet_sterilization from medical_platform.dcm_interpretation as di ' \
                ' left join dcm_interpretation_read as dir on di.id = dir.interpretation_id ' \
                ' where di.created_at < "2025-01-01 00:00:00" and di.id > 40773 and di.device_name = "DR" and di.status = 4 and di.study_instance_uid != ""' \
                ' limit ' + str(page) + ', ' + str(size)

        rows = dbconn3.query(
            sql=sql_str
        )
        
        if len(rows) == 0:
            break
        
        for val in rows:
        
            study_instance_uid = val['study_instance_uid']
            
            sql_str2 = 'select di.id, di.png, di.jpeg, di.boost_png, di.boost_jpeg, di.is_boost from hos_database.de_instances as di ' \
                    ' left join hos_database.de_series as dse on dse.id = di.de_series_id ' \
                    ' left join hos_database.de_study as ds on ds.id = dse.de_study_id ' \
                    ' where ds.study_instance_uid = "'+study_instance_uid+'"'

            rows2 = dbconn3.query(
                sql=sql_str2
            )
            
            images_png = ""
            images_jpeg = ""
            
            for v in rows2:
                jpeg = ''
                png = ''
                
                if v['is_boost'] == 1:
                    jpeg = v['boost_jpeg']
                    png = v['boost_png']
                else:
                    jpeg = v['jpeg']
                    png = v['png']
                    
                images_png += png + ","
                images_jpeg += jpeg + ","
            
            images_png = images_png.strip(',')
            images_jpeg = images_jpeg.strip(',')
            
            data1 = {
                'study_instance_uid': val['study_instance_uid'],
                'describe': val['describe'],
                'diagnosis': val['diagnosis'],
                'images_png': images_png,
                'images_jpeg': images_jpeg,
                'chief_complaint': val['chief_complaint'],
                'pet_species': val['pet_species'],
                'pet_breed': val['pet_breed'],
                'pet_sex': val['pet_sex'],
                'pet_sterilization': val['pet_sterilization'],
                'business_id': val['id'],
            }
                
            list_id = dbconn.insert(table='vpet_data', data=data1)
        
    return '1'


@bp.route('/vetlas_ins')
def vetlas_ins():
    
    save_dir = "/disk0/data/images/disease/"
    
    with open('/Users/haoyanbin/Desktop/vetlas_list.json', 'r') as file:
        
        result = file.read()
        
        rows = json.loads(result)

        # url = 'http://express-s72m-101984-5-1325696513.sh.run.tcloudbase.com/mini/collect/list'

        # result = requests.get(url=url)

        # rows = json.loads(result.text)

        for row in rows:
            
            imagesZipFileId = ''
            if str(row['imagesZipFileId']) != 'None':
                imagesZipFileId = str(row['imagesZipFileId'])
                
            imagesNumber = ''
            if str(row['imagesNumber']) != 'None':
                imagesNumber = str(row['imagesNumber'])
                
            loading = ''
            if str(row['loading']) != 'None':
                loading = str(row['loading'])
                
            failReason = ''
            if str(row['failReason']) != 'None':
                failReason = str(row['failReason'])
            
            data1 = {
                'collectId': row['id'],
                'collectName': row['collectName'],
                'imagesZipFileId': imagesZipFileId,
                'imagesNumber': imagesNumber,
                'meta': json.dumps(row['meta']),
                'thumbnail': row['thumbnail'],
                'tags': json.dumps(row['tags']),
                'loading': loading,
                'failReason': failReason,
                'collect_date': row['createdAt'],
            }
            
            list_id = dbconn.insert(table='vetlas_list', data=data1)
            
            with open('/Users/haoyanbin/Desktop/vetlas_info.json', 'r') as file:
                
                result2 = file.read()
                
                rows2 = json.loads(result2)
            
                # url2 = 'https://express-s72m-101984-5-1325696513.sh.run.tcloudbase.com/mini/filter/list?collectName=Xray_LungPattern_v8.3&id='+row['id']

                # result2 = requests.get(url=url2)
                
                # rows2 = json.loads(result2.text)
                
                for row2 in rows2:
                    
                    free = ''
                    if str(row2['free']) != 'None':
                        free = str(row2['free'])
                    
                    cover = ''
                    if str(row2['cover']) != 'None':
                        cover = str(row2['cover'])
                    
                    data2 = {
                        'list_id': list_id,
                        'collectId': row['id'],
                        'collectName': row2['collectName'],
                        'file_id': json.dumps(row2['file_id']),
                        'file_url': json.dumps(row2['file_url']),
                        'tupuName': row2['tupuName'],
                        'index': row2['index'],
                        'free': free,
                        'cover': cover,
                    }
                
                    info_id = dbconn.insert(table='vetlas_info', data=data2)
                    
                    ins_data21 = []
                    for row21 in row2['marks']:
                        
                        save_path = save_dir + row['id'] + "/"
                        
                        filename = row21['file_url'].split("/")[-1]
                
                        download_image(row21['file_url'], save_path, filename)
                        
                        data21 = {
                            'info_id': info_id,
                            'RGB': json.dumps(row21['RGB']),
                            'point': json.dumps(row21['point']),
                            'system': row21['system'],
                            'yIndex': row21['yIndex'],
                            'file_id': row21['file_id'],
                            'file_url': row21['file_url'],
                            'name_en': row21['name_en'],
                            'name_zh': row21['name_zh'],
                            'markName': row21['markName'],
                            'position': row21['position'],
                            'tupuName': row21['tupuName'],
                            'description': row21['description'],
                            'old_id': row21['ID'],
                            'image_path': save_path + filename
                        }
                    
                        ins_data21.append(data21)
                    
                    dbconn.insert_all(table='vetlas_info_marks', data=ins_data21)
                
                with open('/Users/haoyanbin/Desktop/vetlas_filter.json', 'r') as file:
                    
                    result3 = file.read()
                    
                    rows3 = json.loads(result3)
                    
                    # url3 = 'https://express-s72m-101984-5-1325696513.sh.run.tcloudbase.com/mini/tupu/json_all?collectName=Xray_LungPattern_v8.3&collectId='+row['id']

                    # result3 = requests.get(url=url3)
                    
                    # rows3 = json.loads(result3.text)
                    
                    for row3 in rows3['filter']:
                        data3 = {
                            'list_id': list_id,
                            'name': row3['name'],
                            'system': row3['system'],
                            'default_show': row3['default_show'],
                            'old_id': rows3['id'],
                        }
                    
                        filter_id = dbconn.insert(table='vetlas_filter', data=data3)
                        
                        ins_data3 = []
                        for row31 in row3['marks']:
                            data31 = {
                                'filter_id': filter_id,
                                'RGB': json.dumps(row31['RGB']),
                                'name': row31['name'],
                                'name_en': row31['name_en'],
                                'name_zh': row31['name_zh'],
                                'description': row31['description'],
                                'old_id': row31['ID'],
                                'mark': 1,
                            }
                            ins_data3.append(data31)
                        
                        # dbconn.insert_all(table='vetlas_filter_info', data=ins_data31)
                            
                        # ins_data32 = []
                        for row32 in row3['second']:
                            data32 = {
                                'filter_id': filter_id,
                                'RGB': json.dumps(row32['RGB']),
                                'name': row32['name'],
                                'name_en': row32['name_en'],
                                'name_zh': row32['name_zh'],
                                'description': row32['description'],
                                'old_id': row32['ID'],
                                'mark': 2,
                            }
                            ins_data3.append(data32)
                        
                        dbconn.insert_all(table='vetlas_filter_info', data=ins_data3)
            

    return '1'


@bp.route('/data_aaa')
def data_aaa():
    i = 0
    size = 100
    while i <= 20:
        page = i * size
        i += 1
        
        sql_str = 'select study_instance_uid from medical_platform.dcm_image_list_new' \
                ' where examine_pass_status = 3 and valid_status = 1' \
                ' limit ' + str(page) + ', ' + str(size)

        rows = dbconn3.query(
            sql=sql_str
        )
        
        if len(rows) == 0:
            break
        
        study_instance_uid = ''
        
        for v in rows:
            study_instance_uid += '"'+v['study_instance_uid']+'",'
            
        study_instance_uid = study_instance_uid.strip(',')
        
        sql_str2 = 'select de_instance_id from medical_platform.dcm_image_list_instance' \
                ' where instance_type = 4 and examine_pass_status = 3 and valid_status = 1' \
                ' and study_instance_uid in ('+study_instance_uid+')'

        rows2 = dbconn3.query(
            sql=sql_str2
        )
        
        de_instance_id = ''
        for v2 in rows2:
            de_instance_id += str(v2['de_instance_id'])+','
            
        de_instance_id = de_instance_id.strip(',')
    
        sql_str3 = 'select m.de_instances_id, m._value, m.de_organ_name, m.de_disease_name, m.de_symptom_name, i.jpeg, i.png, i.is_boost, i.boost_jpeg, i.boost_png' \
                    ' from hos_database.de_instance_new_mark as m' \
                    ' left join hos_database.de_instances as i on m.de_instances_id = i.id ' \
                    ' where m.de_instances_id in ('+de_instance_id+') and m.de_organ_name = "肺"'

        rows3 = dbconn3.query(
            sql=sql_str3
        )
        
        print(len(rows3))
        if len(rows3) == 0:
            continue
        
        for v3 in rows3:
            jpeg = ''
            png = ''
            
            if v3['is_boost'] == 1:
                jpeg = v3['boost_jpeg']
                png = v3['boost_png']
            else:
                jpeg = v3['jpeg']
                png = v3['png']
                
            print(v3['de_instances_id'], v3['de_organ_name'], v3['de_disease_name'], v3['de_symptom_name'], jpeg, png, v3['_value'])
        
    return '1'

@bp.route('/data_med')
def data_med():
    i = 0
    size = 100
    while i <= 75:
        page = i * size

        sql_str = 'SELECT id, study_instance_uid, device_name, body_part_examined, check_item' \
                  ', pet_species,  pet_breed, pet_sex, pet_age_year, pet_age_month, pet_sterilization' \
                  ', chief_complaint, pet_history, clinical, image_description, disease_diagnosis' \
                  ', treatment_plan, imaging_diagnosis, return_remark, quality_type, diagnosis_cate_id, illness_cate_id' \
                  ' FROM dcm_interpretation ' \
                  ' WHERE chief_complaint != "" ' \
                  ' limit ' + str(page) + ', ' + str(size)

        rows = dbconn3.query(
            sql=sql_str
        )

        i += 1

        for v in rows:
            
            sql_str1 = 'SELECT body_part_examined, check_item ' \
                  ' FROM dcm_interpretation_image ' \
                  ' WHERE interpretation_id = ' + str(v['id'])

            rows1 = dbconn3.query(
                sql=sql_str1
            )
            
            body_part_examined = ''
            check_item = ''
            
            for v1 in rows1:
                body_part_examined += v1['body_part_examined']
                check_item += v1['check_item']
                
            diagnosis_cate = ''
            if v['diagnosis_cate_id'] > 0:
                sql_str2 = 'SELECT cate_name' \
                  ' FROM dcm_category ' \
                  ' WHERE id = ' + str(v['diagnosis_cate_id'])

                rows2 = dbconn3.query(
                    sql=sql_str2
                )
                
                diagnosis_cate = rows2[0]['cate_name']
            
            illness_cate = ''
            if v['illness_cate_id'] > 0:
                sql_str3 = 'SELECT cate_name' \
                  ' FROM dcm_category ' \
                  ' WHERE id = ' + str(v['illness_cate_id'])

                rows3 = dbconn3.query(
                    sql=sql_str3
                )
                
                illness_cate = rows3[0]['cate_name']
            
            
            sql_str4 = 'SELECT `describe`, diagnosis ' \
                  ' FROM dcm_interpretation_read ' \
                  ' WHERE interpretation_id = ' + str(v['id']) + ' order by id desc'

            rows4 = dbconn3.query(
                sql=sql_str4
            )
            
            describe = ''
            diagnosis = ''
            if len(rows4) > 0 :
                describe = rows4[0]['describe']
                diagnosis = rows4[0]['diagnosis']
             
            data1 = {
                'old_id':v['id'], 
                'study_instance_uid':v['study_instance_uid'],
                'device_name':v['device_name'],
                'body_part_examined':body_part_examined,
                'check_item':check_item,
                'pet_species':v['pet_species'],
                'pet_breed':v['pet_breed'],
                'pet_sex':v['pet_sex'],
                'pet_age_year':v['pet_age_year'],
                'pet_age_month':v['pet_age_month'],
                'pet_sterilization':v['pet_sterilization'],
                'chief_complaint':v['chief_complaint'].strip('，').strip('。'),
                'pet_history':v['pet_history'].strip('，').strip('。'),
                'clinical':v['clinical'].strip('，').strip('。'),
                'image_description':v['image_description'].strip('，').strip('。'),
                'disease_diagnosis':v['disease_diagnosis'].strip('，').strip('。'),
                'treatment_plan':v['treatment_plan'].strip('，').strip('。'),
                'imaging_diagnosis':v['imaging_diagnosis'].strip('，').strip('。'),
                'return_remark':v['return_remark'].strip('，').strip('。'),
                'quality_type':v['quality_type'],
                'diagnosis_cate':diagnosis_cate,
                'illness_cate':illness_cate,
                'describe':describe,
                'diagnosis':diagnosis,
                'source':1
            }
            
            dbconn.insert(table='med_record', data=data1)
    return '1'


@bp.route('/data_cate_goods')
def data_cate_goods():
    i = 1
    size = 100
    while i <= 2:
        page = (i-1) * size
        
        sql_str = 'select xc.id, reply from xd_data1 as xd left join xd_cate as xc on xd.old_id = xc.old_id limit ' + str(page) + ', ' + str(size)

        dataa = dbconn.query(
            sql=sql_str
        )
        
        for v in dataa:
            rows = json.loads(v['reply'])
            
            if rows['data']['treatment'] != "":
                data1 = {
                    'cate_id': v['id'],
                    'treatment': rows['data']['treatment']
                }
                dbconn.insert(table='xd_cate_treatment_msg', data=data1)
            
            if len(rows['data']['commonList']) == 0 :
                continue
            
            for val2 in rows['data']['commonList']:
                
                goods_name = ''
                for val4 in val2['goodsCenterList']:
                    goods_name += val4['goodsName'] + ','
                
                goods_name = goods_name.strip(',')
                
                treatment_id = get_xd_treatment(val2['title'],val2['introduce'],goods_name,val2['id'])
                
                data2 = {
                    'treatment_id': treatment_id,
                    'cate_id': v['id']
                }
                dbconn.insert(table='xd_cate_treatment', data=data2)
                        
                for val3 in val2['goodsCenterList']:
                    rows3 = dbconn.fetch_rows(
                        table='xd_goods',
                        fields='id',
                        condition={'goods_no': val3['goodsCenterNo']},
                        fetchone=True
                    )
                    if rows is None:
                        continue
                    else:
                        data3 = {
                            'treatment_id': treatment_id,
                            'goods_id': rows3['id']
                        }
                        dbconn.insert(table='xd_treatment_goods', data=data3)
                
        i+=1
        
    return "1"


def get_xd_treatment(name,introduce,goods_name,old_id):
    cond_b = {'name': name,'introduce':introduce,'goods_name':goods_name,'old_id':old_id}
    rows = dbconn.fetch_rows(
        table='xd_treatment',
        fields='id, name',
        condition={'old_id': old_id},
        fetchone=True
    )

    if rows is None:
        return dbconn.insert(table='xd_treatment', data=cond_b)
    else:
        return rows['id']


@bp.route('/data_goods')
def data_goods():
    i = 1
    size = 100
    while i <= 24:
        page = (i-1) * size
        
        sql_str = 'select reply from xd_data2 limit ' + str(page) + ', ' + str(size)

        dataa = dbconn.query(
            sql=sql_str
        )
        
        for v in dataa:
            rows = json.loads(v['reply'])

            for val1 in rows['data']['list']:
                data2 = {
                    'name': val1['name'],
                    'goods_no': val1['goodsNo'],
                    'brand_name': val1['brandName'],
                    'specification': val1['specification'],
                    'category_names': ','.join(val1['categoryNames'])
                }
                dbconn.insert(table='xd_goods', data=data2)
        i+=1
        
    return "1"


@bp.route('/rep_xd_data2')
def rep_xd_data2():
    i = 1
    while i <= 2333:
        url = 'https://his.betacat.co/api/pet/v1/admin/goods/center/list?categoryId=0&pageSize=10&pageIndex='+str(i)+'&categoryName=&searchKeys=&activeStatus=-1'

        headers = {
            "Authorization": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJicm93c2VyS2V5IjoiMTI4Mzg4ZTAtZjVjNC0xMWVmLTkzYzctODc1MWVmMDg4YWU0IiwiY3JlYXRlZEF0IjoiMjAyNS0wMi0yOCAxOTowNjo0OCIsImVtcGxveWVlTm8iOiIyMzA2MzE2NTUxNzQ4MzYyMjQiLCJleHBpcmVkQXQiOiIyMDI1LTAzLTMwIDE5OjA2OjQ4IiwiaG9zcGl0YWxFeHBpcmVkQXQiOiIyMDI1LTA3LTI3IDIzOjU5OjU5IiwiaG9zcGl0YWxObyI6IjIwMjMwNzA0MjE1MjQxMzk5MDAxMDAxMyIsImhvc3BpdGFsVGl0bGUiOiLov4XlvrflrqDnianljLvpmaIiLCJuYW1lIjoi5byg5ZCJ5pyLIiwicGVybWlzc2lvbkxpc3QiOltdLCJwaG9uZSI6IjE3NzgzMzE2MTI3Iiwic2hvcE5vIjoiMjAyMzAyMDYxNTE5MDYxNjQwMDEwMDAxIiwic2hvcE5vcyI6WyIyMDIzMDIwNjE1MTkwNjE2NDAwMTAwMDEiXSwic2hvcFJvbGVJRHMiOls3MCw3MCw3MF0sInNob3BSb2xlTGlzdCI6W3siSUQiOjcwLCJUaXRsZSI6IueuoeeQhuWRmCJ9XSwic2hvcFRpdGxlIjoi6L-F5b635a6i5pyNIn0.QfcWmQD2btrAZygVgkNbaV4naDkLLhkqaNOZB3FsINo",
            "Cookie": "loginToken=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJicm93c2VyS2V5IjoiMTI4Mzg4ZTAtZjVjNC0xMWVmLTkzYzctODc1MWVmMDg4YWU0IiwiY3JlYXRlZEF0IjoiMjAyNS0wMi0yOCAxOTowNjo0OCIsImVtcGxveWVlTm8iOiIyMzA2MzE2NTUxNzQ4MzYyMjQiLCJleHBpcmVkQXQiOiIyMDI1LTAzLTMwIDE5OjA2OjQ4IiwiaG9zcGl0YWxFeHBpcmVkQXQiOiIyMDI1LTA3LTI3IDIzOjU5OjU5IiwiaG9zcGl0YWxObyI6IjIwMjMwNzA0MjE1MjQxMzk5MDAxMDAxMyIsImhvc3BpdGFsVGl0bGUiOiLov4XlvrflrqDnianljLvpmaIiLCJuYW1lIjoi5byg5ZCJ5pyLIiwicGVybWlzc2lvbkxpc3QiOltdLCJwaG9uZSI6IjE3NzgzMzE2MTI3Iiwic2hvcE5vIjoiMjAyMzAyMDYxNTE5MDYxNjQwMDEwMDAxIiwic2hvcE5vcyI6WyIyMDIzMDIwNjE1MTkwNjE2NDAwMTAwMDEiXSwic2hvcFJvbGVJRHMiOls3MCw3MCw3MF0sInNob3BSb2xlTGlzdCI6W3siSUQiOjcwLCJUaXRsZSI6IueuoeeQhuWRmCJ9XSwic2hvcFRpdGxlIjoi6L-F5b635a6i5pyNIn0.QfcWmQD2btrAZygVgkNbaV4naDkLLhkqaNOZB3FsINo",
            "content-type": "application/json",
            "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:123.0) Gecko/20100101 Firefox/123.0"
        }

        # params = {"list": [val3['name']]}

        result = requests.request('GET', url=url, headers=headers)
        # result = requests.request('POST', url=url, headers=headers, json=params)

        # rows = json.loads(result.text)
        print(i)

        data2 = {
            'old_id': i,
            'req': url,
            'reply': result.text
        }
        dbconn.insert(table='xd_data2', data=data2)
        
        i+=1

    return '1'


@bp.route('/data_ch_cate')
def data_ch_cate():
    sql_str = 'select keyword from report_keyword where `type`= 5 group by keyword'

    rows = dbconn2.query(
        sql=sql_str
    )

    for val1 in rows:
        data2 = {
            'name': replace_self(val1['keyword'])
        }
        dbconn.insert(table='ch_cate', data=data2)
        
    return "1"
        
            
# update xd_cate set is_set = 0 where id not in (select xd_cate_id from `xd_cate_info` group by xd_cate_id)
# update xd_cate set is_set = 0 where id not in (select cate_id from `xd_cate_treatment` group by cate_id)
@bp.route('/rep_xd_data1')
def rep_xd_data1():
    category3 = dbconn.query(
        sql='SELECT id, `name`, old_id FROM xd_cate where id > 0 and is_set = 0'
    )

    for val3 in category3:
        # time.sleep(random.randint(1, 5))
        url = 'https://his.betacat.co/api/pet/v1/config/preDiagnosis/'+str(val3['old_id'])

        headers = {
            "Authorization": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJicm93c2VyS2V5IjoiMTI4Mzg4ZTAtZjVjNC0xMWVmLTkzYzctODc1MWVmMDg4YWU0IiwiY3JlYXRlZEF0IjoiMjAyNS0wMi0yOCAxOTowNjo0OCIsImVtcGxveWVlTm8iOiIyMzA2MzE2NTUxNzQ4MzYyMjQiLCJleHBpcmVkQXQiOiIyMDI1LTAzLTMwIDE5OjA2OjQ4IiwiaG9zcGl0YWxFeHBpcmVkQXQiOiIyMDI1LTA3LTI3IDIzOjU5OjU5IiwiaG9zcGl0YWxObyI6IjIwMjMwNzA0MjE1MjQxMzk5MDAxMDAxMyIsImhvc3BpdGFsVGl0bGUiOiLov4XlvrflrqDnianljLvpmaIiLCJuYW1lIjoi5byg5ZCJ5pyLIiwicGVybWlzc2lvbkxpc3QiOltdLCJwaG9uZSI6IjE3NzgzMzE2MTI3Iiwic2hvcE5vIjoiMjAyMzAyMDYxNTE5MDYxNjQwMDEwMDAxIiwic2hvcE5vcyI6WyIyMDIzMDIwNjE1MTkwNjE2NDAwMTAwMDEiXSwic2hvcFJvbGVJRHMiOls3MCw3MCw3MF0sInNob3BSb2xlTGlzdCI6W3siSUQiOjcwLCJUaXRsZSI6IueuoeeQhuWRmCJ9XSwic2hvcFRpdGxlIjoi6L-F5b635a6i5pyNIn0.QfcWmQD2btrAZygVgkNbaV4naDkLLhkqaNOZB3FsINo",
            "Cookie": "loginToken=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJicm93c2VyS2V5IjoiMTI4Mzg4ZTAtZjVjNC0xMWVmLTkzYzctODc1MWVmMDg4YWU0IiwiY3JlYXRlZEF0IjoiMjAyNS0wMi0yOCAxOTowNjo0OCIsImVtcGxveWVlTm8iOiIyMzA2MzE2NTUxNzQ4MzYyMjQiLCJleHBpcmVkQXQiOiIyMDI1LTAzLTMwIDE5OjA2OjQ4IiwiaG9zcGl0YWxFeHBpcmVkQXQiOiIyMDI1LTA3LTI3IDIzOjU5OjU5IiwiaG9zcGl0YWxObyI6IjIwMjMwNzA0MjE1MjQxMzk5MDAxMDAxMyIsImhvc3BpdGFsVGl0bGUiOiLov4XlvrflrqDnianljLvpmaIiLCJuYW1lIjoi5byg5ZCJ5pyLIiwicGVybWlzc2lvbkxpc3QiOltdLCJwaG9uZSI6IjE3NzgzMzE2MTI3Iiwic2hvcE5vIjoiMjAyMzAyMDYxNTE5MDYxNjQwMDEwMDAxIiwic2hvcE5vcyI6WyIyMDIzMDIwNjE1MTkwNjE2NDAwMTAwMDEiXSwic2hvcFJvbGVJRHMiOls3MCw3MCw3MF0sInNob3BSb2xlTGlzdCI6W3siSUQiOjcwLCJUaXRsZSI6IueuoeeQhuWRmCJ9XSwic2hvcFRpdGxlIjoi6L-F5b635a6i5pyNIn0.QfcWmQD2btrAZygVgkNbaV4naDkLLhkqaNOZB3FsINo",
            "content-type": "application/json",
            "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:123.0) Gecko/20100101 Firefox/123.0"
        }

        # params = {"list": [val3['name']]}

        result = requests.request('GET', url=url, headers=headers)
        # result = requests.request('POST', url=url, headers=headers, json=params)

        # rows = json.loads(result.text)
        print(url)

        cond = {'id': val3['id']}
        data_end = {'is_set': 1}
        dbconn.update(table='xd_cate', data=data_end, condition=cond)

        data2 = {
            'id':val3['id'],
            'old_id': val3['old_id'],
            'req': url,
            'reply': result.text
        }
        dbconn.insert(table='xd_data1', data=data2)
        # if len(rows['data']['commonList']) == 0:
        #     continue

        # for row in rows['data']['commonList']:
            

    return '1'


@bp.route('/data_xd_goods_cate')
def data_xd_goods_cate():
    json_data = json.load(open("xd_goods_cate.json"))
    
    goods_cate(json_data['data']['list'],0,1)
    
    return "1"


def goods_cate(child, pid, children):
    for val in child:
        data3 = {
            'old_id':val['id'],
            'pid': pid,
            'name': val['title'],
            'children': children
        }
        cateid = dbconn.insert(table='xd_goods_cate', data=data3)
        if len(val['child']) == 0:
            continue
        else:
            goods_cate(val['child'], cateid, children + 1)
        
@bp.route('/rep_xd_goods_bat')
def rep_xd_goods_bat():
    res = dbconn.query(
        sql='SELECT xd_cate_id FROM xd_cate_info where id > 11625 group by xd_cate_id'
    )
    
    for v in res :
        cond = {'id': v['xd_cate_id']}
        data_end = {'is_has': 1}
        dbconn.update(table='xd_cate', data=data_end, condition=cond)
    
    return "1"
    
@bp.route('/rep_xd_goods')
def rep_xd_goods():
    category3 = dbconn.query(
        sql='SELECT id, `name` FROM xd_cate where id > 0 and is_set = 0 and is_has = 0'
    )

    for val3 in category3:
        time.sleep(random.randint(1, 5))
        url = 'https://his.betacat.co/api/pet/v1/medical/book/query'
        # url = 'https://medical.bk-pet.cn/common/getRedisData'

        headers = {
            "Authorization": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJicm93c2VyS2V5IjoiZjY2Y2VmMjAtN2IwMi0xMWVmLWFkYjAtMWJkYjg2OGRmYWJmIiwiY3JlYXRlZEF0IjoiMjAyNC0wOS0yNSAxNTowMjo0NSIsImVtcGxveWVlTm8iOiIyMzI1NTc5NTIxNzgzMjc1NTIiLCJleHBpcmVkQXQiOiIyMDI0LTEwLTI1IDE1OjAyOjQ1IiwiaG9zcGl0YWxFeHBpcmVkQXQiOiIyMDI1LTA3LTI3IDIzOjU5OjU5IiwiaG9zcGl0YWxObyI6IjIwMjMwNzA0MjE1MjQxMzk5MDAxMDAxMyIsImhvc3BpdGFsVGl0bGUiOiLov4XlvrflrqDnianljLvpmaIiLCJuYW1lIjoi5biC5Zy6LeadjuiSmeiSmSIsInBlcm1pc3Npb25MaXN0IjpbXSwicGhvbmUiOiIxMzc3Njg1MDIwMSIsInNob3BObyI6IjIwMjMwMjA2MTUxOTA2MTY0MDAxMDAwMSIsInNob3BOb3MiOlsiMjAyMzAyMDYxNTE5MDYxNjQwMDEwMDAxIl0sInNob3BSb2xlSURzIjpbNzAsNzBdLCJzaG9wUm9sZUxpc3QiOlt7IklEIjo3MCwiVGl0bGUiOiLnrqHnkIblkZgifV0sInNob3BUaXRsZSI6Iui_heW-t-WuouacjSJ9.FXlBo6voiTYFFDOOPKRHFa5UUSGq-GtR__Ob6fXZDAo",
            "Cookie": "Hm_lvt_58a76bea9bf2c966c440612843fe56ef=1727241030; HMACCOUNT=8764FB1D381CED8F; status=false; loginToken=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJicm93c2VyS2V5IjoiZjY2Y2VmMjAtN2IwMi0xMWVmLWFkYjAtMWJkYjg2OGRmYWJmIiwiY3JlYXRlZEF0IjoiMjAyNC0wOS0yNSAxNTowMjo0NSIsImVtcGxveWVlTm8iOiIyMzI1NTc5NTIxNzgzMjc1NTIiLCJleHBpcmVkQXQiOiIyMDI0LTEwLTI1IDE1OjAyOjQ1IiwiaG9zcGl0YWxFeHBpcmVkQXQiOiIyMDI1LTA3LTI3IDIzOjU5OjU5IiwiaG9zcGl0YWxObyI6IjIwMjMwNzA0MjE1MjQxMzk5MDAxMDAxMyIsImhvc3BpdGFsVGl0bGUiOiLov4XlvrflrqDnianljLvpmaIiLCJuYW1lIjoi5biC5Zy6LeadjuiSmeiSmSIsInBlcm1pc3Npb25MaXN0IjpbXSwicGhvbmUiOiIxMzc3Njg1MDIwMSIsInNob3BObyI6IjIwMjMwMjA2MTUxOTA2MTY0MDAxMDAwMSIsInNob3BOb3MiOlsiMjAyMzAyMDYxNTE5MDYxNjQwMDEwMDAxIl0sInNob3BSb2xlSURzIjpbNzAsNzBdLCJzaG9wUm9sZUxpc3QiOlt7IklEIjo3MCwiVGl0bGUiOiLnrqHnkIblkZgifV0sInNob3BUaXRsZSI6Iui_heW-t-WuouacjSJ9.FXlBo6voiTYFFDOOPKRHFa5UUSGq-GtR__Ob6fXZDAo; Hm_lpvt_58a76bea9bf2c966c440612843fe56ef=1727247779",
            "content-type": "application/json",
            "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:123.0) Gecko/20100101 Firefox/123.0"
        }

        params = {"list": [val3['name']]}

        result = requests.request('POST', url=url, headers=headers, json=params)

        rows = json.loads(result.text)

        cond = {'id': val3['id']}
        data_end = {'is_set': 1}
        dbconn.update(table='xd_cate', data=data_end, condition=cond)

        if len(rows['data']['list']) == 0:
            continue

        for row in rows['data']['list'][0]['titles']:
            data2 = {
                'xd_cate_id': val3['id'],
                'title': row['title'],
                'content': row['content']
            }
            dbconn.insert(table='xd_cate_info', data=data2)

    return '1'


@bp.route('/data_xd3')
def data_xd3():
    category3 = dbconn.query(
        sql='SELECT id, parent_id, `name`, description, examintions, remarks, prognosis, adjuvanttherapy FROM lab_category where id > 0 and children = 3'
    )

    for val3 in category3:
        data1 = dbconn.query(
            sql='SELECT id, `name`, children FROM xd_cate where `name` = \'' + val3['name'] + '\''
        )

        if len(data1) == 0:
            continue

        data2 = {
            'cate_id': int(val3['id']),
            'xd_id': int(data1[0]['id'])
        }
        dbconn.insert(table='illness_xd_cate', data=data2)

    return "1"


@bp.route('/data_symptoms2')
def data_symptoms2():
    category3 = dbconn.query(
        sql='SELECT id, `name`, lab_category_id FROM lab_clinical where id > 0 '
    )

    for val3 in category3:
        sid = get_symptoms(val3['name'])

        data1 = dbconn.query(
            sql='SELECT id, `cate_id`,symptoms_id FROM illness_symptoms where symptoms_id = ' + str(
                sid) + ' AND cate_id = ' + str(val3['lab_category_id'])
        )

        if len(data1) > 0:
            continue

        data2 = {
            'cate_id': val3['lab_category_id'],
            'symptoms_id': sid
        }
        dbconn.insert(table='illness_symptoms', data=data2)

        # data1 = dbconn.query(
        #     sql='SELECT id, `cate_id`,symptoms_id FROM illness_symptoms where symptoms_id = ' + sid + ' AND cate_id = '
        # )

        #####
        # if len(name) > 1:
        #     print(val3['id'])
        #     dbconn.delete(table='symptoms', condition={'id': val3['id']})
        #     dbconn.delete(table='illness_symptoms', condition={'symptoms_id': val3['id']})

        ####
        # data1 = dbconn.query(
        #     sql='SELECT id, `cate_id`,symptoms_id FROM illness_symptoms where symptoms_id = ' + str(val3['id'])
        # )
        # for val_n in name:
        #     sid = get_symptoms(val_n)
        #
        #     for val1 in data1:
        #         data2 = {
        #             'cate_id': val1['cate_id'],
        #             'symptoms_id': sid
        #         }
        #         dbconn.insert(table='illness_symptoms', data=data2)

    return "1"


@bp.route('/data_ch4')
def data_ch4():
    messages = []
    # 读取旧的JSONL文件
    with open("./alpaca_gpt4_data_zh.json", "r") as file:
        data = json.load(file)
        for v in data:
            instruction = v["instruction"]
            input = v["input"]
            output = v["output"]
            message = {
                "instruction": instruction,
                "input": input,
                "output": output,
            }
            messages.append(message)

    # 保存重构后的JSONL文件
    with open("./gpt4_data_zh", "w", encoding="utf-8") as file:
        for message in messages:
            file.write(json.dumps(message, ensure_ascii=False) + "\n")
            

    return "1"


@bp.route('/data_ch3')
def data_ch3():
    messages = []
    # 读取旧的JSONL文件
    with open("/Users/haoyanbin/Downloads/alpaca_gpt4_data_zh.json", "r") as file:
        data = json.load(file)
        for v in data:
            instruction = v["instruction"]
            input = v["input"]
            output = v["output"]
            message = {
                "instruction": instruction,
                "input": input,
                "output": output,
            }
            messages.append(message)

    # 保存重构后的JSONL文件
    with open("./gpt4_data_zh", "w", encoding="utf-8") as file:
        for message in messages:
            file.write(json.dumps(message, ensure_ascii=False) + "\n")
            

    return "1"

@bp.route('/data_ch2')
def data_ch2():
    
    messages = []
    
    i = 0
    size = 500
    while i <= 30:
        page = i * size

        #' left join report_main_status as rms on rm.orgin_id=rms.report_main_orgin_id' \
        sql_str = 'SELECT breed, chief_complaint, phys_examination, suspected_disease, treatment, operation_record, doctors_advice, return_visit ' \
                  ' FROM ch_data2 ' \
                  ' WHERE chief_complaint != "" and suspected_disease != "" and treatment != "" ' \
                  ' limit ' + str(page) + ', ' + str(size)

        rows = dbconn.query(
            sql=sql_str
        )
        
        if len(rows) == 0:
            break

        i += 1

        for v in rows:
            chief_complaint = v['chief_complaint'].strip('，').strip('。')
            # phys_examinations = json.loads(v['phys_examination'])
            # phys_examination = ''
            # for v2 in phys_examinations:
            #     if phys_examinations[v2] is None:
            #         continue
            #     if phys_examinations[v2]['Description'] != '':
            #         phys_examination += phys_examinations[v2]['Description']+','
                    
            # phys_examination = phys_examination.strip(',')
            
            instruction = '现在你是一名宠物医生，请根据病情描述给出诊断及治疗方案'
            # if v['breed'] != '':
            #     instruction += v['breed']+'。'
                
            # instruction += replace_self(chief_complaint)
            
            # if phys_examination != '':
                # instruction += phys_examination+'。'
                
            input = replace_self(chief_complaint)
            suspected_diseases = v['suspected_disease'].split(' ')
            if len(suspected_diseases) <= 1:
                suspected_diseases = v['suspected_disease'].split('，')
                
            suspected_disease = '，'.join(suspected_diseases)
            output = suspected_disease+'。'+v['treatment']+'。'
            
            if v['doctors_advice'] != '':
                output += v['doctors_advice']
                
            output = replace_self(output)
            
            data1 = {
                'instruction': instruction,
                'input': input,
                'output': output
            }
            
            messages.append(data1)
            
            # dbconn.insert(table='llama_data', data=data1)
        
    with open('./med_data2.jsonl', "w", encoding="utf-8") as file:
        for message in messages:
            file.write(json.dumps(message, ensure_ascii=False) + "\n")
    return '1'


@bp.route('/data_ch_res')
def data_ch_res():
    
    messages = []
    
    i = 0
    size = 500
    while i <= 30:
        page = i * size

        #' left join report_main_status as rms on rm.orgin_id=rms.report_main_orgin_id' \
        sql_str = 'SELECT id, species, breed, chief_complaint, phys_examination, suspected_disease, treatment, operation_record, doctors_advice ' \
                  ' FROM ch_data2 ' \
                  ' WHERE chief_complaint != "" and suspected_disease != "" and treatment != "" and id > 124' \
                  ' limit ' + str(page) + ', ' + str(size)

        rows = dbconn.query(
            sql=sql_str
        )
        
        if len(rows) == 0:
            break

        i += 1

        for v in rows:
            chief_complaint = v['chief_complaint'].strip('，').strip('。')
            
            phys_examination = ''
            try:
                phys_examinations = json.loads(v['phys_examination'])
            
                for v2 in phys_examinations:
                    if type(phys_examinations[v2]) == dict:
                        if phys_examinations[v2] is None:
                            continue
                        if phys_examinations[v2]['Description'] != '':
                            phys_examination += phys_examinations[v2]['Description']+','
                    
                phys_examination = phys_examination.strip(',')
            
            except Exception as e:
                print(v['id'])
            
            # instruction = '现在你是一名宠物医生，请根据病情描述给出诊断及治疗方案'
            # if v['breed'] != '':
            #     instruction += v['breed']+'。'
                
            # instruction += replace_self(chief_complaint)
            
            # if phys_examination != '':
                # instruction += phys_examination+'。'
                
            chief_complaint = replace_self(chief_complaint)
            
            suspected_diseases = replace_self(v['suspected_disease'])
            # if len(suspected_diseases) <= 1:
            #     suspected_diseases = v['suspected_disease'].split('，')
                
            # suspected_disease = '，'.join(suspected_diseases)
            # output = suspected_disease+'。'+v['treatment']+'。'
            
            # if v['doctors_advice'] != '':
            #     doctors_advice = v['doctors_advice']
                
            # output = replace_self(output)
            
            data1 = {
                'species': v['species'],
                'breed': v['breed'],
                'chief_complaint': chief_complaint,
                'phys_examination': phys_examination,
                'suspected_disease': suspected_diseases,
                'treatment': v['treatment'],
                'doctors_advice': v['doctors_advice'],
                'old_id':v['id']
            }
            
            # messages.append(data1)
            
            dbconn.insert(table='ch_data_res', data=data1)
        
    # with open('./med_data2.jsonl', "w", encoding="utf-8") as file:
    #     for message in messages:
    #         file.write(json.dumps(message, ensure_ascii=False) + "\n")
    return '1'


@bp.route('/data_ch_res2')
def data_ch_res2():
    
    i = 0
    size = 500
    while i <= 300:
        page = i * size

        sql_str = 'SELECT id, species, breed, chief_complaint, phys_examination, suspected_disease, treatment, operation_record, doctors_advice, diagnosis_summary ' \
                  ' FROM ch_data4 ' \
                  ' WHERE id > 150500' \
                  ' limit ' + str(page) + ', ' + str(size)

        rows = dbconn.query(
            sql=sql_str
        )
        
        if len(rows) == 0:
            break

        i += 1

        for v in rows:
            chief_complaint = v['chief_complaint'].strip('，').strip('。')
            
            phys_examination = ''
            try:
                phys_examinations = json.loads(v['phys_examination'])
            
                for v2 in phys_examinations:
                    if type(phys_examinations[v2]) == dict:
                        if phys_examinations[v2] is None:
                            continue
                        if phys_examinations[v2]['Description'] != '':
                            phys_examination += phys_examinations[v2]['Description']+','
                    
                phys_examination = phys_examination.strip(',')
            
            except Exception as e:
                print(v['id'])
                
            chief_complaint = replace_self(chief_complaint)
            
            suspected_diseases = replace_self(v['suspected_disease'])

            data1 = {
                'species': v['species'],
                'breed': v['breed'],
                'chief_complaint': chief_complaint,
                'phys_examination': phys_examination,
                'suspected_disease': suspected_diseases,
                'treatment': v['treatment'],
                'doctors_advice': v['doctors_advice'],
                'old_id':v['id']
            }
            
            if data1['suspected_disease'] == '':
                data1['suspected_disease'] = v['diagnosis_summary']
            
            dbconn.insert(table='ch_data_res2', data=data1)
    
    return '1'


@bp.route('/data_ch')
def data_ch():
    old_id = '1'
    i = 0
    size = 500
    while i <= 31:
        page = i * size

        #' left join report_main_status as rms on rm.orgin_id=rms.report_main_orgin_id' \
        # sql_str = 'SELECT rm.id, rm.orgin_id, rp.species, rp.breed, rp.patient_gender_code, rp.weight, rp.is_sterilization, rp.chief_complaint, rp.phys_examination, rp.suspected_disease, rp.treatment, rp.operation_record, rp.doctors_advice, rp.return_visit ' \
        #           ' FROM ch_report_main as rm ' \
        #           ' left join ch_report_patient as rp on rm.orgin_id=rp.report_main_orgin_id ' \
        #           ' where rm.valid_status = 1 and rm.visit_status = 1 and rm.id >' + old_id + \
        #           ' limit ' + str(page) + ', ' + str(size)
        
        sql_str = 'SELECT rm.id, rm.orgin_id, rp.species, rp.breed, rp.patient_gender_code, rp.weight, rp.is_sterilization, rp.chief_complaint, rp.phys_examination, rp.suspected_disease, rp.treatment, rp.operation_record, rp.doctors_advice, rp.return_visit ' \
                  ' FROM ch_report_main as rm ' \
                  ' left join ch_report_patient as rp on rm.orgin_id=rp.report_main_orgin_id ' \
                  ' where rp.chief_complaint != "" and rp.suspected_disease !="" and rp.treatment !="" and rm.id >' + old_id + \
                  ' limit ' + str(page) + ', ' + str(size)

        rows = dbconn2.query(
            sql=sql_str
        )

        i += 1

        for val1 in rows:
            data1 = {
                'orgin_id': val1['orgin_id'],
                'species': val1['species'],
                'breed': val1['breed'],
                'patient_gender_code': val1['patient_gender_code'],
                'weight': val1['weight'],
                'is_sterilization': val1['is_sterilization'],
                'chief_complaint': val1['chief_complaint'],
                'phys_examination': val1['phys_examination'],
                'suspected_disease': val1['suspected_disease'],
                'treatment': val1['treatment'],
                'operation_record': val1['operation_record'],
                'doctors_advice': val1['doctors_advice'],
                'return_visit': val1['return_visit'],
                'old_id': val1['id']
            }
            
            dbconn.insert(table='ch_data2', data=data1)

            # sql_str2 = "SELECT `type`, keyword  FROM report_keyword where report_main_orgin_id = '" + val1[
            #     'orgin_id'] + "'"

            # rows2 = dbconn2.query(
            #     sql=sql_str2
            # )

            # # 1 主诉 2 诊断报告 3 影像报告 4 分析结果 5 病情诊断 6 治疗方案 7 医嘱
            # for val2 in rows2:
            #     if val2['type'] == 7:
            #         data1['doctors_advice_data'] += val2['keyword'] + ','

            #     if val2['type'] == 5:
            #         data1['illness_data'] += val2['keyword'] + ','

            #     # 主诉-病症
            #     if val2['type'] == 1:
            #         data2 = {
            #             'illness_id': val2['id'],
            #             'name': val2['keyword']
            #         }
            #         dbconn.insert(table='ch_symptoms', data=data2)

            #     # 主诉-病症
            #     if val2['type'] == 6:
            #         data2 = {
            #             'illness_id': val2['id'],
            #             'name': val2['keyword']
            #         }
            #         dbconn.insert(table='ch_treatment', data=data2)

            # dbconn.insert(table='ch_illness', data=data1)

    return '1'


@bp.route('/rep_xd_cate')
def rep_xd_cate():
    category3 = dbconn.query(
        sql='SELECT id, `name` FROM xd_cate where id > 0 and is_set = 0'
    )

    for val3 in category3:
        time.sleep(random.randint(1, 5))
        url = 'https://his.betacat.co/api/pet/v1/medical/book/query'
        # url = 'https://medical.bk-pet.cn/common/getRedisData'

        headers = {
            "Authorization": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJicm93c2VyS2V5IjoiYmIyMzY2ZDAtZmQyOS0xMWVlLWE1NGItYzdkNzU2NWY0MzI2IiwiY3JlYXRlZEF0IjoiMjAyNC0wNC0xOCAxMDozMjo0MiIsImVtcGxveWVlTm8iOiIyMzI1NTc5NTIxNzgzMjc1NTIiLCJleHBpcmVkQXQiOiIyMDI0LTA1LTE4IDEwOjMyOjQyIiwiaG9zcGl0YWxFeHBpcmVkQXQiOiIyMDI0LTA3LTI3IDIzOjU5OjU5IiwiaG9zcGl0YWxObyI6IjIwMjMwNzA0MjE1MjQxMzk5MDAxMDAxMyIsImhvc3BpdGFsVGl0bGUiOiLov4XlvrflrqDnianljLvpmaIiLCJuYW1lIjoi5biC5Zy6LeadjuiSmeiSmSIsInBlcm1pc3Npb25MaXN0IjpbXSwicGhvbmUiOiIxMzc3Njg1MDIwMSIsInNob3BObyI6IjIwMjMwMjA2MTUxOTA2MTY0MDAxMDAwMSIsInNob3BOb3MiOlsiMjAyMzAyMDYxNTE5MDYxNjQwMDEwMDAxIl0sInNob3BSb2xlSURzIjpbNzAsNzBdLCJzaG9wUm9sZUxpc3QiOlt7IklEIjo3MCwiVGl0bGUiOiLnrqHnkIblkZgifV0sInNob3BUaXRsZSI6Iui_heW-t-WuouacjSJ9.UCVLZnjOOvoeRn6H6KV2K6sxbzZNQUwPCoM1OtFaYUI",
            "Cookie": "loginToken=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJicm93c2VyS2V5IjoiYmIyMzY2ZDAtZmQyOS0xMWVlLWE1NGItYzdkNzU2NWY0MzI2IiwiY3JlYXRlZEF0IjoiMjAyNC0wNC0xOCAxMDozMjo0MiIsImVtcGxveWVlTm8iOiIyMzI1NTc5NTIxNzgzMjc1NTIiLCJleHBpcmVkQXQiOiIyMDI0LTA1LTE4IDEwOjMyOjQyIiwiaG9zcGl0YWxFeHBpcmVkQXQiOiIyMDI0LTA3LTI3IDIzOjU5OjU5IiwiaG9zcGl0YWxObyI6IjIwMjMwNzA0MjE1MjQxMzk5MDAxMDAxMyIsImhvc3BpdGFsVGl0bGUiOiLov4XlvrflrqDnianljLvpmaIiLCJuYW1lIjoi5biC5Zy6LeadjuiSmeiSmSIsInBlcm1pc3Npb25MaXN0IjpbXSwicGhvbmUiOiIxMzc3Njg1MDIwMSIsInNob3BObyI6IjIwMjMwMjA2MTUxOTA2MTY0MDAxMDAwMSIsInNob3BOb3MiOlsiMjAyMzAyMDYxNTE5MDYxNjQwMDEwMDAxIl0sInNob3BSb2xlSURzIjpbNzAsNzBdLCJzaG9wUm9sZUxpc3QiOlt7IklEIjo3MCwiVGl0bGUiOiLnrqHnkIblkZgifV0sInNob3BUaXRsZSI6Iui_heW-t-WuouacjSJ9.UCVLZnjOOvoeRn6H6KV2K6sxbzZNQUwPCoM1OtFaYUI",
            "content-type": "application/json",
            "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:123.0) Gecko/20100101 Firefox/123.0"
        }

        params = {"list": [val3['name']]}

        result = requests.request('POST', url=url, headers=headers, json=params)

        rows = json.loads(result.text)

        cond = {'id': val3['id']}
        data_end = {'is_set': 1}
        dbconn.update(table='xd_cate', data=data_end, condition=cond)

        if len(rows['data']['list']) == 0:
            continue

        for row in rows['data']['list'][0]['titles']:
            data2 = {
                'xd_cate_id': val3['id'],
                'title': row['title'],
                'content': row['content']
            }
            dbconn.insert(table='xd_cate_info', data=data2)

    return '1'


@bp.route('/data_xd_cate', methods=['POST', 'GET'])
def data_xd_cate():
    cate_data = json.load(open('catej.json'))

    data_set_cate(cate_data['data']['list'], 0, 1)

    return '2'


def data_set_cate(child, pid, children):
    for val in child:
        data3 = {
            'old_id':val['id'],
            'pid': pid,
            'name': val['title'],
            'children': children
        }
        cateid = dbconn.insert(table='xd_cate', data=data3)
        if len(val['child']) == 0:
            continue
        else:
            data_set_cate(val['child'], cateid, children + 1)


@bp.route('/data_keyword_clinical', methods=['POST', 'GET'])
def data_keyword_clinical():
    sql_str = 'SELECT `name`, keyword FROM symptom where children = 3'

    rows = dbconn.query(
        sql=sql_str
    )

    for val in rows:
        keyword = val['keyword'].split('|')

        for val2 in keyword:
            data3 = {
                'keyword': val2,
                'clinical': val['name']
            }
            dbconn.insert(table='keyword_clinical', data=data3)

    return '2'


@bp.route('/data_trmed')
def data_trmed():
    data = dbconn.query(
        sql='SELECT id, `name`, `medicinal_data` FROM treatment where id > 0'
    )

    for val in data:
        res = val['medicinal_data'].split('、')
        
        drug_keyword = ''
        
        for val2 in res:
            if val2 == "":
                continue
            
            drug = dbconn.query(
                sql='SELECT id, `name` FROM drug where id > 0 and `name` like \'%' + val2 + '%\''
            )

            if len(drug) > 0:
                
                for val4 in drug:
                    data3 = {
                        'treatment_id': val['id'],
                        'drug_id': val4['id']
                    }
                    dbconn.insert(table='treatment_drug', data=data3)
                    drug_keyword += val4['name'] + ','

                
        drug_keyword = drug_keyword.strip(',')
        data_end = {'drug_data': drug_keyword}
        cond = {'id': val['id']}
        
        dbconn.update(table='treatment', data=data_end, condition=cond)
    return '1'


@bp.route('/data_symptoms')
def data_symptoms():
    data = dbconn.query(
        sql='SELECT id, cate_id, `data` FROM illness_data where id > 0'
    )

    for val in data:
        res = val['data'].split('、')

        for val2 in res:
            sid = get_symptoms(val2)

            data2 = {
                'cate_id': val['cate_id'],
                'symptoms_id': sid
            }
            dbconn.insert(table='illness_symptoms', data=data2)


@bp.route('/data_drugkey')
def data_drugkey():
    category3 = dbconn.query(
        sql='SELECT id, `name` FROM medicinal where id > 0'
    )

    for val3 in category3:
        drug = dbconn.query(
            sql='SELECT id, `drug_name` FROM drug where id > 0 and drug_name like \'%' + val3['name'] + '%\''
        )

        if len(drug) > 0:
            drug_keyword = ''
            for val4 in drug:
                drug_keyword += val4['drug_name'] + ','

            drug_keyword = drug_keyword.strip(',')
            data_end = {'drug_name': drug_keyword}
            cond = {'id': val3['id']}
            dbconn.update(table='medicinal', data=data_end, condition=cond)

    return '1'


@bp.route('/data_keywords')
def data_keywords():
    category3 = dbconn.query(
        sql='SELECT id, parent_id, `name`, description, examintions, remarks, prognosis, adjuvanttherapy FROM lab_category where id > 0 and children = 3'
    )

    for val3 in category3:
        # drug_stock = dbconn.query(
        #     sql='SELECT id, useto_keyword FROM drug_stock where useto like \'%' + val3['name'] + '%\''
        # )
        #
        # if len(drug_stock) > 0:
        #     for val4 in drug_stock:
        #         if val4['useto_keyword'] == '':
        #             useto_keyword = val3['name']
        #         else:
        #             useto_keyword =val4['useto_keyword']+','+val3['name']
        #
        #         data_end = {'useto_keyword': useto_keyword}
        #         cond = {'id': val4['id']}
        #         dbconn.update(table='drug_stock', data=data_end, condition=cond)

        # drug_stock2 = dbconn.query(
        #     sql='SELECT id, indication_keyword FROM drug_stock where indication like \'%' + val3['name'] + '%\''
        # )
        #
        # # print(len(drug_stock))
        # if len(drug_stock2) > 0:
        #     for val4 in drug_stock2:
        #         if val4['indication_keyword'] == '':
        #             indication_keyword = val3['name']
        #         else:
        #             indication_keyword = val4['indication_keyword'] + ',' + val3['name']
        #
        #         data_end = {'indication_keyword': indication_keyword}
        #         cond = {'id': val4['id']}
        #         dbconn.update(table='drug_stock', data=data_end, condition=cond)

        # drug_stock = dbconn.query(
        #     sql='SELECT id, useto_keyword FROM vp_codex where useto like \'%' + val3['name'] + '%\''
        # )
        #
        # if len(drug_stock) > 0:
        #     for val4 in drug_stock:
        #         if val4['useto_keyword'] == '':
        #             useto_keyword = val3['name']
        #         else:
        #             useto_keyword =val4['useto_keyword']+','+val3['name']
        #
        #         data_end = {'useto_keyword': useto_keyword}
        #         cond = {'id': val4['id']}
        #         dbconn.update(table='vp_codex', data=data_end, condition=cond)
        drug_stock = dbconn.query(
            sql='SELECT id, keyword FROM disease where disease_name like \'%' + val3['name'] + '%\''
        )

        if len(drug_stock) > 0:
            for val4 in drug_stock:
                if val4['keyword'] == '':
                    keyword = val3['name']
                else:
                    keyword = val4['keyword'] + ',' + val3['name']

                data_end = {'keyword': keyword}
                cond = {'id': val4['id']}
                dbconn.update(table='disease', data=data_end, condition=cond)
    return '1'


@bp.route('/rep_treatment')
def rep_treatment():
    category3 = dbconn.query(
        sql='SELECT id, parent_id, `name`, description, examintions, remarks, prognosis, adjuvanttherapy, third_id FROM lab_category where id > 0 and children = 3'
    )

    for val3 in category3:
        url = 'http://www.aipets.com.cn/Home/DisReportlc?id=' + str(val3['third_id'])

        result = requests.get(url=url)

        soup = BeautifulSoup(result.text, 'html.parser')

        rows = soup.find_all(string=re.compile("临床症状"))

        for row in rows:
            if len(row.parent.select('font')) > 0:
                illness = replace_self(row.parent.select('font')[0].string)
                cate = row.parent.select('font')[1].string

                data2 = {
                    'cate_id': val3['id'],
                    'third_id': val3['third_id'],
                    'illness': illness,
                    'cate': cate
                }
                dbconn.insert(table='illness_cate', data=data2)

    return '1'


@bp.route('/rep_treatment2')
def rep_treatment2():
    category3 = dbconn.query(
        sql='SELECT id, parent_id, `name`, description, examintions, remarks, prognosis, adjuvanttherapy, third_id FROM lab_category where id > 0 and children = 3'
    )

    for val3 in category3:
        # url = 'http://www.aipets.com.cn/Home/DisReportlc?id=' + val3['third_id']
        url = 'http://www.aipets.com.cn/Home/DisReportFa?id=' + str(val3['third_id'])
        # url = 'http://www.aipets.com.cn/Home/DisReportFa?id=3'

        result = requests.get(url=url)

        soup = BeautifulSoup(result.text, 'html.parser')

        # row = soup.find(string=re.compile("以上内容均为建议，所需处方药物，必须在执业兽医指导下使用。非处方药物，也最好在宠物医生指导下使用。"))
        rows = soup.select('.txtdls')

        # data = row.parent.next_sibling.text
        # illness = replace_self(row.parent.select('font')[0].string)
        for row in rows:
            if len(row.select('font')) > 0:
                continue

            if '临床上常用的治疗原则和可能的药物包括' in row.text:
                continue
            if '以上内容均为建议，所需处方药物' in row.text:
                continue
            if '治疗原则及药物使用' in row.text:
                continue
            if '黄家雨' in row.text:
                continue
            if '吴柏青' in row.text:
                continue

            data2 = {
                'cate_id': val3['id'],
                'third_id': val3['third_id'],
                'data': row.text,
            }
            dbconn.insert(table='illness_end', data=data2)

        # rows = soup.find_all(class_="txtdls")
        # ill_data = soup.find(string="通常有以下病症：").parent.select('font')[1].string
        #
        # data = {
        #     'cate_id': val3['id'],
        #     'third_id': val3['third_id'],
        #     'name': val3['name'],
        #     'data': ill_data
        # }
        # dbconn.insert(table='illness_data', data=data)
        #
        # rows = soup.find_all(string="治疗。可能会用到的药物有")
        # # rows = soup.select("p > font")
        # # a = rows.parent.name
        # # print(a)
        # for row in rows:
        #     # print(row.parent.select('font')[0])
        #     illness_name = replace_self(row.parent.select('font')[0].string)
        #     durg_name = row.parent.select('font')[1].string
        #
        #     data2 = {
        #         'cate_id': val3['id'],
        #         'third_id': val3['third_id'],
        #         'illness_name': illness_name,
        #         'drug_name': durg_name
        #     }
        #     dbconn.insert(table='illness_durg', data=data2)

    return '1'


@bp.route('/b')
def test2():
    #
    # category3 = dbconn.query(
    #     sql='SELECT id, parent_id, `name`, description, examintions, remarks, prognosis, adjuvanttherapy, third_id FROM lab_category where id > 0 and children = 3'
    # )
    #
    # for val3 in category3:
    #
    #     url = 'http://www.aipets.com.cn/Home/DisReportlc?id=' + val3['third_id']
    # url = 'https://www.aipets.com.cn/Home/DrugZstpDetTab?k=' + val['drug_name']
    # url = 'https://www.aipets.com.cn/Home/DrugZuoyongDet?k=' + val['disease_name']
    # update_pet_species(url)

    # print(soup.find_all("table","report-table"))
    # for val in soup.find_all("report-table"):
    #     print(val)

    # drug = dbconn.query(
    #     sql='SELECT d.id, d.drug_name,a.res FROM drug as d left join api as a on d.drug_name = a.param where d.id > 943'
    # )
    #
    # for key, val in enumerate(drug):
    #     res = json.loads(val['res'])
    #     for key2, val2 in enumerate(res['data'][0]['children']):
    #         disease_name = val2['name']
    #         disease_id = get_disease(disease_name)
    #
    #         for key3, val3 in enumerate(val2['children']):
    #             data = {
    #                 'drug_id': val['id'],
    #                 'disease_id': disease_id,
    #                 'usage': val3['name']
    #             }
    #             dbconn.insert(table='drug_disease', data=data)
    # drug_name = val['drug_name']
    # url = 'https://www.aipets.com.cn/Home/GetDrugZstpsAm?drugname=' + drug_name
    # response = requests.get(url)
    # apiData = dbconn.query(
    #     sql='SELECT res FROM api where param = "'+drug_name+'"'
    # )
    # res = json.loads(response.text)

    # data = {
    #     'url': url,
    #     'param': drug_name,
    #     'res': response.text
    # }
    # disease_id = dbconn.insert(table='api', data=data)
    # for key2, val2 in enumerate(res['data'][0]['children']):
    #     data = {
    #         'disease_name': val2['name']
    #     }
    #     disease_id = dbconn.insert(table='disease', data=data)
    #
    #     for key3, val3 in enumerate(val2['children']):
    #         data = {
    #             'disease_name': val2['name']
    #         }
    #         disease_id = dbconn.insert(table='disease', data=data)
    # print(disease_id)
    # data = {
    #     'drug_name': 1
    # }
    # dbconn.insert(table='disease', data=data)

    return "1"


@bp.route('/data_presdrug')
def data_presdrug():
    pres = dbconn.query(
        sql='SELECT langtext, prescription_id FROM lab_category_prescription where id > 0 group by prescription_id'
    )

    for val in pres:

        dict1 = json.loads(val['langtext'])

        for val2 in dict1:

            for val3 in val2['drugs']:
                drug_id = get_drug(val3['drugname'])

                cond_b = {'prescription_id': val['prescription_id'], 'drug_id': drug_id, 'method': val2['method'],
                          'methodname': val2['methodname']}

                dbconn.insert(table='prescription_drug', data=cond_b)

    return '1'


@bp.route('/data_biocid')
def data_biocid():
    # sql='SELECT d.id, d.drug_name,a.res FROM drug as d left join api as a on d.drug_name = a.param where d.id > 943'
    clinical = dbconn.query(
        sql='SELECT id, `name`, lab_category_id, `_range` FROM lab_category_biochemical where id > 0'
    )

    for val in clinical:
        clinical_id = get_biochemical(val['name'], val['_range'])
        data_end = {'biochemical_id': clinical_id}
        cond = {'id': val['id']}
        dbconn.update(table='lab_category_biochemical', data=data_end, condition=cond)

    return '1'


@bp.route('/data_presid')
def data_presid():
    # sql='SELECT d.id, d.drug_name,a.res FROM drug as d left join api as a on d.drug_name = a.param where d.id > 943'
    clinical = dbconn.query(
        sql='SELECT id, `name`, lab_category_id FROM lab_category_prescription where id > 0'
    )

    for val in clinical:
        clinical_id = get_prescription(val['name'])
        data_end = {'prescription_id': clinical_id}
        cond = {'id': val['id']}
        dbconn.update(table='lab_category_prescription', data=data_end, condition=cond)

    return '1'


@bp.route('/data_clinicalid')
def data_clinicalid():
    # sql='SELECT d.id, d.drug_name,a.res FROM drug as d left join api as a on d.drug_name = a.param where d.id > 943'
    clinical = dbconn.query(
        sql='SELECT id, `name`, `value`, `qz`, lab_category_id FROM lab_clinical where id > 0'
    )

    for val in clinical:
        clinical_id = get_clinical(val['name'])
        data_end = {'clinical_id': clinical_id}
        cond = {'id': val['id']}
        dbconn.update(table='lab_clinical', data=data_end, condition=cond)

    return '1'


@bp.route('/c')
def test():

    # 建立与ClickHouse数据库的连接
    # conn = Client(
    #     host='cc-2zejbg2y0c0m15o20o.ads.rds.aliyuncs.com',
    #     port='3306',
    #     user='root_house',
    #     password='dbc_root888',
    #     database='dbc_drug'
    # )

    # # sqlStr = 'select * from drug_dh limit 10'
    # sqlStr = 'select drug_name,eng_name,drug_info,uad,useto,note,preparation,adrs,cate,spec,withdrawal_time from vp_codex'
    # result = conn.execute(sqlStr)

    # print(result)

    # for row in result:
    #     # id, dh_name, dh_info, state, file, state_info, createtime = row
    #     # print(id, dh_name, dh_info, state, file, state_info, createtime)
    #     gen_name,indication,useto,usage,unreactions = row
    #     print(gen_name,indication,useto,usage,unreactions)


    #
    # nodes = graph.nodes.match()
    #
    # for node in nodes:
    #     print(node)

    # node_1 = Node("Person", name="test_node_3")
    # node_2 = Node("Person", name="test_node_4")
    # graph.create(node_1)
    # graph.create(node_2)

    # for row in result:
    #     # id, dh_name, dh_info, state, file, state_info, createtime = row
    #     # print(id, dh_name, dh_info, state, file, state_info, createtime)
    #     # gen_name,eng_name,indication,useto,note,usage,basis,spec,unreactions = row
    #     #
    #     # cond_b = {
    #     #     'gen_name': gen_name,
    #     #     'eng_name': eng_name,
    #     #     'indication': indication,
    #     #     'useto': useto,
    #     #     'note': note,
    #     #     'usage': usage,
    #     #     'basis': basis,
    #     #     'spec': spec,
    #     #     'unreactions': unreactions
    #     # }
    #     #
    #     # dbconn.insert(table='drug_stock', data=cond_b)
    #     drug_name, eng_name, drug_info, uad, useto, note, preparation, adrs, cate, spec, withdrawal_time = row

    #     cond_b = {
    #         'drug_name': drug_name,
    #         'eng_name': eng_name,
    #         'drug_info': drug_info,
    #         'uad': uad,
    #         'useto': useto,
    #         'note': note,
    #         'preparation': preparation,
    #         'adrs': adrs,
    #         'cate': cate,
    #         'spec': spec,
    #         'withdrawal_time': withdrawal_time
    #     }

    #     dbconn.insert(table='vp_codex', data=cond_b)

        # node_gen_name = Node("药品", name=gen_name, goods_name=goods_name)
        # node_indication = Node("适应症", name=indication)
        # node_useto = Node("用途", name=useto)
        # node_usage = Node("用法用量", name=usage)
        # node_basis = Node("成分", name=basis)
        # node1_unreactions = Node("不良反应", name=unreactions)
        #
        # link1 = Relationship(node_gen_name, '适应症', node_indication)
        # graph.create(link1)
        #
        # link2 = Relationship(node_gen_name, '用途', node_useto)
        # graph.create(link2)
        #
        # link3 = Relationship(node_gen_name, '用法用量', node_usage)
        # graph.create(link3)
        #
        # link4 = Relationship(node_gen_name, '成分', node_basis)
        # graph.create(link4)
        #
        # link5 = Relationship(node_gen_name, '不良反应', node1_unreactions)
        # graph.create(link5)

    # node_1_call_node_2 = Relationship(node_1, 'CALL', node_2)
    # node_1_call_node_2['count'] = 1  # 该关系的属性
    # node_2_call_node_1 = Relationship(node_2, 'CALL', node_1)
    # node_2_call_node_1['count'] = 2
    # graph.create(node_1_call_node_2)
    # graph.create(node_2_call_node_1)

    # matcher = graph.NodeMatcher(graph)
    # user = matcher.match("Person", name="test_node_1").first()

    # data1 = graph.run('MATCH (n) RETURN n')

    # 通过关系获取节点列表
    # print(list(graph.match(r_type="CALL")))

    # print(data1, type(data1))
    # data1.keys()
    # "[<Record a=(_214:人 {name: '\u5c0f\u660e'})>, <Record a=(_254:人 {age: 22, name: '\u5c0f\u7ea2'})>] <class 'list'>"
    # print(data1[0]['a']['name'])

    # user = graph.run("UNWIND range(1, 3) AS n RETURN n, n * n as n_sq").to_table()
    return "1"


def get_drug(name):
    cond_b = {'drug_name': name}
    rows = dbconn.fetch_rows(
        table='drug',
        fields='id,drug_name',
        condition=cond_b,
        fetchone=True
    )

    if rows is None:
        return dbconn.insert(table='drug', data=cond_b)
    else:
        return rows['id']


def get_disease(name):
    cond_b = {'disease_name': name}
    rows = dbconn.fetch_rows(
        table='disease',
        fields='id,disease_name',
        condition=cond_b,
        fetchone=True
    )

    if rows is None:
        return dbconn.insert(table='disease', data=cond_b)
    else:
        return rows['id']


def get_drug_disease(val1, val2, val3):
    cond_b = {'drug_id': val1, 'disease_id': val2, 'usage': val3, 'pet_species': 0}
    rows = dbconn.fetch_rows(
        table='drug_disease',
        fields='id',
        condition=cond_b,
        fetchone=True
    )

    if rows is None:
        return 0
        # return dbconn.insert(table='drug_disease', data=cond_b)
    else:
        return rows['id']


def get_clinical(name):
    cond_b = {'clinical_name': name}
    rows = dbconn.fetch_rows(
        table='clinical',
        fields='id,clinical_name',
        condition=cond_b,
        fetchone=True
    )

    if rows is None:
        return dbconn.insert(table='clinical', data=cond_b)
    else:
        return rows['id']


def get_prescription(name):
    cond_b = {'prescription_name': name}
    rows = dbconn.fetch_rows(
        table='prescription',
        fields='id, prescription_name',
        condition=cond_b,
        fetchone=True
    )

    if rows is None:
        return dbconn.insert(table='prescription', data=cond_b)
    else:
        return rows['id']


def get_biochemical(name, standard):
    if standard == 'high':
        cn_standard = '高'
    else:
        cn_standard = '低'

    cond_b = {'biochemical_name': name, 'standard': cn_standard}
    rows = dbconn.fetch_rows(
        table='biochemical',
        fields='id, biochemical_name',
        condition=cond_b,
        fetchone=True
    )

    if rows is None:
        return dbconn.insert(table='biochemical', data=cond_b)
    else:
        return rows['id']


def get_symptoms(name):
    cond_b = {'name': name}
    rows = dbconn.fetch_rows(
        table='symptoms',
        fields='id, name',
        condition=cond_b,
        fetchone=True
    )

    if rows is None:
        return dbconn.insert(table='symptoms', data=cond_b)
    else:
        return rows['id']


def get_treatment(name, medicinal_data):
    cond_b = {'name': name}
    rows = dbconn.fetch_rows(
        table='treatment',
        fields='id, name',
        condition=cond_b,
        fetchone=True
    )

    if rows is None:
        cond_b['medicinal_data'] = medicinal_data
        return dbconn.insert(table='treatment', data=cond_b)
    else:
        return rows['id']


def get_medicinal(name):
    cond_b = {'name': name}
    rows = dbconn.fetch_rows(
        table='medicinal',
        fields='id, name',
        condition=cond_b,
        fetchone=True
    )

    if rows is None:
        return dbconn.insert(table='medicinal', data=cond_b)
    else:
        return rows['id']


def get_treatment_medicinal(treatment_id, medicinal_id):
    cond_b = {'treatment_id': treatment_id, 'medicinal_id': medicinal_id}
    rows = dbconn.fetch_rows(
        table='treatment_medicinal',
        fields='id, treatment_id',
        condition=cond_b,
        fetchone=True
    )

    if rows is None:
        return dbconn.insert(table='treatment_medicinal', data=cond_b)
    else:
        return rows['id']


def update_pet_species(url):
    result = requests.get(url=url)
    res = result.text
    # print(res)

    soup = BeautifulSoup(res, 'html.parser')
    # print(soup.prettify())
    drug = soup.select(".txtdls > font")[0].string

    # print(drug)

    drug_id = get_drug(drug)
    print(drug_id)
    doc = soup.select(".report-table>tbody>tr>td")
    # lendoc = int(len(doc)/3)
    #
    # for i in range(lendoc):
    #     print(doc[i].string)
    #     print(doc[i+1].string)
    #     print(doc[i+2].string)
    #     i = i+3

    i = 0
    while i < len(doc):
        disease_id = get_disease(doc[i].string)
        # print(doc[i].string)
        dd_id = get_drug_disease(drug_id, disease_id, doc[i + 1].string)
        # print(doc[i + 1].string)
        print(dd_id)

        if dd_id == 0:
            continue

        data_end = {'pet_species': doc[i + 2].string}
        cond = {'id': dd_id}
        dbconn.update(table='drug_disease', data=data_end, condition=cond)

        # print(doc[i + 2].string)
        i = i + 3

    return


def replace_self(str):
    return str.replace(" ", "").replace("\t", "").replace("\n", "").replace("\r","")


def download_image(url, save_path, filename):
    try:
        # 发送HTTP GET请求
        response = requests.get(url)
        
        # 检查请求是否成功
        if response.status_code == 200:
            os.makedirs(save_path, exist_ok=True)
            # 以二进制写入文件
            with open(save_path+filename, 'wb') as file:
                file.write(response.content)
            print(f"Image successfully downloaded: {save_path}")
        else:
            print(f"Failed to retrieve image. Status code: {response.status_code}")
    except Exception as e:
        print(f"An error occurred: {e}")