<?php
namespace Westdc\Reference;

use Zend\ServiceManager\ServiceManager;
use Zend\ServiceManager\ServiceManagerAwareInterface;
use Westdc\EventModel\AbstractEventManager;
use Westdc\Reference\Listener\ReferenceListener;

class Reference extends AbstractEventManager implements ServiceManagerAwareInterface{

    protected $serviceManager;
	private $db;				//传入PDO对象.
	
	protected $events = NULL;
	public $table;
	public $keyword;
	public $order;
	public $sort = "DESC";
	public $field;
	
	public $reftype;
		
	function __construct($db = NULL,$mail = NULL)
	{

	}

    public function setServiceManager(ServiceManager $serviceManager)
    {
        $this->serviceManager = $serviceManager;

        $this->init();

        return $this;
    }

    private function init(){
        $dbService = $this->serviceManager->get('Db');
        $this->db = $dbService->getPdo();
        unset($dbService);

        $this->table = new \stdClass();
        $this->table->reference = "reference";
        $this->table->reference_author = "ref_author";
        $this->table->source = "source";
        $this->table->metadata_reference = "mdref";
        $this->table->reference_tag = "ref_tag";
        $this->table->metadata = "metadata";
        $this->table->attachments = "attachments";
    }

    /**
     * 添加或编辑文献
     * @param $data
     * @param int $id
     * @return bool|string
     */
	public function reference($data,$id = 0)
	{
		$params = compact('data');
        $results = $this->getEventManager()->trigger('submit.before', $this, $params);
		$cache_data = $results->bottom();
		
		if($cache_data !== true)
		{
			return $cache_data;
		}
		
        $results = $this->getEventManager()->trigger('submit.processData', $this, $params);
		$data = $results->bottom();

        $dbServices = $this->serviceManager->get('Db');
		$dbh = $dbServices->getDbh();
		
		if(empty($id))
		{
			$id = $dbh->insert($this->table->reference,$data,true);
		}else{
			if(!$dbh->update($this->table->reference,$data," id=$id ",true))
			{
				return "修改失败!请重试";
			}
		}
		
		if(!empty($id) && is_numeric($id))
		{
			return true;
		}else{
			return "修改失败";
		}
	}
	
	//上传文献PDF
	public function uploadReferencePdf($file,$autoread = false)
	{
        $configService = $this->serviceManager->get('ConfigService');
        $appConfig = $configService->get('application.ini');

		$fileUploadService = $this->serviceManager->get('File/Upload');
        $fileUploadService->attachDefaultListener();
        $fileUploadService->setParams(['file_type' => 'literature']);
		$file_info = $fileUploadService($file,$appConfig['reference_save_path'],"","",$fileUploadService::DATETIME_MODEL_Y);
		
		if(isset($file_info['error']) && !empty($file_info['error']))
		{
			return array("error" => $file_info['error']);
		}
		
		if($autoread)
		{
			$params = compact('file_data');
			$results = $this->events()->trigger('upload.insertToReferenceTable', $this, $params);
			$cache_data = $results->bottom();
			$file_info = array_merge($file_info,$cache_data);
		}
		
		return $file_info;
	}
	
	//通过文件名自动提取文章标题
	public function getReferenceTitleFromFilenName($filename)
	{
		$file = new Files();
		$title = str_replace( ".".$file->getFileTextExt($filename),"",$filename);
		return $title;
	}
	
	//删除文献文件
	public function deleteReferenceAttchment($attid)
	{
		if(empty($attid) || !is_numeric($attid))
		{
			return array("error"=>"参数错误");
		}
		
		$files = new Files();
		$status = $files->delete($attid);
		
		if($status !== true)
		{
			return array("error"=>$status);
		}else{
			return array("success"=>1);
		}
	}
	
	//所有文献
	public function fetchAll()
	{
		$wheresql = array();
		if(!empty($this->keyword))
		{
			$wheresql[] = " ({$this->table->reference}.title LIKE '%{$this->keyword}%' OR {$this->table->reference}.reference LIKE '%{$this->keyword}%') ";
		}
		
		if(!empty($this->field))
		{
			foreach($this->field as $k=>$v)
			{
				if(!empty($v))
				{
					if(!is_numeric($v)) $v="'{$v}'";
					$wheresql[] = " ({$this->table->reference}.{$k}={$v} ) ";
				}else{
					if(is_numeric($v))
						$wheresql[] = " ({$this->table->reference}.{$k} IS NULL OR {$this->table->reference}.{$k}=0 ) ";
					else
						$wheresql[] = " ({$this->table->reference}.{$k} IS NULL ) ";
				}//if(empty($v)
			}//foreach
		}

		if(count($wheresql)>0)
		{
			$wheresql = " WHERE ".join(" AND ",$wheresql);
		}else{
			$wheresql = "";
		}
		
		if(empty($this->order))
		{
			$order = "{$this->table->reference}.title";
		}else{
			$order = "{$this->table->reference}.{$this->order}";
		}
		
		$sql = "SELECT {$this->table->reference}.* FROM
				{$this->table->reference}
				$wheresql
				ORDER BY $order {$this->sort}";
		$rs = $this->db->query($sql);
		return $rs->fetchAll();
	}
	
	//获取专题数据的文献
	public function fetchThemeReferences($code)
	{
		$wheresql = array();
		//$wheresql[] = " s.code='$code' ";
		if(!empty($this->keyword))
		{
			$wheresql[] = " (ref.title iLIKE '%{$this->keyword}%' OR ref.reference iLIKE '%{$this->keyword}%') ";
		}
		if(count($wheresql)>0)
		{
			$wheresql = " and ".join(" AND ",$wheresql);
		}else{
			$wheresql = "";
		}
		
		if(empty($this->order))
		{
			$order = "ref.year,ref.title";
		}else{
			$order = "ref.{$this->order} {$this->sort}";
		}
		
		$sql="select distinct ref.* from {$this->table->reference} ref where ref.id in (select r.refid from mdref r
			left join datasource ds on r.uuid=ds.uuid left join {$this->table->source} s on s.id=ds.sourceid
			where s.code='$code')
			$wheresql 
			ORDER BY $order";
		$rs=$this->db->query($sql);
		return $rs->fetchAll();
	}

	//Get WestDC references
	public function fetchWestdcReferences()
	{
		$wheresql = array();
		$wheresql[]=" r.uuid='e31f5ea7-a4af-4ae3-9ac1-1a84132c4338' ";
		if(!empty($this->keyword))
		{
			$wheresql[] = " (ref.title LIKE '%{$this->keyword}%' OR ref.reference LIKE '%{$this->keyword}%') ";
		}
		if(count($wheresql)>0)
		{
			$wheresql = " WHERE ".join(" AND ",$wheresql);
		}else{
			$wheresql = "";
		}
		
		if(empty($this->order))
		{
			$order = "ref.year,ref.title";
		}else{
			$order = "ref.{$this->order} {$this->sort}";
		}
		
		$sql="select distinct ref.* from mdref r left join {$this->table->reference} ref on r.refid=ref.id 
			$wheresql
			ORDER BY $order";
		$rs=$this->db->query($sql);
		return $rs->fetchAll();
	}
	
	//Get references which need to deal with
	public function fetchTodoReferences()
	{
		$wheresql = array();
		$wheresql[]=" ref.id not in (select distinct refid from mdref) ";
		if(!empty($this->keyword))
		{
			$wheresql[] = " (ref.title LIKE '%{$this->keyword}%' OR ref.reference LIKE '%{$this->keyword}%') ";
		}
		if(count($wheresql)>0)
		{
			$wheresql = " WHERE ".join(" AND ",$wheresql);
		}else{
			$wheresql = "";
		}
		
		if(empty($this->order))
		{
			$order = "ref.year,ref.title";
		}else{
			$order = "ref.{$this->order} {$this->sort}";
		}
		
		$sql="select distinct ref.* from {$this->table->reference} ref 
			$wheresql
			ORDER BY $order";
		$rs=$this->db->query($sql);
		return $rs->fetchAll();
	}
	
	//Get data author references which need to deal with
	public function fetchAuthorReferences()
	{
		$wheresql = array();
		$wheresql[] = " ref.ris is NULL ";
		$wheresql[]=" ref.id in (select distinct refid from mdref where reftype=0) ";
		if(!empty($this->keyword))
		{
			$wheresql[] = " (ref.title LIKE '%{$this->keyword}%' OR ref.reference LIKE '%{$this->keyword}%') ";
		}
		if(count($wheresql)>0)
		{
			$wheresql = " WHERE ".join(" AND ",$wheresql);
		}else{
			$wheresql = "";
		}
		
		if(empty($this->order))
		{
			$order = "ref.year,ref.title";
		}else{
			$order = "ref.{$this->order} {$this->sort}";
		}
		
		$sql="select distinct ref.* from {$this->table->reference} ref 
			$wheresql
			ORDER BY $order";
		$rs=$this->db->query($sql);
		return $rs->fetchAll();
	}
	
	//Get references by data UUID
	public function fetchReferencesByUUID($uuid)
	{
		$wheresql = array();
		$wheresql[]=" r.uuid='$uuid' ";
		if(!empty($this->keyword))
		{
			$wheresql[] = " (ref.title LIKE '%{$this->keyword}%' OR ref.reference LIKE '%{$this->keyword}%') ";
		}
		if(count($wheresql)>0)
		{
			$wheresql = " WHERE ".join(" AND ",$wheresql);
		}else{
			$wheresql = "";
		}
		
		if(empty($this->order))
		{
			$order = "ref.year,ref.title";
		}else{
			$order = "ref.{$this->order} {$this->sort}";
		}
		
		$sql="select distinct ref.*,r.reftype,r.place,r.id as mrid from {$this->table->reference} ref left join {$this->table->metadata_reference} r on ref.id=r.refid
			$wheresql
			ORDER BY $order";
		$rs=$this->db->query($sql);
		return $rs->fetchAll();
	}

	//Get references with data UUID
	public function fetchReferencesWithUUID($uuid)
	{
		$wheresql = array();
		//$wheresql[]=" r.uuid='$uuid' ";
		if(!empty($this->keyword))
		{
			$wheresql[] = " (ref.title LIKE '%{$this->keyword}%' OR ref.reference LIKE '%{$this->keyword}%') ";
		}
		if(count($wheresql)>0)
		{
			$wheresql = " WHERE ".join(" AND ",$wheresql);
		}else{
			$wheresql = "";
		}
		
		if(empty($this->order))
		{
			$order = "ref.year,ref.title";
		}else{
			$order = "ref.{$this->order} {$this->sort}";
		}
		
		$sql="select distinct ref.*,r.reftype,r.place,r.id as mrid from {$this->table->reference} ref left join
			(select * from {$this->table->metadata_reference} r where uuid='$uuid') r on ref.id=r.refid
			$wheresql
			ORDER BY $order";
		$rs=$this->db->query($sql);
		return $rs->fetchAll();
	}
	
	//单条文献的信息
	public function getOneReferenceData($id)
	{
		if(empty($id) || !is_numeric($id))
		{
			return false;
		}
		
		$sql = "SELECT * FROM {$this->table->reference} WHERE id=$id LIMIT 1";
		$rs = $this->db->query($sql);
		$row = $rs->fetch();
		if ($row['attid'])
		{
			$fileService = $this->serviceManager->get('File');
			$attfile = $fileService->get($row['attid']);
		
			$row['file'] = $attfile;
		}
		return $row;
	}
	
	//获得reference类型的附件
	public function getReferenceFiles()
	{
        $wheresql = [
            "att.filetype='literature'",
        ];

        if(!empty($this->keyword))
        {
            $wheresql[] = " (att.realname LIKE '%{$this->keyword}%' OR att.filename LIKE '%{$this->keyword}%') ";
        }

        $wheresql = " WHERE ".join(" AND ",$wheresql);

		$sql = "SELECT att.*,ref.attid,ref.id as refid FROM {$this->table->attachments} att 
				LEFT JOIN {$this->table->reference} ref ON att.id=ref.attid
				$wheresql";
		$rs = $this->db->query($sql);
		$rows = $rs->fetchAll();
		return $rows;
	}
	
	//删除文献
	public function deleteReference($id,$delete_att = false)
	{
		if(empty($id) || !is_numeric($id))
		{
			return false;
		}
		
		if($delete_att == false)
		{
			$sql = "DELETE FROM {$this->table->reference} WHERE id=$id";
			@$this->db->exec($sql);
			
			$sql = "DELETE FROM {$this->table->metadata_reference} WHERE refid=$id";
			@$this->db->exec($sql);
			
			$this->deleteReferenceAuthor($id);
			$this->deleteReferenceTag($id);
			
			return true;
		}else{
			
		}
	}
	
	//删除作者信息
	public function deleteReferenceAuthor($id)
	{
		if(empty($id) || !is_numeric($id))
		{
			return false;
		}
		
		$sql = "DELETE FROM {$this->table->reference_author} WHERE id=$id ";
		return $this->db->exec($sql);
	}
	
	//删除标签信息
	public function deleteReferenceTag($id)
	{
		if(empty($id) || !is_numeric($id))
		{
			return false;
		}
		
		$sql = "DELETE FROM {$this->table->reference_tag} WHERE id=$id ";
		return $this->db->exec($sql);
	}
	
	//建立文献与数据的关系
	public function createRelationFromReferenceToData($refid,$uuid,$reftype,$place,$id = NULL)
	{
		if(empty($refid) || !is_numeric($refid))
		{
			return "参数错误";
		}

        $tools = $this->serviceManager->get('Tools');
		if(!$tools->isUuid($uuid))
		{
			return "参数错误";
		}
		
		$data = array(
			'uuid'=>$uuid,
			'refid'=>$refid,
			'reftype'=>$reftype,
			'place'=>$place
		);

        $dbService = $this->serviceManager->get('Db');
        $dbh = $dbService->getDbh();
		
		if(empty($id))
		{
			$id = $dbh->insert($this->table->metadata_reference,$data,true);
			
			if(is_numeric($id))
			{
				return $id;
			}else{
				return "关系写入失败，请检查是否已经存在";
			}
		}else{
			$status = $dbh->update($this->table->metadata_reference,$data," id=$id ");
			
			if($status === true)
			{
				return $id;
			}else{
				return "修改失败";
			}
		}		
	}
	
	//获得某个文献关联的数据 (根据文献获得数据)
	public function getDataByReference($id)
	{
		if(empty($id) || !is_numeric($id))
		{
			return "参数错误";
		}
		
		$sql = "SELECT mr.id,mr.refid,mr.reftype,mr.place,md.title,md.uuid FROM {$this->table->metadata_reference} mr
				LEFT JOIN {$this->table->metadata} md ON mr.uuid=md.uuid
				WHERE mr.refid=$id
				ORDER BY mr.place ASC";
				
		$rs = $this->db->query($sql);
		$rows = $rs->fetchAll();
		return $rows;
	}
	
	//获得某个数据关联的文献 (根据数据获得文献)
	public function getReferenceByData($uuid)
	{
		if(!view::isUuid($uuid))
		{
			return "参数错误";
		}
		
		$sql = "SELECT mr.reftype,mr.place,md.title,md.uuid FROM {$this->table->metadata_reference} mr
				LEFT JOIN {$this->table->metadata} md
				WHERE mr.uuid = $uuid";
				
		$rs = $this->db->query($sql);
		$rows = $rs->fetchAll();
		return $rows;
	}
	
	//文献类型
	public function referenceType()
	{
		return array(
			0 => '相关文献',//作者建议的文献或数据中心建议的文献
			1 => '施引文献',
			2 => '参考文献',
			3 => '多篇文献',
			4 => '专题文献'
		);
	}
	
	//写入数据文献
	public function makeMdref($data,$id = NULL)
	{
        $results = $this->getEventManager()->trigger('mdref.pre', $this, compact('data'));
		$cache_data = $results->bottom();
		
		if($cache_data !== true)
		{
			return $cache_data;
		}
		
		$results = $this->getEventManager()->trigger('mdref.processData', $this, compact('data'));
		$data = $results->last();
		
		$id = $this->createRelationFromReferenceToData($data['refid'],$data['uuid'],$data['reftype'],$data['place'],$id);
		
		if(is_numeric($id))
		{
			return true;
		}else{
			return $id;
		}
	}
	
	//删除数据文献
	public function delMdref($id)
	{
		if(empty($id) || !is_numeric($id))
		{
			return "参数错误";
		}
		
		$sql = "DELETE FROM {$this->table->metadata_reference} WHERE id=$id";
		if($this->db->exec($sql))
		{
			return true;
		}else{
			return "删除失败";
		}
	}
	
	//按年份获得文献数量
	public function countByYear()
	{
		$sql = "SELECT count(id) as num,year FROM {$this->table->reference} GROUP BY year ORDER BY year DESC";
		$rs = $this->db->query($sql);
		$rows = $rs->fetchAll();
		
		return $rows;
	}
	
	//获得作者
	public function getAuthorByReference($id,$join = false)
	{
		if(is_numeric($id))
		{
			$sql = "SELECT * FROM {$this->table->reference_author} WHERE id=$id ORDER BY place ASC";
			$rs = $this->db->query($sql);
			if(!$join)
			{
				return $rs->fetchAll();
			}else{
				foreach($rows = $rs->fetchAll() as $k=>$v)
				{
					$rows[$k] = (string)$v['firstname'].$v['lastname'];
				}
				return $rows;
			}
		}
		
		if(is_array($id))
		{
			$sql = "SELECT * FROM {$this->table->reference_author} WHERE id IN (".join(",",$id).")";
			$rs = $this->db->query($sql);
			return $rs->fetchAll();
		}
		
		return;
	}
	
	//获得标签
	public function getTagsByReference($id,$single = false)
	{
		if(is_numeric($id))
		{
			$sql = "SELECT * FROM {$this->table->reference_tag} WHERE id=$id";
			$rs = $this->db->query($sql);
			if(!$single)
			{
				return $rs->fetchAll();
			}else{
				foreach($rows = $rs->fetchAll() as $k=>$v)
				{
					$rows[$k] = (string)$v['tag'];
				}
				return $rows;
			}
		}
		
		return;
	}
	
	//Get data author references
	//$ordertype 0: by data, 1: by literature
	public function getReferencesByAuthor($uid,$ordertype=0,$reftype=0)
	{
		$wheresql = array();
		$wheresql[] = " a.userid=$uid ";
		$wheresql[] = " a.status=1 ";
		if(!empty($this->keyword))
		{
			if($ordertype==0)
			{
				$wheresql[] = " (md.title LIKE '%{$this->keyword}%' OR md.description LIKE '%{$this->keyword}%') ";
			}else{
				$wheresql[] = " (ref.title LIKE '%{$this->keyword}%' OR ref.reference LIKE '%{$this->keyword}%') ";
			}			
		}
		if(count($wheresql)>0)
		{
			$wheresql = " WHERE ".join(" AND ",$wheresql);
		}else{
			$wheresql = "";
		}
		
		if($ordertype==0)
		{
			$order = "md.title";
			$sql="SELECT md.title,md.uuid,count(mr.id) as c FROM metadata md
					LEFT JOIN (select * from mdref where reftype=$reftype) mr ON md.uuid=mr.uuid
					LEFT JOIN mdauthor a ON md.uuid=a.uuid
					left join reference ref on mr.refid=ref.id
					$wheresql
					group by md.title,md.uuid
			ORDER BY c desc,$order";			
		}else{
			$order = "ref.title";
			$sql="SELECT ref.*,count(mr.uuid) as c FROM metadata md
					LEFT JOIN mdref mr ON md.uuid=mr.uuid
					LEFT JOIN mdauthor a ON md.uuid=a.uuid
					left join reference ref on mr.refid=ref.id
					$wheresql and mr.reftype=$reftype
					group by ref.id
			ORDER BY c desc,$order";			
		}
		$rs=$this->db->query($sql);
		return $rs->fetchAll();
	}
	
	//Get author references by data uuid
	public function getReferencesByAuthorUUID($uid,$uuid)
	{
		$wheresql = array();
		$wheresql[] = " a.userid=$uid ";
		$wheresql[] = " a.status=1 ";
		$wheresql[] = " mr.uuid='$uuid' ";
		if(!empty($this->keyword))
		{
			$wheresql[] = " (ref.title LIKE '%{$this->keyword}%' OR ref.reference LIKE '%{$this->keyword}%') ";
		}
		if(count($wheresql)>0)
		{
			$wheresql = " WHERE ".join(" AND ",$wheresql);
		}else{
			$wheresql = "";
		}
		
		$order = "ref.title";
		$sql="SELECT md.title as mdtitle,md.uuid,ref.id,ref.reference,ref.link,mr.place,mr.id as mrid,mr.reftype FROM mdref mr
				LEFT JOIN metadata md ON md.uuid=mr.uuid
				LEFT JOIN mdauthor a ON md.uuid=a.uuid
				left join reference ref on mr.refid=ref.id
				$wheresql
				order by mr.reftype,mr.place ASC,ref.id DESC,md.ts_created desc";
		$rs=$this->db->query($sql);
		return $rs->fetchAll();
	}
	
	//数据作者修改推荐文献的排序
	public function changeOrderByAuthor($uid,$id,$order)
	{
		$sql="update mdref set place=$order where id=$id and uuid in
			(select uuid from mdauthor where status=1 and userid=$uid)";
		if($this->db->exec($sql))
		{
			return true;
		}else{
			return false;
		}
	}
	
	//数据作者移除推荐文献
	public function removeReferenceByAuthor($uid,$id)
	{
		$sql = "DELETE FROM mdref WHERE id=$id and uuid in (select uuid from mdauthor where userid=$uid AND status=1)";
		if($this->db->exec($sql))
		{
			return true;
		}else{
			return false;
		}
	}
	
	//数据作者添加推荐文献
	public function insertMdrefByAuthor($uid,$refid,$uuid,$place,$reftype=0)
	{
		$sql="select * from mdauthor where status=1 and uuid='$uuid' and userid=$uid";
		$rs=$this->db->fetchRow($sql);
		if ($rs)
		{
			$id = $this->createRelationFromReferenceToData($refid,$uuid,$reftype,$place);
			if(is_numeric($id))
			{
				return true;
			}else{
				return $id;
			}
		} else {
			return false;
		}
	}
}