store_id = $store_id;
$this->load->model('setting/setting');
if ($this->store_id === 0) {
$this->store_url = basename(DIR_TEMPLATE) == 'template' ? HTTPS_CATALOG : HTTPS_SERVER;
$this->store_name = $this->config->get('config_name');
} else {
$this->store_url = $this->model_setting_setting->getSettingValue('config_ssl', $store_id);
$this->store_name = $this->model_setting_setting->getSettingValue('config_name', $store_id);
}
$this->endpoint_url = self::API_URL . 'index.php?route=%s';
$this->loadStore($this->store_id);
$this->debug_log = new Log(sprintf(self::DEBUG_LOG_FILENAME, $this->store_id));
}
public function getStoreUrl() {
return $this->store_url;
}
public function getStoreName() {
return $this->store_name;
}
public function getSupportedLanguageId($code) {
$this->load->model('localisation/language');
foreach ($this->model_localisation_language->getLanguages() as $language) {
$language_code = current(explode("-", $language['code']));
if ($this->compareTrimmedLowercase($code, $language_code) === 0) {
return (int)$language['language_id'];
}
}
return 0;
}
public function getSupportedCurrencyId($code) {
$this->load->model('localisation/currency');
foreach ($this->model_localisation_currency->getCurrencies() as $currency) {
if ($this->compareTrimmedLowercase($code, $currency['code']) === 0) {
return (int)$currency['currency_id'];
}
}
return 0;
}
public function getCountryName($code) {
$this->load->config('googleshopping/googleshopping');
$this->load->model('localisation/country');
$countries = $this->config->get('advertise_google_countries');
// Default value
$result = $countries[$code];
// Override with store value, if present
foreach ($this->model_localisation_country->getCountries() as $store_country) {
if ($this->compareTrimmedLowercase($store_country['iso_code_2'], $code) === 0) {
$result = $store_country['name'];
break;
}
}
return $result;
}
public function compareTrimmedLowercase($text1, $text2) {
return strcmp(strtolower(trim($text1)), strtolower(trim($text2)));
}
public function getTargets($store_id) {
$sql = "SELECT * FROM `" . DB_PREFIX . "googleshopping_target` WHERE store_id=" . $store_id;
return array_map(array($this, 'target'), $this->db->query($sql)->rows);
}
public function getTarget($advertise_google_target_id) {
$sql = "SELECT * FROM `" . DB_PREFIX . "googleshopping_target` WHERE advertise_google_target_id=" . (int)$advertise_google_target_id;
return $this->target($this->db->query($sql)->row);
}
public function editTarget($target_id, $target) {
$sql = "UPDATE `" . DB_PREFIX . "googleshopping_target` SET `campaign_name`='" . $this->db->escape($target['campaign_name']) . "', `country`='" . $this->db->escape($target['country']) . "', `budget`='" . (float)$target['budget'] . "', `feeds`='" . $this->db->escape(json_encode($target['feeds'])) . "', `roas`='" . (int)$target['roas'] . "', `status`='" . $this->db->escape($target['status']) . "' WHERE `advertise_google_target_id`='" . (int)$target_id . "'";
$this->db->query($sql);
return $target;
}
public function deleteTarget($target_id) {
$sql = "DELETE FROM `" . DB_PREFIX . "googleshopping_target` WHERE `advertise_google_target_id`='" . (int)$target_id . "'";
$this->db->query($sql);
$sql = "DELETE FROM `" . DB_PREFIX . "googleshopping_product_target` WHERE `advertise_google_target_id`='" . (int)$target_id . "'";
$this->db->query($sql);
return true;
}
public function doJob($job) {
$product_count = 0;
// Initialize push
$init_request = array(
'type' => 'POST',
'endpoint' => self::ENDPOINT_DATAFEED_INIT,
'use_access_token' => true,
'content_type' => 'multipart/form-data',
'data' => array(
'work_id' => $job['work_id']
)
);
$response = $this->api($init_request);
// At this point, the job has been initialized and we can start pushing the datafeed
$page = 0;
while (null !== $products = $this->getFeedProducts(++$page, $job['language_id'], $job['currency'])) {
$post = array();
$post_data = array(
'product' => $products,
'work_id' => $job['work_id'],
'work_step' => $response['work_step']
);
$this->curlPostQuery($post_data, $post);
$push_request = array(
'type' => 'POST',
'endpoint' => self::ENDPOINT_DATAFEED_PUSH,
'use_access_token' => true,
'content_type' => 'multipart/form-data',
'data' => $post
);
$response = $this->api($push_request);
$product_count += count($products);
}
// Finally, close the file to finish the job
$close_request = array(
'type' => 'POST',
'endpoint' => self::ENDPOINT_DATAFEED_CLOSE,
'use_access_token' => true,
'content_type' => 'multipart/form-data',
'data' => array(
'work_id' => $job['work_id'],
'work_step' => $response['work_step']
)
);
$this->api($close_request);
return $product_count;
}
public function getProductVariationIds($page) {
$this->load->config('googleshopping/googleshopping');
$sql = "SELECT DISTINCT pag.product_id, pag.color, pag.size FROM `" . DB_PREFIX . "googleshopping_product` pag LEFT JOIN `" . DB_PREFIX . "product` p ON (p.product_id = pag.product_id) LEFT JOIN `" . DB_PREFIX . "product_to_store` p2s ON (p2s.product_id = p.product_id AND p2s.store_id=" . (int)$this->store_id . ") WHERE p2s.store_id IS NOT NULL AND p.status = 1 AND p.date_available <= NOW() AND p.price > 0 ORDER BY p.product_id ASC LIMIT " . (int)(($page - 1) * $this->config->get('advertise_google_report_limit')) . ', ' . (int)$this->config->get('advertise_google_report_limit');
$result = array();
$this->load->model('localisation/language');
foreach ($this->db->query($sql)->rows as $row) {
foreach ($this->model_localisation_language->getLanguages() as $language) {
$groups = $this->getGroups($row['product_id'], $language['language_id'], $row['color'], $row['size']);
foreach (array_keys($groups) as $id) {
if (!in_array($id, $result)) {
$result[] = $id;
}
}
}
}
return !empty($result) ? $result : null;
}
// A copy of the OpenCart SEO URL rewrite method.
public function rewrite($link) {
$url_info = parse_url(str_replace('&', '&', $link));
$url = '';
$data = array();
parse_str($url_info['query'], $data);
foreach ($data as $key => $value) {
if (isset($data['route'])) {
if (($data['route'] == 'product/product' && $key == 'product_id') || (($data['route'] == 'product/manufacturer/info' || $data['route'] == 'product/product') && $key == 'manufacturer_id') || ($data['route'] == 'information/information' && $key == 'information_id')) {
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "seo_url WHERE `query` = '" . $this->db->escape($key . '=' . (int)$value) . "' AND store_id = '" . (int)$this->config->get('config_store_id') . "' AND language_id = '" . (int)$this->config->get('config_language_id') . "'");
if ($query->num_rows && $query->row['keyword']) {
$url .= '/' . $query->row['keyword'];
unset($data[$key]);
}
} elseif ($key == 'path') {
$categories = explode('_', $value);
foreach ($categories as $category) {
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "seo_url WHERE `query` = 'category_id=" . (int)$category . "' AND store_id = '" . (int)$this->config->get('config_store_id') . "' AND language_id = '" . (int)$this->config->get('config_language_id') . "'");
if ($query->num_rows && $query->row['keyword']) {
$url .= '/' . $query->row['keyword'];
} else {
$url = '';
break;
}
}
unset($data[$key]);
}
}
}
if ($url) {
unset($data['route']);
$query = '';
if ($data) {
foreach ($data as $key => $value) {
$query .= '&' . rawurlencode((string)$key) . '=' . rawurlencode((is_array($value) ? http_build_query($value) : (string)$value));
}
if ($query) {
$query = '?' . str_replace('&', '&', trim($query, '&'));
}
}
return $url_info['scheme'] . '://' . $url_info['host'] . (isset($url_info['port']) ? ':' . $url_info['port'] : '') . str_replace('/index.php', '', $url_info['path']) . $url . $query;
} else {
return $link;
}
}
protected function convertedTaxedPrice($value, $tax_class_id, $currency) {
return number_format($this->currency->convert($this->tax->calculate($value, $tax_class_id, $this->config->get('config_tax')), $this->config->get('config_currency'), $currency), 2, '.', '');
}
protected function getFeedProducts($page, $language_id, $currency) {
$sql = $this->getFeedProductsQuery($page, $language_id);
$result = array();
$this->setRuntimeExceptionErrorHandler();
foreach ($this->db->query($sql)->rows as $row) {
try {
if (!empty($row['image']) && is_file(DIR_IMAGE . $row['image']) && is_readable(DIR_IMAGE . $row['image'])) {
$image = $this->resize($row['image'], 250, 250);
} else {
throw new \RuntimeException("Image does not exist or cannot be read.");
}
} catch (\RuntimeException $e) {
$this->output(sprintf("Error for product %s: %s", $row['model'], $e->getMessage()));
$image = $this->resize('no_image.png', 250, 250);
}
$url = new \Url($this->store_url, $this->store_url);
if ($this->config->get('config_seo_url')) {
$url->addRewrite($this);
}
$price = $this->convertedTaxedPrice($row['price'], $row['tax_class_id'], $currency);
$special_price = null;
if ($row['special_price'] !== null) {
$parts = explode('<[S]>', $row['special_price']);
$special_price = array(
'value' => $this->convertedTaxedPrice($parts[0], $row['tax_class_id'], $currency),
'currency' => $currency
);
if ($parts[1] >= '1970-01-01') {
$special_price['start'] = $parts[1];
}
if ($parts[2] >= '1970-01-01') {
$special_price['end'] = $parts[2];
}
}
$campaigns = array();
$custom_label_0 = '';
$custom_label_1 = '';
$custom_label_2 = '';
$custom_label_3 = '';
$custom_label_4 = '';
if (!empty($row['campaign_names'])) {
$campaigns = explode('<[S]>', $row['campaign_names']);
$i = 0;
do {
${'custom_label_' . ($i++)} = trim(strtolower(array_pop($campaigns)));
} while (!empty($campaigns));
}
$mpn = !empty($row['mpn']) ? $row['mpn'] : '';
if (!empty($row['upc'])) {
$gtin = $row['upc'];
} else if (!empty($row['ean'])) {
$gtin = $row['ean'];
} else if (!empty($row['jan'])) {
$gtin = $row['jan'];
} else if (!empty($row['isbn'])) {
$gtin = $row['isbn'];
} else {
$gtin = '';
}
$base_row = array(
'adult' => !empty($row['adult']) ? 'yes' : 'no',
'age_group' => !empty($row['age_group']) ? $row['age_group'] : '',
'availability' => (int)$row['quantity'] > 0 && !$this->config->get('config_maintenance') ? 'in stock' : 'out of stock',
'brand' => $this->sanitizeText($row['brand'], 70),
'color' => '',
'condition' => !empty($row['condition']) ? $row['condition'] : '',
'custom_label_0' => $this->sanitizeText($custom_label_0, 100),
'custom_label_1' => $this->sanitizeText($custom_label_1, 100),
'custom_label_2' => $this->sanitizeText($custom_label_2, 100),
'custom_label_3' => $this->sanitizeText($custom_label_3, 100),
'custom_label_4' => $this->sanitizeText($custom_label_4, 100),
'description' => $this->sanitizeText($row['description'], 5000),
'gender' => !empty($row['gender']) ? $row['gender'] : '',
'google_product_category' => !empty($row['google_product_category']) ? $row['google_product_category'] : '',
'id' => $this->sanitizeText($row['product_id'], 50),
'identifier_exists' => !empty($row['brand']) && !empty($mpn) ? 'yes' : 'no',
'image_link' => $this->sanitizeText($image, 2000),
'is_bundle' => !empty($row['is_bundle']) ? 'yes' : 'no',
'item_group_id' => $this->sanitizeText($row['product_id'], 50),
'link' => $this->sanitizeText(html_entity_decode($url->link('product/product', 'product_id=' . $row['product_id'], true), ENT_QUOTES, 'UTF-8'), 2000),
'mpn' => $this->sanitizeText($mpn, 70),
'gtin' => $this->sanitizeText($gtin, 14),
'multipack' => !empty($row['multipack']) && (int)$row['multipack'] >= 2 ? (int)$row['multipack'] : '', // Cannot be 1!!!
'price' => array(
'value' => $price,
'currency' => $currency
),
'size' => '',
'size_system' => !empty($row['size_system']) ? $row['size_system'] : '',
'size_type' => !empty($row['size_type']) ? $row['size_type'] : '',
'title' => $this->sanitizeText($row['name'], 150)
);
// Provide optional special price
if ($special_price !== null) {
$base_row['special_price'] = $special_price;
}
$groups = $this->getGroups($row['product_id'], $language_id, $row['color'], $row['size']);
foreach ($groups as $id => $group) {
$base_row['id'] = $id;
$base_row['color'] = $this->sanitizeText($group['color'], 40);
$base_row['size'] = $this->sanitizeText($group['size'], 100);
$result[] = $base_row;
}
}
$this->restoreErrorHandler();
return !empty($result) ? $result : null;
}
public function getGroups($product_id, $language_id, $color_id, $size_id) {
$options = array(
'color' => $this->getProductOptionValueNames($product_id, $language_id, $color_id),
'size' => $this->getProductOptionValueNames($product_id, $language_id, $size_id)
);
$result = array();
foreach ($this->combineOptions($options) as $group) {
$key = $product_id . '-' . md5(json_encode(array('color' => $group['color'], 'size' => $group['size'])));
$result[$key] = $group;
}
return $result;
}
public function getProductOptionValueNames($product_id, $language_id, $option_id) {
$sql = "SELECT DISTINCT pov.product_option_value_id, ovd.name FROM `" . DB_PREFIX . "product_option_value` pov LEFT JOIN `" . DB_PREFIX . "option_value_description` ovd ON (ovd.option_value_id = pov.option_value_id) WHERE pov.product_id=" . (int)$product_id . " AND pov.option_id=" . (int)$option_id . " AND ovd.language_id=" . (int)$language_id;
$result = $this->db->query($sql);
if ($result->num_rows > 0) {
$return = array();
foreach ($result->rows as $row) {
$text = $this->sanitizeText($row['name'], 100);
$name = implode('/', array_slice(array_filter(array_map('trim', preg_split('~[,/;]+~i', $text))), 0, 3));
$return[$row['product_option_value_id']] = $name;
}
return $return;
}
return array('');
}
public function applyFilter(&$sql, &$data) {
if (!empty($data['filter_product_name'])) {
$sql .= " AND pd.name LIKE '" . $this->db->escape($data['filter_product_name']) . "%'";
}
if (!empty($data['filter_product_model'])) {
$sql .= " AND p.model LIKE '" . $this->db->escape($data['filter_product_model']) . "%'";
}
if (!empty($data['filter_category_id'])) {
$sql .= " AND p.product_id IN (SELECT p2c_t.product_id FROM `" . DB_PREFIX . "category_path` cp_t LEFT JOIN `" . DB_PREFIX . "product_to_category` p2c_t ON (p2c_t.category_id=cp_t.category_id) WHERE cp_t.path_id=" . (int)$data['filter_category_id'] . ")";
}
if (isset($data['filter_is_modified']) && $data['filter_is_modified'] !== "") {
$sql .= " AND p.product_id IN (SELECT pag_t.product_id FROM `" . DB_PREFIX . "googleshopping_product` pag_t WHERE pag_t.is_modified=" . (int)$data['filter_is_modified'] . ")";
}
if (!empty($data['filter_store_id'])) {
$sql .= " AND p.product_id IN (SELECT p2s_t.product_id FROM `" . DB_PREFIX . "product_to_store` p2s_t WHERE p2s_t.store_id=" . (int)$data['filter_store_id'] . ")";
}
}
public function getProducts($data, $store_id) {
$sql = "SELECT pag.*, p.product_id, p.image, pd.name, p.model FROM `" . DB_PREFIX . "product` p LEFT JOIN `" . DB_PREFIX . "product_description` pd ON (p.product_id = pd.product_id) LEFT JOIN `" . DB_PREFIX . "googleshopping_product` pag ON (pag.product_id = p.product_id AND pag.store_id = " . (int)$store_id . ") WHERE pag.store_id IS NOT NULL AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'";
$this->applyFilter($sql, $data);
$sql .= " GROUP BY p.product_id";
$sort_data = array(
'name',
'model',
'impressions',
'clicks',
'cost',
'conversions',
'conversion_value',
'has_issues',
'destination_status'
);
if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
$sql .= " ORDER BY " . $data['sort'];
} else {
$sql .= " ORDER BY name";
}
if (isset($data['order']) && ($data['order'] == 'DESC')) {
$sql .= " DESC";
} else {
$sql .= " ASC";
}
if (isset($data['start']) || isset($data['limit'])) {
if ($data['start'] < 0) {
$data['start'] = 0;
}
if ($data['limit'] < 1) {
$data['limit'] = 20;
}
$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
}
return $this->db->query($sql)->rows;
}
public function getTotalProducts($data, $store_id) {
$sql = "SELECT COUNT(*) as total FROM `" . DB_PREFIX . "product` p LEFT JOIN `" . DB_PREFIX . "product_description` pd ON (p.product_id = pd.product_id) LEFT JOIN `" . DB_PREFIX . "googleshopping_product` pag ON (pag.product_id = p.product_id AND pag.store_id = " . (int)$store_id . ") WHERE pag.store_id IS NOT NULL AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'";
$this->applyFilter($sql, $data);
return (int)$this->db->query($sql)->row['total'];
}
public function getProductIds($data, $store_id) {
$result = array();
$this->load->model('localisation/language');
foreach ($this->getProducts($data, $store_id) as $row) {
$product_id = (int)$row['product_id'];
if (!in_array($product_id, $result)) {
$result[] = $product_id;
}
}
return $result;
}
public function clearProductStatuses($product_ids, $store_id) {
$sql = "UPDATE `" . DB_PREFIX . "googleshopping_product_status` SET `destination_statuses`='', `data_quality_issues`='', `item_level_issues`='', `google_expiration_date`=0 WHERE `product_id` IN (" . $this->productIdsToIntegerExpression($product_ids) . ") AND `store_id`=" . (int)$store_id;
$this->db->query($sql);
$sql = "UPDATE `" . DB_PREFIX . "googleshopping_product` SET `has_issues`=0, `destination_status`='pending' WHERE `product_id` IN (" . $this->productIdsToIntegerExpression($product_ids) . ") AND `store_id`=" . (int)$store_id;
$this->db->query($sql);
}
public function productIdsToIntegerExpression($product_ids) {
return implode(",", array_map(array($this, 'integer'), $product_ids));
}
public function integer(&$product_id) {
if (!is_numeric($product_id)) {
return 0;
} else {
return (int)$product_id;
}
}
public function cron() {
$this->enableErrorReporting();
$this->load->config('googleshopping/googleshopping');
$report = array();
$report[] = $this->output("Starting CRON task for " . $this->getStoreUrl());
try {
$report[] = $this->output("Refreshing access token.");
$this->isConnected();
} catch (\RuntimeException $e) {
$report[] = $this->output($e->getMessage());
}
$default_config_tax = $this->config->get("config_tax");
$default_config_store_id = $this->config->get("config_store_id");
$default_config_language_id = $this->config->get("config_language_id");
$default_config_seo_url = $this->config->get("config_seo_url");
// Do product feed uploads
foreach ($this->getJobs() as $job) {
try {
$report[] = $this->output("Uploading product feed. Work ID: " . $job['work_id']);
// Set the tax context for the job
if (in_array("US", $job['countries'])) {
// In case the feed is for the US, disable taxes because they are already configured on the merchant level by the extension
$this->config->set("config_tax", 0);
}
// Set the store and language context for the job
$this->config->set("config_store_id", $this->store_id);
$this->config->set("config_language_id", $job['language_id']);
$this->config->set("config_seo_url", $this->model_setting_setting->getSettingValue("config_seo_url", $this->store_id));
// Do the CRON job
$count = $this->doJob($job);
// Reset the taxes, store, and language to their original state
$this->config->set("config_tax", $default_config_tax);
$this->config->set("config_store_id", $default_config_store_id);
$this->config->set("config_language_id", $default_config_language_id);
$this->config->set("config_seo_url", $default_config_seo_url);
$report[] = $this->output("Uploaded count: " . $count);
} catch (\RuntimeException $e) {
$report[] = $this->output($e->getMessage());
}
}
// Reset the taxes, store, and language to their original state
$this->config->set("config_tax", $default_config_tax);
$this->config->set("config_store_id", $default_config_store_id);
$this->config->set("config_language_id", $default_config_language_id);
$this->config->set("config_seo_url", $default_config_seo_url);
// Pull product reports
$report[] = $this->output("Fetching product reports.");
try {
$report_count = 0;
$page = 0;
$this->clearReports();
while (null !== $product_variation_ids = $this->getProductVariationIds(++$page)) {
foreach (array_chunk($product_variation_ids, (int)$this->config->get('advertise_google_report_limit')) as $chunk) {
$product_reports = $this->getProductReports($chunk);
if (!empty($product_reports)) {
$this->updateProductReports($product_reports, $this->store_id);
$report_count += count($product_reports);
}
}
}
} catch (\RuntimeException $e) {
$report[] = $this->output($e->getMessage());
}
$report[] = $this->output("Fetched report count: " . $report_count);
// Pull product statuses
$report[] = $this->output("Fetching product statuses.");
$page = 1;
$status_count = 0;
do {
$filter_data = array(
'start' => ($page - 1) * $this->config->get('advertise_google_product_status_limit'),
'limit' => $this->config->get('advertise_google_product_status_limit')
);
$page++;
$product_variation_target_specific_ids = $this->getProductVariationTargetSpecificIds($filter_data);
try {
// Fetch latest statuses from the API
if (!empty($product_variation_target_specific_ids)) {
$product_ids = $this->getProductIds($filter_data, $this->store_id);
$this->clearProductStatuses($product_ids, $this->store_id);
foreach (array_chunk($product_variation_target_specific_ids, (int)$this->config->get('advertise_google_product_status_limit')) as $chunk) {
$product_statuses = $this->getProductStatuses($chunk);
if (!empty($product_statuses)) {
$this->updateProductStatuses($product_statuses);
$status_count += count($product_statuses);
}
}
}
} catch (\RuntimeException $e) {
$report[] = $this->output($e->getMessage());
}
} while (!empty($product_variation_target_specific_ids));
$report[] = $this->output("Fetched status count: " . $status_count);
$report[] = $this->output("CRON finished!");
$this->applyNewSetting('advertise_google_cron_last_executed', time());
$this->sendEmailReport($report);
}
public function getProductVariationTargetSpecificIds($data) {
$result = array();
$targets = $this->getTargets($this->store_id);
foreach ($this->getProducts($data, $this->store_id) as $row) {
foreach ($targets as $target) {
foreach ($target['feeds'] as $feed) {
$language_code = $feed['language'];
$language_id = $this->getSupportedLanguageId($language_code);
$groups = $this->getGroups($row['product_id'], $language_id, $row['color'], $row['size']);
foreach (array_keys($groups) as $id) {
$id_parts = array();
$id_parts[] = 'online';
$id_parts[] = $language_code;
$id_parts[] = $target['country']['code'];
$id_parts[] = $id;
$result_id = implode(':', $id_parts);
if (!in_array($result_id, $result)) {
$result[] = $result_id;
}
}
}
}
}
return $result;
}
public function updateProductReports($reports) {
$values = array();
foreach ($reports as $report) {
$entry = array();
$entry['product_id'] = $this->getProductIdFromOfferId($report['offer_id']);
$entry['store_id'] = (int)$this->store_id;
$entry['impressions'] = (int)$report['impressions'];
$entry['clicks'] = (int)$report['clicks'];
$entry['conversions'] = (int)$report['conversions'];
$entry['cost'] = ((int)$report['cost']) / self::MICROAMOUNT;
$entry['conversion_value'] = (float)$report['conversion_value'];
$values[] = "(" . implode(",", $entry) . ")";
}
$sql = "INSERT INTO `" . DB_PREFIX . "googleshopping_product` (`product_id`, `store_id`, `impressions`, `clicks`, `conversions`, `cost`, `conversion_value`) VALUES " . implode(',', $values) . " ON DUPLICATE KEY UPDATE `impressions`=`impressions` + VALUES(`impressions`), `clicks`=`clicks` + VALUES(`clicks`), `conversions`=`conversions` + VALUES(`conversions`), `cost`=`cost` + VALUES(`cost`), `conversion_value`=`conversion_value` + VALUES(`conversion_value`)";
$this->db->query($sql);
}
public function updateProductStatuses($statuses) {
$product_advertise_google = array();
$product_advertise_google_status = array();
$product_level_entries = array();
$entry_statuses = array();
foreach ($statuses as $status) {
$product_id = $this->getProductIdFromTargetSpecificId($status['productId']);
$product_variation_id = $this->getProductVariationIdFromTargetSpecificId($status['productId']);
if (!isset($product_level_entries[$product_id])) {
$product_level_entries[$product_id] = array(
'product_id' => (int)$product_id,
'store_id' => (int)$this->store_id,
'has_issues' => 0,
'destination_status' => 'pending'
);
}
foreach ($status['destinationStatuses'] as $destination_status) {
if (!$destination_status['approvalPending']) {
switch ($destination_status['approvalStatus']) {
case 'approved' :
if ($product_level_entries[$product_id]['destination_status'] == 'pending') {
$product_level_entries[$product_id]['destination_status'] = 'approved';
}
break;
case 'disapproved' :
$product_level_entries[$product_id]['destination_status'] = 'disapproved';
break;
}
}
}
if (!$product_level_entries[$product_id]['has_issues']) {
if (!empty($status['dataQualityIssues']) || !empty($status['itemLevelIssues'])) {
$product_level_entries[$product_id]['has_issues'] = 1;
}
}
if (!isset($entry_statuses[$product_variation_id])) {
$entry_statuses[$product_variation_id] = array();
$entry_statuses[$product_variation_id]['product_id'] = (int)$product_id;
$entry_statuses[$product_variation_id]['store_id'] = (int)$this->store_id;
$entry_statuses[$product_variation_id]['product_variation_id'] = "'" . $this->db->escape($product_variation_id) . "'";
$entry_statuses[$product_variation_id]['destination_statuses'] = array();
$entry_statuses[$product_variation_id]['data_quality_issues'] = array();
$entry_statuses[$product_variation_id]['item_level_issues'] = array();
$entry_statuses[$product_variation_id]['google_expiration_date'] = (int)strtotime($status['googleExpirationDate']);
}
$entry_statuses[$product_variation_id]['destination_statuses'] = array_merge(
$entry_statuses[$product_variation_id]['destination_statuses'],
!empty($status['destinationStatuses']) ? $status['destinationStatuses'] : array()
);
$entry_statuses[$product_variation_id]['data_quality_issues'] = array_merge(
$entry_statuses[$product_variation_id]['data_quality_issues'],
!empty($status['dataQualityIssues']) ? $status['dataQualityIssues'] : array()
);
$entry_statuses[$product_variation_id]['item_level_issues'] = array_merge(
$entry_statuses[$product_variation_id]['item_level_issues'],
!empty($status['itemLevelIssues']) ? $status['itemLevelIssues'] : array()
);
}
foreach ($entry_statuses as &$entry_status) {
$entry_status['destination_statuses'] = "'" . $this->db->escape(json_encode($entry_status['destination_statuses'])) . "'";
$entry_status['data_quality_issues'] = "'" . $this->db->escape(json_encode($entry_status['data_quality_issues'])) . "'";
$entry_status['item_level_issues'] = "'" . $this->db->escape(json_encode($entry_status['item_level_issues'])) . "'";
$product_advertise_google_status[] = "(" . implode(",", $entry_status) . ")";
}
$sql = "INSERT INTO `" . DB_PREFIX . "googleshopping_product_status` (`product_id`, `store_id`, `product_variation_id`, `destination_statuses`, `data_quality_issues`, `item_level_issues`, `google_expiration_date`) VALUES " . implode(',', $product_advertise_google_status) . " ON DUPLICATE KEY UPDATE `destination_statuses`=VALUES(`destination_statuses`), `data_quality_issues`=VALUES(`data_quality_issues`), `item_level_issues`=VALUES(`item_level_issues`), `google_expiration_date`=VALUES(`google_expiration_date`)";
$this->db->query($sql);
foreach ($product_level_entries as $entry) {
$entry['destination_status'] = "'" . $this->db->escape($entry['destination_status']) . "'";
$product_advertise_google[] = "(" . implode(",", $entry) . ")";
}
$sql = "INSERT INTO `" . DB_PREFIX . "googleshopping_product` (`product_id`, `store_id`, `has_issues`, `destination_status`) VALUES " . implode(',', $product_advertise_google) . " ON DUPLICATE KEY UPDATE `has_issues`=VALUES(`has_issues`), `destination_status`=VALUES(`destination_status`)";
$this->db->query($sql);
}
protected function memoryLimitInBytes() {
$memory_limit = ini_get('memory_limit');
if (preg_match('/^(\d+)(.)$/', $memory_limit, $matches)) {
if ($matches[2] == 'G') {
$memory_limit = (int)$matches[1] * 1024 * 1024 * 1024; // nnnG -> nnn GB
} else if ($matches[2] == 'M') {
$memory_limit = (int)$matches[1] * 1024 * 1024; // nnnM -> nnn MB
} else if ($matches[2] == 'K') {
$memory_limit = (int)$matches[1] * 1024; // nnnK -> nnn KB
}
}
return (int)$memory_limit;
}
protected function enableErrorReporting() {
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
}
protected function getProductIdFromTargetSpecificId($target_specific_id) {
return (int)preg_replace('/^online:[a-z]{2}:[A-Z]{2}:(\d+)-[a-f0-9]{32}$/', '$1', $target_specific_id);
}
protected function getProductVariationIdFromTargetSpecificId($target_specific_id) {
return preg_replace('/^online:[a-z]{2}:[A-Z]{2}:(\d+-[a-f0-9]{32})$/', '$1', $target_specific_id);
}
protected function getProductIdFromOfferId($offer_id) {
return (int)preg_replace('/^(\d+)-[a-f0-9]{32}$/', '$1', $offer_id);
}
protected function clearReports() {
$sql = "UPDATE `" . DB_PREFIX . "googleshopping_product` SET `impressions`=0, `clicks`=0, `conversions`=0, `cost`=0.0000, `conversion_value`=0.0000 WHERE `store_id`=" . (int)$this->store_id;
$this->db->query($sql);
}
protected function getJobs() {
$jobs = array();
if ($this->setting->has('advertise_google_work') && is_array($this->setting->get('advertise_google_work'))) {
$this->load->model('extension/advertise/google');
foreach ($this->setting->get('advertise_google_work') as $work) {
$supported_language_id = $this->getSupportedLanguageId($work['language']);
$supported_currency_id = $this->getSupportedCurrencyId($work['currency']);
if (!empty($supported_language_id) && !empty($supported_currency_id)) {
$currency_info = $this->getCurrency($supported_currency_id);
$jobs[] = array(
'work_id' => $work['work_id'],
'countries' => isset($work['countries']) && is_array($work['countries']) ? $work['countries'] : array(),
'language_id' => $supported_language_id,
'currency' => $currency_info['code']
);
}
}
}
return $jobs;
}
protected function output($message) {
$log_message = date('Y-m-d H:i:s - ') . $message;
if (defined('STDOUT')) {
fwrite(STDOUT, $log_message . PHP_EOL);
} else {
echo $log_message . '