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

for i in `ls /etc/opals/conf/`; do cp -p /www/opals/script/convertPatron /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/opl_ha");

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 $sth_pers = $dbh->prepare(<<_STH_);
insert into  opl_personInfo
set   
  sid       =?,  
  title     =?,
  firstname =?,
  middlename=?,
  lastname  =?,
  nickname  =?,
  fullname  =?,
  birthday  =?,
  gender    =?,
  created   =?,
  modified  =?,
  issueBy   =?,
  userbarcode   =?,
  username      =?,
  password      =?,
  categorycode  =?,
  permissions   =?,
  pw_timestamp  =?,
  membershipExipry    =?,
  biometricLnk  =?,
  digitalSign   =?,
  email         =?,
  phone     =?,
  workPhone     =?,
  cellPhone     =?,
  fax           =?,
  addrLine1     =?,
  addrLine2     =?,
  city          =?,
  zip           =?,
  state         =?,    
  country       =?,
  neighborhood  =?,
  residency     =?, 
  addrLine1_alt=?,  
  addrLine2_alt=?,     
  city_alt        =?,       
  zip_alt         =?,        
  state_alt       =?,      
  country_alt     =?,    
  neighborhood_alt=?,
  residency_alt   =?,
  addrExpiry_alt  =?,
  status        =? ,
  buildingcode  =?,
  homeroom      =?,
  teacher       =?,
  grade         =?,
  yeargraduation=?,
  notes         =?,
  pref_lang     =?,
  pref_contact  =?,
  identification=?,
  sysCode       =?,
  libCode       =?,
  program       =?,
  studies       =?,
  permanent     =?,           
  fulltime      =?,
  incomplete    =?   
_STH_
 


 my $sth_famRel = $dbh->prepare(<<_STH_);
insert into  opl_userRelation
set     
  uid  =?,
  relUid     =?,
  relType   =?
_STH_


my $sth = $dbh->prepare(<<_SQL_);
select  * 
from opl_user 
order by uid 
_SQL_



my $sth_guar = $dbh->prepare(<<_STH_);    
select  *
from    opl_guardian
where   uid = ?
_STH_

    $sth->execute;    
    while (my $u = $sth->fetchrow_hashref) {
       print "$u->{'uid'}\n";
       my $contType = 'phone';
            if (defined $u->{'email'} && $u->{'email'} ne ''){
               $contType = 'email';
            }

       $sth_pers->execute($u->{'sid'},$u->{'title'},$u->{'firstname'},'',$u->{'lastname'},'', 
                          $u->{'firstname'} . ' ' . $u->{'lastname'},
                          $u->{'birthday'},$u->{'gender'},$u->{'created'},$u->{'modified'},'',
                          $u->{'userbarcode'},$u->{'username'},$u->{'password'},
                          $u->{'categorycode'},$u->{'permissions'},$u->{'pw_timestamp'},$u->{'expirydate'},
                          '','',$u->{'email'},$u->{'phone'},'',$u->{'cellPhone'},$u->{'fax'},
                          $u->{'addrLine1'},$u->{'addrLine2'},$u->{'city'},$u->{'zip'},$u->{'state'},'US','','',
                          '','','','','','','','','',
                          $u->{'status'},$u->{'buildingcode'},$u->{'homeroom'},$u->{'teacher'},
                          $u->{'grade'},$u->{'yeargraduation'},$u->{'notes'},$u->{'pref_lang'},$contType,'',
                          $u->{'sysCode'},$u->{'libCode'},'','','','',$u->{'incomplete'}                       
                          );
=item       
       my $uid=$dbh->{'mysql_insertid'};
    
    
       $sth_guar->execute($u->{'uid'});
       while (my $uGuard = $sth_guar->fetchrow_hashref) {
             my $contType = 'phone';
            if (defined $uGuard->{'gemail'} && $uGuard->{'gemail'} ne ''){
               $contType = 'email';
            }

            $sth_pers->execute($uGuard->{'gtitle'},$uGuard->{'gfirstname'},'',$uGuard->{'glastname'},'',
                               $uGuard->{'gfirstname'} .' ' .$uGuard->{'glastname'},
                              '','',$u->{'created'},$u->{'modified'},'','','','',
                              '','','','','','',
                              $uGuard->{'gemail'},$uGuard->{'gphone'},'',$uGuard->{'gcellphone'},$uGuard->{'gfax'},
                              $uGuard->{'addrLine1'},$uGuard->{'addrLine2'},$uGuard->{'city'},$uGuard->{'zip'},$uGuard->{'state'},'US','','',
                              '','','','','','','','','',
                              $u->{'status'},'','','','','','',$u->{'pref_lang'},$contType , '','','','','','','',$u->{'incomplete'}
                              );                         
           my  $relUid=$dbh->{'mysql_insertid'}; 
        
            $sth_famRel->execute($uid,$relUid,'guardian');        
       }
=cut
        
    }
    $sth->finish;

#////////////////////////////////////////////////////////////////////////////
#
#
#
# TESTING:
#
#