Jump to content


genya

Member Since 21 Jul 2018
Offline Last Active Jul 30 2019 06:53 PM
-----

Topics I've Started

DependentDropdown(custom query) + PostAjaxCallbacks + MoreButtons + MoreUnsets + Custo...

29 July 2019 - 11:16 PM

Hey everyone,

 

I would like to share my extended library for GroceryCRUD, it has DependentDropdown(custom query) + PostAjaxCallbacks + MoreButtons + MoreUnsets + CustomColumn. This is based off the 1.6.1 version. Be aware some of the features are specifically tied to the flexigrid theme, but it can be adjusted to work for other themes as well.

 

Downloading and using:

Download the zip file and unzip it in some directory. Manually copy the files in the directory to your codeigniter directory as they are in the directory you unzipped. Some of the files have the same name as the original GroceryCRUD files, if you want to preserve the old file you can rename it, but for the library to work with all of its features the filenames have to replace the original files.

 

Setup: Load the library after you loaded the original GroceryCRUD library.

$this->load->library('grocery_CRUD');
$this->load->library('grocery_CRUD_mod');

Then to use in you controller:

function my_boss_is_in_a_hurry()
{
    $crud = new grocery_CRUD_mod(); // this is the only difference
    $crud->set_table('customers');
    $crud->columns('customerName','phone','addressLine1','creditLimit');
 
    $output = $crud->render();
 
    $this->_example_output($output);
}

---------FEATURES---------

 

set_custom_column($column_array):

This will trigger the inner search of GroceryCRUD to perform HAVING clause instead of the WHERE clause on the columns listed in the array argument.

I used this for when generating custom query views and needed to search on specific rows, because the original WHERE did not work on those custom columns.

 

Example:

$crud->set_custom_column(array('col1', 'col3'));
$crud->columns('col1', 'col2', 'col3');

The above example will perform a HAVING search whenever the search is done on col1 or col3, but a WHERE will still be performed on col2. When search all is done, a HAVING will be performed on all the columns.

 

unset_pagnation():

unsets the pagination view. Need to have the assets directory copied over. Only works for flexigrid theme.

 

Example:

    function employees_delete_management()
    {
            $crud = new grocery_CRUD_mod();

            $crud->set_theme('datatables');
            $crud->set_table('employees');
            $crud->set_relation('officeCode','offices','city');
            $crud->display_as('officeCode','Office City');
            $crud->set_subject('Employee');

            $crud->unset_pagnation();
 
            $output = $crud->render();
            $this->_example_output($output);
    }  

unset_search():

unsets the search components. Will not see them in view. Need to have the assets directory copied over. Only works for flexigrid theme.

 

Example:

    function employees_delete_management()
    {
            $crud = new grocery_CRUD_mod();
 
            $crud->set_theme('datatables');
            $crud->set_table('employees');
            $crud->set_relation('officeCode','offices','city');
            $crud->display_as('officeCode','Office City');
            $crud->set_subject('Employee');
            $crud->unset_search();
 
            $output = $crud->render();
            $this->_example_output($output);
    }  

post_ajax_callbacks():

calls your javascript function after a refresh on the table specified occurred. This is useful if need to perform an action right after a CRUD action was done, since the refresh is triggered after all of those actions. Need to have the assets directory copied over. Only works for flexigrid theme.

 

Example:

    function employees_delete_management()
    {
            $crud = new grocery_CRUD_mod();
            
            $crud->set_js("assets/my_js_function.js")
            $crud->post_ajax_callbacks(array("employees_delete_management"=>"js_func"));

            $crud->set_theme('datatables');
            $crud->set_table('employees');
            $crud->set_relation('officeCode','offices','city');
            $crud->display_as('officeCode','Office City');
            $crud->set_subject('Employee');
 
            $output = $crud->render();
            $this->_example_output($output);
    }  

In the above example, the javscript js_func() will be called after a refresh on the employees_delete_management table was performed.

 

add_state(new_state, view_filename, final_url, validation_url, callback, type_of_render):

This adds a custom state inside the GroceryCRUD render framework. This is used in the custom form creation, a more detailed explanation and example is here.

It is also used to create the dependent dropdown. If needed to use this function, when putting the type_of_render argument you can have an immediate_layout. This is like the delete action in the original GroceryCRUD, when delete is pressed no form is popped up, just a statement is executed. I will show an example of the use of immediate layout, but the custom_form type of render is in the link above.

 

Example:

function offices_management_with_actions()
{
    $crud = new grocery_CRUD_mod();
 
    $crud->set_table('offices');
    $crud->set_subject('Office');
    $crud->required_fields('city');
    $crud->columns('city','country','phone');
 
    $crud->add_action('update_blah', '', '[name of controller class]/offices_management_with_actions/update_blah','ui-icon-plus custom-action');
 
                // custom immediate action
		$crud->add_state('update_blah', '', '', '', function($state_info)
		{
			$error_msg = '';
			$success_msg = 'Successfully updated blah.';
                        $flag = true;
                        /*
                         Perform database actions and/or other things
                        */
			
			return array('success' => $flag, 'error_msg' => $error_msg, 'success_msg' => $success_msg);
		}, 'immediate_layout');

    $output = $crud->render();
 
    $this->_example_output($output);
}		

In the above example when the action, update blah, is pressed it will be routed to the function placed in add_state at the callback argument. Also do not forget to put the name of the controller class in the route in add_action. Also you can have a custom success or error msg come out as in the original GroceryCRUD library. When success is false, the error_msg will be reported, when success is true the success_msg will be reported.

 

add_button(button_name, $link, [alignment, css_classes]):

This adds a button like the original add button in the framework, it is similar to add_action, but for placing a custom button on the top of the view. You can select whether to align the button left or right, and also put css classes on it. I usually use this will add_state, to route the action to a custom form.

 

Example:

function offices_management_with_actions()
{
    $crud = new grocery_CRUD_mod();
 
    $crud->set_table('offices');
    $crud->set_subject('Office');
    $crud->required_fields('city');
    $crud->columns('city','country','phone');
 
    $crud->add_button('add a blah', 'offices_management_with_actions/add_blah', 'left', 'ui-icon-plus custom-action');

    $crud->add_state('add_blah', '', '', '[name of controller class]/offices_management_with_actions/add_blah', '', 'custom_form');

    $output = $crud->render();
 
    $this->_example_output($output);
}	

The above example will create a button left aligned on top of the table not in each row. This button is linked to the add_blah state, refer here for what to put in the add_state to make it work.

 

dependent_dropdown_field(field_name, dependent_field, query, [query_binding=array()]):

This will make the field_name depend on the dependent_field by the query specified and the query bindings. In the query binding the order is crucial and directly related to the question mark symbols placed in the query argument. The options for query binding are the dependent_field, other_field, and primary_key.

This means if the key is dependent_field then the value does not really matter in the argument since the value used in the query will be the value selected for the dependent_field. If the other_field is the key, then you need to specify from what field to substitute the value into the query. If primary_key is used in the key, then it will substitute the primary key value of the selected row, the value parameter in the array will not matter since you specified it is the primary_key. The output from the query MUST return an 'id' column and a 'name' column which will be put into the dropdown options.

 

Example:

function offices_management_with_dependent_dropdown()
{
    $crud = new grocery_CRUD_mod();
 
    $crud->set_table('offices');
    $crud->set_subject('Office');
    $crud->required_fields('city');
    $crud->columns('city','country','phone');
 
    // dependent dropdown
    $crud->dependent_dropdown_field('city', 'country', '
       SELECT city_country.city_id AS id, cities.name AS name
       FROM city_country
       LEFT JOIN cities ON (cities.city_id = city_country.country_id)
       WHERE country_id = ?
       ', 
       array('dependent_field' => 'dependent_field'));

    $output = $crud->render();
 
    $this->_example_output($output);
}	

The above query will have city depend on country, and whenever country is changed the cities will be updated accordingly. If you need to nest that city is dependent on country and state, just say for example. You do not need to place two dependencies on city, just put a dependency that state is dependent on country and city is dependent on state. They will adjust accordingly. You can have the query use values from other fields as described above and also use the primary key value in the query if needed.

 

 

 

 

 

 

 

I hope this helps, let me know if you have any questions.


Improve get_total_results query for big tables with a lot of rows

14 June 2019 - 01:55 PM

Hello,

 

The original Grocery_crud_model gets the total number of rows by performing the exact same query without the 'LIMIT x' portion

and then counts the number of rows with codeigniter's num_rows() function. For small tables this approach does not

seem noticeable time wise.

 

I had a table with more than one hundred thousand (100k+) rows and the list would render kind of slow. To improve this,

I made adjustments to Custom_grocery_crud_model from https://www.groceryc...-and-searching/

 

I added to the model 'SELECT COUNT(primary_key) FROM (' + original query + ')' for the get_total_results function, this increased time of rendering by about 4x.

 

Example in controller:

	public function my_table()
	{			
 		$crud = new grocery_CRUD();
		
		$crud->set_model('custom_grocery_crud_model');
		
		$crud->set_table('my_table');
		$crud->columns('col1', 'col2', 'col3');
		
		$crud->basic_model->set_custom_query('
                        SELECT primary_key, col1, col2, another_table.col3
                        FROM my_table
                        LEFT JOIN another_table ON (another_table.id = my_table.id)
		', 'GROUP BY my_table.col1', $query_binding=array());

                // for searching with WHERE clause on col3
		$crud->basic_model->set_column_table(array(
			'col3' => 'another_table',
		));
		
		// for making the second query of getting the row count faster
                // The alias for COUNT has to be num_rows as that is the name used in the model.
		$crud->basic_model->set_header_footer_sql('
			SELECT COUNT(a.primary_key) as num_rows
			FROM
			(		
		', ') a');
		
		$output = $crud->render();

		$this->load->view('my_view.php', $output);
	}

I also added the set_column_table() function for searching on those columns from other tables by WHERE, and there is a query binding argument to the custom query if needed.

 

I attached the model file.

 

I hope this helps.


Custom edit/add/read/etc form

03 June 2019 - 02:44 PM

Hey everyone! Thank you Johnny for this amazing GroceryCrud!

 

I would like to share an extension to the library, I attached the file named Grocery_CRUD_mod.php. Please be aware I am still using library 1.6.1, and do not know how it would transfer to other versions or the latest version 1.6.3.

 

This extension allows for rendering a completely custom form when clicking on your add_action button. It works by adding a custom state and then you can route the add_action url to that state, and have a validation url function and a final database insert/update/etc url function to the state. You can also add a callback function to the add_state that will pass additional data to the form before rendering it.

 

my_controller:

	public function update_validate($pri_key=null)
	{
		$post_data = $this->input->post();
		
		$validation_result = (object)array('success'=>false);
		
		$validation_result->error_message = "yo yo error msg";
		$validation_result->success_message = "yo yo success msg <br /><br /><br />";
		$validation_result->error_fields = array('field1' => 'field1 error', 'field2' => 'field2 error');

		echo json_encode($validation_result);
		die();	
	}
	
	public function additional_data($primary_key)
	{
		$output = array();
		
		$output['query_results'] = $this->db->query('SOME QUERY')->result_array();
		
		$output['another_variable'] = "additional data here";
		
		return $output;
	}
	
	public function table()
	{
 		$crud = new grocery_CRUD_mod();
		
		$crud->set_crud_url_path(site_url(strtolower(__CLASS__."/".__FUNCTION__)),site_url(strtolower(__CLASS__."/table")));
		
		// make sure the state_name 'custom_edit' and the url for the add_action are the same 'my_controller/table/custom_edit'
		$crud->add_state('custom_edit', 'custom_edit_form.php', 'update_validate', NULL, array($this, 'additional_data'));
		
		$crud->add_action('My Custom Edit', '', 'my_controller/table/custom_edit', 'ui-icon-plus custom-action'); 
                // custom-action css class is for modal dialog box	
		
		$crud->set_table('table');
		$crud->columns('col1', 'col2', 'col3');
		
		$output = $crud->render();

		$this->load->view('my_view.php', $output);
	}

custom_edit_form.php:

(I am using flexigrid theme. This file does have to be in assets/grocery_crud/themes/'theme you are using'/views/)

<?php

    $this->set_css($this->default_theme_path.'/flexigrid/css/flexigrid.css');

    $this->set_js_lib($this->default_javascript_path.'/jquery_plugins/jquery.form.min.js');
    $this->set_js_config($this->default_theme_path.'/flexigrid/js/flexigrid-edit.js');

    $this->set_js_lib($this->default_javascript_path.'/jquery_plugins/jquery.noty.js');
    $this->set_js_lib($this->default_javascript_path.'/jquery_plugins/config/jquery.noty.config.js');
?>

<div class="flexigrid crud-form" style='width: 100%;' data-unique-hash="<?php echo $unique_hash; ?>">
    <div class="mDiv">
        <div class="ftitle">
            <div class='ftitle-left'>
                <?php echo $this->l('form_edit'); ?> <?php echo $subject?>
            </div>
            <div class='clear'></div>
        </div>
        <div title="<?php echo $this->l('minimize_maximize');?>" class="ptogtitle">
            <span></span>
        </div>
    </div>
<div id='main-table-box'>


    <div class='form-div'>
    <?php echo form_open( 'my_controller/update_validate/'.$primary_key, 'method="post" id="crudForm"  enctype="multipart/form-data"'); ?>
    
            my custom form!

            Some of my additional vars from additional_data function callback
            <?php

            echo $additional_data['another_variable'];

            ?>

        <?php if ($is_ajax) { ?><input type="hidden" name="is_ajax" value="true" /><?php }?>
        <div id='report-error' class='report-div error'></div>
        <div id='report-success' class='report-div success'></div>
    </div>
    <div class="pDiv">
        <div class='form-button-box'>
            <input  id="form-button-save" type='submit' value='<?php echo $this->l('form_update_changes'); ?>' class="btn btn-large"/>
        </div>
<?php     if(!$this->unset_back_to_list) { ?>
        <div class='form-button-box'>
            <input type='button' value='<?php echo $this->l('form_update_and_go_back'); ?>' id="save-and-go-back-button" class="btn btn-large"/>
        </div>
        <div class='form-button-box'>
            <input type='button' value='<?php echo $this->l('form_cancel'); ?>' class="btn btn-large" id="cancel-button" />
        </div>
<?php     } ?>
        <div class='form-button-box'>
            <div class='small-loading' id='FormLoading'><?php echo $this->l('form_update_loading'); ?></div>
        </div>
        <div class='clear'></div>
    </div>
    <?php echo form_close(); ?>
</div>
</div>
<script type="text/javascript">    
    var validation_url = '<?php
        $my_controller_pos = strpos($list_url, 'my_controller');
        $my_controller_len = strlen('my_controller');
        $base_path = substr($list_url, 0, ($my_controller_pos + $my_controller_len));
        
        echo $base_path . '/update_validate/' . $primary_key;
    ?>';
    var list_url = '<?php echo $list_url?>';

    var message_alert_edit_form = "<?php echo $this->l('alert_edit_form')?>";
    var message_update_error = "<?php echo $this->l('update_error')?>";
</script>

The js validation_url variable needs to be taken from the php variables and passed to the view, that is a future improvement.

 

 

Also the 'custom-action' css class is for rendering a modal dialog box. I am not sure how to properly 'extend' to not modify the javascript files, but if you need the modal dialog box to pop up for your custom actions

in assets/grocery_crud/js/common/list.js these changes have to be made:

 

From:

$('.edit_button,.add_button').unbind('click');
$('.edit_button,.add_button').click(function(){

To:

$('.edit_button,.add_button,.custom-action').unbind('click');
$('.edit_button,.add_button,.custom-action').click(function(){

I hope this helps, and please give me feedback if possible.