Revision bb374138
Von Moritz Bunkus vor mehr als 17 Jahren hinzugefügt
| SL/BP.pm | ||
|---|---|---|
| 34 | 34 |
|
| 35 | 35 |
package BP; |
| 36 | 36 |
|
| 37 |
use SL::DBUtils; |
|
| 38 |
|
|
| 37 | 39 |
sub get_vc {
|
| 38 | 40 |
$main::lxdebug->enter_sub(); |
| 39 | 41 |
|
| ... | ... | |
| 51 | 53 |
check => 'ap', |
| 52 | 54 |
receipt => 'ar'); |
| 53 | 55 |
|
| 54 |
$query = qq|SELECT count(*) |
|
| 55 |
FROM (SELECT DISTINCT ON (vc.id) vc.id |
|
| 56 |
FROM $form->{vc} vc, $arap{$form->{type}} a, status s
|
|
| 57 |
WHERE a.$form->{vc}_id = vc.id
|
|
| 58 |
AND s.trans_id = a.id |
|
| 59 |
AND s.formname = '$form->{type}'
|
|
| 60 |
AND s.spoolfile IS NOT NULL) AS total|; |
|
| 56 |
my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
|
|
| 57 |
my $arap_type = defined($arap{$form->{type}}) ? $arap{$form->{type}} : 'ar';
|
|
| 61 | 58 |
|
| 62 |
my $sth = $dbh->prepare($query); |
|
| 63 |
$sth->execute || $form->dberror($query); |
|
| 64 |
my ($count) = $sth->fetchrow_array; |
|
| 65 |
$sth->finish; |
|
| 59 |
$query = |
|
| 60 |
qq|SELECT count(*) | . |
|
| 61 |
qq|FROM (SELECT DISTINCT ON (vc.id) vc.id FROM $vc vc, $arap_type a, status s | . |
|
| 62 |
qq| WHERE a.${vc}_id = vc.id AND s.trans_id = a.id AND s.formname = ? | .
|
|
| 63 |
qq| AND s.spoolfile IS NOT NULL) AS total|; |
|
| 64 |
|
|
| 65 |
$main::lxdebug->message(0, "kuh1 $query"); |
|
| 66 |
|
|
| 67 |
my ($count) = selectrow_query($form, $dbh, $query, $form->{type});
|
|
| 66 | 68 |
|
| 67 | 69 |
# build selection list |
| 68 | 70 |
if ($count < $myconfig->{vclimit}) {
|
| 69 |
$query = qq|SELECT DISTINCT ON (vc.id) vc.id, vc.name |
|
| 70 |
FROM $form->{vc} vc, $arap{$form->{type}} a, status s
|
|
| 71 |
WHERE a.$form->{vc}_id = vc.id
|
|
| 72 |
AND s.trans_id = a.id |
|
| 73 |
AND s.formname = '$form->{type}'
|
|
| 74 |
AND s.spoolfile IS NOT NULL|; |
|
| 75 |
} |
|
| 76 |
$sth = $dbh->prepare($query); |
|
| 77 |
$sth->execute || $form->dberror($query); |
|
| 71 |
$query = |
|
| 72 |
qq|SELECT DISTINCT ON (vc.id) vc.id, vc.name | . |
|
| 73 |
qq|FROM $vc vc, $arap_type a, status s | . |
|
| 74 |
qq|WHERE a.${vc}_id = vc.id AND s.trans_id = a.id AND s.formname = ? | .
|
|
| 75 |
qq| AND s.spoolfile IS NOT NULL|; |
|
| 76 |
|
|
| 77 |
$sth = $dbh->prepare($query); |
|
| 78 |
$sth->execute($form->{type}) || $form->dberror($query . " ($form->{type})");
|
|
| 79 |
|
|
| 80 |
$form->{"all_${vc}"} = [];
|
|
| 81 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
|
| 82 |
push @{ $form->{"all_${vc}"} }, $ref;
|
|
| 83 |
} |
|
| 84 |
$sth->finish; |
|
| 78 | 85 |
|
| 79 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
|
| 80 |
push @{ $form->{"all_$form->{vc}"} }, $ref;
|
|
| 86 |
$main::lxdebug->message(0, "kuh2 $query"); |
|
| 81 | 87 |
} |
| 82 | 88 |
|
| 83 |
$sth->finish; |
|
| 84 | 89 |
$dbh->disconnect; |
| 85 | 90 |
|
| 86 | 91 |
$main::lxdebug->leave_sub(); |
| ... | ... | |
| 94 | 99 |
# connect to database |
| 95 | 100 |
my $dbh = $form->dbconnect($myconfig); |
| 96 | 101 |
|
| 97 |
my $query = qq|SELECT DISTINCT ON (s.chart_id) c.accno, c.description
|
|
| 98 |
FROM status s, chart c
|
|
| 99 |
WHERE s.chart_id = c.id
|
|
| 100 |
AND s.formname = '$form->{type}'|;
|
|
| 102 |
my $query = |
|
| 103 |
qq|SELECT DISTINCT ON (s.chart_id) c.accno, c.description | .
|
|
| 104 |
qq|FROM status s, chart c | .
|
|
| 105 |
qq|WHERE s.chart_id = c.id AND s.formname = ?|;
|
|
| 101 | 106 |
my $sth = $dbh->prepare($query); |
| 102 |
$sth->execute || $form->dberror($query);
|
|
| 107 |
$sth->execute($form->{type}) || $form->dberror($query . " ($form->{type})");
|
|
| 103 | 108 |
|
| 109 |
$form->{accounts} = [];
|
|
| 104 | 110 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
| 105 | 111 |
push @{ $form->{accounts} }, $ref;
|
| 106 | 112 |
} |
| ... | ... | |
| 119 | 125 |
# connect to database |
| 120 | 126 |
my $dbh = $form->dbconnect($myconfig); |
| 121 | 127 |
|
| 122 |
my ($query, $arap); |
|
| 128 |
my ($query, $arap, @values);
|
|
| 123 | 129 |
my $invnumber = "invnumber"; |
| 124 | 130 |
|
| 131 |
my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
|
|
| 132 |
|
|
| 125 | 133 |
if ($form->{type} eq 'check' || $form->{type} eq 'receipt') {
|
| 126 | 134 |
|
| 127 | 135 |
$arap = ($form->{type} eq 'check') ? "ap" : "ar";
|
| 128 | 136 |
my ($accno) = split /--/, $form->{account};
|
| 129 | 137 |
|
| 130 |
$query = qq|SELECT a.id, s.spoolfile, vc.name, ac.transdate, a.invnumber, |
|
| 131 |
a.invoice, '$arap' AS module |
|
| 132 |
FROM status s, chart c, $form->{vc} vc, $arap a, acc_trans ac
|
|
| 133 |
WHERE s.formname = '$form->{type}'
|
|
| 134 |
AND s.chart_id = c.id |
|
| 135 |
AND c.accno = '$accno' |
|
| 136 |
AND s.trans_id = a.id |
|
| 137 |
AND a.$form->{vc}_id = vc.id
|
|
| 138 |
AND ac.trans_id = s.trans_id |
|
| 139 |
AND ac.chart_id = c.id |
|
| 140 |
AND NOT ac.fx_transaction|; |
|
| 141 |
} else {
|
|
| 138 |
$query = |
|
| 139 |
qq|SELECT a.id, s.spoolfile, vc.name, ac.transdate, a.invnumber, | . |
|
| 140 |
qq| a.invoice, '$arap' AS module | . |
|
| 141 |
qq|FROM status s, chart c, $vc vc, $arap a, acc_trans ac | . |
|
| 142 |
qq|WHERE s.formname = ? | . |
|
| 143 |
qq| AND s.chart_id = c.id | . |
|
| 144 |
qq| AND c.accno = ? | . |
|
| 145 |
qq| AND s.trans_id = a.id | . |
|
| 146 |
qq| AND a.${vc}_id = vc.id | .
|
|
| 147 |
qq| AND ac.trans_id = s.trans_id | . |
|
| 148 |
qq| AND ac.chart_id = c.id | . |
|
| 149 |
qq| AND NOT ac.fx_transaction|; |
|
| 150 |
@values = ($form->{type}, $accno);
|
|
| 142 | 151 |
|
| 152 |
} else {
|
|
| 143 | 153 |
$arap = "ar"; |
| 144 | 154 |
my $invoice = "a.invoice"; |
| 145 | 155 |
|
| ... | ... | |
| 149 | 159 |
$invoice = '0'; |
| 150 | 160 |
} |
| 151 | 161 |
|
| 152 |
$query = qq|SELECT a.id, a.$invnumber AS invnumber, a.ordnumber, |
|
| 153 |
a.quonumber, a.transdate, $invoice AS invoice, |
|
| 154 |
'$arap' AS module, vc.name, s.spoolfile |
|
| 155 |
FROM $arap a, $form->{vc} vc, status s
|
|
| 156 |
WHERE s.trans_id = a.id |
|
| 157 |
AND s.spoolfile IS NOT NULL |
|
| 158 |
AND s.formname = '$form->{type}'
|
|
| 159 |
AND a.$form->{vc}_id = vc.id|;
|
|
| 162 |
$query = |
|
| 163 |
qq|SELECT a.id, a.$invnumber AS invnumber, a.ordnumber, a.quonumber, | . |
|
| 164 |
qq| a.transdate, $invoice AS invoice, '$arap' AS module, vc.name, | . |
|
| 165 |
qq| s.spoolfile | . |
|
| 166 |
qq|FROM $arap a, ${vc} vc, status s | .
|
|
| 167 |
qq|WHERE s.trans_id = a.id | . |
|
| 168 |
qq| AND s.spoolfile IS NOT NULL | . |
|
| 169 |
qq| AND s.formname = ? | . |
|
| 170 |
qq| AND a.${vc}_id = vc.id|;
|
|
| 171 |
@values = ($form->{type});
|
|
| 160 | 172 |
} |
| 161 | 173 |
|
| 162 |
if ($form->{"$form->{vc}_id"}) {
|
|
| 163 |
$query .= qq| AND a.$form->{vc}_id = $form->{"$form->{vc}_id"}|;
|
|
| 164 |
} else {
|
|
| 165 |
if ($form->{ $form->{vc} }) {
|
|
| 166 |
my $name = $form->like(lc $form->{ $form->{vc} });
|
|
| 167 |
$query .= " AND lower(vc.name) LIKE '$name'"; |
|
| 168 |
} |
|
| 174 |
if ($form->{"${vc}_id"}) {
|
|
| 175 |
$query .= qq| AND a.${vc}_id = ?|;
|
|
| 176 |
push(@values, conv_i($form->{"${vc}_id"}));
|
|
| 177 |
} elsif ($form->{ $vc }) {
|
|
| 178 |
$query .= " AND vc.name ILIKE ?"; |
|
| 179 |
push(@values, $form->like($form->{ $vc }));
|
|
| 169 | 180 |
} |
| 170 |
if ($form->{invnumber}) {
|
|
| 171 |
my $number = $form->like(lc $form->{invnumber});
|
|
| 172 |
$query .= " AND lower(a.invnumber) LIKE '$number'"; |
|
| 173 |
} |
|
| 174 |
if ($form->{ordnumber}) {
|
|
| 175 |
my $ordnumber = $form->like(lc $form->{ordnumber});
|
|
| 176 |
$query .= " AND lower(a.ordnumber) LIKE '$ordnumber'"; |
|
| 177 |
} |
|
| 178 |
if ($form->{quonumber}) {
|
|
| 179 |
my $quonumber = $form->like(lc $form->{quonumber});
|
|
| 180 |
$query .= " AND lower(a.quonumber) LIKE '$quonumber'"; |
|
| 181 |
foreach my $column (qw(invnumber ordnumber quonumber)) {
|
|
| 182 |
if ($form->{$column}) {
|
|
| 183 |
$query .= " AND a.$column ILIKE ?"; |
|
| 184 |
push(@values, $form->like($form->{$column}));
|
|
| 185 |
} |
|
| 181 | 186 |
} |
| 182 | 187 |
|
| 183 | 188 |
if ($form->{type} =~ /(invoice|sales_order|sales_quotation|packing_list|puchase_order|request_quotation)$/) {
|
| 184 |
$query .= " AND a.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
|
|
| 185 |
$query .= " AND a.transdate <= '$form->{transdateto}'" if $form->{transdateto};
|
|
| 189 |
if ($form->{transdatefrom}) {
|
|
| 190 |
$query .= " AND a.transdate >= ?"; |
|
| 191 |
push(@values, $form->{transdatefrom});
|
|
| 192 |
} |
|
| 193 |
if ($form->{transdateto}) {
|
|
| 194 |
$query .= " AND a.transdate <= ?"; |
|
| 195 |
push(@values, $form->{transdateto});
|
|
| 196 |
} |
|
| 186 | 197 |
} |
| 187 | 198 |
|
| 188 | 199 |
my @a = (transdate, $invnumber, name); |
| 189 | 200 |
my $sortorder = join ', ', $form->sort_columns(@a); |
| 190 |
$sortorder = $form->{sort} if $form->{sort};
|
|
| 191 | 201 |
|
| 192 |
$query .= " ORDER by $sortorder"; |
|
| 202 |
if (grep({ $_ eq $form->{sort} }
|
|
| 203 |
qw(transdate invnumber ordnumber quonumber name))) {
|
|
| 204 |
$sortorder = $form->{sort};
|
|
| 205 |
} |
|
| 206 |
|
|
| 207 |
$query .= " ORDER BY $sortorder"; |
|
| 193 | 208 |
|
| 194 | 209 |
my $sth = $dbh->prepare($query); |
| 195 |
$sth->execute || $form->dberror($query); |
|
| 210 |
$sth->execute(@values) || |
|
| 211 |
$form->dberror($query . " (" . join(", ", @values) . ")");
|
|
| 196 | 212 |
|
| 213 |
$form->{SPOOL} = [];
|
|
| 197 | 214 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
| 198 | 215 |
push @{ $form->{SPOOL} }, $ref;
|
| 199 | 216 |
} |
| ... | ... | |
| 215 | 232 |
my $query; |
| 216 | 233 |
|
| 217 | 234 |
if ($form->{type} =~ /(check|receipt)/) {
|
| 218 |
$query = qq|DELETE FROM status |
|
| 219 |
WHERE spoolfile = ?|; |
|
| 235 |
$query = qq|DELETE FROM status WHERE spoolfile = ?|; |
|
| 220 | 236 |
} else {
|
| 221 |
$query = qq|UPDATE status SET |
|
| 222 |
spoolfile = NULL, |
|
| 223 |
printed = '1' |
|
| 224 |
WHERE spoolfile = ?|; |
|
| 237 |
$query = |
|
| 238 |
qq|UPDATE status SET spoolfile = NULL, printed = '1' | . |
|
| 239 |
qq|WHERE spoolfile = ?|; |
|
| 225 | 240 |
} |
| 226 | 241 |
my $sth = $dbh->prepare($query) || $form->dberror($query); |
| 227 | 242 |
|
| ... | ... | |
| 238 | 253 |
|
| 239 | 254 |
if ($rc) {
|
| 240 | 255 |
foreach my $i (1 .. $form->{rowcount}) {
|
| 241 |
$_ = qq|$spool/$form->{"spoolfile_$i"}|;
|
|
| 242 | 256 |
if ($form->{"checked_$i"}) {
|
| 243 |
unlink; |
|
| 257 |
unlink(qq|$spool/$form->{"spoolfile_$i"}|);
|
|
| 244 | 258 |
} |
| 245 | 259 |
} |
| 246 | 260 |
} |
| ... | ... | |
| 258 | 272 |
# connect to database |
| 259 | 273 |
my $dbh = $form->dbconnect($myconfig); |
| 260 | 274 |
|
| 261 |
my $query = qq|UPDATE status SET |
|
| 262 |
printed = '1' |
|
| 263 |
WHERE formname = '$form->{type}'
|
|
| 264 |
AND spoolfile = ?|; |
|
| 275 |
my $query = |
|
| 276 |
qq|UPDATE status SET printed = '1' | . |
|
| 277 |
qq|WHERE formname = ? AND spoolfile = ?|; |
|
| 265 | 278 |
my $sth = $dbh->prepare($query) || $form->dberror($query); |
| 266 | 279 |
|
| 267 | 280 |
foreach my $i (1 .. $form->{rowcount}) {
|
| ... | ... | |
| 279 | 292 |
close(IN); |
| 280 | 293 |
close(OUT); |
| 281 | 294 |
|
| 282 |
$sth->execute($form->{"spoolfile_$i"}) || $form->dberror($query);
|
|
| 295 |
$sth->execute($form->{type}, $form->{"spoolfile_$i"}) ||
|
|
| 296 |
$form->dberror($query . " ($form->{type}, " . $form->{"spoolfile_$i"} . ")");
|
|
| 283 | 297 |
$sth->finish; |
| 284 | 298 |
|
| 285 | 299 |
} |
Auch abrufbar als: Unified diff
Vermeidung von SQL injection durch Verwendung parametrisierter Abfragen.