<?php
/************************************************************************
 * This file is part of EspoCRM.
 *
 * EspoCRM - Open Source CRM application.
 * Copyright (C) 2014  Yuri Kuznetsov, Taras Machyshyn, Oleksiy Avramenko
 * Website: http://www.espocrm.com
 *
 * EspoCRM is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * EspoCRM is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with EspoCRM. If not, see http://www.gnu.org/licenses/.
 ************************************************************************/ 

namespace Espo\Modules\Crm\Services;

use \Espo\Core\Exceptions\Error;
use \PDO;

class Activities extends \Espo\Core\Services\Base
{
	protected $dependencies = array(
		'entityManager',
		'user',
		'metadata',
		'acl'
	);
	
	protected function getPDO()
	{
		return $this->getEntityManager()->getPDO();
	}

	protected function getEntityManager()
	{
		return $this->injections['entityManager'];
	}

	protected function getUser()
	{
		return $this->injections['user'];
	}
	
	protected function getAcl()
	{
		return $this->injections['acl'];
	}
	
	protected function getMetadata()
	{
		return $this->injections['metadata'];
	}
	
	protected function isPerson($scope)
	{
		return in_array($scope, array('Contact', 'Lead', 'User'));
	}	
	
	protected function getMeetingQuery($scope, $id, $op = 'IN', $notIn = array())
	{
		$qu = "
			SELECT meeting.id AS 'id', meeting.name AS 'name', meeting.date_start AS 'dateStart', meeting.date_end AS 'dateEnd', 'Meeting' AS '_scope',
			       meeting.assigned_user_id AS assignedUserId, TRIM(CONCAT(user.first_name, ' ', user.last_name)) AS assignedUserName,
			       meeting.parent_type AS 'parentType', meeting.parent_id AS 'parentId', meeting.status AS status, meeting.created_at AS createdAt
			FROM `meeting`
			LEFT JOIN `user` ON user.id = meeting.assigned_user_id
		";
		
		if ($this->isPerson($scope)) {
			switch ($scope) {
				case 'Contact':
					$joinTable = 'contact_meeting';
					$key = 'contact_id';
					break;
				case 'Lead':
					$joinTable = 'lead_meeting';
					$key = 'lead_id';
					break;
				case 'User':
					$joinTable = 'meeting_user';
					$key = 'user_id';
					break;					
			}
			$qu .= "
				JOIN `{$joinTable}` ON meeting.id = {$joinTable}.meeting_id AND {$joinTable}.deleted = 0 AND {$joinTable}.{$key} = ".$this->getPDO()->quote($id)."
			";
		}
		$qu .= "
			WHERE meeting.deleted = 0
		";
		
		if (!$this->isPerson($scope)) {
			$qu .= "
				AND meeting.parent_type = ".$this->getPDO()->quote($scope)." AND meeting.parent_id = ".$this->getPDO()->quote($id)."
			";		
		}
		
		if (!empty($notIn)) {
			$qu .= "
				AND meeting.status {$op} ('". implode("', '", $notIn) . "')
			";
		}
		
		if ($scope == 'Account') {
			$tQu = $qu;			
			$qu = "
				SELECT meeting.id AS 'id', meeting.name AS 'name', meeting.date_start AS 'dateStart', meeting.date_end AS 'dateEnd', 'Meeting' AS '_scope',
					   meeting.assigned_user_id AS assignedUserId, TRIM(CONCAT(user.first_name, ' ', user.last_name)) AS assignedUserName,
					   meeting.parent_type AS 'parentType', meeting.parent_id AS 'parentId', meeting.status AS status, meeting.created_at AS createdAt
				FROM `meeting`
				LEFT JOIN `user` ON user.id = meeting.assigned_user_id
				JOIN contact_meeting ON 
					meeting.id = contact_meeting.meeting_id AND 
					contact_meeting.deleted = 0
				JOIN contact ON 
					contact_meeting.contact_id = contact.id AND					
					contact.deleted = 0
				WHERE 
					meeting.deleted = 0 AND
					meeting.status {$op} ('". implode("', '", $notIn) . "') AND
					contact.account_id = ".$this->getPDO()->quote($id)."					
			";			
			$qu = "				
				{$tQu}
				UNION 
				{$qu}				
			";
		}

		return $qu;
	}
	
	protected function getCallQuery($scope, $id, $op = 'IN', $notIn = array())
	{
		$qu = "
			SELECT call.id AS 'id', call.name AS 'name', call.date_start AS 'dateStart', call.date_end AS 'dateEnd', 'Call' AS '_scope',
			       call.assigned_user_id AS assignedUserId, TRIM(CONCAT(user.first_name, ' ', user.last_name)) AS assignedUserName,
			       call.parent_type AS 'parentType', call.parent_id AS 'parentId', call.status AS status, call.created_at AS createdAt
			FROM `call`
			LEFT JOIN `user` ON user.id = call.assigned_user_id
		";
		
		if ($this->isPerson($scope)) {
			switch ($scope) {
				case 'Contact':
					$joinTable = 'call_contact';
					$key = 'contact_id';
					break;
				case 'Lead':
					$joinTable = 'call_lead';
					$key = 'lead_id';
					break;
				case 'User':
					$joinTable = 'call_user';
					$key = 'user_id';
					break;					
			}
			$qu .= "
				JOIN `{$joinTable}` ON call.id = {$joinTable}.call_id AND {$joinTable}.deleted = 0 AND {$joinTable}.{$key} = ".$this->getPDO()->quote($id)."
			";
		}
		$qu .= "
			WHERE call.deleted = 0
		";
		
		if (!$this->isPerson($scope)) {
			$qu .= "
				AND call.parent_type = ".$this->getPDO()->quote($scope)." AND call.parent_id = ".$this->getPDO()->quote($id)."
			";		
		}
		
		if (!empty($notIn)) {
			$qu .= "
				AND call.status {$op} ('". implode("', '", $notIn) . "')
			";
		}
		
		if ($scope == 'Account') {
			$tQu = $qu;			
			$qu = "
				SELECT call.id AS 'id', call.name AS 'name', call.date_start AS 'dateStart', call.date_end AS 'dateEnd', 'Call' AS '_scope',
					   call.assigned_user_id AS assignedUserId, TRIM(CONCAT(user.first_name, ' ', user.last_name)) AS assignedUserName,
					   call.parent_type AS 'parentType', call.parent_id AS 'parentId', call.status AS status, call.created_at AS createdAt
				FROM `call`
				LEFT JOIN `user` ON user.id = call.assigned_user_id
				JOIN call_contact ON 
					call.id = call_contact.call_id AND 
					call_contact.deleted = 0
				JOIN contact ON 
					call_contact.contact_id = contact.id AND					
					contact.deleted = 0
				WHERE 
					call.deleted = 0 AND
					call.status {$op} ('". implode("', '", $notIn) . "') AND
					contact.account_id = ".$this->getPDO()->quote($id)."					
			";			
			$qu = "				
				{$tQu}
				UNION 
				{$qu}				
			";
		}
		
		return $qu;
	}
	
	protected function getEmailQuery($scope, $id, $op = 'IN', $notIn = array())
	{
		$qu = "
			SELECT DISTINCT
				email.id AS 'id', email.name AS 'name', email.date_sent AS 'dateStart', '' AS 'dateEnd', 'Email' AS '_scope',
				email.assigned_user_id AS assignedUserId, TRIM(CONCAT(user.first_name, ' ', user.last_name)) AS assignedUserName,
				email.parent_type AS 'parentType', email.parent_id AS 'parentId', email.status AS status, email.created_at AS createdAt
			FROM `email`
			LEFT JOIN `user` ON user.id = email.assigned_user_id
		";
		
		if ($this->isPerson($scope)) {
			$qu .= "
				LEFT JOIN entity_email_address AS entity_email_address_2 ON
					entity_email_address_2.email_address_id = email.from_email_address_id AND
					entity_email_address_2.entity_type = " . $this->getPDO()->quote($scope) . " AND 
					entity_email_address_2.deleted = 0
				
				LEFT JOIN email_email_address ON
					email_email_address.email_id = email.id AND
					email_email_address.deleted = 0				
				LEFT JOIN entity_email_address AS entity_email_address_1 ON
					entity_email_address_1.email_address_id = email_email_address.email_address_id AND			 
					
					entity_email_address_1.entity_type = " . $this->getPDO()->quote($scope) . " AND 
					entity_email_address_1.deleted = 0			
			";
		}		
		

		$qu .= "
			WHERE email.deleted = 0
		";
		
		if (!$this->isPerson($scope)) {
			$qu .= "
				AND email.parent_type = ".$this->getPDO()->quote($scope)." AND email.parent_id = ".$this->getPDO()->quote($id)."
			";
		} else {
			$qu .= "
				AND (entity_email_address_1.entity_id = ".$this->getPDO()->quote($id)." OR entity_email_address_2.entity_id = ".$this->getPDO()->quote($id).")
			";
		}
		
		if (!empty($notIn)) {
			$qu .= "
				AND email.status {$op} ('". implode("', '", $notIn) . "')
			";
		}

		
		return $qu;
	}
	
	protected function getResult($parts, $scope, $id, $params)
	{
		$pdo = $this->getEntityManager()->getPDO();
		
		$onlyScope = false;			
		if (!empty($params['scope'])) {
			$onlyScope = $params['scope'];	
		}
		
		if (!$onlyScope) {
			$qu = implode(" UNION ", $parts);
		} else {
			$qu = $parts[$onlyScope];
		}
					
		$countQu = "SELECT COUNT(*) AS 'count' FROM ({$qu}) AS c";
		$sth = $pdo->prepare($countQu);		
		$sth->execute();
	
		$row = $sth->fetch(PDO::FETCH_ASSOC);		
		$totalCount = $row['count'];
		
		$qu .= "
			ORDER BY dateStart DESC, createdAt DESC
		";		
		
		if (!empty($params['maxSize'])) {
			$qu .= "
				LIMIT :offset, :maxSize
			";	
		}		

	

		$sth = $pdo->prepare($qu);
		
		if (!empty($params['maxSize'])) {
			$offset = 0;
			if (!empty($params['offset'])) {
				$offset = $params['offset'];
			}
			
			$sth->bindParam(':offset', $offset, PDO::PARAM_INT);
			$sth->bindParam(':maxSize', $params['maxSize'], PDO::PARAM_INT);
		}
		
		$sth->execute();
		
		$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
		
		$list = array();
		foreach ($rows as $row) {
			$list[] = $row;
		}
		
		return array(
			'list' => $rows,
			'total' => $totalCount
		);
	}
	
	public function getActivities($scope, $id, $params = array())
	{
		$parts = array(
			'Meeting' => $this->getMeetingQuery($scope, $id, 'NOT IN', array('Held', 'Not Held')),
			'Call' => $this->getCallQuery($scope, $id, 'NOT IN', array('Held', 'Not Held')),
		);		
		return $this->getResult($parts, $scope, $id, $params);
	}
	
	public function getHistory($scope, $id, $params)
	{
		$parts = array(
			'Meeting' => $this->getMeetingQuery($scope, $id, 'IN', array('Held')),
			'Call' => $this->getCallQuery($scope, $id, 'IN', array('Held')),
			'Email' => $this->getEmailQuery($scope, $id, 'IN', array('Archived', 'Sent')),
		);
		$result = $this->getResult($parts, $scope, $id, $params);
		
		foreach ($result['list'] as &$item) {
			if ($item['_scope'] == 'Email') {
				$item['dateSent'] = $item['dateStart'];
			}
		}
		
		return $result;	
	}
	
	public function getEvents($userId, $from, $to)
	{
		$pdo = $this->getPDO();
	
		$sql = "
			SELECT 'Meeting' AS scope, meeting.id AS id, meeting.name AS name, meeting.date_start AS dateStart, meeting.date_end AS dateEnd, meeting.status AS status 
			FROM `meeting`
			JOIN meeting_user ON meeting_user.meeting_id = meeting.id AND meeting_user.deleted = 0
			WHERE 
				meeting.deleted = 0 AND
				meeting.date_start >= ".$pdo->quote($from)." AND
				meeting.date_start < ".$pdo->quote($to)." AND
				meeting_user.user_id =".$pdo->quote($userId)." 
			UNION
			SELECT 'Call' AS scope, call.id AS id, call.name AS name, call.date_start AS dateStart, call.date_end AS dateEnd, call.status AS status 
			FROM `call`
			JOIN call_user ON call_user.call_id = call.id AND call_user.deleted = 0
			WHERE 
				call.deleted = 0 AND
				call.date_start >= ".$pdo->quote($from)." AND
				call.date_start < ".$pdo->quote($to)." AND
				call_user.user_id = ".$pdo->quote($userId)." 
			UNION
			SELECT 'Task' AS scope, task.id AS id, task.name AS name, task.date_start AS dateStart, task.date_end AS dateEnd, task.status AS status 
			FROM `task`
			WHERE 
				task.deleted = 0 AND
				task.date_start >= ".$pdo->quote($from)." AND
				task.date_start < ".$pdo->quote($to)." AND
				task.assigned_user_id = ".$pdo->quote($userId)."
		";

		
		$sth = $pdo->prepare($sql);		
		$sth->execute();		
		$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
		
		return $rows;
	}
}

