Auto populate discount_amount from other table:

J P · May 30, 2024

* I dont know how to insert new query to call discount_amount from po_list table.

PHP:

<?php
if(isset($_GET['id']) && $_GET['id'] > 0){
    $qry = $conn->query("SELECT * from `shipment2_list` where id = '{$_GET['id']}' ");
    if($qry->num_rows > 0){
        foreach($qry->fetch_assoc() as $k => $v){
            $$k=$v;
        }
    }
}
?>
<style>
    span.select2-selection.select2-selection--single {
        border-radius: 0;
        padding: 0.25rem 0.5rem;
        padding-top: 0.25rem;
        padding-right: 0.5rem;
        padding-bottom: 0.25rem;
        padding-left: 0.5rem;
        height: auto;
    }
    /* Chrome, Safari, Edge, Opera */
        input::-webkit-outer-spin-button,
        input::-webkit-inner-spin-button {
        -webkit-appearance: none;
        margin: 0;
        }

        /* Firefox */
        input[type=number] {
        -moz-appearance: textfield;
        }
        [name="tax_percentage"],[name="discount_percentage"]{
            width:5vw;
        }

    

</style>
<div class="card card-outline card-info">
    <div class="card-header">
        <h3 class="card-title"><?php echo isset($id) ? "Update Shipment2 Details": "Shipment" ?> </h3>
    </div>
    <div class="card-body">
        <form action="" id="po-form">
            <input type="hidden" name ="id" value="<?php echo isset($id) ? $id : '' ?>">
            <div class="row">
                <div class="col-md-6 form-group">
                <label for="supplier_id">Supplier</label>
                <select name="supplier_id" id="supplier_id" class="custom-select custom-select-sm rounded-0 select2">
                        <option value="" disabled <?php echo !isset($supplier_id) ? "selected" :'' ?>></option>
                        <?php
                            $supplier_qry = $conn->query("SELECT *, discount FROM `supplier_list` order by `name`,`discount` asc");
                            while($row = $supplier_qry->fetch_assoc()):
                        ?>
                        <option value="<?php echo $row['id'] ?>" <?php echo isset($supplier_id) && $supplier_id == $row['id'] ? 'selected' : '' ?> <?php echo $row['status'] == 0? 'disabled' : '' ?>><?php echo $row['name'] ?></option>
                        <?php endwhile; ?>
                    </select>
                </div>
                <div class="col-md-6 form-group">
                    <label for="po_no">PO # <span class="po_err_msg text-danger"></span></label>
                    <input type="text" class="form-control form-control-sm rounded-0" id="po_no" name="po_no" value="<?php echo isset($po_no) ? $po_no : '' ?>">
                    <small><i>Please input PO Number.</i></small>

                </div>
            </div>
            <div class="row">
                <div class="col-md-12">
                    <table class="table table-striped table-bordered" id="item-list">
                        <colgroup>
                            <col width="5%">
                            <col width="5%">
                            <col width="10%">
                            <col width="15%">
                            <col width="10%">
                            <col width="20%">
                            <col width="15%">
                            <col width="15%">
                        </colgroup>
                        <thead>
                            <tr class="bg-navy disabled">
                                <th class="px-1 py-1 text-center"></th>
                                <th class="px-1 py-1 text-center">Qty</th>
                                <th class="px-1 py-1 text-center">Unit</th>
                                <th class="px-1 py-1 text-center">Specs</th>
                                <th class="px-1 py-1 text-center">Item</th>
                                <th class="px-1 py-1 text-center">Size</th>
                                <th class="px-1 py-1 text-center">Price</th>
                                <th class="px-1 py-1 text-center">Total</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                                    setlocale(LC_MONETARY, 'en_US.UTF-8');   
                            if(isset($id)):
                            $shipment_items_qry = $conn->query("SELECT o.*,i.name, i.size FROM `shipment2_items` o inner join item_list i on o.item_id = i.id where o.`po_id` = '$id' ");
                            echo $conn->error;
                            while($row = $shipment_items_qry->fetch_assoc()):
                            ?>
                            <tr class="po-item" data-id="">
                                <td class="align-middle p-1 text-center">
                                    <button class="btn btn-sm btn-danger py-0" type="button" onclick="rem_item($(this))"><i class="fa fa-times"></i></button>
                                </td>
                                <td class="align-middle p-0 text-center">
                                    <input type="number" class="text-center w-100 border-0" step="any" name="qty[]" value="<?php echo $row['quantity'] ?>"required/>
                                </td>
                                <td class="align-middle p-1">
                                    <input type="text" class="text-center w-100 border-0" name="unit[]" value="<?php echo $row['unit'] ?>"required/>
                                </td>
                                <td class="align-middle p-1">
                                    <input type="text" class="text-center w-100 border-0" name="specs[]" value="<?php echo $row['specs'] ?>" required/>
                                </td>
                                <td class="align-middle p-1">
                                    <input type="hidden" name="item_id[]" value="<?php echo $row['item_id'] ?>">
                                    <input type="text" class="text-center w-100 border-0 item_id" value="<?php echo $row['name'] ?>" required/></td>
                                <td class="align-middle text-center p-1 item-size"><?php echo $row['size'] ?></td>

                                <td class="align-middle p-1">
                                    <input type="number" step="any" class="text-center w-100 border-0" name="unit_price[]"  value="<?php echo ($row['unit_price']) ?>" required/></td>
                                <td class="align-middle p-1 text-left total-price"><?php echo number_format($row['quantity'] * $row['unit_price']) ?></td>
                            </tr>
                            <?php endwhile;endif; ?>
                        </tbody>
                        <tfoot>
                            <tr class="bg-lightblue">
                                <tr>
                                    <th class="p-1 text-right" colspan="6"><span><button class="btn btn btn-sm btn-flat btn-primary py-0 mx-1" type="button" id="add_row">Add Row</button></span> PO Amount</th>
                                    <th class="p-1 text-right" colspan="">$</th>
                                    <th class="p-1 text-left" id="sub_total">0</th>
                                </tr>
            

                                <th class="p-1 text-right" colspan="6">Advance Payment
                                    <th class="p-1 text-right" colspan="">$</th>
                                    <th class="p-1"><input type="text" class="w-100 border-0 text-left" value="<?php echo isset($discount_amount) ? $discount_amount : 0 ?>" name="discount_amount"></th>
                                    
                                </tr>
                                <tr>
                                    <th class="p-1 text-right" colspan="6">Total:</th>
                                    <th class="p-1 text-right" colspan="">$</th>
                                    <th class="p-1 text-left" id= "total">0</th>
                                </tr>
                            </tr>
                        </tfoot>
                    </table>
                    <div class="row">
                        <div class="col-md-6">
                            <label for="notes" class="control-label">Notes</label>
                            <textarea name="notes" id="notes" cols="10" rows="4" class="form-control rounded-0"><?php echo isset($notes) ? $notes : '' ?></textarea>
                        </div>
                        <div class="col-md-6">
                            <label for="status" class="control-label">Status</label>
                            <select name="status" id="status" class="form-control form-control-sm rounded-0">
                                <option value="0" <?php echo isset($status) && $status == 0 ? 'selected': '' ?>>Pending</option>
                                <option value="1" <?php echo isset($status) && $status == 1 ? 'selected': '' ?>>Approved</option>
                                <option value="2" <?php echo isset($status) && $status == 2 ? 'selected': '' ?>>Denied</option>
                            </select>
                        </div>
                    </div>
                </div>
            </div>
        </form>
    </div>
    <div class="card-footer">
        <button class="btn btn-flat btn-primary" form="po-form">Save</button>
        <a class="btn btn-flat btn-default" href="?page=shipment2">Cancel</a>
    </div>
</div>
<table class="d-none" id="item-clone">
    <tr class="po-item" data-id="">
        <td class="align-middle p-1 text-center">
            <button class="btn btn-sm btn-danger py-0" type="button" onclick="rem_item($(this))"><i class="fa fa-times"></i></button>
        </td>
        <td class="align-middle p-0 text-center">
            <input type="number" class="text-center w-100 border-0" step="any" name="qty[]"/></td>
        <td class="align-middle p-1">
            <input type="text" class="text-center w-100 border-0" name="unit[]"/></td>
        <td class="align-middle p-1">
            <input type="text" class="text-center w-100 border-0" step="any" name="specs[]"/></td>
        <td class="align-middle p-1">
            <input type="hidden" name="item_id[]">
            <input type="text" class="text-center w-100 border-0 item_id" required/></td>
        <td class="align-middle p-1 item-size"></td>
        <td class="align-middle p-1">
            <input type="number" step="any" class="text-right w-100 border-0" name="unit_price[]" value="0"/></td>
        <td class="align-middle p-1 text-right total-price">0</td>
    </tr>
</table>
<script>
    function rem_item(_this){
        _this.closest('tr').remove()
    }
    function calculate(){
        var _total = 0
        $('.po-item').each(function(){
            var qty = $(this).find("[name='qty[]']").val()
            var unit_price = $(this).find("[name='unit_price[]']").val()
            var row_total = 0;
            if(qty > 0 && unit_price > 0){
                row_total = parseFloat(qty) * parseFloat(unit_price)
            }
            $(this).find('.total-price').text(parseFloat(row_total).toLocaleString('en-US'))
        })
        $('.total-price').each(function(){
            var _price = $(this).text()
                _price = _price.replace(/\,/gi,'')
                _total += parseFloat(_price)
        })
        var discount_perc = 0
        if($('[name="discount_percentage"]').val() > 0){
            discount_perc = $('[name="discount_percentage"]').val()
        }
        var discount_amount = _total * (discount_perc/100);
        $('[name="discount_amount"]').val(parseFloat(discount_amount).toLocaleString("en-US"))
        var tax_perc = 0
        if($('[name="tax_percentage"]').val() > 0){
            tax_perc = $('[name="tax_percentage"]').val()
        }
        var tax_amount = _total * (tax_perc/100);
        $('[name="tax_amount"]').val(parseFloat(tax_amount).toLocaleString("en-US"))
        $('#sub_total').text(parseFloat(_total).toLocaleString("en-US"))
        $('#total').text(parseFloat(_total-discount_amount).toLocaleString("en-US"))
    }

    function _autocomplete(_item){
        _item.find('.item_id').autocomplete({
            source:function(request, response){
                $.ajax({
                    url:_base_url_+"classes/Master2.php?f=search_items",
                    method:'POST',
                    data:{q:request.term},
                    dataType:'json',
                    error:err=>{
                        console.log(err)
                    },
                    success:function(resp){
                        response(resp)
                    }
                })
            },
            select:function(event,ui){
                console.log(ui)
                _item.find('input[name="item_id[]"]').val(ui.item.id)
                _item.find('.item-size').text(ui.item.size)
            }
        })
    }
    $(document).ready(function(){
        $('#add_row').click(function(){
            var tr = $('#item-clone tr').clone()
            $('#item-list tbody').append(tr)
            _autocomplete(tr)
            tr.find('[name="qty[]"],[name="unit_price[]"]').on('input keypress',function(e){
                calculate()
            })
            $('#item-list tfoot').find('[name="discount_percentage"],[name="tax_percentage"]').on('input keypress',function(e){
                calculate()
            })
        })
        if($('#item-list .po-item').length > 0){
            $('#item-list .po-item').each(function(){
                var tr = $(this)
                _autocomplete(tr)
                tr.find('[name="qty[]"],[name="unit_price[]"]').on('input keypress',function(e){
                    calculate()
                })
                $('#item-list tfoot').find('[name="discount_percentage"],[name="tax_percentage"]').on('input keypress',function(e){
                    calculate()
                })
                tr.find('[name="qty[]"],[name="unit_price[]"]').trigger('keypress')
            })
        }else{
        $('#add_row').trigger('click')
        }
        $('.select2').select2({placeholder:"Please Select here",width:"relative"})
        $('#po-form').submit(function(e){
            e.preventDefault();
            var _this = $(this)
            $('.err-msg').remove();
            $('[name="po_no"]').removeClass('border-danger')
            if($('#item-list .po-item').length <= 0){
                alert_toast(" Please add atleast 1 item on the list.",'warning')
                return false;
            }
            start_loader();
            $.ajax({
                url:_base_url_+"classes/Master2.php?f=save_po",
                data: new FormData($(this)[0]),
                cache: false,
                contentType: false,
                processData: false,
                method: 'POST',
                type: 'POST',
                dataType: 'json',
                error:err=>{
                    console.log(err)
                    alert_toast("An error occured",'error');
                    end_loader();
                },
                success:function(resp){
                    if(typeof resp =='object' && resp.status == 'success'){
                        location.href = "./?page=shipment2/view_po&id="+resp.id;
                    }else if((resp.status == 'failed' || resp.status == 'po_failed') && !!resp.msg){
                        var el = $('<div>')
                            el.addClass("alert alert-danger err-msg").text(resp.msg)
                            _this.prepend(el)
                            el.show('slow')
                            $("html, body").animate({ scrollTop: 0 }, "fast");
                            end_loader()
                            if(resp.status == 'po_failed'){
                                $('[name="po_no"]').addClass('border-danger').focus()
                            }
                    }else{
                        alert_toast("An error occured",'error');
                        end_loader();
                        console.log(resp)
                    }
                }
            })
        })

        
    })
    
</script>

🤖 · May 30, 2024

To insert a new query to call the discount_amount from the po_list table, you can use a SQL SELECT statement. Here's an example query that can help you achieve this:

SQL:

SELECT discount_amount
FROM po_list
WHERE <condition>;

In the above query, you need to replace <condition> with the appropriate condition based on your requirements. For example, if you want to retrieve the discount_amount based on a specific po_number, you can use:

SQL:

SELECT discount_amount
FROM po_list
WHERE po_number = 'your_po_number';

Make sure to replace 'your_po_number' with the actual PO number you are interested in.

By executing this query in your database management tool or environment, you will be able to retrieve the discount_amount from the po_list table based on the condition specified.

If you need further assistance or have specific requirements, feel free to provide more details so that I can assist you accordingly.

fireclouu · May 31, 2024

try to use join query , then identify mo yung po_list via po_number, these way select * from (table) only filter out entries that exist within po_list table

or much better reimplement your table creation, implement foreign key, para may better integrity yung database mo, so non-existent reference id will not proceed

J P · Jun 5, 2024

fireclouu said:
try to use join query , then identify mo yung po_list via po_number, these way select * from (table) only filter out entries that exist within po_list table

or much better reimplement your table creation, implement foreign key, para may better integrity yung database mo, so non-existent reference id will not proceed

Can you send me sample for my better understanding. Salamat po may mga terms pa p akong di alam hehe

_Moira · Jun 6, 2024

Gawa ka script sa jquery tapos auto populate mo na nun.

GanbaRuby232 · Jun 9, 2024

Gian Aldover · Sep 30, 2024

Search

Search

Auto populate discount_amount from other table:

J P

Journeyman

🤖

fireclouu

Fanatic

J P

Journeyman

_Moira

GanbaRuby232

Forum Veteran

Gian Aldover

Similar threads

About this Thread

New Topics

Survey: Best backend for ESP32 IoT projects

REPLIT REPLIT REPLIT

Online Mikrotik Voucher Generator no login required saka wala logging

Next.js hosting DMCA ignored?

Sofia + Luhn algorithm for Spotify Trial Python Code Version Valid IMEI 2026

LF may business/offer services/etc. Gawan kita website for Free

Sa mga vibecoders diyan, Ano po gamit niyong subscription rn since mabilis na maubos ang Copilot?

Pano po ma-cloak or change ang domain name ng fb shared post

WAP MASTER POS PARA SA LAHAT NG STORE

Paano gumawa ng Imei Pattern(Spotify)

Trending Topics

Online now

Forum statistics