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

	public function process(Vtiger_Request $request) {
		global $adb,$current_user,$site_URL;
		$mode = $request->get('mode');
		define("SECONDS_PER_HOUR", 60*60);
		if($mode == 'ExportData'){

			$searchValue = $request->get('user_id');
			$daterange = $request->get('daterange');
			$attendance_type = 	$request->get('attendance_type');

			if($daterange != ''){
				$dateformat = $current_user->date_format;
				if($dateformat == 'dd-mm-yyyy'){
					$format = 'd-m-Y';
				}elseif ($dateformat == 'mm-dd-yyyy') {
					$format = 'm-d-Y';
				}else{
					$format = 'Y-m-d';
				}
				$date = explode(' - ',$daterange);
				$startdate = date($format, strtotime(trim($date[0])));
				$enddate = date($format, strtotime(trim($date[1])));
				$startDateTime = Vtiger_Datetime_UIType::getDBDateTimeValue($startdate . ' 00:00:00');
				$endDateTime = Vtiger_Datetime_UIType::getDBDateTimeValue($enddate . ' 23:59:00');
			}

			$SQL = "SELECT vtiger_ctattendance.employee_name,vtiger_users.first_name,vtiger_users.last_name,vtiger_crmentity.createdtime, DATE(vtiger_crmentity.createdtime) as userdate,vtiger_ctattendance.eventid,vtiger_users.user_name,vtiger_crmentity.deleted FROM vtiger_ctattendance INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_ctattendance.ctattendanceid LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_ctattendance.employee_name GROUP BY DATE(vtiger_crmentity.createdtime),vtiger_ctattendance.employee_name HAVING vtiger_crmentity.deleted = 0";

			if($searchValue){
				$SQL.=" AND vtiger_ctattendance.employee_name = '$searchValue'";
			}

			if($startDateTime != '' && $endDateTime != ''){
	 			$SQL.=" AND vtiger_crmentity.createdtime BETWEEN '$startDateTime' AND '$endDateTime'";
	 		}

	 		if($attendance_type != ''){
	 			if($attendance_type == 'Meeting'){
	 				$SQL.=" AND (vtiger_ctattendance.eventid != '')";
	 			}else{
	 				$SQL.=" AND (vtiger_ctattendance.eventid = '' OR vtiger_ctattendance.eventid IS NULL)";
	 			}
	 		}

	 		if($start != '' && $length != ''){
				$SQL.= " LIMIT $start, $length";
			}

			
			$result = $adb->pquery($SQL,array());
			$numofrows = $adb->num_rows($result);
	 		if($numofrows){
	 			$headers = array('User Name','Date','Total (Hours)','Total (Minutes)');
	 			$entries = array();
	 			$total_user_times = 0;
				for ($i=0; $i < $adb->num_rows($result); $i++) { 
					$row = $adb->query_result_rowdata($result,$i);
					$total_times = $this->getTotalTimes($row['employee_name'],$row['userdate']);
					$total_hours = floor($total_times / 3600);
					$total_minutes = floor(($total_times / 60) % 60);
					$total_user_times = $total_user_times + ($total_hours*3600)+ ($total_minutes*60);
					$row['userdate'] = Vtiger_Date_UIType::getDisplayValue($row['userdate']);
					$entries[] = array($row['first_name'].' '.$row['last_name'],$row['userdate'],$total_hours,$total_minutes);
				
				}
				if($searchValue && $adb->num_rows($result) > 0){
					$total_hours = floor($total_user_times / 3600);
					$total_minutes = floor(($total_user_times / 60) % 60);
					$entries[] = array('','',$total_hours,$total_minutes);
				}
				$type = $request->get('type');
				$this->output($headers,$entries,$type);
			}

		}else if($mode == 'ExportDataCount'){
			$searchKey = $request->get('search_key');
			$searchValue = $request->get('search_value');
			$daterange = $request->get('daterange');
			$attendance_type = 	$request->get('attendance_type');
			if($daterange != ''){
				$dateformat = $current_user->date_format;
				if($dateformat == 'dd-mm-yyyy'){
					$format = 'd-m-Y';
				}elseif ($dateformat == 'mm-dd-yyyy') {
					$format = 'm-d-Y';
				}else{
					$format = 'Y-m-d';
				}
				$date = explode(' - ',$daterange);
				$startdate = date($format, strtotime(trim($date[0])));
				$enddate = date($format, strtotime(trim($date[1])));
				$startDateTime = Vtiger_Datetime_UIType::getDBDateTimeValue($startdate . ' 00:00:00');
				$endDateTime = Vtiger_Datetime_UIType::getDBDateTimeValue($enddate . ' 23:59:00');
			}

			$SQL = "SELECT vtiger_ctattendance.employee_name,vtiger_users.first_name,vtiger_users.last_name,vtiger_crmentity.createdtime, DATE(vtiger_crmentity.createdtime) as userdate,vtiger_ctattendance.eventid,vtiger_users.user_name,vtiger_crmentity.deleted FROM vtiger_ctattendance INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_ctattendance.ctattendanceid LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_ctattendance.employee_name GROUP BY DATE(vtiger_crmentity.createdtime),vtiger_ctattendance.employee_name HAVING vtiger_crmentity.deleted = 0";

			if($searchValue){
				$SQL.=" AND vtiger_ctattendance.employee_name = '$searchValue'";
			}

			if($startDateTime != '' && $endDateTime != ''){
	 			$SQL.=" AND vtiger_crmentity.createdtime BETWEEN '$startDateTime' AND '$endDateTime'";
	 		}

	 		if($attendance_type != ''){
	 			if($attendance_type == 'Meeting'){
	 				$SQL.=" AND (vtiger_ctattendance.eventid != '')";
	 			}else{
	 				$SQL.=" AND (vtiger_ctattendance.eventid = '' OR vtiger_ctattendance.eventid IS NULL)";
	 			}
	 		}

	 		if($start != '' && $length != ''){
				$SQL.= " LIMIT $start, $length";
			}
			
			$result = $adb->pquery($SQL,array());
			$numofrows = $adb->num_rows($result);
	 		$results = array('success' => $numofrows);
            $response = new Vtiger_Response();
            $response->setResult($results);
            $response->emit();
		}else{

			$listViewEntries = array();
			$draw = $_POST['draw'];
			$start = $_POST['start'];
			$length = $_POST['length'];

			$searchKey = $request->get('search_key');
			$searchValue = $request->get('search_value');
			$daterange = $request->get('daterange');
			$attendance_type = 	$request->get('attendance_type');
			
			if($daterange != ''){
				$dateformat = $current_user->date_format;
				if($dateformat == 'dd-mm-yyyy'){
					$format = 'd-m-Y';
				}elseif ($dateformat == 'mm-dd-yyyy') {
					$format = 'm-d-Y';
				}else{
					$format = 'Y-m-d';
				}
				$date = explode(' - ',$daterange);
				$startdate = date($format, strtotime(trim($date[0])));
				$enddate = date($format, strtotime(trim($date[1])));
				$startDateTime = Vtiger_Datetime_UIType::getDBDateTimeValue($startdate . ' 00:00:00');
				$endDateTime = Vtiger_Datetime_UIType::getDBDateTimeValue($enddate . ' 23:59:00');
			}

			$SQL = "SELECT vtiger_ctattendance.ctattendanceid,vtiger_ctattendance.employee_name,vtiger_users.first_name,vtiger_users.last_name,vtiger_crmentity.createdtime, DATE(vtiger_crmentity.createdtime) as userdate,vtiger_ctattendance.eventid,vtiger_users.user_name,vtiger_crmentity.deleted FROM vtiger_ctattendance INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_ctattendance.ctattendanceid LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_ctattendance.employee_name GROUP BY DATE(vtiger_crmentity.createdtime),vtiger_ctattendance.employee_name HAVING vtiger_crmentity.deleted = 0";

			if($searchValue){
				$SQL.=" AND vtiger_ctattendance.employee_name = '$searchValue'";
			}

			if($startDateTime != '' && $endDateTime != ''){
	 			$SQL.=" AND vtiger_crmentity.createdtime BETWEEN '$startDateTime' AND '$endDateTime'";
	 		}

	 		if($attendance_type != ''){
	 			if($attendance_type == 'Meeting'){
	 				$SQL.=" AND (vtiger_ctattendance.eventid != '')";
	 			}else{
	 				$SQL.=" AND (vtiger_ctattendance.eventid = '' OR vtiger_ctattendance.eventid IS NULL)";
	 			}
	 		}



	 		$totalSQL = $SQL;
	 		if($start != '' && $length != ''){
				$SQL.= " LIMIT $start, $length";
			}
			
			$result = $adb->pquery($SQL,array());
			$total_user_times = 0;
			for ($i=0; $i < $adb->num_rows($result); $i++) { 
				$row = $adb->query_result_rowdata($result,$i);
				$total_times = $this->getTotalTimes($row['employee_name'],$row['userdate']);
				$total_hours = floor($total_times / 3600);
				$total_minutes = floor(($total_times / 60) % 60);
				$total_user_times = $total_user_times + ($total_hours*3600)+ ($total_minutes*60);
				$row['userdate'] = Vtiger_Date_UIType::getDisplayValue($row['userdate']);
				$listViewEntries[] = array('user_name'=>'<a style="color:#15c;" href="index.php?module=CTAttendance&view=Detail&record='.$row['ctattendanceid'].'" target="_blank">'.$row['first_name'].' '.$row['last_name'].'</a>','date'=>$row['userdate'],'total_hours' => $total_hours,'total_minutes'=>$total_minutes);
				
			}

			if($searchValue && $adb->num_rows($result) > 0){
				$total_hours = floor($total_user_times / 3600);
				$total_minutes = floor(($total_user_times / 60) % 60);
				$listViewEntries[] = array('user_name'=>'','date'=>'','total_hours' =>'<b>'.$total_hours.'</b>','total_minutes'=>'<b>'.$total_minutes.'</b>');
			}
			
			$totalResult = $adb->pquery($totalSQL,array());
			$totalCount = $adb->num_rows($totalResult);

			$result = array('draw'=>(int)$draw,'recordsTotal'=>$totalCount,'recordsFiltered'=>$totalCount,'data'=>$listViewEntries);
			echo json_encode($result);
			exit;
		}
	}

	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 = 'AttendanceSummary-'.date('Y-m-d-H-i-s').'.csv';
		}else{
			$exportType = 'application/x-msexcel';
			$fileName = 'AttendanceSummary-'.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;
}