procedures.sql

DELIMITER //
--7
CREATE OR REPLACE PROCEDURE printPatients(Doc IN VARCHAR(10))
BEGIN
    SELECT p.AID, p.name 
    FROM patient p RIGHT JOIN presc r ON p.AID=r.PatientID
    RIGHT JOIN doctor d ON d.Aadhar = r.DocID
    WHERE d.name = Doc;
END;
//

--4
CREATE PROCEDURE printDrugs(Comp IN VARCHAR(20))
BEGIN
    SELECT * FROM drug d
    JOIN comapany c ON c.CName = d.CName
    WHERE c.CName = Comp;
END; 
//

--3
CREATE PROCEDURE printPrescription(Name IN VARCHAR(10), IN dop DATE)
BEGIN
    SELECT * FROM prescDetails pd
    JOIN presc r ON r.prescID = pd.PID
    RIGHT JOIN patient p ON p.AID = r.PatientID
    WHERE r.DOP = dop AND p.name = Name;
END;
//

--2
CREATE PROCEDURE printPeriodPrescription(IN Name VARCHAR(10), IN begin_date DATE, IN end_date DATE)
BEGIN
    SELECT * FROM prescDetails pd
    JOIN presc r ON r.prescID = pd.PID
    RIGHT JOIN patient p ON p.AID = r.patientID
    WHERE p.name = Name AND r.DOP>=begin_date AND r.DOP<=end_date;
END;
//


--1.1(Company)
CREATE OR REPLACE PROCEDURE insertCompany(Name IN VARCHAR(20), phone IN INT)
BEGIN
    INSERT INTO company(Name, phone);
END;
//

--1.1(Pharmacy)
CREATE PROCEDURE insertPharmacy(IN Name VARCHAR(20), IN Address VARCHAR(50), IN phone INT)
BEGIN 
    INSERT INTO pharmacy(Name, Address, phone);
END;
//

--1.1(Sells)
CREATE PROCEDURE insertSells(IN pharmName VARCHAR(20), IN compName VARCHAR(20), IN drugName VARCHAR(10), IN price INT)
BEGIN
    INSERT INTO sells(pharmName, compName, drugName, price);
END;
//

--1.1(Contracts)
CREATE PROCEDURE insertContract(IN pharmName VARCHAR(20), IN compName VARCHAR(20), IN begin_date DATE, IN eDate DATE, IN super VARCHAR(10), IN cont VARCHAR(50))
BEGIN
    INSERT INTO contract(pharmName,compName, begin_date, eDate, super, cont);
End;
//

--1.1(treated)
CREATE PROCEDURE insertTreated(IN DID INT, IN patID INT)
BEGIN
    INSERT INTO treated(DID,patID);
END;
//

--1.1(drug)
CREATE PROCEDURE insertDrug(IN compName VARCHAR(20), IN drugName VARCHAR(10), IN form VARCHAR(50))
BEGIN
    INSERT INTO drug(compName, drugName, form);
END;
//

--1.1(prescDetails)
CREATE PROCEDURE insertPrescDetails(IN ID INT, IN compName VARCHAR(20), IN drugName VARCHAR(10), IN qt INT)
BEGIN
    INSERT INTO prescDetails(ID, compName, drugName, qt);
END;
//

--1.1(Patient)
CREATE PROCEDURE insertPatient(IN UID INT, IN Name VARCHAR(10), IN a INT, IN addr VARCHAR(50), IN ID INT)
BEGIN
    INSERT INTO patient(UID, Name, a, addr, ID);
END;
//

--1.1(Prescription)
CREATE OR REPLACE PROCEDURE insertContracts(p_PName IN VARCHAR2, p_CName IN VARCHAR2, p_SDate IN DATE, p_EDate IN DATE, p_Supervisor IN VARCHAR2, p_Content IN VARCHAR2) IS
BEGIN
    INSERT INTO contracts(PName, CName, SDate, EDate, Supervisor, Content) VALUES (p_PName, p_CName, p_SDate, p_EDate, p_Supervisor, p_Content);
END;

--1.1(Doctor)
CREATE PROCEDURE insertDoctor(IN DID INT, IN docName VARCHAR(10), IN spec VARCHAR(10), IN yrs INT)
BEGIN
    INSERT INTO doctor(DID, docName, spec, yrs);
END;
//