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%;">
<?php
$u->llenarcombocentros();
?>
</select>
</div>
</div>
<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>
</div>
<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">
<thead>
<tr>
<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>
</tr>
</thead>
<tbody>
<tr>
<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>
</tr>
</tbody>
</table>
</div>
<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>
</div>
<br><br>
<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>
</div>
</div>
<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 -->
</div>
</form>
And this form is filled with this javascript that works with jqueyUI and ajax
auto.js
$(document).on('focus','.autocomplete_txt',function(){
type = $(this).data('type');
if(type =='id_pro' )autoTypeNo=0;
if(type =='nom_pro' )autoTypeNo=1;
$(this).autocomplete({
source: function( request, response ) {
$.ajax({
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 = ui.item.data.split("|");
id_arr = $(this).attr('id');
id = id_arr.split("_");
$('#itemNo_'+id[1]).val(names[0]);
$('#itemName_'+id[1]).val(names[1]);
$('#stock_'+id[1]).val(names[2]);
$('#quantity_'+id[1]).val(1);
}
}); });
and my ajax.php looks like this
<?php
require_once 'config.php';
if(!empty($_POST['type'])){
$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.