#!/usr/bin/perl 
use lib("/www/odev/module");
use strict;
use Date::Calc::Object qw(
    :all
);
use Digest::SHA qw(
    sha1_hex
    sha512_hex
);

use POSIX qw(
    ceil
    floor
);
use XML::Simple;
use Unicode::Normalize;
use LWP::UserAgent;
use HTTP::Request::Common;
use URI::Escape;

use REST::Client;

use MIME::Base64;

use CGI;
use DBI;
use Opals::SIF::SifSubscriber;
use Opals::SIF::ObjectDataParser::SchoolInfoParser;
use Opals::SIF::ObjectDataParser::StaffPersonalParser;
use Opals::SIF::ObjectDataParser::RoomInfoParser;
use Opals::SIF::ObjectDataParser::StudentPersonalParser;
use Opals::SIF::Specs;
use JSON;

#https://middletowncityschools.infinitecampus.org/campus/interchange/mcsd/OPALSCarter
#
my $SIF_Info_1 ={version=>'2.4',
                 sourceId=>'test2',
                 authLevel=>0,
                 encryptLevel=>0,
                 sourceName=>'Subcriber agent',
                 subscribes=>['StudentPersonal']
                 };

my $SIF_Info_3 ={version=>'2.4',
                 sourceId=>'OPALSCarte',
                 authLevel=>0,
                 encryptLevel=>0,
                 sourceName=>'Subcriber agent',
                 subscribes=>['StudentPersonal']
                 };


my $zone_1={url=>"http://openzis.dione.scoolaid.net/zis/test1",
          agentName=>"Subcriber agent" ,
          #username=>'testing',
          #password=>'testing'
          };
my $SIF_Info_2 ={version=>'2.0r1',
                 sourceId=>'OpalsSIFAgent',
                 authLevel=>0,
                 encryptLevel=>0,
                 sourceName=>'Subcriber agent',
                 subscribes=>["SchoolInfo"]
                 };


my $zone_2={url=>"http://zis.wnyric.org/Zis5-Service/Receive.aspx?ZoneSourceId=PI_NY043501",
          agentName=>"Subcriber agent" ,
          #username=>'testing',
          #password=>'testing'
          };


my $zone_3={url=>"https://middletowncityschools.infinitecampus.org/campus/interchange/mcsd/OPALSCarte",
          agentName=>"Subcriber agent" ,
          };


my $sifMsgFatory= Opals::SIF::SIF_MsgFactory->new();
my $msg= $sifMsgFatory->createMessage($SIF_Info_1,"SIF_Request",{objectName=>"StudentPersonal",
                                                                cond=>{xPath=>"MostRecent/SchoolLocalId",
                                                                val=>2,
                                                                operator=>'EQ'}});
#print $msg->getXml();
my $subscriber =Opals::SIF::SifSubscriber->new($SIF_Info_3,$zone_3,\&processEvent);
#my $subscriber =Opals::SIF::SifSubscriber->new($SIF_Info_1,$zone_1,\&processEvent);
#my $subscriber =Opals::SIF::SifSubscriber->new($SIF_Info_2,$zone_2,\&processEvent);
#$subscriber->init();
testParseStudentPersonalObj();
#testParseSchoolInfoObj();
#testParseStaffPersonalObj();
#testParserRoomInfoObj();
#testupdateStaffPersonal();
#testupdateRoomInfo();

#testRoomInfoRequest();
#testStaffPersonalRequest();

exit 0;
#testSubcriberHandlingMsg($SIF_Info_1,$zone_1,\&processEvent);
#$subscriber->requestMessage("SchoolInfo");
$subscriber->requestMessage("StudentPersonal",{xPath=>"MostRecent/SchoolLocalId",
                                                val=>2,
                                                operator=>'EQ'});
#testGetMsg($SIF_Info_1,$zone_1);
#testupdateStudent();
#
while(1){
    print "Check for new messages\n";
    $subscriber->getMessage();
    sleep 30;
}

exit 0;





my $reqMsg=$sifMsgFatory->createMessage($SIF_Info_1,"SIF_Request","SchoolInfo");
print $reqMsg->getXml()," \n";
exit 0;

my $subscriber =Opals::SIF::SifSubscriber->new($SIF_Info_1,$zone_1,\&processEvent);
$subscriber->init();
#sleep 3;
#$subscriber->unRegister($zone);
#exit 0;
#$subscriber->requestMessage();
while(1){
    print "Check for new messages\n";
    $subscriber->getMessage();
    sleep 1;
}

#$sifAgent->subscribe();
#$sifAgent->unSubscribe();
#$sifAgent->register();
=item
if($sifAgent->isRegistered()){
   print  $sifAgent->hasAccess("SIF_SubscribeAccess","StudentPersonal"),"\n";

}
=cut
exit 0;

sub testRoomInfoRequest(){
    my $subscriber =Opals::SIF::SifSubscriber->new($SIF_Info_2,$zone_2);
    $subscriber->requestMessage("RoomInfo");
    my $dbh =getDbh("opl_ha");;
    while(1){
        print "Check for new messages\n";
        my $rs =$subscriber->getMessage();
        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;
                  }
              }
              foreach my $event(@$data){
                updateRoomInfo($dbh,$event->{'objectData'});
              }
             $rs =$subscriber->getMessage();
        }

        sleep 30;
    }


}
sub testStaffPersonalRequest(){
    my $subscriber =Opals::SIF::SifSubscriber->new($SIF_Info_2,$zone_2);
    $subscriber->requestMessage("StaffPersonal");
    my $dbh =getDbh("opl_ha");;
    while(1){
        print "Testing StaffPersonal :: Check for new messages\n";
        my $rs =$subscriber->getMessage();
        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;
                  }
              }
              foreach my $event(@$data){
                updateStaff($dbh,$event->{'objectData'});
              }
             $rs =$subscriber->getMessage();
        }

        sleep 30;
    }


}

sub testupdateStudent{
    my $dbh =getDbh("opals_beta");;
    my $student=testParseStudentPersonalObj();
    updateStudent($dbh,$student);
    
}
sub testupdateRoomInfo{
    my $dbh =getDbh("opl_ha");;
    my $room=testParserRoomInfoObj();
    updateRoomInfo($dbh,$room);
    
}

sub testupdateStaffPersonal{
    my $dbh =getDbh("opl_ha");;
    my $staff=testParseStaffPersonalObj();
    updateStaff($dbh,$staff);
    
}

sub processEvent{
    my ($objType,$action,$objData)=@_;
    print "Processing $action object $objType\n";
    foreach my $fname(keys %$objData){
        print "\t$fname:" , $objData->{$fname},"\n";
    }
    
}
sub testGetMsg{
    my($SIF_Info,$zone)=@_;
    my $subscriber =Opals::SIF::SifSubscriber->new($SIF_Info,$zone,\&processEvent);
    $subscriber->init();
    my $rs =$subscriber->getMessage();
    print "status:", $rs->{'status'}, "\n";
    #open debug, ">/tmp/studentPersonal_1.xml";
    while($rs->{'status'} eq '0'){
        foreach my $o (@{$rs->{'responseObjList'}}){
            print "$o\n";
            while(my($k,$v)=each %{$o->{'objectData'}}){
                print "$k:$v\n" ;
            }
        }
       # print to_json($obj,{pretty=>1});
        $rs =$subscriber->getMessage();
        print "status:", $rs->{'status'}, "\n";
    }

}

sub test_register{
    my($SIF_Info,$zone)=@_;
    $subscriber->init();
}

sub test_request{
    my($SIF_Info,$zone,$objname)=@_;
    my $subscriber =Opals::SIF::SifSubscriber->new($SIF_Info,$zone);
    $subscriber->requestMessage($objname);
}

sub testSubcriberHandlingMsg{
    my($SIF_Info,$zone)=@_;
    my $subscriber =Opals::SIF::SifSubscriber->new($SIF_Info,$zone,\&processEvent);

    my $msgXml_event=<<_TXT_;
<SIF_Message Version="2.0r1" xmlns="http://www.sifinfo.org/infrastructure/2.x">
  <SIF_Ack>
    <SIF_Header>
      <SIF_MsgId>9861A45AAC364607938A7DB440514DDF</SIF_MsgId>
      <SIF_Timestamp>2006-10-14T15:54:42-08:00</SIF_Timestamp>
      <SIF_SourceId>RamseyZIS</SIF_SourceId>
    </SIF_Header>
    <SIF_OriginalSourceId>RamseySIS</SIF_OriginalSourceId>
    <SIF_OriginalMsgId>B0E80A74265A4A75ADDC0ECC50AEF737</SIF_OriginalMsgId>
    <SIF_Status>
      <SIF_Code>0</SIF_Code>
      <SIF_Data>
        <SIF_Message Version="2.0r1">
          <SIF_Event>
            <SIF_Header>
              <SIF_MsgId>AB34DC093261545A31905937B265CE01</SIF_MsgId>
              <SIF_Timestamp>2006-10-14T15:40:12-08:00</SIF_Timestamp>
              <SIF_SourceId>RamseySIS</SIF_SourceId>
            </SIF_Header>
            <SIF_ObjectData>
              <SIF_EventObject ObjectName="StudentPersonal" Action="Change">
                <StudentPersonal RefId="D3E34B359D75101A8C3D00AA001A1652">
                  <PhoneNumberList>
                    <PhoneNumber Type="0096">
                      <Number>(312) 555-1234</Number>
                    </PhoneNumber>
                  </PhoneNumberList>
                </StudentPersonal>
              </SIF_EventObject>
            </SIF_ObjectData>
          </SIF_Event>
        </SIF_Message>
      </SIF_Data>
    </SIF_Status>
  </SIF_Ack>
</SIF_Message>    
_TXT_
   
   my $msgXml_response=<<_TXT_;
<SIF_Message Version="2.0r1" xmlns="http://www.sifinfo.org/infrastructure/2.x">
  <SIF_Ack>
    <SIF_Header>
      <SIF_MsgId>9861A45AAC364607938A7DB440514DDF</SIF_MsgId>
      <SIF_Timestamp>2006-10-14T15:54:42-08:00</SIF_Timestamp>
      <SIF_SourceId>RamseyZIS</SIF_SourceId>
    </SIF_Header>
    <SIF_OriginalSourceId>RamseySIS</SIF_OriginalSourceId>
    <SIF_OriginalMsgId>B0E80A74265A4A75ADDC0ECC50AEF737</SIF_OriginalMsgId>
    <SIF_Status>
      <SIF_Code>0</SIF_Code>
      <SIF_Data>
        <SIF_Message Version="2.0r1">
              <SIF_Response>
                <SIF_Header>
                  <SIF_MsgId>1BCD10580EF250789012AC0554321EA2</SIF_MsgId>
                  <SIF_Timestamp>2006-02-18T08:39:40-08:00</SIF_Timestamp>
                  <SIF_SourceId>SISAgent</SIF_SourceId>
                  <SIF_DestinationId>NetworkAgent</SIF_DestinationId>
                </SIF_Header>
                <SIF_RequestMsgId>FE1078BA3261545A319059376B3A4898</SIF_RequestMsgId>
                <SIF_PacketNumber>1</SIF_PacketNumber>
                <SIF_MorePackets>No</SIF_MorePackets>
                <SIF_ObjectData>
                  <StudentPersonal RefId="E3E34B359D75101A8C3D00AA00184753">
                    <Name Type="04">
                      <LastName>Johnson</LastName>
                      <FirstName>Alicia</FirstName>
                    </Name>
                  </StudentPersonal>
                </SIF_ObjectData>
              </SIF_Response>                 
        </SIF_Message>
      </SIF_Data>
    </SIF_Status>
  </SIF_Ack>
</SIF_Message>    
_TXT_
        my $xmlObj =XML::XPath->new(xml=>$msgXml_response);
        my ($xml)=$xmlObj->findnodes_as_string(XPATH_GET_MSG);
$subscriber->handleGetMsg($xml);

}
sub testParseStudentPersonalObj{
    my $stdXml=<<_TXT_;
<StudentPersonal RefId="0000085E18DEE0718E4887750000C96A">
    <LocalId>95035225</LocalId>
    <StateProvinceId>4082587042</StateProvinceId>
    <Name Type="04">
       <LastName>Higgins</LastName>
       <FirstName>Theodore</FirstName>
       <MiddleName>M</MiddleName>
       <SortName>Higgins, Theodore M</SortName>
       <FullName>Theodore M Higgins</FullName>
    </Name>
    <Demographics>
       <RaceList>
          <Race>
             <Code>1002</Code>
             <OtherCodeList>
                <OtherCode Codeset="StateProvince">700</OtherCode>
             </OtherCodeList>
          </Race>
       </RaceList>
       <HispanicLatino>No</HispanicLatino>
       <Gender>M</Gender>
       <BirthDate>2005-06-28</BirthDate>
       <PlaceOfBirth>Pasadena</PlaceOfBirth>
       <StateOfBirth>US-CA</StateOfBirth>
       <CountryOfBirth>US</CountryOfBirth>
       <LanguageList>
          <Language>
             <Code>eng</Code>
             <OtherCodeList>
                <OtherCode Codeset="StateProvince">00</OtherCode>
             </OtherCodeList>
             <LanguageType>1037</LanguageType>
          </Language>
       </LanguageList>
       <DwellingArrangement>
          <Code>1674</Code>
       </DwellingArrangement>
    </Demographics>
    <AddressList>
       <Address Type="0765">
          <Street>
             <Line1>1414 Harker Ave </Line1>
             <Line3>Palo Alto, CA 94301-3426</Line3>
             <StreetNumber>1414</StreetNumber>
             <StreetName>Harker</StreetName>
             <StreetType>Ave</StreetType>
          </Street>
          <City>Palo Alto</City>
          <StateProvince>CA</StateProvince>
          <Country>US</Country>
          <PostalCode>94301-3426</PostalCode>
       </Address>
    </AddressList>
    <PhoneNumberList>
       <PhoneNumber Type="0096">
          <Number>(626)253-1299</Number>
       </PhoneNumber>
    </PhoneNumberList>
    <ProjectedGraduationYear>2023</ProjectedGraduationYear>
    <MostRecent>
       <SchoolLocalId>6060065</SchoolLocalId>
    </MostRecent>
    <IDEA>No</IDEA>
    <Migrant>No</Migrant>
    <Title1>No</Title1>
    <GiftedTalented>No</GiftedTalented>
    <EconomicDisadvantage>No</EconomicDisadvantage>
    <ELL>No</ELL>
    <Homeless>No</Homeless>
    <Section504>No</Section504>
    <VocationalConcentrator>No</VocationalConcentrator>
    <Immigrant>No</Immigrant>
    <NeglectedDelinquent>No</NeglectedDelinquent>
    <FirstUSEnrollment>2010-08-30</FirstUSEnrollment>
 </StudentPersonal>

_TXT_

  my $sParser=     Opals::SIF::ObjectDataParser::StudentPersonalParser->new();

  my $std=$sParser->parse($stdXml,"2.4");
  while(my ($f,$v)=each %$std){
      print "$f :>$v<\n";
  }
  return $std;
}

sub testParseStaffPersonalObj{
    my $stdXml=<<_TXT_;
<StaffPersonal RefId="D3E34F419D75101A8C3D00AA001A1652">
  <LocalId>946379881</LocalId>
  <StateProvinceId>C2345681</StateProvinceId>
  <ElectronicIdList>
    <ElectronicId Type="Barcode">206655</ElectronicId>
  </ElectronicIdList>
  <OtherIdList>
    <OtherId Type="0004">333333333</OtherId>
  </OtherIdList>
  <Name Type="04">
    <Prefix>Mr.</Prefix>
    <LastName>Woodall</LastName>
    <FirstName>Charles</FirstName>
    <MiddleName>William</MiddleName>
    <PreferredName>Chuck</PreferredName>
  </Name>
  <Demographics>
    <Gender>M</Gender>
  </Demographics>
  <Title>Principal</Title>
  <AddressList>
    <Address Type="0123">
      <Street>
        <Line1>6799 33rd Ave.</Line1>
        <StreetNumber>6799</StreetNumber>
        <StreetName>33rd</StreetName>
        <StreetType>Ave.</StreetType>
      </Street>
      <City>Chicago</City>
      <StateProvince>IL</StateProvince>
      <Country>US</Country>
      <PostalCode>60660</PostalCode>
    </Address>
  </AddressList>
  <PhoneNumberList>
    <PhoneNumber Type="0096">
      <Number>(312) 555-1234</Number>
    </PhoneNumber>
  </PhoneNumberList>
  <EmailList>
    <Email Type="Primary">chuckw\@imginc.com</Email>
  </EmailList>
  <EmployeePersonalRefId>1652D3E34F419D75101A8C3D00AA001A</EmployeePersonalRefId>
</StaffPersonal>
_TXT_

my $sParser=  Opals::SIF::ObjectDataParser::StaffPersonalParser->new();

  my $std=$sParser->parse($stdXml,"2.4");
  while(my ($f,$v)=each %$std){
      print "$f :$v\n";
  }
  return $std;


}
sub testParserRoomInfoObj{
    my $stdXml=<<_TXT_;
<RoomInfo RefId="D3E34B359D75101A8C3D00AA001A1652" SchoolInfoRefId="A2E35B359D75101A8C3D00AA001A0000">
  <RoomNumber>101</RoomNumber>
  <StaffList>
    <StaffPersonalRefId>A8C3A2E35B359D75101D00AA001A0000</StaffPersonalRefId>
    <StaffPersonalRefId>A8C3A2E35B359D75101D00AA001A0001</StaffPersonalRefId>
  </StaffList>
  <Description>Room 101</Description>
  <Building>Main A</Building>
  <HomeroomNumber>10-HR-A</HomeroomNumber>
  <Size>400</Size>
  <Capacity>35</Capacity>
  <PhoneNumber Type="0096">
    <Number>(312) 555-1234</Number>
  </PhoneNumber>
  <RoomTypeRefId>9D75101A8C3D00AA001A0000A2E35B35</RoomTypeRefId>
</RoomInfo>
_TXT_

my $sParser=  Opals::SIF::ObjectDataParser::RoomInfoParser->new();

  my $std=$sParser->parse($stdXml,"2.4");
  while(my ($f,$v)=each %$std){
      print "$f :$v\n";
  }
  return $std;
}

sub testParseSchoolInfoObj{
    my $stdXml=<<_TXT_;
<SchoolInfo RefId="D3E34B359D75101A8C3D00AA001A1652">
  <LocalId>946379881</LocalId>
  <StateProvinceId>C2345681</StateProvinceId>
  <NCESId>421575003045</NCESId>
  <SchoolName>Lincoln High School</SchoolName>
  <LEAInfoRefId>73648462888624AA5294BC6380173276</LEAInfoRefId>
  <OtherLEA SIF_RefObject="LEAInfo">AA648462888624AA5294BC638017320B</OtherLEA>
  <SchoolFocusList>
    <SchoolFocus>Regular</SchoolFocus>
  </SchoolFocusList>
  <SchoolURL>http://www.lincolnhs.edu</SchoolURL>
  <PrincipalInfo>
    <ContactName>Richard Knudson</ContactName>
    <ContactTitle>Dr.</ContactTitle>
  </PrincipalInfo>
  <SchoolContactList>
    <SchoolContact>
      <PublishInDirectory>Yes</PublishInDirectory>
      <ContactInfo>
        <Name Type="04">
          <LastName>Miller</LastName>
          <FirstName>James</FirstName>
        </Name>
        <PositionTitle>Office Assistant</PositionTitle>
        <EmailList>
          <Email Type="Primary">jmiller\@lhs.k12.state.us</Email>
        </EmailList>
        <PhoneNumberList>
          <PhoneNumber Type="0096">
            <Number>(555) 555-1235</Number>
          </PhoneNumber>
        </PhoneNumberList>
      </ContactInfo>
    </SchoolContact>
  </SchoolContactList>
  <PhoneNumberList>
    <PhoneNumber Type="0096">
      <Number>(555) 555-1234</Number>
    </PhoneNumber>
  </PhoneNumberList>
  <IdentificationInfoList>
    <IdentificationInfo Code="0175">00123</IdentificationInfo>
  </IdentificationInfoList>
  <SessionType>0830</SessionType>
  <GradeLevels>
    <GradeLevel>
      <Code>09</Code>
    </GradeLevel>
    <GradeLevel>
      <Code>10</Code>
    </GradeLevel>
    <GradeLevel>
      <Code>11</Code>
    </GradeLevel>
    <GradeLevel>
      <Code>12</Code>
    </GradeLevel>
  </GradeLevels>
  <Title1Status>Targeted</Title1Status>
  <OperationalStatus>0820</OperationalStatus>
  <CongressionalDistrict>3</CongressionalDistrict>
</SchoolInfo>    
_TXT_

  my $sParser=  Opals::SIF::ObjectDataParser::SchoolInfoParser->new();

  my $std=$sParser->parse($stdXml,"2.4");
  while(my ($f,$v)=each %$std){
      print "$f :$v\n";
  }
}


#------------------------------------------------------------------------------
sub updateStudent{
    my ($dbh,$student)=@_;
    my $fMap=getSIF_DB_fieldMap();
    my $refId=$student->{'refId'};
    my $userbarcode=$student->{'localId'};
    my @fields=();
    my @data=();
    while(my ($f,$v)=each %$fMap){
        if(defined $student->{$f}){
            if($f eq 'gender'){
                $student->{$f}=($student->{$f} eq 'M')?1:0;
            }
            push @data,$student->{$f};
            push @fields ,$v ."=? ";
        }
    }
    my $iFieldStr=join(",",@fields);
    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){
        $dbh->do("update opl_user set $iFieldStr where uid= $uid",undef,@data);
    }
    else{
        $dbh->do("insert into opl_user set $iFieldStr",undef,@data);
    }
    
}
#------------------------------------------------------------------------------
sub updateStaff{
    my ($dbh,$staff)=@_;
    my $refId=$staff->{'refId'};
    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 @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);
    my ($id)=$dbh->selectrow_array("select id from SIF_staffPersonal where refId=?",undef,$refId);
    if(defined $id && $id>0){
        $dbh->do("update SIF_staffPersonal set $iFieldStr where id= $id",undef,@data);
    }
    else{
        $dbh->do("insert into SIF_staffPersonal set $iFieldStr",undef,@data);
    }
    
}

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

     };


    my @fields=();
    my @data=();
    while(my ($f,$v)=each %$fMap){
        if(defined $room->{$f}){
            if($f eq 'staffList'){
                my $staffListStr= join($room->{$f},",");
                $room->{$f}=$staffListStr;
            }
            push @data,$room->{$f};
            push @fields ,$v ."=? ";
        }
    }
    #print join(";",@fields), "\n=======\n";
    #print join(";",@data), "\n=======\n";
    my $iFieldStr=join(",",@fields);
    my ($id)=$dbh->selectrow_array("select id from SIF_roomInfo where refId=?",undef,$refId);
    if(defined $id && $id>0){
        $dbh->do("update SIF_roomInfo set $iFieldStr where id= $id",undef,@data);
    }
    else{
        $dbh->do("insert into SIF_roomInfo set $iFieldStr",undef,@data);
    }
    
}
#------------------------------------------------------------------------------
sub getSIF_DB_fieldMap{
    my ($dbh)=@_;

    my $fMap={
       refId                     =>"SIF_refId",
       localId                   =>"userbarcode",
       refId                     =>"SIF_refId",
       firstname                 =>"firstname",
       lastname                  =>"lastname",
       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"
    };
    return $fMap;
    
}
#------------------------------------------------------------------------------
sub loadConfig {
    my ($siteCode) = @_;

    my $configFile = "/etc/opals/conf/$siteCode";
    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($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'};
}