phpgrid.org

<?php
/**
 * PHP Grid Component
 *
 * @author Abu Ghufran <gridphp@gmail.com> - http://www.phpgrid.org
 * @version 2.0.0
 * @license: see license.txt included in package
 */

include_once("config.php");

include(PHPGRID_LIBPATH."inc/jqgrid_dist.php");

// Database config file to be passed in phpgrid constructor
$db_conf = array(
					"type" 		=> PHPGRID_DBTYPE,
					"server" 	=> PHPGRID_DBHOST,
					"user" 		=> PHPGRID_DBUSER,
					"password" 	=> PHPGRID_DBPASS,
					"database" 	=> PHPGRID_DBNAME
				);


// master Grid 01
$g = new jqgrid($db_conf);

// following params will enable subgrid -- by default first column (PK) of parent is passed as param 'id'
$opt["detail_grid_id"] = "list2";
$opt["height"] = "200";
$opt["width"] = "420";

$g->set_options($opt);

// you can provide custom SQL query to display data

$g->select_command = " SELECT m.cie_id, m.cie_name, m.phone, m.town, m.postcode,
 m.status, m.address, c.local_name as country_id, m.contact_name, m.email, m.website, m.source, m.frtmap_code, op.operator_name
 FROM crm_main m INNER JOIN crm_country c ON c.country_id = m.country_id INNER JOIN crm_operators op ON op.computer_name = m.created_by";



$grid["caption"] = "BRINOR CRM SYSTEM";
$grid["rownumbers"] = true;
$grid["rownumWidth"] = 16;
$grid["resizable"] = true; // defaults to false
$grid["autoresize"] = true; // defaults to false

$grid["loadtext"] = "Loading your customer list ;-)";

$grid["toolbar"] = "both";
$grid["multiselect"] = false;
$grid["altRows"] = true;

// Date will be sorted desc, and ID asc.
$grid["sortname"] = "cie_name ASC,cie_id";
$grid["sortorder"] = "ASC";

$grid["scroll"] = true; ///// SCROLL INSTEAD OF PAGE SELECTION
$grid["cellEdit"] = false;  /// Editing like Excel
$grid["toppager"] = true;

// export XLS file
// export to excel parameters
$grid["export"] = array("format"=>"pdf", "filename"=>"my-file", "sheetname"=>"test");

$grid["add_options"] = array("recreateForm" => true, "closeAfterEdit"=>true, 'width'=>'400', 'top'=>'200', 'left'=>'200');
$grid["edit_options"] = array("recreateForm" => true, "closeAfterEdit"=>true, 'width'=>'400', 'top'=>'200', 'left'=>'200');
$grid["view_options"] = array("recreateForm" => true, "closeAfterEdit"=>true, 'width'=>'400', 'top'=>'200', 'left'=>'200');




$g->set_options($grid);
//
$e["on_after_insert"] = array("after_insert_client", null, true);
$e["on_update"] = array("update_client", null, true);
$g->set_events($e);


function after_insert_client($data)
{
// these lines will push data array in error box to debug
//ob_start();
//print_r($data);
//phpgrid_error(ob_get_clean());

$computer = gethostname();
$date = date("Y-m-d");
$str="UPDATE crm_main SET created_by='$computer', created_date='$date' WHERE cie_id ={$data["cie_id"]}";
mysql_query($str);
}

function update_client($data)
{
// these lines will push data array in error box to debug
//ob_start();
//print_r($data);
//phpgrid_error(ob_get_clean());

$computer = gethostname();
$date = date("Y-m-d");
$str="UPDATE crm_main SET updated_by='$computer', updated_date='$date' WHERE cie_id ={$data["cie_id"]}";
mysql_query($str);
}



/////////////////////////////////
////////////////////////////////

$g->set_actions(array(
						"add"=>true, // allow/disallow add
						"edit"=>true, // allow/disallow edit
						"delete"=>true, // allow/disallow delete
						"rowactions"=>false, // show/hide row wise edit/del/save option
						"export"=>true, // show/hide export to excel option
						"autofilter" => true, // show/hide autofilter for search
						"search" => "advance", // show single/multi field search condition (e.g. simple or advance)
            "showhidecolumns" => true
					)
				);

/////////////////////////////////
/////////////////////////////////

$col = array();
$col["title"] = "ID"; // caption of column
$col["name"] = "cie_id"; // grid column name, must be exactly same as returned column-name from sql (tablefield or field-alias)
$col["width"] = "10";
$col["hidden"] = true; // HIDE this column
//$col["editrules"] = array("edithidden"=>true);
$col["align"] = "center"; // this column is not editable
$col["editable"] = false; // this column is not editable
$col["search"] = false; // this column is not searchable
$cols[] = $col;


////////////////
////////////////

# Custom made column to show link, must have default value as it's not db driven
$col = array();
$col["title"] = "Details";
$col["name"] = "more_options";
$col["width"] = "30";
$col["align"] = "center";
$col["search"] = false;
$col["sortable"] = false;
# no new line in this html, only space. otherwise it may break ui of grid
$buttons_html = "<input type='button' value='Edit' onclick='open_edit(this)'>";
$col["default"] = $buttons_html;
$cols[] = $col;


///////////////////////
///////////////////////

$col = array();
$col["title"] = "Customer";
$col["name"] = "cie_name";
$col["width"] = "30";
$col["editable"] = true; // this column is not editable
$col["align"] = "center"; // this column is not editable
$col["search"] = true; // this column is not searchable
$col["editoptions"] = array("size"=>50);
$cols[] = $col;

//////////
/////////

$col = array();
$col["title"] = "Town";
$col["name"] = "town";
$col["width"] = "30";
$col["sortable"] = true; // this column is not sortable
$col["search"] = true; // this column is not searchable
$col["editable"] = true;
$col["editoptions"] = array("size"=>50);
$cols[] = $col;

//////////
/////////

$col = array();
$col["title"] = "Postcode";
$col["name"] = "postcode";
$col["width"] = "25";
$col["align"] = "center"; // this column is not editable
$col["sortable"] = false; // this column is not sortable
$col["search"] = true; // this column is not searchable
$col["editable"] = true;
$col["editoptions"] = array("size"=>12);
$col["hidden"] = true; // HIDE this column
$col["editrules"] = array("edithidden"=>true);
$cols[] = $col;

//////////
/////////

$col = array();
$col["title"] = "Country";
$col["name"] = "country_id";
$col["width"] = "50";
$col["align"] = "center"; // this column is not editable
$col["sortable"] = true; // this column is not sortable
$col["search"] = true; // this column is not searchable

$col["dbname"] = "c.country_id"; // this is required as we need to search in name field, not id
$col["editable"] = true;

# fetch data from database, with alias k for key, v for value
$str = $g->get_dropdown_values("select distinct country_id as k, local_name as v from crm_country where type = 'CO' ORDER BY local_name");

$col["edittype"] = "select"; // render as select
$col["editoptions"] = array("value"=>$str, "defaultValue" => "United Kingdom");
$col["editoptions"]["dataInit"] = "function(){ setTimeout(function(){ link_select2('{$col["name"]}'); },200); }";

$col["stype"] = "select"; // render as select
$col["searchoptions"] = array("value"=>$str);
$col["searchoptions"]["dataInit"] = "function(){ setTimeout(function(){ link_select2('gs_{$col["name"]}'); },200); }";

$cols[] = $col;

//////////
/////////

$col = array();
$col["title"] = "Address";
$col["name"] = "address";
$col["width"] = "60";
$col["sortable"] = false; // this column is not sortable
$col["search"] = false; // this column is not searchable
$col["editable"] = true;
$col["edittype"] = "textarea"; // render as textarea on edit
$col["editoptions"] = array("rows"=>4, "cols"=>51); // with these attributes
$col["hidden"] = true; // HIDE this column
$col["editrules"] = array("edithidden"=>true);
$cols[] = $col;

//////////
/////////

$col = array();
$col["title"] = "Contact Name";
$col["name"] = "contact_name";
$col["width"] = "50";
$col["editable"] = true; // this column is not editable
$col["align"] = "center"; // this column is not editable
$col["search"] = true; // this column is not searchable
$cols[] = $col;

//////////


$col = array();
$col["title"] = "Phone";
$col["name"] = "phone";
$col["width"] = "35";
$col["align"] = "center"; // this column is not editable
$col["sortable"] = false; // this column is not sortable
$col["search"] = true; // this column is not searchable
$col["editable"] = true;
$col["editoptions"] = array("size"=>20); // with default display of textbox with size 20
$cols[] = $col;


//////////
/////////

$col = array();
$col["title"] = "Email";
$col["name"] = "email";
$col["width"] = "50";
$col["align"] = "center"; // this column is not editable
$col["sortable"] = false; // this column is not sortable
$col["search"] = true; // this column is not searchable
$col["editable"] = true;
$col["editoptions"] = array("size"=>50); // with default display of textbox with size 20
$col["formatter"] = "function(cellval,options,rowdata){ return '<a href=\"mailto:'+cellval+'\">'+cellval+'</a>'; }";
$col["unformat"] = "function(cellval,options,cell){ return $('a', cell).attr('href').replace('mailto:',''); }";
$cols[] = $col;


//////////
/////////

$col = array();
$col["title"] = "Website";
$col["name"] = "website";
$col["width"] = "50";
$col["align"] = "center"; // this column is not editable
$col["sortable"] = false; // this column is not sortable
$col["search"] = true; // this column is not searchable
$col["editable"] = true;
$col["editoptions"] = array("size"=>50); // with default display of textbox with size 20
$col["formatter"] = "function(cellval,options,rowdata){ return '<a target=\"_blank\" href=\"http://'+cellval+'\">'+cellval+'</a>'; }";
$col["unformat"] = "function(cellval,options,cell){ return $('a', cell).attr('href').replace('http://',''); }";
$cols[] = $col;


////////////////////////
////////////////////////

$col = array();
$col["title"] = "Source";
$col["name"] = "source";
$col["width"] = "30";
$col["align"] = "center"; // this column is not editable
$col["sortable"] = false; // this column is not sortable
$col["search"] = true; // this column is not searchable
$col["editable"] = true;
$col["editoptions"] = array("size"=>30); // with default display of textbox with size 20
$col["hidden"] = true; // HIDE this column
$col["editrules"] = array("edithidden"=>true);
$cols[] = $col;


//////////
/////////

$col = array();
$col["title"] = "FRTMAP";
$col["name"] = "frtmap_code";
$col["width"] = "25";
$col["align"] = "center"; // this column is not editable
$col["sortable"] = false; // this column is not sortable
$col["search"] = true; // this column is not searchable
$col["editable"] = true;
$col["editoptions"] = array("size"=>10); // with default display of textbox with size 20
$col["hidden"] = true; // HIDE this column
$col["editrules"] = array("edithidden"=>true);
$cols[] = $col;


////////////////////////
////////////////////////

$col = array();
$col["title"] = "Operator";
$col["name"] = "operator_name";
$col["width"] = "20";
$col["align"] = "center"; // this column is not editable
$col["sortable"] = true; // this column is not sortable
$col["search"] = true; // this column is not searchable
$col["editable"] = false;
$col["hidden"] = true; // HIDE this column
//$col["editrules"] = array("edithidden"=>true);
$cols[] = $col;



//////////
/////////

$col = array();
$col["title"] = "Status";
$col["name"] = "status";
$col["width"] = "12";
$col["align"] = "center"; // this column is not editable
$col["sortable"] = true; // this column is not sortable
$col["search"] = true; // this column is not searchable
$col["editable"] = true;
$col["edittype"] = "select";
$col["editoptions"] = array("value"=>'LIVE:LIVE;OFF:OFF');
$cols[] = $col;




/////////
/////////
// pass the cooked columns to grid
$g->set_columns($cols);

/////////////////////////////////
/////////////////////////////////

$g->table = "crm_main";
$out_master = $g->render("list1");





////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////
// Grid 02
$g2 = new jqgrid($db_conf);



$opt = array();
$opt["sortname"] = 'note_id'; // by default sort grid by this field
$opt["sortorder"] = "desc"; // ASC or DESC
$opt["height"] = "100";
$opt["width"] = "400";
$opt["caption"] = "Notes about Cie"; // caption of grid
$opt["multiselect"] = false; // allow you to multi-select through checkboxes
$opt["toolbar"] = top;
$opt["rownumbers"] = true;
$opt["rownumWidth"] = 12;
$opt["resizable"] = false;
$opt["autoresize"] = false;
$opt["scroll"] = true;
$opt["loadtext"] = "Loading Notes ;-)";
//$opt["cellEdit"] = true;
$opt["reloadedit"] = false;

$opt["add_options"] = array("recreateForm" => true, "closeAfterEdit"=>true, 'width'=>'400', 'top'=>'200', 'left'=>'200');
$opt["edit_options"] = array("recreateForm" => true, "closeAfterEdit"=>true, 'width'=>'400', 'top'=>'200', 'left'=>'200');
$opt["view_options"] = array("recreateForm" => true, "closeAfterEdit"=>true, 'width'=>'400', 'top'=>'200', 'left'=>'200');




$g2->set_options($opt);

$cols = array();


$g2->set_actions(array(
						"add"=>true, // allow/disallow add
						"edit"=>true, // allow/disallow edit
						"delete"=>true, // allow/disallow delete
						"rowactions"=>false, // show/hide row wise edit/del/save option
						"export"=>false, // show/hide export to excel option
						"autofilter" => false, // show/hide autofilter for search
						"search" => "simple", // show single/multi field search condition (e.g. simple or advance)
            "showhidecolumns" => false
					)
				);

// receive id, selected row of parent grid
$id = intval($_GET["rowid"]);
// and use in sql for filteration
$g2->select_command = "SELECT note_id, note, cie_id FROM crm_notes WHERE cie_id='$id'";


////////////////
////////////////

# Custom made column to show link, must have default value as it's not db driven
$col = array();
$col["title"] = "Details";
$col["name"] = "more_options";
$col["width"] = "30";
$col["align"] = "center";
$col["search"] = false;
$col["sortable"] = false;
# no new line in this html, only space. otherwise it may break ui of grid
$buttons_htmlGrid2 = "<input type='button' value='Edit' onclick='open_editGrid2(this)'>";
$col["default"] = $buttons_htmlGrid2;
$cols[] = $col;

/////////////////////////////////
/////////////////////////////////

$col = array();
$col["title"] = "ID"; // caption of column
$col["name"] = "note_id"; // grid column name, must be exactly same as returned column-name from sql (tablefield or field-alias)
$col["width"] = "5";
$col["hidden"] = true; // HIDE this column
//$col["editrules"] = array("edithidden"=>true);
$col["align"] = "center"; // this column is not editable
$col["editable"] = false; // this column is not editable
$col["search"] = false; // this column is not searchable
$cols[] = $col;


///////////////////////
///////////////////////

$col = array();
$col["title"] = "Cie ID";
$col["name"] = "cie_id";
$col["width"] = "5";
$col["editable"] = false; // this column is not editable
$col["align"] = "center"; // this column is not editable
$col["search"] = false; // this column is not searchable
$col["hidden"] = true; // HIDE this column
$cols[] = $col;


//////////
/////////

$col = array();
$col["title"] = "Note";
$col["name"] = "note";
$col["width"] = "60";
$col["sortable"] = false; // this column is not sortable
$col["search"] = false; // this column is not searchable
$col["editable"] = true;
$col["edittype"] = "textarea"; // render as textarea on edit
$col["editoptions"] = array("rows"=>4, "cols"=>51); // with these attributes
$col["hidden"] = false; // HIDE this column
$col["editrules"] = array("edithidden"=>true);
$cols[] = $col;

//////////
/////////

/////////
$e["on_insert"] = array("add_client", null, true);
$g2->set_events($e);

function add_client(&$data)
{
    $id = intval($_GET["rowid"]);
    $data["params"]["cie_id"] = $id;
}


// pass the cooked columns to grid
$g2->set_columns($cols);

// generate grid output, with unique grid name as 'list1'
$g2->table = "crm_notes";
$out_list2 = $g2->render("list2");




////// END Detail Grid 02
////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////

?>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html>
<head>
	<link rel="stylesheet" type="text/css" media="screen" href="lib/js/themes/redmond/jquery-ui.custom.css"></link>
	<link rel="stylesheet" type="text/css" media="screen" href="lib/js/jqgrid/css/ui.jqgrid.css"></link>

	<script src="lib/js/jquery.min.js" type="text/javascript"></script>
	<script src="lib/js/jqgrid/js/i18n/grid.locale-en.js" type="text/javascript"></script>
	<script src="lib/js/jqgrid/js/jquery.jqGrid.min.js" type="text/javascript"></script>
	<script src="lib/js/themes/jquery-ui.custom.min.js" type="text/javascript"></script>

    <!--link href="lib/js/integration/select2/select2.css" rel="stylesheet"/>
    <script src="lib/js/integration/select2/select2.min.js"></script-->

    <link rel="stylesheet" href="//cdn.jsdelivr.net/select2/3.5.2/select2.css">
    <script src="//cdn.jsdelivr.net/select2/3.5.2/select2.min.js"></script>

</head>
<body>
	<div style="margin:10px">
	Multiple Detail Grids
	<br>
	<br>
	<?php echo $out_master ?>
	<br>

	<div style="float:left">
	<?php echo $out_list2?>
	</div>


	</div>

	<script>
	function open_edit(o)
	{
		var rowid = jQuery(o).closest('tr').attr('id');
		jQuery("#list1").jqGrid().setSelection(rowid);
		jQuery("#edit_list1").click();
	}
	</script>


	<script>
	function open_editGrid2(o)
	{
		var rowid = jQuery(o).closest('tr').attr('id');
		jQuery("#list2").jqGrid().setSelection(rowid);
		jQuery("#edit_list2").click();
	}
	</script>


      <script>
    function link_select2(id)
    {
        $('select[name='+id+'].editable, select[id='+id+']').select2({width:'95%', dropdownCssClass: 'ui-widget ui-jqdialog', onSelect: function(){ jQuery(this).trigger('change'); }});
        $(document).unbind('keypress').unbind('keydown');
    }
    </script>


</body>
</html>