Revision cb8d5a5d
Von Philip Reetz vor mehr als 16 Jahren hinzugefügt
| SL/CA.pm | ||
|---|---|---|
| 180 | 180 |
$project = qq| AND ac.project_id = ? |; |
| 181 | 181 |
@project_values = (conv_i($form->{project_id}));
|
| 182 | 182 |
} |
| 183 |
my $acc_cash_where = ""; |
|
| 184 |
my $ar_cash_where = ""; |
|
| 185 |
my $ap_cash_where = ""; |
|
| 186 |
|
|
| 187 |
|
|
| 188 |
if ($form->{method} eq "cash") {
|
|
| 189 |
$acc_cash_where = qq| AND (ac.trans_id IN (SELECT id FROM ar WHERE datepaid>='$form->{fromdate}' AND datepaid<='$form->{todate}' UNION SELECT id FROM ap WHERE datepaid>='$form->{fromdate}' AND datepaid<='$form->{todate}' UNION SELECT id FROM gl WHERE transdate>='$form->{fromdate}' AND transdate<='$form->{todate}')) |;
|
|
| 190 |
$ar_ap_cash_where = qq| AND (a.datepaid>='$form->{fromdate}' AND a.datepaid<='$form->{todate}') |;
|
|
| 191 |
} |
|
| 183 | 192 |
|
| 184 | 193 |
if ($form->{accno}) {
|
| 185 | 194 |
|
| ... | ... | |
| 197 | 206 |
WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.ob_transaction
|
| 198 | 207 |
$dpt_where |
| 199 | 208 |
$project |
| 200 |
AND c.accno = ?|; |
|
| 209 |
AND c.accno = ? $acc_cash_where|;
|
|
| 201 | 210 |
|
| 202 | 211 |
($form->{beginning_balance}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});
|
| 203 | 212 |
|
| 204 | 213 |
# get last transaction date |
| 205 | 214 |
my $todate = ($form->{todate}) ? " AND ac.transdate <= '$form->{todate}' " : "";
|
| 206 |
$query = qq|SELECT max(ac.transdate) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) $todate AND c.accno = ?|;
|
|
| 215 |
$query = qq|SELECT max(ac.transdate) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) $todate AND c.accno = ? $acc_cash_where|;
|
|
| 207 | 216 |
($form->{last_transaction}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});
|
| 208 | 217 |
|
| 209 | 218 |
# get old saldo |
| 210 |
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ?|;
|
|
| 219 |
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? $acc_cash_where|;
|
|
| 211 | 220 |
($form->{saldo_old}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno});
|
| 212 | 221 |
|
| 213 | 222 |
#get old balance |
| 214 |
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND ac.amount < 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)|;
|
|
| 223 |
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND ac.amount < 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|;
|
|
| 215 | 224 |
($form->{old_balance_debit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno});
|
| 216 | 225 |
|
| 217 |
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND ac.amount > 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)|;
|
|
| 226 |
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND ac.amount > 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|;
|
|
| 218 | 227 |
($form->{old_balance_credit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno});
|
| 219 | 228 |
|
| 220 | 229 |
# get current saldo |
| 221 | 230 |
my $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : "";
|
| 222 |
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) $todate AND c.accno = ?|;
|
|
| 231 |
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) $todate AND c.accno = ? $acc_cash_where|;
|
|
| 223 | 232 |
($form->{saldo_new}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});
|
| 224 | 233 |
|
| 225 | 234 |
#get current balance |
| 226 | 235 |
my $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : "";
|
| 227 |
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND ac.amount < 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)|;
|
|
| 236 |
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND ac.amount < 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|;
|
|
| 228 | 237 |
($form->{current_balance_debit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});
|
| 229 | 238 |
|
| 230 | 239 |
my $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : "";
|
| 231 |
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND ac.amount > 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)|;
|
|
| 240 |
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND ac.amount > 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|;
|
|
| 232 | 241 |
($form->{current_balance_credit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});
|
| 233 | 242 |
} |
| 234 | 243 |
} |
| ... | ... | |
| 271 | 280 |
qq| AND ac.chart_id = ? | . |
| 272 | 281 |
qq| AND ac.trans_id = a.id | . |
| 273 | 282 |
qq| AND a.customer_id = c.id | . |
| 274 |
qq| AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) | . |
|
| 283 |
qq| AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $ar_ap_cash_where| .
|
|
| 275 | 284 |
|
| 276 | 285 |
qq|UNION ALL | . |
| 277 | 286 |
|
| ... | ... | |
| 284 | 293 |
qq| AND ac.chart_id = ? | . |
| 285 | 294 |
qq| AND ac.trans_id = a.id | . |
| 286 | 295 |
qq| AND a.vendor_id = v.id |; |
| 287 |
qq| AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) | . |
|
| 296 |
qq| AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $ar_ap_cash_where| .
|
|
| 288 | 297 |
|
| 289 | 298 |
push(@values, |
| 290 | 299 |
@where_values, @department_values, @project_values, $id, |
| ... | ... | |
| 314 | 323 |
$todate_where . |
| 315 | 324 |
$dpt_where . |
| 316 | 325 |
$project . |
| 317 |
|
|
| 326 |
$ar_ap_cash_where . |
|
| 318 | 327 |
qq|UNION ALL | . |
| 319 | 328 |
|
| 320 | 329 |
qq|SELECT a.id, a.invnumber, v.name, a.transdate, | . |
| ... | ... | |
| 329 | 338 |
$fromdate_where . |
| 330 | 339 |
$todate_where . |
| 331 | 340 |
$dpt_where . |
| 332 |
$project;
|
|
| 333 |
|
|
| 341 |
$project .
|
|
| 342 |
$ar_ap_cash_where; |
|
| 334 | 343 |
push(@values, |
| 335 | 344 |
$id, @department_values, @project_values, |
| 336 | 345 |
$id, @department_values, @project_values); |
Auch abrufbar als: Unified diff
Kontenuebersicht und SuSa die Unterscheidung von EUR und Blianzierung beigebracht