Como fazer um orçamento multiusuário?

[Frank K Hosaka]

O orçamento é apenas uma tabuada do tipo 2 x 1 = 2, qualquer um pode pegar o celular, abrir o aplicativo calculadora, e executa a operação.

A dor de cabeça começa quando você quer gravar essa operação num banco de dados.

Imagina dois usuários: Frank e Yudi.

Frank, 2 x 1 = 2

Yudi, 2 x 1 = 2

Qual o melhor meio de registrar essas tabuadas? Eu vou criar uma tabela para cada usuário ou coloco tudo numa só tabela?

A minha intuição diz que eu devo usar apenas uma tabela, assim:

tbtabuada

id

usuário

quantidade

preço

A chave do problema é o id da tbtabuada. Imagina que o Frank e o Yudi abrem o celular ao mesmo tempo na opção nova tabuada, os dois recebem o mesmo id, vamos supor o 1. O Frank salva a tabuada primeiro com o id=1, e dois minutos depois o Yudi salva a tabuada dele. Ou seja, a tabuada do Frank vai desaparecer.

Para contornar esse desastre, a ideia é a tabela fornecer o id depois que as informações forem fornecidas. Mas o que acontece se os dois salvarem as informações ao mesmo tempo?

[Gemini]

To effectively manage multi-user budgeting and address the concurrency issue you've described, consider implementing the following approach:

**1. Utilize Unique Transaction IDs and Optimistic Locking:**

- Employ a unique transaction ID (e.g., UUID) for each budget operation to ensure data integrity.

- Implement optimistic locking, a concurrency control mechanism, to prevent conflicts when multiple users attempt to modify the same data simultaneously.

**2. Database Schema with Transaction IDs:**

- Modify your database schema to include a `transaction_id` column alongside the existing `id`, `usuario`, `quantidade`, and `preço` columns in the `tbtabuada` table.

**3. Generate Unique Transaction IDs:**

- Generate a unique transaction ID for each new budget operation. This can be done using a UUID generator or a similar mechanism.

**4. Insert with Optimistic Locking:**

- When a user saves a budget operation, attempt to insert a new record into the `tbtabuada` table with the generated transaction ID, user information, quantity, and price.

- Use an `INSERT ... SELECT` statement to perform the insertion while checking for a conflicting record based on the `usuario`, `quantidade`, and `preço` values.

**5. Conflict Resolution and Retry:**

- If an insert attempt fails due to a conflict (i.e., another user has already saved a record with the same `usuario`, `quantidade`, and `preço`), inform the user of the conflict and prompt them to retry the operation.

- Upon a retry, generate a new unique transaction ID and repeat the insert attempt with the updated transaction ID.

**6. Successful Insert and Response:**

- If the insert is successful, the new record with the generated transaction ID will be added to the `tbtabuada` table.

- Provide a success message to the user indicating that their budget operation has been saved.

**7. Handling Concurrent Requests:**

- In the scenario where both Frank and Yudi attempt to save their budget operations simultaneously, one of the inserts will succeed, and the other will fail due to the conflict.

- The user associated with the failed insert will be notified of the conflict and prompted to retry, while the user associated with the successful insert will receive a confirmation message.

- Upon retrying, the user's insert attempt will generate a new unique transaction ID, increasing the likelihood of success in the subsequent attempt.

By implementing this approach, you can effectively manage multi-user budgeting, prevent data conflicts, and ensure that each user's budget operation is accurately recorded in the database.

[Frank K Hosaka]

Eu não segui a orientação da Gemini, eu acho difícil trabalhar com o id da tabela, eu imagino que trabalhar com o transaction id deve ser mais difícil. Por falar em id, nessa semana aprendi a diferenciar o id da tabela products da tabela users, assim:

$orders=(new Conn)->select("*,orders.id as orderId from orders 
	inner join users on users.id=orders.id_user $where");

Se você inverter a ordem, o MySQL vai considerar como erro fatal.

Para trabalhar no ambiente multiusuário, eu criei o campo id_user na tabela orders, e no formulário embuti uma regra em que só o usuário que criou o pedido é que pode editá-lo.

A seguir os principais códigos da minha solução:

arquivo /Controllers/OrderController.php
<?php
class OrderController
{
  public function aqt()
  {
    $aqt=$_POST['aqt'];
    $id=$_POST['id'];
    $price=(new Conn)->select("price from orderHist where id=$id")[0]->price;
    $subtotal=$aqt*$price;
    (new Conn)->update("orderHist set qt=$aqt, subtotal=$subtotal where id=$id");
    $this->index($_SESSION['order']);
  }
  
  public function cancel($order)
  {
    (new Conn)->update("orders set status='canceled' where id=$order");
    return $this->index($order);
  }
  
  public function delete($id)
  {
    (new Conn)->delete("orderHist where id=$id");
    return $this->index($_SESSION['order']);
  }
  
  public function done($order)
  {
    (new Conn)->update("orders set status='done' where id=$order");
    return $this->index($order);
  }
  
  public function history($reference = null)
  {
    $orders=(new Conn)->select("* from orders inner join users on users.id=orders.id_user");
    $_SESSION['reference'] = (isset($_SESSION['reference'])) ? $_SESSION['reference'] : count($orders);
    if($reference=="all")
    {
      	$where="";
      	$status="all";
    } else {
    	$reference = ($reference) ? $reference : 0;
    	if($_SESSION['reference']>20)
    	{
      	$_SESSION['reference'] = $_SESSION['reference'] + $reference;
    	}
      	$newReference=$_SESSION['reference'];
    	$where="where orders.id < $newReference and status='waiting' limit 20";
      	$status="waiting";
    }
    $orders=(new Conn)->select("*,orders.id as orderId from orders inner join users on users.id=orders.id_user $where");
    return view('history',['status'=>$status,'orders'=>$orders]);
  }
  
  public function index($order = null)
  {
    $prod=(new Conn)->select("* from products order by name");
    $today=date('Y-m-d');
    $where="";
    if($order) 
    {
      	$vr['total']=$this->totalCalc($order);
    	$where="where id=$order";
        $vr['id_prod']=(isset($_SESSION['id_prod'])) ? $_SESSION['id_prod'] : null;
        if($vr['id_prod'])
        {
        	$id_prod=$vr['id_prod'];
          	$_SESSION['id_prod']=null;
            $sale=(new Conn)->select("sale from products where id=$id_prod")[0]->sale;
          	(new Conn)->insert("orderHist (id_order,id_prod,qt,price,subtotal) values ($order,$id_prod,1,$sale,$sale)");
            return $this->index($order);
            } else {
            $vr=['order'=>'','orderDay'=>$today,'totalOrder'=>'',
                'id_prod'=>'','prod'=>null,'price'=>'','subtotal'=>''];
            }
        }
        $order=(new Conn)->select("* from orders $where order by id desc");
    	if($order)
        {
           	$order=$order[0];
		   	$orderId=$order->id;
    		$userId=$order->id_user;
      		$vr['total']=$this->totalCalc($orderId);
          	if($userId==$_SESSION['id'])
         	{
            	$vr['user']=null;
          	} else {
            	$vr['user']=(new Conn)->select("name from users where id=$userId")[0]->name;
         	}
        } else {
          	$id_user=$_SESSION['id'];
          	(new Conn)->insert("orders (orderDay,id_user) values ('$today',$id_user)");
          	return $this->index();
        }
        $vr['order']=$order->id;
        $_SESSION['order']=$order->id;
        $vr['orderDay']=$order->orderDay;
        $vr['total']=$order->total;
    	$vr['status']=$order->status;
    	$orderHist=$_SESSION['hist'];
        return view('orders',['vr'=>$vr,'orderHist'=>$orderHist,'prod'=>$prod]);
  }
  
  public function new()
  {
    $orderDay=date('Y-m-d');
    $userId=$_SESSION['id'];
    (new Conn)->insert("orders (orderDay,id_user) values ('$orderDay',$userId)");
    $last_id=(new Conn)->lastInsertId();
    $verify=(new Conn)->select("id from orders order by id desc")[0]->id;
    $last_id=($verify<$last_id) ? $verify : $last_id;
    $this->index($last_id);
  }
  
  public function select()
  {
    $prod=$_POST['prod'];
    $length=strlen($prod);
    $position=strpos($prod,":");
    if($position==0) 
    {
    	$_SESSION['filter']=$prod;
        return header('location:?ProductController.filter');
    }
    $_SESSION['id_prod']=substr($prod,$position+1,$length-$position);
    return $this->index($_SESSION['order']);
  }
  
  public function totalCalc($orderId)
  {
    $_SESSION['hist']=(new Conn)->select("orderHist.id,orderHist.id_order, orderHist.id_prod, 
         orderHist.qt as qt, orderHist.price as price, orderHist.subtotal, products.name as prod
         from orderHist inner join products on orderHist.id_prod = products.id
         and orderHist.id_order = $orderId order by orderHist.id");
    $total=(new Conn)->select("sum(subtotal) as total from orderHist where id_order= $orderId")[0];
    $newTotal=($total->total) ? $total->total : 0;
    (new Conn)->update("orders set total = $newTotal where id=$orderId");
    return $newTotal;  
  }
  
  public function waiting($order)
  {
    (new Conn)->update("orders set status='waiting' where id=$order");
    return $this->index($order);
  }
}


arquivo /Controllers/ProductController.php
<?php
class ProductController
{
  public function changeSale()
  {
    $newSale=$_POST['sale'];
    $id=$_POST['id'];
    (new Conn)->update("products set sale=$newSale where id=$id");
    $this->index();
  }
  
  public function filter()
  {
  	$filter=$_POST['filter'];
    $products=(new Conn)->select("* from products where name like '%$filter%' order by name");
    return view('products',['products'=>$products]);
  }
  
  public function index()
  {
    $products=(new Conn)->select("* from products order by name");
    return view('products',['products'=>$products]);
  }
  
  public function select($id)
  {
    echo "Produto selecionado $id. Rotina em andamento";exit;
  }
}

arquivo /Views/orders.php
<?php require('menu.php'); ?>
<script>btMenu.innerHTML='Orders';document.title='Orders'</script>
<table class='table table-striped table-sm'>
<tr class=fw-semibold>
	<td><a href=?OrderController.history>History</a>
	<td class=text-danger>Order <?=$vr['order']?> of <?=$vr['orderDay']?></td>
  	<?php if(!$vr['status']): ?>
  		<td><a href=?OrderController.waiting.<?=$vr['order']?>>Send_Order</a>
    <?php endif; ?>
	<td><a href=?OrderController.new> New</a>
</table>
<table class="table table-striped table-sm">
<tr class="fw-semibold">
	<td>Product
	<td class=text-end>Qt
	<td class=text-end>Price
	<td class=text-end>Total
<?php foreach($orderHist as $item): ?>
<tr>
	<td><?=$item->prod?>
	<td class=text-end>
		<form method=post action=?OrderController.aqt>
		<?php if($item->qt==intval($item->qt)): ?>
			<input name=aqt value='<?=$item->qt?>' onchange=submit() size=1 
				style=text-align:right;border:none;background:transparent>
		<?php else: ?>
			<input name=aqt value='<?=$item->qt?>' onchange=submit() size=1 
			style=text-align:right;border:none;background:transparent>
		<?php endif; ?>
		<input type=hidden name=id value=<?=$item->id?>>
		</form>
	<td class=text-end><a href=?OrderController.delete.<?=$item->id?>><?=number_format($item->price,2)?></a>
	<td class=text-end><?=number_format($item->subtotal,2)?>
<?php endforeach; ?>
<tr>
<?php if($vr['status']): ?>
  <td><spam class=text-danger>Status: <b><?=$vr['status']?></b></spam>
<?php goto gambiarra; endif;?>
<?php if(!isset($vr['name'])):?>
	<td>
	<?php if($vr['user']): ?>
      <spam class=text-danger>This order may only be modified by <b><?=$vr['user']?></b></spam>
    <?php else: ?>
	<form action=?OrderController.select method=post>
	<input list=lsprod name=prod placeholder=product onchange=submit() autofocus>
	<datalist id='lsprod'>
	<?php foreach($prod as $pr): ?>
	<option value='<?=$pr->name?> :<?=$pr->id?>'>
	<?php endforeach; ?>
	</datalist>
	</form>
    <?php endif; ?>
<?php else: ?>
	<td><?=$vr['name']?>
	<?php if(!isset($vr['qt'])): ?>
      
arquivo /Views/history.php
<?php include 'menu.php'; ?>
<script>btmenu.innerHTML='Orders Hystory';document.title='Orders Hystory'</script>
<main>
Show orders by <?=$status?>
<table class='table table-striped linha'>
<tr class=fw-semibold>
<td style=width:20px>Day
<td style=width:40px>Order
<td>Collaborator
<td class=text-end>Total
<td>Status
<td>Change To
<?php foreach($orders as $item) : ?>
    <tr><td><?=date('y-m-d',strtotime($item->orderDay))?>
    <td class=text-end><a href=?OrderController.index.<?=$item->orderId?>><?=$item->orderId?></a>
    <td><?=$item->name?>
    <td class=text-end><?=number_format($item->total,2)?>
    <td><?=$item->status?>
    <?php if($item->status): ?>
    <td><a href=?OrderController.done.<?=$item->orderId?>>Done</a>&nbsp;
        <a href=?OrderController.cancel.<?=$item->orderId?>>Cancel</a>
    <?php endif; ?>
<?php endforeach; ?>
</table>
<a href="?OrderController.history.-19">Previus</a>&nbsp;
<a href="?OrderController.index">Return</a>&nbsp;
<a href="?OrderController.history.19">Posterior</a>
<a href="?OrderController.history.all">All Hystory</a>
</main>
		<td class=text-end>
		<form action=?ControleOrcamento.qt method=post>
		<input id=qt name=qt onchange=submit() size=1 placeholder=qt autofocus>
		<td class=text-end><?=$vr['price']?><td>
		</form>
	<?php else: ?>
		<td><?=$vr['qt']?>
		<td class=text-end><?=$vr['price']?>
        <td class=text-end><?=$vr['subtotal']?>
	<?php endif; gambiarra: ?>
<?php endif; ?>
	<tr><td><td><td><td class='fw-semibod text-end'><?=number_format($vr['total'],2)?>
</table>

Agora que eu publiquei os códigos eu vi que defini o método select tanto no OrderController bem como no ProductController, você pode desconsiderar o método select no ProductController. Essa é uma dúvida que eu não sei resolver, no formulário do pedido, eu tenho um <input type=list> que seleciona os produtos, eu não sabia se deveria criar o método no controle do pedido ou do produto, acabei fazendo nos dois.

Editado Julho 25 por Frank K Hosaka