/**
* Updates database prices from the supplied file
* @param $filename string fully qualified filepath and name
* @param $options array of options; see declaration for details
*/
function updatePrices($dbc, $filename, array $options = array())
{
$result = array('updated' => array(), 'failed' => array(), 'not_found' => array(), 'warning' => array(), 'disabled' => array(), 'modified' => array());
$updated = array();
// store product_id => array of product codes matched for it
$stmts = getPreparedStatements($dbc, $options);
$labels = $options['header_labels'];
try {
$importer = new CsvImporter($filename, true, $labels, ",");
$select_only = $options['dry_run'] || $options['disable_only'];
while ($data = $importer->get(2000)) {
foreach ($data as $entry) {
$manufacturer = trim(empty($entry[$labels['manufacturer']['label']]) ? $options['manufacturer'] : $entry[$labels['manufacturer']['label']]);
$product_code = trim($entry[$labels['product_code']['label']]);
$upc = !$options['upc_update'] || empty($entry[$labels['upc']['label']]) ? null : $entry[$labels['upc']['label']];
$list_price = round_up(getAmount($entry[$labels['list_price']['label']]), 2);
$cost_price = isset($entry[$labels['cost_price']['label']]) ? round_up(getAmount($entry[$labels['cost_price']['label']]), 2) : null;
$sale_price = round_up(getAmount($entry[$labels['sale_price']['label']]), 2);
if ($sale_price >= $list_price) {
$list_price = $options['allow_upsell'] ? $sale_price : $list_price;
$sale_price = null;
}
$changed = false;
// flag indicating product (or matrix) prices changed
if (!$stmts['select_product']->bind_param('ss', $product_code, $manufacturer) || !$stmts['select_product']->execute()) {
throw new \RuntimeException("Query failed for manufacturer {$manufacturer} and product code {$product_code}: {$stmts['select_product']->errno} - {$stmts['select_product']->error}");
}
$main_product_id = fetch_assoc_stmt($stmts['select_product']);
$product_id = false;
if ($select_only) {
if (is_int($main_product_id)) {
$result['updated'][] = "Product prices updated; Manufacturer: {$manufacturer} | Product Code: {$product_code} | List Price: \$" . sprintf('%.2f', $list_price) . " | Cost Price: \$" . sprintf('%.2f', $cost_price) . " | Sale Price: \$" . sprintf('%.2f', $sale_price);
$changed = true;
} elseif (!$options['ignore_missing']) {
$result['not_found'][$product_code] = "Product was either not found or prices did not change; Manufacturer: {$manufacturer} | Product Code: {$product_code}";
}
if ($options['update_matrix']) {
if (!$stmts['select_matrix']->bind_param('ss', $product_code, $manufacturer) || !$stmts['select_matrix']->execute()) {
throw new \RuntimeException("Query failed for manufacturer {$manufacturer} and product code {$product_code}: {$stmts['select_matrix']->errno} - {$stmts['select_matrix']->error}");
} elseif (!empty($product_id = fetch_assoc_stmt($stmts['select_matrix']))) {
$result['updated'][] = "Matrix prices updated; Manufacturer: {$manufacturer} | Product Code: {$product_code} | List Price: \$" . sprintf('%.2f', $list_price) . " | Sale Price: \$" . sprintf('%.2f', $sale_price);
$changed = true;
$updated[$product_id][] = $product_code;
// wasn't found as a product, but found as a matrix entry
if (array_key_exists($product_code, $result['not_found'])) {
unset($result['not_found'][$product_code]);
}
} elseif (array_key_exists($product_code, $result['not_found'])) {
$result['not_found'][$product_code] = "Neither product nor matrix entry not found; Manufacturer: {$manufacturer} | Product Code: {$product_code}";
}
}
} else {
if (!$stmts['update_product']->bind_param('dddsi', $list_price, $cost_price, $sale_price, $upc, $main_product_id) || !$stmts['update_product']->execute()) {
throw new \RuntimeException("Query failed for manufacturer {$manufacturer} and product code {$product_code}: {$stmts['update_product']->errno} - {$stmts['update_product']->error}");
} elseif ($stmts['update_product']->affected_rows > 0) {
$result['updated'][] = "Product prices updated; Manufacturer: {$manufacturer} | Product Code: {$product_code} | List Price: \$" . sprintf('%.2f', $list_price) . " | Cost Price: \$" . sprintf('%.2f', $cost_price) . " | Sale Price: \$" . sprintf('%.2f', $sale_price);
$changed = true;
} elseif (!$options['ignore_missing']) {
$result['not_found'][$product_code] = "Product was either not found or prices did not change; Manufacturer: {$manufacturer} | Product Code: {$product_code}";
}
if ($options['update_matrix']) {
if (!$stmts['update_matrix']->bind_param('ddsss', $list_price, $sale_price, $upc, $product_code, $manufacturer) || !$stmts['update_matrix']->execute()) {
throw new \RuntimeException("Query failed for manufacturer {$manufacturer} and product code {$product_code}: {$stmts['update_matrix']->errno} - {$stmts['update_matrix']->error}");
} elseif ($stmts['update_matrix']->affected_rows > 0) {
if (!$stmts['select_matrix']->bind_param('ss', $product_code, $manufacturer) || !$stmts['select_matrix']->execute()) {
throw new \RuntimeException("Query to select product id from matrix table failed for manufacturer {$manufacturer} and product code {$product_code}: {$stmts['select_matrix']->errno} - {$stmts['select_matrix']->error}");
} elseif (empty($product_id = fetch_assoc_stmt($stmts['select_matrix']))) {
$result['failed'][] = "Matrix entry not found after updating! Manufacturer: {$manufacturer} | Product Code: {$product_code}";
} else {
$result['updated'][] = "Matrix prices updated; Manufacturer: {$manufacturer} | Product Code: {$product_code} | List Price: \$" . sprintf('%.2f', $list_price) . " | Sale Price: \$" . sprintf('%.2f', $sale_price);
$changed = true;
$updated[$product_id][] = $product_code;
// wasn't found as a product, but found as a matrix entry
if (array_key_exists($product_code, $result['not_found'])) {
unset($result['not_found'][$product_code]);
}
}
} elseif (array_key_exists($product_code, $result['not_found'])) {
$result['not_found'][$product_code] = "Neither product nor matrix entry was found or updated; Manufacturer: {$manufacturer} | Product Code: {$product_code}";
}
}
}
// Product was found and updated - update 'date updated' field
$id = $main_product_id ? $main_product_id : $product_id;
if ($id && empty($result['modified'][$id]) && ($options['update_date_all'] || $changed && $options['update_date'])) {
if ($select_only) {
$result['modified'][$id] = "Date modified updated for product id {$id}: triggered by {$manufacturer} product {$product_code}";
} elseif (!$stmts['update_date']->bind_param('i', $id) || !$stmts['update_date']->execute()) {
throw new \RuntimeException("Update date query failed for manufacturer {$manufacturer} and product code {$product_code}: {$stmts['update_date']->errno} - {$stmts['update_date']->error}");
} else {
$result['modified'][$id] = "Date modified updated for product id {$id}: triggered by {$manufacturer} product {$product_code}";
}
}
}
}
// TODO option to disable warnings (including display thereof)
// Array only contains entries when updating matrix codes, i.e. option_matrix table has been modified accordingly
foreach ($updated as $product_id => $product_codes) {
// select all product / matrix codes from database for this product
if (!$stmts['select_product_codes']->bind_param('ii', $product_id, $product_id) || !$stmts['select_product_codes']->execute()) {
throw new \RuntimeException("Query to select product codes while checking for warnings failed for product id {$product_id}: {$stmts['select_product_codes']->errno} - {$stmts['select_product_codes']->error}");
}
// disable / warn for any that were not found on the price list
$codes = fetch_assoc_stmt($stmts['select_product_codes']);
$diff = array_diff(is_array($codes) ? $codes : array($codes), $product_codes);
if ($options['disable_products']) {
if ($options['dry_run']) {
$result['disabled'][$product_id] = $diff;
} else {
// Disable matrix entries first
foreach ($diff as $product_code) {
if (!$stmts['disable_matrix']->bind_param('is', $product_id, $product_code) || !$stmts['disable_matrix']->execute()) {
throw new \RuntimeException("Failed to disable matrix entry for product {$product_id} - {$product_code}: {$stmts['disable_matrix']->errno} - {$stmts['disable_matrix']->error}");
} elseif ($stmts['disable_matrix']->affected_rows > 0) {
$result['disabled'][$product_id][] = $product_code;
} else {
$result['warning'][$product_id][] = "Matrix entry for product {$product_id} - {$product_code} could not be disabled: it may already be disabled, but you should double-check";
}
}
// Then disable products that no longer have any enabled matrix options
if (!$stmts['disable_product']->bind_param('iii', $product_id, $product_id, $product_id) || !$stmts['disable_product']->execute()) {
throw new \RuntimeException("Failed to disable product id {$product_id}: {$stmts['disable_product']->errno} - {$stmts['disable_product']->error}");
} elseif ($stmts['disable_product']->affected_rows > 0) {
$result['disabled'][$product_id][] = "Product {$product_id} disabled";
} else {
$result['warning'][$product_id][] = "Product {$product_id} was not be disabled: it may either not need to be or already is disabled; you should double-check";
}
}
} elseif (!empty($diff)) {
$result['warning'][$product_id] = $diff;
}
// Update main product price with the lowest (non-zero) of its enabled matrix options
if ($options['update_main_price']) {
if (!$stmts['lowest_price']->bind_param('i', $product_id) || !$stmts['lowest_price']->execute()) {
throw new \RuntimeException("Failed to fetch lowest matrix price for product {$product_id}: {$stmts['lowest_price']->errno} - {$stmts['lowest_price']->error}");
}
$prices = fetch_assoc_stmt($stmts['lowest_price']);
if (!empty($prices)) {
extract($prices);
if (!$stmts['update_main_price']->bind_param('ddi', $price, $sale_price, $product_id) || !$stmts['update_main_price']->execute()) {
throw new \RuntimeException("Failed to update main prices for product {$product_id}: {$stmts['update_main_price']->errno} - {$stmts['update_main_price']->error}");
} elseif ($stmts['update_main_price']->affected_rows > 0) {
$result['updated'][] = "Main prices for product id {$product_id} set to lowest found in matrix: List Price=\${$price}, Sale Price=\$" . ($sale_price ? $sale_price : '0.00');
} else {
$result['warning'][$product_id][] = "Failed to update prices to \${$price} (sale: \$" . ($sale_price ? $sale_price : '0.00') . ") - prices may already be up-to-date";
}
}
}
}
} catch (\Exception $e) {
$result['error'] = $e->getMessage();
} finally {
foreach ($stmts as $stmt) {
$stmt->close();
}
}
// Sort results by key
foreach ($result as &$array) {
ksort($array);
}
unset($array);
// save puppies
return $result;
}
/**
*
* @param $file the name of the file, with or without extensions
* @param $file_id the primary key id of the file in the CubeCart_filemanager table
* @param $stmts same as array of prepared statements passed to #addFile
* @param $options same as options array passed to #addFile
* @param $message string containing additional information
* @return true if at least one product relationship was added
*/
function addImageRelationships($file, $file_id, array $stmts, array $options, &$message)
{
$add_product = !empty($options['add_product']);
// true to add product:image relationships
$add_product_matrix = !empty($options['add_product_matrix']);
// true to add product:image relationships for each matching matrix entry
$update_matrix = !empty($options['update_matrix']);
// true to add matrix:image relationships
if (!$add_product && !$add_product_matrix && !$update_matrix) {
return false;
}
$code = substr_replace($file, '', strrpos($file, '.'));
$added = 0;
// Find matching product / matrix entries
if (empty($options['regexp']) && (!$stmts['find_products']->bind_param('issss', $file_id, $code, $code, $code, $code) || !$stmts['find_products']->execute()) || !empty($options['regexp']) && (!$stmts['find_products']->bind_param('isss', $file_id, $code, $code, $code) || !$stmts['find_products']->execute())) {
$message .= "<br>ERROR: Database error selecting matching products for {$code}: {$stmts['find_products']}->errno - {$stmts['find_products']}->error";
return false;
}
$products = fetch_assoc_stmt($stmts['find_products'], true);
// Check results when not matching by regular expression to make sure all belong to same product id
// TODO ? check for duplicate product codes => SELECT COUNT(*) AS `count`, product_code FROM cubecart_option_matrix GROUP BY product_code HAVING `count` > 1;
// TODO ? provide option to allow multiple products to be updated with the same image
if (empty($options['regexp'])) {
$product = false;
foreach ($products as $match) {
if (!$product) {
$product = $match;
} elseif ($match['product_id'] != $product['product_id']) {
if ($product['product_code'] == $code && $match['product_code'] != $code && empty($options['add_product_matrix'])) {
// ignore matrix match
} elseif ($match['product_code'] == $code && $product['product_code'] != $code && empty($options['add_product_matrix'])) {
$product = $match;
// use exact match instead of matrix match
} else {
$message .= "<br>ERROR: At least two products matched the file <strong>{$code}</strong>:<br>Product ID: {$product['product_id']} - Product Code: {$product['product_code']}<br>Product ID: {$match['product_id']} - Product Code: {$match['product_code']}";
return false;
}
}
}
}
// Build relationships for each product
$relations = array();
foreach ($products as $match) {
// Check for existing relationship (cannot use INSERT IGNORE due to lack of useful unique key)
if (array_key_exists($match['product_id'], $relations)) {
// relationship already established on product:image level - move on to next part
} elseif (!$stmts['relation_exists']->bind_param('ii', $match['product_id'], $file_id) || !$stmts['relation_exists']->execute()) {
$message .= "<br>ERROR: Database error checking for existing product:image relationships: {$stmts['relation_exists']}->errno - {$stmts['relation_exists']}->error";
return false;
} elseif (empty($relations[$match['product_id']] = fetch_assoc_stmt($stmts['relation_exists']))) {
// No previous product:image relationship exists for this file
// Check for exact match
$product_match = $match['product_code'] === $code;
// Check for variant match
if (!$product_match && !empty($options['allow_variants'])) {
// Check if the image file could be considered a 'variant' image
$var_match = empty($options['allow_variants']) ? '' : '[0-9]+';
$suffix = empty($options['allow_variants']) ? '' : $options['code_suffix'];
$regexp = "/^{$match['product_code']}{$suffix}{$var_match}\$/i";
$product_match = preg_match($regexp, $code);
}
// Check for regular expression match
if (!$product_match && !empty($options['regexp'])) {
// Match the product code against the regexp, completely ignoring the image file's name
$product_match = preg_match("/{$options['regexp']}/i", $match['product_code']);
}
// Add relationship if match found
if ($product_match && $add_product || !empty($match['matrix_id']) && $add_product_matrix) {
if (!empty($options['dry_run'])) {
$relations[$match['product_id']] = 0;
// non-existent index for dry run
} elseif (!$stmts['add_product_img']->bind_param('iis', $match['product_id'], $file_id, $options['main_image']) || !$stmts['add_product_img']->execute()) {
$message .= "<br>ERROR: Database error adding new product:image relationship: {$stmts['add_product_img']}->errno - {$stmts['add_product_img']}->error";
return false;
} else {
$relations[$match['product_id']] = $stmts['add_product_img']->insert_id;
}
if (!is_int($relations[$match['product_id']])) {
$message .= "<br>ERROR: Failed to add product:image relationship - invalid insertion ID";
return false;
} else {
$added++;
$message .= "<br>NOTICE: File associated with product {$match['product_id']} - {$match['product_code']}; image index = {$relations[$match['product_id']]}";
}
}
} else {
$message .= "<br>NOTICE: File id {$file_id} is already associated with product {$match['product_id']} - {$match['product_code']}; image_index id = {$relations[$match['product_id']]}";
}
// Update matrix image entries if applicable
if ($update_matrix && !empty($match['matrix_id'])) {
if (!empty($match['matrix_image_file_id']) && empty($options['force_update'])) {
$message .= "<br>WARNING: Matrix entry {$match['matrix_id']} - {$match['matrix_product_code']} is already associated with file {$match['matrix_image_file_id']} - {$match['matrix_image_file']}";
} elseif (empty($options['dry_run']) && (!$stmts['update_matrix_img']->bind_param('ii', $file_id, $match['matrix_id']) || !$stmts['update_matrix_img']->execute())) {
$message .= "<br>ERROR: Database error updating matrix image id: {$stmts['update_matrix_img']}->errno - {$stmts['update_matrix_img']}->error";
return false;
} else {
$added++;
if (empty($match['matrix_image_file_id'])) {
$message .= "<br>NOTICE: Image id for matrix entry {$match['matrix_id']} - {$match['matrix_product_code']} successfully updated";
} else {
$message .= "<br>WARNING: Image id for matrix entry {$match['matrix_id']} - {$match['matrix_product_code']} was overwritten; previous image was {$match['matrix_image_file_id']} - {$match['matrix_image_file']}";
}
}
}
}
$message .= "<br>NOTICE: Total new associations with this image: {$added}";
return $added > 0;
}
