Issue in pharmacy stock

I am facing another issue. i have pharmacy stock with two different purchase price . for example medicine name “ABC” entered in stock with 50 qty and 50 USD per unit price. and 10 days later, same medicine “ABC” need to be entered in stock but due to inflation price become 60 USD per unit. everything is fine at this point as both the purchasing have different invoice no to differentiate between them.

but the problem start when customer purchase medicine and operator enter details in following below code form . The gfg.php fetch data from stock table . But the issue is there can be more than one entry of same name medicine “ABC” in stock table with different purchase date and invoice no .
Now I want to learn from you that is it possible that in customer bill, when operator select medicine name, then medicine should be select from first entered stock. And when all quantity of first stock stock ended than it automatically select from 2nd entered stock and so on…

is it possible to manage or not ???

<table class="table table-bordered">
                    <thead class="table-success" style="background-color: #3fbbc0;">
                      <tr>
                        
                        <th width="15%"><center>Medicine Name</th>                       
			<th width="10%"><center>Qty</th>                       
			<th width="10%"><center>Purchase Price</th>
			<th width="10%"><center>Sell Price</th>
			<th width="10%"><center>Discount</th>			
			<th width="10%"><center>Total</th>			
			<th width="20%"><center>Remarks</th>
			<th width="5%"></th>

                                               
                         <button type="button" class="btn btn-sm btn-success" onclick="BtnAdd()">Add Item</button>                         
                        </th>
</tr>
                    </thead>
                    <tbody id="TBody">
                      <tr id="TRow" class="d-none">

 <td><Select  class="scode form-control text-end" name="scode[]" id = "tutorial_name"  required onchange="GetDetail(this.closest('tr'))">
<option value="">Select Product</option>
	<?php
	include('db.php');
	$sql = mysqli_query($con,"SELECT * FROM procd2");
	while($row=mysqli_fetch_array($sql))
	{
	echo '<option value="'.$row['pro1'].'">'.$row['pro1'].'</option>';
	} ?></select>



</td>

       
      	<td><input type="text" class="qty form-control text-end" name="qty[]" id="ccc" onfocus="Calc(this);"></td>
     <td><input type="text" class="purprice form-control text-end" name="purprice[]" id="ddd1"   onfocus="Calc(this);" readonly style="background-color: #3fbbc0;"></td>
	<td><input type="text" class="price form-control text-end" name="price1[]" id="ddd"   onfocus="Calc(this);" readonly style="background-color: #3fbbc0;"></td>
	<td><input type="text" class="discunt form-control text-end" name="discunt[]"  id="eee" onchange="Calc(this);"></td>
   	<td><input type="text" class="tot4 form-control text-end" name="tot4[]" id="fff"  readonly style="background-color: #3fbbc0;"></td>			
     
      
           <td><input type="text" class="form-control text-end" name="remarks3[]"  id="zzz" ></td>
		<td class="NoPrint"><button type="button" class="btn btn-success"  style="line-height: 1;" onclick="BtnDel(this)">x</button></td>
			
                      </tr>   </tbody> </table>

below script which fetch auto fill value from database file gfg.php

<script>

		// onkeyup event will occur when the user
// release the key and calls the function
// assigned to this event
function GetDetail(row) {
  let str = row.querySelector(".scode").value;
  if (str.length == 0) {
      row.querySelector(".qty").value = "";
   row.querySelector(".purprice").value = "";
    row.querySelector(".price").value = "";
    row.querySelector(".discunt").value = "";
    row.querySelector(".tot4").value = "";
	
    return;
  } else {
    // Creates a new XMLHttpRequest object
    var xmlhttp = new XMLHttpRequest();
    xmlhttp.onreadystatechange = function() {

      // Defines a function to be called when
      // the readyState property changes
      if (this.readyState == 4 &&
        this.status == 200) {

        // Typical action to be performed
        // when the document is ready
        var myObj = JSON.parse(this.responseText);

        // Returns the response data as a
        // string and store this array in
        // a variable assign the value
        // received to first name input field

       
        row.querySelector(".qty").value = myObj[0];
    row.querySelector(".purprice").value = myObj[0];
        row.querySelector(".price").value = myObj[1];
       row.querySelector(".discunt").value = myObj[2];	
       row.querySelector(".tot4").value = myObj[3];
	
      }
    };

    // xhttp.open("GET", "filename", true);
    xmlhttp.open("GET", "gfg.php?user_id=" + str, true);   // value came from gfg.php

    // Sends the request to the server
    xmlhttp.send();
  }
}
	</script>

I don’t know if there is a “one way to do it” scenario, but one option is to use what are called “variants” of the product (aka stock). Let’s say that the stock has a product ID of “ABC”. The first purchase at 50USD might be labeled with a product ID of “ABC-50” where “-50” is the ID of the variant. Next time it is purchased at 60USD it would be “ABC-60”.

You can tell both are for the same medicine “ABC” but your system can see they are two variants of that same product. You can then keep track of quantities per variant and if the quantity is ever exceeding stock of one, you can have logic to switch to the other. If the variants also have some kind of “order” to them then you can easily see that if “ABC-50” is order 1 and it runs out, go to whatever next in order which would be “ABC-60”.

This is just one way of doing it. It is really up to your business rules. Businesses handle this differently.

1 Like

You would consume and record quantities of products based on the stock_id instead of the product_id, starting with the lowest (oldest) stock_id having a current stock quantity > 0.

If an order consists of more than one stock entry, you would insert a separate row into the order_items table for each stock_id and quantity from that stock entry.

1 Like

Yes, assuming inventory is removed from stock when the order is placed there would probably be only a few records that have the item in stock and so I used qty not empty as a query condition ordered by the date. As he wants price based on the invoice no and the qty available and each invoice no. listed separate for the client with subtotals, it was easiest to do the calculations within the query loop, manually subtracting qty as I calculate the subtotals and build his table rows for each invoice number. I then returned the tr rows and a few extra values as the response to the call. It may not be pretty but it got the job done.

$content='';
$tpurprice='';
$tprice='';
$tdiscunt='';
$total='';	
if (!empty($scode) && !empty($qty)) {	

	$content .= '<tr class="scode_'.$scode.'" style="background-color: #7AE1E6;">
		<th class="text-center">Date</th>                       
		<th class="text-center">Qty</th>                       
		<th class="text-center">Purchase Price</th>
		<th class="text-center">Sell Price</th>
		<th class="text-center">Discount</th>			
		<th class="text-center">SubTotal</th>	
		<th class="text-center">Invoice No.</th>		
		<th colspan="2" class="text-center">Total</th>
	</tr>';
		
	$purprice=0;
	$price=0;	
	$discunt=0;
	$total=0;	 
	$outgoing=0;
	$inventory=0;
	$r=0;

	$query = mysqli_query($con, "SELECT id, invocieno, `date`, qty, purprice, price, discunt, tot4 FROM ph31 WHERE qty != '' AND scode = '$scode' ORDER BY `date`");
	while($row = mysqli_fetch_array($query)){
		$r++;
		
		$rqty = ($row['qty'] >= $qty ? $qty : $row['qty']);
		$qty = ($row['qty'] >= $qty ? 0 : $qty - $row['qty']);
		$subtotal = (!empty($row['price']) && !empty($rqty) ? $rqty * $row['price'] : 0);	
		
		$outgoing += $rqty;
		$inventory += $row['qty'];	
		
		$purprice += (!empty($row['purprice']) ? $row['purprice'] : 0);
		$price += (!empty($row['price']) ? $row['price'] : 0);
		$discunt += (!empty($row['discunt']) ? $row['discunt'] : 0); 
		
		$tpurprice = ((empty($qty) || $outgoing == $inventory) && !empty($purprice) ? $purprice/$r : 0);
		$tprice = ((empty($qty) || $outgoing == $inventory) && !empty($price) ? $price/$r : 0);
		$tdiscunt = ((empty($qty) || $outgoing == $inventory) && !empty($discunt) ? $discunt/$r : 0);
		
		$total += $subtotal;
		$totalShown = ((empty($qty) || $outgoing == $inventory) ? '<input type="text" class="form-control text-end" name="total[]" value="'.$total.'">' : '&nbsp;'); 
		
		if(empty($done)): 
			$content .= '<tr class="scode_'.$scode.'">
				<td><input type="text" class="form-control text-end" name="date[]" value="'.$row['date'].'" readonly></td>
				<td><input type="text" class="form-control text-end" name="qty[]" value="'.$rqty.'" readonly></td>
				<td><input type="text" class="form-control text-end" name="purprice[]" value="'.$row['purprice'].'" readonly></td>
				<td><input type="text" class="form-control text-end" name="price[]" value="'.$row['price'].'" readonly></td>
				<td><input type="text" class="form-control text-end" name="discunt[]" value="'.$row['discunt'].'" readonly></td>
				<td><input type="text" class="form-control text-end" name="subtotal[]" value="'.$subtotal.'"></td>
				<td>
					<input type="hidden" name="ph31_id[]" value="'.$row['id'].'">
					<input type="text" class="form-control text-end" name="invoiceno[]" value="'.$row['invocieno'].'" readonly>
				</td>			 	
				<td colspan="2">'.$totalShown.'</td>	
			</tr>';
		endif;
		$done = (empty($qty) ? true : false);

	}
	
	
	
}

// Send in JSON encoded form
echo json_encode(array($content,$tpurprice,$tprice,$tdiscunt,$total));
?>

1 Like

Thanks alot sir! its solved the issue

You probably noticed that I added a hidden input for ph31_id so that the form processing code would have that invoice no. record id to subtract qty from the record.

1 Like

yup, I noticed it sir!