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(
22 query => [customer_id => $cv,
24 amount => { gt => \'paid'},
25 amount => { lt => \'paid'},
28 with_objects => ['dunnings'],
31 $open_invoices = SL::DB::Manager::PurchaseInvoice->get_all(
32 query => [ vendor_id => $cv,
34 amount => { gt => \'paid'},
35 amount => { lt => \'paid'},
38 sort_by => 'invnumber DESC',
42 if (@{$open_invoices}) {
43 return $self->render(\'', { type => 'json' }) unless scalar @{$open_invoices};
44 $open_items = $self->_list_open_items($open_invoices);
46 my $open_orders = $self->_get_open_orders();
47 return $self->render('customer_vendor_turnover/turnover', { header => 0 }, open_orders => $open_orders, open_items => $open_items, id => $cv);
50 sub _list_open_items {
51 my ($self, $open_items) = @_;
53 return $self->render('customer_vendor_turnover/_list_open_items', { output => 0 }, OPEN_ITEMS => $open_items, title => $::locale->text('Open Items') );
56 sub action_count_open_items_by_year {
59 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
60 my $dbh = SL::DB->client->dbh;
62 my $cv = $::form->{id} || {};
64 my $query = "SELECT EXTRACT (YEAR FROM d.transdate),
71 ON a.customer_id = c.id
73 GROUP BY EXTRACT (YEAR FROM d.transdate), c.id
74 ORDER BY date_part DESC";
76 $self->{dun_statistic} = selectall_hashref_query($::form, $dbh, $query);
77 $self->render('customer_vendor_turnover/count_open_items_by_year', { layout => 0 });
80 sub action_count_open_items_by_month {
84 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
85 my $dbh = SL::DB->client->dbh;
87 my $cv = $::form->{id} || {};
89 my $query = "SELECT CONCAT(EXTRACT (MONTH FROM d.transdate),'/',EXTRACT (YEAR FROM d.transdate)) AS date_part,
96 ON a.customer_id = c.id
98 GROUP BY EXTRACT (YEAR FROM d.transdate), EXTRACT (MONTH FROM d.transdate), c.id
99 ORDER BY EXTRACT (YEAR FROM d.transdate) DESC";
101 $self->{dun_statistic} = selectall_hashref_query($::form, $dbh, $query);
102 $self->render('customer_vendor_turnover/count_open_items_by_year', { layout => 0 });
105 sub action_turnover_by_month {
109 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
111 my $dbh = SL::DB->client->dbh;
112 my $cv = $::form->{id} || {};
114 if ($::form->{db} eq 'customer') {
116 $cv_type = "customer_id";
119 $cv_type = "vendor_id";
122 SELECT CONCAT(EXTRACT (MONTH FROM transdate),'/',EXTRACT (YEAR FROM transdate)) AS date_part,
124 sum(amount) as amount,
125 sum(netamount) as netamount,
127 FROM $db WHERE $cv_type = $cv
128 GROUP BY EXTRACT (YEAR FROM transdate), EXTRACT (MONTH FROM transdate)
129 ORDER BY EXTRACT (YEAR FROM transdate) DESC, EXTRACT (MONTH FROM transdate) DESC
131 $self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query);
132 $self->render('customer_vendor_turnover/count_turnover', { layout => 0 });
135 sub action_turnover_by_year {
138 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
140 my $dbh = SL::DB->client->dbh;
141 my $cv = $::form->{id} || {};
143 if ($::form->{db} eq 'customer') {
145 $cv_type = "customer_id";
148 $cv_type = "vendor_id";
151 SELECT EXTRACT (YEAR FROM transdate) as date_part,
153 sum(amount) as amount,
154 sum(netamount) as netamount,
156 FROM $db WHERE $cv_type = $cv
158 ORDER BY date_part DESC
160 $self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query);
161 $self->render('customer_vendor_turnover/count_turnover', { layout => 0 });
164 sub action_get_invoices {
167 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
169 my $cv = $::form->{id} || {};
171 if ( $::form->{db} eq 'customer' ) {
172 $invoices = SL::DB::Manager::Invoice->get_all(
173 query => [ customer_id => $cv, ],
174 sort_by => 'invnumber DESC',
177 $invoices = SL::DB::Manager::PurchaseInvoice->get_all(
178 query => [ vendor_id => $cv, ],
179 sort_by => 'invnumber DESC',
182 $self->render('customer_vendor_turnover/invoices_statistic', { layout => 0 }, invoices => $invoices);
185 sub action_get_orders {
188 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
190 my $cv = $::form->{id} || {};
192 my $type = $::form->{type};
193 if ( $::form->{db} eq 'customer' ) {
194 $orders = SL::DB::Manager::Order->get_all(
195 query => [ customer_id => $cv,
196 quotation => ($type eq 'quotation' ? 'T' : 'F') ],
197 sort_by => ( $type eq 'order' ? 'ordnumber DESC' : 'quonumber DESC'),
200 $orders = SL::DB::Manager::Order->get_all(
201 query => [ vendor_id => $cv,
202 quotation => ($type eq 'quotation' ? 'T' : 'F') ],
203 sort_by => ( $type eq 'order' ? 'ordnumber DESC' : 'quonumber DESC'),
206 if ( $type eq 'order') {
207 $self->render('customer_vendor_turnover/order_statistic', { layout => 0 }, orders => $orders);
209 $self->render('customer_vendor_turnover/quotation_statistic', { layout => 0 }, orders => $orders);
213 sub _get_open_orders {
216 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
218 my $cv = $::form->{id} || {};
220 if ( $::form->{db} eq 'customer' ) {
221 $open_orders = SL::DB::Manager::Order->get_all(
222 query => [ customer_id => $cv,
225 sort_by => 'ordnumber DESC',
228 $open_orders = SL::DB::Manager::Order->get_all(
229 query => [ vendor_id => $cv,
232 sort_by => 'ordnumber DESC',
236 return 0 unless scalar @{$open_orders};
237 return $self->render('customer_vendor_turnover/_list_open_orders', { output => 0 }, orders => $open_orders, title => $::locale->text('Open Orders') );
240 sub action_get_mails {
243 my $dbh = SL::DB->client->dbh;
245 my $cv = $::form->{id};
247 if ( $::form->{db} eq 'customer') {
249 WITH oe_emails_customer AS (SELECT rc.to_id, rc.from_id, oe.quotation, oe.quonumber, oe.ordnumber, c.id FROM
251 LEFT JOIN oe oe ON rc.from_id = oe.id
252 LEFT JOIN customer c ON oe.customer_id = c.id
253 WHERE rc.to_table = 'email_journal' AND rc.from_table ='oe'),
255 do_emails_customer AS (SELECT rc.to_id, rc.from_id, o.donumber, c.id FROM
257 LEFT JOIN delivery_orders o ON rc.from_id = o.id
258 LEFT JOIN customer c ON o.customer_id = c.id
259 WHERE rc.to_table = 'email_journal' AND rc.from_table = 'delivery_orders'),
261 inv_emails_customer AS (SELECT rc.to_id, rc.from_id, inv.type, inv.invnumber, c.id FROM
263 LEFT JOIN ar inv ON rc.from_id = inv.id
264 LEFT JOIN customer c ON inv.customer_id = c.id
265 WHERE rc.to_table = 'email_journal' AND rc.from_table = 'ar'),
267 letter_emails_customer AS (SELECT rc.to_id, rc.from_id, l.letternumber, c.id FROM
269 LEFT JOIN letter l ON rc.from_id = l.id
270 LEFT JOIN customer c ON l.customer_id = c.id
271 WHERE rc.to_table = 'email_journal' AND rc.from_table = 'letter')
274 oec.quotation WHEN 'F' THEN 'Sales Order'
278 oec.quotation WHEN 'F' THEN oec.ordnumber
281 oec.id AS record_id FROM email_journal ej
282 LEFT JOIN oe_emails_customer oec ON ej.id = oec.to_id
287 SELECT ej.*, 'Delivery Order' AS type, dec.donumber AS recordnumber,dec.id AS record_id FROM email_journal ej
288 LEFT JOIN do_emails_customer dec ON ej.id = dec.to_id
294 iec.type WHEN 'credit_note' THEN 'Credit Note'
295 WHEN 'invoice' THEN 'Invoice'
298 iec.invnumber AS recordnumber,iec.id AS record_id FROM email_journal ej
299 LEFT JOIN inv_emails_customer iec ON ej.id = iec.to_id
304 SELECT ej.*, 'Letter' AS type, lec.letternumber AS recordnumber,lec.id AS record_id FROM email_journal ej
305 LEFT JOIN letter_emails_customer lec ON ej.id = lec.to_id
307 ORDER BY sent_on DESC
312 WITH oe_emails_vendor AS (SELECT rc.to_id, rc.from_id, oe.quotation, oe.quonumber, oe.ordnumber, c.id FROM
314 LEFT JOIN oe oe ON rc.from_id = oe.id
315 LEFT JOIN vendor c ON oe.vendor_id = c.id
316 WHERE rc.to_table = 'email_journal' AND rc.from_table ='oe'),
318 do_emails_vendor AS (SELECT rc.to_id, rc.from_id, o.donumber, c.id FROM
320 LEFT JOIN delivery_orders o ON rc.from_id = o.id
321 LEFT JOIN vendor c ON o.vendor_id = c.id
322 WHERE rc.to_table = 'email_journal' AND rc.from_table = 'delivery_orders'),
324 inv_emails_vendor AS (SELECT rc.to_id, rc.from_id, inv.type, inv.invnumber, c.id FROM
326 LEFT JOIN ap inv ON rc.from_id = inv.id
327 LEFT JOIN vendor c ON inv.vendor_id = c.id
328 WHERE rc.to_table = 'email_journal' AND rc.from_table = 'ar'),
330 letter_emails_vendor AS (SELECT rc.to_id, rc.from_id, l.letternumber, c.id FROM
332 LEFT JOIN letter l ON rc.from_id = l.id
333 LEFT JOIN vendor c ON l.vendor_id = c.id
334 WHERE rc.to_table = 'email_journal' AND rc.from_table = 'letter')
337 oec.quotation WHEN 'F' THEN 'Purchase Order'
338 ELSE 'Request quotation'
341 oec.quotation WHEN 'F' THEN oec.ordnumber
344 oec.id AS record_id FROM email_journal ej
345 LEFT JOIN oe_emails_vendor oec ON ej.id = oec.to_id
350 SELECT ej.*, 'Purchase Delivery Order' AS type, dec.donumber AS recordnumber, dec.id AS record_id FROM email_journal ej
351 LEFT JOIN do_emails_vendor dec ON ej.id = dec.to_id
356 SELECT ej.*, iec.type AS type, iec.invnumber AS recordnumber, iec.id AS record_id FROM email_journal ej
357 LEFT JOIN inv_emails_vendor iec ON ej.id = iec.to_id
362 SELECT ej.*, 'Letter' AS type, lec.letternumber AS recordnumber, lec.id AS record_id FROM email_journal ej
363 LEFT JOIN letter_emails_vendor lec ON ej.id = lec.to_id
365 ORDER BY sent_on DESC
368 my $emails = selectall_hashref_query($::form, $dbh, $query, $cv, $cv, $cv, $cv);
369 $self->render('customer_vendor_turnover/email_statistic', { layout => 0 }, emails => $emails);
372 sub action_get_letters {
375 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
377 my $cv = $::form->{id} || {};
379 my $type = $::form->{type};
380 if ( $::form->{db} eq 'customer' ) {
381 $letters = SL::DB::Manager::Letter->get_all(
382 query => [ customer_id => $cv, ],
383 sort_by => 'date DESC',
386 $letters = SL::DB::Manager::Letter->get_all(
387 query => [ vendor_id => $cv, ],
388 sort_by => 'date DESC',
391 $self->render('customer_vendor_turnover/letter_statistic', { layout => 0 }, letters => $letters);
402 SL::Controller::CustomerVendorTurnover
406 Gets all kinds of records like orders, request orders, quotations, invoices, emails, letters
408 wich belong to customer/vendor and displays them in an extra tab "Records".
414 =item C<action_list_turnover>
416 Basic action wich displays open invoices and open orders if there are any and shows the tab menu for the other actions
418 =item C<action_count_open_items_by_month>
420 gets and shows a dunning statistic of the customer by month
422 =item C<action_count_open_items_by_year>
424 gets and shows a dunning statistic of the customer by year
426 =item C<action_turnover_by_month>
428 gets and shows an invoice statistic of customer/vendor by month
430 =item C<action_turnover_by_year>
432 gets and shows an invoice statistic of customer/vendor by year
434 =item C<action_get_invoices>
436 get and shows all invoices from the customer/vendor in an extra tab
438 =item C<action_get_orders>
440 get and shows all orders from the customer/vendor in an extra tab
442 =item C<action_get_letters>
444 get and shows all letters from the customer/vendor in an extra tab
446 =item C<action_get_mails>
448 get and shows all mails from the customer/vendor in an extra tab
456 =item C<_get_open_orders>
458 retrieves the open orders for customer/vendor to display them
460 =item C<_list_open_items>
462 retrieves open invoices with their dunnings to display them
472 W. Hahn E<lt>wh@futureworldsearch.netE<gt>