Hi,
I created report form for reject type.
Now I need help in displaying the rejects amount per reject type.
here is my code:
<?php
ob_start();
include "connection.php";
$id = "30";
if($id == '30')
{
//----code for date only----//
$sql = "SELECT DISTINCT r.reject_date, r.process_id
FROM op_reject AS r
JOIN process_list AS p ON (p.process_id = r.process_id)
WHERE WEEK(reject_date) + 1 = '$id'
GROUP BY r.reject_date ORDER BY r.reject_date ASC";
$res = mysql_query($sql);
echo "<table>";
echo "<tr>";
echo "<thead>";
echo "<th>Compound</th>";
while($row = mysql_fetch_assoc($res))
{
$report_date[] = $row['reject_date'];
$process_[] = $row['process_id'];
}
$i = 1;
foreach($report_date AS $report_date)
{
echo "<th id=col".$i." colspan=\"3\">$report_date</th>";
$i++;
}
echo "</thead>";
echo "</tr>";
echo '
<script type="text/javascript">
document.getElementById("col2").colSpan='.$i.';
</script>';
$sql_comp = "SELECT DISTINCT p.process_name , r.process_id, r.reject_date
FROM op_reject AS r
JOIN process_list AS p ON (p.process_id = r.process_id)
WHERE WEEK(reject_date) + 1 = '$id' GROUP BY process_name ORDER BY p.process_id 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'];
$reject_date = $row_comp['reject_date'];
echo "<td>$process</td>";
$sql_date = "SELECT DISTINCT r.reject_date
FROM op_reject AS r
JOIN process_list AS p ON (p.process_id = r.process_id)
WHERE WEEK(reject_date) = '$id'
GROUP BY r.reject_date ORDER BY r.reject_date ASC";
$res_date = mysql_query($sql);
while($row_date = mysql_fetch_assoc($res_date))
{
$report_date_ = $row_date['reject_date'];
$sql_rej = "SELECT DISTINCT reject_type FROM op_reject WHERE reject_date = '$report_date_' and process_id = '$process_id' ORDER BY reject_type";
$res_rej = mysql_query($sql_rej);
$cnt_rej = mysql_num_rows($res_rej);
if($cnt_rej > 0)
{
while($row_rej=mysql_fetch_assoc($res_rej))
{
$reject_type = $row_rej['reject_type'];
echo "<td>$reject_type</td>";
}
}
}
$comp = "
(SElECT DISTINCt r.compound_type FROM op_reject AS r WHERE r.process_id = '$process_id' AND WEEK(r.reject_date) + 1 = '$id')
ORDER BY compound_type ASC";
$c = mysql_query($comp);
$cnt = mysql_num_rows($c);
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_date = "SELECT DISTINCT r.reject_date
FROM op_reject AS r
JOIN process_list AS p ON (p.process_id = r.process_id)
WHERE WEEK(reject_date) = '$id'
GROUP BY r.reject_date ORDER BY r.reject_date ASC";
$res_date = mysql_query($sql);
while($row_date = mysql_fetch_assoc($res_date))
{
$report_date_ = $row_date['reject_date'];
//-----
//-----
$sql_reject = "SELECT compound_type, SUM(reject) AS reject, reject_type FROM op_reject
WHERE compound_type = '$compound_type' and process_id = '$process_id' AND reject_date = '$report_date_' GROUP BY compound_type, process_id, reject_TYPE";
$res_reject = mysql_query($sql_reject);
$cnt_rej = mysql_num_rows($res_reject);
if($cnt_rej > 0)
{
while($row_reject = mysql_fetch_assoc($res_reject))
{
$reject = $row_reject['reject'];
echo "<td>$reject</td>";
}
}
else
{
echo "<td></td>";
}
}
echo "</tr>";
}
}
echo "</table>";
}
?>
for better understanding I attached my updated database, the screenshot display for this code and also the correct output i need to display.
thank you.