"Fossies" - the Fresh Open Source Software Archive

Member "absence-v2.1/utils/AbsenceImport.pm" (20 Oct 2013, 7778 Bytes) of package /linux/www/web-absence-2.1.tar.gz:


As a special service "Fossies" has tried to format the requested source page into HTML format using (guessed) Perl source code syntax highlighting (style: standard) with prefixed line numbers and code folding option. Alternatively you can here view or download the uninterpreted source code file. For more information about "AbsenceImport.pm" see the Fossies "Dox" file reference documentation.

    1 #======================================================================
    2 #    This file is part of Absence.
    3 #
    4 #    Absence is free software: you can redistribute it and/or modify
    5 #    it under the terms of the GNU General Public License as published by
    6 #    the Free Software Foundation, either version 3 of the License, or
    7 #    (at your option) any later version.
    8 #
    9 #    Absence is distributed in the hope that it will be useful,
   10 #    but WITHOUT ANY WARRANTY; without even the implied warranty of
   11 #    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   12 #    GNU General Public License for more details.
   13 #
   14 #    You should have received a copy of the GNU General Public License
   15 #    along with Absence.  If not, see <http://www.gnu.org/licenses/>.
   16 #======================================================================
   17 
   18 # copyright Robert Urban
   19 
   20 package AbsenceImport;
   21 
   22 use DBI;
   23 use FileHandle;
   24 use Encode;
   25 
   26 use AbsenceUtils;
   27 
   28 my $DBH;
   29 
   30 END {
   31     defined($DBH) && $DBH->disconnect;
   32 }
   33 
   34 sub init
   35 {
   36     my ($db_user, $db_pass, $db_name, $db_host) = @_;
   37 
   38     my $cs = "dbi:Pg:dbname=$db_name";
   39     if (defined($db_host)) {
   40         $cs .= ";host=$db_host";
   41     }
   42     $DBH = DBI->connect($cs,
   43         $db_user, $db_pass, {AutoCommit => 1, RaiseError => 1});
   44 
   45     defined($DBH) || die "cannot connect to DB";
   46 }
   47 
   48 #------------------------------------------------------------------
   49 # loadCountryTable()
   50 # input:
   51 #   expects a reference to array of the form:
   52 #   [
   53 #       { code => 'CC', name => 'name of country' },
   54 #       { code => 'C2', name => 'name of country2' },
   55 #       { code => 'C3', name => 'name of country3' },
   56 #   ]
   57 # country names should be encoded as UTF8
   58 #------------------------------------------------------------------
   59 sub loadCountryTable
   60 {
   61     my $aref = shift;
   62 
   63     my $sql = qq{
   64         INSERT INTO c_country
   65         (code,name)
   66         VALUES (?,?);
   67     };
   68 
   69     my $sth = $DBH->prepare($sql);
   70     defined($sth) || die "prepare of [$sql] failed";
   71 
   72     my $rv;
   73     foreach my $country (@{ $aref }) {
   74         $rv = $sth->execute($country->{code}, $country->{name});
   75         defined($rv) || print "error occured for code=$country->{code}, name=$country->{name}\n";
   76     }
   77 
   78     $sth->finish;
   79 }
   80 
   81 sub loadHoliday
   82 {
   83     my $href = shift;
   84 
   85     my (@fields, @values, @placeholders);
   86 
   87     foreach my $field (keys(%{ $href })) {
   88         push(@fields, $field);
   89         push(@values, $href->{$field});
   90         push(@placeholders, '?');
   91     }
   92     my $fields = join(',', @fields);
   93     my $ph = join(',', @placeholders);
   94 
   95     my $sql = qq{
   96         INSERT INTO c_holiday
   97         ($fields)
   98         VALUES ($ph);
   99     };
  100     #print "SQL: $sql";
  101 
  102     $DBH->do($sql, undef, @values);
  103 
  104     my $id = getCurrval('c_holiday_id_seq');
  105     return $id;
  106 }
  107 
  108 sub lookupCountry
  109 {
  110     my $code = uc(shift);
  111 
  112     my $sql = qq{
  113         SELECT id
  114         FROM c_country
  115         WHERE UPPER(code) = '$code';
  116     };
  117 
  118     my $ref = dbSelect($sql);
  119     return defined($ref) ? $ref->{id} : undef;
  120 }
  121 
  122 sub lookupRegion
  123 {
  124     my $name = uc(shift);
  125 
  126     my $sql = qq{
  127         SELECT id
  128         FROM c_region
  129         WHERE UPPER(name) = '$name';
  130     };
  131 
  132     my $ref = dbSelect($sql);
  133     return defined($ref) ? $ref->{id} : undef;
  134 }
  135 
  136 sub createRegion
  137 {
  138     my ($name, $desc) = @_;
  139 
  140     my $fields = 'name';
  141     my $values = "'$name'";
  142     if (defined($desc)) {
  143         $fields .= ',description';
  144         $values .= ",'$desc'";
  145     }
  146 
  147     my $sql = qq{
  148         INSERT INTO c_region
  149             ($fields)
  150         VALUES
  151             ($values);
  152     };
  153 
  154     #$DBH->execute($sql);
  155 
  156     my $sth = $DBH->prepare($sql);
  157     $sth->execute || die "execute on sql failed:\n$sql\n--end--\n";
  158     $sth->finish;
  159 
  160     my $id = getCurrval('c_region_id_seq');
  161     return $id;
  162 }
  163 
  164 sub getCurrval
  165 {
  166     my $sequence = shift;
  167 
  168     my $sql = qq{SELECT currval('$sequence');};
  169     my $sth = $DBH->prepare($sql);
  170     $sth->execute || die "failed to get currval for $sequence\n";
  171     my $id = ($sth->fetchrow_array)[0];
  172     defined($id) || die "no ID returned.";
  173     return $id;
  174 }
  175 
  176 sub getDbHolidays
  177 {
  178     my $sql = qq{
  179         SELECT * FROM c_holiday ORDER BY day;
  180     };
  181 
  182     return dbSelect($sql);
  183 }
  184 
  185 my %COUNTRY_CACHE;
  186 sub getCountry
  187 {
  188     my $id = shift;
  189 
  190     if (exists($COUNTRY_CACHE{$id})) {
  191         return $COUNTRY_CACHE{$id};
  192     }
  193 
  194     my $sql = qq{
  195         SELECT * FROM c_country WHERE id = $id;
  196     };
  197 
  198     return dbSelect($sql);
  199 }
  200 
  201 sub getCountries
  202 {
  203     my $sql = qq{
  204         SELECT * FROM c_country;
  205     };
  206 
  207     return dbSelect($sql);
  208 }
  209 
  210 my %REGION_CACHE;
  211 sub getRegion
  212 {
  213     my $id = shift;
  214 
  215     if (exists($REGION_CACHE{$id})) {
  216         return $REGION_CACHE{$id};
  217     }
  218 
  219     my $sql = qq{
  220         SELECT * FROM c_region WHERE id = $id;
  221     };
  222 
  223     return dbSelect($sql);
  224 }
  225 
  226 sub getRegions
  227 {
  228     my $sql = qq{
  229         SELECT * FROM c_region WHERE invalidated IS NULL;
  230     };
  231 
  232     return dbSelect($sql);
  233 }
  234 
  235 sub lookupHoliday
  236 {
  237     my ($d, $m, $y, $addit) = @_;
  238 
  239     my %qual = (
  240         country_id  => undef,
  241         region_id   => undef,
  242     );
  243     if (defined($addit)) {
  244         if (!exists($qual{ $addit->[0] })) {
  245             die "qualifier [$addit->[0]] unknown";
  246         }
  247         $qual{ $addit->[0] } = $addit->[1];
  248     }
  249 
  250     my $day = sprintf('%04d-%02d-%02d', $y, $m, $d);
  251     
  252     my $sql = qq{
  253         SELECT id FROM c_holiday WHERE day = '$day'
  254     };
  255     foreach my $q (keys(%qual)) {
  256         if (defined($qual{$q})) {
  257             $sql .= " AND $q = $qual{$q}";
  258         }
  259         else {
  260             $sql .= " AND $q IS NULL";
  261         }
  262     }
  263     $sql .= ';';
  264 
  265     my $ref = dbSelect($sql);
  266     return defined($ref) ? $ref->{id} : undef;
  267 }
  268 
  269 sub deleteHoliday
  270 {
  271     my $id = shift;
  272 
  273     my $sql = qq{
  274         DELETE FROM c_holiday WHERE id = $id;
  275     };
  276 
  277     my $res = $DBH->do($sql);
  278     defined($res) || die "delete of id [$id] from c_holiday failed\n";
  279 }
  280 
  281 sub deleteAllHolidays
  282 {
  283     my $id = shift;
  284 
  285     my $sql = qq{
  286         DELETE FROM c_holiday;
  287     };
  288 
  289     my $res = $DBH->do($sql);
  290     defined($res) || die "delete of id [$id] from c_holiday failed\n";
  291 
  292     $sql = qq{ ALTER SEQUENCE c_holiday_id_seq RESTART WITH 1; };
  293     $res = $DBH->do($sql);
  294     defined($res) || die "restart sequence c_holiday_id_seq failed";
  295 }
  296 
  297 sub deleteRegion
  298 {
  299     my $id = shift;
  300 
  301     my $now = time();
  302 
  303     my $sql = qq{
  304         UPDATE c_region SET invalidated = int2tsz($now) WHERE id = $id;
  305     };
  306 
  307     my $res = $DBH->do($sql);
  308     defined($res) || die "delete of id [$id] from c_region failed\n";
  309 }
  310 
  311 sub parseIcal
  312 {
  313     my $file = shift;
  314 
  315     my $fh = FileHandle->new($file);
  316     defined($fh) || die "failed to open file [$file] for reading: $!";
  317 
  318     my $curr_event;
  319     my $state;
  320     my $data;
  321     my $debug = 0;
  322 
  323     while(<$fh>) {
  324         s/[\n\r]+$//;
  325         $debug && print "[$_]\n";
  326         if (!defined($state)) {
  327             if (!/^BEGIN:VCALENDAR$/) {
  328                 die "file does not begin with 'BEGIN:VCALENDAR'\n";
  329             }
  330             $state = 'calendar';
  331             $debug && print "  state = calendar\n";
  332         }
  333         elsif ($state eq 'calendar') {
  334             if (/^BEGIN:VEVENT$/) {
  335                 $state = 'event';
  336                 $debug && print "  state = event\n";
  337                 $curr_event = {};
  338                 next;
  339             }
  340         }
  341         elsif ($state eq 'event') {
  342             if (/^END:VEVENT/) {
  343                 $state = 'calendar';
  344                 $debug && print "  state = calendar\n";
  345                 exists($curr_event->{date}) || next;
  346                 my $d = $curr_event->{date};
  347                 if (exists($curr_event->{fixed})) {
  348                     $d->{year} = '*';
  349                 }
  350                 $data->{ $d->{year} }->{ $d->{month} }->{ $d->{day} }
  351                     = $curr_event->{summary};
  352                 next;
  353             }
  354             if (/^SUMMARY:(.*)$/) {
  355                 $curr_event->{summary} = decode_utf8($1);
  356                 $debug && print "  summary = [$1]\n";
  357             }
  358             elsif (/^DESCRIPTION:(.*)$/) {
  359                 $curr_event->{description} = decode_utf8($1);
  360             }
  361             elsif (/^DTSTART(;[^:]+)?:(.*)$/) {
  362                 if ($1 eq ';VALUE=DATE') {
  363                     my ($y, $m, $d) = ($2 =~ /^(\d{4})(\d{2})(\d{2})$/);
  364                     $curr_event->{date} = { year => $y, month => $m, day => $d };
  365                     $debug && print "  date = [$y/$m/$d]\n";
  366                 }
  367             }
  368             elsif (/^RRULE:/ && /FREQ=YEARLY/i) {
  369                 $curr_event->{fixed} = 1;
  370             }
  371         }
  372     }
  373     $fh->close;
  374 
  375     return $data;
  376 }
  377 
  378 1;
  379 ###########################
  380 sub dbSelect
  381 {
  382     my ($sql, @params) = @_;
  383 
  384     my $sth;
  385     eval {
  386         $sth = $DBH->prepare($sql);
  387     };
  388     if ($@) {
  389         die $@;
  390     }
  391 
  392     $sth->execute(@params) || die "statement [$sql] failed";
  393 
  394     my @result;
  395     my $ref;
  396     while(defined($ref = $sth->fetchrow_hashref)) {
  397         push(@result, $ref);
  398     }
  399 
  400     if (wantarray) {
  401         return @result;
  402     } else {
  403         @result || return undef;
  404         return $result[0];
  405     }
  406 }
  407 
  408 1;