__sql__samples.php

	function __sql__(){
		$userid = 0;
		$agency_id=0;
		$user = getUser();
		if ( $user ){
			$userid = $user->val('user_id');
			$agency_id = $user->val('organization_id');
		} else {
			$agency_id = Dataface_Application::getInstance()->getDelegate()->organizationID;
		}
		$sql = "select p.*, 
			concat(m.first_name,' ', m.last_name) as manager_name, 
			m.email_address as manager_email, 
			m.daytime_phone_number as manager_phone,
			if(c.planned_deadline_date>NOW(), c.planned_deadline_date,NULL)  as next_competition_application_deadline,
			if(c.planned_decision_date>NOW(), c.planned_decision_date,NULL) as next_competition_decision_date,
			c.number_of_awards_budgeted as max_no_of_awards_for_next_competition,
			pc.life_success_ratio,
			pc.life_subscription_ratio,
			pc.time_to_decision_seconds,
			pc.avg_rating,
			pc.num_ratings,
			pc.is_internal,
			pc.funding_agency_id,
			o.state_id as funding_agency_state_id,
			pol.role_id,
			if(pc.is_internal and pc.funding_agency_id != '".addslashes($agency_id)."', 1, 0) as not_mine, 
			if(pol.role_id=7 or pol.role_id=8 or pol.role_id=9 or p.status_id<>4 or p.status_id is null, 0,1) as closed_to_me,
			o.corporate_name as funding_agency_name,
			if(ufa.user_id is null,0,1) as favourite,
			
			null as languages_disp,
			null as applicant_types_disp,
			null as organization_types_disp,
			null as funding_agencies_disp,
			null as research_officer_disp
			from programs p
			left join programs_calc pc on p.program_id=pc.program_id
			left join users m on pc.manager_id=m.user_id
			
			left join competitions c on pc.next_competition_id=c.competition_id
			left join program_officer_lookup pol on (pol.program_id=p.program_id and pol.user_id='".addslashes($userid)."')
			left join user_favourites `ufa` on (`ufa`.program_id=p.program_id and `ufa`.user_id='".addslashes($userid)."')
			left join organizations o on o.organization_id=pc.funding_agency_id
			";
		//echo $sql;exit;
		return $sql;
	}
	
	//////
	
	/**
	 * We use the __sql__ method instead of the __sql__ fields.ini directive
	 * because we want to use a different SQL query for the authors table
	 * in different cases.  E.g. if the user is trying to filter
	 * authors so that he only sees those for which he has access, then
	 * the SQL query will filter based on this.
	 * @return string;
	 */
	function __sql__(){
		static $sql = -1;
		if ( !is_string($sql) ){
			$app =& Dataface_Application::getInstance();
			$query =& $app->getQuery();
		
			$sql =  "SELECT authors.*, users.email AS author_email FROM authors LEFT JOIN users ON authors.owner_id = users.user_id";
			if ( $query['-table'] == 'authors' and $query['-action'] == 'list' and isSubscriber() and @$query['-filter-subscribed'] ){
				$subscriptions =& getSubscriptions();
				$cfStr = implode(',', array_keys($subscriptions['conferences']));
				if ( !$cfStr ) $cfStr = '-1';
				$crStr = implode(',', array_keys($subscriptions['courses']));
				if ( !$crStr ) $crStr = '-1';
				$res = mysql_query("create temporary table my_author_subscriptions 
					select distinct aa.author_id 
							from authors_abstracts aa 
							inner join abstracts a on aa.abstract_id=a.AbstractID
							where a.AbstractConferenceID in (".$cfStr.") 
							or a.AbstractCourseID in (".$crStr.")
						", df_db());
				if ( !$res ) trigger_error(mysql_error(df_db()), E_USER_ERROR);
				$sql .= " inner join my_author_subscriptions mas on authors.author_id=mas.author_id";
			
			
			}
		}
		
		return $sql;
	}