<?php

/* * *************************************************************
 * Catabatic Technology Pvt. Ltd.
 * File Name : Cms.php
 * File Description : Cms Model
 * Created By : Praveen Kumar
 * Created Date: 05-September-2014
 * ************************************************************* */

class Application_Model_Cms extends Zend_Db_Table_Abstract {

    function __construct() {
        
    }

    /**
     * getPageContentByPageUrl() method is used to get page content by page url
     * @param page_url string
     * @return object 
     */
    public function getPageContentByPageUrl($page_url) {
        $db = Zend_Db_Table::getDefaultAdapter();
        $select = $db->select()
                ->from(array("tbl_static_pages"), array('page_name', 'page_key', 'page_title', 'page_description', 'background_image', 'content_template_type', 'meta_title', 'meta_keywords', 'meta_description', 'content_menu_link_id'))
                ->where('status =?', 'Activate')
                ->where('page_key =?', $page_url)
                ->where('is_publish =?', 1);
        $result = $db->fetchRow($select);
        return $result;
    }

    /**
     * getAllContentMenuList() method is used to get all content menu list by page id
     * @param content_menu_link_id string
     * @return object 
     */
    public function getAllContentMenuList($content_menu_link_id) {
        $db = Zend_Db_Table::getDefaultAdapter();
        $content_menu_link_id_arr = @explode(",", $content_menu_link_id);
        $select = $db->select()
                ->from(array("hierarchy_menu"), array('id', 'parent_id', 'level', 'url', 'external_url'))
                ->where('status =?', 1)
                ->where('id IN (?)', $content_menu_link_id_arr)
                ->order(array('orderOfAppearance', 'level DESC'));
        $result = $db->fetchAll($select);
        return $result;
    }

    /**
     * getBoardListByBoardType() method is used to get page content by page url
     * @param page_url string
     * @return object
     */
    public function getBoardListByBoardType($board_type) {
        $db = Zend_Db_Table::getDefaultAdapter();
        $select = $db->select()
                ->from(array("tbl_board_directors"), array('id', 'board_type', 'name', 'designation', 'profile_image', 'profile_details'))
                ->where('status =?', 'Activate')
                ->where('board_type =?', $board_type)
                ->order('orderOfAppearance');
        $result = $db->fetchAll($select);
        return $result;
    }

    /**
     * getBoardDetailsByBoardType() method is used to get board details
     * @param page_url string
     * @return object
     */
    public function getBoardDetailsByBoardType($board_type, $ppage_url_int) {
        $db = Zend_Db_Table::getDefaultAdapter();
        $select = $db->select()
                ->from(array("tbl_board_directors"), array('id', 'board_type', 'name', 'designation', 'profile_image', 'profile_details'))
                ->where('status =?', 'Activate')
                ->where('board_type =?', $board_type)
                ->where('id =?', $ppage_url_int);
        $result = $db->fetchRow($select);
        return $result;
    }

    /**
     * getSearchListByKeyword() method is used to get all content by search keyword
     * @param page_url string
     * @return object
     */
    public function getSearchListByKeyword($search_keyword_str) {
        $db = Zend_Db_Table::getDefaultAdapter();
        $search_keyword = trim($search_keyword_str);
        $select = "SELECT hm.id,spt.id, spt.page_name, spt.page_key, spt.page_title, spt.page_description, spt.meta_title 
                      FROM `hierarchy_menu` AS hm 
                      JOIN `tbl_static_pages` AS spt ON (spt.page_key=hm.url) 
                      WHERE hm.status='1' and spt.status='Activate' and spt.is_publish='1' 
                      and (hm.level LIKE '%$search_keyword%' || spt.page_description LIKE '%$search_keyword%')  ";
        $result = $db->fetchAll($select);
        return $result;
    }

    /**
     * getPageDetails() method is used to get board details
     * @param page_url string
     * @return object
     */
    public function getPageDetails() {
        $db = Zend_Db_Table::getDefaultAdapter();
        $select = $db->select()
                ->from(array("tbl_user"), array('page_title', 'page_description', 'page_keywords'))
                ->where('status =?', 'Activate')
                ->where('type =?', 'superadmin')
                ->where('id =?', 1);
        $result = $db->fetchRow($select);
        return $result;
    }

    /**
     * getForexratesDetails() method is used to get forex rates details
     * @param email string
     * @return object 
     */
    public function getForexratesDetails() {
        $dbtable = Zend_Db_Table::getDefaultAdapter();
        $select = $dbtable->select()
                ->from("tbl_forex_rates")
                ->where("status =?", 'Active')
                ->order("id DESC")
                ->limit(1, 0);
        $result = $dbtable->fetchRow($select);
        return $result;
    }

    /**
     * getPressReleaseList() method is used to get page content by page url
     * @param page_url string
     * @return object
     */
    public function getPressReleaseList($type = null,$order = null) {
        $db = Zend_Db_Table::getDefaultAdapter();
        $select = $db->select()
                ->from(array("tbl_press_releases"))
                ->where('status =?', 'Activate')
                ->where('media_type =?', $type)
                ->order($order);
        $result = $db->fetchAll($select);
        return $result;
    }

    /**
     * getContactDetails() method is used to get board details
     * @param page_url string
     * @return object
     */
    public function getContactDetails() {
        $db = Zend_Db_Table::getDefaultAdapter();
        $select = $db->select()
                ->from(array("tbl_contact"), array('firstName', 'lastName', 'email', 'designation'))
                ->where('status =?', 'Activate')
                ->where('id =?', 1);
        $result = $db->fetchRow($select);
        return $result;
    }

    /**
     * add() method is used to add lms data
     * @param array
     * @return true 
     */
    public function add($addData = array()) { //print_r($addData); die;
        $dbtable = new Zend_Db_Table('tbl_quick_message');
        $dbtable->insert($addData);
    }

    /**
     * getFinalResultList() method is used to get page content by page url
     * @param page_url string
     * @return object
     */
    public function getFinalResultList($type = NULL) {
        $db = Zend_Db_Table::getDefaultAdapter();
        $query = "SELECT ir.id, ir.type, title, investorRelationType, subTitle, Q, Q1, Q2, Q3, Q4
FROM `tbl_investor_relations` ir, tbl_financial_year_manager fym
WHERE ir.financial_year_manager_id = fym.id  AND ir.investorRelationType = '" . $type . "' ORDER BY ir.orderOfAppearance";
//        echo $query; die;
        $result = $db->fetchAll($query);
        return $result;
    }

    /**
     * getFinalResultGroup() method is used to get page content by page url
     * @param page_url string
     * @return object
     */
    public function getFinalResultGroup($type = NULL) {
        $db = Zend_Db_Table::getDefaultAdapter();
        if($type == 'codes-policies'){
         $order = "`ir`.`id` ASC";   
        } else{
        $order = "`ir`.`id` DESC";    
        }
        $query = "SELECT ir.id, ir.type, title, investorRelationType, subTitle, Q, Q1, Q2, Q3, Q4
FROM `tbl_investor_relations` ir, tbl_financial_year_manager fym
WHERE ir.financial_year_manager_id = fym.id AND ir.investorRelationType = '" . $type . "' 
             Group BY ir.financial_year_manager_id order by " . $order ;
       // echo $query; die;
        $result = $db->fetchAll($query);
        return $result;
    }

    public function filesize_formatted($path) {
//        return $path;die;
        $size = filesize($path);
        $units = array('B', 'KB', 'MB', 'GB', 'TB', 'PB', 'EB', 'ZB', 'YB');
        $power = $size > 0 ? floor(log($size, 1024)) : 0;
        return number_format($size / pow(1024, $power)) . ' ' . $units[$power];
    }

    /**
     * getSearchResultTypeId() method is used to get searh result by year and type
     * @param page_url string
     * @return object
     */
    public function getSearchResultTypeId($type = null, $title = null) {
        $db = Zend_Db_Table::getDefaultAdapter();
        if($title=='all')
        {
          $cond="";
        } else {
          $cond = "AND title = '" . $title . "'";
        }
        $query = "SELECT ir.id, ir.type, title, investorRelationType, subTitle, Q, Q1, Q2, Q3, Q4
FROM `tbl_investor_relations` ir, tbl_financial_year_manager fym
WHERE ir.financial_year_manager_id = fym.id AND ir.investorRelationType = '" . $type . "' $cond ORDER BY ir.id DESC ";
        //echo $query; die;
        $result = $db->fetchAll($query);
        return $result;
    }

    /**
     * getCorporateResultList() method is used to get page content by page url
     * @param page_url string
     * @return object
     */
    public function getCorporateResultList($type = NULL) {
        if($type == 'debt-financing' || $type == 'equity-investments'){
         $order = 'id ASC ';   
        } else{
        $order = 'id desc ';    
        }
        $db = Zend_Db_Table::getDefaultAdapter();
        $query = "SELECT id, type,investorRelationType, subTitle, Q, Q1, Q2, Q3, Q4
FROM `tbl_investor_relations` 
WHERE investorRelationType = '" . $type . "' ORDER BY  '" . $order . "'";
        //echo $query; die;
        $result = $db->fetchAll($query);
        return $result;
    }

    /* getMediaList() method is used to get page content by page url
     * @param page_url string
     * @return object
     */

    public function getMediaList($type = NULL) {
        $db = Zend_Db_Table::getDefaultAdapter();
        $query = "SELECT pr.id, pr.type,media_type,externalLink,publication,press_release_short_description,press_release_date,publication,file_name, title FROM `tbl_press_releases` pr, tbl_financial_year_manager fym WHERE pr.financial_year_manager_id = fym.id AND pr.status = 'Activate' AND pr.media_type= '" . $type . "'  order by pr.id desc  ";
//        echo $query; die;
        $result = $db->fetchAll($query);
        return $result;
    }

    /**
     * getMediaGroup() method is used to get page content by page url
     * @param page_url string
     * @return object
     */
    public function getMediaGroup($type = NULL) {
        $db = Zend_Db_Table::getDefaultAdapter();

        $query = "SELECT pr.id, pr.type,media_type,externalLink,publication,press_release_short_description,press_release_date,publication,file_name, title FROM `tbl_press_releases` pr, tbl_financial_year_manager fym WHERE pr.financial_year_manager_id = fym.id AND pr.status = 'Activate' AND pr.media_type= '" . $type . "' Group BY pr.financial_year_manager_id order by pr.id desc  ";
        //echo $query; die;
        $result = $db->fetchAll($query);
        return $result;
    }
    
    
        /**
     * getSearchMediaTypeId() method is used to get searh result by year and type
     * @param page_url string
     * @return object
     */
    public function getSearchMediaTypeId($type, $title) {
        
        $db = Zend_Db_Table::getDefaultAdapter();
        $cond = "";
        if($title=="all")
        {
          $cond = "";
        } else {
          $cond = "AND press_release_date LIKE '%$title%'";  
        }
        $query = "SELECT * FROM `tbl_press_releases` WHERE status = 'Activate' AND media_type= '" . $type . "' $cond order by id desc";
        $result = $db->fetchAll($query);
        return $result;
    }
    
    
   

}