<?php

include('../config2.php');

    $query = "SELECT start, finished FROM billingdates ORDER BY start ASC";
    $result = mysql_query($query);


    while ($row2=mysql_fetch_array($result))
    {
        $startdate[] = $row2['start'];
        $enddate[] = $row2['finished'];
    }


$query = "SELECT account, did, calls, msgs, deliveryhistory, frequency FROM reports";
$results = mysql_query($query);

function callsReport()
{
$db = pg_connect('host=10.0.11.64 dbname=call_linx_trans_db_backup user=call_linx password=');

        $query = sprintf("select * from (SELECT to_char(time_start, 'mm-dd-yyyy hh12:mm:ss') AS time_start, dura_caller :: numeric, dura_wrapup ::numeric, callerid, round(round(((round((((COALESCE(dura_caller,.00000000001) :: numeric)/6)+0.49),0)*6)/60),1)+((round((((COALESCE(dura_vmsoutb,.0000000000000001) :: numeric)/6)+0.49),0)*6)/60)+(((round((((COALESCE(dura_wrapup,0) :: numeric)/6)+.49),0)*6)/60)*.5),1) AS agent_time FROM call_stats WHERE mainbox = '" . $row['did'] . "' AND time_start BETWEEN '$startdate[2]' AND '$enddate[2]') as x where x.agent_time > 0 ORDER BY time_start");

        $result = pg_query($query);
        if (!$result) {
            echo "Problem with query " . $query . "<br/>";
            echo pg_last_error();
            exit();
        }

        $num_rows = pg_num_rows($result);

        echo "<div class=\"scrollTableContainer\"><table class=\"dataTable\" cellspacing=\"0\"><thead id='mythead'><tr><th>Start Time</th><th>Agent Time</th><th>Caller ID (When Provided)</th></tr></thead><hr>";

        while($row = pg_fetch_assoc($result)) {
           echo "<tr>";
           echo "<td>" . $row['time_start'] . " </td>";
           echo "<td>";
           echo " " . $row['agent_time'] . " </td>";
           if ( $row['dura_caller'] == 0 ) echo "<td>-</td>";
           else echo "<td>" . $row['callerid'] . " <br /></td></tr>";
        }
        echo "</table></div>";


            $db = pg_connect('host=10.0.11.64 dbname=call_linx_trans_db_backup user=call_linx password=');

    $query2 = "SELECT COUNT(inbound) AS inboundcount FROM call_stats WHERE inbound = 1 AND dura_released = null AND mainbox= '$did' AND time_start between '$startdate[2]' AND '$enddate[2]'";

    $result2 = pg_query($query2);
        if (!$result2) {
            echo "Problem with query " . $query2 . "<br/>";
            echo pg_last_error();
            exit();
        }

$row = pg_fetch_array($result2);

$Inbound = $row['inboundcount'];


$avgcount = "SELECT round((SUM((dura_wait :: numeric))/(SUM(inbound))),1) as avghold FROM call_stats WHERE inbound = 1 AND dura_released = null AND mainbox= '$did' AND time_start between '$startdate[2]' AND '$enddate[2]'";

$avgcountresult = pg_query($avgcount);
        if (!$avgcountresult) {
            echo "Problem with query " . $avgcount . "<br/>";
            echo pg_last_error();
            exit();
        }

$row = pg_fetch_array($avgcountresult);

$AvgHold = $row['avghold'];

$query3 = sprintf("SELECT round(SUM(round(((round((((COALESCE(dura_caller,.00000000001) :: numeric)/6)+0.49),0)*6)/60),1)+((round((((COALESCE(dura_vmsoutb,.0000000000000001) :: numeric)/6)+0.49),0)*6)/60)
+(((round((((COALESCE(dura_wrapup,0) :: numeric)/6)+.49),0)*6)/60)*.5)),1) AS agenttime FROM call_stats WHERE mainbox= '$did' AND time_start between '$startdate[2]' AND '$enddate[2]'");

$result3 = pg_query($query3);
        if (!$result3) {
            echo "Problem with query " . $query3 . "<br/>";
            echo pg_last_error();
            exit();
        }

$row1 = pg_fetch_array($result3);

$TotalMinutes = $row1['agenttime'];

echo "<font size=\"3\"><br /><br />Total Calls: <strong>" . $Inbound . "</strong><br /> Total Minutes Used: <strong>" . $TotalMinutes .  "</strong><br />";
echo " Average Hold (seconds): <strong>"
 . $AvgHold .  "</strong><br /><br /><br />";
echo "</font><br /><br />";
}

while ($row=mysql_fetch_array($results))
    {
        $did=$row['did'];
        $dow = date(l);

        echo $dow;

        // Checks to make sure it can run the report for calls
        if ($row['calls']=="1")
            {
                // Checks the frequency of the run - If it is 1 - it runs daily - If it is 7 it runs weekly - 30 is monthly
                if ($row['frequency']=="1")
                {

                callsReport();

                }
            }

    // This checks the frequency if it is not 1 day a week
    if ($row['frequency']=="7")
                {
                    // Checks to make sure its Monday. If it is not, the report wont run
                    if ($dow=="Monday")
                    {
                        callsReport();


                    }
                }

?>