summaryrefslogtreecommitdiff
path: root/LedgerSMB
diff options
context:
space:
mode:
Diffstat (limited to 'LedgerSMB')
-rw-r--r--LedgerSMB/CA.pm66
-rw-r--r--LedgerSMB/RP.pm18
2 files changed, 82 insertions, 2 deletions
diff --git a/LedgerSMB/CA.pm b/LedgerSMB/CA.pm
index 389550ae..fa10a985 100644
--- a/LedgerSMB/CA.pm
+++ b/LedgerSMB/CA.pm
@@ -43,10 +43,22 @@ sub all_accounts {
# connect to database
my $dbh = $form->{dbh};
+ my $approved = ($form->{approved})? 'TRUE' : 'FALSE';
+
my $query = qq|
SELECT accno, SUM(acc_trans.amount) AS amount
- FROM chart, acc_trans
- WHERE chart.id = acc_trans.chart_id
+ FROM chart
+ JOIN acc_trans ON (chart.id = acc_trans.chart_id)
+ JOIN transactions ON (acc_trans.trans_id = transactions.id)
+ JOIN (SELECT id, approved, 'ap' AS tablename FROM ap
+ UNION
+ SELECT id, approved, 'ar' as tablename FROM ar
+ UNION
+ SELECT id, approved, 'gl' as tablename FROM gl
+ ) g ON (g.id = acc_trans.trans_id
+ AND transactions.table_name = g.tablename)
+ WHERE ($approved OR acc_trans.approved)
+ AND (g.approved OR $approved)
GROUP BY accno|;
my $sth = $dbh->prepare($query);
@@ -108,6 +120,7 @@ sub all_transactions {
# connect to database
my $dbh = $form->{dbh};
+ my $approved = ($form->{approved})? 'TRUE' : 'FALSE';
# get chart_id
my $query = qq|
@@ -246,6 +259,9 @@ sub all_transactions {
AND
a.department_id
= ?
+ AND ($approved OR
+ (a.approved
+ AND ac.approved))
$project |;
push @queryargs, $form->{gifi_accno}, $form->{fromdate},
@@ -272,6 +288,9 @@ sub all_transactions {
AND
a.department_id
= ?
+ AND ($approved OR
+ (a.approved
+ AND ac.approved))
$project |;
push @queryargs, $form->{accno}, $form->{fromdate},
$department_id;
@@ -292,8 +311,27 @@ sub all_transactions {
FROM acc_trans ac
JOIN chart c ON
(ac.chart_id = c.id)
+ JOIN transactions t ON
+ (t.id = ac.trans_id)
+ JOIN (SELECT id, approved
+ 'ar' AS tablename
+ FROM ar
+ UNION
+ SELECT id, approved,
+ 'ap' AS tablename
+ FROM ap
+ UNION
+ SELECT id, approved,
+ 'gl' AS tablename
+ FROM gl
+ ) a ON
+ (a.tablename = t.table_name
+ AND a.id = t.id)
WHERE c.gifi_accno = ?
AND ac.transdate < ?
+ AND ($approved OR
+ (a.approved
+ AND ac.approved))
$project |;
@queryargs = ( $form->{gifi_accno}, $form->{fromdate} );
if ($p_id) {
@@ -306,8 +344,27 @@ sub all_transactions {
FROM acc_trans ac
JOIN chart c
ON (ac.chart_id = c.id)
+ JOIN transactions t ON
+ (t.id = ac.trans_id)
+ JOIN (SELECT id, approved,
+ 'ar' AS tablename
+ FROM ar
+ UNION
+ SELECT id, approved,
+ 'ap' AS tablename
+ FROM ap
+ UNION
+ SELECT id, approved,
+ 'gl' AS tablename
+ FROM gl
+ ) a ON
+ (a.tablename = t.table_name
+ AND a.id = t.id)
WHERE c.accno = ?
AND ac.transdate < ?
+ AND ($approved OR
+ (a.approved
+ AND ac.approved))
$project |;
@queryargs = ( $form->{accno}, $form->{fromdate} );
if ($p_id) {
@@ -344,6 +401,7 @@ sub all_transactions {
$fromdate_where
$todate_where
$dpt_where
+ AND ($approved OR (a.approved AND ac.approved))
$project|;
if ($d_id) {
push @queryargs, $d_id;
@@ -373,6 +431,7 @@ sub all_transactions {
JOIN entity e ON (a.entity_id = e.id)
$dpt_join
WHERE ac.chart_id = ?
+ AND ($approved OR (a.approved AND ac.approved))
$fromdate_where
$todate_where
$dpt_where
@@ -406,6 +465,7 @@ sub all_transactions {
JOIN entity e ON (a.entity_id = e.id)
$dpt_join
WHERE ac.chart_id = ?
+ AND ($approved OR (a.approved AND ac.approved))
$fromdate_where
$todate_where
$dpt_where
@@ -440,6 +500,7 @@ sub all_transactions {
JOIN acc_trans ac ON (ac.chart_id = c.id)
WHERE ac.amount >= 0
AND (c.link = 'AR' OR c.link = 'AP')
+ AND ac.approved OR $approved
AND ac.trans_id = ?|;
my $dr = $dbh->prepare($query) || $form->dberror($query);
@@ -449,6 +510,7 @@ sub all_transactions {
JOIN acc_trans ac ON (ac.chart_id = c.id)
WHERE ac.amount < 0
AND (c.link = 'AR' OR c.link = 'AP')
+ AND ac.approved OR $approved
AND ac.trans_id = ?|;
my $cr = $dbh->prepare($query) || $form->dberror($query);
diff --git a/LedgerSMB/RP.pm b/LedgerSMB/RP.pm
index b51d7290..ebca11c3 100644
--- a/LedgerSMB/RP.pm
+++ b/LedgerSMB/RP.pm
@@ -1213,10 +1213,19 @@ sub get_accounts {
SELECT c.accno, sum(ac.amount) AS amount,
c.description, c.category
FROM acc_trans ac
+ JOIN (SELECT id, approved FROM ar
+ UNION
+ SELECT id, approved FROM ap
+ UNION
+ SELECT id, approved FROM gl
+ ) g ON (ac.trans_id = g.id)
JOIN chart c ON (c.id = ac.chart_id)
$dpt_join
WHERE $where $ywhere $dpt_where $category
$project
+ AND ($approved IS NULL OR
+ $approved =
+ (ac.approved AND g.approved))
GROUP BY c.accno, c.description, c.category|;
if ($excludeyearend) {
@@ -1304,6 +1313,7 @@ sub trial_balance {
my ( $self, $myconfig, $form ) = @_;
my $dbh = $form->{dbh};
+ my $approved = ($form->{approved})? 'TRUE' : 'FALSE';
my ( $query, $sth, $ref );
my %balance = ();
@@ -1353,6 +1363,7 @@ sub trial_balance {
$dpt_join
WHERE ac.transdate < '$form->{fromdate}'
$dpt_where $project
+ AND ($approved OR ac.approved)
GROUP BY g.accno, c.category, g.description,
c.contra|;
@@ -1368,6 +1379,7 @@ sub trial_balance {
$dpt_join
WHERE ac.transdate < '$form->{fromdate}'
$dpt_where $project
+ AND ($approved OR ac.approved)
GROUP BY c.accno, c.category, c.description,
c.contra|;
@@ -1447,6 +1459,7 @@ sub trial_balance {
JOIN gifi g ON (c.gifi_accno = g.accno)
$dpt_join
WHERE $where $dpt_where $project
+ AND ($approved OR ac.approved)
GROUP BY g.accno, g.description, c.category, c.contra
ORDER BY accno|;
@@ -1460,6 +1473,7 @@ sub trial_balance {
JOIN chart c ON (c.id = ac.chart_id)
$dpt_join
WHERE $where $dpt_where $project
+ AND $approved OR (ac.approved)
GROUP BY c.accno, c.description, c.category, c.contra
ORDER BY accno|;
@@ -1474,11 +1488,13 @@ sub trial_balance {
JOIN chart c ON (c.id = ac.chart_id)
$dpt_join
WHERE $where $dpt_where $project AND ac.amount < 0
+ AND ($approved OR ac.approved)
AND c.accno = ?) AS debit,
(SELECT SUM(ac.amount) FROM acc_trans ac
JOIN chart c ON (c.id = ac.chart_id)
$dpt_join
WHERE $where $dpt_where $project AND ac.amount > 0
+ AND ($approved OR ac.approved)
AND c.accno = ?) AS credit |;
if ( $form->{accounttype} eq 'gifi' ) {
@@ -1489,6 +1505,7 @@ sub trial_balance {
JOIN chart c ON (c.id = ac.chart_id)
$dpt_join
WHERE $where $dpt_where $project AND ac.amount < 0
+ AND ($approved OR ac.approved)
AND c.gifi_accno = ?) AS debit,
(SELECT SUM(ac.amount)
@@ -1496,6 +1513,7 @@ sub trial_balance {
JOIN chart c ON (c.id = ac.chart_id)
$dpt_join
WHERE $where $dpt_where $project AND ac.amount > 0
+ AND ($approved OR ac.approved)
AND c.gifi_accno = ?) AS credit|;
}
generated by cgit v1.2.3 (git 2.46.0) at 2024-11-23 18:58:14 +0000