<?

function crmhistory_insert($crmcontactid,$crmhistorytype,$subject,$text,$insertkeyvalarr=array())
{
        if(!is_numeric($crmhistorytype) && $crmhistorytype)
            $crmhistorytype=db_firstval("select crmhistorytypeid from tblcrmhistorytype where crmhistorytypeshortname=".db_tick($crmhistorytype)."");
        if(!db_number($crmhistorytype))
        {
            return;
        }
		if($insertkeyvalarr)
		{
			$insertkeyval=TRUE;
			if(!is_array($insertkeyvalarr))
			{
				developer_error("Invalid insertkeyvalarr passed to crmhistory_insert so unable to insert.");
				$insertkeyval=FALSE;
			}
		}
        $sql="
        		insert into tblcrmhistory
        		(
        			crmcontactid
        			, crmhistorycreatedby
					, crmhistorytypeid
					, crmhistorysubject
					, crmhistorytext
					";
		if($insertkeyval)
			foreach($insertkeyvalarr as $insertkey=>$insertval)
				$sql.=", $insertkey
					";
        $sql.="	)
        		values
        		(
            		 ".db_number($crmcontactid)."
        			,".loginid()."
					, ".db_number($crmhistorytype)."
					, ".db_tick($subject)."
					, ".db_tick($text)."
					";
		if($insertkeyval)
			foreach($insertkeyvalarr as $insertkey=>$insertval)
				$sql.=", $insertval
					";
        $sql.="	)
        		";
        db_exec($sql,1);
 }
 function mcampaign_getfull($mcampaignid)
 {
    global  $mcampaign_getfull_global;
    if($mcampaign_getfull_global[$mcampaignid])
        return $mcampaign_getfull_global[$mcampaignid];
    $mcampaignid =db_number($mcampaignid);
    if(!$mcampaignid)
        return array();
    
    $all = db_first("select *
                     , date_part('month',age(now(), mcampaigncreateddate)) as dayssince
                    from tblmcampaign where mcampaignid = $mcampaignid");
    if(!$all)
        return $all;

	$sql="select *
                from tblmcampaignaction
                where mcampaignactioninactivateddate is null
                	and mcampaignid = $mcampaignid
                 	and mcampaignactionparent=0
                 	and mcampaignactionstartnonsequential=1
                 	and mcampaignactionstartprompttype is null
                order by mcampaignactionstartdays, mcampaignactionid";
	$nonsequential=db_query($sql);
	$sql="select *
                from tblmcampaignaction
                where mcampaignactioninactivateddate is null
                	and mcampaignid = $mcampaignid
                 	and mcampaignactionparent=0
                 	and mcampaignactionstartnonsequential=1
                 	and mcampaignactionstartprompttype is not null
                order by mcampaignactionid";
	$nonsequentialprompt=db_query($sql);
	foreach($nonsequentialprompt as $nskey=>$mcampaignactioninfo)
		$nonsequential[]=$mcampaignactioninfo;

	unset($nonsequentialprompt);
	
	$sql="select *
                from tblmcampaignaction
                where mcampaignactioninactivateddate is null
                	and mcampaignid = $mcampaignid
                 	and mcampaignactionparent>0
                order by mcampaignactionrank, mcampaignactionid";
	$sequential=db_query($sql);

	foreach($sequential as $sequentialkey=>$sequentialinfo)
		$sequentialprevactionarr[$sequentialinfo['mcampaignactionparent']][]=$sequentialinfo;
	unset($sequential);
	$actions=array();
		
	foreach($nonsequential as $nskey=>$mcampaignactioninfo)
	{
		$actions[]=$mcampaignactioninfo;
		if($sequentialprevactionarr[$mcampaignactioninfo['mcampaignactionid']])
			foreach($sequentialprevactionarr[$mcampaignactioninfo['mcampaignactionid']] as $sequentialkey=>$sequentialinfo)
				$actions[]=$sequentialinfo;
	}
	unset($sequentialprevactionarr);
	unset($nonsequential);
    $totaldays=0;
    $allemails=array();
    foreach($actions as $actionk => $action)
    {
    
        $emails=array();
        if($action[mcampaignactionexectemailid] || $action[mcampaignactionexectemaillistid])
        {
            $sql = "select * from tbltemail left join tbltemaillist using(temaillistid) where 0=0 ";
            if(    $action[mcampaignactionexectemailid] )
                $sql.=" and temailid = $action[mcampaignactionexectemailid] ";
            elseif(    $action[mcampaignactionexectemaillistid] )
                $sql.=" and temaillistid = $action[mcampaignactionexectemaillistid] ";
            else
                ddie("Invalid,  you must have at least one criteria");
            $sql.= " order by temailrank";
            $emails = db_query($sql,1);
        }
        $delaydays=$action[mcampaignactionstartdays];
        foreach($emails as $email)
        {
            $email = array_merge($action,$email);
            if($email[temaildays]>0)
                $delaydays+=$email[temaildays];
            
            if($action[mcampaignactionstartnonsequential])
            {
                $email[datemethod]='mcampaigncreateddate';
                $email[delaydays]=$delaydays;
            }
            else
            {
                $email[datemethod]='lastdate';
                $email[delaydays]=$delaydays+$totaldays;
            }
            $actions[$actionk][emails][] = $email;
            $allemails[] = $email;
        }
        
        $action[emails] = $emails;
        if(!$action[mcampaignactionstartnonsequential])
        {
         
            $totaldays+=$delaydays;
        }
    }
    $all[emails]=$allemails;
    $all[actions] = $actions;
    $mcampaign_getfull_global[$mcampaignid] = $all;
    return $all;
 }
 function crmcontactmcampaign_getall($row)
 {
    //param is the crmcontactmcampaign row
    //figure out which actions they have already done and which is the next action
    $full = mcampaign_getfull($row[mcampaignid]);
    
    if(!$row[crmcontactmcampaignlast_mcampaignactionid] )
        $lastfound=1;
    foreach($full[emails] as $email)
    {
        
           if($email[mcampaignactionid] == $row[crmcontactmcampaignlast_mcampaignactionid])
           {
                if(!$lastfound)
                {
                    if(!$ $row[crmcontactmcampaignlast_subrank]) //the rank is still 0,  than means the system was updates to set the last action,  but there was a delay, so we haven't got to the first item yet.
                        $lastfound=1;
                    elseif($row[crmcontactmcampaignlast_subrank]==$email[temailrank])
                    {   //we found the email we ran last,  the next one we see here is th next one
                        $lastfound=1;
                        continue;
                    }elseif($row[crmcontactmcampaignlast_subrank]<$email[temailrank])
                    {   //this email has a rank heigher than the last we ran this is the next in the list
                        $lastfound=1;
                    }
                    $loopedthrulastaction=1;
                }
                        
           }elseif($loopedthrulastaction && !$lastfound) // if we have already looped through the last campaign action and we still haven't found the the last action,  we know we have now
                $lastfound=1;
           if($lastfound)
           {
                if(!$nextsequential&&!$email[mcampaignactionstartnonsequential])
                    $nextsequential=$email;
                if(!$next)
                    $next=$email;
           }
           if(!$nextnonsequential && $email[mcampaignactionstartnonsequential] && $full[dayssince]<$email[delaydays])
           {
                $nextnonsequential = $email;
                if(!$next)
                    $next=$email;
           }
                
           
    }
    if($nextnonsequential )
        $full[next][nonsequential] =$nextnonsequential ;
    if($nextsequential )
        $full[next][sequential] =$nextsequential ;
    if($next )
        $full[next][next] =$next ;
         
    return $full;
 }
 
 function crm_get_apptype_longname($shortname)
{
	$sql="select crmcontactappttypename from tblcrmcontactappttype where crmcontactappttypeshortname = '".db_col($shortname)."' and companyid=".companyid()."";
	$crm_appttype_longname=db_first($sql);
	return $crm_appttype_longname['crmcontactappttypename'];
}
function format_crmappointment_to_event($appointmentarray)
{
	foreach($appointmentarray as $appkjey=>$appvals)
	{
		$crmappointment_event[$appkjey][eventid]=$appvals[crmcontactapptid];
		$crmappointment_event[$appkjey][eventtitle]=$appvals[crmcontactapptsubject];
		$crmappointment_event[$appkjey][eventdescription]=$appvals[crmcontactapptdescr];
		$crmappointment_event[$appkjey][eventtimestart]=$appvals[crmcontactapptdate];
		$crmappointment_event[$appkjey][eventcreatedby]=$appvals[crmcontactapptid];
		$crmappointment_event[$appkjey][eventcreateddate]=$appvals[crmcontactapptcreateddate];
		$crmappointment_event[$appkjey][eventiscrmappointment]=1;
	}
	return $crmappointment_event;
}
function get_campaigncontrols($crmcontactmcampaignid)
{
	?>
	<style>
		.controlbutton.controlbuttondisabled
		{
			opacity:.3;
			cursor:default;
		}
		.controlbutton.controlbuttonpressed
		{
			opacity:1;
			box-shadow:none;
			cursor:default;
		}
		.controlbutton
		{
			width:25px;
			opacity:0.7;
			box-shadow:0 8px 6px -6px rgba(0,0,0,0.7);
			cursor:pointer;
		}
		.controlbutton:hover:not(controlbuttonpressed):not(controlbuttondisabled)
		{
			opacity:1;
			box-shadow:none;
		}
		.controlbutton.controlbuttondisabled:hover:not(controlbuttonpressed):not(controlbuttondisabled)
		{
			opacity:.3;
			cursor:default;
		}
	</style>
	<?
	if(!$crmcontactmcampaignid)
		return "";
	$sql="select 	crmcontactmcampaignpauseddate
					, crmcontactmcampaigncancelleddate
					, crmcontactmcampaigncompleteddate
					, crmcontactmcampaigninactivateddate
					from tblcrmcontactmcampaign
					where crmcontactmcampaignid= ".db_number($crmcontactmcampaignid)."
					";
	$crmcontactmcampaign=db_first($sql);
	if(!$crmcontactmcampaign)
		return "";

	if($crmcontactmcampaign[crmcontactmcampaigninactivateddate])
	    $crmcampaignstatus="Inactive";
	elseif($crmcontactmcampaign[crmcontactmcampaigncompleteddate])
	    $crmcampaignstatus="Complete";
	elseif($crmcontactmcampaign[crmcontactmcampaigncancelleddate])
		echo "Stopped/Cancelled";
	elseif($crmcontactmcampaign[crmcontactmcampaignpauseddate])
		$crmcampaignstatus="Paused";
	else
	    $crmcampaignstatus="Started";

	$addclassplay="controlbuttonpressed";
	$addclasspause="";
	$addclassstop="";
	if($crmcontactmcampaign['crmcontactmcampaigncancelleddate'] || $crmcontactmcampaign['crmcontactmcampaigncompleteddate'])
	{
		$addclassplay="controlbuttondisabled";
		$addclasspause="controlbuttondisabled";
		$addclassstop="controlbuttondisabled";
		$buttondisabled['play']=1;
		$buttondisabled['pause']=1;
		$buttondisabled['stop']=1;
	}
	elseif($crmcontactmcampaign['crmcontactmcampaignpauseddate'])
	{
		$addclassplay="";
		$addclasspause="controlbuttonpressed";
		$addclassstop="";
		$buttondisabled['pause']=1;
	}
	$out= "	<div style='float:left;'>
				<div style='float:left;'>
					<img class='controlbutton $addclassplay' src='/images/icons/coquette/24x24/play.png' ".(!$buttondisabled['play']?"onclick=\"runcrmcontrol('play', $crmcontactmcampaignid);\"":"")." />
				</div>
				<div style=\"float:left;\">
					<img class='controlbutton  $addclasspause' src='/images/icons/coquette/24x24/pause.png' ".(!$buttondisabled['pause']?"onclick=\"runcrmcontrol('pause', $crmcontactmcampaignid);\"":"")." />
				</div>
				<div style=\"float:left;\">
					<img class='controlbutton  $addclassstop' src='/images/icons/coquette/24x24/stop.png' ".(!$buttondisabled['stop']?"onclick=\"runcrmcontrol('stop', $crmcontactmcampaignid);\"":"")." />
				</div>
				<div style=\"float:left;margin-left:10px;\">
					$crmcampaignstatus
				</div>
			</div>";
	return $out;
}
function get_crm_contact_email_fromcrmid($crmcontactid)
{
	$sql="select crmcontactemail from tblcrmcontact where crmcontactid=".db_number($crmcontactid)."";
	$ret=db_first($sql);
	return $ret[crmcontactemail];
}
function trigger_mcampaign($mcampaignid,$crmcontactid)
{
	
	// get mcampaign
	$mcampaigndata = db_firstval('select * from tblmcampaign where mcampaignid = '.$mcampaignid);
		$campaignname = db_firstval('select mcampaignname from tblmcampaign where mcampaignid = '.db_number($_POST[mcampaignid_add]));
				
		$crmcontactmcampaignid = db_firstval("select nextval('tblcrmcontactmcampaign_crmcontactmcampaignid_seq') as crmcontactmcampaignid");
                $sql = "insert into tblcrmcontactmcampaign (crmcontactmcampaignid, crmcontactid, mcampaignid, crmcontactmcampaigncreatedby)
                            values ($crmcontactmcampaignid, $crmcontactid, ".db_number($mcampaignid).", ".loginid().")";
                $runinsert=db_exec($sql); //
		
		$crmcontactmcampaignarr['crmcontactmcampaignid']=$crmcontactmcampaignid;
                crmhistory_insert($crmcontactid,"campaignaction",'Added Marketing Campaign: '.$campaignname,"Added Campaign $campaignname to CRM Contact", $crmcontactmcampaignarr);
                
		if($runinsert)
		{
			$retmsg="MCampaign has been started. ";
		}else
		{
			$retmsg="There was an error, please try again.";
		}
		
		return $retmsg;
		
		
}
function trigger_add_crmcontact_tag($tagid,$crmcontactid)
{
	//will add crm contact tag to crmcontactid
	$sql="select crmcontacttagentryid
					from tblcrmcontacttagentry
					where crmcontactid=".db_number($crmcontactid)."
						and crmcontacttagid=".db_number($tagid)."
					";
	
	$checktag=db_query($sql);
	if($checktag)
	{
		$retmsg="This tag has already been added to the contact.";
	}else
	{
		$sql="insert into tblcrmcontacttagentry
							(
								crmcontacttagentrycreatedby
								,crmcontactid
								,crmcontacttagid
							)
							Values
							(
								".loginid()."
								, ".db_number($crmcontactid)."
								, ".db_number($tagid)."
							)
		";
		db_exec($sql);
		$retmsg="Tag ".$tagid." added to contact.";
	}
	return $retmsg;
	
}

function get_crmrecruitingactions($recsettingsarray,$crmcontactid)
{
    //if the offer is one that was created through the public offers.
	$actiontakenmsg="";
	$hascampagin = db_first("select * from tblcrmcontactmcampaign where crmcontactid = " . db_number($crmcontactid));
	foreach($recsettingsarray as $recsettinggroupnum=>$recsettinggrouparray)
	{
		if(strstr($recsettinggrouparray['savedsettingentrykey'],'_'))
		{
			$shortkeynamex=explode('_',$recsettinggrouparray['savedsettingentrykey']);
			$shortkeyname=end($shortkeynamex);
			if($recsettinggrouparray['savedsettingentryvalue']!="")
				$recsetting[$shortkeyname]=$recsettinggrouparray['savedsettingentryvalue'];
		}
		
	}
	$rec_shortnames=array_keys($recsetting);
	$hashistory = db_firstval ("select crmhistoryid from tblcrmhistory where crmcontactid = " . $crmcontactid . " and crmhistorytypeid = 6 and crmhistorysubject = ". db_tick($recsetting['emailsubject']));
	//if(this email has been sent before to this recruiting contact(all contacts in this crmid() with same email address),  dont send it again)
	// return;
	if(in_array('emailsubject',$rec_shortnames))
	{
		if (!$hashistory)
		{
			//email sent from broker to use for rewcruiting
			$emailfromstaffid=$recsetting['staffid'];
			$emailtocrmcontactid=$crmcontactid;
			$emailfrom=get_staff_email($emailfromstaffid);
			$emailto=get_crm_contact_email_fromcrmid($crmcontactid);
			$emailsubj=$recsetting['emailsubject'];
			$emailbody=$recsetting['emailbody'];
			$emaildelay=$recsetting['delayhours'];
			
			$sql="select * from tblcrmcontact where crmcontactid=".db_number($crmcontactid);
			$crmcontact=db_first($sql);
			
			$replacementmaparr=array();
			if(!$crmcontact['crmcontactfname'])
			    $crmcontact['crmcontactfname']=list_allbutlast($crmcontact['crmcontactname'], ' ');
			if(!$crmcontact['crmcontactlname'])
			    $crmcontact['crmcontactlname']=list_last($crmcontact['crmcontactname'], ' ');
			$crmcontact['crmcontactcompanyname']=$crmcontact['crmcontactcname'];
			$replacementcontact=array();				    
			foreach($crmcontact as $crmcontactkey=>$crmcontactinfo)
			{
				if(is_array($crmcontactinfo))
					continue;
				$replacementcontact[$crmcontactkey]=$crmcontactinfo;
				$tokey="to_".$crmcontactkey;
				$replacementmaparr[$tokey]=$crmcontactkey;
				if(strpos($crmcontactkey, 'crmcontact')===0)//If string starts with crmcontact.
				{
					$tokey="to_".str_replace('crmcontact','',$crmcontactkey);
					if($tokey!=='to_id')
						$replacementmaparr[$tokey]=$crmcontactkey;
				}
			}
			$sql='select 	tbllogin.staffid  
							, tbllogin.loginemailsignature as signature
							, tblstaff.*
							, tblcompany.*
							from tbllogin
								left join tblcompany using(companyid) 				
								left join tblstaff using(staffid)
							where staffid = '.db_number($emailfromstaffid);
			$fromqry=db_first($sql);

			if ($email['flyertemplateid'])
			{
				$flyerhtml = get_flyertemplatewithsub($email['flyertemplateid'], 0, $fromqry['staffid']);
				$emailbody.=$flyerhtml;
			}

			if(!$fromqry)
			{
				$sql="select column_name 
							from 	information_schema.columns
							where table_name = 'tblstaff'";
				$qry=db_query($sql);
				$fromqry=array();
				foreach($qry as $qrykey=>$replacementkey)
					$fromqry[$replacementkey[column_name]]="";
			}

			if($fromqry)
			{
				$replacementpart='staff';
				if(!$fromqry['staffid'] && $fromqry['companyid'])
					$replacementpart='company';
				
				foreach($fromqry as $frominfokey=>$frominfo)
				{
					$fromkey="from_".$frominfokey;
					$replacementmaparr[$fromkey]=$frominfokey;
					if(strpos($frominfokey, $replacementpart)===0) //If string starts with replacement.
					{
						$fromkey="from_".str_replace($replacementpart,'',$frominfokey);
						if($fromkey!='from_id')							
							$replacementmaparr[$fromkey]=$frominfokey;
					}
				}						
			}
			$replacementinfoarr=array_merge($replacementcontact, $fromqry);
			unset($replacementcontact);
			unset($fromqry);
			if($replacementmaparr['from_signature'])
			{
				$emailbody=preg_replace("/##from_signature(\b)/",$replacementinfoarr['signature']."\$1", $emailbody);
				$emailbody=preg_replace("/##from_signature(##)/",$replacementinfoarr['signature']."\$1", $emailbody);	
				unset($replacementmaparr['from_signature']);							
			}
				
			$emailbody = replacetext_frommaparr($emailbody, $replacementmaparr, $replacementinfoarr);
			
			html_mail($emailto,$emailfrom,$emailsubj,$emailbody,0,"",$emaildelay);
			$crmhistoryid = db_nextid('crmhistory');
			$sql = "insert into tblcrmhistory (crmhistoryid, crmhistorycreatedby,crmhistorycreateddate,crmhistorysubject,crmhistorytext,crmhistorytypeid,crmcontactid,crmcontactmcampaignid)
					values(
						" . $crmhistoryid ."
						," . loginid() . "
						, NOW()
						, " . db_tick($memailsubject) . "
						, " . db_tick($memailbody) . "
						, 6
						, " . $crmcontactid . "
						, 0)";
			db_exec ($sql, 1);
			$actiontakenmsg.= " Email Sent to ".$emailto."<br>";
			dlog('recruitingsettings_actionstaken',"Action Taken: Send Email : to: ".$emailto." from: ".$emailfrom." crmcontactid: ".$crmcontactid." URL: ".$_SERVER['REQUEST_URI']."");
		}else
			dlog('recruitingsettings_actionstaken',"Action Taken: Skip Email : to: ".$emailto." from: ".$emailfrom." crmcontactid: ".$crmcontactid." URL: ".$_SERVER['REQUEST_URI']."");
	}
	if (!$hascampagin)
	{
	//if(this campaign has every been started before on this recruiting contact,  don't start it again)

		if(in_array('mcampaignid',$rec_shortnames))
		{
			$actiontakenmsg.=trigger_mcampaign($recsetting['mcampaignid'],$crmcontactid);
			dlog('recruitingsettings_actionstaken',"Action Taken: Start Campaign : Campaign ID: ".$recsetting['mcampaignid']." crmcontactid: ".$crmcontactid." URL: ".$_SERVER['REQUEST_URI']."");
		}
		if(in_array('addcrmcontacttaglist',$rec_shortnames))
		{
			$actiontakenmsg.=trigger_add_crmcontact_tag($recsetting['addcrmcontacttaglist'],$crmcontactid);
			dlog('recruitingsettings_actionstaken',"Action Taken: Add Crm Contact Tag :Tag Id: ".$recsetting['addcrmcontacttaglist']." crmcontactid: ".$crmcontactid." URL: ".$_SERVER['REQUEST_URI']."");
		}
	}
	else{
		dlog('recruitingsettings_actionstaken',"Action Taken: Skip Campaign Already exist: Campaign ID: ".$recsetting['mcampaignid']." crmcontactid: ".$crmcontactid." URL: ".$_SERVER['REQUEST_URI']."");
		return;
	}
	return $actiontakenmsg;
}



function crm_get_recruitingsettings($companyid,$offeraction)
{
	$offeraction = trim(strtolower($offeraction));
	//get all rec settings for this company id
	if ($offeraction == 'new' || $offeraction =='')
		$offeraction = 'submitted';
	return db_query("select savedsettingentrykey, savedsettingentryvalue from tblsavedsettingentry where companyid=" . db_number($companyid) ." and savedsettingentrykey ilike 'recruiting_oo%" . db_col($offeraction) . "%trigger%'");
}

function run_crmcontact_recruiting($contactid,$companyid,$offerstatus)
{
        //logic should prevent this runction from running if the offer is not public.
		/*
			THIS FUNCTION WILL GET THE SUBMITTED OFFER'S CONTACT INFORMATION AND CHECK TO SEE IF WE HAVE A CRMCONTACT RECORD FOR THEM,
			IF WE DO HAVE A RECORD, WE CHECK TO ENSURE THERE IS A CONNECTION RECORD BETWEEN TBLCONTACT AND TBLCRMCONTACT,
			IF NO CONNECTION RECORD, INSERT ONE.
			IF WE DO NOT HAVE A CRMCONTACT RECORD, ONE IS INSERTED WITH THE TBLCONTACT INFORMATION AND THE CONNECTION IS
			INSERTED TO THE TBLCRMCONTACTCONNECT TABLE.
			
			AFTER ALL IS SAID AND DONE, WE WILL RETURN THE CRMCONTACTID FROM THIS FUNCTION
		*/
		$adjustedofferstatus=trim(strtolower($offerstatus));
		if ($adjustedofferstatus == 'new' || $adjustedofferstatus =='')
			$adjustedofferstatus = 'submitted';	
			
		$propertycompanyid = $companyid;
		$offercontactid = $contactid;
		$tblcontactrecord = db_first("select * from tblcontact
									left join tblclient using (clientid)
								 where contactid = " . db_number($contactid) );
		$tblcrmcontactrecord=db_first("select * from tblcrmcontact
								 where crmcontactemail = " . db_tick($tblcontactrecord['contactemail']) . "
							 		and crmcontactcname = " . db_tick($tblcontactrecord['clientcompanyname']),0,1 );
		
		$crmsettings=crm_get_recruitingsettings($propertycompanyid, $adjustedofferstatus);
		$recruitingsettings=array();
		if($crmsettings)
			foreach($crmsettings as $settingkey=>$settinginfo)
				$recruitingsettings[$settinginfo['savedsettingentrykey']]=$settinginfo['savedsettingentryvalue'];				
	
		
		//d('$recruitingsettings',$recruitingsettings);
		if(!$recruitingsettings["recruiting_oo".$adjustedofferstatus."trigger"])  //Check if this recruiting setting was selected to trigger.  If not then return.
		{
			d("Recruting settings for $adjustedofferstatus have not been selected to trigger.  No changes made to tblcrmcontact or tblcrmcontactconnector.");		
			return;
		}
		
		$sql="select 	loginid
						,logininactive 
						from tbllogin 
						where staffid<>0 
							and staffid = ".db_number($recruitingsettings["recruiting_oo".$adjustedofferstatus."trigger_staffid"])."
							and companyid = " . db_number($propertycompanyid). "
						";
		$selectedrecruiter = db_first($sql);
		if(!$selectedrecruiter || $selectedrecruiter['logininactive'])
		{
			$companyemail=db_firstval("select companyemail from tblcompany where companyid=".db_number($propertycompanyid));
			if(is_email($companyemail))
			{
				$emailreplacementarr['recruitingsettingbrokererror'] = "Invalid";
				if($selectedrecruiter['logininactive'])
					$emailreplacementarr['recruitingsettingbrokererror'] = "Inactive";
				$emailreplacementarr['recruitingsettingtype'] = ucfirst($adjustedofferstatus);
				
				$emailarr = db_first("select * from tblemail where emailshortname = 'recruitingsettingbrokererror'"); 
				if(!$emailarr)
					developer_error("crm_workflow_offer", "Missing email from tblemail with shortname recruitingsettingbrokererror");
				if($emailarr)
				{
					$subject = email_replace_arr($emailarr['emailsubject'],$emailreplacementarr);
					$body = email_replace_arr($emailarr['emailtext'],$emailreplacementarr);
					//d('$companyemail',$companyemail);
					//d('$emailarr[emailfrom]',$emailarr['emailfrom']);
					//d('$subject',$subject);
					//d('$body',$body);
										
					if($eml['emailccaddress'])
						html_mail_cc($companyemail,$emailarr['emailccaddress'],$emailarr['emailfrom'],$subject,$body);
					else								
						html_mail($companyemail,$emailarr['emailfrom'],$subject,$body);
				}			
			}
			d("Invalid Broker selected for ".ucfirst($adjustedofferstatus)." Recruiting Setting - no changes made to tblcrmcontact or tblcrmcontactconnector");		
			return;
		}

		$loginid=db_number($selectedrecruiter['loginid']);			
		if($crmsettings && $loginid) //only process the contact and crmcontaCT entries if there are recruiting settings and a login to tie the crmcontact to.  
		{
			$new_crmcontactid=$tblcrmcontactrecord['crmcontactid'];
			if(!$tblcrmcontactrecord)
			{
				$sqlnewcrmcontactid = "select nextval('tblcrmcontact_crmcontactid_seq') as id";
				$new_crmcontactid = db_firstval($sqlnewcrmcontactid);
				$insertcrmcontact = "insert into tblcrmcontact
					(
						crmcontactid
						,crmcontactcreatedby
						,crmcontactcreateddate
						,crmcontactcreatedip
						,crmcontactname
						,crmcontactfname
						,crmcontactlname
						,crmcontactcname
						,crmcontactaddress
						,crmcontactaddress2
						,crmcontactcity
						,crmcontactstate
						,crmcontactzip
						,crmcontactphone
						,crmcontactphoneextension
						,crmcontactfax
						,crmcontactemail
						,crmcontactwebsite
						,crmcontacttitle
						,crmcontactroleid
						,crmid
						
					)
					VALUES
					(
						".$new_crmcontactid."
						,". $loginid ."
						,NOW()
						,".db_tick($_SESSION['login']['loginrecentip'])."
						,".db_tick($tblcontactrecord['contactcname'])."
						,".db_tick($tblcontactrecord['contactfname'])."
						,".db_tick($tblcontactrecord['contactlname'])."
						,".db_tick($tblcontactrecord['clientcompanyname'])."
						,".db_tick($tblcontactrecord['contactaddress'])."
						,".db_tick($tblcontactrecord['contactaddress2'])."
						,".db_tick($tblcontactrecord['contactcity'])."
						,".db_tick($tblcontactrecord['contactstate'])."
						,".db_tick($tblcontactrecord['ocontactzip'])."
						,".db_tick($tblcontactrecord['contactphone'])."
						,".db_tick($tblcontactrecord['contactphoneextension'])."
						,".db_tick($tblcontactrecord['contactphoneextension'])."
						,".db_tick($tblcontactrecord['contactemail'])."
						,".db_tick($tblcontactrecord['contactwebsite'])."
						,".db_tick($tblcontactrecord['contacttitle'])."
						,".db_tick($tblcontactrecord['contactroleid'])."
						,".crmid($loginid)."
					)
				";
			db_exec($insertcrmcontact,1);
			
		    }
			$tblcrmcontactconnectionrecord=db_first("select * from tblcrmcontactconnector where 0=0
								   --contactid='".db_number($contactid)."'
								   and crmcontactid=".$new_crmcontactid."
								   ");
			if(!$tblcrmcontactconnectionrecord)
			{
				$insertcrmconnection="insert into tblcrmcontactconnector
					(crmcontactconnectorcreatedby
					,crmcontactconnectorcreateddate
					,crmcontactid
					,contactid
					
					)
					VALUES
					(
						'".$loginid."'
						,NOW()
						,".db_number($new_crmcontactid)."
						,".db_number($contactid)."
						
					)
				";
				$runquery=db_exec($insertcrmconnection);
				
			}

			$displayreturnval = get_crmrecruitingactions($crmsettings,$new_crmcontactid);
            if(loginid() || !strstr($_SERVER[PHP_SELF], 'submitoffers.php'))//Don't display if user is not logged in and on page submitoffers.php.
                d('$displayreturnval',$displayreturnval); 
            
			return $new_crmcontactid;
		}
		else
		{
			d("no crmrecruiting settings found - no changes made to tblcrmcontact or tblcrmcontactconnector");
		}

}
function check_unsubscribe($email, $crmcontactid=0, $crmcontactmcampaignid=0)
{
	if(!trim($email))
		return false;
	$sql="select unsubscribeid
					from tblunsubscribe
					where crmid=0
						and unsubscribealldate is not null
						and unsubscribeemail=".db_tick($email)."
					";
	$qry=db_firstval($sql);
	if($qry)
		return $qry;
	if(!$crmcontactid && !$crmcontactid)
		return false;
			
	$sql="select unsubscribeid
					from tblunsubscribe
					where crmid<>0
						and unsubscribeemail=".db_tick($email)."
						and
					";
	if($crmcontactid && $crmcontactmcampaignid)
		$sql.="			(
							(
					";
	if($crmcontactid)
		$sql.="					crmid=	(
												select crmid
														from tblcrmcontact
														where crmcontactid=".db_number($crmcontactid)."
										)
								and unsubscribealldate is not null
					";
	if($crmcontactid && $crmcontactmcampaignid)
		$sql.="				)
							or
							(
					";
	if($crmcontactmcampaignid)
		$sql.="					unsubscribeid in	(
														select unsubscribeid
															from tblunsubscribeentry
															where crmcontactmcampaignid=".db_number($crmcontactmcampaignid)."
													)
					";
					
	if($crmcontactid && $crmcontactmcampaignid)
		$sql.="				)
						)
					";
	$qry=db_firstval($sql);
	if($qry)
		return $qry;
	
	return false;
}
function mcampaign_onlyemailactions($crmcontactmcampaignid=0, $mcampaignid=0)
{
	if(!$crmcontactmcampaignid && !$mcampaignid)
		return false;
	$sql="select 	mcampaignactionid
					, mcampaignactionexectemailid
					, mcampaignactionexectemaillistid
					from tblmcampaign
						left join  tblmcampaignaction using (mcampaignid)
					where mcampaignid=".db_number($mcampaignid)."
					";
	if($crmcontactmcampaignid)
	{
		$sql="select 	mcampaignactionid
						, mcampaignactionexectemailid
						, mcampaignactionexectemaillistid
						from tblcrmcontactmcampaign
						join tblmcampaign using(mcampaignid)
							left join  tblmcampaignaction using (mcampaignid)
						where crmcontactmcampaignid=".db_number($crmcontactmcampaignid)."
						";
	}
	$mcampaign=db_query($sql);
	$mcampaignonlyemail=TRUE;
	foreach($mcampaign as $mcampaignkey=>$mcampaigninfo)
	{
		if($mcampaigninfo['mcampaignactionid'] && !$mcampaigninfo['mcampaignactionexectemailid'] && !$mcampaigninfo['mcampaignactionexectemaillistid'])
		{
			$mcampaignonlyemail=FALSE;
			break;
		}
	}
	return $mcampaignonlyemail;
}
function crmcontactmcampaign_stop($crmcontactmcampaignid)
{
	if(!$crmcontactmcampaignid)
		return;
	$sql="select crmcontactid
				,crmcontactmcampaigncancelleddate
				, mcampaignname
				from tblcrmcontactmcampaign
				join tblmcampaign using(mcampaignid)
				where crmcontactmcampaignid=".db_number($crmcontactmcampaignid);
	$crmcontactmcampaign=db_first($sql);
	if(!$crmcontactmcampaign || $crmcontactmcampaign['crmcontactmcampaigncancelleddate'])
		return;
		
	$sql="update tblcrmcontactmcampaign
					set crmcontactmcampaigncancelleddate=now()
					where crmcontactmcampaignid=".db_number($crmcontactmcampaignid)."
					";
	db_exec($sql);
	$crmhistorysubject=$crmcontactmcampaign['mcampaignname']." Campaign Stopped/Canceled";
	$crmhistorytext=$crmcontactmcampaign['mcampaignname']." Campaign has been Stopped/Canceled and will no longer continue to run actions.";
	$crmcontactmcampaignarr['crmcontactmcampaignid']=$crmcontactmcampaignid;
	crmhistory_insert($crmcontactmcampaign['crmcontactid'],"campaignaction",$crmhistorysubject,$crmhistorytext,$crmcontactmcampaignarr);
}

function replacetext_frommaparr($text, $replacementmaparr, $replacementinfoarr)
{
	//replacementmaparr example
	//$replacementmap['fname']='crmcontactfname';
	
	//$replacementinfoarr example
	//$replacementmap['crmcontactfname']='Joe';
	
	if(!is_array($replacementmaparr) || !is_array($replacementinfoarr) || !$text)
		return $text;
	$valuesarray=array();
	foreach($replacementmaparr as $replacementkey=>$replacementinfokey)
	{
		$valuesarray[$replacementkey]=$replacementinfoarr[$replacementinfokey];
	}
	
	$returntext=utf8_htmlreplace_with($text, $valuesarray);
	if($returntext)
		return $returntext;
	return $text;
}

/**
 * @name crm_checkcontact
 * @param  integer $crmcontactid
 * @return  integer contactid or 0
 */
function crm_checkcontact($crmcontactid){
	$contactid = 0;
	if (!$crmcontactid)
		return 0;
	$crmdata = db_first("select crmcontactid, crmcontactname, crmid, crmcontactphone, crmcontactaddress, crmcontactstate, crmcontactcity, crmcontactzip from tblcrmcontact where crmcontactid = " . db_number($crmcontactid));
	$companyid = db_firstval("select companyid from tblcrm where crmid = " . db_number($crmdata['crmid']));
	if (!$companyid)
		$companyid = companyid();
	$contactid = db_firstval("select contactid from tblcrmcontactconnector where crmcontactid = ".db_number($crmcontactid));
	if (!$contactid)
		$contactid = db_firstval("select contactid from tblcontact where contactcname ilike ".db_tick($crmdata['crmcontactname'])." and companyid = ".$companyid." order by contactid limit 1");
	return $contactid;
}
function crm_mergecrmcontacts($from_crmcontactid, $to_crmcontactid)
{
	$fromid = db_number($from_crmcontactid);
	$toid = db_number($to_crmcontactid);
	$result = false;
	$crmcontacts = db_query("select crmcontactid from tblcrmcontact where crmcontactid in ({$fromid}, {$toid})", 1, 0, 'crmcontactid');
	//if we found both the crmcontacts
	if ($crmcontact && (count($crmcontact) == 2) && crmcontacttocontact($fromid))
	{
        //change all references from the fromid to the toid
        $tables = db_getcolumntables('crmcontactid', 'tblcrmcontact');
        if ($tables) foreach ($tables as $table)
        {
            $sql = "update {$table[table_name]} set crmcontactid = $toid where crmcontactid = $fromid";
            db_exec($sql);
        }

        //delete the old crm contact
        $sql = "
            delete from tblcrmcontact
            where crmcontactid = ".$fromid;
        $result = db_exec($sql);
	}
	return $result;
}
/**
 * @name crmcontacttocontact
 * @param integer $crmcontactid
 * @return integer $contactid | 0
 */
function crmcontacttocontact($crmcontactid)
{
	$contactid = 0;
	if ($crmcontactid <1 )
		return $contactid;
	$crmcontact_data = db_first("select * from tblcrmcontact where crmcontactid =" . db_number($crmcontactid));
	if (!$crmcontact_data)
		return $contactid;
	$contactid = crm_checkcontact($crmcontact_data); // this will return 0 or contactid
	if ($contactid > 0){
		$check_connector = db_firstval ("select crmcontactconnectorid from tblcrmcontactconnector where crmcontactid = " . db_number($crmcontactid));
		if ($check_connector < 1)
		{
			db_exec ("insert into tblcrmcontactconnector (crmcontactid, contactid, crmcontactconnectorcreatedby) values (" . $crmcontactid . "," . $contactid . "," . loginid() . ")");
		}
		return $contactid; // we got contactid back so nothing todo here.
	}
	$contactid = db_nextid('contact');
	$companyid = db_firstval("select companyid from tblcrm where crmid = " . db_number($crmcontact_data['crmid']));
	if ($companyid <1)
		$companyid = companyid(); // Just in case this crm does not have companyid
	$sql = "insert into tblcontact (
						contactid
						, contacttype
						, contactcname
						, contactphone
						, contactphoneextension
						, contactemail
						, companyid
						, contactcreated
						, contactcreatedby
						, contactcreatedip
						, contactwebsite
						, contactaddress
						, contactaddress2
						, contactcity
						, contactstate
						, contactzip
						, contactfax)
					values (
						" . $contactid . "
						, 'crmcontact'
						, " . db_tick($crmcontact_data['crmcontactname']) . "
						, " . db_tick($crmcontact_data['crmcontactphone']) . "
						, " . db_tick($crmcontact_data['crmcontactphoneextension']) . "
						, " . db_tick($crmcontact_data['crmcontactemail']) . "
						, " . $companyid . "
						, now()
						, " . loginid() . "
						, " . db_tick($_SERVER['REMOTE_ADDR'])."
						, " . db_tick($crmcontact_data['crmcontactwebsite']) . "
						, " . db_tick($crmcontact_data['crmcontactaddress']) . "
						, " . db_tick($crmcontact_data['crmcontactaddress2']) . "
						, " . db_tick($crmcontact_data['crmcontactcity']) . "
						, " . db_tick($crmcontact_data['crmcontactstate']) . "
						, " . db_tick($crmcontact_data['crmcontactzip']) . "
						, " . db_tick($crmcontact_data['crmcontactfax']) . "
						)";

	if (!db_exec($sql,1))
		return 0;
	// Now create connector ...
	$sql = " insert into tblcrmcontactconnector (
									crmcontactconnectorcreatedby
									, crmcontactid
									, contactid
									)
								values (
									" . loginid() . "
									, " . $crmcontactid . "
									, " . $contactid . "
									)";
      db_exec($sql);
	return $contactid;
}
function getcrmcustomfieldentries($crmcontactid)
{
    $sql="
        select crmcustomfieldid
            , crmcustomfieldentryid
            , crmcustomfieldentryvalue
        from tblcrmcustomfieldentry
        where 0=0
            and companyid=".companyid()."
            and crmcontactid=".db_number($crmcontactid);
    return db_query($sql,1,0,'crmcustomfieldid');
}
function savecrmcustomfield($crmcustomfieldids, $crmcustomfields, $crmcontactid, $crmcustomfieldheading = '')
{
	$headingid = "";
	if (!is_array($crmcustomfieldids))
		return $headingid;
    $sql="
		select crmcustomfieldid
			, crmcustomfieldtype
			, crmcustomfieldentryid
			, crmcustomfieldheading
        from tblcrmcustomfield
            left join
			(
					select crmcustomfieldid
						, crmcustomfieldentryid
					from tblcrmcustomfieldentry
					where companyid = ".companyid()."
						and crmcontactid = ".db_number($crmcontactid)."
			) tblcrmcustomfieldentry using (crmcustomfieldid)
        where crmcustomfieldid in (".implode(",",$crmcustomfieldids).")";
    if (trim($crmcustomfieldheading) != '')
    	$sql .= "
			and crmcustomfieldheading = ".db_tick($crmcustomfieldheading);
    $crmcustomfieldentries = db_query($sql,1,0,'crmcustomfieldid');

	//have to check the whole set rather than the posted set
	//since the posted set may be missing values from the set
	foreach($crmcustomfieldentries as $customfieldid => $data)
	{
		if(!$headingid)
			$headingid = $customfieldid;
		
		$customfieldvalue = $crmcustomfields[$customfieldid];
		if(is_array($customfieldvalue))
			$customfieldvalue = implode(",", $customfieldvalue);
		else if (trim($customfieldvalue) == '')
			$customfieldvalue = " ";

		$sql="";
		//if we have a posted customfield id then insert/update
        if($data['crmcustomfieldentryid'])
        {
            $sql="
                update tblcrmcustomfieldentry
                set crmcustomfieldentryupdatedby= ".loginid()."
                    , crmcustomfieldentryupdateddate= now()
                    ,crmcustomfieldid=".db_number($customfieldid)."
                    ,companyid=".companyid()."
                    ,crmcustomfieldentryvalue=".db_tick($customfieldvalue)."
                    ,crmcontactid=".db_number($crmcontactid)."
                where crmcustomfieldentryid= ".db_number($data['crmcustomfieldentryid']);
        }
        else
        {
            $sql="
                insert into tblcrmcustomfieldentry
                (
                    crmcustomfieldentrycreatedby
                    ,crmcustomfieldid
                    ,companyid
                    ,crmcustomfieldentryvalue
                    ,crmcontactid
                )
                Values
                (
                    ".loginid()."
                    , ".db_number($customfieldid)."
                    , ".companyid()."
                    , ".db_tick($customfieldvalue)."
                    ,".db_number($crmcontactid)."
                )";
        }

		if($sql)
			db_exec($sql);
	}
	return $headingid;
}


function crm_detailstable($crmcontactarray)
{
	
	
	$customfield_readonly=1;
	
	$name = list_allbutlastandlast($crmcontactarray['crmcontactname'], ' ');
	if(!$crmcontactarray['crmcontactfname'])
	    $crmcontactarray['crmcontactfname']=$name[0];
	if(!$crmcontactarray['crmcontactlname'])
	    $crmcontactarray['crmcontactlname']=$name[1];
	
	  $contacts[0]=$crmcontactarray;  
	$crmcontactname = trim($crmcontactqry['crmcontactfname']." ".$crmcontactqry['crmcontactlname']);
	ob_start();
	?>
	<table class='contact_results beta_table'>
				<?
				if( is_users_crm($_GET['id']))
				{
				?>
				<tr>
					<td>
					<?
						echo "<a href='crmcontact.php?tab=details&edit=" .db_number($_GET['id']) . "'>" . button('edit') . " </a>";
						?>
					</td>
					<td>
						
						<?
						echo "<a href='crmcontactlist.php?deactivateid=" . db_number($_GET['id']) . "'  title='Deactivate'>" . button('deactivate','Deactivate') . "</a>";
						?>
					</td>
				</tr>
				<?
				}
				?>
				<tr <? echo alternate_class('bgmint'); ?>>
					<td>First Name</td>
					<td>
						<span class='view_only'><? echo $contacts[0]['crmcontactfname']; ?></span>
						
					</td>
				</tr>
				<tr <? echo alternate_class('bgmint'); ?>>
					<td>Last Name</td>
					<td>
						<span class='view_only'><? echo $contacts[0]['crmcontactlname']; ?></span>
						
					</td>
				</tr>
				
				<tr <? echo alternate_class(bgmint); ?>>
					<td>Address</td>
					<td>
						<span class='view_only'><? echo $contacts[0]['crmcontactaddress']; ?></span>
						
					</td>
				</tr>
				<tr <? echo alternate_class(bgmint); ?>>
					<td>Address 2</td>
					<td>
						<span class='view_only'><? echo $contacts[0]['crmcontactaddress2']; ?></span>
						
					</td>
				</tr>
				<tr <? echo alternate_class(bgmint); ?>>
					<td>City</td>
					<td>
						<span class='view_only'><? echo $contacts[0]['crmcontactcity']; ?></span>
						
					</td>
				</tr>
				<tr <? echo alternate_class(bgmint); ?>>
					<td>State</td>
					<td>
						<span class='view_only'><? echo $contacts[0]['crmcontactstate']; ?></span>
						
					</td>
				</tr>
				<tr <? echo alternate_class(bgmint); ?>>
					<td>Zip</td>
					<td>
						<span class='view_only'><? echo $contacts[0]['crmcontactzip']; ?></span>
						
					</td>
				</tr>
				<tr <? echo alternate_class(bgmint); ?>>
					<td>Phone</td>
					<td>
						<span class='view_only'><? echo $contacts[0]['crmcontactphone']; ?></span>
						
					</td>
				</tr>
				<tr <? echo alternate_class(bgmint); ?>>
					<td>Phone Ext.</td>
					<td>
						<span class='view_only'><? echo $contacts[0]['crmcontactphoneextension']; ?></span>
						
					</td>
				</tr>
				<tr <? echo alternate_class(bgmint); ?>>
					<td>Cell Phone</td>
					<td>
						<span class='view_only'><? echo $contacts[0]['crmcontactcellphone']; ?></span>
						
					</td>
				</tr>
				<tr <? echo alternate_class(bgmint); ?>>
					<td>Fax</td>
					<td>
						<span class='view_only'><? echo $contacts[0]['crmcontactfax']; ?></span>
						
					</td>
				</tr>
				<tr <? echo alternate_class(bgmint); ?>>
					<td>Email</td>
					<td>
						<span class='view_only'><? echo $contacts[0]['crmcontactemail']; ?></span>
						
					</td>
				</tr>
				<tr <? echo alternate_class(bgmint); ?>>
					<td>Website</td>
					<td>
						<span class='view_only'><? echo $contacts[0]['crmcontactwebsite']; ?></span>
						
					</td>
				</tr>
				
			</table>
	<?
	$out=ob_get_clean();
	return $out;
	
}



function is_users_crm($crmid)
{
	$sql="select crmcontactcreatedby from tblcrmcontact where crmcontactid=".db_number($crmid)." and crmcontactcreatedby=".loginid()."";
	$ret=db_first($sql);
	if($ret)
		return 1;
	return 0;
	
}

function crm_contactname($crmcontactid)
{
	$rval = '';
	if (is_numeric($crmcontactid))
		$crmcontact = get_crmcontact($crmcontactid);
	else 
		$crmcontact = get_crmcontact_byname($crmcontactid);

	if ($crmcontact)
	{
		$cname = trim("$crmcontact[crmcontactfname] $crmcontact[crmcontactlname]");
		$rval = ($cname)?$cname:trim($crmcontact[crmcontactname]);
	}
	return $rval;
}
function get_crmcontact($crmcontactid, $activeonly = true, $firstonly = true)
{
	$sql = "
        select * 
        from tblcrmcontact 
        	join tblcrm using (crmid)
        where companyid = ".companyid()."
			and crmcontactid = ".db_number($crmcontactid);
	if(!has_access('viewallcompanycontacts'))
		$sql .= "
			and crmcontactcreatedby = ".db_number(loginid());
	elseif(officelocationid())	
		$sql .= "
			and crmcontactcreatedby in (
											select loginid 
													from tblstaff 
														join tbllogin using(staffid)
													where companyid=".companyid()."
														and officelocationid=".officelocationid()."   
										)";	
	if ($activeonly)
		$sql .= "
			and crmcontactinactivateddate is null";
	$result = ($firstonly)?db_first($sql):db_query($sql);
	return $result;
}
function get_crmcontact_byname($name, $activeonly = true, $firstonly = true)
{
	$name = strtolower($name);//push to lower case
	$name = preg_replace("/[^a-z0-9]/", "", $name);//exchange characters we don't want for spaces

	$sql = "
		select * 
		from tblcrmcontact 
			join tblcrm using (crmid)
		where companyid = ".companyid()."
			and ( 
				lower(regexp_replace(crmcontactname, ' ', '', 'g')) = ".db_tick($name)."
				or lower(regexp_replace(crmcontactfname||crmcontactlname, ' ', '', 'g')) = ".db_tick($name)."
			)";
	if(!has_access('viewallcompanycontacts'))
		$sql .= "
			and crmcontactcreatedby = ".db_number(loginid());
	elseif(officelocationid())	
		$sql .= "
			and crmcontactcreatedby in (
											select loginid 
													from tblstaff 
														join tbllogin using(staffid)
													where companyid=".companyid()."
														and officelocationid=".officelocationid()."   
										)";	
	if ($activeonly)
		$sql .= "
			and crmcontactinactivateddate is null";
	$result = ($firstonly)?db_first($sql):db_query($sql);
	return $result;
}
function get_agentcrmcontacts($preselected=0)
{
    $sql = "
        select *, 0 as inactive
        from tblcrmcontact
			join tblcrm using (crmid)
			join tbllogin using (loginid)
        where tblcrm.companyid = ".companyid()."
			and crmcontactinactivateddate is null
			and logininactive is null 
			and (
					crmcontactcreatedby = ".loginid()."
			";
    if($preselected)
        $sql .= "
					or crmcontactid = ".db_number($preselected);
    $sql .= "
    		)
        order by crmcontactname ";
    $ret=db_query($sql);
    
	$crmaccessible=0;
	if($preselected)
	{
		if($ret) foreach($ret as $$rk=>$rv)
		{
			if($rv['crmcontactid'] == $preselected)
            {
				$crmaccessible=1;
                break;
            }
		}
		if(!$crmaccessible)
		{
		    //If the preselected CRM Contact did not return from the sql above, it must be inactive.
            $sql = "
                select *, 1 as inactive
                from tblcrmcontact
                join tblcrm using (crmid)
                where tblcrm.companyid = ".companyid()."
                and crmcontactid = ".db_number($preselected);
            if(!has_access('viewallcompanycontacts'))
                $sql .= "
                and crmcontactcreatedby = ".db_number(loginid());
			elseif(officelocationid())	
				$sql .= "
					and crmcontactcreatedby in (
													select loginid 
															from tblstaff 
																join tbllogin using(staffid)
															where companyid=".companyid()."
																and officelocationid=".officelocationid()."   
												)";	
            $forceret=db_query($sql);
            
			$ret=array_merge($forceret,$ret);
		}
	}
	return $ret;
}
function touch_crm()
{
	//if we have the crm, the company has crm
	if(has_crm())
	{
		//grab any staff member that has an email address and does not have a crmid
		//and make an entry into the crm table
		$sql = "
			insert into tblcrm (crmcreatedby, loginid, companyid, staffid)
			select loginid, loginid, companyid, staffid
			from 
			(
				select coalesce(staffemail,companyemail) as email, crmid, loginid, companyid, staffid
				from tbllogin
					left join (select staffemail,staffid from tblstaff) as tblstaff using(staffid)
					left join (select companyemail,companyid from tblcompany) as tblcompany using(companyid)
					left join (select loginid,crmid from tblcrm) as tblcrm using (loginid)
				where companyid = ".companyid()."
			) tbltbl
			where email is not null
				and crmid is null ";
		db_exec($sql);
	}
}