#!/usr/bin/perl -w
=item
command to update all the sites:

#for i in `ls /etc/opals/conf/`; do \cp -p /www/opals/script/update_20090615 /tmp/urt; sudo perl -pi -e "s/_MY_SITE_/$i/" /tmp/urt; sudo perl /tmp/urt; done

=cut
use lib '/www/opals/module';
#use Opals::Context("/etc/opals/conf/_MY_SITE_");
use Opals::Context("/etc/opals/conf/ztest");

use strict;
use DBI;
#use Getopt::Std;
use POSIX qw(
    ceil
);
my $dbh = Opals::Context->dbh();
END {
    if ($dbh) {
        $dbh->disconnect();
    }
}

$| = 1;
# Codes start...


my $zdbDir = Opals::Context->config('zRoot') .'/'
           . Opals::Context->config('zPort') .'/'
           . 'record' .'/'
           . Opals::Context->config('zDatabase');

my $indexDir;
my $attTbl;
my $query_insert ;

#change field modDate from timestamp -> datetime 
# to preserve time stamp


getUsers($dbh);

# Codes end.

exit 0;

sub makeConnection {
    my ($config) = @_;
    if (!$config) {
        return;
    }
    my ($db_driver, $db_name, $db_host, $db_port, $db_user, $db_password);

    $db_driver   = $config->{'db_driver'} || 'mysql';
    $db_name     = $config->{'db_name'};
    $db_host     = $config->{'db_host'};
    $db_port     = $config->{'db_port'}   || '3306';
    $db_user     = $config->{'db_user'};
    $db_password = $config->{'db_password'};

    my $dsn = "dbi:$db_driver:$db_name:$db_host:$db_port";

    return DBI->connect($dsn, $db_user, $db_password);
}
############################################################


sub loadConfig {
    my ($configFile) = @_;
#    print "Enter the config filename of Opals: ";
#    $configFile = <STDIN>;
    my $config = {};

    open CONF, $configFile || die "Cannot open file $configFile";
    while (<CONF>) {
        chomp;
        s/#.*//;                # remove comments
        next if /^\s*$/;        # ignore blank lines

        if (/^\s*(\w+)\s*=\s*(.*?)\s*$/) {
            $config->{$1} = $2;
        }
    }
    close CONF;
    
    return $config;
}

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

sub getUsers{
    my ($dbh)=@_;

   my $query ="select uid,schoolname,schoolURL from tmpusers";
   my $sth = $dbh->prepare($query);
   $sth->execute();
   while(my ($uid,$schoolName,$schoolURL)=$sth->fetchrow_array){
        updateTables($dbh,$uid,$schoolName,$schoolURL);
   }

  $sth->finish;

}

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

sub updateTables{
    my ($dbh,$uid,$schoolName,$schoolURL)=@_;
  
my $query1 ="REPLACE INTO `tmpprofile_user` (`id`, `uid`, `dbname`, `dbname_display`, `DBwebsite`, `dbLO`, `url`, `hostAdd`, `marcformat`, `expireDate`, `startDate`, `lastRenewal`, 
                                            `dbDescription`, `ipRangeB`, `ipRangeE`, `usertype`, `defaultDB`, `db_uid`, `db_pwd`, `db_profile`, `db_recFormat`, `default_subDB`)
                VALUES (NULL,?,'Cerf','C.E.R.F.',NULL,1,'http://cerfinfo.com/cerf/bin/cerfquest.asp',NULL,NULL,'2012-09-30',NULL,NULL,NULL,NULL,NULL,'data entry','true',NULL,NULL,NULL,NULL,'false')";
                
my $query2 ="REPLACE INTO `tmpprofile_user` (`id`, `uid`, `dbname`, `dbname_display`, `DBwebsite`, `dbLO`, `url`, `hostAdd`, `marcformat`, `expireDate`, `startDate`, `lastRenewal`, 
                                            `dbDescription`, `ipRangeB`, `ipRangeE`, `usertype`, `defaultDB`, `db_uid`, `db_pwd`, `db_profile`, `db_recFormat`, `default_subDB`)
                VALUES (NULL,?,'Lesson Plans','Encomppass',NULL,2,'http://lessonplan.cerfinfo.com/lpsearchquest.asp',NULL,NULL,'2012-09-30',NULL,NULL,NULL,NULL,NULL,'data entry','false',NULL,NULL,NULL,NULL,'false')";

my $query3 ="REPLACE INTO `tmpprofile_user` (`id`, `uid`, `dbname`, `dbname_display`, `DBwebsite`, `dbLO`, `url`, `hostAdd`, `marcformat`, `expireDate`, `startDate`, `lastRenewal`, 
                                            `dbDescription`, `ipRangeB`, `ipRangeE`, `usertype`, `defaultDB`, `db_uid`, `db_pwd`, `db_profile`, `db_recFormat`, `default_subDB`)
                 VALUES (NULL,?,'NOVEL-Ebsco','NOVEL Database',NULL,7,'http://kbn.cerfinfo.com/inc/asp/ebscosrch.asp',NULL,NULL,'2012-09-30',NULL,NULL,NULL,NULL,NULL,NULL,'false',NULL,NULL,NULL,NULL,'false') ";

my $query4 ="REPLACE INTO `tmpprofile_user` (`id`, `uid`, `dbname`, `dbname_display`, `DBwebsite`, `dbLO`, `url`, `hostAdd`, `marcformat`, `expireDate`, `startDate`, `lastRenewal`, 
                                            `dbDescription`, `ipRangeB`, `ipRangeE`, `usertype`, `defaultDB`, `db_uid`, `db_pwd`, `db_profile`, `db_recFormat`, `default_subDB`)
                 VALUES (NULL,?,'NOVEL-Gale','NOVEL Database',NULL,8,'http://kbn.cerfinfo.com/inc/php/gale.php','z3950.iacenter.com:210/GAU',NULL,'2012-09-30',NULL,NULL,NULL,NULL,NULL,NULL,'false',NULL,NULL,NULL,NULL,'false') ";

my $query5 ="REPLACE INTO `tmpprofile_user` (`id`, `uid`, `dbname`, `dbname_display`, `DBwebsite`, `dbLO`, `url`, `hostAdd`, `marcformat`, `expireDate`, `startDate`, `lastRenewal`, 
                                            `dbDescription`, `ipRangeB`, `ipRangeE`, `usertype`, `defaultDB`, `db_uid`, `db_pwd`, `db_profile`, `db_recFormat`, `default_subDB`)
                 VALUES  (NULL,?,'Plan Book','Teacher Plan Book',NULL,6,NULL,NULL,NULL,'2012-09-30',NULL,NULL,NULL,NULL,NULL,'data entry','false',NULL,NULL,NULL,NULL,'false')";

my $query6 ="REPLACE INTO `tmpprofile_user` (`id`, `uid`, `dbname`, `dbname_display`, `DBwebsite`, `dbLO`, `url`, `hostAdd`, `marcformat`, `expireDate`, `startDate`, `lastRenewal`, 
                                            `dbDescription`, `ipRangeB`, `ipRangeE`, `usertype`, `defaultDB`, `db_uid`, `db_pwd`, `db_profile`, `db_recFormat`, `default_subDB`)
                 VALUES (NULL,?,'School Library',?,?,4,'http://kbn.cerfinfo.com/inc/asp/queryxmlmarc.asp',?,'xml','2012-09-30',NULL,NULL,NULL,NULL,NULL,'data entry','false',NULL,NULL,NULL,NULL,'false') ";

my $query7 ="REPLACE INTO `tmpprofile_user` (`id`, `uid`, `dbname`, `dbname_display`, `DBwebsite`, `dbLO`, `url`, `hostAdd`, `marcformat`, `expireDate`, `startDate`, `lastRenewal`, 
                                            `dbDescription`, `ipRangeB`, `ipRangeE`, `usertype`, `defaultDB`, `db_uid`, `db_pwd`, `db_profile`, `db_recFormat`, `default_subDB`)
                 VALUES (NULL,?,'Union','SCOOLS union ILL','http://www.scools.org/',5,'http://kbn.cerfinfo.com/inc/asp/queryxmlmarc.asp','scools.org:210/sco','xml','2012-09-30',NULL,NULL,NULL,NULL,NULL,'data entry','false',NULL,NULL,NULL,'usmarc','false') ";

my $sth1 = $dbh->prepare($query1);
   $sth1->execute( $uid);

my $sth2 = $dbh->prepare($query2);
   $sth2->execute( $uid);

my $sth3 = $dbh->prepare($query3);
   $sth3->execute( $uid);

my $sth4 = $dbh->prepare($query4);
   $sth4->execute( $uid);

my $sth5 = $dbh->prepare($query5);
   $sth5->execute( $uid);

my $sth6 = $dbh->prepare($query6);
   my $hostAdd=  substr($schoolURL, 7).':210/';
   if($schoolName eq 'Stamford Central School'){
        $hostAdd .= 'onc_st';
   }
   elsif($schoolName eq 'Gilboa Conesville Central School'){
        $hostAdd .= 'onc_gc';
   }
   elsif($schoolName eq 'Roxbury Central School'){
        $hostAdd .= 'onc_ro';
   }
   elsif($schoolName eq 'Charlotte Valley Central School'){
        $hostAdd .= 'onc_cr';
   }
   elsif($schoolName eq 'Andes Central School'){
        $hostAdd .= 'onc_an';
   }
   elsif($schoolName eq 'Cherry Valley-Springfield School'){
        $hostAdd .= 'onc_cv';
   }
   elsif($schoolName eq 'Cooperstown Elementary School'){
        $hostAdd .= 'onc_ce';
   }
   elsif($schoolName eq 'Cooperstown Mid/High School'){
        $hostAdd .= 'onc_ch';
   }
   elsif($schoolName eq 'Edmeston Central School'){
        $hostAdd .= 'onc_ed';
   }
   elsif($schoolName eq 'Laurens Central School'){
        $hostAdd .= 'onc_la';
   }
   elsif($schoolName eq 'Margaretville Central School'){
        $hostAdd .= 'onc_ma';
   }
   elsif($schoolName eq 'Milford Central School'){
        $hostAdd .= 'onc_mi';
   }
   elsif($schoolName eq 'Oneonta High School'){
        $hostAdd .= 'onc_oh';
   }
   elsif($schoolName eq 'Greater Plains Elem'){
        $hostAdd .= 'onc_og';
   }
   elsif($schoolName eq 'Valleyview Elementary School'){
        $hostAdd .= 'onc_ov';
   }
   elsif($schoolName eq 'Riverside Elementary School'){
        $hostAdd .= 'onc_or';
   }
   elsif($schoolName eq 'Oneonta Middle School'){
        $hostAdd .= 'onc_om';
   }
   elsif($schoolName eq 'Center St. Elementary School'){
        $hostAdd .= 'onc_oc';
   }
   elsif($schoolName eq 'Worcester Central School'){
        $hostAdd .= 'onc_wo';
   }
   elsif($schoolName eq 'Jefferson Central school'){
        $hostAdd .= 'onc_je';
   }
   elsif($schoolName eq 'Schenevus Central School'){
        $hostAdd .= 'onc_sc';
   }
   elsif($schoolName eq 'South Kortright Central School'){
        $hostAdd .= 'onc_sk';
   }
   elsif($schoolName eq 'Tannersville HS School'){
        $hostAdd .= 'onc_th';
   }
   elsif($schoolName eq 'Hunter Elementary School'){
        $hostAdd .= 'onc_he';
   }
   elsif($schoolName eq 'Morris Central School'){
        $hostAdd .= 'onc_mo';
   }
   elsif($schoolName eq 'Windham Ashland Jewett School'){
        $hostAdd .= 'onc_wa';
   }
   elsif($schoolName eq 'ONC BOCES SLS - Union'){
        $hostAdd .= 'onc';
   }
   elsif($schoolName eq 'Northern Catskills Occ. Ctr'){
        $hostAdd .= 'onc_ncoc';
   }
   elsif($schoolName eq 'Special Ed. Training Ressources Center'){
        $hostAdd .= 'onc_setrc';
   }
   elsif($schoolName eq 'Otsego Area Occupational Center'){
        $hostAdd .= 'onc_oaoc';
   }
   elsif($schoolName eq "St Mary's School"){
        $hostAdd .= 'onc_os';
   }
 
   $sth6->execute( $uid, $schoolName,$schoolURL,$hostAdd);

my $sth7 = $dbh->prepare($query7);
   $sth7->execute( $uid);


 $sth1->finish;
 $sth2->finish;
 $sth3->finish;
 $sth4->finish;
 $sth5->finish;
 $sth6->finish;
 $sth7->finish;
}

#////////////////////////////////////////////////////////////////////////////