Listing 2

Auszüge aus der Reisekostenabrechnung per Webformular.

#!/usr/local/bin/perl

use CGI::Pretty qw/:standard :html3 :cgi-lib -no_xhtml -oldstyle_urls/;
use strict;
use Spreadsheet::WriteExcel;
use Date::Calc qw(Date_to_Days Delta_Days);

use vars qw ($sheet $zeile $text_f);

my $sheetbase = "/tmp/rk_";
my $ueber_pausch   = "39"; # Pauschale für Übernachtungskosten
my $kuerzung_frueh = "9" ; # Kürzung für Frühstück im Hotelpreis
my $km_pausch      = "0.52"; # Kilometerpauschale
my %pausch = ( "8" => "10",
            "14" => "20",
            "24" => "46");
my @pausch_sprung = sort { $a <=> $b } keys %pausch;

my $q = new CGI;

if ( $q->param()) {
  write_sheet();
} else {
  write_form();
}

sub write_form {

  my @table;

...
    
  push @table, TR(td({colspan => 2},b("Übernachtungskosten")));
  autoEscape(undef);
  push @table, TR(td([radio_group(-name => "hotel",
                      -rows => 2,
                      -default => 'Einzelnachweis',
                      -values => [
                               'Einzelnachweis',
                               'Pauschal' ,
                              ]
                     ),
                textfield(-name => "hotel_einzeln",
                     -size => 20,
                     -maxlength => 100) . " " .
                checkbox(-name => "hotel_fruehstuck",
                     -label => " Inkl. Frühstück",
                     -checked => 0,
                    -value => "1")]));
...
  autoEscape(1);
...

  print header, start_html(-title => "Reisekosten Inland",
                 -bgcolor => "#CCCCCC"),
  start_form(-name => "RKForm",
          -method => "GET"),
  table(@table);
  
  print submit(-name => "Abschicken"),end_form, end_html;
          
}

sub zeit {
  my ($name) = @_;
  return popup_menu(-name => $name . "stunde",
              -values => [ '0' .. '23']) . b(":") .
                popup_menu(-name => $name . "minute",
                     -values => ['00' , '15', '30', '45']);
}

sub write_sheet {
  my $filename = $sheetbase . param("nname") .".xls";
  my $excel = new Spreadsheet::WriteExcel($filename);

  $text_f     = $excel->addformat();
  my $head1_f = $excel->addformat();
  my $head2_f = $excel->addformat();
  my $date_f  = $excel->addformat();
  my $time_f  = $excel->addformat();
  my $money_f  = $excel->addformat();
  my $times_f   = $excel->addformat();
  my (@fahrt, @verpflegung, @uebernachtung);

...  
  $head2_f->set_bold();
  $head2_f->set_font("Arial");
  $head2_f->set_size("10");
  
  $text_f->copy($head2_f);
  $text_f->set_bold(0);

  $date_f->copy($text_f);
  $date_f->set_num_format('dd.mm.yyyy');
...

  $sheet = $excel->addworksheet();
  $zeile = 0;
  $sheet->write($zeile,0,"Reisekostenabrechnung",$head1_f);
  $zeile = 2;
  beschriftung("Vorname, Nachname:" . 
            param("vname"). " ".param("nname"));
  $zeile++;
  beschriftung("Abteilung: iX-Redaktion");
  $zeile += 2;
  beschriftung("Reisebeginn am");
  $sheet->write($zeile,1,excel_date("beginn"),$date_f);
  $sheet->write($zeile,2,excel_time("beginn"),$time_f);
  $zeile++;
...
  $sheet->write($zeile,0,"Kilometergeld:",$text_f);
  if (param("pkw")) {
    $sheet->write($zeile,1,param("pkw"),$times_f);
    $sheet->write($zeile,2,$km_pausch,$money_f);
    $sheet->write_formula($zeile,3,
                 "=".rowcol_to_cell($zeile,1)."*$km_pausch",
                 $money_f);
  }

...
  $sheet->write($zeile,0,"Verpflegungspauschale",$head2_f);
  $sheet->write($zeile,1,"Anzahl Tage",$text_f);
  $sheet->write($zeile,2,"Pauschale",$text_f);
  $zeile++;
  $verpflegung[0] = $zeile;

  my $count;
  foreach my $h (@pausch_sprung) {
    if ($h == 24) {
      $count = tage_gesamt() - 1;
      beschriftung("Täglich für 24 Std.");
    } else {
      $count = (erster_tag() == $h ? 1:0) + 
      (letzter_tag() == $h ? 1: 0);
      beschriftung("Täglich über $h Std.");

    }
    if ($count) {
      $sheet->write($zeile,1,$count,$times_f);
      $sheet->write($zeile,2,$pausch{$h},$money_f);
      $sheet->write($zeile,3,"=".rowcol_to_cell($zeile,1).
        "*".rowcol_to_cell($zeile,2),$money_f);    
    }
    $zeile++;
  }
  
...
  $zeile += 3;
  beschriftung("Auszahlung(+)/Rückzahlung(-)");
  $sheet->write($zeile,3,"=" .
          rowcol_to_cell($zeile-2,3). "-" .
          rowcol_to_cell($zeile-1,3),$money_f);
  $excel->close();
  
}
sub beschriftung {
  $sheet->write($zeile,0,$_[0],$text_f);
}

sub excel_date{
  my ($prefix) = @_;
  my $string = "=DATE(" .
    join(',',((param($prefix."jahr") || 2000) - 1900,
           param($prefix."monat") || 1,
           param($prefix."tag") || 1)) . ")";
  return $string;
}

sub tage_gesamt {
  return Delta_Days(param("beginnjahr"), param("beginnmonat"),
              param("beginntag"),
              param("endejahr"), param("endemonat"),
              param("endetag"));
}

sub erster_tag {
  my $minutes;
  if (tage_gesamt) {
    $minutes = (24 - param("beginnstunde")) * 60 - param("beginnminute");
  } else {
    $minutes = param("beginnstunde") * 60 + param("beginnminute");
  }
  foreach my $p (reverse @pausch_sprung) {
    return $p if ($minutes >= $p * 60);
  } 
  return 0;
}