<?
function contact_get($contactid)
{
	$sql = "select	*
			from	tblcontact
			where	contactid = " . db_number($contactid);
	$contact = db_first($sql);
	
	if($contact)
		return $contact;
	else
		return false;
}

function getcontact($contactid)
{
	$sql = "select 		*
					from 		tblcontact
					left join	tblclient using(clientid)
					left join	tblclienttype using(clienttypeid)
					";
			if (clientcompanyid())
					$sql .= " where 	   (tblcontact.clientcompanyid = " . clientcompanyid();

			if (companyid())
					$sql .= " where 	   (tblcontact.companyid = " . companyid();
			$valuecheck=str_replace("s_", "", $contactid);
			if($valuecheck>0 && (companyid() || clientcompanyid()))
				$sql.=" 		or tblcontact.contactid =  ".db_number($valuecheck).") ";
			else if ((!($valuecheck>0)) && (companyid() || clientcompanyid()))
				$sql .=" )";
			
			if (!companyid() && !clientcompanyid())
				$sql .= " where tblcontact.contactid =  ".db_number($valuecheck);
			
			$sql .= "
					and 		contactinactive is null
					and			clientinactive is null ";
				$sql .= "and contactid = ".db_number($contactid);
			$sql .="
						and trim (contactcname) <> ''
						order by 	" . (!$clientid ? "clientcompanyname, " : "") . "contactcname";
						
			$ret=db_query($sql);
			return $ret;
}


function contactname_get($contactid)
{
	if($contactid==0||!is_numeric($contactid))
		return false;
	$sql = "select	contactname
			from	tblcontact
			where	contactid = " . db_number($contactid);
	$contact = db_first($sql);
	
	if($contact)
		return $contact[contactname];
	else
		return false;
}
function contacts_get($contacttypeid = '', $clienttypeid = '')
{
	if ($contacttypeid == 1303)
	{
		// Exception for CRM Contacts
		$sql  = "
			select * 
			from tblcontact
			where contactinactive is null
				and trim(contacttype) = 'crmcontact'
				and companyid = " . companyid();
	}
	else
	{
		$sql = "
			select *
			from tblcontact";
				
		if($contacttypeid)
			$sql .= "
				join tblcontact_contacttype using (contactid)";
	
		if($clienttypeid)
			$sql .= "
				join tblcontact_contacttype using  (contactid)
				join tblcontacttype using (contacttypeid)";
		
		$sql .= "
			where contactinactive is null 
				and tblcontact.companyid = " . companyid();
		
		if($contacttypeid)
			$sql .= "
				and contacttypeid = " . db_number($contacttypeid);
		
		if($clienttypeid)
			$sql .= "
				and tblcontacttype.clienttypeid = " . db_number($clienttypeid);
		
		$sql .= "
			order by contactcname";
	}
	$contacts = db_query($sql);
	//REMOVE DUPLICATES
	foreach($contacts as $key => $contact)
	{
		if($contact['contactid'] == $last_contact['contactid'])
			unset($contacts[$key]);
		$last_contact = $contact;
	}
	
	if($contacts)
		return $contacts;
	else
		return array();
}
function contactcname_get($contactid)
{
	return contact_getfield($contactid, 'contactcname');
}
function contact_delete($contactid)
{
	$sql = "delete
			from	tblcontact
			where	contactid = " . db_number($contactid);
	if(clientcompanyid())
		$sql .= "
			and		clientcompanyid = " . clientcompanyid();
	else if(companyid())
		$sql .= "
			and		companyid = " . companyid();
	db_exec($sql);
}
function contact_getfield($contactid, $fieldname)
{
	
	$sql = "select	".db_col($fieldname)."
			from	tblcontact
			where	contactid = " . db_number($contactid);
	$contact = db_first($sql);
	
	if($contact[$fieldname])
		return $contact[$fieldname];
	else
		return false;
}

function contact_format(&$contact)
{
	if ($contact)
	{
		$fname = $contact['contactfname'];
		$lname = $contact['contactlname'];
		$cname = $contact['contactcname'];
		if (trim($fname) != '' || trim($lname) != '')
			$cname = trim("$fname $lname");
		else if (trim($cname) != '')
		{
			$lname = trim(list_last($cname, " "));
			$fname = trim(list_allbutlast($cname, " "));
			if ($fname == '')
			{
				$fname = $lname;
				$lname = '';
			}
		}
		$contact['contactcname'] = $cname;
		$contact['contactfname'] = $fname;
		$contact['contactlname'] = $lname;

		foreach (array('contactphone' => 49, 'contactcellphone' => 49) as $key => $len)
		{
			if($contact[$key])
			{
				$data = trim($contact[$key]);
				$datalen = strlen($data);
				$contact[$key] = substr($data , 0, ($datalen < $len ? $datalen : $len));
			}
		}
	}
	return $contact;
}
function contact_getcolumns()
{
	global $contact_columns;
	if (!$contact_columns)
	{
		$sql = "
			select column_name, data_type from information_schema.columns 
			where table_name='tblcontact'
				and column_name not in 
				(
					'contactid', 'contacttype', 'companyid', 'clientcompanyid'
					, 'contactcreated','contactcreatedby','contactcreatedip'
					, 'contactupdated','contactupdatedby','contactupdatedip'
				)";
		$columns_qry = db_query($sql);
		$contact_columns = array();
		foreach ($columns_qry as $data)
		{
			switch ($data[data_type]) {
				case 'timestamp without time zone':
					$contact_columns[$data[column_name]] = 'db_datetimenoadjust';//The only column that is currently hitting this case is contactinactive, which does not have a way of being set with a timezone, so it can safely skip the adjustment for timezone in db_datetime().
					break;
				case 'integer':
					$contact_columns[$data[column_name]] = 'db_number';
					break;
				case 'character varying':
				default:
					$contact_columns[$data[column_name]] = 'db_tick';
			}
		}
	}
	return $contact_columns;
}
function contact_set($contactdata, $contactid=0, $contacttype='', $companyid=0, $clientcompanyid=0)
{
	$contactdata = contact_format($contactdata);
	
	if (!$companyid)
		$companyid = ($contactdata[companyid])?$contactdata[companyid]:companyid();
	if (!$clientcompanyid)
		$clientcompanyid = ($contactdata[clientcompanyid])?$contactdata[clientcompanyid]:clientcompanyid();
		
	//validate the contact id
	if ($contactid > 0)
	{
		$validate_sql = "
			select contactid 
			from tblcontact 
			where contactid = ".db_number($contactid) ;
		if ($companyid)
			$validate_sql .= "
				and companyid = ".db_number($companyid);
		if ($clientcompanyid)
			$validate_sql .= "
				and clientcompanyid = ".db_number($clientcompanyid);
		$contactid = db_firstval($validate_sql);
		//trying to update to an id that doesn't exist in the system
		
		if (!$contactid)
			return false;

		//make sure we are processing all valid columns
		foreach (contact_getcolumns() as $key => $db_func)
		{
			if (isset($contactdata[$key]))
			{
				$addondat .= "
				, {$key} = ".$db_func($contactdata[$key]);
			}
		}

		$sql = "
			update tblcontact
			set contactupdated = now()
				, contactupdatedby = ".loginid()."
				, contactupdatedip = ".db_tick($_SERVER[REMOTE_ADDR])." $addondat
				, contacttype = ".db_tick($contacttype)."
			where contactid = {$contactid}";
	}
	else
	{
		$contactid = db_nextid('contact');
		//make sure we are processing all valid columns
		foreach (contact_getcolumns() as $key => $db_func)
		{
			if (isset($contactdata[$key]))
			{
				$addonsql .= "
				, {$key} ";
				$addondat .= "
				, ".$db_func($contactdata[$key]);
			}
		}
		$sql = "
			insert into tblcontact 
			(	
				contactid
				, contacttype
				, companyid
				, clientcompanyid
				, contactcreated
				, contactcreatedby
				, contactcreatedip $addonsql
			) 
			values
			(
				{$contactid}
				, ".db_tick($contacttype)."
				, ".db_number($companyid)."
				, ".db_number($clientcompanyid)."
				, now()
				, ".loginid()."
				, ".db_tick($_SERVER[REMOTE_ADDR])." $addondat
			)";
	}
	//if we have something to process, process it
	if ($sql && db_exec($sql, 1))
		return $contactid;
	return false;
}	
function get_contact($contactid, $activeonly = true, $firstonly = true)
{
	$sql = "
		select *
		from tblcontact
		where companyid = ".companyid()."
			and contactid = ".db_number($contactid);
	if ($activeonly)
		$sql .= "
			and contactinactive is null";
	$result = ($firstonly)?db_first($sql):db_query($sql);
	return $result;
}
function get_contact_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 tblcontact 
        where companyid = ".companyid()."
			and ( 
        		lower(regexp_replace(contactcname, ' ', '', 'g')) = ".db_tick($name)."
				or lower(regexp_replace(contactfname||contactlname, ' ', '', 'g')) = ".db_tick($name)."
			)";
	if ($activeonly)
		$sql .= "
            and contactinactive is null";
	$result = ($firstonly)?db_first($sql):db_query($sql);
	return $result;
}