<?php

/*
 * Catabatic Technology Pvt. Ltd.
 * File Name :TblLocation.php
 * File Description :TblLocation Model managed location data
 * Created By : Pooja Choudhary
 * Created Date: 07-June-2016
 */

class Travel_Model_TblLocation
{

    private $db = NULL;

    public function __construct()
    {
        $request = Zend_Controller_Front::getInstance()->getRequest();
        $this->baseUrl = $request->getScheme() . '://' . $request->getHttpHost();
        $this->db = Zend_Db_Table::getDefaultAdapter();
    }

    public function __destruct()
    {
        $this->db->closeConnection();
    }

    /*
     * getCountryList(); is used to get all data of country
     * return array;
     * Created By- Pooja
     * Created on- 7 June 2016
     */

    public function getCountryList()
    {

        $select = $this->db->select()
            ->distinct()
            ->from("TB_Master_Geo_Country", array('ContId', 'Title'))
            ->where('isActive = ?', 1)
            ->where('IsMarkForDel = ?', 0)
            ->where('isApprove = ?', 1)
            ->order("Title ASC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    /*
     * getStateList(); is used to get all data of state by country id
     * return array;
     * Created By- Pooja
     * Created on- 7 June 2016
     */

    public function getStateList($contId)
    {
        $select = $this->db->select()
            ->from("TB_Master_Geo_State", array('StateId', 'Title'))
            ->where('isActive = ?', 1)
            ->where("ContSysId =?", $contId)
            ->order("Title ASC");
        $result = $this->db->fetchAll($select);
        return $result;
    }

    /*
     * getStateName(); is used to get name of state by state id
     * return array;
     * Created By- Pooja
     * Created on- 8 June 2016
     */

    public function getStateName($StateId)
    {
        $select = $this->db->select()
            ->from("TB_Master_Geo_State", array('Title'))
            ->where("StateId =?", $StateId)
            ->order("Title ASC");
        $result = $this->db->fetchRow($select);
        return $result;
    }

    /*
     * getCityList(); is used to get list of cities by state name (because state id is 0 for all in city table)
     * return array;
     * Created By- Pooja
     * Created on- 8 June 2016
     */

    public function getCityList($CountryID = NULL)
    {
        /*  if($State != ''){
          $select = $this->db->select()
          ->from("TB_Master_Geo_City", array('CityId', 'Title'))
          ->where('isActive = ?', 1)
          ->where("ContSysId =?", $CountryID);
          } else{
          $select = $this->db->select()
          ->from("TB_Master_Geo_City", array('CityId', 'Title'))
          ->where('isActive = ?', 1)
          ->where("StateOrZone =?", $State);
          } */

        $sql = " SELECT * FROM TB_Master_Geo_City WHERE 1 = 1 AND IsApproved = 1 AND IsMarkForDel = 0  AND isActive = 1 ";

        if (!empty($State)) {
            $sql .= " AND TB_Master_Geo_City.StateOrZone = " . $State;
        }

        if (!empty($CountryID)) {
            $sql .= " AND TB_Master_Geo_City.ContSysId = " . $CountryID;
        }
        $sql .= " ORDER BY TITLE ASC";

        $result = $this->db->query($sql)->fetchAll();

        //        echo "<pre>".$sql;
        //        print_r($result);

        return $result;
    }

    public function getAutoSuggestCountry($keyword)
    {
        $keyword = trim($keyword);

        $sql = " SELECT * FROM TB_Master_Geo_Country WHERE 1 = 1 AND IsApprove = 1 AND IsMarkForDel = 0 AND isActive = 1 ";


        if (!empty($keyword)) {
            $sql .= " AND TB_Master_Geo_Country.Title like '" . $keyword . "%' ";
        }

        $result = $this->db->query($sql)->fetchAll();
        //        echo '<pre>';print_r($result);
        $response = array();
        if (count($result) > 0) {
            foreach ($result as $row) {
                $response[] = array('value' => $row['ContId'], 'label' => ucfirst($row['Title']));

                //$response[] = $row['Title'];
            }
        }
        return $response;
    }

    public function getAutoSuggestCity($keyword, $countryId = NULL)
    {
        $keyword = trim($keyword);

        $sql = " SELECT * FROM TB_Master_Geo_City WHERE 1 = 1 AND IsApproved = 1 AND IsMarkForDel = 0 AND isActive = 1 ";

        if (!empty($countryId)) {
            $sql .= " AND TB_Master_Geo_City.ContSysId = '" . $countryId . "' ";
        }
        if (!empty($keyword)) {
            $sql .= " AND TB_Master_Geo_City.Title like '" . $keyword . "%' ";
        }
        echo $sql;
        exit;
        $result = $this->db->query($sql)->fetchAll();
        //        echo '<pre>';print_r($result);
        $response = array();
        if (count($result) > 0) {
            foreach ($result as $row) {
                $response[] = array('value' => $row['CityId'], 'label' => ucfirst($row['Title']));
            }
        }
        return $response;
    }

    public function getAutoSuggestCityForInvoice($keyword, $countryId = NULL)
    {
        $keyword = trim($keyword);

        $sql = " SELECT t1.ContSysId, t1.CityId, t1.Title as CityName,t1.StateSysId, t2.Title as CountryName FROM TB_Master_Geo_City t1
        left join TB_Master_Geo_Country t2 on t1.ContSysId = t2.ContId
        WHERE 1 = 1 
        AND t1.IsApproved = 1 
        AND t1.IsMarkForDel = 0 
        AND t1.isActive = 1   AND t2.IsMarkForDel = 0 AND t2.IsActive = 1 ";
        if (!empty($keyword)) {
            $sql .= " AND t1.Title like '" . $keyword . "%' ";
        }
        $result = $this->db->query($sql)->fetchAll();
        //        echo '<pre>';print_r($result);
        $response = array();
        if (count($result) > 0) {
            foreach ($result as $row) {
                $lavel = ucfirst($row['CityName']) . ' (' . ucfirst($row['CountryName']) . ')';
                $response[] = array('value' => $row['CityId'], 'label' => $lavel, 'ContSysId' => $row['ContSysId'], 'StateSysId' => $row['StateSysId']);
            }
        }
        return $response;
    }




    public function addCountry($countryTitle = array(), $currentDate = NULL)
    {
        $countryData = array(
            'Title' => $countryTitle ? $countryTitle : 0,
            'Code' => 0,
            'TBBContId' => 0,
            'PhoneCode' => 0,
            'TimeZoneUTC' => 0,
            'RegionId' => 0,
            'Continent' => 0,
            'IsApprove' => 0,
            'Synonyms' => 0,
            'ApproveDate' => 0,
            'UpdateDate' => 0,
            'CreateDate' => $currentDate ? $currentDate : 0,
            'IsMarkForDel' => 0,
            'IsActive' => 0
        );



        try {
            $this->db->insert('TB_Master_Geo_Country', $countryData);
            return $this->db->lastInsertId('TB_Master_Geo_Country');
        } catch (Exception $e) {
            die('There has been an error. ' . $e->getMessage());
        }
        return 0;
    }

    public function addCity($cityTitle, $countryId, $countryTitle, $currentDate)
    {
        $cityData = array(
            'TBBCityId' => 0,
            'Title' => $cityTitle ? $cityTitle : 0,
            'Alias' => $cityTitle ? $cityTitle : 0,
            'StateOrZone' => 0,
            'Country' => $countryTitle ? $countryTitle : 0,
            'GetLat' => 0,
            'GetLong' => 0,
            'StateSysId' => 0,
            'ZoneSysId' => 0,
            'ContSysId' => $countryId ? $countryId : 0,
            'PIN' => 0,
            'IsHaveAirPort' => 0,
            'ApproveDate' => 0,
            'CreateDate' => $currentDate ? $currentDate : EMPTY_DATE,
            'UpdateDate' => 0,
            'IsApproved' => 0,
            'IsMarkForDel' => 0,
            'IsActive' => 0
        );
        try {
            $this->db->insert('TB_Master_Geo_City', $cityData);
            return $this->db->lastInsertId('TB_Master_Geo_City');
        } catch (Exception $e) {
            die('There has been an error. ' . $e->getMessage());
        }
        return 0;
    }

    public function getAllCityList()
    {
        $sql = " SELECT Title,CityId FROM TB_Master_Geo_City WHERE 1 = 1  AND IsApproved = 1 AND IsMarkForDel = 0 AND IsActive = 1 ";
        $sql .= " ORDER BY Title ";
        $rowset = $this->db->query($sql)->fetchAll();
        return $rowset;
    }

    public function addSupplierDestinationMappingData($geographyRegionId, $supplierId, $supplierContactId = 0)
    {
        $select = $this->db->select();
        $select->from(array('tb1' => "TB_MASTER_VIEW_PKGSEARCH_STRING"));
        $select->where("tb1.ID IN (?)", $geographyRegionId);
        $result = $this->db->fetchAll($select);
        if (!empty($result)) {
            $softdeleteArrayData = array('IsMarkForDel' => 1);
            $whereSoft = array('SupplierId = ?' => $supplierId,'SupplierContactId = ?' => $supplierContactId);
            $this->db->update('TB_MASTER_VIEW_SUPPLIERSEACH_STRING', $softdeleteArrayData, $whereSoft);
            foreach ($result as $pkgSearch) {
                $select = $this->db->select();
                $select->from(array('tb1' => "TB_MASTER_VIEW_SUPPLIERSEACH_STRING"));
                $select->where("tb1.RefId = (?)", $pkgSearch['Id']);
                $select->where("tb1.SupplierId = (?)", $supplierId);
                $select->where("tb1.SupplierContactId = (?)", $supplierContactId);
                $result1 = $this->db->fetchAll($select);
                if (!empty($result1)) {
                    $updateData = array(
                        'IsMarkForDel' => 0
                    );
                    $where = array("RefId = ?" => $pkgSearch['Id'], 'SupplierId = ?' => $supplierId, 'SupplierContactId = ?' => $supplierContactId);
                    $this->db->update('TB_MASTER_VIEW_SUPPLIERSEACH_STRING', $updateData, $where);
                } else {
                    $arrayData = array(
                        'SupplierId' => $supplierId,
                        'SupplierContactId' => $supplierContactId,
                        'RefId' => $pkgSearch['Id'],
                        'TypeId' => $pkgSearch['TypeId'],
                        'TypeTitle' => $pkgSearch['TypeTitle'],
                        'IsMarkForDel' => 0
                    );
                    
                    $this->db->insert('TB_MASTER_VIEW_SUPPLIERSEACH_STRING', $arrayData);
                }
            }
            $whereDelete = array("IsMarkForDel = '1'", "SupplierId = $supplierId","SupplierContactId = $supplierContactId");
            $this->db->delete('TB_MASTER_VIEW_SUPPLIERSEACH_STRING', $whereDelete);
        }
    }
}