/************************************************************************
*   Database II	                 				        *	             
*   Spring 2000						                *    		 
*   Home Work 1			                     		        *		 
*                                       				*            
*   Write a PL/SQL procedure that takes as an argument the rate_per_day *
*   for a car then finds the number of cars that have a rate per day    *
*   less than  the passed value. The procedure stores the result in a   *
*   numbers_table, which you must create. The numbers_table table has   *
*   two columns rate_per_day and no_of_cars.                            *
/************************************************************************/




CREATE    PROCEDURE get_no_of_cars(
	rate_in IN  cargroups.rate_per_day%TYPE) IS
   v_rate_per_day NUMBER(5,2) := 10;
    v_no_of_cars NUMBER(5) := 0;
    BEGIN
	SELECT COUNT(C.registration)
	INTO v_no_of_cars
	FROM cars C, cargroups CG
	WHERE C.car_group_name = CG.car_group_name
	AND CG.rate_per_day < rate_in;

	INSERT INTO numbers_table(rate_per_day, no_of_cars)
	VALUES(rate_in, v_no_of_cars);
    END;
/



/*************************************************************************
/*   Database II                                                         *
/*   Spring 2000                                                         *
/*   Home Work 2.1                                                       *
/*									 *
/************************************************************************/  
DECLARE

v_rate_per_day cargroups.rate_per_day%TYPE := 10;
v_no_of_cars NUMBER(3);

BEGIN
    WHILE v_rate_per_day <= 100 LOOP
	SELECT COUNT (DISTINCT cars.registration)
	INTO v_no_of_cars
	FROM cars, cargroups cg
	WHERE cg.rate_per_day < v_rate_per_day 
	AND cars.car_group_name = cg.car_group_name;

	INSERT INTO numbers_table
	VALUES (v_rate_per_day, v_no_of_cars);

	v_rate_per_day := v_rate_per_day + 10;
    END LOOP;

END;
/

/**********************************************************                                          
*   Database II                                           *
*   Spring 2000                                           *
*   Home Work 2.2                                         *
*                                                         *
***********************************************************/   

DECLARE
    v_no_of_days NUMBER(3);

 PROCEDURE del_cust( no_of_days IN NUMBER ) IS
BEGIN
	DELETE
	FROM customer cust
	WHERE cust.cust_no IN 
	      (SELECT cust_no
	       FROM bookings
	       WHERE (SYSDATE - date_reserved) >= no_of_days);
   
END;

BEGIN
     v_no_of_days := 90; 
     del_cust(v_no_of_days);

END;


/*************************************************************************
/*   Database II                                                         *
/*   Spring 2000                                                         *
/*   Home Work 2.3                                                       *
/*                                                                 	 *
/************************************************************************/

DECLARE 
 v_car_group_name cars.car_group_name%TYPE; 
 v_model_name models.model_name%TYPE;
 v_description models.description%TYPE;
 v_cost  number;
CURSOR	cur_maxcost IS
 SELECT DISTINCT m.model_name, c.car_group_name, c.cost, m.description 
 FROM cars c, models m
 WHERE m.model_name = c.model_name
 ORDER BY c.cost DESC;

BEGIN
  OPEN cur_maxcost;
  LOOP
   FETCH cur_maxcost
   INTO v_model_name, v_car_group_name, v_cost, v_description;
   EXIT  when  (cur_maxcost%NOTFOUND)
   OR (cur_maxcost%ROWCOUNT > 1);

  INSERT INTO maxcosts (model_name, car_group_name, cost, description)
  VALUES (v_model_name, v_car_group_name, v_cost, v_description);

 END LOOP

 COMMIT;
 CLOSE cur_maxcost;

END;
/

/*************************************************************************
/*   Database II                                                         *
/*   Spring 2000                                                         *
/*   Home Work 3                                                         *
/*                                                                       *
/************************************************************************/
                                                                            
DECLARE
    CURSOR c1 IS
	SELECT cust_name, date_rent_start, amount_due
	FROM customer, bookings
	WHERE (SYSDATE - date_rent_start) >= 90
	AND amount_due >= 200
	AND paid = 'N'
	AND bookings.cust_no = customer.cust_no;

    c1_rec	   c1%ROWTYPE;  
    v_no_of_days   NUMBER;
    v_severity     NUMBER(1);

BEGIN
    IF NOT c1%ISOPEN THEN
	OPEN c1;
    END IF;

    FETCH c1 INTO c1_rec;
    WHILE c1%FOUND LOOP
	v_no_of_days := SYSDATE - c1_rec.date_rent_start;
	IF (v_no_of_days >= 365) THEN
	   v_severity := 3;
	ELSIF (v_no_of_days > 180) OR (c1_rec.amount_due > 500) THEN
	   v_severity := 2;
	ELSE
	   v_severity := 1;
	END IF;

    INSERT INTO oldbills(cust_name, date_rent_start, amount_due, severity)
    VALUES (c1_rec.cust_name, c1_rec.date_rent_start, 
	c1_rec.amount_due, v_severity); 
    
    FETCH c1 INTO c1_rec;
    END LOOP;
    CLOSE c1;

END;
/

/*************************************************************************
/*                                                                       *
/*   Database II                                                         *
/*   Spring 2000                                                         *
/*   Home Work 4                                                         *
/*                                                                       *
/************************************************************************/
                                                                             
DECLARE
    CURSOR c1 IS
	SELECT registration, model_name
	FROM cars;

    c1_rec	   c1%ROWTYPE;  
    v_ave_period   NUMBER;
    v_ave_amt      NUMBER;
    status	   BOOLEAN;

    PROCEDURE get_averages(
	reg_in       IN  averages.registration%TYPE,
	ave_period   OUT averages.ave_period%TYPE,
	ave_amt      OUT averages.ave_amt%TYPE,
	status       OUT BOOLEAN)   IS
    BEGIN
	SELECT AVG(rental_period), AVG(amount_due)
	INTO ave_period, ave_amt
	FROM bookings
	WHERE bookings.registration = reg_in
	GROUP BY registration;	
	status := true;

    EXCEPTION
	WHEN NO_DATA_FOUND THEN
	    status := false;
    END;

BEGIN
    FOR c1_rec IN c1 LOOP    
	get_averages(c1_rec.registration, v_ave_period, v_ave_amt, status);

	IF (status) THEN
	   NULL;
	ELSE
	   v_ave_period := 0;
	   v_ave_amt := 0;
	END IF;

	INSERT INTO averages
	   (registration, model_name, ave_period, ave_amt)
	VALUES (c1_rec.registration, c1_rec.model_name, v_ave_period, v_ave_amt); 
    
    END LOOP;
END;
/

/************************************************************************
/*                                                                      *
/*   Database II                                                        *
/*   Spring 2000                                                        *
/*   Home Work 5                                                        *
/*                                                                      *
/************************************************************************/
                                                                             
CREATE OR REPLACE TRIGGER check_severity
BEFORE INSERT ON bookings
FOR EACH ROW
DECLARE
    v_cname       customers.cust_name%TYPE;
    v_startdate   bookings.date_rent_start%TYPE;
    v_amtdue      bookings.amount_due%TYPE;
    v_no_of_days  NUMBER;
    v_severity    NUMBER(1);
    attained_severity EXCEPTION;	
BEGIN
    DBMS_OUTPUT.ENABLE;
    SELECT cust_name, date_rent_start, amount_due
    INTO v_cname, v_startdate, v_amtdue
    FROM customers, bookings  
    WHERE bookings.cust_no = :new.cust_no
    AND (SYSDATE - date_rent_start) >= 90
    AND amount_due >= 200
    AND paid = 'N'
    AND bookings.cust_no = customers.cust_no;
    
   
     
	v_no_of_days := SYSDATE - v_startdate;

	IF (v_no_of_days >= 365) THEN
	   v_severity := 3;
	ELSIF (v_no_of_days > 180) OR (v_amtdue > 500) THEN
	   v_severity := 2;
	ELSE
	   v_severity := 1;
	END IF;

	RAISE attained_severity;
        

EXCEPTION
    WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('no data');
    WHEN attained_severity THEN 
	DBMS_OUTPUT.PUT_LINE('Customer: ' || v_cname ||
			     ' has a severity level ' || v_severity);
	RAISE_APPLICATION_ERROR(-20001, 'CANNOT BOOK A CAR');
     WHEN OTHERS THEN NULL; 
    
END;
/