1 package SL::Controller::CustomerVendorTurnover;
3 use parent qw(SL::Controller::Base);
5 use SL::DB::AccTransaction;
8 use SL::DB::EmailJournal;
13 sub action_list_turnover {
16 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
18 my $cv = $::form->{id};
20 if ( $::form->{db} eq 'customer' ) {
21 $open_invoices = SL::DB::Manager::Invoice->get_all(
25 amount => { gt => \'paid'},
26 amount => { lt => \'paid'},
29 with_objects => [ 'dunnings' ],
32 $open_invoices = SL::DB::Manager::PurchaseInvoice->get_all(
36 amount => { gt => \'paid'},
37 amount => { lt => \'paid'},
40 sort_by => 'invnumber DESC',
44 if (@{$open_invoices}) {
45 return $self->render(\'', { type => 'json' }) unless scalar @{$open_invoices};
46 $open_items = $self->_list_open_items($open_invoices);
48 my $open_orders = $self->_get_open_orders;
49 return $self->render('customer_vendor_turnover/turnover', { header => 0 },
50 open_orders => $open_orders,
51 open_items => $open_items,
56 sub _list_open_items {
57 my ($self, $open_items) = @_;
59 return $self->render('customer_vendor_turnover/_list_open_items', { output => 0 },
60 OPEN_ITEMS => $open_items,
61 title => $::locale->text('Open Items'),
65 sub action_count_open_items_by_year {
68 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
69 my $dbh = SL::DB->client->dbh;
71 my $cv = $::form->{id};
74 SELECT EXTRACT (YEAR FROM d.transdate),
78 LEFT JOIN ar a ON a.id = d.trans_id
79 LEFT JOIN customer c ON a.customer_id = c.id
81 GROUP BY EXTRACT (YEAR FROM d.transdate), c.id
82 ORDER BY date_part DESC
85 $self->{dun_statistic} = selectall_hashref_query($::form, $dbh, $query, $cv);
86 $self->render('customer_vendor_turnover/count_open_items_by_year', { layout => 0 });
89 sub action_count_open_items_by_month {
93 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
94 my $dbh = SL::DB->client->dbh;
96 my $cv = $::form->{id};
99 SELECT CONCAT(EXTRACT (MONTH FROM d.transdate),'/',EXTRACT (YEAR FROM d.transdate)) AS date_part,
103 LEFT JOIN ar a ON a.id = d.trans_id
104 LEFT JOIN customer c ON a.customer_id = c.id
106 GROUP BY EXTRACT (YEAR FROM d.transdate), EXTRACT (MONTH FROM d.transdate), c.id
107 ORDER BY EXTRACT (YEAR FROM d.transdate) DESC
110 $self->{dun_statistic} = selectall_hashref_query($::form, $dbh, $query, $cv);
111 $self->render('customer_vendor_turnover/count_open_items_by_year', { layout => 0 });
114 sub action_turnover_by_month {
118 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
120 my $dbh = SL::DB->client->dbh;
121 my $cv = $::form->{id};
123 if ($::form->{db} eq 'customer') {
125 $cv_type = "customer_id";
128 $cv_type = "vendor_id";
131 SELECT CONCAT(EXTRACT (MONTH FROM transdate),'/',EXTRACT (YEAR FROM transdate)) as date_part,
133 sum(amount) as amount,
134 sum(netamount) as netamount,
136 FROM $db WHERE $cv_type = ?
137 GROUP BY EXTRACT (YEAR FROM transdate), EXTRACT (MONTH FROM transdate)
138 ORDER BY EXTRACT (YEAR FROM transdate) DESC, EXTRACT (MONTH FROM transdate) DESC
140 $self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query, $cv);
141 $self->render('customer_vendor_turnover/count_turnover', { layout => 0 });
144 sub action_turnover_by_year {
147 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
149 my $dbh = SL::DB->client->dbh;
150 my $cv = $::form->{id};
152 if ($::form->{db} eq 'customer') {
154 $cv_type = "customer_id";
157 $cv_type = "vendor_id";
160 SELECT EXTRACT (YEAR FROM transdate) as date_part,
162 sum(amount) as amount,
163 sum(netamount) as netamount,
165 FROM $db WHERE $cv_type = ?
167 ORDER BY date_part DESC
169 $self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query, $cv);
170 $self->render('customer_vendor_turnover/count_turnover', { layout => 0 });
173 sub action_get_invoices {
176 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
178 my $cv = $::form->{id};
180 if ( $::form->{db} eq 'customer' ) {
181 $invoices = SL::DB::Manager::Invoice->get_all(
182 query => [ customer_id => $cv, ],
183 sort_by => 'invnumber DESC',
186 $invoices = SL::DB::Manager::PurchaseInvoice->get_all(
187 query => [ vendor_id => $cv, ],
188 sort_by => 'invnumber DESC',
191 $self->render('customer_vendor_turnover/invoices_statistic', { layout => 0 }, invoices => $invoices);
194 sub action_get_orders {
197 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
199 my $cv = $::form->{id};
201 my $type = $::form->{type};
202 if ( $::form->{db} eq 'customer' ) {
203 $orders = SL::DB::Manager::Order->get_all(
206 quotation => ($type eq 'quotation' ? 'T' : 'F')
208 sort_by => ( $type eq 'order' ? 'ordnumber DESC' : 'quonumber DESC'),
211 $orders = SL::DB::Manager::Order->get_all(
214 quotation => ($type eq 'quotation' ? 'T' : 'F')
216 sort_by => ( $type eq 'order' ? 'ordnumber DESC' : 'quonumber DESC'),
219 if ( $type eq 'order') {
220 $self->render('customer_vendor_turnover/order_statistic', { layout => 0 }, orders => $orders);
222 $self->render('customer_vendor_turnover/quotation_statistic', { layout => 0 }, orders => $orders);
226 sub _get_open_orders {
229 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
231 my $cv = $::form->{id};
233 if ( $::form->{db} eq 'customer' ) {
234 $open_orders = SL::DB::Manager::Order->get_all(
239 sort_by => 'ordnumber DESC',
242 $open_orders = SL::DB::Manager::Order->get_all(
247 sort_by => 'ordnumber DESC',
251 return 0 unless scalar @{$open_orders};
252 return $self->render('customer_vendor_turnover/_list_open_orders', { output => 0 },
253 orders => $open_orders,
254 title => $::locale->text('Open Orders'),
258 sub action_get_mails {
261 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
262 my $dbh = SL::DB->client->dbh;
264 my $cv = $::form->{id};
266 if ( $::form->{db} eq 'customer') {
270 AS (SELECT rc.to_id, rc.from_id, oe.quotation, oe.quonumber, oe.ordnumber, c.id
272 LEFT JOIN oe oe ON rc.from_id = oe.id
273 LEFT JOIN customer c ON oe.customer_id = c.id
274 WHERE rc.to_table = 'email_journal'
275 AND rc.from_table ='oe'),
278 AS (SELECT rc.to_id, rc.from_id, o.donumber, c.id
280 LEFT JOIN delivery_orders o ON rc.from_id = o.id
281 LEFT JOIN customer c ON o.customer_id = c.id
282 WHERE rc.to_table = 'email_journal'
283 AND rc.from_table = 'delivery_orders'),
286 AS (SELECT rc.to_id, rc.from_id, inv.type, inv.invnumber, c.id
288 LEFT JOIN ar inv ON rc.from_id = inv.id
289 LEFT JOIN customer c ON inv.customer_id = c.id
290 WHERE rc.to_table = 'email_journal'
291 AND rc.from_table = 'ar'),
293 letter_emails_customer
294 AS (SELECT rc.to_id, rc.from_id, l.letternumber, c.id
296 LEFT JOIN letter l ON rc.from_id = l.id
297 LEFT JOIN customer c ON l.customer_id = c.id
298 WHERE rc.to_table = 'email_journal'
299 AND rc.from_table = 'letter')
303 oec.quotation WHEN 'F' THEN 'Sales Order'
307 oec.quotation WHEN 'F' THEN oec.ordnumber
311 FROM email_journal ej
312 LEFT JOIN oe_emails_customer oec ON ej.id = oec.to_id
317 SELECT ej.*, 'Delivery Order' AS type, dec.donumber AS recordnumber,dec.id AS record_id
318 FROM email_journal ej
319 LEFT JOIN do_emails_customer dec ON ej.id = dec.to_id
326 iec.type WHEN 'credit_note' THEN 'Credit Note'
327 WHEN 'invoice' THEN 'Invoice'
330 iec.invnumber AS recordnumber,
332 FROM email_journal ej
333 LEFT JOIN inv_emails_customer iec ON ej.id = iec.to_id
338 SELECT ej.*, 'Letter' AS type, lec.letternumber AS recordnumber,lec.id AS record_id
339 FROM email_journal ej
340 LEFT JOIN letter_emails_customer lec ON ej.id = lec.to_id
342 ORDER BY sent_on DESC
349 AS (SELECT rc.to_id, rc.from_id, oe.quotation, oe.quonumber, oe.ordnumber, c.id
351 LEFT JOIN oe oe ON rc.from_id = oe.id
352 LEFT JOIN vendor c ON oe.vendor_id = c.id
353 WHERE rc.to_table = 'email_journal'
354 AND rc.from_table ='oe'),
357 AS (SELECT rc.to_id, rc.from_id, o.donumber, c.id
359 LEFT JOIN delivery_orders o ON rc.from_id = o.id
360 LEFT JOIN vendor c ON o.vendor_id = c.id
361 WHERE rc.to_table = 'email_journal'
362 AND rc.from_table = 'delivery_orders'),
365 AS (SELECT rc.to_id, rc.from_id, inv.type, inv.invnumber, c.id
367 LEFT JOIN ap inv ON rc.from_id = inv.id
368 LEFT JOIN vendor c ON inv.vendor_id = c.id
369 WHERE rc.to_table = 'email_journal'
370 AND rc.from_table = 'ar'),
373 AS (SELECT rc.to_id, rc.from_id, l.letternumber, c.id
375 LEFT JOIN letter l ON rc.from_id = l.id
376 LEFT JOIN vendor c ON l.vendor_id = c.id
377 WHERE rc.to_table = 'email_journal'
378 AND rc.from_table = 'letter')
382 oec.quotation WHEN 'F' THEN 'Purchase Order'
383 ELSE 'Request quotation'
386 oec.quotation WHEN 'F' THEN oec.ordnumber
390 FROM email_journal ej
391 LEFT JOIN oe_emails_vendor oec ON ej.id = oec.to_id
396 SELECT ej.*, 'Purchase Delivery Order' AS type, dec.donumber AS recordnumber, dec.id AS record_id
397 FROM email_journal ej
398 LEFT JOIN do_emails_vendor dec ON ej.id = dec.to_id
403 SELECT ej.*, iec.type AS type, iec.invnumber AS recordnumber, iec.id AS record_id
404 FROM email_journal ej
405 LEFT JOIN inv_emails_vendor iec ON ej.id = iec.to_id
410 SELECT ej.*, 'Letter' AS type, lec.letternumber AS recordnumber, lec.id AS record_id
411 FROM email_journal ej
412 LEFT JOIN letter_emails_vendor lec ON ej.id = lec.to_id
414 ORDER BY sent_on DESC
417 my $emails = selectall_hashref_query($::form, $dbh, $query, $cv, $cv, $cv, $cv);
418 $self->render('customer_vendor_turnover/email_statistic', { layout => 0 }, emails => $emails);
421 sub action_get_letters {
424 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
426 my $cv = $::form->{id};
428 my $type = $::form->{type};
429 if ( $::form->{db} eq 'customer' ) {
430 $letters = SL::DB::Manager::Letter->get_all(
431 query => [ customer_id => $cv, ],
432 sort_by => 'date DESC',
435 $letters = SL::DB::Manager::Letter->get_all(
436 query => [ vendor_id => $cv, ],
437 sort_by => 'date DESC',
440 $self->render('customer_vendor_turnover/letter_statistic', { layout => 0 }, letters => $letters);
451 SL::Controller::CustomerVendorTurnover
455 Gets all kinds of records like orders, request orders, quotations, invoices, emails, letters
457 wich belong to customer/vendor and displays them in an extra tab "Records".
465 =item C<action_list_turnover>
467 Basic action wich displays open invoices and open orders if there are any and shows the tab menu for the other actions
469 =item C<action_count_open_items_by_month>
471 gets and shows a dunning statistic of the customer by month
473 =item C<action_count_open_items_by_year>
475 gets and shows a dunning statistic of the customer by year
477 =item C<action_turnover_by_month>
479 gets and shows an invoice statistic of customer/vendor by month
481 =item C<action_turnover_by_year>
483 gets and shows an invoice statistic of customer/vendor by year
485 =item C<action_get_invoices>
487 get and shows all invoices from the customer/vendor in an extra tab
489 =item C<action_get_orders>
491 get and shows all orders from the customer/vendor in an extra tab
493 =item C<action_get_letters>
495 get and shows all letters from the customer/vendor in an extra tab
497 =item C<action_get_mails>
499 get and shows all mails from the customer/vendor in an extra tab
507 =item C<_get_open_orders>
509 retrieves the open orders for customer/vendor to display them
511 =item C<_list_open_items>
513 retrieves open invoices with their dunnings to display them
523 W. Hahn E<lt>wh@futureworldsearch.netE<gt>