GitLab

• In den E-Mails die KontoNr
• Einen Automat der täglich einmal alle NEU Stati auf ABH setzt

INSERT INTO `azeero`.`translation` (`keyword`, `createdAt`) VALUES ('AutomaticStatusNEUABHMail', NOW());
SELECT seq_id FROM translation WHERE keyword = 'AutomaticStatusNEUABHMail';

INSERT INTO `azeero`.`translation_lang` (`ftranslation_seq_id`, `flang_seq_id`, `langword`, `createdAt`) VALUES ('755', '1', 'SammelbestellunglieferungsMail', '2018-11-12 09:16:00');

INSERT INTO `azeero`.`translation` (`keyword`, `createdAt`) VALUES ('This Action sends an email to all persons who ordered something collective and it is now sent', '2018-11-12 09:16:00');
INSERT INTO `azeero`.`translation_lang` (`ftranslation_seq_id`, `flang_seq_id`, `langword`, `createdAt`) VALUES ('756', '1', 'Diese Aktion schickt eine Mail an alle, die etwas aus einer Sammelbestellung bestellt haben und diese auch geliefert wurde.', '2018-11-12 09:16:00');

INSERT INTO `azeero`.`action_calender_actiontype` (`name_ftranslation_seq_id`, `info_ftranslation_seq_id`, `value`) VALUES ('755', '756', 'AutomaticStatusNEUABHMail');

INSERT INTO `azeero`.`action_calender_actionparams` (`type`, `name_ftranslation_seq_id`, `attribute`, `faction_calender_actiontype`) VALUES ('collective_distribution', '753', 'key', '2');

DELIMITER $$
DROP PROCEDURE IF EXISTS ``AutomaticStatusNEUABHMail`` $$
CREATE PROCEDURE ``AutomaticStatusNEUABHMail``(json MEDIUMTEXT,OUT json_return MEDIUMTEXT)
ThisSP:BEGIN
DECLARE cursor_finished INTEGER DEFAULT 0;
DECLARE opd_seq_id INTEGER DEFAULT 0;
DECLARE fcd_seq_id INTEGER DEFAULT 0;
DECLARE last_fcd_seq_id INTEGER DEFAULT 0;
DECLARE email_cursor CURSOR FOR
 SELECT seq_id,fcollective_distribution_seq_id
 FROM order_product_details
 WHERE fcollective_distribution_seq_id @cd_seq_id 
 AND sum_order_status_kz = 'NEU'
 ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET cursor_finished = 1;
DECLARE exit handler for sqlexception, sqlwarning
BEGIN
 GET DIAGNOSTICS CONDITION 1 @error = MESSAGE_TEXT;
 SET json_return = CONCAT('{"error":"',IFNULL(@error,'unknown exit'),'"}');
 ROLLBACK;
END;
SET @acid = (SELECT CONVERT(JSON_EXTRACT(json, "$.acid"),SIGNED));
SET @cd_seq_id = (SELECT CONVERT(JSON_EXTRACT(json, "$.key"),SIGNED));
SET @appid = (SELECT JSON_UNQUOTE(JSON_EXTRACT(json, "$.appid")));
#
IF (@acid IS NULL) THEN
 SET json_return = CONCAT('{"error":"acid not set!"}');
 LEAVE ThisSP;
END IF;
IF (@cd_seq_id IS NULL) THEN
 SET json_return = CONCAT('{"acid":',@acid,',"error":"key not set!"}');
 LEAVE ThisSP;
END IF;
#
START TRANSACTION;
# Für alle Bestellungen mit NEU im Verteilpunkt sende an Shopbesitzer E-Mail
OPEN email_cursor;
get_email: LOOP
 FETCH email_cursor INTO opd_seq_id,fcd_seq_id;
 IF cursor_finished = 1 THEN
  LEAVE get_email;
 END IF;
# Folgendes aus OrderWish, offen alle Parameter korrekt, default-payment? zahlarten wo?
   # BES als Zwischenstatus trotzdem setzen damit Trigger dahinter dies mitbekommen
   UPDATE order_products SET order_status_kz = 'BES' WHERE order_status_kz != 'ABH' AND order_status_kz != 'BES' AND forder_product_details = opd_seq_id;
   UPDATE order_product_details SET updatedAt = SYSDATE(),sum_order_status_kz = 'INA' WHERE sum_order_status_kz != 'ABH' AND sum_order_status_kz != 'BES' AND seq_id = opd_seq_id;
   # order_product_details_u_tr setzt receipt_repeat
   UPDATE order_products SET order_status_kz = 'ABH' WHERE order_status_kz != 'ABH' AND forder_product_details = opd_seq_id;
   UPDATE order_product_details SET updatedAt = SYSDATE(),sum_order_status_kz = 'ABH' WHERE sum_order_status_kz != 'ABH' AND seq_id = opd_seq_id;
   INSERT INTO order_payment(fperson_contact_seq_id,fopd_seq_id,payment_id,status_mark,price,createdAt)
   SELECT @userid,order_product_details_seq_id,@paymentId,'UMS',sum(op.price*op.quantity)+IFNULL(opd.distribution_price,0),SYSDATE()
   FROM order_products op,order_product_details opd
   WHERE opd.seq_id = order_product_details_seq_id
   AND  op.forder_product_details = opd.seq_id
   AND NOT EXISTS (SELECT 1 FROM order_payment WHERE fopd_seq_id = opd.seq_id)
   GROUP BY opd.seq_id
   ;
   # An Käufer
   INSERT INTO communication(type,fperson_receiver_seq_id,template_name,template_params,code_pos,app_id)
   SELECT 'EMAIL',fperson_customer_seq_id,'changeInfoMailBuyer',concat('{"order_seq_id":',@orderid,'}'),'orderWish',@appid
   FROM order_product_details
   WHERE seq_id = order_product_details_seq_id
   ;
   #An shop Besitzer wenn keine Sammelbestellung über die Zeit
   INSERT INTO communication(type,fperson_receiver_seq_id,template_name,template_params,code_pos,app_id)
   SELECT 'EMAIL',shopuserid,'changeInfoMailSeller',concat('{"opd_seq_id":',order_product_details_seq_id,'}'),'orderWish',@appid
   FROM collective_distribution
   WHERE seq_id = @distributionId
   ;

END LOOP get_email;
CLOSE email_cursor;
COMMIT;
SET json_return = CONCAT('{"acid":',@acid,',"cd_seq_id":',@cd_seq_id,',"status_kz":"OK"','}');
END; $$
DELIMITER ;