Revision dddecda1
Von Moritz Bunkus vor fast 18 Jahren hinzugefügt
| SL/RC.pm | ||
|---|---|---|
| 34 | 34 |
|
| 35 | 35 |
package RC; |
| 36 | 36 |
|
| 37 |
use SL::DBUtils; |
|
| 38 |
|
|
| 37 | 39 |
sub paymentaccounts {
|
| 38 | 40 |
$main::lxdebug->enter_sub(); |
| 39 | 41 |
|
| ... | ... | |
| 42 | 44 |
# connect to database |
| 43 | 45 |
my $dbh = $form->dbconnect($myconfig); |
| 44 | 46 |
|
| 45 |
my $query = qq|SELECT c.accno, c.description |
|
| 46 |
FROM chart c |
|
| 47 |
WHERE c.link LIKE '%_paid%' |
|
| 48 |
AND (c.category = 'A' OR c.category = 'L') |
|
| 49 |
ORDER BY c.accno|; |
|
| 50 |
my $sth = $dbh->prepare($query); |
|
| 51 |
$sth->execute || $form->dberror($query); |
|
| 47 |
my $query = |
|
| 48 |
qq|SELECT accno, description | . |
|
| 49 |
qq|FROM chart | . |
|
| 50 |
qq|WHERE link LIKE '%_paid%' AND category IN ('A', 'L') | .
|
|
| 51 |
qq|ORDER BY accno|; |
|
| 52 | 52 |
|
| 53 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
|
| 54 |
push @{ $form->{PR} }, $ref;
|
|
| 55 |
} |
|
| 56 |
$sth->finish; |
|
| 53 |
$form->{PR} = selectall_hashref_query($form, $dbh, $query);
|
|
| 57 | 54 |
$dbh->disconnect; |
| 58 | 55 |
|
| 59 | 56 |
$main::lxdebug->leave_sub(); |
| ... | ... | |
| 67 | 64 |
# connect to database, turn AutoCommit off |
| 68 | 65 |
my $dbh = $form->dbconnect_noauto($myconfig); |
| 69 | 66 |
|
| 70 |
my ($query, $sth);
|
|
| 67 |
my ($query, @values);
|
|
| 71 | 68 |
|
| 72 | 69 |
# get cleared balance |
| 73 | 70 |
if ($form->{fromdate}) {
|
| 74 |
$query = qq|SELECT sum(a.amount),
|
|
| 75 |
(SELECT DISTINCT c2.category FROM chart c2
|
|
| 76 |
WHERE c2.accno = '$form->{accno}') AS category
|
|
| 77 |
FROM acc_trans a
|
|
| 78 |
JOIN chart c ON (c.id = a.chart_id)
|
|
| 79 |
WHERE a.transdate < date '$form->{fromdate}'
|
|
| 80 |
AND a.cleared = '1'
|
|
| 81 |
AND c.accno = '$form->{accno}'
|
|
| 82 |
|; |
|
| 71 |
$query = |
|
| 72 |
qq|SELECT sum(a.amount), | .
|
|
| 73 |
qq| (SELECT DISTINCT c2.category FROM chart c2 | .
|
|
| 74 |
qq| WHERE c2.accno = ?) AS category | .
|
|
| 75 |
qq|FROM acc_trans a | .
|
|
| 76 |
qq|JOIN chart c ON (c.id = a.chart_id) | .
|
|
| 77 |
qq|WHERE a.transdate < date ? AND a.cleared = '1' AND c.accno = ?|;
|
|
| 78 |
@values = ($form->{accno}, conv_date($form->{fromdate}), $form->{accno});
|
|
| 79 |
|
|
| 83 | 80 |
} else {
|
| 84 |
$query = qq|SELECT sum(a.amount),
|
|
| 85 |
(SELECT DISTINCT c2.category FROM chart c2
|
|
| 86 |
WHERE c2.accno = '$form->{accno}') AS category
|
|
| 87 |
FROM acc_trans a
|
|
| 88 |
JOIN chart c ON (c.id = a.chart_id)
|
|
| 89 |
WHERE a.cleared = '1'
|
|
| 90 |
AND c.accno = '$form->{accno}'
|
|
| 91 |
|;
|
|
| 81 |
$query = |
|
| 82 |
qq|SELECT sum(a.amount), | .
|
|
| 83 |
qq| (SELECT DISTINCT c2.category FROM chart c2 | .
|
|
| 84 |
qq| WHERE c2.accno = ?) AS category | .
|
|
| 85 |
qq|FROM acc_trans a | .
|
|
| 86 |
qq|JOIN chart c ON (c.id = a.chart_id) | .
|
|
| 87 |
qq|WHERE a.cleared = '1' AND c.accno = ?|;
|
|
| 88 |
@values = ($form->{accno}, $form->{accno});
|
|
| 92 | 89 |
} |
| 93 | 90 |
|
| 94 |
$sth = $dbh->prepare($query); |
|
| 95 |
$sth->execute || $form->dberror($query); |
|
| 96 |
|
|
| 97 |
($form->{beginningbalance}, $form->{category}) = $sth->fetchrow_array;
|
|
| 98 |
|
|
| 99 |
$sth->finish; |
|
| 91 |
($form->{beginningbalance}, $form->{category}) =
|
|
| 92 |
selectrow_query($form, $dbh, $query, @values); |
|
| 100 | 93 |
|
| 101 | 94 |
my %oid = ('Pg' => 'ac.oid',
|
| 102 | 95 |
'Oracle' => 'ac.rowid'); |
| 96 |
@values = (); |
|
| 97 |
$query = |
|
| 98 |
qq|SELECT c.name, ac.source, ac.transdate, ac.cleared, | . |
|
| 99 |
qq| ac.fx_transaction, ac.amount, a.id, | . |
|
| 100 |
qq| $oid{$myconfig->{dbdriver}} AS oid | .
|
|
| 101 |
qq|FROM customer c, acc_trans ac, ar a, chart ch | . |
|
| 102 |
qq|WHERE c.id = a.customer_id | . |
|
| 103 |
qq| AND ac.cleared = '0' | . |
|
| 104 |
qq| AND ac.trans_id = a.id | . |
|
| 105 |
qq| AND ac.chart_id = ch.id | . |
|
| 106 |
qq| AND ch.accno = ? |; |
|
| 107 |
push(@values, $form->{accno});
|
|
| 108 |
|
|
| 109 |
if($form->{fromdate}) {
|
|
| 110 |
$query .= qq| AND ac.transdate >= ? |; |
|
| 111 |
push(@values, conv_date($form->{fromdate}));
|
|
| 112 |
} |
|
| 103 | 113 |
|
| 104 |
$query = qq|SELECT c.name, ac.source, ac.transdate, ac.cleared, |
|
| 105 |
ac.fx_transaction, ac.amount, a.id, |
|
| 106 |
$oid{$myconfig->{dbdriver}} AS oid
|
|
| 107 |
FROM customer c, acc_trans ac, ar a, chart ch |
|
| 108 |
WHERE c.id = a.customer_id |
|
| 109 |
-- AND NOT ac.fx_transaction |
|
| 110 |
AND ac.cleared = '0' |
|
| 111 |
AND ac.trans_id = a.id |
|
| 112 |
AND ac.chart_id = ch.id |
|
| 113 |
AND ch.accno = '$form->{accno}'
|
|
| 114 |
|; |
|
| 115 |
|
|
| 116 |
$query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
|
|
| 117 |
$query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
|
|
| 118 |
|
|
| 119 |
$query .= qq| |
|
| 120 |
|
|
| 121 |
UNION |
|
| 122 |
SELECT v.name, ac.source, ac.transdate, ac.cleared, |
|
| 123 |
ac.fx_transaction, ac.amount, a.id, |
|
| 124 |
$oid{$myconfig->{dbdriver}} AS oid
|
|
| 125 |
FROM vendor v, acc_trans ac, ap a, chart ch |
|
| 126 |
WHERE v.id = a.vendor_id |
|
| 127 |
-- AND NOT ac.fx_transaction |
|
| 128 |
AND ac.cleared = '0' |
|
| 129 |
AND ac.trans_id = a.id |
|
| 130 |
AND ac.chart_id = ch.id |
|
| 131 |
AND ch.accno = '$form->{accno}'
|
|
| 132 |
|; |
|
| 133 |
|
|
| 134 |
$query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
|
|
| 135 |
$query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
|
|
| 136 |
|
|
| 137 |
$query .= qq| |
|
| 138 |
|
|
| 139 |
UNION |
|
| 140 |
SELECT g.description, ac.source, ac.transdate, ac.cleared, |
|
| 141 |
ac.fx_transaction, ac.amount, g.id, |
|
| 142 |
$oid{$myconfig->{dbdriver}} AS oid
|
|
| 143 |
FROM gl g, acc_trans ac, chart ch |
|
| 144 |
WHERE g.id = ac.trans_id |
|
| 145 |
-- AND NOT ac.fx_transaction |
|
| 146 |
AND ac.cleared = '0' |
|
| 147 |
AND ac.trans_id = g.id |
|
| 148 |
AND ac.chart_id = ch.id |
|
| 149 |
AND ch.accno = '$form->{accno}'
|
|
| 150 |
|; |
|
| 151 |
|
|
| 152 |
$query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
|
|
| 153 |
$query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
|
|
| 114 |
if($form->{todate}){
|
|
| 115 |
$query .= qq| AND ac.transdate <= ? |; |
|
| 116 |
push(@values, conv_date($form->{todate}));
|
|
| 117 |
} |
|
| 154 | 118 |
|
| 155 |
$query .= " ORDER BY 3,7,8"; |
|
| 119 |
$query .= |
|
| 120 |
qq|UNION | . |
|
| 121 |
|
|
| 122 |
qq|SELECT v.name, ac.source, ac.transdate, ac.cleared, | . |
|
| 123 |
qq| ac.fx_transaction, ac.amount, a.id, | . |
|
| 124 |
qq| $oid{$myconfig->{dbdriver}} AS oid | .
|
|
| 125 |
qq|FROM vendor v, acc_trans ac, ap a, chart ch | . |
|
| 126 |
qq|WHERE v.id = a.vendor_id | . |
|
| 127 |
qq| AND ac.cleared = '0' | . |
|
| 128 |
qq| AND ac.trans_id = a.id | . |
|
| 129 |
qq| AND ac.chart_id = ch.id | . |
|
| 130 |
qq| AND ch.accno = ? |; |
|
| 156 | 131 |
|
| 157 |
$sth = $dbh->prepare($query); |
|
| 158 |
$sth->execute || $form->dberror($query); |
|
| 132 |
push(@values, $form->{accno});
|
|
| 133 |
|
|
| 134 |
if($form->{fromdate}) {
|
|
| 135 |
$query .= qq| AND ac.transdate >= ? |; |
|
| 136 |
push(@values, conv_date($form->{fromdate}));
|
|
| 137 |
} |
|
| 159 | 138 |
|
| 160 |
while (my $pr = $sth->fetchrow_hashref(NAME_lc)) {
|
|
| 161 |
push @{ $form->{PR} }, $pr;
|
|
| 139 |
if($form->{todate}){
|
|
| 140 |
$query .= qq| AND ac.transdate <= ? |; |
|
| 141 |
push(@values, conv_date($form->{todate}));
|
|
| 162 | 142 |
} |
| 163 |
$sth->finish; |
|
| 143 |
|
|
| 144 |
$query .= |
|
| 145 |
qq|UNION | . |
|
| 146 |
|
|
| 147 |
qq|SELECT g.description, ac.source, ac.transdate, ac.cleared, | . |
|
| 148 |
qq| ac.fx_transaction, ac.amount, g.id, | . |
|
| 149 |
qq| $oid{$myconfig->{dbdriver}} AS oid | .
|
|
| 150 |
qq|FROM gl g, acc_trans ac, chart ch | . |
|
| 151 |
qq|WHERE g.id = ac.trans_id | . |
|
| 152 |
qq| AND ac.cleared = '0' | . |
|
| 153 |
qq| AND ac.trans_id = g.id | . |
|
| 154 |
qq| AND ac.chart_id = ch.id | . |
|
| 155 |
qq| AND ch.accno = ? |; |
|
| 156 |
|
|
| 157 |
push(@values, $form->{accno});
|
|
| 158 |
|
|
| 159 |
if($form->{fromdate}) {
|
|
| 160 |
$query .= qq| AND ac.transdate >= ? |; |
|
| 161 |
push(@values, conv_date($form->{fromdate}));
|
|
| 162 |
} |
|
| 163 |
|
|
| 164 |
if($form->{todate}){
|
|
| 165 |
$query .= qq| AND ac.transdate <= ? |; |
|
| 166 |
push(@values, conv_date($form->{todate}));
|
|
| 167 |
} |
|
| 168 |
|
|
| 169 |
$query .= " ORDER BY 3,7,8"; |
|
| 170 |
|
|
| 171 |
$form->{PR} = selectall_hashref_query($form, $dbh, $query, @values);
|
|
| 164 | 172 |
|
| 165 | 173 |
$dbh->disconnect; |
| 166 | 174 |
|
| ... | ... | |
| 182 | 190 |
# clear flags |
| 183 | 191 |
for $i (1 .. $form->{rowcount}) {
|
| 184 | 192 |
if ($form->{"cleared_$i"}) {
|
| 185 |
$query = qq|UPDATE acc_trans SET cleared = '1' |
|
| 186 |
WHERE $oid{$myconfig->{dbdriver}} = $form->{"oid_$i"}|;
|
|
| 187 |
$dbh->do($query) || $form->dberror($query); |
|
| 193 |
$query = |
|
| 194 |
qq|UPDATE acc_trans SET cleared = '1' | . |
|
| 195 |
qq|WHERE $oid{$myconfig->{dbdriver}} = ?|;
|
|
| 196 |
do_query($form, $dbh, $query, $form->{"oid_$i"});
|
|
| 188 | 197 |
|
| 189 | 198 |
# clear fx_transaction |
| 190 | 199 |
if ($form->{"fxoid_$i"}) {
|
| 191 |
$query = qq|UPDATE acc_trans SET cleared = '1' |
|
| 192 |
WHERE $oid{$myconfig->{dbdriver}} = $form->{"fxoid_$i"}|;
|
|
| 193 |
$dbh->do($query) || $form->dberror($query); |
|
| 200 |
$query = |
|
| 201 |
qq|UPDATE acc_trans SET cleared = '1' | . |
|
| 202 |
qq|WHERE $oid{$myconfig->{dbdriver}} = ?|;
|
|
| 203 |
do_query($form, $dbh, $query, $form->{"fxoid_$i"});
|
|
| 194 | 204 |
} |
| 195 | 205 |
} |
| 196 | 206 |
} |
Auch abrufbar als: Unified diff
Keine Form-Variablen direkt in SQL-Queries verwenden.