A3.3 QACS ADD_SALE Procedure

Complete the stored procedure in the QACS database named ADD_SALE(CustomerID, EmployeeID, SaleDate, ItemID, TaxRate, SaleID, SubTotal, Total, Err, Msg) below that will add an item ItemID to a sale, returning an updated SubTotal for the sale.

For a new sale, the CustomerID, EmployeeID, SaleDate, ItemID, and TaxRate must be specified. A new record is inserted into the SALE table. Then the item is added to the SALE_ITEM table. The SaleID of the new record is returned, along with the SubTotal, Total, Err and Msg fields. 

When ADD_SALE is used to add a sale item to an existing sale, only the SaleID, ItemID and TaxRate must be specified. CustomerID, EmployeeID and SaleDate are set to zero. The item is added to the SALE_ITEM table. Then the ItemPrice fields of all the SALE_ITEM records for that SaleID are subtotalled, the tax and total for the sale is computed and the SALE record is updated. The SubTotal, Total, Err and Msg fields are returned.

When an error occurs trying to insert a record into the SALE table (because the CustomerID or EmployeeID doesn't exist), set the error code to 99. When there's an error inserting into the SALE_ITEM table because the SaleID doesn't exist) set the error code to 44. When there's an error adding an item to the SALE_ITEM table (ecause the ItemID doesn't exist) set the error code to 66.

The following implements the ADD_SALE procedure, except for the missing sections code shown below.

DELIMITER $$
CREATE PROCEDURE `ADD_SALE`(IN `customer_id` INT, IN `employee_id` INT, IN `sale_date` DATE, IN `item_id` INT, IN `tax_rate` DECIMAL(6,3),
 INOUT `sale_id` INT, OUT `sub_total` DECIMAL(8,2), OUT `total` DECIMAL(8,2), OUT `err` INT, OUT `msg` VARCHAR(500))
    NO SQL
BEGIN
/* customer_id, employee_id, sale_date, item_id, tax_rate, sale_id, sub_total, total, err, msg */
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET msg=CONCAT(msg,' (c=',customer_id,',e=',employee_id,',d=',sale_date,',s=',sale_id,',i=',item_id,')');
        SET msg=CONCAT(msg, ' ROLL BACK - new invoice not started');
    END;
    SET err = 0;
    SET msg = '';
    IF(customer_id>0 AND employee_id>0) THEN
        SET err = 0;  /* replace err = 0 and msg = 'error message' */
        SET msg = 'error message';
/* insert new sale into SALE table */    
        
/* retrieve auto-incremented SaleID field after insert*/    
        SET sale_id = (SELECT 0); /* replace SELECT 0 */
        SET err = 0;
        SET msg = '';    
    END IF;
/* get item price from ITEM table */
    SET @itemPrice = (SELECT 0); /* replace SELECT 0 */
    IF(@itemPrice IS NULL) THEN
        SET err = 0;  /* replace err = 0 and msg = 'error message' */
        SET msg = 'error message';
    ELSE
        SET err = 0;  /* replace err = 0 and msg = 'error message' */
        SET msg = 'error message';
/* insert item into SALE_ITEM table */
        
        SET err = 0;
        SET msg = '';
        SET sub_total = (SELECT 0); /* replace SELECT 0 */
        SET @tax = sub_total * tax_rate / 100;
        SET total = sub_total + @tax;
/* update sub_total, tax and total in SALE record  */
        
        SET msg = CONCAT(msg,' Sale: ',sale_id,' SubTotal: ',FORMAT(sub_total,2),', Tax: ',FORMAT(@tax,3),', Total: ',FORMAT(total,2),' updated.');
    END IF;
END$$
DELIMITER ;

Fill in the following snippets of code above to get the code to work.

SELECT ItemPrice FROM ITEM WHERE ItemID=item_id
SELECT LAST_INSERT_ID()
SELECT SUM(ItemPrice) FROM SALE_ITEM WHERE SaleID=sale_id

INSERT INTO SALE (CustomerID, EmployeeID, SaleDate) VALUES (customer_id,employee_id,sale_date);
INSERT INTO SALE_ITEM (SaleID, ItemID, ItemPrice) VALUES (sale_id,item_id,@itemPrice);

UPDATE SALE SET SubTotal=sub_total,Tax=@tax, Total=total WHERE SaleID=sale_id;

SET err = 44;
SET msg = CONCAT('Error 44 SaleID ',sale_id,' does not exist.');

SET err = 66;
SET msg = CONCAT('Error 66 Item ',item_id,' does not exist.');

SET err = 99;
SET msg = CONCAT('Error 99 inserting SALE. CustomerID: ',customer_id,' or EmployeeID: ',employee_id,' does not exist.');

FINALLY, you will notice that Total is not returned. Fix the code so Total is returned along with SubTotal. (See the video.)