Database

CREATE DEFINER=`root`@`192.168.1.%` PROCEDURE `sp_open_file`(IN param_file_no int, IN param_clinic_id int, IN param_doctor_id int, IN param_account_category int, IN param_account_id int)
BEGIN
    DECLARE param_patient_account_number BIGINT;
    DECLARE param_price_list_id int;
    DECLARE param_account_number BIGINT;

    SET param_patient_account_number = (SELECT account_number FROM patients WHERE file_no = param_file_no);

    IF (param_account_category  = 1) THEN
        SET param_account_number = param_patient_account_number;
    ELSE
        SET param_account_number = (SELECT account_number FROM accounts WHERE account_id = param_account_id);
    END IF;

    SET param_price_list_id = (SELECT price_list_id FROM accounts WHERE account_number = param_account_number);

    INSERT INTO visits (file_no, clinic_id, doctor_id, account_category, account_number, price_list_id) VALUES (param_file_no, param_clinic_id, param_doctor_id, param_account_category , param_account_number, param_price_list_id);
    UPDATE patients SET file_is_open = TRUE;
END