+
+sub action_get_orders {
+ my ($self) = @_;
+
+ return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
+
+ my $cv = $::form->{id} || {};
+ my $orders;
+ my $type = $::form->{type};
+ if ( $::form->{db} eq 'customer' ) {
+ $orders = SL::DB::Manager::Order->get_all(
+ query => [ customer_id => $cv,
+ quotation => ($type eq 'quotation' ? 'T' : 'F') ],
+ sort_by => ( $type eq 'order' ? 'ordnumber DESC' : 'quonumber DESC'),
+ );
+ } else {
+ $orders = SL::DB::Manager::Order->get_all(
+ query => [ vendor_id => $cv,
+ quotation => ($type eq 'quotation' ? 'T' : 'F') ],
+ sort_by => ( $type eq 'order' ? 'ordnumber DESC' : 'quonumber DESC'),
+ );
+ }
+ if ( $type eq 'order') {
+ $self->render('customer_vendor_turnover/order_statistic', { layout => 0 }, orders => $orders);
+ } else {
+ $self->render('customer_vendor_turnover/quotation_statistic', { layout => 0 }, orders => $orders);
+ }
+}
+
+sub _get_open_orders {
+ my ( $self ) = @_;
+
+ return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
+ my $open_orders;
+ my $cv = $::form->{id} || {};
+
+ if ( $::form->{db} eq 'customer' ) {
+ $open_orders = SL::DB::Manager::Order->get_all(
+ query => [ customer_id => $cv,
+ closed => 'F',
+ ],
+ sort_by => 'ordnumber DESC',
+ );
+ } else {
+ $open_orders = SL::DB::Manager::Order->get_all(
+ query => [ vendor_id => $cv,
+ closed => 'F',
+ ],
+ sort_by => 'ordnumber DESC',
+ );
+ }
+
+ return 0 unless scalar @{$open_orders};
+ return $self->render('customer_vendor_turnover/_list_open_orders', { output => 0 }, orders => $open_orders, title => $::locale->text('Open Orders') );
+}
+
+sub action_get_mails {
+ my ( $self ) = @_;
+
+ my $dbh = SL::DB->client->dbh;
+ my $query;
+ my $cv = $::form->{id};
+
+ if ( $::form->{db} eq 'customer') {
+ $query = <<SQL;
+WITH oe_emails_customer AS (SELECT rc.to_id, rc.from_id, oe.quotation, oe.quonumber, oe.ordnumber, c.id FROM
+record_links rc
+LEFT JOIN oe oe ON rc.from_id = oe.id
+LEFT JOIN customer c ON oe.customer_id = c.id
+WHERE rc.to_table = 'email_journal' AND rc.from_table ='oe'),
+
+do_emails_customer AS (SELECT rc.to_id, rc.from_id, o.donumber, c.id FROM
+record_links rc
+LEFT JOIN delivery_orders o ON rc.from_id = o.id
+LEFT JOIN customer c ON o.customer_id = c.id
+WHERE rc.to_table = 'email_journal' AND rc.from_table = 'delivery_orders'),
+
+inv_emails_customer AS (SELECT rc.to_id, rc.from_id, inv.type, inv.invnumber, c.id FROM
+record_links rc
+LEFT JOIN ar inv ON rc.from_id = inv.id
+LEFT JOIN customer c ON inv.customer_id = c.id
+WHERE rc.to_table = 'email_journal' AND rc.from_table = 'ar'),
+
+letter_emails_customer AS (SELECT rc.to_id, rc.from_id, l.letternumber, c.id FROM
+record_links rc
+LEFT JOIN letter l ON rc.from_id = l.id
+LEFT JOIN customer c ON l.customer_id = c.id
+WHERE rc.to_table = 'email_journal' AND rc.from_table = 'letter')
+
+SELECT ej.*, CASE
+ oec.quotation WHEN 'F' THEN 'Sales Order'
+ ELSE 'Quotation'
+ END AS type,
+ CASE
+ oec.quotation WHEN 'F' THEN oec.ordnumber
+ ELSE oec.quonumber
+ END AS recordnumber,
+oec.id AS record_id FROM email_journal ej
+LEFT JOIN oe_emails_customer oec ON ej.id = oec.to_id
+WHERE oec.id = ?
+
+UNION
+
+SELECT ej.*, 'Delivery Order' AS type, dec.donumber AS recordnumber,dec.id AS record_id FROM email_journal ej
+LEFT JOIN do_emails_customer dec ON ej.id = dec.to_id
+WHERE dec.id = ?
+
+UNION
+
+SELECT ej.*, CASE
+ iec.type WHEN 'credit_note' THEN 'Credit Note'
+ WHEN 'invoice' THEN 'Invoice'
+ ELSE 'N/A'
+ END AS type,
+iec.invnumber AS recordnumber,iec.id AS record_id FROM email_journal ej
+LEFT JOIN inv_emails_customer iec ON ej.id = iec.to_id
+WHERE iec.id = ?
+
+UNION
+
+SELECT ej.*, 'Letter' AS type, lec.letternumber AS recordnumber,lec.id AS record_id FROM email_journal ej
+LEFT JOIN letter_emails_customer lec ON ej.id = lec.to_id
+WHERE lec.id = ?
+ORDER BY sent_on DESC
+SQL
+ }
+ else {
+ $query = <<SQL;
+WITH oe_emails_vendor AS (SELECT rc.to_id, rc.from_id, oe.quotation, oe.quonumber, oe.ordnumber, c.id FROM
+record_links rc
+LEFT JOIN oe oe ON rc.from_id = oe.id
+LEFT JOIN vendor c ON oe.vendor_id = c.id
+WHERE rc.to_table = 'email_journal' AND rc.from_table ='oe'),
+
+do_emails_vendor AS (SELECT rc.to_id, rc.from_id, o.donumber, c.id FROM
+record_links rc
+LEFT JOIN delivery_orders o ON rc.from_id = o.id
+LEFT JOIN vendor c ON o.vendor_id = c.id
+WHERE rc.to_table = 'email_journal' AND rc.from_table = 'delivery_orders'),
+
+inv_emails_vendor AS (SELECT rc.to_id, rc.from_id, inv.type, inv.invnumber, c.id FROM
+record_links rc
+LEFT JOIN ap inv ON rc.from_id = inv.id
+LEFT JOIN vendor c ON inv.vendor_id = c.id
+WHERE rc.to_table = 'email_journal' AND rc.from_table = 'ar'),
+
+letter_emails_vendor AS (SELECT rc.to_id, rc.from_id, l.letternumber, c.id FROM
+record_links rc
+LEFT JOIN letter l ON rc.from_id = l.id
+LEFT JOIN vendor c ON l.vendor_id = c.id
+WHERE rc.to_table = 'email_journal' AND rc.from_table = 'letter')
+
+SELECT ej.*, CASE
+ oec.quotation WHEN 'F' THEN 'Purchase Order'
+ ELSE 'Request quotation'
+ END AS type,
+ CASE
+ oec.quotation WHEN 'F' THEN oec.ordnumber
+ ELSE oec.quonumber
+ END AS recordnumber,
+oec.id AS record_id FROM email_journal ej
+LEFT JOIN oe_emails_vendor oec ON ej.id = oec.to_id
+WHERE oec.id = ?
+
+UNION
+
+SELECT ej.*, 'Purchase Delivery Order' AS type, dec.donumber AS recordnumber, dec.id AS record_id FROM email_journal ej
+LEFT JOIN do_emails_vendor dec ON ej.id = dec.to_id
+WHERE dec.id = ?
+
+UNION
+
+SELECT ej.*, iec.type AS type, iec.invnumber AS recordnumber, iec.id AS record_id FROM email_journal ej
+LEFT JOIN inv_emails_vendor iec ON ej.id = iec.to_id
+WHERE iec.id = ?
+
+UNION
+
+SELECT ej.*, 'Letter' AS type, lec.letternumber AS recordnumber, lec.id AS record_id FROM email_journal ej
+LEFT JOIN letter_emails_vendor lec ON ej.id = lec.to_id
+WHERE lec.id = ?
+ORDER BY sent_on DESC
+SQL
+ }
+ my $emails = selectall_hashref_query($::form, $dbh, $query, $cv, $cv, $cv, $cv);
+ $self->render('customer_vendor_turnover/email_statistic', { layout => 0 }, emails => $emails);
+}
+
+sub action_get_letters {
+ my ($self) = @_;
+
+ return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
+
+ my $cv = $::form->{id} || {};
+ my $letters;
+ my $type = $::form->{type};
+ if ( $::form->{db} eq 'customer' ) {
+ $letters = SL::DB::Manager::Letter->get_all(
+ query => [ customer_id => $cv, ],
+ sort_by => 'date DESC',
+ );
+ } else {
+ $letters = SL::DB::Manager::Letter->get_all(
+ query => [ vendor_id => $cv, ],
+ sort_by => 'date DESC',
+ );
+ }
+ $self->render('customer_vendor_turnover/letter_statistic', { layout => 0 }, letters => $letters);
+}
+