/* Full back-up for Week 11 Homework including base tables */

	/* Drop base tables in correct order */
	
	Drop Table cs362djc.Orders;
	Drop Table cs362djc.Items;
	Drop Table cs362djc.ItemCat;
	Drop Table cs362djc.CustMast;
	
	
	/* Recreate Tables */
	
	
	/*Customer Master Entity/Table: CUSTMAST in Schema CS362DJC*/

CREATE TABLE CS362DJC.CUSTMAST ( 
	CUSTID DECIMAL(9, 0) NOT NULL DEFAULT 0 , 
	CUSTNAME CHAR(30)  NOT NULL DEFAULT '' , 
	CUSTADDR CHAR(30)  NOT NULL DEFAULT '' , 
	CUSTCITY CHAR(30)  NOT NULL DEFAULT '' , 
	CUSTZIP CHAR(10)  NOT NULL DEFAULT '' , 
	CUSTST VARCHAR(2)  NOT NULL DEFAULT '' , 
	CUSTDLC DATE NOT NULL DEFAULT CURRENT_DATE , 
	CUSTTLC TIME NOT NULL DEFAULT CURRENT_TIME , 
	CUSTDTC DATE NOT NULL DEFAULT CURRENT_DATE , 
	CUSTULC CHAR(18)  NOT NULL DEFAULT USER , 
	DOLLARS DECIMAL(11, 2) NOT NULL DEFAULT 0.00 , 
	CONSTRAINT CS362DJC.CUSTMASTPK PRIMARY KEY( CUSTID ), 
	CONSTRAINT CS362DJC.STATEMUSTEXISTFK FOREIGN KEY (CUSTST) REFERENCES CS362DJC.STATES (STabbr) 
	ON DELETE RESTRICT 
	ON UPDATE RESTRICT); 
  
LABEL ON TABLE CS362DJC.CUSTMAST 
	IS 'Customer Master List' ; 
  
LABEL ON COLUMN CS362DJC.CUSTMAST 
( CUSTID TEXT IS 'Customer ID number' , 
	CUSTNAME TEXT IS 'Customer Name' , 
	CUSTADDR TEXT IS 'Address Line 1' , 
	CUSTCITY TEXT IS 'Customer City' , 
	CUSTZIP TEXT IS 'Customer Zip code' , 
	CUSTST TEXT IS 'Customer State' , 
	CUSTDLC TEXT IS 'Date Last Changed' , 
	CUSTTLC TEXT IS 'Time of Last Change to this record' , 
	CUSTDTC TEXT IS 'Date Created' , 
	CUSTULC TEXT IS 'Username of person entering the record' , 
	DOLLARS TEXT IS 'Dollars owed to us' ) ; 
	
	/* Seed table with a record */
	
	insert into cs362djc.custmast
	(CUSTID, CUSTNAME, CUSTADDR, CUSTCITY, CUSTST, CUSTZIP)
	values
	(1, 'Dana Clark', '15 Douglass St', 'Keene', 'NH', '03431');
	
	
	/*Item Catalog*/
	CREATE TABLE CS362DJC.ItemCat
	(	ItmCatNum DECIMAL(9,0) NOT NULL DEFAULT 0,
		ItmCatName VARCHAR(30) NOT NULL DEFAULT ' ',
		ItmCatDesc VARCHAR(200) NOT NULL DEFAULT ' ',
		ItmCatQOH DECIMAL(7,0) NOT NULL DEFAULT 0,
		ItmCatPrice DECIMAL(9,2) NOT NULL DEFAULT 0.00,
		CONSTRAINT cs362djc.ItemCatPK Primary Key (ItmCatNum)
	);
	
	LABEL on TABLE CS362DJC.ItemCat is 'Item Master List/catalogue';
	
	LABEL on COLUMN CS362DJC.ItemCat 
	(	ItmCatNum TEXT is 'Item Number',
		ItmCatName TEXT is 'Item Name',
		ItmCatDesc TEXT is 'Item Description',
		ItmCatQOH TEXT is 'Quantity On Hand',
		ItmCatPrice TEXT is 'Current Price of Item'
	);
	
	/* Seed table with a record */
	
	insert into cs362djc.ItemCat
	(ItmCatNum, ItmCatName, ItmCatDesc, ItmCatQOH, ItmCatPrice)
	values
	(1, 'Light Almond Oil', 'Light oil good for cooking and dressings.', 5, 9.95);
	
	/*Orders Entity*/
	CREATE TABLE CS362DJC.ORDERS 
	( 
	/*OrderNum DECIMAL(9,0) NOT NULL DEFAULT 0,*/
	OrderNum INTEGER GENERATED ALWAYS AS IDENTITY (Start with 10, Increment by 10, order, no cycle, no minvalue, no maxvalue, cache 20) not hidden,
	OCustID DECIMAL(9, 0) NOT NULL DEFAULT 0 , 
	OShipADDR1 CHAR(30)  NOT NULL DEFAULT '' , 
	OShipADDR2 CHAR(30)  NOT NULL DEFAULT '' , 
	OShipCITY CHAR(30)  NOT NULL DEFAULT '' , 
	OShipZIP CHAR(10)  NOT NULL DEFAULT '' , 
	OShipST VARCHAR(2)  NOT NULL DEFAULT '' , 
	OrdDATE DATE NOT NULL DEFAULT CURRENT_DATE,
	OUser CHAR(18)  NOT NULL DEFAULT USER , 
	Dollars DECIMAL(11, 2) NOT NULL DEFAULT 0.00 , 
	CONSTRAINT CS362DJC.ORDERNUMPK PRIMARY KEY( ORDERNUM ),
	CONSTRAINT CS362DJC.OCUSTIDFK FOREIGN KEY (OCUSTID) REFERENCES CS362DJC.CUSTMAST (CUSTID) 
	ON DELETE RESTRICT 
	ON UPDATE RESTRICT,
	CONSTRAINT CS362DJC.OSTATEMUSTEXISTFK FOREIGN KEY (OSHIPST) REFERENCES CS362DJC.STATES (STabbr) 
	ON DELETE RESTRICT
	ON UPDATE RESTRICT
	); 
	
	LABEL ON COLUMN CS362DJC.ORDERS 
	(
	ORDERNUM TEXT IS 'Order number' , 
	OCUSTID TEXT IS 'ID number of Customer the order is for' , 
	OSHIPADDR1 TEXT IS 'Shipping Address Line 1' , 
	OSHIPADDR2 TEXT IS 'Shipping ADdress Line 2',
	OSHIPCITY TEXT IS 'Shipping City' , 
	OSHIPZIP TEXT IS 'Shipping Address Zip code' , 
	OSHIPST TEXT IS 'Shipping Address State' , 
	ORDDATE TEXT IS 'Date Order Created' , 
	OUSER TEXT IS 'User who created order',
	DOLLARS TEXT IS 'Total Dollar value of this order' 
	) ; 
	 /* Seed table with a record */
	 insert into cs362djc.orders
	( OCustID, OShipST)
	values
	 (1, 'NH');
	 
	 
	/*Items Entity / Order Detail */
	CREATE TABLE CS362DJC.ITEMS
	(
	ITMORDERNUM INTEGER NOT NULL DEFAULT 0,
	ItmEntryNum Decimal (9,0) not null default 0,
	ITEMNUM DECIMAL (9,0) NOT NULL DEFAULT 0,
	ITEMDESC CHAR(30) NOT NULL DEFAULT '',
	ITMQTY DECIMAL (9,0) NOT NULL DEFAULT 0,
	ITMDATEDUE DATE NOT NULL DEFAULT CURRENT_DATE ,
	ITMCOST DECIMAL (9, 2) NOT NULL DEFAULT 0,
	CONSTRAINT CS362DJC.ITEMPK PRIMARY KEY (ITMORDERNUM, ItmEntryNum),
	CONSTRAINT CS362DJC.ITMORDNUMFK FOREIGN KEY (ITMORDERNUM) REFERENCES CS362DJC.ORDERS (ORDERNUM),
	CONSTRAINT CS362DJC.ItmCatFK FOREIGN KEY (ITEMNUM) REFERENCES CS362DJC.ItemCat (ItmCatNum)
	ON DELETE RESTRICT
	ON UPDATE RESTRICT
	);
	
	LABEL on table cs362djc.items is 'Order detail list';
	
	LABEL ON COLUMN CS362DJC.ITEMS
	(
	ITMORDERNUM TEXT IS 'Coresponding Order Number',
	ITEMNUM TEXT IS 'Item number',
	ITEMDESC TEXT IS 'Item description',
	ITMQTY TEXT IS 'Quantity ordred',
	ITMDATEDUE TEXT IS 'Date Item promised to customer',
	ITMCOST TEXT IS 'Cost per Item');
	
	/* Seed table with a record */
	insert into cs362djc.items
	(itmordernum, itmEntryNum, itmqty, itemnum)
	values
	(10, 1, 5, 1);
	

/******************************************************************/
/* Object:   ItmDtaGen                                               */
/* Author:   Dana Clark                                            */
/* Date:     04/07/2009                                            */
/* Function: Series of SQL commands that creates a stored          */
/*           procedure and runs it. Also does several other        */
/*           SQL commands in a string                              */
/******************************************************************/ 

drop procedure cs362djc.ItmDtaGen;

CREATE PROCEDURE
   CS362DJC.ItmDtaGen 	(  IN numberItems  	Dec(5,0) )
   
 LANGUAGE SQL
 BEGIN
 /* *****declare variables***** */
 
 /* Table Data Variables */
	/*  ***CS362djc.itemcat*** */
	declare ItmCatNum DECIMAL(9,0) ;
	declare ItmCatName VARCHAR(30) ;
	declare ItmCatDesc VARCHAR(200) ;
	declare ItmCatQOH DECIMAL(7,0) ;
	declare ItmCatPrice DECIMAL(9,2) ;
	
 /* data fragment variables */
 
	declare itemName1 char(10);
	declare itemName2 char (15);
	
	declare itemDesc1 char(95);
	declare itemDesc2 char(95);
	
	
 /*utility variables*/
 DECLARE itemCount   	decimal(9,0);
 DECLARE SQLStmt     	char(2000);
 DECLare rowCount		decimal(9,0);
 DECLARE randNum   	integer;
 
 /* loop for the number of times passed into the procedure and build variables */

SET itemCount = 0;
SET ItmCatNum =  (select MAX(cs362djc.itemcat.ItmCatNum) from cs362djc.itemcat);

Label1:
WHILE itemCount < numberItems DO
	SET itemcount = itemcount  + 1;
	SET ItmCatNum = ItmCatNum +10;
	
	set rowCount = (select count(*) from cs362djc.DummyItems);
	
	set randNum = integer((rand()*rowCount)+1);
	set ItemName1 = (select firstPart from cs362djc.DummyItems where rrn(cs362djc.DummyItems) = randNum);
	
	set randNum = integer((rand()*rowCount)+1);
	set ItemName2 = (select lastPart from cs362djc.DummyItems where rrn(cs362djc.DummyItems) = randNum);
	
	set randNum = integer((rand()*rowCount)+1);
	set ItemDesc1 = (select ItemDesc1 from cs362djc.DummyItems where rrn(cs362djc.DummyItems) = randNum);
	
	set randNum = integer((rand()*rowCount)+1);
	set ItemDesc2 = (select ItemDesc2 from cs362djc.DummyItems where rrn(cs362djc.DummyItems) = randNum);
	
	set ItmCatName = trim(ItemName1) || ' '|| trim(ItemName2);
	set ItmCatDesc = trim(ItemDesc1) || ' ' || trim(ItemDesc2);
	
	
	/* set price */
	set ItmCatPrice = (rand()*1000)* (rand());
	
	/* set QuantityOnHand */
	set ItmCatQOH = integer (rand()*1000);
	
	/* build the insert statement */

	set SQLStmt = 'INSERT INTO cs362djc.ItemCat (ItmCatNum, ItmCatName, ItmCatDesc, ItmCatPrice, ItmCatQOH)
	VALUES ( ' || ItmCatNum || ' ,''' ||  ItmCatName ||''' ,''' || ItmCatDesc || ''', ' || ItmCatPrice|| ',' || ItmCatQOH || ' )' ;
	execute immediate SQLStmt;
	
	
	END WHILE Label1;
END;

/* run procedure and test */
CALL cs362djc.ItmDtaGen(100);

select * from cs362djc.ItemCat;	
	 
	 
	 
	 
	 
/******************************************************************/
/* Object:   CustDtaGen                                               */
/* Author:   Dana Clark                                            */
/* Date:     04/07/2009                                            */
/* Function: Series of SQL commands that creates a stored          */
/*           procedure and runs it. Also does several other        */
/*           SQL commands in a string                              */
/******************************************************************/

/* delete the stored procedure if it already exists */

 Drop procedure cs362djc.CustDtaGen;

/* create a procedure to do some useful work. Procedure has */
/* ONE input parameter signified by the word IN, is written in SQL, declares some   */
/* variables                                                */



CREATE PROCEDURE
   CS362DJC.CustDtaGen 	(  IN numberCustomers  	Dec(5,0) )
   
 LANGUAGE SQL
 BEGIN
 /* *****declare variables***** */
 
 /* Table Data Variables */
	/* ***CS362djc.custmast*** */
	DECLARE CUSTID 	DECIMAL(9, 0) ;
	DECLARE CUSTNAME 	CHAR(30) ;
	DECLARE CUSTADDR 	CHAR(30) ;
	DECLARE CUSTCITY 	CHAR(30) ;
	DECLARE CUSTZIP 	CHAR(10) ;
	DECLARE CUSTST 	VARCHAR(2) ; 
	
	
	
 /* data fragment variables */
 
	declare firstName char(10);
	declare midInit	char (2);
	declare lastName char (15);
	
	declare addrNum char(5);
	declare addrName char(15);
	declare addrST char (6);
	
	declare zip1 char(1);
	declare zip2 char(1);
	declare zip3 char(1);
	declare zip4 char(1);
	declare zip5 char(1);
	
 /*utility variables*/
 DECLARE custCount   	decimal(9,0);
 DECLARE itemCatCount   decimal (9,0);
 DECLARE SQLStmt     	char(2000);
 
DECLARE randNum   	integer;
 
 /* loop for the number of times passed into the procedure and build variables */

SET custCount = 0;
SET custID =  (select MAX(cs362djc.custmast.custID) from cs362djc.custmast);

CustMast:
WHILE custcount < numberCustomers DO
	SET custcount = custcount  + 1;
	SET custID = custID +10;
	
	SET randNum = integer(rand()*30);

	case randNum
			when 0 then set  firstName = 'Dana' ;
			when 1 then set  firstName = 'Anne Marie';
			when 2 then set  firstName = 'Julianita' ;
			when 3 then set  firstName = 'Richard';
			when 4 then set  firstName = 'Toby';
			when 5 then set  firstName = 'Irma';
			when 6 then set  firstName = 'Adriana';
			when 7 then set  firstName = 'Anne';
			when 8 then set  firstName = 'Emboo';
			when 9 then set  firstName = 'Emma';
			when 10 then set firstName = 'Ten';
			when 11 then set firstName = 'Adolph';
			when 12 then set firstName = 'Randolph';
			when 13 then set firstName = 'Robert';
			when 14 then set firstName = 'Chumba';
			when 15 then set firstName = 'Alma';
			when 16 then set firstName = 'Ada';
			when 17 then set firstName = 'Arthur';
			when 18 then set firstName = 'Chubba';
			when 19 then set firstName = 'Phillip';
			when 20 then set firstName = 'Humphrey';
			when 21 then set firstName = 'Lewis';
			when 22 then set firstName = 'Monkey';
			when 23 then set firstName = 'Clamps';
			when 24 then set firstName = 'Ricky';
			when 25 then set firstName = 'Julian';
			when 26 then set firstName = 'Leila';
			when 27 then set firstName = 'Amy';
			when 28 then set firstName = 'Boogers';
			when 29 then set firstName = 'Spooky';
			when 30 then set firstName = 'Bonio';
			ELSE set firstName = 'Elsie'|| char(randNum);
	END CASE;

	/*( Middle Initial */
	
	SET randNum = integer(rand()*26);
	case randNum
			when 0 then set  midInit = 'A.' ;
			when 1 then set  midInit = 'B.';
			when 2 then set  midInit = 'C.' ;
			when 3 then set  midInit = 'D.';
			when 4 then set  midInit = 'E.';
			when 5 then set  midInit = 'F.';
			when 6 then set  midInit = 'G.';
			when 7 then set  midInit = 'H.';
			when 8 then set  midInit = 'I.';
			when 9 then set  midInit = 'J.';
			when 10 then set midInit = 'K.';
			when 11 then set midInit = 'L.';
			when 12 then set midInit = 'M.';
			when 13 then set midInit = 'N.';
			when 14 then set midInit = 'O.';
			when 15 then set midInit = 'P.';
			when 16 then set midInit = 'Q.';
			when 17 then set midInit = 'R.';
			when 18 then set midInit = 'S.';
			when 19 then set midInit = 'T.';
			when 20 then set midInit = 'U.';
			when 21 then set midInit = 'V.';
			when 22 then set midInit = 'W.';
			when 23 then set midInit = 'X.';
			when 24 then set midInit = 'Y.';
			when 25 then set midInit = 'Z.';
			
			ELSE set midInit = '7.';
	end case;
	/* Last Name */
	
	SET randNum = integer(rand()*30);

	case randNum
			when 0 then set  lastName = 'Clark' ;
			when 1 then set  lastName = 'Marie';
			when 2 then set  lastName = 'Bonita' ;
			when 3 then set  lastName = 'Jemima';
			when 4 then set  lastName = 'Frye';
			when 5 then set  lastName = 'Bender';
			when 6 then set  lastName = 'X';
			when 7 then set  lastName = 'Akimbo';
			when 8 then set  lastName = 'Eskimo';
			when 9 then set  lastName = 'Demma';
			when 10 then set lastName = 'Ten';
			when 11 then set lastName = 'Skipper';
			when 12 then set lastName = 'Whistler';
			when 13 then set lastName = 'daFoe';
			when 14 then set lastName = 'Bungee';
			when 15 then set lastName = 'Townsend';
			when 16 then set lastName = 'Vonnegut';
			when 17 then set lastName = 'Bukowski';
			when 18 then set lastName = 'Salinger';
			when 19 then set lastName = 'von Purl';
			when 20 then set lastName = 'Hawkings';
			when 21 then set lastName = 'Carrol';
			when 22 then set lastName = 'Chubba';
			when 23 then set lastName = 'Clamps';
			when 24 then set lastName = 'Leahey';
			when 25 then set lastName = 'Orlando';
			when 26 then set lastName = 'Smelzar';
			when 27 then set lastName = 'Wong';
			when 28 then set lastName = 'McKenzie';
			when 29 then set lastName = 'Olefactory';
			when 30 then set lastName = 'Freely';
			ELSE set lastName = 'Elsie'|| char(randNum);
	END CASE;
	
	set custname = trim(firstname) ||' ' || midInit || ' ' || trim(lastName);
	
	/* create custADDR */
	set addrNum = char(integer((rand()*5000) +1));
	set randNum = integer(rand() * 20);
	
	case randNum 
		when 0 then set addrName = 'Maple';
		when 1 then set addrName = 'Elm';
		when 2 then set addrName = 'Muckleberry';
		when 3 then set addrName = 'Mechanic' ;
		when 4 then set addrname = 'Loolee';
		when 5 then set addrname = 'Main';
		when 6 then set addrname = 'Spackling';
		when 7 then set addrname = 'Operknockity';
		when 8 then set addrname = 'Bumpkin';
		when 9 then set addrname = 'Douglass';
		when 10 then set addrname = 'Slapton';
		when 11 then set addrname = 'UnkMunk';
		when 12 then set addrname = 'Frippertronic';
		when 13 then set addrname = 'Sesame';
		when 14 then set addrname = 'No-name';
		when 15 then set addrname = 'Uptown';
		when 16 then set addrname = 'Downtown';
		when 17 then set addrname = 'Leftmost' ;
		when 18 then set addrname = 'Outermost' ;
		when 19 then set addrname = 'Outskirts' ;
		else set addrname = 'Outcast' ;
	end case;
	
	set randNum = integer(rand() *5);
	
	case randNum
		when 0 then set addrST = 'St.';
		when 1 then set addrst = 'Ln.';
		when 2 then set addrst = 'Bvd.';
		when 3 then set addrst = 'Ave.';
		when 4 then set addrst = 'Drive';
		else set addrst = 'St.';
	end case;
	
	set custaddr = addrnum || ' '|| trim(addrname) || ' ' || trim(addrST) ;
	
	/* set city */
	set randNum = integer (rand()*20);
	
	case randNum
		when 0 then set custCity = 'Nutley';
		when 1 then set custCity = 'New New York';
		when 2 then set custCity = 'Buxton';
		when 3 then set custCity = 'Moosen' ;
		when 4 then set custCity = 'Kapow';
		when 5 then set custCity = 'New London';
		when 6 then set custCity = 'Bowxer';
		when 7 then set custCity = 'Clarksville';
		when 8 then set custCity = 'Mudsville';
		when 9 then set custCity = 'Springfield';
		when 10 then set custCity = 'Shelbyville';
		when 11 then set custCity = 'Monkton';
		when 12 then set custCity = 'Keene';
		when 13 then set custCity = 'New Haven';
		when 14 then set custCity = 'Freedom City';
		when 15 then set custCity = 'Appleton';
		when 16 then set custCity = 'Hamburg';
		when 17 then set custCity = 'Big Cow' ;
		when 18 then set custCity = 'Many Monkeys' ;
		when 19 then set custCity = 'Purple Nurple' ;
		else set custCity = 'Outcast' ; 
		
	end case;
	

	
	/* set state via RRN */
	set randNum = integer((rand()*50)+1);
	set custst = (select stabbr from cs362djc.states where rrn(cs362djc.states) = randNum);
	
	/* set zip code */
	set zip1 = (char (integer (rand()*10)));
	set zip2 = (char (integer (rand()*10)));
	set zip3 = (char (integer (rand()*10)));
	set zip4 = (char (integer (rand()*10)));
	set zip5 = (char (integer (rand()*10)));
	set custzip = zip1 || zip2 || zip3 || zip4 || zip5;
	
	/* build the insert statement */

	set SQLStmt = 'INSERT INTO cs362djc.custmast (custid, custname, custaddr, custcity, custzip, custst)
	VALUES ( ''' || custid || ''' ,''' ||  custname ||''' ,''' || custaddr || ''', ''' || custcity|| ''',''' || custzip || ''',''' || custst || ''' )' ;
	execute immediate SQLStmt;
	
	set randNum = rand()*10;
	call  cs362djc.OrdDtaGen (randNum, custID);
	
	END WHILE CustMast;
END;


/*run and test */

call cs362djc.CustDtaGen(40);

select * from cs362djc.custmast;




/* ********************************** */
/* Object: cs362djc.OrdDtaGen         */
/* Author: Dana Clark                 */
/* Purpose: Generate Dummy Data for   */
/*        Order table.                */
/* ********************************** */


drop procedure cs362djc.OrdDtaGen;

CREATE PROCEDURE
   CS362DJC.OrdDtaGen 	(  IN numberOrders  Dec(9,0), IN custID Dec(9,0) )
   
 LANGUAGE SQL
 BEGIN
 /* *****declare variables***** */
 
 /* Table Data Variables */
 
	/* cs362djc.ORDERS */
	declare OCustID DECIMAL(9, 0); 
	declare OShipADDR1 CHAR(30)  ; 
	declare OShipADDR2 CHAR(30)  ;
	declare OShipCITY CHAR(30) ;
	declare OShipZIP CHAR(10)  ;
	declare OShipST VARCHAR(2) ;
	declare OrdDATE DATE ; 
	declare Dollars DECIMAL(11, 2) ;

	/* order items cs362djc.Items*/
	declare ITMORDERNUM INTEGER ;
	declare ItmEntryNum Decimal (9,0);
	declare ITEMNUM 	DECIMAL (9,0) ;
	declare ITMQTY 		DECIMAL (9,0) ;
	declare ITMDATEDUE 	DATE ;
	declare ITMCOST 	DECIMAL (9, 2) ;  /* total of quantity ordered x item price */
	
 
	
 /*utility variables*/
 DECLARE orderCount     decimal(9,0);
 DECLARE itemCount   	decimal(9,0);
 DECLARE itemEntryCount decimal(9,0);
 
 DECLARE SQLStmt     	char(2000);
 DECLare rowCount		decimal(9,0);
 DECLARE randNum   		integer;
 DECLARE randNumItems 	integer;
 Declare itmCatNum 		Decimal (9,0);
 
 /* loop for the number of times passed into the procedure and build variables */

SET orderCount = 0;
SET itemCount = 0;
SET ItmOrderNum =  (select MAX(cs362djc.ORDERS.OrderNum) from cs362djc.orders);

Label1:
WHILE orderCount < numberOrders DO
	SET Dollars = 0;
	SET itemcount = 0;
	SET itemEntryCount=0;
	SET orderCount = orderCount +1;
	SET ItmOrderNum = ItmOrderNum+10;
	
	set rowCount = (select count(*) from cs362djc.custMast);
	set randNum = integer((rand()*rowCount)+1);
	
	set OCustId = custID;
	set OShipAddr1 = (select custaddr from cs362djc.custmast where custId = OCustId);
	set OShipCity = (select custCity from cs362djc.custmast where custId = OCustId);
	set OShipSt = (select custST from cs362djc.custmast where custId = OCustId);
	set OShipZip = (select custZip from cs362djc.custmast where custId = OCustId);
	
	
	set SQLStmt = 'INSERT INTO cs362djc.Orders (OcustId, OShipAddr1, OshipCity, OShipSt, OShipZip)
	VALUES ( ' || OCustId || ' ,''' ||  OShipAddr1 ||''' ,''' || OShipCity || ''', ''' || OShipSt|| ''',''' || OShipZip || ''' )' ;
	execute immediate SQLStmt;
	
	
	
	
	/* set due date to today + up to 50 days in the future  apply to all items on order*/
	set itmdatedue = (current_date + (rand()*50+1)days);
			
	/* Pick a random number of items to order */		
	set randNumItems = (integer(rand()*10));
	set rowCount = (select count(*) from cs362djc.itemCat);
	
		Label2:
		While itemCount <  randNumItems DO

			/*increment itemCount*/
			set itemCount = itemCount+1;
			set itemEntryCount = itemEntryCount+1;
			
			/* set itemEntryNum field */
			set itmEntryNum = itemEntryCount;
			
			/*select an Item from itemCat to attach to Order*/
			set randNum = integer((rand()*rowCount)+1);
			set ItemNum = (select cs362djc.itemcat.itmCatNum from cs362djc.itemcat where rrn(cs362djc.itemCat) = randNum);
			
			/*set number of Items ordered) */
			set randNum = rand()*10+1;
			set ItmQTY = randNum;
	
			/* set itmCost */
			set itmcost = itmQTY * (select cs362djc.itemcat.itmCatPrice from cs362djc.itemCat where ItemNum = cs362djc.itemcat.itmCatNum); 
			/* set itmcost = 34.98; */
			
			Set Dollars = Dollars + itmCost;
			
			set SQLStmt= 'Insert into cs362djc.Items (ItmOrderNum, itmEntryNum, ItemNum, ItmDateDue, ItmCost , ItmQTY)
			values (' || ItmOrderNum || ', ' || itmEntryNum || ', ' || ItemNum || ', ''' || char(ItmDateDue) || ''',' || ItmCost ||  ', ' || ItmQTY || ')';
			
			execute immediate SQLStmt;
		
		end while label2;	
	
	/* build the insert statement */

	set SQLStmt = 'Update  cs362djc.Orders set cs362djc.Orders.Dollars = ' || Dollars || ' where cs362djc.Orders.OrderNum = ' || ItmOrderNum;
	execute immediate SQLStmt;
	
	
	END WHILE Label1;
END;

/* test the procedure */
CALL cs362djc.OrdDtaGen(2, 1);

select * from cs362djc.Orders;
select * from cs362djc.Items;
select * from cs362djc.ItemCat;
select * from cs362djc.CustMast;

select  cm.custID, cm.custName, o.OrderNum, ic.itmCatName from cs362djc.custmast cm, cs362djc.Orders o, cs362djc.ItemCat ic, cs362djc.Items id
	where
id.ItmOrderNum = o.OrderNum and ic.itmCatNum = id.itemNum  and cm.custID = o.ocustID;