Jump to content

Help with PHP error


n3mesis125

Recommended Posts

Hey Folks,

 

The below code I've pasted here throws a page cannot be displayed 500 Internal Error message if you choose 'Date Range' from the filter and pick a full month. Seems like when ever I pull so much data for an entire month, it bogs down when running my foreach() loops. I've tried just print_r($data) to show just the data for the range I choose and the page displays fine if I choose a month. So basically I've determine its a problem with the foreach() loops or functions I'm creating. I've pasted the code below and here is the link to check the live version:

 

http://tinyurl.com/5tozk3

 

 

PHP Code:

<?php

$range = "tw";

if (isset($_GET['sel_date'])) {
$range = $_GET['sel_date'];
$getSpec = $_GET['specdate'];
$getFirst = $_GET['rangeone'];
$getLast = $_GET['rangetwo'];
}

include("../includes/filter_scdpr.inc");

require_once('../Connections/rogers.php');
mysql_select_db($database_rogers, $rogers);

$sql = "SELECT DISTINCT COUNT(`lob`) as `lobtotal`, COUNT(`problem`) as `prtotal`,
	SUM(`resolved`) as `restotal`, COUNT(`service_call`) as `sctotal`, SUM(`aht`) as `myaht`, lob, problem, service_call,
	SUM(UNIX_TIMESTAMP(`sca_date`) - UNIX_TIMESTAMP(`scr_date`)) as `sc_age_time`, `date`
	FROM `paroccs_data`
	WHERE ".$date_range." AND LENGTH(`problem`) > 3
	GROUP BY `date`, `lob`, `problem`, `service_call`";
$res = mysql_query($sql) or die(mysql_error());

$data=array();
while($r = mysql_fetch_array($res))
{
$data[] = array(
	'date' => $r['date'],
	'lob' => $r['lob'],
	'pr' => $r['problem'],
	'prtotal' => $r['prtotal'],
	'sc' => $r['service_call'],
	'sctotal' => $r['sctotal'],
	'resolved' => $r['restotal'],
	'aht' => $r['myaht'],
	'sc_age' => $r['sc_age_time']);
}

$sql2 = "SELECT `prid`, `desc`
	 FROM `occs_pids`";
$res2 = mysql_query($sql2) or die(mysql_error());

$descs=array();
while ($t = mysql_fetch_array($res2))
{
$breakdesc = explode("#", $t['desc']);
$setdesc = $breakdesc[0];

if (strlen($setdesc) > 60) {
	$setdesc = substr($setdesc,0,57) . "...";
}

$descs[] = array(
	'pr' => $t['prid'],
	'desc' => $setdesc);
}

$overall=totalCount();

$myPR = filter("pr");
asort($myPR);
$output = "<table width='100%' border='0' cellspacing='0' cellpadding='0' id='details_header'>";
$output .= "<tr>";
	$output .= "<td width='390' valign='bottom'>Problem #</td>";
	$output .= "<td width='90' align='right' valign='bottom'>Service Call<br/>Volume</td>";
	$output .= "<td width='90' align='right' valign='bottom'>Avg<br/>Touch</td>";
	$output .= "<td width='90' align='right' valign='bottom'>% of Total<br/>Volume</td>";
	$output .= "<td width='120' align='right' valign='bottom'>Handle<br/>Time</td>";
	$output .= "<td width='80' align='right' valign='bottom'>%<br/>Resolved</td>";
	$output .= "<td width='100' align='right' valign='bottom'>SCAA</td>";
	$output .= "<td width='9'><img src='../imgs/transp.gif' width='9'/></td>";
$output .= "</tr>";
$output .= "<tr id='smallborder'><td colspan='8'><img src='../imgs/transp.gif' height='10'/></td></tr>";
$output .= "</table>";

$output .= "<div id='scrolldata'>";
$output .= "<table width='100%' border='0' cellspacing='0' cellpadding='0' id='details_content'>";
$output .= "<tr><td colspan='7'><img src='../imgs/transp.gif' height='6'/></td></tr>";

foreach($myPR as $m)
{
$pr_count = myCount("pr", $m, false);
$my_sc_list = mySC($m);
$pr_unique = count($my_sc_list);
$pr_desc = getDesc($m);
$has_sc = count($my_sc_list);
$pr_aht = myAHT("pr", $m, false);
$pr_resolve = checkResolve("pr", $m, false);
$pr_res_perc = number_format(100*($pr_resolve / $pr_unique),1);
$pr_pot = number_format(100*($pr_count / $overall),1);
$pr_scage = getSCAA("pr", $m, false);
$occs_touch = OCAT($m);
$occs_avg_touch = number_format($occs_touch / $pr_resolve,1);

if ($pr_scage < 0) {
	$pr_avg_scaa = "<font color='red'>error</font>";
} else {
	$pr_avg_scaa = sec2hms(($pr_scage / $pr_resolve),true);
}	

asort($my_sc_list); // Sort the Service Call Array before outputting data.

$output .= "<tr>";
	$output .= "<td width='390'><a href='javascript:openSub(\"".$m."\");'>".$m."</a> - ".$pr_desc."</td>";
	$output .= "<td width='90' align='right'>".$pr_count."</td>";
	$output .= "<td width='90' align='right'>".$occs_avg_touch."</td>";
	$output .= "<td width='90' align='right'>".$pr_pot."%</td>";		
	$output .= "<td width='120' align='right'>".sec2hms($pr_aht,false)."</td>";
	$output .= "<td width='80' align='right'>".$pr_res_perc."%</td>";
	$output .= "<td width='100' align='right'>".$pr_avg_scaa."</td>";
$output .= "</tr>";

if ($has_sc > 0)
{
	foreach($my_sc_list as $sc)
	{
		$sc_count = myCount("sc", $m, $sc);
		$sc_aht = myAHT("sc", $m, $sc);
		$sc_resolve = checkResolve("sc", $m, $sc);
		$sc_is_res=0;

		$sc_days = scDays($m, $sc);

		if ($sc_resolve > 0) {
			$sc_is_res=100;
		}

		$sc_pot = number_format(100*($sc_count / $overall),1);
		$sc_scage = getSCAA("sc", $m, $sc);

		if ($sc_scage < 0) {
			$sc_avg_scaa = "<font color='red'>error</font>";
		} else {
			$sc_avg_scaa = sec2hms(($sc_scage / $sc_count),true);
		}

		$sclink = "<a href='javascript:showSCD(\"$sc\",\"$m\");'>".$sc."</a>";
		$sc_spacer = "  <img src='../imgs/sub_link.gif' align='absmiddle'/> ";

		$output .= "<tr id='".$m."' style='display:none;' onClick='openSub(\"$sc\")' onmouseover='this.className=\"on\"' onmouseout='this.className=\"off\"'>";
			$output .= "<td width='390'>".$sc_spacer."SC: ".$sclink."</td>";
			$output .= "<td width='90' align='right'>".$sc_count."</td>";
			$output .= "<td width='90' align='right'> </td>";
			$output .= "<td width='90' align='right'>".$sc_pot."%</td>";
			$output .= "<td width='120' align='right'>".sec2hms($sc_aht,false)."</td>";
			$output .= "<td align='right' width='80'>".$sc_is_res."%</td>";
			$output .= "<td align='right' width='100'>".$sc_avg_scaa."</td>";
		$output .= "</tr>";

		if ($sc_days != "")
		{
			$output .= "<tr id='".$sc."' style='display:none;'>";
			$output .= "<td colspan='7' width='100%' align='left' valign='top'><img src='../imgs/transp.gif' height='3'/><br/>";
			$output .= "<table width='100%' border='0' cellpadding='0' cellspacing='0' id='subcontent'>";

			foreach($sc_days as $scd)
			{
				$output .= "<tr>";
					$output .= "<td>".$scd."</td>";
				$output .= "</tr>";
			}

			$output .= "<tr><td colspan='6'><img src='../imgs/transp.gif' height='8'/></td></tr>";

			$output .= "</table>";
			$output .= "</td>";
			$output .= "</tr>";

		}

	}

}

$output .= "<tr><td colspan='7'><img src='../imgs/transp.gif' height='8'/></td></tr>";
}

$output .= "</table>";
$output .= "</div>";

function filter($type)
{
global $data;
$prlist=array();

foreach($data as $d)
{
	if ($type == "pr")
	{
		$prlist[] = $d['pr'];
	}	
}
if ($type == "pr"){
	$fixlist = array_unique($prlist);
	return $fixlist;	
}
}

function OCAT($pr)
{
global $data;
$count=0;
$reslist=array();

foreach($data as $d)
{
	if ($d['pr'] == $pr && $d['resolved'] == 1) {
		$reslist[] = array(
			'pr' => $d['pr'],
			'sc' => $d['sc']);
	}	
}
foreach($data as $d)
{
	foreach($reslist as $r)
	{
		if ($d['pr'] == $r['pr'] && $d['sc'] == $r['sc']) {
			$count = $count + $d['sctotal'];
		}		
	}
}
return $count;
}

function myCount($type, $pr, $sc)
{
global $data;
$prcount=0;
$sctotal=0;
$res_pr_total=0;
$res_sc_total=0;

foreach($data as $d)
{
	if ($type == "pr" && $pr == $d['pr']) {
		$prcount = $prcount + $d['prtotal'];
	} else if ($type == "sc" && $pr == $d['pr'] && $sc == $d['sc']) {
		$sctotal = $sctotal + $d['sctotal'];
	} else if ($type == "resolve" && $pr == $d['pr'] && $sc == false) {
		$res_pr_total = $res_pr_total + $d['resolved'];
	} else if ($type == "resolve" && $pr == $d['pr'] && $sc == $d['sc']) {
		$res_sc_total = $res_sc_total + $d['resolved'];
	}
}
if ($type == "pr"){
	return $prcount;
} else if ($type == "sc") {
	return $sctotal;
} else if ($type == "resolve" && $sc == false) {
	return $res_pr_total;
} else if ($type == "resolve" && $sc != false) {
	return $res_sc_total;
}
}

function mySC($pr)
{
global $data;
$sclist=array();

foreach($data as $d)
{
	if ($pr == $d['pr']){
		$sclist[] = $d['sc'];
	}	
}
$fixlist = array_unique($sclist);
return $fixlist;
}

function myAHT($type, $pr, $sc)
{
global $data;
$pr_aht=0;
$sc_aht=0;

foreach ($data as $d)
{
	if ($d['pr'] == $pr && $type == "pr") {
		$pr_aht = $pr_aht + $d['aht'];
	} else if ($d['pr'] == $pr && $sc == $d['sc'] && $type == "sc") {
		$sc_aht = $sc_aht + $d['aht'];
	}	
}
if ($type == "pr") {
	return $pr_aht;
} else if ($type == "sc") {
	return $sc_aht;
}
}

function totalCount()
{
global $data;
$total=0;
foreach($data as $d)
{
	$total = $total + $d['prtotal'];	
}
return $total;
}

function sec2hms ($sec, $padDays = false)
{
    $hms = "";

$days = intval(intval($sec) / 86400);
if ($days > 0) {
	$hms .= ($padDays)
		  ? str_pad($days, 2, "0", STR_PAD_LEFT). 'd '
		  : '';
}

$hours = intval((intval($sec) / 3600) % 24); 
$hms .= str_pad($hours, 2, "0", STR_PAD_LEFT). 'h ';

    $minutes = intval(($sec / 60) % 60); 
    $hms .= str_pad($minutes, 2, "0", STR_PAD_LEFT). 'm ';

if ($days <= 0) {
    $seconds = intval($sec % 60); 
    	$hms .= str_pad($seconds, 2, "0", STR_PAD_LEFT) . "s";
}

    return $hms;
}

function getDesc($pr)
{
global $descs;
$myDesc="";

foreach($descs as $d)
{
	if ($d['pr'] == $pr) {
		$myDesc = $d['desc'];
	}	
}
return $myDesc;
}

function getSCAA($type, $pr, $sc)
{
global $data;
$pr_scaa=0;
$sc_scaa=0;

foreach($data as $d)
{
	if ($type == "pr" && $d['pr'] == $pr && $d['resolved'] == 1) {
		$pr_scaa = $pr_scaa + $d['sc_age'];
	} else if ($type == "sc" && $d['pr'] == $pr && $sc == $d['sc'] && $d['resolved'] == 1) {
		$sc_scaa = $d['sc_age'];
	}
}
if ($type == "pr") {
	return $pr_scaa;
} else if ($type == "sc") {
	return $sc_scaa;
}
}

function checkResolve($type, $pr, $sc)
{
global $data;
global $my_sc_list;

$resolve_count=0;
$resolve_sc_count=0;

foreach($data as $d)
{
	if ($type == "pr" && $d['pr'] == $pr && $sc == false && $d['resolved'] > 0) {
		$resolve_count++;
	} else if ($type == "sc" && $d['pr'] == $pr && $sc == $d['sc'] && $d['resolved'] > 0) {
		$resolve_sc_count++;
	}
}
if ($type == "pr") {
	return $resolve_count;
} else if ($type == "sc") {
	return $resolve_sc_count;
}
}

function scDays($pr, $sc)
{
global $data;
$list="";
foreach($data as $d)
{
	if ($d['sc'] == $sc && $pr == $d['pr']) {
		$list[] = $d['date'];
	}
}
$newlist = array_unique($list);
return $newlist;
}

?>
<html>
<head>
<link href="../styles/text.css" rel="stylesheet" type="text/css">
<title>OCCS - Service Call Details by Problem Record</title>
<script type="text/javascript" src="../scripts/calendarDateInput.js"></script>
<script language='javascript'>

function dateField(obj, sel)
{
var obj;
var sel;
var dr_t = document.getElementById("dr_text");
var dr_f = document.getElementById("dr_fields");
var spec = document.getElementById("specify");

if (sel == 6) {
	dr_t.style.display = "block";
	dr_f.style.display = "block";
} else {
	dr_t.style.display = "none";
	dr_f.style.display = "none";
}

if (sel == 7) {
	spec.style.display = "block";
} else {
	spec.style.display = "none";
}
}

function setrange()
{
var range = "<? echo $range; ?>";
var range_sel = document.getElementById("sel_date");
var dr_t = document.getElementById("dr_text");
var dr_f = document.getElementById("dr_fields");
var spec = document.getElementById("specify");

if (range != "") {
	for (i=0; i<range_sel.length; i++) {
		if (range_sel[i].value == range) {
			range_sel[i].selected = true;
		}
//			if (range_sel[i].value == 'spec' && range == 'spec') {
//				spec.style.display = "block";	
//			}
	}
}
}


function openSub(myID)
{
var fixID = myID;
var sublist = document.getElementsByTagName("TR");

for (s=0; s<sublist.length; s++) {
	var getTR = sublist[s];
	var trID = getTR.id;
	if (trID == myID && getTR.style.display == "none") {
		getTR.style.display = "inline";
	} else if (trID == myID && getTR.style.display != "none") {
		getTR.style.display = "none";
	}
}
}

function showSCD(sc, pr)
{
var url = "scdpr_details.php?pr="+pr+"&sc="+sc;
var width = 750;
var height = 550;

window.open(url,'scrDetails','location=0,menubar=0,resizable=1,toolbar=0,statusbar=1,width='+width+',height='+height);
}

function subform()
{
document.scdpr.submit();
}

function exportToExcel()
{
var oExcel = new ActiveXObject("Excel.Application");
var oBook = oExcel.Workbooks.Add;
var oSheet = oBook.Worksheets(1);
for (var y=0;y<detailsTable.rows.length;y++)
// detailsTable is the table where the content to be exported is
{
for (var x=0;x<detailsTable.rows(y).cells.length;x++)
{
oSheet.Cells(y+1,x+1) =
detailsTable.rows(y).cells(x).innerText;
}
}
oExcel.Visible = true;
oExcel.UserControl = true;
}

</script>
<style>

.tr.off {
background: none;
}

.report_bold {
font-family:Arial, Helvetica, sans-serif;
font-size:14px;
font-weight:bold;	
}

.tr.on {
background: #d5e9fe;
}

#smallborder {
background-image:url(../imgs/tbl_bot_border.gif);
background-repeat:repeat-x;
background-position:bottom;
}

#details_header2 {
background-image:url(../occsqa/imgs/dhbg.gif);
background-repeat:repeat-x;
border:1px solid #9d9d9d;
padding-left:10px;
padding-top:5px;
padding-bottom:7px;
font-family:Arial, Helvetica, sans-serif;
font-size:11px;
font-weight:bold;
color:#000000;
}
#details_content2 {
border-left:1px solid #9d9d9d;
border-right:1px solid #9d9d9d;
border-bottom:1px solid #9d9d9d;
padding:10px;
font-family:Arial, Helvetica, sans-serif;
font-size:11px;
color:#000000;
}

#subcontent {
padding-left:10px;
font-family:Arial, Helvetica, sans-serif;
font-size:11px;
color:#000000;
}

#details_header {
background-image:url(../occsqa/imgs/dhbgsub.gif);
background-repeat:repeat-x;
border-top:1px solid #9d9d9d;
border-left:1px solid #9d9d9d;
border-right:1px solid #9d9d9d;	
padding-left:10px;
height:50px;
font-family:Arial, Helvetica, sans-serif;
font-size:11px;
font-weight:bold;
color:#000000;
}

#details_content {
padding-left:10px;
font-family:Arial, Helvetica, sans-serif;
font-size:11px;
color:#000000;
}

.greybg {
background-image:url(../occsqa/imgs/dhbgsub.gif);
background-repeat:repeat-x;
}



.dhtext {
font-family:Arial, Helvetica, sans-serif;
font-size:11px;
color:#000000;
}
.dhtitle {
font-family:Arial, Helvetica, sans-serif;
font-size:11px;
color:#000000;
font-weight:bold;
border-bottom:1px dotted #f79e5d;
text-decoration:none;
}

#scrolldata {
overflow-y:auto;
overflow-x:hidden;
height:450px;
width:100%;
border-left:1px solid #9d9d9d;
border-bottom:1px solid #9d9d9d;
padding-bottom:10px;
}

</style>
</head>
<body>
<form name='scdpr' action='scdpr.php' method='get'>
<table width="100%" border="0" cellspacing="0" cellpadding="0" name='detailsTable'>
  <tr>
    <td width="18" align="left" valign="top"> </td>
    <td align="left" valign="top"> </td>
    <td width="18" align="left" valign="top"> </td>
  </tr>
  <tr>
    <td align="left" valign="top"> </td>
    <td height="35" align="left" valign="top"><span class="blueheader">Service Call Details by Problem Record</span></td>
    <td align="left" valign="top"> </td>
  </tr>
  
  <tr>
    <td align="left" valign="top"> </td>
    <td align="left" valign="top"> </td>
    <td align="left" valign="top"> </td>
  </tr>
  <tr>
    <td align="left" valign="top"> </td>
    <td align="left" valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="0">
      <tr>
        <td width="34%" align="left" valign="top"><table height='100%' width="348" border="0" cellspacing="0" cellpadding="0">
          <tr>
            <td width="85%" id='details_header2'>REPORT DETAILS<br/></td>
          </tr>
          <tr>
            <td id='details_content2' style='padding-left:10px;'><table width="100%" border="0" cellpadding="0" cellspacing="0" class="dhtext">
                <tr>
                  <td align="left" valign="top"><strong>Filter Range</strong></td>
                  <td align="left" valign="top"><? echo $abr . ": " .$disp_date; ?></td>
                </tr>
                <tr>
                  <td width="46%" align="left" valign="top"> <strong>Total Volume</strong></td>
                  <td width="54%" align="left" valign="top"><? echo $overall; ?></td>
                </tr>
                <tr>
                  <td align="left" valign="top"> </td>
                  <td align="left" valign="top"> </td>
                </tr>
                <tr>
                  <td align="left" valign="top"> </td>
                  <td align="left" valign="top"> </td>
                </tr>
            </table></td>
          </tr>
        </table></td>
        <td width="2%" align="left" valign="top"><img src='../imgs/transp.gif' width='20'/></td>
        <td width="64%" align="left" valign="top"><table width="388" border="0" cellpadding="0" cellspacing="0">
          <tr>
            <td width="51%" align="left" valign="top" class="qtitle">Range:<br />
                <select name='sel_date' class="qtext" onChange="dateField(this, this.selectedIndex);">
                  <option value="today" selected>Today</option>
                  <option value="yestr">Yesterday</option>
                  <option value="tw">This Week</option>
                  <option value="lw">Last Week</option>
                  <option value="tm">This Month</option>
                  <option value="lm">Last Month</option>
                  <option value="dr">Date Range</option>
                  <option value="spec">Specific Date</option>
              </select></td>
            <td width="49%" align="left" valign="bottom"><a href="javascript:subform()"><img src='../imgs/go_butt.gif' border='0' align='absbottom' /></a> </td>
          </tr>
          <tr>
            <td colspan="2" align="left" valign="top"><img src='../imgs/transp.gif' height='10'/></td>
            </tr>
          <tr id='specify' style='display: none;'>
            <td width='51%' align='left' valign='top' class='qtitle'> Specify Date:<br/>
                <script>DateInput('specdate', true, 'YYYY-MM-DD')</script>            </td>
          </tr>
          <tr id='dr_text' style='display: none;'>
            <td width='51%'align="left" valign="middle"><span class='qtitle'>From:</span></td>
            <td align="left" valign="middle"><span class='qtitle'>To:</span></td>
          </tr>
          <tr id='dr_fields' style='display: none;'>
            <td align="left" valign="middle"><script>DateInput('rangeone', true, 'YYYY-MM-DD')</script></td>
            <td align="left" valign="middle"><script>DateInput('rangetwo', true, 'YYYY-MM-DD')</script></td>
          </tr>
        </table></td>
      </tr>
    </table></td>
    <td align="left" valign="top"> </td>
  </tr>
  <tr>
    <td align="left" valign="top"> </td>
    <td align="left" valign="top"> </td>
    <td align="left" valign="top"> </td>
  </tr>
  <tr>
    <td align="left" valign="top"> </td>
    <td align="left" valign="top"><? echo $output; ?></td>
    <td align="left" valign="top"> </td>
  </tr>
</table>
<br/>
</form>
</body>
</html>

Link to comment
https://forums.phpfreaks.com/topic/131255-help-with-php-error/
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.