Cakephp sql Update: add up old field value with value from form

Question

So I am relatively new to the MVC world. What I am doing at the moment is rewriting a php project in CakePHP2.0. Currently I am working on orders, more specifically the delivery of products/stock.

This is the previous sql code for a delivery of products:

$sqlSelect =("SELECT current_stock FROM stocks WHERE id IN (");
$sql = $con->prepare("UPDATE orders SET order_status=1 WHERE order_number=:orderNumber");
$sql2 = $con->prepare("UPDATE stocks SET current_stock= :stock + :delivered  WHERE id =     :stockID");

try {

$con->beginTransaction();

for($count=0; $count<=$_POST['count']; $count++) {

$idQuery [] ="?,";
$idArray []=($_POST['stock_id' .$count]);

}

$sqlSelect .= implode($idQuery);
$sqlSelect =trim($sqlSelect, ",") . ")"; // removes last comma from string, then closes  the bracket //
$stmt =$con->prepare($sqlSelect);
$stmt->execute($idArray);

while ($row=$stmt->fetch()) {

$stock = ($row['current_stock']);

$sql2->bindParam(':stock', $stock);
}

for($count=0; $count<=$_POST['count']; $count++) {

$sql2->bindParam(':delivered', $_POST['delivery_amount' .$count]);
$sql2->bindParam(':stockID', $_POST['stock_id' .$count]);
$sql2->execute();

}

$sql->bindParam(':orderNumber', $_POST['order_num'] );


$sql->execute();
$con->commit();
$con=null;

 echo "<script language=javascript>
    alert('Delivery confirmed. Your stocks have been adjusted.')
location.replace('placeorder2.php')
</script>";


} catch (Exeception $e) {

$con->rollback();
echo "Update Failed: " . $e->getMessage();

}
?>

What I would like to know is what is the easiest way to perform this the 'CakePHP' way?

My Associations are as follows: Product hasOne Stock, Product hasMany Order.

Answer 1

Right, I managed to get it working.

So in my Order Model, I created a method to loop through the $this->data hash retrieved from the OrdersController and apply my necessary logic, and return the rebuilt hash to the Controller.

class Order extends Model {

...

     public function finalizeOrder ($dataHash= array())
     {
         $stockHash = array();

         foreach ($dataHash['Stock'] as $key => $value) {

             $stockHash['Stock'][$key]['id'] = $value['id'];
             $stockHash['Stock'][$key]['current_stock'] = $value['current_stock'] + $value['delivered_units'];
         }

         foreach ($dataHash['Order'] as $key => $value) {

             $stockHash['Order'][$key]['delivered'] = $value['delivered'];
             $stockHash['Order'][$key]['id'] = $value['id'];
         }

         return $stockHash;
     }

}

My Controller looks like this:

class OrdersController extends AppController {
...

public function acceptDelivery() {

    $this->loadModel('Stock');

    $this->data = $this->Order->finalizeOrder($this->data);

    if ($this->Stock->saveAll($this->data['Stock'])) 
            {
        if ($this->Order->saveAll($this->data['Order'])) {
        $this->Session->setFlash('Delivery has been confirm. Your stock has been updated.');
        $this->redirect(array('action' => 'index'));
        }
    } else {
        $this->Session->setFlash('There was an error');
         $this->redirect(array('action' => 'index'));
    }

  }

}

My form (I have omitted some code) :

<? echo $this->Form->create('Order', array('action' => 'acceptDelivery')); ?>
<table class="gnr">
    <tr>
        <th class="product">
           Product Name
        </th>
        <th class="product" style="width: 5%">
            Qty 
        </th>
        <th class="product" style="width: 5%">
            Received
        </th>
    </tr>
    <? foreach ($order as $key => $value): ?>
    <tr>
        <td class="product">
            <? echo $value['Product']['product_name']; ?>
        </td>
                <td class="product">
            <? echo $value['Order']['order_quantity'];?>
        </td>       
        <td class="product">
            <? echo $this->Form->number('Stock.'.$key.'.delivered_units', array(
                'min' => '0', 'class' => 'val1', 'value' => 0
            )); ?>
        </td>
    </tr>

    <? echo $this->Form->hidden('Stock.'.$key.'.id', array(
                'value' => $value['Stock']['id'] )); ?>

    <? echo $this->Form->hidden('Order.'.$key.'.delivered', array(
                'value' => 1 )); ?>

    <? echo $this->Form->hidden('Order.'.$key.'.id', array(
                'value' => $value['Order']['id'] )); ?>

    <? echo $this->Form->hidden('Stock.'.$key.'.current_stock', array(
                'value' => $value['Stock']['current_stock'] )); ?> 
    <? endforeach; ?>
</table>
<? echo $this->Form->end('Confirm Order'); ?>