#!c:/xampp/perl/bin/perl
 
use strict;
use warnings;
use DBI;
use dbConnect_nursing;

sub printVariables(@_)
{
    foreach my $variable (@_)
    {
        print $variable . "\n";
    }
    print "###############################################\n";
}

sub processLine
{
    my @temp;
    chomp($_[0]);
    unshift(@temp, $_[0]);
    my $var = shift(@temp);
    return $var;
}
################################### connect to the database ############################################
# data source name
my $dsn = "DBI:$dbConnect::db_platform:$dbConnect::db_database:$dbConnect::db_host:$dbConnect::db_port";

# perl DBI connect
my $connect = DBI->connect($dsn, $dbConnect::db_user, $dbConnect::db_pw, {'RaiseError' => 1});
################################### connect to the database ############################################
my @file_array;
my $in_file = "c:\\nursing_homes.txt";
my $out_file = "c:\\nursing_homes_out.txt";
if (-e $out_file)
{
    unlink $out_file;
}
open INPUT,'<',$in_file or die "Can't open file " . $in_file . "\n$!\n"; #Open for read
open OUTPUT,'>',$out_file or die "Can't open file " . $out_file . "\n$!\n"; #Open for write
    while (<INPUT>)
    {
        chomp ($_);
        next if $_ =~ /^\s*$/;  # skip over blank lines
        print OUTPUT $_ . "\n";
    }
    close INPUT;
    close OUTPUT;
    
open INPUT,'<',$out_file or die "Can't open file " . $out_file . "\n$!\n"; #Open for read
    while (<INPUT>)
    {
        my $name = $connect->quote($_);
        next;
        my $address1 = $connect->quote($_);
        next;
        my $address2 = $connect->quote($_);
        next;
        my $phone = $connect->quote($_);
        next;
        next if (($_ =~ /^.*Council.*$/) || ($_ =~ /^Continuing.*$/) || ($_ =~ /^Mapping.*$/));
        next if (($_ =~ /^.*Council.*$/) || ($_ =~ /^Continuing.*$/) || ($_ =~ /^Mapping.*$/));
        next if (($_ =~ /^.*Council.*$/) || ($_ =~ /^Continuing.*$/) || ($_ =~ /^Mapping.*$/));
        my $overall = $connect->quote($_);
        next;
        my $inspections = $connect->quote($_);
        next;
        my $staffing = $connect->quote($_);
        next;
        my $quality = $connect->quote($_);
        next;
        my $programs = $connect->quote($_);
        next;
        my $beds = $connect->quote($_);
        next;
        my $ownership = $connect->quote($_);
        next;
        my $query_string = "INSERT INTO nursing_homes (name, address1, address2, phone, overall, inspections, staffing, quality, programs, beds, ownership) VALUES ($name, $address1, $address2, $phone, $overall, $inspections, $staffing, $quality, $programs, $beds, $ownership)";
        #printVariables($name, $address1, $address2, $phone, $overall, $inspections, $staffing, $quality, $programs, $beds, $ownership, $query_string);
        my $query_handle = $connect->prepare("INSERT INTO nursing_homes (name, address1, address2, phone, overall, inspections, staffing, quality, programs, beds, ownership) VALUES ($name, $address1, $address2, $phone, $overall, $inspections, $staffing, $quality, $programs, $beds, $ownership)");
        $query_handle->execute();
    }

close INPUT;
$connect->disconnect();
__END__