9hEs9Mir

1
2 == 20200421105639 Refs7473: migrating
3START TRANSACTION
4
5      ALTER TABLE merch ADD COLUMN tax_mode tinyint(1) NULL AFTER tax;
6
7      UPDATE merch
8        SET tax_mode = CASE
9                         WHEN tax IN ('NoTax','NDS0') THEN 0
10                         WHEN tax IN ('NDS18','NDS18/118') THEN 1
11                         WHEN tax IN ('NDS10','NDS10/110') THEN 2
12                       END;
13
14      ALTER TABLE merch MODIFY tax_mode tinyint(1) NOT NULL, DROP COLUMN tax;
15
16      ALTER TABLE merch_sell ADD COLUMN tax_mode tinyint(1) NULL AFTER tax;
17
18      UPDATE merch_sell
19        SET tax_mode = CASE
20                         WHEN tax IN ('NoTax','NDS0') THEN 0
21                         WHEN tax IN ('NDS18','NDS18/118') THEN 1
22                         WHEN tax IN ('NDS10','NDS10/110') THEN 2
23                       END;
24
25      ALTER TABLE merch_sell MODIFY tax_mode tinyint(1) NOT NULL, DROP COLUMN tax;
26
27DROP FUNCTION IF EXISTS iframe_calc_nds
28CREATE
29  DEFINER = definer@localhost
30  FUNCTION iframe_calc_nds(price INT, tax_mode TINYINT(1)) RETURNS INT
31  COMMENT '0 - Без НДС, 1 - НДС 18%(20%), 2 - НДС 10%'
32BEGIN
33  DECLARE coef INT(11);
34  CASE
35    WHEN tax_mode = 2 THEN SET coef = 10;
36    WHEN tax_mode = 1 THEN SET coef = 20;
37    ELSE SET coef = 0;
38    END CASE;
39  RETURN price * coef / (100 + coef);
40END;
41
42DROP FUNCTION IF EXISTS iframe_order_create
43CREATE DEFINER = definer@localhost PROCEDURE iframe_order_create(
44  IN v_sid varchar(26),
45  IN v_iframe_nid int(10) UNSIGNED,
46  IN v_web_agent_uid int(10) UNSIGNED,
47  IN v_prome_code_id int(10) UNSIGNED,
48  IN v_ld_id int(10) UNSIGNED,
49  IN v_delivery enum('box_office','e_ticket','courier','direct_sale'),
50  IN v_payment varchar(32),
51  IN v_fio varchar(255),
52  IN v_email varchar(255),
53  IN v_phone varchar(255),
54  IN v_ip char(15),
55  IN v_utm_campaign varchar(255),
56  IN v_utm_source varchar(255),
57  IN v_cart LONGTEXT
58)
59  SQL SECURITY DEFINER
60  NOT DETERMINISTIC
61  CONTAINS SQL
62BEGIN
63  DECLARE v_seats_count INT(10) UNSIGNED DEFAULT NULL;
64  DECLARE v_cart_size INT(10) UNSIGNED DEFAULT NULL;
65
66  DECLARE v_quota_id INT(10) UNSIGNED DEFAULT NULL;
67  DECLARE v_reserve_chain_id INT(10) UNSIGNED DEFAULT NULL;
68  DECLARE v_ticket_num_pad_amount INT(10) DEFAULT NULL;
69  DECLARE v_order_nid INT(10) UNSIGNED DEFAULT NULL;
70  DECLARE v_order_num VARCHAR(16) DEFAULT NULL;
71
72  DECLARE v_sql_state CHAR(5) DEFAULT '00000';
73  DECLARE v_sql_error_message TEXT DEFAULT NULL;
74
75  DECLARE v_now_timestamp INT(10) DEFAULT NULL;
76  DECLARE v_now_datetime DATETIME DEFAULT NULL;
77
78  DECLARE v_sc INT(10) DEFAULT NULL;
79  DECLARE v_rc INT(10) DEFAULT NULL;
80
81  DECLARE v_error_seance_seat_id INT(10) DEFAULT NULL;
82  DECLARE v_err_msg TEXT DEFAULT NULL;
83
84  DECLARE v_seance_max_booking INT(10) DEFAULT NULL;
85  DECLARE v_delivery_max_booking INT(10) DEFAULT NULL;
86  DECLARE v_deadline_datetime DATETIME DEFAULT NULL;
87
88  DECLARE v_iframe_fin_rules VARCHAR(8) DEFAULT NULL;
89  DECLARE v_iframe_fin_value INT(10) DEFAULT NULL;
90  DECLARE v_exchange_status TINYINT(1) DEFAULT NULL;
91
92  DECLARE v_pt_minimum_service_percent INT(10) DEFAULT NULL;
93  DECLARE v_ticket_fee_tax_mode TINYINT(1) DEFAULT NULL;
94
95  DECLARE v_promocode_max_usage INT(10) DEFAULT NULL;
96  DECLARE v_promocode_current_usage INT(10) DEFAULT NULL;
97
98  DECLARE v_payee_uid INT(10) DEFAULT NULL;
99
100  DECLARE v_uid INT(10) DEFAULT NULL;
101  DECLARE v_login VARCHAR(255) DEFAULT NULL;
102  DECLARE v_password TEXT DEFAULT NULL;
103  DECLARE v_shop_id VARCHAR(255) DEFAULT NULL;
104  DECLARE v_ofd TINYINT(1) DEFAULT NULL;
105  DECLARE v_apple_pay_merchant_id VARCHAR(80) DEFAULT NULL;
106  DECLARE v_apple_pay_merchant_name VARCHAR(80) DEFAULT NULL;
107  DECLARE v_merchant_name VARCHAR(80) DEFAULT NULL;
108
109
110  DECLARE EXIT HANDLER FOR SQLEXCEPTION
111  BEGIN
112    GET DIAGNOSTICS CONDITION 1
113      v_sql_state = RETURNED_SQLSTATE,
114      v_sql_error_message = MESSAGE_TEXT;
115
116    ROLLBACK;
117
118    IF v_sql_state = '45001' THEN
119      SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'Empty order (no seats)';
120    ELSEIF v_sql_state = '45002' THEN
121      SIGNAL SQLSTATE '45002' SET MESSAGE_TEXT = 'Bad promocode (not found/not started/expired)';
122    ELSEIF v_sql_state = '45003' THEN
123      SIGNAL SQLSTATE '45003' SET MESSAGE_TEXT = 'Bad promocode (already used)';
124    ELSEIF v_sql_state = '45004' THEN
125      SIGNAL SQLSTATE '45004' SET MESSAGE_TEXT = 'Race conditions detected (some seats are disappeared)';
126    ELSEIF v_sql_state = '45005' THEN
127      SIGNAL SQLSTATE '45005' SET MESSAGE_TEXT = v_sql_error_message;
128    ELSEIF v_sql_state = '45006' THEN
129      SIGNAL SQLSTATE '45006' SET MESSAGE_TEXT = v_sql_error_message;
130    ELSEIF v_sql_state = '45007' THEN
131      SIGNAL SQLSTATE '45007' SET MESSAGE_TEXT = v_sql_error_message;
132    ELSEIF v_sql_state = '45008' THEN
133      SIGNAL SQLSTATE '45008' SET MESSAGE_TEXT = 'Specified cart does not correlate to the specified sid';
134    ELSE
135      SIGNAL SQLSTATE '45009' SET MESSAGE_TEXT = v_sql_error_message;
136    END IF;
137  END;
138
139  DROP TEMPORARY TABLE IF EXISTS tmp_cart;
140
141  CREATE TEMPORARY TABLE tmp_cart (
142    item varchar(50)
143  ) ENGINE = MEMORY;
144
145  CALL sp_split(v_cart, '#', 'tmp_cart');
146
147  SELECT COUNT(*) INTO v_cart_size
148  FROM tmp_cart;
149
150  SET v_now_timestamp = UNIX_TIMESTAMP();
151  SET v_now_datetime = FROM_UNIXTIME(v_now_timestamp);
152
153  DROP TEMPORARY TABLE IF EXISTS tmp_seats;
154
155  CREATE TEMPORARY TABLE tmp_seats(
156    seance_seat_id INT(10),
157    datetime_booking_max DATETIME,
158    quota_id INT(10),
159    barcode VARCHAR(24),
160    reserve_chain_id INT(10),
161    ticket_row_number INT(10),
162    ticket_num VARCHAR(16),
163    schema_seat_id INT(10),
164    seance_nid INT(10),
165    waybill_nid INT(10),
166    seance_seat_price INT(10),
167    seance_seat_discount_amount INT(11),
168    ticket_tax_mode TINYINT(1),
169    wb_service_payment_percent INT(10),
170    ticket_fee INT(11)
171  ) ENGINE = MEMORY;
172
173  SET @ticket_row_number = 0;
174
175  INSERT INTO tmp_seats(
176    seance_seat_id, datetime_booking_max, quota_id, ticket_row_number,
177    schema_seat_id, seance_nid, waybill_nid,
178    barcode, seance_seat_price, seance_seat_discount_amount, ticket_tax_mode, wb_service_payment_percent
179  )
180  SELECT
181    ss.id, s.datetime_booking_max, ss.quota_id, (@ticket_row_number:=@ticket_row_number + 1),
182    ss.schema_seats_id, ss.event_seance_nid, ws.waybill_nid,
183    wsb.barcode, ss.cost, T.discount_amount, IFNULL(ev_s.tax_mode, e.field_tax_mode_value),
184    ctw.field_service_payment_value
185  FROM seance_seats AS ss
186  INNER JOIN content_type_event_seance AS s ON s.nid = ss.event_seance_nid
187  INNER JOIN content_type_event AS e ON e.nid = s.event_nid
188  LEFT JOIN event_sector AS ev_s ON ev_s.event_nid = e.nid AND ss.sector_nid = ev_s.sector_nid
189  INNER JOIN waybill_seats AS ws ON ws.id = ss.add_waybill_seats_id
190  INNER JOIN content_type_waybill AS ctw ON ws.waybill_nid = ctw.nid
191  LEFT JOIN waybill_seats_barcode AS wsb ON wsb.waybill_seat_id = ws.id
192  INNER JOIN (
193    SELECT
194      CAST(sp_get_token(item,';',1) as UNSIGNED) as id,
195      CAST(sp_get_token(item,';',2) as UNSIGNED) as cost,
196      CAST(sp_get_token(item,';',3) as SIGNED) as discount_amount
197    FROM tmp_cart
198  ) as T ON T.id = ss.id AND T.cost = ss.cost
199  WHERE ss.sid = v_sid AND ss.status = 20;
200
201  SELECT ROW_COUNT() INTO v_seats_count;
202
203  DROP TEMPORARY TABLE tmp_cart;
204
205  IF v_seats_count <= 0
206  THEN
207    DROP TEMPORARY TABLE tmp_seats;
208    SIGNAL SQLSTATE '45001';
209  END IF;
210
211  IF v_seats_count = v_cart_size
212  THEN
213    IF EXISTS(SELECT 1 FROM tmp_seats WHERE seance_seat_discount_amount IS NULL) THEN
214      DROP TEMPORARY TABLE tmp_seats;
215      SIGNAL SQLSTATE '45008';
216    END IF;
217  ELSE
218    DROP TEMPORARY TABLE tmp_seats;
219    SIGNAL SQLSTATE '45008';
220  END IF;
221
222  IF v_seats_count < 100
223  THEN
224    SET v_ticket_num_pad_amount = 2;
225  ELSE
226    SET v_ticket_num_pad_amount = CEIL(LOG10(v_seats_count + 1));
227  END IF;
228
229  SET v_sc = v_seats_count;
230
231  WHILE (v_sc > 0)
232  DO
233    SELECT quota_id INTO v_quota_id
234    FROM tmp_seats WHERE reserve_chain_id IS NULL LIMIT 1;
235
236    SET v_reserve_chain_id = NULL;
237    SET v_reserve_chain_id = iframe_calc_reserve_chain_id(v_iframe_nid, v_web_agent_uid, v_quota_id);
238
239    IF (v_reserve_chain_id IS NULL)
240    THEN
241      SELECT seance_seat_id INTO v_error_seance_seat_id
242      FROM tmp_seats
243      WHERE quota_id = v_quota_id
244      LIMIT 1;
245
246      SET v_err_msg = CONCAT('Reserve chain id could not be found for the seance seat ', CONVERT(v_error_seance_seat_id, CHAR));
247      SIGNAL SQLSTATE '45005' SET MESSAGE_TEXT = v_err_msg;
248    END IF;
249
250    UPDATE tmp_seats
251    SET reserve_chain_id = v_reserve_chain_id
252    WHERE quota_id = v_quota_id;
253
254    SELECT ROW_COUNT() INTO v_rc;
255
256    SET v_sc = v_sc - v_rc;
257  END WHILE;
258
259  SELECT
260    UNIX_TIMESTAMP(MIN(datetime_booking_max))
261  INTO v_seance_max_booking
262  FROM tmp_seats;
263
264  SELECT
265    cti.field_target_finance_rules_value, cti.field_target_finance_value, tc.exchange_etickets
266  INTO
267    v_iframe_fin_rules, v_iframe_fin_value, v_exchange_status
268  FROM content_type_iframe AS cti
269  INNER JOIN tp_cabinets AS tc ON tc.cabinet_uid = cti.field_web_agent_uid
270  WHERE cti.nid = v_iframe_nid;
271
272  SELECT
273      v_now_timestamp + order_timeout * 60
274  INTO v_delivery_max_booking
275  FROM logical_delivery_type_payment_type
276  WHERE ldt_id = v_ld_id AND payment_type_id = v_payment;
277
278  SELECT minimum_booking_service
279  INTO v_pt_minimum_service_percent
280  FROM payment_types
281  WHERE payment_type_id = v_payment;
282
283  SELECT iframe_calc_payee_uid(v_web_agent_uid) INTO v_payee_uid;
284
285  CALL iframe_payment_account_get(v_payment, v_payee_uid, v_uid, v_login, v_password, v_shop_id, v_ofd, v_apple_pay_merchant_id, v_apple_pay_merchant_name, v_merchant_name);
286
287  IF v_uid IS NULL
288  THEN
289    SET v_err_msg = CONCAT('Payee with ID = ', CONVERT(v_payee_uid, CHAR), ' can not accept payments of type = "', v_payment, '"');
290    SIGNAL SQLSTATE '45007' SET MESSAGE_TEXT = v_err_msg;
291  END IF;
292
293  SELECT
294    cab.ofd_fee_nds
295  INTO v_ticket_fee_tax_mode
296  FROM tp_cabinets AS cab
297  WHERE cab.cabinet_uid = v_payee_uid;
298
299  SET v_order_num = iframe_generate_order_number();
300
301  UPDATE tmp_seats SET
302    barcode = IF(v_exchange_status = 0, iframe_generate_barcode(), barcode),
303    ticket_num = CONCAT(v_order_num, LPAD(CONVERT(ticket_row_number, CHAR), v_ticket_num_pad_amount, '0')),
304    ticket_fee = iframe_calc_ticket_fee(wb_service_payment_percent, v_pt_minimum_service_percent, v_iframe_fin_rules, v_iframe_fin_value, seance_seat_price - seance_seat_discount_amount);
305
306  IF v_prome_code_id IS NULL
307  THEN
308    SET v_promocode_max_usage = NULL;
309  ELSE
310    SELECT
311      max_usage
312    INTO
313      v_promocode_max_usage
314    FROM tp_promo_codes
315    WHERE
316      prcid = v_prome_code_id AND
317      cabinet_uid = v_web_agent_uid AND
318      (iframe_nid IS NULL OR iframe_nid = v_iframe_nid) AND
319      (start IS NULL OR start < v_now_datetime) AND
320      expiration > v_now_datetime;
321
322    SELECT ROW_COUNT() INTO v_rc;
323
324    IF v_rc <> 1
325    THEN
326      SIGNAL SQLSTATE '45002';
327    END IF;
328  END IF;
329
330  SET v_deadline_datetime = FROM_UNIXTIME(IF(v_seance_max_booking > v_delivery_max_booking, v_delivery_max_booking, v_seance_max_booking));
331
332  START TRANSACTION;
333
334  INSERT INTO content_type_order (
335    num, created_uid, ip_address, delivery_cost, created_at,
336    deadline_datetime, status, origin_type, iframe_nid, promocode_id,
337    delivery_type, payment_type,
338    client_name, client_phone, client_email,
339    utm_campaign, utm_source, payee_uid
340  )
341  VALUES (
342    v_order_num, NULL, v_ip, 0, v_now_datetime,
343    v_deadline_datetime,
344    'booked', 'site', v_iframe_nid, v_prome_code_id,
345    v_delivery, v_payment,
346    v_fio, v_phone, v_email,
347    v_utm_campaign, v_utm_source, v_payee_uid
348  );
349
350  SELECT LAST_INSERT_ID() INTO v_order_nid;
351
352  IF v_promocode_max_usage = 1
353  THEN
354    IF EXISTS(
355        SELECT 1 FROM content_type_order AS o WHERE o.promocode_id = v_prome_code_id AND o.status != 'rejected' AND o.nid <> v_order_nid LIMIT 1
356    )
357    THEN
358      ROLLBACK;
359      SIGNAL SQLSTATE '45003';
360    END IF;
361  ELSEIF v_promocode_max_usage IS NOT NULL
362  THEN
363    SELECT COUNT(o.nid) INTO v_promocode_current_usage
364    FROM content_type_order AS o
365    WHERE o.promocode_id = v_prome_code_id AND o.status != 'rejected';
366
367    IF v_promocode_current_usage > v_promocode_max_usage
368    THEN
369      ROLLBACK;
370      SIGNAL SQLSTATE '45003';
371    END IF;
372  END IF;
373
374  INSERT INTO content_type_ticket(
375    num,
376    created_at,
377    created_uid,
378    barcode,
379    service_fee_amount,
380    cost,
381    discount_amount,
382    order_nid,
383    seance_seat_id,
384    reserve_chain_id,
385    refund_amount,
386    seance_nid,
387    waybill_nid,
388    schema_seat_id,
389    tax_mode,
390    payment_tax_amount,
391    service_fee_tax_mode,
392    service_fee_tax_amount
393  )
394  SELECT
395    ticket_num,
396    v_now_datetime,
397    NULL,
398    barcode,
399    ticket_fee,
400    seance_seat_price,
401    seance_seat_discount_amount,
402    v_order_nid,
403    seance_seat_id,
404    reserve_chain_id,
405    0,
406    seance_nid,
407    waybill_nid,
408    schema_seat_id,
409    ticket_tax_mode,
410    iframe_calc_nds((seance_seat_price - seance_seat_discount_amount) * 100, ticket_tax_mode),
411    v_ticket_fee_tax_mode,
412    iframe_calc_nds(ticket_fee, v_ticket_fee_tax_mode)
413  FROM tmp_seats;
414
415
416  UPDATE tmp_seats AS s
417  INNER JOIN seance_seats AS ses ON ses.id = s.seance_seat_id AND ses.sid = v_sid AND ses.status = 20
418  INNER JOIN content_type_ticket AS t ON t.num = s.ticket_num
419  SET ses.ticket_nid = t.nid, ses.sid = NULL, ses.sid_deadline_datetime = NULL;
420
421  SELECT ROW_COUNT() INTO v_rc;
422
423  IF v_rc <> v_seats_count
424  THEN
425    ROLLBACK;
426    SIGNAL SQLSTATE '45004';
427  END IF;
428
429  COMMIT;
430
431  DROP TEMPORARY TABLE tmp_seats;
432  SELECT v_order_nid AS nid, v_order_num AS num, v_deadline_datetime AS deadline_datetime;
433END;
434
435
436PDOException: SQLSTATE[42000]: Syntax error or access violation: 1304 PROCEDURE iframe_order_create already exists in /home/dev/.composer/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:167
437Stack trace:
438#0 /home/dev/.composer/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php(167): PDO->exec('CREATE DEFINER ...')
439#1 /home/dev/.composer/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/AdapterWrapper.php(169): Phinx\Db\Adapter\PdoAdapter->execute('CREATE DEFINER ...')
440#2 /home/dev/.composer/vendor/robmorgan/phinx/src/Phinx/Migration/AbstractMigration.php(230): Phinx\Db\Adapter\AdapterWrapper->execute('CREATE DEFINER ...')
441#3 /home/dev/htdocs.dev4/phinx/migrations/20200421105639_refs7473.php(36): Phinx\Migration\AbstractMigration->execute('CREATE DEFINER ...')
442#4 /home/dev/.composer/vendor/robmorgan/phinx/src/Phinx/Migration/Manager/Environment.php(128): Refs7473->up()
443#5 /home/dev/.composer/vendor/robmorgan/phinx/src/Phinx/Migration/Manager.php(391): Phinx\Migration\Manager\Environment->executeMigration(Object(Refs7473), 'up', false)
444#6 /home/dev/.composer/vendor/robmorgan/phinx/src/Phinx/Migration/Manager.php(365): Phinx\Migration\Manager->executeMigration('db', Object(Refs7473), 'up', false)
445#7 /home/dev/.composer/vendor/robmorgan/phinx/src/Phinx/Console/Command/Migrate.php(122): Phinx\Migration\Manager->migrate('db', 20200427102249, false)
446#8 /home/dev/.composer/vendor/symfony/console/Command/Command.php(255): Phinx\Console\Command\Migrate->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
447#9 /home/dev/.composer/vendor/symfony/console/Application.php(921): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
448#10 /home/dev/.composer/vendor/symfony/console/Application.php(273): Symfony\Component\Console\Application->doRunCommand(Object(Phinx\Console\Command\Migrate), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
449#11 /home/dev/.composer/vendor/robmorgan/phinx/src/Phinx/Console/PhinxApplication.php(88): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
450#12 /home/dev/.composer/vendor/symfony/console/Application.php(149): Phinx\Console\PhinxApplication->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
451#13 /home/dev/.composer/vendor/robmorgan/phinx/bin/phinx(28): Symfony\Component\Console\Application->run()
452#14 {main}
453ERROR: phinx