Revision 2ef19c81
Von Werner Hahn vor etwa 6 Jahren hinzugefügt
| SL/Controller/CustomerVendorTurnover.pm | ||
|---|---|---|
| 15 | 15 |
|
| 16 | 16 |
return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
|
| 17 | 17 |
|
| 18 |
my $cv = $::form->{id} || {};
|
|
| 18 |
my $cv = $::form->{id};
|
|
| 19 | 19 |
my $open_invoices; |
| 20 | 20 |
if ( $::form->{db} eq 'customer' ) {
|
| 21 |
$open_invoices = SL::DB::Manager::Invoice->get_all( |
|
| 22 |
query => [customer_id => $cv, |
|
| 23 |
or => [ |
|
| 24 |
amount => { gt => \'paid'},
|
|
| 25 |
amount => { lt => \'paid'},
|
|
| 26 |
], |
|
| 27 |
], |
|
| 28 |
with_objects => ['dunnings'], |
|
| 29 |
); |
|
| 21 |
$open_invoices = SL::DB::Manager::Invoice->get_all( |
|
| 22 |
query => [ |
|
| 23 |
customer_id => $cv, |
|
| 24 |
or => [ |
|
| 25 |
amount => { gt => \'paid'},
|
|
| 26 |
amount => { lt => \'paid'},
|
|
| 27 |
], |
|
| 28 |
], |
|
| 29 |
with_objects => [ 'dunnings' ], |
|
| 30 |
); |
|
| 30 | 31 |
} else {
|
| 31 | 32 |
$open_invoices = SL::DB::Manager::PurchaseInvoice->get_all( |
| 32 |
query => [ vendor_id => $cv, |
|
| 33 |
or => [ |
|
| 34 |
amount => { gt => \'paid'},
|
|
| 35 |
amount => { lt => \'paid'},
|
|
| 36 |
], |
|
| 37 |
], |
|
| 33 |
query => [ |
|
| 34 |
vendor_id => $cv, |
|
| 35 |
or => [ |
|
| 36 |
amount => { gt => \'paid'},
|
|
| 37 |
amount => { lt => \'paid'},
|
|
| 38 |
], |
|
| 39 |
], |
|
| 38 | 40 |
sort_by => 'invnumber DESC', |
| 39 | 41 |
); |
| 40 | 42 |
} |
| ... | ... | |
| 43 | 45 |
return $self->render(\'', { type => 'json' }) unless scalar @{$open_invoices};
|
| 44 | 46 |
$open_items = $self->_list_open_items($open_invoices); |
| 45 | 47 |
} |
| 46 |
my $open_orders = $self->_get_open_orders(); |
|
| 47 |
return $self->render('customer_vendor_turnover/turnover', { header => 0 }, open_orders => $open_orders, open_items => $open_items, id => $cv);
|
|
| 48 |
my $open_orders = $self->_get_open_orders; |
|
| 49 |
return $self->render('customer_vendor_turnover/turnover', { header => 0 },
|
|
| 50 |
open_orders => $open_orders, |
|
| 51 |
open_items => $open_items, |
|
| 52 |
id => $cv, |
|
| 53 |
); |
|
| 48 | 54 |
} |
| 49 | 55 |
|
| 50 | 56 |
sub _list_open_items {
|
| 51 | 57 |
my ($self, $open_items) = @_; |
| 52 | 58 |
|
| 53 |
return $self->render('customer_vendor_turnover/_list_open_items', { output => 0 }, OPEN_ITEMS => $open_items, title => $::locale->text('Open Items') );
|
|
| 59 |
return $self->render('customer_vendor_turnover/_list_open_items', { output => 0 },
|
|
| 60 |
OPEN_ITEMS => $open_items, |
|
| 61 |
title => $::locale->text('Open Items'),
|
|
| 62 |
); |
|
| 54 | 63 |
} |
| 55 | 64 |
|
| 56 | 65 |
sub action_count_open_items_by_year {
|
| ... | ... | |
| 59 | 68 |
return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
|
| 60 | 69 |
my $dbh = SL::DB->client->dbh; |
| 61 | 70 |
|
| 62 |
my $cv = $::form->{id} || {};
|
|
| 63 |
|
|
| 64 |
my $query = "SELECT EXTRACT (YEAR FROM d.transdate),
|
|
| 65 |
count(d.id),
|
|
| 66 |
max(d.dunning_level)
|
|
| 67 |
FROM dunning d
|
|
| 68 |
LEFT JOIN ar a
|
|
| 69 |
ON a.id = d.trans_id
|
|
| 70 |
LEFT JOIN customer c
|
|
| 71 |
ON a.customer_id = c.id
|
|
| 72 |
WHERE c.id = $cv
|
|
| 73 |
GROUP BY EXTRACT (YEAR FROM d.transdate), c.id
|
|
| 74 |
ORDER BY date_part DESC";
|
|
| 75 |
|
|
| 76 |
$self->{dun_statistic} = selectall_hashref_query($::form, $dbh, $query);
|
|
| 77 |
$self->render('customer_vendor_turnover/count_open_items_by_year', { layout => 0 });
|
|
| 71 |
my $cv = $::form->{id};
|
|
| 72 |
|
|
| 73 |
my $query = <<SQL;
|
|
| 74 |
SELECT EXTRACT (YEAR FROM d.transdate),
|
|
| 75 |
count(d.id),
|
|
| 76 |
max(d.dunning_level)
|
|
| 77 |
FROM dunning d
|
|
| 78 |
LEFT JOIN ar a ON a.id = d.trans_id
|
|
| 79 |
LEFT JOIN customer c ON a.customer_id = c.id
|
|
| 80 |
WHERE c.id = ?
|
|
| 81 |
GROUP BY EXTRACT (YEAR FROM d.transdate), c.id
|
|
| 82 |
ORDER BY date_part DESC
|
|
| 83 |
SQL
|
|
| 84 |
|
|
| 85 |
$self->{dun_statistic} = selectall_hashref_query($::form, $dbh, $query, $cv);
|
|
| 86 |
$self->render('customer_vendor_turnover/count_open_items_by_year', { layout => 0 });
|
|
| 78 | 87 |
} |
| 79 | 88 |
|
| 80 | 89 |
sub action_count_open_items_by_month {
|
| ... | ... | |
| 84 | 93 |
return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
|
| 85 | 94 |
my $dbh = SL::DB->client->dbh; |
| 86 | 95 |
|
| 87 |
my $cv = $::form->{id} || {};
|
|
| 88 |
|
|
| 89 |
my $query = "SELECT CONCAT(EXTRACT (MONTH FROM d.transdate),'/',EXTRACT (YEAR FROM d.transdate)) AS date_part,
|
|
| 90 |
count(d.id),
|
|
| 91 |
max(d.dunning_level)
|
|
| 92 |
FROM dunning d
|
|
| 93 |
LEFT JOIN ar a
|
|
| 94 |
ON a.id = d.trans_id
|
|
| 95 |
LEFT JOIN customer c
|
|
| 96 |
ON a.customer_id = c.id
|
|
| 97 |
WHERE c.id = $cv
|
|
| 98 |
GROUP BY EXTRACT (YEAR FROM d.transdate), EXTRACT (MONTH FROM d.transdate), c.id
|
|
| 99 |
ORDER BY EXTRACT (YEAR FROM d.transdate) DESC";
|
|
| 100 |
|
|
| 101 |
$self->{dun_statistic} = selectall_hashref_query($::form, $dbh, $query);
|
|
| 96 |
my $cv = $::form->{id};
|
|
| 97 |
|
|
| 98 |
my $query = <<SQL
|
|
| 99 |
SELECT CONCAT(EXTRACT (MONTH FROM d.transdate),'/',EXTRACT (YEAR FROM d.transdate)) AS date_part,
|
|
| 100 |
count(d.id),
|
|
| 101 |
max(d.dunning_level)
|
|
| 102 |
FROM dunning d
|
|
| 103 |
LEFT JOIN ar a ON a.id = d.trans_id
|
|
| 104 |
LEFT JOIN customer c ON a.customer_id = c.id
|
|
| 105 |
WHERE c.id = ?
|
|
| 106 |
GROUP BY EXTRACT (YEAR FROM d.transdate), EXTRACT (MONTH FROM d.transdate), c.id
|
|
| 107 |
ORDER BY EXTRACT (YEAR FROM d.transdate) DESC
|
|
| 108 |
SQL
|
|
| 109 |
|
|
| 110 |
$self->{dun_statistic} = selectall_hashref_query($::form, $dbh, $query, $cv);
|
|
| 102 | 111 |
$self->render('customer_vendor_turnover/count_open_items_by_year', { layout => 0 });
|
| 103 | 112 |
} |
| 104 | 113 |
|
| ... | ... | |
| 109 | 118 |
return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
|
| 110 | 119 |
|
| 111 | 120 |
my $dbh = SL::DB->client->dbh; |
| 112 |
my $cv = $::form->{id} || {};
|
|
| 121 |
my $cv = $::form->{id};
|
|
| 113 | 122 |
my ($db, $cv_type); |
| 114 | 123 |
if ($::form->{db} eq 'customer') {
|
| 115 | 124 |
$db = "ar"; |
| ... | ... | |
| 119 | 128 |
$cv_type = "vendor_id"; |
| 120 | 129 |
} |
| 121 | 130 |
my $query = <<SQL; |
| 122 |
SELECT CONCAT(EXTRACT (MONTH FROM transdate),'/',EXTRACT (YEAR FROM transdate)) AS date_part,
|
|
| 123 |
count(id) as count,
|
|
| 124 |
sum(amount) as amount,
|
|
| 125 |
sum(netamount) as netamount,
|
|
| 126 |
sum(paid) as paid
|
|
| 127 |
FROM $db WHERE $cv_type = $cv
|
|
| 128 |
GROUP BY EXTRACT (YEAR FROM transdate), EXTRACT (MONTH FROM transdate)
|
|
| 129 |
ORDER BY EXTRACT (YEAR FROM transdate) DESC, EXTRACT (MONTH FROM transdate) DESC
|
|
| 131 |
SELECT CONCAT(EXTRACT (MONTH FROM transdate),'/',EXTRACT (YEAR FROM transdate)) as date_part,
|
|
| 132 |
count(id) as count,
|
|
| 133 |
sum(amount) as amount,
|
|
| 134 |
sum(netamount) as netamount,
|
|
| 135 |
sum(paid) as paid
|
|
| 136 |
FROM $db WHERE $cv_type = ?
|
|
| 137 |
GROUP BY EXTRACT (YEAR FROM transdate), EXTRACT (MONTH FROM transdate) |
|
| 138 |
ORDER BY EXTRACT (YEAR FROM transdate) DESC, EXTRACT (MONTH FROM transdate) DESC |
|
| 130 | 139 |
SQL |
| 131 |
$self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query);
|
|
| 140 |
$self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query, $cv);
|
|
| 132 | 141 |
$self->render('customer_vendor_turnover/count_turnover', { layout => 0 });
|
| 133 | 142 |
} |
| 134 | 143 |
|
| ... | ... | |
| 138 | 147 |
return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
|
| 139 | 148 |
|
| 140 | 149 |
my $dbh = SL::DB->client->dbh; |
| 141 |
my $cv = $::form->{id} || {};
|
|
| 150 |
my $cv = $::form->{id};
|
|
| 142 | 151 |
my ($db, $cv_type); |
| 143 | 152 |
if ($::form->{db} eq 'customer') {
|
| 144 | 153 |
$db = "ar"; |
| ... | ... | |
| 148 | 157 |
$cv_type = "vendor_id"; |
| 149 | 158 |
} |
| 150 | 159 |
my $query = <<SQL; |
| 151 |
SELECT EXTRACT (YEAR FROM transdate) as date_part, |
|
| 152 |
count(id) as count,
|
|
| 153 |
sum(amount) as amount,
|
|
| 154 |
sum(netamount) as netamount,
|
|
| 155 |
sum(paid) as paid
|
|
| 156 |
FROM $db WHERE $cv_type = $cv
|
|
| 157 |
GROUP BY date_part
|
|
| 158 |
ORDER BY date_part DESC
|
|
| 160 |
SELECT EXTRACT (YEAR FROM transdate) as date_part,
|
|
| 161 |
count(id) as count,
|
|
| 162 |
sum(amount) as amount,
|
|
| 163 |
sum(netamount) as netamount,
|
|
| 164 |
sum(paid) as paid
|
|
| 165 |
FROM $db WHERE $cv_type = ?
|
|
| 166 |
GROUP BY date_part |
|
| 167 |
ORDER BY date_part DESC |
|
| 159 | 168 |
SQL |
| 160 |
$self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query);
|
|
| 169 |
$self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query, $cv);
|
|
| 161 | 170 |
$self->render('customer_vendor_turnover/count_turnover', { layout => 0 });
|
| 162 | 171 |
} |
| 163 | 172 |
|
| ... | ... | |
| 166 | 175 |
|
| 167 | 176 |
return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
|
| 168 | 177 |
|
| 169 |
my $cv = $::form->{id} || {};
|
|
| 178 |
my $cv = $::form->{id};
|
|
| 170 | 179 |
my $invoices; |
| 171 | 180 |
if ( $::form->{db} eq 'customer' ) {
|
| 172 | 181 |
$invoices = SL::DB::Manager::Invoice->get_all( |
| 173 |
query => [ customer_id => $cv, ], |
|
| 182 |
query => [ customer_id => $cv, ],
|
|
| 174 | 183 |
sort_by => 'invnumber DESC', |
| 175 | 184 |
); |
| 176 | 185 |
} else {
|
| 177 | 186 |
$invoices = SL::DB::Manager::PurchaseInvoice->get_all( |
| 178 |
query => [ vendor_id => $cv, ], |
|
| 187 |
query => [ vendor_id => $cv, ],
|
|
| 179 | 188 |
sort_by => 'invnumber DESC', |
| 180 | 189 |
); |
| 181 | 190 |
} |
| ... | ... | |
| 187 | 196 |
|
| 188 | 197 |
return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
|
| 189 | 198 |
|
| 190 |
my $cv = $::form->{id} || {};
|
|
| 199 |
my $cv = $::form->{id};
|
|
| 191 | 200 |
my $orders; |
| 192 | 201 |
my $type = $::form->{type};
|
| 193 | 202 |
if ( $::form->{db} eq 'customer' ) {
|
| 194 | 203 |
$orders = SL::DB::Manager::Order->get_all( |
| 195 |
query => [ customer_id => $cv, |
|
| 196 |
quotation => ($type eq 'quotation' ? 'T' : 'F') ], |
|
| 204 |
query => [ |
|
| 205 |
customer_id => $cv, |
|
| 206 |
quotation => ($type eq 'quotation' ? 'T' : 'F') |
|
| 207 |
], |
|
| 197 | 208 |
sort_by => ( $type eq 'order' ? 'ordnumber DESC' : 'quonumber DESC'), |
| 198 | 209 |
); |
| 199 | 210 |
} else {
|
| 200 | 211 |
$orders = SL::DB::Manager::Order->get_all( |
| 201 |
query => [ vendor_id => $cv, |
|
| 202 |
quotation => ($type eq 'quotation' ? 'T' : 'F') ], |
|
| 212 |
query => [ |
|
| 213 |
vendor_id => $cv, |
|
| 214 |
quotation => ($type eq 'quotation' ? 'T' : 'F') |
|
| 215 |
], |
|
| 203 | 216 |
sort_by => ( $type eq 'order' ? 'ordnumber DESC' : 'quonumber DESC'), |
| 204 | 217 |
); |
| 205 | 218 |
} |
| ... | ... | |
| 215 | 228 |
|
| 216 | 229 |
return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
|
| 217 | 230 |
my $open_orders; |
| 218 |
my $cv = $::form->{id} || {};
|
|
| 231 |
my $cv = $::form->{id};
|
|
| 219 | 232 |
|
| 220 | 233 |
if ( $::form->{db} eq 'customer' ) {
|
| 221 | 234 |
$open_orders = SL::DB::Manager::Order->get_all( |
| 222 |
query => [ customer_id => $cv, |
|
| 223 |
closed => 'F', |
|
| 224 |
], |
|
| 225 |
sort_by => 'ordnumber DESC', |
|
| 226 |
); |
|
| 235 |
query => [ |
|
| 236 |
customer_id => $cv, |
|
| 237 |
closed => 'F', |
|
| 238 |
], |
|
| 239 |
sort_by => 'ordnumber DESC', |
|
| 240 |
); |
|
| 227 | 241 |
} else {
|
| 228 | 242 |
$open_orders = SL::DB::Manager::Order->get_all( |
| 229 |
query => [ vendor_id => $cv, |
|
| 230 |
closed => 'F', |
|
| 231 |
], |
|
| 232 |
sort_by => 'ordnumber DESC', |
|
| 233 |
); |
|
| 243 |
query => [ |
|
| 244 |
vendor_id => $cv, |
|
| 245 |
closed => 'F', |
|
| 246 |
], |
|
| 247 |
sort_by => 'ordnumber DESC', |
|
| 248 |
); |
|
| 234 | 249 |
} |
| 235 | 250 |
|
| 236 | 251 |
return 0 unless scalar @{$open_orders};
|
| 237 |
return $self->render('customer_vendor_turnover/_list_open_orders', { output => 0 }, orders => $open_orders, title => $::locale->text('Open Orders') );
|
|
| 252 |
return $self->render('customer_vendor_turnover/_list_open_orders', { output => 0 },
|
|
| 253 |
orders => $open_orders, |
|
| 254 |
title => $::locale->text('Open Orders'),
|
|
| 255 |
); |
|
| 238 | 256 |
} |
| 239 | 257 |
|
| 240 | 258 |
sub action_get_mails {
|
| 241 | 259 |
my ( $self ) = @_; |
| 242 | 260 |
|
| 261 |
return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
|
|
| 243 | 262 |
my $dbh = SL::DB->client->dbh; |
| 244 | 263 |
my $query; |
| 245 | 264 |
my $cv = $::form->{id};
|
| 246 | 265 |
|
| 247 | 266 |
if ( $::form->{db} eq 'customer') {
|
| 248 | 267 |
$query = <<SQL; |
| 249 |
WITH oe_emails_customer AS (SELECT rc.to_id, rc.from_id, oe.quotation, oe.quonumber, oe.ordnumber, c.id FROM |
|
| 250 |
record_links rc |
|
| 251 |
LEFT JOIN oe oe ON rc.from_id = oe.id |
|
| 268 |
WITH |
|
| 269 |
oe_emails_customer |
|
| 270 |
AS (SELECT rc.to_id, rc.from_id, oe.quotation, oe.quonumber, oe.ordnumber, c.id |
|
| 271 |
FROM record_links rc |
|
| 272 |
LEFT JOIN oe oe ON rc.from_id = oe.id |
|
| 252 | 273 |
LEFT JOIN customer c ON oe.customer_id = c.id |
| 253 |
WHERE rc.to_table = 'email_journal' AND rc.from_table ='oe'), |
|
| 274 |
WHERE rc.to_table = 'email_journal' |
|
| 275 |
AND rc.from_table ='oe'), |
|
| 254 | 276 |
|
| 255 |
do_emails_customer AS (SELECT rc.to_id, rc.from_id, o.donumber, c.id FROM |
|
| 256 |
record_links rc |
|
| 277 |
do_emails_customer |
|
| 278 |
AS (SELECT rc.to_id, rc.from_id, o.donumber, c.id |
|
| 279 |
FROM record_links rc |
|
| 257 | 280 |
LEFT JOIN delivery_orders o ON rc.from_id = o.id |
| 258 | 281 |
LEFT JOIN customer c ON o.customer_id = c.id |
| 259 |
WHERE rc.to_table = 'email_journal' AND rc.from_table = 'delivery_orders'), |
|
| 282 |
WHERE rc.to_table = 'email_journal' |
|
| 283 |
AND rc.from_table = 'delivery_orders'), |
|
| 260 | 284 |
|
| 261 |
inv_emails_customer AS (SELECT rc.to_id, rc.from_id, inv.type, inv.invnumber, c.id FROM |
|
| 262 |
record_links rc |
|
| 285 |
inv_emails_customer |
|
| 286 |
AS (SELECT rc.to_id, rc.from_id, inv.type, inv.invnumber, c.id |
|
| 287 |
FROM record_links rc |
|
| 263 | 288 |
LEFT JOIN ar inv ON rc.from_id = inv.id |
| 264 | 289 |
LEFT JOIN customer c ON inv.customer_id = c.id |
| 265 |
WHERE rc.to_table = 'email_journal' AND rc.from_table = 'ar'), |
|
| 290 |
WHERE rc.to_table = 'email_journal' |
|
| 291 |
AND rc.from_table = 'ar'), |
|
| 266 | 292 |
|
| 267 |
letter_emails_customer AS (SELECT rc.to_id, rc.from_id, l.letternumber, c.id FROM |
|
| 268 |
record_links rc |
|
| 293 |
letter_emails_customer |
|
| 294 |
AS (SELECT rc.to_id, rc.from_id, l.letternumber, c.id |
|
| 295 |
FROM record_links rc |
|
| 269 | 296 |
LEFT JOIN letter l ON rc.from_id = l.id |
| 270 | 297 |
LEFT JOIN customer c ON l.customer_id = c.id |
| 271 |
WHERE rc.to_table = 'email_journal' AND rc.from_table = 'letter') |
|
| 298 |
WHERE rc.to_table = 'email_journal' |
|
| 299 |
AND rc.from_table = 'letter') |
|
| 272 | 300 |
|
| 273 |
SELECT ej.*, CASE |
|
| 301 |
SELECT ej.*, |
|
| 302 |
CASE |
|
| 274 | 303 |
oec.quotation WHEN 'F' THEN 'Sales Order' |
| 275 | 304 |
ELSE 'Quotation' |
| 276 |
END AS type,
|
|
| 277 |
CASE
|
|
| 305 |
END AS type, |
|
| 306 |
CASE |
|
| 278 | 307 |
oec.quotation WHEN 'F' THEN oec.ordnumber |
| 279 | 308 |
ELSE oec.quonumber |
| 280 |
END AS recordnumber, |
|
| 281 |
oec.id AS record_id FROM email_journal ej |
|
| 309 |
END AS recordnumber, |
|
| 310 |
oec.id AS record_id |
|
| 311 |
FROM email_journal ej |
|
| 282 | 312 |
LEFT JOIN oe_emails_customer oec ON ej.id = oec.to_id |
| 283 |
WHERE oec.id = ? |
|
| 313 |
WHERE oec.id = ?
|
|
| 284 | 314 |
|
| 285 | 315 |
UNION |
| 286 | 316 |
|
| 287 |
SELECT ej.*, 'Delivery Order' AS type, dec.donumber AS recordnumber,dec.id AS record_id FROM email_journal ej |
|
| 317 |
SELECT ej.*, 'Delivery Order' AS type, dec.donumber AS recordnumber,dec.id AS record_id |
|
| 318 |
FROM email_journal ej |
|
| 288 | 319 |
LEFT JOIN do_emails_customer dec ON ej.id = dec.to_id |
| 289 |
WHERE dec.id = ? |
|
| 320 |
WHERE dec.id = ?
|
|
| 290 | 321 |
|
| 291 | 322 |
UNION |
| 292 | 323 |
|
| 293 |
SELECT ej.*, CASE |
|
| 324 |
SELECT ej.*, |
|
| 325 |
CASE |
|
| 294 | 326 |
iec.type WHEN 'credit_note' THEN 'Credit Note' |
| 295 | 327 |
WHEN 'invoice' THEN 'Invoice' |
| 296 | 328 |
ELSE 'N/A' |
| 297 |
END AS type, |
|
| 298 |
iec.invnumber AS recordnumber,iec.id AS record_id FROM email_journal ej |
|
| 329 |
END AS type, |
|
| 330 |
iec.invnumber AS recordnumber, |
|
| 331 |
iec.id AS record_id |
|
| 332 |
FROM email_journal ej |
|
| 299 | 333 |
LEFT JOIN inv_emails_customer iec ON ej.id = iec.to_id |
| 300 |
WHERE iec.id = ? |
|
| 334 |
WHERE iec.id = ?
|
|
| 301 | 335 |
|
| 302 | 336 |
UNION |
| 303 | 337 |
|
| 304 |
SELECT ej.*, 'Letter' AS type, lec.letternumber AS recordnumber,lec.id AS record_id FROM email_journal ej |
|
| 338 |
SELECT ej.*, 'Letter' AS type, lec.letternumber AS recordnumber,lec.id AS record_id |
|
| 339 |
FROM email_journal ej |
|
| 305 | 340 |
LEFT JOIN letter_emails_customer lec ON ej.id = lec.to_id |
| 306 |
WHERE lec.id = ? |
|
| 307 |
ORDER BY sent_on DESC |
|
| 341 |
WHERE lec.id = ?
|
|
| 342 |
ORDER BY sent_on DESC
|
|
| 308 | 343 |
SQL |
| 309 | 344 |
} |
| 310 | 345 |
else {
|
| 311 | 346 |
$query = <<SQL; |
| 312 |
WITH oe_emails_vendor AS (SELECT rc.to_id, rc.from_id, oe.quotation, oe.quonumber, oe.ordnumber, c.id FROM |
|
| 313 |
record_links rc |
|
| 347 |
WITH |
|
| 348 |
oe_emails_vendor |
|
| 349 |
AS (SELECT rc.to_id, rc.from_id, oe.quotation, oe.quonumber, oe.ordnumber, c.id |
|
| 350 |
FROM record_links rc |
|
| 314 | 351 |
LEFT JOIN oe oe ON rc.from_id = oe.id |
| 315 | 352 |
LEFT JOIN vendor c ON oe.vendor_id = c.id |
| 316 |
WHERE rc.to_table = 'email_journal' AND rc.from_table ='oe'), |
|
| 353 |
WHERE rc.to_table = 'email_journal' |
|
| 354 |
AND rc.from_table ='oe'), |
|
| 317 | 355 |
|
| 318 |
do_emails_vendor AS (SELECT rc.to_id, rc.from_id, o.donumber, c.id FROM |
|
| 319 |
record_links rc |
|
| 356 |
do_emails_vendor |
|
| 357 |
AS (SELECT rc.to_id, rc.from_id, o.donumber, c.id |
|
| 358 |
FROM record_links rc |
|
| 320 | 359 |
LEFT JOIN delivery_orders o ON rc.from_id = o.id |
| 321 | 360 |
LEFT JOIN vendor c ON o.vendor_id = c.id |
| 322 |
WHERE rc.to_table = 'email_journal' AND rc.from_table = 'delivery_orders'), |
|
| 361 |
WHERE rc.to_table = 'email_journal' |
|
| 362 |
AND rc.from_table = 'delivery_orders'), |
|
| 323 | 363 |
|
| 324 |
inv_emails_vendor AS (SELECT rc.to_id, rc.from_id, inv.type, inv.invnumber, c.id FROM |
|
| 325 |
record_links rc |
|
| 364 |
inv_emails_vendor |
|
| 365 |
AS (SELECT rc.to_id, rc.from_id, inv.type, inv.invnumber, c.id |
|
| 366 |
FROM record_links rc |
|
| 326 | 367 |
LEFT JOIN ap inv ON rc.from_id = inv.id |
| 327 | 368 |
LEFT JOIN vendor c ON inv.vendor_id = c.id |
| 328 |
WHERE rc.to_table = 'email_journal' AND rc.from_table = 'ar'), |
|
| 369 |
WHERE rc.to_table = 'email_journal' |
|
| 370 |
AND rc.from_table = 'ar'), |
|
| 329 | 371 |
|
| 330 |
letter_emails_vendor AS (SELECT rc.to_id, rc.from_id, l.letternumber, c.id FROM |
|
| 331 |
record_links rc |
|
| 372 |
letter_emails_vendor |
|
| 373 |
AS (SELECT rc.to_id, rc.from_id, l.letternumber, c.id |
|
| 374 |
FROM record_links rc |
|
| 332 | 375 |
LEFT JOIN letter l ON rc.from_id = l.id |
| 333 | 376 |
LEFT JOIN vendor c ON l.vendor_id = c.id |
| 334 |
WHERE rc.to_table = 'email_journal' AND rc.from_table = 'letter') |
|
| 377 |
WHERE rc.to_table = 'email_journal' |
|
| 378 |
AND rc.from_table = 'letter') |
|
| 335 | 379 |
|
| 336 |
SELECT ej.*, CASE |
|
| 380 |
SELECT ej.*, |
|
| 381 |
CASE |
|
| 337 | 382 |
oec.quotation WHEN 'F' THEN 'Purchase Order' |
| 338 | 383 |
ELSE 'Request quotation' |
| 339 |
END AS type,
|
|
| 340 |
CASE
|
|
| 384 |
END AS type, |
|
| 385 |
CASE |
|
| 341 | 386 |
oec.quotation WHEN 'F' THEN oec.ordnumber |
| 342 | 387 |
ELSE oec.quonumber |
| 343 |
END AS recordnumber, |
|
| 344 |
oec.id AS record_id FROM email_journal ej |
|
| 388 |
END AS recordnumber, |
|
| 389 |
oec.id AS record_id |
|
| 390 |
FROM email_journal ej |
|
| 345 | 391 |
LEFT JOIN oe_emails_vendor oec ON ej.id = oec.to_id |
| 346 |
WHERE oec.id = ? |
|
| 392 |
WHERE oec.id = ?
|
|
| 347 | 393 |
|
| 348 | 394 |
UNION |
| 349 | 395 |
|
| 350 |
SELECT ej.*, 'Purchase Delivery Order' AS type, dec.donumber AS recordnumber, dec.id AS record_id FROM email_journal ej |
|
| 396 |
SELECT ej.*, 'Purchase Delivery Order' AS type, dec.donumber AS recordnumber, dec.id AS record_id |
|
| 397 |
FROM email_journal ej |
|
| 351 | 398 |
LEFT JOIN do_emails_vendor dec ON ej.id = dec.to_id |
| 352 |
WHERE dec.id = ? |
|
| 399 |
WHERE dec.id = ?
|
|
| 353 | 400 |
|
| 354 | 401 |
UNION |
| 355 | 402 |
|
| 356 |
SELECT ej.*, iec.type AS type, iec.invnumber AS recordnumber, iec.id AS record_id FROM email_journal ej |
|
| 403 |
SELECT ej.*, iec.type AS type, iec.invnumber AS recordnumber, iec.id AS record_id |
|
| 404 |
FROM email_journal ej |
|
| 357 | 405 |
LEFT JOIN inv_emails_vendor iec ON ej.id = iec.to_id |
| 358 |
WHERE iec.id = ? |
|
| 406 |
WHERE iec.id = ?
|
|
| 359 | 407 |
|
| 360 | 408 |
UNION |
| 361 | 409 |
|
| 362 |
SELECT ej.*, 'Letter' AS type, lec.letternumber AS recordnumber, lec.id AS record_id FROM email_journal ej |
|
| 410 |
SELECT ej.*, 'Letter' AS type, lec.letternumber AS recordnumber, lec.id AS record_id |
|
| 411 |
FROM email_journal ej |
|
| 363 | 412 |
LEFT JOIN letter_emails_vendor lec ON ej.id = lec.to_id |
| 364 |
WHERE lec.id = ? |
|
| 365 |
ORDER BY sent_on DESC |
|
| 413 |
WHERE lec.id = ?
|
|
| 414 |
ORDER BY sent_on DESC
|
|
| 366 | 415 |
SQL |
| 367 | 416 |
} |
| 368 | 417 |
my $emails = selectall_hashref_query($::form, $dbh, $query, $cv, $cv, $cv, $cv); |
| ... | ... | |
| 374 | 423 |
|
| 375 | 424 |
return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
|
| 376 | 425 |
|
| 377 |
my $cv = $::form->{id} || {};
|
|
| 426 |
my $cv = $::form->{id};
|
|
| 378 | 427 |
my $letters; |
| 379 | 428 |
my $type = $::form->{type};
|
| 380 | 429 |
if ( $::form->{db} eq 'customer' ) {
|
| 381 | 430 |
$letters = SL::DB::Manager::Letter->get_all( |
| 382 |
query => [ customer_id => $cv, ], |
|
| 431 |
query => [ customer_id => $cv, ],
|
|
| 383 | 432 |
sort_by => 'date DESC', |
| 384 | 433 |
); |
| 385 | 434 |
} else {
|
| 386 | 435 |
$letters = SL::DB::Manager::Letter->get_all( |
| 387 |
query => [ vendor_id => $cv, ], |
|
| 436 |
query => [ vendor_id => $cv, ],
|
|
| 388 | 437 |
sort_by => 'date DESC', |
| 389 | 438 |
); |
| 390 | 439 |
} |
| ... | ... | |
| 407 | 456 |
|
| 408 | 457 |
wich belong to customer/vendor and displays them in an extra tab "Records". |
| 409 | 458 |
|
| 459 |
=back |
|
| 460 |
|
|
| 410 | 461 |
=head1 URL ACTIONS |
| 411 | 462 |
|
| 412 | 463 |
=over 4 |
| ... | ... | |
| 470 | 521 |
=head1 AUTHOR |
| 471 | 522 |
|
| 472 | 523 |
W. Hahn E<lt>wh@futureworldsearch.netE<gt> |
| 524 |
|
|
| 525 |
=back |
|
Auch abrufbar als: Unified diff
Kundenstatistik: Typo und Formatierung