This is a proposal calculator, the problem is this.
When you calculate manually, everything works, but when you choose a template, it doesn’t work:
- After selecting the template, it does not show values in input fields picked from product mysql table.
- Shows only one selected category and not all. (I cannot select other categories).
- It shows only one selected product not all what in category (I cannot select other products).
But here it does not work properly only with the selection of templates.
I added photo for visual more explain.
my logic is not enough to complete this task.
index.php
session_start();
include_once 'config/database.php';
include 'languages/language.php';
include 'assets/session.php';
$query = "SELECT * FROM products";
$result = mysqli_query($conn, $query);
$products = array();
if ($result) {
while ($row = mysqli_fetch_assoc($result)) {
$products[] = $row;
}
} else {
echo "Error: " . mysqli_error($conn);
}
$products_json = json_encode($products);
$query = "SELECT DISTINCT template_name FROM proposal_templates";
$templateResult = mysqli_query($conn, $query);
$templates = array();
if ($templateResult) {
while ($row = mysqli_fetch_assoc($templateResult)) {
$templates[] = $row['template_name'];
}
} else {
echo "Error: " . mysqli_error($conn);
}
?>
<?php include 'assets/header.php'; ?>
<body class="bg-gray-100">
<?php include 'assets/nav.php'; ?>
<div class="container mx-auto mt-8 pl-2">
<div class="mb-6">
<h2 class="text-xl font-semibold mb-3">Proposal calculator</h2>
<button class="mb-3 bg-green-500 hover:bg-green-700 text-white font-bold rounded add-row-btn py-1 px-3 rounded">Add</button>
<select name="templates" class="rounded py-1 px-3 border border-gray-400 px-4 py-2" id="template-dropdown">
<option disabled selected>Select template</option>
<?php foreach ($templates as $template): ?>
<option value="<?= htmlspecialchars($template) ?>"><?= htmlspecialchars($template) ?></option>
<?php endforeach; ?>
</select>
<div class="overflow-x-auto">
<div class="overflow-x-auto">
<table class="w-full table-auto" id="proposal-table">
<thead>
<tr class="bg-gray-200">
<th class="w-1/3 sm:w-1/6 lg:w-1/4 px-4 py-2 text-left border border-gray-400"><?php echo $lang['templates']['category']; ?></th>
<th class="w-1/2 sm:w-2/3 lg:w-3/4 px-4 py-2 text-left border border-gray-400"><?php echo $lang['templates']['product']; ?></th>
<th class="w-1/6 sm:w-1/6 lg:w-1/12 px-4 py-2 border border-gray-400"><?php echo $lang['templates']['quantity']; ?></th>
<th class="w-1/6 sm:w-1/6 lg:w-1/12 px-4 py-2 border border-gray-400"><?php echo $lang['templates']['unitprice']; ?></th>
<th class="w-1/6 sm:w-1/6 lg:w-1/12 px-4 py-2 border border-gray-400"><?php echo $lang['templates']['totalprice']; ?></th>
<th class="w-1/6 sm:w-1/6 lg:w-1/12 px-4 py-2 border border-gray-400"><?php echo $lang['templates']['withtax']; ?></th>
<th class="w-1/6 sm:w-1/6 lg:w-1/12 px-4 py-2 border border-gray-400"><?php echo $lang['templates']['discount']; ?></th>
<th class="w-1/6 sm:w-1/6 lg:w-1/12 px-4 py-2 border border-gray-400"><?php echo $lang['templates']['buyprice']; ?></th>
<th class="w-1/6 sm:w-1/6 lg:w-1/12 px-4 py-2 border border-gray-400"><?php echo $lang['templates']['b2bprice']; ?></th>
<th class="w-1/6 sm:w-1/6 lg:w-1/12 px-4 py-2 border border-gray-400"><?php echo $lang['templates']['specialprice']; ?></th>
<th class="w-1/6 sm:w-1/6 lg:w-1/12 px-4 py-2 border border-gray-400"><?php echo $lang['templates']['power']; ?></th>
<th class="w-1/6 sm:w-1/6 lg:w-1/12 px-4 py-2 border border-gray-400"><?php echo $lang['categories']['actions']; ?></th>
</tr>
</thead>
<tbody>
<tr>
<td>
<!-- category selection dropdown -->
<select name="product_category" class="border border-gray-400 px-4 py-2 text-left w-full">
<option disabled selected><?php echo $lang['products']['category_filter']; ?></option>
<?php
if (!empty($categories)) {
foreach ($categories as $category) {
echo '<option value="' . $category["name"] . '">' . $category["name"] . '</option>';
}
} else {
echo '<option value="">' . $lang['products']['category_filter'] . '</option>';
}
?>
</select>
</td>
<td>
<!-- Product selection dropdown -->
<select class="form-select border border-gray-400 px-4 py-2 text-left w-full">
<option disabled selected class="default-option"></option>
<?php
if (!empty($products)) {
foreach ($products as $product) {
echo '<option value="' . $product["id"] . '" class="product-option" style="display: none;">' . $product["product_name"] . '</option>';
}
}
?>
</select>
</td>
<td class="px-1 py-2 text-center"><input type="text" class="quantity-input rounded w-16 text-center"></td>
<td class="px-1 py-2 text-center"><input type="text" class="sell-price-input rounded w-16 text-center bg-gray-200" readonly></td>
<td class="px-1 py-2 text-center"><input type="text" class="total-price-without-tax-input rounded w-24 text-center bg-gray-200" readonly></td>
<td class="px-1 py-2 text-center"><input type="text" class="total-price-with-tax-input rounded w-24 text-center bg-gray-200" readonly></td>
<td class="px-1 py-2 text-center"><input type="text" class="discount-input rounded w-24 text-center"></td>
<td class="px-1 py-2 text-center"><input type="text" class="buy-price-input rounded w-20 text-center bg-gray-200" readonly></td>
<td class="px-1 py-2 text-center"><input type="text" class="b2b-price-input rounded w-20 text-center bg-gray-200" readonly></td>
<td class="px-1 py-2 text-center"><input type="text" class="special-price-input rounded w-20 text-center bg-gray-200" readonly></td>
<td class="px-1 py-2 text-center"><input type="text" class="product-power-input rounded w-20 text-center bg-gray-200" readonly></td>
<td class=" px-4 py-2 text-center">
<button class="bg-red-500 hover:bg-red-700 text-white font-bold rounded delete-row-btn py-1 px-3 rounded">-</button>
</td>
</tr>
</tbody>
</table>
</div>
</div>
<div class="mt-8 flex justify-start">
<div>
<label class="text-sm">Total Price Without Tax</label>
<input type="text" class="total-price-without-tax rounded text-sm" readonly>
</div>
<div class="ml-2 sm:ml-4">
<label class="text-sm">Total Tax (21%)</label>
<input type="text" class="total-tax rounded text-sm" readonly>
</div>
<div class="ml-2 sm:ml-4">
<label class="text-sm">Total Price With Tax</label>
<input type="text" class="total-price-with-tax rounded text-sm" readonly>
</div>
<div class="ml-2 sm:ml-4">
<label class="text-sm">Support</label>
<input type="text" class="support rounded text-sm" readonly>
</div>
<div class="ml-2 sm:ml-4">
<label class="text-sm">Price with support</label>
<input type="text" class="fullsupport rounded text-sm" readonly>
</div>
<div class="ml-2 sm:ml-4">
<label class="text-sm">Total Discount</label>
<input type="text" class="total-discount rounded text-sm" readonly>
</div>
<div class="ml-2 sm:ml-4">
<label class="text-sm">Discount Percentage</label>
<input type="text" class="total-discount-percentage rounded text-sm" readonly>
</div>
<div class="ml-2 sm:ml-4">
<label class="text-sm">Total Power</label>
<input type="text" class="total-power rounded text-sm" readonly>
</div>
</div>
</div>
</div>
<script src="assets/js/jquery.min.js"></script> <!-- jQuery v3.5.1 | (c) JS Foundation and other contributors | jquery.org/license */ -->
<script src="assets/js/flowbite.min.js"></script>
<script src="assets/js/session.js"></script>
<script>
$(document).ready(function() {
function storeOriginalSellPrice(row, originalSellPrice) {
row.data('original-sell-price', originalSellPrice);
}
function fetchCategories(categoryDropdown) {
$.ajax({
type: "GET",
url: "fetch_categories.php",
success: function(response) {
categoryDropdown.html(response);
fetchProducts(categoryDropdown);
}
});
}
function fetchProducts(categoryDropdown) {
var category = categoryDropdown.val();
var productDropdown = categoryDropdown.closest('tr').find('select.form-select');
$.ajax({
type: "POST",
url: "get_products_by_category.php",
data: { category: category },
success: function(response) {
productDropdown.html(response);
productDropdown.prop('disabled', false);
}
});
}
$(document).ready(function() {
$('#template-dropdown').change(function() {
var templateName = $(this).val();
$.ajax({
type: "POST",
url: "fetch_template_rows.php",
dataType: "json",
data: {templateName: templateName},
success: function(rows) {
$("#proposal-table tbody").empty();
rows.forEach(function(row) {
var newRow = $('<tr>').append(
$('<td>').append(
$('<select>').attr({'name': 'product_category', 'class': 'border border-gray-400 px-4 py-2 text-left w-full'}).append($('<option>').val(row.product_category).text(row.product_category))
),
$('<td>').append(
$('<select>').attr({'class': 'form-select border border-gray-400 px-4 py-2 text-left w-full'}).append($('<option>').val(row.product_name).text(row.product_name))
),
$('<td>').append($('<input>').attr({'type': 'text', 'class': 'quantity-input rounded w-16 text-center'}).val(row.product_quantity)),
$('<td>').append($('<input>').attr({'type': 'text', 'class': 'unit-price-input rounded w-16 text-center'}).val(row.unit_price)),
$('<td>').append($('<input>').attr({'type': 'text', 'class': 'total-price-without-tax-input rounded w-24 text-center bg-gray-200', 'readonly': true})),
$('<td>').append($('<input>').attr({'type': 'text', 'class': 'total-price-with-tax-input rounded w-24 text-center bg-gray-200', 'readonly': true})),
$('<td>').append($('<input>').attr({'type': 'text', 'class': 'discount-input rounded w-16 text-center'}).val(row.product_discount)),
$('<td>').append($('<input>').attr({'type': 'text', 'class': 'buy-price-input rounded w-16 text-center bg-gray-200', 'readonly': true}).val(row.buy_price)),
$('<td>').append($('<input>').attr({'type': 'text', 'class': 'b2b-price-input rounded w-16 text-center bg-gray-200', 'readonly': true}).val(row.b2b_price)),
$('<td>').append($('<input>').attr({'type': 'text', 'class': 'special-price-input rounded w-16 text-center bg-gray-200', 'readonly': true}).val(row.special_price)),
$('<td>').append($('<input>').attr({'type': 'text', 'class': 'product-power-input rounded w-16 text-center bg-gray-200', 'readonly': true}).val(row.product_power)),
$('<td>').append($('<button>').attr({'type': 'button', 'class': 'bg-red-500 hover:bg-red-700 text-white font-bold py-1 px-3 rounded delete-row-btn'}).text('-'))
);
$("#proposal-table tbody").append(newRow);
});
updateTotalCalculations();
}
});
});
function updateTotalCalculations() {
}
$(document).on('click', '.delete-row-btn', function() {
$(this).closest('tr').remove();
updateTotalCalculations();
});
});
$(document).ready(function() {
$('#template-dropdown').change(function() {
var templateName = $(this).val();
$.ajax({
type: "POST",
url: "fetch_template_rows.php",
dataType: "json",
data: {templateName: templateName},
success: function(rows) {
$("#proposal-table tbody").empty();
rows.forEach(function(row) {
var newRow = $('<tr>').append(
$('<td>').append(
$('<select>').attr({'name': 'product_category', 'class': 'border border-gray-400 px-4 py-2 text-left w-full'}).append($('<option>').val(row.product_category).text(row.product_category))
),
$('<td>').append(
$('<select>').attr({'class': 'form-select border border-gray-400 px-4 py-2 text-left w-full'}).append($('<option>').val(row.product_name).text(row.product_name))
),
$('<td>').append($('<input>').attr({'type': 'text', 'class': 'quantity-input rounded w-16 text-center'}).val(row.product_quantity)),
$('<td>').append($('<input>').attr({'type': 'text', 'class': 'unit-price-input rounded w-16 text-center bg-gray-200', 'readonly': true}).val(row.unit_price)),
$('<td>').append($('<input>').attr({'type': 'text', 'class': 'total-price-without-tax-input rounded w-24 text-center bg-gray-200', 'readonly': true})),
$('<td>').append($('<input>').attr({'type': 'text', 'class': 'total-price-with-tax-input rounded w-24 text-center bg-gray-200', 'readonly': true})),
$('<td>').append($('<input>').attr({'type': 'text', 'class': 'discount-input rounded w-16 text-center'}).val(row.product_discount)),
$('<td>').append($('<input>').attr({'type': 'text', 'class': 'buy-price-input rounded w-16 text-center bg-gray-200', 'readonly': true}).val(row.buy_price)),
$('<td>').append($('<input>').attr({'type': 'text', 'class': 'b2b-price-input rounded w-16 text-center bg-gray-200', 'readonly': true}).val(row.b2b_price)),
$('<td>').append($('<input>').attr({'type': 'text', 'class': 'special-price-input rounded w-16 text-center bg-gray-200', 'readonly': true}).val(row.special_price)),
$('<td>').append($('<input>').attr({'type': 'text', 'class': 'product-power-input rounded w-16 text-center bg-gray-200', 'readonly': true}).val(row.product_power)),
$('<td>').append($('<button>').attr({'type': 'button', 'class': 'bg-red-500 hover:bg-red-700 text-white font-bold py-1 px-3 rounded delete-row-btn'}).text('-'))
);
$("#proposal-table tbody").append(newRow);
});
updateTotalCalculations();
fetchCategoriesAndProducts(templateName);
}
});
});
function fetchCategoriesAndProducts(templateName) {
$.ajax({
type: "POST",
url: "fetch_categories_and_products.php",
dataType: "json",
data: {templateName: templateName},
success: function(data) {
var categoryDropdown = $('select[name="product_category"]');
categoryDropdown.empty();
categoryDropdown.append($('<option disabled selected>').text('Select category'));
data.categories.forEach(function(category) {
categoryDropdown.append($('<option>').val(category).text(category));
});
var productDropdown = $('select.form-select');
productDropdown.empty();
productDropdown.append($('<option disabled selected>').text('Select product'));
data.products.forEach(function(product) {
productDropdown.append($('<option>').val(product.id).text(product.name));
});
}
});
}
});
$(document).on('change', 'select[name="product_category"]', function() {
fetchProducts($(this));
});
$(document).on('input', '.quantity-input, .discount-input', function() {
var row = $(this).closest('tr');
updateTotals(row);
});
function updateProductInfo(selectedOption) {
var row = selectedOption.closest('tr');
var sellPrice = parseFloat(selectedOption.data('sell-price'));
storeOriginalSellPrice(row, sellPrice);
row.find('.product-power-input').val(selectedOption.data('product-power'));
row.find('.buy-price-input').val(selectedOption.data('buy-price'));
row.find('.b2b-price-input').val(selectedOption.data('b2b-price'));
row.find('.special-price-input').val(selectedOption.data('special-price'));
row.find('.sell-price-input').val(sellPrice);
updateTotals(row);
}
function updateTotals(row) {
var quantity = parseInt(row.find('.quantity-input').val()) || 0;
var sellPrice = parseFloat(row.find('.sell-price-input').val()) || 0;
var discount = parseFloat(row.find('.discount-input').val()) || 0;
var originalSellPrice = parseFloat(row.data('original-sell-price'));
var discountedPrice = originalSellPrice - discount;
var totalPriceWithoutTax = quantity * discountedPrice;
var totalPriceWithTax = totalPriceWithoutTax * 1.21;
row.find('.total-price-without-tax-input').val(totalPriceWithoutTax.toFixed(2));
row.find('.total-price-with-tax-input').val(totalPriceWithTax.toFixed(2));
row.find('.sell-price-input').val(discountedPrice.toFixed(2));
updateTotalCalculations();
}
$(document).on('input', '.quantity-input, .discount-input', function() {
var row = $(this).closest('tr');
updateTotals(row);
});
$(document).on("change", "select.form-select", function() {
updateProductInfo($(this).find('option:selected'));
});
$(document).on("change", "select[name='product_category']", function() {
fetchProducts($(this));
});
$(".add-row-btn").click(function() {
var newRow = '<tr>' +
'<td>' +
'<select name="product_category" class="border border-gray-400 px-4 py-2 text-left w-full">' +
'<option disabled selected><?php echo $lang['products']['category_filter']; ?></option>' +
'</select>' +
'</td>' +
'<td>' +
'<select class="form-select border border-gray-400 px-4 py-2 text-left w-full" disabled>' +
'<option disabled selected><?php echo $lang['products']['select_product']; ?></option>' +
'</select>' +
'</td>' +
'<td class="px-1 py-2 text-center"><input type="text" class="quantity-input rounded w-16 text-center"></td>' +
'<td class="px-1 py-2 text-center"><input type="text" class="rounded w-16 text-center sell-price-input bg-gray-200" readonly></td>' +
'<td class="px-1 py-2 text-center"><input type="text" class="total-price-without-tax-input rounded w-24 text-center bg-gray-200" readonly></td>' +
'<td class="px-1 py-2 text-center"><input type="text" class="total-price-with-tax-input rounded w-24 text-center bg-gray-200" readonly></td>' +
'<td class="px-1 py-2 text-center"><input type="text" class="discount-input rounded w-24 text-center"></td>' +
'<td class="px-1 py-2 text-center"><input type="text" class="buy-price-input rounded w-20 text-center bg-gray-200" readonly></td>' +
'<td class="px-1 py-2 text-center"><input type="text" class="rounded w-20 text-center b2b-price-input bg-gray-200" readonly></td>' +
'<td class="px-1 py-2 text-center"><input type="text" class="rounded w-20 text-center special-price-input bg-gray-200" readonly></td>' +
'<td class="px-1 py-2 text-center"><input type="text" class="rounded w-20 text-center product-power-input bg-gray-200" readonly></td>' +
'<td class="px-4 py-2 text-center">' +
'<button class="bg-red-500 hover:bg-red-700 text-white font-bold rounded delete-row-btn py-1 px-3 rounded">-</button>' +
'</td>' +
'</tr>';
var newRowElement = $(newRow);
$("#proposal-table tbody").append(newRowElement);
var categoryDropdown = newRowElement.find('select[name="product_category"]');
fetchCategories(categoryDropdown);
});
$(document).on("click", ".delete-row-btn", function() {
$(this).closest("tr").remove();
updateTotalCalculations();
});
$(document).on("change", ".support-checkbox", function() {
updateTotalCalculations();
});
$("select[name='product_category']").each(function() {
fetchCategories($(this));
});
updateTotalCalculations();
});
function updateTotalCalculations() {
var totalWithoutTax = 0;
var totalWithTax = 0;
var totalTax = 0;
var totalDiscount = 0;
var totalPower = 0;
var support = 0;
$('#proposal-table tbody tr').each(function() {
var totalPriceWithoutTax = parseFloat($(this).find('.total-price-without-tax-input').val()) || 0;
var totalPriceWithTax = parseFloat($(this).find('.total-price-with-tax-input').val()) || 0;
var discount = parseFloat($(this).find('.discount-input').val()) || 0;
var power = parseFloat($(this).find('.product-power-input').val()) || 0;
var quantity = parseFloat($(this).find('.quantity-input').val()) || 0;
totalWithoutTax += totalPriceWithoutTax;
totalWithTax += totalPriceWithTax;
totalTax += totalPriceWithTax - totalPriceWithoutTax;
totalDiscount += discount * quantity;
totalPower += power * quantity / 1000;
});
support = (totalPower <= 10) ? totalPower * 323 : (10 * 323);
$('.support').val(support.toFixed(2));
$('.support').val(support.toFixed(2));
var priceWithSupport = totalWithTax - support;
$('.fullsupport').val(priceWithSupport.toFixed(2));
$('.total-price-without-tax').val(totalWithoutTax.toFixed(2));
$('.total-price-with-tax').val(totalWithTax.toFixed(2));
$('.total-tax').val(totalTax.toFixed(2));
$('.total-discount').val(totalDiscount.toFixed(2));
$('.total-discount-percentage').val(((totalDiscount / (totalDiscount + totalWithTax)) * 100).toFixed(2) + '%');
$('.total-power').val(totalPower.toFixed(2));
}
</script>
</body>
</html>
mysql:
Table: "proposal_templates"
All columns:(
`id` int(11) NOT NULL,
`template_name`
`product_category` FOREIGN KEY (category table, name)
`product_name` FOREIGN KEY (products table, product_name)
`product_quantity`
`product_discount`
)
Table: "products"
All columns: (
id INT AUTO_INCREMENT PRIMARY KEY,
product_code VARCHAR(255) NOT NULL,
product_name VARCHAR(255) NOT NULL,
product_power VARCHAR(255) NOT NULL,
product_category VARCHAR(49) NOT NULL,
product_quantity VARCHAR(49) NOT NULL,
product_status VARCHAR(49) NOT NULL,
buy_price DECIMAL(10, 2),
sell_price DECIMAL(10, 2),
b2b_price DECIMAL(10, 2),
special_price DECIMAL(10, 2),
product_description TEXT,
FOREIGN KEY (product_category) REFERENCES categories(name)
)