<?
	function clientstaff_get($clientstaffid){
		$sql = "select	*
				from	tblclientstaff
				where	clientstaffid = " . db_number($clientstaffid);
		$clientstaff = db_first($sql);
		
		if($clientstaff)
			return $clientstaff;
		else
			return false;
	}
	
	function get_clientstaffids_withsubs($ids,$limitroleid=0)
	{
		//optional parameter limitroleid is to be used to ONLY show clientstaffids which are below the current user in their capacity within limitroleid
		//forexample say that clientstaffid=5 is a member of roleid=10 and roleid=11,  and they have staff assigned to them in both roles,  if limitroleid=10 is passed in only members which belong to roles which are below roleid=10 in the heirarchy will be included in the list.
		if(!$ids)
			return "0";
		if($limitroleid>0)
		{
			//get a list of roles below $limitroleid
			$onlystaffinroles = get_sub_roleidlist($limitroleid);
			if($onlystaffinroles)
			{
				$andsql.=" and exists (select 1 from tblloginrole  join tbllogin using(loginid) where clientstaffid  = tblclientstaff.clientstaffid and roleid in ($onlystaffinroles) and roleid !=$limitroleid) ";
			}
		}
		$sql = "select clientstaffid from tblclientstaff where (clientstaffid in ($ids) or (clientstaffparent in ($ids) $andsql )) ";
	
		$sql.=" order by clientstaffid";
		//d($sql);
		$qry= db_query($sql,1,"",clientstaffid);
		$newids = implode(',',array_keys($qry));
		if($newids==$ids)
			return $ids;
		return get_clientstaffids_withsubs($newids,$limitroleid) ;
	}
	function get_clientstaff_name($id)
	{
		if(!$id)
			return "";
		$sql = "select clientstafffname||' '||clientstafflname as name from tblclientstaff join tbllogin using(clientstaffid) where clientstaffid = ".db_number($id);
		if(clientcompanyid())
			$sql .= " and clientcompanyid = ".clientcompanyid();
		//d($sql);
		$qry= db_first($sql,1,"",clientstaffid);
		return  $qry[name];
	}
	function get_sub_clientstaffidlist($clientstaffidlist)
	{
		if(!clientcompanyid())
			ddie("Illegal checking of clientstaffidlist from an invalid client company");
		if(!$clientstaffidlist)
			return 0;
		$sql = "select group_concat(clientstaffid) as clientstaffidlist from tblclientstaff where clientstaffid in ($clientstaffidlist) or clientstaffparent in ($clientstaffidlist)";
		$qryl  = db_first($sql);
		if(!$qryl[clientstaffidlist])
			return 0;
		if($qryl[clientstaffidlist] == $clientstaffidlist)
			return $clientstaffidlist;
		return get_sub_clientstaffidlist($qryl[clientstaffidlist]);
	}
	function get_rolepropertyrestriction($clientstaffid=0)
	{
		if(!$clientstaffid)
			$clientstaffid=clientstaffid();
		if(!$clientstaffid)
			return false;
		
		global $rolepropertyrestrictions;
		if(!isset(	$rolepropertyrestrictions[$clientstaffid]))
		{
			$roles = db_query("select tblrole.* from tblrole join tblloginrole using(roleid) join tbllogin using (loginid) where clientstaffid = ".$clientstaffid,1,0,roleid);

			//loop through all of my roles and set the most restrictive limitation,  overwrite it with the least restrictive limitatio
			//d($roles);
			foreach($roles as $role)
				if($role[rolepropertyrestrictions]==ASSIGNEDTOME)
					$restriction=ASSIGNEDTOME;
			foreach($roles as $role)
				if($role[rolepropertyrestrictions]==VISIBLETOSUBS)
					$restriction=VISIBLETOSUBS;
			foreach($roles as $role)
				if(!$role[rolepropertyrestrictions])
					unset($restriction);
			$rolepropertyrestrictions[$clientstaffid]=$restriction;
		}
		return $rolepropertyrestrictions[$clientstaffid];
	}
	function set_roles($force=0)
	{
		if(!isset($_SESSION[login][roles] ) || $force)
			$_SESSION[login][roles] = db_query("select tblrole.* from tblrole join tblloginrole using(roleid) where loginid = ".loginid(),1,0,roleid);
		return get_roles();
	}
	function get_roles()
	{
		
		if(!isset($_SESSION[login][roles] )  )
			set_roles();
		return $_SESSION[login][roles] ;
	}
	function has_role_executive()
	{
		//check if the user is in the executive role
		$sess = $_SESSION[login][roles];
		//unset($sess);
		//$sess = array(); // do this ONLY to force false return for testing
		if (is_array($sess))
			if(in_array(1, array_keys($sess)))
				return true;
		return false;
	}
	function get_rolesidlist()
	{
		return implode(',',array_keys(get_roles()));
	}
	function get_sub_roleidlist($roleidlist=-1)
	{
		if(!$roleidlist)
			return "0";
		set_roles();
		if($_SESSION[login][roles][1]) //executive role does not have any restrictions
			return "0";
		if($roleidlist==-1)
		{
			$roleidlist = get_rolesidlist();
		}
		if(!$roleidlist)
			return "0";
		$sql = "select group_concat(roleid) as roleidlist from tblrole where roleid in ($roleidlist) or roleparent in ($roleidlist)";
		$qryl  = db_first($sql);
		//d($sql,$qryl);
		if(!$qryl[roleidlist])
			return 0;
		if($qryl[roleidlist] == $roleidlist)
			return $roleidlist;
		return get_sub_roleidlist($qryl[roleidlist]);
	}
	function is_my_rolename($nm)
	{
	
		if(!isset($_SESSION[login][roles] ) || $force)
			set_roles();
		
		foreach($_SESSION[login][roles] as $roleid =>$role)
			if($nm==$role[rolename])
				return true;
		
		return false;
	}
	function get_clientstaff_assignedtome($clientstaffid=0)
	{
		if(!$clientstaffid)
			$clientstaffid = clientstaffid();
		if(!$clientstaffid)
			return array();
		$sql = "select * from tblclientstaff join tbllogin using (clientstaffid) where clientstaffparent = ".db_number($clientstaffid)." and logininactive is null";
		$qry = db_query($sql);
		 
		return $qry;
	}
	function get_loginid_fromclientstaffid($clientstaffid)
	{
		global $get_loginid_fromclientstaffid_cache;
		if(!isset($get_loginid_fromclientstaffid_cache[$clientstaffid]))
		{
			$get_loginid_fromclientstaffid_cache[$clientstaffid] = db_firstval("select loginid from tbllogin where clientstaffid>0 and clientstaffid = ".db_number($clientstaffid));
		}
		return $get_loginid_fromclientstaffid_cache[$clientstaffid];
	}
	function validate_statelist($userstatelist, $statelist='')
	{
		if (trim($userstatelist) == '')
			return '';
		if (!is_array($userstatelist))
			$userstatelist = explode(' ', trim(preg_replace('/[^A-Z]+/', ' ', strtoupper($userstatelist))));
		if (!$statelist)
			$statelist = get_fullstatelist();
		else if (is_array($statelist))
			$statelist = implode(',',$statelist);
		d('is_validstatelist userstatelist', $userstatelist);
		foreach ($userstatelist as $userstate)
		{
			//d("checking: {$userstate} against: {$statelist}");
			if (trim($userstate) != '' && !strstr($statelist, strtoupper($userstate)))
				return false;
		}
		return implode(',', $userstatelist);
	}
	function get_fullstatelist()
	{
		return implode(',', array_keys(db_query('select code from tblstate', 1, 0, 'code')));
	}
	function get_clientstaff_restrictedstates($clientstaffid)
	{
		if($clientstaffid < 1)
			return array();
		$stateslist = db_firstval("select clientstaffrestrictedstates from tblclientstaff where clientstaffid = {$clientstaffid}");
		if (trim($stateslist) != '')
			return trim(preg_replace('/[^A-Z]+/', ',', strtoupper($stateslist)));
		else
			return array();
	}
	function get_statecode($state)
	{
		$rval = '';
		$state = trim(preg_replace('/[^A-Z]/', '', strtoupper($state)));
		if ($state != '')
		{
			$sql = '
				select code state, code from tblstate
				union 
				select upper(name) state, code from tblstate
				order by code';
			$codes = db_query($sql, 1, 0, state);
			if ($codes[$state][code])
				$rval = $codes[$state][code];
		}
		return $rval;
	}
	function get_stateaccountmanager($state)
	{
		$code = get_statecode($state);
		if ($code)
		{
			$rolesel = "
					select roleid
					from tblrole
					where rolename = 'Account Manager'
						and clientcompanyid = ".clientcompanyid();
			$rolesql = "
				insert into tblrole (rolename, rolecreatedby, rolecreateddate, clientcompanyid)
				select 'Account Manager', ".loginid().", now(), ".clientcompanyid()."
				where not exists (
					{$rolesel}
				)";
			db_exec($rolesql);
			$roleid = db_firstval($rolesel);

			$sql = "
				select clientstaffid, count(clientcompanypropertyroleid) managedpropertycount
				from tbllogin 
					join tblloginrole using (loginid)
					join tblclientstaff using (clientstaffid)
					left join tblclientcompanypropertyrole using (clientstaffid, roleid)
				where clientcompanyid = ".clientcompanyid()."
					and roleid = {$roleid} -- Account Manager
					and (clientstaffrestrictedstates ilike '%{$code}%' or clientstaffrestrictedstates is null)
				group by clientstaffid
				order by managedpropertycount
			";
			$qry = db_first($sql);
			$rval[clientstaffid] = $qry[clientstaffid];
			$rval[roleid] = $roleid;
			return $rval;
		}
		return false;
	}
?>