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 ();

my $internalDBH = &getDBConnection || die "Unable to connect to Database\n\n";

my $nowhan = $internalDBH->prepare("select now()");

$nowhan->execute();

our ($now) = $nowhan->fetchrow;

$now =~ s/ / at /;

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

sub fetchColEntries {

	my $colName = shift;

	my $selection = shift;

	my $table = $DBTABLE;

	my $optionList = "";

	# project table in new DB?

#	my $cathan = $internalDBH->prepare("select distinct $colName from $table where $colName <> '' and isNull($colName) = 0 order by $colName");

	my $cathan = $internalDBH->prepare("select distinct $colName from $table 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 $optionList = "";

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

	$cathan->execute();

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

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

	}

	return $optionList;

}

sub generica {

	my $colName = shift;

	my $filter = shift;

	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} $auto2>$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 $auto2>";

		}

	} 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=15 $auto2>";

		}

	} 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 $auto2>";

		}

	} elsif ($colFilterTypeHash{$colName} eq 'ERROR') {

		return "<center><strong><font color=red>ERROR!</font>";

	}

}

sub getData {

	my $fields = shift;

	my $whereClause = shift;

	my $table = shift;

	my $orderby = shift;

#	my $RCid = shift;

#	my $SecLvl = shift;

#	my $selected = join ", ",  @{$fields};

	my $selected = '*';

	$whereClause = scalar @{$whereClause} > 0 ? "where ".join " and ", @{$whereClause} : '';

        # project in new DB?

	# my $getMe = "select distinct $selected from HOST left join PRODUCT_INSTANCE on HOST.server = PRODUCT_INSTANCE.server left join project on hostID = projectHostID $whereClause";

	#my $getMe = "select distinct $selected from HOST left join PRODUCT_INSTANCE on HOST.server=PRODUCT_INSTANCE.server $whereClause and HOST.page=HOST.maxPage and PRODUCT_INSTANCE.page = PRODUCT_INSTANCE.maxPage";

	$orderby = $orderby eq "" ? "" : "order by $orderby";

#	my $getMe = "select distinct $selected from $table $whereClause $orderby";

	my $getMe = "select distinct * from $table $whereClause $orderby";

#warn($getMe);

	my $limhan = $internalDBH->prepare($getMe);			# Get the tickets from the DB

	$limhan->execute();

	my @results = ();

	while (my $P = $limhan->fetchrow_hashref)

	{

#		if ($P->{assignee_id} == $RCid or ($SecLvl >= 3 and $P->{derby_name})) {

#			$P->{derby_name} = "$P->{derby_name} <A HREF='#' onClick=\"window.open('make_lead_shift_change.pl?change=del&shift=$P->{id}','Confirm Shift Change','resizable,height=260,width=370'); return false;\">[DROP]</a>";

#		} elsif (!$P->{derby_name}) {

#			$P->{derby_name} = "<A HREF='#' onClick=\"window.open('make_lead_shift_change.pl?change=add&shift=$P->{id}','Confirm Shift Change','resizable,height=260,width=370'); return false;\">[SIGN UP]</a>";

#		}

		push @results, $P;

	}

	return \@results;

}

sub getDBConnection {

    $dbh = DBI->connect('DBI:mysql:database=rollerco_data;host=qnap.home.lan;port=3306', 'root', 'Jopy666!'

                   ) || die "Could not connect to database: $DBI::errstr";

    return $dbh;

}

sub inArray {

	my $item = shift;

	my $array = shift;

	foreach (@{$array})

	{

		return 1 if $item eq $_;

	}

	return 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;