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) . '' . $tag . ">\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();
}
}