· 7 years ago · Jan 19, 2019, 04:52 PM
1-- MySQL Script generated by MySQL Workbench
2-- Sat Jan 19 19:45:29 2019
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
9
10-- -----------------------------------------------------
11-- Schema webim_site
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema webim_site
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `webim_site` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci ;
18USE `webim_site` ;
19
20-- -----------------------------------------------------
21-- Table `webim_site`.`prtnr_status`
22-- -----------------------------------------------------
23DROP TABLE IF EXISTS `webim_site`.`prtnr_status` ;
24
25CREATE TABLE IF NOT EXISTS `webim_site`.`prtnr_status` (
26 `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
27 `rewardpercent` DECIMAL(5,4) UNSIGNED NULL DEFAULT 0.0000,
28 `title` VARCHAR(45) NOT NULL DEFAULT '',
29 `sumrequired` DECIMAL(14,2) NOT NULL,
30 PRIMARY KEY (`id`),
31 UNIQUE INDEX `reward_min_UNIQUE` (`sumrequired` ASC) VISIBLE)
32ENGINE = InnoDB
33AUTO_INCREMENT = 4
34DEFAULT CHARACTER SET = utf8mb4
35COLLATE = utf8mb4_0900_ai_ci;
36
37
38-- -----------------------------------------------------
39-- Table `webim_site`.`prtnr_partner`
40-- -----------------------------------------------------
41DROP TABLE IF EXISTS `webim_site`.`prtnr_partner` ;
42
43CREATE TABLE IF NOT EXISTS `webim_site`.`prtnr_partner` (
44 `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
45 `statusid` INT(11) UNSIGNED NULL DEFAULT NULL,
46 `accountname` VARCHAR(255) NOT NULL,
47 PRIMARY KEY (`id`),
48 INDEX `partner_status_idx` (`statusid` ASC) VISIBLE,
49 UNIQUE INDEX `accountname_UNIQUE` (`accountname` ASC) VISIBLE,
50 CONSTRAINT `partners_account_id`
51 FOREIGN KEY (`accountname`)
52 REFERENCES `webim_site`.`account` (`accountname`)
53 ON DELETE CASCADE
54 ON UPDATE CASCADE,
55 CONSTRAINT `partners_status_id`
56 FOREIGN KEY (`statusid`)
57 REFERENCES `webim_site`.`prtnr_status` (`id`)
58 ON DELETE SET NULL
59 ON UPDATE CASCADE)
60ENGINE = InnoDB
61DEFAULT CHARACTER SET = utf8mb4
62COLLATE = utf8mb4_0900_ai_ci;
63
64
65-- -----------------------------------------------------
66-- Table `webim_site`.`prtnr_rewardwithdraw`
67-- -----------------------------------------------------
68DROP TABLE IF EXISTS `webim_site`.`prtnr_rewardwithdraw` ;
69
70CREATE TABLE IF NOT EXISTS `webim_site`.`prtnr_rewardwithdraw` (
71 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
72 `partnerid` INT UNSIGNED NOT NULL,
73 `amount` DECIMAL(13,4) NULL DEFAULT 0,
74 `status` ENUM('completed', 'peinding', 'cancelled') NULL DEFAULT NULL,
75 `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
76 `completed` TIMESTAMP NULL DEFAULT NULL,
77 PRIMARY KEY (`id`),
78 INDEX `partner_idx` (`partnerid` ASC) VISIBLE,
79 CONSTRAINT `withdraws_partner_id`
80 FOREIGN KEY (`partnerid`)
81 REFERENCES `webim_site`.`prtnr_partner` (`id`)
82 ON DELETE CASCADE
83 ON UPDATE CASCADE)
84ENGINE = InnoDB;
85
86
87-- -----------------------------------------------------
88-- Table `webim_site`.`prtnr_rewardincome`
89-- -----------------------------------------------------
90DROP TABLE IF EXISTS `webim_site`.`prtnr_rewardincome` ;
91
92CREATE TABLE IF NOT EXISTS `webim_site`.`prtnr_rewardincome` (
93 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
94 `partnerid` INT UNSIGNED NULL,
95 `amount` DECIMAL(16,4) NOT NULL DEFAULT 0,
96 `description` VARCHAR(45) NOT NULL DEFAULT '',
97 `created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
98 `invoiceid` INT(10) UNSIGNED NOT NULL,
99 PRIMARY KEY (`id`),
100 INDEX `partner_idx` (`partnerid` ASC) VISIBLE,
101 UNIQUE INDEX `invoiceid_UNIQUE` (`invoiceid` ASC) VISIBLE,
102 CONSTRAINT `historiy_insertions_partener_id`
103 FOREIGN KEY (`partnerid`)
104 REFERENCES `webim_site`.`prtnr_partner` (`id`)
105 ON DELETE CASCADE
106 ON UPDATE CASCADE,
107 CONSTRAINT `invoice_id`
108 FOREIGN KEY (`invoiceid`)
109 REFERENCES `webim_site`.`invoice` (`id`)
110 ON DELETE CASCADE
111 ON UPDATE CASCADE)
112ENGINE = InnoDB;
113
114USE `webim_site`;
115
116DELIMITER $$
117
118USE `webim_site`$$
119DROP TRIGGER IF EXISTS `webim_site`.`prtnr_status_BEFORE_INSERT` $$
120USE `webim_site`$$
121CREATE
122DEFINER=`root`@`localhost`
123TRIGGER `sys`.`prtnr_status_BEFORE_INSERT`
124BEFORE INSERT ON `sys`.`prtnr_status`
125FOR EACH ROW
126BEGIN
127 DECLARE error_state condition for sqlstate '45000';
128 if (new.reward_percent > 1 or new.reward_percent < 0) then
129 signal error_state
130 set message_text = 'Invalid value. (0 <= value <= 1)';
131 end if;
132END$$
133
134
135USE `webim_site`$$
136DROP TRIGGER IF EXISTS `webim_site`.`prtnr_status_BEFORE_UPDATE` $$
137USE `webim_site`$$
138CREATE
139DEFINER=`root`@`localhost`
140TRIGGER `sys`.`prtnr_status_BEFORE_UPDATE`
141BEFORE UPDATE ON `sys`.`prtnr_status`
142FOR EACH ROW
143BEGIN
144 declare error_state condition for sqlstate '45000';
145 if (new.reward_percent > 1 or new.reward_percent < 0) then
146 signal error_state
147 set message_text = 'Invalid value. (0 <= value <= 1)';
148 end if;
149END$$
150
151
152USE `webim_site`$$
153DROP TRIGGER IF EXISTS `webim_site`.`prtnr_partner_BEFORE_INSERT` $$
154USE `webim_site`$$
155CREATE
156DEFINER=`root`@`localhost`
157TRIGGER `sys`.`prtnr_partner_BEFORE_INSERT`
158BEFORE INSERT ON `sys`.`prtnr_partner`
159FOR EACH ROW
160BEGIN
161 DECLARE required_status_id INT;
162
163 SET required_status_id =
164 (SELECT id FROM prtnr_status
165 WHERE reward_min <= NEW.reward_all
166 ORDER BY reward_min DESC
167 LIMIT 1);
168
169 SET NEW.status_id = required_status_id;
170END$$
171
172
173USE `webim_site`$$
174DROP TRIGGER IF EXISTS `webim_site`.`prtnr_partner_BEFORE_UPDATE` $$
175USE `webim_site`$$
176CREATE
177DEFINER=`root`@`localhost`
178TRIGGER `sys`.`prtnr_partner_BEFORE_UPDATE`
179BEFORE UPDATE ON `sys`.`prtnr_partner`
180FOR EACH ROW
181BEGIN
182 DECLARE required_status_id INT unsigned;
183
184 IF NEW.reward_all > OLD.reward_all THEN
185 SET required_status_id =
186 (SELECT id FROM prtnr_status
187 WHERE reward_min <= NEW.reward_all
188 ORDER BY reward_min DESC
189 LIMIT 1);
190
191 IF required_status_id != NEW.status_id THEN
192 SET NEW.status_id = required_status_id;
193 END IF;
194 END IF;
195END$$
196
197
198USE `webim_site`$$
199DROP TRIGGER IF EXISTS `webim_site`.`sys_config_insert_set_user` $$
200USE `webim_site`$$
201CREATE
202DEFINER=`mysql.sys`@`localhost`
203TRIGGER `sys`.`sys_config_insert_set_user`
204BEFORE INSERT ON `sys`.`sys_config`
205FOR EACH ROW
206BEGIN IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN SET NEW.set_by = USER(); END IF; END$$
207
208
209USE `webim_site`$$
210DROP TRIGGER IF EXISTS `webim_site`.`sys_config_update_set_user` $$
211USE `webim_site`$$
212CREATE
213DEFINER=`mysql.sys`@`localhost`
214TRIGGER `sys`.`sys_config_update_set_user`
215BEFORE UPDATE ON `sys`.`sys_config`
216FOR EACH ROW
217BEGIN IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN SET NEW.set_by = USER(); END IF; END$$
218
219
220USE `webim_site`$$
221DROP TRIGGER IF EXISTS `webim_site`.`prtnr_rwrd_withdraw_AFTER_INSERT` $$
222USE `webim_site`$$
223CREATE
224DEFINER=`root`@`localhost`
225TRIGGER `sys`.`prtnr_rwrd_withdraw_AFTER_INSERT`
226AFTER INSERT ON `sys`.`prtnr_rwrd_withdraw`
227FOR EACH ROW
228BEGIN
229 IF NEW.status = 'completed' THEN
230 INSERT INTO prtnr_rwrd_history (partner_id, amount, description)
231 VALUES (NEW.partner_id, NEW.amount * -1, 'Withdrawed by partner (added by trigger)');
232 END IF;
233END$$
234
235
236USE `webim_site`$$
237DROP TRIGGER IF EXISTS `webim_site`.`prtnr_rwrd_withdraw_AFTER_UPDATE` $$
238USE `webim_site`$$
239CREATE
240DEFINER=`root`@`localhost`
241TRIGGER `sys`.`prtnr_rwrd_withdraw_AFTER_UPDATE`
242AFTER UPDATE ON `sys`.`prtnr_rwrd_withdraw`
243FOR EACH ROW
244BEGIN
245 IF NEW.status = 'completed' THEN
246 INSERT INTO prtnr_rwrd_history (partner_id, amount, description)
247 VALUES (NEW.partner_id, NEW.amount * -1, 'Withdrawed by partner (added by trigger)');
248 END IF;
249END$$
250
251
252USE `webim_site`$$
253DROP TRIGGER IF EXISTS `webim_site`.`prtnr_rwrd_history_AFTER_INSERT` $$
254USE `webim_site`$$
255CREATE
256DEFINER=`root`@`localhost`
257TRIGGER `sys`.`prtnr_rwrd_history_AFTER_INSERT`
258AFTER INSERT ON `sys`.`prtnr_rwrd_history`
259FOR EACH ROW
260BEGIN
261 IF NEW.amount > 0 THEN
262 UPDATE prtnr_partner
263 SET reward_all = reward_all + NEW.amount
264 WHERE id = NEW.partner_id;
265 END IF;
266END$$
267
268
269DELIMITER ;
270
271SET SQL_MODE=@OLD_SQL_MODE;
272SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
273SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;