<?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.
  ***************************************************************************** */


class CTMobile_WS_RoutePlanningReport extends CTMobile_WS_Controller {
	
	function process(CTMobile_API_Request $request) {
		global $current_user,$adb;
		$current_user = $this->getActiveUser();
		$default_charset = VTWS_PreserveGlobal::getGlobal('default_charset');

		$response = new CTMobile_API_Response();
		$user_id = $request->get('user_id');
		$daterange = $request->get('daterange');
		
		if($daterange != ''){
			$date = explode(' - ',$daterange);
			$date1 = date("Y-m-d", strtotime($date[0]));
			$date2 = date("Y-m-d", strtotime($date[1]));
		}
	
		$index = trim($request->get('index'));
		$size = trim($request->get('size'));
		$limit = ($index*$size) - $size;
		
		
		$data = array();
		$sql = "SELECT vtiger_ctrouteplanning.ctrouteplanningid,vtiger_ctrouteplanning.ctroutename,vtiger_ctrouteplanning.ctroute_date,vtiger_ctrouteplanning.ctroute_status,entity2.crmid,
			vtiger_users.first_name, vtiger_users.last_name, entity2.label,entity2.setype,entity3.createdtime as check_in_time,entity3.modifiedtime as check_out_time,vtiger_ctrouteattendance.ctroute_attendance_status,vtiger_ctrouteattendance.check_in_address,vtiger_ctrouteattendance.check_out_address,vtiger_ctrouteattendance.check_in_location,vtiger_ctrouteattendance.check_out_location FROM vtiger_ctrouteplanning INNER JOIN vtiger_crmentity entity1 ON entity1.crmid =  vtiger_ctrouteplanning.ctrouteplanningid INNER JOIN vtiger_users ON vtiger_users.id = entity1.smownerid
				INNER JOIN vtiger_ctrouteplanrel ON vtiger_ctrouteplanrel.ctrouteplanningid = vtiger_ctrouteplanning.ctrouteplanningid INNER JOIN vtiger_crmentity entity2 ON
	 			entity2.crmid = vtiger_ctrouteplanrel.ctroute_realtedto LEFT JOIN  vtiger_ctrouteattendance ON vtiger_ctrouteattendance.related_to = vtiger_ctrouteplanrel.ctroute_realtedto AND vtiger_ctrouteattendance.ctroute_planning = vtiger_ctrouteplanning.ctrouteplanningid LEFT JOIN vtiger_crmentity entity3 ON entity3.crmid = vtiger_ctrouteattendance.ctrouteattendanceid WHERE entity1.deleted = 0 AND entity2.deleted = 0";

		if(!empty($user_id))
			$sql.=" AND entity1.smownerid = '$user_id'";

		if(!empty($daterange))
			$sql.=" AND vtiger_ctrouteplanning.ctroute_date BETWEEN '$date1' AND '$date2' ";
		
		$sql.= " ORDER BY vtiger_ctrouteplanning.ctroute_date DESC";
		$totalQuery = $sql;
		if($index != '' && $size != ''){
			$sql.= " LIMIT $limit, $size";
		}
		
		$result1 = $adb->pquery($totalQuery,array());
		$recordsTotal = $adb->num_rows($result1);
		$result2 = $adb->pquery($sql,array());
		$numofrows = $adb->num_rows($result2);
		$last_latitude = '';
		$last_longitude = '';
		$isLast = true;
		for($i=0;$i<$numofrows;$i++){
			$row = $adb->query_result_rowdata($result2,$i);
			if($row['ctroute_attendance_status'] == 'check_in'){
				$row['check_out_time'] = '';
			}
			if($row['check_in_time'] != ''){
				$row['check_in_time'] = Vtiger_Util_Helper::convertDateTimeIntoUsersDisplayFormat($row['check_in_time']);
			}
			if($row['check_out_time'] != ''){
				$row['check_out_time'] = Vtiger_Util_Helper::convertDateTimeIntoUsersDisplayFormat($row['check_out_time']);
			}
			$row['ctroute_date'] = Vtiger_Date_UIType::getDisplayValue($row['ctroute_date']);
			$ctroute_status = $row['ctroute_status'];
			if(Users_Privileges_Model::isPermitted('CTRoutePlanning', 'DetailView', $row['ctrouteplanningid'])){
				
				array_push($data,array('ctrouteplanningid'=>$row['ctrouteplanningid'],'ctroutename'=>$row['ctroutename'],'ctroute_date'=>$row['ctroute_date'],'user_name'=>decode_html(decode_html($row['first_name'])).' '.decode_html(decode_html($row['last_name'])),'module'=>$row['setype'],'modulelabel'=>vtranslate($row['setype'],$row['setype']),'recordLabel'=>decode_html(decode_html($row['label'])),'ctroute_status'=>$ctroute_status,'ctroute_attendance_status'=>$row['ctroute_attendance_status'],'check_in_time'=>$row['check_in_time'],'check_out_time'=>$row['check_out_time'],'check_in_address'=>$row['check_in_address'],'check_out_address'=>$row['check_out_address']));
			}
		}

		if($index && $size){
			if($recordsTotal > $index*$size){
				$isLast = false;
			}else{
				$isLast = true;
			}
		}


		$users = array();
		$USER_MODEL = Users_Record_Model::getCurrentUserModel();
		$AccessibleUsers = $USER_MODEL->getAccessibleUsers();
		foreach ($AccessibleUsers as $userid => $username) {
			array_push($users,array('id'=>$userid,'label'=>$username));
		}
		
		if(count($data) == 0){
			$message = $this->CTTranslate('No records found');
			$response->setResult(array('Data'=>array(),'code'=>404,'message'=>$message,'isLast'=>$isLast,'recordsTotal'=>$recordsTotal,'users'=>$users));
		}else{
			$response->setResult(array('Data'=>$data,'isLast'=>$isLast,'recordsTotal'=>$recordsTotal,'users'=>$users));
		}
		
		return $response;
	}
	
}
