ivr_report.prg

<?php
$monLibrary = dirname(__FILE__) . "/../library";
$sharedLibrary = dirname(__FILE__) . "/../../shared/library";
require_once("$monLibrary/cu_top.i");
require_once("$monLibrary/ck_hticket.i");
require_once("$monLibrary/cu_pass.i");
require_once("$sharedLibrary/cu_flagconst.i");

//error_reporting(E_ALL & ~E_NOTICE);
/*
 * NOTE: Need to get a valid domain for the IVR server so can utilize the Monitor cookie and get $Hu, among other things.
 *       Uncomment ck_hticket.i when that happens.
  if (!CheckPerm($link, $Hu, basename($_SERVER['SCRIPT_NAME']), $_SERVER['REMOTE_ADDR'])) {
    // ** Permissions failed
    // ** redirect to new page
    header("Location: /hcuadm/hcu_noperm.prg");
    exit;
  }
*/

$dms_ok=array("Flang"=>"string", "action"=>"string",
              "cu"=>"string", "reportstartdate"=>"string", "reportenddate"=>"string",
              "dnid"=>"string", "livebatch"=>"string", "liveserver"=>"string",
              "ahdropdays"=>"string", "lhdropdays"=>"string", "byDNID" => "string",
              "retrylimit"=>"digits", "histdays"=>"digits",
              "rv"=>"string");

dms_import_v2($gNameSpace, "POST", $dms_ok);

  $action = $gNameSpace["POST"]["action"];
  $cu = $gNameSpace["POST"]["cu"];
  $byDNID = $gNameSpace["POST"]["byDNID"];
  $byDNID = isset($byDNID) && $byDNID == "Y";
  $self= $_SERVER['PHP_SELF'];
  $cuName = strtoupper( $cu );
  $cuLower = strtolower( $cu );

  $reportStartDate = $gNameSpace["POST"]["reportstartdate"];
  if ( empty( $reportStartDate ) )
    $reportStartDate = date( "m/01/Y" );
  $reportEndDate = $gNameSpace["POST"]["reportenddate"];
  if ( empty( $reportEndDate ) )
    $reportEndDate = date( "m/t/Y" );

  // Expose column to query if option is used.
  $aDNIDColumn = $byDNID ? ", ivr_call.call_dnid" : "";

  $callTime = "(coalesce(Extract(epoch from ev.evend)::integer, 0) - Extract(epoch from ev.evstart)::integer)";

  $where = "(b.event_date >= date '" . dms_escape_string($reportStartDate) . "'
                AND b.event_date < date '" . dms_escape_string($reportEndDate) . "' + interval '1 day')";

  $sql = "SELECT
          ivr_call.call_cu $aDNIDColumn,
          count(ivr_call.call_id) as calls_count,
          coalesce(avg(CASE WHEN ev.evstart = ev.evend THEN NULL WHEN $callTime <= 0 THEN NULL ELSE $callTime END), 0) as calls_average
          FROM ivr_call
          LEFT JOIN (
            SELECT a.call_id, MIN(b.event_date), MAX(a.event_date)
            FROM ivr_event a
            INNER JOIN ivr_event b ON a.call_id = b.call_id AND b.event_code = 'A'
            WHERE $where
            GROUP BY a.call_id
          ) as ev(call_id, evstart, evend) ON ivr_call.call_id = ev.call_id
          WHERE coalesce(Extract(epoch from ev.evend)::integer, 0) > 0 AND ivr_call.call_cu IS NOT NULL";

  if ( strlen( $cu ) > 0 ) {
    $sql .= "  AND ivr_call.call_cu = '$cu'";
  }

  $sql .= "  GROUP BY call_cu $aDNIDColumn
             ORDER BY call_cu $aDNIDColumn";

  $sthCalls = db_query($sql, $dbh);

  $displayData = array();

  if ( $sthCalls ) {
    $rowCnt = 0;
    $totalCalls = 0;
    $totalSeconds = 0;
    $sumTotalMinutes = 0;
    while ( $row = db_fetch_array($sthCalls, $rowCnt++ ) ) {
      $callCU = $row["call_cu"];
      $callCount = $row['calls_count'];
      $callAvg = number_format( $row['calls_average'], 1 );
      $callTotal = $callCount * $row['calls_average'];

      $callDNID = $byDNID ? intval($row["call_dnid"]) : 0;

      // ** ONLY Show the value in Total Minutes
      $callTotalHours = floor( $callTotal / 60 );
      $sumTotalMinutes += $callTotalHours;

      $callTotalStr = "$callTotalHours";

      $callAvgHours = floor( ( $callAvg / 3600 ) );
      $callAvgMinutes = floor( ( ( $callAvg - $callAvgHours * 3600 ) / 60 ) );
      $callAvgSeconds = round( $callAvg - $callAvgMinutes * 60 - $callAvgHours * 3660 );

      $callAvgStr = "$callAvgHours:$callAvgMinutes:$callAvgSeconds";

      $displayData[] = array( "callCU" => $callCU,
                              "callDNID" => $callDNID,
                              "callCount" => $callCount,
                              "callTotal" => $callTotalStr,
                              "callAvg" => $callAvgStr );

      // add to the totals
      $totalCalls += $callCount;
      $totalSeconds += $callTotal;
    }

    $callTotalStr = "$sumTotalMinutes";

    if ( $totalCalls > 0 ) {
      $totalAvg = number_format( $totalSeconds / $totalCalls, 1 );
    } else {
      $totalAvg = 0;
    }

    $callAvgHours = floor( ( $totalAvg / 3600 ) );
    $callAvgMinutes = floor( ( ( $totalAvg - $callAvgHours * 3600 ) / 60 ) );
    $callAvgSeconds = round( $totalAvg - $callAvgMinutes * 60 - $callAvgHours * 3660 );

    $totalAvgStr = "$callAvgHours:$callAvgMinutes:$callAvgSeconds";

    if ( $gNameSpace["POST"]["rv"] == "RAW" ) {
        // return the data in a CSV style
        $outString = "";

        // totals
        $totalInfo = Array(0, 0, 0, 0);

        // start a header
        $outString .= "CU," . ($byDNID ? "DNID," : "") . "Number Calls,Total Call Time (minutes),Average Call Time\r\n";

        for ( $i = 0; $i < count( $displayData ); $i++ ) {
          $outString .= "{$displayData[$i]['callCU']}," . ($byDNID ? $displayData[$i]["callDNID"] . "," : "") . "{$displayData[$i]['callCount']}," .
                        "{$displayData[$i]['callTotal']},{$displayData[$i]['callAvg']}\r\n";
        }

        $outString .= "T," . ($byDNID ? "," : "") . "$totalCalls,$callTotalStr,$totalAvgStr\r\n";

        header( "Content-length: " . strlen($outString) );
        header( "Content-type: application/octetstream" );
        header( "Content-disposition: inline; filename=\"download.csv\"" );

        print $outString;

        exit;
      }

  }

  // show the report visually
  cu_header("Credit Union IVR Report");

  ?>
  <link href="https://<?php echo $cloudfrontDomainName; ?>/homecu/css/KendoUI/<?php echo $homecuKendoVersion; ?>/kendo.common.min.css" rel="stylesheet">
  <link href="https://<?php echo $cloudfrontDomainName; ?>/homecu/css/KendoUI/<?php echo $homecuKendoVersion; ?>/kendo.default.min.css" rel="stylesheet">
  <link href="https://<?php echo $cloudfrontDomainName; ?>/homecu/css/kendo.homecucustom.4492.min.css" type="text/css" rel="stylesheet">
  <script type="text/javascript" src="https://<?php echo $cloudfrontDomainName; ?>/jquery/js/jquery-1.9.1.min.js"></script>
  <script src="https://<?php echo $cloudfrontDomainName; ?>/homecu/js/KendoUI/<?php echo $homecuKendoVersion; ?>/kendo.core.min.js"></script>
  <script src="https://<?php echo $cloudfrontDomainName ?>/homecu/js/KendoUI/<?php echo $homecuKendoVersion ?>/kendo.custom.min.js.gz"></script>
  <script>
      $(document).ready(function() {
          // create DatePicker from input HTML element
          $("#reportStartDate").kendoDatePicker();

          $("#reportEndDate").kendoDatePicker();
      });
  </script>
  <form name="inputs" action="<?php echo $self?>" method="post">
  <table class="dmsbg" style="border-radius:5px;">
      <tr>
       <td colspan="4" class="bartitle" style="text-align:center;">IVR Call Report</td>
      </tr>
      <tr>
       <td colspan="4" style="background-color:white;">
         <a href="<?php echo "$produrl/monitor/mindex.html" ?>">Return to Monitor</a>
       </td>
      </tr>
<?php

  // allow user to enter a date range and pick a CU (otional)
    $setDNID = ($byDNID ? "CHECKED" : "");
    print <<<EOF
        <tr><td class='hdr' align=right>Data Range: </td>
            <td class='dtl'><input type="text" id="reportStartDate" name="reportstartdate" value="$reportStartDate" style='color:black;'></td>
            <td class='dtl'><input type="text" id="reportEndDate" name="reportenddate" value="$reportEndDate" style='color:black;'></td>
          <td class='dtl'>&nbsp;</td>
        </tr>
        <tr>
        <tr><td class='hdr' align=right>&nbsp;</td>
            <td class='dtl'><input type="checkbox" name="byDNID" value="Y" $setDNID>Show Long Code?</td>
            <td class='dtl' colspan='2'>&nbsp;</td>
        </tr>
EOF;

  // get a list of active CUs
  $sql = "SELECT DISTINCT call_cu
          FROM ivr_call
          WHERE call_cu IS NOT NULL
          ORDER BY call_cu";
  $sth = db_query($sql, $dbh);

  if ( $sth ) {

    print <<<EOF
        <tr><td class='hdr' align=right>Credit Union Filter: </td>
            <td class='dtl'><select name="cu" size="1">
              <option value="">Show All</option>
EOF;
    $rowCnt = 0;
    while ( $row = db_fetch_array($sth, $rowCnt++) ) {
      $thisCU = trim( $row["call_cu"] );
      printf("\n<option value=\"%s\" %s>%s</option>", $thisCU, (strlen( $cu ) && $thisCU == $cu) ? "SELECTED" : "", $thisCU);
    }

    print <<<EOF
          </select></td>
          <td class='dtl'>&nbsp;</td>
          <td class='dtl'>&nbsp;</td>
      </tr>
EOF;
  }

  print <<<EOF
            <tr><td colspan="4"><input type="submit" value="Submit"></td></tr>
EOF;

  $aDNIDDisplayTop = $byDNID ? "<th class='hdrc'>Long Code</th>" : "";
  $aDNIDDisplayColspan = $byDNID ? "colspan='2'" : "";
  if ( $sthCalls ) {
        print <<<EOF
      <tr>
        <th class='hdrc'>CU</th>
        $aDNIDDisplayTop
        <th class='hdrc'>Number Calls</th>
        <th class='hdrc'>Total Call Time (minutes)</th>
        <th class='hdrc'>Average Call Time</th>\n
EOF;

    $RGB = "odd";
    for ( $i = 0; $i < count( $displayData ); $i++ ) {
      print "<tr>
              <td class='dtl $RGB'>&nbsp;{$displayData[$i]['callCU']}&nbsp;</td>"
              . ($byDNID ? "<td class='dtl $RGB'>&nbsp;{$displayData[$i]['callDNID']}&nbsp;</td>" : "") . "
              <td class='dtl $RGB'>&nbsp;{$displayData[$i]['callCount']}&nbsp;</td>
              <td class='dtl $RGB'>&nbsp;{$displayData[$i]['callTotal']}&nbsp;</td>
              <td class='dtl $RGB'>&nbsp;{$displayData[$i]['callAvg']}&nbsp;</td>
              </tr>";

      $RGB = ($RGB == "odd" ? "even" : "odd");
    }

    print "<tr style='color:white; font-weight:bold;'>
            <td $aDNIDDisplayColspan>&nbsp;Totals:&nbsp;</td>
            <td>&nbsp;$totalCalls&nbsp;</td>
            <td>&nbsp;$callTotalStr&nbsp;</td>
            <td>&nbsp;$totalAvgStr&nbsp;</td>
            </tr>";
  } else {
    print "Error reading call log tables";
  }

  print "</table>";
  print "</form>";

  cu_footer();

    function dms_escape_string($value, $len=0, $ignore_mq=0) {
        /*
            PARAMETERS:
        *       value       - This is the value that this function works with.
        *       len             - If desired, the returned value can be truncated to
                                        a certain number of characters.  To truncate, pass a
                                        positive integer in this field.  To not truncate pass a 0
        *       ignore_mq - This paramter will check to see if we check the magic quotes
                                        setting and if set, the stripslashes function will be used.
                                        This is useful when preparing values for the database that were
                                        posted.
        */

        // ** ignore any stripping of slashes if ignore_mq is set
        if ($ignore_mq == 0) {
            // First if magic quotes is on -- then strip slashes
            if (get_magic_quotes_gpc())
                $value = stripslashes($value);
        }
        $value = trim($value);                  // Trim the resulting value

        // ** Before escaping the string, be certain it is not longer than the length that was
            // specified
        if ($len > 0) {
            $value = substr($value, 0, $len);
        }

        // ** Escape the string to be saved
        $ret_value = pg_escape_string($value);

        return $ret_value;
    }
?>