<?php

require_once( dirname( __FILE__ ) . '/../../../../config_vars.php' ) ;
require_once 'imp_user_management.php' ;
/*require_once 'iplat_emaileditor.php' ;
	require_once 'iplat_emaileditor_mgt.php' ;
	require_once 'alertapi.php' ;*/

/**
* imp_institution_management - It contains methods for institution management
*
* @ Copyright 2008 Impelsys India Pvt Ltd.
* @ Author Senthil Kumar B
*
* @ Since : 28-Aug-2008
**/
class imp_institution_management_register extends imp_user_management {

    // @ protected $_db : Object : imp_singleton_database database connection
    protected $_db ;


    /**
    * imp_institution_management::__construct()
    * It creates the object of the imp_singleton_database class
    *
    * @ Author Senthil Kumar B
    * @ Since 28-Aug-2008
    **/
    public function __construct() {

        // Creating object for the imp_singleton_database
        $this->_db 		= imp_singleton_database::getInstance() ;
    } //  End of __construct


    /**
    * imp_institution_management::institutionNameAvailability()
    * It checks whether the institution name is available or not
    *
    * @ Param  : $institutionName - String
    * @ Return : Bool
    *
    * @ Author Senthil Kumar B
    * @ Since 28-Aug-2008
    **/
    public function institutionNameAvailability( $institutionName ) {

        if ( !empty( $institutionName ) ) {
            $institutionName 	= $this->_db->getEscaped( $institutionName ) ;
            $sql 	= " SELECT institutionid  FROM iplat_institution_master "
                    . "  WHERE institutionname = '" . $institutionName . "' " ;
            $this->_db->query( $sql ) ;

            if ( $this->_db->numRows > 0 ) {
                return true ;
            } else {
                return false ;
            }
        }
    } // End of institutionNameAvailability
public function getinstitutionNameid() {

			$db 	= imp_singleton_database::getInstance() ;
			$QRY	= " SELECT institutionid,institutionname  FROM iplat_institution_master where institution_status=\"ACTIVE\" ORDER BY institutionname " ;
			if ( $db->query( $QRY ) ) {
				return 	$db->fetchAssocList() ;
			}
		}

 
    /**
    * imp_institution_management::getPostedInstitutionFields()
    * It assign the posted institution fields to institution meta object
    *
    * @ Param  : $institutionDetails - Array
    * @ Return : $objInstitutionMeta - Object
    *
    * @ Author Senthil Kumar B
    * @ Since 05-Sep-2008
    **/
    public function loadInstitutionDetails( $institutionDetails ) {

        if ( count( $institutionDetails ) > 0 ) {

            $objInstitutionMeta	= new imp_institution_meta() ;

            $objInstitutionMeta->setInstitutionId( trim( $institutionDetails['institutionid'] ) ) ;
            $objInstitutionMeta->setInstitutionName( trim( $institutionDetails['institutionname'] ) ) ;
            $objInstitutionMeta->setContactpersonName( trim( $institutionDetails['contactpersonname'] ) ) ;
            $objInstitutionMeta->setContactPersonEmail( trim( $institutionDetails['contactpersonemail'] ) ) ;
            $objInstitutionMeta->setContactPersonMobile( trim( $institutionDetails['contactpersonmobile'] ) ) ;
            $objInstitutionMeta->setInstitutionAddress1( trim( $institutionDetails['institutionaddress1'] ) ) ;

            $institutionAddress2	= trim( $institutionDetails['institutionaddress2'] ) ;
            if ( !empty( $institutionAddress2 ) ) {
                $objInstitutionMeta->setInstitutionAddress2( $institutionAddress2 ) ;
            }

            $objInstitutionMeta->setInstitutionCity( trim( $institutionDetails['institutioncity'] ) ) ;
            $objInstitutionMeta->setInstitutionState( trim( $institutionDetails['institutionstate'] ) ) ;
            $objInstitutionMeta->setInstitutionCountryId( trim( $institutionDetails['institutioncountryid'] ) ) ;
            $objInstitutionMeta->setInstitutionPostalcode( trim( $institutionDetails['institutionpostalcode'] ) ) ;

            $institutionOffPhone1	= trim( $institutionDetails['institutionoffphone1'] ) ;
            if ( !empty( $institutionOffPhone1 ) ) {
                $objInstitutionMeta->setInstitutionOffPhone1( $institutionOffPhone1 ) ;
            }

            $institutionOffPhone2	= trim( $institutionDetails['institutionoffphone2'] ) ;
            if ( !empty( $institutionOffPhone2 ) ) {
                $objInstitutionMeta->setInstitutionOffPhone2( $institutionOffPhone2 ) ;
            }

            $institutionFax	= trim( $institutionDetails['institutionfax'] ) ;
            if ( !empty( $institutionFax ) ) {
                $objInstitutionMeta->setInstitutionFax( $institutionFax ) ;
            }

            /*
            if ( trim( $institutionDetails['maxuserlimit'] ) ) {
                $objInstitutionMeta->setMaximumUserCount( '0' ) ;
            } else {
                $objInstitutionMeta->setMaximumUserCount( trim( $institutionDetails['maximumusercount'] ) ) ;
            }

            if ( trim( $institutionDetails['concurrentuserlimit'] ) ) {
                $objInstitutionMeta->setConcurrentUserCount( '0' ) ;
            } else {
                $objInstitutionMeta->setConcurrentUserCount( trim( $institutionDetails['concurrentusercount'] ) ) ;
            }

            if ( trim( $institutionDetails['product_concurrent_limit'] ) ) {
                $objInstitutionMeta->setProductConcurrentCount( '0' ) ;
            } else {
                $objInstitutionMeta->setProductConcurrentCount( trim( $institutionDetails['product_concurrent_count'] ) ) ;
            }
            */

            $accountType	= trim( $institutionDetails['account_type'] ) ;
            if ( !empty( $accountType ) ) {
                $objInstitutionMeta->setInstitutionAccountType( $accountType ) ;
            }

            $createdDate	= date_format( date_create( trim( $institutionDetails['createddate'] ) ) , 'Y-m-d' ) ;
            if ( !empty( $createdDate ) ) {
                $objInstitutionMeta->setInstitutionCreatedDate( $createdDate ) ;
            }

            $endDate	    = date_format( date_create( trim( $institutionDetails['end_date'] ) ) , 'Y-m-d' ) ;
            if ( !empty( $endDate ) ) {
                $objInstitutionMeta->setInstitutionEndDate( $endDate ) ;
            }

            return $objInstitutionMeta ;
        }
    } // End of loadInstitutionDetails


    /**
    * imp_institution_management::isValidEmail()
    * It checks whether the email is valid or not
    *
    * @ Param  : $email  - String
    * @ Return : Boolean
    *
    * @ Author Senthil Kumar B
    * @ Since 06-Sep-2008
    **/
    public function isValidEmail( $email ) {

        $pattern = "/^[\p{L}\p{N}._-]+@[\p{L}\p{N}._-]+\.([\p{L}]{2,5})$/" ;
        if ( preg_match( $pattern , $email ) ) {
            return true;
        } else {
            return false;
        }
    } // End of isValidEmail


    public function emailAlreadyExists( $email , $institutionId = '' )
    {
        if ( !empty( $email ) ) {
            $SQL 		= " SELECT institutionid FROM iplat_institution_master WHERE contactpersonemail = '$email' " ;

            if ( !empty( $institutionId ) ) {
                $SQL   .= " AND institutionid != $institutionId " ;
            }

            $this->_db->query( $SQL ) ;
            if ( $this->_db->numRows > 0 ) {
                return true ;
            }
            return false ;
        }
    }


    /**
    * imp_institution_management::validateInstitutionFields()
    * It validate sthe institution fields which is assend in institution meta object
    *
    * @ Param  : $objInstitutionMeta - Object
    * @ Return : Array / Boolean
    *
    * @ Author Senthil Kumar B
    * @ Since 05-Sep-2008
    **/
    public function validateInstitutionFields( &$objInstitutionMeta ) {

        $errorMessages	= array() ;

        // Checking whether the Institution Name is entered and valid or not
        if ( '' == $objInstitutionMeta->getInstitutionName() ) {
            $errorMessages['institutionname']	= 'institution_name_1' ;
        } else if ( mb_strlen( $objInstitutionMeta->getInstitutionName() , 'UTF-8' ) > 300 ) {
            $errorMessages['institutionname']	= 'institution_name_2' ;
        }

        // Checking whether the Contact Person Name is entered and valid or not
        if ( '' == $objInstitutionMeta->getContactPersonName() ) {
            $errorMessages['contactpersonname']	= 'contact_person_name_1' ;
        } else if ( mb_strlen( $objInstitutionMeta->getContactPersonName() , 'UTF-8' ) > 50 ) {
            $errorMessages['contactpersonname']	= 'contact_person_name_2' ;
        } /*else if ( !preg_match( "/^[\p{L}][\p{L}'\s]{0,49}$/" , $objInstitutionMeta->getContactPersonName() ) ) {
            $errorMessages['contactpersonname']	= 'contact_person_name_3' ;
        }*/

        // Checking whether the Contact Person Email is entered and valid or not
        if ( '' == $objInstitutionMeta->getContactPersonEmail() ) {
            $errorMessages['contactpersonemail']	= 'contact_person_email_1' ;
        } else if ( !$this->isValidEmail( $objInstitutionMeta->getContactPersonEmail() ) ) {
            $errorMessages['contactpersonemail']	= 'contact_person_email_2' ;
        } else if ( imp_institution_management::emailAlreadyExists( $objInstitutionMeta->getContactPersonEmail() , $objInstitutionMeta->getInstitutionId() ) ) {
            $errorMessages['contactpersonemail']	= 'contact_person_email_3' ;
        }

        // Checking whether the Contact Person Mobile is entered and valid or not
        if ( '' == $objInstitutionMeta->getContactPersonMobile() ) {
            $errorMessages['contactpersonmobile']	= 'contact_person_mobile_1' ;
        } else if ( !preg_match( "/^[\p{N}\+\- ()]{0,20}$/" , $objInstitutionMeta->getContactPersonMobile() ) ) {
            $errorMessages['contactpersonmobile']	= 'contact_person_mobile_2' ;
        }

        // Checking whether the Institution Address is entered and valid or not
        if ( '' == $objInstitutionMeta->getInstitutionAddress1() ) {
            $errorMessages['institutionaddress1']	= 'institution_address_1' ;
        } else if ( mb_strlen( $objInstitutionMeta->getInstitutionAddress1() , 'UTF-8' ) > 255 ) {
            $errorMessages['institutionaddress1']	= 'institution_address_2' ;
        }

        if ( '' != $objInstitutionMeta->getInstitutionAddress2() ) {
            if ( mb_strlen( $objInstitutionMeta->getInstitutionAddress2() , 'UTF-8' ) > 255 ) {
                $errorMessages['institutionaddress2']	= 'institution_address_2' ;
            }
        }

        if ( '' == $objInstitutionMeta->getInstitutionCity() ) {
            $errorMessages['institutioncity']	= 'institution_city_1' ;
        } else if ( mb_strlen( $objInstitutionMeta->getInstitutionCity() , 'UTF-8' ) > 100 ) {
            $errorMessages['institutioncity']	= 'institution_city_2' ;
        }

        if ( '' == $objInstitutionMeta->getInstitutionState() ) {
            $errorMessages['institutionstate']	= 'institution_state_1' ;
        } else if ( mb_strlen( $objInstitutionMeta->getInstitutionState() , 'UTF-8' ) > 100 ) {
            $errorMessages['institutionstate']	= 'institution_state_2' ;
        }

        // Checking whether the Country is null or not
        if ( '' == $objInstitutionMeta->getInstitutionCountryId() ) {
            $errorMessages['institutioncountryid']	= 'institution_country_1' ;
        }

        // Checking whether the Postal/Zip Code is entered and valid or not
        if ( '' == $objInstitutionMeta->getInstitutionPostalcode() ) {
            $errorMessages['institutionpostalcode']	= 'institution_postalcode_1' ;
        } else if ( !preg_match( "/^[\p{L}\p{N}\s]{0,15}$/" , $objInstitutionMeta->getInstitutionPostalcode() ) ) {
            $errorMessages['institutionpostalcode']	= 'institution_postalcode_2' ;
        }

        // Checking whether the Institution Office Phone is null or not
        if ( '' == $objInstitutionMeta->getInstitutionOffPhone1() ) {
            $errorMessages['institutionoffphone1']	= 'institution_offphone_1' ;
        } else if ( !preg_match( "/^[\p{N}\+\- ()]{0,20}$/" , $objInstitutionMeta->getInstitutionOffPhone1() ) ) {
            $errorMessages['institutionoffphone1']	= 'institution_offphone_2' ;
        }

        if ( '' != $objInstitutionMeta->getInstitutionOffPhone2() ) {
            if ( !preg_match( "/^[\p{N}\+\- ()]{0,20}$/" , $objInstitutionMeta->getInstitutionOffPhone2() ) ) {
                $errorMessages['institutionoffphone2']	= 'institution_offphone_2' ;
            }
        }

        if ( '' != $objInstitutionMeta->getInstitutionFax() ) {
            if ( !preg_match( "/^[\p{N}\+\- ()]{0,20}$/" , $objInstitutionMeta->getInstitutionFax() ) ) {
                $errorMessages['institutionfax']	= 'institution_fax' ;
            }
        }

        /*
        if ( '' == $objInstitutionMeta->getMaximumUserCount() ) {
            $errorMessages['maximumusercount']	= 'maximum_user_1' ;
        } else if ( !preg_match( "/^[\p{N}]{0,20}$/" , $objInstitutionMeta->getMaximumUserCount() ) ) {
            $errorMessages['maximumusercount']	= 'maximum_user_2' ;
        }

        if ( '' == $objInstitutionMeta->getConcurrentUserCount() ) {
            $errorMessages['concurrentusercount']	= 'concurrent_user_1' ;
        } else if ( !preg_match( "/^[\p{N}]{0,20}$/" , $objInstitutionMeta->getConcurrentUserCount() ) ) {
            $errorMessages['concurrentusercount']	= 'concurrent_user_2' ;
        }

        if ( 'ON' == PRODUCT_CONCURRENT ) {
            if ( '' == $objInstitutionMeta->getProductConcurrentCount() ) {
                $errorMessages['productconcurrentcount']	= 'product_concurrent_1' ;
            } else if ( !preg_match( "/^[\p{N}]{0,20}$/" , $objInstitutionMeta->getProductConcurrentCount() ) ) {
                $errorMessages['productconcurrentcount']	= 'product_concurrent_2' ;
            }
        }
        */

        if ( count( $errorMessages ) > 0) {
            return $errorMessages ;
        } else {
            return true ;
        }
    }


    /**
    * imp_institution_management::addInstitutionDb()
    * It inserts the institution details into iplat_institution_master
    *
    * @ Param  : $objInstitutionMeta 		- Object
    * @ Return : $this->_db->lastInsertedId - Integer
    *
    * @ Author Senthil Kumar B
    * @ Since 08-Sep-2008
    **/
    public function addInstitutionDb( $objInstitutionMeta ) {

        // Using BEGIN to start a transaction here if it is institution user
        $SQL	= " BEGIN " ;

        if ( $this->_db->query( $SQL ) ) {
            // Query for inserting the user contact details into iplat_user_contact_extra
            $SQL1	= " INSERT INTO iplat_institution_master  SET "
                    . " institutionname			= '" . $objInstitutionMeta->getInstitutionName() . "' , "
                    . " contactpersonname		= '" . $objInstitutionMeta->getContactPersonName() . "' , "
                    . " contactpersonemail		= '" . $objInstitutionMeta->getContactPersonEmail() . "' , "
                    . " contactpersonmobile		= '" . $objInstitutionMeta->getContactPersonMobile() . "' , "
                    . " institutionaddress1 	= '" . $objInstitutionMeta->getInstitutionAddress1() . "' , "
                    . " institutionaddress2 	= '" . $objInstitutionMeta->getInstitutionAddress1() . "' , "
                    . " institutioncity 		= '" . $objInstitutionMeta->getInstitutionCity() . "' , "
                    . " institutionstate 		= '" . $objInstitutionMeta->getInstitutionState() . "' , "
                    . " institutioncountryid 	=  " . $objInstitutionMeta->getInstitutionCountryId() . " , "
                    . " institutionpostalcode	= '" . $objInstitutionMeta->getInstitutionPostalcode() . "' , "
                    . " institutionoffphone1 	= '" . $objInstitutionMeta->getInstitutionOffPhone1() . "' , "
                    . " institutionoffphone2 	= '" . $objInstitutionMeta->getInstitutionOffPhone2() . "' , "
                    . " institutionfax 			= '" . $objInstitutionMeta->getInstitutionFax() . "' , "
                    . " maximumusercount 		=  " . $objInstitutionMeta->getMaximumUserCount() . " , "
                    . " concurrentusercount		=  " . $objInstitutionMeta->getConcurrentUserCount() . " , "
                    . " createddate				= NOW() " ;

            if ( $this->_db->query( $SQL1 ) ) {
                return $this->_db->lastInsertedId ; // die(var_dump($this->_db->lastInsertedId)) ;
            } else {
                // Using ROLLBACK to cancel the transactions
                $this->_db->query( " ROLLBACK " ) ;
            }
        }
        return false ;
    }


   public function getInstitutionAdminUser( $institutionid ) {

        $SQL 		= " SELECT userid FROM iplat_user_profile WHERE institutionid = $institutionid "
                    . " AND accounttype = 'INSTITUTION_SUBSCRIPTION' AND usertype = 'INSTITUTION_SUBSCRIPTION' " ;
        $this->_db->query( $SQL ) ;
        $numRows 	= $this->_db->numRows ;
        if ( $numRows > 0 ) {
            return $this->_db->fetchAssocList() ;
        } else {
            return false ;
        }
   }

/*custom filed for registration form*/

   public function getinstitution() {

 $SQL 	= "SELECT institutionname FROM iplat_institution_master" ;
        $this->_db->query( $SQL ) ;
        $numRows 	= $this->_db->numRows ;
        if ( $numRows > 0 ) {
            return $this->_db->fetchAssocList() ;
        } else {
            return false ;
        }
       
    }
/*custom filed for registration form*/

    /**
    * imp_institution_management::validateInstitutionId()
    * Validates the Institution ID
    *
    * @author Senthil Kumar B
    * @param String $instId - Passes the Institution
    * @return bool
    * @since 04-12-2007
    */
    public function validateInstitutionId( $instId ) {

        if ( !empty( $instId ) ) {
            // Checking whether Institution ID is valid or not.
            $SQL	= " SELECT institutionid FROM iplat_institution_master WHERE institutionid = " . $instId ;
            $this->_db->query( $SQL ) ;
            if ( $this->_db->numRows > 0 ) {
                return true ;
            }
        }
        return false ;
    }


    /**
    * imp_institution_management::loadInstitutionDetails()
    * To get institution by its ID
    *
    * @author Senthil Kumar B
    * @param Int $instId - Passes the institution ID
    * @return Object - Returns the object for the class institution meta
    * @since 04-12-2007
    */
    public function getInstitutionDetailsById( $instId ) {

        if ( $this->validateInstitutionId( $instId ) ) {
            $SQL	= " SELECT * FROM iplat_institution_master WHERE institutionid = " . $instId ;
            $this->_db->query( $SQL ) ;
            if ( $this->_db->numRows > 0 ) {
                $result			= $this->_db->fetchAssocList() ;
                $objInstMeta 	= $this->loadInstitutionDetails( $result[0] ) ;
                return $objInstMeta ;
            }
        }
        return false ;
    }


    /**
    * imp_institution_management::updateInstitution()
    * Update the Existing Institution in DB
    *
    * @author Senthil Kumar B
    * @param Object $objInstMeta - Passes the object of the class imp_institution_meta
    * @return bool
    * @since 04-12-2007
    */
    public function updateInstitution( &$objInstMeta ) {

        // Updating the instituion details into iplat_instituion_master
        $updateSQL 		= " UPDATE iplat_institution_master SET "
            . " institutionname = '" 		. $this->_db->getEscaped( $objInstMeta->getInstitutionName() ) . "',"
            . " contactpersonname = '" 		. $this->_db->getEscaped( $objInstMeta->getContactPersonName() ) . "',"
            . " contactpersonemail = '" 	. $this->_db->getEscaped( $objInstMeta->getContactPersonEmail() ) . "',"
            . " contactpersonmobile= '" 	. $this->_db->getEscaped( $objInstMeta->getContactPersonMobile() ) . "',"
            . " institutionaddress1 = '" 	. $this->_db->getEscaped( $objInstMeta->getInstitutionAddress1() ) . "',"
            . " institutionaddress2 = '" 	. $this->_db->getEscaped( $objInstMeta->getInstitutionAddress2() ) . "',"
            . " institutioncity = '" 		. $this->_db->getEscaped( $objInstMeta->getInstitutionCity() ) . "',"
            . " institutionstate = '" 		. $this->_db->getEscaped( $objInstMeta->getInstitutionState() ) . "',"
            . " institutioncountryid = '" 	. $objInstMeta->getInstitutionCountryId() . "',"
            . " institutionpostalcode ='" 	. $this->_db->getEscaped( $objInstMeta->getInstitutionPostalCode() ) . "',"
            . " institutionoffphone1 = '" 	. $this->_db->getEscaped( $objInstMeta->getInstitutionOffPhone1() ) . "',"
            . " institutionoffphone2 = '"	. $this->_db->getEscaped( $objInstMeta->getInstitutionOffPhone2() ) . "',"
            . " institutionfax = '" 		. $this->_db->getEscaped( $objInstMeta->getInstitutionFax() ) . "',"
            . " maximumusercount = '" 		. $this->_db->getEscaped( $objInstMeta->getMaximumUserCount() ) . "',"
            . " concurrentusercount = '" 	. $this->_db->getEscaped( $objInstMeta->getConcurrentUserCount() ) . "'," ;

        if ( 'ON' == PRODUCT_CONCURRENT ) {
            $updateSQL .= " product_concurrent_count = '" . $this->_db->getEscaped( $objInstMeta->getProductConcurrentCount() ) . "'," ;
        }
        $updateSQL 	   .= " modifieddate = NOW() WHERE institutionid = "	. $objInstMeta->getInstitutionId() ;

        if ( $this->_db->query( $updateSQL ) ) {

            $SQL_UP	= " UPDATE iplat_user_profile SET "
                    . " email_id		= '" . $this->_db->getEscaped( $objInstMeta->getContactPersonEmail() ) . "' , "
                    . " modifieddate 	= NOW() "
                    . " WHERE accounttype 	= 'INSTITUTION_SUBSCRIPTION' "
                    . "   AND usertype 		= 'INSTITUTION_SUBSCRIPTION' "
                    . "   AND institutionid	= " . $objInstMeta->getInstitutionId() ;
            if ( $this->_db->query( $SQL_UP ) ) {
                return true ;
            }
        }

        return false ;
    }


    public function getAllRefererByInstitution( $institutionId , $refererstatus = 'ALL' , $start = 0 ,
                                                $searchKeyword = '' , $referersPerPage = REFERERS_PER_PAGE ) {

        if ( !empty( $institutionId ) ) {
            $SQL	 = " SELECT * FROM iplat_referer_master WHERE institutionid = " . $institutionId ;

            if ( 'ALL' != $refererstatus ) {
                $SQL	.= " AND refererstatus = '" . $refererstatus . "' " ;
            }

            if ( '' != $searchKeyword ) {
                $SQL	.= " AND (refererurl LIKE '%" . $searchKeyword . "%' "
                        .  "  OR refererpattern LIKE '%" . $searchKeyword . "%') " ;
            }

            $SQL	.= " LIMIT " . $start . " , " . $referersPerPage ;

            if ( $this->_db->query( $SQL ) ) {
                $result	= $this->_db->fetchAssocList() ;
                return $result ;
            }
        }
        return false ;
    }


    public function getTotalRefererByInstitution( $institutionId , $refererstatus = 'ALL' , $searchKeyword = '' ) {

        if ( !empty( $institutionId ) ) {
            $SQL	 = " SELECT COUNT( * ) AS CNT FROM iplat_referer_master WHERE institutionid = " . $institutionId ;

            if ( 'ALL' != $refererstatus ) {
                $SQL	.= " AND refererstatus = '" . $refererstatus . "' " ;
            }

            if ( '' != $searchKeyword ) {
                $SQL	.= " AND ( refererurl LIKE '%" . $searchKeyword . "%' "
                        .  "  OR refererpattern LIKE '%" . $searchKeyword . "%' ) " ;
            }

            if ( $this->_db->query( $SQL ) ) {
                $result	= $this->_db->fetchAssocList() ;
                return $result[0]['CNT'] ;
            }
        }
        return false ;
    }


    public function activateReferers( $institutionId , $refererIds ) {

        $SQL	= " UPDATE iplat_referer_master "
                . "    SET refererstatus = 'ACTIVE', "
                . " 	   modifieddate = NOW() "
                . "  WHERE refererid IN ( " . $refererIds . " ) "
                . "    AND institutionid = " . $institutionId ;
        if ( $this->_db->query( $SQL ) ) {
            return true ;
        }
        return false ;
    }


    public function deactivateReferers( $institutionId , $refererIds ) {

        $SQL	= " UPDATE iplat_referer_master "
                . "    SET refererstatus = 'INACTIVE', "
                . " 	   modifieddate = NOW() "
                . "  WHERE refererid IN ( " . $refererIds . " ) "
                . "    AND institutionid = " . $institutionId ;
        if ( $this->_db->query( $SQL ) ) {
            return true ;
        }
        return false ;
    }


    public function deleteReferers( $institutionId , $refererIds ) {

        $SQL	= " DELETE FROM iplat_referer_master WHERE refererid IN ( " . $refererIds . " ) "
                . "    AND institutionid = " . $institutionId ;
        if ( $this->_db->query( $SQL ) ) {
            return true ;
        }
        return false ;
    }


    public function getRefererById( $institutionId , $refererId ) {

        if ( !empty( $institutionId ) && !empty( $refererId ) ) {

            $SQL	= " SELECT * FROM iplat_referer_master WHERE institutionid = " . $institutionId
                    . "    AND refererid = " . $refererId ;

            if ( $this->_db->query( $SQL ) ) {
                $result	= $this->_db->fetchAssocList() ;
                return $result[0] ;
            }
        }
        return false ;
    }


    public function validateReferer( $post , $refererId = '' ) {

        if ( count( $post ) > 0 ) {

            $errorMessages	                            = array() ;
            $url		                                = trim( $post['refererurl'] ) ;
            $pattern	                                = trim( $post['refererpattern'] ) ;

            if ( '' == $url ) {
                $errorMessages['refererurl']	        = 'url_1' ;
            } else if ( !preg_match( '|^http(s)?://[\p{L}\p{N}-]+(.[\p{L}\p{N}-]+)*(:[\p{N}]+)?(/.*)?$|i' , $url ) ) {
                $errorMessages['refererurl']	        = 'url_2' ;
            } else if ( false === filter_var( $url , FILTER_UNSAFE_RAW ) ) {
                $errorMessages['refererurl']	        = 'url_2' ;
            } else if ( $this->checkRefererAvailablity( $url , $refererId ) ) {
                $errorMessages['refererurl']	        = 'url_3' ;
            }

            if ( '' != $pattern ) {
                if ( false === filter_var( $pattern , FILTER_UNSAFE_RAW ) ) {
                    $errorMessages['refererpattern']	= 'pattern' ;
                }
            }

            if ( count( $errorMessages ) > 0 ) {
                return $errorMessages ;
            } else {
                return true ;
            }
        }
        return false ;
    }


    public function checkRefererAvailablity( $url , $id ) {

        $SQL		= " SELECT COUNT(*) AS CNT FROM iplat_referer_master WHERE refererurl = '" . $url . "' " ;

        if (!empty($id) ) {
            $SQL	.= " AND refererid != " . $id ;
        }

        if ( $this->_db->query( $SQL ) ) {
            $result	= $this->_db->fetchAssocList() ;
            if ( $result[0]['CNT'] > 0 ) {
                return true ;
            } else {
                return false ;
            }
        }
    }


    public function updateReferer( $institutionId , $post ) {

        $SQL	= " UPDATE iplat_referer_master SET "
                . " refererurl = '" 		. $post['refererurl'] . "' , "
                . " refererpattern = '" 	. $post['refererpattern'] . "' , "
                . " refererstatus = '" 		. $post['refererstatus'] . "' , "
                . " modifieddate = NOW() "
                . " WHERE refererid = " 	. $post['refererid']
                . " AND institutionid = "	. $institutionId ;
        if ( $this->_db->query( $SQL ) ) {
            return true ;
        } else {
            return false ;
        }
    }


    public function addReferer( $institutionId , $post ) {

        $SQL	= " INSERT iplat_referer_master SET "
                . " refererurl = '" 		. $post['refererurl'] . "' , "
                . " refererpattern = '" 	. $post['refererpattern'] . "' , "
                . " refererstatus = '" 		. $post['refererstatus'] . "' , "
                . " createddate = NOW() , "
                . " institutionid = "		. $institutionId ;
        if ( $this->_db->query( $SQL ) ) {
            return true ;
        } else {
            return false ;
        }
    }


    public function getAllIPRangeByInstitution( $institutionId , $ipstatus = 'ALL' , $start = 0 ,
                                                $iprangesPerPage = IPRANGES_PER_PAGE ) {

        if ( !empty( $institutionId ) ) {
            $SQL	= " SELECT iprangeid , institutionid , INET_NTOA( startipaddress ) AS startipaddress, "
                    . "        INET_NTOA( endipaddress ) AS endipaddress , ip_status "
                    . "  FROM iplat_account_iprange WHERE institutionid = " . $institutionId ;

            if ( 'ALL' != $ipstatus ) {
                $SQL	.= " AND ip_status = '" . $ipstatus . "' " ;
            }

            $SQL	.= " LIMIT " . $start . " , " . $iprangesPerPage ;

            if ( $this->_db->query( $SQL ) ) {
                $result	= $this->_db->fetchAssocList() ;
                return $result ;
            }
        }
        return false ;
    }


    public function getTotalIPRangeByInstitution( $institutionId , $ipstatus = 'ALL' ) {

        if ( !empty( $institutionId ) ) {
            $SQL	 = " SELECT COUNT( * ) AS CNT FROM iplat_account_iprange WHERE institutionid = " . $institutionId ;

            if ( 'ALL' != $ipstatus ) {
                $SQL	.= " AND ip_status = '" . $ipstatus . "' " ;
            }

            if ( $this->_db->query( $SQL ) ) {
                $result	= $this->_db->fetchAssocList() ;
                return $result[0]['CNT'] ;
            }
        }
        return false ;
    }


    public function activateIPRanges( $institutionId , $ids ) {

        $SQL	= " UPDATE iplat_account_iprange "
                . "    SET ip_status = 'ACTIVE', "
                . " 	   modifieddate = NOW() "
                . "  WHERE iprangeid IN ( " . $ids . " ) "
                . "    AND institutionid = " . $institutionId ;
        if ( $this->_db->query( $SQL ) ) {
            return true ;
        }
        return false ;
    }


    public function deactivateIPRanges( $institutionId , $ids ) {

        $SQL	= " UPDATE iplat_account_iprange "
                . "    SET ip_status = 'INACTIVE' , modifieddate = NOW() "
                . "  WHERE iprangeid IN ( " . $ids . " ) "
                . "    AND institutionid = " . $institutionId ;
        if ( $this->_db->query( $SQL ) ) {
            return true ;
        }
        return false ;
    }


    public function deleteIPRanges( $institutionId , $ids ) {

        $SQL	= " DELETE FROM iplat_account_iprange WHERE iprangeid IN ( " . $ids . " ) "
                . "    AND institutionid = " . $institutionId ;
        if ( $this->_db->query( $SQL ) ) {
            return true ;
        }
        return false ;
    }


    public function getIPRangeById( $institutionId , $id ) {

        if ( !empty( $institutionId ) && !empty( $id ) ) {
            $SQL	= " SELECT iprangeid , institutionid , INET_NTOA( startipaddress ) AS startipaddress, "
                    . " INET_NTOA( endipaddress ) AS endipaddress , ip_status "
                    . " FROM iplat_account_iprange WHERE institutionid = " . $institutionId
                    . " AND iprangeid = " . $id ;

            if ( $this->_db->query( $SQL ) ) {
                $result	= $this->_db->fetchAssocList() ;
                return $result[0] ;
            }
        }
        return false ;
    }


    public function updateIPRange( $institutionId , $post ) {

        $startipaddress	= str_replace( '*' , '0' , trim( $post['startipaddress'] ) ) ;
        $endipaddress	= str_replace( '*' , '255' , trim( $post['endipaddress'] ) ) ;

        $SQL	= " UPDATE iplat_account_iprange SET "
                . " startipaddress = INET_ATON( '" 	. $startipaddress . "' ) , "
                . " endipaddress = INET_ATON( '"		. $endipaddress . "' ) , "
                . " ip_status = '" 					. $post['ip_status'] . "' , "
                . " modifieddate = NOW() "
                . " WHERE iprangeid = " 			. $post['iprangeid']
                . " AND institutionid = "			. $institutionId ;
        if ( $this->_db->query( $SQL ) ) {
            return true ;
        } else {
            return false ;
        }
    }


    public function addIPRange( $institutionId , $post ) {

        $startipaddress	= str_replace( '*' , '0' , trim( $post['startipaddress'] ) ) ;
        $endipaddress	= str_replace( '*' , '255' , trim( $post['endipaddress'] ) ) ;

        $SQL	= " INSERT iplat_account_iprange SET "
                . " startipaddress = INET_ATON( '" 	. $startipaddress . "' ) , "
                . " endipaddress = INET_ATON( '" 	. $endipaddress . "' ) , "
                . " ip_status = '" 					. $post['ip_status'] . "' , "
                . " createddate = NOW() , "
                . " institutionid = "				. $institutionId ;
        if ( $this->_db->query( $SQL ) ) {
            return true ;
        } else {
            return false ;
        }
    }


    public function validateIPRange( $post , $id = '' ) {

        if ( count( $post ) > 0) {
            $errorMessages	= array() ;

            $startipaddress		= trim( $post['startipaddress'] ) ;
            $endipaddress		= trim( $post['endipaddress'] ) ;

            if ( '' == $startipaddress ) {
                $errorMessages['startipaddress']	= 'start_ip_1' ;
            } else if ( !( preg_match( '/^(\p{N}{1,3})\.(\p{N}{1,3}|\*)\.(\p{N}{1,3}|\*)\.(\p{N}{1,3}|\*)$/' , $startipaddress ) ) ) {
                $errorMessages['startipaddress']	= 'start_ip_2' ;
            } else {
                $startipaddress	= str_replace( '*' , '0' , $startipaddress ) ;
                $startLong		= ip2long( $startipaddress ) ;
                $startipArr		= explode( '.' , $startipaddress ) ;
                if ( $startipArr[0] < 0 || $startipArr[0] > 255 ||
                     $startipArr[1] < 0 || $startipArr[1] > 255 ||
                     $startipArr[2] < 0 || $startipArr[2] > 255 ||
                     $startipArr[3] < 0 || $startipArr[3] > 255) {
                    $errorMessages['startipaddress']	= 'start_ip_2' ;
                }
            }

            if ( '' == $endipaddress ) {
                $errorMessages['endipaddress']	= 'end_ip_1' ;
            } else if ( !( preg_match( '/^(\p{N}{1,3})\.(\p{N}{1,3}|\*)\.(\p{N}{1,3}|\*)\.(\p{N}{1,3}|\*)$/' , $endipaddress ) ) ) {
                $errorMessages['endipaddress']	= 'end_ip_2' ;
            } else {
                $endipaddress	= str_replace( '*' , '255' , $endipaddress ) ;
                $endLong		= ip2long( $endipaddress ) ;
                $endipArr		= explode( '.' , $endipaddress ) ;
                if ( $endipArr[0] < 0 || $endipArr[0] > 255 ||
                     $endipArr[1] < 0 || $endipArr[1] > 255 ||
                     $endipArr[2] < 0 || $endipArr[2] > 255 ||
                     $endipArr[3] < 0 || $endipArr[3] > 255) {
                    $errorMessages['endipaddress']	= 'end_ip_2' ;
                }
            }

            if ( empty( $errorMessages['endipaddress'] ) && ( $endLong < $startLong ) ) {
                $errorMessages['endipaddress']	= 'end_ip_3' ;
            } else if ( $this->iprangeAvailability( $startipaddress , $endipaddress , $id ) &&
                       empty( $errorMessages['startipaddress'] ) && empty( $errorMessages['endipaddress'] ) ) {
                $errorMessages['ipaddress']		= 'ip_not_available' ;
            }

            if ( count($errorMessages) > 0) {
                return $errorMessages ;
            } else {
                return true ;
            }
        }
        return false ;
    }


    public function iprangeAvailability( $startipaddress , $endipaddress , $id = '' ) {

        $startipaddress	= str_replace( '*' , '0' , $startipaddress ) ;
        $endipaddress	= str_replace( '*' , '255' , $endipaddress ) ;

        $SQL	= " SELECT COUNT(*) AS CNT FROM iplat_account_iprange "
                . "  WHERE ( startipaddress >= INET_ATON( '" . $startipaddress . "' ) "
                . "    AND endipaddress <= INET_ATON( '" . $endipaddress . "' ) "
                . "     OR INET_ATON( '" . $startipaddress . "' ) BETWEEN startipaddress AND endipaddress "
                . "     OR INET_ATON( '" . $endipaddress . "' ) BETWEEN startipaddress AND endipaddress ) " ;

        if ( !empty( $id ) ) {
            $SQL	.= " AND iprangeid != " .$id ;
        }

        if ( $this->_db->query( $SQL ) ) {
            $result	= $this->_db->fetchAssocList() ;
            if ( $result[0]['CNT'] > 0) {
                return true ;
            }
        }

        return false ;
    }


    public function getInstitutionPermissions( $institutionId ) {

        if ( !empty( $institutionId ) ) {
            $SQL	= " SELECT PM.permissioncode , IPR.relationstatus "
                    . "   FROM iplat_institution_permission_rel AS IPR , iplat_permission_master AS PM "
                    . "  WHERE IPR.permissionid = PM.permissionid "
                    . "    AND PM.permissionstatus = 'ACTIVE' "
                    . "    AND IPR.institutionid = " . $institutionId ;

            if ( $this->_db->query( $SQL ) ) {
                return $this->_db->fetchAssocList() ;
            }
        }
        return false ;
    }


    public function checkMaxuser( $institutionId ) {

        if ( !empty( $institutionId ) ) {

            $SQL1	= " SELECT maximumusercount FROM iplat_institution_master WHERE institutionid = " . $institutionId ;
            if ( $this->_db->query( $SQL1 ) ) {
                $result1	= $this->_db->fetchAssocList() ;
            }

            if ( 0 == $result1[0]['maximumusercount'] ) {
                return true ;
            }

            $SQL 	= " SELECT COUNT(*) AS CNT FROM iplat_user_profile "
                    . "  WHERE institutionid = " . $institutionId
                    . "    AND useraccountstatus = 'ACTIVE' ";
            if ( $this->_db->query( $SQL ) ) {
                $result		= $this->_db->fetchAssocList() ;
            }

            if ( $result[0]['CNT'] < $result1[0]['maximumusercount'] ) {
                return true ;
            }
        }
        return false ;
    }


    public function institutionProductConcurrent( $arrParams ) {

        try {
            if ( !empty( $arrParams ) ) {

                $qrySelectSession                   = ' SELECT 1 AS session_cnt FROM iplat_product_concurrent WHERE session_id = "%s" AND subscribed_id = "%s" ' ;
                $qrySelectSession                   = sprintf( $qrySelectSession , $arrParams['sessionId'] , $arrParams['subscribedDetails']['id'] ) ;
                if ( $this->_db->query( $qrySelectSession ) ) {

                    $arrSession 	                = $this->_db->fetchAssocList() ;
                    if ( !empty( $arrSession[0]['session_cnt'] ) ) {

                        $qryUpdateSession           = ' UPDATE iplat_product_concurrent SET last_activity_time = NOW() , concurrent_access_limit = %d , reading_session_timeout = %d WHERE subscribed_id = "%s" AND session_id = "%s" ' ;

                        $arrUpdateParams            = array() ;
                        $arrUpdateParams[]          = $arrParams['subscribedDetails']['concurrent_access_limit'] ;
                        $arrUpdateParams[]          = $arrParams['subscribedDetails']['reading_session_timeout'] ;
                        $arrUpdateParams[]          = $arrParams['subscribedDetails']['id'] ;
                        $arrUpdateParams[]          = $arrParams['sessionId'] ;
                        $qryUpdateSession           = vsprintf( $qryUpdateSession , $arrUpdateParams ) ;
                        if ( $this->_db->query( $qryUpdateSession ) ) {

                            return true ;
                        }
                    }
                    else {

                        // Query to select the reading count for the subscribed product
                        $qrySelectConcurrentCnt     = ' SELECT COUNT( subscribed_id ) AS concurrent_cnt FROM iplat_product_concurrent WHERE subscribed_id = "%s" ' ;
                        $qrySelectConcurrentCnt     = sprintf( $qrySelectConcurrentCnt , $arrParams['subscribedDetails']['id'] ) ;

                        if ( $this->_db->query( $qrySelectConcurrentCnt ) ) {

                            $arrConcurrentCnt 		= $this->_db->fetchAssocList() ;
                            // If the reading count is less than the concurrent accesslimit of the subscribed product then
                            if ( $arrConcurrentCnt[0]['concurrent_cnt'] < $arrParams['subscribedDetails']['concurrent_access_limit'] ) {

                                $qryInsertSession   = ' INSERT INTO iplat_product_concurrent SET subscribed_id = "%s" , session_id = "%s" , last_activity_time = NOW() , concurrent_access_limit = %d , reading_session_timeout = %d , institutionid = %d , userid = %d , dams_id = %d ' ;
                                $arrInsertParams    = array() ;
                                $arrInsertParams[]  = $arrParams['subscribedDetails']['id'] ;
                                $arrInsertParams[]  = $arrParams['sessionId'] ;
                                $arrInsertParams[]  = $arrParams['subscribedDetails']['concurrent_access_limit'] ;
                                $arrInsertParams[]  = $arrParams['subscribedDetails']['reading_session_timeout'] ;
                                $arrInsertParams[]  = $arrParams['institutionId'] ;
                                $arrInsertParams[]  = $arrParams['userId'] ;
                                $arrInsertParams[]  = $arrParams['damsId'] ;
                                $qryInsertSession   = vsprintf( $qryInsertSession , $arrInsertParams ) ;
                                if ( $this->_db->query( $qryInsertSession ) ) {

                                    return true ;
                                }
                            }
                        }
                    }
                }
            }

            $reportObj	= new imp_reportdatacollection() ;
            $details	= array( array( 'site_id' 	=> '' ,
                                'dams_id' 			=> $arrParams['damsId'] ,
                                'session_id' 		=> $arrParams['sessionId'] ,
                                'cookie_id' 		=> '' ,
                                'referer_url'		=> '' ,
                                'ip_address' 		=> $arrParams['ipAddress'] ,
                                'proxy_ip_address'	=> '' ,
                                'user_id' 			=> $arrParams['userId'] ,
                                'institution_id' 	=> $arrParams['institutionId'] ,
                                'date_time' 		=> date_format( date_create() , 'Y-m-d H:i:s' ) ) ) ;

            $reportObj->turnawayDataCollection( $details ) ;

        }
        catch ( Exception $e ) {

            $msg	= 'Exception  => " ' . __FILE__ . ' -- imp_institution_management::institutionProductConcurrent() " ' . "\n"
                    . $e->getMessage() ;
            ipc_log::logMessages( 'imp_institution_management.log' , $msg ) ;
        }
        return false ;
    }	//	End of institutionProductConcurrent


    public function schoolProductConcurrent( $arrParams ) {

        try {
            if ( !empty( $arrParams ) ) {

                $QRY 				= " SELECT user_concurrency FROM iplat_user_pincode_subscription "
                                . "  WHERE user_id = " . $arrParams['subscriptionUserId']
                                                    . "  AND product_id = " . $arrParams['productId'] ;
                if ( $this->_db->query( $QRY ) ) {

                    $result 				= $this->_db->fetchAssocList() ;
                    $productConcurent 		= $result[0]['user_concurrency'] ;
                    if ( 0 == $productConcurent ) {
                        return true ;
                    }

                    $QRY 				= " SELECT 1 AS readCount FROM iplat_product_concurrent "
                                    . " WHERE dams_id = " . $arrParams['damsId']
                                    . " AND account_type = 'SCHOOL' AND"
                                    . " institutionid 		= " . $arrParams['schoolId']
                                    . " AND session_id = '" . $arrParams['sessionId'] . "' " ;

                    if ( $this->_db->query( $QRY ) ) {

                        $result 		= $this->_db->fetchAssocList() ;
                        if ( !empty( $result[0]['readCount'] ) ) {

                            $QRY 		= " UPDATE iplat_product_concurrent SET "
                                        . " last_activitytime 	= NOW() "
                                        . " WHERE dams_id = " . $arrParams['damsId']
                                        . " AND account_type = 'SCHOOL'"
                                        . " AND session_id = '" . $arrParams['sessionId'] . "' " ;
                            if ( $this->_db->query( $QRY ) ) {
                                return true ;
                            }
                        }

                        $QRY 			= " SELECT COUNT( dams_id ) AS readCount FROM iplat_product_concurrent "
                                        . " WHERE dams_id = " . $arrParams['damsId']
                                                            . " AND account_type = 'SCHOOL'"
                                        . " AND institutionid 		= " . $arrParams['schoolId']
                                        . " AND session_id != '" . $arrParams['sessionId'] . "' " ;
                        if ( $this->_db->query( $QRY ) ) {

                            $result 	= $this->_db->fetchAssocList() ;
                            $readCount	= $result[0]['readCount'] ;

                            if ( $readCount < $productConcurent ) {

                                $QRY 	= " INSERT INTO iplat_product_concurrent SET "
                                        . " institutionid 		= " . $arrParams['schoolId'] . " , "
                                        . " userid 				= " . $arrParams['subscriptionUserId'] . " , "
                                        . " session_id 			= '" . $arrParams['sessionId'] . "' , "
                                        . " dams_id 			= " . $arrParams['damsId'] . " , "
                                        . " account_type 		= 'SCHOOL' , "
                                        . " last_activitytime 	= NOW() " ;
                                if ( $this->_db->query( $QRY ) ) {

                                    return true ;
                                }
                            }
                        }
                    }
                }
            }
        }
        catch ( Exception $e ) {

            $msg	= 'Exception  => " ' . __FILE__ . ' -- imp_institution_management::schoolProductConcurrent() " ' . "\n"
                    . $e->getMessage() ;
            ipc_log::logMessages( 'imp_institution_management.log' , $msg ) ;
        }
        return false ;
    }	//	End of schoolProductConcurrent


    /**
    * imp_institution_management::getIdentifersFromServerVars()
    * It gets the athens identifier from SERVER variable
    *
    * @ Return  : Array of Identifier(s)
    *
    * @ Author  : Senthilkumar B
    * @ Since   : Jul 13, 2011
    **/
    public static function getIdentifersFromServerVars( $value ) {

        if ( strstr( $value , 'IDENTIFIER' ) ) {

            if ( strstr( $value , 'ORGANISATION' ) ) {

                return $value ;
            }
        }
    }   // End of getIdentifersFromServerVars


    /**
    * imp_institution_management::getUsernameFromServerVars()
    * It gets the athens user name from SERVER variable
    *
    * @ Return  : Array of user details
    *
    * @ Author  : Senthilkumar B
    * @ Since   : Jul 13, 2011
    **/
    public static function getUsernameFromServerVars( $value ) {

        if ( strstr( $value , 'USERNAME' ) ) {

            return $value ;
        }
    }   // End of getUsernameFromServerVars


    /**
    * imp_institution_management::getPersistentUidFromServerVars()
    * It gets the athens persistent uid from SERVER variable
    *
    * @ Return  : Array of user details
    *
    * @ Author  : Senthilkumar B
    * @ Since   : Jul 13, 2011
    **/
    public static function getPersistentUidFromServerVars( $value ) {

        if ( strstr( $value , 'PERSISTENTUID' ) ) {

            return $value ;
        }
    }   // End of getPersistentUidFromServerVars


    /**
    * imp_institution_management::getEntitlementFromServerVars()
    * It gets the athens entitlement from SERVER variable
    *
    * @ Return  : Array of Entitlement details
    *
    * @ Author  : Senthilkumar B
    * @ Since   : Dec 12, 2011
    **/
    public static function getEntitlementFromServerVars( $value ) {

        if ( strstr( $value , 'ENTITLEMENT' ) ) {

            return $value ;
        }
    }   // End of getEntitlementFromServerVars


    /**
    * imp_institution_management::getInstitutionByIdentifier()
    * It gets the institution list from the athens identifier which is defined in SERVER variable
    *
    * @ Return  : Array of Institution(s) details in Institution meta object
    *
    * @ Author  : Senthilkumar B
    * @ Since   : Jul 13, 2011
    **/
    public function getInstitutionByIdentifier( $intUserId ) {

        try {
            $intUserId                              = (int) $intUserId ;
            if ( !empty( $intUserId ) ) {

                $arrIdentifiers                     = array() ;
                $arrInstitutionMeta                 = array() ;
                $arrServerVarKeys                   = array_keys( $_SERVER ) ;
                $arrIdentifierKeys                  = array_filter( $arrServerVarKeys , 'imp_institution_management::getIdentifersFromServerVars' ) ;

                foreach( $arrIdentifierKeys as $eachKeys ) {

                    $arrIdentifiers[]               = $_SERVER[$eachKeys] ;
                }
                $strIdentifiers                     = implode( '" , "' , $arrIdentifiers ) ;

                $qrySelectInstitutions              = '
                    SELECT AID.institution_id , AIU.is_active FROM iplat_athens_institution_identifier AS AID

                    INNER JOIN iplat_institution_master AS IM
                            ON AID.institution_id = IM.institutionid AND
                               IM.institution_status = "ACTIVE"

                    LEFT JOIN iplat_athens_institution_user AS AIU
                            ON AIU.institution_id = AID.institution_id AND
                               AIU.user_id = %d
                    WHERE
                        AID.institution_athens_identifier IN ( "%s" )
                ' ;
                $qrySelectInstitutions              = sprintf( $qrySelectInstitutions , $intUserId , $strIdentifiers ) ;
                if ( $this->_db->query( $qrySelectInstitutions ) ) {

                    $arrInstitutionStatus           = $this->_db->fetchAssocList() ;
                    if ( count( $arrInstitutionStatus ) > 0 ) {

                        foreach( $arrInstitutionStatus as $eachInstitution ) {

                            if ( is_null( $eachInstitution['is_active'] ) || 1 == $eachInstitution['is_active'] ) {
                                $arrInstitutionMeta[]   = $this->getInstitutionDetailsById( $eachInstitution['institution_id'] ) ;
                            }
                        }
                        return $arrInstitutionMeta ;
                    }
                }
            }
        }
        catch ( Exception $e ) {

            $msg	= 'Exception  => " ' . __FILE__ . ' -- imp_institution_management::getInstitutionByIdentifier() " ' . "\n"
                    . $e->getMessage() ;
            ipc_log::logMessages( 'imp_institution_management.log' , $msg ) ;
        }
        return false ;
    }   // End of getInstitutionByIdentifier


    /**
    * imp_institution_management::getAthensUserDetails()
    * It gets the athens user details from SERVER variable & DB also. If it is not in DB then it inserts the same in DB
    *
    * @ Return  : Array of User Details
    *
    * @ Author  : Senthilkumar B
    * @ Since   : Jul 13, 2011
    **/
    public function getAthensUserDetails() {

        try {
            $arrUserDetails                         = array() ;
            $arrServerVarKeys                       = array_keys( $_SERVER ) ;

            /* Get the Athens Username from SERVER variable */
            $arrUsernameKeys                        = array_filter( $arrServerVarKeys , 'imp_institution_management::getUsernameFromServerVars' ) ;
            foreach( $arrUsernameKeys as $eachKeys ) {
                $arrUserDetails['username']         = $arrUserDetails['firstname'] = $_SERVER[$eachKeys] ;
            }

            /* Get the Athens Persistent UID from SERVER variable */
            $arrPersistentUidKeys                   = array_filter( $arrServerVarKeys , 'imp_institution_management::getPersistentUidFromServerVars' ) ;
            foreach( $arrPersistentUidKeys as $eachKeys ) {
                $arrUserDetails['puid']             = $_SERVER[$eachKeys] ;
            }

            /* Check whether the user is available or not */
            $qrySelectAthensUser                    = ' SELECT user_id , athens_username FROM iplat_athens_user WHERE persistent_uid = "%s" ' ;
            $qrySelectAthensUser                    = sprintf( $qrySelectAthensUser , $arrUserDetails['puid'] ) ;
            if ( $this->_db->query( $qrySelectAthensUser ) ) {

                $arrAthensUser                      = $this->_db->fetchAssocList() ;
                /* If the user is available in DB then get the details */
                if ( count( $arrAthensUser ) > 0 ) {
                    $arrUserDetails['userid']       = $arrAthensUser[0]['user_id'] ;
                }
                else {

                    /* If the user is not available in DB then insert the user details into DB */
                    $qryInsertUserProfile           = ' INSERT INTO iplat_user_profile SET accounttype = "INSTITUTION" ,
                            usertype = "ATHENS" , username = "%s" , firstname = "%s" , activationtype = "AUTO" ,
                            activationdate = NOW() , createddate = NOW() , useraccountstatus = "ACTIVE" , registered_from = "ATHENS" ' ;
                    $qryInsertUserProfile           = sprintf( $qryInsertUserProfile ,
                                                           md5( $arrUserDetails['username'] . $arrUserDetails['puid'] ) ,
                                                           $arrUserDetails['firstname'] ) ;

                    if ( $this->_db->query( $qryInsertUserProfile ) ) {

                        $arrUserDetails['userid']   = $this->_db->lastInsertedId ;
                        if ( $arrUserDetails['userid'] > 0 ) {

                            $qryInsertAthensUser    = ' INSERT INTO iplat_athens_user SET user_id = %d , athens_username = "%s" ,
                                    persistent_uid = "%s" , created_date = NOW() ' ;
                            $qryInsertAthensUser    = sprintf( $qryInsertAthensUser , $arrUserDetails['userid'] ,
                                                               $arrUserDetails['username'] , $arrUserDetails['puid'] ) ;
                            $this->_db->query( $qryInsertAthensUser ) ;
                        }
                    }
                }
                /* Set the athens user details in array & return it */
                $arrUserDetails['usertype']         = 'ATHENS' ;
                $arrUserDetails['logintime']	    = date_format( date_create() , 'Y-m-d H:m:s' ) ;
                $arrUserDetails['is_trial']	        = 'NO' ;
                $arrUserDetails['accounttype']	    = 'INSTITUTION' ;
                return $arrUserDetails ;
            }
        }
        catch ( Exception $e ) {

            $msg	= 'Exception  => " ' . __FILE__ . ' -- imp_institution_management::getAthensUserDetails() " ' . "\n"
                    . $e->getMessage() ;
            ipc_log::logMessages( 'imp_institution_management.log' , $msg ) ;
        }
        return false ;
    }   // End of getAthensUserDetails


    /**
    * imp_institution_management::getAthensInstitutionUserStatus()
    * It gets the athens institution user status
    *
    * @ Param   : $userId (int)
    * @ Param   : $InstitutionId (int)
    * @ Return  : User Status (string)
    *
    * @ Author  : Senthilkumar B
    * @ Since   : Jul 13, 2011
    **/
    public function getAthensInstitutionUserStatus( $userId , $InstitutionId ) {

        try {
            if ( !empty( $userId ) || !empty( $InstitutionId ) ) {

                $userId                 = (int) $userId ;
                $InstitutionId          = (int) $InstitutionId ;
                $qrySelectUserStatus    = ' SELECT IF( 1 = is_active , "ACTIVE" , "INACTIVE" ) as status FROM iplat_athens_institution_user WHERE user_id = %d AND institution_id = %d ' ;
                $qrySelectUserStatus    = sprintf( $qrySelectUserStatus , $userId , $InstitutionId ) ;
                if ( $this->_db->query( $qrySelectUserStatus ) ) {

                    $arrUserStatus      = $this->_db->fetchAssocList() ;
                    if ( count( $arrUserStatus ) > 0 ) {
                        return $arrUserStatus[0]['status'] ;
                    }
                    else {
                        $qryInsertUserStatus    = ' INSERT INTO iplat_athens_institution_user SET user_id = %d , institution_id = %d , is_active = 1 ' ;
                        $qryInsertUserStatus    = sprintf( $qryInsertUserStatus , $userId , $InstitutionId ) ;
                        if ( $this->_db->query( $qryInsertUserStatus ) ) {

                            return 'ACTIVE' ;
                        }
                    }
                }
            }
        }
        catch ( Exception $e ) {

            $msg	= 'Exception  => " ' . __FILE__ . ' -- imp_institution_management::getAthensInstitutionUserStatus() " ' . "\n"
                    . $e->getMessage() ;
            ipc_log::logMessages( 'imp_institution_management.log' , $msg ) ;
        }
        return false ;
    }   // End of getAthensInstitutionUserStatus
    
    
    public function registerUser($institutionId,$postedData){
        
        
         $QRY			= " INSERT INTO iplat_user_profile SET " ;
            if ( !empty( $institutionId )  && is_numeric($institutionId)) {
                    $QRY1   	= " SELECT maximumusercount FROM iplat_institution_master ". "  WHERE institutionid = " . $institutionId ;
                if ( $this->_db->query( $QRY1 ) ) {
                    $result 				= $this->_db->fetchAssocList() ;
                    $maxUserCount			= $result[0]['maximumusercount'] ;
                }
                if ( 0 != $maxUserCount ) {
                    $QRY1 	= " SELECT COUNT( * ) AS registered_user_count FROM iplat_user_profile "
                    . "  WHERE institutionid = " . $institutionId
                    . "    AND accounttype != 'INSTITUTION_COMMON' "
                    . "    AND useraccountstatus = 'ACTIVE' " ;

                    if ( $this->_db->query( $QRY1 ) ) {
                        $result 				= $this->_db->fetchAssocList() ;
                        $registeredUserCount	= $result[0]['registered_user_count'] ;
                    }
                    if ( $registeredUserCount >= $maxUserCount ) {
                        $flagInsert				= false ;
                    }else{
                            $flagInsert = true;
                        }
                }
                $QRY	   .= " institutionid =  " . $institutionId . " , accounttype =  'INSTITUTION' , " ;
                }else{
                    $QRY	   .= " institutionid = '0' , accounttype =  'NORMAL' , " ;                    
                }

                
                $QRY   		   .= " username 			= '" . $this->_db->getEscaped( $postedData['newusername'] ) . "' , "
                . " userpassword 		= AES_ENCRYPT( '" . $this->_db->getEscaped( $postedData['newpassword'] )
                . "' , '" . ENCRYPTION_KEY . "' ) , "
                . " firstname = '" . $this->_db->getEscaped( $postedData['firstname'] ) . "' , "
                . " lastname = '" . $this->_db->getEscaped( $postedData['lastname'] ) . "' , "
                . " email_id = '" . $this->_db->getEscaped( $postedData['emailid'] ) . "' , " ;

                $QRY   .= " useraccountstatus	= 'ACTIVE' , activationtype = 'AUTO' , " ;
                
                $QRY		   .= " createddate 		= NOW() " ;
                
//                if($flagInsert){
                 
                    if ( $this->_db->query( $QRY ) ) {
                        return  $this->_db->lastInsertedId;
                    }else{
                            return 0;
                    }
               // }else{
               //         return 0;
              //  }
                
                    
    }

    public function saveAdditionalField($userID,$postedData,$institutionId){
    
//print_r($postedData);
       //public function insertextra($userID,$titlename,$countryID,$institutionName,$userprofileid,$otherdet,$coursedet,$graduationyear,$reg_agreement,$special_offer){
     /*  $userprofileid=$postedData['profile'];
       $coursedet = $postedData['course'];
        $otherdet = $postedData['otros'];
	   $graduationyear = 0;
		if($userprofileid=='Profesor' || $userprofileid=='Other'){
			$coursedet='Null';
		}
		if($userprofileid=='Profesor' || $userprofileid=='Estudiante' ){
			$otherdet='Null';
		}*/
		
		/*$SQL = "INSERT INTO registration_custom_info values ('',".$userID.",'".$this->_db->getEscaped($titlename)."','".$this->_db->getEscaped($countryID)."','".$this->_db->getEscaped($institutionName)."','".$this->_db->getEscaped($userprofileid)."','".$this->_db->getEscaped($otherdet)."','".$this->_db->getEscaped($coursedet)."','".$graduationyear."','".$reg_agreement."','".$special_offer."') ";
		$this->_db->query($SQL);
		return true;
	}
    */
        
        $SQL = "INSERT INTO registration_custom_info  SET user_id= ".$userID.", address = '".$this->_db->getEscaped($postedData['gname'])."',  Title = '".$this->_db->getEscaped($postedData['titlename'])."',university = '".$this->_db->getEscaped($postedData['institutionname'])."',semester = '".$this->_db->getEscaped($postedData['semester'])."',carrer = '".$this->_db->getEscaped($postedData['usercategory'])."',terms_and_condition = '".$this->_db->getEscaped($postedData['chkagreement'])."'";
        
        //echo $SQL; 
    
        if ( $this->_db->query( $SQL ) ) {
            return  $this->_db->lastInsertedId;
        }else{
                return 0;
        }

        
    }
    
    
    ///////////////////////////Mobile///////////////////////////////////////
    
     public function saveAdditionalFieldmobile($userID,$postedData,$institutionId){
    
    
       //public function insertextra($userID,$titlename,$countryID,$institutionName,$userprofileid,$otherdet,$coursedet,$graduationyear,$reg_agreement,$special_offer){
     /*  $userprofileid=$postedData['profile'];
       $coursedet = $postedData['course'];
        $otherdet = $postedData['otros'];
	   $graduationyear = 0;
		if($userprofileid=='Profesor' || $userprofileid=='Other'){
			$coursedet='Null';
		}
		if($userprofileid=='Profesor' || $userprofileid=='Estudiante' ){
			$otherdet='Null';
		}*/
		//$postedData['chk1']=0;
		//$postedData['chk2']=0;
		
		/*$SQL = "INSERT INTO registration_custom_info values ('',".$userID.",'".$this->_db->getEscaped($titlename)."','".$this->_db->getEscaped($countryID)."','".$this->_db->getEscaped($institutionName)."','".$this->_db->getEscaped($userprofileid)."','".$this->_db->getEscaped($otherdet)."','".$this->_db->getEscaped($coursedet)."','".$graduationyear."','".$reg_agreement."','".$special_offer."') ";
		$this->_db->query($SQL);
		return true;
	}*/
    
        
       // $SQL = "INSERT INTO registration_custom_info  SET user_id= ".$userID.",  address = '".$this->_db->getEscaped($postedData['address'])."',   	title = '".$this->_db->getEscaped($postedData['title'])."',university='".$this->_db->getEscaped($postedData['university'])."',semester='".$this->_db->getEscaped($postedData['semester'])."',carrer='".$this->_db->getEscaped($postedData['carrer'])."',terms_and_condition ='".$postedData['chk2']."'";
        
      // echo $SQL; 
      if($postedData['chkagreement']!=''){
         $chkagreement='YES';
      }else{
         $chkagreement='NO' ;
      }
        $SQL = "INSERT INTO registration_custom_info  SET user_id= ".$userID.", address = '".$this->_db->getEscaped($postedData['gname'])."',  Title = '".$this->_db->getEscaped($postedData['titlename'])."',university = '".$this->_db->getEscaped($postedData['institutionname'])."',semester = '".$this->_db->getEscaped($postedData['semester'])."',carrer = '".$this->_db->getEscaped($postedData['carrer'])."',terms_and_condition = '".$this->_db->getEscaped($chkagreement)."'";
      
      /* $SQL = "INSERT INTO registration_custom_info  SET user_id= ".$userID.", address = '".$this->_db->getEscaped($postedData['gname'])."',  Title = '".$this->_db->getEscaped($postedData['titlename'])."',university = '".$this->_db->getEscaped($postedData['institutionname'])."',semester = '".$this->_db->getEscaped($postedData['semester'])."',carrer = '".$institutionId."',terms_and_condition = '".$this->_db->getEscaped($postedData['chkagreement'])."'";*/
        
    
        if ( $this->_db->query( $SQL ) ) {
            return  $this->_db->lastInsertedId;
        }else{
                return 0;
        }

        
    }
    
    
    public function useridreceive($emailid) {

			echo $emailid;
			if ( !empty($emailid) ) {
				//$userName 	= $this->_db->getEscaped( $emailid ) ;
				$QRY 		= " SELECT userid,username FROM iplat_user_profile "
							. "  WHERE email_id = '$emailid' " ;
							//. "  WHERE AES_ENCRYPT( username , '$time' ) = AES_ENCRYPT( '$userName' , '$time' ) " ;
				//$this->_db->query( $QRY ) ;
//$val= $this->_db->query( $QRY );
				if ( $this->_db->query( $QRY ) ) {
				return $this->_db->fetchAssocList() ;
			}
			}
		} // End of userNameAvailability
    /////////////////////////////Mobile//////////////////////////////////
    
    
    public function sendRegistrationEmail( $postedData , $emailTemplate = '' ) {

			// get email template name from configuration
			$userActivation							= '/useractivation';
			$arrMailDetails['username']             = $postedData['newusername'] ;
			$arrMailDetails['password']             = $postedData['newpassword'] ;
			$arrMailDetails['firstname']            = $postedData['firstname'] ;
			//$arrMailDetails['toid'] 	            = $objUserMeta->getEmailId() ;
			$arrMailDetails['fromid'] 	            = REGISTRATION_ADMIN_EMAIL ;
			$arrMailDetails['livesite']             = LIVE_SITE_PATH ;
			$arrMailDetails['sitename']             = SITE_NAME ;
			//$arrMailDetails['useractivationlink']   = LIVE_SITE_PATH . "$userActivation?token=".$this->_token;
			
			$arrMailDetails['institutionname']      = iplat_session_management::getInstitution() ;
			$arrMailDetails['schoolname']           = iplat_session_management::getSchoolName() ;
			

            $emailTemplate                          = ( '' != $emailTemplate )
                                                    ? $emailTemplate
                                                    : REGISTRATION_EMAIL_TEMPLATE ;

			// get the email body,subject from db for given template name.
			$objEmailEditorManager 		            = new emaileditor_mgt() ;

			$arrMailContent 			            = $objEmailEditorManager->getEmailContent( $emailTemplate ,
                                                                                               $arrMailDetails ) ;

			$subject 					            = $arrMailContent->getSubject() ;
			$body 						            = $arrMailContent->getContent() ;

			// create alert management mail object to send the mail
			$objAlertMail 				            = new AlertMail() ;
			$objAlertMail->setFromId( REGISTRATION_ADMIN_EMAIL )->setToIds( array( $postedData['emailid'] ) )->setSubject( $subject )->setBody( $body )->setPriority( AlertMail::PRIORITY_REALTIME )->setEmailType( AlertMail::EMAILTYPE_HTML ) ; //PRIORITY_REALTIME

			if ( $objAlertMail->send() ) {
				return true ;
			}
			return false ;
		}

    
}   // End of imp_institution_management