<?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 CTTimeTracker_getTracking_Action extends Vtiger_Save_Action {

	public function process(Vtiger_Request $request) {
		global $adb,$current_user,$site_URL;
		$mode = $request->get('mode');
		if($mode == 'ExportData'){
			$exporttype =  $request->get('exporttype');
			if($exporttype == 'exportsummary'){

				$SQL = "SELECT vtiger_cttimetracker.tracking_title,c2.label as related_to,vtiger_users.first_name,vtiger_users.last_name,vtiger_cttimetracker.total_hour,vtiger_cttimetracker.total_min,vtiger_cttimetracker.total_seconds,vtiger_cttimetracker.tracking_status FROM `vtiger_cttimetracker` 
				    INNER JOIN vtiger_crmentity as c1 ON c1.crmid = vtiger_cttimetracker.cttimetrackerid 
					INNER JOIN vtiger_users ON vtiger_users.id = vtiger_cttimetracker.tracking_user
					LEFT JOIN vtiger_crmentity as c2 ON c2.crmid = vtiger_cttimetracker.related_to AND c2.deleted = 0  
					WHERE c1.deleted = 0";

				
				$result = $adb->pquery($SQL,array());
				$numofrows = $adb->num_rows($result);
		 		if($numofrows){
		 			$headers = array('Title','Related','User Name','Total (Hours)','Total (minutes)','Total (Seconds)','Status');
		 			$entries = array();
		 			$total_user_times = 0;
					for ($i=0; $i < $adb->num_rows($result); $i++) { 
						$row = $adb->query_result_rowdata($result,$i);
						$entries[] = array($row['tracking_title'],$row['related_to'],$row['first_name'].' '.$row['last_name'],$row['total_hour'],$row['total_min'],$row['total_seconds'],$row['tracking_status']);
					
					}
					$type = 'csv';
					$this->output($headers,$entries,$type);
				}
			}else{
				$SQL = "SELECT vtiger_cttimetracker.tracking_title,c3.label as related_to,vtiger_users.first_name,vtiger_users.last_name,vtiger_cttimecontrol.date_start,vtiger_cttimecontrol.time_start,vtiger_cttimecontrol.date_end,vtiger_cttimecontrol.time_end,vtiger_cttimecontrol.duration,vtiger_cttimecontrol.check_in_address,vtiger_cttimecontrol.check_out_address,vtiger_cttimecontrol.check_in_location,vtiger_cttimecontrol.check_out_location,vtiger_cttimecontrol.tracking_notes,vtiger_cttimetracker.tracking_status 
				    FROM `vtiger_cttimecontrol`
                    INNER JOIN vtiger_crmentity as c1 ON c1.crmid = vtiger_cttimecontrol.cttimecontrolid
                    INNER JOIN vtiger_cttimetracker ON vtiger_cttimetracker.cttimetrackerid = vtiger_cttimecontrol.related_tracking
			        INNER JOIN vtiger_crmentity as c2 ON c2.crmid = vtiger_cttimecontrol.related_tracking 
				    INNER JOIN vtiger_users ON vtiger_users.id = vtiger_cttimetracker.tracking_user
				    LEFT JOIN vtiger_crmentity as c3 ON c3.crmid = vtiger_cttimetracker.related_to AND c3.deleted = 0  
				    WHERE c1.deleted = 0";

				
				$result = $adb->pquery($SQL,array());
				$numofrows = $adb->num_rows($result);
		 		if($numofrows){
		 			$headers = array('Title','Related','User Name','Status','Date Start','Time Start','Date End','Time End','Duration (in Seconds)','Location Address When Start Timer','Location Address When Stop Timer','Location When Start Timer','Location When Stop Timer','Notes',);
		 			$entries = array();
		 			$total_user_times = 0;
					for ($i=0; $i < $adb->num_rows($result); $i++) { 
						$row = $adb->query_result_rowdata($result,$i);
						$row['date_start'] = Vtiger_Date_UIType::getDisplayValue($row['date_start']);
						$row['date_end'] = Vtiger_Date_UIType::getDisplayValue($row['date_end']);
						$row['time_start'] = Vtiger_Time_UIType::getDisplayValue($row['time_start']);
						$row['time_end'] = Vtiger_Time_UIType::getDisplayValue($row['time_end']);
						$entries[] = array($row['tracking_title'],$row['related_to'],$row['first_name'].' '.$row['last_name'],$row['tracking_status'],$row['date_start'],$row['time_start'],$row['date_end'],$row['time_end'],$row['duration'],$row['check_in_address'],$row['check_out_address'],$row['check_in_location'],$row['check_out_location'],$row['tracking_notes']);
					
					}
					$type = 'csv';
					$this->output($headers,$entries,$type);
				}
			}

		}else if($mode == 'ExportDataCount'){

			$SQL = "SELECT vtiger_cttimetracker.tracking_title,c2.label as related_to,vtiger_users.first_name,vtiger_users.last_name,vtiger_cttimetracker.total_hour,vtiger_cttimetracker.total_min,vtiger_cttimetracker.total_seconds,vtiger_cttimetracker.tracking_status FROM `vtiger_cttimetracker` 
			    INNER JOIN vtiger_crmentity as c1 ON c1.crmid = vtiger_cttimetracker.cttimetrackerid 
				INNER JOIN vtiger_users ON vtiger_users.id = vtiger_cttimetracker.tracking_user
				LEFT JOIN vtiger_crmentity as c2 ON c2.crmid = vtiger_cttimetracker.related_to AND c2.deleted = 0  
				WHERE c1.deleted = 0";
			
			$result = $adb->pquery($SQL,array());
			$numofrows = $adb->num_rows($result);
	 		$results = array('success' => $numofrows);
            $response = new Vtiger_Response();
            $response->setResult($results);
            $response->emit();
		}
	}

	function getTotalTimes($employee_name,$datetime){
		global $adb;
		$SQL = "SELECT vtiger_crmentity.createdtime, vtiger_crmentity.modifiedtime FROM vtiger_ctattendance INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_ctattendance.ctattendanceid WHERE vtiger_ctattendance.employee_name = ? AND DATE(vtiger_crmentity.createdtime) = ? AND vtiger_crmentity.deleted = 0";

		$result = $adb->pquery($SQL,array($employee_name,$datetime));
		$total_time = 0;
		for($i=0;$i<$adb->num_rows($result);$i++){
			$createdtime = $adb->query_result($result,$i,'createdtime');
			$modifiedtime = $adb->query_result($result,$i,'modifiedtime');

			$startdatetime = strtotime($createdtime);
			$enddatetime = strtotime($modifiedtime);

			$difference = $enddatetime - $startdatetime;
			$total_time = $total_time + $difference;
		}
		return $total_time;
	}

	function output($headers, $entries,$type) {
		// for content disposition header comma should not be there in filename 
		if($type == 'csv'){
			$exportType = 'text/csv';
			$fileName = 'TimeTrackingReport-'.date('Y-m-d-H-i-s').'.csv';
		}else{
			$exportType = 'application/x-msexcel';
			$fileName = 'TimeTrackingReport-'.date('Y-m-d-H-i-s').'.xls';
		}
		header("Content-Disposition:attachment;filename=$fileName");
		header("Content-Type:$exportType;charset=UTF-8");
		header("Expires: Mon, 31 Dec 2000 00:00:00 GMT" );
		header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT" );
		header("Cache-Control: post-check=0, pre-check=0", false );

		if($type == 'csv'){
			$header =  implode("\t", $headers) . "\n";
			echo $header;
			foreach($entries as $row) {
				foreach ($row as $key => $value) {
					/* To support double quotations in CSV format
					 * To review: http://creativyst.com/Doc/Articles/CSV/CSV01.htm#EmbedBRs
					 */
					$row[$key] = str_replace('"', '""', $value);
				}
				echo implode("\t", array_values($row)) . "\n";
			}
		}else{
			$header =  implode("\t", $headers) . "\n";
			echo $header;
			foreach($entries as $row) {
				echo implode("\t", array_values($row)) . "\n";
			}

		}
	}



}

function searcharray($value, $key, $array) {
   foreach ($array as $k => $val) {
       if ($val[$key] == $value) {
           return $k;
       }
   }
   return null;
}