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;