westdc-zf1/application/default/controllers/IndexController.php

<?php

class IndexController extends Zend_Controller_Action
{
    function preDispatch()
    {
        $this->view->config = Zend_Registry::get('config');
        $this->db = Zend_Registry::get('db');
        $this->view->theme = new Theme();
        $this->view->main_nav_pageID = "index";
    }

    function indexAction()
    {
        $this->_helper->layout->setLayout('layout-sanji');
        //统计数据
        $sql = 'select (select count(*) from users) as usernum,(select count(*) from metadata) as metanum,(select count(*) from offlineapp where ts_approved is not null ) as offlinenum';
        $row = $this->db->fetchRow($sql);
        $this->view->usernum = $row['usernum'];
        $this->view->metanum = $row['metanum'];
        $this->view->offlinenum = $row['offlinenum'];
        /*$sql='select count(*) from metadata';
        $row=$this->db->fetchRow($sql);
        $this->view->metanum=$row['count'];*/
        $sql = "select sum(filesize) as sum,datatype from metadata  group by datatype";
        $rows = $this->db->fetchAll($sql);
        foreach ($rows as $row) {
            if ($row['datatype'])
                $this->view->offlinesize = $row['sum'];
            else
                $this->view->onlinesize = $row['sum'];
        }
        //特色推荐
        $sql = "select * from datafeature order by id desc";
        $this->view->feature = $this->db->fetchRow($sql);
        //下载最多数据(top 5)
        $sql = "select uuid,title,id from sjynpmetadata where id in (select m.id from normalmetadata m left join dataorder d on m.uuid=d.uuid group by m.id order by count(d.uuid) desc limit 8)";
        $this->db->setFetchMode(Zend_Db::FETCH_OBJ);
        $this->view->mdtop = $this->db->fetchAll($sql);
        $sql = "select uuid,title,id,description from sjynpmetadata where id in (select m.id from normalmetadata m left join dataorder d on m.uuid=d.uuid group by m.id order by id desc limit 4)";
        $this->view->offlinemdtop = $this->db->fetchAll($sql);
        $sql = "select uuid,title,id,description from sjynpmetadata limit 4";
        $this->view->latestdata = $this->db->fetchAll($sql);
        $sql = "select m.uuid,m.title,m.id,m.description from sjynpmetadata m left join thumbnail t on m.id=t.id where length(t.data)>2 order by random()";
        $this->view->mdrandom = $this->db->fetchRow($sql);
        //搜索
        $this->view->addHelperPath('helper', 'Zend_View_Helper_');
        $searchform = new SearchForm();
        //$searchform->removeElement('submit');
        $this->view->searchform = $searchform;

        $News = new Archive($this->db);

        $time = date("Y-m-d H:i:s", time());
        $sql = "SELECT * FROM archive WHERE is_pub=true AND ts_published<'$time' AND image IS NOT NULL AND image != '' and id in (select ar_catalog.aid from ar_category left join ar_catalog on ar_category.id=ar_catalog.cid where ar_category.code='banner') order by ts_published asc LIMIT 5";
        $sth = $this->db->query($sql);
        $rows = $sth->fetchAll(PDO::FETCH_BOTH);

        foreach ($rows as $k => $v) {
            $url = $News->getArchiveUrlByCid($v['id']);
            $rows[$k]['url'] = $url['archive_url'];
        }

        $this->view->news = $rows;

        //$time = date("Y-m-d H:i:s",time());
        $sql = "SELECT * FROM archive WHERE is_pub=true AND ts_published<now() AND image IS NOT NULL AND image != '' and id in (select ar_catalog.aid from ar_catalog left join ar_category on ar_catalog.cid=ar_category.id where ar_category.id in(4,17)) order by ts_published desc LIMIT 12 ";
        $sth = $this->db->query($sql);
        $rows = $sth->fetchAll(PDO::FETCH_BOTH);

        foreach ($rows as $k => $v) {
            $url = $News->getArchiveUrlByCid($v['id']);
            $rows[$k]['url'] = $url['archive_url'];
            $rows[$k]['type_title'] = $url['type_title'];
            $rows[$k]['type_url'] = $url['type_url'];
        }

        $this->view->list_news = $rows;

        $sql = "select d.*,m.ts_published as publish_year from datadoi d left join sjynpmetadata m on d.uuid=m.uuid where length(d.title)>1 and d.ts_published is not null order by ts_published desc limit 5";
        $sth = $this->db->query($sql);
        $this->view->dois = $sth->fetchAll();

    }

}