#!/usr/local/bin/php
<?php

/*
 * (C) 2016 Christopher Olsen <cwolsen@uBixStudios.com>
 *
 * $Id: dialer_cron.php 2230 2015-06-17 19:03:56Z reddawg $
 *
 */

// Defaults
$_LOCK_FILE = "/tmp/reports.lock";
$_LOG_FILE = "cron/reports.log";
$_LOCK_ENABLE = true;

if ($_LOCK_ENABLE == true) {
  if (file_exists ( $_LOCK_FILE )) {
    print "Script is already running.\n";
    // exit ( 0 );
  }
  system ( "/usr/bin/touch " . $_LOCK_FILE );
}

/* Set Root Directory Of Portal */
$root_dir = "/usr/web/sites/portal.spherepbx.com";

/* Main include info */
ini_set ( 'include_path', $root_dir . "/include/" );
include ("config.php");

$log_file = fopen ( $root_dir . "/log/" . $_LOG_FILE, "a" );

// Get Cron Jobs
$query = "SELECT * FROM cron_jobs";
$jRes = $ubF->DB ['main']->query ( $query );

while ($jData = )

$query = "SELECT * FROM cdr WHERE accountcode = '' AND ";

$query = "select account_code,email from accounts where account_code like '32%'";

$aRes = $ubF->DB ['main']->query ( $query );

$rDate = date ( "Y-m-d", strtotime ( "-1 days" ) );
$rDate = date ( "Y-m-d" );

$sDate = $rDate . " 00:00:00";
$eDate = $rDate . " 23:59:59";

while ( $aData = $aRes->fetch_assoc () ) {
  $query = "SELECT sum(billsec) as time,count(*) AS calls,SUBSTRING_INDEX(SUBSTR(channel,5), '-',1) AS chan FROM cdr_master WHERE channel LIKE 'SIP/" . $aData ['account_code'] . "%' AND calldate >= '" . $sDate . "' AND calldate <= '" . $eDate . "' GROUP BY chan";
  $cRes = $ubF->DB ['cdr']->query ( $query );
  
  $query = "SELECT name,RTRIM(SUBSTRING_INDEX(callerid, '<',1)) AS user FROM sippeers WHERE accountcode = '" . $aData ['account_code'] . "'";
  $pRes = $ubF->DB ['voip']->query ( $query );
  
  while ( $pData = $pRes->fetch_assoc () ) {
    $peers [$pData ['name']] ['name'] = $pData ['user'];
    $peers [$pData ['name']] ['ext'] = substr ( $pData ['name'], - 4 );
  }
  $pRes->free ();
  
  $callData = array ();
  
  while ( $cData = $cRes->fetch_assoc () ) {
    $ext = $cData ['chan']; // substr ( $cData ['chan'], - 4 );
    $callData [$ext] ['name'] = $peers [$ext] ['name'];
    $callData [$ext] ['ext'] = $peers [$ext] ['ext'];
    
    $callData [$ext] ['oCalls'] = $cData ['calls'];
    $callData [$ext] ['oTime'] = $cData ['time'];
  }
  $cRes->free ();
  
  $query = "SELECT sum(billsec) as time,count(*) AS calls,SUBSTRING_INDEX(SUBSTR(dstchannel,5), '-',1) AS chan FROM cdr_master WHERE dstchannel LIKE 'SIP/" . $aData ['account_code'] . "%' AND calldate >= '" . $sDate . "' AND calldate <= '" . $eDate . "' GROUP BY chan";
  $cRes = $ubF->DB ['cdr']->query ( $query );
  
  while ( $cData = $cRes->fetch_assoc () ) {
    $ext = $cData ['chan']; // substr ( $cData ['chan'], - 4 );
    $callData [$ext] ['name'] = $peers [$ext] ['name'];
    $callData [$ext] ['ext'] = $peers [$ext] ['ext'];
    $callData [$ext] ['iCalls'] = $cData ['calls'];
    $callData [$ext] ['iTime'] = $cData ['time'];
  }
  $cRes->free ();
  
  $message = "Call Report:<br />\n";
  
  foreach ( $callData as $key => $val ) {
    $message .= "Ext: " . $val ['ext'] . ", Name: " . $val ['name'] . ", Outbound Calls: " . $val ['oCalls'] . " (" . ($val ['oTime'] / 60) . "), Inbound Calls: " . $val ['iCalls'] . " (" . ($val ['iTime'] / 60) . ")<br />\n";
  }
  
  $message .= "<br />\n<br />\nEND OF REPORT<br />\n<br />\n";
  $ubF->libs ['ubf']->sendMail ( "Call Log For " . $rDate, $message, $aData ['email'], "cwolsen@SpherePBX.com", "theubu@gmail.com" );
  
  print "$message";
}

$aRes->free ();

/*
 *
 *
 * $csv_file = fopen ( "/tmp/VI." . time() . ".csv", "w");
 * fwrite ($csv_file, "TN,Network,E911,E411,T38,CNAM Storage,CNAM DIP\n");
 *
 * fwrite ( $log_file, "Start: " . date ( "Y-m-d H:
 * i:s" ) . "\n" );
 *
 *
 * // Stuff Here - This Is A Sloppy Template
 *
 * $result = $ubF->libs ['vi']->auditDIDs ();
 *
 * system ( "echo '" . json_encode ( $result ) . "' >> /tmp/auditDIDs.log" );
 *
 * // Set All The DID For Provider To 0 Before Audit To Purge Lost DID
 * $query = "UPDATE voip_did SET audit = 0 WHERE provider = 1";
 * $ubF->DB ['main']->query ( $query );
 *
 * foreach ( $result->auditDIDsResult->DIDs->DID as $key => $val ) {
 * $val_data = $ubF->DB ['main']->escape_string ( json_encode ( $val ) );
 *
 * $e911 = ($val->has911 == true) ? 1 : 0;
 * $e411 = ($val->has411 == true) ? 1 : 0;
 * $t38 = ($val->t38 == true) ? 1 : 0;
 * $cnam = ($val->cnamStorageAvailability == true) ? 1 : 0;
 * $cnam_dip = ($val->cnam == true) ? 1 : 0;
 *
 * $query = "INSERT INTO voip_did (audit, provider, did, caller_id, network, provider_data, e911, e411, t38, cnam, cnam_dip, group_id) VALUES(1, 1, '" . $val->tn . "','" . $val->cnamName . "','" . $val->Network . "','" . $val_data . "', $e911, $e411, $t38, $cnam, $cnam_dip, '" . $val->didGroupId . "')";
 *
 * $query .= " ON DUPLICATE KEY UPDATE audit = 1, provider = 1, caller_id = '" . $val->cnamName . "', network = '" . $val->Network . "', provider_data = '" . $val_data . "', e911 = " . $e911 . ", e411 = " . $e411 . ", t38 = " . $t38 . ", cnam = " . $cnam . ", cnam_dip = " . $cnam_dip . ", group_id = '" . $val->didGroupId . "'";
 *
 * echo "[" . $query . "]\n";
 *
 * $csv_str = $val->tn . "," . $val->Network . "," . $e911 . "," . $e411 . "," . $t38 . "," . $cnam . "," . $cnam_dip . "\n";
 * fwrite ( $csv_file, $csv_str );
 *
 * $ubF->DB ['main']->query ( $query );
 * }
 *
 * fclose ( $csv_file );
 *
 * print_r ( $result );
 */
fwrite ( $log_file, "Finish: " . date ( "Y-m-d H:i:s" ) . "\n" );

fclose ( $log_file );

system ( "/bin/rm " . $_LOCK_FILE );

?>