<?php
/**
 * Copyright (c) 2013, Podyachev Evgeny <joker.OW2@gmail.com>
 * All rights reserved.

 * ATTENTION: This commercial software is intended for use with Oxwall Free Community Software http://www.oxwall.org/
 * and is licensed under Oxwall Store Commercial License.
 * Full text of this license can be found at http://www.oxwall.org/store/oscl
 */

/**
 * @author Podyachev Evgeny <joker.OW2@gmail.com>
 * @package ow_plugins.google_maps_location.bol
 * @since 1.0
 */

class GOOGLELOCATION_BOL_LocationDao extends OW_BaseDao
{
    const ENTITY_ID = 'entityId';
    const ENTITY_TYPE = 'entityType';
    const COUNTRY_CODE = 'countryCode';
    const ADDRESS_STRING = 'address';
    const LATITUDE = 'lat';
    const LONDITUDE = 'lng';
    
    const ENTITY_TYPE_USER = 'user';
    const ENTITY_TYPE_EVENT = 'event';

    /**
     * Class constructor
     *
     */
    protected function __construct()
    {
        parent::__construct();
    }
    /**
     * Class instance
     *
     * @var GOOGLELOCATION_BOL_LocationDao
     */
    private static $classInstance;

    /**
     * Returns class instance
     *
     * @return GOOGLELOCATION_BOL_LocationDao
     */
    public static function getInstance()
    {
        if ( self::$classInstance === null )
        {
            self::$classInstance = new self();
        }

        return self::$classInstance;
    }

    /**
     * @see OW_BaseDao::getDtoClassName()
     *
     */
    public function getDtoClassName()
    {
        return 'GOOGLELOCATION_BOL_Location';
    }

    /**
     * @see OW_BaseDao::getTableName()
     *
     */
    public function getTableName()
    {
        return OW_DB_PREFIX . 'googlelocation_data';
    }
    
    /**
     * @param integer $userId
     * @return GOOGLELOCATION_BOL_Location
     */
    public function findByUserId( $userId )
    {
        if ( empty($userId) )
        {
            return null;
        }
        
        $example = new OW_Example();
        $example->andFieldEqual(self::ENTITY_ID, (int)$userId);
        $example->andFieldEqual(self::ENTITY_TYPE, self::ENTITY_TYPE_USER);
        return $this->findObjectByExample($example);
    }
    
    
    /**
     * @param array $userIdList
     * @return array <GOOGLELOCATION_BOL_Location>
     */
    public function findByUserIdList( array $userIdList )
    {
        if ( empty($userIdList) )
        {
            return array();
        }
        
        $example = new OW_Example();
        $example->andFieldInArray(self::ENTITY_ID, $userIdList);
        $example->andFieldEqual(self::ENTITY_TYPE, self::ENTITY_TYPE_USER);
        return $this->findListByExample($example);
    }


    /**
     * @param string $prefix
     * @param float $southWestLat
     * @param float $southWestLng
     * @param float $northEastLat
     * @param float $northEastLng
     * @param string $countryCode
     * @return string
     */
    
    public function getSearchInnerJoinSql( $prefix, $southWestLat, $southWestLng, $northEastLat, $northEastLng, $countryCode = null, $joinType = 'INNER' )
    {
        $countryStr = "";
        /* if ( !empty($countryCode) )
        {
            $countryStr =" AND location.".self::COUNTRY_CODE." = '".$this->dbo->escapeString($countryCode)."' ";
        } */

        $sql = $joinType . " JOIN ".$this->getTableName()." location ON ( $prefix.id = location.entityId AND location.entityType = '".self::ENTITY_TYPE_USER."'
                 AND (
                         location.southWestLat >= ".(float)$southWestLat."
                         AND location.southWestLat <= ".(float)$northEastLat."
                             OR
                         location.northEastLat >= ".(float)$southWestLat."
                         AND location.northEastLat <= ".(float)$northEastLat."
                             OR
                         location.southWestLat >= ".(float)$southWestLat."
                         AND location.northEastLat <= ".(float)$northEastLat."
                             OR
                         location.southWestLat <= ".(float)$southWestLat."
                         AND location.northEastLat >= ".(float)$northEastLat."
                     )

                 AND (
                         location.southWestLng >= ".(float)$southWestLng."
                         AND location.southWestLng <= ".(float)$northEastLng."
                             OR
                         location.northEastLng >= ".(float)$southWestLng."
                         AND location.northEastLng <= ".(float)$northEastLng."
                             OR
                         location.southWestLng >= ".(float)$southWestLng."
                         AND location.northEastLng <= ".(float)$northEastLng."
                             OR
                         location.southWestLng <= ".(float)$southWestLng."
                         AND location.northEastLng >= ".(float)$northEastLng."
                     ) $countryStr ) ";

        return $sql;
    }
    
    /**
     * @return array
     */
    public function getAllLocationsForUserMap()
    {
        $queryParts = BOL_UserDao::getInstance()->getUserQueryFilter("u", "id", array(
            "method" => "GOOGLELOCATION_BOL_LocationDao::getAllLocationsForUserMap"
        ));

        $query = "SELECT l.* FROM " . $this->getTableName() . " l

                INNER JOIN `" . BOL_UserDao::getInstance()->getTableName() . "` as `u`
                    ON( `l`.`entityId` = `u`.`id` )

               " . $queryParts["join"] . "

                WHERE " . $queryParts["where"] . " AND l.entityType = '".self::ENTITY_TYPE_USER."' ";

        return $this->dbo->queryForList($query);
    }
    
    public function getAllLocationsForEntityType($entityType)
    {
        $query = "SELECT l.* FROM " . $this->getTableName() . " l
                WHERE l.entityType = :entityType ";
                
        return $this->dbo->queryForList($query, array('entityType' => $entityType));
    }
    
    public function findUserListByCoordinates( $lat, $lng, $first, $count, $userIdList = array() )
    {
        if ( !isset($lat) || !isset($lng) )
        {
            return array();
        }

        $where = "";

        if ( !empty($userIdList) && is_array($userIdList) )
        {
            $where = " AND u.id IN ( ". $this->dbo->mergeInClause($userIdList) ." ) ";
        }

        

        $queryParts = BOL_UserDao::getInstance()->getUserQueryFilter("u", "id", array(
            "method" => "GOOGLELOCATION_BOL_LocationDao::findUserListByCoordinates"
        ));

        $query = "SELECT u.* FROM " . $this->getTableName() . " l

                INNER JOIN `" . BOL_UserDao::getInstance()->getTableName() . "` as `u`
                    ON( `l`.`entityId` = `u`.`id` )

               " . $queryParts["join"] . "

                WHERE " . $queryParts["where"] . $where . " AND l.lat = :lat AND l.lng = :lng AND l.entityType = '".self::ENTITY_TYPE_USER."'  LIMIT :first, :count";
        
        return $this->dbo->queryForObjectList($query, BOL_UserDao::getInstance()->getDtoClassName(), array( 'lat' => $lat, 'lng' => $lng, 'first' => (int)$first, 'count' => (int)$count ));
    }
    
    public function findUserCountByCoordinates( $lat, $lng, $userIdList = array() )
    {
        if ( !isset($lat) || !isset($lng) )
        {
            return 0;
        }

        $where = "";

        if ( !empty($userIdList) && is_array($userIdList) )
        {
            $where = " AND u.id IN ( ". $this->dbo->mergeInClause($userIdList) ." ) ";
        }

        $queryParts = BOL_UserDao::getInstance()->getUserQueryFilter("u", "id", array(
            "method" => "GOOGLELOCATION_BOL_LocationDao::findUserCountByCoordinates"
        ));

        $query = "SELECT Count(DISTINCT l.entityId) FROM " . $this->getTableName() . " l

                INNER JOIN `" . BOL_UserDao::getInstance()->getTableName() . "` as `u`
                    ON( `l`.`entityId` = `u`.`id` )

                " . $queryParts["join"] . "

                WHERE " . $queryParts["where"] . $where . " AND l.lat = :lat AND l.lng = :lng AND l.entityType = '".self::ENTITY_TYPE_USER."' ";

        return $this->dbo->queryForColumn($query, array( 'lat' => $lat, 'lng' => $lng ));
    }
    
    public function getLocationName( $lat, $lng )
    {
        if ( !isset($lat) || !isset($lng) )
        {
            return null;
        }
        
        $query = "SELECT l.address FROM " . $this->getTableName() . " l
                WHERE l.lat = :lat AND l.lng = :lng LIMIT 1 ";
        
        return $this->dbo->queryForColumn($query, array( 'lat' => $lat, 'lng' => $lng ));
    }
    
    public function findEntityIdAndEntityType( $entityId, $entityType )
    {
        if ( empty($entityId) || empty($entityType) )
        {
            return null;
        }
        
        $example = new OW_Example();
        $example->andFieldEqual(self::ENTITY_ID, $entityId);
        $example->andFieldEqual(self::ENTITY_TYPE, $entityType);
        return $this->findObjectByExample($example);
    }
    
    public function deleteByEntityIdAndEntityType( $entityId, $entityType )
    {
        if ( empty($entityId) || empty($entityType) )
        {
            return false;
        }
        
        $example = new OW_Example();
        $example->andFieldEqual(self::ENTITY_ID, $entityId);
        $example->andFieldEqual(self::ENTITY_TYPE, $entityType);
        return $this->deleteByExample($example);
    }
    
    public function getListOrderedByDistance( $userIdList, $first, $count, $lat, $lon )
    {
        if ( empty($userIdList) )
        {
            return array();
        }
        
        $queryParts = BOL_UserDao::getInstance()->getUserQueryFilter("user", "id", array(
            "method" => "GOOGLELOCATION_BOL_LocationDao::getListOrderedByDistance"
        ));
        
        $where = '';
        
        $join = ' INNER JOIN `'.GOOGLELOCATION_BOL_LocationDao::getInstance()->getTableName(). '` location ON ( user.id = location.entityId AND location.entityType = \''.GOOGLELOCATION_BOL_LocationDao::ENTITY_TYPE_USER.'\' )';
            
        $sql = "SELECT `user`.* FROM `" . BOL_UserDao::getInstance()->getTableName() . "` `user`
            {$queryParts["join"]}
            LEFT JOIN `".GOOGLELOCATION_BOL_LocationDao::getInstance()->getTableName(). "` location ON ( user.id = location.entityId AND location.entityType = '".GOOGLELOCATION_BOL_LocationDao::ENTITY_TYPE_USER."' )
            WHERE `user`.`id` IN (" . $this->dbo->mergeInClause($userIdList) . ") $where
            ORDER BY if( location.id IS NULL, 99999999999999, (
            acos (
                    cos ( radians(:lat) )
                    * cos( radians( location.lat ) )
                    * cos( radians( location.lng ) - radians(:lon) )
                    + sin ( radians(:lat) )
                    * sin( radians( location.lat ) )
                ) )
            ) ASC , `user`.`activityStamp` DESC limit :first, :count";
            
        return $this->dbo->queryForObjectList($sql, $this->getDtoClassName(), array('first' => $first, 'count' => $count, 'lat' => $lat, 'lon' => $lon) );
    }
}
