PageRenderTime 52ms CodeModel.GetById 24ms RepoModel.GetById 0ms app.codeStats 1ms

/upload/catalog/model/catalog/product.php

https://github.com/anugrah/opencart
PHP | 562 lines | 440 code | 122 blank | 0 comment | 109 complexity | bc7e85c2f3b26b146717972601dffb51 MD5 | raw file
Possible License(s): GPL-3.0
  1. <?php
  2. class ModelCatalogProduct extends Model {
  3. public function updateViewed($product_id) {
  4. $this->db->query("UPDATE " . DB_PREFIX . "product SET viewed = (viewed + 1) WHERE product_id = '" . (int)$product_id . "'");
  5. }
  6. public function getProduct($product_id) {
  7. if ($this->customer->isLogged()) {
  8. $customer_group_id = $this->customer->getCustomerGroupId();
  9. } else {
  10. $customer_group_id = $this->config->get('config_customer_group_id');
  11. }
  12. $query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$customer_group_id . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$customer_group_id . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM " . DB_PREFIX . "product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '" . (int)$customer_group_id . "') AS reward, (SELECT ss.name FROM " . DB_PREFIX . "stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "') AS stock_status, (SELECT wcd.unit FROM " . DB_PREFIX . "weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS weight_class, (SELECT lcd.unit FROM " . DB_PREFIX . "length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS length_class, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");
  13. if ($query->num_rows) {
  14. return array(
  15. 'product_id' => $query->row['product_id'],
  16. 'name' => $query->row['name'],
  17. 'description' => $query->row['description'],
  18. 'meta_description' => $query->row['meta_description'],
  19. 'meta_keyword' => $query->row['meta_keyword'],
  20. 'tag' => $query->row['tag'],
  21. 'model' => $query->row['model'],
  22. 'sku' => $query->row['sku'],
  23. 'upc' => $query->row['upc'],
  24. 'ean' => $query->row['ean'],
  25. 'jan' => $query->row['jan'],
  26. 'isbn' => $query->row['isbn'],
  27. 'mpn' => $query->row['mpn'],
  28. 'location' => $query->row['location'],
  29. 'quantity' => $query->row['quantity'],
  30. 'stock_status' => $query->row['stock_status'],
  31. 'image' => $query->row['image'],
  32. 'manufacturer_id' => $query->row['manufacturer_id'],
  33. 'manufacturer' => $query->row['manufacturer'],
  34. 'price' => ($query->row['discount'] ? $query->row['discount'] : $query->row['price']),
  35. 'special' => $query->row['special'],
  36. 'reward' => $query->row['reward'],
  37. 'points' => $query->row['points'],
  38. 'tax_class_id' => $query->row['tax_class_id'],
  39. 'date_available' => $query->row['date_available'],
  40. 'weight' => $query->row['weight'],
  41. 'weight_class_id' => $query->row['weight_class_id'],
  42. 'length' => $query->row['length'],
  43. 'width' => $query->row['width'],
  44. 'height' => $query->row['height'],
  45. 'length_class_id' => $query->row['length_class_id'],
  46. 'subtract' => $query->row['subtract'],
  47. 'rating' => round($query->row['rating']),
  48. 'reviews' => $query->row['reviews'],
  49. 'minimum' => $query->row['minimum'],
  50. 'sort_order' => $query->row['sort_order'],
  51. 'status' => $query->row['status'],
  52. 'date_added' => $query->row['date_added'],
  53. 'date_modified' => $query->row['date_modified'],
  54. 'viewed' => $query->row['viewed']
  55. );
  56. } else {
  57. return false;
  58. }
  59. }
  60. public function getProducts($data = array()) {
  61. if ($this->customer->isLogged()) {
  62. $customer_group_id = $this->customer->getCustomerGroupId();
  63. } else {
  64. $customer_group_id = $this->config->get('config_customer_group_id');
  65. }
  66. $sql = "SELECT p.product_id, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating";
  67. if (!empty($data['filter_category_id'])) {
  68. $sql .= " FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (c.category_id = p2c.category_id) LEFT JOIN " . DB_PREFIX . "product p ON (p2c.product_id = p.product_id)";
  69. } else {
  70. $sql .= " FROM " . DB_PREFIX . "product p";
  71. }
  72. $sql .= " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";
  73. if (!empty($data['filter_category_id'])) {
  74. if (empty($data['filter_sub_category'])) {
  75. $sql .= " AND p2c.category_id = '" . $this->db->escape($data['filter_category_id']) . "'";
  76. } else {
  77. $this->load->model('catalog/category');
  78. $category_info = $this->model_catalog_category->getCategory($data['filter_category_id']);
  79. if ($category_info) {
  80. $sql .= " AND c.`left` BETWEEN '" . (int)$category_info['left'] . "' AND '" . (int)$category_info['right'] . "'";
  81. }
  82. }
  83. }
  84. if (!empty($data['filter_name']) || !empty($data['filter_tag'])) {
  85. $sql .= " AND (";
  86. if (!empty($data['filter_name'])) {
  87. $implode = array();
  88. $words = explode(' ', trim(preg_replace('/\s\s+/', ' ', $data['filter_name'])));
  89. foreach ($words as $word) {
  90. $implode[] = "LCASE(pd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
  91. }
  92. if ($implode) {
  93. $sql .= " " . implode(" AND ", $implode) . "";
  94. }
  95. if (!empty($data['filter_description'])) {
  96. $sql .= " OR MATCH(pd.description) AGAINST('" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "')";
  97. }
  98. }
  99. if (!empty($data['filter_name']) && !empty($data['filter_tag'])) {
  100. $sql .= " OR ";
  101. }
  102. if (!empty($data['filter_tag'])) {
  103. $sql .= "MATCH(pd.tag) AGAINST('" . $this->db->escape(utf8_strtolower($data['filter_tag'])) . "')";
  104. }
  105. if (!empty($data['filter_name'])) {
  106. $sql .= " OR LCASE(p.model) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
  107. }
  108. if (!empty($data['filter_name'])) {
  109. $sql .= " OR LCASE(p.sku) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
  110. }
  111. if (!empty($data['filter_name'])) {
  112. $sql .= " OR LCASE(p.upc) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
  113. }
  114. if (!empty($data['filter_name'])) {
  115. $sql .= " OR LCASE(p.ean) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
  116. }
  117. if (!empty($data['filter_name'])) {
  118. $sql .= " OR LCASE(p.jan) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
  119. }
  120. if (!empty($data['filter_name'])) {
  121. $sql .= " OR LCASE(p.isbn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
  122. }
  123. if (!empty($data['filter_name'])) {
  124. $sql .= " OR LCASE(p.mpn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
  125. }
  126. $sql .= ")";
  127. }
  128. if (!empty($data['filter_manufacturer_id'])) {
  129. $sql .= " AND p.manufacturer_id = '" . (int)$data['filter_manufacturer_id'] . "'";
  130. }
  131. $sql .= " GROUP BY p.product_id";
  132. $sort_data = array(
  133. 'pd.name',
  134. 'p.model',
  135. 'p.quantity',
  136. 'p.price',
  137. 'rating',
  138. 'p.sort_order',
  139. 'p.date_added'
  140. );
  141. if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
  142. if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
  143. $sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
  144. } else {
  145. $sql .= " ORDER BY " . $data['sort'];
  146. }
  147. } else {
  148. $sql .= " ORDER BY p.sort_order";
  149. }
  150. if (isset($data['order']) && ($data['order'] == 'DESC')) {
  151. $sql .= " DESC, LCASE(pd.name) DESC";
  152. } else {
  153. $sql .= " ASC, LCASE(pd.name) ASC";
  154. }
  155. if (isset($data['start']) || isset($data['limit'])) {
  156. if ($data['start'] < 0) {
  157. $data['start'] = 0;
  158. }
  159. if ($data['limit'] < 1) {
  160. $data['limit'] = 20;
  161. }
  162. $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
  163. }
  164. $product_data = array();
  165. $query = $this->db->query($sql);
  166. foreach ($query->rows as $result) {
  167. $product_data[$result['product_id']] = $this->getProduct($result['product_id']);
  168. }
  169. return $product_data;
  170. }
  171. public function getProductSpecials($data = array()) {
  172. if ($this->customer->isLogged()) {
  173. $customer_group_id = $this->customer->getCustomerGroupId();
  174. } else {
  175. $customer_group_id = $this->config->get('config_customer_group_id');
  176. }
  177. $sql = "SELECT DISTINCT ps.product_id, (SELECT AVG(rating) FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = ps.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating FROM " . DB_PREFIX . "product_special ps LEFT JOIN " . DB_PREFIX . "product p ON (ps.product_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ps.customer_group_id = '" . (int)$customer_group_id . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) GROUP BY ps.product_id";
  178. $sort_data = array(
  179. 'pd.name',
  180. 'p.model',
  181. 'ps.price',
  182. 'rating',
  183. 'p.sort_order'
  184. );
  185. if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
  186. if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
  187. $sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
  188. } else {
  189. $sql .= " ORDER BY " . $data['sort'];
  190. }
  191. } else {
  192. $sql .= " ORDER BY p.sort_order";
  193. }
  194. if (isset($data['order']) && ($data['order'] == 'DESC')) {
  195. $sql .= " DESC, LCASE(pd.name) DESC";
  196. } else {
  197. $sql .= " ASC, LCASE(pd.name) ASC";
  198. }
  199. if (isset($data['start']) || isset($data['limit'])) {
  200. if ($data['start'] < 0) {
  201. $data['start'] = 0;
  202. }
  203. if ($data['limit'] < 1) {
  204. $data['limit'] = 20;
  205. }
  206. $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
  207. }
  208. $product_data = array();
  209. $query = $this->db->query($sql);
  210. foreach ($query->rows as $result) {
  211. $product_data[$result['product_id']] = $this->getProduct($result['product_id']);
  212. }
  213. return $product_data;
  214. }
  215. public function getLatestProducts($limit) {
  216. if ($this->customer->isLogged()) {
  217. $customer_group_id = $this->customer->getCustomerGroupId();
  218. } else {
  219. $customer_group_id = $this->config->get('config_customer_group_id');
  220. }
  221. $product_data = $this->cache->get('product.latest.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $customer_group_id . '.' . (int)$limit);
  222. if (!$product_data) {
  223. $query = $this->db->query("SELECT p.product_id FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' ORDER BY p.date_added DESC LIMIT " . (int)$limit);
  224. foreach ($query->rows as $result) {
  225. $product_data[$result['product_id']] = $this->getProduct($result['product_id']);
  226. }
  227. $this->cache->set('product.latest.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id'). '.' . $customer_group_id . '.' . (int)$limit, $product_data);
  228. }
  229. return $product_data;
  230. }
  231. public function getPopularProducts($limit) {
  232. $product_data = array();
  233. $query = $this->db->query("SELECT p.product_id FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' ORDER BY p.viewed, p.date_added DESC LIMIT " . (int)$limit);
  234. foreach ($query->rows as $result) {
  235. $product_data[$result['product_id']] = $this->getProduct($result['product_id']);
  236. }
  237. return $product_data;
  238. }
  239. public function getBestSellerProducts($limit) {
  240. if ($this->customer->isLogged()) {
  241. $customer_group_id = $this->customer->getCustomerGroupId();
  242. } else {
  243. $customer_group_id = $this->config->get('config_customer_group_id');
  244. }
  245. $product_data = $this->cache->get('product.bestseller.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id'). '.' . $customer_group_id . '.' . (int)$limit);
  246. if (!$product_data) {
  247. $product_data = array();
  248. $query = $this->db->query("SELECT op.product_id, COUNT(*) AS total FROM " . DB_PREFIX . "order_product op LEFT JOIN `" . DB_PREFIX . "order` o ON (op.order_id = o.order_id) LEFT JOIN `" . DB_PREFIX . "product` p ON (op.product_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE o.order_status_id > '0' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' GROUP BY op.product_id ORDER BY total DESC LIMIT " . (int)$limit);
  249. foreach ($query->rows as $result) {
  250. $product_data[$result['product_id']] = $this->getProduct($result['product_id']);
  251. }
  252. $this->cache->set('product.bestseller.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id'). '.' . $customer_group_id . '.' . (int)$limit, $product_data);
  253. }
  254. return $product_data;
  255. }
  256. public function getProductAttributes($product_id) {
  257. $product_attribute_group_data = array();
  258. $product_attribute_group_query = $this->db->query("SELECT ag.attribute_group_id, agd.name FROM " . DB_PREFIX . "product_attribute pa LEFT JOIN " . DB_PREFIX . "attribute a ON (pa.attribute_id = a.attribute_id) LEFT JOIN " . DB_PREFIX . "attribute_group ag ON (a.attribute_group_id = ag.attribute_group_id) LEFT JOIN " . DB_PREFIX . "attribute_group_description agd ON (ag.attribute_group_id = agd.attribute_group_id) WHERE pa.product_id = '" . (int)$product_id . "' AND agd.language_id = '" . (int)$this->config->get('config_language_id') . "' GROUP BY ag.attribute_group_id ORDER BY ag.sort_order, agd.name");
  259. foreach ($product_attribute_group_query->rows as $product_attribute_group) {
  260. $product_attribute_data = array();
  261. $product_attribute_query = $this->db->query("SELECT a.attribute_id, ad.name, pa.text FROM " . DB_PREFIX . "product_attribute pa LEFT JOIN " . DB_PREFIX . "attribute a ON (pa.attribute_id = a.attribute_id) LEFT JOIN " . DB_PREFIX . "attribute_description ad ON (a.attribute_id = ad.attribute_id) WHERE pa.product_id = '" . (int)$product_id . "' AND a.attribute_group_id = '" . (int)$product_attribute_group['attribute_group_id'] . "' AND ad.language_id = '" . (int)$this->config->get('config_language_id') . "' AND pa.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY a.sort_order, ad.name");
  262. foreach ($product_attribute_query->rows as $product_attribute) {
  263. $product_attribute_data[] = array(
  264. 'attribute_id' => $product_attribute['attribute_id'],
  265. 'name' => $product_attribute['name'],
  266. 'text' => $product_attribute['text']
  267. );
  268. }
  269. $product_attribute_group_data[] = array(
  270. 'attribute_group_id' => $product_attribute_group['attribute_group_id'],
  271. 'name' => $product_attribute_group['name'],
  272. 'attribute' => $product_attribute_data
  273. );
  274. }
  275. return $product_attribute_group_data;
  276. }
  277. public function getProductOptions($product_id) {
  278. $product_option_data = array();
  279. $product_option_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option po LEFT JOIN `" . DB_PREFIX . "option` o ON (po.option_id = o.option_id) LEFT JOIN " . DB_PREFIX . "option_description od ON (o.option_id = od.option_id) WHERE po.product_id = '" . (int)$product_id . "' AND od.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY o.sort_order");
  280. foreach ($product_option_query->rows as $product_option) {
  281. if ($product_option['type'] == 'select' || $product_option['type'] == 'radio' || $product_option['type'] == 'checkbox' || $product_option['type'] == 'image') {
  282. $product_option_value_data = array();
  283. $product_option_value_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option_value pov LEFT JOIN " . DB_PREFIX . "option_value ov ON (pov.option_value_id = ov.option_value_id) LEFT JOIN " . DB_PREFIX . "option_value_description ovd ON (ov.option_value_id = ovd.option_value_id) WHERE pov.product_id = '" . (int)$product_id . "' AND pov.product_option_id = '" . (int)$product_option['product_option_id'] . "' AND ovd.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY ov.sort_order");
  284. foreach ($product_option_value_query->rows as $product_option_value) {
  285. $product_option_value_data[] = array(
  286. 'product_option_value_id' => $product_option_value['product_option_value_id'],
  287. 'option_value_id' => $product_option_value['option_value_id'],
  288. 'name' => $product_option_value['name'],
  289. 'image' => $product_option_value['image'],
  290. 'quantity' => $product_option_value['quantity'],
  291. 'subtract' => $product_option_value['subtract'],
  292. 'price' => $product_option_value['price'],
  293. 'price_prefix' => $product_option_value['price_prefix'],
  294. 'weight' => $product_option_value['weight'],
  295. 'weight_prefix' => $product_option_value['weight_prefix']
  296. );
  297. }
  298. $product_option_data[] = array(
  299. 'product_option_id' => $product_option['product_option_id'],
  300. 'option_id' => $product_option['option_id'],
  301. 'name' => $product_option['name'],
  302. 'type' => $product_option['type'],
  303. 'option_value' => $product_option_value_data,
  304. 'required' => $product_option['required']
  305. );
  306. } else {
  307. $product_option_data[] = array(
  308. 'product_option_id' => $product_option['product_option_id'],
  309. 'option_id' => $product_option['option_id'],
  310. 'name' => $product_option['name'],
  311. 'type' => $product_option['type'],
  312. 'option_value' => $product_option['option_value'],
  313. 'required' => $product_option['required']
  314. );
  315. }
  316. }
  317. return $product_option_data;
  318. }
  319. public function getProductDiscounts($product_id) {
  320. if ($this->customer->isLogged()) {
  321. $customer_group_id = $this->customer->getCustomerGroupId();
  322. } else {
  323. $customer_group_id = $this->config->get('config_customer_group_id');
  324. }
  325. $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_discount WHERE product_id = '" . (int)$product_id . "' AND customer_group_id = '" . (int)$customer_group_id . "' AND quantity > 1 AND ((date_start = '0000-00-00' OR date_start < NOW()) AND (date_end = '0000-00-00' OR date_end > NOW())) ORDER BY quantity ASC, priority ASC, price ASC");
  326. return $query->rows;
  327. }
  328. public function getProductImages($product_id) {
  329. $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_image WHERE product_id = '" . (int)$product_id . "' ORDER BY sort_order ASC");
  330. return $query->rows;
  331. }
  332. public function getProductRelated($product_id) {
  333. $product_data = array();
  334. $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_related pr LEFT JOIN " . DB_PREFIX . "product p ON (pr.related_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pr.product_id = '" . (int)$product_id . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");
  335. foreach ($query->rows as $result) {
  336. $product_data[$result['related_id']] = $this->getProduct($result['related_id']);
  337. }
  338. return $product_data;
  339. }
  340. public function getProductLayoutId($product_id) {
  341. $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_layout WHERE product_id = '" . (int)$product_id . "' AND store_id = '" . (int)$this->config->get('config_store_id') . "'");
  342. if ($query->num_rows) {
  343. return $query->row['layout_id'];
  344. } else {
  345. return $this->config->get('config_layout_product');
  346. }
  347. }
  348. public function getCategories($product_id) {
  349. $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_category WHERE product_id = '" . (int)$product_id . "'");
  350. return $query->rows;
  351. }
  352. public function getTotalProducts($data = array()) {
  353. if ($this->customer->isLogged()) {
  354. $customer_group_id = $this->customer->getCustomerGroupId();
  355. } else {
  356. $customer_group_id = $this->config->get('config_customer_group_id');
  357. }
  358. $sql = "SELECT COUNT(DISTINCT p.product_id) AS total";
  359. if (!empty($data['filter_category_id'])) {
  360. $sql .= " FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (c.category_id = p2c.category_id) LEFT JOIN " . DB_PREFIX . "product p ON (p2c.product_id = p.product_id)";
  361. } else {
  362. $sql .= " FROM " . DB_PREFIX . "product p";
  363. }
  364. $sql .= " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";
  365. if (!empty($data['filter_category_id'])) {
  366. if (empty($data['filter_sub_category'])) {
  367. $sql .= " AND c.category_id = '" . $this->db->escape($data['filter_category_id']) . "'";
  368. } else {
  369. $this->load->model('catalog/category');
  370. $category_info = $this->model_catalog_category->getCategory($data['filter_category_id']);
  371. if ($category_info) {
  372. $sql .= " AND c.`left` BETWEEN '" . (int)$category_info['left'] . "' AND '" . (int)$category_info['right'] . "'";
  373. }
  374. }
  375. }
  376. if (!empty($data['filter_name']) || !empty($data['filter_tag'])) {
  377. $sql .= " AND (";
  378. if (!empty($data['filter_name'])) {
  379. $implode = array();
  380. $words = explode(' ', trim(preg_replace('/\s\s+/', ' ', $data['filter_name'])));
  381. foreach ($words as $word) {
  382. $implode[] = "LCASE(pd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
  383. }
  384. if ($implode) {
  385. $sql .= " " . implode(" AND ", $implode) . "";
  386. }
  387. if (!empty($data['filter_description'])) {
  388. $sql .= " OR MATCH(pd.description) AGAINST('" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "')";
  389. }
  390. }
  391. if (!empty($data['filter_name']) && !empty($data['filter_tag'])) {
  392. $sql .= " OR ";
  393. }
  394. if (!empty($data['filter_tag'])) {
  395. $sql .= "MATCH(pd.tag) AGAINST('" . $this->db->escape(utf8_strtolower($data['filter_tag'])) . "')";
  396. }
  397. if (!empty($data['filter_name'])) {
  398. $sql .= " OR LCASE(p.model) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
  399. }
  400. if (!empty($data['filter_name'])) {
  401. $sql .= " OR LCASE(p.sku) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
  402. }
  403. if (!empty($data['filter_name'])) {
  404. $sql .= " OR LCASE(p.upc) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
  405. }
  406. if (!empty($data['filter_name'])) {
  407. $sql .= " OR LCASE(p.ean) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
  408. }
  409. if (!empty($data['filter_name'])) {
  410. $sql .= " OR LCASE(p.jan) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
  411. }
  412. if (!empty($data['filter_name'])) {
  413. $sql .= " OR LCASE(p.isbn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
  414. }
  415. if (!empty($data['filter_name'])) {
  416. $sql .= " OR LCASE(p.mpn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
  417. }
  418. $sql .= ")";
  419. }
  420. if (!empty($data['filter_manufacturer_id'])) {
  421. $sql .= " AND p.manufacturer_id = '" . (int)$data['filter_manufacturer_id'] . "'";
  422. }
  423. $query = $this->db->query($sql);
  424. return $query->row['total'];
  425. }
  426. public function getTotalProductSpecials() {
  427. if ($this->customer->isLogged()) {
  428. $customer_group_id = $this->customer->getCustomerGroupId();
  429. } else {
  430. $customer_group_id = $this->config->get('config_customer_group_id');
  431. }
  432. $query = $this->db->query("SELECT COUNT(DISTINCT ps.product_id) AS total FROM " . DB_PREFIX . "product_special ps LEFT JOIN " . DB_PREFIX . "product p ON (ps.product_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ps.customer_group_id = '" . (int)$customer_group_id . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW()))");
  433. if (isset($query->row['total'])) {
  434. return $query->row['total'];
  435. } else {
  436. return 0;
  437. }
  438. }
  439. }
  440. ?>