from flask import Flask,render_template,request,redirect
from flask_sqlalchemy import SQLAlchemy

whsj = Flask(__name__)
whsj.config['SQLALCHEMY_DATABASE_URI']='mysql://root:123456@127.0.0.1:3306/yiqing'
#设置每次请求结束后会自动提交数据库中的改动
whsj.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True

whsj.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True

db=SQLAlchemy(whsj)
'''
class City(db.Model):
	id = db.Column(db.Integer,primary_key=True,autoincrement=True)
	provincename=db.Column(db.String(255))
	cityname=db.Column(db.String(255))
	usernumber=db.Column(db.Integer)

db.create_all()
city2=City(provincename="hunan",cityname="zhuzhou",usernumber=256)
db.session.add(city2)
db.session.commit()
city1=City(provincename="hunan",cityname="zhuzhou",usernumber=255)
db.session.add(city1)
db.session.commit()
'''
class areadata(db.Model):
    time=db.Column(db.Date,primary_key=True)
    confirmed=db.Column(db.Integer)
    suspected=db.Column(db.Integer)
    death=db.Column(db.Integer)
    areaname=db.Column(db.String(255),primary_key=True)
class area(db.Model):
    area_name=db.Column(db.String(255),primary_key=True)
    area_population=db.Column(db.Integer)
class cooperation(db.Model):
    doc_id=db.Column(db.String,primary_key='True')
    nur_id=db.Column(db.String,primary_key='True')

@whsj.route("/")
def index():
    return render_template("load.html")
'''
@whsj.route("/")
def index():
    return select_all()
'''
@whsj.route("/main page")
def mainpage():
    areadata_list=areadata.query.all()

    return render_template("main page.html",areadata_list=areadata_list)

@whsj.route("/get_area",methods=['GET','POST'])
def get_area():
    path="/"+request.form["area_name"]
    return redirect(asa)
@whsj.route("/asa")
def asa():
    return render_template("asa.html")
@whsj.route("/jianghan")
def jianghan():
    sql = 'select * from areadata where areaname="江汉区"'
    jhlist = db.session.execute(sql)
    return render_template("jianghan.html",jhlist = jhlist)
@whsj.route("/wuchang")
def wuchang():
    sql = 'select * from areadata where areaname="武昌区"'
    wclist = db.session.execute(sql)
    return render_template("wuchang.html",wclist = wclist)
@whsj.route("/qiaokou")
def qiaokou():
    sql = 'select * from areadata where areaname="硚口区"'
    qklist = db.session.execute(sql)
    return render_template("qiaokou.html",qklist = qklist)
@whsj.route("/renmin")
def renmin():
    sql1 = 'select * from doctor where workunit="武汉大学人民医院";'
    doctor_list = db.session.execute(sql1)
    sql2 = 'select patient.id as id,patient.name as name,patient.sex as sex,patient.year_birth as year_birth,doctor.name as doctor,nurse.name as nurse from (patient left join doctor on patient.treat_doc=doctor.id) left join nurse on patient.treat_nur = nurse.id where patient.treat_hos="武汉大学人民医院";'
    patient_list = db.session.execute(sql2)
    sql3='select * from nurse where workunit="武汉大学人民医院";'
    nurse_list = db.session.execute(sql3)
    return render_template("renmin.html", doctor_list=doctor_list, patient_list=patient_list,nurse_list=nurse_list)
@whsj.route("/zhongyi")
def zhongyi():
    sql1 = 'select * from doctor where workunit="湖北省中医院花园山院区";'
    doctor_list = db.session.execute(sql1)
    sql2 = 'select patient.id as id,patient.name as name,patient.sex as sex,patient.year_birth as year_birth,doctor.name as doctor,nurse.name as nurse from (patient left join doctor on patient.treat_doc=doctor.id) left join nurse on patient.treat_nur = nurse.id where patient.treat_hos="湖北省中医院花园山院区";'
    patient_list = db.session.execute(sql2)
    sql3='select * from nurse where workunit="湖北省中医院花园山院区";'
    nurse_list = db.session.execute(sql3)
    return render_template("zhongyi.html", doctor_list=doctor_list, patient_list=patient_list,nurse_list=nurse_list)
@whsj.route("/zhongnan")
def zhongnan():
    sql1 = 'select * from doctor where workunit="武汉大学中南医院";'
    doctor_list = db.session.execute(sql1)
    sql2 = 'select patient.id as id,patient.name as name,patient.sex as sex,patient.year_birth as year_birth,doctor.name as doctor,nurse.name as nurse from (patient left join doctor on patient.treat_doc=doctor.id) left join nurse on patient.treat_nur = nurse.id where patient.treat_hos="武汉大学中南医院";'
    patient_list = db.session.execute(sql2)
    sql3='select * from nurse where workunit="武汉大学中南医院";'
    nurse_list = db.session.execute(sql3)
    return render_template("zhongnan.html", doctor_list=doctor_list, patient_list=patient_list,nurse_list=nurse_list)
@whsj.route("/zhongxi")
def zhongxi():
    sql1 = 'select * from doctor where workunit="武汉市中西医结合医院";'
    doctor_list = db.session.execute(sql1)
    sql2 = 'select patient.id as id,patient.name as name,patient.sex as sex,patient.year_birth as year_birth,doctor.name as doctor,nurse.name as nurse from (patient left join doctor on patient.treat_doc=doctor.id) left join nurse on patient.treat_nur = nurse.id where patient.treat_hos="武汉市中西医结合医院";'
    patient_list = db.session.execute(sql2)
    sql3='select * from nurse where workunit="武汉市中西医结合医院";'
    nurse_list = db.session.execute(sql3)
    return render_template("zhongxi.html", doctor_list=doctor_list, patient_list=patient_list,nurse_list=nurse_list)
@whsj.route("/xiehe")
def xiehe():
    sql1 = 'select * from doctor where workunit="武汉协和医院";'
    doctor_list = db.session.execute(sql1)
    sql2 = 'select patient.id as id,patient.name as name,patient.sex as sex,patient.year_birth as year_birth,doctor.name as doctor,nurse.name as nurse from (patient left join doctor on patient.treat_doc=doctor.id) left join nurse on patient.treat_nur = nurse.id where patient.treat_hos="武汉协和医院";'
    patient_list = db.session.execute(sql2)
    sql3='select * from nurse where workunit="武汉协和医院";'
    nurse_list = db.session.execute(sql3)
    return render_template("xiehe.html", doctor_list=doctor_list, patient_list=patient_list,nurse_list=nurse_list)

if __name__ == '__main__':
    whsj.run()