render_template('orders-export.php', array( 'available_fields' => $this->get_available_fields(), 'field_map' => $this->get_field_map(), 'selectable_statuses' => $this->selectable_statuses(), 'selected_statuses' => $this->get_selected_statuses(), 'recent_jobs' => $this->recent_jobs('orders_export', 10), 'last_first_id' => is_array($last_range) && !empty($last_range['first_id']) ? (int) $last_range['first_id'] : 1, 'last_last_id' => is_array($last_range) && !empty($last_range['last_id']) ? (int) $last_range['last_id'] : 0, 'last_result' => is_array($last_result) ? $last_result : array(), )); } private function get_export_dir() { $uploads = wp_upload_dir(); $base_dir = isset($uploads['basedir']) ? (string) $uploads['basedir'] : trailingslashit(WP_CONTENT_DIR) . 'uploads'; $base_url = isset($uploads['baseurl']) ? (string) $uploads['baseurl'] : trailingslashit(WP_CONTENT_URL) . 'uploads'; $path = trailingslashit($base_dir) . self::EXPORT_SUBDIR; $url = trailingslashit($base_url) . self::EXPORT_SUBDIR; if (!file_exists($path)) { wp_mkdir_p($path); } return array('path' => $path, 'url' => $url); } public function get_available_fields() { return array( 'OrderId' => array('label' => __('Order ID', 'ai-eshop-optimizer'), 'default_header' => 'OrderId'), 'TrnDate' => array('label' => __('Transaction date (YYYYMMDD)', 'ai-eshop-optimizer'), 'default_header' => 'TrnDate'), 'Series' => array('label' => __('Series', 'ai-eshop-optimizer'), 'default_header' => 'Series'), 'ceMail' => array('label' => __('Customer email', 'ai-eshop-optimizer'), 'default_header' => 'ceMail'), 'cName' => array('label' => __('Customer name', 'ai-eshop-optimizer'), 'default_header' => 'cName'), 'cCompanyName' => array('label' => __('Company name', 'ai-eshop-optimizer'), 'default_header' => 'cCompanyName'), 'cVat' => array('label' => __('VAT number', 'ai-eshop-optimizer'), 'default_header' => 'cVat'), 'cVatStatus' => array('label' => __('VAT status', 'ai-eshop-optimizer'), 'default_header' => 'cVatStatus'), 'ExpVal1' => array('label' => __('Export value 1', 'ai-eshop-optimizer'), 'default_header' => 'ExpVal1'), 'ExpVal2' => array('label' => __('Export value 2', 'ai-eshop-optimizer'), 'default_header' => 'ExpVal2'), 'cDoy' => array('label' => __('Tax office', 'ai-eshop-optimizer'), 'default_header' => 'cDoy'), 'billAddress' => array('label' => __('Billing address', 'ai-eshop-optimizer'), 'default_header' => 'billAddress'), 'billCity' => array('label' => __('Billing city', 'ai-eshop-optimizer'), 'default_header' => 'billCity'), 'billZip' => array('label' => __('Billing postcode', 'ai-eshop-optimizer'), 'default_header' => 'billZip'), 'billCountry' => array('label' => __('Billing country', 'ai-eshop-optimizer'), 'default_header' => 'billCountry'), 'shipAddress' => array('label' => __('Shipping address', 'ai-eshop-optimizer'), 'default_header' => 'shipAddress'), 'shipCity' => array('label' => __('Shipping city', 'ai-eshop-optimizer'), 'default_header' => 'shipCity'), 'shipZip' => array('label' => __('Shipping postcode', 'ai-eshop-optimizer'), 'default_header' => 'shipZip'), 'shipCountry' => array('label' => __('Shipping country', 'ai-eshop-optimizer'), 'default_header' => 'shipCountry'), 'mSKU' => array('label' => __('Item SKU', 'ai-eshop-optimizer'), 'default_header' => 'mSKU'), 'cPhone01' => array('label' => __('Phone 1', 'ai-eshop-optimizer'), 'default_header' => 'cPhone01'), 'cPhone02' => array('label' => __('Phone 2', 'ai-eshop-optimizer'), 'default_header' => 'cPhone02'), 'PrimaryQty' => array('label' => __('Quantity', 'ai-eshop-optimizer'), 'default_header' => 'PrimaryQty'), 'Price' => array('label' => __('Unit price', 'ai-eshop-optimizer'), 'default_header' => 'Price'), 'DiscVal' => array('label' => __('Discount value', 'ai-eshop-optimizer'), 'default_header' => 'DiscVal'), 'cPostalCost' => array('label' => __('Shipping cost', 'ai-eshop-optimizer'), 'default_header' => 'cPostalCost'), 'cAntikatavoliCost' => array('label' => __('COD fee', 'ai-eshop-optimizer'), 'default_header' => 'cAntikatavoliCost'), 'LineValue' => array('label' => __('Line value', 'ai-eshop-optimizer'), 'default_header' => 'LineValue'), 'LineVatValue' => array('label' => __('Line VAT value', 'ai-eshop-optimizer'), 'default_header' => 'LineVatValue'), 'cPayment' => array('label' => __('Payment method', 'ai-eshop-optimizer'), 'default_header' => 'cPayment'), 'cShipment' => array('label' => __('Shipping method', 'ai-eshop-optimizer'), 'default_header' => 'cShipment'), ); } public function get_field_map() { $stored = get_option(self::FIELDS_OPTION, null); if (!is_array($stored) || empty($stored)) { $out = array(); foreach ($this->get_available_fields() as $key => $info) { $out[] = array('key' => $key, 'header' => $info['default_header'], 'enabled' => true); } return $out; } return $stored; } public function ajax_save_fields() { AIEO_DMM::verify_ajax_request(); $raw = isset($_POST['fields']) && is_array($_POST['fields']) ? map_deep(wp_unslash($_POST['fields']), 'sanitize_text_field') : array(); $available = $this->get_available_fields(); $out = array(); foreach ($raw as $entry) { if (!is_array($entry) || empty($entry['key'])) { continue; } $key = (string) $entry['key']; if (!isset($available[$key])) { continue; } $out[] = array( 'key' => $key, 'header' => isset($entry['header']) ? sanitize_text_field($entry['header']) : $available[$key]['default_header'], 'enabled' => !empty($entry['enabled']), ); } update_option(self::FIELDS_OPTION, $out, false); wp_send_json_success(array('message' => __('Field map saved.', 'ai-eshop-optimizer'))); } public function ajax_start() { AIEO_DMM::verify_ajax_request(); if (!AIEO_Order_Store::hpos_readable()) { wp_send_json_error(array('message' => __('Requires WooCommerce HPOS (high-performance order storage). Activate it under WooCommerce → Settings → Advanced → Features.', 'ai-eshop-optimizer'))); } $first_id = isset($_POST['first_order_id']) ? absint(wp_unslash($_POST['first_order_id'])) : 1; $last_id = isset($_POST['last_order_id']) ? absint(wp_unslash($_POST['last_order_id'])) : 0; $format = isset($_POST['format']) && sanitize_key(wp_unslash($_POST['format'])) === 'xml' ? 'xml' : 'csv'; $cache_mode_allowed = array('stable_url', 'version_query', 'unique_filename'); $cache_mode = isset($_POST['cache_mode']) ? sanitize_key(wp_unslash($_POST['cache_mode'])) : 'stable_url'; if (!in_array($cache_mode, $cache_mode_allowed, true)) { $cache_mode = 'stable_url'; } $selectable = array_keys($this->selectable_statuses()); $req_statuses = (isset($_POST['statuses']) && is_array($_POST['statuses'])) ? array_map('sanitize_text_field', wp_unslash($_POST['statuses'])) : array(); $statuses = array_values(array_intersect($req_statuses, $selectable)); if (empty($statuses)) { $statuses = $this->get_selected_statuses(); } update_option(self::STATUSES_OPTION, $statuses, false); $args = array('limit' => -1, 'type' => 'shop_order', 'return' => 'ids'); global $wpdb; $table_orders = $this->get_orders_table(); $where = array('type = %s'); $params = array('shop_order'); $where[] = 'id >= %d'; $params[] = $first_id; if ($last_id > 0) { $where[] = 'id <= %d'; $params[] = $last_id; } $sql = "SELECT COUNT(*) FROM {$table_orders} WHERE " . implode(' AND ', $where) . $this->status_in_sql($statuses); $total = (int) $wpdb->get_var($wpdb->prepare($sql, $params)); if ($total <= 0) { wp_send_json_error(array('message' => __('No orders match the range.', 'ai-eshop-optimizer'))); } update_option(self::LAST_RANGE_OPTION, array( 'first_id' => (int) $first_id, 'last_id' => (int) $last_id, 'saved_at' => current_time('mysql', true), ), false); $dir = $this->get_export_dir(); $job_id = wp_generate_password(16, false, false); $ext = ($format === 'xml') ? 'xml' : 'csv'; if ($cache_mode === 'unique_filename') { $filename = 'orders-export-' . $job_id . '.' . $ext; } else { $filename = 'exported_orders.' . $ext; } $path = trailingslashit($dir['path']) . $filename; $url = trailingslashit($dir['url']) . $filename; $headers = $this->softone_headers(); $h = fopen($path, 'w'); if (!$h) { wp_send_json_error(array('message' => __('Cannot create export file.', 'ai-eshop-optimizer'))); } if ($format === 'xml') { fwrite($h, "\n\n"); } else { fputcsv($h, $headers); } fclose($h); global $wpdb; $jobs = $wpdb->prefix . 'aieo_dmm_export_jobs'; $wpdb->insert($jobs, array( 'job_id' => $job_id, 'type' => 'orders_export', 'status' => 'running', 'total' => $total, 'processed' => 0, 'filename' => $filename, 'params' => wp_json_encode(array( 'first_id' => $first_id, 'last_id' => $last_id, 'format' => $format, 'cache_mode' => $cache_mode, 'statuses' => $statuses, 'path' => $path, 'url' => $url, 'cursor' => max(0, $first_id - 1), )), 'user_id' => get_current_user_id(), ), array('%s', '%s', '%s', '%d', '%d', '%s', '%s', '%d')); wp_send_json_success(array('job_id' => $job_id, 'total' => $total)); } public function ajax_process() { AIEO_DMM::verify_ajax_request(); if (!AIEO_Order_Store::hpos_readable()) { wp_send_json_error(array('message' => __('Requires WooCommerce HPOS (high-performance order storage). Activate it under WooCommerce → Settings → Advanced → Features.', 'ai-eshop-optimizer'))); } $job_id = isset($_POST['job_id']) ? sanitize_text_field(wp_unslash($_POST['job_id'])) : ''; global $wpdb; $jobs = $wpdb->prefix . 'aieo_dmm_export_jobs'; $job = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$jobs} WHERE job_id = %s", $job_id), ARRAY_A); if (!$job || $job['type'] !== 'orders_export') { wp_send_json_error(array('message' => __('Invalid job.', 'ai-eshop-optimizer'))); } $params = json_decode($job['params'], true); if (!is_array($params)) { wp_send_json_error(array('message' => __('Corrupted job.', 'ai-eshop-optimizer'))); } $first_id = (int) $params['first_id']; $last_id = (int) $params['last_id']; $cursor = (int) $params['cursor']; $path = (string) $params['path']; $format = isset($params['format']) && $params['format'] === 'xml' ? 'xml' : 'csv'; $statuses = (isset($params['statuses']) && is_array($params['statuses'])) ? $params['statuses'] : array(); $batch_size = 100; $table_orders = $this->get_orders_table(); $where = 'type = %s AND id > %d'; $args = array('shop_order', $cursor); if ($last_id > 0) { $where .= ' AND id <= %d'; $args[] = $last_id; } $where .= $this->status_in_sql($statuses); $sql = $wpdb->prepare( "SELECT id FROM {$table_orders} WHERE {$where} AND id >= %d ORDER BY id ASC LIMIT %d", array_merge($args, array($first_id, $batch_size)) ); $order_ids = array_map('intval', (array) $wpdb->get_col($sql)); $rows_written = 0; $h = fopen($path, 'a'); if (!$h) { wp_send_json_error(array('message' => __('Cannot append to export file.', 'ai-eshop-optimizer'))); } $results = $this->softone_query($order_ids); $cols = $this->softone_headers(); foreach ($results as $r) { $row = $this->softone_row($r); $row = apply_filters('aieo_dmm_export_orders_row', $row, array('result' => $r, 'order_id' => (int) $r->OrderId)); if ($format === 'xml') { fwrite($h, " \n"); foreach (array_values($row) as $i => $val) { $tag = isset($cols[$i]) ? preg_replace('/[^A-Za-z0-9_]/', '', (string) $cols[$i]) : ('col' . $i); fwrite($h, " <{$tag}>" . htmlspecialchars((string) $val, ENT_QUOTES | ENT_XML1, 'UTF-8') . "\n"); } fwrite($h, " \n"); } else { fputcsv($h, array_values($row)); } $rows_written++; } fclose($h); if (!empty($order_ids)) { $cursor = max($order_ids); } $processed = (int) $job['processed'] + count($order_ids); $complete = count($order_ids) < $batch_size; $params['cursor'] = $cursor; $public_url = $params['url']; $etag = ''; if ($complete) { if ($format === 'xml') { $closing = fopen($path, 'a'); if ($closing) { fwrite($closing, "\n"); fclose($closing); } } $etag = @md5_file($path); $etag_short = $etag ? substr($etag, 0, 10) : (string) time(); $params['etag'] = $etag_short; $cache_mode = isset($params['cache_mode']) ? (string) $params['cache_mode'] : 'stable_url'; $public_url = AIEO_DMM_Marketplace_Feeds::compute_public_url( $path, ($cache_mode === 'stable_url' || $cache_mode === 'version_query') ? 'version_query' : $cache_mode, $etag_short ); $params['public_url'] = $public_url; update_option(self::LAST_RESULT_OPTION, array( 'public_url' => $public_url, 'path' => $path, 'filename' => isset($params['url']) ? basename((string) $params['url']) : basename($path), 'etag' => $etag_short, 'rows_total' => (int) $job['total'], 'rows_processed'=> (int) ($processed ?? 0), 'first_id' => (int) ($params['first_id'] ?? 0), 'last_id' => (int) ($params['last_id'] ?? 0), 'completed_at' => current_time('mysql', true), ), false); } $wpdb->update($jobs, array( 'processed' => $processed, 'status' => $complete ? 'complete' : 'running', 'params' => wp_json_encode($params), ), array('job_id' => $job_id), array('%d', '%s', '%s'), array('%s')); wp_send_json_success(array( 'processed' => $processed, 'total' => (int) $job['total'], 'written' => $rows_written, 'complete' => $complete, 'percentage' => (int) $job['total'] > 0 ? (int) floor(min(100, ($processed / (int) $job['total']) * 100)) : 100, 'download_url' => $complete ? $public_url : null, 'path' => $complete ? $path : null, 'public_url' => $complete ? $public_url : null, 'etag' => $complete ? ($etag ? substr($etag, 0, 10) : '') : '', )); } public static function excluded_statuses() { return (array) apply_filters('aieo_dmm_orders_export_excluded_statuses', array( 'wc-checkout-draft', 'checkout-draft', 'auto-draft', 'trash', )); } public function selectable_statuses() { $out = array(); if (function_exists('wc_get_order_statuses')) { foreach (wc_get_order_statuses() as $slug => $label) { $out[(string) $slug] = (string) $label; } } foreach (self::excluded_statuses() as $ex) { unset($out[$ex]); } return $out; } public function get_selected_statuses() { $valid = array_keys($this->selectable_statuses()); $stored = get_option(self::STATUSES_OPTION, null); if (is_array($stored)) { $sel = array_values(array_intersect(array_map('strval', $stored), $valid)); if (!empty($sel)) { return $sel; } } return in_array('wc-completed', $valid, true) ? array('wc-completed') : $valid; } private function status_in_sql(array $slugs) { $slugs = array_values(array_unique(array_filter(array_map('strval', $slugs)))); if (empty($slugs)) { $slugs = array('wc-completed'); } $in = "'" . implode("','", array_map('esc_sql', $slugs)) . "'"; return " AND status IN ({$in})"; } private function get_orders_table() { global $wpdb; return $wpdb->prefix . 'wc_orders'; } private function softone_headers() { $out = array(); foreach ($this->get_available_fields() as $info) { $out[] = $info['default_header']; } return $out; } private function softone_row($r) { $out = array(); foreach (array_keys($this->get_available_fields()) as $alias) { $out[] = isset($r->$alias) ? (string) $r->$alias : ''; } return $out; } private function softone_query(array $order_ids) { global $wpdb; $order_ids = array_values(array_unique(array_filter(array_map('intval', $order_ids)))); if (empty($order_ids)) { return array(); } $in = implode(',', $order_ids); $p = $wpdb->prefix; $sql = "SELECT o.id as OrderId, CAST(SUBSTR(DATE_FORMAT(o.date_created_gmt, '%Y%m%d'), 1, 10) AS CHAR) as TrnDate, 1 as Series, oba.email as ceMail, concat(oba.first_name, ' ' , oba.last_name) as cName, replace(pmcompany.meta_value, ',', ' ') as cCompanyName, pmvat.meta_value as cVat, (case when pmvat.meta_value <>'' then 2 else 1 end) as cVatStatus, '222' as ExpVal1, '22222' as ExpVal2, pmdoy.meta_value as cDoy, replace(concat(oba.address_1, ' ', oba.address_2), ',', ' ') as billAddress, replace(oba.city, ',', ' ') as billCity, replace(oba.postcode, ',', ' ') as billZip, replace(oba.country, ',', ' ') as billCountry, replace(concat(osa.address_1, ' ', osa.address_2), ',', ' ') as shipAddress, replace(osa.city, ',', ' ') as shipCity, replace(osa.postcode, ',', ' ') as shipZip, replace(osa.country, ',', ' ') as shipCountry, (CASE WHEN oimpv.meta_value=0 THEN pv.sku ELSE pvv.sku END) as mSKU, oba.phone as cPhone01, '' as cPhone02, convert(oimq.meta_value, decimal(3,0)) as PrimaryQty, convert(convert(oims.meta_value, decimal(7,2))/convert(oimq.meta_value, decimal(3,0)), decimal(5,2)) as Price, convert(ood.discount_total_amount, decimal(7,2)) as DiscVal, convert(ood.shipping_total_amount, decimal(7,2)) as cPostalCost, (CASE WHEN o.payment_method_title = 'Αντικαταβολή' THEN 1.5 ELSE 0 END) as cAntikatavoliCost, convert(oims.meta_value, decimal(7,2)) as LineValue, '' as LineVatValue, o.payment_method_title as cPayment, (SELECT order_item_name FROM {$p}woocommerce_order_items where order_item_type='shipping' and order_id=o.id LIMIT 1) as cShipment FROM {$p}woocommerce_order_items oi join {$p}wc_orders o on o.id=oi.order_id join {$p}woocommerce_order_itemmeta oimp on oi.order_item_id=oimp.order_item_id and oimp.meta_key='_product_id' join {$p}woocommerce_order_itemmeta oimpv on oi.order_item_id=oimpv.order_item_id and oimpv.meta_key='_variation_id' left join {$p}woocommerce_order_itemmeta oimq on oi.order_item_id=oimq.order_item_id and oimq.meta_key='_qty' left join {$p}woocommerce_order_itemmeta oims on oi.order_item_id=oims.order_item_id and oims.meta_key='_line_subtotal' left join {$p}wc_order_operational_data ood on ood.order_id=o.id left join {$p}aieo_core_product_vitals pv on oimp.meta_value=pv.product_id left join {$p}aieo_core_product_vitals pvv on oimpv.meta_value=pvv.product_id join {$p}wc_order_addresses oba on oba.order_id = o.id and oba.address_type='billing' join {$p}wc_order_addresses osa on osa.order_id = o.id and osa.address_type='shipping' left join {$p}wc_orders_meta pmcompany on pmcompany.order_id=ood.order_id and pmcompany.meta_key='_billing_store' left join {$p}wc_orders_meta pmvat on pmvat.order_id=o.id and pmvat.meta_key='_billing_vat' left join {$p}wc_orders_meta pmdoy on pmdoy.order_id=o.id and pmdoy.meta_key='_billing_irs' where oi.order_item_type='line_item' and o.type='shop_order' and oi.order_id IN ({$in})"; return (array) $wpdb->get_results($sql); } private function build_row($order, $item, array $map) { $row = array(); foreach ($map as $entry) { if (empty($entry['enabled'])) { continue; } $row[$entry['header']] = $this->field_value($entry['key'], $order, $item); } return $row; } private function field_value($key, $order, $item) { switch ($key) { case 'order_id': return (string) $order->get_id(); case 'order_date': return $order->get_date_created() ? $order->get_date_created()->date('Y-m-d H:i:s') : ''; case 'status': return $order->get_status(); case 'currency': return $order->get_currency(); case 'total': return (string) $order->get_total(); case 'subtotal': return (string) $order->get_subtotal(); case 'discount_total': return (string) $order->get_discount_total(); case 'tax_total': return (string) $order->get_total_tax(); case 'shipping_total': return (string) $order->get_shipping_total(); case 'customer_email': return (string) $order->get_billing_email(); case 'customer_name': return trim($order->get_billing_first_name() . ' ' . $order->get_billing_last_name()); case 'company': return (string) $order->get_billing_company(); case 'vat': return (string) ($order->get_meta('_billing_vat_number') ?: $order->get_meta('_vat_number')); case 'billing_address_1': return (string) $order->get_billing_address_1(); case 'billing_city': return (string) $order->get_billing_city(); case 'billing_postcode': return (string) $order->get_billing_postcode(); case 'billing_country': return (string) $order->get_billing_country(); case 'shipping_address_1':return (string) $order->get_shipping_address_1(); case 'shipping_city': return (string) $order->get_shipping_city(); case 'shipping_postcode': return (string) $order->get_shipping_postcode(); case 'shipping_country': return (string) $order->get_shipping_country(); case 'payment_method': return (string) $order->get_payment_method(); case 'shipping_method': return (string) $order->get_shipping_method(); case 'item_sku': $p = is_callable(array($item, 'get_product')) ? $item->get_product() : null; return $p ? (string) $p->get_sku() : ''; case 'item_name': return is_callable(array($item, 'get_name')) ? (string) $item->get_name() : ''; case 'item_qty': return is_callable(array($item, 'get_quantity')) ? (string) $item->get_quantity() : ''; case 'item_price': return is_callable(array($item, 'get_subtotal')) && $item->get_quantity() > 0 ? (string) ((float) $item->get_subtotal() / (int) $item->get_quantity()) : ''; case 'item_total': return is_callable(array($item, 'get_total')) ? (string) $item->get_total() : ''; case 'item_tax': return is_callable(array($item, 'get_total_tax')) ? (string) $item->get_total_tax() : ''; } return ''; } public function recent_jobs($type, $limit) { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_export_jobs'; if (!aieo_dmm_table_exists($table)) { return array(); } self::sweep_stale_running_jobs($type, 5); $rows = $wpdb->get_results($wpdb->prepare( "SELECT * FROM {$table} WHERE type = %s ORDER BY created_at DESC LIMIT %d", $type, (int) $limit ), ARRAY_A); return is_array($rows) ? $rows : array(); } public static function sweep_stale_running_jobs($type, $minutes = 5) { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_export_jobs'; if (!aieo_dmm_table_exists($table)) { return 0; } $minutes = max(1, (int) $minutes); $count = (int) $wpdb->query($wpdb->prepare( "UPDATE {$table} SET status = 'aborted', log = CONCAT(IFNULL(log, ''), '\n[auto-aborted: page navigated away — last tick ', updated_at, ' UTC]') WHERE type = %s AND status = 'running' AND updated_at < (UTC_TIMESTAMP() - INTERVAL {$minutes} MINUTE)", $type )); return $count; } }