Inventory with Variable Price Products


I have created a php / mysql system for dispatch with product inventory and the problem I have is that I do not know how to calculate the cost of the dispatch when I have income from the same product with different prices.

My database is composed as follows:

Product Table : Only Keeps id, name and quantity of the products in the inventory

Income Table : Registers the income of products with their respective price when buying the supplier (Only table that registers prices)

Devoprov table : Registers returns to the provider

Transactions table: Registers the dispatches with an id, the center to which it is sent, id of the product that dispatches, quantity of the product that dispatches, comment, responsible, date and id_tra registers a 1 for dispatch and 2 for return.

With php register in this way the dispatches

  if(isset($_POST['btnnewsal'])){   // preguntar si presiono el boton

     //Array Productos
    require_once 'config.php';
    for ($i=0; $i < count($_POST['itemNo']); $i++ ) {
    $idventa = $_POST['venta'];
    $idpro = $_POST['itemNo'][$i];
    $res = $_SESSION['nombre'];
    $cre = "NOW()";
    $comentario = $_POST['notes'];
    $centro = $_POST['cbocod'];
    $canpro = $_POST['quantity'][$i];
    $qryUpt = "INSERT INTO transacciones VALUES ('$idventa','$centro','$idpro','$canpro','$comentario','$res',$cre,1)";
    mysqli_query($con,$qryUpt) or die(mysqli_error());

    //Disminuir inventario
    for ($i=0; $i < count($_POST['itemNo']); $i++ ) {
    $idpro = $_POST['itemNo'][$i];
    $canpro = $_POST['quantity'][$i];
    $qryUpt = "UPDATE producto SET uni_pro = uni_pro-'$canpro' WHERE id_pro='$idpro'";
    mysqli_query($con,$qryUpt) or die(mysqli_error());


My form to rescue the products looks like this (despacho.php)

<form class="form" method="post" action="despacho.php">
        <div class="form-group">
               <label for="cbocod" class="col-sm-2 control-label">Centro Medico:</label>
              <div class="col-sm-6">
                <select class="form-control select2" id="cbocod" name="cbocod" style="width: 50%;">
        <div class="form-group">
               <label for="cbocod" class="col-sm-2 control-label">Codigo Transaccion:</label>
              <div class="col-md-1">
                <input type="text" style="text-align: center;" class="form-control" id="venta" name="venta" value="<?php $u->contarventas(); ?>" readonly>
        <div class="col-xs-12 col-sm-12 col-md-12 col-lg-12">
            <table id="vender" width="90%" class="table table-bordered table-hover">
                        <th width="2%"><input id="check_all" class="formcontrol" type="checkbox"/></th>
                        <th width="15%">Codigo</th>
                        <th width="35%">Nombre</th> <!-- 34% -->
                        <th width="10%">Stock</th>
                        <th width="10%">Cantidad</th>
                        <td><input class="case" type="checkbox"/></td>
                        <td><input type="text" data-type="id_pro" name="itemNo[]" id="itemNo_1" class="form-control autocomplete_txt changesNo" autocomplete="off" required></td>
                        <td><input type="text" data-type="nom_pro" name="itemName[]" id="itemName_1" class="form-control autocomplete_txt changesNo" autocomplete="off" required></td>
                        <td><input type="number" name="stock[]" id="stock_1" class="form-control changesNo" autocomplete="off" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;" readonly></td>
                        <td><input type="number" name="quantity[]" id="quantity_1" class="form-control changesNo" autocomplete="off" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;" required></td>


        <div class="col-xs-12 col-sm-3 col-md-3 col-lg-3">
            <button class="btn btn-danger delete" type="button">- Borrar</button>
            <button class="btn btn-success addmore" type="button">+ Agregar Producto</button>

    <div class="row">

        <div class="col-xs-12 col-sm-12 col-md-12 col-lg-12">
            <div class="form-group">
                <label for="notes" class="col-sm-1 control-label">Comentarios:</label>
                <div class="col-sm-6">
                    <input type="text" class="form-control" id="notes" name="notes" placeholder="Comentarios">
        <div class="col-xs-11 col-sm-11 col-md-11 col-lg-11">
                <button type="reset" class="btn btn-default">Limpiar</button>
                <button type="submit" id="btnnewsal" name="btnnewsal" class="btn btn-info pull-right">Despachar</button>
        </div><!-- /.box-footer -->

And this form is filled with this javascript that works with jqueyUI and ajax


type = $(this).data('type');

if(type =='id_pro' )autoTypeNo=0;
if(type =='nom_pro' )autoTypeNo=1;

    source: function( request, response ) {
            url : 'ajax.php',
            dataType: "json",
            method: 'post',
            data: {
               name_startsWith: request.term,
               type: type
             success: function( data ) {
                 response( $.map( data, function( item ) {
                    var code = item.split("|");
                    return {
                        label: code[autoTypeNo],
                        value: code[autoTypeNo],
                        data : item
    autoFocus: true,            
    minLength: 0,
    select: function( event, ui ) {
        var names ="|");                        
        id_arr = $(this).attr('id');
        id = id_arr.split("_");

}); });

and my ajax.php looks like this

require_once 'config.php';
    $type = $_POST['type'];
    $name = $_POST['name_startsWith'];
    $query = "SELECT id_pro, nom_pro, uni_pro FROM producto where       UPPER($type) LIKE '".strtoupper($name)."%'";
    $result = mysqli_query($con, $query);
    $data = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $name = $row['id_pro'].'|'.$row['nom_pro'].'|'.$row['uni_pro'];
        array_push($data, $name);
    echo json_encode($data);exit;

I hope you can really help me to be able to show the cost of the office with the different types of prices that may be for the same product because I really have days in this and I can not figure out how to do it.

asked by anguswild 22.03.2016 в 22:19

1 answer


I think if you have many products with the same name but of different price, which do not make any difference in your database how to handle a code or relate the products in a table, it is going to be complicated that you can do a summation , which is what I understood with the cost of the office. You want to know how much has produced the office, the branch, or equivalent. I also understand that you have a table of inventories, therefore, what you are looking for is not the quantity in existence, but knowing what to differentiate at the time of doing the summation. My recommendation groups them to get totals.

answered by 23.03.2016 в 07:14