#! /usr/perl

use DBI;
use warnings;
use strict;

my $dbh = DBI->connect('DBI:Oracle:R12COE','apps','app5vis') or die "couldn't connect to database: " . DBI->errstr;

my $sth = $dbh->prepare("SELECT DISTINCT UPPER(OBJECT_NAME) FROM CG_COMPARATIVE_MATRIX_TAB WHERE OBJECT_NAME IS NOT NULL ORDER BY 1 ASC") or die "couldn't pr
epare statement: " . $dbh->errstr;

$dbh->{AutoCommit}    = 0;

$dbh->{RaiseError}    = 1;

$dbh->{ora_check_sql} = 0;

$dbh->{RowCacheSize}  = 16;

my $sth1;
my $count = 0;
my $i = 0;
my $path = "";
my $result;
my @search;
my $arrsize;
my $filename;
my $fextn;
my @files;
my $dir;
my $file;
my $fh;
my $ext = "";
my $j = 0;
my @data;
$sth->execute;
my @obj_name;
my $obj_name;
while(@data = $sth->fetchrow_array())
{
        $obj_name[$j]= $data[0];
        $j++;
}

$dir = '/u05/oracle/R12COE/spotlighter/Search_Files/Forms';
opendir(DIR,$dir)or die $!;
@files = grep{-f "$dir/$_"} readdir(DIR);

#       $result = `grep -i -w -c "$data[0]"  /u05/oracle/R12COE/spotlighter/Search_Files/Forms/*`;
        foreach $file (@files)
        {
                open $fh,"< $file" or die "couldn't open $file:$!";
                {
                        for $obj_name(@obj_name)
                        {
                                ($ext) = $file =~ /(\.[^.]+)$/;
                                #printf "%s has %d matches for pattern /%s/\n",$file,scalar(grep /$obj_name/, <$fh>),$obj_name;
                                $count = scalar(grep /$obj_name/, <$fh>);

                                $sth1 = $dbh->prepare("INSERT INTO CUSTOM_FILES_SUMMARY(FILE_NAME,FILE_TYPE,DEP_OBJECT_NAME,OCCURANCE)VALUES('$file','$ext','
$obj_name',$count)") or die "couldn't insert statement: " . $dbh->errstr;
                                $sth1->execute;
                        }
                }
                close $fh;

        }
$sth->finish;
$sth1->finish;
$dbh->disconnect;