render_template('price-export.php', array( 'available_fields' => $this->get_available_fields(), 'recent_jobs' => $this->recent_jobs(), )); } public function get_available_fields() { return array( 'product_id' => array('label' => __('Product ID', 'ai-eshop-optimizer'), 'default_header' => 'ProductId'), 'parent_id' => array('label' => __('Parent ID', 'ai-eshop-optimizer'), 'default_header' => 'ParentId'), 'sku' => array('label' => __('SKU', 'ai-eshop-optimizer'), 'default_header' => 'SKU'), 'name' => array('label' => __('Name', 'ai-eshop-optimizer'), 'default_header' => 'Name'), 'regular_price' => array('label' => __('Regular price', 'ai-eshop-optimizer'), 'default_header' => 'RegularPrice'), 'sale_price' => array('label' => __('Sale price', 'ai-eshop-optimizer'), 'default_header' => 'SalePrice'), 'stock' => array('label' => __('Stock', 'ai-eshop-optimizer'), 'default_header' => 'Stock'), 'brand' => array('label' => __('Brand', 'ai-eshop-optimizer'), 'default_header' => 'Brand'), 'role_prices' => array('label' => __('All role prices (JSON)', 'ai-eshop-optimizer'), 'default_header' => 'RolePrices'), ); } public function ajax_start() { AIEO_DMM::verify_ajax_request(); $format = isset($_POST['format']) && sanitize_key(wp_unslash($_POST['format'])) === 'xml' ? 'xml' : 'csv'; $selected_fields = isset($_POST['fields']) && is_array($_POST['fields']) ? array_map('sanitize_key', wp_unslash($_POST['fields'])) : array_keys($this->get_available_fields()); $source_for_filter = isset($_POST['source']) && sanitize_key(wp_unslash($_POST['source'])) === 'vitals' ? 'vitals' : 'live'; if ($source_for_filter === 'vitals') { $selected_fields = array_values(array_filter( $selected_fields, static function ($f) { return $f !== 'role_prices'; } )); } $dir = AIEO_DMM::get_upload_path('prices'); $job_id = wp_generate_password(16, false, false); $ext = $format === 'xml' ? 'xml' : 'csv'; $filename = 'prices-export-' . $job_id . '.' . $ext; $path = trailingslashit($dir['path']) . $filename; $url = trailingslashit($dir['url']) . $filename; if ($format === 'csv') { $h = fopen($path, 'w'); if (!$h) { wp_send_json_error(array('message' => __('Cannot create export file.', 'ai-eshop-optimizer'))); } AIEO_DMM_Sample::write_csv_bom($h); $headers = array(); foreach ($selected_fields as $k) { $fields = $this->get_available_fields(); if (isset($fields[$k])) { $headers[] = $fields[$k]['default_header']; } } fputcsv($h, $headers, ',', '"', ''); fclose($h); } else { file_put_contents($path, "\n\n"); } global $wpdb; $source = isset($_POST['source']) && sanitize_key(wp_unslash($_POST['source'])) === 'vitals' ? 'vitals' : 'live'; if ($source === 'vitals') { $vitals_table = $wpdb->prefix . 'aieo_core_product_vitals'; $total = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$vitals_table} WHERE product_id > 0"); } else { $total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type IN ('product','product_variation') AND post_status = 'publish'" ); } $wpdb->insert($wpdb->prefix . 'aieo_dmm_export_jobs', array( 'job_id' => $job_id, 'type' => 'price_export', 'status' => 'running', 'total' => $total, 'processed' => 0, 'filename' => $filename, 'params' => wp_json_encode(array( 'format' => $format, 'fields' => $selected_fields, 'source' => $source, 'path' => $path, 'url' => $url, 'offset' => 0, )), '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(); $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'] !== 'price_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'))); } $offset = (int) $params['offset']; $source = isset($params['source']) && $params['source'] === 'vitals' ? 'vitals' : 'live'; $fields = $params['fields']; $h = fopen($params['path'], 'a'); if (!$h) { wp_send_json_error(array('message' => __('Cannot append to file.', 'ai-eshop-optimizer'))); } $available = $this->get_available_fields(); $count_in_batch = 0; if ($source === 'vitals') { $batch_size = 3000; $vitals_table = $wpdb->prefix . 'aieo_core_product_vitals'; $rows = $wpdb->get_results($wpdb->prepare( "SELECT v.product_id, v.parent_id, v.sku, v.name, v.product_name, v.parent_name, v.parent_sku, v.regular_price, v.price, v.brand, v.stock, v.stock_status, v.gtin FROM {$vitals_table} v WHERE v.product_id > 0 ORDER BY v.product_id ASC LIMIT %d OFFSET %d", $batch_size, $offset ), ARRAY_A); foreach ((array) $rows as $v) { $row_out = $this->build_row_from_vitals($v, $fields, $available); $this->write_row($h, $params['format'], $row_out); } $count_in_batch = count((array) $rows); } else { $batch_size = 500; $posts = $wpdb->get_results($wpdb->prepare( "SELECT ID, post_parent, post_title FROM {$wpdb->posts} WHERE post_type IN ('product','product_variation') AND post_status = 'publish' ORDER BY ID ASC LIMIT %d OFFSET %d", $batch_size, $offset )); foreach ($posts as $p) { $product = function_exists('wc_get_product') ? wc_get_product($p->ID) : null; if (!$product) { continue; } $row_out = $this->build_row($product, $fields, $available); $this->write_row($h, $params['format'], $row_out); } $count_in_batch = count((array) $posts); } fclose($h); $new_processed = $offset + $count_in_batch; $complete = $count_in_batch < $batch_size; $params['offset'] = $new_processed; if ($complete && $params['format'] === 'xml') { file_put_contents($params['path'], "\n", FILE_APPEND); } $wpdb->update($jobs, array( 'processed' => $new_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' => $new_processed, 'total' => (int) $job['total'], 'complete' => $complete, 'percentage'=> (int) $job['total'] > 0 ? (int) floor(min(100, ($new_processed / (int) $job['total']) * 100)) : 100, 'download_url' => $complete ? $params['url'] : null, )); } private function write_row($h, $format, array $row_out) { if ($format === 'csv') { fputcsv($h, array_values($row_out), ',', '"', ''); return; } $xml = " \n"; foreach ($row_out as $key => $val) { $tag = preg_replace('/[^A-Za-z0-9_]/', '', (string) $key); if ($tag === '') { continue; } $xml .= ' <' . $tag . '>' . self::xml_escape((string) $val) . '\n"; } $xml .= " \n"; fwrite($h, $xml); } private function build_row_from_vitals(array $v, array $fields, array $available) { $row = array(); foreach ($fields as $k) { if (!isset($available[$k])) { continue; } $header = $available[$k]['default_header']; switch ($k) { case 'product_id': $row[$header] = (string) ($v['product_id'] ?? ''); break; case 'parent_id': $pid = (int) ($v['parent_id'] ?? 0); $row[$header] = (string) $pid; break; case 'sku': $row[$header] = (string) ($v['sku'] ?? ''); break; case 'name': $row[$header] = (string) ($v['name'] ?? ($v['product_name'] ?? '')); break; case 'regular_price': $row[$header] = $this->fmt_price($v['regular_price'] ?? null); break; case 'sale_price': $reg = isset($v['regular_price']) ? (float) $v['regular_price'] : 0.0; $cur = isset($v['price']) ? (float) $v['price'] : 0.0; $row[$header] = ($cur > 0 && $reg > 0 && $cur < $reg) ? $this->fmt_price($cur) : ''; break; case 'stock': $sq = $v['stock'] ?? null; $row[$header] = ($sq === null || $sq === '') ? '' : (string) (int) $sq; break; case 'brand': $row[$header] = (string) ($v['brand'] ?? ''); break; case 'role_prices': $row[$header] = $this->role_prices_json_from_row($v); break; } } return $row; } private function fmt_price($v) { if ($v === null || $v === '') { return ''; } $s = (string) $v; if (strpos($s, '.') !== false) { $s = rtrim(rtrim($s, '0'), '.'); if ($s === '' || $s === '-') { return '0'; } } return $s; } private function role_prices_json_from_row(array $v) { $out = array(); foreach ($v as $col => $val) { if (strpos((string) $col, 'role_') === 0 && $val !== null && $val !== '') { $out[substr((string) $col, 5)] = (float) $val; } } return empty($out) ? '' : wp_json_encode($out); } private function build_row($product, array $fields, array $available) { $pid = (int) $product->get_id(); $row = array(); foreach ($fields as $k) { if (!isset($available[$k])) { continue; } $header = $available[$k]['default_header']; switch ($k) { case 'product_id': $row[$header] = (string) $pid; break; case 'parent_id': $row[$header] = (string) ($product->is_type('variation') ? $product->get_parent_id() : 0); break; case 'sku': $row[$header] = (string) $product->get_sku(); break; case 'name': $row[$header] = (string) $product->get_name(); break; case 'regular_price': $row[$header] = (string) $product->get_regular_price(); break; case 'sale_price': $row[$header] = (string) $product->get_sale_price(); break; case 'stock': $sq = $product->get_stock_quantity(); $row[$header] = $sq === null ? '' : (string) (int) $sq; break; case 'brand': $terms = wp_get_object_terms($pid, 'product_brand', array('fields' => 'names')); $row[$header] = is_wp_error($terms) ? '' : implode('|', (array) $terms); break; case 'role_prices': $row[$header] = $this->get_role_prices_json($pid); break; } } return $row; } private function get_role_prices_json($pid) { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_role_prices'; if (!aieo_dmm_table_exists($table)) { return ''; } $row = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$table} WHERE product_id = %d", $pid), ARRAY_A); if (!$row) { return ''; } $out = array(); foreach ($row as $col => $val) { if (strpos($col, 'role_') === 0 && $val !== null && $val !== '') { $out[substr($col, 5)] = (float) $val; } } return wp_json_encode($out); } public static function xml_escape($s) { return htmlspecialchars($s, ENT_QUOTES | ENT_XML1, 'UTF-8'); } private function recent_jobs() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_export_jobs'; if (!aieo_dmm_table_exists($table)) { return array(); } $rows = $wpdb->get_results("SELECT * FROM {$table} WHERE type = 'price_export' ORDER BY created_at DESC LIMIT 10", ARRAY_A); return is_array($rows) ? $rows : array(); } }