Subversion Repositories ORC

#######################################################################
# A set of functions to assist the scan data display tool.
#       Kept here to unclutter the main script.
#######################################################################
#
#
# $Log: scanFunctions.pm,v $
#

use DBI ();
use WebDB;

#my $internalDBH = &getDBConnection || die "Unable to connect to Database\n\n";
my $internalDBH = WebDB->connect () || die "Unable to connect to Database\n\n";

sub currentTime {
  my $nowhan = $internalDBH->prepare("select now()");
  $nowhan->execute();
  my ($now) = $nowhan->fetchrow;
  $now =~ s/ / at /;
  
  return $now;
}

sub byfield { $colOrderHash{$a} <=> $colOrderHash{$b};  }

sub exportExcel {
  my $listref = shift;
  my $FN_Prefix = shift // "ORC_Export";
  
  use Spreadsheet::WriteExcel;

        my $date = `date +"%m%d%y%H%M%S"`; chomp $date;
  my $filename = "${FN_Prefix}_${date}_$$.xls";
  
        print "Content-type: application/vnd.ms-excel\n";
  # The Content-Disposition will generate a prompt to save the file. If you want
  # to stream the file to the browser, comment out the following line.
  print "Content-Disposition: attachment; filename=$filename\n";
  print "\n";
 
  # Create a new workbook and add a worksheet. The special Perl filehandle - will
  # redirect the output to STDOUT
  #
  my $workbook  = Spreadsheet::WriteExcel->new(\*STDOUT);
  my $worksheet = $workbook->add_worksheet();
        
        my $format = $workbook->add_format();
        $format->set_bold();
        
        my $col = $row = 0;
        
        foreach $f (@displayFields)
                { $worksheet->write($row, $col++, "$NAME{$f}", $format); }
        
        foreach $t (sort @{ $listref })                 # Unt now we print the tickets!
        {
                $col = 0;
                $row++;
                foreach $f (@displayFields)
                        {
                                $t->{$f} =~ s/<br>/\n/ig; $worksheet->write($row, $col++, "$t->{$f}");
                        }
        }
        
        $workbook->close();
  return;
}


sub fetchColEntries {                        
        my $colName = shift;
        my $selection = shift;
        my $table = $DBTABLE;
        my $optionList = "";
  
  if ($colName eq "derby_name" and $LVL < 2) {
    # special case to anonymize assignees...
#    my @opts = ("-blank-", $ORCUSER->{derby_name});
    $optionList = join "", map { $selection eq $_ ? "<OPTION selected>$_</OPTION>" : "<OPTION>$_</OPTION>" } ("-blank-", $ORCUSER->{derby_name});
  } else {
    my $dept_where = $table eq "v_shift" ? "where dept != 'PER'" : "";
          my $cathan = $internalDBH->prepare("select distinct $colName from $table $dept_where order by $colName");

        $cathan->execute();
        while (my ($cat) = $cathan->fetchrow) {
          if ($cat eq "") { $cat = "-blank-"; }
          if ($cat eq $selection) {
            $optionList .= "<OPTION selected>$cat</OPTION>";
          } else {
                  $optionList .= "<OPTION>$cat</OPTION>";
                }
        }
  }
  
        return $optionList;
}

sub fetchDerbyNameWithRCid {
  my $DEPT = shift // "";
        my $optionList = "";
        
        my $cathan;
        if (!$DEPT) {
          $cathan = $internalDBH->prepare("select RCid, derby_name from official where level > 0 order by derby_name");
        $cathan->execute();
        } else {
          $cathan = $internalDBH->prepare("select RCid, derby_name from official where level > 0 and department like ? order by derby_name");
        $cathan->execute("%".$DEPT."%");
        }

        while (my ($id, $cat) = $cathan->fetchrow) {
                $optionList .= "<OPTION value=$id>$cat</OPTION>";
        }

        return $optionList;
}


sub filter {
        my $colName = shift;
        my $filter = shift;
        
        if (exists &{"filter_".$colName}) { return &{"filter_".$colName} ($colName, $filter); }

        if ($colFilterTypeHash{$colName} eq 'select')
        {
                if (defined $filter)    {
                        if ($filter eq "-blank-") {
                                return "($colName = '' or isNull($colName) = 1)";
                        }
#                       $filter = s/'/\'/g;
                        return "$colName = \"$filter\"";
                }
                else
                {
                        my $thing = "filter-${colName}";
                        my $categories = &fetchColEntries($colName, $FORM{$thing});
                        my $Options = "<OPTION></OPTION>".$categories;
        
                        $Options =~ s/>($FORM{$thing})/ selected>$1/;
                        return "<SELECT name=filter-${colName} $onChange>$Options</SELECT>";
                }
        } elsif ($colFilterTypeHash{$colName} eq 'text') {
                if (defined $filter)
                {
                        if ($filter =~ s/^=\s*//) { return "$colName = \"$filter\""; }
                        
                        if ($filter !~ s/^\^\s*//) { $filter = "\%$filter"; }
                        if ($filter !~ s/\s*\$$//) { $filter = "$filter\%"; }
                        $filter =~ s/\*/%/g;
      
                        return "$colName like \"$filter\"";
                }
                else
                {
                        my $thing = "filter-${colName}";
                        return "<INPUT type=text name=$thing value=\"$FORM{$thing}\" size=15 $onChange>";
                }
        } elsif ($colFilterTypeHash{$colName} eq 'number') {
                if (defined $filter)
                {
                        if ($filter =~ /^[>=<]\s*/) { return "$colName $filter"; }
                        else { return "$colName = $filter"; }
                }
                else
                {
                        my $thing = "filter-${colName}";
                        return "<INPUT type=text name=$thing value=\"$FORM{$thing}\" size=2 $onChange>";
                }
        } elsif ($colFilterTypeHash{$colName} eq 'date') {
                if (defined $filter)
                {
                        if ($filter =~ s/^<\s*//) { return "$colName < '$filter'"; }
                        if ($filter =~ s/^>\s*//) { return "$colName > '$filter'"; }
                        
                        return "$colName = '$filter'";
                }
                else
                {
                        my $thing = "filter-${colName}";
                        return "<INPUT type=text name=$thing value=\"$FORM{$thing}\" size=15 $onChange>";
                }
        } elsif ($colFilterTypeHash{$colName} eq 'ERROR') {
                return "<center><strong><font color=red>ERROR!</font>";
        } elsif ($colFilterTypeHash{$colName} eq 'none') {
                return;
        }
}


sub getData {
        my $fields = shift;
        my $whereClause = shift;
        my $table = shift;
        my $orderby = shift;
        my $curpage = shift // 0; 
        my $pagelimit = shift // "All";
        
        my $selected = '*';
        $whereClause = scalar @{$whereClause} > 0 ? "where ".join " and ", @{$whereClause} : '';
  
  if ($orderby eq "dayofweek") {
        $orderby = "order by date, time";
  } elsif ($orderby eq "eventid") { # only applicable to the log viewer...
        $orderby = "order by eventid desc";
  } else {
          $orderby = $orderby eq "" ? "" : "order by $orderby";
  }
  
  my $getMe;
  if ($pagelimit eq "All") {
        $getMe = "select distinct * from $table $whereClause $orderby";
  } else {
    $curpage = ($curpage - 1) * $pagelimit;
        $getMe = "select distinct * from $table $whereClause $orderby limit $curpage, $pagelimit";
  }
        my ($totalcount) = $internalDBH->selectrow_array ("select distinct count(*) from $table $whereClause");

        my $limhan = $internalDBH->prepare($getMe);                     # Get the tickets from the DB
        $limhan->execute();
        
        my @results = ();
        while (my $P = $limhan->fetchrow_hashref)
        {               
                push @results, $P;
        }
        
        return (\@results, $totalcount);
}


sub getDBConnection {
  use WebDB;
  $dbh = WebDB::connect ();
  return $dbh;
}


sub inArray {
        my $item = shift;
        my $array = shift;
        foreach (@{$array})
        {
                return 1 if $item eq $_;
        }
        return 0;
}

sub notInArray {
        return ! inArray (@_);
}


sub uniq (@) {
    # From CPAN List::MoreUtils, version 0.22
    my %h;
    map { $h{$_}++ == 0 ? $_ : () } @_;
}


sub whereInArray {
        my $item = shift;
        my $array = shift;
        my $i = 0;
        foreach (@{$array})
        {
                return $i if $item eq $_;
                $i++;
        }
        return -1;
}



# Leave this alone, it's needed to compile correctly
return 1;