<?
function eval_unreadmessages($messagearr)
{
	if($messagearr && is_array($messagearr)) foreach($messagearr as $k=>$message)
    {
    	$tome = 0;
    	$message_tome=0;
    	
    	if($message[clientmessage_from_client])
    	{
    		if($message[clientmessage_assoc_clientcompanypropertyassignmentid])
    		{
	    		if($messagearr2[$message[clientmessageid]][loginid] == $messagearr2[$message[clientmessageid]][clientmessagecreatedby]) //Message sent by current user.
	    		{
		    		$message[from_role] = $messagearr2[$message[clientmessageid]][assignmentmethodname];
		    		$message[from_name] = $messagearr2[$message[clientmessageid]][staffname];
					$message[to_role] = $messagearr3[$message[clientmessageid]][assignmentmethodname];
	    			$message[to_name] = $messagearr3[$message[clientmessageid]][staffname];
	    		}
				else 											//Message sent to the current user.
				{
		    		$message[from_role] = $messagearr3[$message[clientmessageid]][assignmentmethodname];
		    		$message[from_name] = $messagearr3[$message[clientmessageid]][staffname];
		    		$message[to_role] = $messagearr2[$message[clientmessageid]][assignmentmethodname];
		    		$message[to_name] = $messagearr2[$message[clientmessageid]][staffname];
					if(!$message[messagebetweenassignedusers])
					{
						$message_tome = 1;
						$tome |= 1;
					}
				}
    		}
    		else
    		{
    			//d('is it for me',$message);
	    		$message[from_role] = $message[rolename];
	    		if($message[clientmessagecreatedby])
	    			$message[from_name] = get_login_info($message[clientmessagecreatedby]);
	    		else
	    			$message[from_name] = 'System';
	    		$message[to_role] = $message[assignmentmethodname];
	    		$message[to_name] = $message[staffname];
				if($message[from_name]==$message[to_name])
					$message[from_name] = 'System';
				if (clientcompanyid()) //if I am a client company
				{
					if ($message['staffid'] == clientstaffid()) //and the message is to me
					{
						$message_tome = 1;
						$tome |= 2;
					}
				}
				else //if I am not a client company
				{
					if($message[from_name] == 'System') //and this is a system message
					{
						$message_tome = 1;
						$tome |= 4;
					}
				}
    		}
    	}
    	else
    	{
    		$message[to_role] = $message[rolename];
    		$message[to_name] = $message[clientcompanyname];
    		$message[from_role] = $message[assignmentmethodname];
    		$message[from_name] = $message[staffname];
    	}

    	$unread=0;
    	if ((!clientcompanyid() && $message[clientmessage_from_client]) || (is_clientcompanynotother() && !$message[clientmessage_from_client]))
    	{
    		if($message[clientmessagecreatedby] == loginid() && ($message[from_name] != 'System'))
    			$message_tome=0;
    		else //company message?
    		{
				$message_tome = 1;
				$tome |= 8;
    		}
    	}
    	if (!$message[clientmessagereaddate])
    	{
    		if($message_tome)
    		{
    			$unread_count[$tome]++;
    			if ($unread_message[$tome][$message['clientmessagesubject']])
					$unread_message[$tome][$message['clientmessagesubject']] .= ",".$message['clientmessageid'];
    			else
					$unread_message[$tome][$message['clientmessagesubject']] = $message['clientmessageid'];
    			$unread=1;
    		}
    	}
	
    	$message[unread] = $unread;
    	$message[message_tome] = $message_tome;
    	$messagearr[$k]=$message;
    }

	d_dev('Unread Counts [tome] = count', $unread_count);
	d_dev('Unread Message [tome][id] = message', $unread_message);
    return $messagearr;
}

function get_messages($timezone, $pe_config='', $property='', $propertyid='', $filter='', $unreadssp=false)
{
	if(companyid())
	{
		$sql = "
			select tblclientmessage.*
				,clientcompanypropertyaddress
				, staffid
				, stafffname ||' '||stafflname as staffname
				, rolename
				, clientcompanyname
				, assignmentmethodname
				, (select 1 from tblclientmessage a where clientmessageoriginalid = tblclientmessage.clientmessageid limit 1) as has_submessages ";
		if ($pe_config) foreach($pe_config as $pekey => $pe)
			$sql .= "
				, (select propertyextensionentryvalue from tblpropertyextensionentry where propertyextensionkey = '$pekey' and clientcompanypropertyid = a.clientcompanypropertyid) as $pekey \n";
		if($filter == 'unreadfromssp')
			$sql .= "
				, (select propertyid from tblproperty where clientcompanypropertyid=tblclientmessage.clientcompanypropertyid and tblproperty.companyid=".companyid().") as tiedtopropertyid";
		$sql .="
				,  dt_formatwithtz( clientmessagecreateddate," . db_number($timezone[offset]) . ",". db_tick($timezone[initial]) .") as clientmessagecreateddatewithtimezone
			from tblclientmessage
				join tblclientcompanyproperty a using(clientcompanypropertyid)";
		if($filter == 'unreadfromssp')
		{
			$sql .="
				join (
					select clientcompanypropertyid 
					from tblclientcompanyproperty
						join tblclientcompanypropertyassignment using(clientcompanypropertyid)
						join tblassignmentmethod using (assignmentmethodid)
					where 0=0
						and assignmentmethodactivessp=1
						and clientcompanypropertyassignmentaccepteddate is not null
						and clientcompanypropertyassignmentdenieddate is null
						and clientcompanypropertyassignmentrevokeddate is null
				) ssp using(clientcompanypropertyid) ";
							
		}
		$sql .="
				join tblclientcompany using(clientcompanyid)
				join tblclientcompanypropertyassignment b on 
				(
					a.clientcompanypropertyid = b.clientcompanypropertyid
					and (
							clientmessage_clientcompanypropertyassignmentid = clientcompanypropertyassignmentid
							or clientmessage_assoc_clientcompanypropertyassignmentid = clientcompanypropertyassignmentid
							--and clientmessage_assoc_clientcompanypropertyassignmentid = 0
					)
				)
				join tblassignmentmethod using(assignmentmethodid)
				join tbllogin using(staffid)
				join tblstaff using(staffid)
				left join tblrole on (tblrole.roleid = clientmessage_roleid)
			where 0 = 0
				and companyid = ".companyid();
		if(client_portal())
			$sql .="
				and a.clientcompanypropertyhide is null
				and (
						b.clientcompanypropertyassignmentrevokeddate is null 
						or (
								b.clientcompanypropertyassignmentrevokeddate is not null
								and (b.clientcompanypropertyassignmentrevokeddate + interval '30 days') > now()
						)
				)					
				";					
		if($property)
			$sql.="
				and a.clientcompanypropertyid = $property[clientcompanypropertyid] ";
		if($filter=='unreadfromssp')
		{
			$sql.="
				and clientmessagereaddate is null
				and clientmessage_from_client=1 ";
			if(!is_developer() && !is_devsite())
				$sql.="
				and clientcompanydev =0";
		}
	
		if(client_portal() && !accountid() && staffid())
		{
			if(has_access('viewableproperties', 'Broker') && officelocationid())  //If Broker in an office only allow them to see messages assigned to users in their office.
			{
				$sql.="	and staffid in 
						(
							select staffid 
									from tblstaff 
									where officelocationid=".officelocationid()."
						)
						";			
			}
			else
			{
				$sql .=	"
					and staffid=".staffid()."
					";
			}						
		}					
		$sql .= "
			order by clientmessageid desc";
		$messagearr = db_query($sql);
	}
	else if(is_clientcompanynotother())
	{
		$sql = "
			select tblclientmessage.*
				,clientcompanypropertyaddress
				, staffid
				, assignname as staffname
				, clientstafffname ||' '||clientstafflname as clientstaffname
				, rolename
				, clientcompanyname
				, assignmentmethodname
				, rolepropertyworking
				, (case when clientmessageoriginalid = clientmessageid then 1 else 0 end) as has_submessages ";
		if($unreadssp)
			$sql .= "	
				, (select propertyid from tblproperty where clientcompanypropertyid=tblclientmessage.clientcompanypropertyid limit 1) as tiedtopropertyid";
		
		if(clientstaffid())
			$sql.="	
				, (select 1 from tblclientcompanypropertyrole where  clientcompanypropertyid = a.clientcompanypropertyid and roleid = tblrole.roleid and tblclientcompanypropertyrole.clientstaffid = ".clientstaffid()." limit 1) as myassignment ";
			
		//select the staff members below me and get the property list under them
		$csassigntome = get_clientstaff_assignedtome();
		$outarr=array();
		foreach($csassigntome as $k=>$cs)
			$outarr[$cs[clientstaffid]]=$cs;
		$csassigntome = $outarr;
		foreach($csassigntome as $k=>$cs)
		{
			$restrictids="$cs[clientstaffid]";
			if($restrictids)
				$sql .= "
				, (select 1 from tblclientcompanypropertyrole where    clientcompanypropertyid = a.clientcompanypropertyid and roleid = tblrole.roleid and tblclientcompanypropertyrole.clientstaffid in ($restrictids) limit 1) as assignedto_staffassignedtome_$cs[clientstaffid] ";
			else
				$sql .= "	
				, 0 as assignedto_staffassignedtome_$cs[clientstaffid] ";
		}

	    if ($pe_config) foreach($pe_config as $pekey => $pe)
	        $sql .= "
	        	, (select propertyextensionentryvalue from tblpropertyextensionentry where propertyextensionkey = '$pekey' and clientcompanypropertyid = a.clientcompanypropertyid) as $pekey \n";

		$sql.="
				,  dt_formatwithtz( clientmessagecreateddate," . db_number( $timezone[offset]) . ",". db_tick( $timezone[initial] ) .") as clientmessagecreateddatewithtimezone
			from tblclientmessage
				join 
				(
					select * 
					from tblclientcompanyproperty
						join tblclientcompany using(clientcompanyid)
					where clientcompanyid = " . clientcompanyid() . "
				) as a using(clientcompanypropertyid)";
					
		if($unreadssp)
		{
		    $sql .="
				join 
		    	(
					select clientcompanypropertyid from tblclientcompanyproperty
						join tblclientcompanypropertyassignment using(clientcompanypropertyid)
						join tblassignmentmethod using (assignmentmethodid)
					where 0=0
						and assignmentmethodactivessp=1
						and clientcompanypropertyassignmentaccepteddate is not null
						and clientcompanypropertyassignmentdenieddate is null
						and clientcompanypropertyassignmentrevokeddate is null
				) ssp using(clientcompanypropertyid) ";
		}
		$sql.="
				join (select *,roleid as clientmessage_roleid from tblrole ) as tblrole using (clientmessage_roleid)
				join (select *, clientcompanypropertyassignmentid as clientmessage_clientcompanypropertyassignmentid from tblclientcompanypropertyassignment where clientcompanypropertyid in (select clientcompanypropertyid from tblclientcompanyproperty where clientcompanyid=" . clientcompanyid() . ")) as b using (clientcompanypropertyid, clientmessage_clientcompanypropertyassignmentid)
				join tblassignmentmethod using(assignmentmethodid)
				join vw_assigntypes using(accountstaffid, staffid, externaluserid,assignmentaliasclientstaffid)
				left join tblclientcompanypropertyrole pr using (roleid,clientcompanypropertyid)
				left join tblclientstaff using (clientstaffid)
			where a.clientcompanyid = ".clientcompanyid();
		if($property)
			$sql.=" 
				and a.clientcompanypropertyid = $property[clientcompanypropertyid] ";

		$rolerestrictionlist = get_sub_roleidlist();  //only display messages sent to roles that i can see (mine or below()
		if($rolerestrictionlist)
			$sql.="
				and clientmessage_roleid in ($rolerestrictionlist) ";
		
		$rolepropertyrestriction_staffidlist = get_rolepropertyrestriction_stafflist(clientstaffid());  // only display messages that are in the "downline" of members.  if they have one
		if($rolepropertyrestriction_staffidlist)
		{
			$sql.="
				and	exists 
				(
					select 1
					from tblclientcompanypropertyrole
					where clientcompanypropertyid = a.clientcompanypropertyid
						and clientstaffid in (" . $rolepropertyrestriction_staffidlist . ")
				)";
		}
		$sql .= "
			order by clientmessageid desc";
		$messagearr = db_query($sql);
	} 
	else if(accountstaffid()) 
	{
        $sql = "
        	select tblclientmessage.*
				,clientcompanypropertyaddress
                , accountstaffid
                , accountstafffname ||' '||accountstafflname as staffname
                , coalesce (rolename, '') as rolename
                , clientcompanyname
                , assignmentmethodname
                , (select 1 from tblclientmessage a where clientmessageoriginalid = tblclientmessage.clientmessageid limit 1) as has_submessages ";
        
        if ($pe_config) foreach($pe_config as $pekey => $pe)
            $sql .= "
				, (select propertyextensionentryvalue from tblpropertyextensionentry where propertyextensionkey = '$pekey' and clientcompanypropertyid = a.clientcompanypropertyid) as $pekey \n";
        
        $sql .="
				,  dt_formatwithtz( clientmessagecreateddate," . db_number( $timezone[offset]) . ",". db_tick( $timezone[initial] ) .") as clientmessagecreateddatewithtimezone
			from tblclientmessage
				join tblclientcompanyproperty a using(clientcompanypropertyid)
                join tblclientcompany using(clientcompanyid)
                join tblclientcompanypropertyassignment b on 
        		(
					a.clientcompanypropertyid = b.clientcompanypropertyid
					and (
						clientmessage_clientcompanypropertyassignmentid = clientcompanypropertyassignmentid
						or clientmessage_assoc_clientcompanypropertyassignmentid = clientcompanypropertyassignmentid
					)
				)
				join tblassignmentmethod using(assignmentmethodid)
				join tbllogin using(accountstaffid)
				join tblaccountstaff using(accountstaffid)
				left join tblrole on (tblrole.roleid = clientmessage_roleid)
			where 0 = 0 ";
		if(accountstaffid() && !has_customization("corporateaccount"))
		{
			$sql.="	
				and b.accountstaffid in
				(
					select accountstaffid
					from tblaccountstaff
					where officelocationid in
					(
						select officelocationid
						from tblaccountstaff
						where accountstaffid = ".accountstaffid()."
					)
				)";
		}
		else if(accountid() && has_customization("corporateaccount") )
		{
			//	this corporate account gets to see all properties for all assigments for all subs
			$sql.=" 
				and b.accountstaffid in
				(
					select accountstaffid
					from tblaccountstaff
						join tblaccount using (accountid)
					where 0 = 0 ";
			if( !account_hasparent(accountid()))
				$sql.="
						and accountparentaccountid = ".accountid();
			else
				$sql.=" 
						and accountid = ".accountid();
			$sql.= "
				) ";
		}
        if($property)
            $sql.=" 
				and a.clientcompanypropertyid = $property[clientcompanypropertyid] ";
        $sql .= "
        	order by clientmessageid desc";
        $messagearr = db_query($sql);
	} 
	else if(externaluserid()) 
	{
        $sql = "
        	select tblclientmessage.*
				, clientcompanypropertyaddress
                , externaluserid as staffid
                , externaluserfname ||' '||externaluserlname as staffname
                , coalesce (rolename, '') as rolename
                , clientcompanyname
                , assignmentmethodname
                , (select 1 from tblclientmessage a where clientmessageoriginalid = tblclientmessage.clientmessageid limit 1) as has_submessages ";
        if ($pe_config) foreach($pe_config as $pekey => $pe)
            $sql .= "
            	, (select propertyextensionentryvalue from tblpropertyextensionentry where propertyextensionkey = '$pekey' and clientcompanypropertyid = a.clientcompanypropertyid) as $pekey \n";
        $sql .="
        		, dt_formatwithtz( clientmessagecreateddate," . db_number( $timezone[offset]) . ",". db_tick( $timezone[initial] ) .") as clientmessagecreateddatewithtimezone
			from tblclientmessage
				join 
        		(
					select * 
					from tblclientcompanyproperty
						join tblclientcompany using(clientcompanyid)
				) as a using(clientcompanypropertyid)
                join tblclientcompanypropertyassignment b on 
        		(
					a.clientcompanypropertyid = b.clientcompanypropertyid
					and 
        			(
							clientmessage_clientcompanypropertyassignmentid = clientcompanypropertyassignmentid
							or clientmessage_assoc_clientcompanypropertyassignmentid = clientcompanypropertyassignmentid
							--and clientmessage_assoc_clientcompanypropertyassignmentid = 0
					)
				)
				join tblassignmentmethod using(assignmentmethodid)
				join tbllogin using(externaluserid)
				join tblexternaluser using(externaluserid)
				left join tblrole on (tblrole.roleid = clientmessage_roleid)
			where 0 = 0
				and b.externaluserid = ".externaluserid();
        if($property)
            $sql .= " 
				and a.clientcompanypropertyid = $property[clientcompanypropertyid] ";
        $sql .= "
        	order by clientmessageid desc";
		$messagearr = db_query($sql);
	}
	else
		ddie("Not client or company ....");

	if (is_sister())
	{
		$sql = "
			select tblclientmessage.*
				, clientcompanypropertyaddress
				, assignmentaliasclientstaffid as staffid
				, (select clientstafffname || ' ' || clientstafflname from tblclientstaff where clientstaffid = assignmentaliasclientstaffid) as staffname
				, coalesce (rolename, '') as rolename
				, clientcompanyname
				, assignmentmethodname
				, (select 1 from tblclientmessage a where clientmessageoriginalid = tblclientmessage.clientmessageid limit 1) as has_submessages
				,  dt_formatwithtz( clientmessagecreateddate,0,'M') as clientmessagecreateddatewithtimezone
			from (select coalesce(clientmessage_clientcompanypropertyassignmentid, clientmessage_assoc_clientcompanypropertyassignmentid) clientcompanypropertyassignmentid, clientmessage_roleid roleid, * from tblclientmessage) tblclientmessage
				join 
				(
					select * 
					from tblclientcompanyproperty
						join tblclientcompany using(clientcompanyid)
				) tblccprop using (clientcompanypropertyid)
				join tblclientcompanypropertyassignment using (clientcompanypropertyid, clientcompanypropertyassignmentid)
				join tblassignmentmethod using(assignmentmethodid)
				left join tblrole using (roleid)
			where 0 = 0
				and 
				(
					assignmentaliasclientstaffid in (select clientstaffid from tbllogin where clientcompanyid = ".clientcompanyid()." and clientstaffid > 0) ";
		if ($propertyid)
			$sql .= "
					and clientcompanypropertyid = ".db_number($propertyid);
		$sql .= "
				)";
		$messagearr_sister = db_query($sql);
		$messagearr = array_merge($messagearr , $messagearr_sister);
	}
	return $messagearr;
}

function get_unreadmessagecount()
{
	$count = 0;

	timetrack_start('unreadmessagecount');
	$messages = eval_unreadmessages(get_messages(get_timezone()));
	if ($messages) foreach ($messages as $message)
	{
		if ($message[unread])
			$count++;
	} 
	timetrack_stop('unreadmessagecount');
	unset($messages);
	return $count;
}