Aplogies for the cluttered database file. Now the amount and quantity are being gotten from Sales_Ledger_Product_Sales and Sales_Ledger_Product_Sales has a foreign key which is sales_ledger_id.
Webville312
14
Newbie Poster
Webville312
14
Newbie Poster
Meanwhile, thanx for the direction. Lemme try it out. Will let you know of what the results will have been.
Webville312
14
Newbie Poster
Thanx Diafol, the code worked. Now when I transfer it into my code to try and make the rowspan, it is showing me a distorted table. This is the code that I am currently using;
<?php
/*******************************************************************************
** FILE: display_vat_detail_report.php
**
** PURPOSE: Display the VAT detail report for the beer products
**
**
** WRITTEN BY: CLOVIS WANZIGUYA DATE: March.2014
**
** MODIFIED BY: Clovis Wanziguya DATE: April.2014
** Wrote the script for fetching the records from the database
** Created the table for displaying the tables thanx to Diafol from Daniweb
**
**
*********************************************************************************/
include('includes/connect.php');
$start_date = "2012-03-24 00:00:00";
$end_date = "2014-12-12 00:00:00";
// Connect to the database
//$mysqli = connect2DB3();
$query = "
SELECT SL.sales_ledger_id,
SL.sales_ledger_date,
SL.sales_person,
SP.product_description,
SLPS.quantity,
SLPS.amount,
SLPS.product_number,
SLPS.amount * STI.vat_rate /100 AS tax
FROM Sales_Ledger AS SL
INNER JOIN Sales_Ledger_Product_Sales AS SLPS ON SLPS.sales_ledger_id = SL.sales_ledger_id
INNER JOIN Spirits_Products AS SP ON SP.product_number = SLPS.product_number
INNER JOIN Spirits_Tax_Invoice_From_UBL_Entry AS STI ON STI.product_number = SP.product_number
WHERE SL.sales_ledger_date >= '2012-04-01 00:00:00'
AND SL.sales_ledger_date <= '2014-04-05 00:00:00'
ORDER BY SL.sales_ledger_date DESC , SL.sales_person";
$result = mysql_query($query);
// Find out if any results have been returned by counting the number of rows
$num_rows = mysql_num_rows($result);
if($num_rows ==0)
{
echo "<div id='record_not_found'>Record not found.</div>";
}
else
{
// Set the header for the report
echo "
<table width=\"922\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\" width=\"750\"><tr>
<td colspan=\"6\"><strong>REPORT TYPE:</strong> Detailed Sales Report <br>
<strong>DATE RANGE: </strong> Between ".date("l, F d, Y",strtotime($_POST['start_date']))." AND ".date("l, F d, Y",strtotime($_POST['end_date']))."</td>
</tr>
</table>
<table border=\"1\" cellpadding=\"0\" cellspacing=\"0\" width=\"750\">
<thead><tr>
<th><strong>SALES DATE</strong></th>
<th><strong>SALES PERSON</strong></th>
<th><strong>PRODUCT</strong></th>
<th><strong>QUANTITY</strong></th>
<th><strong>AMOUNT</strong></th>
<th><strong>VAT</strong></th>
</tr></thead>";
// Do while loop for out put records.
while($report_data = mysql_fetch_array($result))
{
$sales_date = $report_data['sales_ledger_date'];
$sales_person = $report_data['sales_person'];
$number_of_sales_persons = $report_data['number_of_sales_persons'];
$product_name = $report_data['product_description'];
$product_quantity = $report_data['quantity'];
$amount = $report_data['amount'];
$product_no = $report_data['product_number'];
$total_amount +=$amount;
$total_quantity +=$product_quantity;
$tax = $report_data['tax'];
// count how many times a particular sales person appears
$count1_sql = "
SELECT COUNT(sales_person) AS count_sales_persons
FROM Sales_Ledger
WHERE sales_person='$sales_person' ";
$count1_result = mysql_query($count1_sql);
$count1_num_rows = mysql_num_rows($count1_result);
$count_data = mysql_fetch_array($count1_result);
$count_sales_persons = $count_data['count_sales_persons'];
echo $count_sales_persons;
if($count_sales_persons >0)
{
echo "<tbody><tr>";
echo "<td rowspan=$count_sales_persons>$sales_date</td>";
echo "<td rowspan=$count_sales_persons>$sales_person</td>";
// loop through the products in here
$sql2 = $query;
$result2 = mysql_query($sql2);
while($row2 = mysql_fetch_array($result2))
{
echo "<td>$row2[product_description]</td>
<td>$row2[quantity]</td>
<td>$row2[amount]</td>
<td>$tax</td></tr>";
}
// end loop of products
}
}
}
echo "<tr>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td><strong>TOTALS</strong></td>
<td> </td>
<td> </td>
<td>".round($total_quantity,CURRENCY_DECIMAL_PLACES)."</td>
<td>".round($total_amount,CURRENCY_DECIMAL_PLACES)."</td>
<td>";
// Display the Sum of the total VAT amount
echo round($total_vat,CURRENCY_DECIMAL_PLACES);
echo "</td>
</tr>";
?>
How do I create the rospans for each sales person.
diafol
Have a look at this:
SELECT SL.sales_ledger_id,
SL.sales_ledger_date,
SL.sales_person,
SP.product_description,
SUM(SLPS.quantity) AS qty,
SUM(SLPS.amount) AS amt,
SLPS.product_number,
SUM(SLPS.amount * STI.vat_rate /100) AS tax
FROM Sales_Ledger AS SL
INNER JOIN Sales_Ledger_Product_Sales AS SLPS ON SLPS.sales_ledger_id = SL.sales_ledger_id
INNER JOIN Spirits_Products AS SP ON SP.product_number = SLPS.product_number
INNER JOIN Spirits_Tax_Invoice_From_UBL_Entry AS STI ON STI.product_number = SP.product_number
WHERE SL.sales_ledger_date >= '2012-04-01 00:00:00'
AND SL.sales_ledger_date <= '2014-04-05 00:00:00'
GROUP BY SL.sales_ledger_date, SL.sales_person, SP.product_description WITH ROLLUP
It uses rollup. I cheated by grouping certain fields. Anyway, it gives 3 extra rows for: totals for Julius for that date, total for everybody for that date and total altogether for everybody for every date.
You have to check the values - rollup can be temperamental. If you have more data, you'd see more of these total rows interspersed through the dataset.
Webville312
14
Newbie Poster
Thanx Diafol. The query sorted out the issue of selecting from the database.
However, the challenge is that I am unable to display the records with a rowspan. Yes the loops are creating rowspans, but the other data is distorted.
I dunno how to do this, and I have been stuck on this step for a week now ...
rpv_sen
59
Junior Poster
I think the below code will help you to get the expected result
<table width='700' border='0'>
<tr style='font-size: 12px; font-weight: bold; color: rgb(0, 153, 204); background: none repeat scroll 0% 0% rgb(230, 249, 217);'>
<td width='150'>Login date</td>
<td width='150'>User</td>
<td width='100'>Login result</td>
<td width='200'>Notes</td>
</tr>
<?php
$num=1;
$sql1 = mysql_query("select count(user_id),user_id,login_date,username FROM login_logs group by school_id");
while($row = mysql_fetch_array($sql1))
{
$count = $row['count(user_id)'];
$skizzy = $row['user_id'];
?>
<tr bgcolor="<?php echo $bg; ?>">
<td rowspan="<?php echo $count; ?>"><?php echo $row['login_date']." [GMT+3]"; ?></td>
<td rowspan="<?php echo $count; ?>"><?php echo $row['username']; ?></td>
<?php $sql="SELECT * FROM login_logs where school_id='$skizzy'";
$result=mysql_query($sql);
while($rows=mysql_fetch_array($result))
{ ?>
<td><?php echo $rows['log_result']; ?></td>
<td><?php echo $rows['login_notes']; ?></td>
</tr>
<?php } } ?>
</table>
rpv_sen
59
Junior Poster
<table width='700' border='0'>
<tr style='font-size: 12px; font-weight: bold; color: rgb(0, 153, 204); background: none repeat scroll 0% 0% rgb(230, 249, 217);'>
<td width='150'>Login date</td>
<td width='150'>User</td>
<td width='100'>Login result</td>
<td width='200'>Notes</td>
</tr>
<?php
$num=1;
$sql1 = mysql_query("select count(user_id),user_id,login_date,username FROM login_logs group by user_id");
while($row = mysql_fetch_array($sql1))
{
$count = $row['count(user_id)'];
$skizzy = $row['user_id'];
?>
<tr bgcolor="<?php echo $bg; ?>">
<td rowspan="<?php echo $count; ?>"><?php echo $row['login_date']." [GMT+3]"; ?></td>
<td rowspan="<?php echo $count; ?>"><?php echo $row['username']; ?></td>
<?php $sql="SELECT * FROM login_logs where user_id='$skizzy'";
$result=mysql_query($sql);
while($rows=mysql_fetch_array($result))
{ ?>
<td><?php echo $rows['log_result']; ?></td>
<td><?php echo $rows['login_notes']; ?></td>
</tr>
<?php } } ?>
</table>
diafol
OK, after a bit of trial and error:
$table = array();
while($row = ...)
{
$table[$row['date']][$row['seller']][] = array_slice($row,2);
}
$output = '';
foreach ($table as $k=>$tbl)
{
$output .= "<tr><td";
$rowspanDate = 0;
$inside ='';
foreach($tbl as $k2=>$tb)
{
$rowspanSeller = count($tb);
$inside .= '<td';
$inside .= ($rowspanSeller > 1) ? " rowspan='$rowspanSeller'>" : '>';
$inside .= $k2 . '</td>';
$further = '';
foreach($tb as $data)
{
$further .= "<td>{$data['product']}</td><td>{$data['qty']}</td><td>{$data['amount']}</td><td>{$data['tax']}</td></tr>";
}
$inside .= $further;
$rowspanDate += count($tb);
}
$output .= ($rowspanDate > 1) ? " rowspan='$rowspanDate'>" : '>';
$output .= $k . '</td>' . $inside;
}
echo "<table>" . $output . "</table>";
Webville312
14
Newbie Poster
Thanx guyz, lemme try it out. Will let you know if it worked. I really appreciate your time.
Webville312
14
Newbie Poster
After a very long hussle, I finally put it together, by creating a second table within a table data. Thanx guyz for your help. Am gonna post the code, in case someone else ever faces the same problem.
Webville312
14
Newbie Poster
Now the code is still raw, coz am still cleaning it up, but it is a starting point for whoever faces such a challenge. Anyways, here's the code;
<?php
include('includes/connect.php');
$start_date = "2012-03-24 00:00:00";
$end_date = "2014-12-12 00:00:00";
// Connect to the database
//$mysqli = connect2DB3();
$query = "
SELECT DISTINCT
Spirits_Products.product_description,
Sales_ledger.sales_person,
Sales_ledger.sales_ledger_date,
Sales_Ledger_Product_Sales.product_number,
Sales_Ledger_Product_Sales.sales_ledger_id,
Sales_Ledger_Product_Sales.quantity,
Sales_ledger.sales_person,
Sales_Ledger_Product_Sales.amount
FROM Sales_Ledger_Product_Sales, Spirits_Products,Sales_ledger
WHERE Spirits_Products.product_number = Sales_Ledger_Product_Sales.product_number
AND Sales_ledger.sales_ledger_id=Sales_Ledger_Product_Sales.sales_ledger_id
AND Sales_ledger.sales_ledger_date >='$start_date'
AND Sales_ledger.sales_ledger_date <='$end_date'
GROUP BY sales_ledger_date";
$result = mysql_query($query);
// Find out if any results have been returned by counting the number of rows
$num_rows = mysql_num_rows($result);
if($num_rows ==0)
{
echo "<div id='record_not_found'>Record not found.</div>";
}
else
{
// Set the header for the report
echo "
<table width=\"922\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\" width=\"750\"><tr>
<td colspan=\"6\"><strong>REPORT TYPE:</strong> Detailed Sales Report <br>
<strong>DATE RANGE: </strong> Between ".date("l, F d, Y",strtotime($_POST['start_date']))." AND ".date("l, F d, Y",strtotime($_POST['end_date']))."</td>
</tr>
</table>
<table border=\"1\" cellpadding=\"0\" cellspacing=\"0\" width=\"750\">
<thead><tr>
<th><strong>SALES DATE</strong></th>
<th><strong>SALES PERSON</strong></th>
<th><strong>PRODUCT DESCRIPTION</strong></th>
</tr></thead>";
// Do while loop for out put records.
while($report_data = mysql_fetch_array($result))
{
$sales_date = $report_data['sales_ledger_date'];
$sales_person = $report_data['sales_person'];
$number_of_sales_persons = $report_data['number_of_sales_persons'];
$product_name = $report_data['product_description'];
$product_quantity = $report_data['quantity'];
$amount = $report_data['amount'];
$product_no = $report_data['product_number'];
$number_of_sales_persons = $report_data['number_of_sales_persons'];
$sales_ledger_id = $report_data['sales_ledger_id'];
$total_amount +=$amount;
$total_quantity +=$product_quantity;
echo "<tr>";
echo "<td>$sales_date</td>";
echo "<td>$sales_person</td>";
echo "<td>";
// Fetch the product information
$get_data = "
SELECT
Spirits_Products.product_description,
Sales_Ledger_Product_Sales.quantity,
Sales_Ledger_Product_Sales.amount,
Spirits_Products.product_number
FROM Sales_Ledger_Product_Sales, Spirits_Products,Sales_Ledger
WHERE Spirits_Products.product_number = '$product_no'
AND Sales_Ledger_Product_Sales.sales_ledger_id = '$sales_ledger_id'
AND Sales_ledger.sales_ledger_date ='$sales_date'
AND Sales_Ledger.sales_person = '$sales_person'
";
$product_results = mysql_query($get_data) or die(mysql_error());
while($rows = mysql_fetch_array($product_results))
{
echo "
<table>
<tr>
<td>$rows[product_description]</td>
<td>$rows[quantity]</td>
<td>$rows[amount]</td>
<td>";
// Get the tax for each product
$pdct_no = $rows['product_number'];
$get_vat_per_product = "
SELECT DISTINCT vat_rate
FROM Spirits_Tax_Invoice_From_UBL_Entry
WHERE product_number='$pdct_no'";
$vat_result = mysql_query($get_vat_per_product);
$vat_report_data = mysql_fetch_array($vat_result);
$vat_rate = $vat_report_data['vat_rate'];
$vat_per_product = ($vat_rate/100)*$rows['amount'];
echo round($vat_per_product,CURRENCY_DECIMAL_PLACES);
// Get the sum of the VAT for each product
$total_vat +=$vat_per_product;
$sub_total_quantity += $rows['quantity'];
$sub_total_amount += $rows['amount'];
echo "</td>
";
}
echo "</td>
</tr>
<tr>
<td><strong>SUB TOTALS:</strong></td>
<td>$sub_total_quantity</td>
<td>$sub_total_amount</td>
<td>".round($total_vat,CURRENCY_DECIMAL_PLACES)."</td>
</tr>
</table>";
}
//}
}
echo "
<tr>
<td><strong>TOTALS</strong></td>
<td> </td>
<td> </td>
<td>".round($total_quantity,CURRENCY_DECIMAL_PLACES)."</td>
<td>".round($total_amount,CURRENCY_DECIMAL_PLACES)."</td>
<td>";
// Display the Sum of the total VAT amount
echo round($total_vat,CURRENCY_DECIMAL_PLACES);
echo "</td>
</tr>";
?>
diafol
Glad it worked for you. Out of interest my code didn't work? Just asking as you are running many sql queries and creating nested tables whereas my solution ran one and created one. Curious.
Webville312
14
Newbie Poster
Um, I tried it out, but when it came to creating of rowspans, I had a distorted table, just as the one attached.
Now the query worked well, only issue was that the rows were being repeated according to the number of times tha sales person appears.
So, I thought about ... why not create a table withing the table data that contains the results??
And so far it has worked.
Thanks for your time, responses and effort. I really appreciate.
Webville312
14
Newbie Poster
And like I said before, this was just after doing some trial and error. I am currently cleaning and sanitizing my code ...
diafol
OK just to let you know I created a class for generic rowspanning, in case you need to do this sort of thing again:
I hope you don't mind, but I used your data as an example.
Webville312
14
Newbie Poster
I don't mind at all .... Thanks once again.
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.