Hi,
I have form that displaying report per week. First thing to do is choose what week then automatically display the date/shift that week ranges.
here is my weekly_report.php
<?php
error_reporting(0);
session_start();
ob_start();
date_default_timezone_set("Asia/Singapore");
include('connection.php');
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<title>Weekly Report</title>
<head>
<link rel="stylesheet" type="text/css" href="op_report.css" />
<script type="text/javascript" src="jquery.js"></script>
<script type='text/javascript' src='jquery.autocomplete.js'></script>
<link rel="stylesheet" type="text/css" href="jquery.autocomplete.css" />
<script type="text/javascript">
//----auto complete week--//
$().ready(function() {
$("#week_selected").autocomplete("get_week_list.php", {
width: 115,
matchContains: true,
mustMatch: true,
selectFirst: false
});
$("#week_selected").result(function(event, data, formatted) {
$("#week_number").val(data[1]);
});
});
/*AJAX*/
function AJAX(){
var xmlHttp;
try{
xmlHttp=new XMLHttpRequest(); // Firefox, Opera 8.0+, Safari
return xmlHttp;
}
catch (e){
try{
xmlHttp=new ActiveXObject("Msxml2.XMLHTTP"); // Internet Explorer
return xmlHttp;
}
catch (e){
try{
xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
return xmlHttp;
}
catch (e){
alert("Your browser does not support AJAX!");
return false;
}
}
}
}
//-----get weekdata from week---//
function getweekdata()
{
// if (window.event.keyCode==13 || window.event.keyCode==10) {
divid = "week_data";
var url = "get_weekly_data.php";
var str = "id=" + document.getElementById("week_number").value;
var xmlHttp = AJAX();
xmlHttp.onreadystatechange = function(){
if(xmlHttp.readyState > 0 && xmlHttp.readyState < 4){
// document.getElementById(divid).innerHTML=loadingmessage;
}
if (xmlHttp.readyState == 4) {
if (xmlHttp.status == 200) {
var jsonart = xmlHttp.responseText;
document.getElementById(divid).innerHTML = jsonart;
}
}
}
xmlHttp.open("POST", url, true);
xmlHttp.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
xmlHttp.setRequestHeader("Content-length", str.length);
xmlHttp.setRequestHeader("Connection", "close");
xmlHttp.send(str);
// }
}
</script>
</head>
<body onload=document.getElementById("week_selected").focus();>
<form name="weekly_report" action="" method="post">
<div id="ddcolortabs">
<ul>
<li> <a href="index.php" title="Operator's Shift Report"><span>Operator's Shift Report</span></a></li>
<li id="current"> <a href="weekly_report.php" title="Reports"><span>Reports</span></a></li>
</ul>
</div>
<br/>
<div>
<table>
<tr>
<td style="border: none;">Type Week:</td>
<td><input type="text" name="week_selected" id="week_selected" value="" size="15" onkeyup="getweekdata();"></td>
</tr>
</table>
</div>
<input type="hidden" name="week_number" id="week_number">
<div id='week_data'>
</div>
</form>
</body>
</html>
//get_week_list.php
<?php
ob_start();
include "connection.php";
$q = strtolower($_GET["q"]);
if ($q == '') {
header("HTTP/1.0 404 Not Found", true, 404);
}
//else (!$q) return;
else{
$sql = "select week_id, week_number from week_list where week_number LIKE '$q%'";
$rsd = mysql_query($sql);
$cnt = mysql_num_rows($rsd);
if($cnt > 0)
{
while($rs = mysql_fetch_array($rsd)) {
$pid = $rs['week_id'];
$pname = $rs['week_number'];
echo "$pname|$pid\n";
}
}
else
{
header("HTTP/1.0 404 Not Found", true, 404);
}
}
?>
and here is my get_weekly_data.php // which display the data for that week.
<?php
ob_start();
include "connection.php";
if($_POST["id"])
{
$sql = "select r.report_date, s.shift_type FROM op_reports AS r, shift_list AS s WHERE WEEK(report_date) + 1 = '" . ($_POST["id"]) . "'
GROUP BY shift_type ORDER BY shift_id ASC";
$res = mysql_query($sql);
echo "<table>";
echo "<tr>";
echo "<th>Comp</th>";
while($row = mysql_fetch_assoc($res))
{
$report_date = $row['report_date'];
$report_shift = $row['shift_type'];
echo "<th>$report_date/$report_shift</th>";
}
echo "</tr>";
$sql_r = "select r.report_date, s.shift_type FROM op_reports AS r, shift_list AS s WHERE WEEK(report_date) + 1 = '" . ($_POST["id"]) . "'
GROUP BY shift_type ORDER BY shift_type DESC";
$res_r = mysql_query($sql_r);
echo "<tr>";
echo "<th></th>";
while($r = mysql_fetch_assoc($res_r)){
echo "<th>Output</th>";
}
echo "</tr>";
$sql_comp = "SELECT DISTINCT p.process_name , r.process_id, r.report_shift
FROM op_reports AS r
JOIN process_list AS p ON (p.process_id = r.process_id)
WHERE WEEK(report_date) + 1 = '" . ($_POST["id"]) . "' GROUP BY process_name ORDER BY p.process_name ASC";
$res_comp = mysql_query($sql_comp);
echo "<tr>";
while($row_comp = mysql_fetch_assoc($res_comp))
{
$process = $row_comp['process_name'];
$process_id = $row_comp['process_id'];
echo "<td>$process</td>";
$comp = "SELECT DISTINCT o.compound_type, o.process_id, o.shift_date
FROM op_output AS o WHERE process_id = '$process_id' GROUP BY o.shift_id, compound_type ORDER BY compound_type ASC";
$c = mysql_query($comp);
echo "<tr>";
while($co = mysql_fetch_assoc($c))
{
$compound_type = $co['compound_type'];
$process_i = $co['process_id'];
$shift_date = $co['shift_date'];
echo "<td>$compound_type</td>";
$sql_output = "SELECT DISTINCT o.compound_type, SUM(o.compound_output) AS compound_output, o.process_id, o.shift_id, o.shift_date
FROM op_output AS o WHERE process_id = '$process_id' AND o.compound_type = '$compound_type' GROUP BY o.shift_id, o.shift_date, compound_type ORDER BY o.shift_id ASC";
$res_output = mysql_query($sql_output);
while($row_output = mysql_fetch_assoc($res_output))
{
$compound_output = $row_output['compound_output'];
$shift = $row_output['shift_id'];
$compound = $row_output['compound_type'];
$date = $row_output['shift_date'];
if($shift == 1 && $date == $shift_date)
{
echo"<td>$compound_output</td>";
}
else
{
echo "<td></td>";
}
if($shift == 2 && $date == $shift_date)
{
echo"<td>$compound_output</td>";
}
else
{
echo "<td></td>";
}
if($shift == 3 && $date == $shift_date)
{
echo"<td>$compound_output</td>";
}
else
{
echo "<td></td>";
}
if($shift == 4 && $date == $shift_date)
{
echo"<td>$compound_output</td>";
}
else
{
echo "<td></td>";
}
if($shift == 5 && $date == $shift_date)
{
echo"<td>$compound_output</td>";
}
else
{
echo "<td></td>";
}
}
echo "</tr>";
}
}
echo "</tr>";
echo "</table>";
}
?>
I attached my sample screenshots that display by this code and also a format that I need to display and the database.
My problem now is on displaying of output per date/shift.
It's been todays that I tried to fixed this. I hope somebody can help me.
Thank you so much
sample updated.zip
