| 5 |
5 |
use SL::DB::AccTransaction;
|
| 6 |
6 |
use SL::DB::Invoice;
|
| 7 |
7 |
use SL::DB::Order;
|
|
8 |
use SL::DB::EmailJournal;
|
|
9 |
use SL::DB::Letter;
|
| 8 |
10 |
use SL::DB;
|
| 9 |
11 |
|
| 10 |
12 |
__PACKAGE__->run_before('check_auth');
|
| ... | ... | |
| 75 |
77 |
$self->{dun_statistic} = selectall_hashref_query($::form, $dbh, $query);
|
| 76 |
78 |
$self->render('customer_vendor_turnover/count_open_items_by_year', { layout => 0 });
|
| 77 |
79 |
}
|
|
80 |
|
| 78 |
81 |
sub action_count_open_items_by_month {
|
| 79 |
82 |
|
| 80 |
83 |
my ($self) = @_;
|
| ... | ... | |
| 99 |
102 |
$self->{dun_statistic} = selectall_hashref_query($::form, $dbh, $query);
|
| 100 |
103 |
$self->render('customer_vendor_turnover/count_open_items_by_year', { layout => 0 });
|
| 101 |
104 |
}
|
|
105 |
|
| 102 |
106 |
sub action_turnover_by_month {
|
| 103 |
107 |
|
| 104 |
108 |
my ($self) = @_;
|
| ... | ... | |
| 128 |
132 |
$self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query);
|
| 129 |
133 |
$self->render('customer_vendor_turnover/count_turnover', { layout => 0 });
|
| 130 |
134 |
}
|
|
135 |
|
| 131 |
136 |
sub action_turnover_by_year {
|
| 132 |
137 |
my ($self) = @_;
|
| 133 |
138 |
|
| ... | ... | |
| 233 |
238 |
return $self->render('customer_vendor_turnover/_list_open_orders', { output => 0 }, orders => $open_orders, title => $::locale->text('Open Orders') );
|
| 234 |
239 |
}
|
| 235 |
240 |
|
|
241 |
sub action_get_mails {
|
|
242 |
my ( $self ) = @_;
|
|
243 |
|
|
244 |
my $dbh = SL::DB->client->dbh;
|
|
245 |
my $query;
|
|
246 |
my $cv = $::form->{id};
|
|
247 |
|
|
248 |
if ( $::form->{db} eq 'customer') {
|
|
249 |
$query = <<SQL;
|
|
250 |
WITH oe_emails_customer AS (SELECT rc.to_id, rc.from_id, oe.quotation, oe.quonumber, oe.ordnumber, c.id FROM
|
|
251 |
record_links rc
|
|
252 |
LEFT JOIN oe oe ON rc.from_id = oe.id
|
|
253 |
LEFT JOIN customer c ON oe.customer_id = c.id
|
|
254 |
WHERE rc.to_table = 'email_journal' AND rc.from_table ='oe'),
|
|
255 |
|
|
256 |
do_emails_customer AS (SELECT rc.to_id, rc.from_id, o.donumber, c.id FROM
|
|
257 |
record_links rc
|
|
258 |
LEFT JOIN delivery_orders o ON rc.from_id = o.id
|
|
259 |
LEFT JOIN customer c ON o.customer_id = c.id
|
|
260 |
WHERE rc.to_table = 'email_journal' AND rc.from_table = 'delivery_orders'),
|
|
261 |
|
|
262 |
inv_emails_customer AS (SELECT rc.to_id, rc.from_id, inv.type, inv.invnumber, c.id FROM
|
|
263 |
record_links rc
|
|
264 |
LEFT JOIN ar inv ON rc.from_id = inv.id
|
|
265 |
LEFT JOIN customer c ON inv.customer_id = c.id
|
|
266 |
WHERE rc.to_table = 'email_journal' AND rc.from_table = 'ar'),
|
|
267 |
|
|
268 |
letter_emails_customer AS (SELECT rc.to_id, rc.from_id, l.letternumber, c.id FROM
|
|
269 |
record_links rc
|
|
270 |
LEFT JOIN letter l ON rc.from_id = l.id
|
|
271 |
LEFT JOIN customer c ON l.customer_id = c.id
|
|
272 |
WHERE rc.to_table = 'email_journal' AND rc.from_table = 'letter')
|
|
273 |
|
|
274 |
SELECT ej.*, CASE
|
|
275 |
oec.quotation WHEN 'F' THEN 'Sales Order'
|
|
276 |
ELSE 'Quotation'
|
|
277 |
END AS type,
|
|
278 |
CASE
|
|
279 |
oec.quotation WHEN 'F' THEN oec.ordnumber
|
|
280 |
ELSE oec.quonumber
|
|
281 |
END AS recordnumber,
|
|
282 |
oec.id AS record_id FROM email_journal ej
|
|
283 |
LEFT JOIN oe_emails_customer oec ON ej.id = oec.to_id
|
|
284 |
WHERE oec.id = ?
|
|
285 |
|
|
286 |
UNION
|
|
287 |
|
|
288 |
SELECT ej.*, 'Delivery Order' AS type, dec.donumber AS recordnumber,dec.id AS record_id FROM email_journal ej
|
|
289 |
LEFT JOIN do_emails_customer dec ON ej.id = dec.to_id
|
|
290 |
WHERE dec.id = ?
|
|
291 |
|
|
292 |
UNION
|
|
293 |
|
|
294 |
SELECT ej.*, CASE
|
|
295 |
iec.type WHEN 'credit_note' THEN 'Credit Note'
|
|
296 |
WHEN 'invoice' THEN 'Invoice'
|
|
297 |
ELSE 'N/A'
|
|
298 |
END AS type,
|
|
299 |
iec.invnumber AS recordnumber,iec.id AS record_id FROM email_journal ej
|
|
300 |
LEFT JOIN inv_emails_customer iec ON ej.id = iec.to_id
|
|
301 |
WHERE iec.id = ?
|
|
302 |
|
|
303 |
UNION
|
|
304 |
|
|
305 |
SELECT ej.*, 'Letter' AS type, lec.letternumber AS recordnumber,lec.id AS record_id FROM email_journal ej
|
|
306 |
LEFT JOIN letter_emails_customer lec ON ej.id = lec.to_id
|
|
307 |
WHERE lec.id = ?
|
|
308 |
ORDER BY sent_on DESC
|
|
309 |
SQL
|
|
310 |
}
|
|
311 |
else {
|
|
312 |
$query = <<SQL;
|
|
313 |
WITH oe_emails_vendor AS (SELECT rc.to_id, rc.from_id, oe.quotation, oe.quonumber, oe.ordnumber, c.id FROM
|
|
314 |
record_links rc
|
|
315 |
LEFT JOIN oe oe ON rc.from_id = oe.id
|
|
316 |
LEFT JOIN vendor c ON oe.vendor_id = c.id
|
|
317 |
WHERE rc.to_table = 'email_journal' AND rc.from_table ='oe'),
|
|
318 |
|
|
319 |
do_emails_vendor AS (SELECT rc.to_id, rc.from_id, o.donumber, c.id FROM
|
|
320 |
record_links rc
|
|
321 |
LEFT JOIN delivery_orders o ON rc.from_id = o.id
|
|
322 |
LEFT JOIN vendor c ON o.vendor_id = c.id
|
|
323 |
WHERE rc.to_table = 'email_journal' AND rc.from_table = 'delivery_orders'),
|
|
324 |
|
|
325 |
inv_emails_vendor AS (SELECT rc.to_id, rc.from_id, inv.type, inv.invnumber, c.id FROM
|
|
326 |
record_links rc
|
|
327 |
LEFT JOIN ap inv ON rc.from_id = inv.id
|
|
328 |
LEFT JOIN vendor c ON inv.vendor_id = c.id
|
|
329 |
WHERE rc.to_table = 'email_journal' AND rc.from_table = 'ar'),
|
|
330 |
|
|
331 |
letter_emails_vendor AS (SELECT rc.to_id, rc.from_id, l.letternumber, c.id FROM
|
|
332 |
record_links rc
|
|
333 |
LEFT JOIN letter l ON rc.from_id = l.id
|
|
334 |
LEFT JOIN vendor c ON l.vendor_id = c.id
|
|
335 |
WHERE rc.to_table = 'email_journal' AND rc.from_table = 'letter')
|
|
336 |
|
|
337 |
SELECT ej.*, CASE
|
|
338 |
oec.quotation WHEN 'F' THEN 'Purchase Order'
|
|
339 |
ELSE 'Request quotation'
|
|
340 |
END AS type,
|
|
341 |
CASE
|
|
342 |
oec.quotation WHEN 'F' THEN oec.ordnumber
|
|
343 |
ELSE oec.quonumber
|
|
344 |
END AS recordnumber,
|
|
345 |
oec.id AS record_id FROM email_journal ej
|
|
346 |
LEFT JOIN oe_emails_vendor oec ON ej.id = oec.to_id
|
|
347 |
WHERE oec.id = ?
|
|
348 |
|
|
349 |
UNION
|
|
350 |
|
|
351 |
SELECT ej.*, 'Purchase Delivery Order' AS type, dec.donumber AS recordnumber, dec.id AS record_id FROM email_journal ej
|
|
352 |
LEFT JOIN do_emails_vendor dec ON ej.id = dec.to_id
|
|
353 |
WHERE dec.id = ?
|
|
354 |
|
|
355 |
UNION
|
|
356 |
|
|
357 |
SELECT ej.*, iec.type AS type, iec.invnumber AS recordnumber, iec.id AS record_id FROM email_journal ej
|
|
358 |
LEFT JOIN inv_emails_vendor iec ON ej.id = iec.to_id
|
|
359 |
WHERE iec.id = ?
|
|
360 |
|
|
361 |
UNION
|
|
362 |
|
|
363 |
SELECT ej.*, 'Letter' AS type, lec.letternumber AS recordnumber, lec.id AS record_id FROM email_journal ej
|
|
364 |
LEFT JOIN letter_emails_vendor lec ON ej.id = lec.to_id
|
|
365 |
WHERE lec.id = ?
|
|
366 |
ORDER BY sent_on DESC
|
|
367 |
SQL
|
|
368 |
}
|
|
369 |
my $emails = selectall_hashref_query($::form, $dbh, $query, $cv, $cv, $cv, $cv);
|
|
370 |
$self->render('customer_vendor_turnover/email_statistic', { layout => 0 }, emails => $emails);
|
|
371 |
}
|
|
372 |
|
| 236 |
373 |
sub _list_articles_by_invoice {
|
| 237 |
374 |
}
|
| 238 |
375 |
sub _list_count_articles_by_year {
|
Kundenstatistik: Emails