0

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.

0

Meanwhile, thanx for the direction. Lemme try it out. Will let you know of what the results will have been.

0

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>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td><strong>TOTALS</strong></td>
    <td>&nbsp;</td>
    <td>&nbsp;</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.

0

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.

0

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 ...

0

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>
0
<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>

Edited by rpv_sen: edited some data

0

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>";
0

Thanx guyz, lemme try it out. Will let you know if it worked. I really appreciate your time.

0

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.

0

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>&nbsp;</td>
    <td>&nbsp;</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>";

?>
0

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.

0

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.

Attachments results.JPG 74.2 KB
0

And like I said before, this was just after doing some trial and error. I am currently cleaning and sanitizing my code ...

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.