<?php
/**
## 	Jorge	-	11-SEP-2019 - version 2.1.1:
	Add the ability to have an action dropdown menu over checkboxes where a user can select multiple rows within the table and use the 
	options on the action dropdown list to action the selected items.
	
up arrow:   &#x25B2; (ASC)
down arrow: &#x25BC; (DESC)
This file aims to build a list that is displayed in a table. Certain parameters will need to be setup in order for this
list to know how to be constructed and displayed. We use $_SESSION['listParams'] to hold these parameters:
**/
class DataList
{
    // Properties of the class:
	var $page, $modulePage, $offset, $colspan, $cnt, $fieldCnt, $orderField, $orderDirection, $path, $link, $sdb, 
	$queryRows, $moduleNiceName, $exportSQL, $ouputField, $params, $resetList, $refinedList;			

	//The following function is actually the CONSTRUCTOR (note the same name as the class):
	function __construct() //class Constructor
	{
		$this->sdb = new SableDB();
	} 
	
	function __destruct() //class Destructor
	{
		unset($this->sdb); //to destroy the instance created;
	}
	
	private function getYesNoSQL($fieldName, $searchText, $fieldNo)
	{
		$sql = '';
		$yay = isset($_SESSION['listParams']['listFields'][$x]['yesNoYes'])?$_SESSION['listParams']['listFields'][$x]['yesNoYes']:"Yes";
		//$nay = isset($_SESSION['listParams']['listFields'][$x]['yesNoNo'])?$_SESSION['listParams']['listFields'][$x]['yesNoNo']:"No";		
		$match = preg_match("~".$searchText."~i", $yay);
		if ($match)
		{
			$sql = "(".$fieldName." = '".$_SESSION['listParams']['listFields'][$fieldNo]['yesNoOn']."')";
		}
		else
		{
			$sql = "(".$fieldName." <> '".$_SESSION['listParams']['listFields'][$fieldNo]['yesNoOn']."')";
		}
		return $sql;
	}
	
	private function getSearchString($sql)
	{
		$srchStr = $_SESSION['listParams']['searchString'] ?? '';// -->Jag||||
		$srchStr = str_replace('|', '', $srchStr);
		
		$slsf = null;
		
		if (!empty($srchStr))
		{		
			$append = (strpos($sql, 'where') > 0);
			
			$useHaving = $_SESSION['listParams']['groupField'] ?? 0;
			$useHaving = $useHaving || strpos($sql, "concat");

			
			$havest = $useHaving ? " having " : " where ";
			if ($append) $sql .= " and "; else $sql .= $havest; //changed from " and " to " having "... 12/05/2020
			$searchStringFields = explode('|', ($_SESSION['listParams']['searchString'] ?? ''));
			
			$chk = false;
			$x = 0;
			foreach ($searchStringFields as $sfs)
			{
				if (!empty($sfs))
				{
					if ($chk) $sql .= " and ";
					$slsf = $_SESSION['listParams']['searchField'][$x] ?? 0;
					if (strpos($sfs, ',') > 0)
					{
						$commaSeparatedFields = explode(',', $sfs);
						$sql .= "(".
						$lc = false;
						//for ($i=0; $i<sizeof($csf); $i++)
						foreach($commaSeparatedFields as $csf)
						{
							if ($lc) $sql .= " or ";							
							/*----*/							
							if (is_array($slsf))
							{
								//being here means we have mutiple fields to consider in one:
								$fc = false;
								$sql .= "(";
								foreach($slsf as $key => $fl)
								{
									if (is_numeric($key))
									{
										if ($fc) $sql .= " or ";
										
										if ($_SESSION['listParams']['listFields'][$x]['yesNo'] ?? 0)
										{
											$sql .= $this->getYesNoSQL($fl, $sfs, $x);
										}
										else
										{
											$sql .= "(".$fl." like '%".$sfs."%')";
										}
										$fc = true;																		
									}
								}
								$sql .= ")";
							}
							else
							{
							/*----*/							
								//$sql .= "(".$slsf." like '%".$sfs."%') ";								
								if ($_SESSION['listParams']['listFields'][$x]['yesNo'] ?? 0)
								{
									$sql .= $this->getYesNoSQL($slsf, $sfs, $x);
								}
								else
								{
									$sql .= "(".$slsf." like '%".$sfs."%')";
								}								
							}
							$lc = true;
						}
						$sql .= ")";						
					}
					else 
					{
						if (is_array($slsf))
						{
							//being here means we have mutiple fields to consider in one:
							$fc = false;
							$sql .= "(";
							
							foreach($slsf as $key => $fl)
							{
								if (is_numeric($key))
								{
									if ($fc) $sql .= " or ";
									if ($_SESSION['listParams']['listFields'][$x]['yesNo'] ?? 0)
										$sql .= $this->getYesNoSQL($fl, $sfs, $x);
									else
										$sql .= "(".$fl." like '%".$sfs."%')";
									$fc = true;																		
								}
							}
							
							$sql .= ")";
						}
						else
						{
						/*----*/							
							if ($_SESSION['listParams']['listFields'][$x]['yesNo'] ?? 0)
							{
								$sql .= $this->getYesNoSQL($slsf, $sfs, $x);
							}
							else
							{
								$sql .= "(".$slsf." like '%".$sfs."%')";
							}
						}						
					}
					$chk = true;
				}
				$x++;
			}
		}		
		//SableDebug::debug($sql);
		return $sql;
	}
	
	private function getToolBar()
	{
		$html = "<tr>
					<td colspan='".sizeof($_SESSION['listParams']['listFields'])."'>";
		
		$html .= "<table colspacing='0' cellspacing='0' cellpadding='0'><tr>";
					
		if (isset($_SESSION['listParams']['searchVisible'])) //no search bar
		{			
			$loc = '';

			//$ss = str_replace('|', '', ($_SESSION['listParams']['searchString'] ?? 0));
			//if (!empty($ss))
			if ($this->refinedList)
			{
				$html .= "<td>";
				$html .= "<span class='data-list-button'>
						<a href='javascript:doClearSearchClick()'>
							<i class='fa ".SABLE_DATALIST_CLEAR."'></i>&nbsp;Reset</a></span>&nbsp;&nbsp;&nbsp;";
				$html .= "</td>";
			}
			
			$html .= "<td>";
			$html .= "<span class='data-list-button'>
			<a href='javascript:doSearchClick()' id='btnOk'>
				<i class='fa ".SABLE_DATALIST_SEARCH."'></i>&nbsp;Search</a></span>&nbsp;&nbsp;&nbsp;";
			$html .= "</td>";
		}
		
		$showToolbar = ($_SESSION['listParams']['toolbarVisible'] ?? null) ? true : ($_SESSION['listParams']['toolbarVisible'] ?? true);
		if ($showToolbar) //no toolbar?
		{		
		
			if (isset($_SESSION['module']['back']) && ($_SESSION['module']['back']))
			{
				$backlink = isset($_SESSION['module']['backlink']) ?	$_SESSION['module']['backlink'] :
																		SableSystem::getCallPageLink($this->path.$this->modulePage);
				$html .= "<td>";
				$html .= "<span class='data-list-button'>
							<a href='".$backlink."'><i class='fa ".SABLE_DATALIST_BACK."'></i>&nbsp;Back</a>
						  </span>&nbsp;&nbsp;&nbsp;";				
				$html .= "</td>";
			}

			$aa =	isset($_SESSION['listParams']['addAccess']) && !empty($_SESSION['listParams']['addAccess']) ? 
					$_SESSION['listParams']['addAccess'] : SABLE_ACCESS_MANAGER;

			if ($_SESSION['user']['access_level'] >= $aa)
			{
				if (isset($_SESSION['module']['add']) && (!empty($_SESSION['module']['add'])))
				{
					$addLink = isset($_SESSION['module']['addlink']) && (!empty($_SESSION['module']['addlink'])) ? 
								$_SESSION['module']['addlink'] : SableSystem::getCallPageLink($this->path.'add.php');
					$addText = isset($_SESSION['module']['addtext']) && (!empty($_SESSION['module']['addtext'])) ? 
								$_SESSION['module']['addtext'] : "Add ".$this->moduleNiceName;

					$html .= "<td>";
					$html .= "<span class='data-list-button'>
								<a href='".$addLink."'><i class='fa ".SABLE_DATALIST_ADD."'></i>&nbsp;".$addText."</a>
							  </span>&nbsp;&nbsp;&nbsp;";
					$html .= "</td>";
				}
			}

			$html .= "<td>";
			$html .= "  <span class='data-list-button'>
						  <a href='".SableSystem::getSableLinkPath('/home/')."'><i class='fa ".SABLE_DATALIST_HOME."'></i>&nbsp;Home</a>
						</span>&nbsp;&nbsp;&nbsp;";
			$html .= "</td>";

			/** Export Query **/
			$exportAccess = $_SESSION['listParams']['exportAccess'] ?? 0;
			if ((isset($_SESSION['listParams']['allowExport']) && !empty($_SESSION['listParams']['allowExport'])) && 
				$_SESSION['user']['access_level'] >= $exportAccess)
			{
				$param1 = SableSystem::sableEncode($_SESSION['listParams']['moduleFolder']);
				$param2 = $this->exportSQL;

				//Change special chars for use in /classes/sable_db.php --> exportData()
				$param2 = str_replace('%', '__PRC__',	$param2);
				$param2 = str_replace('&', '__AMP__',	$param2);
				$param2 = str_replace('+', '__PLS__',	$param2);
				$param2 = str_replace('"', '__DBQ__',	$param2);
				$param2 = str_replace('\'', '__SBQ__',	$param2);
				$param2 = str_replace('(', '__RTB__',	$param2);
				$param2 = str_replace(')', '__LTB__',	$param2);
				$param2 = str_replace(' ', '__SPC__',	$param2);

				$link = '<a href="javascript:exportQuery(\''.$param1.'\', \''.$param2.'\')">
							<i class="fa '.SABLE_DATALIST_DOWNLOAD.'"></i>&nbsp;Export
						</a>';

				$html .= "<td>";
				$html .= "<span class='data-list-button'>
							".$link."
						  </span>&nbsp;&nbsp;&nbsp;";
				$html .= "</td>";
			}		

			$html .= "<td>";
			$html .="	<span class='data-list-button'>
						  <a href='".SableSystem::getCallPageLink('login/logout.php')."'><i class='fa ".SABLE_DATALIST_LOGOFF." fa-rotate-180'></i>&nbsp;Log Out</a></span>&nbsp;&nbsp;&nbsp;</td></tr>";
			$html .= "</td>";
		}
		
		$html .= "</tr></table>";
		
		return $html;
	}
	
	private function getTableHeadings()
	{
		$html = '';
		if ($this->orderDirection) 
		{
			$sortPic = '&nbsp;<i class="fa '.SABLE_DATALIST_SORT_UP.'" aria-hidden="true"></i>&nbsp;';//'&#x25BC;'; 
			$sortDir = 0; //the next option
			$_SESSION['listParams']['orderDirection'] = $sortDir;
		}
		else 
		{
			$sortPic = '&nbsp;<i class="fa '.SABLE_DATALIST_SORT_DOWN.'" aria-hidden="true"></i>&nbsp;';//'&#x25B2';
			$sortDir = 1;
			$_SESSION['listParams']['orderDirection'] = $sortDir;
		}
				
		//Prepare the HTML headings:
		$html .= "  <tr>"; 
		 
		for ($x=0; $x < sizeof($_SESSION['listParams']['listHeadings']); $x++)
		{
			if (!empty($_SESSION['listParams']['listHeadingWidth'][$x])) //only work with headings that have values
			{			
				if ($x == $this->orderField) 
				{
					$sortArrow = $sortPic; 
					$sort = $sortDir; 
				}
				else 
				{
					$sort = "0"; //ascending as default
					$sortArrow = "";
				}
				
				//SableDebug::debug($this->params);
				if (is_array($this->params))
				{
					//SableDebug::debug($this->params);
					$params = '[';
					$cnt = false;
					foreach($this->params as $p) 
					{
						if ($cnt) $params .= ','.$p; else $params .= $p;
						$cnt = true;
					}
					$params .= ']';
				}
				
				$paramString = !empty($params) ? $x.", ".$sort.", ".$params : $x.", ".$sort.", 0";
				$cls 	= isset($_SESSION['listParams']['listHeadings'][$x]['action']) ? 'data_heading' : 'data_heading pointer link';
				$desc 	= is_array($_SESSION['listParams']['listHeadings'][$x]) ? 'Action' : $_SESSION['listParams']['listHeadings'][$x];
				$sortClick = isset($_SESSION['listParams']['listHeadings'][$x]['action']) ? '' : 'onclick="doSortClick('.$paramString.')"';
				$html .= "<td class='".$cls."' ".$sortClick." width='".
						$_SESSION['listParams']['listHeadingWidth'][$x]."'>".$desc.$sortArrow."</td>";
			}
			else $html .= "<td class='data_heading'>".$_SESSION['listParams']['listHeadings'][$x]."</td>";
		}
		$html .= "  </tr>";
		return $html;
	}
	
	private function getSearchFields()
	{
		$html	= '';
		$onclk	= '';
		
		//Add the search fields:
		$searchStringFields = explode('|', ($_SESSION['listParams']['searchString'] ?? ''));
		if (isset($_SESSION['listParams']['searchVisible']))
		{						
			$html = "  <tr>"; 			 
			for ($x=0; $x < sizeof($_SESSION['listParams']['listHeadings']); $x++)
			{
				if (empty($_SESSION['listParams']['listHeadings'][$x])) //no search on action fields
					$html .= "<td></td>";
				else
				{
					if (isset($_SESSION['listParams']['listHeadings'][$x]['action']))
					{
						$html .= "	<td>
										<div class='dropdown'>
											<select class='searchTextBox' onChange='".$_SESSION['listParams']['listHeadings'][$x]['event']."'>
												<option value='0'></option>";
						foreach($_SESSION['listParams']['listHeadings'][$x]['options'] as $opt)
						{
							$onclk = $opt['onclick'] ?? "";
							$html .= 			"<option onclick='".$onclk."'>".$opt['caption']."</option>";
						}
						$html .= "
											</select>
										</div>
									</td>";						
					}
					else
					{
						$ssf = $searchStringFields[$x] ?? '';
						$html .= "<td><input style='vertical-align:middle' type='text' id='id_".
								$_SESSION['listParams']['listFields'][$x]['field']."' class='searchTextBox' placeholder='&#xF002;'
								 onkeypress='handleEnter(this, event)' value='".$ssf."'/></td>";
					}
				}
			}
			$html .= "  </tr>";	
		}
		return $html;
	}
	
	private function removeRefinedQuery()
	{
		//clear the search data in the DB
		$res = $this->sdb->updateQuery("delete from list_query 
		where list_query_module_page = '{$this->modulePage}' 
		and list_query_module_folder = '{$_SESSION['listParams']['moduleFolder']}' 
		and user_code = {$_SESSION['user']['user_code']}");
		if ($res) 
		{
			$this->refinedList = 0; //ensure this cannot be a refined list anymore.
			$this->resetList	= 1; //we can reset the data list.
			$this->page				= 0;
			$this->offset			= $_SESSION['listParams']['offset'] ?? $this->offset;
			$this->orderField		= $_SESSION['listParams']['orderField'] ?? $this->orderField;
			$this->orderDirection	= $_SESSION['listParams']['orderDirection'] ?? $this->orderDirection;
			$this->params			= '';			
		}
	}
	
	private function checkForRefinedQuery()
	{
		/**
		This feature has been implemented so that the app will 'remember' the search/filter used on a data list.
		If you click, for example, on the view icon after applying a search/filter on a data list, then after viewing you click 'back', then you get the full list again. This feature restores that previous search/filter/sort
		**/
		if ($this->resetList)
		{
			$this->removeRefinedQuery();
		}
		else
		{
			if (!$this->refinedList)
			{
				$sql = "select * 
						from list_query 
						where list_query_module_page = '{$_SESSION['listParams']['modulePage']}' 
						and list_query_module_folder = '{$_SESSION['listParams']['moduleFolder']}' 
						and user_code = {$_SESSION['user']['user_code']}";
				//SableDebug::debug($sql);
				$row = $this->sdb->getRow($sql);	
				if (!empty($row))
				{
					$this->page				= $row['list_query_page'];
					$this->offset			= $row['list_query_offset'];
					$this->orderField		= $row['list_query_order_field'];
					$this->orderDirection	= $row['list_query_direction'];
					$this->outputField		= $row['list_query_output_field'];
					$this->params			= $row['list_query_params'];

					$_SESSION['listParams']['searchString'] = $this->params;
					$this->refinedList = 1; //ensure we work with this refined list.
				}
			}
		}
	}
	
	private function storeRefinedQuery()
	{
		//only store the query if there has been a refinement added:
		//$tmp = str_replace('|', '', $this->params); //ensure we have values
		//if (empty($tmp))
		if ($this->refinedList)
		{
			$ins = "insert into list_query 
					(list_query_page, list_query_offset, list_query_order_field, list_query_direction, list_query_output_field, 
					list_query_params, list_query_module_page, list_query_module_folder, user_code) 
					values 
					({$this->page}, {$this->offset}, {$this->orderField}, {$this->orderDirection}, '{$this->ouputField}', 
					'{$this->params}', '{$this->modulePage}', '{$_SESSION['listParams']['moduleFolder']}', 
					{$_SESSION['user']['user_code']}) 
					on duplicate key update 
					list_query_page = {$this->page},
					list_query_offset = {$this->offset},
					list_query_order_field = {$this->orderField},
					list_query_direction = {$this->orderDirection},
					list_query_output_field = '{$this->ouputField}',
					list_query_params = '{$this->params}',
					list_query_module_page = '{$this->modulePage}',
					list_query_module_folder = '{$_SESSION['listParams']['moduleFolder']}'";
			return $this->sdb->updateQuery($ins);	
		}
		else
		{
			$this->removeRefinedQuery();
		}
	}
	
	private function processQuery()
	{
		//if there is a search string, then we need to build up the SQL statement with the various where statements.
		//We check the parameters for the searchable fields:
		$sql = $this->getSearchString($_SESSION['listParams']['listSql']);
		unset($this->sql);
		
//		if ($this->page)	$_SESSION['listParams']['page']		= $this->page; else $_SESSION['listParams']['page'] = 0;
		if ($this->offset)	$_SESSION['listParams']['offset']	= $this->offset; //else $_SESSION['listParams']['offset'] = 30; //default
		
		//page must always start at zero.
		if ($_SESSION['listParams']['page'] < 0) $_SESSION['listParams']['page'] = 0;
		
		if (isset($_SESSION['listParams']['groupField']) &&!empty($_SESSION['listParams']['groupField']))
		{
			$sql .= " group by ".$_SESSION['listParams']['groupField']; //could be multiple fields separated by commas
		}
				
		//if there is an order statement then add it:
		$vidx = is_numeric($this->orderField) ? $this->orderField : 0;	
		$sls = $_SESSION['listParams']['searchField'][$vidx] ?? 0;
			
		$vval = is_array($sls) ? $sls['title'] : $sls;
		$sql .= empty($vval) ? "" : " order by ".$vval;

		if ($this->orderDirection)	$sql .= " DESC";
		
		//Save the current SQL for the export before adding limit information:
		$this->exportSQL = $sql;
		
		//add the sql limit clause based on the page and offset... for example: limit 0, 30:
		
		/**
		$listFrom	= ($_SESSION['listParams']['page'] * $_SESSION['listParams']['offset']);
		$listTo		= $_SESSION['listParams']['offset'];
		**/
		
		$listFrom = $this->page ? $this->page:$_SESSION['listParams']['page'];
		$listFrom = $this->page * $this->offset;
		$listTo   = $this->offset?$this->offset:$_SESSION['listParams']['offset'];
		
		
		$sql .= " limit ".$listFrom.", ".$listTo;
		//SableDebug::debugx($sql);
		
		//add sql statement to produce a limit of, say, 30 items per page. (i.e. paging);
		$this->sdb->useCallingSite= isset($_SESSION['listParams']['useCallingSite'])?$_SESSION['listParams']['useCallingSite']:false;	
		$this->queryRows = $this->sdb->getRows(strtolower($sql));
		
		//SableDebug::debug($this->queryRows);

		$this->cnt 		= $this->sdb->recordCount;
		$this->fieldCnt = $this->sdb->fieldCount;
	}
	
	public function showDataList()
	{
		$this->checkForRefinedQuery();
		$this->processQuery();
		$html = "";
		//if (!isset($_SESSION['listParams']['orderField'])) $_SESSION['listParams']['orderField'] = 0;
		//if (empty($this->orderField)) $this->orderField = $_SESSION['listParams']['orderField'];
		
		$this->moduleNiceName = (isset($_SESSION['listParams']['moduleFolderDesc'])&&(!empty($_SESSION['listParams']['moduleFolderDesc'])))?
								SableGeneralFunctions::getFormattedModuleName($_SESSION['listParams']['moduleFolderDesc']):
								SableGeneralFunctions::getFormattedModuleName($_SESSION['listParams']['module']);
		
		$v2 = $this->page * $this->offset;
		$v1 = $v2 + 1; 
		$v4 = $this->cnt;
		$v3 = $v2 + $v4;
		
		$showingList= " (showing ".$v1." to ".$v3.").";
		/**
		
		if (isset($_SESSION['listParams']['searchString']) && !empty($_SESSION['listParams']['searchString']))
			$title = "Searching for '" . $_SESSION['listParams']['searchString'] . "' in " . $_SESSION['listParams']['module'].$showingList;
		else
		{
			$title = "Displaying ".$this->moduleNiceName." records".$showingList;
		}
		**/
		if ($this->refinedList || empty($_SESSION['listParams']['searchString']))
		{
			$title = "Displaying ".$this->moduleNiceName." records".$showingList;
		}
		else
		{
			$title = "Searching for '" . $_SESSION['listParams']['searchString'] . "' in " . $_SESSION['listParams']['module'].$showingList;
		}
			
		$this->path = (isset($_SESSION['listParams']['moduleFolder']))?$_SESSION['listParams']['moduleFolder']."/":$_SESSION['current_dir']."/";
			
		$this->modulePage = (isset($_SESSION['listParams']['modulePage']))?$_SESSION['listParams']['modulePage']:'index.php';
		
		$this->link = $this->path.$this->modulePage;
		$this->link = ltrim($this->link, '/');
		$this->link = '/'.$this->link;
		//$this->link = $this->page
		
		//$menuFile = $path.'/menu.php';
		//convertURLPathToWorkPath($menuFile);
		$html .= "<script src='".SABLE_DOMAIN_ROOT.$_SESSION['listParams']['moduleFolder']."/".$_SESSION['listParams']['moduleFolder'].".js?v=".SABLE_VERSION."' ></script>";
		$html .= "<script src='".SableGeneralFunctions::getModuleJS('list')."' ></script>";
		$html .= "<div id='data_list' class='dataList' align='center' style='overflow-x:scroll;'>";		
		$html .= "<form id='frmDataList' name='frmDataList' >";
		$html .= "<table border='0' id='tblDataList'>"; //.table-bordered
		$this->colspan = sizeof($_SESSION['listParams']['listFields']);
		
		$html .= $this->getToolBar();
		$html .= "<tr>
					<td colspan='".sizeof($_SESSION['listParams']['listFields'])."'>
						<p><h4>&nbsp;".$title."</h4></p>
					</td>
				 </tr>";		
		
		$html .= $this->showNavButtons();
		if ($this->cnt==0) 
		{					
			$html .= "<tr><td colspan='".$this->fieldCnt."'>No records were found or the end of the list has been reached.</td></tr>";
			$html .= $this->showNavButtons();		
		}
		else
		{
			$html .= $this->getTableHeadings();			
			$html .= $this->getSearchFields();
			
			//now we add the data:
			
			for($i=0; $i < $this->cnt; $i++)
			{
				if(($i%2)==0)
				{
					$class="class='sable_data_row'";
					$classLink="class='link_contrast'";
				}
				else
				{
					$class="class='sable_data_row_alt'";
					$classLink="class='link_contrast'";
				}
				
				$classLink .= " style='white-space: nowrap; overflow: hidden; text-overflow: ellipsis; cursor: pointer; max-width:300px;'"; //" style='white-space:nowrap; cursor: pointer'";
				
				$row = $this->queryRows[$i];
				//$this->colspan = sizeof($_SESSION['listParams']['listFields']);
				
				$html .= "  <tr ".$class.">"; 
				
				//Apply data formatting:
				for ($x=0; $x < $this->colspan; $x++)
				{
					//first initialise:
					$val = "";
					$valOnClick = "";
					$valEmailLink = "";
					$valUrl = "";
					$valLink = "";
					$valImage = "";
					
					/*
					The "depends on" set is to display values based on the value of another fields value. This need arises from the fact
					that we cannot use a conditional result in a join statement (see devices/view_pending_allocations.php). In this example
					we have the following SQL statement:
						---------------------------------------------------------------------------------------------------------------
						select device_allocation_id, hardware_release.hardware_release_desc, serial_no, 
						date(device_allocation_date) as allocation_date, before_class.class_desc as class_desc_before,
						after_class.class_desc as class_desc_after, prog_lock_no_before, prog_lock_no_after, deposit_paid, 
						concat(user_name,' ',user_surname) as user_full_name, kits.kit_no, device_allocation_approved, kits.kit_deposit
						from device_allocation 
						inner join devices on devices.device_id = device_allocation.device_id
						inner join user on user.user_code = device_allocation.user_code
						inner join classification before_class on (before_class.class_id = device_allocation.class_id_before)
						inner join classification after_class on (after_class.class_id = device_allocation.class_id_after)
						inner join hardware_release on hardware_release.hardware_release_id = devices.hardware_release_id
						inner join kits on (kits.kit_id = devices.kit_id) 
						where ((device_allocation_approved = 0) or (invoice_no = 0))
						---------------------------------------------------------------------------------------------------------------
					In this case - see the last join - we only want to see the kit_deposit (amount) when devices.deposit_paid is true (1).
					We can now achieve this by using the "depends_on_field" ultimately like displaying the kit_deposit which depends on
					the value of another field. We need to provide the other field, the condition and value and the result can be
					determined.
					
					depends_on_else_onClick & depends_on_result_onClick were introduced so that we can still maintain a cursor pointer
					on rows that are displayed, and remove the cursor pointer when they are not displayed (see devices/view.php "Replace Device").
					*/
					if (isset($_SESSION['listParams']['listFields'][$x]['depends_on_operator']))
					{
						$testValue		= isset($_SESSION['listParams']['listFields'][$x]['depends_on_constant_test'])?
										$_SESSION['listParams']['listFields'][$x]['depends_on_constant_test']:
										$row[$_SESSION['listParams']['listFields'][$_SESSION['listParams']['listFields'][$x]['depends_on_field_test']]['field']];
										
						$compareValue	= isset($_SESSION['listParams']['listFields'][$x]['depends_on_constant_compare'])?
										$_SESSION['listParams']['listFields'][$x]['depends_on_constant_compare']:
										$row[$_SESSION['listParams']['listFields'][$_SESSION['listParams']['listFields'][$x]['depends_on_field_compare']]['field']];
																					
						$resultValue	= isset($_SESSION['listParams']['listFields'][$x]['depends_on_constant_result'])?
										$_SESSION['listParams']['listFields'][$x]['depends_on_constant_result']:
										$row[$_SESSION['listParams']['listFields'][$_SESSION['listParams']['listFields'][$x]['depends_on_field_result']]['field']];
						
						$elseValue		= isset($_SESSION['listParams']['listFields'][$x]['depends_on_constant_else'])?
										$_SESSION['listParams']['listFields'][$x]['depends_on_constant_else']:
										$row[$_SESSION['listParams']['listFields'][$_SESSION['listParams']['listFields'][$x]['depends_on_field_else']]['field']];
										
						$operator		= $_SESSION['listParams']['listFields'][$x]['depends_on_operator'];
						//SableDebug::debug('if "'.$testValue.'"'.$operator.'"'.$compareValue.'" then result = "'.$resultValue.'" else result = "'.$elseValue).'"';
						$val='---'; //dummy value required to ensure this variable is not empty
						if ($operator == '>') 	$val = ($testValue >  $compareValue)?$resultValue:$elseValue;
						if ($operator == '>=') 	$val = ($testValue >= $compareValue)?$resultValue:$elseValue;
						if ($operator == '!=') 	$val = ($testValue != $compareValue)?$resultValue:$elseValue;
						if ($operator == '==') 	$val = ($testValue == $compareValue)?$resultValue:$elseValue;
						if ($operator == '<')	$val = ($testValue <  $compareValue)?$resultValue:$elseValue;
						if ($operator == '<=')	$val = ($testValue <= $compareValue)?$resultValue:$elseValue;
//						if (isset($_SESSION['listParams']['listFields'][$x]['currency'])) $val = "R " . number_format($val, 2);
																		
						if ($val == $resultValue)
						{
							if (isset($_SESSION['listParams']['listFields'][$x]['depends_on_result_onClick']))
							{
								$_SESSION['listParams']['listFields'][$x]['onClick'] = $_SESSION['listParams']['listFields'][$x]['depends_on_result_onClick'];
								$_SESSION['listParams']['listFields'][$x]['field'] = $val;
							}
						}
						if ($val == $elseValue)
						{
							if (isset($_SESSION['listParams']['listFields'][$x]['depends_on_else_onClick']))
							{
								$_SESSION['listParams']['listFields'][$x]['onClick'] = $_SESSION['listParams']['listFields'][$x]['depends_on_else_onClick'];
								$_SESSION['listParams']['listFields'][$x]['field'] = $val;
							}
						}						
					}
					
					if (empty($val)) 
					{
						$val = $row[$_SESSION['listParams']['listFields'][$x]['field']] ?? '';
						
						if (isset($_SESSION['listParams']['listFields'][$x]['currency'])) 
							$val = "R " . number_format(floatval($val), 2, '.', ', ');
						
						if (isset($_SESSION['listParams']['listFields'][$x]['number'])) 
						{
							if (isset($_SESSION['listParams']['listFields'][$x]['precision'])) 
								$val = number_format(floatval($val), $_SESSION['listParams']['listFields'][$x]['precision']);
							elseif (isset($_SESSION['listParams']['listFields'][$x]['sprintf']))
								$val = sprintf($_SESSION['listParams']['listFields'][$x]['sprintf'], intval($val));
						}						
						
					}
					
					if (isset($_SESSION['listParams']['listFields'][$x]['prefix']))
					{
						$val = $_SESSION['listParams']['listFields'][$x]['prefix'].$val;
					}
					if (isset($_SESSION['listParams']['listFields'][$x]['suffix']))
					{
						$val .= $_SESSION['listParams']['listFields'][$x]['suffix'];
					}
					
					if (isset($_SESSION['listParams']['listFields'][$x]['image']))
					{
						$valImage = $_SESSION['listParams']['listFields'][$x]['field'];
						$valImageSize = (isset($_SESSION['listParams']['listFields'][$x]['image_size'])&&
										!empty($_SESSION['listParams']['listFields'][$x]['image_size']))?
										$_SESSION['listParams']['listFields'][$x]['image_size']:100;
					}
										
					if (isset($_SESSION['listParams']['listFields'][$x]['time']))
					{
						$format = empty($_SESSION['listParams']['listFields'][$x]['format']) ? 'H:i:s' : $_SESSION['listParams']['listFields'][$x]['format'];
						$val = $row[$_SESSION['listParams']['listFields'][$x]['field']]?
								date($format, strtotime($row[$_SESSION['listParams']['listFields'][$x]['field']])) : "";
					}
					
					if (isset($_SESSION['listParams']['listFields'][$x]['date']))
					{
						$format = empty($_SESSION['listParams']['listFields'][$x]['format']) ? 'Y-m-d' : $_SESSION['listParams']['listFields'][$x]['format'];
						$val = $row[$_SESSION['listParams']['listFields'][$x]['field']]?
								date($format, strtotime($row[$_SESSION['listParams']['listFields'][$x]['field']])) : "";
					}
					
					if (isset($_SESSION['listParams']['listFields'][$x]['datetime']))
					{
						$format = empty($_SESSION['listParams']['listFields'][$x]['format']) ? 'Y-m-d H:i:s' : $_SESSION['listParams']['listFields'][$x]['format'];
						$val = $row[$_SESSION['listParams']['listFields'][$x]['field']]?
							date($format, strtotime($row[$_SESSION['listParams']['listFields'][$x]['field']])) : "";
					}					
					
					if (isset($_SESSION['listParams']['listFields'][$x]['link']))
					{
						$id = SableSystem::sableEncode($row[$_SESSION['listParams']['moduleIdField']]);
						$valLink = $_SESSION['listParams']['listFields'][$x]['link'];
						$valLink = str_replace("--#--", $id, $valLink);
						$valLink = str_replace("--!--", $val, $valLink);
						$valLink = str_replace("--^--", SableSystem::sableEncode(session_id()), $valLink); //for session id
					}
					
					if (isset($_SESSION['listParams']['listFields'][$x]['fontawesome']))
					{
						$valFontSize = (isset($_SESSION['listParams']['listFields'][$x]['font_size'])&&
										!empty($_SESSION['listParams']['listFields'][$x]['font_size']))?
										$_SESSION['listParams']['listFields'][$x]['font_size']:32;						
						$val = '<span style="font-family:\'Font Awesome 5 Pro\', \'Font Awesome 5 Brands\'; font-size:'.$valFontSize.'px;">'.$row[$_SESSION['listParams']['listFields'][$x]['field']].'</span>';
					}					
					
					
					if (isset($_SESSION['listParams']['listFields'][$x]['yesNo'])) 
					{
						$on  = isset($_SESSION['listParams']['listFields'][$x]['yesNoOn']) ? $_SESSION['listParams']['listFields'][$x]['yesNoOn'] : 1;
						
						$yay = isset($_SESSION['listParams']['listFields'][$x]['yesNoYes'])?$_SESSION['listParams']['listFields'][$x]['yesNoYes']:"Yes";
						$nay = isset($_SESSION['listParams']['listFields'][$x]['yesNoNo'])?$_SESSION['listParams']['listFields'][$x]['yesNoNo']:"No";
						$val = $val==$on?$yay:$nay;
					}
					
					$valEmailLink = (isset($_SESSION['listParams']['listFields'][$x]['emailLink']));							
					$valPhoneLink = (isset($_SESSION['listParams']['listFields'][$x]['phoneLink']));
					$valFileLink  = (isset($_SESSION['listParams']['listFields'][$x]['fileLink']));
					$valUrl = (isset($_SESSION['listParams']['listFields'][$x]['url']));
					
					if (isset($_SESSION['listParams']['listFields'][$x]['onClick']))
					{
						$id = SableSystem::sableEncode($row[$_SESSION['listParams']['moduleIdField']]);
						$val = $row[$_SESSION['listParams']['listFields'][$x]['field']] ?? '';

						/*						
						$tagArray = explode(' ',$_SESSION['listParams']['listFields'][$x]['field']);
						if (in_array(array('<img', '<input'), $tagArray)) 
						{
							echo('...testing, please scroll down for your data...');
							$val = $_SESSION['listParams']['listFields'][$x]['field'];
						}
						*/
						
						if (strpos($_SESSION['listParams']['listFields'][$x]['field'], 'img') > 0)
						{
							$val = $_SESSION['listParams']['listFields'][$x]['field'];
						}
						
						if (strpos($_SESSION['listParams']['listFields'][$x]['field'], 'input') > 0)
						{
							$val = $_SESSION['listParams']['listFields'][$x]['field'];
						}
						
						if (strpos($_SESSION['listParams']['listFields'][$x]['field'], 'fa-') > 0)
						{
							$val = $_SESSION['listParams']['listFields'][$x]['field'];
						}						
						
									
						//$valOnClick becomes the "onclick" for the <td> tag:
						$fieldDescription = $row[$_SESSION['listParams']['moduleDescField']??'']??'';
						//&#39; ==> SINGLE Quote
						$fieldDescription = str_replace("'", "&#39;", $fieldDescription);
						//&#34; --> DOUBLE Quote
						$fieldDescription = str_replace("'", "&#34;", $fieldDescription);						
						$val = str_replace("--!--", $fieldDescription, $val);	
						$val = str_replace("--#--", $id, $val);				
						$valOnClick = $_SESSION['listParams']['listFields'][$x]['onClick'];						
						$valOnClick = str_replace("--#--", $id, $valOnClick); //item id						
						$valOnClick = str_replace("--!--", $fieldDescription, $valOnClick); //item description
						$valOnClick = str_replace("--^--", SableSystem::sableEncode(session_id()), $valOnClick); //for session id
					}										
					
					/*
					//let's highlight the results:
					if (isset($_SESSION['listParams']['searchString']) && !empty($_SESSION['listParams']['searchString']))
					{
						$hilite = "<span class='searchResultMarker'>".$_SESSION['listParams']['searchString']."</span>";
						$val = str_ireplace($_SESSION['listParams']['searchString'], $hilite, $val);
					}
					*/					
					
					if ($valEmailLink) //if this is an email field, add the correct "mailto:" link:
					{					
						$html .= "<td ".$classLink."><a ".$classLink." href='mailto:".$val."' title='".$val."'>".$val."</a></td>";
					}
					elseif (!empty($valImage))
					{
						//if this record is marked as an image and a link has been assigned to it, then
						if (empty($val)) $val = SABLE_UNKNOWN_IMAGE;
						$workingValImage = $val;
						SableSystem::convertURLPathToWorkPath($workingValImage);
						$workingValImage = SABLE_WORKING_PATH.ltrim($workingValImage, '/');
								
						if (!file_exists($workingValImage))
						{
							if (SABLE_UNKNOWN_IMAGE)
							{
								$workingValImage = SABLE_UNKNOWN_IMAGE;
								SableSystem::convertURLPathToWorkPath($workingValImage);
							}
						}
						
						$width  = 1;
						$height = 1;
						if (!empty($workingValImage)) SableSystem::setImageSize($valImageSize, $workingValImage, $width, $height);
						
						$imgln = "<div style='background-size:contain; background-repeat:no-repeat; width: ".$width."px; height: ".$height."px !important; background-image:url(\"".$val."\"); background-position:center center; background-color:white;'></div>";
						
						//use the onclick defined, else open the image for viewing:\
						if (!empty($valOnClick))
							$html .= "<td ".$classLink." ".$valOnClick.">".$imgln."</td>";
						else
							$html .= "<td ".$classLink." onclick='viewImage(\"".$val."\")'>".$imgln."</td>";
					}
					elseif (!empty($valLink))
					{
						$html .= "<td ".$classLink." ".$valLink.">".$val."</td>";
					}					
					elseif ($valPhoneLink)
					{
						$html .= "<td ".$classLink."><a ".$classLink." href='tel:".$val."' title='".$val."'>".$val."</a></td>";
					}
					elseif ($valFileLink)
					{
						$html .= "<td ".$classLink."><a ".$classLink." href='".$val."' target='_blank' title='".$val."'>".$val."</a></td>";
					}					
					elseif (!empty($valUrl)) //if this is an url, then create the link to the site.
					{
						if (strpos($val, SABLE_PROTOCOL) === false)
							$html .= "<td ".$classLink."><a ".$classLink." href='".SABLE_URL_ROOT.ltrim($val, '/')."' target='new' title='".$val."'>".$val."</a></td>";
						else
							$html .= "<td ".$classLink."><a ".$classLink." href='".$val."' title='".$val."'>".$val."</a></td>";	
					}
					elseif (!empty($valOnClick)) //if there is an onClick event specified, then add it:				
					{
						$html .= "<td ".$classLink." ".$valOnClick.">".$val."</td>";
					}
					else
					{
						$html .= "<td >".$val."</td>";
					}
				}
				$html .= "  </tr>";
			}
			
			$html .= $this->showNavButtons();
			
		} // if ($this->cnt==0)
		
		$html .= "<tr><td height='100%' colspan='".($this->fieldCnt+5)."'>&nbsp;</td></tr>"; //width='100%'
		$html .= "</table>";
		//our hidden input fields for ajax return:
		$html .= "<input id='sId' type='hidden' value='".SableSystem::sableEncode(session_id())."' />";
		$html .= "<input id='url' type='hidden' value='".$this->link."' />";
		
		$html .= "<input id='page' type='hidden' value='".$this->page."' />";
		$html .= "<input id='offset' type='hidden' value='".$this->offset."' />";
		$html .= "<input id='orderField' type='hidden' value='".$this->orderField."' />";
		$html .= "<input id='orderDirection' type='hidden' value='".$this->orderDirection."' />";
		//$html .= "<input id='sql' type='hidden' value='".$sql."' />";
		
//		$html .= "<input id='url' type='hidden' value='list/index.php' />";
		$html .= "</form></div>";
		
		$this->storeRefinedQuery();
		
		if (empty($_POST)) echo($html); else SableSystem::ajaxFillField($html, $this->ouputField);
		
	} // function showDataList()
	
	private function showNavButtons()
	{
		// Add a row for the paging navigation:
		$colspanPrev = ceil($this->colspan / 2);
		$colspanPrev = $this->colspan - $colspanPrev;
		if ($this->colspan < 1) $this->colspan = 1;
		if ($colspanPrev < 1) $colspanPrev = 1;
		if ($colspanPrev < 1) $colspanPrev = 1;
		
		$prevVal = 0;
		$nextVal = 0;
		//SableDebug::debug($this);
		$prevVal = $this->page - 1; //$_SESSION['listParams']['page'] - 1;
		if ($prevVal < 0) $prevVal = 0;
		$nextVal = $this->page + 1; //$_SESSION['listParams']['page'] + 1;
		
		/*
		$prevOnClick = " onclick='window.location = \"".SableSystem::getCallPageLink($this->link.'?'.$prevVal."+".$this->offset."+".$this->orderField."+".$this->orderDirection)."\"' ";
		$nextOnClick = " onclick='window.location = \"".SableSystem::getCallPageLink($this->link.'?'.$nextVal."+".$this->offset."+".$this->orderField."+".$this->orderDirection)."\"' ";	
		*/
		
		$parmArray = [];
		$parmArray['sId'] = session_id();
		$parmArray['offset'] = $this->offset;
		$parmArray['orderField'] = $this->orderField;
		$parmArray['orderDirection'] = $this->orderDirection;
		
		$parmArray['page'] = $prevVal;
		$prevx = "'".session_id()."', ".$this->offset.", ".$prevVal.", ".$this->orderField.", ".$this->orderDirection;//json_encode($parmArray);
		
		$parmArray['page'] = $nextVal;
		//page(sId, offset, page, orderField, orderDirection)
		$nextx = "'".session_id()."', ".$this->offset.", ".$nextVal.", ".$this->orderField.", ".$this->orderDirection;//json_encode($parmArray);
		
		$prevOnClick = ' onclick="pagenav('.$prevx.')"';
		//$nextOnClick = ' onclick="page('.$nextx.')"';
		$nextOnClick = ' onclick="pagenav('.$nextx.')"';
		
		$style = "style='height:28px; width:auto;'";
		
		//if (($this->page > 0) || ($this->cnt <= 0))
		if ($this->page > 0)
		{
			$prev = '<td class="pointer" colspan="'.$colspanPrev.'" align="left" '.$prevOnClick.'>
							<img src="'.SableSystem::getSableImgPath('prior.png').'" title="View previous page" 
								'.$style.' class="img-responsive"/>
					</td>';
		}
		else
		{
			$prev = "<td>&nbsp;</td>";
			$colspanPrev = $this->colspan;
		}
			
		if ($this->cnt >= $this->offset)
		{
			$next = '<td class="pointer" colspan="'.$colspanPrev.'" align="right" '.$nextOnClick.'>
							<img src="'.SableSystem::getSableImgPath('next.png').'" title="View next page" 
								'.$style.' class="img-responsive"/>
					</td>';
		}
		else
		{
			$next = "<td>&nbsp;</td>";
			$colspanPrev = $this->colspan;
		}
			
		return "<tr>".$prev."<td>&nbsp;</td>".$next."</tr>";
	}
	
} /* Class DataList */