ablears/php-crud-api-w2ui.php

## php-crud-api-w2ui.php
<!DOCTYPE html>
<html>
<head>
    <title>W2UI Demo: grid-3</title>
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/2.1.0/jquery.min.js"></script>
    <script type="text/javascript" src="http://rawgit.com/vitmalina/w2ui/master/dist/w2ui.min.js"></script>
    <link rel="stylesheet" type="text/css" href="http://rawgit.com/vitmalina/w2ui/master/dist/w2ui.min.css" />
</head>
<body>

<section id="tables"></section>

<div id="grid" style="width: 100%; height: 400px; overflow: hidden;"></div>
<br>
<button class="w2ui-btn" onclick="var obj = w2ui['grid']; obj.show.header = !obj.show.header; w2ui['grid'].refresh();">Header</button>
<button class="w2ui-btn" onclick="var obj = w2ui['grid']; obj.show.toolbar = !obj.show.toolbar; w2ui['grid'].refresh();">Toolbar</button>
<button class="w2ui-btn" onclick="var obj = w2ui['grid']; obj.show.columnHeaders = !obj.show.columnHeaders; w2ui['grid'].refresh();">Column Headers</button>
<button class="w2ui-btn" onclick="var obj = w2ui['grid']; obj.show.footer = !obj.show.footer; w2ui['grid'].refresh();">Footer</button>
<button class="w2ui-btn" onclick="var obj = w2ui['grid']; obj.show.lineNumbers = !obj.show.lineNumbers; w2ui['grid'].refresh();">Line Numbers</button>
<button class="w2ui-btn" onclick="var obj = w2ui['grid']; obj.show.selectColumn = !obj.show.selectColumn; w2ui['grid'].refresh();">Select Column</button>
<button class="w2ui-btn" onclick="var obj = w2ui['grid']; obj.show.expandColumn = !obj.show.expandColumn; w2ui['grid'].refresh();">Expand Column</button>

<script type="text/javascript">

$(function () {

    //get the list of tables from the API
    $.get( "api.php", function( scheme_data ) {

        if( typeof scheme_data.tags !=="undefined" ){
            var tables = scheme_data.tags;
            var output = "<ul>";
            for (var i = 0; i < tables.length; i++) {
                output+="<li><a href='"+tables[i].name+"'>"+tables[i].name+"</a></li>";
            }
            //add the table markup
            $( '#tables' ).html( output );
            //set activate table when clicked
            handle_new_api_selection(  );
        }
    })

    .then( function( scheme_data ){

        //make a request to the API
        get_api_response( scheme_data );

    });

    /**
     * Gets the api response via AJAX and renders the result
     *
     * @param      {Array}  scheme_data  The database's scheme data
     */
    function get_api_response( scheme_data ){
        //find the records for the selected table

        //harcoded params for now
        var params = "?page=1,20";

        //what table is selected?
        var selected_table = get_selected_table(  );

        var api_url = "api.php/"+selected_table+"/"+params;

        var searches = true;//we want to support searching the data

        $.get( api_url, function( table_data ) {

            var results = format_for_w2grid( table_data, selected_table, searches );

            if( searches && typeof scheme_data!=="undefined"){
                //add searches
                results[ selected_table ].searches = add_searches_from_column_data( scheme_data, selected_table );
            }
            else{
                results[ selected_table ].searches = [  ];
            }
            console.log(results[ selected_table ].searches);

            $('#grid').w2grid({
                name: 'grid',
                header: 'Barcodes API',
                method: 'GET',
                show: {
                  header        : true,
                  toolbar       : true,
                  footer        : true,
                  lineNumbers   : true,
                  selectColumn  : true
                },
                multiSearch: true,
                columns: results[selected_table].columns,
                records: results[selected_table].records,
                searches: results[ selected_table ].searches
            });
        });
    }

    /**
     * Format API columns and records data
     *
     * @param      {Array}   data            The API data
     * @param      {string}  selected_table  The user-selected table
     *
     * @return     {Array}   { Formatted array suitable for w2ui }
     */
    function format_for_w2grid( data, selected_table ){

        var columns = data[ selected_table ].columns;
        var formatted_columns = [  ];

        var records = data[ selected_table ].records;
        var formatted_records = [  ];

        /*Get column names and ids*/
        for (var i = 0; i < columns.length; i++) {
            formatted_columns[i]={field:columns[i], caption: columns[i]};
        }

        /*Column names need to be added to records ( output needs name/value pairs )*/
        for (var i = 0; i < records.length; i++) {
            var record = records[ i ];

            //for each record, find the column name so we can create name/value JSON pairs
            var record_formatted = [  ];
            for (var z = 0; z < record.length; z++) {

                var column_name = formatted_columns[z].field;

                record_formatted[column_name] = record[ z ];
                record_formatted['recid'] =  i;
            }

            formatted_records.push( record_formatted );

        }


        data[ selected_table ].records = formatted_records;
        data[ selected_table ].columns = formatted_columns;

        return data;
    }

    /**
     * Set a class on the selected table link,
     * destroy the current w2ui object,
     * get this table's data from API
     */
    function handle_new_api_selection(  ){
        $( '#tables a' ).on( 'click', function( e ){
            $( '#tables a' ).removeClass(  );
            $( this ).addClass( 'selected-table' );

            w2ui['grid'].destroy();
            //pass this selection to the API
            get_api_response(  );

            e.preventDefault(  );
        } );
    }

    /**
     * Get the user-selected table.
     *
     * @return     {string}  The selected table's link target.
     */
    function get_selected_table(  ){
        var table = $( '#tables li a.selected-table' )

        //if there isn't a selected table ( eg on first load ), default to the first
        if( table.length === 0 ){
            table = $( '#tables li:first-child a');
        }
        //find the href, this will be the table name
        table = table.attr( 'href' );

        return table;
    }

    /**
     * Map php-crud-api's scheme data to w2ui searches syntax.
     *
     * @param      {Array}   scheme_data     The API scheme data
     * @param      {string}  selected_table  The selected database table
     * @return     {Array}   { Array of w2ui searches  }
     */
    function add_searches_from_column_data( scheme_data, selected_table ){

        /*Array to return
        searches: [
                  { type: 'int',  field: 'recid', caption: 'ID' },
                  { type: 'int', field: 'barcode_number', caption: 'barcode_number' }
        ]
        */

        //get the columns for this table
        var column_properties = scheme_data.paths["/"+selected_table].get.responses[200].schema.properties[selected_table].items.properties;
        var formatted_columns_for_search = [ ];

        //iterate over columns changing the values to match w2ui required JSON
        Object.keys(column_properties).forEach(function(key,index) {

            column_properties[ key ].field = key //add the column name as field name/value
            column_properties[ key ].caption = key //duplicate this for the caption

            //map data type values as expected by w2ui
            if( column_properties[ key ].type === "integer" ){
                column_properties[ key ].type = "int";
            }
            if( column_properties[ key ].type === "string" ){
                column_properties[ key ].type = "text";
            }

            //remove stuff we don't need
            delete column_properties[ key ]['x-nullable'];
            delete column_properties[ key ]['x-dbtype'];
            delete column_properties[ key ]['x-primary-key'];
            delete column_properties[ key ]['default'];

            formatted_columns_for_search.push( column_properties[ key ] );

        });

        return formatted_columns_for_search;
    }


});
</script>

</body>
</html>
	<!DOCTYPE html>
	<html>
	<head>
	<title>W2UI Demo: grid-3</title>
	<script src="http://ajax.googleapis.com/ajax/libs/jquery/2.1.0/jquery.min.js"></script>
	<script type="text/javascript" src="http://rawgit.com/vitmalina/w2ui/master/dist/w2ui.min.js"></script>
	<link rel="stylesheet" type="text/css" href="http://rawgit.com/vitmalina/w2ui/master/dist/w2ui.min.css" />
	</head>
	<body>

	<section id="tables"></section>

	<div id="grid" style="width: 100%; height: 400px; overflow: hidden;"></div>
	<br>
	<button class="w2ui-btn" onclick="var obj = w2ui['grid']; obj.show.header = !obj.show.header; w2ui['grid'].refresh();">Header</button>
	<button class="w2ui-btn" onclick="var obj = w2ui['grid']; obj.show.toolbar = !obj.show.toolbar; w2ui['grid'].refresh();">Toolbar</button>
	<button class="w2ui-btn" onclick="var obj = w2ui['grid']; obj.show.columnHeaders = !obj.show.columnHeaders; w2ui['grid'].refresh();">Column Headers</button>
	<button class="w2ui-btn" onclick="var obj = w2ui['grid']; obj.show.footer = !obj.show.footer; w2ui['grid'].refresh();">Footer</button>
	<button class="w2ui-btn" onclick="var obj = w2ui['grid']; obj.show.lineNumbers = !obj.show.lineNumbers; w2ui['grid'].refresh();">Line Numbers</button>
	<button class="w2ui-btn" onclick="var obj = w2ui['grid']; obj.show.selectColumn = !obj.show.selectColumn; w2ui['grid'].refresh();">Select Column</button>
	<button class="w2ui-btn" onclick="var obj = w2ui['grid']; obj.show.expandColumn = !obj.show.expandColumn; w2ui['grid'].refresh();">Expand Column</button>

	<script type="text/javascript">

	$(function () {

	//get the list of tables from the API
	$.get( "api.php", function( scheme_data ) {

	if( typeof scheme_data.tags !=="undefined" ){
	var tables = scheme_data.tags;
	var output = "<ul>";
	for (var i = 0; i < tables.length; i++) {
	output+="<li><a href='"+tables[i].name+"'>"+tables[i].name+"</a></li>";
	}
	//add the table markup
	$( '#tables' ).html( output );
	//set activate table when clicked
	handle_new_api_selection( );
	}
	})

	.then( function( scheme_data ){

	//make a request to the API
	get_api_response( scheme_data );

	});

	/**
	* Gets the api response via AJAX and renders the result
	*
	* @param {Array} scheme_data The database's scheme data
	*/
	function get_api_response( scheme_data ){
	//find the records for the selected table

	//harcoded params for now
	var params = "?page=1,20";

	//what table is selected?
	var selected_table = get_selected_table( );

	var api_url = "api.php/"+selected_table+"/"+params;

	var searches = true;//we want to support searching the data

	$.get( api_url, function( table_data ) {

	var results = format_for_w2grid( table_data, selected_table, searches );

	if( searches && typeof scheme_data!=="undefined"){
	//add searches
	results[ selected_table ].searches = add_searches_from_column_data( scheme_data, selected_table );
	}
	else{
	results[ selected_table ].searches = [ ];
	}
	console.log(results[ selected_table ].searches);

	$('#grid').w2grid({
	name: 'grid',
	header: 'Barcodes API',
	method: 'GET',
	show: {
	header : true,
	toolbar : true,
	footer : true,
	lineNumbers : true,
	selectColumn : true
	},
	multiSearch: true,
	columns: results[selected_table].columns,
	records: results[selected_table].records,
	searches: results[ selected_table ].searches
	});
	});
	}

	/**
	* Format API columns and records data
	*
	* @param {Array} data The API data
	* @param {string} selected_table The user-selected table
	*
	* @return {Array} { Formatted array suitable for w2ui }
	*/
	function format_for_w2grid( data, selected_table ){

	var columns = data[ selected_table ].columns;
	var formatted_columns = [ ];

	var records = data[ selected_table ].records;
	var formatted_records = [ ];

	/Get column names and ids/
	for (var i = 0; i < columns.length; i++) {
	formatted_columns[i]={field:columns[i], caption: columns[i]};
	}

	/Column names need to be added to records ( output needs name/value pairs )/
	for (var i = 0; i < records.length; i++) {
	var record = records[ i ];

	//for each record, find the column name so we can create name/value JSON pairs
	var record_formatted = [ ];
	for (var z = 0; z < record.length; z++) {

	var column_name = formatted_columns[z].field;

	record_formatted[column_name] = record[ z ];
	record_formatted['recid'] = i;
	}

	formatted_records.push( record_formatted );

	}


	data[ selected_table ].records = formatted_records;
	data[ selected_table ].columns = formatted_columns;

	return data;
	}

	/**
	* Set a class on the selected table link,
	* destroy the current w2ui object,
	* get this table's data from API
	*/
	function handle_new_api_selection( ){
	$( '#tables a' ).on( 'click', function( e ){
	$( '#tables a' ).removeClass( );
	$( this ).addClass( 'selected-table' );

	w2ui['grid'].destroy();
	//pass this selection to the API
	get_api_response( );

	e.preventDefault( );
	} );
	}

	/**
	* Get the user-selected table.
	*
	* @return {string} The selected table's link target.
	*/
	function get_selected_table( ){
	var table = $( '#tables li a.selected-table' )

	//if there isn't a selected table ( eg on first load ), default to the first
	if( table.length === 0 ){
	table = $( '#tables li:first-child a');
	}
	//find the href, this will be the table name
	table = table.attr( 'href' );

	return table;
	}

	/**
	* Map php-crud-api's scheme data to w2ui searches syntax.
	*
	* @param {Array} scheme_data The API scheme data
	* @param {string} selected_table The selected database table
	* @return {Array} { Array of w2ui searches }
	*/
	function add_searches_from_column_data( scheme_data, selected_table ){

	/*Array to return
	searches: [
	{ type: 'int', field: 'recid', caption: 'ID' },
	{ type: 'int', field: 'barcode_number', caption: 'barcode_number' }
	]
	*/

	//get the columns for this table
	var column_properties = scheme_data.paths["/"+selected_table].get.responses[200].schema.properties[selected_table].items.properties;
	var formatted_columns_for_search = [ ];

	//iterate over columns changing the values to match w2ui required JSON
	Object.keys(column_properties).forEach(function(key,index) {

	column_properties[ key ].field = key //add the column name as field name/value
	column_properties[ key ].caption = key //duplicate this for the caption

	//map data type values as expected by w2ui
	if( column_properties[ key ].type === "integer" ){
	column_properties[ key ].type = "int";
	}
	if( column_properties[ key ].type === "string" ){
	column_properties[ key ].type = "text";
	}

	//remove stuff we don't need
	delete column_properties[ key ]['x-nullable'];
	delete column_properties[ key ]['x-dbtype'];
	delete column_properties[ key ]['x-primary-key'];
	delete column_properties[ key ]['default'];

	formatted_columns_for_search.push( column_properties[ key ] );

	});

	return formatted_columns_for_search;
	}


	});
	</script>

	</body>
	</html>