<?php

// GET

if ( ! function_exists( 'wpj_get_user_offers_received' ) ) {
	function wpj_get_user_offers_received( $rid = '', $uid = '', $return = '' ) {
		global $wpdb;

		$query = "
			SELECT * FROM (
				SELECT * FROM {$wpdb->prefix}job_pm
				WHERE associate_request_id = '{$rid}'
				AND (
					( user = '{$uid}' AND show_to_destination = '1' ) OR ( initiator = '{$uid}' AND show_to_source = '1' )
				)
				ORDER BY datemade DESC
			) AS t1
			ORDER BY datemade ASC
		";

		$r = $wpdb->get_results( $query );

		if ( $return == 'query' ) return $query;
		if ( $return == 'count' ) return count( $r );

		return $r;

	}
}

if ( ! function_exists( 'wpj_get_request_offers_received' ) ) {
	function wpj_get_request_offers_received( $rid = '', $return = '' ) {
		global $wpdb;

		$query = "
			SELECT * FROM (
				SELECT * FROM {$wpdb->prefix}job_pm
				WHERE associate_request_id = '{$rid}'
				ORDER BY datemade DESC
			) AS t1
			ORDER BY datemade ASC
		";

		if ( $return == 'query' ) return $query;

		if ( $return == 'count' ) {
			return $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$wpdb->prefix}job_pm WHERE associate_request_id = %d", $rid ) );
		}

		return $wpdb->get_results( $query );
	}
}

if ( ! function_exists( 'wpj_get_expired_active_requests' ) ) {
	function wpj_get_expired_active_requests() {
		global $wpdb;

		$query = "
			SELECT p.ID
			FROM {$wpdb->prefix}posts p
			INNER JOIN {$wpdb->prefix}postmeta pm ON (p.ID = pm.post_id)
			WHERE p.post_type = 'request'
				AND p.post_status = 'publish'
				AND pm.meta_key = 'active'
				AND pm.meta_value = '1'
				AND (
					(
						EXISTS (
							SELECT 1
							FROM {$wpdb->prefix}postmeta AS mt1
							WHERE p.ID = mt1.post_id
							AND mt1.meta_key = 'request_end_date'
							AND mt1.meta_value != ''
							AND mt1.meta_value <= UNIX_TIMESTAMP()
						)
					)
					OR
					(
						EXISTS (
							SELECT 1
							FROM {$wpdb->prefix}postmeta AS mt2
							WHERE p.ID = mt2.post_id
							AND mt2.meta_key = 'request_deadline'
							AND mt2.meta_value != ''
							AND mt2.meta_value <= UNIX_TIMESTAMP()
						)
					)
				)
			GROUP BY p.ID
		";

		return $wpdb->get_results( $query );
	}
}