Revision 5e2cb6ed
Von Sven Schöling vor mehr als 10 Jahren hinzugefügt
| SL/RP.pm | ||
|---|---|---|
|
my @headingaccounts = ();
|
||
|
my $dpt_where;
|
||
|
my $dpt_where_without_arapgl;
|
||
|
my ($customer_where, $customer_join, $customer_no_union);
|
||
|
my $project;
|
||
|
|
||
|
my $where = "1 = 1";
|
||
| ... | ... | |
|
(SELECT department_id FROM gl WHERE gl.id=ac.trans_id),
|
||
|
(SELECT department_id FROM ap WHERE ap.id=ac.trans_id)) = | . conv_i($department_id);
|
||
|
}
|
||
|
if ($form->{customer_id}) {
|
||
|
$customer_join = qq| JOIN ar a ON (ac.trans_id = a.id) |;
|
||
|
$customer_where = qq| AND (a.customer_id = | . conv_i($form->{customer_id}, 'NULL') . qq|) |;
|
||
|
$customer_no_union = qq| AND 1=0 |;
|
||
|
}
|
||
|
|
||
|
# project_id only applies to getting transactions
|
||
|
# it has nothing to do with a trial balance
|
||
| ... | ... | |
|
my $min_max = $prefix eq 'from' ? 'min' : 'max';
|
||
|
$query = qq|SELECT ${min_max}(transdate)
|
||
|
FROM acc_trans ac
|
||
|
$customer_join
|
||
|
WHERE (1 = 1)
|
||
|
$dpt_where_without_arapgl
|
||
|
$dpt_where
|
||
|
$customer_where
|
||
|
$project|;
|
||
|
($form->{"${prefix}date"}) = selectfirst_array_query($form, $dbh, $query);
|
||
|
}
|
||
| ... | ... | |
|
qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount, c.description
|
||
|
FROM acc_trans ac
|
||
|
LEFT JOIN chart c ON (ac.chart_id = c.id)
|
||
|
$customer_join
|
||
|
WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.ob_transaction
|
||
|
$dpt_where_without_arapgl
|
||
|
$dpt_where
|
||
|
$customer_where
|
||
|
$project
|
||
|
GROUP BY c.accno, c.category, c.description |;
|
||
|
|
||
| ... | ... | |
|
SELECT c.accno, c.description, c.category, SUM(ac.amount) AS amount
|
||
|
FROM acc_trans ac
|
||
|
JOIN chart c ON (c.id = ac.chart_id)
|
||
|
$customer_join
|
||
|
WHERE $where
|
||
|
$dpt_where_without_arapgl
|
||
|
$project
|
||
| ... | ... | |
|
JOIN chart c ON (p.income_accno_id = c.id)
|
||
|
WHERE $invwhere
|
||
|
$dpt_where
|
||
|
$customer_where
|
||
|
$project
|
||
|
GROUP BY c.accno, c.description, c.category
|
||
|
|
||
| ... | ... | |
|
JOIN chart c ON (p.expense_accno_id = c.id)
|
||
|
WHERE $invwhere
|
||
|
$dpt_where
|
||
|
$customer_no_union
|
||
|
$project
|
||
|
GROUP BY c.accno, c.description, c.category
|
||
|
|;
|
||
| ... | ... | |
|
(SELECT SUM(ac.amount) * -1
|
||
|
FROM acc_trans ac
|
||
|
JOIN chart c ON (c.id = ac.chart_id)
|
||
|
$customer_join
|
||
|
WHERE $where
|
||
|
$dpt_where_without_arapgl
|
||
|
$dpt_where
|
||
|
$customer_where
|
||
|
$project
|
||
|
AND (ac.amount < 0)
|
||
|
AND (c.accno = ?)) AS debit,
|
||
| ... | ... | |
|
(SELECT SUM(ac.amount)
|
||
|
FROM acc_trans ac
|
||
|
JOIN chart c ON (c.id = ac.chart_id)
|
||
|
$customer_join
|
||
|
WHERE $where
|
||
|
$dpt_where_without_arapgl
|
||
|
$dpt_where
|
||
|
$customer_where
|
||
|
$project
|
||
|
AND ac.amount > 0
|
||
|
AND c.accno = ?) AS credit,
|
||
|
(SELECT SUM(ac.amount)
|
||
|
FROM acc_trans ac
|
||
|
JOIN chart c ON (ac.chart_id = c.id)
|
||
|
$customer_join
|
||
|
WHERE $saldowhere
|
||
|
$dpt_where_without_arapgl
|
||
|
$dpt_where
|
||
|
$customer_where
|
||
|
$project
|
||
|
AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo,
|
||
|
|
||
|
(SELECT SUM(ac.amount)
|
||
|
FROM acc_trans ac
|
||
|
JOIN chart c ON (ac.chart_id = c.id)
|
||
|
$customer_join
|
||
|
WHERE $sumwhere
|
||
|
$dpt_where_without_arapgl
|
||
|
$dpt_where
|
||
|
$customer_where
|
||
|
$project
|
||
|
AND amount > 0
|
||
|
AND ac.amount > 0
|
||
|
AND c.accno = ?) AS sum_credit,
|
||
|
|
||
|
(SELECT SUM(ac.amount)
|
||
|
FROM acc_trans ac
|
||
|
JOIN chart c ON (ac.chart_id = c.id)
|
||
|
$customer_join
|
||
|
WHERE $sumwhere
|
||
|
$dpt_where_without_arapgl
|
||
|
$dpt_where
|
||
|
$customer_where
|
||
|
$project
|
||
|
AND amount < 0
|
||
|
AND ac.amount < 0
|
||
|
AND c.accno = ?) AS sum_debit,
|
||
|
|
||
|
(SELECT max(ac.transdate) FROM acc_trans ac
|
||
|
JOIN chart c ON (ac.chart_id = c.id)
|
||
|
$customer_join
|
||
|
WHERE $where
|
||
|
$dpt_where_without_arapgl
|
||
|
$dpt_where
|
||
|
$customer_where
|
||
|
$project
|
||
|
AND c.accno = ?) AS last_transaction
|
||
|
|
||
| ... | ... | |
|
JOIN chart c ON (p.expense_accno_id = c.id)
|
||
|
WHERE $invwhere
|
||
|
$dpt_where
|
||
|
$customer_no_union
|
||
|
$project
|
||
|
AND c.accno = ?) AS debit,
|
||
|
|
||
| ... | ... | |
|
JOIN chart c ON (p.income_accno_id = c.id)
|
||
|
WHERE $invwhere
|
||
|
$dpt_where
|
||
|
$customer_where
|
||
|
$project
|
||
|
AND c.accno = ?) AS credit,
|
||
|
|
||
|
(SELECT SUM(ac.amount)
|
||
|
FROM acc_trans ac
|
||
|
JOIN chart c ON (ac.chart_id = c.id)
|
||
|
$customer_join
|
||
|
WHERE $saldowhere
|
||
|
$dpt_where_without_arapgl
|
||
|
$dpt_where
|
||
|
$customer_where
|
||
|
$project
|
||
|
AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo,
|
||
|
|
||
|
(SELECT SUM(ac.amount)
|
||
|
FROM acc_trans ac
|
||
|
JOIN chart c ON (ac.chart_id = c.id)
|
||
|
$customer_join
|
||
|
WHERE $sumwhere
|
||
|
$dpt_where_without_arapgl
|
||
|
$dpt_where
|
||
|
$customer_where
|
||
|
$project
|
||
|
AND amount > 0
|
||
|
AND ac.amount > 0
|
||
|
AND c.accno = ?) AS sum_credit,
|
||
|
|
||
|
(SELECT SUM(ac.amount)
|
||
|
FROM acc_trans ac
|
||
|
JOIN chart c ON (ac.chart_id = c.id)
|
||
|
$customer_join
|
||
|
WHERE $sumwhere
|
||
|
$dpt_where
|
||
|
$dpt_where_without_arapgl
|
||
|
$customer_where
|
||
|
$project
|
||
|
AND amount < 0
|
||
|
AND ac.amount < 0
|
||
|
AND c.accno = ?) AS sum_debit,
|
||
|
|
||
|
|
||
|
(SELECT max(ac.transdate) FROM acc_trans ac
|
||
|
JOIN chart c ON (ac.chart_id = c.id)
|
||
|
$customer_join
|
||
|
WHERE $where
|
||
|
$dpt_where_without_arapgl
|
||
|
$dpt_where
|
||
|
$customer_where
|
||
|
$project
|
||
|
AND c.accno = ?) AS last_transaction
|
||
|
|;
|
||
| bin/mozilla/rp.pl | ||
|---|---|---|
|
|
||
|
use SL::DB::Default;
|
||
|
use SL::DB::Project;
|
||
|
use SL::DB::Customer;
|
||
|
use SL::PE;
|
||
|
use SL::RP;
|
||
|
use SL::Iconv;
|
||
| ... | ... | |
|
);
|
||
|
|
||
|
$::form->{title} = $title{$::form->{report}};
|
||
|
$::request->{layout}->add_javascripts('autocomplete_customer.js');
|
||
|
|
||
|
# get departments
|
||
|
$::form->all_departments(\%::myconfig);
|
||
| ... | ... | |
|
$form->{company} = $locale->text('Company') . " " . $defaults->company;
|
||
|
push (@options, $form->{company});
|
||
|
|
||
|
if ($::form->{customer_id}) {
|
||
|
my $customer = SL::DB::Manager::Customer->find_by(id => $::form->{customer_id});
|
||
|
push @options, $::locale->text('Customer') . ' ' . $customer->displayable_name;
|
||
|
}
|
||
|
|
||
|
|
||
|
$form->{template_to} = $locale->date(\%myconfig, $form->{todate}, 0);
|
||
|
|
||
| templates/webpages/rp/report.html | ||
|---|---|---|
|
</td>
|
||
|
</tr>
|
||
|
[%- END %]
|
||
|
[%- BLOCK customer %]
|
||
|
<tr>
|
||
|
<th align=right nowrap>[% 'Customer' | $T8 %]</th>
|
||
|
<td colspan=3>[% L.customer_picker('customer_id') %]</td>
|
||
|
</tr>
|
||
|
[%- END %]
|
||
|
[%- BLOCK projectnumber %]
|
||
|
<tr>
|
||
|
<th align=right nowrap>[% 'Project' | $T8 %]</th>
|
||
| ... | ... | |
|
[%- END %]
|
||
|
|
||
|
[%- IF is_trial_balance %]
|
||
|
[%- PROCESS customer %]
|
||
|
[%- PROCESS projectnumber %]
|
||
|
<input type=hidden name=nextsub value=generate_trial_balance>
|
||
|
</table>
|
||
Auch abrufbar als: Unified diff
Kundenauswahl bei Summen-/Saldenliste