Subversion Repositories VORC

#package tableViewer;

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

# A set of functions to assist the scan data display tool.

#	Kept here to unclutter the main script.

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

#

#

# $Log: scanFunctions.pm,v $

#

#use strict;

use Exporter 'import';

use cPanelUserConfig;

use DBI ();

use WebDB;

our @EXPORT = qw( byfield currentTime exportExcel fetchColEntries fetchDerbyNameWithRCid filter getData getDBConnection inArray notInArray uniq whereInArray );

#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 {

  use DateTime;

  my $now = DateTime->now (time_zone => 'America/Los_Angeles');

  $now =~ s/T/ at /;

  return $now." US/Pacific";

}

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)	{

			if ($f eq "derby_name") {

				if ($user->{department}->{"OFF"} < 2 and $t->{derby_name} and $t->{RCid} != $RCid and $LVL < 5) {

    			$t->{derby_name} = "FILLED";

  			}

			}

			$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});

    push @opts, map { @{$_} } @{ $internalDBH->selectall_arrayref ("select derby_name from official where showme = 1 and derby_name <> ? order by derby_name", undef, $ORCUSER->{derby_name}) };

    $optionList = join "", map { $selection eq $_ ? "<OPTION selected>$_</OPTION>" : "<OPTION>$_</OPTION>" } @opts;

  } else {

    my $dept_where = $table eq "v_shift" ? "where dept != 'PER' and year(date) = year(now())" : "";

    my $orderby = $colName eq "dayofweek" ? "field(dayofweek, 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday')" : $colName;

	  my $cathan = $internalDBH->prepare("select distinct $colName from $table $dept_where order by $orderby");

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

  if (ref $_[0] eq "HASH") {

    $ATTRIBUTES = shift;

  }

  my $DEPT = shift // "";

  my $selected = shift // "";

	my $optionList = "";

	my $cathan;

	if (!$DEPT or $DEPT eq "CMP") {

	  $cathan = $internalDBH->prepare("select RCid, derby_name from official where access > 0 order by derby_name");

  	$cathan->execute();

  } elsif ($DEPT eq "CLA") {

    $cathan = $internalDBH->prepare("select RCid, derby_name from v_official where access > 0 and isnull(MVPid) = false and last_login > CURRENT_DATE - INTERVAL 365 DAY order by derby_name");

    $cathan->execute;

	} else {

    $cathan = $internalDBH->prepare("select RCid, derby_name from official where access > 0 and (department like ? and department not like ?) and last_login > CURRENT_DATE - INTERVAL 365 DAY order by derby_name");

    $cathan->execute("%".$DEPT."%", "%".$DEPT."-0%");

	}

  my $valuelabel = $ATTRIBUTES->{DATALIST} ? "data-value" : "value";

	while (my ($id, $cat) = $cathan->fetchrow) {

	  if ($id == $selected) {

	    $optionList .= "<OPTION $valuelabel=$id selected>$cat</OPTION>";

	  } else {

	    $optionList .= "<OPTION $valuelabel=$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 =~ /\d{1,2}:\d{2}/ and $ORCUSER->{timeformat} eq "ampm") {

			  my ($h, $m) = split /:/, $filter;

			  my $caret;

			  if ($h =~ s/^\^\s*//) { $caret = "^"; }

			  if ($h < 8) { $h += 12; }

			  $filter = $caret.join ":", $h, $m;

			}

			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 'boolean') {

		if (defined $filter)

		{

			return "$colName = $filter";

		}

		else

		{

			my $thing = "filter-${colName}";

			my $Options = "<OPTION></OPTION><OPTION>True</OPTION><OPTION>False</OPTION>";

			$Options =~ s/>($FORM{$thing})/ selected>$1/;

			return "<SELECT name=filter-${colName} $onChange>$Options</SELECT>";

		}

	} 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; $curpage = 1 unless $curpage;

	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_arrayref ("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;