Revision 29318a62
Von Werner Hahn vor etwa 6 Jahren hinzugefügt
| SL/Controller/CustomerVendorTurnover.pm | ||
|---|---|---|
| 5 | 5 |
use SL::DB::AccTransaction; |
| 6 | 6 |
use SL::DB::Invoice; |
| 7 | 7 |
use SL::DB::Order; |
| 8 |
use SL::DB::EmailJournal; |
|
| 9 |
use SL::DB::Letter; |
|
| 8 | 10 |
use SL::DB; |
| 9 | 11 |
|
| 10 | 12 |
__PACKAGE__->run_before('check_auth');
|
| ... | ... | |
| 75 | 77 |
$self->{dun_statistic} = selectall_hashref_query($::form, $dbh, $query);
|
| 76 | 78 |
$self->render('customer_vendor_turnover/count_open_items_by_year', { layout => 0 });
|
| 77 | 79 |
} |
| 80 |
|
|
| 78 | 81 |
sub action_count_open_items_by_month {
|
| 79 | 82 |
|
| 80 | 83 |
my ($self) = @_; |
| ... | ... | |
| 99 | 102 |
$self->{dun_statistic} = selectall_hashref_query($::form, $dbh, $query);
|
| 100 | 103 |
$self->render('customer_vendor_turnover/count_open_items_by_year', { layout => 0 });
|
| 101 | 104 |
} |
| 105 |
|
|
| 102 | 106 |
sub action_turnover_by_month {
|
| 103 | 107 |
|
| 104 | 108 |
my ($self) = @_; |
| ... | ... | |
| 128 | 132 |
$self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query);
|
| 129 | 133 |
$self->render('customer_vendor_turnover/count_turnover', { layout => 0 });
|
| 130 | 134 |
} |
| 135 |
|
|
| 131 | 136 |
sub action_turnover_by_year {
|
| 132 | 137 |
my ($self) = @_; |
| 133 | 138 |
|
| ... | ... | |
| 233 | 238 |
return $self->render('customer_vendor_turnover/_list_open_orders', { output => 0 }, orders => $open_orders, title => $::locale->text('Open Orders') );
|
| 234 | 239 |
} |
| 235 | 240 |
|
| 241 |
sub action_get_mails {
|
|
| 242 |
my ( $self ) = @_; |
|
| 243 |
|
|
| 244 |
my $dbh = SL::DB->client->dbh; |
|
| 245 |
my $query; |
|
| 246 |
my $cv = $::form->{id};
|
|
| 247 |
|
|
| 248 |
if ( $::form->{db} eq 'customer') {
|
|
| 249 |
$query = <<SQL; |
|
| 250 |
WITH oe_emails_customer AS (SELECT rc.to_id, rc.from_id, oe.quotation, oe.quonumber, oe.ordnumber, c.id FROM |
|
| 251 |
record_links rc |
|
| 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'), |
|
| 255 |
|
|
| 256 |
do_emails_customer AS (SELECT rc.to_id, rc.from_id, o.donumber, c.id FROM |
|
| 257 |
record_links rc |
|
| 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'), |
|
| 261 |
|
|
| 262 |
inv_emails_customer AS (SELECT rc.to_id, rc.from_id, inv.type, inv.invnumber, c.id FROM |
|
| 263 |
record_links rc |
|
| 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'), |
|
| 267 |
|
|
| 268 |
letter_emails_customer AS (SELECT rc.to_id, rc.from_id, l.letternumber, c.id FROM |
|
| 269 |
record_links rc |
|
| 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') |
|
| 273 |
|
|
| 274 |
SELECT ej.*, CASE |
|
| 275 |
oec.quotation WHEN 'F' THEN 'Sales Order' |
|
| 276 |
ELSE 'Quotation' |
|
| 277 |
END AS type, |
|
| 278 |
CASE |
|
| 279 |
oec.quotation WHEN 'F' THEN oec.ordnumber |
|
| 280 |
ELSE oec.quonumber |
|
| 281 |
END AS recordnumber, |
|
| 282 |
oec.id AS record_id FROM email_journal ej |
|
| 283 |
LEFT JOIN oe_emails_customer oec ON ej.id = oec.to_id |
|
| 284 |
WHERE oec.id = ? |
|
| 285 |
|
|
| 286 |
UNION |
|
| 287 |
|
|
| 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 |
|
| 290 |
WHERE dec.id = ? |
|
| 291 |
|
|
| 292 |
UNION |
|
| 293 |
|
|
| 294 |
SELECT ej.*, CASE |
|
| 295 |
iec.type WHEN 'credit_note' THEN 'Credit Note' |
|
| 296 |
WHEN 'invoice' THEN 'Invoice' |
|
| 297 |
ELSE 'N/A' |
|
| 298 |
END AS type, |
|
| 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 |
|
| 301 |
WHERE iec.id = ? |
|
| 302 |
|
|
| 303 |
UNION |
|
| 304 |
|
|
| 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 |
|
| 307 |
WHERE lec.id = ? |
|
| 308 |
ORDER BY sent_on DESC |
|
| 309 |
SQL |
|
| 310 |
} |
|
| 311 |
else {
|
|
| 312 |
$query = <<SQL; |
|
| 313 |
WITH oe_emails_vendor AS (SELECT rc.to_id, rc.from_id, oe.quotation, oe.quonumber, oe.ordnumber, c.id FROM |
|
| 314 |
record_links rc |
|
| 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'), |
|
| 318 |
|
|
| 319 |
do_emails_vendor AS (SELECT rc.to_id, rc.from_id, o.donumber, c.id FROM |
|
| 320 |
record_links rc |
|
| 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'), |
|
| 324 |
|
|
| 325 |
inv_emails_vendor AS (SELECT rc.to_id, rc.from_id, inv.type, inv.invnumber, c.id FROM |
|
| 326 |
record_links rc |
|
| 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'), |
|
| 330 |
|
|
| 331 |
letter_emails_vendor AS (SELECT rc.to_id, rc.from_id, l.letternumber, c.id FROM |
|
| 332 |
record_links rc |
|
| 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') |
|
| 336 |
|
|
| 337 |
SELECT ej.*, CASE |
|
| 338 |
oec.quotation WHEN 'F' THEN 'Purchase Order' |
|
| 339 |
ELSE 'Request quotation' |
|
| 340 |
END AS type, |
|
| 341 |
CASE |
|
| 342 |
oec.quotation WHEN 'F' THEN oec.ordnumber |
|
| 343 |
ELSE oec.quonumber |
|
| 344 |
END AS recordnumber, |
|
| 345 |
oec.id AS record_id FROM email_journal ej |
|
| 346 |
LEFT JOIN oe_emails_vendor oec ON ej.id = oec.to_id |
|
| 347 |
WHERE oec.id = ? |
|
| 348 |
|
|
| 349 |
UNION |
|
| 350 |
|
|
| 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 |
|
| 353 |
WHERE dec.id = ? |
|
| 354 |
|
|
| 355 |
UNION |
|
| 356 |
|
|
| 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 |
|
| 359 |
WHERE iec.id = ? |
|
| 360 |
|
|
| 361 |
UNION |
|
| 362 |
|
|
| 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 |
|
| 365 |
WHERE lec.id = ? |
|
| 366 |
ORDER BY sent_on DESC |
|
| 367 |
SQL |
|
| 368 |
} |
|
| 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);
|
|
| 371 |
} |
|
| 372 |
|
|
| 236 | 373 |
sub _list_articles_by_invoice {
|
| 237 | 374 |
} |
| 238 | 375 |
sub _list_count_articles_by_year {
|
Auch abrufbar als: Unified diff
Kundenstatistik: Emails