Makefile

.PHONY: update-renew-order

all :
	@echo Please select a specific target

update-renew-order :
ifdef DB_PASSWORD
	mysql --user=$(DB_USER) --password=$(DB_PASSWORD)  $(DB_DATABASE) <renew_order.sql 
else 
	$(info Please source ../reports/.credentials to define DB_PASSWORD etc)
endif

redate_order.sql

DROP PROCEDURE IF EXISTS redate_order;
delimiter //
CREATE PROCEDURE redate_order (INOUT orderid INT, INOUT stamp VARCHAR(255))
proc:BEGIN
DECLARE count int DEFAULT -1;
   START TRANSACTION;
   update wp_wpsg_order set cdate=stamp where id=orderid;
   select ROW_COUNT() into count ;
   IF count = 0 THEN
     SET stamp=concat("order ID ",orderid, " nicht gefunden");
   ELSE 
     insert into wp_wpsg_orderlog set o_id=orderid,cdate=now(),title="dummy";
     SET @neworder=LAST_INSERT_ID();
     update wp_wpsg_orderlog set title=concat("Datum manuell geändert auf ",stamp) where id=@neworder;
     SET stamp=(select title from wp_wpsg_orderlog where id=@neworder);
   END IF;
   COMMIT;
END
//
delimiter ;

renew_order.sql

DROP PROCEDURE IF EXISTS renew_order;
delimiter //
CREATE PROCEDURE renew_order (INOUT orderid INT)
proc:BEGIN
   START TRANSACTION;
   DROP TEMPORARY TABLE IF EXISTS tmp_order;
   CREATE TEMPORARY TABLE tmp_order SELECT * FROM wp_wpsg_order WHERE ID=orderid AND STATUS=110;
   -- Alte Bestellung kopieren
   UPDATE tmp_order SET status=0, id=NULL, 
		  admincomment=CONCAT('Automatisch erzeugte Folgebestellung für Bestellung Nr. ',orderid,'\r\n', admincomment), 
          payed_date=NULL WHERE ID=orderid;
   SELECT COUNT(*) into @countcopied from tmp_order;
   IF @countcopied = 0 THEN
	SET orderid=-3;
	LEAVE proc;
   END IF;
   INSERT INTO wp_wpsg_order SELECT * from tmp_order;
   SET @neworder=LAST_INSERT_ID();
   IF @neworder = 0 THEN
      SET orderid=-1;
      LEAVE proc;
   END IF;
   UPDATE wp_wpsg_order SET onr=@neworder WHERE ID=@neworder;
   DROP TEMPORARY TABLE IF EXISTS tmp_order_product;
   CREATE TEMPORARY TABLE tmp_order_product SELECT * from wp_wpsg_order_products WHERE o_id=orderid ORDER BY ID desc LIMIT 1;
   UPDATE tmp_order_product set id=NULL, o_id=@neworder 
          WHERE o_id=orderid;
   INSERT INTO wp_wpsg_order_products SELECT * from tmp_order_product;
   SET @neworderproduct=LAST_INSERT_ID();
   INSERT INTO wp_wpsg_orderlog (o_id,cdate,title) VALUES(@neworder, NOW(), CONCAT("Automatisch erzeugte Folgebestellung für Bestellung Nr. ",orderid));
   INSERT INTO wp_wpsg_orderlog (o_id,cdate,title) VALUES(orderid, NOW(), CONCAT("Folgebestellung für diese Bestellung automatisch erzeugt - Nr. ",@neworder));
   DROP TEMPORARY TABLE IF EXISTS tmp_order_product;
   DROP TEMPORARY TABLE IF EXISTS tmp_order;
   -- Aus "E50" mach "S50" usw.
   UPDATE wp_wpsg_order_products SET 
          p_id=(SELECT id FROM wp_wpsg_products WHERE anr=REPLACE((SELECT anr from wp_wpsg_products WHERE id=p_id ORDER BY cdate DESC LIMIT 1) ,"E","S") ORDER BY cdate desc LIMIT 1)
   WHERE id=@neworderproduct;
   IF (SELECT p_id FROM wp_wpsg_order_products where id=@neworderproduct) = 0 THEN
	SET orderid=-2;
        ROLLBACK;
        LEAVE proc;
   END IF; 
   -- Preis kopieren, falls er sich geändert hat
   UPDATE wp_wpsg_order_products SET 
          price=(SELECT preis FROM wp_wpsg_products WHERE id=p_id),
          productkey=p_id
   WHERE id=@neworderproduct;
   -- Preis kopieren, Datum in die Zukunft verlegen, Cache ("custom_data") löschen,  
   UPDATE wp_wpsg_order SET
          price_gesamt=(SELECT menge FROM wp_wpsg_order_products WHERE id=@neworderproduct)*(SELECT price FROM wp_wpsg_order_products WHERE id=@neworderproduct),
          cdate=ADDDATE(cdate,INTERVAL (select (floor((select wpsg_mod_abo_durration from wp_wpsg_products where ID=(select p_id from wp_wpsg_order_products WHERE o_id=@neworder)) / 30))) MONTH),
	  custom_data=''
   WHERE id=@neworder;
   SET orderid=@neworder;   
   COMMIT;
END
//
delimiter ;

update_prices.sql

DROP PROCEDURE IF EXISTS update_prices;
delimiter //
CREATE PROCEDURE update_prices (INOUT orderid TEXT)
proc:BEGIN
   START TRANSACTION;
   drop temporary table if exists price_update;
   create temporary table price_update as (select o_id from wp_wpsg_order_products where o_id>orderid and p_id=45);
   update wp_wpsg_order_products set p_id=1, price=816.00 where p_id=45 and o_id in (select o_id from price_update);
   SET orderid=(select group_concat(",",o_id) as a from price_update);
   COMMIT;
END
//
delimiter ;