# encoding: utf-8
import os
import random
import re
import time

from flask import Blueprint, json
import requests
from py2neo import Graph, Node, Relationship, NodeMatcher
from clickhouse_driver import Client
from app.model.base import dbconn, dbconn2, graph
from bs4 import BeautifulSoup

# from database.neo import graph

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


@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_ch')
def data_ch():
    old_id = '0'
    i = 1
    size = 100
    while i <= 10:
        page = i * size

        sql_str = 'SELECT rm.id, rm.orgin_id, rp.chief_complaint, rp.treatment, rp.doctors_advice, rms.symptom, rms.subject, re.diagnosis_summary' \
                  ' FROM ch_report_main as rm ' \
                  ' left join ch_report_evalute as re on rm.orgin_id=re.report_main_orgin_id ' \
                  ' left join ch_report_patient as rp on rm.orgin_id=rp.report_main_orgin_id ' \
                  ' left join ch_report_customer as rc on rm.orgin_id=rc.report_main_orgin_id' \
                  ' left join report_main_status as rms on rm.orgin_id=rms.report_main_orgin_id' \
                  ' where rm.valid_status = 1 and rm.visit_status = 1 and id >' + old_id + \
                  ' limit ' + str(page) + ', ' + str(size)

        rows = dbconn2.query(
            sql=sql_str
        )

        i += 1

        for val1 in rows:
            data1 = {
                'name': val1['diagnosis_summary'],
                'chief': val1['chief_complaint'],
                'doctors_advice': val1['doctors_advice'],
                'doctors_advice_data': '',
                'subject': val1['subject'],
                'treatment': val1['treatment'],
                'symptom': val1['symptom'],
                'old_id': val1['id'],
                'illness_data': ''
            }

            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.loads('', encoding='utf-8')

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

    return '2'


def data_set_cate(child, pid, children):
    for val in child:
        data3 = {
            'pid': pid,
            'name': val['title'],
            'children': children
        }
        cateid = dbconn.insert(table='xd_cate', data=data3)
        if len(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('/neo_treatment_medicinal')
def neo_treatment_medicinal():
    graph = Graph("", auth=("neo4j", "11111111"))

    node_matcher = NodeMatcher(graph)

    sql_str = 'SELECT tm.medicinal_id, tm.treatment_id, me.name ' \
              ' FROM treatment_medicinal as tm ' \
              ' left join medicinal as me on tm.medicinal_id = me.id ' \
              ' where tm.id > 0'

    rows = dbconn.query(
        sql=sql_str
    )

    for val in rows:
        node_treatment = node_matcher.match('治疗方案', id=val['treatment_id'], type=2).first()
        if node_treatment is None:
            continue

        node_medicinal = node_matcher.match('药品', id=val['medicinal_id'], type=2).first()
        if node_medicinal is None:
            node_medicinal = Node('药品', id=val['medicinal_id'], name=val['name'], type=2)

        link3 = Relationship(node_treatment, '适用药品', node_medicinal)

        graph.create(link3)

    return '1'


@bp.route('/neo_illness_treatment')
def neo_illness_treatment():
    graph = Graph("", auth=("neo4j", "11111111"))

    node_matcher = NodeMatcher(graph)

    sql_str = 'SELECT il.cate_id, il.treatment_id, tr.name, tr.medicinal_data ' \
              ' FROM illness_treatment as il ' \
              ' left join treatment as tr on il.treatment_id = tr.id ' \
              ' where il.id > 0'

    rows = dbconn.query(
        sql=sql_str
    )

    for val in rows:
        node_cate3 = node_matcher.match('疾病（三）', id=val['cate_id']).first()
        if node_cate3 is None:
            continue

        node_symptoms = node_matcher.match('治疗方案', id=val['treatment_id'], type=2).first()
        if node_symptoms is None:
            node_symptoms = Node('治疗方案', id=val['treatment_id'], name=val['name'], medicinal_data=val['medicinal_data'],
                                 type=2)

        link3 = Relationship(node_cate3, '治疗', node_symptoms)

        graph.create(link3)

    return '1'


@bp.route('/neo_illness_symptoms')
def neo_illness_symptoms():
    # graph = Graph("", auth=("neo4j", "11111111"))

    node_matcher = NodeMatcher(graph)

    sql_str = 'SELECT il.cate_id, il.symptoms_id, sy.name ' \
              ' FROM illness_symptoms as il ' \
              ' left join symptoms as sy on il.symptoms_id = sy.id ' \
              ' where il.id > 0'

    rows = dbconn.query(
        sql=sql_str
    )

    for val in rows:
        node_cate3 = node_matcher.match('疾病（三）', id=val['cate_id']).first()
        if node_cate3 is None:
            continue

        node_symptoms = node_matcher.match('病症', id=val['symptoms_id'], type=2).first()
        if node_symptoms is None:
            node_symptoms = Node('病症', id=val['symptoms_id'], name=val['name'], type=2)

        link3 = Relationship(node_cate3, '病症', node_symptoms)
        link2 = Relationship(node_symptoms, '疾病', node_cate3)

        graph.create(link2)
        graph.create(link3)

    return '1'


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

    for val in data:
        tid = get_treatment(val['illness_name'], val['drug_name'])

        data3 = {
            'cate_id': val['cate_id'],
            'treatment_id': tid
        }
        dbconn.insert(table='illness_treatment', data=data3)

        res = val['drug_name'].split('、')

        for val2 in res:
            mid = get_medicinal(val2)

            get_treatment_medicinal(tid, mid)
            # data2 = {
            #     'treatment_id': tid,
            #     'medicinal_id': mid
            # }
            # dbconn.insert(table='treatment_medicinal', data=data2)

    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('/neo_biochemical')
def neo_biochemical():
    graph = Graph("", auth=("neo4j", "11111111"))

    node_matcher = NodeMatcher(graph)

    rows = dbconn.query(
        sql='SELECT id, abbreviation, `name`, _range, lab_category_id, biochemical_id FROM lab_category_biochemical where id > 0'
    )

    for val in rows:
        node_cate3 = node_matcher.match('疾病（三）', id=val['lab_category_id']).first()
        if node_cate3 is None:
            continue

        if val['_range'] == 'high':
            cn_standard = '高'
        else:
            cn_standard = '低'

        name = val['name'] + '（' + cn_standard + '）'
        node_biochemical = node_matcher.match('检查项', id=val['biochemical_id']).first()
        if node_biochemical is None:
            node_biochemical = Node("检查项", id=val['biochemical_id'], name=name, cn_name=val['name'],
                                    abbreviation=val['abbreviation'], standard=val['_range'],
                                    category_id=val['lab_category_id'])

        link3 = Relationship(node_cate3, '检查', node_biochemical)
        graph.create(link3)

    return '1'


@bp.route('/neo_prestext')
def neo_prestext():
    graph = Graph("", auth=("neo4j", "11111111"))

    node_matcher = NodeMatcher(graph)

    pres = dbconn.query(
        sql='SELECT langtext, prescription_id FROM lab_category_prescription where id > 0 group by prescription_id'
    )

    for val in pres:
        node_pres = node_matcher.match('治疗原则', id=val['prescription_id']).first()
        if node_pres is None:
            continue

        text = json.loads(val['langtext'])
        node_text = Node('')

        link3 = Relationship(node_pres, '治疗', node_text)
        graph.create(link3)

    return '1'


@bp.route('/neo_pres')
def neo_pres():
    graph = Graph("", auth=("neo4j", "11111111"))

    node_matcher = NodeMatcher(graph)

    pres = dbconn.query(
        sql='SELECT id, `name`, lab_category_id, langtext, prescription_id FROM lab_category_prescription where id > 0'
    )

    for val in pres:
        node_cate3 = node_matcher.match('疾病（三）', id=val['lab_category_id']).first()
        if node_cate3 is None:
            continue

        node_pres = node_matcher.match('治疗原则', id=val['prescription_id']).first()
        if node_pres is None:
            node_pres = Node("治疗原则", id=val['prescription_id'], name=val['name'], category_id=val['lab_category_id'])

        link3 = Relationship(node_cate3, '治疗', node_pres)
        graph.create(link3)

    return '1'


@bp.route('/neo_cate2')
def neo_cate2():
    graph = Graph("", auth=("neo4j", "11111111"))

    node_matcher = NodeMatcher(graph)

    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:
        # node_cate3 = Node("疾病（三）", id=val3['id'], name=val3['name'], parent_id=val3['parent_id'])

        node_cate3 = node_matcher.match('疾病（三）', id=val3['id']).first()
        if node_cate3 is None:
            node_cate3 = Node("疾病（三）", id=val3['id'], name=val3['name'], parent_id=val3['parent_id'])

        # description = replace_self(val3['description'])
        # if description is not None:
        #     node_description = Node("描述", name=description)
        #     link3 = Relationship(node_cate3, '描述', node_description)
        #     graph.create(link3)
        #
        # examintions = replace_self(val3['examintions'])
        # if examintions is not None:
        #     node_examintions = Node("建议检测项目", name=examintions)
        #     link4 = Relationship(node_cate3, '建议检测项目', node_examintions)
        #     graph.create(link4)
        #
        # remarks = replace_self(val3['remarks'])
        # if remarks is not None:
        #     node_remarks = Node("医嘱", name=remarks)
        #     link5 = Relationship(node_cate3, '医嘱', node_remarks)
        #     graph.create(link5)

        prognosis = replace_self(val3['prognosis'])
        if prognosis is not None:
            node_prognosis = Node("预后", name=prognosis)
            link6 = Relationship(node_cate3, '预后', node_prognosis)
            graph.create(link6)

        adjuvanttherapy = replace_self(val3['adjuvanttherapy'])
        if adjuvanttherapy is not None:
            dict_adj = json.loads(adjuvanttherapy)
            str_adj = ''
            for adj in dict_adj:
                str_adj += adj['servicename'] + '。'

            node_adjuvanttherapy = Node("辅助疗法", name=str_adj)
            link7 = Relationship(node_cate3, '辅助疗法', node_adjuvanttherapy)
            graph.create(link7)

    return '1'


@bp.route('/neo_cate')
def neo_cate():
    graph = Graph("", auth=("neo4j", "11111111"))

    node_matcher = NodeMatcher(graph)

    # 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'
    category = dbconn.query(
        sql='SELECT id, parent_id, `name`, description, examintions, remarks, prognosis, adjuvanttherapy FROM lab_category where id > 0 and children = 1'
    )

    category2 = dbconn.query(
        sql='SELECT id, parent_id, `name`, description, examintions, remarks, prognosis, adjuvanttherapy FROM lab_category where id > 0 and children = 2'
    )

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

    for val in category:

        node_cate = Node("疾病（一）", id=val['id'], name=val['name'], parent_id=val['parent_id'])

        for val2 in category2:
            if val2['parent_id'] == val['id']:

                node_cate2 = Node("疾病（二）", id=val2['id'], name=val2['name'], parent_id=val2['parent_id'])

                link1 = Relationship(node_cate, '分类', node_cate2)
                graph.create(link1)

                for val3 in category3:
                    if val3['parent_id'] == val2['id']:
                        node_cate3 = Node("疾病（三）", id=val3['id'], name=val3['name'], parent_id=val3['parent_id'])

                        link2 = Relationship(node_cate2, '分类', node_cate3)
                        graph.create(link2)

                        description = replace_self(val3['description'])
                        if description is not None:
                            node_description = Node("描述", name=description)
                            link3 = Relationship(node_cate3, '描述', node_description)
                            graph.create(link3)

                        examintions = replace_self(val3['examintions'])
                        if examintions is not None:
                            node_examintions = Node("建议检测项目", name=examintions)
                            link4 = Relationship(node_cate3, '建议检测项目', node_examintions)
                            graph.create(link4)

                        remarks = replace_self(val3['remarks'])
                        if remarks is not None:
                            node_remarks = Node("医嘱", name=remarks)
                            link5 = Relationship(node_cate3, '医嘱', node_remarks)
                            graph.create(link5)

                        prognosis = replace_self(val3['prognosis'])
                        if prognosis is not None:
                            node_prognosis = Node("预后", name=prognosis)
                            link6 = Relationship(node_cate3, '预后', node_prognosis)
                            graph.create(link6)

                        adjuvanttherapy = replace_self(val3['adjuvanttherapy'])
                        if prognosis is not None:
                            node_adjuvanttherapy = Node("辅助疗法", name=adjuvanttherapy)
                            link7 = Relationship(node_cate3, '辅助疗法', node_adjuvanttherapy)
                            graph.create(link7)

    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('/neo_clinical')
def neo_clinical():
    graph = Graph("", auth=("neo4j", "11111111"))

    node_matcher = NodeMatcher(graph)

    # clinical_id = dbconn.query(
    #     sql='SELECT id, `name`, `value`, `qz`, lab_category_id FROM lab_clinical where id > 0'
    # )

    # 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, clinical_id FROM lab_clinical where id > 0'
    )

    category = dbconn.query(
        sql='SELECT id, parent_id, `name` FROM lab_category where id > 0 and children = 3'
    )

    for val in clinical:
        # clinical_id = get_clinical(val['name'])
        node_clinical = node_matcher.match('病症', id=val['clinical_id']).first()
        if node_clinical is None:
            node_clinical = Node("病症", id=val['clinical_id'], name=val['name'])

        for val2 in category:
            if val2['id'] == val['lab_category_id']:
                node_cate = node_matcher.match('疾病（三）', id=val2['id']).first()
                if node_cate is None:
                    node_cate = Node("疾病（三）", id=val2['id'], name=val2['name'], parent_id=val2['parent_id'])

                # node_clinical = Node("病症", id=val['id'], name=val['name'], value=val['value'], qz=val['qz'], category_id=val['lab_category_id'])

                link2 = Relationship(node_clinical, '疾病', node_cate)
                link3 = Relationship(node_cate, '病症', node_clinical)

                graph.create(link2)
                graph.create(link3)

    return '1'


@bp.route('/neo_drug')
def neo_drug():
    graph = Graph("", auth=("neo4j", "11111111"))

    node_matcher = NodeMatcher(graph)

    # 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'
    drug = dbconn.query(
        sql='SELECT d.id, d.drug_name FROM drug as d where d.id > 0 '
    )

    # print(drug)

    drug_disease = dbconn.query(
        sql='SELECT d.drug_id, d.disease_id, d.usage, d.pet_species FROM drug_disease as d where d.id > 0'
    )

    disease = dbconn.query(
        sql='SELECT d.id, d.disease_name FROM disease as d where d.id > 0'
    )

    disease_data = {}

    for val in disease:
        disease_data[val['id']] = val['disease_name']

    for val2 in drug:

        node_drug = Node("药品", id=val2['id'], name=val2['drug_name'])

        for val3 in drug_disease:
            if val2['id'] == val3['drug_id']:
                node_disease = node_matcher.match('适应症', id=val3['disease_id']).first()
                if node_disease is None:
                    node_disease = Node("适应症", id=val3['disease_id'], name=disease_data[val3['disease_id']])

                node_pet_species = Node("品种", drug_id=val3['drug_id'], disease_id=val3['disease_id'],
                                        name=val3['pet_species'])
                # link1 = Relationship(node_drug, '品种', node_pet_species)
                link2 = Relationship(node_disease, '品种', node_pet_species)

                node_usage = Node("用法用量", drug_id=val3['drug_id'], disease_id=val3['disease_id'], name=val3['usage'])
                link3 = Relationship(node_pet_species, '用法用量', node_usage)

                link4 = Relationship(node_drug, '适应症', node_disease)
                link1 = Relationship(node_disease, '用药', node_drug)

                graph.create(link1)
                graph.create(link2)
                graph.create(link3)
                graph.create(link4)

        # 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)

    return "1"


@bp.route('/c')
def test():
    # test_graph = Graph("", auth=("neo4j", "11111111"))

    # node_matcher = NodeMatcher(test_graph)
    #
    # case = node_matcher.match('人', name='小绿').first()  # 获得name为XXX刑事判决文书的“判决文书”类 节点
    # people = node_matcher.match('人', name='小红').first()  # 获得name为“XXX”的“被告人”类节点
    # relation = Relationship(case, '爱', people)  # 创建判决文书和被告人间的关系，关系词为“被告人”
    # test_graph.create(relation)  # 将创建传递到图上
    # 建立节点
    # test_node_1 = Node("人", id=2)
    # test_node_2 = Node("人", id=1)
    # test_graph.create(test_node_1)
    # test_graph.create(test_node_2)
    # test_node_1.add_label("teacher")
    # 建立关系
    # node_1_call_node_2 = Relationship(test_node_1, '喜欢', test_node_2)
    # # node_1_call_node_2['count'] = 1
    # node_2_call_node_1 = Relationship(test_node_2, '讨厌', test_node_1)
    # # node_2_call_node_1['count'] = 2
    # test_graph.create(node_1_call_node_2)
    # test_graph.create(node_2_call_node_1)

    # return "1"

    # 建立与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)

    # graph = Graph("", auth=("neo4j", "11111111"))

    #
    # 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", "")