TYPE=TRIGGERS
triggers='CREATE DEFINER=`root`@`localhost` trigger `void_detect` BEFORE UPDATE on `tillinvoices` \nfor each row BEGIN\n	DECLARE new_void_flag INTEGER;\n	DECLARE old_void_flag INTEGER;\n	DECLARE old_voided_db INTEGER; #temp var\n	DECLARE debtor_id INTEGER;\n	DECLARE account_amount DOUBLE;\n	DECLARE debtor_date DATE;\n	\n	DECLARE flag_isrefund INTEGER;\n	DECLARE flag_isrefund_db INTEGER;\n	DECLARE done INT DEFAULT 0;\n	DECLARE a INT;\n	declare b DOUBLE;\n	DECLARE cur1 CURSOR FOR SELECT tods.plu,tods.qty \n			     FROM tillordersdetails AS tods, tillorders AS tod\n			     where\n			     tods.orders_id = tod.id\n			     and tod.tableops_id = NEW.tableops_id\n			     and tods.split = new.split;\n	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;\n	\n	SET flag_isrefund = 0;\n	SELECT isrefund INTO flag_isrefund_db from tillinvoices\n	where id = NEW.id;\n	\n	IF flag_isrefund_db > 0 THEN\n	SET flag_isrefund = flag_isrefund_db;\n	END IF;\n	#call debugme("flag_isrefund",flag_isrefund);\n	#call debugme("flag_isrefund_db",flag_isrefund_db);\n	#retreive old and new void stage\n	SET old_void_flag = 0;\n	SET new_void_flag = 0;\n	\n	#deal with nulls\n	IF NEW.voided > 0 THEN\n	SET new_void_flag = NEW.VOIDED;\n	END IF;\n	\n	SELECT voided into old_voided_db\n	from tillinvoices\n	where id = new.id;\n	\n	#deal with nulls\n	IF old_voided_db >0 THEN\n	SET old_void_flag = old_voided_db;\n	END IF;\n	#retreived\n	\n	#call debugme("voiding",CONCAT("Old Void Flag: ", old_void_flag, "NEW VOID FLAG: ", new_void_flag));\n	#check for new void\n	IF old_void_flag = 0 AND (new_void_flag = 1 OR new_void_flag = 2) THEN\n		#deal with stock\n		IF new_void_flag = 1 THEN\n			OPEN cur1;\n			#deal with the stock on hand column\n			IF flag_isrefund > 0 THEN\n				REPEAT\n				FETCH cur1 INTO a, b;\n				IF NOT done THEN\n					update menu set stockonhand = stockonhand + b where id = a;\n				END IF;\n				UNTIL done END REPEAT;\n			ELSE\n				REPEAT\n				FETCH cur1 INTO a, b;\n				IF NOT done THEN\n					update menu set stockonhand = stockonhand + b where id = a;\n				END IF;\n				UNTIL done END REPEAT;\n			END IF;\n			CLOSE cur1;\n			#stock on hand delt\n		END IF;\n		#delt with stock\n		#deal with accounts\n		IF NEW.ACCOUNT != 0 THEN\n			\n			SET account_amount = abs(NEW.ACCOUNT);\n			SELECT date INTO debtor_date \n			from dcash where f_status < 100\n			order by date desc limit 1;\n			\n			IF NEW.ACCOUNT > 0 THEN\n				\n				INSERT INTO accounts \n				(account,entityid,credit,description,document,stamp,date)\n				values\n				(1, NEW.client_id,account_amount,"VOID",CONCAT(\'POS DOC# \', NEW.ID),current_timestamp,debtor_date);\n			ELSE\n				INSERT INTO accounts \n				(account,entityid,debit,description,document,stamp,date)\n				values\n				(1, NEW.client_id,account_amount,"VOID",CONCAT(\'POS DOC# \', NEW.ID),current_timestamp,debtor_date);\n			END IF;\n		END IF; #deal with account\n	ELSE \n	  IF old_void_flag = 1 AND (new_void_flag = 0) THEN\n	  # undo void\n		#deal with stock\n		IF new_void_flag = 0 THEN\n			OPEN cur1;\n			#deal with the stock on hand column\n			IF flag_isrefund > 0 THEN\n				REPEAT\n				FETCH cur1 INTO a, b;\n				IF NOT done THEN\n					update menu set stockonhand = stockonhand + b*(-1) where id = a;\n				END IF;\n				UNTIL done END REPEAT;\n			ELSE\n				REPEAT\n				FETCH cur1 INTO a, b;\n				IF NOT done THEN\n					update menu set stockonhand = stockonhand - b where id = a;\n				END IF;\n				UNTIL done END REPEAT;\n			END IF;\n			CLOSE cur1;\n			#stock on hand delt\n		END IF;\n		#delt with stock\n		#deal with accounts\n		IF NEW.ACCOUNT != 0 THEN\n			\n			SET account_amount = abs(NEW.ACCOUNT);\n			SELECT date INTO debtor_date \n			from dcash where f_status < 100\n			order by date desc limit 1;\n			\n			IF NEW.ACCOUNT > 0 THEN\n				\n				INSERT INTO accounts \n				(account,entityid,debit,description,document,stamp,date)\n				values\n				(1, NEW.client_id,account_amount,"UNDO VOID",CONCAT(\'POS DOC# \', NEW.ID),current_timestamp,debtor_date);\n			ELSE\n				INSERT INTO accounts \n				(account,entityid,credit,description,document,stamp,date)\n				values\n				(1, NEW.client_id,account_amount,"UNDO VOID",CONCAT(\'POS DOC# \', NEW.ID),current_timestamp,debtor_date);\n			END IF;\n		END IF; #deal with account\n	    END IF; # undo void\n	END IF;\n	#check for undo void\nEND'
sql_modes=0
definers='root@localhost'
client_cs_names='utf8'
connection_cl_names='utf8_general_ci'
db_cl_names='latin1_swedish_ci'