/*Name: Shirley Cabrera

     Panther-ID:6119474

     Course: COP 4710 

     Assignment#: 3

     Due: Thu, Sep 24, 2020

     I hereby certify that this work is my own and none of
     it is the work of any other person.
	 
	 Shirley Cabrera*/

CREATE DEFINER=`root`@`localhost` PROCEDURE `uspGetRepStas`(
	-- parameters for the stored procedure uspGetRepStas
	IN repNumber CHAR(2) )
	
BEGIN
	-- Local variables
	DECLARE currRep CHAR(30);
	DECLARE totalCustomers INT DEFAULT 0;
	DECLARE totalBalance DECIMAL (8, 2);
	DECLARE totalNumbOrders DECIMAL (3, 0);
	DECLARE totalRevenue DECIMAL(6, 2);
    
    -- Statements for the procedure 
    SELECT concat(firstname, ' ', lastname) INTO currRep
    FROM rep
    WHERE repNum = repNumber;
    
    IF (currRep is Null) THEN
    	SELECT concat('No rep exists with the repNumber ', repNumber) As ErrorMessage; /*retturn error message if the repNumber is not found in the table rep*/
    ELSE 
		SELECT CONCAT('Statistics of the sales rep ', repNumber) as Answer;
		
		SELECT CONCAT('Name: ', currRep) as ' '; /*return the name of the rep with the repNumber*/
		
        /*return the total customer for thie rep*/
		SELECT  count(*) INTO totalCustomers
		FROM customer 
		WHERE repnum = repNumber;
		SELECT concat('Number of Customers: ', totalCustomers) as ' ';
        
		/*return the totalBalance of all the customers for the repNumber*/
		SELECT  sum(balance) INTO totalBalance 
		FROM customer
		WHERE repnum = repNumber;
		SELECT concat('Total Balance of this Customers: ', totalBalance) as ' ';
		
        /*return the number of orders placed by all these customers.*/
		SELECT count(*) INTO totalNumbOrders
		FROM orders, customer 
		WHERE orders.customernum = customer.customernum AND customer.customernum IN 
			(SELECT customerNum
			FROM customer 
			WHERE customer.repnum = repNumber);
		SELECT concat('Number of orders: ', totalNumbOrders) as ' ';
		
		/*return the total revenue of the orders placed by all these customers.*/
		SELECT sum(numordered* quotedPrice) INTO totalRevenue
		FROM orderline
		WHERE ordernum in
			(SELECT orderNum 
			FROM orders, customer 
			WHERE orders.customernum = customer.customernum AND customer.customernum IN 
				(SELECT customerNum
				FROM customer 
				WHERE customer.repnum = repNumber));
		SELECT concat('Total revenue: ', totalRevenue) as ' ';
   END IF;
   END