diff options
Diffstat (limited to 'public/admin/model/extension/advertise/google.php')
| -rw-r--r-- | public/admin/model/extension/advertise/google.php | 697 |
1 files changed, 697 insertions, 0 deletions
diff --git a/public/admin/model/extension/advertise/google.php b/public/admin/model/extension/advertise/google.php new file mode 100644 index 0000000..467e90b --- /dev/null +++ b/public/admin/model/extension/advertise/google.php @@ -0,0 +1,697 @@ +<?php + +use \googleshopping\exception\Connection as ConnectionException; +use \googleshopping\Googleshopping; + +class ModelExtensionAdvertiseGoogle extends Model { + private $events = array( + 'admin/view/common/column_left/before' => array( + 'extension/advertise/google/admin_link', + ), + 'admin/model/catalog/product/addProduct/after' => array( + 'extension/advertise/google/addProduct', + ), + 'admin/model/catalog/product/copyProduct/after' => array( + 'extension/advertise/google/copyProduct', + ), + 'admin/model/catalog/product/deleteProduct/after' => array( + 'extension/advertise/google/deleteProduct', + ), + 'catalog/controller/checkout/success/before' => array( + 'extension/advertise/google/before_checkout_success' + ), + 'catalog/view/common/header/after' => array( + 'extension/advertise/google/google_global_site_tag' + ), + 'catalog/view/common/success/after' => array( + 'extension/advertise/google/google_dynamic_remarketing_purchase' + ), + 'catalog/view/product/product/after' => array( + 'extension/advertise/google/google_dynamic_remarketing_product' + ), + 'catalog/view/product/search/after' => array( + 'extension/advertise/google/google_dynamic_remarketing_searchresults' + ), + 'catalog/view/product/category/after' => array( + 'extension/advertise/google/google_dynamic_remarketing_category' + ), + 'catalog/view/common/home/after' => array( + 'extension/advertise/google/google_dynamic_remarketing_home' + ), + 'catalog/view/checkout/cart/after' => array( + 'extension/advertise/google/google_dynamic_remarketing_cart' + ) + ); + + private $rename_tables = array( + 'advertise_google_target' => 'googleshopping_target', + 'category_to_google_product_category' => 'googleshopping_category', + 'product_advertise_google_status' => 'googleshopping_product_status', + 'product_advertise_google_target' => 'googleshopping_product_target', + 'product_advertise_google' => 'googleshopping_product' + ); + + private $table_columns = array( + 'googleshopping_target' => array( + 'advertise_google_target_id', + 'store_id', + 'campaign_name', + 'country', + 'budget', + 'feeds', + 'status' + ), + 'googleshopping_category' => array( + 'google_product_category', + 'store_id', + 'category_id' + ), + 'googleshopping_product_status' => array( + 'product_id', + 'store_id', + 'product_variation_id', + 'destination_statuses', + 'data_quality_issues', + 'item_level_issues', + 'google_expiration_date' + ), + 'googleshopping_product_target' => array( + 'product_id', + 'store_id', + 'advertise_google_target_id' + ), + 'googleshopping_product' => array( + 'product_advertise_google_id', + 'product_id', + 'store_id', + 'has_issues', + 'destination_status', + 'impressions', + 'clicks', + 'conversions', + 'cost', + 'conversion_value', + 'google_product_category', + 'condition', + 'adult', + 'multipack', + 'is_bundle', + 'age_group', + 'color', + 'gender', + 'size_type', + 'size_system', + 'size', + 'is_modified' + ) + ); + + public function isAppIdUsed($app_id, $store_id) { + $sql = "SELECT `store_id` FROM `" . DB_PREFIX . "setting` WHERE `key`='advertise_google_app_id' AND `value`='" . $this->db->escape($store_id) . "' AND `store_id`!=" . (int)$store_id . " LIMIT 1"; + + $result = $this->db->query($sql); + + if ($result->num_rows > 0) { + try { + $googleshopping = new Googleshopping($this->registry, (int)$result->row['store_id']); + + return $googleshopping->isConnected(); + } catch (\RuntimeException $e) { + return false; + } + } + + return false; + } + + public function getFinalProductId() { + $sql = "SELECT product_id FROM `" . DB_PREFIX . "product` ORDER BY product_id DESC LIMIT 1"; + + $result = $this->db->query($sql); + + if ($result->num_rows > 0) { + return (int)$result->row['product_id']; + } + + return null; + } + + public function isAnyProductCategoryModified($store_id) { + $sql = "SELECT pag.is_modified FROM `" . DB_PREFIX . "googleshopping_product` pag WHERE pag.google_product_category IS NOT NULL AND pag.store_id=" . (int)$store_id . " LIMIT 0,1"; + + return $this->db->query($sql)->num_rows > 0; + } + + public function getAdvertisedCount($store_id) { + $result = $this->db->query("SELECT COUNT(product_id) as total FROM `" . DB_PREFIX . "googleshopping_product_target` WHERE store_id=" . (int)$store_id . " GROUP BY `product_id`"); + + return $result->num_rows > 0 ? (int)$result->row['total'] : 0; + } + + public function getMapping($store_id) { + $sql = "SELECT * FROM `" . DB_PREFIX . "googleshopping_category` WHERE store_id=" . (int)$store_id; + + return $this->db->query($sql)->rows; + } + + public function setCategoryMapping($google_product_category, $store_id, $category_id) { + $sql = "INSERT INTO `" . DB_PREFIX . "googleshopping_category` SET `google_product_category`='" . $this->db->escape($google_product_category) . "', `store_id`=" . (int)$store_id . ", `category_id`=" . (int)$category_id . " ON DUPLICATE KEY UPDATE `category_id`=" . (int)$category_id; + + $this->db->query($sql); + } + + public function getMappedCategory($google_product_category, $store_id) { + $sql = "SELECT GROUP_CONCAT(cd.name ORDER BY cp.level SEPARATOR ' > ') AS name, cp.category_id FROM " . DB_PREFIX . "category_path cp LEFT JOIN " . DB_PREFIX . "category_description cd ON (cp.path_id = cd.category_id) LEFT JOIN `" . DB_PREFIX . "googleshopping_category` c2gpc ON (c2gpc.category_id = cp.category_id) WHERE cd.language_id=" . (int)$this->config->get('config_language_id') . " AND c2gpc.google_product_category='" . $this->db->escape($google_product_category) . "' AND c2gpc.store_id=" . (int)$store_id; + + $result = $this->db->query($sql); + + if ($result->num_rows > 0) { + return $result->row; + } + + return null; + } + + public function getProductByProductAdvertiseGoogleId($product_advertise_google_id) { + $sql = "SELECT pag.product_id FROM `" . DB_PREFIX . "googleshopping_product` pag WHERE pag.product_advertise_google_id=" . (int)$product_advertise_google_id; + + $result = $this->db->query($sql); + + if ($result->num_rows) { + $this->load->model('catalog/product'); + + return $this->model_catalog_product->getProduct($result->row['product_id']); + } + } + + public function getProductAdvertiseGoogle($product_advertise_google_id) { + $sql = "SELECT pag.* FROM `" . DB_PREFIX . "googleshopping_product` pag WHERE pag.product_advertise_google_id=" . (int)$product_advertise_google_id; + + return $this->db->query($sql)->row; + } + + public function hasActiveTarget($store_id) { + $sql = "SELECT agt.advertise_google_target_id FROM `" . DB_PREFIX . "googleshopping_target` agt WHERE agt.store_id=" . (int)$store_id . " AND agt.status='active' LIMIT 1"; + + return $this->db->query($sql)->num_rows > 0; + } + + public function getRequiredFieldsByProductIds($product_ids, $store_id) { + $this->load->config('googleshopping/googleshopping'); + + $result = array(); + $countries = $this->getTargetCountriesByProductIds($product_ids, $store_id); + + foreach ($countries as $country) { + foreach ($this->config->get('advertise_google_country_required_fields') as $field => $requirements) { + if ( + (!empty($requirements['countries']) && in_array($country, $requirements['countries'])) + || + (is_array($requirements['countries']) && empty($requirements['countries'])) + ) { + $result[$field] = $requirements; + } + } + } + + return $result; + } + + public function getRequiredFieldsByFilter($data, $store_id) { + $this->load->config('googleshopping/googleshopping'); + + $result = array(); + $countries = $this->getTargetCountriesByFilter($data, $store_id); + + foreach ($countries as $country) { + foreach ($this->config->get('advertise_google_country_required_fields') as $field => $requirements) { + if ( + (!empty($requirements['countries']) && in_array($country, $requirements['countries'])) + || + (is_array($requirements['countries']) && empty($requirements['countries'])) + ) { + $result[$field] = $requirements; + } + } + } + + return $result; + } + + public function getTargetCountriesByProductIds($product_ids, $store_id) { + $sql = "SELECT DISTINCT agt.country FROM `" . DB_PREFIX . "googleshopping_product_target` pagt LEFT JOIN `" . DB_PREFIX . "googleshopping_target` agt ON (agt.advertise_google_target_id = pagt.advertise_google_target_id AND agt.store_id = pagt.store_id) WHERE pagt.product_id IN (" . $this->googleshopping->productIdsToIntegerExpression($product_ids) . ") AND pagt.store_id=" . (int)$store_id; + + return array_map(array($this, 'country'), $this->db->query($sql)->rows); + } + + public function getTargetCountriesByFilter($data, $store_id) { + $sql = "SELECT DISTINCT agt.country FROM `" . DB_PREFIX . "googleshopping_product_target` pagt LEFT JOIN `" . DB_PREFIX . "googleshopping_target` agt ON (agt.advertise_google_target_id = pagt.advertise_google_target_id AND agt.store_id = pagt.store_id) LEFT JOIN `" . DB_PREFIX . "product` p ON (pagt.product_id = p.product_id) LEFT JOIN `" . DB_PREFIX . "product_description` pd ON (pd.product_id = pagt.product_id) WHERE pagt.store_id=" . (int)$store_id . " AND pd.language_id=" . (int)$this->config->get('config_language_id'); + + $this->googleshopping->applyFilter($sql, $data); + + return array_map(array($this, 'country'), $this->db->query($sql)->rows); + } + + public function getProductOptionsByProductIds($product_ids) { + $sql = "SELECT po.option_id, od.name FROM `" . DB_PREFIX . "product_option` po LEFT JOIN `" . DB_PREFIX . "option_description` od ON (od.option_id=po.option_id AND od.language_id=" . (int)$this->config->get('config_language_id') . ") LEFT JOIN `" . DB_PREFIX . "option` o ON (o.option_id = po.option_id) WHERE o.type IN ('select', 'radio') AND po.product_id IN (" . $this->googleshopping->productIdsToIntegerExpression($product_ids) . ")"; + + return $this->db->query($sql)->rows; + } + + public function getProductOptionsByFilter($data) { + $sql = "SELECT DISTINCT po.option_id, od.name FROM `" . DB_PREFIX . "product_option` po LEFT JOIN `" . DB_PREFIX . "option_description` od ON (od.option_id=po.option_id AND od.language_id=" . (int)$this->config->get('config_language_id') . ") LEFT JOIN `" . DB_PREFIX . "option` o ON (o.option_id = po.option_id) LEFT JOIN `" . DB_PREFIX . "product` p ON (po.product_id = p.product_id) LEFT JOIN `" . DB_PREFIX . "product_description` pd ON (pd.product_id = po.product_id) WHERE o.type IN ('select', 'radio') AND pd.language_id=" . (int)$this->config->get('config_language_id'); + + $this->googleshopping->applyFilter($sql, $data); + + return $this->db->query($sql)->rows; + } + + public function addTarget($target, $store_id) { + $sql = "INSERT INTO `" . DB_PREFIX . "googleshopping_target` SET `store_id`=" . (int)$store_id . ", `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'])) . "', `date_added`=NOW(), `roas`=" . (int)$target['roas'] . " , `status`='" . $this->db->escape($target['status']) . "'"; + + $this->db->query($sql); + + return $this->db->getLastId(); + } + + public function deleteProducts($product_ids) { + $sql = "DELETE FROM `" . DB_PREFIX . "googleshopping_product` WHERE `product_id` IN (" . $this->googleshopping->productIdsToIntegerExpression($product_ids) . ")"; + + $this->db->query($sql); + + $sql = "DELETE FROM `" . DB_PREFIX . "googleshopping_product_target` WHERE `product_id` IN (" . $this->googleshopping->productIdsToIntegerExpression($product_ids) . ")"; + + $this->db->query($sql); + + $sql = "DELETE FROM `" . DB_PREFIX . "googleshopping_product_status` WHERE `product_id` IN (" . $this->googleshopping->productIdsToIntegerExpression($product_ids) . ")"; + + $this->db->query($sql); + + return true; + } + + public function setAdvertisingBySelect($post_product_ids, $post_target_ids, $store_id) { + if (!empty($post_product_ids)) { + $product_ids = array_map(array($this->googleshopping, 'integer'), $post_product_ids); + + $product_ids_expression = implode(',', $product_ids); + + $this->db->query("DELETE FROM `" . DB_PREFIX . "googleshopping_product_target` WHERE product_id IN (" . $product_ids_expression . ") AND store_id=" . (int)$store_id); + + if (!empty($post_target_ids)) { + $target_ids = array_map(array($this->googleshopping, 'integer'), $post_target_ids); + + $values = array(); + + foreach ($product_ids as $product_id) { + foreach ($target_ids as $target_id) { + $values[] = '(' . $product_id . ',' . $store_id . ',' . $target_id . ')'; + } + } + + $sql = "INSERT INTO `" . DB_PREFIX . "googleshopping_product_target` (`product_id`, `store_id`, `advertise_google_target_id`) VALUES " . implode(',', $values); + + $this->db->query($sql); + } + } + } + + public function setAdvertisingByFilter($data, $post_target_ids, $store_id) { + $sql = "DELETE pagt FROM `" . DB_PREFIX . "googleshopping_product_target` pagt LEFT JOIN `" . DB_PREFIX . "product` p ON (pagt.product_id = p.product_id) LEFT JOIN `" . DB_PREFIX . "product_description` pd ON (pd.product_id = p.product_id) WHERE pd.language_id=" . (int)$this->config->get('config_language_id'); + + $this->googleshopping->applyFilter($sql, $data); + + $this->db->query($sql); + + if (!empty($post_target_ids)) { + $target_ids = array_map(array($this->googleshopping, 'integer'), $post_target_ids); + + $insert_sql = "SELECT p.product_id, " . (int)$store_id . " as store_id, '{TARGET_ID}' as advertise_google_target_id FROM `" . DB_PREFIX . "product` p LEFT JOIN `" . DB_PREFIX . "product_description` pd ON (pd.product_id = p.product_id) WHERE pd.language_id=" . (int)$this->config->get('config_language_id'); + + $this->googleshopping->applyFilter($insert_sql, $data); + + foreach ($target_ids as $target_id) { + $sql = "INSERT INTO `" . DB_PREFIX . "googleshopping_product_target` (`product_id`, `store_id`, `advertise_google_target_id`) " . str_replace('{TARGET_ID}', (string)$target_id, $insert_sql); + + $this->db->query($sql); + } + } + } + + public function insertNewProducts($product_ids = array(), $store_id) { + $sql = "INSERT INTO `" . DB_PREFIX . "googleshopping_product` (`product_id`, `store_id`, `google_product_category`) SELECT p.product_id, p2s.store_id, (SELECT c2gpc.google_product_category FROM `" . DB_PREFIX . "product_to_category` p2c LEFT JOIN `" . DB_PREFIX . "category_path` cp ON (p2c.category_id = cp.category_id) LEFT JOIN `" . DB_PREFIX . "googleshopping_category` c2gpc ON (c2gpc.category_id = cp.path_id AND c2gpc.store_id = " . (int)$store_id . ") WHERE p2c.product_id = p.product_id AND c2gpc.google_product_category IS NOT NULL ORDER BY cp.level DESC LIMIT 0,1) as `google_product_category` FROM `" . DB_PREFIX . "product` p LEFT JOIN `" . DB_PREFIX . "product_to_store` p2s ON (p2s.product_id = p.product_id AND p2s.store_id = " . (int)$store_id . ") LEFT JOIN `" . DB_PREFIX . "googleshopping_product` pag ON (pag.product_id = p.product_id AND pag.store_id=p2s.store_id) WHERE pag.product_id IS NULL AND p2s.store_id IS NOT NULL"; + + if (!empty($product_ids)) { + $sql .= " AND p.product_id IN (" . $this->googleshopping->productIdsToIntegerExpression($product_ids) . ")"; + } + + $this->db->query($sql); + } + + public function updateGoogleProductCategoryMapping($store_id) { + $sql = "INSERT INTO `" . DB_PREFIX . "googleshopping_product` (`product_id`, `store_id`, `google_product_category`) SELECT p.product_id, " . (int)$store_id . " as store_id, (SELECT c2gpc.google_product_category FROM `" . DB_PREFIX . "product_to_category` p2c LEFT JOIN `" . DB_PREFIX . "category_path` cp ON (p2c.category_id = cp.category_id) LEFT JOIN `" . DB_PREFIX . "googleshopping_category` c2gpc ON (c2gpc.category_id = cp.path_id AND c2gpc.store_id = " . (int)$store_id . ") WHERE p2c.product_id = p.product_id AND c2gpc.google_product_category IS NOT NULL ORDER BY cp.level DESC LIMIT 0,1) as `google_product_category` FROM `" . DB_PREFIX . "product` p LEFT JOIN `" . DB_PREFIX . "googleshopping_product` pag ON (pag.product_id = p.product_id) WHERE pag.product_id IS NOT NULL ON DUPLICATE KEY UPDATE `google_product_category`=VALUES(`google_product_category`)"; + + $this->db->query($sql); + } + + public function updateSingleProductFields($data) { + $values = array(); + + $entry = array(); + $entry['product_id'] = (int)$data['product_id']; + $entry = array_merge($entry, $this->makeInsertData($data)); + + $values[] = "(" . implode(",", $entry) . ")"; + + $sql = "INSERT INTO `" . DB_PREFIX . "googleshopping_product` (`product_id`, `store_id`, `google_product_category`, `condition`, `adult`, `multipack`, `is_bundle`, `age_group`, `color`, `gender`, `size_type`, `size_system`, `size`, `is_modified`) VALUES " . implode(',', $values) . " ON DUPLICATE KEY UPDATE " . $this->makeOnDuplicateKeyData(); + + $this->db->query($sql); + } + + public function updateMultipleProductFields($filter_data, $data) { + $insert_sql = "SELECT p.product_id, {INSERT_DATA} FROM `" . DB_PREFIX . "product` p LEFT JOIN `" . DB_PREFIX . "product_description` pd ON (pd.product_id = p.product_id) WHERE pd.language_id=" . (int)$this->config->get('config_language_id'); + + $this->googleshopping->applyFilter($insert_sql, $filter_data); + + $insert_data = array(); + $keys[] = "`product_id`"; + + foreach ($this->makeInsertData($data) as $key => $value) { + $insert_data[] = $value . " as `" . $key . "`"; + $keys[] = "`" . $key . "`"; + } + + $sql = "INSERT INTO `" . DB_PREFIX . "googleshopping_product` (" . implode(", ", $keys) . ") " . str_replace('{INSERT_DATA}', implode(", ", $insert_data), $insert_sql) . " ON DUPLICATE KEY UPDATE " . $this->makeOnDuplicateKeyData(); + + $this->db->query($sql); + } + + protected function makeInsertData($data) { + $insert_data = array(); + + $insert_data['store_id'] = (int)$data['store_id']; + $insert_data['google_product_category'] = "'" . $this->db->escape($data['google_product_category']) . "'"; + $insert_data['condition'] = "'" . $this->db->escape($data['condition']) . "'"; + $insert_data['adult'] = (int)$data['adult']; + $insert_data['multipack'] = (int)$data['multipack']; + $insert_data['is_bundle'] = (int)$data['is_bundle']; + $insert_data['age_group'] = "'" . $this->db->escape($data['age_group']) . "'"; + $insert_data['color'] = (int)$data['color']; + $insert_data['gender'] = "'" . $this->db->escape($data['gender']) . "'"; + $insert_data['size_type'] = "'" . $this->db->escape($data['size_type']) . "'"; + $insert_data['size_system'] = "'" . $this->db->escape($data['size_system']) . "'"; + $insert_data['size'] = (int)$data['size']; + $insert_data['is_modified'] = 1; + + return $insert_data; + } + + protected function makeOnDuplicateKeyData() { + return "`google_product_category`=VALUES(`google_product_category`), `condition`=VALUES(`condition`), `adult`=VALUES(`adult`), `multipack`=VALUES(`multipack`), `is_bundle`=VALUES(`is_bundle`), `age_group`=VALUES(`age_group`), `color`=VALUES(`color`), `gender`=VALUES(`gender`), `size_type`=VALUES(`size_type`), `size_system`=VALUES(`size_system`), `size`=VALUES(`size`), `is_modified`=VALUES(`is_modified`)"; + } + + public function getCategories($data = array(), $store_id) { + $sql = "SELECT cp.category_id AS category_id, GROUP_CONCAT(cd1.name ORDER BY cp.level SEPARATOR ' > ') AS name, c1.parent_id, c1.sort_order FROM " . DB_PREFIX . "category_path cp LEFT JOIN `" . DB_PREFIX . "category_to_store` c2s ON (c2s.category_id = cp.category_id AND c2s.store_id=" . (int)$store_id . ") LEFT JOIN " . DB_PREFIX . "category c1 ON (cp.category_id = c1.category_id) LEFT JOIN " . DB_PREFIX . "category c2 ON (cp.path_id = c2.category_id) LEFT JOIN " . DB_PREFIX . "category_description cd1 ON (cp.path_id = cd1.category_id) LEFT JOIN " . DB_PREFIX . "category_description cd2 ON (cp.category_id = cd2.category_id) WHERE c2s.store_id IS NOT NULL AND cd1.language_id = '" . (int)$this->config->get('config_language_id') . "' AND cd2.language_id = '" . (int)$this->config->get('config_language_id') . "'"; + + if (!empty($data['filter_name'])) { + $sql .= " AND cd2.name LIKE '%" . $this->db->escape($data['filter_name']) . "%'"; + } + + $sql .= " GROUP BY cp.category_id"; + + $sort_data = array( + 'name', + 'sort_order' + ); + + if (isset($data['sort']) && in_array($data['sort'], $sort_data)) { + $sql .= " ORDER BY " . $data['sort']; + } else { + $sql .= " ORDER BY sort_order"; + } + + 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']; + } + + $query = $this->db->query($sql); + + return $query->rows; + } + + public function getProductCampaigns($product_id, $store_id) { + $sql = "SELECT agt.advertise_google_target_id, agt.campaign_name FROM `" . DB_PREFIX . "googleshopping_product_target` pagt LEFT JOIN `" . DB_PREFIX . "googleshopping_target` agt ON (pagt.advertise_google_target_id = agt.advertise_google_target_id) WHERE pagt.product_id=" . (int)$product_id . " AND pagt.store_id=" . (int)$store_id; + + return $this->db->query($sql)->rows; + } + + public function getProductIssues($product_id, $store_id) { + $this->load->model('localisation/language'); + + $sql = "SELECT pag.color, pag.size, pd.name, p.model FROM `" . DB_PREFIX . "googleshopping_product` pag LEFT JOIN `" . DB_PREFIX . "product` p ON (p.product_id = pag.product_id) LEFT JOIN `" . DB_PREFIX . "product_description` pd ON (pd.product_id = pag.product_id AND pd.language_id=" . (int)$this->config->get('config_language_id') . ") WHERE pag.product_id=" . (int)$product_id . " AND pag.store_id=" . (int)$store_id; + + $product_info = $this->db->query($sql)->row; + + if (!empty($product_info)) { + $result = array(); + $result['name'] = $product_info['name']; + $result['model'] = $product_info['model']; + $result['entries'] = array(); + + foreach ($this->model_localisation_language->getLanguages() as $language) { + $language_id = $language['language_id']; + $groups = $this->googleshopping->getGroups($product_id, $language_id, $product_info['color'], $product_info['size']); + + $result['entries'][$language_id] = array( + 'language_name' => $language['name'], + 'issues' => array() + ); + + foreach ($groups as $id => $group) { + $issues = $this->db->query("SELECT * FROM `" . DB_PREFIX . "googleshopping_product_status` WHERE product_id=" . (int)$product_id . " AND store_id=" . (int)$store_id . " AND product_variation_id='" . $this->db->escape($id) . "'")->row; + + $destination_statuses = !empty($issues['destination_statuses']) ? json_decode($issues['destination_statuses'], true) : array(); + $data_quality_issues = !empty($issues['data_quality_issues']) ? json_decode($issues['data_quality_issues'], true) : array(); + $item_level_issues = !empty($issues['item_level_issues']) ? json_decode($issues['item_level_issues'], true) : array(); + $google_expiration_date = !empty($issues['google_expiration_date']) ? date($this->language->get('datetime_format'), $issues['google_expiration_date']) : $this->language->get('text_na'); + + $result['entries'][$language_id]['issues'][] = array( + 'color' => $group['color'] != "" ? $group['color'] : $this->language->get('text_na'), + 'size' => $group['size'] != "" ? $group['size'] : $this->language->get('text_na'), + 'destination_statuses' => $destination_statuses, + 'data_quality_issues' => $data_quality_issues, + 'item_level_issues' => $item_level_issues, + 'google_expiration_date' => $google_expiration_date + ); + } + } + + return $result; + } + + return null; + } + + /* + * Shortly after releasing the extension, + * we learned that the table names are actually + * clashing with third-party extensions. + * Hence, this renaming script was created. + */ + public function renameTables() { + foreach ($this->rename_tables as $old_table => $new_table) { + $new_table_name = DB_PREFIX . $new_table; + $old_table_name = DB_PREFIX . $old_table; + + if ($this->tableExists($old_table_name) && !$this->tableExists($new_table_name) && $this->tableColumnsMatch($old_table_name, $this->table_columns[$new_table])) { + $this->db->query("RENAME TABLE `" . $old_table_name . "` TO `" . $new_table_name . "`"); + } + } + } + + private function tableExists($table) { + return $this->db->query("SHOW TABLES LIKE '" . $table . "'")->num_rows > 0; + } + + private function tableColumnsMatch($table, $columns) { + $num_columns = $this->db->query("SHOW COLUMNS FROM `" . $table . "` WHERE Field IN (" . implode(',', $this->wrap($columns, '"')) . ")")->num_rows; + + return $num_columns == count($columns); + } + + private function wrap($text, $char) { + if (is_array($text)) { + foreach ($text as &$string) { + $string = $char . $string . $char; + } + + return $text; + } else { + return $char . $text . $char; + } + } + + public function createTables() { + $this->db->query("CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "googleshopping_product` ( + `product_advertise_google_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, + `product_id` INT(11), + `store_id` INT(11) NOT NULL DEFAULT '0', + `has_issues` TINYINT(1), + `destination_status` ENUM('pending','approved','disapproved') NOT NULL DEFAULT 'pending', + `impressions` INT(11) NOT NULL DEFAULT '0', + `clicks` INT(11) NOT NULL DEFAULT '0', + `conversions` INT(11) NOT NULL DEFAULT '0.0000', + `cost` decimal(15,4) NOT NULL DEFAULT '0.0000', + `conversion_value` decimal(15,4) NOT NULL DEFAULT '0.0000', + `google_product_category` VARCHAR(10), + `condition` ENUM('new','refurbished','used'), + `adult` TINYINT(1), + `multipack` INT(11), + `is_bundle` TINYINT(1), + `age_group` ENUM('newborn','infant','toddler','kids','adult'), + `color` INT(11), + `gender` ENUM('male','female','unisex'), + `size_type` ENUM('regular','petite','plus','big and tall','maternity'), + `size_system` ENUM('AU','BR','CN','DE','EU','FR','IT','JP','MEX','UK','US'), + `size` INT(11), + `is_modified` TINYINT(1) NOT NULL DEFAULT '0', + PRIMARY KEY (`product_advertise_google_id`), + UNIQUE `product_id_store_id` (`product_id`, `store_id`) + ) ENGINE=MyISAM DEFAULT CHARSET=utf8"); + + $this->db->query("CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "googleshopping_product_status` ( + `product_id` INT(11), + `store_id` INT(11) NOT NULL DEFAULT '0', + `product_variation_id` varchar(64), + `destination_statuses` TEXT NOT NULL, + `data_quality_issues` TEXT NOT NULL, + `item_level_issues` TEXT NOT NULL, + `google_expiration_date` INT(11) NOT NULL DEFAULT '0', + PRIMARY KEY (`product_id`, `store_id`, `product_variation_id`) + ) ENGINE=MyISAM DEFAULT CHARSET=utf8"); + + $this->db->query("CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "googleshopping_product_target` ( + `product_id` INT(11) NOT NULL, + `store_id` INT(11) NOT NULL DEFAULT '0', + `advertise_google_target_id` INT(11) UNSIGNED NOT NULL, + PRIMARY KEY (`product_id`, `advertise_google_target_id`) + ) ENGINE=MyISAM DEFAULT CHARSET=utf8"); + + $this->db->query("CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "googleshopping_category` ( + `google_product_category` VARCHAR(10) NOT NULL, + `store_id` INT(11) NOT NULL DEFAULT '0', + `category_id` INT(11) NOT NULL, + INDEX `category_id_store_id` (`category_id`, `store_id`), + PRIMARY KEY (`google_product_category`, `store_id`) + ) ENGINE=MyISAM DEFAULT CHARSET=utf8"); + + $this->db->query("CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "googleshopping_target` ( + `advertise_google_target_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, + `store_id` INT(11) NOT NULL DEFAULT '0', + `campaign_name` varchar(255) NOT NULL DEFAULT '', + `country` varchar(2) NOT NULL DEFAULT '', + `budget` decimal(15,4) NOT NULL DEFAULT '0.0000', + `feeds` text NOT NULL, + `date_added` DATE, + `roas` INT(11) NOT NULL DEFAULT '0', + `status` ENUM('paused','active') NOT NULL DEFAULT 'paused', + INDEX `store_id` (`store_id`), + PRIMARY KEY (`advertise_google_target_id`) + ) ENGINE=MyISAM DEFAULT CHARSET=utf8"); + } + + public function fixColumns() { + $has_auto_increment = $this->db->query("SHOW COLUMNS FROM `" . DB_PREFIX . "googleshopping_product` WHERE Field='product_advertise_google_id' AND Extra LIKE '%auto_increment%'")->num_rows > 0; + + if (!$has_auto_increment) { + $this->db->query("ALTER TABLE " . DB_PREFIX . "googleshopping_product MODIFY COLUMN product_advertise_google_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT"); + } + + $has_unique_key = $this->db->query("SHOW INDEX FROM `" . DB_PREFIX . "googleshopping_product` WHERE Key_name='product_id_store_id' AND Non_unique=0")->num_rows == 2; + + if (!$has_unique_key) { + $index_exists = $this->db->query("SHOW INDEX FROM `" . DB_PREFIX . "googleshopping_product` WHERE Key_name='product_id_store_id'")->num_rows > 0; + + if ($index_exists) { + $this->db->query("ALTER TABLE `" . DB_PREFIX . "googleshopping_product` DROP INDEX product_id_store_id;"); + } + + $this->db->query("CREATE UNIQUE INDEX product_id_store_id ON `" . DB_PREFIX . "googleshopping_product` (product_id, store_id)"); + } + + $has_date_added_column = $this->db->query("SHOW COLUMNS FROM `" . DB_PREFIX . "googleshopping_target` WHERE Field='date_added'")->num_rows > 0; + + if (!$has_date_added_column) { + $this->db->query("ALTER TABLE " . DB_PREFIX . "googleshopping_target ADD COLUMN date_added DATE"); + + $this->db->query("UPDATE " . DB_PREFIX . "googleshopping_target SET date_added = NOW() WHERE date_added IS NULL"); + } + + $has_roas_column = $this->db->query("SHOW COLUMNS FROM `" . DB_PREFIX . "googleshopping_target` WHERE Field='roas'")->num_rows > 0; + + if (!$has_roas_column) { + $this->db->query("ALTER TABLE " . DB_PREFIX . "googleshopping_target ADD COLUMN roas INT(11) NOT NULL DEFAULT '0'"); + } + } + + public function dropTables() { + $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "googleshopping_target`"); + $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "googleshopping_category`"); + $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "googleshopping_product_status`"); + $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "googleshopping_product_target`"); + $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "googleshopping_product`"); + } + + public function deleteEvents() { + $this->load->model('setting/event'); + + $this->model_setting_event->deleteEventByCode('advertise_google'); + } + + public function createEvents() { + $this->load->model('setting/event'); + + foreach ($this->events as $trigger => $actions) { + foreach ($actions as $action) { + $this->model_setting_event->addEvent('advertise_google', $trigger, $action, 1, 0); + } + } + } + + public function getAllowedTargets() { + $this->load->config('googleshopping/googleshopping'); + + $result = array(); + + foreach ($this->config->get('advertise_google_targets') as $target) { + $result[] = array( + 'country' => array( + 'code' => $target['country'], + 'name' => $this->googleshopping->getCountryName($target['country']) + ), + 'languages' => $this->googleshopping->getLanguages($target['languages']), + 'currencies' => $this->googleshopping->getCurrencies($target['currencies']) + ); + } + + return $result; + } + + protected function country($row) { + return $row['country']; + } +} |