<?php 
# AST_agent_time_detail.php
# 
# Pulls time stats per agent selectable by campaign or user group
# should be most accurate agent stats of all of the reports
#
# Copyright (C) 2022  Matt Florell <vicidial@gmail.com>    LICENSE: AGPLv2
#
# CHANGES
# 90522-0723 - First build
# 90908-1103 - Added DEAD time stats
# 100203-1147 - Added CUSTOMER time statistics
# 100214-1421 - Sort menu alphabetically
# 100216-0042 - Added popup date selector
# 100712-1324 - Added system setting slave server option
# 100802-2347 - Added User Group Allowed Reports option validation
# 100914-1326 - Added lookup for user_level 7 users to set to reports only which will remove other admin links
# 101207-1634 - Changed limits on seconds to 65000 from 30000 in vicidial_agent_log
# 101207-1719 - Fixed download file formatting bugs(issue 394)
# 101208-0320 - Fixed issue 404
# 110307-1057 - Added user_case setting in options.php
# 110623-0728 - Fixed user group selection bug
# 110708-1727 - Added options.php setting for time precision
# 111104-1248 - Added user_group restrictions for selecting in-groups
# 120224-0910 - Added HTML display option with bar graphs
# 121130-0957 - Fix for user group permissions issue #588
# 130414-0137 - Added report logging
# 220414-2111 - Added counts on parked(hold) calls
# 130610-0849 - Finalized changing of all ereg instances to preg
# 130621-0821 - Added filtering of input to prevent SQL injection attacks and new user auth
# 130704-0945 - Fixed issue #675
# 130829-2012 - Changed to mysqli PHP functions
# 131122-0657 - Added options.php atdr_login_logout_user_link option
# 131129-1020 - Fixed division by zero bug in HTML mode
# 140108-0708 - Added webserver and hostname to report logging
# 140314-0852 - Fixed several division by zero bugs
# 140328-0005 - Converted division calculations to use MathZDC function
# 141113-1124 - Finalized adding QXZ translation to all admin files
# 141125-0950 - Changed AGENT TIME to LOGIN TIME for uniform headers with other reports, issue #427
# 141204-0548 - Fix for download headers, issue #805
# 141230-0929 - Added code for on-the-fly language translations display
# 150210-1356 - Added option to show time in seconds
# 150422-1643 - Added two new shift options
# 150516-1311 - Fixed Javascript element problem, Issue #857
# 150529-1921 - Sub statuses are now sorted in alphabetical order
# 151110-1612 - Changed download function to always export time with hours
# 151112-1335 - Added option to search archived tables
# 160121-2037 - Added report title header, default report format, cleaned up formatting
# 160301-2051 - Expanded full name to 25 characters on text display
# 160330-0649 - Fixed issue with names and non-latin setting
# 160411-1958 - Fixed issue with download field order
# 160714-2348 - Added and tested ChartJS features for more aesthetically appealing graphs
# 170409-1542 - Added IP List validation code
# 170829-0040 - Added screen color settings
# 171012-2015 - Fixed javascript/apache errors with graphs
# 180502-2215 - Added new help display, added CONNECTED column
# 191013-0846 - Fixes for PHP7
# 210318-1558 - Added agent screen visibility stats
# 210324-2010 - Added agent visibility stats to HTML graphs
# 211115-1513 - Fix for case-mismatch on pause code statuses for multi-campaign selects
# 220303-1427 - Added allow_web_debug system setting
#

$startMS = microtime();

require("dbconnect_mysqli.php");
require("functions.php");

$atdr_login_logout_user_link=0;
if (file_exists('options.php'))
	{
	require('options.php');
	}

$REQUEST_URI_array = explode('/',$_SERVER['REQUEST_URI']);
array_pop($REQUEST_URI_array);
$ADMIN = implode('/',$REQUEST_URI_array).'/index.php';

$PHP_AUTH_USER=$_SERVER['PHP_AUTH_USER'];
$PHP_AUTH_PW=$_SERVER['PHP_AUTH_PW'];
$PHP_SELF=$_SERVER['PHP_SELF'];
$PHP_SELF = preg_replace('/\.php.*/i','.php',$PHP_SELF);
if (isset($_GET["event_date"]))				{$event_date=$_GET["event_date"];}
	elseif (isset($_POST["event_date"]))	{$event_date=$_POST["event_date"];}
if (isset($_GET["campaign_id"]))				{$campaign_id=$_GET["campaign_id"];}
	elseif (isset($_POST["campaign_id"]))	{$campaign_id=$_POST["campaign_id"];}
if (isset($_GET["user"]))				{$user=$_GET["user"];}
	elseif (isset($_POST["user"]))	{$user=$_POST["user"];}
if (isset($_GET["unique_id"]))				{$unique_id=$_GET["unique_id"];}
	elseif (isset($_POST["unique_id"]))	{$unique_id=$_POST["unique_id"];}
if (isset($_GET["submit"]))				{$submit=$_GET["submit"];}
	elseif (isset($_POST["submit"]))	{$submit=$_POST["submit"];}

$DB=preg_replace("/[^0-9a-zA-Z]/","",$DB);

$end_date = $event_date.' 23:59:59';
$event_date = $event_date. ' 00:00:00';

$MT[0]='';
$NOW_DATE = date("Y-m-d");
$NOW_TIME = date("Y-m-d H:i:s");
$STARTtime = date("U");
if (!isset($group)) {$group = array();}
if (!isset($user_group)) {$user_group = array();}
if (!isset($event_date)) {$event_date = $NOW_DATE;}
if (!isset($end_date)) {$end_date = $NOW_DATE;}
if (strlen($shift)<2) {$shift='ALL';}
if (strlen($stage)<2) {$stage='NAME';}

$report_name = 'Login Hours Report';
$db_source = 'M';

#############################################
##### START SYSTEM_SETTINGS LOOKUP #####
$stmt = "SELECT use_non_latin,outbound_autodial_active,slave_db_server,reports_use_slave_db,enable_languages,language_method,report_default_format,allow_web_debug FROM system_settings;";
$rslt=mysql_to_mysqli($stmt, $link);
#if ($DB) {echo "|$stmt\n";}
$qm_conf_ct = mysqli_num_rows($rslt);
if ($qm_conf_ct > 0)
	{
	$row=mysqli_fetch_row($rslt);
	$non_latin =					$row[0];
	$outbound_autodial_active =		$row[1];
	$slave_db_server =				$row[2];
	$reports_use_slave_db =			$row[3];
	$SSenable_languages =			$row[4];
	$SSlanguage_method =			$row[5];
	$SSreport_default_format =		$row[6];
	$SSallow_web_debug =			$row[7];
	}
if ($SSallow_web_debug < 1) {$DB=0;}
if (strlen($report_display_type)<2) {$report_display_type = $SSreport_default_format;}
##### END SETTINGS LOOKUP #####
###########################################

# Variables filtered further down in the code
# $group
# $user_group

if ($non_latin < 1)
	{
	$PHP_AUTH_USER = preg_replace('/[^-_0-9a-zA-Z]/', '', $PHP_AUTH_USER);
	$PHP_AUTH_PW = preg_replace('/[^-_0-9a-zA-Z]/', '', $PHP_AUTH_PW);
	$shift = preg_replace('/[^-_0-9a-zA-Z]/', '', $shift);
	}
else
	{
	$PHP_AUTH_USER = preg_replace('/[^-_0-9\p{L}]/u', '', $PHP_AUTH_USER);
	$PHP_AUTH_PW = preg_replace('/[^-_0-9\p{L}]/u', '', $PHP_AUTH_PW);
	$shift = preg_replace('/[^-_0-9\p{L}]/u', '', $shift);
	}

$stmt="SELECT selected_language,full_name from vicidial_users where user='$PHP_AUTH_USER';";
if ($DB) {echo "|$stmt|\n";}
$rslt=mysql_to_mysqli($stmt, $link);
$sl_ct = mysqli_num_rows($rslt);
if ($sl_ct > 0)
	{
	$row=mysqli_fetch_row($rslt);
	$VUselected_language =		$row[0];
	$LOGfull_name				=$row[1];
	}

$auth=0;
$reports_auth=0;
$admin_auth=0;
$auth_message = user_authorization($PHP_AUTH_USER,$PHP_AUTH_PW,'REPORTS',1,0);
if ( ($auth_message == 'GOOD') or ($auth_message == '2FA') )
	{
	$auth=1;
	if ($auth_message == '2FA')
		{
		header ("Content-type: text/html; charset=utf-8");
		echo _QXZ("Your session is expired").". <a href=\"index.php\">"._QXZ("Click here to log in")."</a>.\n";
		exit;
		}
	}

if ($auth > 0)
	{
	$stmt="SELECT count(*) from vicidial_users where user='$PHP_AUTH_USER' and user_level > 7 and view_reports='1';";
	if ($DB) {echo "|$stmt|\n";}
	$rslt=mysql_to_mysqli($stmt, $link);
	$row=mysqli_fetch_row($rslt);
	$admin_auth=$row[0];

	$stmt="SELECT count(*) from vicidial_users where user='$PHP_AUTH_USER' and user_level > 6 and view_reports='1';";
	if ($DB) {echo "|$stmt|\n";}
	$rslt=mysql_to_mysqli($stmt, $link);
	$row=mysqli_fetch_row($rslt);
	$reports_auth=$row[0];

	if ($reports_auth < 1)
		{
		$VDdisplayMESSAGE = _QXZ("You are not allowed to view reports");
		Header ("Content-type: text/html; charset=utf-8");
		echo "$VDdisplayMESSAGE: |$PHP_AUTH_USER|$auth_message|\n";
		exit;
		}
	if ( ($reports_auth > 0) and ($admin_auth < 1) )
		{
		$ADD=999999;
		$reports_only_user=1;
		}
	}
else
	{
	$VDdisplayMESSAGE = _QXZ("Login incorrect, please try again");
	if ($auth_message == 'LOCK')
		{
		$VDdisplayMESSAGE = _QXZ("Too many login attempts, try again in 15 minutes");
		Header ("Content-type: text/html; charset=utf-8");
		echo "$VDdisplayMESSAGE: |$PHP_AUTH_USER|$auth_message|\n";
		exit;
		}
	if ($auth_message == 'IPBLOCK')
		{
		$VDdisplayMESSAGE = _QXZ("Your IP Address is not allowed") . ": $ip";
		Header ("Content-type: text/html; charset=utf-8");
		echo "$VDdisplayMESSAGE: |$PHP_AUTH_USER|$auth_message|\n";
		exit;
		}
	Header("WWW-Authenticate: Basic realm=\"CONTACT-CENTER-ADMIN\"");
	Header("HTTP/1.0 401 Unauthorized");
	echo "$VDdisplayMESSAGE: |$PHP_AUTH_USER|$PHP_AUTH_PW|$auth_message|\n";
	exit;
	}

	$stmt = "SELECT campaign_id from vicidial_campaigns where active = 'Y'";
	$rslt=mysql_to_mysqli($stmt, $link);
	$campaign_ct = mysqli_num_rows($rslt);
	$o = 0;
	while($campaign_ct > $o)
	{
		$row = mysqli_fetch_row($rslt);
		$campaigns_dropdown[] = $row[0];
		$o++;
	}

	$user_string = '"'.implode('", "', explode(",", $user)).'"';


	$stmt="SELECT DATE_FORMAT(event_time,'%Y-%m-%d') as event_date,val.campaign_id,full_name,vu.user,SUM(dispo_sec) as dispo ,SUM(CASE WHEN sub_status = 'LOGIN' THEN 0 WHEN sub_status is null THEN 0 ELSE pause_sec END) as pause_sec,sub_status,SUM(CASE WHEN wait_sec <= vc.dial_timeout THEN wait_sec ELSE vc.dial_timeout END + talk_sec + dispo_sec + pause_sec) as loginWbrk,  SUM(talk_sec) as talk, SUM(CASE WHEN wait_sec <= vc.dial_timeout THEN wait_sec ELSE vc.dial_timeout END) as wait,SUM(CASE WHEN status is not null THEN 1 ELSE 0 END) as count,vu.user_group, SUM(CASE WHEN wait_sec <= vc.dial_timeout THEN wait_sec ELSE vc.dial_timeout END + talk_sec + dispo_sec) as loginWoutbrk, SUM(dead_sec) as dead_sec, SUM(CASE WHEN sub_status = 'LAGGED' THEN pause_sec ELSE 0 END) as lagged, SUM(CASE WHEN sub_status = 'LOGIN'  THEN pause_sec WHEN sub_status != 'LOGIN' or sub_status is not null or sub_status != 'LAGGED'  THEN 0 ELSE pause_sec END) as pause_sec_login, SUM(pause_sec) as pause2 FROM vicidial_users vu,vicidial_agent_log val INNER JOIN vicidial_campaigns vc ON vc.campaign_id = val.campaign_id  WHERE event_time <= '".$end_date."' AND event_time >= '".$event_date."' AND vu.user IN(".$user_string.") AND val.campaign_id = '".$campaign_id."' AND vu.user=val.user AND pause_sec<65000 GROUP BY val.user,full_name,val.campaign_id,DATE_FORMAT(event_time,'%Y-%m-%d') ORDER BY user,full_name,sub_status DESC;";
	$rslt = mysql_to_mysqli($stmt, $link);
	$data_to_print = mysqli_num_rows($rslt);
	$data_to_download['agent'] = array();
	$data_to_download['admin'] = array();
	
	while($row = mysqli_fetch_assoc($rslt))		
	{
		$row['talk'] = $row['talk'] * 1;
		$row['loginWoutbrk'] = $row['loginWoutbrk'] * 1;
		$row['pause2'] = $row['pause2'] * 1;
		$row['pause_sec_login'] = $row['pause_sec_login'] * 1;
		
		$data_to_download['agent'][] = $row;
	}

	if($unique_id)
	{
		$stmt = "SELECT * FROM admin_day_planning_event WHERE unique_id = '".$unique_id."' ORDER BY id ASC ";
		$rslt = mysql_to_mysqli($stmt, $link);
		$total_break_time = 0;
		$total_call_time = 0;
		if($rslt)
		{
			$activityEvent_ct = mysqli_num_rows($rslt);
			$o = 0;

			while($activityEvent_ct > $o)
			{
				$row = mysqli_fetch_array($rslt);
				$data_to_download['admin']['data'][] = $row;
				$total_call_time += $row['time_diff_on_call'];
				$total_break_time += $row['time_diff_on_break'];
				$o++;
			}
			$data_to_download['admin']['total_call_time'] = $total_call_time;
			$data_to_download['admin']['total_break_time'] = $total_break_time;
		}
		else
		{
			$data_to_download['admin'][] = '';
		}
	}

	echo json_encode($data_to_download);

	?>