Sorry in advance for the long of the post but i can't explain it in other way, considering that English is not my native language :S
I create it (With the help of the WWW) a software with php/mysql for the dispatch of products with inventory and the problem I have is that I don't know how to calculate the cost of the dispatch when I have entries of the same product with different price.
My DB looks like this:
Productotable: only maintains id, name and quantity of the product of the inventoryIngresotable: it register the entry of the products with their respective prices at the moment of the purchase (Only table that register prices)Devoprovtable: it register the return to the suppliersTransaccionestable: it register the dispatch with an id, where is headed, id of the product, quantity of the product, comment, who made it, date y id_tra it register a 1 for dispatch and a 2 for return.
With PHP, I register this way the dispatch
if(isset($_POST['btnnewsal'])){
//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());
}
}
Mi form for getting the products it 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 get his data with this js and it works with jqueryUI.autocomplete 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
<?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;
}
and i came with this for the reports but it does not do what i need it only show the total of products that have been dispatched
require_once 'config.php';
if(isset($_POST['generar'])){
$start = $_POST['desde'];
$end = $_POST['hasta'];
$centro = $_POST['cbocod'];
if($centro==-1){
$qryMem = "SELECT calendar.datefield AS DATE, IFNULL(SUM(transacciones.can_ven),0) AS total_sales FROM transacciones RIGHT JOIN calendar ON (DATE(transacciones.cre_dep) = calendar.datefield) WHERE calendar.datefield BETWEEN '$start' AND '$end' AND transacciones.id_tra=1 GROUP BY DATE";
$memresult = mysqli_query($con, $qryMem) or die(mysqli_error());
I hope you guys can help me to show the cost of the dispatch with the multiple prices that can have a same product because i been tryng for days and i really dont know how to do it. I am willing to change the DB or anything to resolve this
