#!/usr/bin/perl 
use lib("/www/opals/module");
use strict;
use DBI;
use Opals::SIF::SifSubscriber;
use Opals::SIF::ObjectDataParser::SchoolInfoParser;
use Opals::SIF::Specs;
use JSON;

#################################################################################
#my @zisList=(
#    {   
#        zone=>{ url=>"http://z.....",
#                agentName=>"OPALS SIF Agent"},
#        sifInfo=>{version=>'2.0r1',
#                 sourceId=>'OpalsSIFAgent',
#                 authLevel=>0,
#                 encryptLevel=>0,
#                 sourceName=>'Subcriber agent',
#                 subscribes=>["studentPersonal","RoomInfo","StudentSchoolEnrollment","StaffPersonal","SchoolInfo"]},
#        sites=>[{siteCode=>"opl_ha",schoolLocalId=>"2"}]        
#                
#     }
##################################################################################



my @zisList=();
my $sifsrvCfg=loadSifsrvCfg("/etc/opals/SIF.conf");
my $pendingStaffReq={};

#------------------------------------------------------------------------------
while(1){
    load_ZIS();
    #print to_json({zisList=>\@zisList},{pretty=>1});
    foreach my $zis(@zisList){
        if(!defined $zis->{'agent'}){
            $zis->{'agent'}=Opals::SIF::SifSubscriber->new($zis->{'sifInfo'},$zis->{'zone'});
            $zis->{'agent'}->init();
        }
        print "checking message from:",$zis->{'zone'}->{'url'},"\n";
        my $rs =$zis->{'agent'}->getMessage();
        #print to_json($rs,{pretty=>1}) ,"\n";
        my $data=[];
        while($rs->{'status'} eq '0'){
              if(defined $rs->{'eventObj'}){
                  push @$data,$rs->{'eventObj'};
              }
              elsif(defined $rs->{'responseObjList'}){
                  foreach my $o (@{$rs->{'responseObjList'}}){
                      $o->{'action'}='add';
                       push @$data,$o;
                  }
              }
             processData($zis,$data);
             $rs =$zis->{'agent'}->getMessage();
        }
        sleep $sifsrvCfg->{'synFreq'};
    }
}


#------------------------------------------------------------------------------
sub processData{
    my ($zis,$eventObjList)=@_;
    my $data={};
    my $i=1;
    foreach my $event(@$eventObjList){
        my $siteCode= getSiteCode($zis,$event);
        if(defined $siteCode){
            $data->{$siteCode}=[] if(!$data->{$siteCode});
            push @{$data->{$siteCode}},$event;
        }
        elsif($event->{'objectType'} eq 'StaffPersonal'){
            #case StaffPersonal, it has no schoolInfoRefId, so update all schools within this ZIS
            foreach my $site(@{$zis->{'sites'}}){
                my $siteCode=$site->{'siteCode'};
                $data->{$siteCode}=[] if(!$data->{$siteCode});
                push @{$data->{$siteCode}},$event;
            }
        }
    }
    foreach my $site(keys %$data){
        my $dbh=getDbh($site);
        my($schName)=$dbh->selectrow_array("select schoolName from SIF_schoolInfo limit 1");
        print "update SchoolInfo $schName ($site) \n";
        my $userType=$dbh->selectrow_array("select val from opl_preference where var='defCategory'");
        $userType='' if !defined($userType);
        foreach my $event(@{$data->{$site}}){
            logEvent($event);
            if($event->{'objectType'} eq 'StudentPersonal'){
                updateStudent($dbh,$event,$userType);
            }
            elsif($event->{'objectType'} eq 'RoomInfo'){
                updateRoomInfo($dbh,$event);
            }
            elsif($event->{'objectType'} eq 'StaffPersonal'){
                updateStaff($dbh,$event)
            }
            elsif($event->{'objectType'} eq 'StudentSchoolEnrollment'){
                updateStudentEnrollment($dbh,$event,$zis);
            }
            elsif($event->{'objectType'} eq 'SchoolInfo'){
                updateSchoolInfo($dbh,$event);
                my $action=uc($event->{'action'});
                # update/sync roomInfo, student enrollment once there is change or  new school info. 
                if($action eq 'ADD' || $action eq 'CHANGE'){
                    my $school=$event->{'objectData'};
                    my $refId=$school->{'refId'};
                    #requestMsg($zis,"StudentSchoolEnrollment",{xPath=>"\@SchoolInfoRefId",val=>$refId,operator=>'EQ'});
                    #requestMsg($zis,"RoomInfo",{xPath=>"\@SchoolInfoRefId",val=>$refId,operator=>'EQ'});
                 }

            }
        }
        $dbh->disconnect;
    }
    
}
#------------------------------------------------------------------------------
sub requestMsg{
    my($zis,$objName,$queryCond)=@_;
    if($zis->{'agent'}->hasAccess('SIF_RequestAccess',$objName)){
        $zis->{'agent'}->requestMessage($objName,$queryCond);
    }
}
#------------------------------------------------------------------------------
sub logEvent{
    my($event)=@_;
    printf  "%s %s %s %s\n", getTimeStamp(), uc($event->{'action'}),
           $event->{'objectType'}, 
           to_json($event->{'objectData'});
}
#------------------------------------------------------------------------------

sub getSiteCode{
    my ($zis,$event)=@_;

    my @sites=@{$zis->{'sites'}};
    my $code=undef;
    my $objData=$event->{"objectData"};
    my $schoolLocalId=undef;
    my $schoolRefId==undef;
    #print "getSiteCode :" , $event->{'objectType'} ,"\n" ;
    if($event->{'objectType'} eq 'SchoolInfo'){
        $schoolRefId=$objData->{'refId'};
        $schoolLocalId=$objData->{'localId'};
    }
    elsif($event->{'objectType'} eq 'StudentPersonal'){
        $schoolLocalId=$objData->{'schoolLocalId'};
    }
    else{
        $schoolRefId=$objData->{'schoolInfoRefId'};
    }
    if(scalar(@sites)==1 && !defined $sites[0]->{'schoolLocalId'}){
        $code=$sites[0]->{'siteCode'};
    }

    elsif(defined $schoolLocalId || defined $schoolRefId ){
        foreach my $s (@sites){
            if(defined $s->{'schoolLocalId'} && $s->{'schoolLocalId'} eq $schoolLocalId ){

                $code=$s->{'siteCode'};
                $s->{'schoolRefId'} =$schoolRefId if(defined $schoolRefId);
                last;
            }
            elsif(defined $s->{'schoolRefId'} && $s->{'schoolRefId'} eq $schoolRefId ){
                $code=$s->{'siteCode'};
            }
        }
    }
    return $code;
}


#------------------------------------------------------------------------------
sub updateStudent{
    my ($dbh,$event,$userCategory)=@_;
    my $action=uc($event->{'action'});
    my $student=$event->{'objectData'};
    my $fMap=getSIF_DB_fieldMap();
    my $refId=$student->{'refId'};
    my $userbarcode=$student->{'localId'};
    my @fields=();
    my @data=();
    if($action eq'ADD'){
        push @fields,"status=? ";
        push @data,"1";
        $student->{'sid'}=$student->{'localId'};
    }
    my ($homeroom,$building)=getHomeroomBldg($dbh,$refId);
    my $teacher=getTeacher($dbh,$refId);
    $student->{'teacher'}  =$teacher if(defined $teacher && $teacher ne'');
    $student->{'homeroom'} =$homeroom if(defined $homeroom && $homeroom ne'');
    $student->{'building'} =$building if(defined $building && $building ne'');
    # sometime, the data does not contain the onTimeGraduationYear field. take projectedGraduationYear instead
    if((!defined $student->{'onTimeGraduationYear'} || $student->{'onTimeGraduationYear'} eq '') 
       &&  $student->{'projectedGraduationYear'} ne ''){
        $student->{'onTimeGraduationYear'}= $student->{'projectedGraduationYear'} ;
    }
    while(my ($f,$v)=each %$fMap){
        if(defined $student->{$f}){
            if($f eq 'gender'){
                $student->{$f}=($student->{$f} eq 'M' || $student->{$f} eq '1')?1:0;
            }
            push @data,$student->{$f};
            push @fields ,"$v=? ";
        }
    }

   


    my ($uid)=$dbh->selectrow_array("select uid from opl_user where SIF_refId=?",undef,$refId);
    if(!defined $uid && defined $userbarcode && $userbarcode ne ""){
        ($uid)=$dbh->selectrow_array("select uid from opl_user where userbarcode=?",undef,$userbarcode);
    }
    if(defined $uid && $uid>0){
        my $iFieldStr=join(",",@fields);
        if($action eq 'CHANGE' || $action eq 'ADD'){
            $dbh->do("update opl_user set modified=now(),$iFieldStr where uid= $uid",undef,@data);
        }
        elsif($action eq 'DELETE'){
            delStudent($dbh,$uid);
        }
    }
    else{
        push @fields,'categoryCode=?';
        push @data,$userCategory;
        my $iFieldStr=join(",",@fields);
        $dbh->do("insert into opl_user set created=now(),modified=now(),$iFieldStr",undef,@data);
    }
    
}

#------------------------------------------------------------------------------
sub delStudent{
    my($dbh,$uid)=@_;
    my ($site)=$dbh->selectrow_array("select database()");
    my $OPALS_CONF_DIR = '/etc/opals/conf/';
    my ($opals_conf, $vhost_conf, $opals_root, $script);
        $opals_conf = $OPALS_CONF_DIR . $site;
        $vhost_conf = 
            `grep -R "$opals_conf\$" /etc/httpd/conf.d/opals | cut -d: -f1`;
       chomp $vhost_conf;

        # OPALS root
        $opals_root = '/' .
            `cat $vhost_conf | sed -re 's/#.+\$//' | grep OPALS_ROOT | cut -d/ -f2,3`;
        chomp $opals_root;

    
    $script = $opals_root . '/script/cron/exec_delUser';
    $ENV{'PERL5LIB'} = $opals_root . '/module';
    $ENV{'OPALS_CONF'} = $opals_conf;
    $ENV{'DEL_UID'} = $uid;
    system($script);
    
}
#------------------------------------------------------------------------------
sub getSIF_DB_fieldMap{
    my ($dbh)=@_;
  my $fMap={
       refId                     =>"SIF_refId",
       localId                   =>"userbarcode",
       sid                       =>"sid",
       refId                     =>"SIF_refId",
       firstname                 =>"firstname",
       lastname                  =>"lastname",
       preferredName             =>"nickname",
       middlename                =>"middlename",
       fullname                  =>"fullname",
       gender                    =>"gender",
       birthDate                 =>"birthday",
       addressLine1              =>"addrLine1",
       addressLine2              =>"addrLine2",
       city                      =>"city",
       state                     =>"state",
       country                   =>"country",
       zipCode                   =>"zip",
       phone                     =>"phone",
       email                     =>"email",
       onTimeGraduationYear     =>"yeargraduation",
       homeroom                 =>"homeroom",
       gradeLevel               =>"grade",
       homeroom                 =>"homeroom",
       building                 =>"buildingCode",
       teacher                  =>"teacher"
    };

    return $fMap;
}
#------------------------------------------------------------------------------
sub updateSchoolInfo{
    my ($dbh,$event)=@_;
  my ($dbh,$event)=@_;
    my $fMap={
            localId                 =>"localId",
            refId                   =>"refId",
            stateProvinceId         =>"stateProvinceId",
            NCESId                  =>"NCESId",
            schoolName              =>"schoolName",
            schoolURL               =>"schoolURL",
            principalName           =>"principalName"

            
    };
    my $action=uc($event->{'action'});
    my $school=$event->{'objectData'};
    my $refId=$school->{'refId'};
    my ($id)=$dbh->selectrow_array("select id from SIF_schoolInfo where refId=?",undef,$refId);
    my @fields=();
    my @data=();
    while(my ($f,$v)=each %$fMap){
        if(defined $school->{$f}){
            push @data,$school->{$f};
            push @fields ,$v ."=? ";
        }
    }
    my $iFieldStr=join(",",@fields);
    if(defined $id && $id>0){
        if($action eq 'CHANGE' || $action eq 'ADD'){
            $dbh->do("update SIF_schoolInfo set $iFieldStr where id= $id",undef,@data);
        }
        elsif($action eq 'DELETE'){
            $dbh->do("delete from SIF_schoolInfo where id=?",undef,$id);
        }
    }
    else{
        $dbh->do("insert into SIF_schoolInfo set $iFieldStr",undef,@data);
    }
    
}
#------------------------------------------------------------------------------
sub updateStaff{
    my ($dbh,$event)=@_;
    my $fMap={
            localId                 =>"localId",
            refId                   =>"refId",
            employeePersonalRefId   =>"employeePersonalRefId",
            barcode                 =>"barcode",
            firstname               =>"firstname",
            lastname                =>"lastname",
            middlename              =>"middlename",
            preferredName           =>"preferredName",
            gender                  =>"gender",
            email                   =>"email",
            addressLine1            =>"addressLine1",
            addressLine2            =>"addressLine2",
            city                    =>"city",
            state                   =>"state",
            country                 =>"country",
            zipCode                 =>"zipCode"
    };
    my $action=uc($event->{'action'});
    my $staff=$event->{'objectData'};
    my $refId=$staff->{'refId'};
    my ($id)=$dbh->selectrow_array("select id from SIF_staffPersonal where refId=?",undef,$refId);


    my @fields=();
    my @data=();
    while(my ($f,$v)=each %$fMap){
        if(defined $staff->{$f}){
            if($f eq 'gender'){
                $staff->{$f}=($staff->{$f} eq 'M')?1:0;
            }
            push @data,$staff->{$f};
            push @fields ,$v ."=? ";
        }
    }
    my $iFieldStr=join(",",@fields);
    if(defined $id && $id>0){
        if($action eq 'CHANGE' || $action eq 'ADD'){
            $dbh->do("update SIF_staffPersonal set $iFieldStr where id= $id",undef,@data);
        }
        elsif($action eq 'DELETE'){
            $dbh->do("delete from SIF_staffPersonal where id=?",undef,$id);
            $dbh->do("update opl_user u inner join SIF_studentEnrollment e on u.SIF_refId=e.studentRefId 
                      set teacher='' where e.advisorRefId=?",undef,$refId);
        }
    }
    else{
        $dbh->do("insert into SIF_staffPersonal set $iFieldStr",undef,@data);
    }
    if($action eq 'CHANGE' || $action eq 'ADD'){
        $pendingStaffReq->{$refId}=0;
        updateStudentTeacher($dbh,$refId);
    }

}

#------------------------------------------------------------------------------
sub updateRoomInfo{
    my ($dbh,$event)=@_;
    my $room=$event->{'objectData'};
    my $refId           =$room->{'refId'};
    my $fMap={
            refId            =>"refId",
            schoolInfoRefId  =>"schoolInfoRefId",
            staffList        =>"staffList",
            description      =>"description",
            building         =>"building",
            homeroomNumber   =>"homeroomNumber",
            roomNumber       =>"roomNumber",
            size             =>"size",
            capacity         =>"capacity",
            phone            =>"phone"

     };


    my $action=uc($event->{'action'});
    my @fields=();
    my @data=();
    while(my ($f,$v)=each %$fMap){
        if(defined $room->{$f}){
            push @data,$room->{$f};
            push @fields ,$v ."=? ";
        }
    }
    my $iFieldStr=join(",",@fields);
    my ($id)=$dbh->selectrow_array("select id from SIF_roomInfo where refId=?",undef,$refId);
    if(defined $id && $id>0){
        if($action eq 'CHANGE' || $action eq 'ADD'){
            $dbh->do("update SIF_roomInfo set $iFieldStr where id= $id",undef,@data);
        }
        elsif($action eq 'DELETE'){
            $dbh->do("delete from SIF_roomInfo where id=?",undef,$id);
            $dbh->do("update opl_user u inner join SIF_studentEnrollment e on u.SIF_refId=e.studentRefId
                      set homeroom='' ,buildingCode='' where e.homeRoomRefId=?",undef,$refId);
        }
    }
    else{
        $dbh->do("insert into SIF_roomInfo set $iFieldStr",undef,@data);
    }
    if($action eq 'CHANGE' || $action eq 'ADD'){
         updateStudentHomeroomBldg($dbh,$refId);
    }
        
}

#------------------------------------------------------------------------------
sub updateStudentEnrollment{
    my ($dbh,$event,$zis)=@_;
    
    my $action=uc($event->{'action'});
    my $objData=$event->{'objectData'};
    my $refId=$objData->{'refId'};

    my $studentPersonalRefId=$objData->{'studentPersonalRefId'};
    my $fMap={
            refId            =>"refId",
            studentPersonalRefId =>"studentRefId",
            schoolInfoRefId  =>"schoolInfoRefId",
            advisorRefId     =>"advisorRefId",
            homeRoomRefId    =>"homeRoomRefId",
            counselorRefId   =>"counselorRefId"

     };
    my @fields=();
    my @data=();
    while(my ($f,$v)=each %$fMap){
        if(defined $objData->{$f}){
            push @data,$objData->{$f};
            push @fields ,$v ."=? ";
        }
    }
    my $iFieldStr=join(",",@fields);
    my ($id)=$dbh->selectrow_array("select id from SIF_studentEnrollment where refId=?",undef,$refId);
    if(defined $id && $id>0){
        if($action eq 'CHANGE' || $action eq 'ADD'){
            $dbh->do("update SIF_studentEnrollment set $iFieldStr where id= $id",undef,@data);
        }
        elsif($action eq 'DELETE'){
            $dbh->do("delete from SIF_studentEnrollment where refId=?",undef,$refId);
            $dbh->do("update opl_user u inner join SIF_studentEnrollment e on u.SIF_refId=e.studentRefId
                      set homeroom='' ,buildingCode='' ,teacher='' where e.refId=?",undef,$refId);
        }
    }
    else{
        $dbh->do("insert into SIF_studentEnrollment set $iFieldStr",undef,@data);
    }
    #update student teacher .
    if($action ne 'DELETE'){
        if(defined $objData->{'advisorRefId'}){
            my $staffRefId=$objData->{'advisorRefId'};
            my $teacher = getTeacherByRefId($staffRefId);
            if(defined $teacher){
                updateStudentTeacher($dbh,$staffRefId);
            }
            elsif(!defined $pendingStaffReq->{$staffRefId}){
                $pendingStaffReq->{$staffRefId}=1;
                requestMsg($zis,"StaffPersonal",{xPath=>"\@RefId",val=>$staffRefId,operator=>'EQ'});

            }
        }
        #update student roominfo .
        if(defined $objData->{'homeRoomRefId'}){
            updateStudentHomeroomBldg($dbh,$objData->{'homeRoomRefId'});
        }
    }

        
}
#------------------------------------------------------------------------------
sub getTeacherByRefId{
    my($dbh,$refId)=@_;
    my $teacher = $dbh->selectrow_hashref("select * from SIF_staffPersonal where refId=?",undef,$refId );
    return $teacher;
}
#------------------------------------------------------------------------------
sub getHomeroomBldg{
    my ($dbh,$stdRefId)=@_;
    my $sql=<<_SQL_;
    select r.homeroomNumber homeroom,r.building 
    from  SIF_studentEnrollment e inner join SIF_roomInfo r on e.homeRoomRefId=r.refId 
    where e.studentRefId= ?   
_SQL_
    my ($homeroom,$building)=$dbh->selectrow_array($sql,undef,$stdRefId);
    return ($homeroom,$building);
}
#------------------------------------------------------------------------------
sub getTeacher{
    my ($dbh,$stdRefId)=@_;
    my $sql=<<_SQL_;
    select trim(concat(s.firstname, " ",s.lastname)) teacher
    from  SIF_studentEnrollment e inner join SIF_staffPersonal s on e.advisorRefId = s.refId 
    where e.studentRefId= ?   
_SQL_
    my ($teacher)=$dbh->selectrow_array($sql,undef,$stdRefId);
    return $teacher;
}


#------------------------------------------------------------------------------
sub updateStudentTeacher{
    my ($dbh,$staffRefId)=@_;
    my $sql=<<_SQL_;
    update opl_user u inner join SIF_studentEnrollment e on u.SIF_refId=e.studentRefId 
            inner join SIF_staffPersonal s on s.refId=e.advisorRefId
    set u.teacher=trim(concat(s.firstname,' ',s.lastname)) where e.advisorRefId=?
_SQL_
  $dbh->do($sql,undef,$staffRefId);
}
#------------------------------------------------------------------------------
sub updateStudentHomeroomBldg{
    my ($dbh,$roomRefId)=@_;
    my $sql = <<_SQL_;
    update opl_user u inner join SIF_studentEnrollment e on u.SIF_refId=e.studentRefId 
            inner join SIF_roomInfo r on r.refId=e.homeRoomRefId 
      set u.homeroom=if(r.homeroomNumber<>'',r.homeroomNumber,u.homeroom), 
          u.buildingCode=if(r.building<>'',r.building,u.buildingCode) 
      where r.refId=?
_SQL_

    $dbh->do($sql,undef,$roomRefId);
}

#------------------------------------------------------------------------------

sub load_ZIS {
    my $OPALS_CONF_DIR = '/etc/opals/conf/';
        open SITECODE, "ls $OPALS_CONF_DIR | ";
        my @siteList;
        while (<SITECODE>) {
            chomp;
            my $code = $_;

            my $site;
            my $config = loadConfig("/etc/opals/conf/$code");
            if (!$config || $config->{'type'} eq 'union') {
                next;
            }
            push @siteList, $code;
        }
        close SITECODE;

        return if (scalar(@siteList) == 0);

        my ($opals_conf, $vhost_conf, $opals_root, $script);

        foreach my $s (@siteList) {
                getSIfConfig($s);
        }
    
}
#------------------------------------------------------------------------------
sub getSIfConfig{
    my ($site)=@_;
    my $dbh=getDbh($site);
    my($SIF_zis_url,$SIF_version,$SIF_sourceId,$SIF_schoolLocalId)=(undef,undef,undef,undef);
    my ($SIF_enable)=$dbh->selectrow_array("select val from opl_preference where var='SIF_enable'");
       ($SIF_zis_url)=$dbh->selectrow_array("select val from opl_preference where var='SIF_zis_url'");
    if(defined $SIF_enable && $SIF_enable eq '1'){
        ($SIF_version)=$dbh->selectrow_array("select val from opl_preference where var='SIF_version'");
        ($SIF_sourceId)=$dbh->selectrow_array("select val from opl_preference where var='SIF_sourceId'");
        ($SIF_schoolLocalId)=$dbh->selectrow_array("select val from opl_preference where var='SIF_schoolLocalId'");

        my $found=0;
        if(defined $SIF_zis_url && $SIF_zis_url ne '' ){
            my $s={siteCode=>$site};
            $s->{'schoolLocalId'}=$SIF_schoolLocalId if($SIF_schoolLocalId ne '');
            foreach my $z (@zisList){
                if($z->{'zone'}->{'url'} eq $SIF_zis_url && $z->{'sifInfo'}->{'sourceId'} eq $SIF_sourceId){
                    foreach my $e(@{$z->{'sites'}}){
                        if($site eq $e->{'siteCode'}){
                            $found=1;
                            last;
                        }
                    }
                    if(!$found){
                        push @{$z->{'sites'}},$s;
                        $found=1;
                        last;
                    }
                }
            }
            if(!$found){
            my $z={
                zone=>{ url=>$SIF_zis_url,
                    agentName=>"OPALS SIF Agent"},
                sifInfo=>{version=>$SIF_version,
                        sourceId=>$SIF_sourceId,
                        authLevel=>0,
                        encryptLevel=>0,
                        sourceName=>"OPALS SIF Agent",
                        subscribes=>$sifsrvCfg->{'subscription'}},
                sites=>[$s]        
                };
            push @zisList,$z;

            }
        }
    }
    else{
        foreach my $z (@zisList){
            my $arr=[];
            foreach my $e(@{$z->{'sites'}}){
                 if($site ne $e->{'siteCode'}){
                       push @$arr,$e;
                 }
            }
            $z->{'sites'}=$arr;

        }
    }
}


#------------------------------------------------------------------------------
sub getTimeStamp {
 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time); 
    my $ts  =sprintf( "%04d-%02d-%02d %02d:%02d:%02d", $year+1900,$mon+1,$mday,$hour,$min,$sec);
    return $ts;
}
#------------------------------------------------------------------------------
sub loadSifsrvCfg{
   my $sifSrvCfg={
       synFreq =>300,
       subscription=>["StudentPersonal","RoomInfo","StudentSchoolEnrollment","StaffPersonal","SchoolInfo"]
   };
   my $cfg=loadConfig("/etc/opals/SIF.conf");
   if(defined $cfg){
       if(defined $cfg->{'subscription'} && $cfg->{'subscription'} ne ''){
            my $subscription=[];
            foreach my $objName(split(",",$cfg->{'subscription'} )){
                $objName=~ s/^\s+|\s+$//g;
                push @$subscription,$objName;
            }
            $sifSrvCfg->{'subscription'}=$subscription if(scalar(@$subscription)>0)
       }
       if(defined $cfg->{'syncFrequency'} && $cfg->{'syncFrequency'} =~ m/(\d+)/  ){
            my $freq =$1;
            $freq =~ s/^[ 0]+//g;
            if($freq >0){
                $sifSrvCfg->{'synFreq'}=int($freq);

            }
       }
   }
   return $sifSrvCfg;
}
#------------------------------------------------------------------------------
sub loadConfig {
    my ($configFile) = @_;

    if (! -r $configFile) {
        return;
    }
    
    my $config;

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

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

    return $config;
}
#######################################################################

sub getDbh {
    my($siteCode) =@_;
    my $config=loadConfig("/etc/opals/conf/$siteCode");
    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);
    my $dbh = DBI->connect($dsn, $db_user, $db_password);

    my ($TZ) = $dbh->selectrow_array(<<_STH_);
select val
from   opl_preference
where  var='timezone'
_STH_

    if ($TZ) {
        $ENV{'TZ'} = $TZ;
        $dbh->do("set time_zone='$TZ'");
    }

    return $dbh;
#    $g_context->{'dbh'} = DBI->connect($dsn, $db_user, $db_password);
#
#    return $g_context->{'dbh'};
}

#------------------------------------------------------------------------------