n3mesis125 Posted November 3, 2008 Share Posted November 3, 2008 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 More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.