Revision 8e99e751
Von Werner Hahn vor etwa 6 Jahren hinzugefügt
| SL/Controller/CustomerVendorTurnover.pm | ||
|---|---|---|
| 4 | 4 |
use SL::DBUtils; |
| 5 | 5 |
use SL::DB::AccTransaction; |
| 6 | 6 |
use SL::DB::Invoice; |
| 7 |
use SL::DB::Order; |
|
| 7 | 8 |
use SL::DB; |
| 8 | 9 |
|
| 9 | 10 |
__PACKAGE__->run_before('check_auth');
|
| ... | ... | |
| 15 | 16 |
|
| 16 | 17 |
my $cv = $::form->{id} || {};
|
| 17 | 18 |
my $open_invoices; |
| 19 |
if ( $::form->{db} eq 'customer' ) {
|
|
| 18 | 20 |
$open_invoices = SL::DB::Manager::Invoice->get_all( |
| 19 | 21 |
query => [customer_id => $cv, |
| 20 |
paid => {lt_sql => 'amount'},
|
|
| 22 |
or => [ |
|
| 23 |
amount => { gt => \'paid'},
|
|
| 24 |
amount => { lt => \'paid'},
|
|
| 25 |
], |
|
| 21 | 26 |
], |
| 22 | 27 |
with_objects => ['dunnings'], |
| 23 | 28 |
); |
| 29 |
} else {
|
|
| 30 |
$open_invoices = SL::DB::Manager::PurchaseInvoice->get_all( |
|
| 31 |
query => [ vendor_id => $cv, |
|
| 32 |
or => [ |
|
| 33 |
amount => { gt => \'paid'},
|
|
| 34 |
amount => { lt => \'paid'},
|
|
| 35 |
], |
|
| 36 |
], |
|
| 37 |
sort_by => 'invnumber DESC', |
|
| 38 |
); |
|
| 39 |
} |
|
| 24 | 40 |
my $open_items; |
| 25 | 41 |
if (@{$open_invoices}) {
|
| 26 | 42 |
return $self->render(\'', { type => 'json' }) unless scalar @{$open_invoices};
|
| 27 | 43 |
$open_items = $self->_list_open_items($open_invoices); |
| 28 | 44 |
} |
| 29 |
return $self->render('customer_vendor_turnover/turnover', { header => 0 }, open_items => $open_items, id => $cv);
|
|
| 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);
|
|
| 30 | 47 |
} |
| 31 | 48 |
|
| 32 | 49 |
sub _list_open_items {
|
| ... | ... | |
| 39 | 56 |
my ($self) = @_; |
| 40 | 57 |
|
| 41 | 58 |
return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
|
| 42 |
my $dbh = SL::DB->client->dbh()
|
|
| 59 |
my $dbh = SL::DB->client->dbh;
|
|
| 43 | 60 |
|
| 44 | 61 |
my $cv = $::form->{id} || {};
|
| 45 | 62 |
|
| ... | ... | |
| 63 | 80 |
my ($self) = @_; |
| 64 | 81 |
|
| 65 | 82 |
return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
|
| 66 |
my $dbh = SL::DB->client->dbh()
|
|
| 83 |
my $dbh = SL::DB->client->dbh;
|
|
| 67 | 84 |
|
| 68 | 85 |
my $cv = $::form->{id} || {};
|
| 69 | 86 |
|
| ... | ... | |
| 88 | 105 |
|
| 89 | 106 |
return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
|
| 90 | 107 |
|
| 91 |
my $dbh = SL::DB->client->dbh()
|
|
| 108 |
my $dbh = SL::DB->client->dbh;
|
|
| 92 | 109 |
my $cv = $::form->{id} || {};
|
| 93 |
my $query = "SELECT CONCAT(EXTRACT (MONTH FROM transdate),'/',EXTRACT (YEAR FROM transdate)) AS date_part, |
|
| 110 |
my ($db, $cv_type); |
|
| 111 |
if ($::form->{db} eq 'customer') {
|
|
| 112 |
$db = "ar"; |
|
| 113 |
$cv_type = "customer_id"; |
|
| 114 |
} else {
|
|
| 115 |
$db = "ap"; |
|
| 116 |
$cv_type = "vendor_id"; |
|
| 117 |
} |
|
| 118 |
my $query = <<SQL; |
|
| 119 |
SELECT CONCAT(EXTRACT (MONTH FROM transdate),'/',EXTRACT (YEAR FROM transdate)) AS date_part, |
|
| 94 | 120 |
count(id) as count, |
| 95 | 121 |
sum(amount) as amount, |
| 96 | 122 |
sum(netamount) as netamount, |
| 97 | 123 |
sum(paid) as paid |
| 98 |
FROM ar WHERE customer_id = $cv
|
|
| 124 |
FROM $db WHERE $cv_type = $cv
|
|
| 99 | 125 |
GROUP BY EXTRACT (YEAR FROM transdate), EXTRACT (MONTH FROM transdate) |
| 100 |
ORDER BY EXTRACT (YEAR FROM transdate) DESC, EXTRACT (MONTH FROM transdate) DESC";
|
|
| 101 |
|
|
| 126 |
ORDER BY EXTRACT (YEAR FROM transdate) DESC, EXTRACT (MONTH FROM transdate) DESC |
|
| 127 |
SQL |
|
| 102 | 128 |
$self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query);
|
| 103 | 129 |
$self->render('customer_vendor_turnover/count_turnover', { layout => 0 });
|
| 104 | 130 |
} |
| 105 | 131 |
sub action_turnover_by_year {
|
| 106 |
|
|
| 107 | 132 |
my ($self) = @_; |
| 108 | 133 |
|
| 109 | 134 |
return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
|
| 110 | 135 |
|
| 111 |
my $dbh = SL::DB->client->dbh()
|
|
| 136 |
my $dbh = SL::DB->client->dbh;
|
|
| 112 | 137 |
my $cv = $::form->{id} || {};
|
| 113 |
my $query = "SELECT EXTRACT (YEAR FROM transdate) as date_part, |
|
| 138 |
my ($db, $cv_type); |
|
| 139 |
if ($::form->{db} eq 'customer') {
|
|
| 140 |
$db = "ar"; |
|
| 141 |
$cv_type = "customer_id"; |
|
| 142 |
} else {
|
|
| 143 |
$db = "ap"; |
|
| 144 |
$cv_type = "vendor_id"; |
|
| 145 |
} |
|
| 146 |
my $query = <<SQL; |
|
| 147 |
SELECT EXTRACT (YEAR FROM transdate) as date_part, |
|
| 114 | 148 |
count(id) as count, |
| 115 | 149 |
sum(amount) as amount, |
| 116 | 150 |
sum(netamount) as netamount, |
| 117 | 151 |
sum(paid) as paid |
| 118 |
FROM ar WHERE customer_id = $cv
|
|
| 152 |
FROM $db WHERE $cv_type = $cv
|
|
| 119 | 153 |
GROUP BY date_part |
| 120 |
ORDER BY date_part DESC";
|
|
| 121 |
|
|
| 154 |
ORDER BY date_part DESC |
|
| 155 |
SQL |
|
| 122 | 156 |
$self->{turnover_statistic} = selectall_hashref_query($::form, $dbh, $query);
|
| 123 | 157 |
$self->render('customer_vendor_turnover/count_turnover', { layout => 0 });
|
| 124 | 158 |
} |
| 159 |
|
|
| 125 | 160 |
sub action_get_invoices {
|
| 126 | 161 |
my ($self) = @_; |
| 127 | 162 |
|
| 128 | 163 |
return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
|
| 129 | 164 |
|
| 130 | 165 |
my $cv = $::form->{id} || {};
|
| 131 |
my $invoices = SL::DB::Manager::Invoice->get_all( |
|
| 132 |
query => [ customer_id => $cv, ], |
|
| 133 |
sort_by => 'invnumber DESC', |
|
| 134 |
); |
|
| 166 |
my $invoices; |
|
| 167 |
if ( $::form->{db} eq 'customer' ) {
|
|
| 168 |
$invoices = SL::DB::Manager::Invoice->get_all( |
|
| 169 |
query => [ customer_id => $cv, ], |
|
| 170 |
sort_by => 'invnumber DESC', |
|
| 171 |
); |
|
| 172 |
} else {
|
|
| 173 |
$invoices = SL::DB::Manager::PurchaseInvoice->get_all( |
|
| 174 |
query => [ vendor_id => $cv, ], |
|
| 175 |
sort_by => 'invnumber DESC', |
|
| 176 |
); |
|
| 177 |
} |
|
| 135 | 178 |
$self->render('customer_vendor_turnover/invoices_statistic', { layout => 0 }, invoices => $invoices);
|
| 136 | 179 |
} |
| 180 |
|
|
| 181 |
sub action_get_orders {
|
|
| 182 |
my ($self) = @_; |
|
| 183 |
|
|
| 184 |
return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
|
|
| 185 |
|
|
| 186 |
my $cv = $::form->{id} || {};
|
|
| 187 |
my $orders; |
|
| 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'), |
|
| 194 |
); |
|
| 195 |
} else {
|
|
| 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'), |
|
| 200 |
); |
|
| 201 |
} |
|
| 202 |
if ( $type eq 'order') {
|
|
| 203 |
$self->render('customer_vendor_turnover/order_statistic', { layout => 0 }, orders => $orders);
|
|
| 204 |
} else {
|
|
| 205 |
$self->render('customer_vendor_turnover/quotation_statistic', { layout => 0 }, orders => $orders);
|
|
| 206 |
} |
|
| 207 |
} |
|
| 208 |
|
|
| 209 |
sub _get_open_orders {
|
|
| 210 |
my ( $self ) = @_; |
|
| 211 |
|
|
| 212 |
return $self->render('generic/error', { layout => 0 }, label_error => "list_transactions needs a trans_id") unless $::form->{id};
|
|
| 213 |
my $open_orders; |
|
| 214 |
my $cv = $::form->{id} || {};
|
|
| 215 |
|
|
| 216 |
if ( $::form->{db} eq 'customer' ) {
|
|
| 217 |
$open_orders = SL::DB::Manager::Order->get_all( |
|
| 218 |
query => [ customer_id => $cv, |
|
| 219 |
closed => 'F', |
|
| 220 |
], |
|
| 221 |
sort_by => 'ordnumber DESC', |
|
| 222 |
); |
|
| 223 |
} else {
|
|
| 224 |
$open_orders = SL::DB::Manager::Order->get_all( |
|
| 225 |
query => [ vendor_id => $cv, |
|
| 226 |
closed => 'F', |
|
| 227 |
], |
|
| 228 |
sort_by => 'ordnumber DESC', |
|
| 229 |
); |
|
| 230 |
} |
|
| 231 |
|
|
| 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') );
|
|
| 234 |
} |
|
| 235 |
|
|
| 137 | 236 |
sub _list_articles_by_invoice {
|
| 138 | 237 |
} |
| 139 | 238 |
sub _list_count_articles_by_year {
|
Auch abrufbar als: Unified diff
Kundenstatistik: offene Rechnungen, offene Aufträge, Angebote, Aufträge,
Kunden und Lieferanten
Kunden Angebote, Aufträge, Rechnungen
Lieferanten Preisanfragen, Lieferantenaufträge, Rechnungen