Revision 05fea791
Von Moritz Bunkus vor mehr als 17 Jahren hinzugefügt
| SL/GL.pm | ||
|---|---|---|
| 48 | 48 |
# connect to database |
| 49 | 49 |
my $dbh = $form->dbconnect_noauto($myconfig); |
| 50 | 50 |
|
| 51 |
my $query = qq|DELETE FROM gl WHERE id = $form->{id}|;
|
|
| 52 |
$dbh->do($query) || $form->dberror($query); |
|
| 53 |
|
|
| 54 |
$query = qq|DELETE FROM acc_trans WHERE trans_id = $form->{id}|;
|
|
| 55 |
$dbh->do($query) || $form->dberror($query); |
|
| 51 |
my @values = (conv_i($form->{id}));
|
|
| 52 |
do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values); |
|
| 53 |
do_query($form, $dbh, qq|DELETE FROM gl WHERE id = ?|, @values); |
|
| 56 | 54 |
|
| 57 | 55 |
# commit and redirect |
| 58 | 56 |
my $rc = $dbh->commit; |
| ... | ... | |
| 91 | 89 |
# if there is a $form->{id} replace the old transaction
|
| 92 | 90 |
# delete all acc_trans entries and add the new ones |
| 93 | 91 |
|
| 94 |
# escape ' |
|
| 95 |
map { $form->{$_} =~ s/\'/\'\'/g } qw(reference description notes);
|
|
| 96 |
|
|
| 97 | 92 |
if (!$form->{taxincluded}) {
|
| 98 | 93 |
$form->{taxincluded} = 0;
|
| 99 | 94 |
} |
| ... | ... | |
| 103 | 98 |
if ($form->{id}) {
|
| 104 | 99 |
|
| 105 | 100 |
# delete individual transactions |
| 106 |
$query = qq|DELETE FROM acc_trans |
|
| 107 |
WHERE trans_id = $form->{id}|;
|
|
| 108 |
$dbh->do($query) || $form->dberror($query);
|
|
| 101 |
$query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;
|
|
| 102 |
@values = (conv_i($form->{id}));
|
|
| 103 |
do_query($form, $dbh, $query, @values);
|
|
| 109 | 104 |
|
| 110 | 105 |
} else {
|
| 111 |
my $uid = time; |
|
| 112 |
$uid .= $form->{login};
|
|
| 113 |
|
|
| 114 |
$query = qq|INSERT INTO gl (reference, employee_id) |
|
| 115 |
VALUES ('$uid', (SELECT e.id FROM employee e
|
|
| 116 |
WHERE e.login = '$form->{login}'))|;
|
|
| 117 |
$dbh->do($query) || $form->dberror($query); |
|
| 118 |
|
|
| 119 |
$query = qq|SELECT g.id FROM gl g |
|
| 120 |
WHERE g.reference = '$uid'|; |
|
| 121 |
$sth = $dbh->prepare($query); |
|
| 122 |
$sth->execute || $form->dberror($query); |
|
| 123 |
|
|
| 124 |
($form->{id}) = $sth->fetchrow_array;
|
|
| 125 |
$sth->finish; |
|
| 106 |
$query = qq|SELECT nextval('glid')|;
|
|
| 107 |
($form->{id}) = selectrow_query($form, $dbh, $query);
|
|
| 126 | 108 |
|
| 109 |
$query = |
|
| 110 |
qq|INSERT INTO gl (id, employee_id) | . |
|
| 111 |
qq|VALUES (?, (SELECT id FROM employee WHERE login = ?))|; |
|
| 112 |
@values = ($form->{id}, $form->{login});
|
|
| 113 |
do_query($form, $dbh, $query, @values); |
|
| 127 | 114 |
} |
| 128 | 115 |
|
| 129 |
my ($null, $department_id) = split /--/, $form->{department};
|
|
| 116 |
my ($null, $department_id) = split(/--/, $form->{department});
|
|
| 130 | 117 |
$department_id *= 1; |
| 131 | 118 |
|
| 132 |
$query = qq|UPDATE gl SET |
|
| 133 |
reference = '$form->{reference}',
|
|
| 134 |
description = '$form->{description}',
|
|
| 135 |
notes = '$form->{notes}',
|
|
| 136 |
transdate = '$form->{transdate}',
|
|
| 137 |
department_id = $department_id, |
|
| 138 |
taxincluded = '$form->{taxincluded}'
|
|
| 139 |
WHERE id = $form->{id}|;
|
|
| 119 |
$query = |
|
| 120 |
qq|UPDATE gl SET |
|
| 121 |
reference = ?, description = ?, notes = ?, |
|
| 122 |
transdate = ?, department_id = ?, taxincluded = ? |
|
| 123 |
WHERE id = ?|; |
|
| 140 | 124 |
|
| 141 |
$dbh->do($query) || $form->dberror($query); |
|
| 142 |
($taxkey, $rate) = split(/--/, $form->{taxkey});
|
|
| 125 |
@values = ($form->{reference}, $form->{description}, $form->{notes},
|
|
| 126 |
conv_date($form->{transdate}), $department_id, $form->{taxincluded},
|
|
| 127 |
conv_i($form->{id}));
|
|
| 128 |
do_query($form, $dbh, $query, @values); |
|
| 143 | 129 |
|
| 144 | 130 |
# insert acc_trans transactions |
| 145 | 131 |
for $i (1 .. $form->{rowcount}) {
|
| 146 |
my $taxkey; |
|
| 147 |
my $rate; |
|
| 148 | 132 |
# extract accno |
| 149 |
print(STDERR $form->{"taxchart_$i"}, "TAXCHART\n");
|
|
| 150 | 133 |
my ($accno) = split(/--/, $form->{"accno_$i"});
|
| 151 |
my ($taxkey, $rate) = split(/--/, $form->{"taxchart_$i"});
|
|
| 152 |
($form->{"tax_id_$i"}, $NULL) = split /--/, $form->{"taxchart_$i"};
|
|
| 134 |
($form->{"tax_id_$i"}) = split(/--/, $form->{"taxchart_$i"});
|
|
| 153 | 135 |
if ($form->{"tax_id_$i"} ne "") {
|
| 154 |
$query = qq|SELECT t.taxkey, t.rate |
|
| 155 |
FROM tax t |
|
| 156 |
WHERE t.id=$form->{"tax_id_$i"}|;
|
|
| 157 |
|
|
| 158 |
$sth = $dbh->prepare($query); |
|
| 159 |
$sth->execute || $form->dberror($query); |
|
| 160 |
($taxkey, $rate) = |
|
| 161 |
$sth->fetchrow_array; |
|
| 162 |
$sth->finish; |
|
| 136 |
$query = qq|SELECT taxkey, rate FROM tax WHERE id = ?|; |
|
| 137 |
($taxkey, $rate) = selectrow_query($form, $dbh, $query, conv_i($form->{"tax_id_$i"}));
|
|
| 163 | 138 |
} |
| 164 | 139 |
|
| 165 | 140 |
my $amount = 0; |
| ... | ... | |
| 181 | 156 |
|
| 182 | 157 |
# if there is an amount, add the record |
| 183 | 158 |
if ($amount != 0) {
|
| 184 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, |
|
| 185 |
source, memo, project_id, taxkey) |
|
| 186 |
VALUES |
|
| 187 |
($form->{id}, (SELECT c.id
|
|
| 188 |
FROM chart c |
|
| 189 |
WHERE c.accno = '$accno'), |
|
| 190 |
$amount, '$form->{transdate}', |
|
|
| 191 |
. $dbh->quote($form->{"source_$i"}) . qq|, |
|
|
| 192 |
. $dbh->quote($form->{"memo_$i"}) . qq|,
|
|
| 193 |
?, $taxkey)|; |
|
| 194 |
|
|
| 195 |
do_query($form, $dbh, $query, $project_id); |
|
| 159 |
$query = |
|
| 160 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, |
|
| 161 |
source, memo, project_id, taxkey) |
|
| 162 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), |
|
| 163 |
?, ?, ?, ?, ?, ?)|; |
|
| 164 |
@values = (conv_i($form->{id}), $accno, $amount, conv_date($form->{transdate}),
|
|
| 165 |
$form->{"source_$i"}, $form->{"memo_$i"}, $project_id, $taxkey);
|
|
| 166 |
do_query($form, $dbh, $query, @values); |
|
| 196 | 167 |
} |
| 197 | 168 |
|
| 198 | 169 |
if ($tax != 0) {
|
| 199 | 170 |
# add taxentry |
| 200 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, |
|
| 201 |
source, memo, project_id, taxkey) |
|
| 202 |
VALUES |
|
| 203 |
($form->{id}, (SELECT t.chart_id
|
|
| 204 |
FROM tax t |
|
| 205 |
WHERE t.id = $form->{"tax_id_$i"}),
|
|
| 206 |
$tax, '$form->{transdate}', |
|
|
| 207 |
. $dbh->quote($form->{"source_$i"}) . qq|, |
|
|
| 208 |
. $dbh->quote($form->{"memo_$i"}) . qq|, ?, $taxkey)|;
|
|
| 209 |
|
|
| 210 |
do_query($form, $dbh, $query, $project_id); |
|
| 171 |
$query = |
|
| 172 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, |
|
| 173 |
source, memo, project_id, taxkey) |
|
| 174 |
VALUES (?, (SELECT chart_id FROM tax WHERE id = ?), |
|
| 175 |
?, ?, ?, ?, ?, ?)|; |
|
| 176 |
@values = (conv_i($form->{id}), conv_i($form->{"tax_id_$i"}),
|
|
| 177 |
$tax, conv_date($form->{transdate}), $form->{"source_$i"},
|
|
| 178 |
$form->{"memo_$i"}, $project_id, $taxkey);
|
|
| 179 |
do_query($form, $dbh, $query, @values); |
|
| 211 | 180 |
} |
| 212 | 181 |
} |
| 213 | 182 |
|
| ... | ... | |
| 229 | 198 |
my ($query, $sth, $source, $null); |
| 230 | 199 |
|
| 231 | 200 |
my ($glwhere, $arwhere, $apwhere) = ("1 = 1", "1 = 1", "1 = 1");
|
| 201 |
my (@glvalues, @arvalues, @apvalues); |
|
| 232 | 202 |
|
| 233 | 203 |
if ($form->{reference}) {
|
| 234 |
$source = $form->like(lc $form->{reference});
|
|
| 235 |
$glwhere .= " AND lower(g.reference) LIKE '$source'"; |
|
| 236 |
$arwhere .= " AND lower(a.invnumber) LIKE '$source'"; |
|
| 237 |
$apwhere .= " AND lower(a.invnumber) LIKE '$source'"; |
|
| 204 |
$glwhere .= qq| AND g.reference ILIKE ?|; |
|
| 205 |
$arwhere .= qq| AND a.invnumber ILIKE ?|; |
|
| 206 |
$apwhere .= qq| AND a.invnumber ILIKE ?|; |
|
| 207 |
push(@glvalues, '%' . $form->{reference} . '%');
|
|
| 208 |
push(@arvalues, '%' . $form->{reference} . '%');
|
|
| 209 |
push(@apvalues, '%' . $form->{reference} . '%');
|
|
| 238 | 210 |
} |
| 211 |
|
|
| 239 | 212 |
if ($form->{department}) {
|
| 240 |
($null, $source) = split /--/, $form->{department};
|
|
| 241 |
$glwhere .= " AND g.department_id = $source"; |
|
| 242 |
$arwhere .= " AND a.department_id = $source"; |
|
| 243 |
$apwhere .= " AND a.department_id = $source"; |
|
| 213 |
my ($null, $department) = split /--/, $form->{department};
|
|
| 214 |
$glwhere .= qq| AND g.department_id = ?|; |
|
| 215 |
$arwhere .= qq| AND a.department_id = ?|; |
|
| 216 |
$apwhere .= qq| AND a.department_id = ?|; |
|
| 217 |
push(@glvalues, $department); |
|
| 218 |
push(@arvalues, $department); |
|
| 219 |
push(@apvalues, $department); |
|
| 244 | 220 |
} |
| 245 | 221 |
|
| 246 | 222 |
if ($form->{source}) {
|
| 247 |
$source = $form->like(lc $form->{source});
|
|
| 248 |
$glwhere .= " AND lower(ac.source) LIKE '$source'"; |
|
| 249 |
$arwhere .= " AND lower(ac.source) LIKE '$source'"; |
|
| 250 |
$apwhere .= " AND lower(ac.source) LIKE '$source'"; |
|
| 223 |
$glwhere .= " AND ac.source ILIKE ?"; |
|
| 224 |
$arwhere .= " AND ac.source ILIKE ?"; |
|
| 225 |
$apwhere .= " AND ac.source ILIKE ?"; |
|
| 226 |
push(@glvalues, '%' . $form->{source} . '%');
|
|
| 227 |
push(@arvalues, '%' . $form->{source} . '%');
|
|
| 228 |
push(@apvalues, '%' . $form->{source} . '%');
|
|
| 251 | 229 |
} |
| 230 |
|
|
| 252 | 231 |
if ($form->{datefrom}) {
|
| 253 |
$glwhere .= " AND ac.transdate >= '$form->{datefrom}'";
|
|
| 254 |
$arwhere .= " AND ac.transdate >= '$form->{datefrom}'";
|
|
| 255 |
$apwhere .= " AND ac.transdate >= '$form->{datefrom}'";
|
|
| 232 |
$glwhere .= " AND ac.transdate >= ?"; |
|
| 233 |
$arwhere .= " AND ac.transdate >= ?"; |
|
| 234 |
$apwhere .= " AND ac.transdate >= ?"; |
|
| 235 |
push(@glvalues, $form->{datefrom});
|
|
| 236 |
push(@arvalues, $form->{datefrom});
|
|
| 237 |
push(@apvalues, $form->{datefrom});
|
|
| 256 | 238 |
} |
| 239 |
|
|
| 257 | 240 |
if ($form->{dateto}) {
|
| 258 |
$glwhere .= " AND ac.transdate <= '$form->{dateto}'";
|
|
| 259 |
$arwhere .= " AND ac.transdate <= '$form->{dateto}'";
|
|
| 260 |
$apwhere .= " AND ac.transdate <= '$form->{dateto}'";
|
|
| 241 |
$glwhere .= " AND ac.transdate <= ?"; |
|
| 242 |
$arwhere .= " AND ac.transdate <= ?"; |
|
| 243 |
$apwhere .= " AND ac.transdate <= ?"; |
|
| 244 |
push(@glvalues, $form->{dateto});
|
|
| 245 |
push(@arvalues, $form->{dateto});
|
|
| 246 |
push(@apvalues, $form->{dateto});
|
|
| 261 | 247 |
} |
| 248 |
|
|
| 262 | 249 |
if ($form->{description}) {
|
| 263 |
my $description = $form->like(lc $form->{description});
|
|
| 264 |
$glwhere .= " AND lower(g.description) LIKE '$description'"; |
|
| 265 |
$arwhere .= " AND lower(ct.name) LIKE '$description'"; |
|
| 266 |
$apwhere .= " AND lower(ct.name) LIKE '$description'"; |
|
| 250 |
$glwhere .= " AND g.description ILIKE ?"; |
|
| 251 |
$arwhere .= " AND ct.name ILIKE ?"; |
|
| 252 |
$apwhere .= " AND ct.name ILIKE ?"; |
|
| 253 |
push(@glvalues, '%' . $form->{description} . '%');
|
|
| 254 |
push(@arvalues, '%' . $form->{description} . '%');
|
|
| 255 |
push(@apvalues, '%' . $form->{description} . '%');
|
|
| 267 | 256 |
} |
| 257 |
|
|
| 268 | 258 |
if ($form->{notes}) {
|
| 269 |
my $notes = $form->like(lc $form->{notes});
|
|
| 270 |
$glwhere .= " AND lower(g.notes) LIKE '$notes'"; |
|
| 271 |
$arwhere .= " AND lower(a.notes) LIKE '$notes'"; |
|
| 272 |
$apwhere .= " AND lower(a.notes) LIKE '$notes'"; |
|
| 259 |
$glwhere .= " AND g.notes ILIKE ?"; |
|
| 260 |
$arwhere .= " AND a.notes ILIKE ?"; |
|
| 261 |
$apwhere .= " AND a.notes ILIKE ?"; |
|
| 262 |
push(@glvalues, '%' . $form->{notes} . '%');
|
|
| 263 |
push(@arvalues, '%' . $form->{notes} . '%');
|
|
| 264 |
push(@apvalues, '%' . $form->{notes} . '%');
|
|
| 273 | 265 |
} |
| 266 |
|
|
| 274 | 267 |
if ($form->{accno}) {
|
| 275 | 268 |
$glwhere .= " AND c.accno = '$form->{accno}'";
|
| 276 | 269 |
$arwhere .= " AND c.accno = '$form->{accno}'";
|
| 277 | 270 |
$apwhere .= " AND c.accno = '$form->{accno}'";
|
| 278 | 271 |
} |
| 272 |
|
|
| 279 | 273 |
if ($form->{category} ne 'X') {
|
| 280 | 274 |
$glwhere .= |
| 281 |
" AND gl.id in (SELECT trans_id FROM acc_trans ac2 WHERE ac2.chart_id IN (SELECT id FROM chart c2 WHERE c2.category = '$form->{category}'))";
|
|
| 275 |
qq| AND gl.id in (SELECT trans_id FROM acc_trans ac2 WHERE ac2.chart_id IN |
|
| 276 |
(SELECT id FROM chart c2 WHERE c2.category = ?))|; |
|
| 282 | 277 |
$arwhere .= |
| 283 |
" AND ar.id in (SELECT trans_id FROM acc_trans ac2 WHERE ac2.chart_id IN (SELECT id FROM chart c2 WHERE c2.category = '$form->{category}'))";
|
|
| 278 |
qq| AND ar.id in (SELECT trans_id FROM acc_trans ac2 WHERE ac2.chart_id IN |
|
| 279 |
(SELECT id FROM chart c2 WHERE c2.category = ?))|; |
|
| 284 | 280 |
$apwhere .= |
| 285 |
" AND ap.id in (SELECT trans_id FROM acc_trans ac2 WHERE ac2.chart_id IN (SELECT id FROM chart c2 WHERE c2.category = '$form->{category}'))";
|
|
| 281 |
qq| AND ap.id in (SELECT trans_id FROM acc_trans ac2 WHERE ac2.chart_id IN |
|
| 282 |
(SELECT id FROM chart c2 WHERE c2.category = ?))"|; |
|
| 283 |
push(@glvalues, $form->{category});
|
|
| 284 |
push(@arvalues, $form->{category});
|
|
| 285 |
push(@apvalues, $form->{category});
|
|
| 286 | 286 |
} |
| 287 |
|
|
| 287 | 288 |
if ($form->{project_id}) {
|
| 288 |
$glwhere .= " AND g.id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE project_id = " . conv_i($form->{project_id}, 'NULL') . ")";
|
|
| 289 |
$glwhere .= qq| AND g.id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE project_id = ?)|;
|
|
| 289 | 290 |
$arwhere .= |
| 290 |
" AND ((a.globalproject_id = " . conv_i($form->{project_id}, 'NULL') . ") OR " .
|
|
| 291 |
" (a.id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE project_id = " . conv_i($form->{project_id}, 'NULL') . ")))";
|
|
| 291 |
qq| AND ((a.globalproject_id = ?) OR
|
|
| 292 |
(a.id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE project_id = ?)))|;
|
|
| 292 | 293 |
$apwhere .= |
| 293 |
" AND ((a.globalproject_id = " . conv_i($form->{project_id}, 'NULL') . ") OR " .
|
|
| 294 |
" (a.id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE project_id = " . conv_i($form->{project_id}, 'NULL') . ")))";
|
|
| 294 |
qq| AND ((a.globalproject_id = ?) OR |
|
| 295 |
(a.id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE project_id = ?)))|; |
|
| 296 |
my $project_id = conv_i($form->{project_id});
|
|
| 297 |
push(@glvalues, $project_id); |
|
| 298 |
push(@arvalues, $project_id, $project_id); |
|
| 299 |
push(@apvalues, $project_id, $project_id); |
|
| 295 | 300 |
} |
| 296 | 301 |
|
| 297 | 302 |
my ($project_columns, %project_join); |
| 298 | 303 |
if ($form->{"l_projectnumbers"}) {
|
| 299 |
$project_columns = ", ac.project_id, pr.projectnumber";
|
|
| 300 |
$project_join = "LEFT JOIN project pr ON (ac.project_id = pr.id)";
|
|
| 304 |
$project_columns = qq|, ac.project_id, pr.projectnumber|;
|
|
| 305 |
$project_join = qq|LEFT JOIN project pr ON (ac.project_id = pr.id)|;
|
|
| 301 | 306 |
} |
| 302 | 307 |
|
| 303 | 308 |
if ($form->{accno}) {
|
| 304 |
|
|
| 305 | 309 |
# get category for account |
| 306 |
$query = qq|SELECT c.category |
|
| 307 |
FROM chart c |
|
| 308 |
WHERE c.accno = '$form->{accno}'|;
|
|
| 309 |
$sth = $dbh->prepare($query); |
|
| 310 |
|
|
| 311 |
$sth->execute || $form->dberror($query); |
|
| 312 |
($form->{ml}) = $sth->fetchrow_array;
|
|
| 313 |
$sth->finish; |
|
| 310 |
$query = qq|SELECT category FROM chart WHERE accno = ?|; |
|
| 311 |
($form->{ml}) = selectrow_query($form, $dbh, $query, $form->{accno});
|
|
| 314 | 312 |
|
| 315 | 313 |
if ($form->{datefrom}) {
|
| 316 |
$query = qq|SELECT SUM(ac.amount) |
|
| 317 |
FROM acc_trans ac, chart c |
|
| 318 |
WHERE ac.chart_id = c.id |
|
| 319 |
AND c.accno = '$form->{accno}'
|
|
| 320 |
AND ac.transdate < date '$form->{datefrom}'
|
|
| 321 |
|; |
|
| 322 |
$sth = $dbh->prepare($query); |
|
| 323 |
$sth->execute || $form->dberror($query); |
|
| 324 |
|
|
| 325 |
($form->{balance}) = $sth->fetchrow_array;
|
|
| 326 |
$sth->finish; |
|
| 314 |
$query = |
|
| 315 |
qq|SELECT SUM(ac.amount) |
|
| 316 |
FROM acc_trans ac |
|
| 317 |
LEFT JOIN chart c ON (ac.chart_id = c.id) |
|
| 318 |
WHERE (c.accno = ?) AND (ac.transdate < ?)|; |
|
| 319 |
($form->{balance}) = selectrow_query($form, $dbh, $query, $form->{accno}, conv_date($form->{datefrom}));
|
|
| 327 | 320 |
} |
| 328 | 321 |
} |
| 329 | 322 |
|
| 330 | 323 |
my $false = ($myconfig->{dbdriver} eq 'Pg') ? FALSE: q|'0'|;
|
| 331 | 324 |
|
| 332 |
my $sortorder = join ', ', |
|
| 333 |
$form->sort_columns(qw(transdate reference source description accno)); |
|
| 334 |
my %ordinal = (transdate => 6, |
|
| 335 |
reference => 4, |
|
| 336 |
source => 7, |
|
| 337 |
description => 5); |
|
| 338 |
map { $sortorder =~ s/$_/$ordinal{$_}/ } keys %ordinal;
|
|
| 325 |
my $sortorder; |
|
| 339 | 326 |
|
| 340 | 327 |
if ($form->{sort}) {
|
| 328 |
$form->{sort} =~ s/[^a-zA-Z_]//g;
|
|
| 341 | 329 |
$sortorder = $form->{sort} . ",";
|
| 342 |
} else {
|
|
| 343 |
$sortorder = ""; |
|
| 344 | 330 |
} |
| 345 | 331 |
|
| 346 | 332 |
my $query = |
| 347 |
qq|SELECT ac.oid AS acoid, g.id, 'gl' AS type, $false AS invoice, g.reference, ac.taxkey, c.link, |
|
| 348 |
g.description, ac.transdate, ac.source, ac.trans_id, |
|
| 349 |
ac.amount, c.accno, g.notes, t.chart_id, ac.oid |
|
| 350 |
$project_columns |
|
| 351 |
FROM gl g, acc_trans ac $project_join, chart c LEFT JOIN tax t ON |
|
| 352 |
(t.chart_id=c.id) |
|
| 353 |
WHERE $glwhere |
|
| 354 |
AND ac.chart_id = c.id |
|
| 355 |
AND g.id = ac.trans_id |
|
| 356 |
UNION |
|
| 357 |
SELECT ac.oid AS acoid, a.id, 'ar' AS type, a.invoice, a.invnumber, ac.taxkey, c.link, |
|
| 358 |
ct.name, ac.transdate, ac.source, ac.trans_id, |
|
| 359 |
ac.amount, c.accno, a.notes, t.chart_id, ac.oid |
|
| 360 |
$project_columns |
|
| 361 |
FROM ar a, acc_trans ac $project_join, customer ct, chart c LEFT JOIN tax t ON |
|
| 362 |
(t.chart_id=c.id) |
|
| 363 |
WHERE $arwhere |
|
| 364 |
AND ac.chart_id = c.id |
|
| 365 |
AND a.customer_id = ct.id |
|
| 366 |
AND a.id = ac.trans_id |
|
| 367 |
UNION |
|
| 368 |
SELECT ac.oid AS acoid, a.id, 'ap' AS type, a.invoice, a.invnumber, ac.taxkey, c.link, |
|
| 369 |
ct.name, ac.transdate, ac.source, ac.trans_id, |
|
| 370 |
ac.amount, c.accno, a.notes, t.chart_id, ac.oid |
|
| 371 |
$project_columns |
|
| 372 |
FROM ap a, acc_trans ac $project_join, vendor ct, chart c LEFT JOIN tax t ON |
|
| 373 |
(t.chart_id=c.id) |
|
| 374 |
WHERE $apwhere |
|
| 375 |
AND ac.chart_id = c.id |
|
| 376 |
AND a.vendor_id = ct.id |
|
| 377 |
AND a.id = ac.trans_id |
|
| 378 |
ORDER BY $sortorder transdate, trans_id, acoid, taxkey DESC|; |
|
| 333 |
qq|SELECT |
|
| 334 |
ac.oid AS acoid, g.id, 'gl' AS type, $false AS invoice, g.reference, ac.taxkey, c.link, |
|
| 335 |
g.description, ac.transdate, ac.source, ac.trans_id, |
|
| 336 |
ac.amount, c.accno, g.notes, t.chart_id, ac.oid |
|
| 337 |
$project_columns |
|
| 338 |
FROM gl g, acc_trans ac $project_join, chart c |
|
| 339 |
LEFT JOIN tax t ON (t.chart_id = c.id) |
|
| 340 |
WHERE $glwhere |
|
| 341 |
AND (ac.chart_id = c.id) |
|
| 342 |
AND (g.id = ac.trans_id) |
|
| 343 |
|
|
| 344 |
UNION |
|
| 345 |
|
|
| 346 |
SELECT ac.oid AS acoid, a.id, 'ar' AS type, a.invoice, a.invnumber, ac.taxkey, c.link, |
|
| 347 |
ct.name, ac.transdate, ac.source, ac.trans_id, |
|
| 348 |
ac.amount, c.accno, a.notes, t.chart_id, ac.oid |
|
| 349 |
$project_columns |
|
| 350 |
FROM ar a, acc_trans ac $project_join, customer ct, chart c |
|
| 351 |
LEFT JOIN tax t ON (t.chart_id=c.id) |
|
| 352 |
WHERE $arwhere |
|
| 353 |
AND (ac.chart_id = c.id) |
|
| 354 |
AND (a.customer_id = ct.id) |
|
| 355 |
AND (a.id = ac.trans_id) |
|
| 356 |
|
|
| 357 |
UNION |
|
| 358 |
|
|
| 359 |
SELECT ac.oid AS acoid, a.id, 'ap' AS type, a.invoice, a.invnumber, ac.taxkey, c.link, |
|
| 360 |
ct.name, ac.transdate, ac.source, ac.trans_id, |
|
| 361 |
ac.amount, c.accno, a.notes, t.chart_id, ac.oid |
|
| 362 |
$project_columns |
|
| 363 |
FROM ap a, acc_trans ac $project_join, vendor ct, chart c |
|
| 364 |
LEFT JOIN tax t ON (t.chart_id=c.id) |
|
| 365 |
WHERE $apwhere |
|
| 366 |
AND (ac.chart_id = c.id) |
|
| 367 |
AND (a.vendor_id = ct.id) |
|
| 368 |
AND (a.id = ac.trans_id) |
|
| 369 |
|
|
| 370 |
ORDER BY $sortorder transdate, trans_id, acoid, taxkey DESC|; |
|
| 371 |
|
|
| 372 |
my @values = (@glvalues, @arvalues, @apvalues); |
|
| 379 | 373 |
|
| 380 | 374 |
# Show all $query in Debuglevel LXDebug::QUERY |
| 381 | 375 |
$callingdetails = (caller (0))[3]; |
| 382 |
$main::lxdebug->message(LXDebug::QUERY, "$callingdetails \$query=\n $query"); |
|
| 383 |
|
|
| 384 |
my $sth = $dbh->prepare($query); |
|
| 385 |
$sth->execute || $form->dberror($query); |
|
| 376 |
dump_query(LXDebug::QUERY, "$callingdetails", $query, @values); |
|
| 377 |
|
|
| 378 |
$sth = prepare_execute_query($form, $dbh, $query, @values); |
|
| 386 | 379 |
my $trans_id = ""; |
| 387 | 380 |
my $trans_id2 = ""; |
| 388 | 381 |
|
| 382 |
$form->{GL} = [];
|
|
| 389 | 383 |
while (my $ref0 = $sth->fetchrow_hashref(NAME_lc)) {
|
| 390 |
|
|
| 384 |
|
|
| 391 | 385 |
$trans_id = $ref0->{id};
|
| 392 |
|
|
| 386 |
|
|
| 393 | 387 |
if ($trans_id != $trans_id2) { # first line of a booking
|
| 394 |
|
|
| 388 |
|
|
| 395 | 389 |
if ($trans_id2) {
|
| 396 |
push @{ $form->{GL} }, $ref;
|
|
| 390 |
push(@{ $form->{GL} }, $ref);
|
|
| 397 | 391 |
$balance = 0; |
| 398 | 392 |
} |
| 399 |
|
|
| 393 |
|
|
| 400 | 394 |
$ref = $ref0; |
| 401 | 395 |
$trans_id2 = $ref->{id};
|
| 402 | 396 |
|
| ... | ... | |
| 427 | 421 |
$ref->{"projectnumbers"}->{$ref->{"projectnumber"}} = 1 if ($ref->{"projectnumber"});
|
| 428 | 422 |
|
| 429 | 423 |
$balance = $ref->{amount};
|
| 430 |
|
|
| 431 |
# Linenumbers of General Ledger
|
|
| 424 |
|
|
| 425 |
# Linenumbers of General Ledger |
|
| 432 | 426 |
$k = 0; # Debit # AP # Soll |
| 433 | 427 |
$l = 0; # Credit # AR # Haben |
| 434 | 428 |
$i = 0; # Debit Tax # AP_tax # VSt |
| 435 | 429 |
$j = 0; # Credit Tax # AR_tax # USt |
| 436 |
|
|
| 430 |
|
|
| 437 | 431 |
|
| 438 | 432 |
if ($ref->{chart_id} > 0) { # all tax accounts first line, no line increasing
|
| 439 | 433 |
if ($ref->{amount} < 0) {
|
| 440 | 434 |
if ($ref->{link} =~ /AR_tax/) {
|
| 441 | 435 |
$ref->{credit_tax}{$j} = $ref->{amount};
|
| 442 |
$ref->{credit_tax_accno}{$j} = $ref->{accno};
|
|
| 436 |
$ref->{credit_tax_accno}{$j} = $ref->{accno};
|
|
| 443 | 437 |
} |
| 444 | 438 |
if ($ref->{link} =~ /AP_tax/) {
|
| 445 | 439 |
$ref->{debit_tax}{$i} = $ref->{amount} * -1;
|
| 446 |
$ref->{debit_tax_accno}{$i} = $ref->{accno};
|
|
| 440 |
$ref->{debit_tax_accno}{$i} = $ref->{accno};
|
|
| 447 | 441 |
} |
| 448 | 442 |
} else {
|
| 449 | 443 |
if ($ref->{link} =~ /AR_tax/) {
|
| 450 | 444 |
$ref->{credit_tax}{$j} = $ref->{amount};
|
| 451 |
$ref->{credit_tax_accno}{$j} = $ref->{accno};
|
|
| 445 |
$ref->{credit_tax_accno}{$j} = $ref->{accno};
|
|
| 452 | 446 |
} |
| 453 | 447 |
if ($ref->{link} =~ /AP_tax/) {
|
| 454 | 448 |
$ref->{debit_tax}{$i} = $ref->{amount} * -1;
|
| 455 |
$ref->{debit_tax_accno}{$i} = $ref->{accno};
|
|
| 449 |
$ref->{debit_tax_accno}{$i} = $ref->{accno};
|
|
| 456 | 450 |
} |
| 457 | 451 |
} |
| 458 | 452 |
} else { #all other accounts first line
|
| ... | ... | |
| 475 | 469 |
} else { # following lines of a booking, line increasing
|
| 476 | 470 |
|
| 477 | 471 |
$ref2 = $ref0; |
| 478 |
$trans_old =$trans_id2;
|
|
| 472 |
$trans_old = $trans_id2;
|
|
| 479 | 473 |
$trans_id2 = $ref2->{id};
|
| 480 |
|
|
| 474 |
|
|
| 481 | 475 |
$balance = |
| 482 | 476 |
(int($balance * 100000) + int(100000 * $ref2->{amount})) / 100000;
|
| 483 | 477 |
|
| ... | ... | |
| 490 | 484 |
$j++; |
| 491 | 485 |
} |
| 492 | 486 |
$ref->{credit_tax}{$j} = $ref2->{amount};
|
| 493 |
$ref->{credit_tax_accno}{$j} = $ref2->{accno};
|
|
| 487 |
$ref->{credit_tax_accno}{$j} = $ref2->{accno};
|
|
| 494 | 488 |
} |
| 495 | 489 |
if ($ref2->{link} =~ /AP_tax/) {
|
| 496 | 490 |
if ($ref->{debit_tax_accno}{$i} ne "") {
|
| 497 | 491 |
$i++; |
| 498 | 492 |
} |
| 499 | 493 |
$ref->{debit_tax}{$i} = $ref2->{amount} * -1;
|
| 500 |
$ref->{debit_tax_accno}{$i} = $ref2->{accno};
|
|
| 494 |
$ref->{debit_tax_accno}{$i} = $ref2->{accno};
|
|
| 501 | 495 |
} |
| 502 | 496 |
} else {
|
| 503 | 497 |
if ($ref2->{link} =~ /AR_tax/) {
|
| ... | ... | |
| 505 | 499 |
$j++; |
| 506 | 500 |
} |
| 507 | 501 |
$ref->{credit_tax}{$j} = $ref2->{amount};
|
| 508 |
$ref->{credit_tax_accno}{$j} = $ref2->{accno};
|
|
| 502 |
$ref->{credit_tax_accno}{$j} = $ref2->{accno};
|
|
| 509 | 503 |
} |
| 510 | 504 |
if ($ref2->{link} =~ /AP_tax/) {
|
| 511 | 505 |
if ($ref->{debit_tax_accno}{$i} ne "") {
|
| 512 | 506 |
$i++; |
| 513 | 507 |
} |
| 514 | 508 |
$ref->{debit_tax}{$i} = $ref2->{amount} * -1;
|
| 515 |
$ref->{debit_tax_accno}{$i} = $ref2->{accno};
|
|
| 509 |
$ref->{debit_tax_accno}{$i} = $ref2->{accno};
|
|
| 516 | 510 |
} |
| 517 | 511 |
} |
| 518 | 512 |
} else { # all other accounts, following lines
|
| ... | ... | |
| 540 | 534 |
$sth->finish; |
| 541 | 535 |
|
| 542 | 536 |
if ($form->{accno}) {
|
| 543 |
$query = |
|
| 544 |
qq|SELECT c.description FROM chart c WHERE c.accno = '$form->{accno}'|;
|
|
| 545 |
$sth = $dbh->prepare($query); |
|
| 546 |
$sth->execute || $form->dberror($query); |
|
| 547 |
|
|
| 548 |
($form->{account_description}) = $sth->fetchrow_array;
|
|
| 549 |
$sth->finish; |
|
| 537 |
$query = qq|SELECT c.description FROM chart c WHERE c.accno = ?|; |
|
| 538 |
($form->{account_description}) = selectrow_query($form, $dbh, $query, $form->{accno});
|
|
| 550 | 539 |
} |
| 551 | 540 |
|
| 552 |
$main::lxdebug->leave_sub(); |
|
| 553 |
|
|
| 554 | 541 |
$dbh->disconnect; |
| 555 | 542 |
|
| 543 |
$main::lxdebug->leave_sub(); |
|
| 556 | 544 |
} |
| 557 | 545 |
|
| 558 | 546 |
sub transaction {
|
| 559 | 547 |
my ($self, $myconfig, $form) = @_; |
| 560 | 548 |
$main::lxdebug->enter_sub(); |
| 561 | 549 |
|
| 562 |
my ($query, $sth, $ref); |
|
| 550 |
my ($query, $sth, $ref, @values);
|
|
| 563 | 551 |
|
| 564 | 552 |
# connect to database |
| 565 | 553 |
my $dbh = $form->dbconnect($myconfig); |
| 566 | 554 |
|
| 555 |
$query = qq|SELECT closedto, revtrans FROM defaults|; |
|
| 556 |
($form->{closedto}, $form->{revtrans}) = selectrow_query($form, $dbh, $query);
|
|
| 557 |
|
|
| 567 | 558 |
if ($form->{id}) {
|
| 568 |
$query = "SELECT closedto, revtrans |
|
| 569 |
FROM defaults"; |
|
| 570 |
$sth = $dbh->prepare($query); |
|
| 571 |
$sth->execute || $form->dberror($query); |
|
| 572 |
|
|
| 573 |
($form->{closedto}, $form->{revtrans}) = $sth->fetchrow_array;
|
|
| 574 |
$sth->finish; |
|
| 575 |
|
|
| 576 |
$query = "SELECT g.reference, g.description, g.notes, g.transdate, |
|
| 577 |
d.description AS department, e.name as employee, g.taxincluded, g.gldate |
|
| 578 |
FROM gl g |
|
| 579 |
LEFT JOIN department d ON (d.id = g.department_id) |
|
| 580 |
LEFT JOIN employee e ON (e.id = g.employee_id) |
|
| 581 |
WHERE g.id = $form->{id}";
|
|
| 582 |
$sth = $dbh->prepare($query); |
|
| 583 |
$sth->execute || $form->dberror($query); |
|
| 584 |
$ref = $sth->fetchrow_hashref(NAME_lc); |
|
| 559 |
$query = |
|
| 560 |
qq|SELECT g.reference, g.description, g.notes, g.transdate, |
|
| 561 |
d.description AS department, e.name AS employee, g.taxincluded, g.gldate |
|
| 562 |
FROM gl g |
|
| 563 |
LEFT JOIN department d ON (d.id = g.department_id) |
|
| 564 |
LEFT JOIN employee e ON (e.id = g.employee_id) |
|
| 565 |
WHERE g.id = ?|; |
|
| 566 |
$ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
|
|
| 585 | 567 |
map { $form->{$_} = $ref->{$_} } keys %$ref;
|
| 586 |
$sth->finish; |
|
| 587 | 568 |
|
| 588 | 569 |
# retrieve individual rows |
| 589 |
$query = qq|SELECT c.accno, t.taxkey AS accnotaxkey, a.amount, a.memo, |
|
| 590 |
a.transdate, a.cleared, a.project_id, p.projectnumber, |
|
| 591 |
a.taxkey, t.rate AS taxrate, t.id, (SELECT c1.accno FROM chart c1, tax t1 WHERE t1.id=t.id AND c1.id=t.chart_id) AS taxaccno, (SELECT tk.tax_id FROM taxkeys tk WHERE tk.chart_id =a.chart_id AND tk.startdate<=a.transdate ORDER BY tk.startdate desc LIMIT 1) AS tax_id |
|
| 592 |
FROM acc_trans a |
|
| 593 |
JOIN chart c ON (c.id = a.chart_id) |
|
| 594 |
LEFT JOIN project p ON (p.id = a.project_id) |
|
| 595 |
LEFT JOIN tax t ON (t.id=(SELECT tk.tax_id from taxkeys tk WHERE (tk.taxkey_id=a.taxkey) AND ((CASE WHEN a.chart_id IN (SELECT chart_id FROM taxkeys WHERE taxkey_id=a.taxkey) THEN tk.chart_id=a.chart_id ELSE 1=1 END) OR (c.link LIKE '%tax%')) AND startdate <=a.transdate ORDER BY startdate DESC LIMIT 1)) |
|
| 596 |
WHERE a.trans_id = $form->{id}
|
|
| 597 |
AND a.fx_transaction = '0' |
|
| 598 |
ORDER BY a.oid,a.transdate|; |
|
| 599 |
|
|
| 600 |
$sth = $dbh->prepare($query); |
|
| 601 |
$sth->execute || $form->dberror($query); |
|
| 602 |
|
|
| 603 |
$form->{GL} = [];
|
|
| 604 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
|
| 605 |
push @{ $form->{GL} }, $ref;
|
|
| 606 |
} |
|
| 607 |
|
|
| 608 |
# get tax description |
|
| 609 |
$query = qq| SELECT * FROM tax t order by t.taxkey|; |
|
| 610 |
$sth = $dbh->prepare($query); |
|
| 611 |
$sth->execute || $form->dberror($query); |
|
| 612 |
$form->{TAX} = [];
|
|
| 613 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
|
| 614 |
push @{ $form->{TAX} }, $ref;
|
|
| 615 |
} |
|
| 570 |
$query = |
|
| 571 |
qq|SELECT c.accno, t.taxkey AS accnotaxkey, a.amount, a.memo, |
|
| 572 |
a.transdate, a.cleared, a.project_id, p.projectnumber, |
|
| 573 |
a.taxkey, t.rate AS taxrate, t.id, |
|
| 574 |
(SELECT c1.accno |
|
| 575 |
FROM chart c1, tax t1 |
|
| 576 |
WHERE (t1.id = t.id) AND (c1.id = t.chart_id)) AS taxaccno, |
|
| 577 |
(SELECT tk.tax_id |
|
| 578 |
FROM taxkeys tk |
|
| 579 |
WHERE (tk.chart_id = a.chart_id) AND (tk.startdate <= a.transdate) |
|
| 580 |
ORDER BY tk.startdate desc LIMIT 1) AS tax_id |
|
| 581 |
FROM acc_trans a |
|
| 582 |
JOIN chart c ON (c.id = a.chart_id) |
|
| 583 |
LEFT JOIN project p ON (p.id = a.project_id) |
|
| 584 |
LEFT JOIN tax t ON |
|
| 585 |
(t.id = |
|
| 586 |
(SELECT tk.tax_id |
|
| 587 |
FROM taxkeys tk |
|
| 588 |
WHERE (tk.taxkey_id = a.taxkey) AND |
|
| 589 |
((CASE WHEN a.chart_id IN |
|
| 590 |
(SELECT chart_id FROM taxkeys WHERE taxkey_id = a.taxkey) |
|
| 591 |
THEN tk.chart_id = a.chart_id |
|
| 592 |
ELSE 1 = 1 |
|
| 593 |
END) |
|
| 594 |
OR (c.link LIKE '%tax%')) |
|
| 595 |
AND (startdate <= a.transdate) |
|
| 596 |
ORDER BY startdate DESC LIMIT 1)) |
|
| 597 |
WHERE (a.trans_id = ?) |
|
| 598 |
AND (a.fx_transaction = '0') |
|
| 599 |
ORDER BY a.oid, a.transdate|; |
|
| 600 |
$form->{GL} = selectall_hashref_query($form, $dbh, $query, conv_i($form->{id}));
|
|
| 616 | 601 |
|
| 617 |
$sth->finish; |
|
| 618 | 602 |
} else {
|
| 619 |
$query = "SELECT closedto, revtrans FROM defaults"; |
|
| 620 |
($form->{closedto}, $form->{revtrans}) = $dbh->selectrow_array($query);
|
|
| 621 | 603 |
$query = |
| 622 |
"SELECT COALESCE(" .
|
|
| 623 |
" (SELECT transdate FROM gl WHERE id = " . |
|
| 624 |
" (SELECT MAX(id) FROM gl) LIMIT 1), " . |
|
| 625 |
" current_date)"; |
|
| 626 |
($form->{transdate}) = $dbh->selectrow_array($query);
|
|
| 627 |
|
|
| 628 |
# get tax description |
|
| 629 |
$query = qq| SELECT * FROM tax t order by t.taxkey|; |
|
| 630 |
$sth = $dbh->prepare($query); |
|
| 631 |
$sth->execute || $form->dberror($query); |
|
| 632 |
$form->{TAX} = ();
|
|
| 633 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
|
| 634 |
push @{ $form->{TAX} }, $ref;
|
|
| 635 |
} |
|
| 604 |
qq|SELECT COALESCE( |
|
| 605 |
(SELECT transdate |
|
| 606 |
FROM gl |
|
| 607 |
WHERE id = (SELECT MAX(id) FROM gl) |
|
| 608 |
LIMIT 1), |
|
| 609 |
current_date)|; |
|
| 610 |
($form->{transdate}) = selectrow_query($form, $dbh, $query);
|
|
| 636 | 611 |
} |
| 637 | 612 |
|
| 638 |
$sth->finish; |
|
| 639 |
my $transdate = "current_date"; |
|
| 640 |
if ($form->{transdate}) {
|
|
| 641 |
$transdate = qq|'$form->{transdate}'|;
|
|
| 642 |
} |
|
| 643 |
# get chart of accounts |
|
| 644 |
$query = qq|SELECT c.accno, c.description, c.link, tk.taxkey_id, tk.tax_id |
|
| 645 |
FROM chart c |
|
| 646 |
LEFT JOIN taxkeys tk ON (tk.id = (SELECT id from taxkeys where taxkeys.chart_id =c.id AND startdate<=$transdate ORDER BY startdate desc LIMIT 1)) |
|
| 647 |
ORDER BY c.accno|; |
|
| 648 |
$sth = $dbh->prepare($query); |
|
| 649 |
$sth->execute || $form->dberror($query); |
|
| 650 |
$form->{chart} = ();
|
|
| 651 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
|
| 652 |
push @{ $form->{chart} }, $ref;
|
|
| 653 |
} |
|
| 654 |
$sth->finish; |
|
| 613 |
# get tax description |
|
| 614 |
$query = qq|SELECT * FROM tax ORDER BY taxkey|; |
|
| 615 |
$form->{TAX} = selectall_hashref_query($form, $dbh, $query);
|
|
| 655 | 616 |
|
| 656 |
$sth->finish; |
|
| 657 |
$main::lxdebug->leave_sub(); |
|
| 617 |
# get chart of accounts |
|
| 618 |
$query = |
|
| 619 |
qq|SELECT c.accno, c.description, c.link, tk.taxkey_id, tk.tax_id |
|
| 620 |
FROM chart c |
|
| 621 |
LEFT JOIN taxkeys tk ON (tk.id = |
|
| 622 |
(SELECT id |
|
| 623 |
FROM taxkeys |
|
| 624 |
WHERE (taxkeys.chart_id = c.id) |
|
| 625 |
AND (startdate <= ?) |
|
| 626 |
ORDER BY startdate DESC |
|
| 627 |
LIMIT 1)) |
|
| 628 |
ORDER BY c.accno|; |
|
| 629 |
$form->{chart} = selectall_hashref_query($form, $dbh, $query, conv_date($form->{transdate}));
|
|
| 658 | 630 |
|
| 659 | 631 |
$dbh->disconnect; |
| 660 | 632 |
|
| 633 |
$main::lxdebug->leave_sub(); |
|
| 661 | 634 |
} |
| 662 | 635 |
|
| 663 | 636 |
1; |
| 664 |
|
|
Auch abrufbar als: Unified diff
Vermeidung von SQL injections durch die Verwendung von parametrisierten Queries.