[smoothoperator] r4190 committed - Progress commit with new report

[smootho...@googlecode.com]

Revision: 4190
Author: ma...@venturevoip.com
Date: Thu Apr 11 12:40:20 2013
Log: Progress commit with new report
http://code.google.com/p/smoothoperator/source/detail?r=4190

Added:
/SmoothOperatorCRM/report_contact_rate.php
Modified:
/SmoothOperatorCRM/functions/database.php
/SmoothOperatorCRM/report_agent_utilisation.php
/SmoothOperatorCRM/reports.php

=======================================
--- /dev/null
+++ /SmoothOperatorCRM/report_contact_rate.php Thu Apr 11 12:40:20 2013
@@ -0,0 +1,167 @@
+<?
+require "header.php";
+function mysqli_result($res, $row, $field=0) {
+ $res->data_seek($row);
+ $datarow = $res->fetch_array();
+ return $datarow[$field];
+}
+if (!isset($_POST['jobid'])) {
+ echo "<br />";
+ box_start(500);
+ ?><center>
+ <h3>Search for contact rate</h3>
+ <form action = "report_contact_rate.php" method="post">
+ Job Name: <select name="jobid">
+ <?
+ $result = mysqli_query($connection, "SELECT id, name FROM jobs");
+ while ($row = mysqli_fetch_assoc($result)) {
+ print_pre($row);
+ echo '<option value="'.$row['id'].'">'.$row['name'].'</option>';
+ }
+ ?>
+ </select><br />
+ Time Period:<select name="range" id="range">
+ <option value="alltime">All Time</option>
+ <?/*<option value="today">Today</option>
+ <option value="date">Select Date</option>*/?>
+ </select><br />
+ <div id="dateselect" style="display:none">
+ <p>From Date:
+ <input type="text" id="from_date" name="from_date" style="width:
200px">
+ <br />
+ To Date:
+ <input type="text" id="to_date" name="to_date" style="width: 200px">
+ <br />
+
+ </div>
+ <input type="submit" value="Display Results"><br />
+ <br />
+ </form>
+ <script>
+ jQuery(function() {
+ jQuery( "#from_date" ).datepicker({
+ dateFormat : 'yy-mm-dd'
+ });
+ jQuery( "#to_date" ).datepicker({
+ dateFormat : 'yy-mm-dd'
+ });
+
+ });
+ jQuery(function() { // Makes sure the code contained doesn't run
until
+ // all the DOM elements have loaded
+
+ jQuery('#range').change(function(){
+ //alert("x");
+ //alert($('#range').val());
+ if (jQuery('#range').val() == "date") {
+ jQuery('#dateselect').show();
+ } else {
+ jQuery('#dateselect').hide();
+
+ }
+
+ });
+ });
+ </script>
+
+ <?
+ box_end();
+ require "footer.php";
+ exit(0);
+}
+$result = mysqli_query($connection, "SELECT name FROM jobs WHERE id
= ".sanitize($_POST['jobid']));
+$name = mysqli_result($result,0,0);
+
+/* This is a two step process. Step one
+ *
+ */
+
+
+$sql = "select * from campaign_stats where campaign_id
= ".(100000+$_POST['jobid'])." and report_date = curdate() order by
report_time desc limit 1";
+$result = mysqli_query($connection, $sql);
+$total = 0;
+$answered = 0;
+while ($row = mysqli_fetch_assoc($result)) {
+ print_pre($row);
+ $total += $row['new'];
+ $total += $row['answered'];
+ $total += $row['busy'];
+ $total += $row['congested'];
+ $total += $row['amd'];
+ $total += $row['unknown'];
+ $total += $row['pressed1'];
+ $total += $row['hungup'];
+ $total += $row['timeout'];
+
+ $answered += $row['answered'];
+ $answered += $row['amd'];
+ $answered += $row['pressed1'];
+}
+//echo $answered."/".$total."<br />";
+
+$sql = "select count(*), text, not_contacted from customer_dispositions,
job_dispositions where customer_dispositions.job_id
= ".sanitize($_POST['jobid'])." and customer_dispositions.disposition =
job_dispositions.id group by disposition";
+$result = mysqli_query($connection, $sql);
+$total_disp = 0;
+$not_answered_disp = 0;
+while ($row = mysqli_fetch_assoc($result)) {
+ $total_disp += $row['count(*)'];
+ if ($row['not_contacted'] == 1) {
+ $not_answered_disp += $row['count(*)'];
+ }
+ //print_pre($row);
+}
+//echo $not_answered_disp;
+
+//exit(0);
+
+switch ($_POST['range']) {
+ case "today":
+ $sql = "select count(*), text from customer_dispositions,
job_dispositions where customer_dispositions.job_id
= ".sanitize($_POST['jobid'])." and customer_dispositions.disposition =
job_dispositions.id and date(contact_date_time) = curdate() group by
disposition";
+ $title = 'Dispositions for '.ucwords($name).' Today';
+ break;
+ case "date":
+ $sql = "select count(*), text from customer_dispositions,
job_dispositions where customer_dispositions.job_id
= ".sanitize($_POST['jobid'])." and customer_dispositions.disposition =
job_dispositions.id and date(contact_date_time)
between ".sanitize($_POST['from_date'])."
and ".sanitize($_POST['to_date'])." group by disposition";
+ $title = 'Dispositions for '.ucwords($name).'
Between '.$_POST['from_date'].' and '.$_POST['to_date'];
+ break;
+ default:
+ $sql = "select count(*), text from customer_dispositions,
job_dispositions where customer_dispositions.job_id
= ".sanitize($_POST['jobid'])." and customer_dispositions.disposition =
job_dispositions.id group by disposition";
+ $title = 'Dispositions for '.ucwords($name).' All Time';
+ break;
+}
+?>
+<script type="text/javascript" src="https://www.google.com/jsapi"></script>
+<script type="text/javascript">
+google.load("visualization", "1", {packages: ["corechart"]});
+google.setOnLoadCallback(drawChart);
+function drawChart() {
+
+ var data = new google.visualization.DataTable();
+
+
+ var data = google.visualization.arrayToDataTable([
+
['Disposition', 'Count'],
+ <?
+ //$result =
mysqli_query($connection, $sql) or die(mysqli_error($connection));
+ //while ($row =
mysqli_fetch_assoc($result)) {
+
echo "['Contacted', ".($answered-$not_answered_disp)."],\n";
+ echo "['Dispositioned
Not Contacted', ".($not_answered_disp)."],\n";
+ echo "['Not
Contacted', ".($total-$answered)."],\n";
+ //}
+ ?>
+
+ ]);
+
+
+
+
+ var options = {
+ title: '<?=$title?>'
+ };
+ var chart = new
google.visualization.PieChart(document.getElementById('chart_div'));
+ chart.draw(data, options);
+}
+</script>
+<div id="chart_div" style="width: 900px; height: 500px;"></div>
+<?
+require "footer.php";
+?>
=======================================
--- /SmoothOperatorCRM/functions/database.php Thu Apr 11 09:17:39 2013
+++ /SmoothOperatorCRM/functions/database.php Thu Apr 11 12:40:20 2013
@@ -309,7 +309,8 @@
(21,'Add
Script','en',1,'scripts.php?add=1',0,0,1,8,'database_add.png'),
(23,'Dialer','en',10,'dialer.php',0,0,1,-1,'phone.png'),
(24,'Disposition
Report','en',10,'report_dispositions.php',0,0,0,-1,NULL),
- (25,'Agent Utilisation
Report','en',10,'report_agent_utilisation.php',0,0,0,-1,NULL)";
+ (25,'Agent Utilisation
Report','en',10,'report_agent_utilisation.php',0,0,0,-1,NULL),
+ (26,'Contact Rate
Report','en',10,'report_contact_rate.php',0,0,0,-1,NULL)";
$result = mysqli_query($link, $sql);
}

=======================================
--- /SmoothOperatorCRM/report_agent_utilisation.php Wed Apr 10 07:17:15 2013
+++ /SmoothOperatorCRM/report_agent_utilisation.php Thu Apr 11 12:40:20 2013
@@ -9,7 +9,7 @@
echo "<br />";
box_start(500);
?><center>
- <h3>Search for dispositions</h3>
+ <h3>Search for utilisation</h3>
<form action = "report_agent_utilisation.php" method="post">
Agent: <select name="user_id">
<?
=======================================
--- /SmoothOperatorCRM/reports.php Wed Apr 10 07:17:15 2013
+++ /SmoothOperatorCRM/reports.php Thu Apr 11 12:40:20 2013
@@ -14,7 +14,7 @@
<?/* <a href="drawpie.php" class="new_button"><img
src="images/crystal/apps/blockdevice.png" width="96px" height="96px"><br
/>Number Statuses</a>*/?>
<a href="report_dispositions.php" class="new_button"><img
src="images/crystal/apps/Volume%20Manager.png" width="96px"
height="96px"><br />Disposition Chart</a>
<a href="report_agent_utilisation.php" class="new_button"><img
src="images/crystal/apps/xclock.png" width="96px" height="96px"><br />Agent
Dispositions</a><br />
-
+ <a href="report_contact_rate.php" class="new_button"><img
src="images/crystal/apps/irc_protocol.png" width="96px" height="96px"><br
/>Contact Rate</a>
<a href="index.php" class="new_button_home"><img
src="images/crystal/apps/kfm_home.png" width="96px" height="96px"><br
/>Home</a>
<?
}