/** * 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; }
$code_suffix = filter_input(INPUT_POST, 'code_suffix'); if (!is_string($code_suffix)) { $errors['code_suffix'] = '* Please enter a valid string'; $code_suffix = ''; } elseif (!preg_match('/^[\\(\\)\\?\\|\\+-_a-z0-9]*$/i', $code_suffix)) { $errors['code_suffix'] = '* Valid characters are -, _, a-z, 0-9, (, ), |, ?, and +'; } $options = array('dry_run' => isset($_POST['btn_dry']), 'recursive' => filter_input(INPUT_POST, 'recursive') ? true : false, 'update_size' => filter_input(INPUT_POST, 'update_size') ? true : false, 'add_product' => filter_input(INPUT_POST, 'add_product') ? true : false, 'main_image' => filter_input(INPUT_POST, 'main_image') ? '1' : '0', 'add_product_matrix' => filter_input(INPUT_POST, 'add_product_matrix') ? true : false, 'update_matrix' => filter_input(INPUT_POST, 'update_matrix') ? true : false, 'force_update' => filter_input(INPUT_POST, 'force_update') ? true : false, 'ignore_existing' => filter_input(INPUT_POST, 'ignore_existing') ? true : false, 'allow_variants' => filter_input(INPUT_POST, 'allow_variants') ? true : false, 'code_suffix' => empty($code_suffix) ? '(-|_)+' : $code_suffix, 'regexp' => filter_input(INPUT_POST, 'regexp')); $show_advanced = !empty($code_suffix) || !empty($options['regexp']) || $options['allow_variants']; $show_options = $show_advanced || !empty($options['add_product']) || !empty($options['add_product_matrix']) || !empty($options['update_matrix']) || !empty($options['code_suffix']); if (empty($errors)) { $dbc = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME); if (mysqli_connect_errno()) { die("Could not connect to MySQL: " . mysqli_connect_error()); } $stmts = getPreparedStatements($dbc, $options); if (USE_TRANSACTIONS) { $dbc->autocommit(false); try { $result = addFiles($dir, $stmts, $options); // Rollback any changes that may have occurred during dry run (no changes should have been made, but just in case) if (!empty($options['dry_run'])) { throw new \Exception("Dry run completed successfully"); } } catch (\Exception $e) { $result['failed'][] = "Rolling back changes due to exception: {$e->getMessage()}"; if (!$dbc->rollback()) { $result['failed'][] = "CRITICAL: Failed to rollback database!"; } } finally { if (!$dbc->autocommit(true)) {