1. 处理就诊信息

# -*- coding: utf-8 -*-
"""
@Project : 01-python-learn
@File    : 12_医院管理系统.py
@IDE     : PyCharm
@Author  : 刘庆东
@Date    : 2025/9/16 14:15
"""

# 1.导入模块
import pymysql
import pandas as pd
from datetime import datetime


class HospitalManagementSystem:
    def __init__(self):#定义构造方法
        self.connection=self.connect_db()

    #做为数据库连接的方法
    """
      pymysql.cursors.DictCursor 
      因为默认返回的是元组类型 
      通过设置  cursorclass 使返回类型变为字典类型
      处理大量数据的时候更加的方便
    """
    def connect_db(self):
        try:
            conn=pymysql.connect(
                host="192.168.88.151",
                user="root",
                password="hadoop",
                database="hospital_db",
                charset="utf8",
                cursorclass=pymysql.cursors.DictCursor)
            print("✅ 数据库连接成功了")
            return conn
        except Exception as e:
            print(f"数据库连接失败:{e}")
            return None

    # 定义关闭资源的方法
    def close_connection(self):
        if self.connection:
            self.connection.close()#当connection不是空的时候调用方法进行数据库的关闭
            print("🔌数据库连接已经断开!")
    # ------ 患者管理-------------
    def add_patients(self):
       print("添加患者")
       name=input("姓名：").strip()
       if not name:
           print("❌姓名不能为空!")
           return
       gender=input("性别 (男/女):").strip()

       if gender not in ['男','女']:
           print("❌性别只能是男女")
           return
       try:
           age=int(input("年龄:"))
           if age<0 or age>150:
               print("❌年龄不合法!")
       except ValueError:
           print("年龄必须是数字")
           return
       phone=input("电话 (可选):").strip()
       address=input("地址 (可选):").strip()

       #将上面的信息添加到数据库中
       try:
           with self.connection.cursor() as cursor:
               sql="INSERT INTO patients(NAME,gender,age,phone,address) VALUES (%s,%s,%s,%s,%s)"
               cursor.execute(sql,(name,gender,age,phone,address))
               #提交事务
               self.connection.commit()
               print(f"✅患者 {name} ，添加成功了！")

       except Exception as e:
           #回滚 撤销刚才的所有的操作
           self.connection.rollback()
           print(f"❌添加失败：{e}")


    #查询所有的患者
    def view_all_patients(self):
        try:
            with self.connection.cursor() as cursor:
                cursor.execute("SELECT * FROM patients ORDER BY id ")
                patients = cursor.fetchall()#提取全部的数据
                if not patients:
                    print("暂时没有患者")
                    return
                print("患者列表如下:")
                print("_"*36)
                for p in patients:
                    print(f"ID: {p['id']:>2} |"
                          f"姓名：{p['name']:<10} |"
                          f"性别:{p['gender']} |"
                          f"年龄:{p['age']:>2}|"
                          f"电话:{p['phone'] or '_':<12}|"
                          f"地址:{p['address']  or '_'}")
                print("_" * 36)

        except Exception as e:
            print(f"查询失败了:{e}")
    # 根据姓名模糊查询患者或者 根据ID条件查询患者
    def find_patient(self):
        key = input("按姓名查找请输入姓名，按ID查找请输入数字ID: ").strip()
        print("------------------")
        try:
           with self.connection.cursor() as cursor:# 这种写法只能用于一个数据库操作
               if key.isdigit():#判断你输入的内容是不是纯数字
                   # 输入的是编号
                   cursor.execute("SELECT * FROM patients WHERE id=%s",(int(key),))#是 数字
               else:
                   #根据姓名进行模糊查询
                   cursor.execute("SELECT * FROM patients WHERE NAME LIKE %s",(f'%{key}%'))
               # 查询全部[查询姓名中包含key的患者信息]的数据
               results=cursor.fetchall()
               if not results:#结果是空的
                   print("🔍未找到患者")
                   return
               #结果不是空的
               print("查询结果如下:")
               """
                  id INT AUTO_INCREMENT PRIMARY KEY,
                    NAME VARCHAR(100) NOT NULL,
                    gender ENUM('男', '女') NOT NULL,
                    age INT NOT NULL,
                    phone VARCHAR(15),
                    address TEXT
               """
               print("_"*36)
               for p in results:
                   #print(p)
                    print(f"ID: {p['id']:>2} |"
                          f"姓名：{p['name']:<10} |"
                          f"性别:{p['gender']} |"
                          f"年龄:{p['age']:>2}|"
                          f"电话:{p['phone'] or '_':<12}|"
                          f"地址:{p['address']  or '_'}")
               print("_"*36)
        except Exception as e:
            print(f"❌查询失败:{e}")

    # ------医生管理-----
    def view_all_doctors(self):
        try:
          with self.connection.cursor() as cursor:
              sql="""
               SELECT d.id,d.name,d.title,dep.name AS dept_name FROM 
                  doctors d 
                  JOIN departments dep ON
                  d.department_id=dep.id
                  ORDER BY dep.name,d.id
              """
              cursor.execute(sql)#执行操作任务
              doctors = cursor.fetchall()#查询全部的医生
              if not doctors:
                  print("暂无医生")
                  return
              print("👨‍⚕️ 医生列表如下:")
              print("_"*36)
              for d in doctors:
                  #print(d)
                  #print(f"ID: {d['id']:>2} |") 你们这样写
                  print(f"ID: {d['id']:>2} | 姓名: {d['name']:<10} | 科室: {d['dept_name']:<8} | 职称: {d['title']}")
              print("_"*36)
        except Exception as e:
            print(f"❌查询失败:{e}")

    # 添加医生
    def add_doctor(self):
        print("➕ 添加医生:")
        name=input("医生姓名: ").strip()
        if not name:
            print("❌姓名不能为空")
            return
        #显示科室
        self.view_all_departments()
        try:
            dept_id =int(input("选择科室ID: "))
        except Exception as e:
            print("❌ID必须是数字")

        title =input("职称 (如: 主治医师): ").strip() or "医师"

        try:
            with self.connection.cursor() as cursor:
                cursor.execute("SELECT id FROM departments WHERE id=%s",(dept_id,))#根据id查询科室的编号
                if not cursor.fetchone():
                    print("❌科室不存在!")
                    return
                #没问题 科室是正常的
                sql="INSERT INTO doctors (NAME,department_id,title) VALUES (%s,%s,%s)"
                cursor.execute(sql,(name,dept_id,title))#执行sql语句 添加参数数据到sql中
                self.connection.commit()#增删改需要提交事务
                print(f"✅ 医生 '{name}' 添加成功！")

        except Exception as e:
            #一旦出错误 执行回滚操作   撤销刚才的所有动作【操作】 保证ACID
            self.connection.rollback()
            print("❌添加医生信息失败")




    # -------科室管理----------
    def view_all_departments(self):#显示科室列表
        try:
            with self.connection.cursor() as cursor:
                cursor.execute("SELECT * FROM departments ORDER BY id")
                depts = cursor.fetchall()#查询所有的数据
                print("🏢 科室列表:")
                """
                  id INT AUTO_INCREMENT PRIMARY KEY,
                    NAME VARCHAR(100) NOT NULL UNIQUE,
                    description TEXT
                """
                print("_"*36)
                for d in depts:
                    print(f"ID:{d['id']:>2} | 名称: {d['name']:<10} | 描述: {d['description'] or '无'}")
                print("_"*36)
        except Exception as e:
            print(f"❌查询失败:{e}")

    # 问诊
        # ------------------- 挂号管理 -------------------
    def register_patient(self):
        # 1.显示所有的患者，选择患者
        print("🎫 患者挂号")
        #调用显示所有的患者
        self.view_all_patients()
        try:
            pid=int(input("输入患者ID: "))
        # except Exception as e:
        except ValueError as e:
            print("❌请输入有效的ID")
            return
        #根据患者的ID查询患者信息
        #因多次使用cursor游标  不能使用with方式 防止出现 cursor关闭异常!!!
        cursor=self.connection.cursor()
        cursor.execute("SELECT name  FROM patients WHERE id=%s",(pid,))
        if not cursor.fetchone():
            #木有查到数据
            print("❌患者不存在")
            return
        cursor.close()#关闭游标
        # 2. 查询所有的科室 选择挂号的科室
        self.view_all_departments()
        try:
            dept_id = int(input("选择科室ID: "))
            # except Exception as e:
        except ValueError as e:
            print("❌ID必须是数字")
            return
        #读取数据
        cursor = self.connection.cursor()
        cursor.execute("SELECT id FROM departments WHERE id=%s", (dept_id,))
        cursor.close()  # 关闭游标
        if not cursor.fetchone():
            # 木有查到数据
            print("❌科室不存在")
            return


        #3. 选择科室某位医生
        cursor=self.connection.cursor()
        cursor.execute("SELECT id,name,title FROM doctors WHERE department_id=%s",(dept_id,))
        doctors=cursor.fetchall()
        if not doctors:
            print("❌该科室暂无医生可以挂号")
            return
        print("📋 可挂号医生:")
        for d in doctors:
            print(f"ID :{d['id']:>2} | {d['name']:<10} | {d['title']}")
        cursor.close()#关闭游标

        #4.挂号成功
        try:
            did = int(input("选医生ID: "))
            # except Exception as e:
        except ValueError as e:
            print("❌ID必须是数字")
            return
        #提示你选择的ID 有可能不是这个科室的
        if not any(d['id']==did for d in doctors):
            print("❌医生不属于该科室")
            return
        try:
            #添加数据到挂号表
            sql="""
             INSERT INTO registrations 
                (patient_id,doctor_id)
                VALUES (%s,%s)
            """
            cursor=self.connection.cursor()#获取游标
            cursor.execute(sql,(pid,did))#执行sql传入参数
            cursor.close()  # 关闭游标
            #增删改 需要提交事务
            self.connection.commit()


            print("✅ 挂号成功！")
        except Exception as e:
            #一旦出现异常 回滚数据
            self.connection.rollback()#撤销刚才的所有操作
            print(f"❌挂号失败:{e}")
            # 一个方法中 涉及到 多个数据库的操作，不建议使用 with
    #等待就诊
    def view_pending_registrations(self):
        try:
            with self.connection.cursor() as cursor:
                sql="""
                SELECT r.`id`,p.`name` AS pname,d.`name` AS dname,dep.`name` AS dept,
                    r.`registration_date` 
                    FROM registrations r
                    JOIN  patients p ON r.patient_id=p.`id`
                    JOIN  doctors d ON r.`doctor_id`=d.`id`
                    JOIN  departments dep ON d.`department_id`=dep.`id`
                    WHERE r.`status`='待就诊'
                    ORDER BY r.`registration_date` DESC 
                """
                cursor.execute(sql)#执行sql语句
                regss=cursor.fetchall()#获取到待就诊的列表
                if not regss:
                    print("❌无待就诊患者")
                    return
                print("⏳ 待就诊列表:")
                print("_"*36)
                for r in regss:
                    time_str=r['registration_date'].strftime("%Y-%m-%d %H:%M:%S")#处理时间显示问题
                    print(f"挂号ID: {r['id']} | 患者: {r['pname']} | 医生: {r['dname']} | 科室: {r['dept']} | 时间: {time_str}")
                print("_"*36)
        except Exception as e:
            print("❌该科室暂无医生可以挂号")
            return




    #处理就诊
    def process_registration(self):
        print("✅ 处理就诊")
        #调用待就诊信息的方法
        self.view_pending_registrations()

        try:
            rid=int(input("输入挂号ID:"))
        except ValueError:
            print("❌请输入有效的ID")
            return
        try:
            with self.connection.cursor() as cursor:
                #处理待就诊的人员
                cursor.execute("SELECT status FROM registrations WHERE id=%s",(rid,))
                reg = cursor.fetchone()
                if not reg or reg['status']!='待就诊':
                    print("❌无法处理[状态的是非待就诊状态]")
                    return
                zhenduan=input("诊断:").strip() or "无"
                chufang=input("处方:").strip() or "无"
                self.connection.begin()#开始事务
                #更改看病的状态为 已经就诊
                cursor.execute("UPDATE registrations SET STATUS='已就诊' WHERE id=%s",(rid,))
                cursor.execute("INSERT INTO medical_records (registration_id,diagnosis,prescription) VALUES (%s,%s,%s)"
                               ,(rid,zhenduan,chufang,))
                self.connection.commit()#提交事务
                print("✅ 就诊处理完成！")
        except Exception as e:
            self.connection.rollback()#回滚
            print("❌处理失败了")



    #疾病的历史记录
    def view_medical_history(self):
        pass




    #菜单
    def show_menu(self):
        print("="*36)
        print("       🏥 医院挂号问诊管理系统")
        print("=" * 36)
        print("""
        1.  添加患者
        2.  查看所有患者
        3.  查找患者
        4.  查看所有医生
        5.  添加医生
        6.  查看所有科室
        7.  患者挂号
        8.  查看待就诊挂号
        9.  处理就诊
        10. 查看历史就诊记录
        11. 导出数据到Excel
        12. 退出
        """)
        print("-"*36)

    def run(self):
        if not self.connection:
            print("❌系统启动失败，数据库连接异常！")
            return
        #没有问题
        while True:
            self.show_menu()#调用显示菜单的方法
            choice=input("请选择 (1-12):").strip()

            actions={
                '1':self.add_patients,
                '2':self.view_all_patients,
                '3':self.find_patient,
                '4':self.view_all_doctors,
                '5':self.add_doctor,
                '6':self.view_all_departments,
                '7':self.register_patient,
                '8':self.view_pending_registrations,
                '9':self.process_registration,
                '12':lambda :print("系统退出") or exit()
                # 你们写  3  4 5 6 7 8 。。
            }
            #调用系统提供的方法
            action=actions.get(choice)#根据你的选择进行获取功能

            if action:
                action()
            else:
                print("❌无效的选择，请重新输入！")


#创建对象 跑程序
if __name__ == '__main__':
    sytemss=HospitalManagementSystem()
    try:
        sytemss.run()
    except Exception as e:
        print(f"❌出问题了:{e}")
    finally:
        sytemss.close_connection()