projekt kivitendo

use SL::DBUtils;

sub get_coa {

  my ( $self, $form, $myconfig) = @_;

  my $query = q{ SELECT coa FROM defaults };

  my $dbh = $form->dbconnect($myconfig);

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

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

  $sth->finish;

  $dbh->disconnect;

  $form->{coa} = $coa;

  $form->{"COA_$coa"} = '1';

  $form->{COA_Germany} = '1' if ( $coa eq 'Germany-DATEV-SKR03EU' or $coa eq 'Germany-DATEV-SKR04EU');

sub report_variables {

  # Get all positions for taxreport out of the database

  # Needs Databaseupdate Pg-upgrade2/USTVA_abstraction.pl

  return unless defined wantarray;

  my ( $self,

       $arg_ref) = @_;

  my $myconfig   = $arg_ref->{myconfig};

  my $form       = $arg_ref->{form};

  my $type       = $arg_ref->{type}; # 'paied' || 'received' || ''

  my $dec_places = (defined $arg_ref->{dec_places}) ? $arg_ref->{dec_places}:undef;

  my $where_type = "AND tax.report_headings.type = '$type'" if ( $type );

  my $query = qq|

    SELECT $attribute

      ON (tax.report_variables.heading_id = tax.report_headings.id)

    $where_type

  |;

  my $dbh = $form->dbconnect($myconfig);

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

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

  my @positions;

  while ( my $row_ref = $sth->fetchrow_arrayref() ) {

    push @positions, @$row_ref;  # Copy the array contents

  }

  $sth->finish;

  $dbh->disconnect;

  return @positions;

}

sub create_steuernummer {

  my ($self, $elsterland, $elsterFFFF, $steuernummer) = @_;

  my $steuernummer_input = '';

  $elster_land  = $elsterland;

  $elster_FFFF  = $elsterFFFF;

  $steuernummer = '0000000000' if ($steuernummer eq '');

      $steuernummer_input .= qq|</select>\n|;

    }

  }

  $main::lxdebug->leave_sub();

  return $steuernummer_input;

}

  # Prototyp f?r diese Konstruktion

  my ($self, $land, $elsterFFFF, $elster_init) = @_;

  my $terminal = '';

  my $FFFF     = $elsterFFFF;

  my $ffff     = '';

      attribute   => 'position',

      dec_places  => '2',

  });

  push @category_cent, qw(83  Z43  Z45  Z53  Z62  Z65  Z67);

  my @category_euro = USTVA->report_variables({

      myconfig    => $myconfig,

      form        => $form,

      attribute   => 'position',

      dec_places  => '0',

  });

  push @category_euro, USTVA->report_variables({

      myconfig    => $myconfig,

      form        => $form,

  }

  my $coa_name = coa_get($dbh);

  $form->{coa} = $coa_name;

  # Controlvariable for templates

  $form->{"$coa_name"} = '1';

  # Nationspecific Modfications

  #

  ###########################################

  # Germany

  if ( $form->{coa} eq 'Germany-DATEV-SKR03EU' or $form->{coa} eq 'Germany-DATEV-SKR04EU'){

    # 16%/19% Umstellung

    # Umordnen der Kennziffern

    if ( $form->{year} < 2007) {

  }

  # weil kein eigenes Ausgabeformular

  # sotte aber aus der allgem?inen Steuerberechnung verschwinden

  #

  $form->{"97r"} = $form->{"971"};

  $form->{"93r"} = $form->{"931"};

                     + $form->{"98"};

  $form->{"Z45"} = $form->{"Z43"};

                     + $form->{"85"}  + $form->{"65"};

                     - $form->{"64"}  - $form->{"59"};

  $form->{"Z65"} = $form->{"Z62"}     - $form->{"69"};

  $form->{"83"}  = $form->{"Z65"}     - $form->{"39"};

  $dbh->disconnect;

  $main::lxdebug->leave_sub();

sub coa_get {

  my ($dbh) = @_;

  my $query= qq|SELECT coa FROM defaults|;

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

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

  ($ref) = $sth->fetchrow_array;

  return $ref;

};

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

      $subwhere .= " AND transdate >= '$fromdate'";

      $glwhere = " AND ac.transdate >= '$fromdate'";

    }

    $APwhere .= " AND AP.transdate >= '$fromdate'";

    $where .= " AND ac.transdate >= '$fromdate'";

  #

  ############################################

    $query = qq|

       SELECT

         -- USTVA IST-Versteuerung

         -- (Teilzahlungen werden prozentual auf verschiedene Steuern aufgeteilt)

            -- Bezahlt / Rechnungssumme

             SELECT SUM(acc.amount)

             FROM acc_trans acc

                                    AND c.link   like  '%AR_paid%')

             WHERE

              $ARwhere

              AND acc.trans_id = ac.trans_id

              )

           )

         ) AS amount,

         tk.pos_ustva

       LEFT JOIN ar      ON (ar.id = ac.trans_id)

       LEFT JOIN taxkeys tk ON (

         tk.id = (

             AND startdate <= COALESCE(ar.deliverydate,ar.transdate)

           ORDER BY startdate DESC LIMIT 1

         )

       )

       $acc_trans_where

       GROUP BY tk.pos_ustva

    |;

  } elsif ($form->{method} eq 'accrual') {

    #########################################

    # Method eq 'accrual' = Soll Versteuerung

    $query = qq|

       -- Alle Einnahmen AR und pos_ustva erfassen

       SELECT

         tk.pos_ustva

       JOIN ar ON (ar.id = ac.trans_id)

       JOIN taxkeys tk ON (

         tk.id = (

             AND startdate <= COALESCE(ar.deliverydate,ar.transdate)

           ORDER BY startdate DESC LIMIT 1

         )

       $where

       GROUP BY tk.pos_ustva

  |;

  } else {

    $form->error("Unknown tax method: $form->{method}")

  }

  #########################################

  # Ausgaben und Gl Buchungen sind gleich

  # f?r Ist- und Soll-Versteuerung

  #########################################

     UNION -- alle Ausgaben AP erfassen

       SELECT

         tk.pos_ustva

       FROM acc_trans ac

       JOIN ap ON (ap.id = ac.trans_id )

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

       LEFT JOIN taxkeys tk ON (

           tk.id = (

             WHERE 1=1

               AND startdate <= COALESCE(AP.transdate)

             ORDER BY startdate DESC LIMIT 1

           )

     UNION -- Einnahmen direkter gl Buchungen erfassen

       SELECT sum

         tk.pos_ustva

       FROM acc_trans ac

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

       JOIN gl a ON (a.id = ac.trans_id)

       LEFT JOIN taxkeys tk ON (

         tk.id = (

             AND startdate <= COALESCE(ac.transdate)

           ORDER BY startdate DESC LIMIT 1

         )

     UNION -- Ausgaben direkter gl Buchungen erfassen

       SELECT sum

         tk.pos_ustva

       FROM acc_trans ac

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

       JOIN gl a ON (a.id = ac.trans_id)

       LEFT JOIN taxkeys tk ON (

         tk.id = (

             AND startdate <= COALESCE(ac.transdate)

           ORDER BY startdate DESC LIMIT 1

         )

  # Show all $query in Debuglevel LXDebug::QUERY

  $callingdetails = (caller (0))[3];

  $main::lxdebug->message(LXDebug::QUERY, "$callingdetails \$query=\n $query");

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

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

  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {

    # create file if file does not exist

    open my $FANEW, ">", $filename  or $form->error("CREATE: $filename : $!");

    close $FANEW                    or $form->error("CLOSE: $filename : $!");

    #try again open file

    open my $FACONF, "<", $filename or $form->error("OPEN: $filename : $!");

  };