from flask import Blueprint, request, jsonify, send_from_directory from flask_jwt_extended import jwt_required from db.db import get_db_connection from config.auth import role_required # โœ… imported properly now import mysql.connector from datetime import date import openpyxl from datetime import datetime import os import json prescription_bp = Blueprint('prescription', __name__) @prescription_bp.route('/prescriptions', methods=['GET']) @jwt_required() @role_required('user', 'doctor', 'admin') def get_prescriptions(): try: conn = get_db_connection() if conn is None: return jsonify({'error': 'Failed to connect to the database'}), 500 cursor = conn.cursor(dictionary=True) query = """ SELECT prescriptions.id AS prescription_id, prescriptions.prescription, prescriptions.special_note, prescriptions.date_issued, prescriptions.status, -- โœ… Added this line patients.id AS patient_id, patients.name AS patient_name, patients.contact, patients.address, patients.dob FROM prescriptions JOIN patients ON prescriptions.patient_id = patients.id ORDER BY prescriptions.date_issued DESC """ cursor.execute(query) results = cursor.fetchall() return jsonify(results), 200 except mysql.connector.Error as err: return jsonify({'error': f"Database error: {err}"}), 500 finally: if conn.is_connected(): cursor.close() conn.close() @prescription_bp.route('/today_prescriptions', methods=['GET']) @jwt_required() @role_required('user', 'doctor', 'admin') def today_prescriptions(): conn = get_db_connection() cur = conn.cursor(dictionary=True) try: today_str = date.today().strftime('%Y-%m-%d') # Fetch prescriptions for today cur.execute(""" SELECT p.id AS prescription_id, p.patient_id, pt.name AS patient_name, pt.dob, p.special_note, p.date_issued, p.status FROM prescriptions p JOIN patients pt ON p.patient_id = pt.id WHERE DATE(p.date_issued) = %s ORDER BY p.date_issued DESC """, (today_str,)) prescriptions = cur.fetchall() # Fetch medicines and doctor charges for each prescription for pres in prescriptions: # Medicines cur.execute(""" SELECT medicine_name, dosage, frequency, duration, instruction, total FROM prescription_medicines WHERE prescription_id = %s """, (pres["prescription_id"],)) pres["medicines"] = cur.fetchall() # Doctor charges cur.execute(""" SELECT charge_id, charge_name, charge_code, price, qty, total FROM prescription_doctor_charges WHERE prescription_id = %s """, (pres["prescription_id"],)) pres["doctor_charges"] = cur.fetchall() return jsonify(prescriptions), 200 except Exception as e: print("Error:", e) return jsonify({"status": "error", "message": str(e)}), 500 finally: cur.close() conn.close() # @prescription_bp.route('/get-prescriptions_previous/', methods=['GET']) # @jwt_required() # @role_required('doctor') # def get_prescriptions_previous(patient_id): # conn = get_db_connection() # cur = conn.cursor(dictionary=True) # try: # # ๐Ÿ‘ค Get patient info # cur.execute(""" # SELECT id, name, address, contact, dob, gender # FROM patients # WHERE id = %s # """, (patient_id,)) # patient = cur.fetchone() # if not patient: # return jsonify({"status": "error", "message": "Patient not found"}), 404 # # ๐Ÿ“„ Get all prescriptions for this patient # cur.execute(""" # SELECT id, special_note, date_issued, status # FROM prescriptions # WHERE patient_id = %s # ORDER BY date_issued DESC # """, (patient_id,)) # prescriptions = cur.fetchall() # # ๐Ÿง  For each prescription, add medicines + charges # for pres in prescriptions: # pres_id = pres['id'] # # ๐Ÿ’Š Medicines # cur.execute(""" # SELECT medicine_name, dosage, frequency, duration, instruction, total # FROM prescription_medicines # WHERE prescription_id = %s # """, (pres_id,)) # pres['medicines'] = cur.fetchall() # # ๐Ÿ’ต Doctor charges # cur.execute(""" # SELECT charge_name, charge_code, price, qty, total # FROM prescription_doctor_charges # WHERE prescription_id = %s # """, (pres_id,)) # pres['charges'] = cur.fetchall() # return jsonify({ # "status": "success", # "patient": patient, # "prescriptions": prescriptions # }) # except Exception as e: # print("Error:", e) # return jsonify({"status": "error", "message": str(e)}), 500 # finally: # cur.close() # conn.close() # @prescription_bp.route('/get-prescriptions_previous/', methods=['GET']) # @jwt_required() # @role_required('doctor') # def get_prescriptions_previous(patient_id): # conn = get_db_connection() # cur = conn.cursor(dictionary=True) # try: # # ๐Ÿ‘ค Get patient info # cur.execute(""" # SELECT id, name, address, contact, dob, gender # FROM patients # WHERE id = %s # """, (patient_id,)) # patient = cur.fetchone() # if not patient: # return jsonify({"status": "error", "message": "Patient not found"}), 404 # # ๐Ÿ“„ Get all prescriptions for this patient # cur.execute(""" # SELECT id, special_note, date_issued, status # FROM prescriptions # WHERE patient_id = %s # ORDER BY date_issued DESC # """, (patient_id,)) # prescriptions = cur.fetchall() # # ๐Ÿง  For each prescription, add medicines + charges and format date # for pres in prescriptions: # pres_id = pres['id'] # # Format date_issued # if pres.get('date_issued'): # pres['date_issued'] = pres['date_issued'].strftime("%Y/%m/%d %I:%M:%S %p") # # ๐Ÿ’Š Medicines # cur.execute(""" # SELECT medicine_name, dosage, frequency, duration, instruction, total # FROM prescription_medicines # WHERE prescription_id = %s # """, (pres_id,)) # pres['medicines'] = cur.fetchall() # # ๐Ÿ’ต Doctor charges # cur.execute(""" # SELECT charge_name, charge_code, price, qty, total # FROM prescription_doctor_charges # WHERE prescription_id = %s # """, (pres_id,)) # pres['charges'] = cur.fetchall() # # ๐Ÿงช Get all previous investigations for the same patient # cur.execute(""" # SELECT # id, present_complaint, past_history, family_history, # symptoms, probable_diagnosis, investigations, remarks, clinician, created_at # FROM patient_investigations # WHERE patient_id = %s # ORDER BY created_at DESC # """, (patient_id,)) # investigations = cur.fetchall() # # Convert JSON text field into Python objects & format date # for inv in investigations: # try: # if inv.get('investigations'): # inv['investigations'] = json.loads(inv['investigations']) # else: # inv['investigations'] = [] # except Exception: # inv['investigations'] = [] # # Format created_at # if inv.get('created_at'): # inv['created_at'] = inv['created_at'].strftime("%Y/%m/%d %I:%M:%S %p") # # โœ… Final response # return jsonify({ # "status": "success", # "patient": patient, # "prescriptions": prescriptions, # "investigations": investigations # }), 200 # except Exception as e: # print("โŒ Error in get_prescriptions_previous:", e) # return jsonify({"status": "error", "message": str(e)}), 500 # finally: # cur.close() # conn.close() # @prescription_bp.route('/get-patient-full-history/', methods=['GET']) # @jwt_required() # @role_required('doctor') # def get_patient_full_history(patient_id): # conn = get_db_connection() # cur = conn.cursor(dictionary=True) # try: # # ๐Ÿง Get Patient Info # cur.execute(""" # SELECT id, name, address, contact, dob, gender # FROM patients WHERE id = %s # """, (patient_id,)) # patient = cur.fetchone() # if not patient: # return jsonify({"status": "error", "message": "Patient not found"}), 404 # # ๐Ÿงช Get All Investigations # cur.execute(""" # SELECT # id, present_complaint, past_history, family_history, # symptoms, probable_diagnosis, investigations, remarks, clinician, created_at # FROM patient_investigations # WHERE patient_id = %s # ORDER BY created_at DESC # """, (patient_id,)) # investigations = cur.fetchall() # for inv in investigations: # inv['investigations'] = json.loads(inv['investigations'] or "[]") # inv['created_at'] = inv['created_at'].strftime("%Y/%m/%d %I:%M %p") if inv['created_at'] else None # # ๐Ÿ’Š Get All Prescriptions # cur.execute(""" # SELECT id, investigation_id, special_note, date_issued, status # FROM prescriptions # WHERE patient_id = %s # ORDER BY date_issued DESC # """, (patient_id,)) # prescriptions = cur.fetchall() # for pres in prescriptions: # pres_id = pres['id'] # pres['date_issued'] = pres['date_issued'].strftime("%Y/%m/%d %I:%M %p") if pres['date_issued'] else None # # ๐Ÿ’Š Prescription Medicines # cur.execute(""" # SELECT medicine_name, dosage, frequency, duration, instruction, total # FROM prescription_medicines # WHERE prescription_id = %s # """, (pres_id,)) # pres['medicines'] = cur.fetchall() # # ๐Ÿ’Š Pharmacy Medicines (new) # cur.execute(""" # SELECT medicine_no, medicine_name, dosage, frequency, duration, instruction # FROM prescription_pharmacy_medicines # WHERE prescription_id = %s # ORDER BY medicine_no ASC # """, (pres_id,)) # pres['pharmacy_medicines'] = cur.fetchall() # # ๐Ÿ’ต Doctor Charges # cur.execute(""" # SELECT charge_name, charge_code, price, qty, total # FROM prescription_doctor_charges # WHERE prescription_id = %s # """, (pres_id,)) # pres['charges'] = cur.fetchall() # pres['total_charges'] = sum(float(c['total'] or 0) for c in pres['charges']) # return jsonify({ # "status": "success", # "patient": patient, # "investigations": investigations, # "prescriptions": prescriptions # }), 200 # except Exception as e: # print("โŒ Error in get_patient_full_history:", e) # return jsonify({"status": "error", "message": str(e)}), 500 # finally: # cur.close() # conn.close() # @prescription_bp.route('/get-patient-full-history/', methods=['GET']) # @jwt_required() # @role_required('doctor') # def get_patient_full_history(patient_id): # conn = get_db_connection() # cur = conn.cursor(dictionary=True) # try: # # ๐Ÿง Get Patient Info # cur.execute(""" # SELECT id, name, address, contact, dob, gender # FROM patients # WHERE id = %s # """, (patient_id,)) # patient = cur.fetchone() # if not patient: # return jsonify({"status": "error", "message": "Patient not found"}), 404 # # ๐Ÿงช Get All Investigations # cur.execute(""" # SELECT # id, present_complaint, past_history, family_history, # symptoms, probable_diagnosis, investigations, remarks, clinician, created_at # FROM patient_investigations # WHERE patient_id = %s # ORDER BY created_at DESC # """, (patient_id,)) # investigations = cur.fetchall() # for inv in investigations: # inv['investigations'] = json.loads(inv['investigations'] or "[]") # inv['created_at'] = ( # inv['created_at'].strftime("%Y/%m/%d %I:%M %p") # if inv['created_at'] else None # ) # # ๐Ÿงช Get Lab Results and attach to investigations # cur.execute(""" # SELECT # id, patient_id, investigation_id, lab_date, results, notes, created_at # FROM lab_results # WHERE patient_id = %s # ORDER BY lab_date DESC # """, (patient_id,)) # lab_results = cur.fetchall() # # Parse JSON results and format dates # for lr in lab_results: # try: # lr['results'] = json.loads(lr['results'] or "[]") # except: # lr['results'] = [] # lr['lab_date'] = lr['lab_date'].strftime("%Y/%m/%d") if lr['lab_date'] else None # lr['created_at'] = lr['created_at'].strftime("%Y/%m/%d %I:%M %p") if lr['created_at'] else None # # Group lab results by investigation_id # lab_results_map = {} # for lr in lab_results: # inv_id = lr["investigation_id"] # if inv_id not in lab_results_map: # lab_results_map[inv_id] = [] # lab_results_map[inv_id].append(lr) # # Attach lab results to investigations # for inv in investigations: # inv_id = inv["id"] # inv["lab_results"] = lab_results_map.get(inv_id, []) # # ๐Ÿ’Š Get All Prescriptions # cur.execute(""" # SELECT id, investigation_id, special_note, date_issued, status # FROM prescriptions # WHERE patient_id = %s # ORDER BY date_issued DESC # """, (patient_id,)) # prescriptions = cur.fetchall() # for pres in prescriptions: # pres_id = pres['id'] # pres['date_issued'] = pres['date_issued'].strftime("%Y/%m/%d %I:%M %p") if pres['date_issued'] else None # # Prescription Medicines # cur.execute(""" # SELECT medicine_name, dosage, frequency, duration, instruction, total # FROM prescription_medicines # WHERE prescription_id = %s # """, (pres_id,)) # pres['medicines'] = cur.fetchall() # # Pharmacy Medicines # cur.execute(""" # SELECT medicine_no, medicine_name, dosage, frequency, duration, instruction # FROM prescription_pharmacy_medicines # WHERE prescription_id = %s # ORDER BY medicine_no ASC # """, (pres_id,)) # pres['pharmacy_medicines'] = cur.fetchall() # # Doctor Charges # cur.execute(""" # SELECT charge_name, charge_code, price, qty, total # FROM prescription_doctor_charges # WHERE prescription_id = %s # """, (pres_id,)) # pres['charges'] = cur.fetchall() # pres['total_charges'] = sum(float(c['total'] or 0) for c in pres['charges']) # # Final response # return jsonify({ # "status": "success", # "patient": patient, # "investigations": investigations, # "prescriptions": prescriptions # }), 200 # except Exception as e: # print("โŒ Error in get_patient_full_history:", e) # return jsonify({"status": "error", "message": str(e)}), 500 # finally: # cur.close() # conn.close() # @prescription_bp.route('/get-patient-full-history/', methods=['GET']) # @jwt_required() # @role_required('doctor') # def get_patient_full_history(patient_id): # """ # Get full patient history including: # - Patient info # - Investigations (with lab results) # - Prescriptions (with medicines and charges) # """ # conn = get_db_connection() # cur = conn.cursor(dictionary=True) # try: # # -------------------------- # # ๐Ÿ‘ค Patient Info # # -------------------------- # cur.execute(""" # SELECT id, name, address, contact, dob, gender # FROM patients # WHERE id = %s # """, (patient_id,)) # patient = cur.fetchone() # if not patient: # return jsonify({"status": "error", "message": "Patient not found"}), 404 # # -------------------------- # # ๐Ÿงช Investigations # # -------------------------- # cur.execute(""" # SELECT # id, present_complaint, past_history, family_history, # symptoms, probable_diagnosis, investigations, remarks, clinician, created_at # FROM patient_investigations # WHERE patient_id = %s # ORDER BY created_at DESC # """, (patient_id,)) # investigations = cur.fetchall() # # Parse JSON fields and format dates # for inv in investigations: # inv['investigations'] = json.loads(inv['investigations'] or "[]") # inv['created_at'] = inv['created_at'].strftime("%Y/%m/%d %I:%M %p") if inv['created_at'] else None # # -------------------------- # # ๐Ÿ”ฌ Lab Results # # -------------------------- # cur.execute(""" # SELECT # id, patient_id, investigation_id, lab_date, results, notes, created_at # FROM lab_results # WHERE patient_id = %s # ORDER BY lab_date DESC # """, (patient_id,)) # lab_results = cur.fetchall() # # Parse JSON results and format dates # for lr in lab_results: # try: # lr['results'] = json.loads(lr['results'] or "[]") # except Exception: # lr['results'] = [] # lr['lab_date'] = lr['lab_date'].strftime("%Y/%m/%d %I:%M %p") if lr['lab_date'] else None # lr['created_at'] = lr['created_at'].strftime("%Y/%m/%d %I:%M %p") if lr['created_at'] else None # # Group lab results by investigation_id # lab_results_map = {} # for lr in lab_results: # inv_id = lr['investigation_id'] # lab_results_map.setdefault(inv_id, []).append(lr) # # Attach lab results to investigations # for inv in investigations: # inv_id = inv['id'] # inv['lab_results'] = lab_results_map.get(inv_id, []) # # -------------------------- # # ๐Ÿ’Š Prescriptions # # -------------------------- # cur.execute(""" # SELECT id, investigation_id, special_note, date_issued, status # FROM prescriptions # WHERE patient_id = %s # ORDER BY date_issued DESC # """, (patient_id,)) # prescriptions = cur.fetchall() # for pres in prescriptions: # pres_id = pres['id'] # pres['date_issued'] = pres['date_issued'].strftime("%Y/%m/%d %I:%M %p") if pres['date_issued'] else None # # Prescription Medicines # cur.execute(""" # SELECT medicine_name, dosage, frequency, duration, instruction, total # FROM prescription_medicines # WHERE prescription_id = %s # """, (pres_id,)) # pres['medicines'] = cur.fetchall() # # Pharmacy Medicines # cur.execute(""" # SELECT medicine_no, medicine_name, dosage, frequency, duration, instruction # FROM prescription_pharmacy_medicines # WHERE prescription_id = %s # ORDER BY medicine_no ASC # """, (pres_id,)) # pres['pharmacy_medicines'] = cur.fetchall() # # Doctor Charges # cur.execute(""" # SELECT charge_name, charge_code, price, qty, total # FROM prescription_doctor_charges # WHERE prescription_id = %s # """, (pres_id,)) # pres['charges'] = cur.fetchall() # pres['total_charges'] = sum(float(c['total'] or 0) for c in pres['charges']) # # -------------------------- # # โœ… Final Response # # -------------------------- # return jsonify({ # "status": "success", # "patient": patient, # "investigations": investigations, # "prescriptions": prescriptions # }), 200 # except Exception as e: # print("โŒ Error in get_patient_full_history:", e) # return jsonify({"status": "error", "message": str(e)}), 500 # finally: # cur.close() # conn.close() @prescription_bp.route('/get-patient-full-history/', methods=['GET']) @jwt_required() @role_required('doctor') def get_patient_full_history(patient_id): """ Get full patient history including: - Patient info (with weight_date, weight_value) - Investigations (with lab results) - Prescriptions (with medicines and charges) """ conn = get_db_connection() cur = conn.cursor(dictionary=True) try: # -------------------------- # ๐Ÿ‘ค Patient Info # -------------------------- cur.execute(""" SELECT id, name, address, contact, dob, gender, weight_date, weight_value FROM patients WHERE id = %s """, (patient_id,)) patient = cur.fetchone() if not patient: return jsonify({"status": "error", "message": "Patient not found"}), 404 # Format weight_date if patient.get("weight_date"): patient["weight_date"] = patient["weight_date"].strftime("%Y/%m/%d") # -------------------------- # ๐Ÿงช Investigations # -------------------------- cur.execute(""" SELECT id, present_complaint, past_history, family_history, symptoms, probable_diagnosis, investigations, remarks, clinician, created_at FROM patient_investigations WHERE patient_id = %s ORDER BY created_at DESC """, (patient_id,)) investigations = cur.fetchall() # Parse JSON fields and format dates for inv in investigations: inv['investigations'] = json.loads(inv['investigations'] or "[]") inv['created_at'] = ( inv['created_at'].strftime("%Y/%m/%d %I:%M %p") if inv['created_at'] else None ) # -------------------------- # ๐Ÿ”ฌ Lab Results # -------------------------- cur.execute(""" SELECT id, patient_id, investigation_id, lab_date, results, notes, created_at FROM lab_results WHERE patient_id = %s ORDER BY lab_date DESC """, (patient_id,)) lab_results = cur.fetchall() # Parse JSON results and format dates for lr in lab_results: try: lr['results'] = json.loads(lr['results'] or "[]") except Exception: lr['results'] = [] lr['lab_date'] = ( lr['lab_date'].strftime("%Y/%m/%d %I:%M %p") if lr['lab_date'] else None ) lr['created_at'] = ( lr['created_at'].strftime("%Y/%m/%d %I:%M %p") if lr['created_at'] else None ) # Group lab results by investigation_id lab_results_map = {} for lr in lab_results: inv_id = lr['investigation_id'] lab_results_map.setdefault(inv_id, []).append(lr) # Attach grouped lab results for inv in investigations: inv_id = inv['id'] inv['lab_results'] = lab_results_map.get(inv_id, []) # -------------------------- # ๐Ÿ’Š Prescriptions # -------------------------- cur.execute(""" SELECT id, investigation_id, special_note, date_issued, status FROM prescriptions WHERE patient_id = %s ORDER BY date_issued DESC """, (patient_id,)) prescriptions = cur.fetchall() for pres in prescriptions: pres_id = pres['id'] pres['date_issued'] = ( pres['date_issued'].strftime("%Y/%m/%d %I:%M %p") if pres['date_issued'] else None ) # Prescription Medicines cur.execute(""" SELECT medicine_name, dosage, frequency, duration, instruction, total FROM prescription_medicines WHERE prescription_id = %s """, (pres_id,)) pres['medicines'] = cur.fetchall() # Pharmacy Medicines cur.execute(""" SELECT medicine_no, medicine_name, dosage, frequency, duration, instruction FROM prescription_pharmacy_medicines WHERE prescription_id = %s ORDER BY medicine_no ASC """, (pres_id,)) pres['pharmacy_medicines'] = cur.fetchall() # Doctor Charges cur.execute(""" SELECT charge_name, charge_code, price, qty, total FROM prescription_doctor_charges WHERE prescription_id = %s """, (pres_id,)) pres['charges'] = cur.fetchall() pres['total_charges'] = sum(float(c['total'] or 0) for c in pres['charges']) # -------------------------- # โœ… Final Response # -------------------------- return jsonify({ "status": "success", "patient": patient, "investigations": investigations, "prescriptions": prescriptions }), 200 except Exception as e: print("โŒ Error in get_patient_full_history:", e) return jsonify({"status": "error", "message": str(e)}), 500 finally: cur.close() conn.close() @prescription_bp.route('/get-all-investigations/', methods=['GET']) @jwt_required() @role_required('doctor') def get_all_investigations(patient_id): conn = get_db_connection() cur = conn.cursor(dictionary=True) try: cur.execute(""" SELECT present_complaint, past_history, family_history, symptoms, probable_diagnosis, investigations, -- JSON string stored in DB remarks, clinician, created_at FROM patient_investigations WHERE patient_id = %s ORDER BY created_at DESC """, (patient_id,)) investigations = cur.fetchall() # Parse JSON fields and prepare investigations list for inv in investigations: if inv.get('investigations'): try: inv['investigations'] = json.loads(inv['investigations']) except Exception: inv['investigations'] = [] else: inv['investigations'] = [] if inv.get('created_at'): inv['created_at'] = inv['created_at'].strftime("%Y-%m-%d %H:%M:%S") return jsonify({ "status": "success", "investigations": investigations }), 200 except Exception as e: print("โŒ Error in get_all_investigations:", e) return jsonify({"status": "error", "message": str(e)}), 500 finally: cur.close() conn.close() # @prescription_bp.route('/get_prescription_data', methods=['GET']) # @jwt_required() # @role_required('doctor') # def get_prescription_data(): # conn = None # cursor = None # try: # # Connect to database # try: # conn = get_db_connection() # cursor = conn.cursor(dictionary=True) # except Exception as db_err: # return jsonify({'error': f'Database connection failed: {str(db_err)}'}), 500 # # Fetch doctor data # cursor.execute("SELECT id, excel_file_path FROM doctors LIMIT 1;") # doctor_data = cursor.fetchone() # if not doctor_data: # return jsonify({'error': 'No doctor record found in database'}), 404 # excel_file_path = doctor_data.get('excel_file_path') # if not excel_file_path: # return jsonify({'error': 'Excel file path missing for doctor'}), 400 # # Check if file exists # if not os.path.exists(excel_file_path): # return jsonify({'error': f'Excel file not found: {excel_file_path}'}), 404 # # Load Excel file safely # try: # wb = openpyxl.load_workbook(excel_file_path) # sheet = wb.active # except Exception as file_err: # return jsonify({'error': f'Failed to read Excel file: {str(file_err)}'}), 400 # # Read headers # headers = [cell.value for cell in sheet[1] if cell.value] # if "Medicine Name" not in headers: # return jsonify({'error': '"Medicine Name" column not found in Excel file'}), 400 # col_index = headers.index("Medicine Name") + 1 # Excel is 1-based # # Collect medicine names # medicines = [] # for row in sheet.iter_rows(min_row=2, values_only=True): # medicine = row[col_index - 1] # if medicine: # medicines.append(medicine.strip()) # if not medicines: # return jsonify({'error': 'No medicine data found in Excel file'}), 404 # # Remove duplicates while preserving order # unique_medicines = list(dict.fromkeys(medicines)) # return jsonify({'prescriptions': unique_medicines}), 200 # except Exception as e: # # Catch-all for unexpected issues # return jsonify({'error': f'Unexpected error: {str(e)}'}), 500 # finally: # # Ensure cleanup # if cursor: # cursor.close() # if conn: # conn.close() # @prescription_bp.route('/save_prescription', methods=['POST']) # @jwt_required() # @role_required('doctor') # def save_prescription(): # data = request.get_json() # print("Received payload:", data) # patient_id = data.get('patient_id') # investigation_id = data.get('investigation_id') # NEW: get investigation_id # special_note = data.get('special_note', '') # status = data.get('status', 0) # date_issued = datetime.now() # medicines = data.get('medicines', []) # charges = data.get('charges', []) # if not patient_id: # return jsonify({"status": "error", "message": "Patient ID is required"}), 400 # conn = get_db_connection() # cur = conn.cursor() # try: # # Insert prescription including investigation_id # cur.execute(""" # INSERT INTO prescriptions # (patient_id, investigation_id, special_note, date_issued, status) # VALUES (%s, %s, %s, %s, %s) # """, (patient_id, investigation_id, special_note, date_issued, status)) # prescription_id = cur.lastrowid # # Insert medicines # for m in medicines: # cur.execute(""" # INSERT INTO prescription_medicines # (prescription_id, medicine_id, medicine_name, dosage, frequency, duration, instruction, total) # VALUES (%s, %s, %s, %s, %s, %s, %s, %s) # """, ( # prescription_id, # m.get('medicine_id'), # m.get('medicine_name', ''), # m.get('dosage', ''), # m.get('frequency', ''), # m.get('duration', ''), # m.get('instruction', ''), # m.get('total', '') # )) # # Insert doctor charges # for c in charges: # cur.execute(""" # INSERT INTO prescription_doctor_charges # (prescription_id, charge_id, charge_name, charge_code, price, qty, total) # VALUES (%s, %s, %s, %s, %s, %s, %s) # """, ( # prescription_id, # c.get('charge_id'), # c.get('charge_name', ''), # c.get('charge_code', ''), # c.get('price', 0), # c.get('qty', 0), # c.get('total', 0) # )) # conn.commit() # print(f"Prescription {prescription_id} saved successfully") # return jsonify({ # "status": "success", # "message": "Prescription saved successfully", # "prescription_id": prescription_id # }) # except Exception as e: # conn.rollback() # print("Error:", str(e)) # return jsonify({"status": "error", "message": str(e)}), 500 # finally: # cur.close() # conn.close() # @prescription_bp.route('/save_full_prescription', methods=['POST']) # @jwt_required() # @role_required('doctor') # def save_full_prescription(): # data = request.json # patient_id = data.get("patient_id") # if not patient_id: # return jsonify({"status": "error", "message": "Missing patient_id"}), 400 # conn = get_db_connection() # cur = conn.cursor() # try: # # ------------------------------------------------------ # # 1๏ธโƒฃ SAVE INVESTIGATION DATA # # ------------------------------------------------------ # cur.execute(""" # INSERT INTO patient_investigations # (patient_id, present_complaint, past_history, family_history, # investigations, remarks, created_at) # VALUES (%s,%s,%s,%s,%s,%s,NOW()) # """, ( # patient_id, # data.get('present_complaint', ''), # data.get('past_history', ''), # data.get('family_history', ''), # json.dumps(data.get('investigations', [])), # data.get('remarks', '') # )) # investigation_id = cur.lastrowid # # ------------------------------------------------------ # # 2๏ธโƒฃ SAVE PRESCRIPTION MAIN RECORD # # ------------------------------------------------------ # cur.execute(""" # INSERT INTO prescriptions # (patient_id, investigation_id, special_note, date_issued) # VALUES (%s,%s,%s,NOW()) # """, (patient_id, investigation_id, data.get('special_note', ''))) # prescription_id = cur.lastrowid # # ------------------------------------------------------ # # 3๏ธโƒฃ SAVE PRESCRIPTION MEDICINES # # ------------------------------------------------------ # for m in data.get("medicines", []): # cur.execute(""" # INSERT INTO prescription_medicines # (prescription_id, medicine_id, medicine_name, dosage, frequency, duration, instruction, total) # VALUES (%s,%s,%s,%s,%s,%s,%s,%s) # """, ( # prescription_id, # m.get("medicine_id"), # m.get("medicine_name"), # m.get("dosage"), # m.get("frequency"), # m.get("duration"), # m.get("instruction"), # m.get("total") # )) # # ------------------------------------------------------ # # 4๏ธโƒฃ SAVE DOCTOR CHARGES # # ------------------------------------------------------ # for c in data.get("charges", []): # cur.execute(""" # INSERT INTO prescription_doctor_charges # (prescription_id, charge_id, charge_name, charge_code, price, qty, total) # VALUES (%s,%s,%s,%s,%s,%s,%s) # """, ( # prescription_id, # c.get("charge_id"), # c.get("charge_name"), # c.get("charge_code"), # c.get("price"), # c.get("qty"), # c.get("total") # )) # conn.commit() # return jsonify({ # "status": "success", # "message": "Full prescription saved successfully", # "prescription_id": prescription_id, # "investigation_id": investigation_id # }) # except Exception as e: # conn.rollback() # return jsonify({"status": "error", "message": str(e)}), 500 # finally: # cur.close() # conn.close() @prescription_bp.route('/save_full_prescription', methods=['POST']) @jwt_required() @role_required('doctor') def save_full_prescription(): data = request.json patient_id = data.get("patient_id") if not patient_id: return jsonify({"status": "error", "message": "Missing patient_id"}), 400 conn = get_db_connection() cur = conn.cursor() try: # ------------------------------------------------------ # 1๏ธโƒฃ SAVE INVESTIGATION DATA # ------------------------------------------------------ cur.execute(""" INSERT INTO patient_investigations (patient_id, present_complaint, past_history, family_history, investigations, remarks, created_at) VALUES (%s,%s,%s,%s,%s,%s,NOW()) """, ( patient_id, data.get('present_complaint', ''), data.get('past_history', ''), data.get('family_history', ''), json.dumps(data.get('investigations', [])), data.get('remarks', '') )) investigation_id = cur.lastrowid # ------------------------------------------------------ # 2๏ธโƒฃ SAVE PRESCRIPTION MAIN RECORD # ------------------------------------------------------ cur.execute(""" INSERT INTO prescriptions (patient_id, investigation_id, special_note, date_issued) VALUES (%s,%s,%s,NOW()) """, (patient_id, investigation_id, data.get('special_note', ''))) prescription_id = cur.lastrowid # ------------------------------------------------------ # 3๏ธโƒฃ SAVE PRESCRIPTION MEDICINES # ------------------------------------------------------ for m in data.get("medicines", []): cur.execute(""" INSERT INTO prescription_medicines (prescription_id, medicine_id, medicine_name, dosage, frequency, duration, instruction, total) VALUES (%s,%s,%s,%s,%s,%s,%s,%s) """, ( prescription_id, m.get("medicine_id"), m.get("medicine_name"), m.get("dosage"), m.get("frequency"), m.get("duration"), m.get("instruction"), m.get("total") )) # ------------------------------------------------------ # 4๏ธโƒฃ SAVE DOCTOR CHARGES # ------------------------------------------------------ for c in data.get("charges", []): cur.execute(""" INSERT INTO prescription_doctor_charges (prescription_id, charge_id, charge_name, charge_code, price, qty, total) VALUES (%s,%s,%s,%s,%s,%s,%s) """, ( prescription_id, c.get("charge_id"), c.get("charge_name"), c.get("charge_code"), c.get("price"), c.get("qty"), c.get("total") )) # ------------------------------------------------------ # 5๏ธโƒฃ UPDATE QUEUE STATUS TO COMPLETE # ------------------------------------------------------ cur.execute(""" UPDATE patient_queue SET status = 'completed' WHERE patient_id = %s AND DATE(created_at) = CURDATE() """, (patient_id,)) conn.commit() return jsonify({ "status": "success", "message": "Full prescription saved successfully", "prescription_id": prescription_id, "investigation_id": investigation_id }) except Exception as e: conn.rollback() return jsonify({"status": "error", "message": str(e)}), 500 finally: cur.close() conn.close() @prescription_bp.route('/save_prescription_pharmacy', methods=['POST']) @jwt_required() @role_required('doctor') def save_prescription_pharmacy(): data = request.get_json() print("Received Pharmacy Payload:", data) prescription_id = data.get('prescription_id') # <-- IMPORTANT medicines = data.get('medicines', []) if not prescription_id: return jsonify({"status": "error", "message": "Prescription ID is required"}), 400 conn = get_db_connection() cur = conn.cursor() try: # Save pharmacy medicines only for m in medicines: cur.execute(""" INSERT INTO prescription_pharmacy_medicines (prescription_id, medicine_no, medicine_name, dosage, frequency, instruction, duration) VALUES (%s, %s, %s, %s, %s, %s, %s) """, ( prescription_id, m.get('row_no'), m.get('medicine_name'), m.get('dosage'), m.get('frequency'), m.get('instruction'), m.get('duration') )) conn.commit() return jsonify({ "status": "success", "message": "Pharmacy Prescription Saved", "prescription_id": prescription_id }) except Exception as e: conn.rollback() return jsonify({"status": "error", "message": str(e)}), 500 finally: cur.close() conn.close() # -------------------- LIST MEDICINES -------------------- @prescription_bp.route('/list_medicines', methods=['GET']) @jwt_required() @role_required('doctor') def list_medicines(): conn = get_db_connection() if not conn: return jsonify({"error": "Database connection failed"}), 500 try: cur = conn.cursor(dictionary=True) cur.execute("SELECT id, medicine_name FROM pharmacy_medicine_name ORDER BY id DESC") rows = cur.fetchall() return jsonify(rows), 200 except Exception as e: return jsonify({"error": str(e)}), 500 finally: cur.close() conn.close() # -------------------- ADD MEDICINE -------------------- @prescription_bp.route('/add_medicine', methods=['POST']) @jwt_required() @role_required('doctor') def add_medicine(): data = request.get_json() if not data or "medicine_name" not in data: return jsonify({"error": "medicine_name is required"}), 400 name = data["medicine_name"].strip() if name == "": return jsonify({"error": "medicine_name cannot be empty"}), 400 conn = get_db_connection() if not conn: return jsonify({"error": "Database connection failed"}), 500 try: cur = conn.cursor() cur.execute("INSERT INTO pharmacy_medicine_name (medicine_name) VALUES (%s)", (name,)) conn.commit() return jsonify({"message": "Medicine added successfully"}), 201 except mysql.connector.IntegrityError: return jsonify({"error": "Medicine already exists"}), 409 except Exception as e: return jsonify({"error": str(e)}), 500 finally: cur.close() conn.close() # -------------------- EDIT MEDICINE -------------------- @prescription_bp.route('/edit_medicine/', methods=['PUT']) @jwt_required() @role_required('doctor') def edit_medicine(medicine_id): try: data = request.get_json(force=True) if not data or "medicine_name" not in data: return jsonify({"error": "medicine_name is required"}), 400 new_name = data["medicine_name"].strip() if new_name == "": return jsonify({"error": "medicine_name cannot be empty"}), 400 conn = get_db_connection() if not conn: return jsonify({"error": "Database connection failed"}), 500 cur = conn.cursor() cur.execute("SELECT id FROM pharmacy_medicine_name WHERE id = %s", (medicine_id,)) if cur.fetchone() is None: return jsonify({"error": "Medicine not found"}), 404 cur.execute("UPDATE pharmacy_medicine_name SET medicine_name=%s WHERE id=%s", (new_name, medicine_id)) conn.commit() return jsonify({"message": "Medicine updated successfully"}), 200 except Exception as e: return jsonify({"error": str(e)}), 500 finally: if 'cur' in locals(): cur.close() if 'conn' in locals(): conn.close() # -------------------- DELETE MEDICINE -------------------- @prescription_bp.route('/delete_medicine/', methods=['DELETE']) @jwt_required() @role_required('doctor') def delete_medicine(medicine_id): conn = get_db_connection() if not conn: return jsonify({"error": "Database connection failed"}), 500 try: cur = conn.cursor() cur.execute("SELECT id FROM pharmacy_medicine_name WHERE id = %s", (medicine_id,)) if cur.fetchone() is None: return jsonify({"error": "Medicine not found"}), 404 cur.execute("DELETE FROM pharmacy_medicine_name WHERE id=%s", (medicine_id,)) conn.commit() return jsonify({"message": "Medicine deleted successfully"}), 200 except Exception as e: return jsonify({"error": str(e)}), 500 finally: cur.close() conn.close() @prescription_bp.route('/get_prescription_data', methods=['GET']) @jwt_required() @role_required('doctor') def get_prescription_data(): """ Fetch all medicine names from pharmacy_medicine_name table for prescriptions (no Excel dependency). """ conn = None cur = None try: conn = get_db_connection() if not conn: return jsonify({'error': 'Database connection failed'}), 500 cur = conn.cursor(dictionary=True) cur.execute("SELECT medicine_name FROM pharmacy_medicine_name ORDER BY medicine_name ASC") rows = cur.fetchall() if not rows: return jsonify({'error': 'No medicines found in database'}), 404 # Extract medicine names medicine_list = [row['medicine_name'] for row in rows if row['medicine_name']] return jsonify({'prescriptions': medicine_list}), 200 except Exception as e: return jsonify({'error': f'Unexpected error: {str(e)}'}), 500 finally: if cur: cur.close() if conn: conn.close()