<?php
class WPJ_Summary extends \stdClass {
	public $_report_type, $_wpdb;

	function __construct(){
		global $wpdb;

		$this->_wpdb   = $wpdb;
		$this->_prefix = $wpdb->prefix;

		add_action( 'wp_ajax_job_sales_report', [ $this, "job_sales_report" ] );
		add_action( 'wp_ajax_nopriv_job_sales_report', [ $this, "job_sales_report" ] );

		add_action( 'wp_ajax_topup_sales_report', [ $this, "topup_sales_report" ] );
		add_action( 'wp_ajax_nopriv_topup_sales_report', [ $this, "topup_sales_report" ] );

		add_action( 'wp_ajax_featured_sales_report', [ $this, "featured_sales_report" ] );
		add_action( 'wp_ajax_nopriv_featured_sales_report', [ $this, "featured_sales_report" ] );

		add_action( 'wp_ajax_refund_sales_report', [ $this, "refund_sales_report" ] );
		add_action( 'wp_ajax_nopriv_refund_sales_report', [ $this, "refund_sales_report" ] );

		add_action( 'wp_ajax_withdrawal_sales_report', [ $this, "withdrawal_sales_report" ] );
		add_action( 'wp_ajax_nopriv_withdrawal_sales_report', [ $this, "withdrawal_sales_report" ] );

		add_action( 'wp_ajax_custom_extra_sales_report', [ $this, "custom_extra_sales_report" ] );
		add_action( 'wp_ajax_nopriv_custom_extra_sales_report', [ $this, "custom_extra_sales_report" ] );

		add_action( 'wp_ajax_tips_sales_report', [ $this, "tips_sales_report" ] );
		add_action( 'wp_ajax_nopriv_tips_sales_report', [ $this, "tips_sales_report" ] );

		add_action( 'wp_ajax_subscription_sales_report', [ $this, "subscription_sales_report" ] );
		add_action( 'wp_ajax_nopriv_subscription_sales_report', [ $this, "subscription_sales_report" ] );

		add_action( 'wp_ajax_badge_sales_report', [ $this, "badge_sales_report" ] );
		add_action( 'wp_ajax_nopriv_badge_sales_report', [ $this, "badge_sales_report" ] );

		add_action( 'wp_ajax_summary_sales_report', [ $this, "summary_sales_report" ] );
		add_action( 'wp_ajax_nopriv_summary_sales_report', [ $this, "summary_sales_report" ] );

		add_action( 'init', [ $this, 'get_date_and_user' ] );
	}

	public static function init() {
		$class = __CLASS__; new $class;
	}

	function summary_sales_report( $condition = [] ){

		$job_array = [
			'columns' => 'sum(mc_gross+processing_fees+tax_amount) AS total,
						sum(tax_amount) as total_tax_job',
			'where' => 'payment_status = "completed" AND date_made >= ' . $this->from_date . ' AND date_made <= '. $this->from_to . $this->job_where_array,
			'table' => $this->_prefix.'job_orders'
		];
		$topup_array = [
			'columns' => 'sum(package_amount) as total_package_amount,
						sum(tax) as total_tax_topup',
			'table' => $this->_prefix.'job_topup_orders',
			'where' => 'payment_status = "completed" AND added_on >= ' . $this->from_date . ' AND added_on <= '. $this->from_to . $this->topup_where_array,
		];
		$featured_array = [
			'columns' => 'sum(payable_amount) as featured_amount,
						sum(tax) as total_tax_featured',
			'table' => $this->_prefix.'job_featured_orders',
			'where' => 'payment_status = "completed" AND added_on >= ' . $this->from_date . ' AND added_on <= '. $this->from_to . $this->featured_where_array,
		];
		$custom_extra_array = [
			'columns' => 'sum(payable_amount) as custom_extra_amount,
						sum(tax) as total_tax_custom_extra',
			'table' => $this->_prefix.'job_custom_extra_orders',
			'where' => 'payment_status = "completed" AND added_on >= ' . $this->from_date . ' AND added_on <= '. $this->from_to . $this->custom_extra_where_array,
		];
		$tips_array = [
			'columns' => 'sum(tips_amount) as tips_amount,
						sum(tax) as total_tax_tips',
			'table' => $this->_prefix.'job_tips_orders',
			'where' => 'payment_status = "completed" AND added_on >= ' . $this->from_date . ' AND added_on <= '. $this->from_to . $this->tips_where_array,
		];
		$subscription_array = [
			'columns' => 'sum(amount) as subscription_amount',
			'table' => $this->_prefix.'job_subscription_orders',
			'where' => 'LOWER(payment_status) = LOWER("completed") AND addon_date >= ' . $this->from_date . ' AND addon_date <= '. $this->from_to . $this->subscription_where_array,
		];
		$badge_array = [
			'columns' => 'sum(price) as badge_amount',
			'table' => $this->_prefix.'job_badge_orders',
			'where' => 'LOWER(payment_status) = LOWER("completed") AND date_made >= ' . $this->from_date . ' AND date_made <= '. $this->from_to . $this->badge_where_array,
		];
		$refund_array = [
			'columns' => 'sum(jpt.amount) as refund_amount',
			'table' => $this->_prefix.'job_subscription_orders jo, ' . $this->_prefix.'job_payment_transactions jpt',
			'where' => 'jo.id = jpt.oid AND jpt.rid = 7 AND jpt.datemade >= ' . $this->from_date . ' AND jpt.datemade <= '. $this->from_to . $this->job_where_array,
		];

		$job_all_orders = [
			'table' => $this->_prefix.'job_orders',
			'where' => 'payment_status = "completed" AND date_made >= ' . $this->from_date . ' AND date_made <= '. $this->from_to . $this->job_where_array,
		];
		$topup_all_orders = [
			'table' => $this->_prefix.'job_topup_orders',
			'where' => 'payment_status = "completed" AND added_on >= ' . $this->from_date . ' AND added_on <= '. $this->from_to . $this->topup_where_array,
		];
		$featured_all_orders = [
			'table' => $this->_prefix.'job_featured_orders fo, ' . $this->_prefix.'job_payment_received pr',
			'where' => 'fo.payment_status="completed" AND fo.added_on >= ' . $this->from_date . ' AND fo.added_on <= '. $this->from_to . $this->featured_where_array . ' AND fo.id=pr.payment_type_id AND payment_type="feature"'
		];
		$custom_extra_all_orders = [
			'table' => $this->_prefix.'job_custom_extra_orders ce, ' . $this->_prefix.'job_payment_received pr',
			'where' => 'ce.payment_status="completed" AND ce.added_on >= ' . $this->from_date . ' AND ce.added_on <= '. $this->from_to . $this->custom_extra_where_array . ' AND ce.id=pr.payment_type_id AND payment_type="custom_extra"'
		];
		$tips_all_orders = [
			'table' => $this->_prefix.'job_tips_orders tp, ' . $this->_prefix.'job_payment_received pr',
			'where' => 'tp.payment_status="completed" AND tp.added_on >= ' . $this->from_date . ' AND tp.added_on <= '. $this->from_to . $this->tips_where_array . ' AND tp.id=pr.payment_type_id AND payment_type="tips"'
		];

		$getQuery = new WPJ_Query();

		$job          = $getQuery->get_wpj_query( $job_array );
		$topup        = $getQuery->get_wpj_query( $topup_array );
		$featured     = $getQuery->get_wpj_query( $featured_array );
		$custom_extra = $getQuery->get_wpj_query( $custom_extra_array );
		$tips         = $getQuery->get_wpj_query( $tips_array );
		$subscription = $getQuery->get_wpj_query( $subscription_array );
		$badge        = $getQuery->get_wpj_query( $badge_array );
		$refund       = $getQuery->get_wpj_query( $refund_array );

		// Job Purchase
		$job_site_fee  = 0;
		$job_processing_fees = 0;
		$job_orders = $getQuery->get_wpj_query( $job_all_orders );
		foreach ($job_orders as $row) {
			$site_fees = wpj_get_site_fee_by_amount( $row->mc_gross, $row->id );
			$job_site_fee += $site_fees;
			$job_processing_fees += $row->processing_fees;
		}

		// Topup
		$topup_orders = $getQuery->get_wpj_query( $topup_all_orders );
		$total_cost  = 0;
		$total_credt = 0;
		foreach ($topup_orders as $row) {
			if($row->currency != wpj_get_site_default_currency()){
				$currency_from = $row->currency;
				$currency_to = wpj_get_site_default_currency();

				$cost_input = $row->package_cost_without_tax;
				$cost = wpj_get_exchanged_value($cost_input, $currency_from, $currency_to);

				$credit_input = $row->package_credit_without_tax;
				$credit = wpj_get_exchanged_value($credit_input, $currency_from, $currency_to);
			}else{
				$cost = ($row->package_cost_without_tax != '') ? $row->package_cost_without_tax : '';
				$credit = ($row->package_credit_without_tax != '') ? $row->package_credit_without_tax : '';
			}

			if($credit == ''){
				$package = $row->package_id;

				if($this->user_to_search != ''){ $user_filter = ' AND user_id = '.$this->user_to_search; } else { $user_filter = ''; }
				$date_to_search = ' AND added_on >= ' . $this->from_date . ' AND added_on <= '. $this->from_to;

				$topup_packages = wpj_get_option( 'topup-packages-settings' );
				$credit = isset( $topup_packages['topup_cost'][0] ) ? $topup_packages['topup_cost'][0] : 0;
			}
			$total_cost += $cost;
			$total_credt += $credit;
		}

		// Featured
		$featured_processing_fees = 0;
		$featured_tax             = 0;
		$featured_paid_amount     = 0;
		$featured_orders = $getQuery->get_wpj_query( $featured_all_orders );
		foreach ( $featured_orders as $row ) {
			$featured_paid_amount      = wpj_get_exchanged_value( $row->payable_amount, $row->final_amount_currency, wpj_get_site_default_currency() );
			$featured_processing_fees += $row->fees + $row->featured_amount;
			$featured_tax             += $row->tax;
		}

		// Custom Extra
		$ce_site_fee        = 0;
		$ce_processing_fees = 0;
		$ce_paid_amount     = 0;
		$ce_tax             = 0;
		$ce_orders = $getQuery->get_wpj_query( $custom_extra_all_orders );
		foreach ( $ce_orders as $row ) {
			$ce_paid_amount     += $row->amount;
			$ce_site_fee        += wpj_get_site_fee_by_amount( $row->custom_extra_amount, $row->order_id );
			$ce_tax             += $row->tax;
			$ce_processing_fees += $row->fees;
		}

		// Tips
		$tp_site_fee        = 0;
		$tp_processing_fees = 0;
		$tp_paid_amount     = 0;
		$tp_tax             = 0;
		$tp_orders = $getQuery->get_wpj_query( $tips_all_orders );
		foreach ( $tp_orders as $row ) {
			$tp_paid_amount     += $row->amount;
			$tp_site_fee        += wpj_get_site_fee_by_amount( $row->amount, $row->order_id );
			$tp_tax             += $row->tax;
			$tp_processing_fees += $row->fees;
		}

		// Total Sale //
		$this->total_sales_job          = ($job)      ? $job[0]->total : 0;
		$this->total_sales_topup        = ($topup)    ? $topup[0]->total_package_amount : 0;
		$this->total_sales_featured     = $featured_paid_amount;
		$this->total_sales_custom_extra = $ce_paid_amount;
		$this->total_sales_tips         = $tp_paid_amount;
		$this->total_sales_subscription = ($subscription) ? $subscription[0]->subscription_amount : 0;
		$this->total_sales_badge        = ($badge) ? $badge[0]->badge_amount : 0;
		$this->total_sales_refund       = ($refund) ? $refund[0]->refund_amount : 0;

		$this->total_sales              = $this->total_sales_job + $this->total_sales_topup + $this->total_sales_featured + $this->total_sales_custom_extra + $this->total_sales_tips + $this->total_sales_subscription + $this->total_sales_badge;
		$this->total_sales_after_refund = $this->total_sales - $this->total_sales_refund;

		// Total Fees/Profit //
		$this->total_topup_fees        = $total_cost - $total_credt;
		$this->total_job_fees          = $job_site_fee + $job_processing_fees;
		$this->total_featured_fees     = $featured_processing_fees;
		$this->total_custom_extra_fees = $ce_site_fee + $ce_processing_fees;
		$this->total_tips_fees         = $tp_site_fee + $tp_processing_fees;
		$this->total_subscription_fees = $this->total_sales_subscription;
		$this->total_badge_fees        = $this->total_sales_badge;

		$this->total_fees              = $this->total_topup_fees + $this->total_job_fees + $this->total_featured_fees + $this->total_custom_extra_fees + $this->total_tips_fees + $this->total_subscription_fees + $this->total_badge_fees;

		// Total Taxes //
		$this->total_taxes_job          = ($job)      ? $job[0]->total_tax_job : 0;
		$this->total_taxes_topup        = ($topup)    ? $topup[0]->total_tax_topup : 0;
		$this->total_taxes_featured     = $featured_tax;
		$this->total_taxes_custom_extra = $ce_tax;
		$this->total_taxes_tips         = $tp_tax;
		$this->total_taxes_subscription = 0;
		$this->total_taxes_badge        = 0;

		$this->total_taxes              = $this->total_taxes_job + $this->total_taxes_topup + $this->total_taxes_featured + $this->total_taxes_custom_extra + $this->total_taxes_tips + $this->total_taxes_subscription + $this->total_taxes_badge;

		// TABLE FORMAT //

		$tableInfo = '';
		$tableInfo .= '<table id="summary-table" class="widefat post fixed" cellspacing="0">';
			$tableInfo .= '<thead>';
				$tableInfo .= '<tr>';
					$tableInfo .= '<th width="20%"></th>';
					$tableInfo .= '<th width="20%">'.__('Job Purchase','wpjobster').'</th>';
					$tableInfo .= '<th width="20%">'.__('Topup','wpjobster').'</th>';
					$tableInfo .= '<th width="20%">'.__('Featured','wpjobster').'</th>';
					$tableInfo .= '<th width="20%">'.__('Custom Extra','wpjobster').'</th>';
					$tableInfo .= '<th width="20%">'.__('Tips','wpjobster').'</th>';
					$tableInfo .= '<th width="20%">'.__('Subscription','wpjobster').'</th>';
					$tableInfo .= '<th width="20%">'.__('Badge','wpjobster').'</th>';
					$tableInfo .= '<th width="20%"><b>'.__('Total','wpjobster').'</b></th>';
					$tableInfo .= '<th width="20%">'.__('Refund','wpjobster').'</th>';
					$tableInfo .= '<th width="20%">'.__('Total after refund','wpjobster').'</th>';
				$tableInfo .= '</tr>';
			$tableInfo .= '</thead>';

			$tableInfo .= '<tbody>';
				$tableInfo .= '<tr>';
					$tableInfo .= '<td>'.__('Total Sale','wpjobster').'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($this->total_sales_job)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($this->total_sales_topup)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($this->total_sales_featured)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($this->total_sales_custom_extra)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($this->total_sales_tips)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($this->total_sales_subscription)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($this->total_sales_badge)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($this->total_sales)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($this->total_sales_refund)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($this->total_sales_after_refund)).'</td>';
				$tableInfo .= '</tr>';

				$tableInfo .= '<tr>';
					$tableInfo .= '<td>'.__('Total Fees/Profit','wpjobster').'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($this->total_job_fees)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($this->total_topup_fees)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($this->total_featured_fees)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($this->total_custom_extra_fees)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($this->total_tips_fees)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($this->total_subscription_fees)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($this->total_badge_fees)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($this->total_fees)).'</td>';
					$tableInfo .= '<td>-</td>';
					$tableInfo .= '<td>-</td>';
				$tableInfo .= '</tr>';

				$tableInfo .= '<tr>';
					$tableInfo .= '<td>'.__('Total Taxes','wpjobster').'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($this->total_taxes_job)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($this->total_taxes_topup)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($this->total_taxes_featured)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($this->total_taxes_custom_extra)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($this->total_taxes_tips)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($this->total_taxes_subscription)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($this->total_taxes_badge)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($this->total_taxes)).'</td>';
					$tableInfo .= '<td>-</td>';
					$tableInfo .= '<td>-</td>';
				$tableInfo .= '</tr>';

				$tableInfo .= '<tr>';
					$tableInfo .= '<td align="center" colspan="11">';
						$tableInfo .= "<b>" . strtoupper( __( "The total credits held by the website users: ", "wpjobster" ) ) . $this->total_credits_owed_to_user() . "</b>";
					$tableInfo .= '</td>';
				$tableInfo .= '</tr>';
			$tableInfo .= '</tbody>';
		$tableInfo .= '</table>';

		$tableInfo .= '<br />';

		$tableInfo .= '<table class="widefat post fixed" cellspacing="0">';
			$tableInfo .= '<tr>';
				$tableInfo .= '<td width="200">'.__("Total number of jobs","wpjobster").'</td>';
				$tableInfo .= '<td>'.wpj_get_site_jobs_count().'</td>';
			$tableInfo .= '</tr>';

			$tableInfo .= '<tr>';
				$tableInfo .= '<td>'.__("Open jobs","wpjobster").'</td>';
				$tableInfo .= '<td>'.wpj_get_site_jobs_count( 'open' ).'</td>';
			$tableInfo .= '</tr>';

			$tableInfo .= '<tr>';
				$tableInfo .= '<td>'.__("Closed","wpjobster").'</td>';
				$tableInfo .= '<td>'.wpj_get_site_jobs_count( 'closed' ).'</td>';
			$tableInfo .= '</tr>';

			$tableInfo .= '<tr>';
				$tableInfo .= '<td colspan="2" align="center">';
					$result = count_users();
					$tableInfo .= '<b>'.strtoupper ( __('There are ','wpjobster').' '.$result['total_users'].' '.__(' total users','wpjobster') ).'</b>';
						foreach($result['avail_roles'] as $role => $count)
							$tableInfo .= '<b>'.strtoupper( ', '.$count.' '.__(' are ','wpjobster').' '.$role.'s' ).'</b>';
					$tableInfo .= '.';
				$tableInfo .= '</td>';
			$tableInfo .= '</tr>';
		$tableInfo .= '</table>';

		// END TABLE FORMAT //

		// GRAPH FORMAT //

		$graphInfo = [];
		$graphInfo['cols'] = [
			['label' => __('Date', 'wpjobster'),         'type' => 'string'],
			['label' => __('Job Purchase', 'wpjobster'), 'type' => 'number'],
			['label' => __('Topup', 'wpjobster'),        'type' => 'number'],
			['label' => __('Featured', 'wpjobster'),     'type' => 'number'],
			['label' => __('Custom Extra', 'wpjobster'), 'type' => 'number'],
			['label' => __('Tips', 'wpjobster'),         'type' => 'number'],
			['label' => __('Subscription', 'wpjobster'), 'type' => 'number'],
			['label' => __('Badge', 'wpjobster'),        'type' => 'number'],
		];

		$rows = [];

		$temp = [];
		$temp[] = ['v' => (string) __('Total Sale','wpjobster') ];
		$temp[] = ['v' => (float) $this->total_sales_job];
		$temp[] = ['v' => (float) $this->total_sales_topup];
		$temp[] = ['v' => (float) $this->total_sales_featured];
		$temp[] = ['v' => (float) $this->total_sales_custom_extra];
		$temp[] = ['v' => (float) $this->total_sales_tips];
		$temp[] = ['v' => (float) $this->total_sales_subscription];
		$temp[] = ['v' => (float) $this->total_sales_badge];
		$rows[] = ['c' => $temp];

		$temp = [];
		$temp[] = ['v' => (string) __('Total Fees/Profit','wpjobster') ];
		$temp[] = ['v' => (float) $this->total_job_fees];
		$temp[] = ['v' => (float) $this->total_topup_fees];
		$temp[] = ['v' => (float) $this->total_featured_fees];
		$temp[] = ['v' => (float) $this->total_custom_extra_fees];
		$temp[] = ['v' => (float) $this->total_tips_fees];
		$temp[] = ['v' => (float) $this->total_subscription_fees];
		$temp[] = ['v' => (float) $this->total_badge_fees];
		$rows[] = ['c' => $temp];

		$temp = [];
		$temp[] = ['v' => (string) __('Total Taxes','wpjobster') ];
		$temp[] = ['v' => (float) $this->total_taxes_job];
		$temp[] = ['v' => (float) $this->total_taxes_topup];
		$temp[] = ['v' => (float) $this->total_taxes_featured];
		$temp[] = ['v' => (float) $this->total_taxes_custom_extra];
		$temp[] = ['v' => (float) $this->total_taxes_tips];
		$temp[] = ['v' => (float) $this->total_taxes_badge];
		$rows[] = ['c' => $temp];

		$graphInfo['rows'] = $rows;

		// END GRAPH FORMAT

		$jsonTable = json_encode( [ "tableInfo" => $tableInfo, "graphInfo" => $graphInfo, "userError" => $this->userError ] );
		echo $jsonTable;

		die();
	}

	function job_sales_report( $condition = [] ){

		$getQuery = new WPJ_Query();

		// TABLE FORMAT //

		$job_all_orders = [
			'table' => $this->_prefix.'job_orders',
			'order_by' => 'id DESC',
			'where' => 'payment_status="completed" AND date_made >= ' . $this->from_date . ' AND date_made <= '. $this->from_to . $this->job_where_array,
		];
		$job_orders = $getQuery->get_wpj_query( $job_all_orders );

		$tableInfo = '';

		$tableInfo .= '<table id="job-purchase-table" class="widefat post fixed" cellspacing="0">';
			$tableInfo .= '<thead>';
				$tableInfo .= '<tr>';
					$tableInfo .= '<th width="4%">'.__( "No", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="8%">'.__( "Payment Date", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="8%">'.__( "Delivered date", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="6%">'.__( "Transaction ID", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="4%">'.__( "Job ID", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="9%">'.__( "Job Title", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="8%">'.__( "Buyer", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="8%">'.__( "Seller", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="6%">'.__( "Payment Gateway", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="6.5%">'.__( "Transaction Amount", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="6.5%">'.__( "Shipping", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="6.5%">'.__( "Amount cleared (Seller Amount)", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="6.5%">'.__( "Buyer Fees (Processing Fee)", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="6.5%">'.__( "Seller Commision (Site Fee)", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="6.5%">'.__( "Tax", "wpjobster" ).'</th>';
				$tableInfo .= '</tr>';
			$tableInfo .= '</thead>';

			$tableInfo .= '<tbody>';
				$i = 1;

				$total_transaction_amount = 0;
				$total_amount_cleared     = 0;
				$total_shipping           = 0;
				$total_buyer_fees         = 0;
				$total_seller_commision   = 0;
				$total_tax                = 0;

				foreach ( $job_orders as $row ) {
					$buyer_user = $row->uid;
					$buyer_user = get_user_by( 'id', $buyer_user );
					$buyer_user_name = ( $buyer_user ) ? wpj_get_user_display_type( $buyer_user->ID ) : '';

					$seller_user = wpj_get_seller_id( $row );
					$seller_user = get_user_by( 'id', $seller_user );
					$seller_user_name = ($seller_user) ? wpj_get_user_display_type( $seller_user->ID ) : '';

					$get_job_id = $getQuery->get_wpj_query( [ 'columns' => 'ID', 'table' => $this->_prefix.'posts', 'where' => 'post_title = "' . $row->job_title .'"' ] );
					$job_id = (isset($get_job_id[0]->ID)) ? $get_job_id[0]->ID : '';

					$trans_amount = explode( '|', $row->payedamount );
					$total_amount = apply_filters( 'wpj_admin_order_list_amount_filter', $trans_amount[1] + wpj_get_custom_extras_amount( $row, 'amount' ), $row->id, 'job_purchase' );

					if ( ! empty( $trans_amount[0] ) && $trans_amount[0] != wpj_get_site_default_currency() ) {
						$currency_from = $trans_amount[0];
						$currency_to   = wpj_get_site_default_currency();

						$transaction_amount = wpj_get_exchanged_value( $total_amount, $currency_from, $currency_to );

					} else {
						$transaction_amount = $total_amount;

					}

					if ( $transaction_amount == '0' ) {
						$shipping       = 0;
						$amount_cleared = 0;
						$buyer_fees     = 0;
						$site_fee       = 0;
						$tax            = 0;
					} else {
						$shipping       = floatval( $row->shipping );
						$amount_cleared = $row->clearing_period == 1 ? floatval( apply_filters( 'wpj_total_net_sales_filter', wpj_get_order_total_price( $row )['seller_commission'], $row, 'net' ) ) : 0;
						$buyer_fees     = floatval( $row->processing_fees );
						$site_fee       = floatval( $row->admin_fee );
						$tax            = floatval( $row->tax_amount );
					}

					if ( $row->date_made && $row->date_made != 0 ) $payment_date = wpj_date( wpj_get_option( 'date_format' ) . ' ' . wpj_get_option( 'time_format' ), $row->date_made );
					else $payment_date = '-';

					if ( $row->date_finished && $row->date_finished != 0 ) $delivered_date = wpj_date( wpj_get_option( 'date_format' ) . ' ' . wpj_get_option( 'time_format' ), $row->date_finished );
					else $delivered_date = '-';

					$tableInfo .= '<tr>';
						$tableInfo .= '<td>'.$i.'</td>';
						$tableInfo .= '<td>'.$payment_date.'</td>';
						$tableInfo .= '<td>'.$delivered_date.'</td>';
						$tableInfo .= '<td>'.$row->id.'</td>';
						$tableInfo .= '<td>'.$job_id.'</td>';
						$tableInfo .= '<td>'.$row->job_title.'</td>';
						$tableInfo .= '<td>'.$buyer_user_name.'</td>';
						$tableInfo .= '<td>'.$seller_user_name.'</td>';
						$tableInfo .= '<td>'.$row->payment_gateway.'</td>';
						$tableInfo .= '<td>'.wpj_show_price_classic( $transaction_amount ).'</td>';
						$tableInfo .= '<td>'.wpj_show_price_classic( $shipping ).'</td>';
						$tableInfo .= '<td>'.wpj_show_price_classic( $amount_cleared ).'</td>';
						$tableInfo .= '<td>'.wpj_show_price_classic( $buyer_fees ).'</td>';
						$tableInfo .= '<td>'.wpj_show_price_classic( $site_fee ).'</td>';
						$tableInfo .= '<td>'.wpj_show_price_classic( $tax ).'</td>';
					$tableInfo .= '</tr>';

					$i++;

					$total_transaction_amount += $transaction_amount;
					$total_amount_cleared     += $amount_cleared;
					$total_shipping           += $shipping;
					$total_buyer_fees         += $buyer_fees;
					$total_seller_commision   += $site_fee;
					$total_tax                += $tax;
				}

				$tableInfo .= '<tr>';
					$tableInfo .= '<td>'.__( "Total", "wpjobster" ).'</td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($total_transaction_amount)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($total_shipping)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($total_amount_cleared)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($total_buyer_fees)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($total_seller_commision)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($total_tax)).'</td>';
				$tableInfo .= '</tr>';
			$tableInfo .= '</tbody>';
		$tableInfo .= '</table>';

		// END TABLE FORMAT //

		// GRAPH FORMAT //

		$graphInfo = [];
		$graphInfo['cols'] = [
			['label' => __('Date','wpjobster'), 'type' => 'string'],
			['label' => __('Transaction Amount','wpjobster'), 'type' => 'number'],
			['label' => __('Shipping','wpjobster'), 'type' => 'number'],
			['label' => __('Amount cleared','wpjobster'), 'type' => 'number'],
			['label' => __('Buyer Fees','wpjobster'), 'type' => 'number'],
			['label' => __('Seller Commision','wpjobster'), 'type' => 'number'],
			['label' => __('Tax','wpjobster'), 'type' => 'number'],
		];

		$graphInfo['rows'] = $this->graph_range_report('job_orders','date_made','job_purchase',$this->job_where_array);

		// END GRAPH FORMAT

		$jsonTable = json_encode(["tableInfo" => $tableInfo, "graphInfo" => $graphInfo, "userError" => $this->userError]);
		echo $jsonTable;

		die();

	}

	function topup_sales_report( $condition = [] ){

		$getQuery = new WPJ_Query();

		// TABLE FORMAT //

		$topup_all_orders = [
			'table' => $this->_prefix.'job_topup_orders',
			'order_by' => 'id DESC',
			'where' => 'payment_status="completed" AND added_on >= ' . $this->from_date . ' AND added_on <= '. $this->from_to . $this->topup_where_array,
		];
		$topup_orders = $getQuery->get_wpj_query( $topup_all_orders );

		$tableInfo = '';

		$tableInfo .= '<table id="topup-table" class="widefat post fixed" cellspacing="0">';
			$tableInfo .= '<thead>';
				$tableInfo .= '<tr>';
					$tableInfo .= '<th>'.__( "No", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Payment Date", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Transaction ID", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Username", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Payment Gateway", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Package ID", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Transaction Amount", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "User Credits", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Earnings", "wpjobster" ).'</th>';
				$tableInfo .= '</tr>';
			$tableInfo .= '</thead>';

			$tableInfo .= '<tbody>';
				$i=1;

				$total_cost     = 0;
				$total_credit   = 0;
				$total_earnings = 0;

				foreach ($topup_orders as $row) {
					$user = get_user_by('id', $row->user_id);
					$user_name = ($user) ? wpj_get_user_display_type( $user->ID ) : '';

					if($row->added_on && $row->added_on != 0) $payment_date = wpj_date( wpj_get_option( 'date_format' ) . ' ' . wpj_get_option( 'time_format' ), $row->added_on );
					else $payment_date = '-';

					$package_cost_without_tax = wpj_get_exchanged_value( $row->package_cost_without_tax, $row->currency, wpj_get_site_default_currency() );
					$package_credit_without_tax = wpj_get_exchanged_value( $row->package_credit_without_tax, $row->currency, wpj_get_site_default_currency() );
					$earnings = $package_cost_without_tax - $package_credit_without_tax;

					$tableInfo .= '<tr>';
						$tableInfo .= '<td>'.$i.'</td>';
						$tableInfo .= '<td>'.$payment_date.'</td>';
						$tableInfo .= '<td>'.$row->id.'</td>';
						$tableInfo .= '<td>'.$user_name.'</td>';
						$tableInfo .= '<td>'.$row->payment_gateway_name.'</td>';
						$tableInfo .= '<td>'.$row->package_id.'</td>';
						$tableInfo .= '<td>'.wpj_show_price_classic(floatval($package_cost_without_tax)).'</td>';
						$tableInfo .= '<td>'.wpj_show_price_classic(floatval($package_credit_without_tax)).'</td>';
						$tableInfo .= '<td>'.wpj_show_price_classic($earnings).'</td>';
					$tableInfo .= '</tr>';

					$total_cost += $row->package_cost_without_tax;
					$total_credit += $row->package_credit_without_tax;
					$total_earnings += $earnings;

					$i++;
				}

				$tableInfo .= '<tr>';
					$tableInfo .= '<td>'.__( "Total", "wpjobster" ).'</td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($total_cost)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($total_credit)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic($total_earnings).'</td>';
				$tableInfo .= '</tr>';
			$tableInfo .= '</tbody>';
		$tableInfo .= '</table>';

		// END TABLE FORMAT //

		// GRAPH FORMAT //

		$graphInfo = [];
		$graphInfo['cols'] = [
			['label' => __('Date','wpjobster'), 'type' => 'string'],
			['label' => __('Transaction Amount','wpjobster'), 'type' => 'number'],
			['label' => __('User Credits','wpjobster'), 'type' => 'number'],
			['label' => __('Earnings','wpjobster'), 'type' => 'number'],
		];

		$graphInfo['rows'] = $this->graph_range_report('job_topup_orders','added_on','topup',$this->topup_where_array);

		// END GRAPH FORMAT

		$jsonTable = json_encode(["tableInfo" => $tableInfo, "graphInfo" => $graphInfo, "userError" => $this->userError]);
		echo $jsonTable;

		die();
	}

	function featured_sales_report( $condition = [] ){

		$getQuery = new WPJ_Query();

		// TABLE FORMAT //

		$featured_all_orders = [
			'table' => $this->_prefix.'job_featured_orders fo, ' . $this->_prefix.'job_payment_received pr',
			'order_by' => 'fo.id DESC',
			'where' => 'fo.payment_status="completed" AND fo.added_on >= ' . $this->from_date . ' AND fo.added_on <= '. $this->from_to . $this->featured_where_array . ' AND fo.id=pr.payment_type_id AND payment_type="feature"',
		];
		$featured_orders = $getQuery->get_wpj_query( $featured_all_orders );

		$tableInfo = '';

		$tableInfo .= '<table id="featured-table" class="widefat post fixed" cellspacing="0">';
			$tableInfo .= '<thead>';
				$tableInfo .= '<tr>';
					$tableInfo .= '<th>'.__( "No", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Payment Date", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Transaction ID", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Job ID", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Job Title", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Username", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Payment Gateway", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Featured duration Homepage", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Featured duration Category", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Featured duration Subcategory", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Transaction Amount", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Feature Amount", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Processing Fee", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Tax", "wpjobster" ).'</th>';
				$tableInfo .= '</tr>';
			$tableInfo .= '</thead>';
			$tableInfo .= '<tbody>';
				$i=1;

				$total_amount    = 0;
				$featured_amount = 0;
				$total_fees      = 0;
				$total_tax       = 0;

				foreach ($featured_orders as $row) {
					$user         = get_user_by('id', $row->user_id);
					$user_name    = ($user) ? wpj_get_user_display_type( $user->ID ) : '';

					$h_start_date = wpj_get_featured_start_date('homepage', $row->job_id);
					$h_end_date   = wpj_get_featured_end_date($h_start_date);

					$c_start_date = wpj_get_featured_start_date('category', $row->job_id);
					$c_end_date   = wpj_get_featured_end_date($c_start_date);

					$s_start_date = wpj_get_featured_start_date('subcategory', $row->job_id);
					$s_end_date   = wpj_get_featured_end_date($s_start_date);

					$temp = [];
					$temp[] = [ 'v' => (int) $row->id ];
					$temp[] = [ 'v' => (float) wpj_get_exchanged_value( $row->featured_amount, $row->final_amount_currency, wpj_get_site_default_currency() ) ];
					$rows[] = [ 'c' => $temp ];

					if($row->paid_on && $row->paid_on != 0) $payment_date = wpj_date( wpj_get_option( 'date_format' ) . ' ' . wpj_get_option( 'time_format' ), $row->paid_on );
					else $payment_date = '-';

					if($h_start_date && $h_start_date != 0) $h_start_date = wpj_date( wpj_get_option( 'date_format' ), $h_start_date );
					else $h_start_date = '-';

					if($c_start_date && $c_start_date != 0) $c_start_date = wpj_date( wpj_get_option( 'date_format' ), $c_start_date );
					else $c_start_date = '-';

					if($s_start_date && $s_start_date != 0) $s_start_date = wpj_date( wpj_get_option( 'date_format' ), $s_start_date );
					else $s_start_date = '-';

					if($h_end_date && $h_end_date != 0) $h_end_date = wpj_date( wpj_get_option( 'date_format' ), $h_end_date );
					else $h_end_date = '-';

					if($c_end_date && $c_end_date != 0) $c_end_date = wpj_date( wpj_get_option( 'date_format' ), $c_end_date );
					else $c_end_date = '-';

					if($s_end_date && $s_end_date != 0) $s_end_date = wpj_date( wpj_get_option( 'date_format' ), $s_end_date );
					else $s_end_date = '-';

					$tableInfo .= '<tr>';
						$tableInfo .= '<td>'.$i.'</td>';
						$tableInfo .= '<td>'.$payment_date.'</td>';
						$tableInfo .= '<td>'.$row->id.'</td>';
						$tableInfo .= '<td>'.$row->job_id.'</td>';
						$tableInfo .= '<td>'.get_the_title($row->job_id).'</td>';
						$tableInfo .= '<td>'.$user_name.'</td>';
						$tableInfo .= '<td>'.$row->payment_gateway_name.'</td>';
						$tableInfo .= '<td>'.$h_start_date .' - '. $h_end_date.'</td>';
						$tableInfo .= '<td>'.$c_start_date .' - '. $c_end_date.'</td>';
						$tableInfo .= '<td>'.$s_start_date .' - '. $s_end_date.'</td>';
						$tableInfo .= '<td>'.wpj_show_price_classic( wpj_get_exchanged_value( $row->payable_amount, $row->final_amount_currency, wpj_get_site_default_currency() ) ).'</td>';
						$tableInfo .= '<td>'.wpj_show_price_classic( $row->featured_amount ).'</td>';
						$tableInfo .= '<td>'.wpj_show_price_classic( $row->fees ).'</td>';
						$tableInfo .= '<td>'.wpj_show_price_classic( $row->tax ).'</td>';
					$tableInfo .= '</tr>';

					$total_amount    += wpj_get_exchanged_value( $row->payable_amount, $row->final_amount_currency, wpj_get_site_default_currency() );
					$featured_amount += $row->featured_amount;
					$total_fees      += $row->fees;
					$total_tax       += $row->tax;

					$i++;
				}

				$tableInfo .= '<tr>';
					$tableInfo .= '<td>'.__( "Total", "wpjobster" ).'</td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($total_amount)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($featured_amount)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic($total_fees).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic($total_tax).'</td>';

				$tableInfo .= '</tr>';
			$tableInfo .= '</tbody>';
		$tableInfo .= '</table>';

		// END TABLE FORMAT //

		// GRAPH FORMAT //

		$graphInfo = [];
		$graphInfo['cols'] = [
			['label' => __('Date','wpjobster'), 'type' => 'string'],
			['label' => __('Transaction Amount','wpjobster'), 'type' => 'number'],
			['label' => __('Featured Amount','wpjobster'), 'type' => 'number'],
			['label' => __('Processing Fee','wpjobster'), 'type' => 'number'],
			['label' => __('Tax','wpjobster'), 'type' => 'number'],
		];

		$graphInfo['rows'] = $this->graph_range_report('job_featured_orders','added_on','featured',$this->featured_where_array);

		// END GRAPH FORMAT

		$jsonTable = json_encode(["tableInfo" => $tableInfo, "graphInfo" => $graphInfo, "userError" => $this->userError]);
		echo $jsonTable;

		die();

	}
	function refund_sales_report( $condition = [] ){

		$getQuery = new WPJ_Query();

		// TABLE FORMAT //

		$job_all_orders = [
			'table' => $this->_prefix.'job_orders',
			'order_by' => 'id DESC',
			'where' => 'payment_status="completed" AND closed = 1 AND date_made >= ' . $this->from_date . ' AND date_made <= '. $this->from_to . $this->job_where_array,
		];
		$job_orders = $getQuery->get_wpj_query( $job_all_orders );
		$tableInfo = '';

		$tableInfo .= '<table id="refund-table" class="widefat post fixed" cellspacing="0">';

			$tableInfo .= '<thead>';
				$tableInfo .= '<tr>';
					$tableInfo .= '<th>'.__( "No", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Refund Date", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Transaction ID", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Job ID", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Job Title", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Refunded To User", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Payment Gateway", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Refund Amount", "wpjobster" ).'</th>';
				$tableInfo .= '</tr>';
			$tableInfo .= '</thead>';

			$tableInfo .= '<tbody>';
				$i=1;
				$total_refund_amount = 0;

				foreach ($job_orders as $row) {
					$user = get_user_by('id', $row->uid);
					$user_name = ($user) ? wpj_get_user_display_type( $user->ID ) : '';

					$get_job_id = $getQuery->get_wpj_query( [ 'columns' => 'ID', 'table' => $this->_prefix.'posts', 'where' => 'post_title = "' . $row->job_title .'"' ] );
					$job_id = (isset($get_job_id[0]->ID)) ? $get_job_id[0]->ID : '';

					$get_refund_amount = $getQuery->get_wpj_query( [ 'columns' => 'amount, datemade', 'table' => $this->_prefix.'job_payment_transactions', 'where' => 'oid = "' . $row->id .'" AND rid=7' ] );
					$refund_amount = (isset($get_refund_amount[0]->amount)) ? $get_refund_amount[0]->amount : '';
					$refund_date = (isset($get_refund_amount[0]->datemade)) ? $get_refund_amount[0]->datemade : '';
					$refund_date = $refund_date ? wpj_date( wpj_get_option( 'date_format' ) . ' ' . wpj_get_option( 'time_format' ), $refund_date ) : '-';

					$tableInfo .= '<tr>';
						$tableInfo .= '<td>'.$i.'</td>';
						$tableInfo .= '<td>'.$refund_date.'</td>';
						$tableInfo .= '<td>'.$row->id.'</td>';
						$tableInfo .= '<td>'.$job_id.'</td>';
						$tableInfo .= '<td>'.$row->job_title.'</td>';
						$tableInfo .= '<td>'.$user_name.'</td>';
						$tableInfo .= '<td>'.$row->payment_gateway.'</td>';
						$tableInfo .= '<td>'.wpj_show_price_classic(floatval($refund_amount)).'</td>';
					$tableInfo .= '</tr>';

					$i++;
					$total_refund_amount += $refund_amount;
				}

				$tableInfo .= '<tr>';
					$tableInfo .= '<td>'.__('Total','wpjobster').'</td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($total_refund_amount)).'</td>';
				$tableInfo .= '</tr>';

			$tableInfo .= '</tbody>';

		$tableInfo .= '</table>';

		// END TABLE FORMAT //

		// GRAPH FORMAT //

		$graphInfo = [];
		$graphInfo['cols'] = [
			['label' => __('Date','wpjobster'), 'type' => 'string'],
			['label' => __('Transaction Amount','wpjobster'), 'type' => 'number'],
		];

		$graphInfo['rows'] = $this->graph_range_report('job_orders','date_made','refund',$this->job_where_array);

		// END GRAPH FORMAT

		$jsonTable = json_encode(["tableInfo" => $tableInfo, "graphInfo" => $graphInfo, "userError" => $this->userError]);
		echo $jsonTable;

		die();
	}

	function withdrawal_sales_report( $condition = [] ){

		$getQuery = new WPJ_Query();

		// TABLE FORMAT //

		$withdrawal_all_orders = [
			'table' => $this->_prefix.'job_withdraw',
			'order_by' => 'id DESC',
			'where' => 'datemade >= ' . $this->from_date . ' AND datemade <= '. $this->from_to . $this->withdrawal_where_array,
		];
		$withdrawal_orders = $getQuery->get_wpj_query( $withdrawal_all_orders );

		$tableInfo = '';
		$tableInfo .= '<table id="withdrawal-table" class="widefat post fixed" cellspacing="0">';

			$tableInfo .= '<thead>';
				$tableInfo .= '<tr>';
					$tableInfo .= '<th>'.__( "No", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Withdrawal Date", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Transaction ID", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Username", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Gateway", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Approved On", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Status", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Withdrawal Amount", "wpjobster" ).'</th>';
				$tableInfo .= '</tr>';
			$tableInfo .= '</thead>';

			$tableInfo .= '<tbody>';
				$i=1;
				$total_withdrawal = 0;

				foreach ($withdrawal_orders as $row) {
					$user = get_user_by("id", $row->uid);
					$user_name = ($user) ? wpj_get_user_display_type( $user->ID ) : '';
					if($row->datedone == 0){
						$status = __("pending","wpjobster");
					}else if($row->rejected_on != 0){
						$status = __("rejected","wpjobster");
					}else if($row->rejected_on == 0 && $row->datedone != 0){
						$status = __("approved","wpjobster");
					}else{
						$status = __("N/A","wpjobster");
					}
					$datedone = ($row->datedone != 0) ? wpj_date( wpj_get_option( 'date_format' ) . ' ' . wpj_get_option( 'time_format' ), $row->datedone ) : "N/A";

					$temp = [];
					$temp[] = ['v' => (int) $row->id];
					$temp[] = ['v' => (float) $row->amount];
					$rows[] = ['c' => $temp];

					if($row->datemade && $row->datemade != 0) $withdrawal_date = wpj_date( wpj_get_option( 'date_format' ) . ' ' . wpj_get_option( 'time_format' ), $row->datemade );
					else $withdrawal_date = '-';

					$tableInfo .= '<tr>';
						$tableInfo .= '<td>'.$i.'</td>';
						$tableInfo .= '<td>'.$withdrawal_date.'</td>';
						$tableInfo .= '<td>'.$row->id.'</td>';
						$tableInfo .= '<td>'.$user_name.'</td>';
						$tableInfo .= '<td>'.$row->methods.'</td>';
						$tableInfo .= '<td>'.$datedone.'</td>';
						$tableInfo .= '<td>'.$status.'</td>';
						$tableInfo .= '<td>'.wpj_show_price_classic(floatval($row->amount)).'</td>';
					$tableInfo .= '</tr>';

					$i++;
					$total_withdrawal += $row->amount;
				}

				$tableInfo .= '<tr>';
					$tableInfo .= '<td>'.__('Total','wpjobster').'</td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($total_withdrawal)).'</td>';
				$tableInfo .= '</tr>';
			$tableInfo .= '</tbody>';

		$tableInfo .= '</table>';

		// END TABLE FORMAT //

		// GRAPH FORMAT //

		$graphInfo = [];
		$graphInfo['cols'] = [
			['label' => __('Date','wpjobster'), 'type' => 'string'],
			['label' => __('Amount','wpjobster'), 'type' => 'number'],
		];

		$graphInfo['rows'] = $this->graph_range_report('job_withdraw','datemade','withdrawal',$this->withdrawal_where_array);

		// END GRAPH FORMAT

		$jsonTable = json_encode(["tableInfo" => $tableInfo, "graphInfo" => $graphInfo, "userError" => $this->userError]);
		echo $jsonTable;

		die();
	}

	function custom_extra_sales_report( $condition = [] ){

		$getQuery = new WPJ_Query();

		// TABLE FORMAT //

		$custom_extra_all_orders = [
			'table' => $this->_prefix.'job_orders wjo,' . $this->_prefix.'job_custom_extra_orders wjceo',
			'order_by' => 'wjo.id DESC',
			'where' => 'wjo.id = wjceo.order_id AND wjceo.payment_status="completed" AND wjceo.paid_on >= ' . $this->from_date . ' AND wjceo.paid_on <= '. $this->from_to . $this->job_where_array,
		];
		$custom_extra_orders = $getQuery->get_wpj_query( $custom_extra_all_orders );

		$tableInfo = '';

		$tableInfo .= '<table id="custom-extra-table" class="widefat post fixed" cellspacing="0">';
			$tableInfo .= '<thead>';
				$tableInfo .= '<tr>';
					$tableInfo .= '<th width="5%">'.__( "No", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="8%">'.__( "Payment Date", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="8%">'.__( "Delivered date", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="6%">'.__( "Transaction ID", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="4%">'.__( "Job ID", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="10%">'.__( "Job Title", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="8%">'.__( "Buyer", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="8%">'.__( "Seller", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="8%">'.__( "Payment Gateway", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="7%">'.__( "Transaction Amount", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="7%">'.__( "Amount cleared (Seller Amount)", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="7%">'.__( "Buyer Fees (Processing Fee)", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="7%">'.__( "Seller Commision (Site Fee)", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="7%">'.__( "Tax", "wpjobster" ).'</th>';
				$tableInfo .= '</tr>';
			$tableInfo .= '</thead>';

			$tableInfo .= '<tbody>';
				$i=1;

				$total_transaction_amount = 0;
				$total_amount_cleared     = 0;
				$total_shipping           = 0;
				$total_buyer_fees         = 0;
				$total_seller_commision   = 0;
				$total_tax                = 0;

				foreach ($custom_extra_orders as $row) {
					$buyer_user = $row->uid;
					$buyer_user = get_user_by( 'id', $buyer_user );
					$buyer_user_name = ( $buyer_user ) ? wpj_get_user_display_type( $buyer_user->ID ) : '';

					$seller_user = wpj_get_seller_id( $row->order_id );
					$seller_user = get_user_by( 'id', $seller_user );
					$seller_user_name = ($seller_user) ? wpj_get_user_display_type( $seller_user->ID ) : '';

					$get_job_id = $getQuery->get_wpj_query( [ 'columns' => 'ID', 'table' => $this->_prefix.'posts', 'where' => 'post_title = "' . $row->job_title .'"' ] );
					$job_id = (isset($get_job_id[0]->ID)) ? $get_job_id[0]->ID : '';

					$get_custom_order_amount = $getQuery->get_wpj_query( ['table' => $this->_prefix.'job_payment_received', 'where' => 'payment_type_id = "' . $row->id .'" AND payment_type="custom_extra"' ] );
					$order = $get_custom_order_amount[0];
					$transaction_amount = (isset($order->final_amount)) ? $order->final_amount : $row->payable_amount;
					$processing_fees = (isset($order->fees)) ? $order->fees : 0;
					$tax = (isset($order->tax)) ? $order->tax : 0;
					$site_fees = wpj_get_site_fee_by_amount( floatval($transaction_amount) - floatval($processing_fees) - floatval($tax), $row->order_id );
					$amount_cleared = floatval($transaction_amount) - floatval($site_fees) - floatval($processing_fees) - floatval($tax);

					if($order->datemade && $order->datemade != 0) $payment_date = wpj_date( wpj_get_option( 'date_format' ) . ' ' . wpj_get_option( 'time_format' ), $order->datemade );
					else $payment_date = '-';

					if($order->payment_made_on && $order->payment_made_on != 0) $delivered_date = wpj_date( wpj_get_option( 'date_format' ) . ' ' . wpj_get_option( 'time_format' ), $order->payment_made_on );
					else $delivered_date = '-';

					$tableInfo .= '<tr>';
						$tableInfo .= '<td>'.$i.'</td>';
						$tableInfo .= '<td>'.$payment_date.'</td>';
						$tableInfo .= '<td>'.$delivered_date.'</td>';
						$tableInfo .= '<td>'.$row->id.'</td>';
						$tableInfo .= '<td>'.$job_id.'</td>';
						$tableInfo .= '<td>'.$row->job_title.'</td>';
						$tableInfo .= '<td>'.$buyer_user_name.'</td>';
						$tableInfo .= '<td>'.$seller_user_name.'</td>';
						$tableInfo .= '<td>'.$order->payment_gateway.'</td>';
						$tableInfo .= '<td>'.wpj_show_price_classic($transaction_amount).'</td>';
						$tableInfo .= '<td>'.wpj_show_price_classic($amount_cleared).'</td>';
						$tableInfo .= '<td>'.wpj_show_price_classic(floatval($processing_fees)).'</td>';
						$tableInfo .= '<td>'.wpj_show_price_classic(floatval($site_fees)).'</td>';
						$tableInfo .= '<td>'.wpj_show_price_classic(floatval($tax)).'</td>';
					$tableInfo .= '</tr>';

					$i++;

					$total_transaction_amount += $transaction_amount;
					$total_amount_cleared     += $amount_cleared;
					$total_buyer_fees         += $processing_fees;
					$total_seller_commision   += $site_fees;
					$total_tax                += $tax;
				}

				$tableInfo .= '<tr>';
					$tableInfo .= '<td>'.__( "Total", "wpjobster" ).'</td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($total_transaction_amount)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($total_amount_cleared)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($total_buyer_fees)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($total_seller_commision)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($total_tax)).'</td>';
				$tableInfo .= '</tr>';
			$tableInfo .= '</tbody>';
		$tableInfo .= '</table>';

		// END TABLE FORMAT //

		// GRAPH FORMAT //

		$graphInfo = [];
		$graphInfo['cols'] = [
			['label' => __('Date','wpjobster'), 'type' => 'string'],
			['label' => __('Transaction Amount','wpjobster'), 'type' => 'number'],
			['label' => __('Amount cleared','wpjobster'), 'type' => 'number'],
			['label' => __('Processing Fees','wpjobster'), 'type' => 'number'],
			['label' => __('Seller Commision','wpjobster'), 'type' => 'number'],
			['label' => __('Tax','wpjobster'), 'type' => 'number'],
		];

		$graphInfo['rows'] = $this->graph_range_report('job_orders','date_made','custom_extra',$this->job_where_array);

		// END GRAPH FORMAT

		$jsonTable = json_encode(["tableInfo" => $tableInfo, "graphInfo" => $graphInfo, "userError" => $this->userError]);
		echo $jsonTable;

		die();
	}

	function tips_sales_report( $condition = [] ){

		$getQuery = new WPJ_Query();

		// TABLE FORMAT //

		$tips_all_orders = [
			'table' => $this->_prefix.'job_orders wjo,' . $this->_prefix.'job_tips_orders wjto',
			'order_by' => 'wjo.id DESC',
			'where' => 'wjo.id = wjto.order_id AND wjto.payment_status="completed" AND wjto.paid_on >= ' . $this->from_date . ' AND wjto.paid_on <= '. $this->from_to . $this->job_where_array,
		];
		$tips_orders = $getQuery->get_wpj_query( $tips_all_orders );

		$tableInfo = '';

		$tableInfo .= '<table id="tips-table" class="widefat post fixed" cellspacing="0">';
			$tableInfo .= '<thead>';
				$tableInfo .= '<tr>';
					$tableInfo .= '<th width="5%">'.__( "No", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="8%">'.__( "Payment Date", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="8%">'.__( "Delivered date", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="6%">'.__( "Transaction ID", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="4%">'.__( "Job ID", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="10%">'.__( "Job Title", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="8%">'.__( "Buyer", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="8%">'.__( "Seller", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="8%">'.__( "Payment Gateway", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="7%">'.__( "Transaction Amount", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="7%">'.__( "Amount cleared (Seller Amount)", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="7%">'.__( "Buyer Fees (Processing Fee)", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="7%">'.__( "Seller Commision (Site Fee)", "wpjobster" ).'</th>';
					$tableInfo .= '<th width="7%">'.__( "Tax", "wpjobster" ).'</th>';
				$tableInfo .= '</tr>';
			$tableInfo .= '</thead>';

			$tableInfo .= '<tbody>';
				$i=1;

				$total_transaction_amount = 0;
				$total_amount_cleared     = 0;
				$total_shipping           = 0;
				$total_buyer_fees         = 0;
				$total_seller_commision   = 0;
				$total_tax                = 0;

				foreach ($tips_orders as $row) {
					$get_refunded_amount = $getQuery->get_wpj_query( [ 'columns' => 'amount, datemade', 'table' => $this->_prefix.'job_payment_transactions', 'where' => 'oid = "' . $row->order_id .'" AND rid=7' ] );

					if ( ! $get_refunded_amount ) {

						$buyer_user = $row->uid;
						$buyer_user = get_user_by( 'id', $buyer_user );
						$buyer_user_name = ( $buyer_user ) ? wpj_get_user_display_type( $buyer_user->ID ) : '';

						$seller_user = wpj_get_seller_id( $row->order_id );
						$seller_user = get_user_by( 'id', $seller_user );
						$seller_user_name = ($seller_user) ? wpj_get_user_display_type( $seller_user->ID ) : '';

						$get_job_id = $getQuery->get_wpj_query( [ 'columns' => 'ID', 'table' => $this->_prefix.'posts', 'where' => 'post_title = "' . $row->job_title .'"' ] );
						$job_id = (isset($get_job_id[0]->ID)) ? $get_job_id[0]->ID : '';

						$get_custom_order_amount = $getQuery->get_wpj_query( ['table' => $this->_prefix.'job_payment_received', 'where' => 'payment_type_id = "' . $row->id .'" AND payment_type="tips"' ] );
						$order = $get_custom_order_amount[0];
						$transaction_amount = (isset($order->final_amount)) ? $order->final_amount : $row->payable_amount;
						$processing_fees = (isset($order->fees)) ? $order->fees : 0;
						$tax = (isset($order->tax)) ? $order->tax : 0;
						$site_fees = wpj_get_site_fee_by_amount( floatval($transaction_amount) - floatval($processing_fees) - floatval($tax), $row->order_id );
						$amount_cleared = floatval($transaction_amount) - floatval($site_fees) - floatval($processing_fees) - floatval($tax);

						if($order->datemade && $order->datemade != 0) $payment_date = wpj_date( wpj_get_option( 'date_format' ) . ' ' . wpj_get_option( 'time_format' ), $order->datemade );
						else $payment_date = '-';

						if($order->payment_made_on && $order->payment_made_on != 0) $delivered_date = wpj_date( wpj_get_option( 'date_format' ) . ' ' . wpj_get_option( 'time_format' ), $order->payment_made_on );
						else $delivered_date = '-';

						$tableInfo .= '<tr>';
							$tableInfo .= '<td>'.$i.'</td>';
							$tableInfo .= '<td>'.$payment_date.'</td>';
							$tableInfo .= '<td>'.$delivered_date.'</td>';
							$tableInfo .= '<td>'.$row->id.'</td>';
							$tableInfo .= '<td>'.$job_id.'</td>';
							$tableInfo .= '<td>'.$row->job_title.'</td>';
							$tableInfo .= '<td>'.$buyer_user_name.'</td>';
							$tableInfo .= '<td>'.$seller_user_name.'</td>';
							$tableInfo .= '<td>'.$order->payment_gateway.'</td>';
							$tableInfo .= '<td>'.wpj_show_price_classic($transaction_amount).'</td>';
							$tableInfo .= '<td>'.wpj_show_price_classic($amount_cleared).'</td>';
							$tableInfo .= '<td>'.wpj_show_price_classic(floatval($processing_fees)).'</td>';
							$tableInfo .= '<td>'.wpj_show_price_classic(floatval($site_fees)).'</td>';
							$tableInfo .= '<td>'.wpj_show_price_classic(floatval($tax)).'</td>';
						$tableInfo .= '</tr>';

						$i++;

						$total_transaction_amount += $transaction_amount;
						$total_amount_cleared     += $amount_cleared;
						$total_buyer_fees         += $processing_fees;
						$total_seller_commision   += $site_fees;
						$total_tax                += $tax;
					}
				}

				$tableInfo .= '<tr>';
					$tableInfo .= '<td>'.__( "Total", "wpjobster" ).'</td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($total_transaction_amount)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($total_amount_cleared)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($total_buyer_fees)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($total_seller_commision)).'</td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($total_tax)).'</td>';
				$tableInfo .= '</tr>';
			$tableInfo .= '</tbody>';
		$tableInfo .= '</table>';

		// END TABLE FORMAT //

		// GRAPH FORMAT //

		$graphInfo = [];
		$graphInfo['cols'] = [
			['label' => __('Date','wpjobster'), 'type' => 'string'],
			['label' => __('Transaction Amount','wpjobster'), 'type' => 'number'],
			['label' => __('Amount cleared','wpjobster'), 'type' => 'number'],
			['label' => __('Processing Fees','wpjobster'), 'type' => 'number'],
			['label' => __('Seller Commision','wpjobster'), 'type' => 'number'],
			['label' => __('Tax','wpjobster'), 'type' => 'number'],
		];

		$graphInfo['rows'] = $this->graph_range_report('job_orders','date_made','tips',$this->job_where_array);

		// END GRAPH FORMAT

		$jsonTable = json_encode(["tableInfo" => $tableInfo, "graphInfo" => $graphInfo, "userError" => $this->userError]);
		echo $jsonTable;

		die();
	}

	function subscription_sales_report() {
		$getQuery = new WPJ_Query();

		// TABLE FORMAT //

		$subscription_all_orders = [
			'table' => $this->_prefix.'job_subscription_orders so, ' . $this->_prefix.'job_payment_received pr',
			'order_by' => 'so.id DESC',
			'where' => 'so.payment_status="completed" AND so.addon_date >= ' . $this->from_date . ' AND so.addon_date <= '. $this->from_to . $this->subscription_where_array . ' AND so.id=pr.payment_type_id AND payment_type="subscription"',
		];
		$subscription_orders = $getQuery->get_wpj_query( $subscription_all_orders );

		$tableInfo = '';

		$tableInfo .= '<table id="subscription-table" class="widefat post fixed" cellspacing="0">';
			$tableInfo .= '<thead>';
				$tableInfo .= '<tr>';
					$tableInfo .= '<th>'.__( "No", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Payment Date", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Transaction ID", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Plan", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Level", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Username", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Payment Gateway", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Transaction Amount", "wpjobster" ).'</th>';
				$tableInfo .= '</tr>';
			$tableInfo .= '</thead>';

			$tableInfo .= '<tbody>';
				$i=1;

				$total_transaction_amount = 0;

				foreach ($subscription_orders as $row) {
					$user = get_user_by( 'id', $row->user_id );
					$user_name = ( $user ) ? wpj_get_user_display_type( $user->ID ) : '';

					if($row->payment_date && $row->payment_date != 0) $payment_date = wpj_date( wpj_get_option( 'date_format' ) . ' ' . wpj_get_option( 'time_format' ), $row->payment_date );
					else $payment_date = '-';

					$transaction_amount = (isset($row->final_amount)) ? $row->final_amount : $row->amount;
					$gateway = (isset($row->payment_gateway_name)) ? $row->payment_gateway_name : $row->payment_gateway;

					$tableInfo .= '<tr>';
						$tableInfo .= '<td>'.$i.'</td>';
						$tableInfo .= '<td>'.$payment_date.'</td>';
						$tableInfo .= '<td>'.$row->payment_gateway_transaction_id.'</td>';
						$tableInfo .= '<td>'.ucfirst( $row->plan ).'</td>';
						$tableInfo .= '<td>'.ucfirst( $row->level ).'</td>';
						$tableInfo .= '<td>'.$user_name.'</td>';
						$tableInfo .= '<td>'.$gateway.'</td>';
						$tableInfo .= '<td>'.wpj_show_price_classic($transaction_amount).'</td>';
					$tableInfo .= '</tr>';

					$i++;

					$total_transaction_amount += $transaction_amount;
				}

				$tableInfo .= '<tr>';
					$tableInfo .= '<td>'.__( "Total", "wpjobster" ).'</td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($total_transaction_amount)).'</td>';
				$tableInfo .= '</tr>';
			$tableInfo .= '</tbody>';
		$tableInfo .= '</table>';

		// END TABLE FORMAT //

		// GRAPH FORMAT //

		$graphInfo = [];
		$graphInfo['cols'] = [
			['label' => __('Date','wpjobster'), 'type' => 'string'],
			['label' => __('Transaction Amount','wpjobster'), 'type' => 'number'],
		];

		$graphInfo['rows'] = $this->graph_range_report('job_subscription_orders','addon_date','subscription',$this->subscription_where_array);

		// END GRAPH FORMAT

		$jsonTable = json_encode(["tableInfo" => $tableInfo, "graphInfo" => $graphInfo, "userError" => $this->userError]);
		echo $jsonTable;

		die();
	}

	function badge_sales_report() {
		$getQuery = new WPJ_Query();

		// TABLE FORMAT //

		$badge_all_orders = [
			'table' => $this->_prefix.'job_badge_orders bo, ' . $this->_prefix.'job_payment_received pr',
			'order_by' => 'bo.id DESC',
			'where' => 'bo.payment_status="completed" AND bo.date_made >= ' . $this->from_date . ' AND bo.date_made <= '. $this->from_to . $this->badge_where_array . ' AND bo.id=pr.payment_type_id AND pr.payment_type="badge"',
		];
		$badge_orders = $getQuery->get_wpj_query( $badge_all_orders );

		$tableInfo = '';

		$tableInfo .= '<table id="badge-table" class="widefat post fixed" cellspacing="0">';
			$tableInfo .= '<thead>';
				$tableInfo .= '<tr>';
					$tableInfo .= '<th>'.__( "No", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Payment Date", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Transaction ID", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Description", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Badge nr.", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Username", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Payment Gateway", "wpjobster" ).'</th>';
					$tableInfo .= '<th>'.__( "Transaction Amount", "wpjobster" ).'</th>';
				$tableInfo .= '</tr>';
			$tableInfo .= '</thead>';

			$tableInfo .= '<tbody>';
				$i=1;

				$total_transaction_amount = 0;

				foreach ($badge_orders as $row) {
					$user = get_user_by( 'id', $row->user_id );
					$user_name = ( $user ) ? wpj_get_user_display_type( $user->ID ) : '';

					if($row->payment_date && $row->payment_date != 0) $payment_date = wpj_date( wpj_get_option( 'date_format' ) . ' ' . wpj_get_option( 'time_format' ), $row->payment_date );
					else $payment_date = '-';

					$transaction_amount = (isset($row->final_amount)) ? $row->final_amount : $row->amount;
					$gateway = (isset($row->payment_gateway_name)) ? $row->payment_gateway_name : $row->payment_gateway;

					$tableInfo .= '<tr>';
						$tableInfo .= '<td>'.$i.'</td>';
						$tableInfo .= '<td>'.$payment_date.'</td>';
						$tableInfo .= '<td>#' . wpj_camouflage_oid( $row->id, $row->date_made ).'</td>';
						$tableInfo .= '<td>'.ucfirst( $row->description ).'</td>';
						$tableInfo .= '<td>'.ucfirst( $row->badge_nr ).'</td>';
						$tableInfo .= '<td>'.$user_name.'</td>';
						$tableInfo .= '<td>'.$gateway.'</td>';
						$tableInfo .= '<td>'.wpj_show_price_classic($transaction_amount).'</td>';
					$tableInfo .= '</tr>';

					$i++;

					$total_transaction_amount += $transaction_amount;
				}

				$tableInfo .= '<tr>';
					$tableInfo .= '<td>'.__( "Total", "wpjobster" ).'</td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td></td>';
					$tableInfo .= '<td>'.wpj_show_price_classic(floatval($total_transaction_amount)).'</td>';
				$tableInfo .= '</tr>';
			$tableInfo .= '</tbody>';
		$tableInfo .= '</table>';

		// END TABLE FORMAT //

		// GRAPH FORMAT //

		$graphInfo = [];
		$graphInfo['cols'] = [
			['label' => __('Date','wpjobster'), 'type' => 'string'],
			['label' => __('Transaction Amount','wpjobster'), 'type' => 'number'],
		];

		$graphInfo['rows'] = $this->graph_range_report('job_badge_orders','date_made','badge',$this->badge_where_array);

		// END GRAPH FORMAT

		$jsonTable = json_encode(["tableInfo" => $tableInfo, "graphInfo" => $graphInfo, "userError" => $this->userError]);
		echo $jsonTable;

		die();
	}

	function total_credits_owed_to_user(){
		global $wpdb;
		$user_meta_table = defined( 'CUSTOM_USER_META_TABLE' ) ? CUSTOM_USER_META_TABLE : $wpdb->prefix.'usermeta';
		$sql = "SELECT sum(meta_value) as total_credits FROM {$user_meta_table} WHERE meta_key = 'credits' ";
		$result = $this->_wpdb->get_results($sql);
		if($result){
			return wpj_show_price_classic($result['0']->total_credits);
		}else{
			return false;
		}
	}

	function get_date_and_user(){
		wpj_set_site_timezone();

		$this->user_inp  = isset($_POST['userid']) ? $_POST['userid'] : '';
		$this->from_date = isset($_POST['from_date']) ? $_POST['from_date'] : '';
		$this->from_to   = isset($_POST['to_date']) ? $_POST['to_date'] : '';

		$date_now = date("Y/m/d");
		if($this->from_to == $date_now)
			$this->from_to = date("Y/m/d H:i:s");

		$this->from_date = strtotime(trim(htmlspecialchars($this->from_date)));
		$this->from_to   = strtotime(trim(htmlspecialchars($this->from_to)));

		if($this->user_inp){
			if(username_exists($this->user_inp)){
				$user = get_user_by('login', $this->user_inp);
				$this->user_to_search = $user->ID;
			}else{
				$user = get_userdata( $this->user_inp );
				if ( $user === false ) {
					$this->user_to_search = '';
				} else {
					$this->user_to_search = $_POST['userid'];
				}
			}
		}
		if(isset($this->user_to_search) && $this->user_to_search != ''){
			$this->job_where_array          = ' AND uid = '.$this->user_to_search;
			$this->topup_where_array        = ' AND user_id = '.$this->user_to_search;
			$this->featured_where_array     = ' AND user_id = '.$this->user_to_search;
			$this->withdrawal_where_array   = ' AND uid = '.$this->user_to_search;
			$this->custom_extra_where_array = ' AND user_id = '.$this->user_to_search;
			$this->tips_where_array         = ' AND user_id = '.$this->user_to_search;
			$this->subscription_where_array = ' AND user_id = '.$this->user_to_search;
			$this->badge_where_array        = ' AND user_id = '.$this->user_to_search;
		}else{
			$this->job_where_array          = '';
			$this->topup_where_array        = '';
			$this->featured_where_array     = '';
			$this->withdrawal_where_array   = '';
			$this->custom_extra_where_array = '';
			$this->tips_where_array         = '';
			$this->subscription_where_array = '';
			$this->badge_where_array        = '';
		}

		if($this->user_inp && $this->user_to_search == ''){
			$this->userError = "This user does not exist!";
		}else{
			$this->userError = "";
		}
	}

	// FUNCTIONS FOR GRAPHS //

	function graph_range_report($table_name='', $date_db_label='', $type='', $whereArr=''){

		$getQuery = new WPJ_Query();

		$rows = [];

		$startTime = $this->from_date;
		$endTime = $this->from_to;

		$day_in_seconds     = 60 * 60 * 24;
		$week_in_seconds    = 60 * 60 * 24 * 7;
		$month_in_seconds   = 60 * 60 * 24 * 30;
		$year_in_seconds    = 60 * 60 * 24 * 365;

		$dateRangeNo = floor( ($endTime - $startTime) / (60 * 60 * 24));

		$rangeType = $_POST['graph-range'];

		if($rangeType == 'months'){
			$rangeToShow = $month_in_seconds;
		} else if($rangeType == 'weeks'){
			$rangeToShow = $week_in_seconds;
		} else {
			$rangeToShow = $day_in_seconds;
		}

		for ( $i = $startTime; $i <= $endTime; $i = $i + $rangeToShow ) {
			$dates = wpj_date( wpj_get_option( 'date_format' ), $i );

			$dateStart = $i - ($i % $rangeToShow) + $rangeToShow;
			$dateEnd = $dateStart + $rangeToShow;

			$payment_status = $type != 'withdrawal' ? 'payment_status="completed" AND ' : '';

			$query_graph = [
				'table' => $this->_prefix.$table_name,
				'order_by' => 'id DESC',
				'where' => $payment_status . $date_db_label .' > ' . $dateStart . ' AND ' . $date_db_label . ' <= '. $dateEnd . $whereArr,
			];
			if($type =='custom_extra'){
				$query_graph = [
					'table' => $this->_prefix.'job_orders wjo,' . $this->_prefix.'job_custom_extra_orders wjceo',
					'order_by' => 'wjo.id DESC',
					'where' => 'wjceo.payment_status="completed" AND wjo.id = wjceo.order_id AND date_made >= ' . $dateStart . ' AND date_made <= '. $dateEnd . $whereArr,
				];
			}
			if ( $type == 'tips' ) {
				$query_graph = [
					'table' => $this->_prefix.'job_orders wjo,' . $this->_prefix.'job_tips_orders wjto',
					'order_by' => 'wjo.id DESC',
					'where' => 'wjto.payment_status="completed" AND wjo.id = wjto.order_id AND date_made >= ' . $dateStart . ' AND date_made <= '. $dateEnd . $whereArr,
				];
			}
			if ( $type == 'featured' ) {
				$query_graph = [
					'table' => $this->_prefix.'job_featured_orders fo, ' . $this->_prefix.'job_payment_received pr',
					'order_by' => 'fo.id DESC',
					'where' => 'fo.payment_status="completed" AND fo.added_on > ' . $dateStart . ' AND fo.added_on <= '. $dateEnd . $whereArr . ' AND fo.id=pr.payment_type_id AND payment_type="feature"',
				];
			}

			$result_query_graph = $getQuery->get_wpj_query( $query_graph );

			if($table_name == 'job_orders' && $type != 'refund' && $type != 'custom_extra' && $type != 'tips'){
				$temp = $this->get_job_orders_data_graph($result_query_graph, $dates);
			}else if($table_name == 'job_topup_orders'){
				$temp = $this->get_topup_orders_data_graph($result_query_graph, $dates);
			}else if($table_name == 'job_featured_orders'){
				$temp = $this->get_featured_orders_data_graph($result_query_graph, $dates);
			}else if($table_name == 'job_orders' && $type =='refund'){
				$temp = $this->get_refund_orders_data_graph($result_query_graph, $dates);
			}else if($table_name == 'job_withdraw'){
				$temp = $this->get_withdraw_orders_data_graph($result_query_graph, $dates);
			}else if($table_name == 'job_orders' && $type =='custom_extra'){
				$temp = $this->get_custom_extra_orders_data_graph($result_query_graph, $dates);
			}else if($table_name == 'job_orders' && $type =='tips'){
				$temp = $this->get_tips_orders_data_graph($result_query_graph, $dates);
			}else if($table_name == 'job_subscription_orders'){
				$temp = $this->get_subscription_orders_data_graph($result_query_graph, $dates);
			}else if($table_name == 'job_badge_orders'){
				$temp = $this->get_badge_orders_data_graph($result_query_graph, $dates);
			}else{
				$temp = [];
			}

			$rows[] = ['c' => $temp];
		}
		return $rows;
	}

	function get_job_orders_data_graph($results, $dates){
		$total_tr_am_graph     = 0;
		$total_am_cl_graph     = 0;
		$total_ship_graph      = 0;
		$total_pr_fees_graph   = 0;
		$total_site_fees_graph = 0;
		$total_tax_am_graph    = 0;

		foreach ($results as $r_graph) {

			$trans_amount_graph = explode('|', $r_graph->final_paidamount);
			if($trans_amount_graph[0] && $trans_amount_graph[0] != wpj_get_site_default_currency()){
				$currency_from_graph = $trans_amount_graph[0];
				$currency_to_graph = wpj_get_site_default_currency();
				$transaction_amount_graph = wpj_get_exchanged_value($trans_amount_graph[1], $currency_from_graph, $currency_to_graph);
			}else{
				$transaction_amount_graph = $trans_amount_graph[1];
			}

			if ( $transaction_amount_graph == '0' ) {
				$shipping_graph       = 0;
				$amount_cleared_graph = 0;
				$buyer_fees_graph     = 0;
				$site_fee_graph       = 0;
				$tax_graph            = 0;
			} else {
				$shipping_graph       = floatval( $r_graph->shipping );
				$amount_cleared_graph = floatval( $r_graph->mc_gross ) - floatval( $r_graph->site_fees );
				$buyer_fees_graph     = floatval( $r_graph->processing_fees );
				$site_fee_graph       = floatval( $r_graph->site_fees );
				$tax_graph            = floatval( $r_graph->tax_amount );
			}

			$total_tr_am_graph     += $transaction_amount_graph;
			$total_ship_graph      += $shipping_graph;
			$total_am_cl_graph     += $amount_cleared_graph;
			$total_pr_fees_graph   += $buyer_fees_graph;
			$total_site_fees_graph += $site_fee_graph;
			$total_tax_am_graph    += $tax_graph;
		}

		$temp = [];
		$temp[] = ['v' => (string) $dates];
		$temp[] = ['v' => (float) $total_tr_am_graph ];
		$temp[] = ['v' => (float) $total_ship_graph ];
		$temp[] = ['v' => (float) $total_am_cl_graph ];
		$temp[] = ['v' => (float) $total_pr_fees_graph ];
		$temp[] = ['v' => (float) $total_site_fees_graph ];
		$temp[] = ['v' => (float) $total_tax_am_graph ];

		return $temp;
	}

	function get_topup_orders_data_graph($results, $dates){
		$total_tr_amount_graph  = 0;
		$total_usr_credit_graph = 0;
		$total_earnings_graph   = 0;

		foreach ($results as $r_graph) {
			$earnings = floatval($r_graph->package_cost_without_tax) - floatval($r_graph->package_credit_without_tax);
			$total_tr_amount_graph += $r_graph->package_cost_without_tax;
			$total_usr_credit_graph += $r_graph->package_credit_without_tax;
			$total_earnings_graph += $earnings;
		}

		$temp = [];
		$temp[] = ['v' => (string) $dates];
		$temp[] = ['v' => (float) $total_tr_amount_graph ];
		$temp[] = ['v' => (float) $total_usr_credit_graph ];
		$temp[] = ['v' => (float) $total_earnings_graph ];

		return $temp;
	}

	function get_featured_orders_data_graph($results, $dates){
		$total_transaction_amount_graph     = 0;
		$total_featured_amount_graph        = 0;
		$total_processing_fees_amount_graph = 0;
		$total_tax_amount_graph             = 0;

		foreach ($results as $r_graph) {
			$total_transaction_amount_graph     += wpj_get_exchanged_value( $r_graph->payable_amount, $r_graph->final_amount_currency, wpj_get_site_default_currency() );
			$total_featured_amount_graph        += $r_graph->featured_amount;
			$total_processing_fees_amount_graph += $r_graph->fees;
			$total_tax_amount_graph             += $r_graph->tax;
		}

		$temp = [];
		$temp[] = ['v' => (string) $dates];
		$temp[] = ['v' => (float) $total_transaction_amount_graph ];
		$temp[] = ['v' => (float) $total_featured_amount_graph ];
		$temp[] = ['v' => (float) $total_processing_fees_amount_graph ];
		$temp[] = ['v' => (float) $total_tax_amount_graph ];

		return $temp;
	}

	function get_refund_orders_data_graph($results, $dates){
		$getQuery = new WPJ_Query();

		$total_tr_amount_graph = 0;

		foreach ($results as $r_graph) {
			$get_refund_amount = $getQuery->get_wpj_query( [ 'columns' => 'amount, datemade', 'table' => $this->_prefix.'job_payment_transactions', 'where' => 'oid = "' . $r_graph->id .'" AND rid=7' ] );
			$refund_amount = (isset($get_refund_amount[0]->amount)) ? $get_refund_amount[0]->amount : 0;

			$total_tr_amount_graph += $refund_amount;
		}

		$temp = [];
		$temp[] = ['v' => (string) $dates];
		$temp[] = ['v' => (float) $total_tr_amount_graph ];

		return $temp;
	}

	function get_withdraw_orders_data_graph($results, $dates){

		$total_tr_amount_graph = 0;

		foreach ($results as $r_graph) {
			$total_tr_amount_graph += $r_graph->amount;
		}

		$temp = [];
		$temp[] = ['v' => (string) $dates];
		$temp[] = ['v' => (float) $total_tr_amount_graph ];

		return $temp;
	}

	function get_custom_extra_orders_data_graph($results, $dates){
		$getQuery = new WPJ_Query();

		$total_tr_am_graph     = 0;
		$total_am_cl_graph     = 0;
		$total_pr_fees_graph   = 0;
		$total_site_fees_graph = 0;
		$total_tax_am_graph    = 0;

		foreach ($results as $r_graph) {

			$get_custom_order_amount = $getQuery->get_wpj_query( ['table' => $this->_prefix.'job_payment_received', 'where' => 'payment_type_id = "' . $r_graph->id .'" AND payment_type="custom_extra"' ] );

			$total_amount          = (isset($get_custom_order_amount[0]->final_amount)) ? $get_custom_order_amount[0]->final_amount : $r_graph->payable_amount;
			$total_processing_fees = (isset($get_custom_order_amount[0]->fees)) ? $get_custom_order_amount[0]->fees : 0;
			$total_taxes           = (isset($get_custom_order_amount[0]->tax)) ? $get_custom_order_amount[0]->tax : 0;
			$total_site_fees       = wpj_get_site_fee_by_amount( floatval($total_amount) - floatval($total_processing_fees) - floatval($total_taxes), $r_graph->id );
			$total_amount_cleared  = floatval($total_amount) - floatval($total_site_fees) - floatval($total_processing_fees) - floatval($total_taxes);

			$total_tr_am_graph     += $total_amount;
			$total_pr_fees_graph   += $total_processing_fees;
			$total_tax_am_graph    += $total_taxes;
			$total_site_fees_graph += $total_site_fees;
			$total_am_cl_graph     += $total_amount_cleared;
		}

		$temp = [];
		$temp[] = ['v' => (string) $dates];
		$temp[] = ['v' => (float) $total_tr_am_graph ];
		$temp[] = ['v' => (float) $total_am_cl_graph ];
		$temp[] = ['v' => (float) $total_pr_fees_graph ];
		$temp[] = ['v' => (float) $total_site_fees_graph ];
		$temp[] = ['v' => (float) $total_tax_am_graph ];

		return $temp;
	}

	function get_tips_orders_data_graph($results, $dates){
		$getQuery = new WPJ_Query();

		$total_tr_am_graph     = 0;
		$total_am_cl_graph     = 0;
		$total_pr_fees_graph   = 0;
		$total_site_fees_graph = 0;
		$total_tax_am_graph    = 0;

		foreach ($results as $r_graph) {

			$get_refunded_amount = $getQuery->get_wpj_query( [ 'columns' => 'amount, datemade', 'table' => $this->_prefix.'job_payment_transactions', 'where' => 'oid = "' . $r_graph->order_id .'" AND rid=7' ] );

			if ( ! $get_refunded_amount ) {

				$get_custom_order_amount = $getQuery->get_wpj_query( ['table' => $this->_prefix.'job_payment_received', 'where' => 'payment_type_id = "' . $r_graph->id .'" AND payment_type="tips"' ] );

				$total_amount          = (isset($get_custom_order_amount[0]->final_amount)) ? $get_custom_order_amount[0]->final_amount : $r_graph->payable_amount;
				$total_processing_fees = (isset($get_custom_order_amount[0]->fees)) ? $get_custom_order_amount[0]->fees : 0;
				$total_taxes           = (isset($get_custom_order_amount[0]->tax)) ? $get_custom_order_amount[0]->tax : 0;
				$total_site_fees       = wpj_get_site_fee_by_amount( floatval($total_amount) - floatval($total_processing_fees) - floatval($total_taxes), $r_graph->id );
				$total_amount_cleared  = floatval($total_amount) - floatval($total_site_fees) - floatval($total_processing_fees) - floatval($total_taxes);

				$total_tr_am_graph     += $total_amount;
				$total_pr_fees_graph   += $total_processing_fees;
				$total_tax_am_graph    += $total_taxes;
				$total_site_fees_graph += $total_site_fees;
				$total_am_cl_graph     += $total_amount_cleared;

			}
		}

		$temp = [];
		$temp[] = ['v' => (string) $dates];
		$temp[] = ['v' => (float) $total_tr_am_graph ];
		$temp[] = ['v' => (float) $total_am_cl_graph ];
		$temp[] = ['v' => (float) $total_pr_fees_graph ];
		$temp[] = ['v' => (float) $total_site_fees_graph ];
		$temp[] = ['v' => (float) $total_tax_am_graph ];

		return $temp;
	}

	function get_subscription_orders_data_graph($results, $dates){
		$total_tr_amount_graph = 0;

		foreach ($results as $r_graph) {
			$total_tr_amount_graph += $r_graph->amount;
		}

		$temp = [];
		$temp[] = ['v' => (string) $dates];
		$temp[] = ['v' => (float) $total_tr_amount_graph ];

		return $temp;
	}

	function get_badge_orders_data_graph($results, $dates){
		$total_tr_amount_graph = 0;

		foreach ($results as $r_graph) {
			$total_tr_amount_graph += $r_graph->price;
		}

		$temp = [];
		$temp[] = ['v' => (string) $dates];
		$temp[] = ['v' => (float) $total_tr_amount_graph ];

		return $temp;
	}

	// END FUNCTIONS FOR GRAPHS //

}

add_action( 'after_setup_theme', [ 'WPJ_Summary', 'init' ] );

class WPJ_Query{

	public $_wpdb;

	public function __construct(){
		global $wpdb, $_select_sql, $_select_result;
		$this->_wpdb = $wpdb;
	}

	public function get_wpj_query( $params = [] ){
		if ( isset( $params['columns'] ) && $params['columns'] != '' ) {
			$select = "SELECT " . $params['columns'];
		} else {
			$select = "SELECT *";
		}

		if ( isset( $params['table'] ) && $params['table'] != '' ) {
			$from = " FROM " . $params['table'];
		} else {
			$from = " FROM ";
		}

		if ( isset( $params['where'] ) && $params['where'] != '' ) {
			$where = " WHERE " . $params['where'];
		} else {
			$where = "";
		}

		if ( isset( $params['order_by'] ) && $params['order_by'] != '' ) {
			$order_by = " ORDER BY " . $params['order_by'];
		} else {
			$order_by = "";
		}

		if ( isset( $params['group_by'] ) && $params['group_by'] != '' ) {
			$group_by = " GROUP BY " . $params['group_by'];
		} else {
			$group_by = "";
		}

		if ( isset( $params['limit'] ) && $params['limit'] != '' ) {
			$limit = " LIMIT " . $params['limit']['1'] . ", " . $params['limit']['0'];
		} else {
			$limit = "";
		}

		$this->_select_sql = $select . $from . $where . $order_by . $group_by . $limit;
		$this->_result_sql = $this->_wpdb->get_results( $this->_select_sql );

		return $this->_result_sql;
	}
}