How to count Total, Tax using data from database

Question

so im kinda new in codeigniter and web developing in general but is it possible to count Sub Total and Tax, automatically input that value into somekind of input box in codeigniter?

for example :

i have model that join some column and show the data into a table

table model.php

public function getPurchaseOrder()
    {
        $query =$this->db->query('SELECT dkmno, kodeprod,kodebarang_op,qty_op,unit_op,price,catatan FROM order_product JOIN barang_dkm ON order_product.kodebarang_op = barang_dkm.kodebarang');
        return $query->result_array();
    }

table view.php

<table class="table table-fixed table-bordered table-hover" style="width:100%;" id="tebal">
                                <thead>
                                    <tr>
                                        <th scope="col">DKM No </th>
                                        <th scope="col">Kode Produksi </th>
                                        <th scope="col">Kode Barang </th>
                                        <th scope="col">Deskripsi </th>
                                        <th scope="col">Jumlah/Quantity </th>
                                        <th scope="col">Unit </th>
                                        <th scope="col">Unit Price </th>
                                        <th scope="col">Sub Total </th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php foreach ($dataTabel as $kiki) : ?>
                                        <tr class="table-row">
                                            <td><?php echo $kiki["dkmno"]; ?></td>
                                            <td><?php echo $kiki["kodeprod"]; ?></td>
                                            <td><?php echo $kiki["kodebarang_op"]; ?></td>
                                            <td><?php echo $kiki["catatan"]; ?></td>
                                            <td><?php echo $kiki["qty_op"]; ?></td>
                                            <td><?php echo $kiki["unit_op"]; ?></td>
                                            <td><?php echo $kiki["price"]; ?></td>
                                            <td class="calc"><?php $a=$kiki["qty_op"]*$kiki["price"]; echo $a ?></td>
                                        </tr>
                                    <?php endforeach;?>
                                </tbody>
                            </table>

and i want to count sub total from the table above and show the answer in a textbox or inputbox
subtotal, discount, and tax code if you want to know

<div class="row mt-5">
                    <div class="col-sm" id="kontol">
                        <div class="form-group">
                            <div class="row">
                                <div class="col-sm-3">
                                    <label for="SubTotal" class="control-label">Sub Total : </label>
                                </div>
                                <div class="col-sm-9">
                                    <input type="number" id="subTotal" name="SubTotal" value="" style="text-align: right;" class="form-control" disabled>
                                </div>
                            </div>
                        </div>
                        <div class="form-group">
                            <div class="row">
                                <div class="col-lg-3">
                                    <label for="Discount" class="label-control" id="Discount">Discount : </label>
                                </div>
                                <div class="col-lg-3">
                                    <div class="input-group">
                                        <input type="number" name="Discount" class="form-control" id="inputDiscount" value="" min="0" max="100" onchange="calculatedSubTotal()"><span class="input-group-addon">%</span>
                                    </div>
                                </div>
                                <div class="col-lg-6">
                                    <input type="number" name="SubTotalDiscount" value="" style="text-align: right;"  class="form-control" id="subTotalDiscount" disabled>
                                </div>
                            </div>
                        </div>
                        <div class="form-group">
                            <div class="row">
                                <div class="col-lg-3">
                                    <label for="Tax" class="label-control">Tax : </label>
                                </div>
                                <div class="col-lg-3">
                                    <div class="input-group">
                                        <input type="number" name="Tax" value="" min="0" max="100" class="form-control" id="inputTax" onchange="calculatedSubTotal()"><span class="input-group-addon">%</span>
                                    </div>
                                </div>
                                <div class="col-lg-6">
                                    <input type="number" name="SubTotalTax" style="text-align: right;" class="form-control" id="subTotalTax" value="" disabled>
                                </div>
                            </div>
                        </div>

i have try to look at people who ask the same question but i still need somekind a detail how the model.php, controller.php, view.php, script.js work, if you need more details here's the link the ui that i already made : https://i.stack.imgur.com/OCAXK.png

you can use javascript to calculate the total or you want from query itself — Jacky, Jul 29 '19 at 07:34

score 0 · Answer 1 · answered Jul 29 '19 at 04:02

0

Try this:

$this->db->select_sum('your_column_to_calculate');
$this->db->select('your_column_to_calculate');
$this->db->from('price');
$this->db->get();

or like this:

query = $this->db->query('SELECT sum(your_column_to_calculate) FROM price');

Hope it helps

answered Jul 29 '19 at 04:02

Serghei Leonenco

3,478
2
8
16

can show me how to sum a join table? like the code that i just add – Frsal Jul 29 '19 at 06:44

score 0 · Answer 2 · answered Jul 29 '19 at 07:39

0

This is javascript code,This calculate the only the calc row and show the result in subtotal text box and it will load automatically:

    <script>
        $(document).ready(function(){               

                    var hh2 = 0;              
                    $('#tebal > tbody  > tr').each(function() {
              var price = $(this).find('.calc').val();
                hh2 += parseFloat(price);                   
                    });
$('#SubTotal').val(hh2);
            });
</script>

answered Jul 29 '19 at 07:39

Jacky

771
3
20

if i input the value of tax ```10%``` and it will output the value of ```subtotal*tax``` – Frsal Jul 29 '19 at 08:44
did you got it or not – Jacky Jul 29 '19 at 09:55

score 0 · Answer 3 · answered Jul 29 '19 at 07:46

you can count all of the sum with php itself

set variable $subtotal with zero, and add $subtotal in every loop

<?php 
$subtotal = 0;
foreach ($dataTabel as $kiki) : ?>
<tr class="table-row">
    ....
</tr> <?php $subtotal = $subtotal+$kiki["qty_op"]*$kiki["price"]; endforeach;?>

Hope you get it.

Hasta Dhana · Accepted Answer · 2019-07-30T08:59:54.337

0

You could get the subtotal using CodeIgniter, but you have to use javascript to calculate the tax because it involves user interaction.

For example you have the subtotal input like this :

    <div class="form-group">
        <label class="col-sm-3 control-label" for="SubTotal">Sub Total</label>
        <div class="col-sm-9">
            <input type="number" name="SubTotal" id="subTotal" class="form-control" value="" title="Sub Total" disabled><br/>
        </div>
    </div>
    <div class="form-group">
        <label class="col-sm-3 control-label" for="Discount">Discount</label>
        <div class="col-sm-9">
            <div class="col-sm-6">
                <div class="input-group input-group-md">
                    <input type="number" name="Discount" id="inputDiscount" class="form-control" value="" title="Discount" min="0" max="100" onchange="calculateSubTotal()"><span class="input-group-addon">%</span>
                </div>
            </div>
            <div class="col-sm-6">
                <input type="number" name="SubTotalDiscount" id="subTotalDiscount" class="form-control" value="" title="Subtotal After Discount" disabled><br/>
            </div>
        </div>
    </div>
    <div class="form-group">
        <label class="col-sm-3 control-label" for="Tax">Tax</label>
        <div class="col-sm-9">
            <div class="col-sm-6">
                <div class="input-group input-group-md">
                    <input type="number" name="Tax" id="inputTax" class="form-control" value="" title="Tax" min="0" max="100" onchange="calculateSubTotal()"><span class="input-group-addon">%</span>
                </div>
            </div>
            <div class="col-sm-6">
                <input type="number" name="SubTotalTax" id="subTotalTax" class="form-control" value="" title="Subtotal After Tax" disabled><br/>
            </div>
        </div>
    </div>

Then you could set the subtotal value and also count the subtotals value after discount and after tax like this :

<script>
    jQuery(function () {
        let sum = 0.0;
        jQuery('.calc').each(function() {
            sum += parseInt(jQuery(this).text());
        });
        jQuery("#subTotal").val(sum);
    });
    function calculateSubTotal() {
        let subtotal = jQuery("#subTotal").val();
        jQuery("#subTotalDiscount").val(subtotal - (Math.round((jQuery("#inputDiscount").val() / 100) * subtotal)));
        let subtotal_discount = jQuery("#subTotalDiscount").val();
        jQuery("#subTotalTax").val(+subtotal_discount + (Math.round((jQuery("#inputTax").val() / 100) * subtotal_discount)));
    }
</script>

That will set all of the subtotals using javascript instead.

edited Jul 30 '19 at 08:59

answered Jul 29 '19 at 08:27

Hasta Dhana

4,699
7
17
26

have you assign the `id="subTotal"` to the *Sub Total* input? also do you get any console message? also, could you share the subtotal, discount & tax input code? – Hasta Dhana Jul 30 '19 at 03:08
yeah i did assign my subtotal id to that, yeah sure i just edited my question – Frsal Jul 30 '19 at 04:33
I see you've renamed the `calculateSubTotal()` to `calculatedSubTotal()` on your code, could also you show the javascript code? – Hasta Dhana Jul 30 '19 at 04:45
the javascript i try is the one that u write – Frsal Jul 30 '19 at 05:06
there is no error, only there is no value that shows up – Frsal Jul 30 '19 at 06:32
So the *Sub Total* (first input) value is empty? – Hasta Dhana Jul 30 '19 at 06:34
yeah the ```subTotal``` input is empty – Frsal Jul 30 '19 at 06:42
Try this on console : `let y = 0.0;$('.calc').each(function(){y += parseInt($(this).text())});console.log(y);`, what does it return? – Hasta Dhana Jul 30 '19 at 06:54
can you explain to me how to do that, i still not really understand javascript – Frsal Jul 30 '19 at 07:12
Open Dev Tools by pressing : `Ctrl`+`Shift`+`i` OR `F12`, then paste those codes on the *Console* tab – Hasta Dhana Jul 30 '19 at 07:55
```Uncaught TypeError: Cannot read property 'each' of null at :1:23``` it return that error – Frsal Jul 30 '19 at 08:00
please refresh the page and try replace `$` sign with `jQuery` on the console : `let y = 0.0;jQuery('.calc').each(function(){y += parseInt(jQuery(this).text())});console.log(y);` – Hasta Dhana Jul 30 '19 at 08:18
yeah it show the sum value of subTotal from the table – Frsal Jul 30 '19 at 08:38
if i use ```$(function () { let sum = 0.0; $('.calc').each(function() { sum += parseInt($(this).text()); }); $("#subTotal").val(sum); });``` in console it return this ```k.fn.init [document]``` and the value appear in the input box – Frsal Jul 30 '19 at 08:43
its works! thank you, is it because i havent install jquery? – Frsal Jul 30 '19 at 09:59
you're welcome! maybe it's because your library using `jQuery.noConflict()` which in order to use the jquery, you have to replace `$` with `jQuery`, [more](https://stackoverflow.com/a/6746395/8566549) – Hasta Dhana Jul 30 '19 at 10:08

How to count Total, Tax using data from database

4 Answers4