1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
CREATE DEFINER = 'cargoport'@'%' PROCEDURE `profit_export_zones`(
IN `p_year` INTEGER(11),
IN `p_month_begin` INTEGER(11),
IN `p_month_end` INTEGER(11),
IN `p_date_from` DATETIME,
IN `p_date_to` DATETIME,
IN `p_div` INTEGER(11),
IN `p_decimal_places` INTEGER(11)
)
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT ''
BEGIN
SELECT *,
sum_fact_prr_1 + sum_fact_other_1 + sum_fact_teo_1 + sum_fact_fares_1 + sum_fact_storage_1 AS sum_fact_1,
sum_fact_prr_2 + sum_fact_other_2 + sum_fact_teo_2 + sum_fact_fares_2 + sum_fact_storage_2 AS sum_fact_2,
sum_fact_prr_4 + sum_fact_other_4 + sum_fact_teo_4 + sum_fact_fares_4 + sum_fact_storage_4 AS sum_fact_4,
sum_fact_prr + sum_fact_other + sum_fact_teo + sum_fact_fares + sum_fact_storage AS sum_fact
FROM
(SELECT cg.cargo_group_name,
ROUND(invoicing_sum_zonenum(pcp.cargo_status, cg.id, cz.id, '1', p_date_from, p_date_to, 'ПЕРЕГРУЗКА')/p_div, p_decimal_places) AS sum_fact_prr_1,
ROUND(invoicing_sum_zonenum(pcp.cargo_status, cg.id, cz.id, '1', p_date_from, p_date_to, 'ПРОЧИЕ')/p_div, p_decimal_places) AS sum_fact_other_1,
ROUND(invoicing_sum_zonenum(pcp.cargo_status, cg.id, cz.id, '1', p_date_from, p_date_to, 'ТЭО')/p_div, p_decimal_places) AS sum_fact_teo_1,
ROUND(invoicing_sum_zonenum(pcp.cargo_status, cg.id, cz.id, '1', p_date_from, p_date_to, 'ПРОВОЗНАЯ ПЛАТА')/p_div, p_decimal_places) AS sum_fact_fares_1,
ROUND(invoicing_sum_zonenum(pcp.cargo_status, cg.id, cz.id, '1', p_date_from, p_date_to, 'ХРАНЕНИЕ')/p_div, p_decimal_places) AS sum_fact_storage_1,
ROUND(invoicing_sum_zonenum(pcp.cargo_status, cg.id, cz.id, '2', p_date_from, p_date_to, 'ПЕРЕГРУЗКА')/p_div, p_decimal_places) AS sum_fact_prr_2,
ROUND(invoicing_sum_zonenum(pcp.cargo_status, cg.id, cz.id, '2', p_date_from, p_date_to, 'ПРОЧИЕ')/p_div, p_decimal_places) AS sum_fact_other_2,
ROUND(invoicing_sum_zonenum(pcp.cargo_status, cg.id, cz.id, '2', p_date_from, p_date_to, 'ТЭО')/p_div, p_decimal_places) AS sum_fact_teo_2,
ROUND(invoicing_sum_zonenum(pcp.cargo_status, cg.id, cz.id, '2', p_date_from, p_date_to, 'ПРОВОЗНАЯ ПЛАТА')/p_div, p_decimal_places) AS sum_fact_fares_2,
ROUND(invoicing_sum_zonenum(pcp.cargo_status, cg.id, cz.id, '2', p_date_from, p_date_to, 'ХРАНЕНИЕ')/p_div, p_decimal_places) AS sum_fact_storage_2,
ROUND(invoicing_sum_zonenum(pcp.cargo_status, cg.id, cz.id, '4', p_date_from, p_date_to, 'ПЕРЕГРУЗКА')/p_div, p_decimal_places) AS sum_fact_prr_4,
ROUND(invoicing_sum_zonenum(pcp.cargo_status, cg.id, cz.id, '4', p_date_from, p_date_to, 'ПРОЧИЕ')/p_div, p_decimal_places) AS sum_fact_other_4,
ROUND(invoicing_sum_zonenum(pcp.cargo_status, cg.id, cz.id, '4', p_date_from, p_date_to, 'ТЭО')/p_div, p_decimal_places) AS sum_fact_teo_4,
ROUND(invoicing_sum_zonenum(pcp.cargo_status, cg.id, cz.id, '4', p_date_from, p_date_to, 'ПРОВОЗНАЯ ПЛАТА')/p_div, p_decimal_places) AS sum_fact_fares_4,
ROUND(invoicing_sum_zonenum(pcp.cargo_status, cg.id, cz.id, '4', p_date_from, p_date_to, 'ХРАНЕНИЕ')/p_div, p_decimal_places) AS sum_fact_storage_4,
ROUND(invoicing_sum(pcp.cargo_status, cg.id, cz.id, p_date_from, p_date_to, 'ПЕРЕГРУЗКА')/p_div, p_decimal_places) AS sum_fact_prr,
ROUND(invoicing_sum(pcp.cargo_status, cg.id, cz.id, p_date_from, p_date_to, 'ПРОЧИЕ')/p_div, p_decimal_places) AS sum_fact_other,
ROUND(invoicing_sum(pcp.cargo_status, cg.id, cz.id, p_date_from, p_date_to, 'ТЭО')/p_div, p_decimal_places) AS sum_fact_teo,
ROUND(invoicing_sum(pcp.cargo_status, cg.id, cz.id, p_date_from, p_date_to, 'ПРОВОЗНАЯ ПЛАТА')/p_div, p_decimal_places) AS sum_fact_fares,
ROUND(invoicing_sum(pcp.cargo_status, cg.id, cz.id, p_date_from, p_date_to, 'ХРАНЕНИЕ')/p_div, p_decimal_places) AS sum_fact_storage
FROM plan_cargo_processing pcp
INNER JOIN cargo_group cg ON cg.id = pcp.cargo_group_id
INNER JOIN cargo_zone cz ON cz.id = pcp.cargo_zone_id
WHERE pcp.cargo_status = 'export'
AND pcp.year = p_year
AND pcp.month BETWEEN p_month_begin AND p_month_end
GROUP BY cg.id) iq
ORDER BY cargo_group_name
;
END;
