iejtFahZ

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 id_ bigint(20);
84    declare companyId bigint(20);
85    declare title varchar(255);
86    declare orderNumber int(11);
87
88    declare cursorIsDone tinyint default false;
89    declare orderStatusCursor cursor for
90        select os.id, os.company_id, os.title, os.order_number
91        from order_status_test1 os
92        order by os.company_id, os.order_number;
93    # Fires when all data was fetched
94    declare continue handler for not found set cursorIsDone = true;
95
96    # Loop through all order_status rows ordered by company_id
97    open orderStatusCursor;
98    loopLink:
99        loop
100            fetch orderStatusCursor into id_, companyId, title, orderNumber;
101            if cursorIsDone then
102                leave loopLink;
103            else
104                # If all previous company rows are processed
105                if ((companyId != lastCompanyId) and (lastCompanyId != -1)) then
106                    # Do create/update for the last company
107                    call createOrUpdateOrderStatus(
108                            lastCompanyId,
109                            lastOrderNumber,
110                            isCanceledExists,
111                            isReceivedExists
112                    );
113                    # Reset flags
114                    set isCanceledExists = false;
115                    set isReceivedExists = false;
116                # Do order number correction only within the same company_id
117                else
118                    if ((orderNumber - lastOrderNumber) != 1) then
119                        set @temp := orderNumber;
120                        set orderNumber = lastOrderNumber + 1;
121                        update order_status_test1 os
122                        set os.order_number = orderNumber
123                        where os.id = id_;
124                        call debug_msg((
125                            select concat_ws('',
126                                'company_id ', companyId,
127                                ' order_status id ', id_,
128                                ' updated order_number from ', @temp, ' to ', orderNumber)
129                        ));
130                    end if;
131                end if;
132
133                # Remember last cursor row data
134                set lastCompanyId = companyId;
135                set lastOrderNumber = orderNumber;
136                if (title = 'Отменен') then
137                    set isCanceledExists = true;
138                end if;
139                if (title = 'Получен') then
140                    set isReceivedExists = true;
141                end if;
142            end if;
143        end loop;
144    # Do create/update for the last company
145    call createOrUpdateOrderStatus(
146            lastCompanyId,
147            lastOrderNumber,
148            isCanceledExists,
149            isReceivedExists
150    );
151    close orderStatusCursor;
152end$$
153
154delimiter ;
155call orderStatusFix();
156
157# Cleanup
158drop procedure orderStatusFix;
159drop procedure createOrUpdateOrderStatus;
160drop procedure debug_msg;
161# Don't forget to drop debug_logs table after checking it:
162# drop table if exists `debug_logs`;