[edramirez]

I am rather new to both CodeIgniter and GroceryCRUD. So, I wanted to ask if this code listed below is stable. This is a MASTER-DETAIL system which appears to be working with a few records.

 

First of all, here is are the two tables - the master table (invoices) and the detail table (invoicesx):

 

CREATE TABLE `invoices` (
 `refno` varchar(10) NOT NULL DEFAULT '',
 `date` date DEFAULT NULL,
 `custcode` varchar(10) NOT NULL,
 `agent` varchar(10) NOT NULL,
 PRIMARY KEY (`refno`),
 KEY `custcode` (`custcode`),
 KEY `agent` (`agent`),
 CONSTRAINT `invoices_ibfk_1` FOREIGN KEY (`custcode`) REFERENCES `customers` (`custcode`),
 CONSTRAINT `invoices_ibfk_2` FOREIGN KEY (`agent`) REFERENCES `agents` (`agent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

CREATE TABLE `invoicesx` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `refno` varchar(10) NOT NULL DEFAULT '',
 `lineno` int(4) NOT NULL,
 `itemcode` varchar(10) NOT NULL,
 `qty` int(5) NOT NULL,
 `price` decimal(12,2) DEFAULT '0.00',
 PRIMARY KEY (`id`),
 KEY `itemcode` (`itemcode`),
 CONSTRAINT `invoicesx_ibfk_1` FOREIGN KEY (`itemcode`) REFERENCES `items` (`itemcode`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

 

Initially I used a detail table using the RefNo and LineNo fields as the primary key. However, my tests showed that grocerycrud doesn't seem to handle MASTER-DETAIL correctly when the detail table consists of a primary key with compound fields. So, I created id as the primary key and everything worked. But, I am not sure if there are loopholes that need to be addressed.

 

This function resides in invoices.php:

public function assemble()

{

$gcrud = new grocery_crud();

$gcrud->set_table('invoices'); // table

$gcrud->set_subject('Invoices'); // grid heading

$gcrud->columns('refno','date','custcode','agent'); // list of grid columns

$gcrud->display_as('refno','Reference') // display fields using label as heading

->display_as('date','Date')

->display_as('custcode','Customer Code')

->display_as('agent','Agent');

$gcrud->fields('refno','date','custcode','agent');

$gcrud->add_action('Details',base_url().'images/detail.png','invoicesd/assemble');

$gcrud->set_rules('refno','Reference','required|min_length[1]|max_length[10]'); 

$gcrud->set_rules('date','Date','required|exact_length[10]'); 

$gcrud->set_rules('custcode','Customer Code',

'required|min_length[1]|max_length[10]'); 

$gcrud->set_rules('agent','Agent','required|min_length[1]|max_length[10]'); 

$gcrud->set_relation('custcode','customers','{custcode} - {name}',null,'name'); 

$gcrud->set_relation('agent','agents','agent'); 

        $output = $gcrud->render();

$this->load->view('templates/template2.php',$output); 

}

 

These functions reside in invoicesd.php:

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,'.',',');

}

 

To summarize:

 

1. invoices.php shows a list of records for the MASTER table - Invoices table. I inserted an add_action function like so:

$gcrud->add_action('Details',base_url().'images/detail.png','invoicesd/assemble');

 

2. invoices.php shows a list of records for the DETAIL table - Invoicesx table. It takes on a single parameter which is the primary key field of the previous table, like so:

public function assemble($pk)

 

3. I filtered the detail table and arranged the records by lineno field:

$gcrud->where('refno',$pk);

$gcrud->order_by('lineno');

$gcrud->set_table('invoicesx');

 

4. then I hid the refno field while giving it the default value of the primary key value of the master table:

$gcrud->fields('refno','lineno','itemcode','qty','price');

$gcrud->field_type('refno','hidden',$pk);

 

My question is this: Is there a way to make all these more efficient since this both MASTER and DETAIL tables are expected to get really large very fast, with a size of around 50,000 records for the detail table. There would be separate tables for master and detail records for a single year. I am guessing that I need to do something more to improve on the performance of the code over time.

 

Thanks in advance.

 

Ed

[davidoster]

Hello and welcome to the forums [member=edramirez].

First of all thank you for sharing this code!

 

Secondly, lets focus on the table design and the code you posted.

Table invoices has as primary key the refno field. This needs to be assigned so Grocery CRUD library indexes correctly and be on the safe side that it is using the field we want as primary key. Check this function set_primary_key.

 

From the looks of it you load a new view via an action button when the user presses the details button. This is perfectly fine so there is nothing that can be optimized there.

But at least for the details table I would suggest to use datatables since it handles better than flexigrid (which at the moment doesn't have ajax processing enabled) large number of records.

Maybe over time you might need to use the same (datatables) for the invoices table.

Check the set_theme function for changing the theme.

 

In general I don't see many optimizations that you could do with the above code because it is very simple and clean. You filter your details via a where. Where function is as fast as MySQL provides it and CodeIgniter (and hence Grocery CRUD) doesn't actually add any considerable overhead processing on top of it.

So in terms of performance is as fast as it can get from the controller point of view.

 

Special consideration needs to be taken into account about the view and the theme you are using as I mentioned before.

Bear in mind that if you don't like the default view of datatables appeareance you can change it via the themeroller as it is described on these,

/topic/1524-how-to-change-listview-style-css/#entry6606

/topic/1383-replace-gray-icons-in-datatables-theme/#entry5906

 

 

I hope this help you a bit.

[edramirez]

Hi, David -

 

Thanks for your insights. I will look into all the points you've mentioned. Just a few thoughts to add.

 

You mentioned that "But at least for the details table I would suggest to use datatables since it handles better than flexigrid (which at the moment doesn't have ajax processing enabled) large number of records."

 

This might surprise you but flexigrid has better performance than datatables. I've tested loading different files with over 4000 records. In this situation, flexigrid can load 25 records very fast. However, datatables will take almost half a minute, at best, even if the page were just loading the same 25 records. Honestly, I really like the neat appearance of datatables, but flexigrid stands out when handling large tables.

 

Again, thanks for your insights.

 

Regards,

 

Ed

 

 

 

[davidoster]

You welcome.

Just to clarify something that you might not know.

 

Flexigrid theme, at least as it is used withing Grocery CRUD, isn't using ajax even though it can load items via ajax.

[sstekols]

Anyone has an example where work properly?

Was it published?

Thanks in advance

 

Sergio

[buoncri]

mmm ... I am trying with the code of the first examples ...

[acor]

I am rather new to both CodeIgniter and GroceryCRUD. So, I wanted to ask if this code listed below is stable. This is a MASTER-DETAIL system which appears to be working with a few records.

 

First of all, here is are the two tables - the master table (invoices) and the detail table (invoicesx):

 

CREATE TABLE `invoices` (
 `refno` varchar(10) NOT NULL DEFAULT '',
 `date` date DEFAULT NULL,
 `custcode` varchar(10) NOT NULL,
 `agent` varchar(10) NOT NULL,
 PRIMARY KEY (`refno`),
 KEY `custcode` (`custcode`),
 KEY `agent` (`agent`),
 CONSTRAINT `invoices_ibfk_1` FOREIGN KEY (`custcode`) REFERENCES `customers` (`custcode`),
 CONSTRAINT `invoices_ibfk_2` FOREIGN KEY (`agent`) REFERENCES `agents` (`agent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

CREATE TABLE `invoicesx` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `refno` varchar(10) NOT NULL DEFAULT '',
 `lineno` int(4) NOT NULL,
 `itemcode` varchar(10) NOT NULL,
 `qty` int(5) NOT NULL,
 `price` decimal(12,2) DEFAULT '0.00',
 PRIMARY KEY (`id`),
 KEY `itemcode` (`itemcode`),
 CONSTRAINT `invoicesx_ibfk_1` FOREIGN KEY (`itemcode`) REFERENCES `items` (`itemcode`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

 

Initially I used a detail table using the RefNo and LineNo fields as the primary key. However, my tests showed that grocerycrud doesn't seem to handle MASTER-DETAIL correctly when the detail table consists of a primary key with compound fields. So, I created id as the primary key and everything worked. But, I am not sure if there are loopholes that need to be addressed.

 

This function resides in invoices.php:

public function assemble()

{

$gcrud = new grocery_crud();

$gcrud->set_table('invoices'); // table

$gcrud->set_subject('Invoices'); // grid heading

$gcrud->columns('refno','date','custcode','agent'); // list of grid columns

$gcrud->display_as('refno','Reference') // display fields using label as heading

->display_as('date','Date')

->display_as('custcode','Customer Code')

->display_as('agent','Agent');

$gcrud->fields('refno','date','custcode','agent');

$gcrud->add_action('Details',base_url().'images/detail.png','invoicesd/assemble');

$gcrud->set_rules('refno','Reference','required|min_length[1]|max_length[10]'); 

$gcrud->set_rules('date','Date','required|exact_length[10]'); 

$gcrud->set_rules('custcode','Customer Code',

'required|min_length[1]|max_length[10]'); 

$gcrud->set_rules('agent','Agent','required|min_length[1]|max_length[10]'); 

$gcrud->set_relation('custcode','customers','{custcode} - {name}',null,'name'); 

$gcrud->set_relation('agent','agents','agent'); 

        $output = $gcrud->render();

$this->load->view('templates/template2.php',$output); 

}

 

These functions reside in invoicesd.php:

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,'.',',');

}

 

To summarize:

 

1. invoices.php shows a list of records for the MASTER table - Invoices table. I inserted an add_action function like so:

$gcrud->add_action('Details',base_url().'images/detail.png','invoicesd/assemble');

 

2. invoices.php shows a list of records for the DETAIL table - Invoicesx table. It takes on a single parameter which is the primary key field of the previous table, like so:

public function assemble($pk)

 

3. I filtered the detail table and arranged the records by lineno field:

$gcrud->where('refno',$pk);

$gcrud->order_by('lineno');

$gcrud->set_table('invoicesx');

 

4. then I hid the refno field while giving it the default value of the primary key value of the master table:

$gcrud->fields('refno','lineno','itemcode','qty','price');

$gcrud->field_type('refno','hidden',$pk);

 

My question is this: Is there a way to make all these more efficient since this both MASTER and DETAIL tables are expected to get really large very fast, with a size of around 50,000 records for the detail table. There would be separate tables for master and detail records for a single year. I am guessing that I need to do something more to improve on the performance of the code over time.

 

Thanks in advance.

 

Ed

I please could you help me. I tried the code you suggested but I cannot managed the rows

first form

$output = $gcrud->render();

$this->load->view('templates/template2.php',$output); 

 

 

second form

 

$output = $gcrud->render();

$this->load->view('templates/template3a',$output);

$this->load->view('templates/template3b',$data);

 

}

 

 

please could you post  templates files?

thanks in advance