<?php

/*
 * ****************************************************************************************
 * Copyright (c) 2013 Christopher W. Olsen <cwolsen@ubixtechnologies.com>
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without modification, are
 * permitted provided that the following conditions are met:
 *
 * Redistributions of source code must retain the above copyright notice, this list of
 * conditions, the following disclaimer and the list of authors. Redistributions in binary
 * form must reproduce the above copyright notice, this list of conditions, the following
 * disclaimer and the list of authors in the documentation and/or other materials provided
 * with the distribution. Neither the name of the uBix Cube Project nor the names of its
 * contributors may be used to endorse or promote products derived from this software
 * without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY
 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
 * MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL
 * THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT
 * OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
 * HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR
 * TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * $Id: stats.php 940 2017-09-07 20:12:42Z reddawg $
 *
 * ***************************************************************************************
 */

/* Main include info */
ini_set ( 'include_path', $_SERVER ['DOCUMENT_ROOT'] . "/include/" );
include ("config.php");

/* Assign the page title */
$ubF->tpl->assign ( "PAGE_TITLE", "Admin: Home" );

/* Main defines */
$ubF->tpl->define ( array (
    "index" => "admin/index/index_tv.html" 
) );
$ubF->tpl->define ( array (
    "body" => "admin/main/stats.html" 
) );

$ubF->session->Validate ( 'A_G_', 0 );

$maxODT = 15;
$maxCT = 10;
$maxSubjectLength = 60;

$s_date = date ( "Y-m-d 0:0:0" );
$e_date = date ( "Y-m-d 23:59:59" );

$v_code = 'ubt';

$query = "SELECT SUBSTRING(SUBSTRING_INDEX(channel, '-', 1), 8) as n_src,SUM(billsec) as billsec,count(*) as calls FROM cdr_master WHERE calldate >= '" . $s_date . "'
 AND calldate <= '" . $e_date . "' AND accountcode = '" . $v_code . "' AND channel like 'SIP/" . $v_code . "%' GROUP BY n_src";
$result =$ubF->DB['cdr']->query ( $query );

while ( $qData = $result->fetch_assoc () ) {
  $j_data [$qData ['n_src']] ['outbound_calls'] = $qData ['calls'];
  $j_data [$qData ['n_src']] ['outbound_calltime'] = $qData ['billsec'];
}

$result->free ();

$query = "SELECT SUBSTRING(SUBSTRING_INDEX(dstchannel, '-', 1), 8) as n_dst,SUM(billsec) as billsec,count(*) as calls FROM cdr_master WHERE calldate >= '" . $s_date . "' AND calldate <= '" . $e_date . "' AND accountcode = '" . $v_code . "' AND dstchannel like 'SIP/" . $v_code . "%' GROUP BY n_dst";
$result =$ubF->DB['cdr']->query ( $query );

while ( $qData = $result->fetch_assoc () ) {
  $j_data [$qData ['n_dst']] ['inbound_calls'] = $qData ['calls'];
  $j_data [$qData ['n_dst']] ['inbound_calltime'] = $qData ['billsec'];
}

$result->free ();

$crows = "";

foreach ( $j_data as $key => $val ) {
  $crows .= "<tr><td class=\"ct_row\">" . $key . "</td><td style=\"text-align: center;\">" . $val ['outbound_calls'] . " / " . $val ['inbound_calls'] . "</td><td style=\"text-align: center;\">" . number_format ( $val ['outbound_calltime'] / $val ['outbound_calls'], 2 ) . " / " . number_format ( $val ['inbound_calltime'] / $val ['inbound_calls'], 2 ) . "s</td><td>" . ($val ['outbound_calls'] + $val ['inbound_calls']) . "</td></tr>\n";
}

$ubF->tpl->assign ( "CROWS", $crows );

$odt_rows = "";

switch ( date ( "D" ) ) {
  case 'Mon' :
    $last_act = time () - (60 * 60 * 24 * 3);
  case 'Tues' :
    $last_act = time () - (60 * 60 * 24 * 3);
  default :
    $last_act = time () - (60 * 60 * 24 * 2);
}

$query = "SELECT ticketmaskid, subject, lastactivity FROM swtickets WHERE (ticketstatustitle != 'Completed' AND ticketstatustitle != 'Closed' AND ticketstatustitle != 'To Be Billed' AND ticketstatustitle != 'On Hold') AND lastactivity <= $last_act ORDER BY lastactivity";

$result = $data ['ticket_db']->query ( $query );

if ( $result->num_rows > $maxODT )
  $mRows = $maxODT;
else
  $mRows = $result->num_rows;

$ODTC = $result->num_rows;

$ubF->tpl->assign ( "ODT", $result->num_rows );

for ( $i = 0 ; $i < $mRows ; $i++ ) {
  $qData = $result->fetch_assoc ();
  
  if ( strlen ( $qData ['subject'] ) > $maxSubjectLength )
    $qData ['subject'] = substr ( $qData ['subject'], 0, $maxSubjectLength - 3 ) . "...";
  
  $odt_rows .= "<tr><td>1</td><td>" . $qData ['ticketmaskid'] . "</td><td>" . $qData ['subject'] . "</td><td>" . date ( "m-d-Y H:i", $qData ['lastactivity'] ) . "</td></tr>\n";
}

$result->free ();

$ubF->tpl->assign ( "ODT_ROWS", $odt_rows );

$ct_rows = "";

$query = "SELECT ticketmaskid, subject, lastactivity FROM swtickets WHERE (ticketstatustitle != 'Completed' AND ticketstatustitle != 'Closed' AND ticketstatustitle != 'To Be Billed') AND lastactivity > $last_act ORDER BY lastactivity DESC";

$result = $data ['ticket_db']->query ( $query );

$maxCT += ($maxODT - $mRows);

if ( $result->num_rows > $maxCT )
  $mRows = $maxCT;
else
  $mRows = $result->num_rows;

$CTC = $result->num_rows;

$ubF->tpl->assign ( "CT", $result->num_rows );

for ( $i = 0 ; $i < $mRows ; $i++ ) {
  $qData = $result->fetch_assoc ();
  
  if ( strlen ( $qData ['subject'] ) > $maxSubjectLength )
    $qData ['subject'] = substr ( $qData ['subject'], 0, $maxSubjectLength - 3 ) . "...";
  
  $ct_rows .= "<tr><td>0</td><td>" . $qData ['ticketmaskid'] . "</td><td>" . $qData ['subject'] . "</td><td>" . date ( "m-d-Y H:i", $qData ['lastactivity'] ) . "</td></tr>\n";
}

$result->free ();

$ubF->tpl->assign ( "CT_ROWS", $ct_rows );

if ( $CTC < ($ODTC / 2) )
  $ubF->tpl->assign ( "RATIO_NOTIFICATION", "Ratio Of Overdue Tickets Extremely Unbalanced!" );
else if ( $CTC < $ODTC )
  $ubF->tpl->assign ( "RATIO_NOTIFICATION", "Ratio Of Overdue Tickets Unbalanced!" );
else
  $ubF->tpl->assign ( "RATIO_NOTIFICATION", "Ratio Of Overdue Tickets Neutral" );

$r_ratio = number_format ( $ODTC / $CTC, 2 );

if ( $r_ratio < 1 ) {
  $r_level = "Healthy";
  $r_color = "#323232";
  $b_color = "#323232";
}
else if ( $r_ratio >= 1 && $r_ratio <= 2 ) {
  $r_level = "Very High";
  $r_color = "#63CD0E";
  $b_color = "#63CD0E";
}
else {
  $r_level = "Extremely High";
  $r_color = "#FF0000";
  $b_color = "#FF0000";
  $r_color = "#AC0303";
  $b_color = "#AC0303";
}

$ubF->tpl->assign ( "RATIO_COLOR", $r_color );
$ubF->tpl->assign ( "RATIO_NOTIFICATION", "Ratio " . $r_ratio . ":1 - $r_level" );

$ubF->tpl->assign ( "BACKGROUND_COLOR", $b_color );

$trows = "";

$ubF->tpl->assign ( "TROWS", $trows );

$quote = json_decode ( file_get_contents ( "http://www.iheartquotes.com/api/v1/random?format=json" ) );

$ubF->tpl->assign ( "QUOTE", $quote->quote );
/*
 * $query = "SELECT company, telephone, voip_code FROM accounts ORDER BY company";
 * $res = $data['ubix_db']->query($query);
 *
 * $customer_rows = "";
 * while ($qData = $res->fetch_row()) {
 * $customer_rows .= "<tr><td>" . $qData[0] . "</td><td>" . $qData[1] . "</td><td>" . $qData[2] . "</td><td>" . $qData[3] . "</td></tr>";
 * }
 *
 * $data['tpl']->assign("CUSTOMER_ROWS", $customer_rows);
 */

/* Parse and print */
$ubF->tpl->parse ( "BODY", array (
    "body" 
) );
$ubF->tpl->parse ( "INDEX", array (
    "index" 
) );
$ubF->tpl->FastPrint ( "INDEX" );

?>