ZAQehXue

1DELETE FROM nd USING `notification_definitions` AS nd
2WHERE
3    `origin`     = 'table'       AND
4    `type`       = 'date'        AND
5    `table_name` = 'table_13' AND
6    NOT EXISTS (
7       SELECT NULL FROM $table WHERE `notification_definition_id` = nd.`id`
8    );
9
10UPDATE `notification_definitions` SET `is_active` = 'no' WHERE `table_name` = 'table_13';
11
12CALL `notifications.new_notification_definition`('table_13', ?, ?, 123, ?, ?);
13CALL `notifications.new_notification_definition`('table_13', ?, ?, 2, ?, ?);
14CALL `notifications.new_notification_definition`('table_13', ?, ?, 7, ?, ?);
15CALL `notifications.new_notification_definition`('table_13', ?, ?, 30, ?, ?);
16	
17DELIMITER ;;
18CREATE DEFINER=`root`@`localhost` PROCEDURE `notifications.new_notification_definition`(
19IN `in_table_name`          VARCHAR(100),
20IN `in_primary_field_label` VARCHAR(250),
21IN `in_field_label`         VARCHAR(250),
22IN `in_days`                INT(11) UNSIGNED,
23IN `in_color`               VARCHAR(20),
24IN `in_message`             VARCHAR(250)
25)
26BEGIN
27
28-- Select old definition
29SELECT `id` INTO @id FROM `notification_definitions`
30WHERE 
31    `origin`              = "table"                  AND
32    `type`                = "date"                   AND
33    `table_name`          = `in_table_name`          AND
34    `primary_field_label` = `in_primary_field_label` AND
35    `field_label`         = `in_field_label`         AND
36    `days`                = `in_days`
37LIMIT 1;
38
39IF @id IS NULL THEN
40INSERT `notification_definitions` SET
41    `origin`              = "table",
42    `type`                = "date",
43    `table_name`          = `in_table_name`,
44    `primary_field_label` = `in_primary_field_label`,
45    `field_label`         = `in_field_label`,
46    `days`                = `in_days`,
47    `color`               = `in_color`,
48    `message`             = `in_message`,
49    `created_at`          = NOW(),
50    `updated_at`          = NOW();
51ELSE 
52UPDATE `notification_definitions` SET
53    `color`      = `in_color`,
54    `message`    = `in_message`,
55    `is_active`  = 'yes',
56    `updated_at` = NOW()
57WHERE id = @id
58LIMIT 1;
59END IF;
60
61END ;;