· 7 years ago · Nov 19, 2018, 09:32 AM
1BEGIN
2 DECLARE done INT DEFAULT FALSE;
3 DECLARE `id_var` varchar(255);
4 DECLARE `cur1` CURSOR FOR
5 SELECT `id` FROM `clients`
6 WHERE `status` = 'Active';
7 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
8 DROP TABLE IF EXISTS `tblquota_nc`;
9 CREATE TABLE IF NOT EXISTS `tblquota_nc` (
10 `id` int(11) NOT NULL AUTO_INCREMENT,
11 `user_id` int(11) NOT NULL,
12 `email` varchar(255),
13 `pack_id` int(11) NOT NULL,
14 `pack_name` varchar(255) NOT NULL,
15 `quota` int(11) NULL,
16 PRIMARY KEY (`id`)
17);
18 OPEN cur1;
19 read_loop: LOOP
20 FETCH NEXT
21 FROM cur1
22 INTO id_var;
23 IF done THEN
24 LEAVE read_loop;
25 END IF;
26
27 SELECT clients.id as `User ID`, `email`, `packageid` as `Pack ID`, `name` as `Pack`, (CASE
28 WHEN `name` = "Basic" THEN '10'
29 WHEN `name` = "Silver" THEN '100'
30 WHEN `name` = "Gold" THEN '1000'
31 ELSE '10'
32 END) as Quota INTO @mid, @mail, @p_id, @packname, @quota
33 FROM `clients`
34 INNER JOIN `tblhosting` ON clients.id = tblhosting.userid
35 INNER JOIN `tblproducts` ON tblhosting.packageid = tblproducts.id
36 WHERE clients.status = 'Active'
37 AND tblhosting.domainstatus = 'Active'
38 AND clients.id = id_var;
39 IF (SELECT id FROM tblquota_nc WHERE user_id = @mid) THEN
40 BEGIN
41 END;
42 ELSE
43 BEGIN
44 if (@mid IS NOT NULL AND @p_id IS NOT NULL AND @packname IS NOT NULL) then
45 INSERT INTO tblquota_nc (user_id, email, pack_id, pack_name, quota) VALUES (@mid, @mail, @p_id, @packname, @quota);
46 end if;
47 END;
48 END IF;
49 END LOOP;
50 CLOSE cur1;
51END
52
53id | email | status
54----------------------------
551 | user1@mail.com | Active
562 | user2@mail.com | Inactive
573 | user3@mail.com | Active
58
59id | userid | packageid | domainstatus
60------------------------------------------------
611 | 1 | 2 | Active
622 | 2 | 3 | Active
633 | 3 | 1 | Active
64
65id | name
66-----------
671 | Basic
682 | Silver
693 | Gold
70
71id | user_id | email | pack_id | pack_name | quota
72-----------------------------------------------------------
731 | 1 | user1@mail.com | 2 | Silver | 100
742 | 2 | user2@mail.com | 3 | Gold | 1000
753 | 3 | user3@mail.com | 1 | Basic | 10