<?php
 /*+*******************************************************************************
 * The content of this file is subject to the CRMTiger Pro license.
 * ("License"); You may not use this file except in compliance with the License
 * The Initial Developer of the Original Code is vTiger
 * The Modified Code of the Original Code owned by https://crmtiger.com/
 * Portions created by CRMTiger.com are Copyright(C) CRMTiger.com
 * All Rights Reserved.
  ***************************************************************************** */
vimport ('~~/include/Webservices/Query.php');

class CTMobile_WS_GetMonthBaseEventCount extends CTMobile_WS_Controller {
	
	function process(CTMobile_API_Request $request) {
		global $current_user,$adb, $site_URL;
		$current_user = $this->getActiveUser();
		$moduleModel = Vtiger_Module_Model::getInstance('Calendar');
		if(!in_array($moduleModel->get('presence'), array('0','2'))){
			$message = vtranslate('Calendar','Calendar')." ".vtranslate('Module is Disabled','CTMobile');
			throw new WebServiceException(404,$message);
		}
		$userid = trim($request->get('userid'));
		$month = trim($request->get('month'));
		$year = trim($request->get('year'));
		$response = new CTMobile_API_Response();
		$recentEvent_data = array();
		$generator = new QueryGenerator('Events', $current_user);
		$generator->setFields(array('subject','activitytype','location','date_start','due_date','time_start','time_end','location','createdtime','modifiedtime','id'));
		$eventQuery = $generator->getQuery();
		if ($month == '') {
			$message = $this->CTTranslate('Month cannot be empty');
			$response->setError(1501, $message);
			return $response;
		}
		$year = $request->get('year');
		if ($year == '') {
			$message = $this->CTTranslate('Year cannot be empty');
			$response->setError(1501, $message);
			return $response;
		}
		if ($userid == '') {
			$message = $this->CTTranslate('Userid cannot be empty');
			$response->setError(1501, $message);
			return $response;
		}
		if(strlen($month) == 1){
			$month = sprintf("%02d", $month);
		}

		if($current_user->date_format == 'dd-mm-yyyy'){
			$format = 'd-m-Y';
		}else if($current_user->date_format == 'mm-dd-yyyy'){
			$format = 'm-d-Y';
		}else{
			$format = 'Y-m-d';
		}
		$startdate = date($format,strtotime(date($year.'-'.$month.'-01')));
		$enddate = date($format,strtotime(date($year.'-'.$month.'-t')));
		
		$startDateTime = Vtiger_Datetime_UIType::getDBDateTimeValue($startdate . ' 00:00:00');
		$endDateTime = Vtiger_Datetime_UIType::getDBDateTimeValue($enddate . ' 23:59:00');

		$query = "SELECT * FROM vtiger_calendar_user_activitytypes 
			INNER JOIN vtiger_calendar_default_activitytypes on vtiger_calendar_default_activitytypes.id=vtiger_calendar_user_activitytypes.defaultid 
			WHERE vtiger_calendar_user_activitytypes.userid=?";
		$results = $adb->pquery($query, array($current_user->id));
		$rows = $adb->num_rows($results);

		$calendarViewTypes = Array();
		for($i=0; $i<$rows; $i++){
			$activityTypes = $adb->query_result_rowdata($results, $i);
			$moduleInstance = Vtiger_Module_Model::getInstance($activityTypes['module']);
			//If there is no module view permission, should not show in calendar view
			if($moduleInstance === false || !$moduleInstance->isPermitted('Detail')) {
				continue;
			}
			$fieldInstance = Vtiger_Field::getInstance($activityTypes['fieldname'], $moduleInstance);
			if($fieldInstance) {
				$fieldLabel = $fieldInstance->label;
			} else {
				$fieldLabel = $activityTypes['fieldname'];
			}
			if($activityTypes['visible'] == '1') {
				$calendarViewTypes['visible'][] = array('module'=>$activityTypes['module'], 'fieldname'=>$activityTypes['fieldname'], 'fieldlabel'=>$fieldLabel, 'visible' => $activityTypes['visible'] , 'color' => $activityTypes['color']);
			} else {
				$calendarViewTypes['invisible'][] = array('module'=>$activityTypes['module'], 'fieldname'=>$activityTypes['fieldname'], 'fieldlabel'=>$fieldLabel, 'visible' => $activityTypes['visible'] , 'color' => $activityTypes['color']);
			}
		}

		foreach($calendarViewTypes['visible'] as $key=>$value) {  
			$type = $value['module'];
			if($type == 'Events' || $type == 'Calendar'){
				$start = $startDateTime;
				$end = $endDateTime;
			}else{
				$start = date('Y-m-d',strtotime($startdate));
				$end = date('Y-m-d',strtotime($enddate));
			}
			$userid = $current_user->id;
			$color = $value['color'];
			$targetModule = '';
			$fieldName = $value['fieldname'];
			$isGroupId = false;
			$mapping = '';
			$conditions = '';
			$result = array(); 
			switch ($type) {
				case 'Events': $this->pullEvents($start, $end, $result,$userid,$color,$isGroupId,$conditions,$year,$month); break;
				case 'Calendar': $this->pullTasks($start, $end, $result,$color,$year,$month); break;
				case 'Potentials': $this->pullPotentials($start, $end, $result, $color,$year,$month); break;
				case 'Contacts':
							if($fieldName == 'support_end_date') {
								$this->pullContactsBySupportEndDate($start, $end, $result, $color,$year,$month);
							}else{
								$this->pullContactsByBirthday($start, $end, $result, $color,$year,$month);
							}
							break;

				case 'Invoice': $this->pullInvoice($start, $end, $result, $color,$year,$month); break;
				case 'MultipleEvents' : $this->pullMultipleEvents($start,$end, $result,$mapping);break;
				case 'Project': $this->pullProjects($start, $end, $result, $color,$year,$month); break;
				case 'ProjectTask': $this->pullProjectTasks($start, $end, $result, $color,$year,$month); break;
			}
			if(!empty($result)){
				$recentEvent_data = array_merge($recentEvent_data,$result);
			}
		}
										  
		if(count($recentEvent_data) == 0){
			$message = $this->CTTranslate('No event for this month'); 
			$response->setResult(array('GetEventCount'=>[],'date_format'=>$current_user->date_format,'hour_format'=>$current_user->hour_format,'code'=>404,'message'=>$message));
		} else {
			$recentEvent_data = array_values(array_unique($recentEvent_data));
			sort($recentEvent_data);
			$response->setResult(array('GetEventCount'=>$recentEvent_data,'date_format'=>$current_user->date_format,'hour_format'=>$current_user->hour_format,'message'=>''));
		}
		return $response;
	}

	protected function generateCalendarViewConditionQuery($conditions) {
		$conditionQuery = $operator = '';
		switch ($conditions['operator']) {
			case 'e' : $operator = '=';
		}

		if(!empty($operator) && !empty($conditions['fieldname']) && !empty($conditions['value'])) {
			$conditionQuery = ' '.$conditions['fieldname'].$operator.'\'' .$conditions['value'].'\' ';
		}
		return $conditionQuery;
	}

	protected function getGroupsIdsForUsers($userId) {
		vimport('~~/include/utils/GetUserGroups.php');

		$userGroupInstance = new GetUserGroups();
		$userGroupInstance->getAllUserGroups($userId);
		return $userGroupInstance->user_groups;
	}

	protected function queryForRecords($query, $onlymine=true) {
		global $current_user;
		$current_user = $this->getActiveUser();
		$user = Users_Record_Model::getCurrentUserModel();
		if ($onlymine) {
			$groupIds = $this->getGroupsIdsForUsers($user->getId());
			$groupWsIds = array();
			foreach($groupIds as $groupId) {
				$groupWsIds[] = vtws_getWebserviceEntityId('Groups', $groupId);
			}
			$userwsid = vtws_getWebserviceEntityId('Users', $user->getId());
			$userAndGroupIds = array_merge(array($userwsid),$groupWsIds);
			$query .= " AND assigned_user_id IN ('".implode("','",$userAndGroupIds)."')";
		}
		// TODO take care of pulling 100+ records
		return vtws_query($query.';', $user);
	}

	protected function pullEvents($start, $end, &$result, $userid = false, $color = null,$isGroupId = false, $conditions = '',$year,$month) {
		global $current_user;
		$current_user = $this->getActiveUser();
		$dbStartDateTimeComponents = explode(' ', $start);
		$dbStartDate = $dbStartDateTimeComponents[0];

		$currentUser = Users_Record_Model::getCurrentUserModel();
		$db = PearDatabase::getInstance();
		$groupsIds = Vtiger_Util_Helper::getGroupsIdsForUsers($currentUser->getId());
		require('user_privileges/user_privileges_'.$currentUser->id.'.php');
		require('user_privileges/sharing_privileges_'.$currentUser->id.'.php');

		$moduleModel = Vtiger_Module_Model::getInstance('Events');
		if($userid && !$isGroupId){
			$focus = new Users();
			$focus->id = $userid;
			$focus->retrieve_entity_info($userid, 'Users');
			$user = Users_Record_Model::getInstanceFromUserObject($focus);
			$userName = $user->getName();
			$queryGenerator = new QueryGenerator($moduleModel->get('name'), $user);
		}else{
			$queryGenerator = new QueryGenerator($moduleModel->get('name'), $currentUser);
		}

		$queryGenerator->setFields(array('subject', 'eventstatus', 'visibility','date_start','time_start','due_date','time_end','assigned_user_id','id','activitytype','recurringtype'));
		$query = $queryGenerator->getQuery();

		$query.= " AND vtiger_activity.activitytype NOT IN ('Emails','Task') AND ";
		$hideCompleted = $currentUser->get('hidecompletedevents');
		if($hideCompleted)
			$query.= "vtiger_activity.eventstatus != 'HELD' AND ";

		if(!empty($conditions)) {
			$conditions = Zend_Json::decode(Zend_Json::decode($conditions));
			$query .=  $this->generateCalendarViewConditionQuery($conditions).'AND ';
		}
		$query.= " ((concat(date_start, '', time_start)  >= '$start' AND concat(due_date, '', time_end) < '$end') OR ( due_date >= '$dbStartDate'))";

		$params = array();
		if(empty($userid)){
			$eventUserId  = $currentUser->getId();
			$params = array_merge(array($eventUserId), $this->getGroupsIdsForUsers($eventUserId));
		}else{
			$eventUserId = $userid;
			$params = array($eventUserId);
		}

		$query.= " AND vtiger_crmentity.smownerid IN (".  generateQuestionMarks($params).")";
		$queryResult = $db->pquery($query, $params);

		while($record = $db->fetchByAssoc($queryResult)){
			$activityid = $record['activityid'];
			$dateTimeFieldInstance = new DateTimeField($record['date_start'].' '.$record['time_start']);
			$userDateTimeString = $dateTimeFieldInstance->getDisplayDateTimeValue($currentUser);
			$dateTimeComponents = explode(' ',$userDateTimeString);
			$dateComponent = $dateTimeComponents[0];
			$startDate = $dateComponent;

			$dateTimeFieldInstance = new DateTimeField($record['due_date'].' '.$record['time_end']);
			$userDateTimeString = $dateTimeFieldInstance->getDisplayDateTimeValue($currentUser);
			$dateTimeComponents = explode(' ',$userDateTimeString);
			$dateComponent = $dateTimeComponents[0];
			$endDate = $dateComponent;
			if(Users_Privileges_Model::isPermitted('Calendar', 'DetailView', $activityid)){
				if($currentUser->get('date_format') == 'dd-mm-yyyy'){
					$format = 'd-m-Y';
				}else if($currentUser->get('date_format') == 'mm-dd-yyyy'){
					$format = 'm-d-Y';
				}else{
					$format = 'Y-m-d';
				}
				$dates = dateCustBet($startDate,$endDate,$format,$year,$month);
				if(!empty($dates)){
					$result = array_merge($result,$dates);
				}
			}
		}
	}

	protected function pullMultipleEvents($start, $end, &$result, $data) {

		foreach ($data as $id=>$backgroundColorAndTextColor) {
			$userEvents = array();
			$colorComponents = explode(',',$backgroundColorAndTextColor);
			$this->pullEvents($start, $end, $userEvents ,$id, $colorComponents[0], $colorComponents[2]);
			$result[$id] = $userEvents;
		}
	}

	protected function pullTasks($start, $end, &$result, $color = null,$year,$month) {
		global $current_user;
		$current_user = $this->getActiveUser();
		$dbStartDateTimeComponents = explode(' ', $start);
		$dbStartDate = $dbStartDateTimeComponents[0];

		$dbEndDateTimeComponents = explode(' ', $end);
		$dbEndDate = $dbEndDateTimeComponents[0];

		$user = Users_Record_Model::getCurrentUserModel();
		$db = PearDatabase::getInstance();

		$moduleModel = Vtiger_Module_Model::getInstance('Calendar');
		$userAndGroupIds = array_merge(array($user->getId()),$this->getGroupsIdsForUsers($user->getId()));
		$queryGenerator = new QueryGenerator($moduleModel->get('name'), $user);

		$queryGenerator->setFields(array('activityid','subject', 'taskstatus','activitytype', 'date_start','time_start','due_date','time_end','id'));
		$query = $queryGenerator->getQuery();

		$query.= " AND vtiger_activity.activitytype = 'Task' AND ";
		$currentUser = Users_Record_Model::getCurrentUserModel();
		$hideCompleted = $currentUser->get('hidecompletedevents');
		if($hideCompleted)
			$query.= "vtiger_activity.status != 'Completed' AND ";
		$query.= " ((date_start >= '$dbStartDate' AND due_date < '$dbEndDate') OR ( due_date >= '$dbStartDate'))";
		$params = $userAndGroupIds;
		$query.= " AND vtiger_crmentity.smownerid IN (".generateQuestionMarks($params).")";
		$queryResult = $db->pquery($query,$params);

		while($record = $db->fetchByAssoc($queryResult)){
			$activityid = $record['activityid'];
			$dateTimeFieldInstance = new DateTimeField($record['date_start'].' '.$record['time_start']);
			$userDateTimeString = $dateTimeFieldInstance->getDisplayDateTimeValue();
			$dateTimeComponents = explode(' ',$userDateTimeString);
			$dateComponent = $dateTimeComponents[0];
			$start = $dateComponent;
			if(Users_Privileges_Model::isPermitted('Calendar', 'DetailView', $activityid)){
				if(date('Y',strtotime($start)) == $year && date('m',strtotime($start)) == $month){
					$result[] = $start;
				}
			}
		}
	}

	protected function pullPotentials($start, $end, &$result, $color = null,$year,$month) {
		global $current_user;
		$current_user = $this->getActiveUser();
		$query = "SELECT potentialname,closingdate FROM Potentials";
		$query.= " WHERE closingdate >= '$start' AND closingdate <= '$end'";
		$records = $this->queryForRecords($query);
		foreach ($records as $record) {
			$item = array();
			list ($modid, $crmid) = vtws_getIdComponents($record['id']);
			if(Users_Privileges_Model::isPermitted('Potentials', 'DetailView', $crmid)){
				if(date('Y',strtotime($record['closingdate'])) == $year && date('m',strtotime($record['closingdate'])) == $month){
					$result[] = Vtiger_Date_UIType::getDisplayDateValue($record['closingdate']);
				}
			}
		}
	}

	protected function pullContacts($start, $end, &$result, $color = null,$year,$month) {
		$this->pullContactsBySupportEndDate($start, $end, $result, $color,$year,$month);
		$this->pullContactsByBirthday($start, $end, $result, $color,$year,$month);
	}

	protected function pullContactsBySupportEndDate($start, $end, &$result, $color = null,$year,$month) {
		global $current_user;
		$current_user = $this->getActiveUser();
		$query = "SELECT firstname,lastname,support_end_date FROM Contacts";
		$query.= " WHERE support_end_date >= '$start' AND support_end_date <= '$end'";
		$records = $this->queryForRecords($query);
		foreach ($records as $record) {
			list ($modid, $crmid) = vtws_getIdComponents($record['id']);
			if(Users_Privileges_Model::isPermitted('Contacts', 'DetailView', $crmid)){
				if(date('Y',strtotime($record['support_end_date'])) == $year && date('m',strtotime($record['support_end_date'])) == $month){
					$result[] = Vtiger_Date_UIType::getDisplayDateValue($record['support_end_date']);
				}
			}
		}
	}

	protected  function pullContactsByBirthday($start, $end, &$result, $color = null,$year,$month) {
		global $current_user;
		$current_user = $this->getActiveUser();
		$db = PearDatabase::getInstance();
		$user = Users_Record_Model::getCurrentUserModel();
		$startDateComponents = split('-', $start);
		$endDateComponents = split('-', $end);
        
        $userAndGroupIds = array_merge(array($user->getId()),$this->getGroupsIdsForUsers($user->getId()));
        $params = array($start,$end,$start,$end);
        $params = array_merge($userAndGroupIds, $params);
        
		$year = $startDateComponents[0];

		$query = "SELECT firstname,lastname,birthday,crmid FROM vtiger_contactdetails";
		$query.= " INNER JOIN vtiger_contactsubdetails ON vtiger_contactdetails.contactid = vtiger_contactsubdetails.contactsubscriptionid";
		$query.= " INNER JOIN vtiger_crmentity ON vtiger_contactdetails.contactid = vtiger_crmentity.crmid";
		$query.= " WHERE vtiger_crmentity.deleted=0 AND smownerid IN (".  generateQuestionMarks($userAndGroupIds) .") AND";
		$query.= " ((CONCAT('$year-', date_format(birthday,'%m-%d')) >= ?
						AND CONCAT('$year-', date_format(birthday,'%m-%d')) <= ?)";

        
		$endDateYear = $endDateComponents[0];
		if ($year !== $endDateYear) {
			$query .= " OR
						(CONCAT('$endDateYear-', date_format(birthday,'%m-%d')) >= ?
							AND CONCAT('$endDateYear-', date_format(birthday,'%m-%d')) <= ?)";
		}
		$query .= ")";

		$queryResult = $db->pquery($query, $params);

		while($record = $db->fetchByAssoc($queryResult)){
			$item = array();
			$crmid = $record['crmid'];
			$recordDateTime = new DateTime($record['birthday']);

			$calendarYear = $year;
			if($recordDateTime->format('m') < $startDateComponents[1]) {
				$calendarYear = $endDateYear;
			}
			$recordDateTime->setDate($calendarYear, $recordDateTime->format('m'), $recordDateTime->format('d'));
			$record['birthday'] = $recordDateTime->format('Y-m-d');
			if(Users_Privileges_Model::isPermitted('Contacts', 'DetailView', $crmid)){
				if(date('Y',strtotime($record['birthday'])) == $year && date('m',strtotime($record['birthday'])) == $month){
					$result[] = Vtiger_Date_UIType::getDisplayDateValue($record['birthday']);
				}
			}
		}
	}

	protected function pullInvoice($start, $end, &$result, $color = null,$year,$month) {
		global $current_user;
		$current_user = $this->getActiveUser();
		$query = "SELECT subject,duedate FROM Invoice";
		$query.= " WHERE duedate >= '$start' AND duedate <= '$end'";
		$records = $this->queryForRecords($query);
		foreach ($records as $record) {
			$item = array();
			list ($modid, $crmid) = vtws_getIdComponents($record['id']);
			if(Users_Privileges_Model::isPermitted('Invoice', 'DetailView', $crmid)){
				if(date('Y',strtotime($record['duedate'])) == $year && date('m',strtotime($record['duedate'])) == $month){
					$result[] = Vtiger_Date_UIType::getDisplayDateValue($record['duedate']);
				}
			}
		}
	}

	/**
	 * Function to pull all the current user projects
	 * @param type $startdate
	 * @param type $actualenddate
	 * @param type $result
	 * @param type $color
	 * @param type $textColor
	 */
	protected function pullProjects($start, $end, &$result, $color = null,$year,$month) {
		global $current_user;
		$current_user = $this->getActiveUser();
		$db = PearDatabase::getInstance();
		$user = Users_Record_Model::getCurrentUserModel();
		$userAndGroupIds = array_merge(array($user->getId()),$this->getGroupsIdsForUsers($user->getId()));
        $params = array($start,$end,$start);
        //$params = array_merge($userAndGroupIds, $params);
        
		$query = "SELECT projectname, startdate, targetenddate, crmid FROM vtiger_project";
		$query.= " INNER JOIN vtiger_crmentity ON vtiger_project.projectid = vtiger_crmentity.crmid";
		$query.= " WHERE vtiger_crmentity.deleted=0 AND smownerid IN (". $user->getId() .") AND ";
		$query.= " ((startdate >= ? AND targetenddate < ?) OR ( targetenddate >= ?))";
		$queryResult = $db->pquery($query, $params);

		while($record = $db->fetchByAssoc($queryResult)){
			$item = array();
			$crmid = $record['crmid'];
			if(Users_Privileges_Model::isPermitted('Project', 'DetailView', $crmid)){
				$startDate = Vtiger_Date_UIType::getDisplayDateValue($record['startdate']);
				$endDate = Vtiger_Date_UIType::getDisplayDateValue($record['targetenddate']);
				if($user->get('date_format') == 'dd-mm-yyyy'){
					$format = 'd-m-Y';
				}else if($user->get('date_format') == 'mm-dd-yyyy'){
					$format = 'm-d-Y';
				}else{
					$format = 'Y-m-d';
				}
				$dates = dateCustBet($startDate,$endDate,$format,$year,$month);
				if(!empty($dates)){
					$result = array_merge($result,$dates);
				}
			}
		}
	}

	/**
	 * Function to pull all the current user porjecttasks
	 * @param type $startdate
	 * @param type $enddate
	 * @param type $result
	 * @param type $color
	 * @param type $textColor
	 */
	protected function pullProjectTasks($start, $end, &$result, $color = null,$year,$month) {
		global $current_user;
		$current_user = $this->getActiveUser();
		$db = PearDatabase::getInstance();
		$user = Users_Record_Model::getCurrentUserModel();
        $userAndGroupIds = array_merge(array($user->getId()),$this->getGroupsIdsForUsers($user->getId()));
         $params = array($start,$end,$start);
        $params = array_merge($params, $userAndGroupIds);
		
		$query = "SELECT projecttaskname, startdate, enddate, crmid FROM vtiger_projecttask";
		$query.= " INNER JOIN vtiger_crmentity ON vtiger_projecttask.projecttaskid = vtiger_crmentity.crmid";
		$query.= " WHERE vtiger_crmentity.deleted=0 AND ";
		$query.= " ((startdate >= ? AND enddate < ?) OR ( enddate >= ?))";
                $query.= " AND smownerid IN (". generateQuestionMarks($userAndGroupIds) .")";
		$queryResult = $db->pquery($query, $params);

		while($record = $db->fetchByAssoc($queryResult)){
			$item = array();
			$crmid = $record['crmid'];
			if(Users_Privileges_Model::isPermitted('ProjectTask', 'DetailView', $crmid)){
				if(date('Y',strtotime($record['startdate'])) == $year && date('m',strtotime($record['startdate'])) == $month){
					$result[] = Vtiger_Date_UIType::getDisplayDateValue($record['startdate']);
				}
			}
		}
	}
}

function dateCustBet($date_from,$date_to,$format,$year,$month){
	$date_from = Vtiger_Date_UIType::getDBInsertedValue($date_from);
	$date_to = Vtiger_Date_UIType::getDBInsertedValue($date_to);
	// Specify the start date. This date can be any English textual format  
	$date_from = strtotime($date_from); // Convert date to a UNIX timestamp  
	// Specify the end date. This date can be any English textual format  
	$date_to = strtotime($date_to); // Convert date to a UNIX timestamp    
	// Loop from the start date to end date and output all dates inbetween  
	$Retmasterdates = array();
	for ($i=$date_from; $i<=$date_to; $i+=86400) { 
		if(date('Y', $i) == $year && date('m', $i) == $month){
	    	$Retmasterdates[] = date($format, $i);  
	    }	
	}
	return $Retmasterdates;
}
