⚠ In case you've missed it, we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This is an archived forum. ⚠

  •     

profile picture

How do i insert a calculated field in a table?



Ralph Schipper

Ralph Schipper
  • profile picture
  • Member

Posted 08 November 2013 - 10:19 AM

Hello people,

I just found out aboud grocery crud, it looks great!
I will defenatly use this, as I am going to write a invoice system.

One question:
How do I manage calculated fields?
Lets say I want to add a record that has these fields:
qty, price, total.
So I enter the qty and price, how do I insert the calculated field "total"?.


Regards Ralph

raiethan

raiethan
  • profile picture
  • Member

Posted 09 November 2013 - 06:50 AM

use callback_column

$crud->callback_column('total',array($this,'total'));

 function total($value, $row)
{
                $quantity = $row->quantity;
                $price = $row->price;
                $total = $quantity * $price;
               
                return $total;

}


edramirez

edramirez
  • profile picture
  • Member

Posted 11 November 2013 - 18:36 PM

Here's an actual example from a program I made. This comes from a master-detail relationship and the code below is from the detail controller. Take note that the calculated field is displayed as a formatted string representing a decimal number with two places.

 

public function assemble($pk)

{
$data['link'] = 'invoices'; // link for "back to invoices"
$data['label'] = 'Invoices'; // label used for "back to invoices"
$gcrud = new grocery_crud();
$gcrud->where('refno',$pk);
$gcrud->order_by('lineno');
$gcrud->set_table('invoicesx');
$gcrud->set_subject('Invoice Details - '.$pk);
$gcrud->columns('lineno','itemcode','qty','price','amount'); 
$gcrud->display_as('lineno','Line #')
->display_as('itemcode','Item Code')
->display_as('qty','Quantity')
->display_as('price','Price')
->display_as('amount','Amount');
$gcrud->callback_column('amount',array($this,'compute_amount'));
$gcrud->fields('refno','lineno','itemcode','qty','price');
$gcrud->field_type('refno','hidden',$pk);
$gcrud->set_rules('lineno','Line #','required|integer');
$gcrud->set_rules('itemcode','Item Code','required'); 
$gcrud->set_rules('qty','Quantity','required|integer'); 
$gcrud->set_rules('price','Price','required|decimal'); 
$gcrud->set_relation('itemcode','items','{itemcode} - {description}',
null,'description'); 
$output = $gcrud->render();
$this->load->view('templates/template3a',$output);
$this->load->view('templates/template3b',$data);
}
 
public function compute_amount($value, $row)
{
return number_format($row->qty*$row->price,2,'.',',');
}
 
 
Regards,
 
Ed Ramirez
 
P.S. Dont forget to click the Like button.  B)

victor

victor
  • profile picture
  • Member

Posted 12 November 2013 - 09:24 AM

You can use callback_before_insert to prepare the "total" value to insert into the database

$crud->callback_before_insert(array($this,'calculate'));
function calculate($post_array){


$post_array['total']  = $post_array['qty']*$post_array['price'];
return $post_array
}

Ralph Schipper

Ralph Schipper
  • profile picture
  • Member

Posted 12 November 2013 - 12:29 PM

ok this makes sense,

 

but what if I have 2 calculated fiels?

 

do I need to do something like this?

2 times a callback and 2 functions?

 

$crud->callback_before_insert(array($this,'discount'));

$crud->callback_before_insert(array($this,'total'));

 

 

function discount($post_array){


$post_array['total']  = $post_array['qty']*$post_array['price'];
return $post_array
}

 

function total($post_array){


$post_array['total']  = some code;
return $post_array
}


victor

victor
  • profile picture
  • Member

Posted 12 November 2013 - 12:34 PM

You should use the callback function once.

 

function callback($post_array){

$post_array['total']  = $post_array['qty']*$post_array['price'];

$post_array['discount']  =  some code;

 

return $post_array
}

 

for example:

$post_array['total']  = $post_array['qty']*$post_array['price'];

$discount = getting user's discount here;

$post_array['discounted_price']  = $post_array['total']-($post_array['total']*$discount);

 

end then you will have this result:

 

$post_array['total'] is 100$

$post_array['discounted_price'] is 80$


victor

victor
  • profile picture
  • Member

Posted 12 November 2013 - 12:42 PM

the callback function have to be used once


Ralph Schipper

Ralph Schipper
  • profile picture
  • Member

Posted 12 November 2013 - 13:05 PM

	public function brandstof()
	{
			$crud = new grocery_CRUD();

			$crud->set_theme('datatables');
			$crud->set_table('invoer');
			$crud->set_subject('Brandstof');
			$crud->required_fields('datum', 'aant_km', 'ltr', 'prijs');
                        $crud->fields('datum', 'aant_dagen', 'aant_km', 'ltr', 'prijs');
                        $crud->order_by('datum', 'desc');
                        $crud->callback_before_insert(array($this,'calc'));

			$output = $crud->render();

			$this->_example_output($output);
	}
        

        function calc($post_array)
        {
            $post_array['tot'] = $post_array['prijs'] * $post_array['ltr'];
            $post_array['een_op'] = $post_array['aant_km'] / $post_array['ltr'];
            $post_array['ltr_per_100_km'] = $post_array['ltr'] / $post_array['aant_km'];
            
            return $post_array;
        }
ok this is my script (it's in dutch)
the result is that he's not entering the calculated fields
 
what's wrong with this code?
 

 


victor

victor
  • profile picture
  • Member

Posted 12 November 2013 - 13:06 PM

show your table structure


Ralph Schipper

Ralph Schipper
  • profile picture
  • Member

Posted 12 November 2013 - 13:09 PM

i asume you mean the SQL table?

 

se attached tumbnail[attachment=710:table structure.jpg]


victor

victor
  • profile picture
  • Member

Posted 12 November 2013 - 13:16 PM

re: the result is that he's not entering the calculated fields.

 

is the field is empty after inserting?

 

make print_r($post_array);

an you will see the post result


Ralph Schipper

Ralph Schipper
  • profile picture
  • Member

Posted 12 November 2013 - 13:27 PM

in phpmyadmin I see null in the calculated fields

 

make print_r($post_array);

an you will see the post result

 

yes about that

 

so I edit this record and hit save

where do I put this print_r($post_array)  ??

 

in my main function or view?


Ralph Schipper

Ralph Schipper
  • profile picture
  • Member

Posted 12 November 2013 - 14:30 PM

the strange thing is that phpmyadmin displays null in the calculated field

and if I don't call 

$crud->callback_before_insert(array($this,'calc'));

 
and do it again, then I still see null
I'm nu sure if he actualy calls the function
 
anny idea? and does someone know where to put the print_r($post_array)?
 
 
Ralph