<?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_getCallRecording_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 == 'listRecord'){
			
			$daterange = $request->get('daterange');
			$listUsers = $request->get('listUsers');
			$searchvalue = 	$request->get('searchtext');
			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 ctmobile_calllog_activity.activityid,c1.label,c1.smownerid,c1.createdtime,vtiger_attachments.attachmentsid,vtiger_attachments.path,vtiger_attachments.name, c2.crmid as cntid, c3.crmid as orgid,c2.label as cntlbl, c3.label as orglbl FROM ctmobile_calllog_activity INNER JOIN vtiger_crmentity as c1 ON c1.crmid = ctmobile_calllog_activity.activityid LEFT JOIN vtiger_attachments ON vtiger_attachments.attachmentsid = ctmobile_calllog_activity.attachmentsid LEFT JOIN vtiger_users ON vtiger_users.id = c1.smownerid LEFT JOIN vtiger_cntactivityrel ON ctmobile_calllog_activity.activityid = vtiger_cntactivityrel.activityid LEFT JOIN vtiger_crmentity as c2 ON vtiger_cntactivityrel.contactid = c2.crmid AND c2.deleted = 0  LEFT JOIN vtiger_seactivityrel ON ctmobile_calllog_activity.activityid = vtiger_seactivityrel.activityid LEFT JOIN vtiger_crmentity as c3 ON vtiger_seactivityrel.crmid = c3.crmid AND c3.deleted = 0 WHERE c1.deleted = 0 ";

	 		
	 		if(!empty($searchvalue))
				$sql.= " AND (c1.label LIKE '%".$searchvalue."%' OR c2.label LIKE '%".$searchvalue."%' OR c3.label LIKE '%".$searchvalue."%')";
	 		
	 		$result = $adb->pquery($sql,array());
			$data = array();
			for($i=0;$i<$adb->num_rows($result);$i++){

				$id = $adb->query_result($result,$i,'activityid');
				$label = $adb->query_result($result,$i,'label');
				$cntlbl = $adb->query_result($result,$i,'cntlbl');
				$orglbl = $adb->query_result($result,$i,'orglbl');
				$related = '';
				if($cntlbl != ''){
					$related = decode_html(decode_html($cntlbl));
					$relatedcrmid = $adb->query_result($result,$i,'cntid');
				}else{
					$related = decode_html(decode_html($orglbl));
					$relatedcrmid = $adb->query_result($result,$i,'orgid');
				}
				$data[] = array('id' => $id ,'text' =>$label.'-'.$related);
				if(strpos(strtolower($related), strtolower($searchvalue)) !== false){
					if(!searcharray($relatedcrmid,'id',$data)){
						$data[] = array('id' => $relatedcrmid ,'text' =>$related);
					}
				}
			}
			
			$response = new Vtiger_Response();
			$response->setResult($data);
			$response->emit();
			
		}else if($mode == 'ExportData'){

			$searchValue = $request->get('usersFilter');
			$daterange = $request->get('daterange');
			$searchvalue = 	$request->get('searchbox');
			$searchrecid = 	$request->get('searchtextvalue');
			
			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 ctmobile_calllog_activity.activityid,c1.label,c1.smownerid,c1.createdtime,ac1.date_start,ac1.time_start,ac1.due_date,ac1.time_end,vtiger_attachments.attachmentsid,vtiger_attachments.path,vtiger_attachments.name,c2.label as cntlbl,c3.label as orglbl FROM ctmobile_calllog_activity INNER JOIN vtiger_crmentity as c1 ON c1.crmid = ctmobile_calllog_activity.activityid INNER JOIN vtiger_activity as ac1 ON ac1.activityid = ctmobile_calllog_activity.activityid LEFT JOIN vtiger_attachments ON vtiger_attachments.attachmentsid = ctmobile_calllog_activity.attachmentsid LEFT JOIN vtiger_users ON vtiger_users.id = c1.smownerid LEFT JOIN vtiger_cntactivityrel ON ctmobile_calllog_activity.activityid = vtiger_cntactivityrel.activityid LEFT JOIN vtiger_crmentity as c2 ON vtiger_cntactivityrel.contactid = c2.crmid AND c2.deleted = 0 LEFT JOIN vtiger_seactivityrel ON ctmobile_calllog_activity.activityid = vtiger_seactivityrel.activityid LEFT JOIN vtiger_crmentity as c3 ON vtiger_seactivityrel.crmid = c3.crmid AND c3.deleted = 0 WHERE c1.deleted = 0";
				
			if($searchValue){
				$SQL.=" AND vtiger_users.user_name = '$searchValue'";
			}

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

	 		if(!empty($searchrecid)){
	 			$setype = VtigerCRMObject::getSEType($searchrecid);
	 			if($setype == 'Contacts'){
	 				$SQL.= " AND c2.crmid ='$searchrecid'";
	 			}else if($setype == 'Calendar' || $setype == 'Events'){
	 				$SQL.= " AND ctmobile_calllog_activity.activityid ='$searchrecid'";
	 			}else{
					$SQL.= " AND c3.crmid ='$searchrecid'";
	 			}
	 		}

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

			
			$result = $adb->pquery($SQL,array());
			$numofrows = $adb->num_rows($result);
	 		if($numofrows){
	 			$headers = array('Name','User Name','Related','CallLogging Time','Duration','Recording');
	 			$entries = array();
				for ($i=0; $i < $adb->num_rows($result); $i++) { 
					$row = $adb->query_result_rowdata($result,$i);
					$related = '';
					if($row['cntlbl'] != '')
						$related = decode_html(decode_html($row['cntlbl']));
					else
						$related = decode_html(decode_html($row['orglbl']));


					$startdatetime = strtotime($row['date_start'].' '.$row['time_start']);
					// calculate the end timestamp
					$enddatetime = strtotime($row['due_date'].' '.$row['time_end']);
					// calulate the difference in seconds
					$difference = $enddatetime - $startdatetime;
			    	$hours = floor($difference / SECONDS_PER_HOUR);
					$minutes = floor(($difference / 60) % 60);
					if($row['path'] !='' && $row['name'] != ''){

						$userRecordModel = Users_Record_Model::getInstanceById($row['smownerid'],'Users');
						$username = decode_html(decode_html($userRecordModel->get('first_name').' '.$userRecordModel->get('last_name')));
						$fileURL = $site_URL.$row['path'].$row['attachmentsid'].'_'.$row['name'];
						$entries[] = array($row['label'],$username,$related,Vtiger_Datetime_UIType::getDisplayValue($row['date_start'].' '.$row['time_start']),$hours." Hrs ".$minutes." Mins",$fileURL);
					}else{
						$userRecordModel = Users_Record_Model::getInstanceById($row['smownerid'],'Users');
						$username = decode_html(decode_html($userRecordModel->get('first_name').' '.$userRecordModel->get('last_name')));
						$entries[] = array($row['label'],$username,$related,Vtiger_Datetime_UIType::getDisplayValue($row['date_start'].' '.$row['time_start']),$hours." Hrs ".$minutes." Mins",'No Recording..');
					}
				}
				$type = 'csv';
				$fileName = 'CallLogs-'.date('Y-m-d-H-i-s').'.csv';
				$this->output($headers,$entries,$type,$fileName);
			}

		}else if($mode == 'ExportDataCount'){
			$searchKey = $request->get('search_key');
			$searchValue = $request->get('search_value');
			$daterange = $request->get('daterange');
			$searchvalue = 	$request->get('searchtext');
			$searchrecid = 	$request->get('searchrecid');
			
			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 ctmobile_calllog_activity.activityid,c1.label,c1.smownerid,c1.createdtime,ac1.date_start,ac1.time_start,ac1.due_date,ac1.time_end,vtiger_attachments.attachmentsid,vtiger_attachments.path,vtiger_attachments.name,c2.label as cntlbl,c3.label as orglbl FROM ctmobile_calllog_activity INNER JOIN vtiger_crmentity as c1 ON c1.crmid = ctmobile_calllog_activity.activityid INNER JOIN vtiger_activity as ac1 ON ac1.activityid = ctmobile_calllog_activity.activityid LEFT JOIN vtiger_attachments ON vtiger_attachments.attachmentsid = ctmobile_calllog_activity.attachmentsid LEFT JOIN vtiger_users ON vtiger_users.id = c1.smownerid LEFT JOIN vtiger_cntactivityrel ON ctmobile_calllog_activity.activityid = vtiger_cntactivityrel.activityid LEFT JOIN vtiger_crmentity as c2 ON vtiger_cntactivityrel.contactid = c2.crmid AND c2.deleted = 0 LEFT JOIN vtiger_seactivityrel ON ctmobile_calllog_activity.activityid = vtiger_seactivityrel.activityid LEFT JOIN vtiger_crmentity as c3 ON vtiger_seactivityrel.crmid = c3.crmid AND c3.deleted = 0 WHERE c1.deleted = 0";
				
			if($searchValue){
				$SQL.=" AND vtiger_users.user_name = '$searchValue'";
			}

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

	 		if(!empty($searchvalue) && !empty($searchrecid)){
	 			$setype = VtigerCRMObject::getSEType($searchrecid);
	 			if($setype == 'Contacts'){
	 				$SQL.= " AND c2.crmid ='$searchrecid'";
	 			}else if($setype == 'Calendar' || $setype == 'Events'){
	 				$SQL.= " AND ctmobile_calllog_activity.activityid ='$searchrecid'";
	 			}else{
					$SQL.= " AND c3.crmid ='$searchrecid'";
	 			}
	 		}

	 		if(!empty($searchvalue) && !empty($searchrecid)){
	 			$setype = VtigerCRMObject::getSEType($searchrecid);
	 			if($setype == 'Contacts'){
	 				$SQL.= " AND c2.crmid ='$searchrecid'";
	 			}else if($setype == 'Calendar' || $setype == 'Events'){
	 				$SQL.= " AND ctmobile_calllog_activity.activityid ='$searchrecid'";
	 			}else{
					$SQL.= " AND c3.crmid ='$searchrecid'";
	 			}
	 		}

	 		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');
			$searchvalue = 	$request->get('searchtext');
			$searchrecid = 	$request->get('searchrecid');
			
			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 ctmobile_calllog_activity.activityid,c1.label,c1.smownerid,c1.createdtime,ac1.date_start,ac1.time_start,ac1.due_date,ac1.time_end,vtiger_attachments.attachmentsid,vtiger_attachments.path,vtiger_attachments.name,c2.label as cntlbl,c3.label as orglbl FROM ctmobile_calllog_activity INNER JOIN vtiger_crmentity as c1 ON c1.crmid = ctmobile_calllog_activity.activityid INNER JOIN vtiger_activity as ac1 ON ac1.activityid = ctmobile_calllog_activity.activityid LEFT JOIN vtiger_attachments ON vtiger_attachments.attachmentsid = ctmobile_calllog_activity.attachmentsid LEFT JOIN vtiger_users ON vtiger_users.id = c1.smownerid LEFT JOIN vtiger_cntactivityrel ON ctmobile_calllog_activity.activityid = vtiger_cntactivityrel.activityid LEFT JOIN vtiger_crmentity as c2 ON vtiger_cntactivityrel.contactid = c2.crmid AND c2.deleted = 0 LEFT JOIN vtiger_seactivityrel ON ctmobile_calllog_activity.activityid = vtiger_seactivityrel.activityid LEFT JOIN vtiger_crmentity as c3 ON vtiger_seactivityrel.crmid = c3.crmid AND c3.deleted = 0 WHERE c1.deleted = 0";
				
			if($searchValue){
				$SQL.=" AND vtiger_users.user_name = '$searchValue'";
			}

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

	 		if(!empty($searchvalue) && !empty($searchrecid)){
	 			$setype = VtigerCRMObject::getSEType($searchrecid);
	 			if($setype == 'Contacts'){
	 				$SQL.= " AND c2.crmid ='$searchrecid'";
	 			}else if($setype == 'Calendar' || $setype == 'Events'){
	 				$SQL.= " AND ctmobile_calllog_activity.activityid ='$searchrecid'";
	 			}else{
					$SQL.= " AND c3.crmid ='$searchrecid'";
	 			}
	 		}


	 		$totalSQL = $SQL;
	 		if($start != '' && $length != ''){
				$SQL.= " LIMIT $start, $length";
			}
			
			$result = $adb->pquery($SQL,array());
			for ($i=0; $i < $adb->num_rows($result); $i++) { 
				$row = $adb->query_result_rowdata($result,$i);
				$related = '';
				if($row['cntlbl'] != ''){
					$related = decode_html(decode_html($row['cntlbl']));
				}else{
					$related = decode_html(decode_html($row['orglbl']));
				}

				$startdatetime = strtotime($row['date_start'].' '.$row['time_start']);
			    // calculate the end timestamp
			    $enddatetime = strtotime($row['due_date'].' '.$row['time_end']);
			    
			    // calulate the difference in seconds
			    $difference = $enddatetime - $startdatetime;
			    $hours = floor($difference / SECONDS_PER_HOUR);
				$minutes = floor(($difference / 60) % 60);

				if($row['path'] !='' && $row['name'] != ''){
					$userRecordModel = Users_Record_Model::getInstanceById($row['smownerid'],'Users');
					$username = decode_html(decode_html($userRecordModel->get('first_name').' '.$userRecordModel->get('last_name')));
					$fileURL = $row['path'].$row['attachmentsid'].'_'.$row['name'];
					$listViewEntries[] = array('label'=>'<a style="color:#15c;" href="index.php?module=Calendar&view=Detail&record='.$row['activityid'].'" target="_blank">'.$row['label'].'</a>','username'=>$username,'related' => $related,'createdtime'=>Vtiger_Datetime_UIType::getDisplayValue($row['date_start'].' '.$row['time_start']),'duration'=>$hours." Hrs ".$minutes." Mins",'file'=>'<audio controls><source src="'.$fileURL.'" type="audio/mpeg">');
				}else{
					$userRecordModel = Users_Record_Model::getInstanceById($row['smownerid'],'Users');
					$username = decode_html(decode_html($userRecordModel->get('first_name').' '.$userRecordModel->get('last_name')));
					$listViewEntries[] = array('label'=>'<a style="color:#15c;" href="index.php?module=Calendar&view=Detail&record='.$row['activityid'].'" target="_blank">'.$row['label'].'</a>','username'=>$username,'related' => $related,'createdtime'=>Vtiger_Datetime_UIType::getDisplayValue($row['date_start'].' '.$row['time_start']),'duration'=>$hours." Hrs ".$minutes." Mins",'file'=>'No Recording..');
				}
			}
			
			$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 output($headers, $entries,$type,$fileName) {
		// for content disposition header comma should not be there in filename 
		if($type == 'csv'){
			$exportType = 'text/csv';
		}else{
			$exportType = 'application/x-msexcel';
		}
		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;
}