<?
class sample_class {	
	static public function usrGrp($uid){ // sample get user groups API function
		$children=array(); // dynatree() object "children" data entry
		$s="[]"; // empty results if error
		$cnn = new PDO('sqlite:data/sample.sqlite3'); // create PDO objecft for SQLite3
		$cnn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // support exception handling
		$cnn->exec("PRAGMA foreign_keys = ON"); // turn on foreign key support (optional)
		try {	
			$sql = "select grp_code, grp_name from usr, grp, ugr where usr_id=ugr_usr_id and grp_id=ugr_grp_id and usr_uid=:uid order by grp_name"; // select query
			$res = $cnn->prepare($sql); // prepare query
			$res->execute(array(':uid' => $uid)); // execute query with parms (user id)
			$res->setFetchMode(PDO::FETCH_ASSOC); // set PDO fetch mode associative
			while ($row = $res->fetch()){ // iterate data fetch
				$x=array(); // dynatree data emty array
				$x["key"]=trim($row['grp_code']); // store group code as key
				$x["title"]=trim($row['grp_name']); // store group name as title
				$x["icon"]="user-group-icon.png"; // store user group icon file name
				array_push($children,$x); // add group data to childrens array
			}
			$folder=array(); // root node data array
			$folder["key"]="###TARGET###"; // default key
			$folder["title"]="Assigned Groups"; // default title
			$folder["isFolder"]=true; // node is folder
			$folder["children"]=$children; // store data
			$x=array();
			array_push($x,$folder);
			$s=json_encode($x); // convert to json string
		}
		catch(PDOException $e) {  
		}

		echo $s; // return data to AJAX
	}
	static public function grpAll(){ // sample get all groups API function
		$children=array(); // dynatree() object "children" data entry
		$s="[]"; // empty results if error
		$cnn = new PDO('sqlite:data/sample.sqlite3'); // create PDO objecft for SQLite3
		$cnn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // support exception handling
		$cnn->exec("PRAGMA foreign_keys = ON"); // turn on foreign key support (optional)
		try {  
			$sql ="select grp_code,grp_name from grp order by grp_name"; // select query
			$res = $cnn->query($sql); // execute query
			$res->setFetchMode(PDO::FETCH_ASSOC); // set PDO fetch mode associative
			while ($row = $res->fetch()) { // iterate data fetch
				$x=array(); // dynatree data emty array
				$x["key"]=trim($row['grp_code']); // store group code as key
				$x["title"]=trim($row['grp_name']); // store group name as title
				$x["icon"]="user-group-icon.png"; // store user group icon file name
				array_push($children,$x); // add group data to childrens array
			}
			$folder=array(); // root node data array
			$folder["key"]="###SOURCE###"; // default key
			$folder["title"]="All Groups"; // default title
			$folder["isFolder"]=true; // node is folder
			$folder["children"]=$children; // store data
			$x=array();
			array_push($x,$folder);
			$s=json_encode($x); // convert to json string
		}		  
		catch(PDOException $e) {}
		echo $s; // return data to AJAX
	}
	static public function usrGet(){ // sample get all users data API function
		$output = array("data" => array()); // empty data in dataTable data format
		$cnn = new PDO('sqlite:data/sample.sqlite3'); // create PDO objecft for SQLite3
		$cnn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // support exception handling
		$cnn->exec("PRAGMA foreign_keys = ON"); // turn on foreign key support (optional)
		try {	
			$sql = "select usr_id,usr_uid,usr_lname,usr_fname,usr_mail,usr_country,usr_city from usr"; // select query
			$res = $cnn->query($sql); // execute query
			$res->setFetchMode(PDO::FETCH_ASSOC); // set PDO fetch mode associative
			$i=1;
			while ($row = $res->fetch()){ // iterate data fetch
				$r = array();
				$r[]='<input type="checkbox" />'; // chechbox <input>
				$r[]=$i++; // row index
				$r[]=trim($row["usr_id"]); // user id in SQLite DB
				$r[]=trim($row["usr_uid"]); // user ID for login
				$r[]=trim($row["usr_lname"]); // user last name
				$r[]=trim($row["usr_fname"]); // user first name
				$r[]=trim($row["usr_mail"]); // user e-mail
				$r[]=trim($row["usr_country"]); // user country
				$r[]=trim($row["usr_city"]); // user city
				$output['data'][] = $r; // store user data
			}
		}
		catch(PDOException $e) {  
		}

		echo json_encode( $output ); // return data as json string to dataTable() object ajax call
	}
	static public function jsList() { 
		global $cms;
		$output = array("data" => array()); // empty data in dataTable data format
		$i=1;
		// sample data
		$output['data'][] = array('<input type="checkbox" />', $i++, 'jquery', '/cms/srv/js/jquery-1.10.2.min.js');
		$output['data'][] = array('<input type="checkbox" />', $i++, 'jquery-migrate', '/cms/srv/js/jquery-migrate-1.2.1.min.js');
		$output['data'][] = array('<input type="checkbox" />', $i++, 'superfish', '/cms/srv/lib/superfish-1.7.4/js/superfish.min.js');
		$output['data'][] = array('<input type="checkbox" />', $i++, 'jquery-ui', '/cms/srv/lib/jquery-ui-1.10.3.custom/js/jquery-ui-1.10.3.custom.min.js');
		$output['data'][] = array('<input type="checkbox" />', $i++, 'cookie', '/cms/srv/js/jquery.cookie.1.3.1.js');
		$output['data'][] = array('<input type="checkbox" />', $i++, 'dynatree', '/cms/srv/lib/dynatree-1.2.5/dist/jquery.dynatree.min.js');
		$output['data'][] = array('<input type="checkbox" />', $i++, 'datatables', '/cms/srv/lib/DataTables-1.9.4/media/js/jquery.dataTables.min.js');
		$output['data'][] = array('<input type="checkbox" />', $i++, 'datatables-rowreordering', '/cms/srv/js/jquery.dataTables.rowReordering.js');
		$output['data'][] = array('<input type="checkbox" />', $i++, 'validate', '/cms/srv/lib/jquery-validation-master/jquery.validate.js');
		$output['data'][] = array('<input type="checkbox" />', $i++, 'validate-add', '/cms/srv/lib/jquery-validation-master/additional-methods.js');
		$output['data'][] = array('<input type="checkbox" />', $i++, 'form', '/cms/srv/js/jquery.form.3.32.0.js');
		echo json_encode( $output );  // return data as json string to dataTable() object ajax call
	}
	
	static public function treeGetAll() { // sample cms_tree_table plugin dynatree() object ajax API function
		
		$cnn = new PDO('sqlite:data/sample.sqlite3'); // create PDO objecft for SQLite3
		$cnn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // support exception handling
		$cnn->exec("PRAGMA foreign_keys = ON"); // turn on foreign key support (optional)
						
		function getChildren($up=0, $cnn) { // function for recursive data collection
			$children=array(); // // empty data in dataTable data format
			if (is_null($up)) $up="0"; // for safety check if null and set to 0 if true
			try {  
				$sql ="select dir_id,dir_up_id,dir_title from dir where dir_up_id=:up"; //query string to get all folder having parent directory id=$up
				$res = $cnn->prepare($sql); // prepare query
				$res->execute(array(':up' => $up)); // execute query with parms
				$res->setFetchMode(PDO::FETCH_ASSOC); // set PDO fetch mode associative
				while ($row = $res->fetch()) { // iterate data fetch
					$x=array();
					$x["key"]=trim($row['dir_id']); //  folder id as key
					$x["title"]=trim($row['dir_title']); // folder title as title
					$x["type"]="1"; // type=1 (folder)
					$x["isFolder"]=true; // node is folder
					$next=getChildren(trim($row['dir_id']), $cnn); // recursion to subfolders
					if (count($next)>0) $x["children"]=$next; // if subfolder exist store subfolder data into array
					array_push($children,$x); // store data
				}
				$sql ="select doc_id, doc_title, doc_title_full, doc_desc, doc_keyw, doc_lang, doc_url from doc where doc_dir_id=:up"; //query string to get all items having parent directory id=$up			
				$res2 = $cnn->prepare($sql); // prepare query
				$res2->execute(array(':up' => $up)); // execute query with parms
				$res2->setFetchMode(PDO::FETCH_ASSOC); // set PDO fetch mode associative
				while ($row2 = $res2->fetch()) { // iterate data fetch
					$xx=array();
					$xx["key"]="DOC_".trim($row2['doc_id']); //  item  id as key with prefix "DOC_"
					$xx["title"]=trim($row2['doc_title']);	// item  title as title	
					$xx["type"]="2"; // type=2 (item)									
					$xx["tooltip"] ="<table class='cms-url-tree-tooltip'>"; // tooltip class
					$xx["tooltip"].="<tr><td>Title: </td><td>".trim($row2['doc_title'])."</td></tr>"; // tooltip title
					$xx["tooltip"].="<tr><td>Extended title: </td><td>".trim($row2['doc_title_full'])."</td></tr>"; // tooltip extended title
					$xx["tooltip"].="<tr><td>Description: </td><td>".trim($row2['doc_desc'])."</td></tr>"; // tooltip description
					$xx["tooltip"].="<tr><td>Keywords: </td><td>".trim($row2['doc_keyw'])."</td></tr>"; // tooltip keywords
					$xx["tooltip"].="<tr><td>Language: </td><td>".trim($row2['doc_lang'])."</td></tr>"; // tooltip language
					$xx["tooltip"].="<tr><td>URL: </td><td>".trim($row2['doc_url'])."</td></tr>"; // tooltip url
					$xx["tooltip"].="</table>";
					$xx["isFolder"]=false;	// node is not folder							
					array_push($children,$xx); // store data
				}
			}		  
			catch(PDOException $e) {$children=array();}
			return $children;
		}

		$children=getChildren(0,$cnn); // start data collection
		$ar = array();
		
		$folder=array();
		$folder["key"]="0"; // root node key=0
		$folder["title"]="/"; // root title = "/"
		$folder["type"]="0"; // type=0 (root)
		$folder["isFolder"]=true; // isfolder
		$folder["hideCheckbox"]=true; // root node never be checked

		$children = array_merge($children, $ar); // merge data 		
		if (count($children)>0) $folder["children"]=$children; // if has childrens store
		
		$x=array();
		array_push($x,$folder);
		$s=json_encode($x);	// convert to json string	
		echo $s; // return to cms_tree_table plugin dynatree() object ajax call
	}
	
	static public function treeGetList() {			
		if ($_SERVER['REQUEST_METHOD'] == "GET") $request = $_GET; else $request = $_POST;
		$parms = array(
			'key'	=> 'doc_id',
			'table'	=> 'doc',
			'columns' => array(
				array(
					'dt' => 'action_input',
					'format' => function($col, $row, $no){
						return '<input type="checkbox">';
					}						
				),
				array(
					'dt' => 'no',
					'format' => function($col, $row, $no){
						return $no;
					}
				),
				array('db' => 'doc_id'),
				array('db' => 'doc_url'),
				array('db' => 'doc_title'),
				array('db' => 'doc_title_full')
			)
		);
		if ( isset($request['dir'])){
			$parms['filter'] = "doc_dir_id=" . $request['dir'];
		}
		$o = new sample_table_class(); 
		$o->table($parms);
	}
	
	static public function treePaste() { // sample dummy "paste" API function for cms_tree_table plugin sample					
		$s = json_decode('{"rc":"0","msg":"success"}'); // prepare return object on success event
		$slim = new \Slim\App(); $request = $slim->getContainer()->get('request'); // get Slim request object
		$post = json_decode($request->getBody()); // get POST data object, containing post data as properties
		// code to be inserted begin
		$s->msg="Actually it is <strong>not an error</strong> - just simulation to show data was sent to api and got response. Data was sent:<br>";
		$s->msg .= json_encode($post); // add POST data into return message
		$s->rc = "1"; // simulate error to be shown as message
		// code to be inserted stop
		echo json_encode($s);
	}
	static public function treeNewFolder($id) { // sample dummy "newFolder" API function for cms_tree_table plugin sample;					
		$s = json_decode('{"rc":"0","msg":"success"}'); // prepare return object on success event
		$node_key=trim($_POST['node_key']);
		$node_title=trim($_POST['node_title']);
		// code to be inserted begin
		$s->msg="Actually it is <strong>not an error</strong> - just simulation to show data was sent to api and got response. Data was sent:<br>";
		$s->msg .= "\$_POST['node_key']: ".$node_key.", \$_POST['node_title']: ".$node_title; // add POST data into return message
		$s->rc = "1"; // simulate error to be shown as message
		// code to be inserted stop
		echo json_encode($s);
	}
	static public function treeUpdateFolder($id) { // sample dummy "updateFolder" API function for cms_tree_table plugin sample	 					
		$s = json_decode('{"rc":"0","msg":"success","data":[]}'); // prepare return object on success event
		$node_key=trim($_POST['node_key']);
		$node_title=trim($_POST['node_title']);
		// code to be inserted begin
		$s->data["key"]=$node_key;
		$s->data["title"]=$node_title;;
		$s->msg="Actually it is <strong>not an error</strong> - just simulation to show data was sent to api and got response. Data was sent:<br>";
		$s->msg .= "\$_POST['node_key']: ".$node_key.", \$_POST['node_title']: ".$node_title; // add POST data into return message
		$s->rc = "1"; // simulate error to be shown as message
		// code to be inserted stop
		echo json_encode($s);
	}
	static public function treeUpdateItem($id) { // sample dummy "updateItem" API function for cms_tree_table plugin sample	  					
		$s = json_decode('{"rc":"0","msg":"success","data":[]}'); // prepare return object on success event
		$node_key=trim($_POST['node_key']);
		$node_title=trim($_POST['node_title']);
		// code to be inserted begin
		$s->data["key"]=$node_key;
		$s->data["title"]=$node_title;;
		$s->msg="Actually it is <strong>not an error</strong> - just simulation to show data was sent to api and got response. Data was sent:<br>";
		$s->msg .= "\$_POST['node_key']: ".$node_key.", \$_POST['node_title']: ".$node_title; // add POST data into return message
		$s->rc = "1"; // simulate error to be shown as message
		// code to be inserted stop
		echo json_encode($s);
	}
	static public function treeDrop() { // sample dummy "drop" API function for cms_tree_table plugin sample	 					
		$s = json_decode('{"rc":"0","msg":"success"}'); // prepare return object on success event
		$slim = new \Slim\App(); $request = $slim->getContainer()->get('request'); // get Slim request object
		$post = json_decode($request->getBody()); // get POST data object, containing post data as properties
		// code to be inserted begin
		$s->msg="Actually it is <strong>not an error</strong> - just simulation to show data was sent to api and got response. Data was sent:<br>";
		$s->msg .= json_encode($post); // add POST data into return message
		$s->rc = "1"; // simulate error to be shown as message
		// code to be inserted stop
		echo json_encode($s);
	}
	static public function treeReorder() { // sample dummy "reorder" API function for cms_tree_table plugin sample	 					
		$id  = $_POST['id']; // table row ID
		$fromPosition = $_POST['fromPosition']; // from position in table list
		$toPosition = $_POST['toPosition']; // to position in table list
		// todo code start
		// todo code stop
		$s = json_decode('{"rc":"0","msg":"success"}'); // prepare return object on success event
		echo json_encode($s);
	}
	
	static public function usrList() {			
		$parms = array(
			'key'	=> 'usr_id',
			'table'	=> 'usr',
			'join'	=> 'left join (select usr_name as name, usr_id as id from usr) as cms on usr_editor=id',
			'columns' => array(
				array(
					'dt' => 'action_input',
					'format' => function($col, $row, $no){
						return '<input type="checkbox">';
					}						
				),
				array(
					'dt' => 'no',
					'format' => function($col, $row, $no){
						return $no;
					}
				),
				array('db' => 'usr_id'),
				array('db' => 'usr_uid'),
				array('db' => 'usr_lname'),
				array('db' => 'usr_fname'),
				array('db' => 'usr_mail'),
				array('db' => 'usr_country'),
				array('db' => 'usr_city')
			)
		);
		$o = new sample_table_class(); 
		$o->table($parms);
	}
}
?>
Title:	".trim($row2['doc_title'])."
Extended title:	".trim($row2['doc_title_full'])."
Description:	".trim($row2['doc_desc'])."
Keywords:	".trim($row2['doc_keyw'])."
Language:	".trim($row2['doc_lang'])."
URL:	".trim($row2['doc_url'])."