#!/usr/bin/perl 
use lib("/www/opals/module");

=item
command to update all the sites:

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

=cut

#use Opals::Context('/etc/opals/conf/ztest');
use Opals::Context("/etc/opals/conf/_MY_SITE_");
use strict;
use Opals::Constant; 
use Date::Calc::Object qw(
    :all
);
use Digest::SHA qw(
    sha1_hex
    sha512_hex
);

use POSIX qw(
    ceil
    floor
);
use Opals::CallNumberUtil qw(
    cn_parseLCC
    cn_parseDewey
    cn_getCallNumSortByDewey
    cn_getCallNumSortByLcc
);
use Opals::Search qw(
    srch_getRecType
);
use MARC::Record;
use MARC::Field;
use MARC::File::XML;

use CGI;
use DBI;

my $dbh = Opals::Context->dbh();
my $zRoot   = Opals::Context->config('zRoot');
my $zPort   = Opals::Context->config('zPort');
my $zDatabase = Opals::Context->config('zDatabase');
$dbh->do(<<_SQL_);
ALTER TABLE opl_item drop callNumSort_dewey, drop callNumSort_lcc
_SQL_

$dbh->do(<<_SQL_);
ALTER TABLE opl_item 
ADD callNumSort_dewey varchar(100) DEFAULT '', 
ADD callNumSort_lcc varchar(100) DEFAULT '',
ADD tmpModDate datetime DEFAULT NULL
_SQL_

$dbh->do(<<_SQL_);
UPDATE opl_item set tmpModDate=modDate
_SQL_

$dbh->do(<<_SQL_);
ALTER TABLE opl_marcImport 
ADD `sf856Code` char(1) DEFAULT '', 
ADD `sf856Data` varchar(255) DEFAULT '', 
ADD `type` enum('hardCopy','ebook') DEFAULT 'hardCopy'
_SQL_

$dbh->do(<<_SQL_);
ALTER TABLE opl_marcRecord 
CHANGE recFormat recFormat set('book','ebook','journal','artifact','photoPoster','musicCassette','bookOnTape','movie','libraryKit','map','sheetMusic','internet','electronicMedia','CDMusic','compactDisc') DEFAULT 'book'
_SQL_

$dbh->do(<<_SQL_);
REPLACE INTO opl_prefFormParam(var,listOrder,name,val) 
    VALUES('classificationSystem',0,'Dewey Decimal System','dewey'),
    ('classificationSystem',1,'Library of Congress classification','LCC'),
    ('OpacLoginRequired',0,'No login required','0'),
    ('OpacLoginRequired',1,'Login required','1'),
    ('barcodeType',0,'Sagebrush','4')
_SQL_
$dbh->do(<<_SQL_);
REPLACE INTO opl_preference(var,val,valShow,opt,varType,description,hidden,gid,gOrder) 
VALUES('classificationSystem','dewey', 'Dewey Decimal System','dewey|LCC','select','Dewey Decimal System',0,5,13),
      ('OpacLoginRequired','0','No','0|1','radio','Turn on/off OPAC Access login requirement',0,1,11)
_SQL_

fillCallNumSortFields();
updateRecFortmat();

$dbh->do(<<_SQL_);
UPDATE opl_item set modDate=tmpModDate
_SQL_

$dbh->do(<<_SQL_);
ALTER TABLE opl_item 
DROP tmpModDate
_SQL_

#############################################################################################################
sub fillCallNumSortFields{
    my $sth =$dbh->prepare("select barcode,callNumber from opl_item where callnumber<>'' ");
    my $sth_update=$dbh->prepare("update opl_item set callNumSort_dewey=?,callNumSort_lcc=? where barcode=?");
    $sth->execute();
    while(my($barcode,$callNum)=$sth->fetchrow_array()){
        my ($cnSort_dewey,$cnSort_lcc)=("","");
        $cnSort_dewey=cn_getCallNumSortByDewey($callNum);
        $cnSort_lcc  =cn_getCallNumSortByLcc($callNum);
        $sth_update->execute($cnSort_dewey,$cnSort_lcc,$barcode);
    }
}
#############################################################################################################

sub updateRecFortmat{
    
    my $sth =$dbh->prepare("select distinct rid from opl_item where barcode not regexp '^\_\_\_'");
    my $sth_update=$dbh->prepare("update opl_marcRecord set recFormat=? where rid= ?");
    $sth->execute();
    while(my ($rid) = $sth->fetchrow_array()){
        my $xml =getMarcXml($rid);
        my $marc = Opals::Marc::Record::newFromXml($xml);
        # Media type
        my($leader,$cf006,$cf007,$cf008)=('','','');
        if($marc->leader){
            $leader = $marc->leader;
        }
        if($marc->field('006')){
            $cf006 = $marc->field('006')->data();
        }
        if($marc->field('007')){
            $cf007 = $marc->field('007')->data();
        }
        if($marc->field('008')){
            $cf008 = $marc->field('008')->data();
        }
        my $recFormat =srch_getRecType($leader,$cf006,$cf007,$cf008);
        $sth_update->execute($recFormat,$rid);
        print "$rid:$recFormat\n";
    }

}

sub getMarcXml{
    my ($rid)= @_;
    my $xml="";
    my $dir = "$zRoot/$zPort/record/$zDatabase/" . ceil($rid/1000);
    open REC_DEL, "<$dir/$rid.xml";
    while (<REC_DEL>) {
        $xml .= $_;
    }
    close REC_DEL;

    return $xml;

}