ZcQyAxAw

1DELIMITER $$
2DROP PROCEDURE IF EXISTS `debug_msg`$$
3CREATE PROCEDURE debug_msg(message VARCHAR(255))
4BEGIN
5    create table if not exists `debug_logs` (
6        created_at datetime DEFAULT CURRENT_TIMESTAMP,
7        msg text
8    );
9    insert into `debug_logs` (msg) value (message);
10END $$
11
12delimiter $$
13drop procedure if exists createOrUpdateOrderStatus$$
14create procedure createOrUpdateOrderStatus(
15    in lastCompanyId bigint(20),
16    in lastOrderNumber int(11),
17    in isCanceledExists tinyint,
18    in isReceivedExists tinyint
19)
20begin
21    # Common data
22    set @update_id := 0;
23
24    # Update canceled status
25    if (isCanceledExists) then
26        update order_status_test1 os
27        set os.title = 'Отменён', os.general_status = 'FAILED', os.removable = false, id = (select @update_id := id)
28        where os.company_id = lastCompanyId and os.title = 'Отменен';
29        call debug_msg((
30            select concat_ws('',
31                'company_id ', lastCompanyId,
32                ' order_status id ', @update_id,
33                ' title updated to Отменён')
34        ));
35    # Create canceled status
36    else
37        insert into order_status_test1 (color, order_number, title, company_id, general_status, removable)
38            value ('#f6686b', lastOrderNumber + 1, 'Отменён', lastCompanyId, 'FAILED', false);
39        call debug_msg((
40            select concat_ws('',
41                'company_id ', lastCompanyId,
42                ' order_status id ', LAST_INSERT_ID(),
43                ' inserted a new status with title Отменён')
44        ));
45    end if;
46
47    # Update received status
48    if (isReceivedExists) then
49        update order_status_test1 os
50        set os.title = 'Успешный', os.general_status = 'SUCCESSFUL', os.removable = false,
51            id = (select @update_id := id)
52        where os.company_id = lastCompanyId and os.title = 'Получен';
53        call debug_msg((
54            select concat_ws('',
55                'company_id ', lastCompanyId,
56                ' order_status id ', @update_id,
57                ' title updated to Успешный')
58        ));
59    # Create received status
60    else
61        insert into order_status_test1 (color, order_number, title, company_id, general_status, removable)
62            value ('#eaa43eeb', lastOrderNumber + 1, 'Успешный', lastCompanyId, 'SUCCESSFUL', false);
63        call debug_msg((
64            select concat_ws('',
65                'company_id ', lastCompanyId,
66                ' order_status id ', LAST_INSERT_ID(),
67                ' inserted a new status with title Успешный')
68        ));
69    end if;
70end$$
71
72delimiter $$
73drop procedure if exists orderStatusFix$$
74create procedure orderStatusFix()
75begin
76    # Loop data
77    declare lastCompanyId bigint(20) default -1;
78    declare lastOrderNumber int(11) default 0;
79    declare isCanceledExists tinyint default false;
80    declare isReceivedExists tinyint default false;
81
82    # Cursor data
83    declare companyId bigint(20);
84    declare title varchar(255);
85    declare orderNumber int(11);
86
87    declare cursorIsDone tinyint default false;
88    declare orderStatusCursor cursor for
89        select os.company_id, os.title, os.order_number
90        from order_status_test1 os
91        order by os.company_id, os.id;
92    # Fires when all data was fetched
93    declare continue handler for not found set cursorIsDone = true;
94
95    # Loop through all order_status rows ordered by company_id
96    open orderStatusCursor;
97    loopLink:
98        loop
99            fetch orderStatusCursor into companyId, title, orderNumber;
100            if cursorIsDone then
101                leave loopLink;
102            else
103                # If all previous company rows are processed
104                if ((companyId != lastCompanyId) and (lastCompanyId != -1)) then
105                    # Do create/update for the last company
106                    call createOrUpdateOrderStatus(
107                            lastCompanyId,
108                            lastOrderNumber,
109                            isCanceledExists,
110                            isReceivedExists
111                    );
112                    # Reset flags
113                    set isCanceledExists = false;
114                    set isReceivedExists = false;
115                end if;
116
117                # Remember last cursor row data
118                set lastCompanyId = companyId;
119                set lastOrderNumber = orderNumber;
120                if (title = 'Отменен') then
121                    set isCanceledExists = true;
122                end if;
123                if (title = 'Получен') then
124                    set isReceivedExists = true;
125                end if;
126            end if;
127        end loop;
128    # Do create/update for the last company
129    call createOrUpdateOrderStatus(
130            lastCompanyId,
131            lastOrderNumber,
132            isCanceledExists,
133            isReceivedExists
134    );
135    close orderStatusCursor;
136end$$
137
138delimiter ;
139call orderStatusFix();
140
141# Cleanup
142drop procedure orderStatusFix;
143drop procedure createOrUpdateOrderStatus;
144drop procedure debug_msg;
145# Don't forget to drop debug_logs table after checking it:
146# drop table if exists `debug_logs`;