PHP mysql ดึงข้อมูลจาก sql ที่มีเครืองหมาย " มาแสดงใน list ไม่ได้ แสดงเฉพาะข้อมูล แต่ไม่แสดงเครื่องหมาย

<?php 
session_start();
if ($_SESSION['status'] != "T") {
    header("Location: index.php");
    exit;
} else {
    include('connection.php');

    if (isset($_POST['submit'])) {
        $user_add = $_SESSION['name'];
        $details = $_POST['detail'];
        $units = $_POST['unit'];
        $prices = $_POST['price'];
        $amounts = $_POST['amount'];
        $bill_num = $_POST['bill_num'];
        $bill_type = $_POST['bill_type'];
        $bill_date = $_POST['bill_date'];
        $sender = $_POST['sender'];
        $shop_id = $_POST['shop_id'];
        $ivat = isset($_POST['ivat']) ? 1 : 0; 
        $discount = $_POST['discount'];

        mysqli_begin_transaction($conn);

        try {
            $bill_ready = ($bill_type == 1) ? 1 : 0;

            $sql = "INSERT INTO bill(bill_num, bill_type, bill_date, ivat, sender, shop_id, user_add, bill_ready) VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
            $stmt = mysqli_prepare($conn, $sql);
            mysqli_stmt_bind_param($stmt, "sssssisi", $bill_num, $bill_type, $bill_date, $ivat, $sender, $shop_id, $user_add, $bill_ready);
            mysqli_stmt_execute($stmt);
            mysqli_stmt_close($stmt);

            $id_bill = $conn->insert_id;
            $base_price_total = 0;

            $ref_bill_items = [];
            if ($bill_type == 2 && isset($_POST['ref_bill_num']) && is_array($_POST['ref_bill_num']) && !empty($_POST['ref_bill_num'])) {
                $ref_bill_ids = implode(',', array_map('intval', $_POST['ref_bill_num']));
                $sql_ref_items = "SELECT detail, unit FROM wasadu WHERE bill_id IN ($ref_bill_ids)";
                $result_ref_items = mysqli_query($conn, $sql_ref_items);
                while($row = mysqli_fetch_assoc($result_ref_items)) {
                    $ref_bill_items[] = ['detail' => $row['detail'], 'unit' => $row['unit']];
                }
            }

            foreach ($details as $i => $detail) {
                if (trim($detail) != '') {
                    $item_exists_in_ref_bill = false;
                    foreach ($ref_bill_items as $ref_item) {
                        if ($ref_item['detail'] == $detail && $ref_item['unit'] == $units[$i]) {
                            $item_exists_in_ref_bill = true;
                            break;
                        }
                    }

                    $sql_wasadu_insert = "INSERT INTO wasadu(detail, unit, price, amount, bill_id) VALUES(?, ?, ?, ?, ?)";
                    $stmt_wasadu_insert = mysqli_prepare($conn, $sql_wasadu_insert);
                    mysqli_stmt_bind_param($stmt_wasadu_insert, "ssdii", $detail, $units[$i], $prices[$i], $amounts[$i], $id_bill);
                    mysqli_stmt_execute($stmt_wasadu_insert);
                    mysqli_stmt_close($stmt_wasadu_insert);
                    
                    $base_price_total += $prices[$i] * $amounts[$i];
                    
                    if ($bill_type == 1 || ($bill_type == 2 && !$item_exists_in_ref_bill)) {
                        $sql_stock_check = "SELECT id FROM stock WHERE detail = ? AND unit = ?";
                        $stmt_stock_check = mysqli_prepare($conn, $sql_stock_check);
                        mysqli_stmt_bind_param($stmt_stock_check, "ss", $detail, $units[$i]);
                        mysqli_stmt_execute($stmt_stock_check);
                        $result_stock = mysqli_stmt_get_result($stmt_stock_check);
                        $row_stock = mysqli_fetch_assoc($result_stock);
                        mysqli_stmt_close($stmt_stock_check);

                        if ($row_stock) {
                            $sql_stock_update = "UPDATE stock SET amount = amount + ? WHERE id = ?";
                            $stmt_stock_update = mysqli_prepare($conn, $sql_stock_update);
                            mysqli_stmt_bind_param($stmt_stock_update, "ii", $amounts[$i], $row_stock['id']);
                            mysqli_stmt_execute($stmt_stock_update);
                            mysqli_stmt_close($stmt_stock_update);
                        } else {
                            $sql_stock_insert = "INSERT INTO stock(detail, unit, amount) VALUES(?, ?, ?)";
                            $stmt_stock_insert = mysqli_prepare($conn, $sql_stock_insert);
                            mysqli_stmt_bind_param($stmt_stock_insert, "ssi", $detail, $units[$i], $amounts[$i]);
                            mysqli_stmt_execute($stmt_stock_insert);
                            mysqli_stmt_close($stmt_stock_insert);
                        }
                    }
                }
            }
            
            if ($bill_type == 2 && isset($_POST['ref_bill_num']) && is_array($_POST['ref_bill_num']) && !empty($_POST['ref_bill_num'])) {
                $ref_bill_ids = $_POST['ref_bill_num'];
                $sql_update_bill_status = "UPDATE bill SET bill_ready = 0 WHERE id = ?";
                $stmt_update_bill_status = mysqli_prepare($conn, $sql_update_bill_status);
                foreach ($ref_bill_ids as $ref_bill_id) {
                    mysqli_stmt_bind_param($stmt_update_bill_status, "i", $ref_bill_id);
                    mysqli_stmt_execute($stmt_update_bill_status);
                }
                mysqli_stmt_close($stmt_update_bill_status);
            }
                                    
            $sum = 0;
            $vat = 0;
            $grand_total = 0;
            $total_after_discount = $base_price_total - $discount;

            if ($ivat == 0) {
                $grand_total = $total_after_discount;
                $sum = $grand_total / 1.07;
                $vat = $grand_total - $sum;
            } else {
                $sum = $total_after_discount;
                $vat = $sum * 0.07;
                $grand_total = $sum + $vat;
            }
            $sum = round($sum, 2);
            $vat = round($vat, 2);
            $grand_total = round($grand_total, 2);

            $sql_update_bill = "UPDATE bill SET sum = ?, vat = ?, total = ?, discount = ? WHERE id = ?";
            $stmt_update_bill = mysqli_prepare($conn, $sql_update_bill);
            mysqli_stmt_bind_param($stmt_update_bill, "ddddi", $sum, $vat, $grand_total, $discount, $id_bill);
            mysqli_stmt_execute($stmt_update_bill);
            mysqli_stmt_close($stmt_update_bill);

            mysqli_commit($conn);

            header("Location: add_material.php?status=success");
            exit;

        } catch (mysqli_sql_exception $e) {
            mysqli_rollback($conn);
            echo "Error: " . $e->getMessage();
            exit;
        }
    }

    $sql_detail = "SELECT DISTINCT detail FROM stock";
    $query_detail = mysqli_query($conn, $sql_detail);
    $datalist_details = [];
    while ($d = mysqli_fetch_assoc($query_detail)) {
        $datalist_details[] = $d['detail'];
    }
    
    $sql_unit = "SELECT DISTINCT unit FROM stock";
    $query_unit = mysqli_query($conn, $sql_unit);
    $datalist_units = [];
    while ($d = mysqli_fetch_assoc($query_unit)) {
        $datalist_units[] = $d['unit'];
    }

?>
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>ระบบพัสดุ วิศวกรรมเครื่องกลเรือ</title>
    <link rel="icon" href="logo.png" type="image/png">
    <script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAPJcXn/tWtIaxVXM" crossorigin="anonymous"></script>
    <link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
</head>
<body>
<?php include("sidebar.php"); ?>

<div class="container my-5">
    <div class="text-center mb-4">
        <h2 class="fw-bold">ใบเสร็จรับวัสดุ</h2>
<?php
if (isset($_GET['status']) && $_GET['status'] == 'success') {
    echo '<div class="alert alert-success alert-dismissible fade show" role="alert" style="margin-top: 20px;">';
    echo 'บันทึกข้อมูลเรียบร้อยแล้ว!';
    echo '<button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>';
    echo '</div>';
}
?>      
    </div>
<?php
    $sql = "SELECT DISTINCT * FROM shop";
    $query = mysqli_query($conn,$sql);
?>
<form method="post" onsubmit="return showData()">
    <div class="card shadow-sm p-4 mb-4">
        <h5 class="mb-3">ข้อมูลร้านค้า</h5>
        <div class="row g-3">
            <div class="col-md-6">
                <label for="title" class="form-label">ชื่อร้านค้า</label>
                <input list="abc" type="text" class="form-control" name="title" id="title" required autocomplete="off" placeholder="ชื่อร้าน/บริษัท">
                <datalist id="abc">
                    <?php 
                        mysqli_data_seek($query, 0);
                        while ($d = mysqli_fetch_assoc($query)) {
                            echo "<option value='".htmlspecialchars($d['title'], ENT_QUOTES, 'UTF-8')."'></option>";
                        }
                    ?>
                </datalist>
            </div>
            <div class="col-md-6 d-flex align-items-end">
                <a href="add_shop.php" class="btn btn-secondary ms-2">จัดการร้านค้า</a>
            </div>
            <div class="col-12">
                <label for="shop_address" class="form-label">ที่อยู่</label>
                <input type="text" class="form-control" name="shop_address" id="shop_address" disabled>
            </div>
            <div class="col-md-4">
                <label for="tell" class="form-label">เบอร์โทร</label>
                <input type="text" class="form-control" name="tell" id="tell" disabled>
            </div>
            <div class="col-md-4">
                <label for="email" class="form-label">อีเมล</label>
                <input type="text" class="form-control" name="email" id="email" disabled>
            </div>
            <div class="col-md-4">
                <label for="tax_id" class="form-label">เลขผู้เสียภาษี</label>
                <input type="text" class="form-control" name="tax_id" id="tax_id" disabled>
            </div>
        </div>
        <input type="hidden" name="shop_id" id="shop_id">
    </div>

    <div class="card shadow-sm p-4 mb-4">
        <h5 class="mb-3">ข้อมูลใบเสร็จ</h5>
        <div class="row g-3">
            <div class="col-md-4">
                <label for="bill_type" class="form-label">ชนิดใบเสร็จ</label>
                <select class="form-select" name="bill_type" id="bill_type" required>
                    <option value="">เลือก..</option>
                    <option value="1">ใบส่งของ</option>
                    <option value="2">ใบกำกับภาษี</option>
                </select>
            </div>
            <div class="col-md-4" id="delivery_bill_select" style="display: none;">
                <label for="ref_bill_num" class="form-label">อ้างอิงใบส่งของเลขที่</label>
                <div id="ref_bill_checkbox_container" class="form-control" style="height: 150px; overflow-y: scroll;">
                    <?php
                        $sql_ref = "SELECT DISTINCT b.bill_num, b.id, s.title FROM bill b JOIN shop s ON b.shop_id = s.id WHERE b.bill_type = 1 AND b.bill_ready = 1";
                        $query_ref = mysqli_query($conn, $sql_ref);
                        while ($d = mysqli_fetch_assoc($query_ref)) {
                            echo "<div class='form-check'><input class='form-check-input ref-bill-checkbox' type='checkbox' value='".$d['id']."' id='bill_".$d['id']."' name='ref_bill_num[]'><label class='form-check-label' for='bill_".$d['id']."'>".$d['title']." (เลขบิล: ".$d['bill_num'].")</label></div>";
                        }
                    ?>
                </div>
            </div>          
            <div class="col-md-4">
                <label for="bill_num" class="form-label">เลขที่/เล่มที่ ใบเสร็จ</label>
                <input type="text" class="form-control" name="bill_num" autocomplete="off" required>
            </div>
            <div class="col-md-4">
                <label for="bill_date" class="form-label">วันที่ใบเสร็จ</label>
                <input type="date" class="form-control" name="bill_date" autocomplete="off">
            </div>
            <div class="col-md-6">
                <label for="sender" class="form-label">ผู้ส่งของ</label>
                <input type="text" class="form-control" name="sender" autocomplete="off">
            </div>
            <div class="col-md-6 d-flex align-items-end" id="myCheckbox" style="display: none;">
                <div class="form-check">
                    <input class="form-check-input" type="checkbox" id="ivat" name="ivat">
                    <label class="form-check-label" for="ivat">
                        ถ้าเป็น VAT นอก ให้เลือกช่องนี้
                    </label>
                </div>
            </div>
        </div>
    </div>
    <div class="card shadow-sm p-4 mb-4">
        <h5 class="mb-3">รายการวัสดุ</h5>
        <table class="table table-bordered" id="dynamic_fill">
            <thead class="table-light text-center">
                <tr>
                    <th>รายการวัสดุ</th>
                    <th>หน่วยนับ</th>
                    <th>ราคา</th>
                    <th>จำนวน</th>
                    <th></th>
                </tr>
            </thead>
            <tbody id="wasadu_table_body">
            </tbody>
        </table>
        <div class="mt-3">
            <div class="d-flex justify-content-end mb-2">
                <label for="discount_input" class="me-3">ส่วนลด:</label>
                <div style="width: 150px;">
                    <input type="text" class="form-control text-end" id="discount_input" name="discount" value="0.00" onkeypress="return chkNumber(this)">
                </div>
            </div>
            <div class="d-flex justify-content-end">
                <span class="me-3">ยอดรวม (ก่อนภาษี):</span>
                <span id="subtotal" class="fw-bold">0.00</span>
            </div>
            <div class="d-flex justify-content-end">
                <span class="me-3">ภาษีมูลค่าเพิ่ม (7%):</span>
                <span id="vat_amount" class="fw-bold">0.00</span>
            </div>
            <div class="d-flex justify-content-end">
                <span class="me-3">ยอดรวมทั้งหมด:</span>
                <span id="grand_total" class="fw-bold">0.00</span>
            </div>
        </div>
    </div>

    <div class="text-center">
        <button type="submit" name="submit" id="btn_submit" class="btn btn-primary px-4">บันทึก</button>
        <a href="index.php" class="btn btn-secondary px-4 ms-2">กลับหน้าหลัก</a>
    </div>
</form>
</div>
<?php mysqli_close($conn); ?>
</body>
</html>

<?php
    }
?>

<script>
    // const datalist_details_data = <?php echo json_encode($datalist_details); ?>;
    // const datalist_units_data = <?php echo json_encode($datalist_units); ?>;
    const datalist_details_data = <?php echo json_encode($datalist_details, JSON_HEX_QUOT); ?>;
    const datalist_units_data = <?php echo json_encode($datalist_units, JSON_HEX_QUOT); ?>;

    function createDatalistOptions(data) {
        let optionsHtml = '';
        data.forEach(item => {
            optionsHtml += `<option value="${item.replace(/"/g, '')}"></option>`;
            // optionsHtml += `<option value="${item}"></option>`;
        });
        return optionsHtml;
    }

    function addWasaduRow(detailValue = '', unitValue = '', priceValue = '', amountValue = '') {
        const datalist_details_html = createDatalistOptions(datalist_details_data);
        const datalist_units_html = createDatalistOptions(datalist_units_data);

        let newRow = `
            <tr>
                <td>
                    <input list="xyz" class="form-control" type="text" name="detail[]" value="${detailValue}" autocomplete="off">
                    <datalist id="xyz">${datalist_details_html}</datalist>
                </td>
                <td>
                    <input list="zxy" class="form-control" type="text" name="unit[]" value="${unitValue}" autocomplete="off">
                    <datalist id="zxy">${datalist_units_html}</datalist>
                </td>
                <td><input class="form-control text-center" type="text" name="price[]" value="${priceValue}" onkeypress="return chkNumber(this)"></td>
                <td><input class="form-control text-center" type="text" name="amount[]" value="${amountValue}" onkeypress="return chkint(this)"></td>
                <td class="text-center"></td>
            </tr>`;
        
        $('#wasadu_table_body').append(newRow);
        updateAddRemoveButtons();
    }
    
    function updateAddRemoveButtons() {
        const rows = $('#wasadu_table_body tr');
        rows.each(function(index, row) {
            const lastCell = $(row).find('td:last-child');
            if (index === rows.length - 1) {
                lastCell.html(`<button type="button" class="btn btn-success btn_add">เพิ่มแถว</button>`);
            } else {
                lastCell.html(`<button type="button" class="btn btn-danger btn_del">ลบแถว</button>`);
            }
        });
    }

    $(document).ready(function(){
        $("#title").change(function(){
            $.ajax({ 
                url: "return_shop.php",
                type: "POST",
                data: 'title=' +$("#title").val()
            })
            .done(function(result) { 
                var obj = jQuery.parseJSON(result);
                if(obj == null) {
                    $('#shop_address').val('');
                    $('#tell').val('');
                    $('#email').val('');
                    $('#tax_id').val('');
                    $('#shop_id').val('');
                } else {
                    $.each(obj, function(key, inval) {
                        $("#title").val(inval["title"]);
                        $("#shop_address").val(inval["shop_address"]);
                        $("#tell").val(inval["tell"]);
                        $("#email").val(inval["email"]);
                        $("#tax_id").val(inval["tax_id"]);
                        $("#shop_id").val(inval["id"]);
                    });
                }
            });
        });

        addWasaduRow();
        calculateTotals();

        const tableBody = $('#wasadu_table_body');
        const selectBillType = document.getElementById('bill_type');
        const checkboxVatDiv = document.getElementById('myCheckbox');
        const deliveryBillSelectDiv = document.getElementById('delivery_bill_select');
        const refBillCheckboxes = $('.ref-bill-checkbox');
        
        selectBillType.addEventListener('change', function() {
            if (this.value === '2') {
                checkboxVatDiv.style.display = 'block';
                deliveryBillSelectDiv.style.display = 'block';
            } else {
                checkboxVatDiv.style.display = 'none';
                deliveryBillSelectDiv.style.display = 'none';
                refBillCheckboxes.prop('checked', false);
            }
            $('#wasadu_table_body').empty();
            addWasaduRow();
            calculateTotals();
        });

        $(document).on('change', '.ref-bill-checkbox', function(){
            var selected_bill_ids = [];
            $('.ref-bill-checkbox:checked').each(function(){
                selected_bill_ids.push($(this).val());
            });

            if (selected_bill_ids.length > 0) {
                $.ajax({
                    url: "get_wasadu_items.php",
                    type: "POST",
                    data: { bill_ids: selected_bill_ids },
                    dataType: "json",
                    success: function(items) {
                        tableBody.empty();
                        if (items.length > 0) {
                            $.each(items, function(i, item) {
                                addWasaduRow(item.detail, item.unit, item.price, item.amount);
                            });
                        }
                        addWasaduRow();
                        calculateTotals();
                    }
                });
            } else {
                tableBody.empty();
                addWasaduRow();
                calculateTotals();
            }
        });

        $(document).on('click', '.btn_add', function() {
            addWasaduRow();
        });

        $(document).on('click', '.btn_del', function() {
            $(this).closest('tr').remove();
            updateAddRemoveButtons();
            calculateTotals();
        });

        $(document).on('input', 'input[name="price[]"], input[name="amount[]"], #discount_input', function() {
            calculateTotals();
        });
        
        $('#ivat').on('change', function() {
            calculateTotals();
        });
    });

    function chkint(ele) {
        var vchar = String.fromCharCode(event.keyCode);
        if ((vchar < '0' || vchar > '9')) return false;
        ele.onKeyPress = vchar;
    }
    
    function chkNumber(ele) {
        var vchar = String.fromCharCode(event.keyCode);
        if ((vchar < '0' || vchar > '9') && (vchar != '.')) return false;
        ele.onKeyPress = vchar;
    }

    function calculateTotals() {
        let subtotal_base = 0;
        const discount_val = parseFloat($('#discount_input').val()) || 0;
        const isVatOutside = $('#ivat').is(':checked');

        $('#dynamic_fill tr').each(function() {
            const price = parseFloat($(this).find('input[name="price[]"]').val()) || 0;
            const amount = parseInt($(this).find('input[name="amount[]"]').val()) || 0;
            subtotal_base += price * amount;
        });

        let subtotal = 0;
        let vat_amount = 0;
        let grand_total = 0;

        const total_after_discount = subtotal_base - discount_val;

        if (isVatOutside) {
            subtotal = total_after_discount;
            vat_amount = subtotal * 0.07;
            grand_total = subtotal + vat_amount;
        } else {
            grand_total = total_after_discount;
            subtotal = grand_total / 1.07;
            vat_amount = grand_total - subtotal;
        }

        $('#subtotal').text(subtotal.toLocaleString('en-US', { minimumFractionDigits: 2, maximumFractionDigits: 2 }));
        $('#vat_amount').text(vat_amount.toLocaleString('en-US', { minimumFractionDigits: 2, maximumFractionDigits: 2 }));
        $('#grand_total').text(grand_total.toLocaleString('en-US', { minimumFractionDigits: 2, maximumFractionDigits: 2 }));
    }

    function showData() {
        const wasaduRows = document.querySelectorAll('#wasadu_table_body tr');
        let hasEmptyFields = false;
        
        for (let i = 0; i < wasaduRows.length; i++) {
            const row = wasaduRows[i];
            const detail = row.querySelector('input[name="detail[]"]').value.trim();
            const unit = row.querySelector('input[name="unit[]"]').value.trim();
            const price = row.querySelector('input[name="price[]"]').value.trim();
            const amount = row.querySelector('input[name="amount[]"]').value.trim();
            
            if (detail !== '' || unit !== '' || price !== '' || amount !== '') {
                if (detail === '' || unit === '' || price === '' || amount === '') {
                    hasEmptyFields = true;
                    break;
                }
            }
        }
        
        if (hasEmptyFields) {
            alert('กรุณากรอกข้อมูลในแถวที่เพิ่มให้ครบทุกช่อง!');
            return false;
        }

        let total_base_price = 0;
        const discount_val = parseFloat(document.getElementById('discount_input').value) || 0;
        let is_vat_included = document.getElementById('ivat').checked;
        const prices = document.getElementsByName('price[]');
        const amounts = document.getElementsByName('amount[]');

        for (let i = 0; i < prices.length; i++) {
            const price = parseFloat(prices[i].value) || 0;
            const amount = parseInt(amounts[i].value) || 0;
            total_base_price += price * amount;
        }

        let subtotal = 0;
        let vat = 0;
        let grand_total = 0;

        const total_after_discount = total_base_price - discount_val;

        if (is_vat_included) {
            subtotal = total_after_discount;
            vat = subtotal * 0.07;
            grand_total = subtotal + vat;
        } else {
            grand_total = total_after_discount;
            subtotal = grand_total / 1.07;
            vat = grand_total - subtotal;
        }
        
        const message = `
            ส่วนลด: ${discount_val.toLocaleString('en-US', { minimumFractionDigits: 2, maximumFractionDigits: 2 })} บาท
            ราคาสินค้า: ${subtotal.toLocaleString('en-US', { minimumFractionDigits: 2, maximumFractionDigits: 2 })} บาท
            ภาษีมูลค่าเพิ่ม (7%): ${vat.toLocaleString('en-US', { minimumFractionDigits: 2, maximumFractionDigits: 2 })} บาท
            ราคารวมทั้งหมด: ${grand_total.toLocaleString('en-US', { minimumFractionDigits: 2, maximumFractionDigits: 2 })} บาท
            
            ต้องการบันทึกข้อมูลใช่หรือไม่?
        `;

        return confirm(message);
    }
</script>