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