------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                                         ------------------- Mahdi Asali | 206331795 (and)  Elon Avi Sror |  305370801 | Assignement 4  -------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


drop table Docking;
drop table Piers;
drop table Ships;

alter session set nls_date_format = 'dd/mm/yy';

SET SERVEROUTPUT ON
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                                         ------------------- Part 1-------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

create table Piers(
    pid NUMBER(2) PRIMARY KEY,
    name varchar2(20),
    capacity NUMBER(5) NOT NULL,
    type varchar2(20)UNIQUE,
    totalShips INTEGER DEFAULT(0)
)
create table Ships(
sid  NUMBER(5) PRIMARY KEY,
name  varchar2(20),
country  varchar2(20),
cargo_weight  NUMBER(5) NOT NULL
)
create table Docking(
sid  NUMBER(5)  ,
pid NUMBER(2)  ,
arrivaldate  DATE,
departuredate  DATE,
Primary key(sid,pid),
CONSTRAINT pierid FOREIGN KEY( pid) REFERENCES Piers(pid),
CONSTRAINT shipid FOREIGN KEY( sid) REFERENCES Ships(sid)
)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                                         ------------------- Part 2  -------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

---------------TRIGGER----------------

--Please add EXCEPTION..
CREATE  OR REPLACE TRIGGER  totalTrigger

BEFORE INSERT ON DOCKING
FOR EACH ROW
 DECLARE
 varCapacity Piers.Capacity%TYPE;
 varCargoWeight SHIPS.CARGO_WEIGHT%TYPE;

BEGIN


update Piers
set TOTALSHIPS =TOTALSHIPS +1
where
 PID= :NEW.PID
 and
 :NEW.arrivaldate <= :NEW.departuredate
 and
Piers.Capacity >= (select cargo_weight
                                                                                                    from SHIPS
                                                                                                        where SHIPS.sid = :NEW.sid);

 --If The CargoWeight is Illegal
SELECT Capacity INTO varCapacity FROM Piers WHERE Piers.pid =:NEW.pid;

SELECT cargo_weight INTO varCargoWeight FROM Ships WHERE Ships.sid=:NEW.sid;

 if varCargoWeight > varCapacity
    Then
            Raise_Application_Error (-20343, 'ERROR: Cargo weight should be smaller than Capacity.');
END IF;
 -- if the Date is Illegal.
IF :NEW.arrivaldate> :NEW.departuredate   THEN
Raise_Application_Error (-20343, 'ERROR: Illegal date .');
END IF;
END totalTrigger;
/

------------------------------------------------------------------------------------
INSERT INTO Piers VALUES(1,'A',30000,'agricultural exports',0);
INSERT INTO Piers VALUES(2,'B',30000,'timber',0);
INSERT INTO Piers VALUES(3,'C',30000,'metals',0);
INSERT INTO Piers VALUES(4,'D',30000,'sling',0);
INSERT INTO Piers VALUES(5,'E',60000,'Panamax',0);
INSERT INTO Piers VALUES(6,'F',30000,'bulk',0);


------------------------------------------------------------------------------------
INSERT INTO Ships VALUES(11,'S1','China',30000);
INSERT INTO Ships VALUES(22,'S2','Zimbabwe',25000);
INSERT INTO Ships VALUES(33,'S3','Guatemala',15000);
INSERT INTO Ships VALUES(44,'S4','China',25000);
INSERT INTO Ships VALUES(55,'S5','Marshall Islands',20000);
INSERT INTO Ships VALUES(66,'S6','Russia',20000);
INSERT INTO Ships VALUES(77,'S7','Malta',45000);
INSERT INTO Ships VALUES(88,'S8','Panama',50000);
INSERT INTO Ships VALUES(99,'S9','Malta',15000);
INSERT INTO Ships VALUES(1010,'S10','Marshall Islands',20000);
INSERT INTO Ships VALUES(1111,'S11','Liberia',25000);
INSERT INTO Ships VALUES(1212,'S12','Liberia',15000);
INSERT INTO Ships VALUES(1313,'S13','Zimbabwe',20000);
INSERT INTO Ships VALUES(1414,'S14','Panama',55000);
------------------------------------------------------------------------------------
INSERT INTO Docking VALUES(88,5,'15/8/17','15/8/17');
INSERT INTO Docking VALUES(22,1,'17/8/17','18/8/17');
INSERT INTO Docking VALUES(1414,5,'16/8/17','20/8/17');
INSERT INTO Docking VALUES(1010,3,'15/8/17','19/8/17');
INSERT INTO Docking VALUES(99,2,'16/8/17','16/8/17');
INSERT INTO Docking VALUES(33,2,'15/8/17','15/8/17');
INSERT INTO Docking VALUES(66,3,'17/8/17','19/8/17');
INSERT INTO Docking VALUES(1111,5,'16/8/17','16/8/17');
INSERT INTO Docking VALUES(1212,2,'15/8/17','15/8/17');
INSERT INTO Docking VALUES(55,4,'17/8/17','20/8/17');
INSERT INTO Docking VALUES(77,5,'16/8/17','16/8/17');
INSERT INTO Docking VALUES(44,1,'16/8/17','17/8/17');
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                                         ------------------- Part 3  -------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

select * from Piers;



------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                                         ------------------- Part 4 (a)  -------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-- NORMAL CURSOR

DECLARE

/* Output variables to hold the result of the query: */
INPUT_PID DOCKING.PID%TYPE;
showSID DOCKING.SID%TYPE;
showPID DOCKING.PID%TYPE;
showARR DOCKING.ARRIVALDATE%TYPE;
showDEP DOCKING.DEPARTUREDATE%TYPE;
CURSOR pat_cursor  (INPUT_PID  DOCKING.PID%TYPE)
IS
SELECT sid,pid,arrivaldate,departuredate from DOCKING
where
DOCKING.pid=INPUT_PID
and
(DOCKING.departuredate-DOCKING.arrivaldate)=(select max(d1.departuredate-d1.arrivaldate) from DOCKING d1 where d1.PID=INPUT_PID);
BEGIN
INPUT_PID := '&INPUT_PIDQ5';
DBMS_OUTPUT.put_line('This report for pier '||TO_CHAR(INPUT_PID) );
OPEN pat_cursor (INPUT_PID);
LOOP
FETCH pat_cursor INTO  showSID,showPID, showARR, showDEP;
EXIT  WHEN pat_cursor%NOTFOUND;
DBMS_OUTPUT.put_line(TO_CHAR(showSID) || ' | ' || TO_CHAR(showARR) || ' | ' || TO_CHAR(showDEP) || ' | ' || TO_CHAR(showDEP-showARR) ||  ' days' );
END LOOP;
CLOSE pat_cursor;
END;
/


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                                         ------------------- Part 4 (b) -------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE
-- Declare cursor to get employee info
CURSOR Docking_Cursor (INPUT_PID NUMBER)
IS
SELECT sid,pid,arrivaldate,departuredate FROM DOCKING
WHERE pid = INPUT_PID
and
(DOCKING.departuredate-DOCKING.arrivaldate)=(select max(d1.departuredate-d1.arrivaldate) from DOCKING d1 where d1.PID=INPUT_PID);

-- Declare record to hold cursor row
r_dock_cur Docking_Cursor%ROWTYPE;
job varchar2(10);
BEGIN
job:='&j';
OPEN Docking_Cursor (job) ;
LOOP
-- Retrieve row from active set
FETCH Docking_Cursor INTO r_dock_cur;
-- Exit loop if last fetch found nothing
EXIT WHEN Docking_Cursor%NOTFOUND;
dbms_output.put_line('Question 4.b');
dbms_output.put_line('This report for pier:'||r_dock_cur.pid);
dbms_output.put_line('sid: '||r_dock_cur.sid || '| ' ||r_dock_cur.arrivaldate || '| ' || r_dock_cur.departuredate || ' | '|| TO_CHAR(r_dock_cur.departuredate - r_dock_cur.arrivaldate) || ' days');
END LOOP;
CLOSE Docking_Cursor;
END;



------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                                         ------------------- Part 5  -------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE  PROCEDURE print_ships_list(arrival IN DATE, departure IN DATE) AS
    Sname VARCHAR2(20);
    Pname VARCHAR2(20);
    CURSOR d_cursor IS
     SELECT  pid, sid, arrivaldate, departuredate FROM docking;
    details d_cursor%ROWTYPE;
BEGIN
    OPEN d_cursor;
    LOOP
         FETCH d_cursor INTO details;
           IF (details.arrivaldate =arrival AND details.departuredate> departure)or (details.arrivaldate >=arrival AND details.departuredate= departure)THEN
             SELECT  name INTO Sname FROM ships WHERE details.sid = sid;
             SELECT name INTO Pname FROM piers WHERE details.pid = pid;
             dbms_output.put_line(Pname || ' | ' || Sname || ' | ' || details.arrivaldate || ' | ' || details.departuredate);
           END IF;
    EXIT WHEN d_cursor%NOTFOUND;
    END LOOP;
    CLOSE d_cursor;
END print_ships_list;
/
---------------------------Helper Procedure -------------------------------------------------------
CREATE OR REPLACE FUNCTION count_ships(arrival IN  DATE, departure IN DATE) RETURN INTEGER
IS
    day_counter INTEGER;
    no_ships EXCEPTION;
BEGIN

    SELECT COUNT(*)  INTO day_counter FROM DOCKING WHERE (arrivaldate =arrival AND departuredate> departure)or (arrivaldate >=arrival AND departuredate= departure);

    IF day_counter = 0
    THEN RAISE no_ships;
    END IF;
    dbms_output.put_line('From: ' || arrival || 'To: ' || departure);
       print_ships_list(arrival, departure);
    RETURN day_counter;

 EXCEPTION
     WHEN no_ships THEN RETURN -2;


END;
/
---------- PROCEDURE END ----------


---------- CALLING PROCEDURE CODE BLOCK ----------




SET SERVEROUTPUT ON
accept ar_date prompt 'Arrival Date (dd/mm/yy) ';
accept dep_date prompt 'Departure DATE (dd/mm/yy)';

DECLARE
    ret_val NUMBER;
      ardate DATE:= TO_DATE('&ar_date', 'dd/mm/yy');
    depdate DATE:=TO_DATE('&dep_date', 'dd/mm/yy') ;

BEGIN
    ret_val := count_ships(ardate ,depdate);
    IF ret_val = 0 THEN
      dbms_output.put_line('There are no ships in period: ' || ardate || ' - ' || depdate);
    ELSIF ret_val = -2 THEN
      dbms_output.put_line('EXCEOTION : NO SHIPS IN THE PIERS ');
    ELSE
     dbms_output.put_line('From  ' ||ardate || ' To  ' || depdate || 'there were ' || ret_val || '  ships');
    END IF;
END;
/

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                                         ------------------- Part 6 -------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create or replace procedure findShipByName(inp_country Ships.COUNTRY%type)
as
total number;
average number;
BEGIN
total :=0;
average:=0;
for r_ship_rec in
(select d.ARRIVALDATE,d.DEPARTUREDATE,d.sid,d.pid,s.cargo_weight
    from DOCKING d, Piers p,Ships s where
             p.pid = d.pid
                        and
                            s.sid=d.sid
                                                    and
                                                           s.country=inp_country )
LOOP
 total:=total+1;
 average:=average+r_ship_rec.cargo_weight;
DBMS_OUTPUT.put_line( r_ship_rec.sid ||' | ' || r_ship_rec.pid|| ' | ' || r_ship_rec.arrivaldate || ' | ' ||r_ship_rec.departuredate);
END LOOP;
if(total>0 and average>0) then
DBMS_OUTPUT.put_line( 'Total Ships: '||total);
DBMS_OUTPUT.put_line( 'Average weight: '||(average/total));
else
DBMS_OUTPUT.put_line( 'ERROR: No Docked Ships from this country. ');
end if;
END findShipByName;
/


---------- CALLING PROCEDURE CODE BLOCK ----------
DECLARE
COUNTRY varchar2(20);

BEGIN
COUNTRY := ('&cnry');
findShipByName(COUNTRY);
END;
/




--------------------------------------------