<?php

// GET

if ( ! function_exists( 'wpj_users_have_transactions' ) ) {
	function wpj_users_have_transactions( $usr1 = '', $usr2 = '' ) {
		if ( $usr1 && $usr2 ) {
			global $wpdb;

			$row = $wpdb->get_row( "
				SELECT *
				FROM {$wpdb->prefix}job_orders orders, {$wpdb->prefix}posts posts
				WHERE orders.pid = posts.ID
				AND ( orders.uid = '{$usr1}' AND posts.post_author = '{$usr2}' )
				OR ( orders.uid = '{$usr2}' AND posts.post_author = '{$usr1}' )
				LIMIT 1
			" );

			if ( $row ) {
				return true;
			}
		}

		return false;
	}
}

if ( ! function_exists( 'wpj_get_sales_orders' ) ) {
	function wpj_get_sales_orders( $uid = '', $order_status = 'active', $ret_type = 'count' ) {
		if ( ! $uid ) {
			$uid = wpj_get_user_id();
		}

		if ( $order_status ) {
			global $wpdb;

			if ( $order_status == 'active' ) {
				$query = $wpdb->prepare(
					"
					SELECT DISTINCT *
					FROM {$wpdb->prefix}job_orders orders, {$wpdb->prefix}posts posts
					WHERE posts.post_author      = %d
						AND posts.ID             = orders.pid
						AND orders.done_seller   = '0'
						AND orders.done_buyer    = '0'
						AND orders.date_finished = '0'
						AND orders.closed        = '0'
						AND payment_status      != 'pending'
					ORDER BY orders.id DESC
					",
					$uid
				);

			} elseif ( $order_status == 'pending-confirmation' ) {
				$query = $wpdb->prepare(
					"
					SELECT DISTINCT *
					FROM {$wpdb->prefix}job_orders orders, {$wpdb->prefix}posts posts
					WHERE posts.post_author = %d
						AND posts.ID = orders.pid
						AND orders.done_seller = '0'
						AND orders.done_buyer = '0'
						AND orders.date_finished = '0'
						AND orders.closed = '0'
						AND orders.seller_confirmation = '0'
						AND orders.payment_status != 'pending'
					ORDER BY orders.id DESC
					",
					$uid
				);

			} elseif ( $order_status == 'pending-payment' ) {
				$query = $wpdb->prepare(
					"
					SELECT DISTINCT *
					FROM {$wpdb->prefix}job_orders orders, {$wpdb->prefix}posts posts
					WHERE posts.post_author      = %d
						AND posts.ID             = orders.pid
						AND orders.done_seller   = '0'
						AND orders.done_buyer    = '0'
						AND orders.date_finished = '0'
						AND orders.closed        = '0'
						AND ( payment_status     = 'pending'
							OR payment_status    = 'processing' )
					ORDER BY orders.id DESC
					",
					$uid
				);

			} elseif ( $order_status == 'delivered' ) {
				$query = $wpdb->prepare(
					"
					SELECT DISTINCT *
					FROM {$wpdb->prefix}job_orders orders, {$wpdb->prefix}posts posts
					WHERE posts.post_author    = %d
						AND posts.ID           = orders.pid
						AND orders.done_seller = '1'
						AND orders.done_buyer  = '0'
						AND orders.closed      = '0'
					ORDER BY orders.id DESC
					",
					$uid
				);

			} elseif ( $order_status == 'completed' ) {
				$query = $wpdb->prepare(
					"
					SELECT DISTINCT *
					FROM {$wpdb->prefix}job_orders orders, {$wpdb->prefix}posts posts
					WHERE posts.post_author    = %d
						AND posts.ID           = orders.pid
						AND orders.done_seller = '1'
						AND orders.done_buyer  = '1'
						AND orders.closed      = '0'
					ORDER BY orders.id DESC
					",
					$uid
				);

			} elseif ( $order_status == 'cancelled' ) {
				$query = $wpdb->prepare(
					"
					SELECT DISTINCT *
					FROM {$wpdb->prefix}job_orders orders, {$wpdb->prefix}posts posts
					WHERE posts.post_author = %d
						AND posts.ID        = orders.pid
						AND orders.closed   = '1'
					ORDER BY orders.id DESC
					",
					$uid
				);

			} elseif ( $order_status == 'delivered_on_time_percent' ) {
				$query = $wpdb->prepare(
					"
					SELECT AVG( CASE WHEN date_finished <= expected_delivery THEN 100.0 ELSE 0.0 END ) AS delivered_on_time_percent
					FROM {$wpdb->prefix}job_orders orders, {$wpdb->prefix}posts posts
					WHERE posts.post_author    = %d
						AND posts.ID           = orders.pid
					",
					$uid
				);

			} elseif ( $order_status == 'completed_percent' ) {
				$query = $wpdb->prepare(
					"
					SELECT AVG( CASE WHEN completed = 1 THEN 100.0 ELSE 0.0 END ) AS completed_percent
					FROM {$wpdb->prefix}job_orders orders, {$wpdb->prefix}posts posts
					WHERE posts.post_author    = %d
						AND posts.ID           = orders.pid
					",
					$uid
				);

			} elseif ( $order_status == 'response_rate' ) {
				$query = $wpdb->prepare(
					"
					SELECT AVG( CASE WHEN force_cancellation != 7 THEN 100.0 ELSE 0.0 END ) AS response_rate
					FROM {$wpdb->prefix}job_orders orders, {$wpdb->prefix}posts posts
					WHERE posts.post_author    = %d
						AND posts.ID           = orders.pid
					",
					$uid
				);

			}

			if ( isset( $query ) ) {
				if ( $ret_type == 'query' )
					return apply_filters( 'wpj_sales_' . $order_status . '_orders_query_filter', $query );

				if ( $ret_type == 'result' )
					return apply_filters( 'wpj_sales_' . $order_status . '_orders_result_filter', $wpdb->get_results( $query ) );

				if ( $ret_type == 'row' )
					return apply_filters( 'wpj_sales_' . $order_status . '_orders_row_filter', $wpdb->get_row( $query ) );

				if ( $ret_type == 'var' )
					return apply_filters( 'wpj_sales_' . $order_status . '_orders_var_filter', $wpdb->get_var( $query ) );

				if ( $ret_type == 'count' )
					return apply_filters( 'wpj_sales_' . $order_status . '_orders_count_filter', count( $wpdb->get_results( $query ) ) );
			}
		}

		return 0;
	}
}

if ( ! function_exists( 'wpj_get_shopping_orders' ) ) {
	function wpj_get_shopping_orders( $uid = '', $order_status = 'active', $ret_type = 'count' ) {
		if ( ! $uid ) {
			$uid = wpj_get_user_id();
		}

		if ( $order_status ) {
			global $wpdb;

			if ( $order_status == 'active' ) {
				$query = $wpdb->prepare(
					"
					SELECT DISTINCT *
					FROM {$wpdb->prefix}job_orders
					WHERE uid               = %d
						AND done_seller     = '0'
						AND done_buyer      = '0'
						AND date_finished   = '0'
						AND closed          = '0'
						AND payment_status != 'pending'
					",
					$uid
				);

			} elseif ( $order_status == 'pending-confirmation' ) {
				$query = $wpdb->prepare(
					"
					SELECT DISTINCT *
					FROM {$wpdb->prefix}job_orders orders,
						 {$wpdb->prefix}posts posts
					WHERE orders.uid = %d
						AND posts.ID = orders.pid
						AND orders.done_seller = '0'
						AND orders.done_buyer = '0'
						AND orders.date_finished = '0'
						AND orders.closed = '0'
						AND orders.seller_confirmation = '0'
						AND orders.payment_status != 'pending'
					ORDER BY orders.id DESC
					",
					$uid
				);

			} elseif ( $order_status == 'pending-review' ) {
				$query = $wpdb->prepare(
					"
					SELECT DISTINCT *
					FROM {$wpdb->prefix}job_orders
					WHERE uid           = %d
						AND done_seller = '1'
						AND done_buyer  = '0'
						AND closed      = '0'
					",
					$uid
				);

			} elseif ( $order_status == 'completed' ) {
				$query = $wpdb->prepare(
					"
					SELECT DISTINCT *
					FROM {$wpdb->prefix}job_orders
					WHERE uid           = %d
						AND completed   = '1'
						AND done_seller = '1'
						AND done_buyer  = '1'
						AND closed      = '0'
					ORDER BY id DESC
					",
					$uid
				);

			} elseif ( $order_status == 'pending-payment' ) {
				$query = $wpdb->prepare(
					"
					SELECT DISTINCT *
					FROM {$wpdb->prefix}job_orders
					WHERE uid                 = %d
						AND done_seller       = '0'
						AND done_buyer        = '0'
						AND date_finished     = '0'
						AND closed            = '0'
						AND ( payment_status  = 'pending'
							OR payment_status = 'processing' )
					",
					$uid
				);

			} elseif ( $order_status == 'cancelled' ) {
				$query = $wpdb->prepare(
					"
					SELECT DISTINCT *
					FROM {$wpdb->prefix}job_orders
					WHERE uid      = %d
						AND closed = '1'
					ORDER BY id DESC
					",
					$uid
				);

			} elseif ( $order_status == 'active_custom_offer' ) {
				$query = $wpdb->prepare(
					"
					SELECT DISTINCT *
					FROM {$wpdb->prefix}job_orders orders, {$wpdb->prefix}posts posts
					WHERE orders.pid               = posts.ID
						AND orders.uid             = %d
						AND orders.done_seller     = '0'
						AND orders.done_buyer      = '0'
						AND orders.date_finished   = '0'
						AND orders.closed          = '0'
						AND orders.payment_status != 'pending'
						AND posts.post_type        = 'offer'
					",
					$uid
				);

			} elseif ( $order_status == 'completed_custom_offer' ) {
				$query = $wpdb->prepare(
					"
					SELECT DISTINCT *
					FROM {$wpdb->prefix}job_orders orders, {$wpdb->prefix}posts posts
					WHERE orders.pid           = posts.ID
						AND orders.uid         = %d
						AND orders.completed   = '1'
						AND orders.done_seller = '1'
						AND orders.done_buyer  = '1'
						AND orders.closed      = '0'
						AND posts.post_type    = 'offer'
					ORDER BY orders.id DESC
					",
					$uid
				);

			} elseif ( $order_status == 'cancelled_custom_offer' ) {
				$query = $wpdb->prepare(
					"
					SELECT DISTINCT *
					FROM {$wpdb->prefix}job_orders orders, {$wpdb->prefix}posts posts
					WHERE orders.pid        = posts.ID
					AND orders.uid          = %d
						AND orders.closed   = '1'
						AND posts.post_type = 'offer'
					ORDER BY orders.id DESC
					",
					$uid
				);

			}

			if ( isset( $query ) ) {
				if ( $ret_type == 'query' )
					return apply_filters( 'wpj_shopping_' . $order_status . '_orders_query_filter', $query );

				if ( $ret_type == 'result' )
					return apply_filters( 'wpj_shopping_' . $order_status . '_orders_result_filter', $wpdb->get_results( $query ) );

				if ( $ret_type == 'row' )
					return apply_filters( 'wpj_shopping_' . $order_status . '_orders_row_filter', $wpdb->get_row( $query ) );

				if ( $ret_type == 'var' )
					return apply_filters( 'wpj_shopping_' . $order_status . '_orders_var_filter', $wpdb->get_var( $query ) );

				if ( $ret_type == 'count' )
					return apply_filters( 'wpj_shopping_' . $order_status . '_orders_count_filter', count( $wpdb->get_results( $query ) ) );
			}
		}

		return 0;

	}
}


// UPDATE

if ( ! function_exists( 'wpj_update_user_earned_amount' ) ) {
	function wpj_update_user_earned_amount( $uid = '', $order_price = 0, $force_recheck = false ) {
		$user_total_earnings = get_user_meta( $uid, 'user_total_earnings', true );

		if ( $user_total_earnings && $order_price && ! $force_recheck ) {
			update_user_meta( $uid, 'user_total_earnings', $user_total_earnings + $order_price );

		} else {
			global $wpdb;

			$query = "
				SELECT SUM(mc_gross) AS total_earnings
				FROM {$wpdb->prefix}job_orders orders, {$wpdb->prefix}posts posts
				WHERE posts.post_author        = {$uid}
					AND posts.ID               = orders.pid
					AND orders.done_seller     = '1'
					AND orders.done_buyer      = '1'
					AND orders.completed       = '1'
					AND orders.closed          = '0'
					AND ( orders.clearing_period = '1' || orders.clearing_period = '2' )
			";

			$total_earnings = $wpdb->get_var( $query );

			if ( $total_earnings ) {
				update_user_meta( $uid, 'user_total_earnings', $total_earnings );
			}
		}
	}
}

if ( ! function_exists( 'wpj_update_completed_orders_amount' ) ) {
	function wpj_update_completed_orders_amount( $uid = '', $order_price = 0, $force_recheck = false ) {
		$user_total_spendings = get_user_meta( $uid, 'user_total_spendings', true );

		if ( $user_total_spendings && $order_price && ! $force_recheck ) {
			update_user_meta( $uid, 'user_total_spendings', $user_total_spendings + $order_price );

		} else {
			global $wpdb;

			$query = "
				SELECT SUM(mc_gross) AS total_spendings
				FROM {$wpdb->prefix}job_orders orders
				WHERE orders.uid = {$uid}
					AND orders.completed   = '1'
					AND orders.done_buyer  = '1'
					AND orders.done_seller = '1'
					AND orders.closed      = '0'
			";

			$total_spendings = $wpdb->get_var( $query );

			if ( $total_spendings ) {
				update_user_meta( $uid, 'user_total_spendings', $total_spendings );
			}
		}
	}
}