/*     Name: Joshua Yaques
 *
 *    Panther-ID: x x x -0833
 *
 *    Course: COP 4710
 *
 *    Assignment#: 3
 *
 *    Due: Tue, Sep 24, 2020
 *
 *    I hereby certify that this work is my own and none of
 *   it is the work of any other person.
 *
 *                            Signature: Joshua Yaques
*/

CREATE DEFINER=`root`@`localhost` PROCEDURE `uspGetRepStats`(IN `CurrRep` CHAR(2), OUT `Answer` CHAR(150))


BEGIN	
	-- Local variable declaration --
	 DECLARE RepFullName char(40);			-- Reps Full Name
	 DECLARE CustomerCount char(2);			-- Number of Customers
	 DECLARE TotBalance decimal(10, 2);		-- Total Customers Balance
	 DECLARE TotOrders char(2);				-- Total Number of Orders
	 DECLARE TotRevenue decimal(10,2);		-- Total Revnue Made
	
	-- Representative Number --
	SELECT repnum
	From rep
	where repnum = CurrRep;
	
	-- Representative table check
	IF (CurrRep is NULL) THEN
		SET Answer = concat('No rep exists with the repnumber "', cast(CurrRep as char(2)), '"');
	ELSE
		-- Pulls Representatives full name --
		Select concat(firstname, ' ', lastname) into RepFullName
		FROM rep
        where repnum = CurrRep;
		-- Counts number of Customers the representative has --
		Select COUNT(*) INTO CustomerCount
		FROM customer
		where repnum = CurrRep;
		-- Sums up the balance of these customers --
		Select SUM(balance) INTO TotBalance
		FROM customer
		where repnum = CurrRep;
		-- Total number of orders --
		Select COUNT(*) INTO TotOrders
		FROM customer NATURAL JOIN orders
		WHERE repnum = CurrRep;
		-- Revenure from parts ordered --
		Select SUM(numordered * quotedprice) INTO TotRevenue
		FROM customer NATURAL JOIN orders NATURAL JOIN orderline
		WHERE repnum = CurrRep;
		SET Answer = concat('Name: ', RepFullName, '\n',
							'Number of Customers: ', CustomerCount, '\n',
							'Total balance of these customers: ', TotBalance, '\n',
							'Number of orders: ', TotOrders, '\n',
							'Total Revenue: ', TotRevenue, '\n');
	END IF;
	
	

END