utm-add-on-hold-status

functions.php

<?php

namespace WPGens_ST\Includes\WooCommerce;

use Automattic\WooCommerce\Utilities\OrderUtil;

defined('ABSPATH') || exit;

class WPGENS_ST_Orders_Analytics
{
	public function get_orders($data)
	{
		$per_page = 20;
		$curr_page = isset($data['page']) ? intval($data['page']) : 1;
		$touch_type = sanitize_text_field($data['touch']);
		$meta_key = "wpg_" . $touch_type;

		if (OrderUtil::custom_orders_table_usage_is_enabled()) {
			$orders = $this->fetch_filtered_orders($data, $meta_key);
		} else {
			$orders = $this->fetch_non_hpos_filtered_orders($data, $meta_key);
		}

		$formatted_orders = [];
		foreach ($orders as $order) {
			$order_id = $order->get_id();
			$order_meta = maybe_unserialize($order->get_meta($meta_key));
			$order_date = $order->get_date_created();
			$first_visit = isset($order_meta['lpt']) ? new \DateTime($order_meta['lpt']) : null;

			$formatted_order = [
				'date' => date_i18n(get_option('date_format') . ' ' . get_option('time_format'), strtotime($order_date)),
				'id' => "<a target='_blank' href='" . get_edit_post_link($order_id) . "'>#" . $order_id . "</a>",
				'amount' => $order->get_formatted_order_total(),
				'referrer' => !empty($order_meta['referrer']) ? $order_meta['referrer'] : '-',
				'source' => !empty($order_meta['source']) ? $order_meta['source'] : '-',
				'medium' => !empty($order_meta['medium']) ? $order_meta['medium'] : '-',
				'campaign' => !empty($order_meta['campaign']) ? $order_meta['campaign'] : '-',
				'term' => !empty($order_meta['term']) ? $order_meta['term'] : '-',
				'content' => !empty($order_meta['content']) ? $order_meta['content'] : '-',
				'conversion_lag' => $first_visit ? $this->calculate_conversion_lag($order) : '-'
			];

			$formatted_orders[] = $formatted_order;
		}

		$total_orders = count($orders);
		$num_of_pages = ceil($total_orders / $per_page);

		return [
			'data' => array_slice($formatted_orders, ($curr_page - 1) * $per_page, $per_page),
			'num_of_pages' => $num_of_pages
		];
	}

	private function calculate_conversion_lag($order)
	{
		$first_time = $order->get_meta('_wpg_first_lpt', true);
		$first_landing_page_time = new \DateTime($first_time);
		$interval = $first_landing_page_time->diff($order->get_date_created());

		if ($interval->days > 0) {
			return $interval->days . ' days';
		} elseif ($interval->h > 0) {
			return $interval->h . ' hours and ' . $interval->i . ' minutes';
		} else {
			return $interval->i . ' minutes';
		}
	}


	public function get_reports($data)
	{
		$touch_type = sanitize_text_field($data['touch']);
		$meta_key = "wpg_" . $touch_type;


		if (OrderUtil::custom_orders_table_usage_is_enabled()) {
			$orders = $this->fetch_filtered_orders($data, $meta_key);
		} else {
			$orders = $this->fetch_non_hpos_filtered_orders($data, $meta_key);
		}


		// TYPE and total orders amount
		$total_amount = 0;
		$utm_groups = $sources = $campaigns = $mediums = $contents = $terms = $referrers = $lps = $devices = $browsers = $countries = [];

		foreach ($orders as $order) {
			$order_total = $order->get_total();
			$total_amount += $order_total;

			$order_meta = maybe_unserialize($order->get_meta($meta_key));

			if (!$order_meta) {
				continue;
			}

			$this->process_utm_data($sources, $order_meta, 'source', $order_total);
			$this->process_utm_data($campaigns, $order_meta, 'campaign', $order_total);
			$this->process_utm_data($mediums, $order_meta, 'medium', $order_total);
			$this->process_utm_data($contents, $order_meta, 'content', $order_total);
			$this->process_utm_data($terms, $order_meta, 'term', $order_total);
			$this->process_utm_data($referrers, $order_meta, 'referrer', $order_total);
			$this->process_utm_data($lps, $order_meta, 'lp', $order_total);
			$this->process_utm_data($devices, $order_meta, 'device', $order_total);
			$this->process_utm_data($browsers, $order_meta, 'browser', $order_total);

			$country = $order->get_billing_country();
			if (!empty($country)) {
				if (!isset($countries[$country])) {
					$countries[$country] = [
						'title' => $country,
						'num_of_orders' => 0,
						'total_amount' => 0
					];
				}
				$countries[$country]['num_of_orders']++;
				$countries[$country]['total_amount'] += $order_total;
			}

			$referrer = $order->get_meta('_' . $meta_key . '_referrer') ?? 'direct';

			$utm_key = implode(' / ', array_filter([
				$referrer,
				$order_meta['source'] ?? '',
				$order_meta['medium'] ?? '',
				$order_meta['campaign'] ?? '',
				$order_meta['content'] ?? '',
				$order_meta['term'] ?? ''
			]));

			if (!isset($utm_groups[$utm_key])) {
				$utm_groups[$utm_key] = [
					'utm_string' => $utm_key,
					'referrer' => $referrer,
					'source' => $order_meta['source'] ?? '',
					'medium' => $order_meta['medium'] ?? '',
					'campaign' => $order_meta['campaign'] ?? '',
					'content' => $order_meta['content'] ?? '',
					'term' => $order_meta['term'] ?? '',
					'num_of_orders' => 0,
					'total_amount' => 0
				];
			}

			$utm_groups[$utm_key]['num_of_orders']++;
			$utm_groups[$utm_key]['total_amount'] += $order_total;
		}

		$number_of_orders = count($orders);
		$average_order_amount = $number_of_orders > 0 ? $total_amount / $number_of_orders : 0;

		$this->sort_and_format_data($utm_groups, $total_amount);
		$this->sort_and_format_data($sources, $total_amount);
		$this->sort_and_format_data($campaigns, $total_amount);
		$this->sort_and_format_data($mediums, $total_amount);
		$this->sort_and_format_data($contents, $total_amount);
		$this->sort_and_format_data($terms, $total_amount);
		$this->sort_and_format_data($referrers, $total_amount);
		$this->sort_and_format_data($lps, $total_amount);
		$this->sort_and_format_data($devices, $total_amount);
		$this->sort_and_format_data($browsers, $total_amount);
		$this->sort_and_format_data($countries, $total_amount);

		return [
			'orders' => ['number' => $number_of_orders, 'total' => wc_price($total_amount), 'average' => wc_price($average_order_amount)],
			'sources' => array_values($sources),
			'campaigns' => array_values($campaigns),
			'mediums' => array_values($mediums),
			'contents' => array_values($contents),
			'terms' => array_values($terms),
			'utm_groups' => array_values($utm_groups),
			'referrers' => array_values($referrers),
			'lps' => array_values($lps),
			'devices' => array_values($devices),
			'browsers' => array_values($browsers),
			'countries' => array_values($countries),
		];
	}



	private function fetch_filtered_orders($data, $meta_key)
	{
		$from_date = date('Y-m-d', strtotime(sanitize_text_field($data['start_date'])));
		$to_date = date('Y-m-d', strtotime('+1 day', strtotime(sanitize_text_field($data['end_date']))));

		$args = array(
			'date_created' => strtotime($from_date) . '...' . strtotime($to_date),
			'status' => array('wc-pending', 'wc-processing', 'wc-completed', 'wc-on-hold'),
			'limit' => 29999,
		);

		// Add meta query for additional filters
		$meta_query = array('relation' => 'AND');

		// List of valid filter keys that correspond to meta keys
		$valid_filters = array(
			'source' => '_' . $meta_key . '_source',
			'campaign' => '_' . $meta_key . '_campaign',
			'content' => '_' . $meta_key . '_content',
			'medium' => '_' . $meta_key . '_medium',
			'term' => '_' . $meta_key . '_term',
			'referrer' => '_' . $meta_key . '_referrer',
			'lp' => '_' . $meta_key . '_lp',
			'device' => '_' . $meta_key . '_device',
			'browser' => '_' . $meta_key . '_browser',
		);

		foreach ($valid_filters as $filter_key => $meta_key) {
			if (isset($data[$filter_key]) && !empty($data[$filter_key])) {
				$meta_query[] = array(
					'key' => $meta_key,
					'value' => sanitize_text_field($data[$filter_key]),
					'compare' => '=',
				);
			}
		}

		if (count($meta_query) > 1) {
			$args['meta_query'] = $meta_query;
		}

		// Handle country code filter
		if (isset($data['country']) && !empty($data['country'])) {
			$args['billing_country'] = sanitize_text_field($data['country']);
		}

		return wc_get_orders($args);
	}

	private function fetch_non_hpos_filtered_orders($data, $meta_key)
	{
		global $wpdb;

		$from_date = date('Y-m-d', strtotime(sanitize_text_field($data['start_date'])));
		$to_date = date('Y-m-d', strtotime('+1 day', strtotime(sanitize_text_field($data['end_date']))));

		$query = $wpdb->prepare(
			"SELECT DISTINCT p.ID
			FROM {$wpdb->posts} p
			JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
			WHERE p.post_type = 'shop_order'
			AND p.post_status IN ('wc-pending', 'wc-processing', 'wc-completed', 'wc-on-hold')
			AND p.post_date >= %s AND p.post_date < %s",
			$from_date,
			$to_date
		);

		$valid_filters = array(
			'source' => '_' . $meta_key . '_source',
			'campaign' => '_' . $meta_key . '_campaign',
			'content' => '_' . $meta_key . '_content',
			'medium' => '_' . $meta_key . '_medium',
			'term' => '_' . $meta_key . '_term',
			'referrer' => '_' . $meta_key . '_referrer',
			'lp' => '_' . $meta_key . '_lp',
			'device' => '_' . $meta_key . '_device',
			'browser' => '_' . $meta_key . '_browser',
		);

		foreach ($valid_filters as $filter_key => $meta_key) {
			if (isset($data[$filter_key]) && !empty($data[$filter_key])) {
				$query .= $wpdb->prepare(
					" AND p.ID IN (
						SELECT post_id FROM {$wpdb->postmeta}
						WHERE meta_key = %s AND meta_value = %s
					)",
					$meta_key,
					sanitize_text_field($data[$filter_key])
				);
			}
		}

		if (isset($data['country']) && !empty($data['country'])) {
			$query .= $wpdb->prepare(
				" AND p.ID IN (
					SELECT post_id FROM {$wpdb->postmeta}
					WHERE meta_key = '_billing_country' AND meta_value = %s
				)",
				sanitize_text_field($data['country'])
			);
		}

		$order_ids = $wpdb->get_col($query);
		$orders = array_map('wc_get_order', $order_ids);

		return $orders;
	}

	private function process_utm_data(&$data, $order_meta, $key, $order_total)
	{
		if (isset($order_meta[$key])) {
			$value = $order_meta[$key];
			if ($value) {
				$data[$value] = array_merge(
					$data[$value] ?? [],
					[
						'title' => $value,
						'num_of_orders' => ($data[$value]['num_of_orders'] ?? 0) + 1,
						'total_amount' => ($data[$value]['total_amount'] ?? 0) + $order_total
					]
				);
			}
		}
	}

	private function sort_and_format_data(&$data, $total_amount)
	{
		array_multisort(array_column($data, 'total_amount'), SORT_DESC, $data);
		foreach ($data as &$item) {
			$item['percentage'] = round(($item['total_amount'] / $total_amount) * 100, 2);
			$item['total_amount'] = wc_price($item['total_amount']);
		}
	}

	public function format_order($order_id)
	{
		$order = wc_get_order($order_id);
		//		$touch_type = sanitize_text_field($data['touch']);
		$meta_key = "wpg_last"; // .$touch_type;

		$product_list = array();
		foreach ($order->get_items() as $item_id => $item) {
			$product_name = $item->get_name();
			$quantity = $item->get_quantity();
			$product_list[] = $product_name . ' x ' . $quantity;
		}
		$product_list_string = implode(", ", $product_list);

		$st_meta = $order->get_meta($meta_key, true);

		$keys_to_exclude = array('utm_source', 'visit_date', 'custom_params');

		$formattedString = '';
		foreach ($st_meta as $key => $value) {
			if (in_array($key, $keys_to_exclude)) {
				continue;
			}
			$formattedString .= '<strong>' . htmlspecialchars(ucwords(str_replace('_', ' ', $key))) . ':</strong> ' . htmlspecialchars($value) . '<br>';
		}

		return array(
			'first_visit' => isset($st_meta['visit_date']) ? date_i18n('M j Y, H:i', strtotime($st_meta['visit_date'])) : '-',
			'order_date' =>  date_i18n('M j Y, H:i', strtotime($order->get_date_created())),
			'order_id' => "<a target='_blank' href='" . get_edit_post_link($order_id) . "'>#" . $order_id . "</a>",
			'products' => $product_list_string,
			'order_total' => $order->get_formatted_order_total(),
			'utm_source' => isset($st_meta['utm_source']) ? $st_meta['utm_source'] : '-',
			'utm_details' => $formattedString,
			'order_status' => wc_get_order_status_name($order->get_status()),
		);
	}
}