CustomerFieldsFactory.php
11 months ago
CustomerOrderFieldsFactory.php
1 year ago
CustomerReviewFieldsFactory.php
1 year ago
CustomerSubscriptionFieldsFactory.php
11 months ago
OrderFieldsFactory.php
2 months ago
TermOptionsBuilder.php
2 years ago
TermParentsLoader.php
1 year ago
index.php
3 years ago
CustomerOrderFieldsFactory.php
415 lines
| 1 | <?php declare(strict_types = 1); |
| 2 | |
| 3 | namespace MailPoet\Automation\Integrations\WooCommerce\Fields; |
| 4 | |
| 5 | if (!defined('ABSPATH')) exit; |
| 6 | |
| 7 | |
| 8 | use DateTimeImmutable; |
| 9 | use DateTimeZone; |
| 10 | use MailPoet\Automation\Engine\Data\Field; |
| 11 | use MailPoet\Automation\Integrations\WooCommerce\Payloads\CustomerPayload; |
| 12 | use MailPoet\Automation\Integrations\WooCommerce\WooCommerce; |
| 13 | use WC_Customer; |
| 14 | use WC_Order; |
| 15 | use WC_Order_Item_Product; |
| 16 | use WC_Product; |
| 17 | |
| 18 | class CustomerOrderFieldsFactory { |
| 19 | /** @var WooCommerce */ |
| 20 | private $wooCommerce; |
| 21 | |
| 22 | /** @var TermOptionsBuilder */ |
| 23 | private $termOptionsBuilder; |
| 24 | |
| 25 | /** @var TermParentsLoader */ |
| 26 | private $termParentsLoader; |
| 27 | |
| 28 | public function __construct( |
| 29 | WooCommerce $wooCommerce, |
| 30 | TermOptionsBuilder $termOptionsBuilder, |
| 31 | TermParentsLoader $termParentsLoader |
| 32 | ) { |
| 33 | $this->wooCommerce = $wooCommerce; |
| 34 | $this->termOptionsBuilder = $termOptionsBuilder; |
| 35 | $this->termParentsLoader = $termParentsLoader; |
| 36 | } |
| 37 | |
| 38 | /** @return Field[] */ |
| 39 | public function getFields(): array { |
| 40 | return [ |
| 41 | new Field( |
| 42 | 'woocommerce:customer:spent-total', |
| 43 | Field::TYPE_NUMBER, |
| 44 | __('Total spent', 'mailpoet'), |
| 45 | function (CustomerPayload $payload, array $params = []) { |
| 46 | $customer = $payload->getCustomer(); |
| 47 | $inTheLastSeconds = isset($params['in_the_last']) ? (int)$params['in_the_last'] : null; |
| 48 | if (!$customer) { |
| 49 | $order = $payload->getOrder(); |
| 50 | return $order && $this->isInTheLastSeconds($order, $inTheLastSeconds) ? $payload->getTotalSpent() : 0.0; |
| 51 | } |
| 52 | return $inTheLastSeconds === null |
| 53 | ? $payload->getTotalSpent() |
| 54 | : $this->getRecentSpentTotal($customer, $inTheLastSeconds); |
| 55 | }, |
| 56 | [ |
| 57 | 'params' => ['in_the_last'], |
| 58 | ] |
| 59 | ), |
| 60 | new Field( |
| 61 | 'woocommerce:customer:spent-average', |
| 62 | Field::TYPE_NUMBER, |
| 63 | __('Average spent', 'mailpoet'), |
| 64 | function (CustomerPayload $payload, array $params = []) { |
| 65 | $customer = $payload->getCustomer(); |
| 66 | $inTheLastSeconds = isset($params['in_the_last']) ? (int)$params['in_the_last'] : null; |
| 67 | |
| 68 | if (!$customer) { |
| 69 | $order = $payload->getOrder(); |
| 70 | return $order && $this->isInTheLastSeconds($order, $inTheLastSeconds) ? $payload->getAverageSpent() : 0.0; |
| 71 | } |
| 72 | |
| 73 | if ($inTheLastSeconds === null) { |
| 74 | return $payload->getAverageSpent(); |
| 75 | } else { |
| 76 | $totalSpent = $this->getRecentSpentTotal($customer, $inTheLastSeconds); |
| 77 | $orderCount = $this->getRecentOrderCount($customer, $inTheLastSeconds); |
| 78 | return $orderCount > 0 ? ($totalSpent / $orderCount) : 0.0; |
| 79 | } |
| 80 | }, |
| 81 | [ |
| 82 | 'params' => ['in_the_last'], |
| 83 | ] |
| 84 | ), |
| 85 | new Field( |
| 86 | 'woocommerce:customer:order-count', |
| 87 | Field::TYPE_INTEGER, |
| 88 | __('Order count', 'mailpoet'), |
| 89 | function (CustomerPayload $payload, array $params = []) { |
| 90 | $customer = $payload->getCustomer(); |
| 91 | $inTheLastSeconds = isset($params['in_the_last']) ? (int)$params['in_the_last'] : null; |
| 92 | if (!$customer) { |
| 93 | $order = $payload->getOrder(); |
| 94 | return $order && $this->isInTheLastSeconds($order, $inTheLastSeconds) ? $payload->getOrderCount() : 0; |
| 95 | } |
| 96 | return $inTheLastSeconds === null |
| 97 | ? $payload->getOrderCount() |
| 98 | : $this->getRecentOrderCount($customer, $inTheLastSeconds); |
| 99 | }, |
| 100 | [ |
| 101 | 'params' => ['in_the_last'], |
| 102 | ] |
| 103 | ), |
| 104 | new Field( |
| 105 | 'woocommerce:customer:first-paid-order-date', |
| 106 | Field::TYPE_DATETIME, |
| 107 | __('First paid order date', 'mailpoet'), |
| 108 | function (CustomerPayload $payload) { |
| 109 | $customer = $payload->getCustomer(); |
| 110 | if (!$customer) { |
| 111 | $order = $payload->getOrder(); |
| 112 | return $order && $order->is_paid() ? $order->get_date_created() : null; |
| 113 | } |
| 114 | return $this->getPaidOrderDate($customer, true); |
| 115 | } |
| 116 | ), |
| 117 | new Field( |
| 118 | 'woocommerce:customer:last-paid-order-date', |
| 119 | Field::TYPE_DATETIME, |
| 120 | __('Last paid order date', 'mailpoet'), |
| 121 | function (CustomerPayload $payload) { |
| 122 | $customer = $payload->getCustomer(); |
| 123 | if (!$customer) { |
| 124 | $order = $payload->getOrder(); |
| 125 | return $order && $order->is_paid() ? $order->get_date_created() : null; |
| 126 | } |
| 127 | return $this->getPaidOrderDate($customer, false); |
| 128 | } |
| 129 | ), |
| 130 | new Field( |
| 131 | 'woocommerce:customer:purchased-categories', |
| 132 | Field::TYPE_ENUM_ARRAY, |
| 133 | __('Purchased categories', 'mailpoet'), |
| 134 | function (CustomerPayload $payload, array $params = []) { |
| 135 | $customer = $payload->getCustomer(); |
| 136 | $inTheLastSeconds = isset($params['in_the_last']) ? (int)$params['in_the_last'] : null; |
| 137 | if (!$customer) { |
| 138 | $order = $payload->getOrder(); |
| 139 | $items = $order && $order->is_paid() && $this->isInTheLastSeconds($order, $inTheLastSeconds) ? $order->get_items() : []; |
| 140 | $ids = []; |
| 141 | foreach ($items as $item) { |
| 142 | $product = $item instanceof WC_Order_Item_Product ? $item->get_product() : null; |
| 143 | $ids = array_merge($ids, $product instanceof WC_Product ? $product->get_category_ids() : []); |
| 144 | } |
| 145 | $ids = array_unique($ids); |
| 146 | } else { |
| 147 | $ids = $this->getOrderProductTermIds($customer, 'product_cat', $inTheLastSeconds); |
| 148 | } |
| 149 | $ids = array_merge($ids, $this->termParentsLoader->getParentIds($ids)); |
| 150 | sort($ids); |
| 151 | return $ids; |
| 152 | }, |
| 153 | [ |
| 154 | 'options' => $this->termOptionsBuilder->getTermOptions('product_cat'), |
| 155 | 'params' => ['in_the_last'], |
| 156 | ] |
| 157 | ), |
| 158 | new Field( |
| 159 | 'woocommerce:customer:purchased-tags', |
| 160 | Field::TYPE_ENUM_ARRAY, |
| 161 | __('Purchased tags', 'mailpoet'), |
| 162 | function (CustomerPayload $payload, array $params = []) { |
| 163 | $customer = $payload->getCustomer(); |
| 164 | $inTheLastSeconds = isset($params['in_the_last']) ? (int)$params['in_the_last'] : null; |
| 165 | if (!$customer) { |
| 166 | $order = $payload->getOrder(); |
| 167 | $items = $order && $order->is_paid() && $this->isInTheLastSeconds($order, $inTheLastSeconds) ? $order->get_items() : []; |
| 168 | $ids = []; |
| 169 | foreach ($items as $item) { |
| 170 | $product = $item instanceof WC_Order_Item_Product ? $item->get_product() : null; |
| 171 | $ids = array_merge($ids, $product instanceof WC_Product ? $product->get_tag_ids() : []); |
| 172 | } |
| 173 | $ids = array_unique($ids); |
| 174 | } else { |
| 175 | $ids = $this->getOrderProductTermIds($customer, 'product_tag', $inTheLastSeconds); |
| 176 | } |
| 177 | sort($ids); |
| 178 | return $ids; |
| 179 | }, |
| 180 | [ |
| 181 | 'options' => $this->termOptionsBuilder->getTermOptions('product_tag'), |
| 182 | 'params' => ['in_the_last'], |
| 183 | ] |
| 184 | ), |
| 185 | ]; |
| 186 | } |
| 187 | |
| 188 | private function getRecentSpentTotal(WC_Customer $customer, int $inTheLastSeconds): float { |
| 189 | global $wpdb; |
| 190 | $statuses = array_map(function (string $status) { |
| 191 | return "wc-$status"; |
| 192 | }, $this->wooCommerce->wcGetIsPaidStatuses()); |
| 193 | |
| 194 | if ($this->wooCommerce->isWooCommerceCustomOrdersTableEnabled()) { |
| 195 | return (float)$wpdb->get_var( |
| 196 | $wpdb->prepare( |
| 197 | ' |
| 198 | SELECT SUM(o.total_amount) |
| 199 | FROM %i o |
| 200 | WHERE o.customer_id = %d |
| 201 | AND o.status IN (' . implode(',', array_fill(0, count($statuses), '%s')) . ') |
| 202 | AND o.date_created_gmt >= DATE_SUB(current_timestamp, INTERVAL %d SECOND) |
| 203 | ', |
| 204 | array_merge( |
| 205 | [ |
| 206 | $wpdb->prefix . 'wc_orders', |
| 207 | $customer->get_id(), |
| 208 | ], |
| 209 | $statuses, |
| 210 | [$inTheLastSeconds] |
| 211 | ) |
| 212 | ) |
| 213 | ); |
| 214 | } |
| 215 | |
| 216 | return (float)$wpdb->get_var( |
| 217 | $wpdb->prepare( |
| 218 | " |
| 219 | SELECT SUM(pm_total.meta_value) |
| 220 | FROM {$wpdb->posts} p |
| 221 | LEFT JOIN {$wpdb->postmeta} pm_user ON p.ID = pm_user.post_id AND pm_user.meta_key = '_customer_user' |
| 222 | LEFT JOIN {$wpdb->postmeta} pm_total ON p.ID = pm_total.post_id AND pm_total.meta_key = '_order_total' |
| 223 | WHERE p.post_type = 'shop_order' |
| 224 | AND p.post_status IN (" . implode(',', array_fill(0, count($statuses), '%s')) . ") |
| 225 | AND pm_user.meta_value = %d |
| 226 | AND p.post_date_gmt >= DATE_SUB(current_timestamp, INTERVAL %d SECOND) |
| 227 | ", |
| 228 | array_merge( |
| 229 | $statuses, |
| 230 | [$customer->get_id(), $inTheLastSeconds] |
| 231 | ) |
| 232 | ) |
| 233 | ); |
| 234 | } |
| 235 | |
| 236 | private function getRecentOrderCount(WC_Customer $customer, int $inTheLastSeconds): int { |
| 237 | global $wpdb; |
| 238 | $statuses = array_keys($this->wooCommerce->wcGetOrderStatuses()); |
| 239 | |
| 240 | if ($this->wooCommerce->isWooCommerceCustomOrdersTableEnabled()) { |
| 241 | return (int)$wpdb->get_var( |
| 242 | $wpdb->prepare( |
| 243 | ' |
| 244 | SELECT COUNT(o.id) |
| 245 | FROM %i o |
| 246 | WHERE o.customer_id = %d |
| 247 | AND o.status IN (' . implode(',', array_fill(0, count($statuses), '%s')) . ') |
| 248 | AND o.date_created_gmt >= DATE_SUB(current_timestamp, INTERVAL %d SECOND) |
| 249 | ', |
| 250 | array_merge( |
| 251 | [ |
| 252 | $wpdb->prefix . 'wc_orders', |
| 253 | $customer->get_id(), |
| 254 | ], |
| 255 | $statuses, |
| 256 | [$inTheLastSeconds] |
| 257 | ) |
| 258 | ) |
| 259 | ); |
| 260 | } |
| 261 | |
| 262 | return (int)$wpdb->get_var( |
| 263 | $wpdb->prepare( |
| 264 | " |
| 265 | SELECT COUNT(p.ID) |
| 266 | FROM {$wpdb->posts} p |
| 267 | LEFT JOIN {$wpdb->postmeta} pm_user ON p.ID = pm_user.post_id AND pm_user.meta_key = '_customer_user' |
| 268 | WHERE p.post_type = 'shop_order' |
| 269 | AND p.post_status IN (" . implode(',', array_fill(0, count($statuses), '%s')) . ") |
| 270 | AND pm_user.meta_value = %d |
| 271 | AND p.post_date_gmt >= DATE_SUB(current_timestamp, INTERVAL %d SECOND) |
| 272 | ", |
| 273 | array_merge( |
| 274 | $statuses, |
| 275 | [ |
| 276 | $customer->get_id(), |
| 277 | $inTheLastSeconds, |
| 278 | ] |
| 279 | ) |
| 280 | ) |
| 281 | ); |
| 282 | } |
| 283 | |
| 284 | private function getPaidOrderDate(WC_Customer $customer, bool $fetchFirst): ?DateTimeImmutable { |
| 285 | global $wpdb; |
| 286 | $sorting = $fetchFirst ? 'ASC' : 'DESC'; |
| 287 | $statuses = array_map(function (string $status) { |
| 288 | return "wc-$status"; |
| 289 | }, $this->wooCommerce->wcGetIsPaidStatuses()); |
| 290 | |
| 291 | if ($this->wooCommerce->isWooCommerceCustomOrdersTableEnabled()) { |
| 292 | $date = $wpdb->get_var( |
| 293 | $wpdb->prepare( |
| 294 | ' |
| 295 | SELECT o.date_created_gmt |
| 296 | FROM %i o |
| 297 | WHERE o.customer_id = %d |
| 298 | AND o.status IN (' . implode(',', array_fill(0, count($statuses), '%s')) . ') |
| 299 | AND o.total_amount > 0 |
| 300 | ORDER BY o.date_created_gmt ' . $sorting /* phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The argument is safe. */ . ' |
| 301 | LIMIT 1 |
| 302 | ', |
| 303 | array_merge( |
| 304 | [ |
| 305 | $wpdb->prefix . 'wc_orders', |
| 306 | $customer->get_id(), |
| 307 | ], |
| 308 | $statuses |
| 309 | ) |
| 310 | ) |
| 311 | ); |
| 312 | } else { |
| 313 | $date = $wpdb->get_var( |
| 314 | $wpdb->prepare( |
| 315 | " |
| 316 | SELECT p.post_date_gmt |
| 317 | FROM {$wpdb->prefix}posts p |
| 318 | LEFT JOIN {$wpdb->prefix}postmeta pm_total ON p.ID = pm_total.post_id AND pm_total.meta_key = '_order_total' |
| 319 | LEFT JOIN {$wpdb->prefix}postmeta pm_user ON p.ID = pm_user.post_id AND pm_user.meta_key = '_customer_user' |
| 320 | WHERE p.post_type = 'shop_order' |
| 321 | AND p.post_status IN (" . implode(',', array_fill(0, count($statuses), '%s')) . ") |
| 322 | AND pm_user.meta_value = %d |
| 323 | AND pm_total.meta_value > 0 |
| 324 | ORDER BY p.post_date_gmt " . $sorting /* phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The argument is safe. */ . " |
| 325 | LIMIT 1 |
| 326 | ", |
| 327 | array_merge( |
| 328 | $statuses, |
| 329 | [$customer->get_id()] |
| 330 | ) |
| 331 | ) |
| 332 | ); |
| 333 | } |
| 334 | |
| 335 | return $date ? new DateTimeImmutable($date, new DateTimeZone('GMT')) : null; |
| 336 | } |
| 337 | |
| 338 | private function getOrderProductTermIds(WC_Customer $customer, string $taxonomy, ?int $inTheLastSeconds = null): array { |
| 339 | global $wpdb; |
| 340 | |
| 341 | $statuses = array_map(function (string $status) { |
| 342 | return "wc-$status"; |
| 343 | }, $this->wooCommerce->wcGetIsPaidStatuses()); |
| 344 | $statusesPlaceholder = implode(',', array_fill(0, count($statuses), '%s')); |
| 345 | |
| 346 | // get all product categories that the customer has purchased |
| 347 | if ($this->wooCommerce->isWooCommerceCustomOrdersTableEnabled()) { |
| 348 | $inTheLastFilter = isset($inTheLastSeconds) ? 'AND o.date_created_gmt >= DATE_SUB(current_timestamp, INTERVAL %d SECOND)' : ''; |
| 349 | |
| 350 | $orderIdsSubquery = " |
| 351 | SELECT o.id |
| 352 | FROM %i o |
| 353 | WHERE o.status IN ($statusesPlaceholder) |
| 354 | AND o.customer_id = %d |
| 355 | $inTheLastFilter |
| 356 | "; |
| 357 | $orderIdsSubqueryArgs = array_merge( |
| 358 | [$wpdb->prefix . 'wc_orders'], |
| 359 | $statuses, |
| 360 | [$customer->get_id()], |
| 361 | $inTheLastSeconds ? [$inTheLastSeconds] : [] |
| 362 | ); |
| 363 | } else { |
| 364 | $inTheLastFilter = isset($inTheLastSeconds) ? 'AND p.post_date_gmt >= DATE_SUB(current_timestamp, INTERVAL %d SECOND)' : ''; |
| 365 | |
| 366 | $orderIdsSubquery = " |
| 367 | SELECT p.ID |
| 368 | FROM {$wpdb->posts} p |
| 369 | LEFT JOIN {$wpdb->postmeta} pm_user ON p.ID = pm_user.post_id AND pm_user.meta_key = '_customer_user' |
| 370 | WHERE p.post_type = 'shop_order' |
| 371 | AND p.post_status IN ($statusesPlaceholder) |
| 372 | AND pm_user.meta_value = %d |
| 373 | $inTheLastFilter |
| 374 | "; |
| 375 | $orderIdsSubqueryArgs = array_merge( |
| 376 | $statuses, |
| 377 | [$customer->get_id()], |
| 378 | $inTheLastSeconds ? [$inTheLastSeconds] : [] |
| 379 | ); |
| 380 | } |
| 381 | |
| 382 | $result = $wpdb->get_col( |
| 383 | // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- The number of replacements is dynamic. |
| 384 | $wpdb->prepare( |
| 385 | " |
| 386 | SELECT DISTINCT tt.term_id |
| 387 | FROM {$wpdb->term_taxonomy} tt |
| 388 | JOIN %i AS oi ON oi.order_id IN (" . $orderIdsSubquery . /* phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The subquery uses placeholders. */ ") AND oi.order_item_type = 'line_item' |
| 389 | JOIN %i AS pid ON oi.order_item_id = pid.order_item_id AND pid.meta_key = '_product_id' |
| 390 | JOIN {$wpdb->posts} p ON pid.meta_value = p.ID |
| 391 | JOIN {$wpdb->term_relationships} tr ON IF(p.post_type = 'product_variation', p.post_parent, p.ID) = tr.object_id AND tr.term_taxonomy_id = tt.term_taxonomy_id |
| 392 | WHERE tt.taxonomy = %s |
| 393 | ORDER BY tt.term_id ASC |
| 394 | ", |
| 395 | array_merge( |
| 396 | [$wpdb->prefix . 'woocommerce_order_items'], |
| 397 | $orderIdsSubqueryArgs, |
| 398 | [ |
| 399 | $wpdb->prefix . 'woocommerce_order_itemmeta', |
| 400 | (string)($taxonomy), |
| 401 | ] |
| 402 | ) |
| 403 | ) |
| 404 | ); |
| 405 | return array_map('intval', $result); |
| 406 | } |
| 407 | |
| 408 | private function isInTheLastSeconds(WC_Order $order, ?int $inTheLastSeconds): bool { |
| 409 | if ($inTheLastSeconds === null) { |
| 410 | return true; |
| 411 | } |
| 412 | return $order->get_date_created() >= new DateTimeImmutable("-$inTheLastSeconds seconds"); |
| 413 | } |
| 414 | } |
| 415 |