<?php

class main_model extends Model {

    public function getSlideShow() {
        $result = $this->query("SELECT * FROM azonics_boxes WHERE box_status<>'0' ORDER BY box_id ASC;");
        if (sizeof($result)>0) {
            return $result;
        }
        else {
            return false;
        }
    }
    public function getMenu($section = 'tours') {
        $result = $this->query("SELECT * FROM azonics_menus WHERE box_status<>'0' AND box_subtitle='".$section."' ORDER BY box_id ASC;");
        if (sizeof($result)>0) {
            return $result;
        }
        else {
            return false;
        }
    }


    public function getBoxes($lang = 'en') {
        $result = $this->query("SELECT * FROM azonics_blocks WHERE box_status<>'0' ORDER BY box_id ASC;");
        if (sizeof($result)>0) {
            return $result;
        }
        else {
            return false;
        }
    }

    public function getServices() {
        $result = $this->query("SELECT * FROM azonics_services WHERE box_status<>'0' ORDER BY box_id ASC;");
        if (sizeof($result)>0) {
            return $result;
        }
        else {
            return false;
        }
    }

    public function getReviews($from,$limit) {
        $result = $this->query("SELECT * FROM azonics_reviews WHERE box_status<>'0' ORDER BY box_id ASC LIMIT ".$from.", ".$limit.";");
        if (sizeof($result)>0) {
            return $result;
        }
        else {
            return false;
        }
    }

    public function getFAQs($limit) {
        if ($limit != 0) {
            $result = $this->query("SELECT * FROM azonics_faq WHERE box_status<>'0' ORDER BY box_id ASC LIMIT 0, ".$limit.";");
        }
        else {
            $result = $this->query("SELECT * FROM azonics_faq WHERE box_status<>'0' ORDER BY box_id;");
        }
        if (sizeof($result)>0) {
            return $result;
        }
        else {
            return false;
        }
    }

    public function getGallery() {
        $result = $this->query("SELECT * FROM azonics_slideshow WHERE slide_status<>'0' ORDER BY slide_id ASC;");
        if (sizeof($result)>0) {
            return $result;
        }
        else {
            return false;
        }
    }

    public function getBoarding() {
        $result = $this->query("SELECT * FROM azonics_boarding WHERE box_id='1';");
        if (sizeof($result)>0) {
            return $result[0];
        }
        else {
            return false;
        }
    }

    public function getOurServices() {
        $result = $this->query("SELECT * FROM azonics_pages WHERE page_id='1';");
        if (sizeof($result)>0) {
            return $result[0];
        }
        else {
            return false;
        }
    }

    public function getPortfolio($lang = 'en') {
        $result = $this->query("SELECT * FROM azonics_blog WHERE page_status<>'0' AND page_category='befektetesek' ORDER BY page_id ASC;");
        if (sizeof($result)>0) {
            return $result;
        }
        else {
            return false;
        }
    }

    public function getAnnos($lang = 'en') {
        $result = $this->query("SELECT * FROM azonics_blog WHERE page_status<>'0' AND page_category='kozzetetelek' ORDER BY page_id ASC;");
        if (sizeof($result)>0) {
            return $result;
        }
        else {
            return false;
        }
    }

    public function getBox($id) {
        $result = $this->query("SELECT * FROM azonics_boxes WHERE box_status<>'0' AND box_id='".$id."';");
        if (sizeof($result)>0) {
            return $result[0];
        }
        else {
            return false;
        }
    }


    public function getAllPropertiesCount() {
        $result = $this->query("SELECT * FROM hiper_ingatlan WHERE statusz='aktualis' AND (rockhomera_mehet='1' OR rockhomera_mehet='0');");
        return sizeof($result);
    }


    public function getAllAgentsCount() {
        $result = $this->query("SELECT * FROM hiper_munkatars WHERE aktiv='1';");
        return sizeof($result);
    }


    public function getAllOfficesCount() {
        $result = $this->query("SELECT * FROM hiper_iroda WHERE aktiv='1';");
        return sizeof($result);
    }


    public function get_telepulesek() {
        $result = $this->query("SELECT telepules FROM hiper_ingatlan WHERE telepules<>'' AND statusz='aktualis' AND (rockhomera_mehet='1' OR rockhomera_mehet='0') GROUP BY telepules ORDER BY telepules ASC;");
        return $result;
    }


    public function getCitySuggession($key) {
        $key = $this->escapeString($key);
        $result = $this->query("SELECT telepules FROM hiper_ingatlan WHERE telepules<>'' AND telepules LIKE '%".$key."%' AND statusz='aktualis' AND (rockhomera_mehet='1' OR rockhomera_mehet='0') GROUP BY telepules ORDER BY telepules ASC;");
        return $result;
    }


    public function getMoreOffers($limit,$source) {
        $search = $this->escapeArray($search);

        $tempSQL = "SELECT * FROM hiper_ingatlan WHERE statusz='aktualis' AND (rockhomera_mehet='1' OR rockhomera_mehet='0')";

        if ($_SESSION['storedOrderField']=='') $_SESSION['storedOrderField'] = 'letrehozva';
        if ($_SESSION['storedOrderDirection']=='') $_SESSION['storedOrderDirection'] = 'DESC';

        $tempSQL.= $this->getInactiveOffices()." ORDER BY ".$_SESSION['storedOrderField']." ".$_SESSION['storedOrderDirection'].";";

        $querySQL = $tempSQL;
        
        $result = $this->query($querySQL);

        if (sizeof($result)>0) {
            $i = 0;
            foreach ($result as $row) {
                $skip = false;
                foreach ($source as $item) {
                    if ($item->id==$row->id) {
                        $skip = true;
                    }
                }
                if ($skip==false && $i<$limit) {
                    $i++;
                    $source[] = $row;
                }
            }

            return $source;
        }
        else {
            return false;
        }
    }


    public function getInactiveOffices() {
        $check = $this->query("SELECT * FROM irodak where active='0';");
        foreach ($check as $row) {
            $temp[] = $row->office_id;
        }

        if (count($temp)>0) {
            return ' AND iroda NOT IN (\''.implode('\',\'',$temp).'\')';
        }
        else {
            return '';
        }
    }

    public function getAgentName($id) {
        $id = $this->escapeString($id);
        if ($id!='') {
            $agent = $this->query("SELECT * FROM hiper_munkatars WHERE felhasznalonev='".$id."';");
            if (sizeof($agent)>0) {
                return $agent[0]->nev;
            }
            else {
                return false;
            }
        }
        else {
            return false;
        }
    }

    public function get_offers($search) {
        $search = $this->escapeArray($search);
        
        $tempSQL = "SELECT *,getimgurl(indexkep,'ingatlan') AS indexkep FROM hiper_ingatlan WHERE statusz='aktualis' AND (rockhomera_mehet='1' OR rockhomera_mehet='0')";

        /*if ($_SESSION['search']['city']!='' && stristr($_SESSION['search']['city']," (")==false) {
            $list = explode(",",$_SESSION['search']['city']);
            $cities = "";
            foreach ($list as $city) {
                if ($city!='Budapest') {
                    $cities.= "'".$city."',";
                }
            }
            $cities = rtrim($cities,',');
            if ($cities!='') {
                $tempSQL.= " AND telepules IN (".$cities.")";
            }

            foreach ($list as $city) {
                if ($city=='Budapest') {
                    $tempSQL.= " AND telepules LIKE '%".$city."%'";
                }
            }
        }
        elseif (stristr($_SESSION['search']['city']," (")) {
            $tempSQL.= " AND telepules LIKE '%Budapest%'";
        }*/

        if ($_SESSION['storedOrderField']=='') $_SESSION['storedOrderField'] = 'letrehozva';
        if ($_SESSION['storedOrderDirection']=='') $_SESSION['storedOrderDirection'] = 'DESC';

        $tempSQL.= $this->getInactiveOffices()." ORDER BY ".$_SESSION['storedOrderField']." ".$_SESSION['storedOrderDirection']." LIMIT 0,8;";

        //die($tempSQL);
        
        $querySQL = $tempSQL;
        
        $result = $this->query($querySQL);

        if (sizeof($result)>0) {
            if (sizeof($result)<8) {
                $diff = 8-count($result);
                $result = $this->getMoreOffers($diff,$result);
            }
            foreach ($result as $item) {
                $item->tanacsado = $this->getAgentName($item->tanacsado);
                $lista[] = $item;
            }
            return $result;
        }
        else {
            return false;
        }
    }


    public function exportdata() {
        $sqlStatement = "SELECT 
        hiper_ingatlan.id AS ocId, 
        hiper_ingatlan.regszam AS regszam, 
        hiper_ingatlan.iroda AS irodaKod, 
        hiper_ingatlan.ertekesites AS ertekesites, 
        hiper_ingatlan.letrehozva AS rogzites_datuma, 
        hiper_ingatlan.modositva AS modositas_datuma, 
        hiper_ingatlan.jogi_statusz AS jogiStatus, 
        hiper_ingatlan.tipus, 
        hiper_ingatlan.altipus AS epitesModja, 
        hiper_ingatlan.megye, 
        hiper_ingatlan.iranyitoszam, 
        hiper_ingatlan.telepules, 
        hiper_ingatlan.korzet AS varosresz, 
        hiper_ingatlan.iranyar, 
        hiper_ingatlan.meret_netto AS nettoMeret, 
        hiper_ingatlan.meret_brutto As bruttoMeret, 
        hiper_ingatlan.meret_telek AS telekMeret, 
        hiper_ingatlan.szoba_egesz AS egeszSzoba, 
        hiper_ingatlan.szoba_furdo AS furdoSzoba, 
        hiper_ingatlan.belmagassag, 
        hiper_ingatlan.futes, 
        hiper_ingatlan.ingatlan_allapota AS allapot, 
        hiper_ingatlan.tajolas, 
        hiper_ingatlan.epites_eve AS epitesEve, 
        hiper_ingatlan.csatorna, 
        hiper_ingatlan.gaz, 
        hiper_ingatlan.villany, 
        hiper_ingatlan.viz, 
        hiper_ingatlan.ferohely_garazs, 
        hiper_ingatlan.ferohely_nyitottbeallo, 
        hiper_ingatlan_text.szlogen AS szlogen, 
        hiper_ingatlan_text.leiras AS leiras, 
        hiper_ingatlan.meret_terasz as teraszMeret, 
        hiper_ingatlan.utcanev AS utca, 
        hiper_ingatlan.emelet AS emelet, 
        hiper_ingatlan.zaroszint AS zaroszint, 
        hiper_ingatlan.kilatas AS kilatas, 
        hiper_ingatlan.kozoskoltseg AS kozoskoltseg, 
        hiper_ingatlan.energetikai_tanusitvany AS energetikai_tanusitvany, 
        hiper_ingatlan.szoba_fel AS szoba_fel, 
        hiper_ingatlan.ferohely_fedettbeallo AS ferohely_fedettbeallo, 
        hiper_ingatlan.lift AS lift, 
        hiper_ingatlan.jutalek as jutalek, 
        hiper_ingatlan.statusz AS statusz, 
        hiper_iroda.id AS irodaId, 
        hiper_iroda.nev AS partneriroda_neve, 
        hiper_tranzakcio.ingatlan_sorsa AS ingatlan_sorsa, 
        hiper_tranzakcio.teljes_ar AS teljes_ar, 
        hiper_tranzakcio.szerzodeskotes_datuma AS szerzodeskotes_datuma FROM hiper_ingatlan 
        LEFT JOIN hiper_ingatlan_text ON hiper_ingatlan_text.regszam=hiper_ingatlan.regszam 
        LEFT JOIN hiper_tranzakcio ON hiper_tranzakcio.regszam=hiper_ingatlan.regszam 
        LEFT JOIN hiper_iroda ON hiper_iroda.azonosito=hiper_ingatlan.iroda 
        WHERE hiper_ingatlan.statusz='eladva' AND hiper_tranzakcio.ingatlan_sorsa='eladva' ORDER BY hiper_ingatlan.letrehozva ASC;";
        $result = $this->query($sqlStatement,true);

        foreach ($result as $row) {
            $row->ertekesites = $this->getDict('ertekesites',$row->ertekesites);
            $row->jogiStatus = $this->getDict('jogi_statusz',$row->jogiStatus);
            $row->tipus = $this->getDict('tipus',$row->tipus);
            $row->epitesModja = $this->getDict('altipus',$row->epitesModja);
            $row->belmagassag = $this->getDict('belmagassag',$row->belmagassag);
            $row->futes = $this->getDict('futes',$row->futes);
            $row->allapot = $this->getDict('ingatlan_allapota',$row->allapot);
            $row->tajolas = $this->getDict('tajolas',$row->tajolas);
            $row->zaroszint = $this->getDict('zaroszint',$row->zaroszint);
            $row->kilatas = $this->getDict('kilatas',$row->kilatas);
        }

        return $result;
    }


    public function exportActiveData() {
        $sqlStatement = "SELECT 
        hiper_ingatlan.id AS ocId, 
        hiper_ingatlan.regszam AS regszam, 
        hiper_ingatlan.iroda AS irodaKod, 
        hiper_ingatlan.ertekesites AS ertekesites, 
        hiper_ingatlan.letrehozva AS rogzites_datuma, 
        hiper_ingatlan.modositva AS modositas_datuma, 
        hiper_ingatlan.jogi_statusz AS jogiStatus, 
        hiper_ingatlan.tipus, 
        hiper_ingatlan.altipus AS epitesModja, 
        hiper_ingatlan.megye, 
        hiper_ingatlan.iranyitoszam, 
        hiper_ingatlan.telepules, 
        hiper_ingatlan.korzet AS varosresz, 
        hiper_ingatlan.iranyar, 
        hiper_ingatlan.meret_netto AS nettoMeret, 
        hiper_ingatlan.meret_brutto As bruttoMeret, 
        hiper_ingatlan.meret_telek AS telekMeret, 
        hiper_ingatlan.szoba_egesz AS egeszSzoba, 
        hiper_ingatlan.szoba_furdo AS furdoSzoba, 
        hiper_ingatlan.belmagassag, 
        hiper_ingatlan.futes, 
        hiper_ingatlan.ingatlan_allapota AS allapot, 
        hiper_ingatlan.tajolas, 
        hiper_ingatlan.epites_eve AS epitesEve, 
        hiper_ingatlan.csatorna, 
        hiper_ingatlan.gaz, 
        hiper_ingatlan.villany, 
        hiper_ingatlan.viz, 
        hiper_ingatlan.ferohely_garazs, 
        hiper_ingatlan.ferohely_nyitottbeallo, 
        hiper_ingatlan_text.szlogen AS szlogen, 
        hiper_ingatlan_text.leiras AS leiras, 
        hiper_ingatlan.meret_terasz as teraszMeret, 
        hiper_ingatlan.utcanev AS utca, 
        hiper_ingatlan.emelet AS emelet, 
        hiper_ingatlan.zaroszint AS zaroszint, 
        hiper_ingatlan.kilatas AS kilatas, 
        hiper_ingatlan.kozoskoltseg AS kozoskoltseg, 
        hiper_ingatlan.energetikai_tanusitvany AS energetikai_tanusitvany, 
        hiper_ingatlan.szoba_fel AS szoba_fel, 
        hiper_ingatlan.ferohely_fedettbeallo AS ferohely_fedettbeallo, 
        hiper_ingatlan.lift AS lift, 
        hiper_ingatlan.jutalek as jutalek, 
        hiper_ingatlan.statusz AS statusz, 
        hiper_iroda.id AS irodaId, 
        hiper_iroda.nev AS partneriroda_neve, 
        hiper_tranzakcio.ingatlan_sorsa AS ingatlan_sorsa, 
        hiper_tranzakcio.teljes_ar AS teljes_ar, 
        hiper_tranzakcio.szerzodeskotes_datuma AS szerzodeskotes_datuma FROM hiper_ingatlan 
        LEFT JOIN hiper_ingatlan_text ON hiper_ingatlan_text.regszam=hiper_ingatlan.regszam 
        LEFT JOIN hiper_tranzakcio ON hiper_tranzakcio.regszam=hiper_ingatlan.regszam 
        LEFT JOIN hiper_iroda ON hiper_iroda.azonosito=hiper_ingatlan.iroda 
        WHERE hiper_ingatlan.statusz='aktualis' ORDER BY hiper_ingatlan.letrehozva ASC;";
        $result = $this->query($sqlStatement,true);

        foreach ($result as $row) {
            $row->ertekesites = $this->getDict('ertekesites',$row->ertekesites);
            $row->jogiStatus = $this->getDict('jogi_statusz',$row->jogiStatus);
            $row->tipus = $this->getDict('tipus',$row->tipus);
            $row->epitesModja = $this->getDict('altipus',$row->epitesModja);
            $row->belmagassag = $this->getDict('belmagassag',$row->belmagassag);
            $row->futes = $this->getDict('futes',$row->futes);
            $row->allapot = $this->getDict('ingatlan_allapota',$row->allapot);
            $row->tajolas = $this->getDict('tajolas',$row->tajolas);
            $row->zaroszint = $this->getDict('zaroszint',$row->zaroszint);
            $row->kilatas = $this->getDict('kilatas',$row->kilatas);
        }

        return $result;
    }


    public function getDict($group,$value) {
        $trans = $this->query("SELECT * FROM `dictionary` WHERE `group`='".$group."' AND `config_key`='".$value."' AND `language`='hu';",true);
        return $trans[0]->config_value;
    }


}