SL/Controller/CustomerVendorTurnover.pm

   1 package SL::Controller::CustomerVendorTurnover;
   2 use strict;
   3 use parent qw(SL::Controller::Base);
   4 use SL::DBUtils;
   5 use SL::DB::AccTransaction;
   6 use SL::DB::Invoice;
   7
   8 __PACKAGE__->run_before('check_auth');
   9
  10 sub action_list_turnover {
  11   my ($self) = @_;
  12
  13   return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
  14
  15   my $cv = $::form->{id} || {};
  16   my $open_invoices;
  17   $open_invoices = SL::DB::Manager::Invoice->get_all(
  18     query => [customer_id => $cv,
  19               paid => {lt_sql => 'amount'},
  20     ],
  21     with_objects => ['dunnings'],
  22   );
  23   my $open_items;
  24   if (@{$open_invoices}) {
  25     return $self->render(\'', { type => 'json' }) unless scalar @{$open_invoices};
  26     $open_items = $self->_list_open_items($open_invoices);
  27   }
  28   return $self->render('customer_vendor_turnover/turnover', { header => 0 }, open_items => $open_items, id => $cv);
  29 }
  30
  31 sub _list_open_items {
  32   my ($self, $open_items) = @_;
  33
  34   return $self->render('customer_vendor_turnover/_list_open_items', { output => 0 }, OPEN_ITEMS => $open_items, title => $::locale->text('Open Items') );
  35 }
  36
  37 sub action_count_open_items_by_year {
  38   my ($self) = @_;
  39
  40   return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
  41   my $dbh = $::form->get_standard_dbh();
  42
  43   my $cv = $::form->{id} || {};
  44
  45   my $query = "SELECT EXTRACT (YEAR FROM d.transdate),
  46     count(d.id),
  47     max(d.dunning_level)
  48     FROM dunning d
  49     LEFT JOIN ar a
  50     ON a.id = d.trans_id
  51     LEFT JOIN customer c
  52     ON a.customer_id = c.id
  53     WHERE c.id = $cv
  54     GROUP BY EXTRACT (YEAR FROM d.transdate), c.id
  55     ORDER BY date_part DESC";
  56
  57    $self->{dun_statistic} = selectall_hashref_query($::form, $dbh, $query);
  58    $self->render('customer_vendor_turnover/count_open_items_by_year', { layout => 0 });
  59 }
  60 sub action_count_open_items_by_month {
  61
  62   my ($self) = @_;
  63
  64   return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
  65   my $dbh = $::form->get_standard_dbh();
  66
  67   my $cv = $::form->{id} || {};
  68
  69   my $query = "SELECT CONCAT(EXTRACT (MONTH FROM d.transdate),'/',EXTRACT (YEAR FROM d.transdate)) AS date_part,
  70     count(d.id),
  71     max(d.dunning_level)
  72     FROM dunning d
  73     LEFT JOIN ar a
  74     ON a.id = d.trans_id
  75     LEFT JOIN customer c
  76     ON a.customer_id = c.id
  77     WHERE c.id = $cv
  78     GROUP BY EXTRACT (YEAR FROM d.transdate), EXTRACT (MONTH FROM d.transdate), c.id
  79     ORDER BY EXTRACT (YEAR FROM d.transdate) DESC";
  80
  81    $self->{dun_statistic} = selectall_hashref_query($::form, $dbh, $query);
  82    $self->render('customer_vendor_turnover/count_open_items_by_year', { layout => 0 });
  83 }
  84 sub action_turnover_by_month {
  85
  86   my ($self) = @_;
  87
  88   return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
  89
  90   my $dbh = $::form->get_standard_dbh();
  91   my $cv = $::form->{id} || {};
  92   my $query = "SELECT CONCAT(EXTRACT (MONTH FROM transdate),'/',EXTRACT (YEAR FROM transdate)) AS date_part,
  93     count(id) as count,
  94     sum(amount) as amount,
  95     sum(netamount) as netamount,
  96     sum(paid) as paid
  97     FROM ar WHERE customer_id = $cv
  98     GROUP BY EXTRACT (YEAR FROM transdate), EXTRACT (MONTH FROM transdate)
  99     ORDER BY EXTRACT (YEAR FROM transdate) DESC, EXTRACT (MONTH FROM transdate) DESC";
 100
 101    $self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query);
 102    $self->render('customer_vendor_turnover/count_turnover', { layout => 0 });
 103 }
 104 sub action_turnover_by_year {
 105
 106   my ($self) = @_;
 107
 108   return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
 109
 110   my $dbh = $::form->get_standard_dbh();
 111   my $cv = $::form->{id} || {};
 112   my $query = "SELECT EXTRACT (YEAR FROM transdate) as date_part,
 113     count(id) as count,
 114     sum(amount) as amount,
 115     sum(netamount) as netamount,
 116     sum(paid) as paid
 117     FROM ar WHERE customer_id = $cv
 118     GROUP BY date_part
 119     ORDER BY date_part DESC";
 120
 121    $self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query);
 122    $self->render('customer_vendor_turnover/count_turnover', { layout => 0 });
 123 }
 124 sub action_get_invoices {
 125   my ($self) = @_;
 126
 127   return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
 128
 129   my $cv = $::form->{id} || {};
 130   my $invoices = SL::DB::Manager::Invoice->get_all(
 131     query => [ customer_id => $cv, ],
 132     sort_by => 'invnumber DESC',
 133   );
 134   $self->render('customer_vendor_turnover/invoices_statistic', { layout => 0 }, invoices => $invoices);
 135 }
 136 sub _list_articles_by_invoice {
 137 }
 138 sub _list_count_articles_by_year {
 139 }
 140 sub check_auth {
 141   $::auth->assert('general_ledger');
 142 }
 143 1;