BEGIN
	DECLARE xuser_found int(1);
	DECLARE xemail varchar(100);
	DECLARE xbalance DECIMAL(10,3) DEFAULT 0.0;
	DECLARE xlimit DECIMAL(10,3); -- this is the balance limit
	DECLARE xflags varchar(10);  -- flag field from the user record
	DECLARE xcustomer_donation_type CHAR(1) DEFAULT 'X';
	DECLARE xcustomer_donation_amt DECIMAL(10,3) DEFAULT 0.0;

	DECLARE xorder_id int(11);
	DECLARE xorder_detail_id int(11);
	DECLARE xitem_id int(11);
	DECLARE xitem_external_id VARCHAR(50) DEFAULT NULL;  -- used for sending to a POS integration

	DECLARE xmenu_id int(11);
	DECLARE xmenu_version DECIMAL(10,2);
	DECLARE xmenu_merchant_id int(11);    
	DECLARE xmenu_item_owner int(11);  -- used to make sure the item is owned by the merchant that is identified in the order
	DECLARE xitem_quantity int(11);
	DECLARE xmenu_type_name VARCHAR(50);
	DECLARE xmenu_type_active CHAR(1);
	DECLARE xmenu_type_id int(11);
	DECLARE xsize_id int(11);
	DECLARE xsizeprice_id int(11);
	DECLARE xsize_name  VARCHAR(100);
	DECLARE xsize_print_name  VARCHAR(100);
	DECLARE xitem_name  VARCHAR(100);
	DECLARE xitem_active CHAR(1);
	DECLARE xitem_print_name  VARCHAR(100);
	DECLARE xitem_price  DECIMAL(10,3) DEFAULT 0.0;
	DECLARE xitem_tax_group INT(1) DEFAULT 1;
	DECLARE xitem_tax_rate DECIMAL(10,3) DEFAULT 0.0;
	DECLARE xitem_price_active CHAR(1);
	DECLARE xitem_sub_total  DECIMAL(10,3) DEFAULT 0.0;
	DECLARE xitem_nameofuser VARCHAR(100); -- used to attach a name to an order item
	DECLARE xitem_note VARCHAR(100); -- used to attach a note to a particular order item
	DECLARE xquantity int(11) DEFAULT 1; -- total numver of items in the order
	DECLARE xnote varchar(250) DEFAULT ''; -- note for entire order
	DECLARE xitem_points_used INT(11) DEFAULT 0;
	DECLARE xitem_amount_off_from_points  DECIMAL(10,2) DEFAULT 0.0;
	DECLARE xtrans_fee_amt DECIMAL(10,3) DEFAULT 0.25;  -- we will actually get this from teh merchant object
	DECLARE xtrans_fee_amt_override DECIMAL(10,3);  -- in this way we can set users trans fee as an override.
	DECLARE xsub_total DECIMAL(10,3) DEFAULT 0.0;

	DECLARE xtax_total_rate DECIMAL(10,3) DEFAULT 0.0;
	DECLARE xtax_total_amt DECIMAL(10,3) DEFAULT 0.0;
	DECLARE xtax_running_total_amt DECIMAL(10,3) DEFAULT 0.0;
	DECLARE xstatus CHAR(1) DEFAULT 'O';  -- order status
	DECLARE xgrand_total DECIMAL(10,3) DEFAULT 0.0;

	DECLARE xorder_type VARCHAR(10);
	DECLARE xpromo_code VARCHAR(50);
	DECLARE xpromo_id int(11);
	DECLARE xpromo_amt DECIMAL(10,3) DEFAULT 0.0;
	DECLARE xpromo_tax_amt DECIMAL(10,3) DEFAULT 0.0;

	-- skin stuff
	DECLARE xskin_donation_active CHAR(1) DEFAULT 'N';
	DECLARE xskin_in_production CHAR(1);

	DECLARE xmerchant_type CHAR(1); -- full or quick
	DECLARE xnumeric_id int(11);
	DECLARE xorder_del_type CHAR(1); -- P,T,F  etc.  we need this so that we know when to use the abbreviations.
	DECLARE xmerch_default_lead int(11);
	DECLARE xactive CHAR(1);  -- flag to determine if merchant is active
	DECLARE xmerchant_name VARCHAR(50);
	DECLARE xmerch_delete CHAR(1);  -- flag to determine if merchant is no longer in program
	DECLARE xmerch_donate_on CHAR(1) DEFAULT 'N';  --  indicates if the merchant is participating in the food bank donation program.
	DECLARE xmerch_donate_amt DECIMAL(10,3) DEFAULT 0.000;
	DECLARE xdelivery_active CHAR(1) DEFAULT 'N';
	DECLARE xdelivery_minimum DECIMAL(10,2) DEFAULT 0.00;
	DECLARE xdelivery_cost DECIMAL(10,2)	DEFAULT 0.00;
	DECLARE linebuster CHAR(1) DEFAULT 'N';

	DECLARE xtime_zone INT(11);
	DECLARE xtime_zone_offset INT(11);
	DECLARE xserver_offset INT(11);
	DECLARE xlocal_pickup_dt datetime DEFAULT NULL;
	DECLARE xlocal_order_dt datetime DEFAULT NULL;

-- CHANGE THIS?
	DECLARE xserver_pickup_dt timestamp;
	DECLARE xlocal_leadCon TIME;
	DECLARE xserver_leadCon TIME;
	
	DECLARE found int(11)  DEFAULT 0;
	DECLARE lto_found int(11)  DEFAULT 0;
	DECLARE row_count int(11) default  0;
	DECLARE xlogical_delete CHAR(1);
	DECLARE xtest varchar(5) default 'false'; -- DEPRICATED   used to indicate that this is a test message
	DECLARE xskip_hours int(1) default 0; -- used to indicate that this is a test message
	DECLARE xdbname varchar(20); -- used to ehlp when applying rules
	DECLARE xholiday_hours int(1) DEFAULT 0; -- used to indicate that these are holiday hours

	-- temp table stuff
	DECLARE xtemp_order_detail_id int(11);
	DECLARE xnum_of_temp_order_items int(11);
	DECLARE xtemp_order_item_mod_id int(11);
	DECLARE xcalced_order_total DECIMAL(10,3) DEFAULT 0.0; -- USING THIS TO TEST HTE APPS MATH ON ORDER CALC.  COULD BE USEFUL.
	DECLARE xcalced_order_sub_total DECIMAL(10,3) DEFAULT 0.0; -- USING THIS TO TEST HTE APPS MATH ON ORDER CALC.  COULD BE USEFUL.
	
	DECLARE xcash CHAR(1);

	DECLARE xmodifier_group_price_override DECIMAL (10,3);

	DECLARE xraw_stamp VARCHAR(255);
	DECLARE xserver VARCHAR(10);

	-- DECLARE orderItems CURSOR FOR SELECT temp_order_detail_id,sizeprice_id, quantity,name,note FROM TempOrderItems;
	DECLARE orderItems CURSOR FOR SELECT temp_order_detail_id,sizeprice_id, quantity,name,note,points_used,amount_off_from_points FROM TempOrderItems;
	
	SELECT DATABASE() INTO xdbname;
	SELECT value INTO xserver FROM Property WHERE name = 'server';

	-- determine if this is a test
	IF LOCATE('skip hours',LOWER(in_note)) > 0 THEN
		SET xskip_hours = 1;
	END IF;

		INSERT INTO Errors values(null,CONCAT('NO STAMP',' Starting SMAWSP_CREATE_ORDER'),CONCAT('user:',in_user_id),'about to get temporary order data',CONCAT('Note:',in_note),now());
		-- get additional fields that were passed in through the TempOrders  table
		SELECT order_type,promo_code,promo_id,promo_amt,promo_tax_amt,delivery_amt,cash,stamp,trans_fee_amt INTO xorder_type,xpromo_code,xpromo_id,xpromo_amt,xpromo_tax_amt,xdelivery_cost,xcash,xraw_stamp,xtrans_fee_amt FROM TempOrders; 

		SET logit = 1;
        
         INSERT INTO Errors values(null,CONCAT(xraw_stamp,' Starting SMAWSP_CREATE_ORDER'),CONCAT('user:',in_user_id),'GOT TEMP DATA',CONCAT('Note:',in_note),now());

mainBlock:BEGIN

		-- get skin properties
		SELECT in_production, donation_active INTO xskin_in_production, xskin_donation_active FROM Skin WHERE skin_id = in_skin_id;

		-- Get user properties
		SELECT 1,balance,credit_limit,flags,trans_fee_override,email 
		INTO xuser_found,xbalance,xlimit,xflags,xtrans_fee_amt_override,xemail 
		FROM User
		WHERE user_id = in_user_id and logical_delete = 'N';

		IF xuser_found IS NULL THEN
				SET out_return_id = 100;
				SET out_message = 'SERIOUS_DATA_ERROR_USER_ID_DOES_NOT_EXIST';
				IF logit THEN
					INSERT INTO Errors values(null,CONCAT(xraw_stamp,' SERIOUS APP ERROR! THIS USER DOES NOT EXIST'),CONCAT('user:',in_user_id),'','',now());
				END IF;
				LEAVE mainBlock;
		END IF;

/*  Doing this in the controller now

		IF xcash = 'N' AND substring(xflags,1,2) != '1C' AND (in_sub_total > 0.00 OR in_tip > 0.00) THEN
				SET out_return_id = 150;
				SET out_message = 'NO_CREDIT_CARD_ON_FILE';
				IF logit THEN
					INSERT INTO Errors values(null,CONCAT(xraw_stamp,' USER DOES NOT HAVE A CREDIT CARD ON FILE'),CONCAT('user:',in_user_id),'','',now());
				END IF;
				LEAVE mainBlock;
		END IF;
*/		
		-- SUCCESS WITH USER, AND BALANCE SO NOW GET ALL MERCHANT DATA TO CHECK ITEMS ARE ACTIVE, MERCHANT IS OPEN, AND HOW TO SEND THE ORDER

		SELECT m.name,m.time_zone,m.lead_time,m.active,m.merchant_type,m.order_del_type,m.donate,m.logical_delete,m.numeric_id
		INTO xmerchant_name, xtime_zone, xmerch_default_lead,xactive,xmerchant_type,xorder_del_type, xmerch_donate_on,xmerch_delete,xnumeric_id
		FROM Merchant m
		WHERE m.merchant_id = in_merchant_id;

		-- detemine if we need to get the merchant delivery info for xmerch_default_lead
		IF in_order_type = 'D' THEN
			SELECT minimum_delivery_time INTO xmerch_default_lead FROM Merchant_Delivery_Info WHERE merchant_id = in_merchant_id AND logical_delete = 'N';
		END IF;

		IF xmerchant_name IS NULL THEN
				-- serious data error. merchant doesn't exist
				SET out_return_id = 500;
				-- SET xmerchant_name = CONCAT('SORRY :( ', xmerchant_name,' is not accepting splick-it orders at this time.  Sorry for the inconvenience');
				SET out_message = 'SERIOUS_DATA_ERROR_MERCHANT_ID_DOES_NOT_EXIST';
				IF logit THEN
					INSERT INTO Errors values(null,CONCAT(xraw_stamp,' SERIOUS APP ERROR! THIS MERCHANT DOES NOT EXIST'),CONCAT('user:',in_user_id),CONCAT('merch:',in_merchant_id),'',now());
				END IF;
				LEAVE mainBlock;
		END IF;

		-- merchant has discontinued their association with splick-it
		IF xmerch_delete = 'Y' THEN
			SET out_return_id = 510;
			SET out_message = 'MERCHANT_DELETED';
			IF logit THEN
				INSERT INTO Errors values(null,CONCAT(xraw_stamp,' ERROR! THIS MERCHANT IS NO LONGER REGISTERED WITH SPLICKIT'),CONCAT('user:', in_user_id),CONCAT('merch:', in_merchant_id),'',now());
			END IF;
			LEAVE mainBlock;
		END IF;

		-- merchant could be innactive
		IF (xactive = 'N' AND in_user_id > 1000) THEN
			SET out_return_id = 520;
			SET out_message = 'MERCHANT_NOT_ACTIVE';
			IF logit THEN
				INSERT INTO Errors values(null,CONCAT(xraw_stamp,' ERROR! THIS MERCHANT IS NOT ACTIVE'),CONCAT('user:', in_user_id),CONCAT('merch:', in_merchant_id),'',now());
			END IF;
			LEAVE mainBlock;
		END IF;

		IF xemail = CONCAT(xnumeric_id,'_manager@order140.com') THEN
			SET linebuster = 'Y';
			INSERT INTO Errors values(null,CONCAT(xraw_stamp,' LINE BUSTER!'),CONCAT('user:', in_user_id),CONCAT('merch:', in_merchant_id),'',now());
		END IF;

		-- get total tax from merchant (this is jsut a place holder now since items may have different tax rates)
		SELECT sum(rate) INTO xtax_total_rate FROM `Tax` WHERE merchant_id = in_merchant_id AND tax_group = 1 AND logical_delete = 'N';

		IF logit THEN
			INSERT INTO Errors values(null,CONCAT(xraw_stamp,' SUCCESS! WITH USER AND QUICK MERCHANT.'),CONCAT('user:', in_user_id),CONCAT('merch:',xmerchant_name),CONCAT('tax 1',xtax_total_rate),now());
		END IF;

		-- determine if there is a customer trans fee override
/*		IF xtrans_fee_amt_override IS NOT NULL THEN
			SET xtrans_fee_amt = xtrans_fee_amt_override;
		END IF;
*/
		SELECT value INTO xserver_offset FROM Property WHERE name = 'default_server_timezone_offset';
		SET xtime_zone_offset = (60*60*(xtime_zone - xserver_offset));

		-- we now need to get the pickup time. since the server and shop could be in differenct TZ, we need to calculate what the pickup time is for both. 
		-- local pickup time is for hte user and the merchant, server pickup time is so we know when to send messages out.
		
		-- rounding hack since phone time appears to be different or round differently
		IF SECOND(NOW()) > 0 THEN
			-- SET in_lead = in_lead+1;
			SET in_lead = in_lead;
		END IF;

		IF in_lead IS NOT NUll THEN
			-- user entered a lead time detemine if its greater than the merchants default, if not then set to default
			IF in_lead > xmerch_default_lead THEN
				SET xlocal_leadCon = SEC_TO_TIME(60*in_lead + xtime_zone_offset);
				SET xserver_leadCon = SEC_TO_TIME(60*in_lead);
			ELSE
				SET xlocal_leadCon = SEC_TO_TIME(60*xmerch_default_lead + xtime_zone_offset);
				SET xserver_leadCon = SEC_TO_TIME(60*xmerch_default_lead);
			END IF;
		ELSE
			-- there was no lead time sent so set it to the merchant default.
			SET xlocal_leadCon = SEC_TO_TIME(60*xmerch_default_lead + xtime_zone_offset);
			SET xserver_leadCon = SEC_TO_TIME(60*xmerch_default_lead);
		END IF;

		SET xlocal_order_dt = ADDTIME(now(),SEC_TO_TIME(xtime_zone_offset));
		SET xlocal_pickup_dt = ADDTIME(now(),xlocal_leadCon);
		SET xserver_pickup_dt = ADDTIME(now(),xserver_leadCon);

		IF logit THEN
			INSERT INTO Errors values(null,CONCAT(xraw_stamp,' Local Pickup time set'),CONCAT('user:', in_user_id),CONCAT('Date:',xlocal_pickup_dt),'',now());
			INSERT INTO Errors values(null,CONCAT(xraw_stamp,' Local Order time set'),CONCAT('user:', in_user_id),CONCAT('Date:',xlocal_order_dt),'',now());
			INSERT INTO Errors values(null,CONCAT(xraw_stamp,' Server pickup time set'),CONCAT('user:', in_user_id),CONCAT('Date:',xserver_pickup_dt),'',now());
		END IF;

	foundBlock:BEGIN
		-- calculate tax amount
		-- probably dont need to do this anymore since we reset the subtotal after the item loop.
		-- we are not figuring in teh promo amt here since dave already calulates is as part of the in_sub_total;

-- again these are just place holders for now.  we'll recalculate and update before making the order final.  in fact on older versions of the app, the subtotal thats calculated will be wrong because of incorrect use of promo amt
		SET xtax_total_amt = xtax_total_rate/100 * in_sub_total;
		SET xgrand_total = xtax_total_amt+in_sub_total+xtrans_fee_amt+xcustomer_donation_amt+in_tip;

		IF logit THEN
			INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' Values to send to create the order with'),CONCAT('user:',in_user_id),CONCAT(':MerchID ', in_merchant_id,':',xmerchant_name,':User',in_user_id,':',xlocal_pickup_dt,':',xserver_pickup_dt,':',xsub_total,':',xtrans_fee_amt,':',0.0,':GTotal ',xgrand_total,':',in_note,':',xstatus,':',xquantity),'',NOW());
		END IF;

/*		-- we are now setting the pickup_dt_tm to be alligned with eh server on GMT
		-- actually No we're not.  pickup and order time are the local times at the merchant since these are only used for display purposes.
		IF xdbname = 'smaw_test' THEN
			SET xlocal_pickup_dt = xserver_pickup_dt;
			SET xlocal_order_dt = NOW();
		END IF;
*/
		-- begin the actual creation of the order.  we do this as a transaction so we can roll it all back if there is any problem with any data insert (items, mods, users balance, messages, etc)
		START TRANSACTION;
-- HERE
			INSERT INTO `Orders` (`merchant_id`,`order_dt_tm`,`user_id`,`pickup_dt_tm`,`order_amt`,`total_tax_amt`,`trans_fee_amt`, `tip_amt`,`promo_amt`,`customer_donation_amt`,`grand_total`, `merchant_donation_amt`, `note`, `status`, `order_qty`,`order_type`,`skin_id`,`created`)
			VALUES (in_merchant_id, xlocal_order_dt,in_user_id,xlocal_pickup_dt,in_sub_total,xtax_total_amt,xtrans_fee_amt,in_tip,-xpromo_amt,xcustomer_donation_amt,xgrand_total, xmerch_donate_amt, in_note,'P',xquantity,in_order_type,in_skin_id,NOW()); 

			SELECT LAST_INSERT_ID() INTO xorder_id;
			SET out_return_id = xorder_id;
			IF logit Then
				INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' Order added to Orders'),CONCAT('user:',in_user_id),CONCAT('Order:',xorder_id),'',now());                       
			END IF;

			-- now loop through the temporary table
			OPEN orderItems;

			BEGIN
				DECLARE noMoreRows int(1) DEFAULT 0;
				DECLARE xmenu_type_start_time TIME; -- to determine if the menu type is only available certain hours
				DECLARE xmenu_type_end_time	TIME; -- to determine if the menu type is only available certain hours 
				DECLARE xcalced_item_sub_total DECIMAL (10,3) DEFAULT 0.00; -- to hold the total price of this item (mods and anything else) before tax caculations
				DECLARE CONTINUE HANDLER FOR NOT FOUND
				BEGIN
					SET noMoreRows = 1;
				END;

				-- GET NUMBER OF ITEMS IN THE TABLE
				SELECT COUNT(sizeprice_id) INTO xnum_of_temp_order_items FROM TempOrderItems;

				SET xcalced_order_total = 0.0;
			
			-- now loop through each item in the temp table 
			orderInsertLoop:LOOP
		
				-- FETCH orderItems INTO  xtemp_order_detail_id,xsizeprice_id,xitem_quantity, xitem_nameofuser,xitem_note;
				FETCH orderItems INTO  xtemp_order_detail_id,xsizeprice_id,xitem_quantity, xitem_nameofuser,xitem_note,xitem_points_used,xitem_amount_off_from_points;
				IF noMoreRows THEN
					leave orderInsertLoop;
				END IF;
				INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' got info from temporary table'),CONCAT('SPid:', xsizeprice_id),CONCAT('qty:', xitem_quantity),CONCAT(xitem_nameofuser,':',xitem_note),now());                       

				SELECT a.item_id,a.size_id,b.item_name,b.item_print_name,a.price,a.active,a.merchant_id,d.size_name,d.size_print_name,
					c.start_time,c.end_time,a.external_id,a.tax_group,c.menu_type_name,b.active,c.active,c.menu_type_id  
				INTO xitem_id,xsize_id,xitem_name,xitem_print_name,xitem_price,xitem_price_active,xmenu_item_owner,xsize_name,xsize_print_name,xmenu_type_start_time,
					xmenu_type_end_time, xitem_external_id,xitem_tax_group,xmenu_type_name,xitem_active,xmenu_type_active,xmenu_type_id    
				FROM Item_Size_Map a, Item b, Menu_Type c, Sizes d 
				WHERE a.item_id = b.item_id AND a.item_size_id = xsizeprice_id AND b.menu_type_id = c.menu_type_id AND a.size_id = d.size_id;

				IF xitem_id IS NULL THEN
					ROLLBACK;
					CLOSE orderItems;
					INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' DATA INTEGRITY! ERROR, ITEM NO LONGER EXISTS FROM APP!'),'','','',now()); 
					SET out_return_id = 705;
					SET out_message = 'DATA_INTEGRITY_ERROR_APP_ITEM_NO_LONGER_EXISTS';
					leave foundBlock;
				END IF;

				-- first check to see if the menu type is available at the pickup time but dont check if this is the test system
-- CHANGE THIS  it blows up on inverted hours
			IF xdbname = 'smaw_prod' AND xskip_hours = 0 THEN
				IF TIME(xlocal_pickup_dt) < xmenu_type_start_time OR TIME(xlocal_pickup_dt) > xmenu_type_end_time THEN
					ROLLBACK;
					CLOSE orderItems;
					INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' ITEM OUT OF MENU TYPE HOURS!'),CONCAT('SPid:', xsizeprice_id),CONCAT('qty:', xitem_quantity),
												CONCAT(xitem_id,':',xsize_id,':',xitem_name,':',xitem_price,':',xitem_price_active,':', xlocal_pickup_dt),now()); 
					SET out_return_id = 780;
					IF TIME(xlocal_pickup_dt) < xmenu_type_start_time THEN
						SET out_message = CONCAT('SORRY :( The ',xitem_name,' isn''t available until ',TIME_FORMAT(xmenu_type_start_time,'%l:%i'),'. Please remove from your cart before placing your order. Thanks for using splick-it!');
					ELSE
						SET out_message = CONCAT('SORRY :( The ',xitem_name,' isn''t available after ',TIME_FORMAT(xmenu_type_end_time,'%l:%i'),'. Please remove from your cart before placing your order. Thanks for using splick-it!');
					END IF;
					leave foundBlock;
				END IF;                      
			END IF;	

				IF xitem_price_active = 'N' OR xitem_active = 'N' OR xmenu_type_active = 'N' THEN
					-- SHOULD NEVER HAPPEN SINCE I SEND THE ACTIVE ITEMS TO THE APP WHEN THE CUSTOMER STARTS THE APP
					ROLLBACK;
					CLOSE orderItems;
					INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' DATA INTEGRITY! ERROR ITEM NOT ACTIVE FROM APP!'),CONCAT('SPid:', xsizeprice_id),CONCAT('qty:', xitem_quantity),
												CONCAT(xitem_id,':',xsize_id,':',xitem_name,':',xitem_price,':',xitem_price_active),now()); 
					SET out_return_id = 700;
					SET out_message = 'DATA_INTEGRITY_ERROR_APP_ITEM_NOT_ACTIVE';
					leave foundBlock;
				END IF;  

				IF xmenu_type_active = 'L' THEN
					-- WE HAVE AN LTO SO LETS CHECK TO MAKE SURE ITS ON RIGHT NOW.
					SELECT 1 INTO lto_found FROM Menu_Change_Schedule WHERE object_type = 'menu_type' AND object_id = xmenu_type_id AND day_of_week = DAYOFWEEK(xlocal_pickup_dt) AND active = 'Y' AND TIME(xlocal_pickup_dt) > start AND TIME(xlocal_pickup_dt) < stop AND merchant_id = in_merchant_id;

					IF lto_found THEN
						INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' SUCCESSFULL LTO VERIFICATION'),CONCAT('menu_type:', xmenu_type_id),NULL,
										CONCAT(xitem_id,':',xsize_id,':',xitem_name,':',xitem_price,':',xitem_price_active),now());
					ELSE
	
						ROLLBACK;
						CLOSE orderItems;
						INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' MENU ERROR! LTO MENU_TYPE NOT ACTIVE FROM APP!'),CONCAT('menu_type:', xmenu_type_id),NULL,
										CONCAT(xitem_id,':',xsize_id,':',xitem_name,':',xitem_price,':',xitem_price_active),now()); 
						SET out_return_id = 715;
						SET out_message = 'DATA_INTEGRITY_ERROR_LTO_NOT_VALID';
						leave foundBlock;
					END IF;
				END IF;  

				-- make sure the item is owned by the merchant referenced in the order
				IF in_order_type = 'D' THEN
					SELECT menu_id INTO xmenu_id FROM Merchant_Menu_Map WHERE merchant_id = in_merchant_id AND merchant_menu_type = 'delivery' AND logical_delete = 'N';
				ELSE
					SELECT menu_id INTO xmenu_id FROM Merchant_Menu_Map WHERE merchant_id = in_merchant_id AND merchant_menu_type = 'pickup' AND logical_delete = 'N';
				END IF;
				
				-- get version of menu
				SELECT version INTO xmenu_version FROM Menu WHERE menu_id = xmenu_id AND logical_delete = 'N';
				
				IF xmenu_version > 2.0 THEN
					SET xmenu_merchant_id = in_merchant_id;
				ELSE
					SET xmenu_merchant_id = 0;
				END IF;
			
				IF logit THEN			
					INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' got item info from Item_Size_Map'),CONCAT('SizePriceId:', xsizeprice_id,'   qty:', xitem_quantity),CONCAT('item_id:', xitem_id,'  size_id:', xsize_id),CONCAT('name:',xitem_name,'  price:',xitem_price,'  price_active:',xitem_price_active),now());                       
			 	END IF;

				IF xmenu_item_owner != xmenu_merchant_id THEN
					-- should never happen
					ROLLBACK;
					CLOSE orderItems;
					INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' DATA INTEGRITY ERROR! ITEM NOT OWNED BY THIS MERCHANTANT!'),CONCAT('merchant_id: ', xmenu_merchant_id),CONCAT('owner id:', xmenu_item_owner),'',now()); 
					SET out_return_id = 710;
					SET out_message = 'DATA_INTEGRITY_ERROR_ITEM_NOT_OWNED_BY_SUBMITTED_MERCHANT';
					leave foundBlock;
				END IF;                      
				
				SET xitem_sub_total = xitem_quantity*xitem_price;
				SET xcalced_item_sub_total = xitem_price;

				INSERT INTO `Order_Detail` ( `order_id`, `item_size_id`,`external_id`,`menu_type_name`,`size_name`,`size_print_name`,`item_name`,`item_print_name`,`name`,`note`,`quantity`,`price`,`item_total`,`created`)
				VALUES (xorder_id, xsizeprice_id, xitem_external_id, xmenu_type_name, xsize_name, xsize_print_name, xitem_name, xitem_print_name, xitem_nameofuser,xitem_note,xitem_quantity,xitem_price,xitem_sub_total,now());

				-- get last inserted id so we can associate the modifications with a particular item
				SELECT LAST_INSERT_ID() INTO xorder_detail_id;

				IF logit THEN
					INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' Item Added to Order_Items'),CONCAT('user:',in_user_id),CONCAT(xitem_id,':',xsize_id,':',xitem_quantity,':',xitem_price,':',xitem_sub_total),CONCAT('Order:',xorder_id),now());                          				END IF;
			
				-- Here starts the modification code
				BEGIN

					DECLARE noMoreModRows int(1) DEFAULT 0;
					DECLARE xmod_sizeprice_id int(11);
					DECLARE xmod_price DECIMAL(10,3) DEFAULT 0.0;
					DECLARE xmod_qty int(11);
					DECLARE xmod_total_price DECIMAL(10,3);
					DECLARE xmodifier_item_name VARCHAR(50);
					DECLARE xmodifier_item_print_name VARCHAR(50);
					DECLARE xmodifier_item_id int(11);
					DECLARE xmodifier_item_priority int(11);
					DECLARE xmodifier_item_external_id VARCHAR(50) DEFAULT NULL; -- POS integration
					DECLARE xmodifier_group_external_id VARCHAR(50) DEFAULT NULL; -- POS integration
					DECLARE xmodifier_concat_external_id VARCHAR(100) DEFAULT NULL; -- POS integration
					DECLARE xmodifier_group_name VARCHAR(50);
					DECLARE xmodifier_group_id int(11);
					DECLARE xmodifier_type CHAR(2) DEFAULT 'T';
					DECLARE found_comes_with int(1);
					DECLARE xcomes_with CHAR(1) DEFAULT 'N';
					DECLARE xhold_it CHAR(1) DEFAULT 'N';
					DECLARE xhold_it_modifier_group_id int(11);
					DECLARE xhold_it_modifier_group_name VARCHAR(50);

					DECLARE order_item_mods CURSOR FOR 
								SELECT a.mod_sizeprice_id,b.modifier_price,a.mod_quantity,(b.modifier_price*a.mod_quantity) as mod_total_price,c.modifier_item_name,c.modifier_item_print_name,
										c.modifier_item_id,d.modifier_group_name,d.modifier_type,d.modifier_group_id,b.external_id,c.priority,d.external_modifier_group_id 
								FROM TempOrderItemMods a, Modifier_Size_Map b, Modifier_Item c, Modifier_Group d 
								WHERE a.mod_sizeprice_id = b.modifier_size_id AND b.modifier_item_id = c.modifier_item_id AND c.modifier_group_id = d.modifier_group_id AND a.temp_order_detail_id = xtemp_order_detail_id;

					DECLARE CONTINUE HANDLER FOR NOT FOUND
					BEGIN
						INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' WE GOT A NOT FOUND IN TEH MODIFIER CURSOR'),'','','',now());
						SET noMoreModRows = 1;
					END;
				
					OPEN order_item_mods;
						
					modInsertLoop:LOOP
						FETCH order_item_mods INTO xmod_sizeprice_id,xmod_price,xmod_qty,xmod_total_price, xmodifier_item_name, xmodifier_item_print_name, xmodifier_item_id, xmodifier_group_name, xmodifier_type,xmodifier_group_id, xmodifier_item_external_id, xmodifier_item_priority, xmodifier_group_external_id;
						IF noMoreModRows THEN
							leave modInsertLoop;
						END IF;
						INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' got MODIFICATION price from temporary table'),CONCAT('mod_sizeprice_id:', xmod_sizeprice_id,'    mod_id:', xmodifier_item_id),CONCAT('mod_group_id: ', xmodifier_group_id),CONCAT('qty:', xmod_qty,'   total_mod_price:', xmod_total_price),now());              
	/*
						IF xmodifier_group_external_id IS NULL THEN
							SET xmodifier_concat_external_id = xmodifier_item_external_id;
						ELSE
							IF xmodifier_item_external_id IS NULL THEN
								SET xmodifier_concat_external_id = xmodifier_group_external_id;
							ELSE
								SET xmodifier_concat_external_id = CONCAT(xmodifier_group_external_id,':',xmodifier_item_external_id);
							END IF;
						END IF;
*/
						INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' the external values'),CONCAT('group_external:', xmodifier_group_external_id),CONCAT('item_external:', xmodifier_item_external_id),CONCAT('concat:', xmodifier_concat_external_id),now());

						IF xmodifier_type = 'Q' AND xmod_qty > 1 THEN
							SET xitem_quantity = xmod_qty;
						END IF;

						-- now determine if this is a comes with item, if so then deduct a single price unit from the total mod price (quantity*unit price).  the message builder will detmine if its shown on the ticket.  had to still include it in the order otherwiese the message builder will think its being held, as in HOLD the mayo.
						-- somthing else:  so if the sandwich is ham/swiss and the person swaps out cheddar for swiss, they will get charged for cheddar.  we made a concious decision NOT to deal with swaps.  tough luck kind of thing.

						-- but..... if the comes with modifier doesn't exist, we could test to see if an added modifier is in the same group as the comes with modifier, if so compare the price 
						--       and if they're the same then subtract.......  hmmmmm..........  maybe this is for 2.5

						-- first selection zero price override?????
							
						-- we can skip this code if the price is zero already though
						-- IF xmod_price > 0.00 THEN
							INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' CHECK IF MOD IS ON THE COMES WITH LIST'),CONCAT('mod_sizeprice_id:', xmod_sizeprice_id,'    mod_id:', xmodifier_item_id),CONCAT('mod_group_id: ', xmodifier_group_id),CONCAT('qty:', xmod_qty,'   total_mod_price:', xmod_total_price),now());                       
							set found_comes_with = 0;
							set xcomes_with = 'N';
							-- is this on the list?
							SELECT 'Y' INTO xcomes_with FROM Item_Modifier_Item_Map WHERE item_id = xitem_id AND modifier_item_id = xmodifier_item_id AND logical_delete = 'N';
							INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' comes with is: ', xcomes_with),'','','',now());
							IF xcomes_with = 'Y' THEN
								INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' Its on the comes with list'),NULL,NULL,NULL,now());                       

								-- with the addition of the price_override functionality we only need to do this calculation if price_override is 0.00.  if its more than zero, we'll let ht price get settled
								-- in the adjustment section below.
								SELECT price_override INTO xmodifier_group_price_override FROM Item_Modifier_Group_Map WHERE modifier_group_id = xmodifier_group_id AND item_id = xitem_id AND logical_delete = 'N' AND merchant_id = xmenu_merchant_id;
								
								IF xmodifier_group_price_override = 0.00 THEN
									-- no price override so let the comes with price logic work
									SET xmod_total_price = xmod_total_price - xmod_price;
								END IF;
							END IF;
								
							-- and now for the HACK.  
							-- the not found handler is screwing things up here when there was no rows found for comes with 
							-- so we need to set the noMoreModRows to '0' to keep the loop going
							SET noMoreModRows = 0;
						-- END IF;
							
						INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' about to do the insert for modifier'),CONCAT('name:', xmodifier_item_name),CONCAT('s_name:', xmodifier_item_print_name),'',now());						

						-- now insert it into the order_detail_mod table
						INSERT INTO `Order_Detail_Modifier` (`order_detail_id`,`modifier_size_id`,`external_id`,`modifier_item_id`,`modifier_item_priority`,`modifier_group_id`,`modifier_group_name`,`mod_name`,
										`mod_print_name`,`modifier_type`,`comes_with`,`hold_it`,`mod_quantity`,`mod_price`,`mod_total_price`,`created`)
						VALUES (	xorder_detail_id,xmod_sizeprice_id, xmodifier_item_external_id,xmodifier_item_id,xmodifier_item_priority,xmodifier_group_id,
 								xmodifier_group_name ,xmodifier_item_name,xmodifier_item_print_name,xmodifier_type,xcomes_with,xhold_it,xmod_qty,xmod_price,xmod_total_price,NOW());
		
						INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' modifier INSERTED!'),'','','',now());

						SET xcalced_item_sub_total = xcalced_item_sub_total + xmod_total_price;

					END LOOP; -- mod insert loop

					CLOSE order_item_mods;

					-- NOW FIGURE OUT WHAT THE 'HOLD THE' ITEMS ARE AND ADD THEM TO THE ORDER DETAIL
					holdtheBlock:BEGIN

						DECLARE comes_with_items CURSOR FOR SELECT a.modifier_item_id, b.modifier_item_name, b.modifier_item_print_name,b.modifier_group_id,b.priority 
												FROM Item_Modifier_Item_Map a, Modifier_Item b 
												WHERE a.item_id = xitem_id AND a.modifier_item_id = b.modifier_item_id AND a.logical_delete = 'N';						
						SET noMoreModRows = 0;
						INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' starting the hold it loop'),'','','',now());
						OPEN comes_with_items;
						holdtheInsertLoop:LOOP
							FETCH comes_with_items INTO xmodifier_item_id,xmodifier_item_name,xmodifier_item_print_name,xhold_it_modifier_group_id,xmodifier_item_priority;
							IF noMoreModRows THEN
								leave holdtheInsertLoop;
							END IF;
							SET xhold_it = 'Y';
							INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' looping'),xmodifier_item_id,xmodifier_item_name,CONCAT('order_datail: ', xorder_detail_id),now());
							SELECT 'N' INTO xhold_it FROM `Order_Detail_Modifier` WHERE order_detail_id = xorder_detail_id AND modifier_item_id = xmodifier_item_id;
							INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' after select N'),'','',CONCAT('order_datail: ', xorder_detail_id),now());
							IF xhold_it = 'Y' THEN
								INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' *****WE HAVE A HOLD IT!******'),xmodifier_item_id,xmodifier_item_name,'',now());

								-- short name sub for null short name.   legacy stuff.  shouldn't be nulls going forward.
								IF xmodifier_item_print_name IS NULL THEN
									-- SET xmodifier_item_name = xmodifier_item_print_name;
									SET xmodifier_item_print_name = xmodifier_item_name;
								END IF;
	
								-- for POS we need the maping id of the held item so....
								SET xmod_sizeprice_id = null;
								SELECT modifier_size_id,external_id INTO xmod_sizeprice_id, xmodifier_item_external_id FROM Modifier_Size_Map WHERE modifier_item_id = xmodifier_item_id AND size_id = xsize_id AND logical_delete = 'N' AND merchant_id = xmenu_merchant_id;
								IF xmod_sizeprice_id IS NULL THEN
								    SELECT modifier_size_id, external_id INTO xmod_sizeprice_id, xmodifier_item_external_id FROM Modifier_Size_Map WHERE modifier_item_id = xmodifier_item_id AND size_id = 0 AND logical_delete = 'N' AND merchant_id = xmenu_merchant_id;
								END IF;
								IF xmod_sizeprice_id IS NULL THEN
									-- ok looks like we have an orphaned Item_Modifier_Item record, so we'll just skip it.
								    INSERT INTO Errors VALUES (null,xraw_stamp,'EMAIL ERROR', 
											CONCAT('NULL value for mod_size_map_id. Skipping hold it insert. modifier_item_id: ', xmodifier_item_name,'  ',xmodifier_item_id),
											CONCAT('item_id:',xitem_id,'    merchant_id:', xmenu_merchant_id),now());
								ELSE
									INSERT INTO `Order_Detail_Modifier` (`order_detail_id`,`modifier_size_id`,`external_id`,`modifier_item_id`,`modifier_item_priority`,`modifier_group_id`,`mod_name`,`mod_print_name`,`modifier_type`,
												`comes_with`,`hold_it`,`mod_quantity`,`mod_price`,`mod_total_price`,`created`)
									VALUES (	xorder_detail_id, xmod_sizeprice_id, xmodifier_item_external_id,xmodifier_item_id,xmodifier_item_priority,xhold_it_modifier_group_id,xmodifier_item_name,xmodifier_item_print_name,'H','H',xhold_it,0,0.00,0.00,now());
								END IF;
							ELSE
								INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' do not hold it!'),xmodifier_item_id,xmodifier_item_name,'',now());
							END IF;	
							SET noMoreModRows = 0;
						END LOOP;

					END; -- HOLD THE BLOCK

					-- now do any price recalcs for zero price override or mod group max price.
					-- get the mod groups that have some rules
					-- determine total price on each of those groups in the order
					-- make subtotal changes if necessary 
						-- add a row in the modifier details table for this price change?  maybe. yes
						-- need a dummy modifier that holds this place in the order_details table?   should NOT display on receipt to user
					
					-- maybe can do a check here for a free/promo item
	priceadjustblock:BEGIN 
							
						DECLARE xmodifier_group_price_max DECIMAL (10,3);
						DECLARE xgroup_total_price_for_item DECIMAL (10,3); 
						DECLARE xadjustment_amount DECIMAL (10,3);
					
						DECLARE order_item_mod_groups CURSOR FOR SELECT modifier_group_id,price_override,price_max FROM Item_Modifier_Group_Map 
									WHERE item_id = xitem_id AND logical_delete = 'N' AND merchant_id = xmenu_merchant_id AND 
									modifier_group_id IN (SELECT DISTINCT modifier_group_id FROM Order_Detail_Modifier WHERE order_detail_id = xorder_detail_id AND ( modifier_type LIKE 'I%' OR modifier_type = 'T' OR modifier_type = 'S' )); 

						DECLARE CONTINUE HANDLER FOR NOT FOUND
							SET noMoreModRows = 1;
						
						OPEN order_item_mod_groups;
						LOOP
							SET noMoreModRows = 0;
							FETCH order_item_mod_groups INTO xmodifier_group_id, xmodifier_group_price_override, xmodifier_group_price_max;
							IF noMoreModRows THEN
								LEAVE priceadjustblock;
							END IF;
							INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' we have fetched Price Overrides'), xmodifier_group_id, xmodifier_group_price_override, xmodifier_group_price_max,now());

							-- so now see what the total amount spent on the group is.  problem is that a comes with item wont be charged so it wont be figured in.  HOW TO SOLVE FOR THIS?????
							--  maybe in price calculations above we need to see if the modifier_item is part of item_group_map that has an override, if so, we do charge for the item above and let it work
							-- out in the logic here.
							SELECT SUM(mod_total_price) INTO xgroup_total_price_for_item FROM Order_Detail_Modifier WHERE modifier_group_id = xmodifier_group_id AND order_detail_id = xorder_detail_id;

							INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' we have summed price override'), xgroup_total_price_for_item, '', '',now());
							
							IF xmodifier_group_price_override > 0.00 THEN
								SET xadjustment_amount = xmodifier_group_price_override;
								IF xgroup_total_price_for_item < xmodifier_group_price_override THEN
									SET xadjustment_amount = xgroup_total_price_for_item;
								END IF;
								
								-- add row to order_detail_modifier table
								IF xadjustment_amount > 0.00 THEN
									INSERT INTO Order_Detail_Modifier (order_detail_id,modifier_item_id,modifier_group_id,mod_name,mod_print_name,modifier_type,hold_it,mod_quantity,mod_price,mod_total_price,created) 
										VAlUES (xorder_detail_id,0, xmodifier_group_id,'price adjustment','override','A','N',1,-xadjustment_amount,-xadjustment_amount,NOW());											
									-- adjust xitem_sub_total
									SET xcalced_item_sub_total = xcalced_item_sub_total - xadjustment_amount;

									-- have to adjust xgroup_total_price_for_item here so it works for the next section.  very low probability of this happening though
									SET xgroup_total_price_for_item = xgroup_total_price_for_item - xadjustment_amount;
								END IF;

							END IF;
							IF xmodifier_group_price_max IS NOT NULL AND xgroup_total_price_for_item > xmodifier_group_price_max THEN
								-- add row in modifier details table for the adjustment
								SET xadjustment_amount = xgroup_total_price_for_item - xmodifier_group_price_max;
								INSERT INTO Order_Detail_Modifier (order_detail_id,modifier_item_id,modifier_group_id,mod_name,mod_print_name,modifier_type,hold_it,mod_quantity,mod_price,mod_total_price,created) 
									VAlUES (xorder_detail_id,0, xmodifier_group_id,'price adjustment','group max','A','N',1,-xadjustment_amount,-xadjustment_amount,NOW());	 
								-- adjust xitem_sub_total
								SET xcalced_item_sub_total = xcalced_item_sub_total - xadjustment_amount;
							END IF;
						END LOOP; -- loop of mod groups in order
					END;  -- priceadjustblock

	paywithpointsblock:BEGIN
						IF xitem_points_used > 0 THEN
							INSERT INTO Order_Detail_Modifier (order_detail_id,modifier_item_id,modifier_group_id,mod_name,mod_print_name,modifier_type,hold_it,mod_quantity,mod_price,mod_total_price,created) 
									VAlUES (xorder_detail_id,0, 0,'price adjustment','points','P','N',xitem_points_used,-xitem_amount_off_from_points,-xitem_amount_off_from_points,NOW());	 						
							-- adjust xitem_sub_total
							SET xcalced_item_sub_total = xcalced_item_sub_total - xitem_amount_off_from_points;
						END IF;
					END;  -- paywithpointsblock

					-- xcalced_item_sub_total should be loaded up with the totals from the modifiers for this item by the time the code gets to here.
					
					--  here is where i do the tax for the item
					-- get tax rate for this item from the tax table 
					IF xitem_tax_group = 0 THEN
 						SET xitem_tax_rate = 0.00;
					ELSE					
						SELECT sum(rate) INTO xitem_tax_rate FROM `Tax` WHERE merchant_id = in_merchant_id AND tax_group = xitem_tax_group AND logical_delete = 'N';
						IF (xitem_tax_rate IS NULL AND xitem_tax_group > 1) THEN
							SELECT sum(rate) INTO xitem_tax_rate FROM `Tax` WHERE merchant_id = in_merchant_id AND tax_group = 1 AND logical_delete = 'N';
							INSERT INTO Errors VALUES (null,xraw_stamp,'EMAIL ERROR',
												CONCAT( 'NO TAX GROUP FOR THIS ITEM Defaulting to 1 group ','item_id: ', xitem_id,'  tax group: ',xitem_tax_group),
												CONCAT('size_price_id:', xsizeprice_id),now());
						END IF;                       
					END IF;	
					-- determine if there is a quantity modifier of more than 1 and adjust
					IF xitem_quantity > 1 THEN
						SET xcalced_item_sub_total = xitem_quantity * xcalced_item_sub_total;
						SET xitem_sub_total = xitem_quantity*xitem_price;
					END IF;

					-- now get the tax amount for this item and add it to the running total tax amit
					SET xtax_running_total_amt = xtax_running_total_amt + ((xitem_tax_rate/100) * xcalced_item_sub_total);

					UPDATE Order_Detail SET item_total_w_mods = xcalced_item_sub_total,item_tax = ((xitem_tax_rate/100) * xcalced_item_sub_total),item_total = xitem_sub_total,quantity = xitem_quantity WHERE order_detail_id = xorder_detail_id;
	
					IF logit Then
						INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' TAX for item calculated'),CONCAT('rate:', xitem_tax_rate),CONCAT('subtotal: ', xcalced_item_sub_total),CONCAT('item tax total:', (xitem_tax_rate/100) * xcalced_item_sub_total),now());                       
					END IF;

					-- now update the running total
					SET xcalced_order_sub_total = xcalced_order_sub_total + xcalced_item_sub_total;

				END; -- MODS SECTION

			END LOOP;

			CLOSE orderItems;
		END;

		-- adjust taxes if necessary because of promo
		IF xpromo_tax_amt > 0.00 THEN
			INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' ABOUT TO ADJUST TAX TOTAL FOR PROMO'),CONCAT('amt:  ', xpromo_tax_amt),CONCAT('order_id:  ', xorder_id),'',NOW());
		END IF;
		SET xtax_running_total_amt = xtax_running_total_amt - xpromo_tax_amt;

		-- write to the error log if the submitted sub total and calculated sub total do not match
		IF in_sub_total != xcalced_order_sub_total+xdelivery_cost THEN
			INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' ERROR! ORDER SUBTOTAL MIS-MATCH'),CONCAT('in:', in_sub_total), CONCAT('calced:', (xcalced_order_sub_total-xpromo_amt)),'',now());
		END IF;			

		INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' ORDER TOTALS   tax_total:', xtax_running_total_amt,'  calced_sub_total', xcalced_order_sub_total),CONCAT('transfee:', xtrans_fee_amt,'  tip: ', in_tip),CONCAT('promo: ', xpromo_amt),CONCAT('delivery:', xdelivery_cost),now());
		SET xcalced_order_total = xtax_running_total_amt +xcalced_order_sub_total+xtrans_fee_amt+in_tip-xpromo_amt+xdelivery_cost;

		-- get donation information 
		IF xcash != 'Y' AND xskin_donation_active = 'Y' THEN
			SELECT donation_type, donation_amt INTO xcustomer_donation_type, xcustomer_donation_amt FROM User_Skin_Donation WHERE user_id = in_user_id AND skin_id = in_skin_id AND donation_active = 'Y';
			INSERT INTO Errors values (null,CONCAT(xraw_stamp,' 1: CUSTOMER DONATION: ', xcustomer_donation_type),CONCAT('DONATION:', xcustomer_donation_amt),CONCAT(in_user_id,' ',in_skin_id),CONCAT('ORDER_ID: ', xorder_id),now());
			IF xcustomer_donation_type = 'R' THEN
				SET xcustomer_donation_amt = ((SELECT FLOOR(xcalced_order_total))+1)-xcalced_order_total;
				IF xcalced_order_total = 1.00 THEN
					SET xcustomer_donation_amt = 0.25;
				END IF;
				INSERT INTO Errors values (null,CONCAT(xraw_stamp,' 2: CUSTOMER DONATION: ', xcustomer_donation_type),CONCAT('DONATION:', xcustomer_donation_amt),'',CONCAT('ORDER_ID: ', xorder_id),now());			END IF;
		ELSE
				INSERT INTO Errors values (null,CONCAT(xraw_stamp,' SKIPPING DONATION BECAUSE OF CASH '),'','',CONCAT('ORDER_ID: ', xorder_id),now());		END IF;		

		SET xcalced_order_total = xcalced_order_total + xcustomer_donation_amt;

		IF logit THEN
			INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' CALCED ORDER TOTAL'),CONCAT('total:', xcalced_order_total),'','',now());
		END IF;

		UPDATE `Orders` SET `order_amt` = xcalced_order_sub_total,`total_tax_amt`= xtax_running_total_amt,`grand_total` = xcalced_order_total, `order_qty` = xnum_of_temp_order_items, `tip_amt` = in_tip, `promo_amt` = -xpromo_amt, `customer_donation_amt` = xcustomer_donation_amt, `delivery_amt` = xdelivery_cost, `cash` = xcash, `stamp` = xraw_stamp WHERE `order_id` = xorder_id;

		-- check for duplicate order.		
		IF xdbname = 'smaw_prod' AND in_user_id > 1000 AND linebuster = 'N' THEN
			IF logit THEN
				INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' checking for duplicate.  Merch_id: ', in_merchant_id,'    user_id:',in_user_id),CONCAT('grand total:', xcalced_order_total),CONCAT('qty:', xnum_of_temp_order_items),CONCAT('calced_order_total:', xcalced_order_total),now());                       			END IF;
			SET found = 0;
			SELECT COUNT(*) INTO found FROM `Orders` WHERE merchant_id = in_merchant_id AND user_id = in_user_id AND order_amt = xcalced_order_sub_total AND tip_amt = in_tip AND `order_qty` = xnum_of_temp_order_items AND status IN ('E','P','O') AND created > DATE_SUB(NOW(), INTERVAL 15 MINUTE);
			IF found > 1 AND xcalced_order_sub_total > 0.00 THEN
				-- UPDATE `Orders` SET `status` = 'D' WHERE `order_id` = xorder_id;
				SET out_return_id = 70;
				SET out_message = 'DUPLICATE_ORDER_ERROR';
				IF logit THEN
					INSERT INTO Errors values(null,CONCAT(xraw_stamp,'  ',out_message),CONCAT('user:',in_user_id),CONCAT('merch:',xmerchant_name),CONCAT('order:', xorder_id),now());
				END IF;
				LEAVE foundBlock;
			END IF;
		END IF;

		-- if we got here then we know all the inserts suceeded and we can commit all the changes
		COMMIT;  
		
		IF logit THEN
			INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' Returned id from creating the order is'),CONCAT('user:',in_user_id),concat('Returned order id:',out_return_id),'',now());
		END IF;

		-- now need to update the users balance.  need to create the messages too which is called from the update balance SP.
		-- had to do it this way since some messages are dependent on the users balance.  probably could have figured out a bettter way.  stupid.

		-- OK FOR LENNY'S right here i would call the create messages completely skipping the update balance SP
IF xorder_type = 'Z' THEN
	UPDATE `Orders` SET `status` = 'Z' WHERE `order_id` = out_return_id LIMIT 1;
	SET out_message = "SuccessfulGetCheckOut";
ELSE
		IF xorder_del_type = 'K' OR xcash = 'Y' OR linebuster = 'Y' THEN
			IF xcash = 'Y' THEN	
				INSERT INTO Errors values (null,CONCAT(xraw_stamp,' SKIPPING UPDATE BALANCE BECAUSE OF CASH OR JM OR LineBuster '),'','',CONCAT('ORDER_ID: ', xorder_id),now());
			END IF;
			IF linebuster = 'Y' THEN	
				INSERT INTO Errors values (null,CONCAT(xraw_stamp,' SETTING DEFAULT LEAD TO -1 TO INDICATE LINE BUSTER FOR MESSAGES '),'','',CONCAT('ORDER_ID: ', xorder_id),now());
				SET 	xmerch_default_lead = -1;
			END IF;

			CALL SMAWSP_CREATE_ORDER_MESSAGES(in_merchant_id,xorder_id,xserver_pickup_dt,xmerch_default_lead,in_user_id,0.00,@return);
			-- add pickup time to merchant same.  we'll serparate out on the sms_order page
			SET out_message = CONCAT(xmerchant_name,'#',xlocal_pickup_dt);
			UPDATE `Orders` SET `status` = 'O' WHERE `order_id` = xorder_id LIMIT 1;
		ELSE
			CALL SMAWSP_UPDATE_BALANCE(in_user_id,xbalance,xorder_id,in_merchant_id,xserver_pickup_dt, xcalced_order_total,xmerch_default_lead,xtest,logit,@result,@returnCode,@errorMessage);
			IF @result = 'FAILURE' THEN
				SET out_return_id = @returnCode;
				SET out_message = @errorMessage;
				IF logit THEN
					INSERT INTO Errors values(null,CONCAT(xraw_stamp,'  ',out_message),CONCAT('user:',in_user_id),CONCAT('merch:',xmerchant_name),'',now());
				END IF;
				LEAVE foundBlock;
			END IF;
	
			-- add pickup time to merchant same.  we'll serparate out on the sms_order page
			SET out_message = CONCAT(xmerchant_name,'#',xlocal_pickup_dt);		
			-- set the overlimit flag if the user is over the limit
			IF xbalance-xcalced_order_total < xlimit THEN
				SET out_message = CONCAT(out_message,'#runcard');
			ELSE
				-- ok so the order did not take the user over the limit so update the order status to Open from Pending
				UPDATE `Orders` SET `status` = 'O' WHERE `order_id` = xorder_id LIMIT 1;
			END IF;
		END IF; -- detemine if LENNY"S type
END IF;	
		IF logit THEN
			INSERT INTO Errors VALUES (null,CONCAT(xraw_stamp,' completion of found block in SMAW_CREATE_ORDER'),xorder_id,out_message,'',now());
		END IF;
	END;  -- end found block
  END; -- end main block
END