Create a table for databse results with rowspan

[Webville312]

Dear all,

I have been thinking of this task, but I have no idea where to start from.
I am retrieving login logs from the database, which is cool.
Now, the challenge is that in case a user has logged in more than once in the same day, I want his records to be in the same row, probably create a rowspan for the other login information. I am thinking that the login log should look like this;

    ----------------------------------------------------------------------------
    |DATE               | USER               | LOGIN RESULT   | COMMENTS        |
    ----------------------------------------------------------------------------
    |2014:03:28         | Julius             | Successful     |Correct details  |
    ----------------------------------------------------------------------------
    |                                        | Failed         |Wrong Password   |
    ----------------------------------------------------------------------------
    |                                        |Successful      |Correct details  |
    ----------------------------------------------------------------------------

The code I know of only gets them in a new row. This is the code am using;

<?php
    include('includes/connect.php');
            // select record from mysql 
            $sql="SELECT * FROM login_logs";
            $result=mysql_query($sql);
            if(mysql_num_rows($result) == 0){ echo "<font color='red' size='3'>No Registered records yet</font>"; 
            }
                else
                {
          echo "<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;
                        while($rows=mysql_fetch_array($result)){
                        $num++;
                        $skizzy = $rows['school_id']; 
                        if(($num%2)!=0){
                        $bg="#FFFF99";
                        }else{
                        $bg="#FFFFFF";
                        }
                        ?>

                <tr bgcolor="<?php echo $bg; ?>">
                   <td><?php  echo $rows['login_date']." [GMT+3]"; ?></td>
                   <td><?php  echo $rows['username']; ?></td>
                   <td><?php  echo $rows['login_result']; ?></td>
                   <td><?php  echo $rows['login_notes']; ?></td>
                  </tr>
                  <?php
                        }
                }
                        ?>
              </table>

If anybody can point me to the right direction ....

Thank you.

[minitauros]

What abouuuut you select the logins only for the user that is logged in? :) For example SELECT * FROM logins WHERE user = "julius".

You will then only fetch the login records for that user, which you can display one by one.

Alternatively, you can use an ORDER BY clause to sort your results per user, or per date. E.g. SELECT * FROM logins ORDER BY user ASC, date DESC to sort by the username (ascending) first, and then by the login date (descending) second.

[Webville312]

I have thought about that, but then, the admin wants to have all the user login logs on one page, however, he says he does not want repetition of names and dates.

[minitauros]

Well, it appears to me that he then wants the most recent logins, not necessarily grouped by username. You could solve this problem by simply retrieving the last x number of rows and displaying those one by one. You could use the DISTINCT() selector to select only unique usernames. E.g. SELECT DISTINCT(username), * FROM logins ORDER BY date DESC.

Would that be a solution to your problem?

[Webville312]

Not really, he wants all of the login logs, however, the method you recommended would not work for a single user who logged in at different times. And yet this is the on ehe wants the most.He wants to track all user logins and what time they logged in.

[minitauros]

Hmm well, taking that I assume that you will be working with a huge bunch of rows, and that you will probably need to build pagination possibilities.

What you could do is the following:

SELECT *
FROM login
ORDER BY user ASC,
date DESC
LIMIT 50 -- Example limit

This will simply select the first 50 logins from the logins table, starting with the usernames starting with an A, moving up to the Z. Per username the results will be ordered by date.

Then you could use PHP to traverse to the results and display everything you need. For example:

<?php
if($results) {
    foreach($results as $result) {
        $date = $result['date'];
        $username = $result['username'];
        $status = $result['status'];
        $display_username = false;

        if(!$last_used_username || $username != $last_used_username) {
            //* Either we're working with a new username, or this is the first row.
            $display_username = true;
        }

        $last_used_username = $username;
        ?>
        <tr>
            <td>
                <?php if($display_username) echo $username; ?>
            </td>
            <td>
                <?php echo $date; ?>
            </td>
            <td>
                <?php echo $status; ?>
            </td>
        </tr>
        <?php
    }
}

Would this be of any help?

[diafol]

WHy not use GROUP_CONCAT?

[Webville312]

@minitauros, lemme try that out.

[Webville312]

@diafol, GROUP_CONCAT is putting everything in one cell and separating them with a comma.

[Webville312]

Thanx guyz for all your time and help. Although I did not use GROUP_CONCAT OR minitauros's approach, I played around with the script and was able to get it to work. In case anyone else faces the same problem, here is the code that I used;

<?php  
error_reporting(0);
?>
<table width="75%" border="1" cellspacing="0" cellpadding="1">
  <tr>
    <td><b>Login date</b></td>
    <td><b>Username</b></td>
    <td><b>Login result</b></td>
    <td><b>Login Notes</b></td>
  </tr>
<?php
include('includes/connect.php');
$sql1 = "SELECT * FROM login_logs";
$result1 = mysql_query($sql1);
$num_rows1 = mysql_num_rows($result1);

    if($num_rows1 > 0){

        while($row1 = mysql_fetch_array($result1)){

            $loginId = $row1['Id'];
            $username = $row1['username']; 
            $login_date = $row1['login_date'];

            // count how many usernames match with the one returned
            $count1_sql = "SELECT * FROM login_logs WHERE username = '$username'";
            $count1_result = mysql_query($count1_sql) or die(mysql_error());
            $count1_num_rows = mysql_num_rows($count1_result);
            echo $count1_num_rows;

            echo "<tr>";
            echo "<td rowspan=$count1_num_rows>$login_date</td>";
            echo "<td rowspan=$count1_num_rows>$username</td>";

            // loop through the username here
            $sql2 = "$count1_sql";
            $result2 = mysql_query($sql2);

                while($row2 = mysql_fetch_array($result2)){

                    echo "<td>$row2[login_result]</td>";
                    echo "<td>$row2[login_notes]</td>";
                    echo "</tr>";            
                }
            // end loop of username
            echo "</tr>";            

        } // end while($row1 = mysql_fetch_array($result1))

    } //  end if($num_rows > 0)
?>
</table>

[minitauros]

Glad that it's working :). But why are you executing the exact same query twice? Seems like you can re-use the result stored in $count1_result instead of having to create $result2.

[Webville312]

@minitauros, Like I mentioned, I kept changing things here and there, but now am cleaning my code and putting everything right.

Once again, thanx for your time.

[minitauros]

Okay, good luck then!

[Webville312]

Thanx a bunch.

[diafol]

This can be done with one SQL query - you could be running many - with loops inside loops - quite wasteful.

Here's an idea for a one-query solution

<?php

function getLogTable($db,$orderBy=NULL)
{
    //Provide option of changing ORDER
    $order = ($orderBy) ? $orderBy : 'login_date DESC, username';
    $result = $db->query("SELECT login_date, username, login_result, login_notes FROM login_logs ORDER BY $order");
    if($result->num_rows)
    {
        $data = $result->fetch_all(MYSQLI_ASSOC);

        //Initialise Variables
        $date = '';
        $username = '';
        $table = "<table><thead><tr><th>Date</th><th>Username</th><th>Result</th><th>Notes</th></thead><tbody>";

        //Loop over log dataset
        foreach($data as $datum)
        {
            $table .= "<tr>";

            $tableDate = ($date != $datum['login_date']) ? $datum['login_date'] : '&nbsp;'; 
            $tableUser = ($username != $datum['username'] || $date != $datum['login_date']) ? $datum['username'] : '&nbsp;';

            $table .= ($date == $datum['login_date'] && $username == $datum['username']) ? "<td colspan='2'>&nbsp;</td>" : "<td>$tableDate</td><td>$tableUser</td>";      
            $table .= "<td>{$datum['login_result']}</td><td>{$datum['login_notes']}</td></tr>";
            $date = $datum['login_date'];
            $username = $datum['username'];
        }

        $table .= "</tbody></table>";
        return $table;
    }
}

$db = new mysqli("localhost","root","","daniweb");
?>

<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>Logs</title>
<style>
    table{
        border-collapse:collapse;
        border: black solid 1px;    
    }
    table tr th, table tr td{
        border: black solid 1px;
    }
</style>
</head>
<body>
<?php
    if($table = getLogTable($db)) echo $table;
?>
</body>
</html>

[Webville312]

@Diafol, talk of many ways to kill a rat ... that is a good idea. lemme try that too.

[Webville312]

Thanx.

[Webville312]

Hey Guys, so; I decided to add in more tables [Three to be exact]. I have three database tables; The first one is Sales_ledger_Product_Sales which has the following columns;
1. product_number
2. sales_ledger_id
3. quantity
4. amount
5. sales_ledger_id

The second one, which is Sales_Ledger has the following columns;
1. sales_ledger_id
2. sales_person

The third table is Spirits_Products which has the following columns;
1. product_number
2. product_description

The challenge I have is that of trying to create a rowspan for each sales_person on a particular date. The code I am using is not producing all the information I need. Here is the code am working with;

<?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 * 
        FROM Sales_Ledger,Sales_Ledger_Product_Sales,Spirits_Products 
        WHERE Sales_Ledger.sales_ledger_date >= '$start_date'
        AND Sales_Ledger.sales_ledger_date <= '$end_date'
        AND Sales_Ledger_Product_Sales.sales_ledger_id=Sales_Ledger.sales_ledger_id
        AND Sales_Ledger_Product_Sales.product_number=Spirits_Products.product_number 
        ORDER BY sales_ledger_date DESC";

        $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'];
            $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;

            // count how many times a particular sales person appears
            $count1_sql = "
            SELECT * 
            FROM Sales_Ledger 
            WHERE sales_person = '$sales_person' 
            ORDER BY sales_person";

            $count1_result = mysql_query($count1_sql);
            $count1_num_rows = mysql_num_rows($count1_result);

            if($count1_num_rows >1)
            {
                echo "<tbody><tr>";
            echo "<td rowspan=$count1_num_rows>$sales_date</td>";
            echo "<td rowspan=$count1_num_rows>$sales_person</td>";

            // loop through the products in here
            $sql2 = "
            SELECT *  
            FROM Spirits_products 
            WHERE product_number='$product_no'";

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

            // Calculate the vat rate for each product sold
            $get_vat_per_product = "
            SELECT DISTINCT vat_rate 
            FROM Spirits_Tax_Invoice_From_UBL_Entry 
            WHERE product_number='$product_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)*$amount;

            echo round($vat_per_product,CURRENCY_DECIMAL_PLACES);

            // Get the sum of the VAT for each product
            $total_vat +=$vat_per_product;

                    echo "</td></tr>";            
                }
            // end loop of products  
            }

            else
            {
                echo "More than one";
            }
        }
        }
    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>";

?>

Thanx in advance.

[diafol]

That's really difficult to follow - so much html generated from php and we're not shown what the expected output should look like. If you have some examples of expected output (e.g. mockup screenshots or text tables - as you provided in previous question),that would be useful.

There seems to be a lot of querying and looping going on - I'm sure there must be a more succinct way of doing this, but as I mentioned, we probably need a visual to go on.

[Webville312]

Ok, really sorry that I did not provide the visual output. The expected output is as follows;

-----------------------------------------------------------------------
|Sales date     | Sales person |Product      |Quantity | Cost      | VAT|
 -----------------------------------------------------------------------
|                |              | Bell Lager | 100     | 200000    | 15    
|                |              |---------------------------------------
|                |              | Senator    | 10      | 10000     | 12    
|                |              |---------------------------------------
| 2014:04:02     | Julius       | Tusker     | 20      | 10000     | 18    
|                |              |---------------------------------------
|                |              | Ug Waragi  | 10      | 2000      |           |
 -----------------------------------------------------------------------

[diafol]

OK, I'll have a look, but I'm wondering whether most of this could be done with just MySQL using WITH ROLLUP.

[Webville312]

Ok, I'll be waiting and trying out a few things too.
Thanx.

[diafol]

Spirits_Tax_Invoice_From_UBL_Entry

This isn't mentioned in your '3' tables. Also amount seems to be entered manaully by the sales person? Not calculated per unit price of the product. Is that right?

I'm pretty sure that the whole lot could be done with just one SQL query and the totals from WITH ROLLUP.

An sql dump that we could use to replicate your db would be useful.

[Webville312]

Well, The Spirits_Tax_Invoice_From_UBL is the table that contains the Value Added Tax for each product, and of course in order to get that tax, I need to get it by querying the relevant table.

At the moment, I am not sure of how to use the ROLLUPS, but It would be great to learn.

Thanx once again.

[Webville312]

Wwell, here is the database definition [Attached]

[Webville312]

Wwell, here is the database definition [Attached]

[Webville312]

I hope you guys can see the database, coz on my side; I aint seeing it yet ...

[Webville312]

I am unabe to upload the database, so I have pasted it as below;

--
-- Database: `db176098_bi_test`
--

-- --------------------------------------------------------

--
-- Table structure for table `Analysis_Book_Loads`
--

CREATE TABLE IF NOT EXISTS `Analysis_Book_Loads` (
  `analysis_book_entry_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `product_number` int(11) NOT NULL,
  `sales_person_0` varchar(60) NOT NULL,
  `sales_person_1` varchar(60) NOT NULL,
  `sales_person_2` varchar(60) NOT NULL,
  `sales_person_3` varchar(60) NOT NULL,
  `sales_person_4` varchar(60) NOT NULL,
  `sales_person_5` varchar(60) NOT NULL,
  `sales_person_6` varchar(60) NOT NULL,
  `sales_person_7` varchar(60) NOT NULL,
  `sales_person_8` varchar(60) NOT NULL,
  `sales_person_9` varchar(60) NOT NULL,
  `sales_person_10` varchar(60) NOT NULL,
  PRIMARY KEY (`analysis_book_entry_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store analysis book entries' AUTO_INCREMENT=35 ;

--
-- Dumping data for table `Analysis_Book_Loads`
--

INSERT INTO `Analysis_Book_Loads` (`analysis_book_entry_id`, `date`, `product_number`, `sales_person_0`, `sales_person_1`, `sales_person_2`, `sales_person_3`, `sales_person_4`, `sales_person_5`, `sales_person_6`, `sales_person_7`, `sales_person_8`, `sales_person_9`, `sales_person_10`) VALUES
(15, '2013-04-22 00:00:00', -1, 'Julius', 'Felix', 'Alex', 'Firestone', '', '', '', '', '', '', ''),
(16, '2013-04-22 00:00:00', -2, 'Kyenshama', 'Town', 'Office', 'Sanga', '', '', '', '', '', '', ''),
(17, '2013-04-22 00:00:00', 660345, '30', '120', '81', '60', '', '', '', '', '', '', ''),
(18, '2013-04-22 00:00:00', 661314, '0', '5', '6', '0', '', '', '', '', '', '', ''),
(19, '2013-04-22 00:00:00', 642754, '10', '25', '0', '0', '', '', '', '', '', '', ''),
(20, '2013-04-22 00:00:00', -1, 'Julius', 'Felix', 'Alex', 'Firestone', '', '', '', '', '', '', ''),
(21, '2013-04-22 00:00:00', -2, 'Kyenshama', 'Town', 'Office', 'Sanga', '', '', '', '', '', '', ''),
(22, '2013-04-22 00:00:00', 660345, '30', '120', '81', '60', '', '', '', '', '', '', ''),
(23, '2013-04-22 00:00:00', 661314, '0', '5', '6', '0', '', '', '', '', '', '', ''),
(24, '2013-04-22 00:00:00', 642754, '10', '25', '0', '0', '', '', '', '', '', '', ''),
(25, '2013-04-22 00:00:00', -1, 'Julius', 'Felix', 'Alex', 'Firestone', '', '', '', '', '', '', ''),
(26, '2013-04-22 00:00:00', -2, 'Kyenshama', 'Town', 'Office', 'Sanga', '', '', '', '', '', '', ''),
(27, '2013-04-22 00:00:00', 660345, '30', '120', '81', '60', '', '', '', '', '', '', ''),
(28, '2013-04-22 00:00:00', 661314, '0', '5', '6', '0', '', '', '', '', '', '', ''),
(29, '2013-04-22 00:00:00', 642754, '10', '25', '0', '0', '', '', '', '', '', '', ''),
(30, '2013-04-22 00:00:00', -1, 'Julius', 'Felix', 'Alex', 'Firestone', '', '', '', '', '', '', ''),
(31, '2013-04-22 00:00:00', -2, 'Kyenshama', 'Town', 'Office', 'Sanga', '', '', '', '', '', '', ''),
(32, '2013-04-22 00:00:00', 660345, '30', '120', '81', '60', '', '', '', '', '', '', ''),
(33, '2013-04-22 00:00:00', 661314, '0', '5', '6', '0', '', '', '', '', '', '', ''),
(34, '2013-04-22 00:00:00', 642754, '10', '25', '0', '0', '', '', '', '', '', '', '');

-- --------------------------------------------------------

--
-- Table structure for table `Analysis_Book_Returns`
--

CREATE TABLE IF NOT EXISTS `Analysis_Book_Returns` (
  `analysis_book_returns_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `product_number` int(11) NOT NULL,
  `sales_person_0` varchar(60) NOT NULL,
  `sales_person_1` varchar(60) NOT NULL,
  `sales_person_2` varchar(60) NOT NULL,
  `sales_person_3` varchar(60) NOT NULL,
  `sales_person_4` varchar(60) NOT NULL,
  `sales_person_5` varchar(60) NOT NULL,
  `sales_person_6` varchar(60) NOT NULL,
  `sales_person_7` varchar(60) NOT NULL,
  `sales_person_8` varchar(60) NOT NULL,
  `sales_person_9` varchar(60) NOT NULL,
  `sales_person_10` varchar(60) NOT NULL,
  PRIMARY KEY (`analysis_book_returns_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store analysis book returns' AUTO_INCREMENT=11 ;

--
-- Dumping data for table `Analysis_Book_Returns`
--

INSERT INTO `Analysis_Book_Returns` (`analysis_book_returns_id`, `date`, `product_number`, `sales_person_0`, `sales_person_1`, `sales_person_2`, `sales_person_3`, `sales_person_4`, `sales_person_5`, `sales_person_6`, `sales_person_7`, `sales_person_8`, `sales_person_9`, `sales_person_10`) VALUES
(6, '2013-04-22 00:00:00', -1, 'Julius', 'Felix', 'Alex', 'Firestone', '', '', '', '', '', '', ''),
(7, '2013-04-22 00:00:00', -2, 'Kyenshama', 'Town', 'Office', 'Sanga', '', '', '', '', '', '', ''),
(8, '2013-04-22 00:00:00', 660345, '0', '50', '64', '4', '', '', '', '', '', '', ''),
(9, '2013-04-22 00:00:00', 661314, '0', '5', '4', '0', '', '', '', '', '', '', ''),
(10, '2013-04-22 00:00:00', 642754, '0', '01', '16', '0', '', '', '', '', '', '', '');

-- --------------------------------------------------------

--
-- Table structure for table `Analysis_Book_Sales`
--

CREATE TABLE IF NOT EXISTS `Analysis_Book_Sales` (
  `analysis_book_sales_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `product_number` int(11) NOT NULL,
  `sales_person_0` varchar(60) NOT NULL,
  `sales_person_1` varchar(60) NOT NULL,
  `sales_person_2` varchar(60) NOT NULL,
  `sales_person_3` varchar(60) NOT NULL,
  `sales_person_4` varchar(60) NOT NULL,
  `sales_person_5` varchar(60) NOT NULL,
  `sales_person_6` varchar(60) NOT NULL,
  `sales_person_7` varchar(60) NOT NULL,
  `sales_person_8` varchar(60) NOT NULL,
  `sales_person_9` varchar(60) NOT NULL,
  `sales_person_10` varchar(60) NOT NULL,
  PRIMARY KEY (`analysis_book_sales_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store analysis book sales' AUTO_INCREMENT=11 ;

--
-- Dumping data for table `Analysis_Book_Sales`
--

INSERT INTO `Analysis_Book_Sales` (`analysis_book_sales_id`, `date`, `product_number`, `sales_person_0`, `sales_person_1`, `sales_person_2`, `sales_person_3`, `sales_person_4`, `sales_person_5`, `sales_person_6`, `sales_person_7`, `sales_person_8`, `sales_person_9`, `sales_person_10`) VALUES
(6, '2013-04-22 00:00:00', -1, 'Julius', 'Felix', 'Alex', 'Firestone', '', '', '', '', '', '', ''),
(7, '2013-04-22 00:00:00', -2, 'Kyenshama', 'Town', 'Office', 'Sanga', '', '', '', '', '', '', ''),
(8, '2013-04-22 00:00:00', 660345, '30', '70', '17', '56', '', '', '', '', '', '', ''),
(9, '2013-04-22 00:00:00', 661314, '0', '0', '2', '0', '', '', '', '', '', '', ''),
(10, '2013-04-22 00:00:00', 642754, '10', '24', '-16', '0', '', '', '', '', '', '', '');

-- --------------------------------------------------------

--
-- Table structure for table `Analysis_Book_Stock`
--

CREATE TABLE IF NOT EXISTS `Analysis_Book_Stock` (
  `analysis_book_stock_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `product_number` int(11) NOT NULL,
  `balance_brought_forward` int(11) NOT NULL,
  `purchases` int(11) NOT NULL,
  `ubl_breakage` int(11) NOT NULL,
  `free_issue` int(11) NOT NULL,
  `end_of_day` int(11) NOT NULL,
  PRIMARY KEY (`analysis_book_stock_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store analysis book stock' AUTO_INCREMENT=7 ;

--
-- Dumping data for table `Analysis_Book_Stock`
--

INSERT INTO `Analysis_Book_Stock` (`analysis_book_stock_id`, `date`, `product_number`, `balance_brought_forward`, `purchases`, `ubl_breakage`, `free_issue`, `end_of_day`) VALUES
(4, '2013-04-22 00:00:00', 660345, 5872, 5872, 5872, 0, 5699),
(5, '2013-04-22 00:00:00', 661314, 55, 55, 55, 0, 53),
(6, '2013-04-22 00:00:00', 642754, 64, 64, 64, 0, 20);

-- --------------------------------------------------------

--
-- Table structure for table `Beer_Invoice`
--

CREATE TABLE IF NOT EXISTS `Beer_Invoice` (
  `beer_invoice_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `beer_invoice_number` int(11) NOT NULL,
  `beer_invoice_date` datetime NOT NULL,
  `client` varchar(60) NOT NULL,
  `payment_due_date` datetime NOT NULL,
  `authorized_by` varchar(60) NOT NULL,
  `received_by` varchar(60) NOT NULL,
  `total` double NOT NULL,
  PRIMARY KEY (`beer_invoice_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store Beer Invoices' AUTO_INCREMENT=7 ;

--
-- Dumping data for table `Beer_Invoice`
--

INSERT INTO `Beer_Invoice` (`beer_invoice_id`, `beer_invoice_number`, `beer_invoice_date`, `client`, `payment_due_date`, `authorized_by`, `received_by`, `total`) VALUES
(1, 609, '2013-05-10 00:00:00', 'Baija Kenneth/ Jennifer Baija', '2013-05-10 00:00:00', 'Yaguma', '', 450000),
(3, 1, '2013-10-14 00:00:00', '111', '2013-10-14 00:00:00', 'paul', 'paul', 2500),
(4, 0, '2013-11-20 00:00:00', '', '2013-11-20 00:00:00', '', '', 0),
(5, 0, '2014-02-25 00:00:00', '', '2014-02-25 00:00:00', '', '', 0),
(6, 12345656, '2014-02-25 00:00:00', 'Sample M/S', '2014-02-25 00:00:00', 'Clovis', 'Manager', 4250000);

-- --------------------------------------------------------

--
-- Table structure for table `Beer_Invoice_Entry`
--

CREATE TABLE IF NOT EXISTS `Beer_Invoice_Entry` (
  `beer_invoice_entry_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `beer_invoice_id` bigint(20) NOT NULL,
  `product_number` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `rate` double NOT NULL,
  PRIMARY KEY (`beer_invoice_entry_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store Beer Invoice Entry' AUTO_INCREMENT=31 ;

--
-- Dumping data for table `Beer_Invoice_Entry`
--

INSERT INTO `Beer_Invoice_Entry` (`beer_invoice_entry_id`, `beer_invoice_id`, `product_number`, `quantity`, `rate`) VALUES
(1, 1, 660345, 10, 45000),
(2, 1, 661314, 0, 0),
(3, 1, 642754, 0, 0),
(4, 1, 350605, 0, 0),
(5, 1, 616874, 0, 0),
(15, 3, 616874, 0, 0),
(14, 3, 350605, 0, 0),
(13, 3, 642754, 0, 0),
(12, 3, 661314, 0, 0),
(11, 3, 660345, 1, 2500),
(16, 4, 660345, 0, 0),
(17, 4, 661314, 0, 0),
(18, 4, 642754, 0, 0),
(19, 4, 350605, 0, 0),
(20, 4, 616874, 0, 0),
(21, 5, 660345, 0, 0),
(22, 5, 661314, 0, 0),
(23, 5, 642754, 0, 0),
(24, 5, 350605, 0, 0),
(25, 5, 616874, 0, 0),
(26, 6, 660345, 500, 2300),
(27, 6, 661314, 200, 2500),
(28, 6, 642754, 100, 3000),
(29, 6, 350605, 1000, 1500),
(30, 6, 616874, 400, 2000);

-- --------------------------------------------------------

--
-- Table structure for table `Beer_Issue_Returns_And_Pay_Slip`
--

CREATE TABLE IF NOT EXISTS `Beer_Issue_Returns_And_Pay_Slip` (
  `beer_issue_returns_and_pay_slip_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `number` int(11) NOT NULL DEFAULT '0',
  `date` datetime NOT NULL,
  `to_address` varchar(160) NOT NULL,
  `sales_person` varchar(60) NOT NULL,
  `vehicle_registration_number` varchar(16) NOT NULL,
  `sales_person_note` varchar(100) NOT NULL,
  `store_keeper` varchar(60) NOT NULL,
  `manager` varchar(60) NOT NULL,
  `security_guard` varchar(60) NOT NULL,
  `empties_in` int(11) NOT NULL DEFAULT '0',
  `empties_out` int(11) NOT NULL DEFAULT '0',
  `shells_in` int(11) NOT NULL DEFAULT '0',
  `shells_out` int(11) NOT NULL DEFAULT '0',
  `number_of_products` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`beer_issue_returns_and_pay_slip_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store beer issue returns and pay slip' AUTO_INCREMENT=5 ;

--
-- Dumping data for table `Beer_Issue_Returns_And_Pay_Slip`
--

INSERT INTO `Beer_Issue_Returns_And_Pay_Slip` (`beer_issue_returns_and_pay_slip_id`, `number`, `date`, `to_address`, `sales_person`, `vehicle_registration_number`, `sales_person_note`, `store_keeper`, `manager`, `security_guard`, `empties_in`, `empties_out`, `shells_in`, `shells_out`, `number_of_products`) VALUES
(3, 5071, '2012-10-01 00:00:00', 'Rubindi', 'Julius', 'UAN634Z', 'offloading paid', '', '', '', 0, 0, 0, 0, 5),
(2, 5071, '2012-10-01 00:00:00', 'Rubindi', 'Julius', 'UAN634Z', 'offloading paid', '', '', '', 0, 0, 0, 0, 3),
(4, 0, '0000-00-00 00:00:00', '', '', '', '', '', '', '', 0, 0, 0, 0, 5);

-- --------------------------------------------------------

--
-- Table structure for table `Beer_Issue_Returns_And_Pay_Slip_Entry`
--

CREATE TABLE IF NOT EXISTS `Beer_Issue_Returns_And_Pay_Slip_Entry` (
  `beer_issue_returns_and_pay_slip_entry_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `beer_issue_returns_and_pay_slip_id` bigint(20) NOT NULL,
  `brand` varchar(60) NOT NULL,
  `received_from_store` int(11) NOT NULL DEFAULT '0',
  `additions` int(11) NOT NULL DEFAULT '0',
  `sales_returns` int(11) NOT NULL DEFAULT '0',
  `total_sales_quantity` int(11) NOT NULL DEFAULT '0',
  `beer_breakages` int(11) NOT NULL DEFAULT '0',
  `loose_breakages` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`beer_issue_returns_and_pay_slip_entry_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store beer issue returns and pay slip entry' AUTO_INCREMENT=9 ;

--
-- Dumping data for table `Beer_Issue_Returns_And_Pay_Slip_Entry`
--

INSERT INTO `Beer_Issue_Returns_And_Pay_Slip_Entry` (`beer_issue_returns_and_pay_slip_entry_id`, `beer_issue_returns_and_pay_slip_id`, `brand`, `received_from_store`, `additions`, `sales_returns`, `total_sales_quantity`, `beer_breakages`, `loose_breakages`) VALUES
(1, 2, 'Bell Lager 500ml RET 25X01 LONGNECK', 141, 0, 2, 139, 3, 0),
(2, 2, 'Tusker In Bottle 500ml RET 25X01 LOCAL', 130, 0, 55, 75, 0, 0),
(3, 2, 'Tusker Malt In 330ml RET 25X01', 2, 0, 2, 0, 0, 0),
(4, 4, 'Bell Lager 500ml RET 25X01 LONGNECK', 0, 0, 0, 0, 0, 0),
(5, 4, 'Tusker In Bottle 500ml RET 25X01 LOCAL', 0, 0, 0, 0, 0, 0),
(6, 4, 'Tusker Malt In 330ml RET 25X01', 0, 0, 0, 0, 0, 0),
(7, 4, 'Shell - Large Plastic', 0, 0, 0, 0, 0, 0),
(8, 4, 'Senator Lager 500ml RET 25X01', 0, 0, 0, 0, 0, 0);

-- --------------------------------------------------------

--
-- Table structure for table `Beer_Tax_Invoice_From_UBL`
--

CREATE TABLE IF NOT EXISTS `Beer_Tax_Invoice_From_UBL` (
  `beer_tax_invoice_from_ubl_id` int(11) NOT NULL AUTO_INCREMENT,
  `document_number` bigint(20) NOT NULL,
  `our_reference` int(11) NOT NULL,
  `order_date` datetime NOT NULL,
  `document_date` datetime NOT NULL,
  `purchase_order_number` varchar(30) NOT NULL,
  `dispatch_date` datetime NOT NULL,
  `invoice_address` varchar(160) NOT NULL,
  `invoice_branch_id` int(11) NOT NULL,
  `account_number` varchar(12) NOT NULL,
  `payment_due_date` datetime NOT NULL,
  `consignment_note_number` int(12) NOT NULL,
  `delivery_note_number` int(12) NOT NULL,
  `order_type` varchar(20) NOT NULL,
  `tax_point_date` datetime NOT NULL,
  `arrival_date` datetime NOT NULL,
  `delivery_address` varchar(160) NOT NULL,
  `delivery_branch_id` int(11) NOT NULL,
  `customer_vat_number` varchar(12) NOT NULL,
  `supplier_tin_number` varchar(20) NOT NULL,
  `supplier_vat_number` varchar(12) NOT NULL,
  `payment_terms` varchar(160) NOT NULL,
  `vehicle_number` varchar(16) NOT NULL,
  `haulier` varchar(160) NOT NULL,
  `order_created_by` varchar(30) NOT NULL,
  `dispatched_from` varchar(160) NOT NULL,
  `delivery_terms` varchar(30) NOT NULL,
  `line_values_calculated_total` double NOT NULL DEFAULT '0',
  `line_values_entered_total` double NOT NULL,
  `taxable_value` double NOT NULL,
  `vat_rate` double NOT NULL,
  `tax_amount` double NOT NULL,
  `currency` varchar(3) NOT NULL,
  `total_due` double NOT NULL,
  `number_of_line_entries` int(11) NOT NULL,
  PRIMARY KEY (`beer_tax_invoice_from_ubl_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store beer tax invoice from UBL' AUTO_INCREMENT=30 ;

--
-- Dumping data for table `Beer_Tax_Invoice_From_UBL`
--

INSERT INTO `Beer_Tax_Invoice_From_UBL` (`beer_tax_invoice_from_ubl_id`, `document_number`, `our_reference`, `order_date`, `document_date`, `purchase_order_number`, `dispatch_date`, `invoice_address`, `invoice_branch_id`, `account_number`, `payment_due_date`, `consignment_note_number`, `delivery_note_number`, `order_type`, `tax_point_date`, `arrival_date`, `delivery_address`, `delivery_branch_id`, `customer_vat_number`, `supplier_tin_number`, `supplier_vat_number`, `payment_terms`, `vehicle_number`, `haulier`, `order_created_by`, `dispatched_from`, `delivery_terms`, `line_values_calculated_total`, `line_values_entered_total`, `taxable_value`, `vat_rate`, `tax_amount`, `currency`, `total_due`, `number_of_line_entries`) VALUES
(13, 9550165677, 101088272, '2013-03-24 00:00:00', '2013-03-24 00:00:00', 'P08102012111336', '2013-03-24 00:00:00', 'Ankole Original Traders,499, ,, MBARARA,UGANDA', 0, '111335', '2013-03-24 00:00:00', 0, 49250598, 'Duty Paid', '2013-03-24 00:00:00', '0000-00-00 00:00:00', 'Ankole Original Traders - Ibanda,499, ,, IBANDA,UGANDA', 0, '10567Q', '1000023775', '11616K', '9 Days from Invoice Date', 'UAQ092C', 'Tibbett and Britten International,, ,, ,', 'MPANJALL', 'DHL Uganda (Port Bell),7130, ,, KAMPALA,UGANDA', 'Ex Works', 46952334.5, 0, 46952331.38, 18, 8451419.65, 'UGX', 55403751.03, 1),
(14, 9550179563, 101847774, '2013-04-23 00:00:00', '2013-04-24 00:00:00', 'A23044013111335/1', '2013-04-23 00:00:00', 'Ankole Original Traders,499, ,, MBARARA,UGANDA', 0, '111335', '2013-04-03 00:00:00', 0, 41316146, 'Duty Paid', '2013-04-24 00:00:00', '0000-00-00 00:00:00', 'Ankole Original Traders,499, ,, MBARARA,UGANDA', 0, '10567Q', '1000023775', '11616K', '9 Days from Invoice Date', '', 'Tibbett and Britten International,, ,, ,', 'OCHAKIVA', 'DHL Uganda (Port Bell),7130, ,, KAMPALA,UGANDA', 'Ex Works', 34862713, 0, 34862710.32, 18, 6275287.86, 'UGX', 41137998.18, 1),
(15, 2, 2, '2013-07-11 00:00:00', '2013-07-11 00:00:00', '2', '2013-07-11 00:00:00', 'Ankole Original Traders - Ibanda, 499, , , , IBANDA, UGANDA', 2, '111335', '2013-07-11 00:00:00', 2, 2, 'Duty Paid', '2013-07-11 00:00:00', '0000-00-00 00:00:00', 'Ankole Original Traders - Ibanda, 499, , , , IBANDA, UGANDA', 2, '10567Q', '1000023775', '11616K', '9 Days from Invoice Date', 'UAN202W', 'Tibbett and Britten International,7062, ,, ,', 'Arthur', 'DHL Uganda (Port Bell),7130, ,, KAMPALA,UGANDA', 'Ex Works', 0, 0, 20000, 18, 3600, 'UGX', 23600, 1),
(16, 3, 3, '2013-07-12 00:00:00', '2013-07-12 00:00:00', '3', '2013-07-12 00:00:00', 'Ankole Original Traders, 499, , , , MBARARA, UGANDA', 1, '111335', '2013-07-12 00:00:00', 3, 3, 'Duty Paid', '2013-07-12 00:00:00', '0000-00-00 00:00:00', 'Ankole Original Traders - Ibanda, 499, , , , IBANDA, UGANDA', 2, '10567Q', '1000023775', '11616K', '9 Days from Invoice Date', 'UAR560R', 'Tibbett and Britten International,7062, ,, ,', 'Arthur', 'DHL Uganda (Port Bell),7130, ,, KAMPALA,UGANDA', 'Ex Works', 30000, 0, 30000, 18, 5400, 'UGX', 35400, 1),
(17, 3, 3, '2013-12-02 00:00:00', '2013-12-16 00:00:00', '3', '2013-12-24 00:00:00', 'Ankole Original Traders, 499, , , , MBARARA, UGANDA', 1, '111335', '2013-12-26 00:00:00', 3, 3, 'Duty Paid', '2013-12-31 00:00:00', '0000-00-00 00:00:00', 'Ankole Original Traders, 499, , , , MBARARA, UGANDA', 1, '10567Q', '1000023775', '11616K', '9 Days from Invoice Date', '333', 'Tibbett and Britten International,7062, ,, ,', '3', 'DHL Uganda (Port Bell),7130, ,, KAMPALA,UGANDA', 'Ex Works', 0, 0, 3, 18, 0.54, 'UGX', 3.54, 1),
(27, 12344556, 1010882718, '2014-03-07 00:00:00', '2014-03-07 00:00:00', 'P09333647846', '2014-03-07 00:00:00', 'Ankole Original Traders, 499, , , , MBARARA, UGANDA', 1, '', '2014-03-07 00:00:00', 2147483647, 534227723, '', '2014-03-07 00:00:00', '0000-00-00 00:00:00', 'Ankole Original Traders, 499, , , , MBARARA, UGANDA', 1, '', '', '', 'Cash', 'UAK 245W', 'Tibbett and Britten International,7062, ,, ,', 'Clovis', 'DHL Uganda (Port Bell),7130, ,, KAMPALA,UGANDA', '', 34141000, 34141002, 1000000, 15, 0, '', 1000000, 1),
(25, 22222222, 101088271, '2014-03-07 00:00:00', '2014-03-07 00:00:00', 'P09333647845', '2014-03-07 00:00:00', 'Ankole Original Traders, 499, , , , MBARARA, UGANDA', 1, '', '2014-03-07 00:00:00', 2147483647, 534227723, '', '2014-03-07 00:00:00', '0000-00-00 00:00:00', 'Ankole Original Traders, 499, , , , MBARARA, UGANDA', 1, '', '', '', 'Cash', 'UAK 245X', 'Tibbett and Britten International,7062, ,, ,', 'Clovis', 'DHL Uganda (Port Bell),7130, ,, KAMPALA,UGANDA', '', 23216200, 0, 1000000, 15, 0, '', 1000000, 2),
(24, 1234455, 101088271, '2014-03-07 00:00:00', '2014-03-07 00:00:00', 'P0933364784', '2014-03-07 00:00:00', 'Ankole Original Traders, 499, , , , MBARARA, UGANDA', 1, '', '2014-03-07 00:00:00', 2147483647, 534227722, '', '2014-03-07 00:00:00', '0000-00-00 00:00:00', 'Ankole Original Traders, 499, , , , MBARARA, UGANDA', 1, '', '', '', 'Cash', 'UAK 245T', 'Tibbett and Britten International,7062, ,, ,', 'Clovis', 'DHL Uganda (Port Bell),7130, ,, KAMPALA,UGANDA', '', 18801500, 0, 1000000, 12, 200000, '', 1000000, 2),
(23, 1234455, 101088271, '2014-03-07 00:00:00', '2014-03-07 00:00:00', 'P0933364784', '2014-03-07 00:00:00', 'Ankole Original Traders, 499, , , , MBARARA, UGANDA', 1, '', '2014-03-07 00:00:00', 2147483647, 534227722, '', '2014-03-07 00:00:00', '0000-00-00 00:00:00', 'Ankole Original Traders, 499, , , , MBARARA, UGANDA', 1, '', '', '', 'Cash', 'UAK 245T', 'Tibbett and Britten International,7062, ,, ,', 'Clovis', 'DHL Uganda (Port Bell),7130, ,, KAMPALA,UGANDA', '', 18801500, 0, 1000000, 12, 200000, '', 1000000, 2);

-- --------------------------------------------------------

--
-- Table structure for table `Beer_Tax_Invoice_From_UBL_Entry`
--

CREATE TABLE IF NOT EXISTS `Beer_Tax_Invoice_From_UBL_Entry` (
  `beer_tax_invoice_from_ubl_entry_id` int(11) NOT NULL AUTO_INCREMENT,
  `beer_tax_invoice_from_ubl_id` int(11) NOT NULL,
  `product_number` int(11) NOT NULL,
  `product_description` varchar(60) NOT NULL,
  `product_abv_percent` double NOT NULL,
  `quantity` int(11) NOT NULL,
  `uom` varchar(6) NOT NULL,
  `base_price` double NOT NULL,
  `customer_discount` double NOT NULL,
  `promotional_discount` double NOT NULL,
  `net_price_per_unit` double NOT NULL,
  `line_value` double NOT NULL,
  `line_value_entered` double NOT NULL,
  `vat_rate` double NOT NULL,
  PRIMARY KEY (`beer_tax_invoice_from_ubl_entry_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store beer tax invoice from UBL entries' AUTO_INCREMENT=67 ;

--
-- Dumping data for table `Beer_Tax_Invoice_From_UBL_Entry`
--

INSERT INTO `Beer_Tax_Invoice_From_UBL_Entry` (`beer_tax_invoice_from_ubl_entry_id`, `beer_tax_invoice_from_ubl_id`, `product_number`, `product_description`, `product_abv_percent`, `quantity`, `uom`, `base_price`, `customer_discount`, `promotional_discount`, `net_price_per_unit`, `line_value`, `line_value_entered`, `vat_rate`) VALUES
(1, 2, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 300, 'CAS', 36229, 0, 0, 36228.82, 10868644.8, 0, 15),
(2, 2, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 300, 'CAS', 36229, 0, 0, 36228.82, 10868644.8, 0, 18),
(3, 2, 661314, 'Tusker In Bottle 500ml RET 25X01 LOCAL', 4.2, 50, 'CAS', 36186, 0, 0, 36186.44, 15254236, 0, 18),
(4, 3, 661314, '', 4, 300, '', 36229, 0, 0, 36228.82, 10868644.8, 0, 18),
(5, 6, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 300, 'CAS', 36229, 0, 0, 36228.82, 10868646, 0, 18),
(6, 6, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 525, 'CAS', 36229, 0, 0, 36228.82, 19020130.5, 0, 18),
(7, 6, 661314, 'Tusker In Bottle 500ml RET 25X01 LOCAL', 4.2, 50, 'CAS', 36186, 0, 0, 36186.44, 1809322, 0, 18),
(8, 6, 642754, 'Tusker Malt In 330ml RET 25X01', 5, 400, 'CAS', 38136, 0, 0, 38135.59, 15254236, 0, 18),
(9, 7, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 300, 'CAS', 36229, 0, 0, 36228.82, 10868646, 0, 18),
(10, 7, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 525, 'CAS', 36229, 0, 0, 36228.82, 19020130.5, 0, 18),
(11, 7, 661314, 'Tusker In Bottle 500ml RET 25X01 LOCAL', 4.2, 50, 'CAS', 36186, 0, 0, 36186.44, 1809322, 0, 18),
(12, 7, 642754, 'Tusker Malt In 330ml RET 25X01', 5, 400, 'CAS', 38136, 0, 0, 38135.59, 15254236, 0, 18),
(40, 14, 616874, 'Senator Lager 500ml RET 25X01', 6, 1340, 'CAS', 26016.95, 0, 0, 26016.95, 34862713, 0, 18),
(14, 8, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 525, 'CAS', 36229, 0, 0, 36228.82, 19020130.5, 0, 18),
(15, 8, 661314, 'Tusker In Bottle 500ml RET 25X01 LOCAL', 4.2, 50, 'CAS', 36186, 0, 0, 36186.44, 1809322, 0, 18),
(16, 8, 642754, 'Tusker Malt In 330ml RET 25X01', 5, 400, 'CAS', 38136, 0, 0, 38135.59, 15254236, 0, 18),
(17, 9, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 300, 'CAS', 36229, 0, 0, 36228.82, 10868646, 0, 18),
(18, 9, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 525, 'CAS', 36229, 0, 0, 36228.82, 19020130.5, 0, 18),
(19, 9, 661314, 'Tusker In Bottle 500ml RET 25X01 LOCAL', 4.2, 50, 'CAS', 36186, 0, 0, 36186.44, 1809322, 0, 18),
(20, 9, 642754, 'Tusker Malt In 330ml RET 25X01', 5, 400, 'CAS', 38136, 0, 0, 38135.59, 15254236, 0, 18),
(21, 10, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 300, 'CAS', 36229, 0, 0, 36228.82, 10868646, 0, 18),
(22, 10, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 525, 'CAS', 36229, 0, 0, 36228.82, 19020130.5, 0, 18),
(23, 10, 661314, 'Tusker In Bottle 500ml RET 25X01 LOCAL', 4.2, 50, 'CAS', 36186, 0, 0, 36186.44, 1809322, 0, 18),
(24, 10, 642754, 'Tusker Malt In 330ml RET 25X01', 5, 400, 'CAS', 38136, 0, 0, 38135.59, 15254236, 0, 18),
(25, 11, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 300, 'CAS', 36229, 0, 0, 36228.82, 10868646, 0, 18),
(26, 11, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 525, 'CAS', 36229, 0, 0, 36228.82, 19020130.5, 0, 18),
(27, 11, 661314, 'Tusker In Bottle 500ml RET 25X01 LOCAL', 4.2, 50, 'CAS', 36186, 0, 0, 36186.44, 1809322, 0, 18),
(28, 11, 642754, 'Tusker Malt In 330ml RET 25X01', 5, 400, 'CAS', 38136, 0, 0, 38135.59, 15254236, 0, 18),
(39, 13, 661314, 'Tusker In Bottle 500ml RET 25X01 LOCAL', 4.2, 50, 'CAS', 36186.44, 0, 0, 36186.44, 1809322, 0, 18),
(34, 13, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 525, 'CAS', 36229, 0, 0, 36228.82, 19020130.5, 0, 18),
(33, 13, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 300, 'CAS', 36229, 0, 0, 36228.82, 10868646, 0, 18),
(36, 13, 642754, 'Tusker Malt In 330ml RET 25X01', 5, 400, 'CAS', 38136, 0, 0, 38135.59, 15254236, 0, 18),
(41, 15, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 0, 'CAS', 20000, 0, 0, 20000, 0, 0, 18),
(42, 16, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 1, 'CAS', 30000, 0, 0, 30000, 30000, 0, 18),
(43, 17, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 0, 'CAS', 0, 0, 0, 0, 0, 0, 18),
(44, 18, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 200, 'CAS', 2500, 1, 5, 2650, 530000, 0, 12),
(45, 19, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 200, 'CAS', 2500, 1, 5, 2650, 530000, 0, 12),
(46, 20, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 200, 'CAS', 36250, 0, 0, 36250, 7250000, 0, 15),
(47, 20, 661314, 'Tusker In Bottle 500ml RET 25X01 LOCAL', 4.2, 300, 'CAS', 38505, 0, 0, 38505, 11551500, 0, 18),
(48, 21, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 200, 'CAS', 36250, 0, 0, 36250, 7250000, 0, 15),
(49, 21, 661314, 'Tusker In Bottle 500ml RET 25X01 LOCAL', 4.2, 300, 'CAS', 38505, 0, 0, 38505, 11551500, 0, 18),
(50, 22, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 200, 'CAS', 36250, 0, 0, 36250, 7250000, 0, 15),
(51, 22, 661314, 'Tusker In Bottle 500ml RET 25X01 LOCAL', 4.2, 300, 'CAS', 38505, 0, 0, 38505, 11551500, 0, 18),
(52, 23, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 200, 'CAS', 36250, 0, 0, 36250, 7250000, 0, 15),
(53, 23, 661314, 'Tusker In Bottle 500ml RET 25X01 LOCAL', 4.2, 300, 'CAS', 38505, 0, 0, 38505, 11551500, 0, 18),
(54, 24, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 200, 'CAS', 36250, 0, 0, 36250, 7250000, 0, 15),
(55, 24, 661314, 'Tusker In Bottle 500ml RET 25X01 LOCAL', 4.2, 300, 'CAS', 38505, 0, 0, 38505, 11551500, 0, 18),
(56, 25, 642754, 'Tusker Malt In 330ml RET 25X01', 5, 500, 'CAS', 36250, 0, 0, 36250, 18125000, 0, 15),
(57, 25, 616874, 'Senator Lager 500ml RET 25X01', 6, 200, 'CAS', 25456, 0, 0, 25456, 5091200, 0, 18),
(58, 26, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 300, 'CAS', 36523, 0, 0, 36523, 10956900, 0, 12),
(59, 26, 661314, 'Tusker In Bottle 500ml RET 25X01 LOCAL', 4.2, 500, 'CAS', 36543, 0, 0, 36543, 18271500, 0, 18),
(60, 27, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 300, 'CAS', 36523, 0, 0, 36523, 10956900, 10956902, 12),
(61, 27, 661314, 'Tusker In Bottle 500ml RET 25X01 LOCAL', 4.2, 500, 'CAS', 36543, 0, 0, 36543, 18271500, 18271500, 18),
(62, 28, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 300, 'CAS', 36523, 0, 0, 36523, 10956900, 10956900, 12),
(63, 28, 661314, 'Tusker In Bottle 500ml RET 25X01 LOCAL', 4.2, 500, 'CAS', 36543, 0, 0, 36543, 18271500, 18271500, 18),
(64, 29, 660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 4, 200, 'CAS', 36250, 0, 0, 36250, 7250000, 7250000, 12),
(65, 27, 616874, 'Senator Lager 500ml RET 25X01', 6, 100, 'CAS', 24563, 0, 0, 24563, 2456300, 2456300, 18),
(66, 27, 616874, 'Senator Lager 500ml RET 25X01', 6, 100, 'CAS', 24563, 0, 0, 24563, 2456300, 2456300, 18);

-- --------------------------------------------------------

--
-- Table structure for table `Branches`
--

CREATE TABLE IF NOT EXISTS `Branches` (
  `branch_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(60) NOT NULL,
  `po_box` varchar(30) NOT NULL,
  `street_number` varchar(30) NOT NULL,
  `street_name` varchar(100) NOT NULL,
  `area` varchar(100) NOT NULL,
  `city` varchar(100) NOT NULL,
  `country` varchar(100) NOT NULL,
  `last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`branch_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store branch information' AUTO_INCREMENT=5 ;

--
-- Dumping data for table `Branches`
--

INSERT INTO `Branches` (`branch_id`, `name`, `po_box`, `street_number`, `street_name`, `area`, `city`, `country`, `last_modified`) VALUES
(1, 'Ankole Original Traders', '499', '', '', '', 'MBARARA', 'UGANDA', '2014-03-14 07:00:00'),
(2, 'Ankole Original Traders - Ibanda', '499', '', '', '', 'IBANDA', 'UGANDA', '0000-00-00 00:00:00'),
(4, 'Ankole Original Traders - Bushenyi', '499', '', '', '', 'BUSHENYI', 'UGANDA', '2013-07-05 11:27:01');

-- --------------------------------------------------------

--
-- Table structure for table `Breakage_Claim`
--

CREATE TABLE IF NOT EXISTS `Breakage_Claim` (
  `breakage_claim_id` int(11) NOT NULL AUTO_INCREMENT,
  `invoice_date` datetime NOT NULL,
  `branch_id` int(11) NOT NULL,
  `vehicle_number` varchar(20) NOT NULL,
  `invoice_number` bigint(20) NOT NULL,
  PRIMARY KEY (`breakage_claim_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store Breakage Claims' AUTO_INCREMENT=7 ;

--
-- Dumping data for table `Breakage_Claim`
--

INSERT INTO `Breakage_Claim` (`breakage_claim_id`, `invoice_date`, `branch_id`, `vehicle_number`, `invoice_number`) VALUES
(6, '2013-12-03 00:00:00', 1, '', 0),
(5, '2013-11-20 00:00:00', 1, '', 0),
(4, '2012-09-01 00:00:00', 2, '', 9550163573);

-- --------------------------------------------------------

--
-- Table structure for table `Breakage_Claim_Entry`
--

CREATE TABLE IF NOT EXISTS `Breakage_Claim_Entry` (
  `breakage_claim_entry_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `breakage_claim_id` int(11) NOT NULL,
  `product_number` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  PRIMARY KEY (`breakage_claim_entry_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store Breakage Claim Entry' AUTO_INCREMENT=31 ;

--
-- Dumping data for table `Breakage_Claim_Entry`
--

INSERT INTO `Breakage_Claim_Entry` (`breakage_claim_entry_id`, `breakage_claim_id`, `product_number`, `quantity`) VALUES
(1, 0, 660345, 108),
(2, 0, 661314, 0),
(3, 0, 642754, 0),
(4, 0, 350605, 0),
(5, 0, 616874, 0),
(6, 0, 660345, 37),
(7, 0, 661314, 0),
(8, 0, 642754, 0),
(9, 0, 350605, 0),
(10, 0, 616874, 0),
(11, 3, 660345, 29),
(12, 3, 661314, 0),
(13, 3, 642754, 0),
(14, 3, 350605, 0),
(15, 3, 616874, 0),
(16, 4, 660345, 108),
(17, 4, 661314, 0),
(18, 4, 642754, 0),
(19, 4, 350605, 0),
(20, 4, 616874, 0),
(21, 5, 660345, 0),
(22, 5, 661314, 0),
(23, 5, 642754, 0),
(24, 5, 350605, 0),
(25, 5, 616874, 0),
(26, 6, 660345, 0),
(27, 6, 661314, 0),
(28, 6, 642754, 0),
(29, 6, 350605, 0),
(30, 6, 616874, 0);

-- --------------------------------------------------------

--
-- Table structure for table `Clients`
--

CREATE TABLE IF NOT EXISTS `Clients` (
  `client_id` int(11) NOT NULL AUTO_INCREMENT,
  `business_name` varchar(60) NOT NULL,
  `contact_name` varchar(60) NOT NULL,
  `contact_phone` varchar(20) NOT NULL,
  `territory_id` int(11) NOT NULL,
  `client_debt` double NOT NULL,
  PRIMARY KEY (`client_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store clients' AUTO_INCREMENT=7 ;

--
-- Dumping data for table `Clients`
--

INSERT INTO `Clients` (`client_id`, `business_name`, `contact_name`, `contact_phone`, `territory_id`, `client_debt`) VALUES
(1, 'Gabiro', 'Bigirwa', '0793851858', 2, 0),
(3, 'Agip Motel', 'Muhwezi', '0795551234', 0, 0),
(4, 'The Heat', 'Silvia', '0772551234', 0, 0),
(5, 'Rwamporo', 'Rwamporo', '07017771234', 0, 3500000);

-- --------------------------------------------------------

--
-- Table structure for table `Client_Debt`
--

CREATE TABLE IF NOT EXISTS `Client_Debt` (
  `client_debt_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `client_id` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `details` varchar(160) NOT NULL,
  `type` varchar(6) NOT NULL,
  `amount` int(11) NOT NULL,
  PRIMARY KEY (`client_debt_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to Store Client Debt' AUTO_INCREMENT=6 ;

--
-- Dumping data for table `Client_Debt`
--

INSERT INTO `Client_Debt` (`client_debt_id`, `client_id`, `date`, `details`, `type`, `amount`) VALUES
(1, 5, '2012-09-01 00:00:00', 'Balance brought forward', 'DEBIT', 5000000),
(2, 5, '2012-09-10 00:00:00', 'Recovery', 'CREDIT', 1500000),
(5, 1, '2013-11-20 00:00:00', '', 'DEBIT', 0);

-- --------------------------------------------------------

--
-- Table structure for table `Dispatchers`
--

CREATE TABLE IF NOT EXISTS `Dispatchers` (
  `dispatcher_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(60) NOT NULL,
  `po_box` varchar(30) NOT NULL,
  `street_number` varchar(30) NOT NULL,
  `street_name` varchar(100) NOT NULL,
  `area` varchar(100) NOT NULL,
  `city` varchar(100) NOT NULL,
  `country` varchar(100) NOT NULL,
  `last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`dispatcher_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store dispatcher information' AUTO_INCREMENT=4 ;

--
-- Dumping data for table `Dispatchers`
--

INSERT INTO `Dispatchers` (`dispatcher_id`, `name`, `po_box`, `street_number`, `street_name`, `area`, `city`, `country`, `last_modified`) VALUES
(1, 'DHL Uganda (Port Bell)', '7130', '', '', '', 'KAMPALA', 'UGANDA', '0000-00-00 00:00:00');

-- --------------------------------------------------------

--
-- Table structure for table `EmployeeLoginLogs`
--

CREATE TABLE IF NOT EXISTS `EmployeeLoginLogs` (
  `username` varchar(60) NOT NULL DEFAULT '',
  `login_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `login_ip` varchar(17) NOT NULL DEFAULT '',
  KEY `clientEmail` (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Records client logins';

--
-- Dumping data for table `EmployeeLoginLogs`
--

INSERT INTO `EmployeeLoginLogs` (`username`, `login_time`, `login_ip`) VALUES
('clovis', '2014-02-23 23:48:30', '41.202.240.3'),
('clovis', '2014-02-23 23:53:20', '41.202.240.3'),
('clovis', '2014-02-23 23:56:58', '41.202.240.3'),
('clovis', '2014-02-23 23:57:16', '41.202.240.3'),
('clovis', '2014-02-24 01:24:28', '41.202.240.3'),
('clovis', '2014-02-24 01:26:20', '41.202.240.3'),
('clovis', '2014-02-24 01:29:47', '41.202.240.3'),
('clovis', '2014-02-24 01:37:32', '41.202.240.11'),
('clovis', '2014-02-24 01:37:39', '41.202.240.11'),
('clovis', '2014-02-24 01:53:40', '41.202.240.11'),
('clovis', '2014-02-24 01:53:51', '41.202.240.11'),
('clovis', '2014-02-24 01:58:08', '41.202.240.11'),
('clovis', '2014-02-24 02:12:14', '41.202.240.11'),
('clovis', '2014-02-24 02:12:38', '41.202.240.11'),
('clovis', '2014-02-24 02:15:10', '41.202.240.11'),
('clovis', '2014-02-24 02:28:27', '41.202.240.9'),
('clovis', '2014-02-24 02:28:47', '41.202.240.9'),
('clovis', '2014-02-24 02:47:07', '41.202.240.9'),
('clovis', '2014-02-24 02:47:33', '41.202.240.9'),
('clovis', '2014-02-24 02:51:10', '41.202.240.9'),
('clovis', '2014-02-24 02:52:49', '41.202.240.9'),
('clovis', '2014-02-24 02:53:29', '41.202.240.9'),
('clovis', '2014-02-24 02:56:14', '41.202.240.9'),
('clovis', '2014-02-24 02:58:07', '41.202.240.9'),
('clovis', '2014-02-24 03:00:29', '41.202.240.9'),
('clovis', '2014-02-24 03:02:42', '41.202.240.9'),
('clovis', '2014-02-24 03:04:25', '41.202.240.9'),
('clovis', '2014-02-24 03:10:19', '41.202.240.9'),
('clovis', '2014-02-24 03:20:52', '41.202.233.186'),
('clovis', '2014-02-24 03:22:01', '41.202.233.186'),
('paul', '2014-02-24 22:59:40', '41.202.240.13'),
('clovis', '2014-02-24 23:06:01', '41.202.240.13'),
('paul', '2014-02-24 23:35:45', '41.202.240.13'),
('clovis', '2014-02-24 23:50:06', '41.202.240.13'),
('paul', '2014-02-25 23:11:50', '41.202.233.178'),
('clovis', '2014-02-25 23:47:25', '41.202.233.178'),
('paul', '2014-02-26 23:34:42', '41.202.240.12'),
('paul', '2014-03-03 04:27:49', '41.202.240.2'),
('paul', '2014-03-03 22:52:09', '41.202.233.187'),
('paul', '2014-03-04 00:10:55', '41.202.233.187'),
('paul', '2014-03-05 01:12:02', '41.202.240.5'),
('paul', '2014-03-05 22:27:52', '41.202.240.1'),
('clovis', '2014-03-05 22:53:25', '41.202.240.1'),
('clovis', '2014-03-07 00:49:36', '41.190.208.198'),
('paul', '2014-03-10 00:29:45', '41.202.233.184'),
('clovis', '2014-03-11 02:56:23', '41.202.240.4'),
('paul', '2014-03-11 03:06:20', '41.202.240.4'),
('paul', '2014-03-12 00:09:01', '197.157.0.10'),
('paul', '2014-03-14 02:10:25', '41.202.233.184'),
('paul', '2014-03-17 00:43:28', '41.202.233.187'),
('paul', '2014-03-17 05:45:02', '41.202.233.187'),
('paul', '2014-03-17 23:59:20', '41.202.240.2'),
('paul', '2014-03-19 00:38:38', '41.202.233.182'),
('paul', '2014-03-19 06:10:57', '41.202.233.182'),
('clovis', '2014-03-21 05:24:50', '41.202.233.183'),
('kapere', '2014-03-25 00:49:16', '197.157.0.14'),
('clovis', '2014-03-25 02:40:49', '197.157.0.14'),
('clovis', '2014-03-25 02:52:09', '197.157.0.14'),
('clovis', '2014-03-25 03:16:05', '197.157.0.14'),
('clovis', '2014-03-25 03:25:08', '197.157.0.14'),
('clovis', '2014-03-25 03:29:24', '197.157.0.14'),
('clovis', '2014-03-25 03:34:42', '197.157.0.14'),
('clovis', '2014-03-25 03:37:42', '197.157.0.14'),
('clovis', '2014-03-25 03:47:34', '197.157.0.14'),
('pascal', '2014-03-25 04:38:50', '197.157.0.14'),
('pascal', '2014-03-25 04:41:53', '197.157.0.14'),
('clovis', '2014-03-25 04:50:10', '197.157.0.14'),
('john', '2014-03-25 05:00:50', '197.157.0.14'),
('john', '2014-03-25 05:11:25', '197.157.0.14'),
('clovis', '2014-03-25 05:13:39', '197.157.0.14'),
('pascal', '2014-03-25 05:15:27', '197.157.0.14'),
('john', '2014-03-25 05:22:05', '197.157.0.14'),
('pascal', '2014-03-26 03:15:28', '41.202.233.177'),
('paul', '2014-03-26 05:26:50', '41.202.233.177'),
('pascal', '2014-03-27 00:16:45', '41.202.240.6'),
('john', '2014-03-27 00:17:09', '41.202.240.6'),
('clovis', '2014-03-27 00:41:39', '41.202.240.6'),
('pascal', '2014-03-27 05:49:16', '41.202.240.6'),
('clovis', '2014-03-27 05:51:16', '41.202.240.6'),
('pascal', '2014-03-27 05:51:49', '41.202.240.6'),
('clovis', '2014-03-27 05:52:24', '41.202.240.6'),
('john', '2014-03-27 05:52:52', '41.202.240.6'),
('paul', '2014-03-27 05:54:28', '41.202.240.6'),
('arthur', '2014-03-27 05:56:01', '41.202.240.6'),
('kale', '2014-03-27 05:56:17', '41.202.240.6'),
('clovis', '2014-03-27 05:56:27', '41.202.240.6'),
('paul', '2014-03-27 05:56:42', '41.202.240.6'),
('arthur', '2014-03-27 05:57:22', '41.202.240.6'),
('arthur', '2014-03-28 04:56:21', '197.157.0.2'),
('philip', '2014-03-28 05:40:29', '197.157.0.2'),
('arthur', '2014-03-28 05:40:42', '197.157.0.2'),
('clovis', '2014-03-28 06:40:02', '197.157.0.2'),
('paul', '2014-03-31 05:13:20', '41.202.233.190'),
('paul', '2014-04-02 00:25:34', '41.202.240.3'),
('paul', '2014-04-02 00:47:06', '41.202.240.6');

-- --------------------------------------------------------

--
-- Table structure for table `Employees`
--

CREATE TABLE IF NOT EXISTS `Employees` (
  `employee_id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(60) NOT NULL,
  `last_name` varchar(60) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `employee_debt` double NOT NULL,
  `username` varchar(60) NOT NULL,
  `password` varchar(100) NOT NULL,
  `type` varchar(20) NOT NULL,
  `last_login_time` datetime NOT NULL,
  `last_login_ip` varchar(20) NOT NULL,
  PRIMARY KEY (`employee_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to Store Employee Information' AUTO_INCREMENT=13 ;

--
-- Dumping data for table `Employees`
--

INSERT INTO `Employees` (`employee_id`, `first_name`, `last_name`, `phone`, `employee_debt`, `username`, `password`, `type`, `last_login_time`, `last_login_ip`) VALUES
(1, 'John', 'Kiwanuka', '[phone]', 163000, 'john', '*196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7', 'data_entrant', '2014-03-27 05:52:52', '41.202.240.6'),
(3, 'Arthur', 'Ntozi', '[phone]', 0, 'arthur', '*0FA338FCF20B7C4C2A977D5CB6CC6C6E439C6824', 'admin', '2014-03-28 05:40:42', '197.157.0.2'),
(6, 'Clovis', 'Wanziguya', '[phone]', 0, 'clovis', '*3DBE4768E69681363B1BFD1F1F6B56AE1410036B', 'admin', '2014-03-28 06:40:02', '197.157.0.2'),
(8, 'Paul', 'Ndungutse', '[phone]', 0, 'paul', '*6FB41365E1B2A47EFD44E5A418287A74A2B84382', 'admin', '2014-04-02 00:47:06', '41.202.240.6'),
(10, 'Pascal', 'Ocan', '[phone]', 0, 'pascal', '*196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7', 'shareholder', '2014-03-27 05:51:49', '41.202.240.6'),
(11, 'kale', 'kayihura', '[phone]', 0, 'kale', '*CCF6C93D6C0C3A57608EA05BF4A43A4BD15D077E', 'accountant', '2014-03-27 05:56:17', '41.202.240.6'),
(12, 'Philip', 'Mbonye', '[phone]', 0, 'philip', '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29', 'shareholder', '2014-03-28 05:40:29', '197.157.0.2');

-- --------------------------------------------------------

--
-- Table structure for table `Employee_Debt`
--

CREATE TABLE IF NOT EXISTS `Employee_Debt` (
  `employee_debt_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `details` varchar(160) NOT NULL,
  `type` varchar(6) NOT NULL,
  `amount` double NOT NULL,
  PRIMARY KEY (`employee_debt_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to Store Employee Debt' AUTO_INCREMENT=11 ;

--
-- Dumping data for table `Employee_Debt`
--

INSERT INTO `Employee_Debt` (`employee_debt_id`, `employee_id`, `date`, `details`, `type`, `amount`) VALUES
(4, 1, '2013-05-02 00:00:00', 'Salary Loan', 'DEBIT', 150000),
(9, 1, '2013-11-20 00:00:00', '', 'DEBIT', 0),
(5, 1, '2013-06-06 00:00:00', 'August Salary', 'CREDIT', 70000),
(6, 1, '2013-06-06 00:00:00', 'September Salary', 'CREDIT', 75000),
(7, 1, '2013-06-06 00:00:00', 'Salary Loan', 'DEBIT', 230000),
(8, 1, '2013-06-06 00:00:00', 'October Salary', 'CREDIT', 72000),
(10, 1, '2013-12-03 00:00:00', '', 'DEBIT', 0);

-- --------------------------------------------------------

--
-- Table structure for table `Empty_Delivery_Note`
--

CREATE TABLE IF NOT EXISTS `Empty_Delivery_Note` (
  `empty_delivery_note_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `distributor` varchar(60) NOT NULL,
  `edn_to` varchar(60) NOT NULL,
  `truck_registration_number` varchar(20) NOT NULL,
  `date` datetime NOT NULL,
  `edn_number` bigint(20) NOT NULL,
  `empties_entry_decl_number` bigint(20) NOT NULL,
  `senders_name` varchar(60) NOT NULL,
  `transporters_driver` varchar(60) NOT NULL,
  `empties_checked_in_by` varchar(60) NOT NULL,
  `empties_verified_in_by` varchar(60) NOT NULL,
  PRIMARY KEY (`empty_delivery_note_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store Empty Delivery Note' AUTO_INCREMENT=5 ;

--
-- Dumping data for table `Empty_Delivery_Note`
--

INSERT INTO `Empty_Delivery_Note` (`empty_delivery_note_id`, `distributor`, `edn_to`, `truck_registration_number`, `date`, `edn_number`, `empties_entry_decl_number`, `senders_name`, `transporters_driver`, `empties_checked_in_by`, `empties_verified_in_by`) VALUES
(3, 'Ankole Original Traders', '', '', '2013-11-20 00:00:00', 0, 0, '', '', '', ''),
(4, 'Ankole Original Traders', '', '', '2013-12-03 00:00:00', 0, 0, '', '', '', ''),
(2, 'Ankole Original Traders', 'EABL', 'UAN202W', '2013-05-30 00:00:00', 301, 1, 'Josephat', 'Kiryowa', 'Janet', 'Janet');

-- --------------------------------------------------------

--
-- Table structure for table `Empty_Delivery_Note_Entry`
--

CREATE TABLE IF NOT EXISTS `Empty_Delivery_Note_Entry` (
  `empty_delivery_note_entry_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `empty_delivery_note_id` bigint(20) NOT NULL,
  `product_number` int(11) NOT NULL,
  `complete_cases` int(11) NOT NULL,
  `shells` int(11) NOT NULL,
  `bottles` int(11) NOT NULL,
  PRIMARY KEY (`empty_delivery_note_entry_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store Empty Delivery Note Entries' AUTO_INCREMENT=31 ;

--
-- Dumping data for table `Empty_Delivery_Note_Entry`
--

INSERT INTO `Empty_Delivery_Note_Entry` (`empty_delivery_note_entry_id`, `empty_delivery_note_id`, `product_number`, `complete_cases`, `shells`, `bottles`) VALUES
(25, 3, 616874, 0, 0, 0),
(24, 3, 350605, 0, 0, 0),
(23, 3, 642754, 0, 0, 0),
(22, 3, 661314, 0, 0, 0),
(21, 3, 660345, 0, 0, 0),
(16, 2, 660345, 10, 0, 0),
(17, 2, 661314, 0, 110, 0),
(18, 2, 642754, 0, 0, 1110),
(19, 2, 350605, 20, 0, 0),
(20, 2, 616874, 0, 220, 0),
(26, 4, 660345, 0, 0, 0),
(27, 4, 661314, 0, 0, 0),
(28, 4, 642754, 0, 0, 0),
(29, 4, 350605, 0, 0, 0),
(30, 4, 616874, 0, 0, 0);

-- --------------------------------------------------------

--
-- Table structure for table `Expense_Types`
--

CREATE TABLE IF NOT EXISTS `Expense_Types` (
  `expense_id` int(11) NOT NULL AUTO_INCREMENT,
  `expense_name` varchar(60) NOT NULL,
  PRIMARY KEY (`expense_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store expense types' AUTO_INCREMENT=7 ;

--
-- Dumping data for table `Expense_Types`
--

INSERT INTO `Expense_Types` (`expense_id`, `expense_name`) VALUES
(1, 'Lunch'),
(2, 'Allowance'),
(3, 'Parking Fee'),
(5, 'Road Toll');

-- --------------------------------------------------------

--
-- Table structure for table `Fuel_Ledger`
--

CREATE TABLE IF NOT EXISTS `Fuel_Ledger` (
  `fuel_ledger_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `fuel_supplier_id` int(11) NOT NULL,
  `details` varchar(100) NOT NULL,
  `voucher_type` varchar(60) NOT NULL,
  `voucher_number` varchar(20) NOT NULL,
  `vehicle_number` varchar(20) NOT NULL,
  `amount` double NOT NULL,
  `transaction_type` varchar(10) NOT NULL,
  PRIMARY KEY (`fuel_ledger_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to Store Fuel Ledger Information' AUTO_INCREMENT=4 ;

--
-- Dumping data for table `Fuel_Ledger`
--

INSERT INTO `Fuel_Ledger` (`fuel_ledger_id`, `date`, `fuel_supplier_id`, `details`, `voucher_type`, `voucher_number`, `vehicle_number`, `amount`, `transaction_type`) VALUES
(3, '2013-11-20 00:00:00', 1, 'Fuel Sales', 'CASH_SALE_INVOICE', '0', '', 0, 'DEBIT');

-- --------------------------------------------------------

--
-- Table structure for table `Fuel_Suppliers`
--

CREATE TABLE IF NOT EXISTS `Fuel_Suppliers` (
  `fuel_supplier_id` int(11) NOT NULL AUTO_INCREMENT,
  `fuel_supplier_name` varchar(60) NOT NULL,
  `contact_name` varchar(60) NOT NULL,
  `contact_phone` varchar(20) NOT NULL,
  `address_city` varchar(60) NOT NULL,
  `fuel_supplier_balance` double NOT NULL DEFAULT '0',
  PRIMARY KEY (`fuel_supplier_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to Store Fuel Supplier Information' AUTO_INCREMENT=3 ;

--
-- Dumping data for table `Fuel_Suppliers`
--

INSERT INTO `Fuel_Suppliers` (`fuel_supplier_id`, `fuel_supplier_name`, `contact_name`, `contact_phone`, `address_city`, `fuel_supplier_balance`) VALUES
(1, 'Total Kashari', 'Silvia', '048521708', 'Mbarara', 0),
(2, 'Total - Bushenyi', 'Jackie', '0751235555', 'BUSHENYI', 0);

-- --------------------------------------------------------

--
-- Table structure for table `Hauliers`
--

CREATE TABLE IF NOT EXISTS `Hauliers` (
  `haulier_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(60) NOT NULL,
  `po_box` varchar(30) NOT NULL,
  `street_number` varchar(30) NOT NULL,
  `street_name` varchar(100) NOT NULL,
  `area` varchar(100) NOT NULL,
  `city` varchar(100) NOT NULL,
  `country` varchar(100) NOT NULL,
  `last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`haulier_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store haulier information' AUTO_INCREMENT=3 ;

--
-- Dumping data for table `Hauliers`
--

INSERT INTO `Hauliers` (`haulier_id`, `name`, `po_box`, `street_number`, `street_name`, `area`, `city`, `country`, `last_modified`) VALUES
(1, 'Tibbett and Britten International', '7062', '', '', '', '', '', '0000-00-00 00:00:00');

-- --------------------------------------------------------

--
-- Table structure for table `Payment_Voucher`
--

CREATE TABLE IF NOT EXISTS `Payment_Voucher` (
  `payment_voucher_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `payment_voucher_number` int(11) NOT NULL,
  `payment_voucher_date` datetime NOT NULL,
  `payment_voucher_to` varchar(60) NOT NULL,
  `account_number` int(11) NOT NULL,
  `authorized_by` varchar(60) NOT NULL,
  `prepared_by` varchar(60) NOT NULL,
  `received_by` varchar(60) NOT NULL,
  `total` double NOT NULL,
  PRIMARY KEY (`payment_voucher_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store Payment Voucher information' AUTO_INCREMENT=3 ;

--
-- Dumping data for table `Payment_Voucher`
--

INSERT INTO `Payment_Voucher` (`payment_voucher_id`, `payment_voucher_number`, `payment_voucher_date`, `payment_voucher_to`, `account_number`, `authorized_by`, `prepared_by`, `received_by`, `total`) VALUES
(2, 5399, '2013-05-09 00:00:00', '', 111335, 'Josephat', 'Patience', '', 259000);

-- --------------------------------------------------------

--
-- Table structure for table `Payment_Voucher_Entry`
--

CREATE TABLE IF NOT EXISTS `Payment_Voucher_Entry` (
  `payment_voucher_entry_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `payment_voucher_id` bigint(20) NOT NULL,
  `particulars` varchar(100) NOT NULL,
  `amount` double NOT NULL,
  PRIMARY KEY (`payment_voucher_entry_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store Payment Voucher Entry Information' AUTO_INCREMENT=7 ;

--
-- Dumping data for table `Payment_Voucher_Entry`
--

INSERT INTO `Payment_Voucher_Entry` (`payment_voucher_entry_id`, `payment_voucher_id`, `particulars`, `amount`) VALUES
(5, 2, 'Steering rods repair', 20000),
(4, 2, 'Unleaded Oil Service', 229000),
(6, 2, 'Wire', 10000);

-- --------------------------------------------------------

--
-- Table structure for table `Products`
--

CREATE TABLE IF NOT EXISTS `Products` (
  `product_number` int(11) NOT NULL,
  `product_description` varchar(70) NOT NULL,
  `short_description` varchar(10) NOT NULL,
  `product_abv_percent` double NOT NULL,
  `uom` varchar(20) NOT NULL,
  PRIMARY KEY (`product_number`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Table to store product information';

--
-- Dumping data for table `Products`
--

INSERT INTO `Products` (`product_number`, `product_description`, `short_description`, `product_abv_percent`, `uom`) VALUES
(660345, 'Bell Lager 500ml RET 25X01 LONGNECK', 'Bell', 4, 'CAS'),
(661314, 'Tusker In Bottle 500ml RET 25X01 LOCAL', 'Tusker', 4.2, 'CAS'),
(642754, 'Tusker Malt In 330ml RET 25X01', 'TML', 5, 'CAS'),
(350605, 'Shell - Large Plastic', 'Shell', 0, 'EA'),
(616874, 'Senator Lager 500ml RET 25X01', 'Senator', 6, 'CAS');

-- --------------------------------------------------------

--
-- Table structure for table `Sales_Ledger`
--

CREATE TABLE IF NOT EXISTS `Sales_Ledger` (
  `sales_ledger_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `branch_id` int(11) NOT NULL,
  `sales_territory` varchar(60) NOT NULL,
  `sales_vehicle` varchar(20) NOT NULL,
  `sales_person` varchar(60) NOT NULL,
  `sales_ledger_date` datetime NOT NULL,
  `total_product_sales` double NOT NULL,
  `cash_on_hand` double NOT NULL,
  `total_expenses` double NOT NULL,
  `total_cheques` double NOT NULL,
  PRIMARY KEY (`sales_ledger_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store sales ledger information' AUTO_INCREMENT=18 ;

--
-- Dumping data for table `Sales_Ledger`
--

INSERT INTO `Sales_Ledger` (`sales_ledger_id`, `branch_id`, `sales_territory`, `sales_vehicle`, `sales_person`, `sales_ledger_date`, `total_product_sales`, `cash_on_hand`, `total_expenses`, `total_cheques`) VALUES
(5, 1, 'Nyeihanga', 'UAJ481M', 'Felix', '2013-05-28 00:00:00', 7340500, 11301100, 43800, 697100),
(7, 0, 'Town', 'UAN202W', 'Felix', '2013-05-07 00:00:00', 9440500, 10301100, 253800, 1697100),
(8, 4, 'Town', 'UAN202W', 'Julius', '2013-07-11 00:00:00', 20000, 20000, 2000, 0),
(9, 1, 'Kyenshama', '', 'Julius', '0000-00-00 00:00:00', 0, 0, 0, 0),
(10, 1, 'Kyenshama', '', 'Julius', '0000-00-00 00:00:00', 0, 0, 0, 0),
(11, 1, 'Jeza', 'UAB 001A', 'Julius', '2014-03-17 00:00:00', 300, 0, 7000, 0),
(12, 1, 'Kyenshama', 'UAB 001B', 'Felix', '2014-03-18 00:00:00', 5442000, 0, 0, 0),
(13, 1, 'Kyenshama', 'UAB 001S', 'Julius', '2014-03-19 00:00:00', 15290677.8, 0, 25000, 500000),
(14, 1, 'Kyenshama', '', 'Julius', '0000-00-00 00:00:00', 15290677.8, 0, 0, 0),
(17, 1, 'Kyenshama', 'UAB 001A', 'Julius', '2014-04-01 00:00:00', 16911016.8, 0, 0, 0);

-- --------------------------------------------------------

--
-- Table structure for table `Sales_Ledger_Cheques`
--

CREATE TABLE IF NOT EXISTS `Sales_Ledger_Cheques` (
  `sales_ledger_cheques_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `sales_ledger_id` bigint(20) NOT NULL,
  `cheque_from` varchar(60) NOT NULL,
  `cheque_amount` double NOT NULL,
  PRIMARY KEY (`sales_ledger_cheques_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store Sales Ledger Cheques Information' AUTO_INCREMENT=5 ;

--
-- Dumping data for table `Sales_Ledger_Cheques`
--

INSERT INTO `Sales_Ledger_Cheques` (`sales_ledger_cheques_id`, `sales_ledger_id`, `cheque_from`, `cheque_amount`) VALUES
(3, 8, '', 0),
(2, 7, 'Agip', 1697100),
(4, 13, 'Sample Chaque Name', 500000);

-- --------------------------------------------------------

--
-- Table structure for table `Sales_Ledger_Expenses`
--

CREATE TABLE IF NOT EXISTS `Sales_Ledger_Expenses` (
  `sales_ledger_expenses_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `sales_ledger_id` bigint(20) NOT NULL,
  `expense_type` varchar(60) NOT NULL,
  `expense_amount` double NOT NULL,
  PRIMARY KEY (`sales_ledger_expenses_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store Slaes Ledger Expenses' AUTO_INCREMENT=15 ;

--
-- Dumping data for table `Sales_Ledger_Expenses`
--

INSERT INTO `Sales_Ledger_Expenses` (`sales_ledger_expenses_id`, `sales_ledger_id`, `expense_type`, `expense_amount`) VALUES
(10, 8, 'Lunch', 2000),
(3, 5, 'Parking Fee', 1800),
(7, 7, 'Lunch', 121000),
(8, 7, 'Allowance', 121000),
(9, 7, 'Parking Fee', 11800),
(11, 11, 'Lunch', 5000),
(12, 11, 'Parking Fee', 2000),
(13, 13, 'Lunch', 5000),
(14, 13, 'Allowance', 20000);

-- --------------------------------------------------------

--
-- Table structure for table `Sales_Ledger_Product_Sales`
--

CREATE TABLE IF NOT EXISTS `Sales_Ledger_Product_Sales` (
  `sales_ledger_product_sales_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `sales_ledger_id` bigint(20) NOT NULL,
  `product_number` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `amount` double NOT NULL,
  PRIMARY KEY (`sales_ledger_product_sales_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store Sales Ledger Product Sales Information' AUTO_INCREMENT=73 ;

--
-- Dumping data for table `Sales_Ledger_Product_Sales`
--

INSERT INTO `Sales_Ledger_Product_Sales` (`sales_ledger_product_sales_id`, `sales_ledger_id`, `product_number`, `quantity`, `amount`) VALUES
(30, 9, 616874, 0, 0),
(29, 9, 350605, 0, 0),
(28, 9, 642754, 0, 0),
(27, 9, 661314, 0, 0),
(26, 9, 660345, 0, 0),
(25, 8, 616874, 0, 0),
(24, 8, 350605, 0, 0),
(23, 8, 642754, 0, 0),
(22, 8, 661314, 0, 0),
(21, 8, 660345, 1, 20000),
(35, 10, 616874, 0, 0),
(34, 10, 350605, 0, 0),
(33, 10, 642754, 0, 0),
(32, 10, 661314, 0, 0),
(31, 10, 660345, 0, 0),
(16, 7, 660345, 80, 4634000),
(17, 7, 661314, 73, 4492000),
(18, 7, 642754, 4, 314500),
(19, 7, 350605, 0, 0),
(20, 7, 616874, 0, 0),
(36, 11, 660345, 50, 120),
(37, 11, 661314, 25, 55),
(38, 11, 642754, 10, 15),
(39, 11, 350605, 10, 10),
(40, 11, 616874, 50, 100),
(41, 12, 660345, 50, 1814000),
(42, 12, 661314, 100, 3628000),
(43, 12, 642754, 0, 0),
(44, 12, 350605, 0, 0),
(45, 12, 616874, 0, 0),
(46, 13, 660345, 10, 782203.3),
(47, 13, 661314, 100, 11593220),
(48, 13, 642754, 50, 2915254.5),
(49, 13, 350605, 0, 0),
(50, 13, 616874, 0, 0),
(51, 14, 660345, 10, 782203.3),
(52, 14, 661314, 100, 11593220),
(53, 14, 642754, 50, 2915254.5),
(54, 14, 350605, 0, 0),
(55, 14, 616874, 0, 0),
(72, 17, 2, 0, 0),
(71, 17, 1, 0, 0),
(70, 17, 650274, 10, 1620339),
(69, 17, 646700, 10, 2915254.5),
(68, 17, 603588, 100, 11593220),
(67, 17, 603643, 10, 782203.3);

-- --------------------------------------------------------

--
-- Table structure for table `Sales_Persons`
--

CREATE TABLE IF NOT EXISTS `Sales_Persons` (
  `sales_persons_id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(20) NOT NULL,
  `last_name` varchar(20) NOT NULL,
  PRIMARY KEY (`sales_persons_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store sales person information' AUTO_INCREMENT=6 ;

--
-- Dumping data for table `Sales_Persons`
--

INSERT INTO `Sales_Persons` (`sales_persons_id`, `first_name`, `last_name`) VALUES
(1, 'Julius', ''),
(2, 'Felix', ''),
(3, 'Alex', ''),
(4, 'Firestone', '');

-- --------------------------------------------------------

--
-- Table structure for table `Sales_Receipt`
--

CREATE TABLE IF NOT EXISTS `Sales_Receipt` (
  `sales_receipt_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `client_id` int(11) NOT NULL,
  `client_business_name` varchar(60) NOT NULL,
  `sales_person` varchar(60) NOT NULL,
  `sales_receipt_number` bigint(20) NOT NULL,
  `total` double NOT NULL,
  PRIMARY KEY (`sales_receipt_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store sales receipts' AUTO_INCREMENT=10 ;

--
-- Dumping data for table `Sales_Receipt`
--

INSERT INTO `Sales_Receipt` (`sales_receipt_id`, `date`, `client_id`, `client_business_name`, `sales_person`, `sales_receipt_number`, `total`) VALUES
(9, '2013-04-29 00:00:00', 1, 'Gabiro', 'Alex', 123065, 1575000);

-- --------------------------------------------------------

--
-- Table structure for table `Sales_Receipt_Entry`
--

CREATE TABLE IF NOT EXISTS `Sales_Receipt_Entry` (
  `sales_receipt_entry_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `sales_receipt_id` bigint(20) NOT NULL,
  `product_description` varchar(70) NOT NULL,
  `product_number` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `rate` double NOT NULL,
  `amount` double NOT NULL,
  PRIMARY KEY (`sales_receipt_entry_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store sales receipt entries' AUTO_INCREMENT=87 ;

--
-- Dumping data for table `Sales_Receipt_Entry`
--

INSERT INTO `Sales_Receipt_Entry` (`sales_receipt_entry_id`, `sales_receipt_id`, `product_description`, `product_number`, `quantity`, `rate`, `amount`) VALUES
(83, 9, 'Senator Lager 500ml RET 25X01', 616874, 0, 0, 0),
(82, 9, 'Shell - Large Plastic', 350605, 0, 0, 0),
(81, 9, 'Tusker Malt In 330ml RET 25X01', 642754, 30, 20000, 600000),
(80, 9, 'Tusker In Bottle 500ml RET 25X01 LOCAL', 661314, 20, 30000, 600000),
(79, 9, 'Bell Lager 500ml RET 25X01 LONGNECK', 660345, 15, 25000, 375000),
(86, 9, 'Shells', 0, 0, 0, 0),
(85, 9, 'Bottles', 0, 0, 0, 0),
(84, 9, 'Empties', 0, 0, 0, 0);

-- --------------------------------------------------------

--
-- Table structure for table `Sales_Territories`
--

CREATE TABLE IF NOT EXISTS `Sales_Territories` (
  `territory_id` int(11) NOT NULL AUTO_INCREMENT,
  `branch_id` int(3) NOT NULL,
  `territory_name` varchar(60) NOT NULL,
  PRIMARY KEY (`territory_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store sales territories' AUTO_INCREMENT=16 ;

--
-- Dumping data for table `Sales_Territories`
--

INSERT INTO `Sales_Territories` (`territory_id`, `branch_id`, `territory_name`) VALUES
(1, 2, 'Kyenshama'),
(2, 1, 'Town'),
(3, 0, 'Office'),
(5, 0, 'Sanga'),
(6, 0, 'Kikagati'),
(7, 0, 'Rubindi'),
(8, 0, 'Kikagati II'),
(9, 0, 'Rwenturagasa'),
(10, 0, 'Kamubaizi'),
(11, 0, 'Bugango'),
(12, 0, 'Ntantamuki'),
(13, 1, 'Nyeihanga'),
(15, 1, 'Jeza');

-- --------------------------------------------------------

--
-- Table structure for table `Spirits_Products`
--

CREATE TABLE IF NOT EXISTS `Spirits_Products` (
  `product_number` int(11) NOT NULL,
  `product_description` varchar(70) NOT NULL,
  `short_product_description` varchar(20) NOT NULL,
  `product_abv_percent` double NOT NULL,
  `uom` varchar(20) NOT NULL,
  PRIMARY KEY (`product_number`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Table to store product information';

--
-- Dumping data for table `Spirits_Products`
--

INSERT INTO `Spirits_Products` (`product_number`, `product_description`, `short_product_description`, `product_abv_percent`, `uom`) VALUES
(603643, 'Uganda Waragi 200ml 24x01', 'U.W 200mls', 40, 'CAS'),
(603588, 'UGANDA WARAGI PE 200ml 48x01', 'U.W PE 200mls', 40, 'CAS'),
(646700, 'Uganda Waragi 10cl 48x01 PET', 'U.W 10Cl', 40, 'CAS'),
(650274, 'Gilbeys Gin 35cl 24x01 EL', 'Gilbeys 35cl', 40, 'CAS'),
(1, 'Gilbeys Gin 350 mls', 'Gilbeys 350ml', 40, 'BOT'),
(2, 'Gilbeys Gin 200 mls', 'Gilbeys 200ml', 40, 'BOT');

-- --------------------------------------------------------

--
-- Table structure for table `Spirits_Receipt`
--

CREATE TABLE IF NOT EXISTS `Spirits_Receipt` (
  `spirits_receipt_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `client_id` int(11) NOT NULL,
  `client` varchar(60) NOT NULL,
  `sales_person` varchar(60) NOT NULL,
  `spirits_receipt_number` bigint(20) NOT NULL,
  `total` double NOT NULL,
  PRIMARY KEY (`spirits_receipt_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store spirits receipts' AUTO_INCREMENT=3 ;

--
-- Dumping data for table `Spirits_Receipt`
--

INSERT INTO `Spirits_Receipt` (`spirits_receipt_id`, `date`, `client_id`, `client`, `sales_person`, `spirits_receipt_number`, `total`) VALUES
(2, '0000-00-00 00:00:00', 0, '', 'Julius', 0, 0);

-- --------------------------------------------------------

--
-- Table structure for table `Spirits_Receipt_Entry`
--

CREATE TABLE IF NOT EXISTS `Spirits_Receipt_Entry` (
  `spirits_receipt_entry_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `spirits_receipt_id` bigint(20) NOT NULL,
  `product_description` varchar(70) NOT NULL,
  `product_number` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `rate` double NOT NULL,
  `amount` double NOT NULL,
  PRIMARY KEY (`spirits_receipt_entry_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store spirits receipt entries' AUTO_INCREMENT=13 ;

--
-- Dumping data for table `Spirits_Receipt_Entry`
--

INSERT INTO `Spirits_Receipt_Entry` (`spirits_receipt_entry_id`, `spirits_receipt_id`, `product_description`, `product_number`, `quantity`, `rate`, `amount`) VALUES
(11, 2, 'Gilbeys Gin 350 mls', 1, 0, 0, 0),
(10, 2, 'Gilbeys Gin 35cl 24x01 EL', 650274, 0, 0, 0),
(9, 2, 'Uganda Waragi 10cl 48x01 PET', 646700, 0, 0, 0),
(8, 2, 'UGANDA WARAGI PE 200ml 48x01', 603588, 0, 0, 0),
(7, 2, 'Uganda Waragi 200ml 24x01', 603643, 0, 0, 0),
(12, 2, 'Gilbeys Gin 200 mls', 2, 0, 0, 0);

-- --------------------------------------------------------

--
-- Table structure for table `Spirits_Tax_Invoice_From_UBL`
--

CREATE TABLE IF NOT EXISTS `Spirits_Tax_Invoice_From_UBL` (
  `spirits_tax_invoice_from_ubl_id` int(11) NOT NULL AUTO_INCREMENT,
  `document_number` bigint(20) NOT NULL,
  `our_reference` int(11) NOT NULL,
  `order_date` datetime NOT NULL,
  `document_date` datetime NOT NULL,
  `purchase_order_number` varchar(30) NOT NULL,
  `dispatch_date` datetime NOT NULL,
  `invoice_address` varchar(160) NOT NULL,
  `account_number` varchar(12) NOT NULL,
  `payment_due_date` datetime NOT NULL,
  `consignment_note_number` int(12) NOT NULL,
  `delivery_note_number` int(12) NOT NULL,
  `order_type` varchar(20) NOT NULL,
  `tax_point_date` datetime NOT NULL,
  `arrival_date` datetime NOT NULL,
  `delivery_address` varchar(160) NOT NULL,
  `customer_vat_number` varchar(12) NOT NULL,
  `supplier_tin_number` varchar(20) NOT NULL,
  `supplier_vat_number` varchar(12) NOT NULL,
  `payment_terms` varchar(160) NOT NULL,
  `vehicle_number` varchar(16) NOT NULL,
  `haulier` varchar(160) NOT NULL,
  `order_created_by` varchar(30) NOT NULL,
  `dispatched_from` varchar(160) NOT NULL,
  `delivery_terms` varchar(30) NOT NULL,
  `line_values_calculated_total` double NOT NULL DEFAULT '0',
  `line_values_entered_total` double NOT NULL,
  `taxable_value` double NOT NULL,
  `vat_rate` double NOT NULL,
  `tax_amount` double NOT NULL,
  `currency` varchar(3) NOT NULL,
  `total_due` double NOT NULL,
  `number_of_line_entries` int(11) NOT NULL,
  PRIMARY KEY (`spirits_tax_invoice_from_ubl_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table to store spirits tax invoice from UBL' AUTO_INCR

[diafol]

You could have posted a link to the sql file! heh heh. Ok will have a look later on.

//EDIT

 $sql2 = "SELECT *  
        FROM Spirits_products 
        WHERE product_number='$product_no'";
        $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>";
        // Calculate the vat rate for each product sold

Spirits_products has the fields:

product_number   
product_description
short_product_description
product_abv_percent         
uom

So why 'quantity' and 'amount'?

[diafol]

OK had a look, but your tables are a bit confusing, they seem to be non-normalized.

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 >= '$start_date'
    AND 
    SL.sales_ledger_date <= '$end_date'  
ORDER BY 
    SL.sales_ledger_date DESC, SL.sales_person

If you run that in a mysql GUI or phpmyadmin (hardcode the dates!), you should see the output you need - but without the totals - you can code that by looking for a change in the ledgerDate or salesPerson as you loop - start/stop totalling on change.