<?php
class KnowledgeController extends Zend_Controller_Action 
{
    function indexAction()
    {
    }
    function preDispatch()
    {
    	$this->db=Zend_Registry::get('db');
    	$this->view->config = Zend_Registry::get('config');
    	$this->messenger=$this->_helper->getHelper('FlashMessenger');
    	$this->view->messages = $this->messenger->getMessages();
    	$this->view->theme=new Theme();
    }
    function datacenterAction()
    {
    	$siteid="e31f5ea7-a4af-4ae3-9ac1-1a84132c4338";//site uuid from geonetowrk
   	$sql="select * from mdref mr left join reference r on mr.refid=r.id where mr.uuid=? order by r.year desc, reference desc";
   	$sth = $this->db->prepare($sql);
	$sth->execute(array($siteid));
	$rows = $sth->fetchAll();
	$paginator = Zend_Paginator::factory($rows);
	$paginator->setCurrentPageNumber($this->_getParam('page'));
	$paginator->setItemCountPerPage(10);
	$paginator->setView($this->view);
	Zend_View_Helper_PaginationControl::setDefaultViewPartial('pagination_param.phtml');
	$this->view->paginator=$paginator;
    }
    function waterAction()
    {
   	$sql="select * from reference where id in (select refid from mdref where uuid in (select uuid from en.normalmetadata)) order by year desc, reference desc";
   	$sth = $this->db->prepare($sql);
	$sth->execute();
	$rows = $sth->fetchAll();
	$paginator = Zend_Paginator::factory($rows);
	$paginator->setCurrentPageNumber($this->_getParam('page'));
	$paginator->setItemCountPerPage(10);
	$paginator->setView($this->view);
	Zend_View_Helper_PaginationControl::setDefaultViewPartial('pagination_param.phtml');
	$this->view->paginator=$paginator;
    }

    function userAction()
    {
	$uuid=$this->_request->getParam('uuid');
	if (preg_match('/([a-f0-9]{8}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{12})/',$uuid))
	{
	    $sql="select uuid,title from metadata where uuid='$uuid'";
	    $sth = $this->db->prepare($sql);
	    $sth->execute();
	    $this->view->md = $sth->fetch();
	    $sql="select * from reference where id in (select refid from mdref where reftype=1 and uuid='$uuid') order by year desc, reference desc";
	} else 
	    $sql="select * from reference where id in (select refid from mdref where reftype=1 and uuid in (select uuid from normalmetadata)) order by year desc, reference desc";
	$sth = $this->db->prepare($sql);
	$sth->execute();
	$rows = $sth->fetchAll();
	$paginator = Zend_Paginator::factory($rows);
	$paginator->setCurrentPageNumber($this->_getParam('page'));
	$paginator->setItemCountPerPage(10);
	$paginator->setView($this->view);
	Zend_View_Helper_PaginationControl::setDefaultViewPartial('pagination_param.phtml');
	$this->view->paginator=$paginator;
    }   

    function authorAction()
    {
	$sql="select * from reference where id in (select refid from mdref where reftype=0 and uuid in (select uuid from normalmetadata)) order by year desc, reference desc";
	$sth = $this->db->prepare($sql);
	$sth->execute();
	$rows = $sth->fetchAll();
	$paginator = Zend_Paginator::factory($rows);
	$paginator->setCurrentPageNumber($this->_getParam('page'));
	$paginator->setItemCountPerPage(10);
	$paginator->setView($this->view);
	Zend_View_Helper_PaginationControl::setDefaultViewPartial('pagination_param.phtml');
	$this->view->paginator=$paginator;
    }

    function westplanAction()
    {
	$sql="select distinct array_to_string(array(select author from knl_author t where t.item_id=c.item_id order by place asc),'; ') as author,c.title,c.publisher,c.ts_created,c.ts_issued,c.item_id,c.url from knl_article c where c.url <>'' order by ts_created desc";
	$sth = $this->db->prepare($sql);
	$sth->execute();
	$rows = $sth->fetchAll();
	$paginator = Zend_Paginator::factory($rows);
	$paginator->setCurrentPageNumber($this->_getParam('page'));
	$paginator->setItemCountPerPage(10);
	$paginator->setView($this->view);
	Zend_View_Helper_PaginationControl::setDefaultViewPartial('pagination_param.phtml');
	$this->view->paginator=$paginator;
    }
    
    function searchAction()
    {
	$key=$this->_request->getParam('q');
	$author = (int)$this->_request->getParam('author');
	$place = (int)$this->_request->getParam('place');
	$source=$this->_request->getParam('searchsource');
	if(preg_match("/\"|'|<|>/",$key))
	{
	    $data=array('<'=>'&lt;','>'=>'&gt;',	"\'"=>'’',	"\""=>'”');
	    $patterns = array();
	    $replacements = array();
			foreach($data as $k=>$v)
			{
					$patterns[]='/'.$k.'/i';
					$replacements[]=$v;
			}
			ksort($patterns);
			ksort($replacements);
			$key=preg_replace($patterns, $replacements, $key);
		}
		
  	if (!empty($key)) {
    		$search=new SimpleSearch($key);
    		$where=$search->sql_expr(array("reference"));
    		$sql="select * from reference where ".$where." order by year desc, reference desc";
	} else if ($author && $place) {
	    $sql="select * from reference where id in (select a1.id from ref_author a1,ref_author a2 where a1.firstname=a2.firstname and a1.lastname=a2.lastname and a2.id=$author and a2.place=$place)";
	}
 		$sth = $this->db->prepare($sql);
		$sth->execute();
		$rows = $sth->fetchAll();
		$paginator = Zend_Paginator::factory($rows);
		$paginator->setCurrentPageNumber($this->_getParam('page'));
		$paginator->setItemCountPerPage(10);
		$paginator->setView($this->view);
		Zend_View_Helper_PaginationControl::setDefaultViewPartial('pagination_param.phtml');
		$this->view->paginator=$paginator;
    		$this->view->key=$key;
    		$this->view->source=$source;
    		$this->_helper->viewRenderer('search-data');
    	  	
    }
    
    function paperAction()
    {
        $id = (int)$this->_request->getParam('id');
	$sql="select * from reference where id=$id";
		$sth = $this->db->prepare($sql);
		$sth->execute();
		$this->view->paper = $sth->fetch();
		
		$sql="select * from ref_author where id=$id order by place";
		$sth = $this->db->prepare($sql);
		$sth->execute();
		$this->view->author = $sth->fetchAll();
		
		$sql="select * from ref_tag where id=$id";
		$sth = $this->db->prepare($sql);
		$sth->execute();
		$this->view->tag = $sth->fetchAll();
    }
}