/*
 * This file is released under the terms of the Artistic License.  Please see
 * the file LICENSE, included in this package, for details.
 *
 * Copyright The DBT-2 Authors
 *
 * Based on TPC-C Standard Specification Revision 5.0 Clause 2.6.2.
 */


drop procedure if exists order_status;

delimiter |


CREATE PROCEDURE order_status (in_c_id INT,
         	               in_c_w_id INT,
	                       in_c_d_id INT,
	                       in_c_last TEXT)
BEGIN
DECLARE out_c_first TEXT;
DECLARE out_c_middle char(2);
DECLARE out_c_balance NUMERIC;
DECLARE out_o_id INT;
DECLARE out_o_carrier_id INT;
DECLARE out_o_entry_d VARCHAR(28);
DECLARE out_o_ol_cnt INT;
DECLARE out_ol_supply_w_id1 INT; 
DECLARE out_ol_i_id1 INT;
DECLARE out_ol_quantity1 INT; 
DECLARE out_ol_amount1 NUMERIC;
DECLARE out_ol_delivery_d1 VARCHAR(28);
DECLARE out_ol_supply_w_id2 INT;
DECLARE out_ol_i_id2 INT;
DECLARE out_ol_quantity2 INT;
DECLARE out_ol_amount2 NUMERIC;
DECLARE out_ol_delivery_d2 VARCHAR(28);
DECLARE out_ol_supply_w_id3 INT;
DECLARE out_ol_i_id3 INT;
DECLARE out_ol_quantity3 INT;
DECLARE out_ol_amount3 NUMERIC;
DECLARE out_ol_delivery_d3 VARCHAR(28);
DECLARE out_ol_supply_w_id4 INT;
DECLARE out_ol_i_id4 INT;
DECLARE out_ol_quantity4 INT; 
DECLARE out_ol_amount4 NUMERIC;
DECLARE out_ol_delivery_d4 VARCHAR(28);
DECLARE out_ol_supply_w_id5 INT; 
DECLARE out_ol_i_id5 INT;
DECLARE out_ol_quantity5 INT; 
DECLARE out_ol_amount5 NUMERIC;
DECLARE out_ol_delivery_d5 VARCHAR(28);
DECLARE out_ol_supply_w_id6 INT; 
DECLARE out_ol_i_id6 INT;
DECLARE out_ol_quantity6 INT; 
DECLARE out_ol_amount6 NUMERIC;
DECLARE out_ol_delivery_d6 VARCHAR(28);
DECLARE out_ol_supply_w_id7 INT; 
DECLARE out_ol_i_id7 INT;
DECLARE out_ol_quantity7 INT; 
DECLARE out_ol_amount7 NUMERIC;
DECLARE out_ol_delivery_d7 VARCHAR(28);
DECLARE out_ol_supply_w_id8 INT; 
DECLARE out_ol_i_id8 INT;
DECLARE out_ol_quantity8 INT; 
DECLARE out_ol_amount8 NUMERIC;
DECLARE out_ol_delivery_d8 VARCHAR(28);
DECLARE out_ol_supply_w_id9 INT; 
DECLARE out_ol_i_id9 INT;
DECLARE out_ol_quantity9 INT; 
DECLARE out_ol_amount9 NUMERIC;
DECLARE out_ol_delivery_d9 VARCHAR(28);
DECLARE out_ol_supply_w_id10 INT; 
DECLARE out_ol_i_id10 INT;
DECLARE out_ol_quantity10 INT; 
DECLARE out_ol_amount10 NUMERIC;
DECLARE out_ol_delivery_d10 VARCHAR(28);
DECLARE out_ol_supply_w_id11 INT; 
DECLARE out_ol_i_id11 INT;
DECLARE out_ol_quantity11 INT; 
DECLARE out_ol_amount11 NUMERIC;
DECLARE out_ol_delivery_d11 VARCHAR(28);
DECLARE out_ol_supply_w_id12 INT; 
DECLARE out_ol_i_id12 INT;
DECLARE out_ol_quantity12 INT; 
DECLARE out_ol_amount12 NUMERIC;
DECLARE out_ol_delivery_d12 VARCHAR(28);
DECLARE out_ol_supply_w_id13 INT; 
DECLARE out_ol_i_id13 INT;
DECLARE out_ol_quantity13 INT; 
DECLARE out_ol_amount13 NUMERIC;
DECLARE out_ol_delivery_d13 VARCHAR(28);
DECLARE out_ol_supply_w_id14 INT; 
DECLARE out_ol_i_id14 INT;
DECLARE out_ol_quantity14 INT; 
DECLARE out_ol_amount14 NUMERIC;
DECLARE out_ol_delivery_d14 VARCHAR(28);
DECLARE out_ol_supply_w_id15 INT; 
DECLARE out_ol_i_id15 INT;
DECLARE out_ol_quantity15 INT; 
DECLARE out_ol_amount15 NUMERIC;
DECLARE out_ol_delivery_d15 VARCHAR(28);

	DECLARE out_c_id INT;
	DECLARE out_c_last VARCHAR(255);
        declare rc int default 0;

        declare c cursor for SELECT ol_i_id, ol_supply_w_id, ol_quantity, 
                                    ol_amount, ol_delivery_d
                             FROM order_line
                             WHERE ol_w_id = in_c_w_id
                               AND ol_d_id = in_c_d_id
                               AND ol_o_id = out_o_id;

        declare continue handler for sqlstate '02000' set rc = 1;

#	out_c_first VARCHAR(255);
#	out_c_middle VARCHAR(255);
#	out_c_balance NUMERIC;
#	out_o_id INT;
#	out_o_carrier_id INT;
#	out_o_entry_d VARCHAR(255);
#	out_o_ol_cnt INT;

	/*
	 * Pick a customer by searching for c_last, should pick the one in the
	 * middle, not the first one.
	 */
	IF in_c_id = 0 THEN
		SELECT c_id
		INTO out_c_id 
		FROM customer
		WHERE c_w_id = in_c_w_id
		  AND c_d_id = in_c_d_id
		  AND c_last = in_c_last
		ORDER BY c_first ASC limit 1;
	ELSE
		set out_c_id = in_c_id;
	END IF;

	SELECT c_first, c_middle, c_last, c_balance
	INTO out_c_first, out_c_middle, out_c_last, out_c_balance
	FROM customer
	WHERE c_w_id = in_c_w_id   
	  AND c_d_id = in_c_d_id
	  AND c_id = out_c_id;

	SELECT o_id, o_carrier_id, o_entry_d, o_ol_cnt
	INTO out_o_id, out_o_carrier_id, out_o_entry_d, out_o_ol_cnt
	FROM orders
	WHERE o_w_id = in_c_w_id
  	AND o_d_id = in_c_d_id
  	AND o_c_id = out_c_id
	ORDER BY o_id DESC limit 1;

        open c;

        fetch_block:
        BEGIN
         fetch c into out_ol_i_id1, out_ol_supply_w_id1, out_ol_quantity1, 
                      out_ol_amount1, out_ol_delivery_d1;
         if rc then
            leave fetch_block;
         end if;
         fetch c into out_ol_i_id2, out_ol_supply_w_id2, out_ol_quantity2, 
                      out_ol_amount2, out_ol_delivery_d2;
         if rc then
            leave fetch_block;
         end if;
         fetch c into out_ol_i_id3, out_ol_supply_w_id3, out_ol_quantity3, 
                      out_ol_amount3, out_ol_delivery_d3;
         if rc then
            leave fetch_block;
         end if;
         fetch c into out_ol_i_id4, out_ol_supply_w_id4, out_ol_quantity4, 
                      out_ol_amount4, out_ol_delivery_d4;
         if rc then
            leave fetch_block;
         end if;
         fetch c into out_ol_i_id5, out_ol_supply_w_id5, out_ol_quantity5, 
                      out_ol_amount5, out_ol_delivery_d5;
         if rc then
            leave fetch_block;
         end if;
         fetch c into out_ol_i_id6, out_ol_supply_w_id6, out_ol_quantity6, 
                      out_ol_amount6, out_ol_delivery_d6;
         if rc then
            leave fetch_block;
         end if;
         fetch c into out_ol_i_id7, out_ol_supply_w_id7, out_ol_quantity7, 
                      out_ol_amount7, out_ol_delivery_d7;
         if rc then
            leave fetch_block;
         end if;
         fetch c into out_ol_i_id8, out_ol_supply_w_id8, out_ol_quantity8, 
                      out_ol_amount8, out_ol_delivery_d8;
         if rc then
            leave fetch_block;
         end if;
         fetch c into out_ol_i_id9, out_ol_supply_w_id9, out_ol_quantity9, 
                      out_ol_amount9, out_ol_delivery_d9;
         if rc then
            leave fetch_block;
         end if;
         fetch c into out_ol_i_id10, out_ol_supply_w_id10, out_ol_quantity10, 
                      out_ol_amount10, out_ol_delivery_d10;
         if rc then
            leave fetch_block;
         end if;
         fetch c into out_ol_i_id11, out_ol_supply_w_id11, out_ol_quantity11, 
                      out_ol_amount11, out_ol_delivery_d11;
         if rc then
            leave fetch_block;
         end if;
         fetch c into out_ol_i_id12, out_ol_supply_w_id12, out_ol_quantity12, 
                      out_ol_amount12, out_ol_delivery_d12;
         if rc then
            leave fetch_block;
         end if;
         fetch c into out_ol_i_id13, out_ol_supply_w_id13, out_ol_quantity13, 
                      out_ol_amount13, out_ol_delivery_d13;
         if rc then
            leave fetch_block;
         end if;
         fetch c into out_ol_i_id14, out_ol_supply_w_id14, out_ol_quantity14, 
                      out_ol_amount14, out_ol_delivery_d14;
         if rc then
            leave fetch_block;
         end if;
         fetch c into out_ol_i_id15, out_ol_supply_w_id15, out_ol_quantity15, 
                      out_ol_amount15, out_ol_delivery_d15;
       end fetch_block;

       close c;

END|

delimiter ;
