#!/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_restoreTitle /tmp/urt; perl -pi -e "s/_MY_SITE_/$i/" /tmp/urt; sudo /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...

$dbh->do(<<_SQL_);
    DROP TABLE if exists opl_category_tmp
_SQL_

$dbh->do(<<_SQL_);
CREATE TABLE `opl_category_tmp` (
  `catid` int(10) unsigned NOT NULL auto_increment,
  `old_catid` int(10) default 0,
  `cattype` varchar(30) character set utf8 collate utf8_unicode_ci NOT NULL,
  `catname` varchar(30) default NULL,
  `maxloans` int(10) unsigned NOT NULL default '1',
  `maxreserv` int(10) unsigned NOT NULL default '1',
  `required` enum('0','1') character set utf8 collate utf8_unicode_ci NOT NULL default '0',
  `defaultPerm` text character set utf8 collate utf8_unicode_ci,
  PRIMARY KEY  (`catid`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1  
_SQL_

$dbh->do(<<_SQL_);
DROP TABLE  if exists opl_user_tmp 
_SQL_

$dbh->do(<<_SQL_);
CREATE TABLE opl_user_tmp select * from opl_user
_SQL_

$dbh->do(<<_SQL_);
ALTER TABLE opl_user_tmp 
ADD `old_categorycode` int(11) unsigned AFTER `categorycode`  
_SQL_

$dbh->do(<<_SQL_);
DROP TABLE  if exists opl_itemTypeParam_tmp  
_SQL_

$dbh->do(<<_SQL_);
CREATE TABLE opl_itemTypeParam_tmp select * from opl_itemTypeParam
_SQL_

$dbh->do(<<_SQL_);
ALTER TABLE opl_itemTypeParam_tmp 
ADD `old_userTypeId` int(10) unsigned AFTER `userTypeId`
_SQL_

doTbl_opl_category($dbh);
doTbl_opl_user($dbh);
doTbl_opl_itemTypeParam($dbh);

# Codes end.

exit 0;

############################################################
sub doTbl_opl_category{
    my ($dbh)= @_;
    my $sql = "select * from opl_category";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $sql_insert = "insert into opl_category_tmp (catid,old_catid,cattype,catname,maxloans,maxreserv,required,defaultPerm) 
                        VALUES (?,?,?,?,?,?,?,?)";
    my $sth_insert = $dbh->prepare($sql_insert);

    my $sql_insert_else = "insert into opl_category_tmp (old_catid,cattype,catname,maxloans,maxreserv,required,defaultPerm) 
                           VALUES (?,?,?,?,?,?,?)";
    my $sth_insert_else = $dbh->prepare($sql_insert_else);

    my @params =();
    my ($hasStudent,$hasTeacher,$hasStaff) = (0,0,0);
    while (my $c = $sth->fetchrow_hashref()){
        my $catid = $c->{'catid'}; 
        my $catname = $c->{'catname'}; 
        my $maxloans = $c->{'maxloans'};
        my $maxreserv = $c->{'maxreserv'};
        my $required = $c->{'required'};
        my $defaultPerm = $c->{'defaultPerm'};
        print $catname, "\n";
        if (lc($catname) eq 'student'){
            $hasStudent = 1;
            @params = (1,$catid,$catname,$catname,$maxloans,$maxreserv,$required,$defaultPerm);
            $sth_insert->execute(@params);
        }
        elsif (lc($catname) eq 'staff'){
            $hasStaff = 1;
            @params = (2,$catid,$catname,$catname,$maxloans,$maxreserv,$required,$defaultPerm);
            $sth_insert->execute(@params);
        }
        elsif (lc($catname) eq 'teacher'){
            $hasTeacher = 1;
            @params = (3,$catid,$catname,$catname,$maxloans,$maxreserv,$required,$defaultPerm);
            $sth_insert->execute(@params);
        }
        else{
            @params = ($catid,$catname,$catname,$maxloans,$maxreserv,$required,$defaultPerm);
            $sth_insert_else->execute(@params);
        }
    }
    if (! $hasStudent){
        @params = (1,0,'Student','Student',0,0,1,'');
        $sth_insert->execute(@params);
    }
    if (! $hasStaff){
        @params = (2,0,'Staff','Staff',0,0,1,'');
        $sth_insert->execute(@params);
    }
    if (! $hasTeacher){
        @params = (3,0,'Teacher','Teacher',0,0,1,'');
        $sth_insert->execute(@params);
    }
    $sth->finish();
    $sth_insert->finish();
    $sth_insert_else->finish();
}

############################################################
sub doTbl_opl_user{
    my ($dbh) = @_;
    my $sql = "update opl_user_tmp u
                inner join opl_category_tmp c on (u.categorycode = c.old_catid)
                set u.old_categorycode = u.categorycode, u.categorycode = c.catid";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    $sth->finish();
}

sub doTbl_opl_itemTypeParam{
    my ($dbh) = @_;
    my $sql = "update opl_itemTypeParam_tmp t
                inner join opl_category_tmp c on (t.userTypeId = c.old_catid)
                set t.old_userTypeId = t.userTypeId, t.userTypeId = c.catid";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    $sth->finish();

}
#////////////////////////////////////////////////////////////////////////////