redate_order.sql

DROP PROCEDURE IF EXISTS redate_order;
delimiter //
CREATE PROCEDURE redate_order (INOUT orderid INT, INOUT stamp VARCHAR(255))
proc:BEGIN
   START TRANSACTION;
   update wp_wpsg_order set cdate=stamp where id=orderid;
   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);
   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 Folgerechnung 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 Folgerechnung für Bestellung Nr. ",orderid));
   INSERT INTO wp_wpsg_orderlog (o_id,cdate,title) VALUES(orderid, NOW(), CONCAT("Folgerechnung 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 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)
   WHERE id=@neworder;
   SET orderid=@neworder;   
   COMMIT;
END
//
delimiter ;