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

/*
 * (C) 2016 Christopher Olsen <cwolsen@uBixOS.com>
 *
 * $Id: dialer_cron.php 2230 2015-06-17 19:03:56Z reddawg $
 *
 */
$_LOCK_FILE = "/tmp/reports.lock";
$_LOG_FILE = "cron/reports.log";

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" );

$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 );

?>