· 9 years ago · Jan 31, 2017, 08:26 AM
1DELIMITER $$
2
3DROP PROCEDURE IF EXISTS rpt_due_invoices$$
4
5CREATE PROCEDURE rpt_due_invoices
6(
7 in _tenant_id int,
8 in _ses_user_id int,
9 in _customer_id int,
10 in _customer_fiscal_entity_id int,
11 in _tenant_fiscal_entity_id int,
12 in _project_id int,
13 in _reference_id int,
14 in _date_from date,
15 in _date_to date,
16 in _due_date_from date,
17 in _due_date_to date,
18 in _overdue_only int,
19 in _invoice_type int,
20 in _responsible_id int,
21 in _practice_id int
22)
23 LANGUAGE SQL
24 DETERMINISTIC
25 CONTAINS SQL
26BEGIN
27
28 declare _UserRoleID int;
29
30 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
31
32
33 DROP TABLE IF EXISTS tmp_project_list;
34 CREATE TEMPORARY TABLE tmp_project_list
35 (
36 project_id INT
37 );
38
39 -- if is admin, management or support, ignore other security
40 if ( fn_has_sys_access(_tenant_id, _ses_user_id, 1) or fn_has_sys_access(_tenant_id, _ses_user_id, 2) or fn_has_sys_access(_tenant_id, _ses_user_id, 3) ) then
41 insert into tmp_project_list
42 (project_id)
43 select distinct p.project_id
44 from project p
45 join invoice_project ip on ip.project_id = p.project_id
46 join invoice i on i.invoice_id = ip.invoice_id
47 join invoice_ext ext on ext.invoice_id = i.invoice_id
48 where 1=1
49 and p.project_type = 'project'
50 and p.tenant_id = _tenant_id
51 and ((_customer_id is null) or (i.customer_id = _customer_id))
52 and ((_customer_fiscal_entity_id is null) or (ext.customer_fiscal_entity_id = _customer_fiscal_entity_id))
53 and (
54 (p.responsible_id = _ses_user_id)
55 or
56 (p.project_id in
57 (select distinct p.project_id
58 from project p
59 join project_manager pm on pm.project_id = p.project_id
60 where p.tenant_id = _tenant_id
61 and pm.project_manager_id = _ses_user_id
62 )
63 )
64 );
65 else
66 insert into tmp_project_list
67 (project_id)
68 select distinct p.project_id
69 from project p
70 join invoice_project ip on ip.project_id = p.project_id
71 join invoice i on i.invoice_id = ip.invoice_id
72 join invoice_ext ext on ext.invoice_id = i.invoice_id
73 where 1=1
74 and p.project_type = 'project'
75 and p.tenant_id = _tenant_id
76 and ((_customer_id is null) or (i.customer_id = _customer_id))
77 and ((_customer_fiscal_entity_id is null) or (ext.customer_fiscal_entity_id = _customer_fiscal_entity_id));
78 end if;
79
80 drop table if exists tmp_result;
81 create temporary table tmp_result (
82 invoice_id int,
83 invoice_number varchar(100),
84 `date` date,
85 due_date date,
86 customer_id int,
87 customer_name varchar(400),
88 customer_fiscal_entity_name varchar(400),
89 currency char(3),
90 item_id int,
91 item_name varchar(100),
92 base_item_type char(7), -- service / expense
93 base_amount decimal(10,2), total_amount decimal(10,2), paid_amount decimal(10,2), due_amount decimal(10,2), base_amount_rc decimal(10,2), total_amount_rc decimal(10,2), paid_amount_rc decimal(10,2), due_amount_rc decimal(10,2),
94 due_days int, project_id int,
95 project_name varchar(400),
96 controller_id int, controller_name varchar(200),
97 practice_id int, practice_name varchar(200)
98
99 ) engine=memory;
100
101 /*
102 (invoice_id, invoice_number, `date`,
103 due_date,
104 customer_id,
105 customer_name,
106 customer_fiscal_entity_name,
107 currency,
108 item_id,
109 item_name,
110 base_amount, total_amount, paid_amount, due_amount decimal(10,2), base_amount_rc decimal(10,2), total_amount_rc decimal(10,2), paid_amount_rc decimal(10,2), due_amount_rc decimal(10,2),
111 due_days int, project_id int,
112 project_name varchar(400),
113 controller_id int, controller_name varchar(200),
114 practice_id int, practice_name varchar(200)
115
116 */
117
118 if (_invoice_type is not null) then
119 -- stim exact ce se doreste - proforma sau fiscala, deci punem filtru
120 insert into tmp_result
121 select
122 i.invoice_id,
123 case when i.invoice_type_id = 2 THEN i.serial_number ELSE CONCAT(i.serial_number, '*') END AS invoice_number,
124 i.date, i.due_date, i.customer_id, cust.customer_name, cfe.name as customer_fiscal_entity_name,
125 i.currency, id.item_id, sii.name as item_name,
126 case when id.item_id in (1,3,4) then 'SERVICE' else 'EXPENSE' end as base_item_type,
127 -- invoice_detail totals
128 id.base_amount, id.total_amount, id.paid_amount, id.due_amount,
129 id.base_amount_rc, id.total_amount_rc, id.paid_amount_rc, id.due_amount_rc,
130 -- invoice totals
131 -- i.total_amount, i.paid_amount, i.due_amount, ext.total_amount_rc, ext.paid_amount_rc, ext.due_amount_rc, ext.base_amount_rc - ext.base_paid_amount_rc as base_due_amount_rc,
132 datediff(curdate(), i.due_date) AS due_days,
133 p.project_id, p.project_name, u.user_id as controller_id, u.full_name as controller_name,
134 practice.practice_id, practice.practice_name
135 from invoice i
136 join invoice_detail id on id.invoice_id = i.invoice_id
137 join invoice_ext ext on ext.invoice_id = i.invoice_id
138 join customer cust on cust.customer_id = i.customer_id
139 join customer_fiscal_entity cfe on cfe.customer_fiscal_entity_id = ext.customer_fiscal_entity_id
140 join tenant_fiscal_entity tfe on tfe.tenant_fiscal_entity_id = ext.tenant_fiscal_entity_id
141 join project p on p.project_id = id.master_project_id and p.project_type = 'project'
142 join project_ext pext on pext.project_id = p.project_id
143 -- visibility filter
144 join tmp_project_list tpl on tpl.project_id = p.project_id
145 left join project ref on ref.project_id = id.source_project_id and ref.project_type = 'reference'
146 join user u on u.user_id = p.responsible_id
147 join sys_invoice_item sii on sii.item_id = id.item_id
148 left join practice on practice.practice_id = pext.practice_id
149 where 1=1
150 and i.tenant_id = _tenant_id
151 and i.invoice_status_id in (2,4,5)
152 and abs(id.due_amount) <> 0
153 and ((_customer_id is null) or (i.customer_id = _customer_id))
154 and ((_customer_fiscal_entity_id is null) or (ext.customer_fiscal_entity_id = _customer_fiscal_entity_id))
155 and ((_tenant_fiscal_entity_id is null) or (ext.tenant_fiscal_entity_id = _tenant_fiscal_entity_id))
156 and ((_project_id is null) or (id.master_project_id = _project_id))
157 and ((_reference_id is null) or (ref.project_id = _reference_id))
158 and ((_date_from is null) or (i.date >= _date_from))
159 and ((_date_to is null) or (i.date <= _date_to))
160 and ((_due_date_from is null) or (i.due_date >= _date_from))
161 and ((_due_date_to is null) or (i.due_date <= _date_to))
162 and ((_responsible_id is null) or (p.responsible_id = _responsible_id))
163 and ((_practice_id is null) or (pext.practice_id = _practice_id))
164 and ((_overdue_only = 0) or (datediff(curdate(), i.due_date) > 0))
165 and (i.invoice_type_id = _invoice_type);
166 else
167 -- nu se specifica, deci se ia doar prima (source_document_id null
168 insert into tmp_result
169 select
170 i.invoice_id,
171 case when i.invoice_type_id = 2 THEN i.serial_number ELSE CONCAT(i.serial_number, '*') END AS invoice_number,
172 i.date, i.due_date, i.customer_id, cust.customer_name, cfe.name as customer_fiscal_entity_name,
173 i.currency, id.item_id, sii.name as item_name,
174 case when id.item_id in (1,3,4) then 'SERVICE' else 'EXPENSE' end as base_item_type,
175 -- invoice_detail totals
176 id.base_amount, id.total_amount, id.paid_amount, id.due_amount,
177 id.base_amount_rc, id.total_amount_rc, id.paid_amount_rc, id.due_amount_rc,
178 -- invoice totals
179 -- i.total_amount, i.paid_amount, i.due_amount, ext.total_amount_rc, ext.paid_amount_rc, ext.due_amount_rc, ext.base_amount_rc - ext.base_paid_amount_rc as base_due_amount_rc,
180 datediff(curdate(), i.due_date) AS due_days,
181 p.project_id, p.project_name, u.user_id as controller_id, u.full_name as controller_name,
182 practice.practice_id, practice.practice_name
183 from invoice i
184 join invoice_detail id on id.invoice_id = i.invoice_id
185 join invoice_ext ext on ext.invoice_id = i.invoice_id
186 join customer cust on cust.customer_id = i.customer_id
187 join customer_fiscal_entity cfe on cfe.customer_fiscal_entity_id = ext.customer_fiscal_entity_id
188 join tenant_fiscal_entity tfe on tfe.tenant_fiscal_entity_id = ext.tenant_fiscal_entity_id
189 join project p on p.project_id = id.master_project_id and p.project_type = 'project'
190 join project_ext pext on pext.project_id = p.project_id
191 -- visibility filter
192 join tmp_project_list tpl on tpl.project_id = p.project_id
193 join user u on u.user_id = p.responsible_id
194 join sys_invoice_item sii on sii.item_id = id.item_id
195 left join project ref on ref.project_id = id.source_project_id and ref.project_type = 'reference'
196 left join practice on practice.practice_id = pext.practice_id
197 where 1=1
198 and i.tenant_id = _tenant_id
199 and i.invoice_status_id in (2,4,5)
200 and abs(id.due_amount) <> 0
201 and ((_customer_id is null) or (i.customer_id = _customer_id))
202 and ((_customer_fiscal_entity_id is null) or (ext.customer_fiscal_entity_id = _customer_fiscal_entity_id))
203 and ((_tenant_fiscal_entity_id is null) or (ext.tenant_fiscal_entity_id = _tenant_fiscal_entity_id))
204 and ((_project_id is null) or (id.master_project_id = _project_id))
205 and ((_reference_id is null) or (ref.project_id = _reference_id))
206 and ((_date_from is null) or (i.date >= _date_from))
207 and ((_date_to is null) or (i.date <= _date_to))
208 and ((_due_date_from is null) or (i.due_date >= _date_from))
209 and ((_due_date_to is null) or (i.due_date <= _date_to))
210 and ((_responsible_id is null) or (p.responsible_id = _responsible_id))
211 and ((_practice_id is null) or (pext.practice_id = _practice_id))
212 and ((_overdue_only = 0) or (datediff(curdate(), i.due_date) > 0))
213 and (i.source_document_id is null);
214 end if;
215
216 select
217 tmp.invoice_id, invoice_number, `date`, due_date, customer_id, customer_name, customer_fiscal_entity_name, currency, base_item_type, due_days, project_id, project_name, controller_id, controller_name, practice_id, practice_name,
218 sum(base_amount) as base_amount, sum(total_amount) as total_amount, sum(paid_amount) as paid_amount, sum(due_amount) as due_amount, sum(base_amount_rc) as base_amount_rc, sum(total_amount_rc) as total_amount_rc, sum(paid_amount_rc) as paid_amount_rc, sum(due_amount_rc) as due_amount_rc,
219 comments.comments
220 from tmp_result tmp
221 left join (
222 select entity_id, entity_type, group_concat(
223 concat(u.full_name, ' (', DATE_FORMAT(c.created_at,'%d %b %y'), ') - ', comment) separator '\n') as comments
224 from comment c
225 join user u on u.user_id = c.created_by
226 where 1=1
227 and c.tenant_id = _tenant_id
228 and u.tenant_id = _tenant_id
229 and c.entity_type = 'invoice'
230 group by entity_id, entity_type
231 ) comments on comments.entity_id = tmp.invoice_id
232 group by invoice_id, invoice_number, `date`, due_date, customer_id, customer_name, customer_fiscal_entity_name, currency, base_item_type, due_days, project_id, project_name, controller_id, controller_name, practice_id, practice_name;
233
234 DROP TABLE IF EXISTS tmp_project_list;
235 DROP TABLE IF EXISTS tmp_result;
236
237 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
238END$$
239
240
241-- call rpt_due_invoices (1, 1, null, null, null, null, null, '2016-01-01', null, null, null, 0, null, null, 1);