1 package SL::Controller::CustomerVendorTurnover;
3 use parent qw(SL::Controller::Base);
5 use SL::DB::AccTransaction;
10 __PACKAGE__->run_before('check_auth');
12 sub action_list_turnover {
15 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
17 my $cv = $::form->{id} || {};
19 if ( $::form->{db} eq 'customer' ) {
20 $open_invoices = SL::DB::Manager::Invoice->get_all(
21 query => [customer_id => $cv,
23 amount => { gt => \'paid'},
24 amount => { lt => \'paid'},
27 with_objects => ['dunnings'],
30 $open_invoices = SL::DB::Manager::PurchaseInvoice->get_all(
31 query => [ vendor_id => $cv,
33 amount => { gt => \'paid'},
34 amount => { lt => \'paid'},
37 sort_by => 'invnumber DESC',
41 if (@{$open_invoices}) {
42 return $self->render(\'', { type => 'json' }) unless scalar @{$open_invoices};
43 $open_items = $self->_list_open_items($open_invoices);
45 my $open_orders = $self->_get_open_orders();
46 return $self->render('customer_vendor_turnover/turnover', { header => 0 }, open_orders => $open_orders, open_items => $open_items, id => $cv);
49 sub _list_open_items {
50 my ($self, $open_items) = @_;
52 return $self->render('customer_vendor_turnover/_list_open_items', { output => 0 }, OPEN_ITEMS => $open_items, title => $::locale->text('Open Items') );
55 sub action_count_open_items_by_year {
58 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
59 my $dbh = SL::DB->client->dbh;
61 my $cv = $::form->{id} || {};
63 my $query = "SELECT EXTRACT (YEAR FROM d.transdate),
70 ON a.customer_id = c.id
72 GROUP BY EXTRACT (YEAR FROM d.transdate), c.id
73 ORDER BY date_part DESC";
75 $self->{dun_statistic} = selectall_hashref_query($::form, $dbh, $query);
76 $self->render('customer_vendor_turnover/count_open_items_by_year', { layout => 0 });
78 sub action_count_open_items_by_month {
82 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
83 my $dbh = SL::DB->client->dbh;
85 my $cv = $::form->{id} || {};
87 my $query = "SELECT CONCAT(EXTRACT (MONTH FROM d.transdate),'/',EXTRACT (YEAR FROM d.transdate)) AS date_part,
94 ON a.customer_id = c.id
96 GROUP BY EXTRACT (YEAR FROM d.transdate), EXTRACT (MONTH FROM d.transdate), c.id
97 ORDER BY EXTRACT (YEAR FROM d.transdate) DESC";
99 $self->{dun_statistic} = selectall_hashref_query($::form, $dbh, $query);
100 $self->render('customer_vendor_turnover/count_open_items_by_year', { layout => 0 });
102 sub action_turnover_by_month {
106 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
108 my $dbh = SL::DB->client->dbh;
109 my $cv = $::form->{id} || {};
111 if ($::form->{db} eq 'customer') {
113 $cv_type = "customer_id";
116 $cv_type = "vendor_id";
119 SELECT CONCAT(EXTRACT (MONTH FROM transdate),'/',EXTRACT (YEAR FROM transdate)) AS date_part,
121 sum(amount) as amount,
122 sum(netamount) as netamount,
124 FROM $db WHERE $cv_type = $cv
125 GROUP BY EXTRACT (YEAR FROM transdate), EXTRACT (MONTH FROM transdate)
126 ORDER BY EXTRACT (YEAR FROM transdate) DESC, EXTRACT (MONTH FROM transdate) DESC
128 $self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query);
129 $self->render('customer_vendor_turnover/count_turnover', { layout => 0 });
131 sub action_turnover_by_year {
134 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
136 my $dbh = SL::DB->client->dbh;
137 my $cv = $::form->{id} || {};
139 if ($::form->{db} eq 'customer') {
141 $cv_type = "customer_id";
144 $cv_type = "vendor_id";
147 SELECT EXTRACT (YEAR FROM transdate) as date_part,
149 sum(amount) as amount,
150 sum(netamount) as netamount,
152 FROM $db WHERE $cv_type = $cv
154 ORDER BY date_part DESC
156 $self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query);
157 $self->render('customer_vendor_turnover/count_turnover', { layout => 0 });
160 sub action_get_invoices {
163 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
165 my $cv = $::form->{id} || {};
167 if ( $::form->{db} eq 'customer' ) {
168 $invoices = SL::DB::Manager::Invoice->get_all(
169 query => [ customer_id => $cv, ],
170 sort_by => 'invnumber DESC',
173 $invoices = SL::DB::Manager::PurchaseInvoice->get_all(
174 query => [ vendor_id => $cv, ],
175 sort_by => 'invnumber DESC',
178 $self->render('customer_vendor_turnover/invoices_statistic', { layout => 0 }, invoices => $invoices);
181 sub action_get_orders {
184 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
186 my $cv = $::form->{id} || {};
188 my $type = $::form->{type};
189 if ( $::form->{db} eq 'customer' ) {
190 $orders = SL::DB::Manager::Order->get_all(
191 query => [ customer_id => $cv,
192 quotation => ($type eq 'quotation' ? 'T' : 'F') ],
193 sort_by => ( $type eq 'order' ? 'ordnumber DESC' : 'quonumber DESC'),
196 $orders = SL::DB::Manager::Order->get_all(
197 query => [ vendor_id => $cv,
198 quotation => ($type eq 'quotation' ? 'T' : 'F') ],
199 sort_by => ( $type eq 'order' ? 'ordnumber DESC' : 'quonumber DESC'),
202 if ( $type eq 'order') {
203 $self->render('customer_vendor_turnover/order_statistic', { layout => 0 }, orders => $orders);
205 $self->render('customer_vendor_turnover/quotation_statistic', { layout => 0 }, orders => $orders);
209 sub _get_open_orders {
212 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
214 my $cv = $::form->{id} || {};
216 if ( $::form->{db} eq 'customer' ) {
217 $open_orders = SL::DB::Manager::Order->get_all(
218 query => [ customer_id => $cv,
221 sort_by => 'ordnumber DESC',
224 $open_orders = SL::DB::Manager::Order->get_all(
225 query => [ vendor_id => $cv,
228 sort_by => 'ordnumber DESC',
232 return 0 unless scalar @{$open_orders};
233 return $self->render('customer_vendor_turnover/_list_open_orders', { output => 0 }, orders => $open_orders, title => $::locale->text('Open Orders') );
236 sub _list_articles_by_invoice {
238 sub _list_count_articles_by_year {
241 $::auth->assert('general_ledger');