Projekt

Allgemein

Profil

« Zurück | Weiter » 

Revision 703f76b6

Von Philip Reetz vor mehr als 15 Jahren hinzugefügt

  • ID 703f76b624a9c447bf77e40db2cd3d5f3cc19e51
  • Vorgänger 15985765
  • Nachfolger 51e25e57

Kontenuebersicht und Kontenblatt angepasst, so dass auch bei EUR korrekte Werte angezeigt werden

Unterschiede anzeigen:

SL/CA.pm
48 48 
  # connect to database
49 49 
  my $dbh = $form->dbconnect($myconfig);
50 50 

  		




  
  51
  
    
  if ($form->{method} eq "cash") {


  		




  
  52
  
    
    $acc_cash_where = qq| AND (a.trans_id IN (SELECT id FROM ar WHERE datepaid>=(select date_trunc('year', current_date)) UNION SELECT id FROM ap WHERE datepaid>=(select date_trunc('year', current_date)) UNION SELECT id FROM gl WHERE transdate>=(select date_trunc('year', current_date)))) |;


  		




  
  53
  
    
  } else {


  		




  
  54
  
    
    $acc_cash_where = " AND ((select date_trunc('year', a.transdate::date)) >= (select date_trunc('year', current_date)))";


  		




  
  55
  
    
  }


  		




  
  56
  
    

  		




  51
  57
  
    
  my $query =


  		




  52
  58
  
    
    qq|SELECT c.accno, SUM(a.amount) AS amount | .


  		




  53
  59
  
    
    qq|FROM chart c, acc_trans a | .


  		




  54
  60
  
    
    qq|WHERE c.id = a.chart_id | .


  		




  
  61
  
    
    qq|$acc_cash_where| .


  		




  55
  62
  
    
    qq|GROUP BY c.accno|;


  		




  56
  63
  
    
  my $sth = $dbh->prepare($query);


  		




  57
  64
  
    
  $sth->execute || $form->dberror($query);


  		




  ......




  186
  193
  
    

  		




  187
  194
  
    

  		




  188
  195
  
    
  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
  
  
    
  }


  		




  
  196
  
    
    $where = qq| (ac.trans_id IN (SELECT id FROM ar WHERE datepaid>= ? AND datepaid<= ? UNION SELECT id FROM ap WHERE datepaid>= ? AND datepaid<= ? UNION SELECT id FROM gl WHERE transdate>= ? AND transdate<= ?)) |;


  		




  
  197
  
    
    @where_values = ();


  		




  
  198
  
    
    push(@where_values, conv_date($form->{fromdate}));


  		




  
  199
  
    
    push(@where_values, conv_date($form->{todate}));


  		




  
  200
  
    
    push(@where_values, conv_date($form->{fromdate}));


  		




  
  201
  
    
    push(@where_values, conv_date($form->{todate}));


  		




  
  202
  
    
    push(@where_values, conv_date($form->{fromdate}));


  		




  
  203
  
    
    push(@where_values, conv_date($form->{todate}));


  		




  
  204
  
    
 }


  		




  192
  205
  
    

  		




  193
  206
  
    
  if ($form->{accno}) {


  		




  194
  207
  
    

  		




  ......




  204
  217
  
    
            JOIN chart c ON (ac.chart_id = c.id)


  		




  205
  218
  
    
            WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.ob_transaction


  		




  206
  219
  
    
              $project


  		




  207
  
  
    
            AND c.accno = ? $acc_cash_where|;


  		




  
  220
  
    
            AND c.accno = ?|;


  		




  208
  221
  
    

  		




  209
  222
  
    
      ($form->{beginning_balance}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});


  		




  210
  223
  
    

  		




  211
  224
  
    
      # get last transaction date


  		




  212
  225
  
    
      my $todate = ($form->{todate}) ? " AND ac.transdate <= '$form->{todate}' " : "";


  		




  213
  
  
    
      $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|;


  		




  214
  
  
    
      ($form->{last_transaction}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});


  		




  
  226
  
    
      $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 = ?  AND $where|;


  		




  
  227
  
    
      ($form->{last_transaction}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno}, @where_values);


  		




  215
  228
  
    

  		




  216
  229
  
    
      # get old saldo


  		




  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 (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|;


  		




  218
  
  
    
      ($form->{saldo_old}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno});


  		




  
  230
  
    
      $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 (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND $where|;


  		




  
  231
  
    
      ($form->{saldo_old}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno}, @where_values);


  		




  219
  232
  
    

  		




  220
  233
  
    
      #get old balance


  		




  221
  
  
    
      $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|;


  		




  222
  
  
    
      ($form->{old_balance_debit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno});


  		




  
  234
  
    
      $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)  AND $where|;


  		




  
  235
  
    
      ($form->{old_balance_debit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno}, @where_values);


  		




  223
  236
  
    

  		




  224
  
  
    
      $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|;


  		




  225
  
  
    
      ($form->{old_balance_credit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno});


  		




  
  237
  
    
      $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)  AND $where|;


  		




  
  238
  
    
      ($form->{old_balance_credit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno}, @where_values);


  		




  226
  239
  
    

  		




  227
  240
  
    
      # get current saldo


  		




  228
  241
  
    
      my $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : "";


  		




  229
  
  
    
      $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 (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $acc_cash_where|;


  		




  230
  
  
    
      ($form->{saldo_new}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});


  		




  
  242
  
    
      $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 (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) AND $where|;


  		




  
  243
  
    
      ($form->{saldo_new}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno}, @where_values);


  		




  231
  244
  
    

  		




  232
  245
  
    
      #get current balance


  		




  233
  246
  
    
      my $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : "";


  		




  234
  
  
    
      $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|;


  		




  235
  
  
    
      ($form->{current_balance_debit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});


  		




  
  247
  
    
      $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)  AND $where|;


  		




  
  248
  
    
      ($form->{current_balance_debit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno}, @where_values);


  		




  236
  249
  
    

  		




  237
  250
  
    
      my $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : "";


  		




  238
  
  
    
      $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|;


  		




  239
  
  
    
      ($form->{current_balance_credit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});


  		




  
  251
  
    
      $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)  AND $where|;


  		




  
  252
  
    
      ($form->{current_balance_credit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno}, @where_values);


  		




  240
  253
  
    
    }


  		




  241
  254
  
    
  }


  		




  242
  255
  
    

  		




  ......




  278
  291
  
    
      qq| AND ac.chart_id = ? | .


  		




  279
  292
  
    
      qq| AND ac.trans_id = a.id | .


  		




  280
  293
  
    
      qq| AND a.customer_id = c.id | .


  		




  281
  
  
    
      qq| AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $ar_ap_cash_where| .


  		




  
  294
  
    
      qq| AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)| .


  		




  282
  295
  
    

  		




  283
  296
  
    
      qq|UNION ALL | .


  		




  284
  297
  
    

  		




  ......




  291
  304
  
    
      qq| AND ac.chart_id = ? | .


  		




  292
  305
  
    
      qq| AND ac.trans_id = a.id | .


  		




  293
  306
  
    
      qq| AND a.vendor_id = v.id |;


  		




  294
  
  
    
      qq| AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $ar_ap_cash_where| .


  		




  295
  
  
    

  		




  
  307
  
    
      qq| AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)| .


  		




  296
  308
  
    
    push(@values,


  		




  297
  309
  
    
         @where_values, @department_values, @project_values, $id,


  		




  298
  310
  
    
         @where_values, @department_values, @project_values, $id,


  		












  

    
      bin/mozilla/ca.pl
    
  





  78
  78
  
    

  		




  79
  79
  
    
  $form->{title} = $locale->text('Chart of Accounts');


  		




  80
  80
  
    

  		




  
  81
  
    
  if ($eur) {


  		




  
  82
  
    
    $form->{method} = "cash";


  		




  
  83
  
    
  }


  		




  
  84
  
    

  		




  81
  85
  
    
  CA->all_accounts(\%myconfig, \%$form);


  		




  82
  86
  
    

  		




  83
  87
  
    
  my @columns     = qw(accno description debit credit);


  		












Auch abrufbar als:  Unified diff