1 package SL::Controller::CustomerVendorTurnover;
3 use parent qw(SL::Controller::Base);
5 use SL::DB::AccTransaction;
8 use SL::DB::EmailJournal;
12 __PACKAGE__->run_before('check_auth');
14 sub action_list_turnover {
17 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
19 my $cv = $::form->{id} || {};
21 if ( $::form->{db} eq 'customer' ) {
22 $open_invoices = SL::DB::Manager::Invoice->get_all(
23 query => [customer_id => $cv,
25 amount => { gt => \'paid'},
26 amount => { lt => \'paid'},
29 with_objects => ['dunnings'],
32 $open_invoices = SL::DB::Manager::PurchaseInvoice->get_all(
33 query => [ vendor_id => $cv,
35 amount => { gt => \'paid'},
36 amount => { lt => \'paid'},
39 sort_by => 'invnumber DESC',
43 if (@{$open_invoices}) {
44 return $self->render(\'', { type => 'json' }) unless scalar @{$open_invoices};
45 $open_items = $self->_list_open_items($open_invoices);
47 my $open_orders = $self->_get_open_orders();
48 return $self->render('customer_vendor_turnover/turnover', { header => 0 }, open_orders => $open_orders, open_items => $open_items, id => $cv);
51 sub _list_open_items {
52 my ($self, $open_items) = @_;
54 return $self->render('customer_vendor_turnover/_list_open_items', { output => 0 }, OPEN_ITEMS => $open_items, title => $::locale->text('Open Items') );
57 sub action_count_open_items_by_year {
60 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
61 my $dbh = SL::DB->client->dbh;
63 my $cv = $::form->{id} || {};
65 my $query = "SELECT EXTRACT (YEAR FROM d.transdate),
72 ON a.customer_id = c.id
74 GROUP BY EXTRACT (YEAR FROM d.transdate), c.id
75 ORDER BY date_part DESC";
77 $self->{dun_statistic} = selectall_hashref_query($::form, $dbh, $query);
78 $self->render('customer_vendor_turnover/count_open_items_by_year', { layout => 0 });
81 sub action_count_open_items_by_month {
85 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
86 my $dbh = SL::DB->client->dbh;
88 my $cv = $::form->{id} || {};
90 my $query = "SELECT CONCAT(EXTRACT (MONTH FROM d.transdate),'/',EXTRACT (YEAR FROM d.transdate)) AS date_part,
97 ON a.customer_id = c.id
99 GROUP BY EXTRACT (YEAR FROM d.transdate), EXTRACT (MONTH FROM d.transdate), c.id
100 ORDER BY EXTRACT (YEAR FROM d.transdate) DESC";
102 $self->{dun_statistic} = selectall_hashref_query($::form, $dbh, $query);
103 $self->render('customer_vendor_turnover/count_open_items_by_year', { layout => 0 });
106 sub action_turnover_by_month {
110 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
112 my $dbh = SL::DB->client->dbh;
113 my $cv = $::form->{id} || {};
115 if ($::form->{db} eq 'customer') {
117 $cv_type = "customer_id";
120 $cv_type = "vendor_id";
123 SELECT CONCAT(EXTRACT (MONTH FROM transdate),'/',EXTRACT (YEAR FROM transdate)) AS date_part,
125 sum(amount) as amount,
126 sum(netamount) as netamount,
128 FROM $db WHERE $cv_type = $cv
129 GROUP BY EXTRACT (YEAR FROM transdate), EXTRACT (MONTH FROM transdate)
130 ORDER BY EXTRACT (YEAR FROM transdate) DESC, EXTRACT (MONTH FROM transdate) DESC
132 $self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query);
133 $self->render('customer_vendor_turnover/count_turnover', { layout => 0 });
136 sub action_turnover_by_year {
139 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
141 my $dbh = SL::DB->client->dbh;
142 my $cv = $::form->{id} || {};
144 if ($::form->{db} eq 'customer') {
146 $cv_type = "customer_id";
149 $cv_type = "vendor_id";
152 SELECT EXTRACT (YEAR FROM transdate) as date_part,
154 sum(amount) as amount,
155 sum(netamount) as netamount,
157 FROM $db WHERE $cv_type = $cv
159 ORDER BY date_part DESC
161 $self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query);
162 $self->render('customer_vendor_turnover/count_turnover', { layout => 0 });
165 sub action_get_invoices {
168 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
170 my $cv = $::form->{id} || {};
172 if ( $::form->{db} eq 'customer' ) {
173 $invoices = SL::DB::Manager::Invoice->get_all(
174 query => [ customer_id => $cv, ],
175 sort_by => 'invnumber DESC',
178 $invoices = SL::DB::Manager::PurchaseInvoice->get_all(
179 query => [ vendor_id => $cv, ],
180 sort_by => 'invnumber DESC',
183 $self->render('customer_vendor_turnover/invoices_statistic', { layout => 0 }, invoices => $invoices);
186 sub action_get_orders {
189 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
191 my $cv = $::form->{id} || {};
193 my $type = $::form->{type};
194 if ( $::form->{db} eq 'customer' ) {
195 $orders = SL::DB::Manager::Order->get_all(
196 query => [ customer_id => $cv,
197 quotation => ($type eq 'quotation' ? 'T' : 'F') ],
198 sort_by => ( $type eq 'order' ? 'ordnumber DESC' : 'quonumber DESC'),
201 $orders = SL::DB::Manager::Order->get_all(
202 query => [ vendor_id => $cv,
203 quotation => ($type eq 'quotation' ? 'T' : 'F') ],
204 sort_by => ( $type eq 'order' ? 'ordnumber DESC' : 'quonumber DESC'),
207 if ( $type eq 'order') {
208 $self->render('customer_vendor_turnover/order_statistic', { layout => 0 }, orders => $orders);
210 $self->render('customer_vendor_turnover/quotation_statistic', { layout => 0 }, orders => $orders);
214 sub _get_open_orders {
217 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
219 my $cv = $::form->{id} || {};
221 if ( $::form->{db} eq 'customer' ) {
222 $open_orders = SL::DB::Manager::Order->get_all(
223 query => [ customer_id => $cv,
226 sort_by => 'ordnumber DESC',
229 $open_orders = SL::DB::Manager::Order->get_all(
230 query => [ vendor_id => $cv,
233 sort_by => 'ordnumber DESC',
237 return 0 unless scalar @{$open_orders};
238 return $self->render('customer_vendor_turnover/_list_open_orders', { output => 0 }, orders => $open_orders, title => $::locale->text('Open Orders') );
241 sub action_get_mails {
244 my $dbh = SL::DB->client->dbh;
246 my $cv = $::form->{id};
248 if ( $::form->{db} eq 'customer') {
250 WITH oe_emails_customer AS (SELECT rc.to_id, rc.from_id, oe.quotation, oe.quonumber, oe.ordnumber, c.id FROM
252 LEFT JOIN oe oe ON rc.from_id = oe.id
253 LEFT JOIN customer c ON oe.customer_id = c.id
254 WHERE rc.to_table = 'email_journal' AND rc.from_table ='oe'),
256 do_emails_customer AS (SELECT rc.to_id, rc.from_id, o.donumber, c.id FROM
258 LEFT JOIN delivery_orders o ON rc.from_id = o.id
259 LEFT JOIN customer c ON o.customer_id = c.id
260 WHERE rc.to_table = 'email_journal' AND rc.from_table = 'delivery_orders'),
262 inv_emails_customer AS (SELECT rc.to_id, rc.from_id, inv.type, inv.invnumber, c.id FROM
264 LEFT JOIN ar inv ON rc.from_id = inv.id
265 LEFT JOIN customer c ON inv.customer_id = c.id
266 WHERE rc.to_table = 'email_journal' AND rc.from_table = 'ar'),
268 letter_emails_customer AS (SELECT rc.to_id, rc.from_id, l.letternumber, c.id FROM
270 LEFT JOIN letter l ON rc.from_id = l.id
271 LEFT JOIN customer c ON l.customer_id = c.id
272 WHERE rc.to_table = 'email_journal' AND rc.from_table = 'letter')
275 oec.quotation WHEN 'F' THEN 'Sales Order'
279 oec.quotation WHEN 'F' THEN oec.ordnumber
282 oec.id AS record_id FROM email_journal ej
283 LEFT JOIN oe_emails_customer oec ON ej.id = oec.to_id
288 SELECT ej.*, 'Delivery Order' AS type, dec.donumber AS recordnumber,dec.id AS record_id FROM email_journal ej
289 LEFT JOIN do_emails_customer dec ON ej.id = dec.to_id
295 iec.type WHEN 'credit_note' THEN 'Credit Note'
296 WHEN 'invoice' THEN 'Invoice'
299 iec.invnumber AS recordnumber,iec.id AS record_id FROM email_journal ej
300 LEFT JOIN inv_emails_customer iec ON ej.id = iec.to_id
305 SELECT ej.*, 'Letter' AS type, lec.letternumber AS recordnumber,lec.id AS record_id FROM email_journal ej
306 LEFT JOIN letter_emails_customer lec ON ej.id = lec.to_id
308 ORDER BY sent_on DESC
313 WITH oe_emails_vendor AS (SELECT rc.to_id, rc.from_id, oe.quotation, oe.quonumber, oe.ordnumber, c.id FROM
315 LEFT JOIN oe oe ON rc.from_id = oe.id
316 LEFT JOIN vendor c ON oe.vendor_id = c.id
317 WHERE rc.to_table = 'email_journal' AND rc.from_table ='oe'),
319 do_emails_vendor AS (SELECT rc.to_id, rc.from_id, o.donumber, c.id FROM
321 LEFT JOIN delivery_orders o ON rc.from_id = o.id
322 LEFT JOIN vendor c ON o.vendor_id = c.id
323 WHERE rc.to_table = 'email_journal' AND rc.from_table = 'delivery_orders'),
325 inv_emails_vendor AS (SELECT rc.to_id, rc.from_id, inv.type, inv.invnumber, c.id FROM
327 LEFT JOIN ap inv ON rc.from_id = inv.id
328 LEFT JOIN vendor c ON inv.vendor_id = c.id
329 WHERE rc.to_table = 'email_journal' AND rc.from_table = 'ar'),
331 letter_emails_vendor AS (SELECT rc.to_id, rc.from_id, l.letternumber, c.id FROM
333 LEFT JOIN letter l ON rc.from_id = l.id
334 LEFT JOIN vendor c ON l.vendor_id = c.id
335 WHERE rc.to_table = 'email_journal' AND rc.from_table = 'letter')
338 oec.quotation WHEN 'F' THEN 'Purchase Order'
339 ELSE 'Request quotation'
342 oec.quotation WHEN 'F' THEN oec.ordnumber
345 oec.id AS record_id FROM email_journal ej
346 LEFT JOIN oe_emails_vendor oec ON ej.id = oec.to_id
351 SELECT ej.*, 'Purchase Delivery Order' AS type, dec.donumber AS recordnumber, dec.id AS record_id FROM email_journal ej
352 LEFT JOIN do_emails_vendor dec ON ej.id = dec.to_id
357 SELECT ej.*, iec.type AS type, iec.invnumber AS recordnumber, iec.id AS record_id FROM email_journal ej
358 LEFT JOIN inv_emails_vendor iec ON ej.id = iec.to_id
363 SELECT ej.*, 'Letter' AS type, lec.letternumber AS recordnumber, lec.id AS record_id FROM email_journal ej
364 LEFT JOIN letter_emails_vendor lec ON ej.id = lec.to_id
366 ORDER BY sent_on DESC
369 my $emails = selectall_hashref_query($::form, $dbh, $query, $cv, $cv, $cv, $cv);
370 $self->render('customer_vendor_turnover/email_statistic', { layout => 0 }, emails => $emails);
373 sub action_get_letters {
376 return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
378 my $cv = $::form->{id} || {};
380 my $type = $::form->{type};
381 if ( $::form->{db} eq 'customer' ) {
382 $letters = SL::DB::Manager::Letter->get_all(
383 query => [ customer_id => $cv, ],
384 sort_by => 'date DESC',
387 $letters = SL::DB::Manager::Letter->get_all(
388 query => [ vendor_id => $cv, ],
389 sort_by => 'date DESC',
392 $self->render('customer_vendor_turnover/letter_statistic', { layout => 0 }, letters => $letters);
395 sub _list_articles_by_invoice {
397 sub _list_count_articles_by_year {
400 $::auth->assert('general_ledger');