· 6 years ago · Jan 14, 2020, 03:56 PM
1<?php
2
3if (!defined('_PS_VERSION_'))
4 exit();
5
6/*
7 Developer: Mindrescu Ionut
8 Date: 24.05.2018 - 10:45
9 Sublime Text 3
10*/
11
12use PrestaShop\PrestaShop\Core\Module\WidgetInterface;
13use PrestaShop\PrestaShop\Core\Product\ProductListingPresenter;
14use PrestaShop\PrestaShop\Adapter\Image\ImageRetriever;
15use PrestaShop\PrestaShop\Adapter\Product\PriceFormatter;
16use PrestaShop\PrestaShop\Adapter\Product\ProductColorsRetriever;
17
18class Comuniti_Scraper extends Module
19{
20 public function __construct()
21 {
22 $this->name = 'comuniti_scraper';
23 $this->tab = 'front_office_features';
24 $this->version = '1.0.0';
25 $this->author = 'Assist Software';
26 $this->need_instance = 1;
27 $this->ps_versions_compliancy = array('min' => '1.7.1.0', 'max' => _PS_VERSION_);
28 $this->bootstrap = true;
29
30 parent::__construct();
31
32 $this->displayName = $this->l('Comuniti Scraper', 'comuniti_scraper');
33 $this->description = $this->l('This module is used to get products from the Scraper API.', 'comuniti_scraper');
34
35 $this->confirmUninstall = $this->l('Are you sure you want to uninstall?', 'comuniti_scraper');
36 }
37
38 public function install()
39 {
40 if (Shop::isFeatureActive())
41 Shop::setContext(Shop::CONTEXT_ALL);
42
43 return parent::install() &&
44 $this->registerHook('displayProductCompetitors') &&
45 $this->registerHook('displayAdminProductsExtra') &&
46 $this->registerHook('actionProductUpdate') &&
47 $this->createTables();
48 }
49
50 public function uninstall()
51 {
52 if (!parent::uninstall() || !$this->deleteTables())
53 return false;
54 return true;
55 }
56
57 protected function createTables()
58 {
59 $query_create = (bool)Db::getInstance()->execute('
60 CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'comuniti_scraper` (
61 `id` int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
62 `store_name` text NOT NULL,
63 `product_id` int(11) NOT NULL,
64 `item_name` text NOT NULL,
65 `item_image` text NOT NULL,
66 `unit_price` float NOT NULL,
67 `unit` varchar(8) NOT NULL,
68 `package_price` float NOT NULL,
69 `package_unit` varchar(16) NOT NULL,
70 `currency` varchar(2) NOT NULL,
71 `status` varchar(8) NOT NULL,
72 `deleted_at` timestamp NULL DEFAULT NULL,
73 `created_at` timestamp NOT NULL,
74 `updated_at` timestamp NOT NULL,
75 `store_logo` text NOT NULL
76 ) ENGINE=' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=UTF8;');
77
78 $query_create &= (bool)Db::getInstance()->execute('
79 ALTER TABLE `' . _DB_PREFIX_ . 'product` ADD `s_original_price` decimal(10,2) NOT NULL DEFAULT 0 AFTER `state`;
80 ALTER TABLE `' . _DB_PREFIX_ . 'product` ADD `s_minimum_price` decimal(10,2) NOT NULL DEFAULT 0 AFTER `state`;
81 ALTER TABLE `' . _DB_PREFIX_ . 'product` ADD `s_minimum_price_unit` decimal(10,2) NOT NULL DEFAULT 0 AFTER `state`;
82 ALTER TABLE `' . _DB_PREFIX_ . 'product` ADD `s_drop_percent` decimal(10,2) NOT NULL DEFAULT 0 AFTER `state`;
83 ALTER TABLE `' . _DB_PREFIX_ . 'product` ADD `s_accept_drop` tinyint(1) NOT NULL DEFAULT 0 AFTER `state`;
84 ');
85
86 $query_create &= (bool)Db::getInstance()->execute('
87 CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'comuniti_scraper_logs` (
88 `id` int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
89 `store_name` text NOT NULL,
90 `product_id` int(11) NOT NULL,
91 `item_name` text NOT NULL,
92 `unit_price` float NOT NULL,
93 `unit` varchar(8) NOT NULL,
94 `package_price` float NOT NULL,
95 `package_unit` varchar(16) NOT NULL,
96 `currency` varchar(2) NOT NULL,
97 `status` varchar(8) NOT NULL,
98 `deleted_at` timestamp NULL DEFAULT NULL,
99 `created_at` timestamp NOT NULL,
100 `updated_at` timestamp NOT NULL,
101 `timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
102 ) ENGINE=' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=UTF8;');
103
104 $query_create &= (bool)Db::getInstance()->execute('
105 CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'comuniti_scraper_pricedrop_logs` (
106 `id` int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
107 `product_id` int(11) NOT NULL,
108 `old_price` decimal(10,2) NOT NULL,
109 `new_price` decimal(10,2) NOT NULL,
110 `type` text DEFAULT NULL,
111 `store_name` text DEFAULT NULL,
112 `timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
113 ) ENGINE=' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=UTF8;');
114
115 return $query_create;
116 }
117
118 protected function deleteTables()
119 {
120 $query_delete = (bool)Db::getInstance()->execute('
121 ALTER TABLE `' . _DB_PREFIX_ . 'product` DROP `s_original_price`;
122 ALTER TABLE `' . _DB_PREFIX_ . 'product` DROP `s_minimum_price`;
123 ALTER TABLE `' . _DB_PREFIX_ . 'product` DROP `s_minimum_price_unit`;
124 ALTER TABLE `' . _DB_PREFIX_ . 'product` DROP `s_drop_percent`;
125 ALTER TABLE `' . _DB_PREFIX_ . 'product` DROP `s_accept_drop`;
126 ');
127
128 $query_delete &= Db::getInstance()->execute('DROP TABLE IF EXISTS `' . _DB_PREFIX_ . 'comuniti_scraper_pricedrop_logs`;');
129 $query_delete &= Db::getInstance()->execute('DROP TABLE IF EXISTS `' . _DB_PREFIX_ . 'comuniti_scraper_logs`;');
130 $query_delete &= Db::getInstance()->execute('DROP TABLE IF EXISTS `' . _DB_PREFIX_ . 'comuniti_scraper`;');
131 return $query_delete;
132 }
133
134 /**
135 * function ajaxPushProductStores($params)
136 * @param: $product_id (id of the product from Prestashop catalog)
137 * @return: this function will populate the comuniti_scraper table with prices from the API Scraper
138 */
139 public static function ajaxPushProductStores($params)
140 {
141 // < get data from request >
142 $json_escape = str_replace('\/', '/', $params);
143 $object_products = json_decode($json_escape, FALSE);
144
145 Db::getInstance()->execute("DELETE FROM " . _DB_PREFIX_ . "comuniti_scraper WHERE package_price = 0 OR package_price IS NULL");
146
147 // < check if status is true, means the products exists >
148 if (isset($object_products->push_data->data->stores) && count($object_products->push_data->data->stores) > 0) {
149 $stores_received=[];
150 foreach ($object_products->push_data->data->stores as $value) {
151 $stores_received[] = $value->store_name;
152 }
153 Db::getInstance()->execute("DELETE FROM " . _DB_PREFIX_ . "comuniti_scraper WHERE product_id=".$object_products->push_data->data->product_id_on_site." AND store_name NOT IN ('".implode("','",$stores_received)."')");
154 try {
155 // < foreach the stores to get info >
156 foreach ($object_products->push_data->data->stores as $value) {
157 $id_product = $object_products->push_data->data->product_id_on_site;
158
159 // < query database to check if store exists >
160 $query_check = Db::getInstance()->getRow("SELECT a.id FROM " . _DB_PREFIX_ . "comuniti_scraper as a WHERE a.product_id = " . $id_product . " AND a.store_name = '" . $value->store_name . "'");
161
162 if ($value->package_price != 0 && $value->package_price != null && !empty($value->package_price)) {
163 // < if there is no record about this store, insert a new record >
164 if ($query_check == false) {
165 // < store each product info in database >
166 Db::getInstance()->insert('comuniti_scraper', array(
167 "store_name" => $value->store_name,
168 "product_id" => $id_product,
169 "item_name" => $value->item_name,
170 "item_image" => $value->item_image,
171 "unit_price" => $value->unit_price,
172 "unit" => $value->unit,
173 "package_price" => $value->package_price,
174 "package_unit" => $value->package_unit,
175 "currency" => $value->currency,
176 "status" => $value->status,
177 "deleted_at" => $value->deleted_at,
178 "created_at" => $value->created_at,
179 "updated_at" => $value->updated_at,
180 "store_logo" => 'http://ravit.projects.readybytes.net' . $value->store_logo
181 ));
182 } else // < if there is already a record about this store, update the current one >
183 {
184 Db::getInstance()->update('comuniti_scraper', array(
185 "store_name" => $value->store_name,
186 "product_id" => $id_product,
187 "item_name" => $value->item_name,
188 "item_image" => $value->item_image,
189 "unit_price" => $value->unit_price,
190 "unit" => $value->unit,
191 "package_price" => $value->package_price,
192 "package_unit" => $value->package_unit,
193 "currency" => $value->currency,
194 "status" => $value->status,
195 "deleted_at" => $value->deleted_at,
196 "created_at" => $value->created_at,
197 "updated_at" => $value->updated_at,
198 "store_logo" => 'http://ravit.projects.readybytes.net' . $value->store_logo), 'id = ' . $query_check['id']);
199 }
200
201 // < store all the stores in the scraper logs >
202 Db::getInstance()->insert('comuniti_scraper_logs', array(
203 "store_name" => $value->store_name,
204 "product_id" => $id_product,
205 "item_name" => $value->item_name,
206 "unit_price" => $value->unit_price,
207 "unit" => $value->unit,
208 "package_price" => $value->package_price,
209 "package_unit" => $value->package_unit,
210 "currency" => $value->currency,
211 "status" => $value->status,
212 "deleted_at" => $value->deleted_at,
213 "created_at" => $value->created_at,
214 "updated_at" => $value->updated_at
215 ));
216 }
217 }
218
219 // < update product's price using scrapper settings >
220 $id_product = $object_products->push_data->data->product_id_on_site;
221 $product = new Product($id_product);
222 $lowest_price = Comuniti_Scraper::getProductLowestPrice($id_product);
223 $new_price_pack = $lowest_price['package_price'] - ($lowest_price['package_price'] / 100) * $product->s_drop_percent;
224 $new_price_unit = $lowest_price['unit_price'] - ($lowest_price['unit_price'] / 100) * $product->s_drop_percent;
225
226 $query_cb_active = Db::getInstance()->getRow("SELECT count(*) AS 'count' FROM " . _DB_PREFIX_ . "comuniti_buy_profile WHERE id_product = " . $id_product . " AND status = 1 AND (start_unix <= " . time() . " AND duration_unix >= " . time() . ")");
227 $query_cb_tostart = Db::getInstance()->getRow("SELECT * FROM " . _DB_PREFIX_ . "comuniti_buy_profile WHERE id_product = " . $id_product . " AND status = 0 AND (start_unix IS NULL OR start_unix = 0 OR start_unix > " . time() . ")");
228 $minimum_cb = !$query_cb_tostart ? 0 : ($query_cb_tostart['minimum_price']+($query_cb_tostart['minimum_price']*$query_cb_tostart['treshold']));
229 // < check if product accepts price drop from scraper >
230 if ($product->s_accept_drop && $product->s_original_price > $new_price_pack && !$query_cb_active['count']>0 && $minimum_cb < $new_price_pack) {
231 // < check if product's minimum price is still higher than the new dropped price >
232 if ($new_price_pack >= $product->s_minimum_price) {
233 // < log the price update >
234 Db::getInstance()->insert('comuniti_scraper_pricedrop_logs', array(
235 "product_id" => $id_product,
236 "type" => "dropped by scraper",
237 "store_name" => $lowest_price['store_name'],
238 "old_price" => Comuniti_Scraper::getPriceWithVAT($product->price, Tax::getProductTaxRate($id_product)),
239 "new_price" => $new_price_pack,
240 ));
241
242 Db::getInstance()->update('product', array(
243 "price" => $new_price_pack - Comuniti_Scraper::getVATAmount($new_price_pack, Tax::getProductTaxRate($id_product)),
244 "unit_price_ratio" => ($new_price_pack - Comuniti_Scraper::getVATAmount($new_price_pack, Tax::getProductTaxRate($id_product))) / ($new_price_unit - Comuniti_Scraper::getVATAmount($new_price_unit, Tax::getProductTaxRate($id_product)))
245 ), 'id_product = ' . $id_product);
246
247 Db::getInstance()->update('product_shop', array(
248 "price" => $new_price_pack - Comuniti_Scraper::getVATAmount($new_price_pack, Tax::getProductTaxRate($id_product)),
249 "unit_price_ratio" => ($new_price_pack - Comuniti_Scraper::getVATAmount($new_price_pack, Tax::getProductTaxRate($id_product))) / ($new_price_unit - Comuniti_Scraper::getVATAmount($new_price_unit, Tax::getProductTaxRate($id_product)))
250 ), 'id_product = ' . $id_product);
251
252 // < update comuniti buy's price >
253 if ($product->comuniti_children != 0) {
254 $c_profile = Db::getInstance()->getRow("SELECT * FROM "._DB_PREFIX_."comuniti_buy_profile WHERE id_product = ". $product->comuniti_children);
255 if($c_profile['status'] == 0 && ($c_profile['start_unix'] == 0 || $c_profile['start_unix'] >= time())) {
256 Db::getInstance()->update('product', array(
257 "price" => $new_price_pack - Comuniti_Scraper::getVATAmount($new_price_pack, Tax::getProductTaxRate($product->comuniti_children)),
258 "unit_price_ratio" => ($new_price_pack - Comuniti_Scraper::getVATAmount($new_price_pack, Tax::getProductTaxRate($product->comuniti_children))) / ($new_price_unit - Comuniti_Scraper::getVATAmount($new_price_unit, Tax::getProductTaxRate($id_product)))
259 ), 'id_product = ' . $product->comuniti_children);
260 Db::getInstance()->update('product_shop', array(
261 "price" => $new_price_pack - Comuniti_Scraper::getVATAmount($new_price_pack, Tax::getProductTaxRate($product->comuniti_children)),
262 "unit_price_ratio" => ($new_price_pack - Comuniti_Scraper::getVATAmount($new_price_pack, Tax::getProductTaxRate($product->comuniti_children))) / ($new_price_unit - Comuniti_Scraper::getVATAmount($new_price_unit, Tax::getProductTaxRate($id_product)))
263 ), 'id_product = ' . $product->comuniti_children);
264 }
265 }
266 } elseif ($new_price_pack < $product->s_minimum_price) // < set product price to minimum price because the dropped price is lower than it >
267 {
268 // < log the price update >
269 Db::getInstance()->insert('comuniti_scraper_pricedrop_logs', array(
270 "product_id" => $id_product,
271 "type" => "dropped by minimum price",
272 "store_name" => 'minimum_price',
273 "old_price" => Comuniti_Scraper::getPriceWithVAT($product->price, Tax::getProductTaxRate($id_product)),
274 "new_price" => $product->s_minimum_price,
275 ));
276
277 // < update product's price >
278 Db::getInstance()->update('product', array(
279 "price" => $product->s_minimum_price - Comuniti_Scraper::getVATAmount($product->s_minimum_price, Tax::getProductTaxRate($id_product)),
280 "unit_price_ratio" => ($product->s_minimum_price - Comuniti_Scraper::getVATAmount($product->s_minimum_price, Tax::getProductTaxRate($id_product))) / ($product->s_minimum_price_unit - Comuniti_Scraper::getVATAmount($product->s_minimum_price_unit, Tax::getProductTaxRate($id_product)))
281 ), 'id_product = ' . $id_product);
282
283 // < update product's price >
284 Db::getInstance()->update('product_shop', array(
285 "price" => $product->s_minimum_price - Comuniti_Scraper::getVATAmount($product->s_minimum_price, Tax::getProductTaxRate($id_product)),
286 "unit_price_ratio" => ($product->s_minimum_price - Comuniti_Scraper::getVATAmount($product->s_minimum_price, Tax::getProductTaxRate($id_product))) / ($product->s_minimum_price_unit - Comuniti_Scraper::getVATAmount($product->s_minimum_price_unit, Tax::getProductTaxRate($id_product)))
287 ), 'id_product = ' . $id_product);
288
289 // < update comuniti buy's price >
290 if ($product->comuniti_children != 0) {
291 $c_profile = Db::getInstance()->getRow("SELECT * FROM "._DB_PREFIX_."comuniti_buy_profile WHERE id_product = ". $product->comuniti_children);
292 if($c_profile['status'] == 0 && ($c_profile['start_unix'] == 0 || $c_profile['start_unix'] >= time())) {
293 Db::getInstance()->update('product', array(
294 "price" => $product->s_minimum_price - Comuniti_Scraper::getVATAmount($product->s_minimum_price, Tax::getProductTaxRate($id_product)),
295 "unit_price_ratio" => ($product->s_minimum_price - Comuniti_Scraper::getVATAmount($product->s_minimum_price, Tax::getProductTaxRate($id_product))) / ($product->s_minimum_price_unit - Comuniti_Scraper::getVATAmount($product->s_minimum_price_unit, Tax::getProductTaxRate($id_product)))
296 ), 'id_product = ' . $product->comuniti_children);
297 Db::getInstance()->update('product_shop', array(
298 "price" => $product->s_minimum_price - Comuniti_Scraper::getVATAmount($product->s_minimum_price, Tax::getProductTaxRate($id_product)),
299 "unit_price_ratio" => ($product->s_minimum_price - Comuniti_Scraper::getVATAmount($product->s_minimum_price, Tax::getProductTaxRate($id_product))) / ($product->s_minimum_price_unit - Comuniti_Scraper::getVATAmount($product->s_minimum_price_unit, Tax::getProductTaxRate($id_product)))
300 ), 'id_product = ' . $product->comuniti_children);
301 }
302 }
303 }
304 }
305
306 $id_product = $object_products->push_data->data->product_id_on_site;
307 $product = new Product($id_product);
308 if ($specificPrices = SpecificPrice::getByProductId($id_product)) {
309 foreach ($specificPrices as $specificPrice) {
310 if (Product::getPriceStatic($id_product, true, null, 2, null, false, true, $specificPrice['from_quantity']) < $product->s_minimum_price) {
311 $sp = new SpecificPrice($specificPrice['id_specific_price']);
312 $sp->delete();
313 }
314 }
315 }
316
317 $specificPriceCategories = Db::getInstance()->executeS('SELECT * FROM ' . _DB_PREFIX_ . 'comuniti_category_specificprice');
318 $categories = $product->getCategories();
319 foreach ($specificPriceCategories as $specificPriceCategory) {
320 $ifexists = false;
321 foreach ($categories as $category) {
322 $ifexists = in_array($category, Comuniti_Scraper::getNestedCategoriesFromParent($specificPriceCategory['id_category']));
323 }
324 if ($ifexists) {
325 // < delete rules that has the same from_quantity value >
326 Db::getInstance()->execute("DELETE FROM " . _DB_PREFIX_ . "specific_price WHERE id_product = " . $product->id . " AND from_quantity = " . $specificPriceCategory['from_quantity']);
327 if (($product->getPrice(true) - ($product->getPrice(true) * $specificPriceCategory['reduction'])) > $product->s_minimum_price) {
328 Db::getInstance()->insert('specific_price', array(
329 'parent_rule_by_category' => $specificPriceCategory['id_category_specificprice'],
330 'id_specific_price_rule' => 0,
331 'id_cart' => 0,
332 'id_shop' => 1,
333 'id_shop_group' => 0,
334 'id_product_attribute' => 0,
335 'price' => -1.000000,
336 'id_product' => $product->id,
337 'id_currency' => $specificPriceCategory['id_currency'],
338 'id_country' => $specificPriceCategory['id_country'],
339 'id_group' => $specificPriceCategory['id_group'],
340 'id_customer' => $specificPriceCategory['id_customer'],
341 'from' => $specificPriceCategory['from'],
342 'to' => $specificPriceCategory['to'],
343 'from_quantity' => $specificPriceCategory['from_quantity'],
344 'reduction' => $specificPriceCategory['reduction'],
345 'reduction_type' => $specificPriceCategory['reduction_type'],
346 'reduction_tax' => $specificPriceCategory['reduction_tax']
347 ));
348 }
349 }
350 }
351 return Comuniti_Scraper::json_response(200, 'product has been updated');
352 } catch (Exception $e) {
353 return Comuniti_Scraper::json_response(500, 'something went wrong when trying to insert data' . $e);
354 }
355 } else {
356 $id_product = $object_products->push_data->data->product_id_on_site;
357 $product = new Product($id_product);
358 // < check if product accepts price drop from scraper >
359 if ($product->s_accept_drop) {
360 // < update product's price using scrapper settings (when scraper returns nulls) >
361 Db::getInstance()->execute("DELETE FROM " . _DB_PREFIX_ . "comuniti_scraper WHERE id_product=" . $id_product);
362
363 $new_price_pack = $product->s_original_price > 0 ? $product->s_original_price : $product->s_minimum_price;
364 $new_price_unit = $product->s_original_unit_price > 0 ? $product->s_original_unit_price : $product->s_minimum_price_unit;
365
366 Db::getInstance()->update('product', array(
367 "price" => $new_price_pack - Comuniti_Scraper::getVATAmount($new_price_pack, Tax::getProductTaxRate($id_product)),
368 "unit_price_ratio" => ($new_price_pack - Comuniti_Scraper::getVATAmount($new_price_pack, Tax::getProductTaxRate($id_product))) / ($new_price_unit - Comuniti_Scraper::getVATAmount($new_price_unit, Tax::getProductTaxRate($id_product)))
369 ), 'id_product = ' . $id_product);
370
371 Db::getInstance()->update('product_shop', array(
372 "price" => $new_price_pack - Comuniti_Scraper::getVATAmount($new_price_pack, Tax::getProductTaxRate($id_product)),
373 "unit_price_ratio" => ($new_price_pack - Comuniti_Scraper::getVATAmount($new_price_pack, Tax::getProductTaxRate($id_product))) / ($new_price_unit - Comuniti_Scraper::getVATAmount($new_price_unit, Tax::getProductTaxRate($id_product)))
374 ), 'id_product = ' . $id_product);
375
376 $product = new Product($id_product);
377 if ($specificPrices = SpecificPrice::getByProductId($id_product)) {
378 foreach ($specificPrices as $specificPrice) {
379 if (Product::getPriceStatic($id_product, true, null, 2, null, false, true, $specificPrice['from_quantity']) < $product->s_minimum_price) {
380 $sp = new SpecificPrice($specificPrice['id_specific_price']);
381 $sp->delete();
382 }
383 }
384 }
385 $specificPriceCategories = Db::getInstance()->executeS('SELECT * FROM ' . _DB_PREFIX_ . 'comuniti_category_specificprice');
386 $categories = $product->getCategories();
387 foreach ($specificPriceCategories as $specificPriceCategory) {
388 $ifexists = false;
389 foreach ($categories as $category) {
390 $ifexists = in_array($category, Comuniti_Scraper::getNestedCategoriesFromParent($specificPriceCategory['id_category']));
391 }
392 if ($ifexists) {
393 // < delete rules that has the same from_quantity value >
394 Db::getInstance()->execute("DELETE FROM " . _DB_PREFIX_ . "specific_price WHERE id_product = " . $product->id . " AND from_quantity = " . $specificPriceCategory['from_quantity']);
395 if (($product->getPrice(true) - ($product->getPrice(true) * $specificPriceCategory['reduction'])) > $product->s_minimum_price) {
396 Db::getInstance()->insert('specific_price', array(
397 'parent_rule_by_category' => $specificPriceCategory['id_category_specificprice'],
398 'id_specific_price_rule' => 0,
399 'id_cart' => 0,
400 'id_shop' => 1,
401 'id_shop_group' => 0,
402 'id_product_attribute' => 0,
403 'price' => -1.000000,
404 'id_product' => $product->id,
405 'id_currency' => $specificPriceCategory['id_currency'],
406 'id_country' => $specificPriceCategory['id_country'],
407 'id_group' => $specificPriceCategory['id_group'],
408 'id_customer' => $specificPriceCategory['id_customer'],
409 'from' => $specificPriceCategory['from'],
410 'to' => $specificPriceCategory['to'],
411 'from_quantity' => $specificPriceCategory['from_quantity'],
412 'reduction' => $specificPriceCategory['reduction'],
413 'reduction_type' => $specificPriceCategory['reduction_type'],
414 'reduction_tax' => $specificPriceCategory['reduction_tax']
415 ));
416 }
417 }
418 }
419 return Comuniti_Scraper::json_response(400, 'invalid data format');
420 }
421 }
422 }
423
424 /**
425 * function getProductCompetitors($product_id)
426 * @param: $product_id (id of the product from Prestashop catalog)
427 * @return: this function will return all prices from competitors with images etc.
428 */
429 public function getProductCompetitors($product_id)
430 {
431 return $query_price = Db::getInstance()->executeS('SELECT * FROM `' . _DB_PREFIX_ . 'comuniti_scraper` WHERE `product_id` = ' . $product_id);
432 }
433
434 /**
435 * function getProductCompetitorsLogs($product_id)
436 * @param: $product_id (id of the product from Prestashop catalog)
437 * @return: this function will return all prices from competitors with images etc.
438 */
439 public function getProductCompetitorsLogs($product_id)
440 {
441 return $query_logs = Db::getInstance()->executeS('SELECT * FROM `' . _DB_PREFIX_ . 'comuniti_scraper_logs` WHERE `product_id` = ' . $product_id . ' ORDER BY `store_name`, `timestamp` DESC LIMIT 50');
442 }
443
444 /**
445 * function getProductPriceDropLogs($product_id)
446 * @param: $product_id (id of the product from Prestashop catalog)
447 * @return: this function will return all prices from competitors with images etc.
448 */
449 public function getProductPriceDropLogs($product_id)
450 {
451 return $query_logs = Db::getInstance()->executeS('SELECT * FROM `' . _DB_PREFIX_ . 'comuniti_scraper_pricedrop_logs` WHERE `product_id` = ' . $product_id . ' ORDER BY `timestamp` DESC LIMIT 50');
452 }
453
454 /**
455 * function getProductLowestPrice($product_id)
456 * @param: $product_id (id of the product from Prestashop catalog)
457 * @return: this function will return lowest price from competitors, ordered by `unit_price`
458 */
459 public static function getProductLowestPrice($product_id)
460 {
461 return $query_price = Db::getInstance()->getRow('SELECT * FROM `' . _DB_PREFIX_ . 'comuniti_scraper` WHERE `product_id` = ' . $product_id . ' ORDER BY `package_price` ASC');
462 }
463
464 /**
465 * function getProductHighestPrice($product_id)
466 * @param: $product_id (id of the product from Prestashop catalog)
467 * @return: this function will return highest price from competitors, ordered by `unit_price`
468 */
469 public static function getProductHighestPrice($product_id)
470 {
471 return $query_price = Db::getInstance()->getRow('SELECT * FROM `' . _DB_PREFIX_ . 'comuniti_scraper` WHERE `product_id` = ' . $product_id . ' ORDER BY `package_price` DESC');
472 }
473
474 /**
475 * @hook: {hook h="hookDisplayProductCompetitors" id_product=null}
476 * @param: $product_id (id of the product from Prestashop catalog)
477 * @return: this function will create competitors block on product page
478 */
479 public function hookDisplayProductCompetitors($params = null)
480 {
481 $product = $params['product'];
482 $competitors = $this->getProductCompetitors($product['id_product']);
483 $highest_price = Comuniti_Scraper::getProductHighestPrice($product['id_product']);
484
485 if (count($competitors) > 1 && ($highest_price['package_price'] - $product['embedded_attributes']['price']) > 0)
486 $save_money = $highest_price['package_price'] - $product['embedded_attributes']['price'];
487 else $save_money = 0;
488
489 $this->context->smarty->assign(
490 array(
491 'id_product', $product['id_product'],
492 'competitors' => $competitors,
493 'save_money' => $save_money,
494 'curency' => $this->context->currency->sign,
495 'image_url' => $this->context->link->getMediaLink(_MODULE_DIR_ . 'comuniti_scraper/images/competitors/')
496 )
497 );
498 return $this->display(__FILE__, 'views/templates/hook/competitors.tpl');
499 }
500
501 public function getProductDetailsByIDforFO($product)
502 {
503 $assembler = new ProductAssembler($this->context);
504
505 $presenterFactory = new ProductPresenterFactory($this->context);
506 $presentationSettings = $presenterFactory->getPresentationSettings();
507 $presenter = new ProductListingPresenter(
508 new ImageRetriever(
509 $this->context->link
510 ),
511 $this->context->link,
512 new PriceFormatter(),
513 new ProductColorsRetriever(),
514 $this->context->getTranslator()
515 );
516
517 $products_info = $presenter->present(
518 $presentationSettings,
519 $assembler->assembleProduct($product),
520 $this->context->language
521 );
522 return $products_info;
523 }
524
525 /**
526 * @function: json_response($code = 200, $message = null)
527 * @param: $code (id of the http response code) | $message (message of the response)
528 * @return: this function will return the status and message code
529 * @source: https://gist.github.com/james2doyle/33794328675a6c88edd6#file-simple-json-reponse-php
530 */
531 public static function json_response($code = 200, $message = null)
532 {
533 header_remove();
534 http_response_code($code);
535 header("Cache-Control: no-transform,public,max-age=300,s-maxage=900");
536 header('Content-Type: application/json');
537
538 $status = array(
539 200 => '200 OK',
540 400 => '400 Bad Request',
541 422 => 'Unprocessable Entity',
542 500 => '500 Internal Server Error'
543 );
544
545 header('Status: ' . $status[$code]);
546 die(json_encode(array
547 (
548 'status' => $code < 300,
549 'message' => $message
550 )));
551 }
552
553 /**
554 * @source http://thisinterestsme.com/calculating-vat-with-php/
555 * @description This function is used to calculate the vat amount from the gross price
556 * @param $price
557 * @param $vat
558 * @return float
559 */
560 public static function getVATAmount($price, $vat)
561 {
562 $vatDivisor = 1 + ($vat / 100);
563 $priceBeforeVat = $price / $vatDivisor;
564 $vatAmount = $price - $priceBeforeVat;
565 return round($vatAmount, 6);
566 }
567
568 /**
569 * @source http://thisinterestsme.com/calculating-vat-with-php/
570 * @description This function is used to calculate the vat amount from the gross price
571 * @param $price
572 * @param $vat
573 * @return float
574 */
575 public static function getPriceWithVAT($price, $vat)
576 {
577 $vatDivisor = 1 + ($vat / 100);
578 $priceBeforeVat = $price * $vatDivisor;
579 return round($priceBeforeVat, 2);
580 }
581
582 // < Display Settings Tab in product BO page >
583 public function hookDisplayAdminProductsExtra($params)
584 {
585 $id_product = $params['id_product'];
586 $q_data = Db::getInstance()->getRow('SELECT * FROM `' . _DB_PREFIX_ . 'product` WHERE `id_product` = ' . $id_product);
587 $c_data = $this->getProductCompetitors($id_product);
588 $l_data = $this->getProductCompetitorsLogs($id_product);
589 $d_data = $this->getProductPriceDropLogs($id_product);
590
591 $data_assign = array(
592 'query_data' => $q_data,
593 'competitors' => $c_data,
594 'competitors_logs' => $l_data,
595 'pricedrop_logs' => $d_data,
596 'image_url' => $this->context->link->getMediaLink(_MODULE_DIR_ . 'comuniti_scraper/images/competitors/')
597 );
598
599 // < render the template from the product BO tab >
600 $this->context->smarty->assign($data_assign);
601 return $this->display(__FILE__, 'comuniti_scraper.tpl');
602 }
603
604 // < Update product's scraper settings from the BO tab >
605 public function hookActionProductUpdate($params)
606 {
607 // < update comuniti profile of the product >
608 Db::getInstance()->update('product', array(
609 's_original_price' => (float)Tools::getValue('s_original_price'),
610 's_original_unit_price' => (float)Tools::getValue('s_original_unit_price'),
611 's_accept_drop' => (int)Tools::getValue('s_accept_drop'),
612 's_drop_percent' => (float)Tools::getValue('s_drop_percent'),
613 's_minimum_price' => (float)Tools::getValue('s_minimum_price'),
614 's_minimum_price_unit' => (float)Tools::getValue('s_minimum_price_unit')), 'id_product = ' . (int)Tools::getValue('id_product'));
615
616 $id_product = (int)Tools::getValue('id_product');
617
618 Db::getInstance()->execute("DELETE FROM " . _DB_PREFIX_ . "comuniti_scraper WHERE (package_price = 0 OR package_price IS NULL) AND product_id = " . $id_product);
619
620 // < query database to check if store exists >
621 $query_check = Db::getInstance()->getRow("SELECT count(*) AS 'count' FROM " . _DB_PREFIX_ . "comuniti_scraper as a WHERE a.product_id = " . $id_product);
622
623 if ($query_check['count'] > 0) {
624 $product = new Product($id_product);
625 // < update product's price using scrapper settings >
626 $lowest_price = Comuniti_Scraper::getProductLowestPrice($id_product);
627 $new_price_pack = $lowest_price['package_price'] - ($lowest_price['package_price'] / 100) * $product->s_drop_percent;
628 $new_price_unit = $lowest_price['unit_price'] - ($lowest_price['unit_price'] / 100) * $product->s_drop_percent;
629
630 $query_cb_active = Db::getInstance()->getRow("SELECT count(*) AS 'count' FROM " . _DB_PREFIX_ . "comuniti_buy_profile WHERE id_product = " . $id_product . " AND status = 1 AND (start_unix <= " . time() . " AND duration_unix >= " . time() . ")");
631 $query_cb_tostart = Db::getInstance()->getRow("SELECT * FROM " . _DB_PREFIX_ . "comuniti_buy_profile WHERE id_product = " . $id_product . " AND status = 0 AND (start_unix IS NULL OR start_unix = 0 OR start_unix > " . time() . ")");
632 $minimum_cb = !$query_cb_tostart ? 0 : ($query_cb_tostart['minimum_price']+($query_cb_tostart['minimum_price']*$query_cb_tostart['treshold']));
633 // < check if product accepts price drop from scraper >
634 if ($product->s_accept_drop && $product->s_original_price > $new_price_pack && !$query_cb_active['count']>0 && $minimum_cb < $new_price_pack) {
635 // < check if product's minimum price is still higher than the new dropped price >
636 if ($new_price_pack >= $product->s_minimum_price) {
637 // < log the price update >
638 Db::getInstance()->insert('comuniti_scraper_pricedrop_logs', array(
639 "product_id" => $id_product,
640 "type" => "dropped by scraper",
641 "store_name" => $lowest_price['store_name'],
642 "old_price" => Comuniti_Scraper::getPriceWithVAT($product->price, Tax::getProductTaxRate($id_product)),
643 "new_price" => $new_price_pack,
644 ));
645
646 Db::getInstance()->update('product', array(
647 "price" => $new_price_pack - Comuniti_Scraper::getVATAmount($new_price_pack, Tax::getProductTaxRate($id_product)),
648 "unit_price_ratio" => ($new_price_pack - Comuniti_Scraper::getVATAmount($new_price_pack, Tax::getProductTaxRate($id_product))) / ($new_price_unit - Comuniti_Scraper::getVATAmount($new_price_unit, Tax::getProductTaxRate($id_product)))
649 ), 'id_product = ' . $id_product);
650
651 Db::getInstance()->update('product_shop', array(
652 "price" => $new_price_pack - Comuniti_Scraper::getVATAmount($new_price_pack, Tax::getProductTaxRate($id_product)),
653 "unit_price_ratio" => ($new_price_pack - Comuniti_Scraper::getVATAmount($new_price_pack, Tax::getProductTaxRate($id_product))) / ($new_price_unit - Comuniti_Scraper::getVATAmount($new_price_unit, Tax::getProductTaxRate($id_product)))
654 ), 'id_product = ' . $id_product);
655
656 // < update comuniti buy's price >
657 if ($product->comuniti_children != 0) {
658 $c_profile = Db::getInstance()->getRow("SELECT * FROM "._DB_PREFIX_."comuniti_buy_profile WHERE id_product = ". $product->comuniti_children);
659 if($c_profile['status'] == 0 && ($c_profile['start_unix'] == 0 || $c_profile['start_unix'] >= time())) {
660 Db::getInstance()->update('product', array(
661 "price" => $new_price_pack - Comuniti_Scraper::getVATAmount($new_price_pack, Tax::getProductTaxRate($product->comuniti_children)),
662 "unit_price_ratio" => ($new_price_pack - Comuniti_Scraper::getVATAmount($new_price_pack, Tax::getProductTaxRate($product->comuniti_children))) / ($new_price_unit - Comuniti_Scraper::getVATAmount($new_price_unit, Tax::getProductTaxRate($id_product)))
663 ), 'id_product = ' . $product->comuniti_children);
664 Db::getInstance()->update('product_shop', array(
665 "price" => $new_price_pack - Comuniti_Scraper::getVATAmount($new_price_pack, Tax::getProductTaxRate($product->comuniti_children)),
666 "unit_price_ratio" => ($new_price_pack - Comuniti_Scraper::getVATAmount($new_price_pack, Tax::getProductTaxRate($product->comuniti_children))) / ($new_price_unit - Comuniti_Scraper::getVATAmount($new_price_unit, Tax::getProductTaxRate($id_product)))
667 ), 'id_product = ' . $product->comuniti_children);
668 }
669 }
670 } elseif ($new_price_pack < $product->s_minimum_price) // < set product price to minimum price because the dropped price is lower than it >
671 {
672 // < log the price update >
673 Db::getInstance()->insert('comuniti_scraper_pricedrop_logs', array(
674 "product_id" => $id_product,
675 "type" => "dropped by minimum price",
676 "store_name" => 'minimum_price',
677 "old_price" => Comuniti_Scraper::getPriceWithVAT($product->price, Tax::getProductTaxRate($id_product)),
678 "new_price" => $product->s_minimum_price,
679 ));
680
681 // < update product's price >
682 Db::getInstance()->update('product', array(
683 "price" => $product->s_minimum_price - Comuniti_Scraper::getVATAmount($product->s_minimum_price, Tax::getProductTaxRate($id_product)),
684 "unit_price_ratio" => ($product->s_minimum_price - Comuniti_Scraper::getVATAmount($product->s_minimum_price, Tax::getProductTaxRate($id_product))) / ($product->s_minimum_price_unit - Comuniti_Scraper::getVATAmount($product->s_minimum_price_unit, Tax::getProductTaxRate($id_product)))
685 ), 'id_product = ' . $id_product);
686
687 // < update product's price >
688 Db::getInstance()->update('product_shop', array(
689 "price" => $product->s_minimum_price - Comuniti_Scraper::getVATAmount($product->s_minimum_price, Tax::getProductTaxRate($id_product)),
690 "unit_price_ratio" => ($product->s_minimum_price - Comuniti_Scraper::getVATAmount($product->s_minimum_price, Tax::getProductTaxRate($id_product))) / ($product->s_minimum_price_unit - Comuniti_Scraper::getVATAmount($product->s_minimum_price_unit, Tax::getProductTaxRate($id_product)))
691 ), 'id_product = ' . $id_product);
692
693 // < update comuniti buy's price >
694 if ($product->comuniti_children != 0) {
695 $c_profile = Db::getInstance()->getRow("SELECT * FROM "._DB_PREFIX_."comuniti_buy_profile WHERE id_product = ". $product->comuniti_children);
696 if($c_profile['status'] == 0 && ($c_profile['start_unix'] == 0 || $c_profile['start_unix'] >= time())) {
697 Db::getInstance()->update('product', array(
698 "price" => $product->s_minimum_price - Comuniti_Scraper::getVATAmount($product->s_minimum_price, Tax::getProductTaxRate($id_product)),
699 "unit_price_ratio" => ($product->s_minimum_price - Comuniti_Scraper::getVATAmount($product->s_minimum_price, Tax::getProductTaxRate($id_product))) / ($product->s_minimum_price_unit - Comuniti_Scraper::getVATAmount($product->s_minimum_price_unit, Tax::getProductTaxRate($id_product)))
700 ), 'id_product = ' . $product->comuniti_children);
701 Db::getInstance()->update('product_shop', array(
702 "price" => $product->s_minimum_price - Comuniti_Scraper::getVATAmount($product->s_minimum_price, Tax::getProductTaxRate($id_product)),
703 "unit_price_ratio" => ($product->s_minimum_price - Comuniti_Scraper::getVATAmount($product->s_minimum_price, Tax::getProductTaxRate($id_product))) / ($product->s_minimum_price_unit - Comuniti_Scraper::getVATAmount($product->s_minimum_price_unit, Tax::getProductTaxRate($id_product)))
704 ), 'id_product = ' . $product->comuniti_children);
705 }
706 }
707 }
708 }
709
710 if ($specificPrices = SpecificPrice::getByProductId($id_product)) {
711 foreach ($specificPrices as $specificPrice) {
712 if (Product::getPriceStatic($id_product, true, null, 2, null, false, true, $specificPrice['from_quantity']) < $product->s_minimum_price) {
713 $sp = new SpecificPrice($specificPrice['id_specific_price']);
714 $sp->delete();
715 }
716 }
717 }
718
719 $specificPriceCategories = Db::getInstance()->executeS('SELECT * FROM ' . _DB_PREFIX_ . 'comuniti_category_specificprice');
720 $categories = $product->getCategories();
721 foreach ($specificPriceCategories as $specificPriceCategory) {
722 $ifexists = false;
723 foreach ($categories as $category) {
724 $ifexists = in_array($category, Comuniti_Scraper::getNestedCategoriesFromParent($specificPriceCategory['id_category']));
725 }
726 if ($ifexists) {
727 // < delete rules that has the same from_quantity value >
728 Db::getInstance()->execute("DELETE FROM " . _DB_PREFIX_ . "specific_price WHERE id_product = " . $product->id . " AND from_quantity = " . $specificPriceCategory['from_quantity']);
729 if (($product->getPrice(true) - ($product->getPrice(true) * $specificPriceCategory['reduction'])) > $product->s_minimum_price) {
730 Db::getInstance()->insert('specific_price', array(
731 'parent_rule_by_category' => $specificPriceCategory['id_category_specificprice'],
732 'id_specific_price_rule' => 0,
733 'id_cart' => 0,
734 'id_shop' => 1,
735 'id_shop_group' => 0,
736 'id_product_attribute' => 0,
737 'price' => -1.000000,
738 'id_product' => $product->id,
739 'id_currency' => $specificPriceCategory['id_currency'],
740 'id_country' => $specificPriceCategory['id_country'],
741 'id_group' => $specificPriceCategory['id_group'],
742 'id_customer' => $specificPriceCategory['id_customer'],
743 'from' => $specificPriceCategory['from'],
744 'to' => $specificPriceCategory['to'],
745 'from_quantity' => $specificPriceCategory['from_quantity'],
746 'reduction' => $specificPriceCategory['reduction'],
747 'reduction_type' => $specificPriceCategory['reduction_type'],
748 'reduction_tax' => $specificPriceCategory['reduction_tax']
749 ));
750 }
751 }
752 }
753 }else{
754 $product = new Product($id_product);
755
756 // < check if product accepts price drop from scraper >
757 if ($product->s_accept_drop) {
758 Db::getInstance()->execute("DELETE FROM " . _DB_PREFIX_ . "comuniti_scraper WHERE id_product=" . $id_product);
759
760 $new_price_pack = $product->s_original_price > 0 ? $product->s_original_price : $product->s_minimum_price;
761 $new_price_unit = $product->s_original_unit_price > 0 ? $product->s_original_unit_price : $product->s_minimum_price_unit;
762
763 Db::getInstance()->update('product', array(
764 "price" => $new_price_pack - Comuniti_Scraper::getVATAmount($new_price_pack, Tax::getProductTaxRate($id_product)),
765 "unit_price_ratio" => ($new_price_pack - Comuniti_Scraper::getVATAmount($new_price_pack, Tax::getProductTaxRate($id_product))) / ($new_price_unit - Comuniti_Scraper::getVATAmount($new_price_unit, Tax::getProductTaxRate($id_product)))
766 ), 'id_product = ' . $id_product);
767
768 Db::getInstance()->update('product_shop', array(
769 "price" => $new_price_pack - Comuniti_Scraper::getVATAmount($new_price_pack, Tax::getProductTaxRate($id_product)),
770 "unit_price_ratio" => ($new_price_pack - Comuniti_Scraper::getVATAmount($new_price_pack, Tax::getProductTaxRate($id_product))) / ($new_price_unit - Comuniti_Scraper::getVATAmount($new_price_unit, Tax::getProductTaxRate($id_product)))
771 ), 'id_product = ' . $id_product);
772 }
773
774 if ($specificPrices = SpecificPrice::getByProductId($id_product)) {
775 foreach ($specificPrices as $specificPrice) {
776 if (Product::getPriceStatic($id_product, true, null, 2, null, false, true, $specificPrice['from_quantity']) < $product->s_minimum_price) {
777 $sp = new SpecificPrice($specificPrice['id_specific_price']);
778 $sp->delete();
779 }
780 }
781 }
782
783 $specificPriceCategories = Db::getInstance()->executeS('SELECT * FROM ' . _DB_PREFIX_ . 'comuniti_category_specificprice');
784 $categories = $product->getCategories();
785 foreach ($specificPriceCategories as $specificPriceCategory) {
786 $ifexists = false;
787 foreach ($categories as $category) {
788 $ifexists = in_array($category, Comuniti_Scraper::getNestedCategoriesFromParent($specificPriceCategory['id_category']));
789 }
790 if ($ifexists) {
791 // < delete rules that has the same from_quantity value >
792 Db::getInstance()->execute("DELETE FROM " . _DB_PREFIX_ . "specific_price WHERE id_product = " . $product->id . " AND from_quantity = " . $specificPriceCategory['from_quantity']);
793 if (($product->getPrice(true) - ($product->getPrice(true) * $specificPriceCategory['reduction'])) > $product->s_minimum_price) {
794 Db::getInstance()->insert('specific_price', array(
795 'parent_rule_by_category' => $specificPriceCategory['id_category_specificprice'],
796 'id_specific_price_rule' => 0,
797 'id_cart' => 0,
798 'id_shop' => 1,
799 'id_shop_group' => 0,
800 'id_product_attribute' => 0,
801 'price' => -1.000000,
802 'id_product' => $product->id,
803 'id_currency' => $specificPriceCategory['id_currency'],
804 'id_country' => $specificPriceCategory['id_country'],
805 'id_group' => $specificPriceCategory['id_group'],
806 'id_customer' => $specificPriceCategory['id_customer'],
807 'from' => $specificPriceCategory['from'],
808 'to' => $specificPriceCategory['to'],
809 'from_quantity' => $specificPriceCategory['from_quantity'],
810 'reduction' => $specificPriceCategory['reduction'],
811 'reduction_type' => $specificPriceCategory['reduction_type'],
812 'reduction_tax' => $specificPriceCategory['reduction_tax']
813 ));
814 }
815 }
816 }
817 }
818 }
819
820 public static function getNestedCategoriesFromParent($id_category)
821 {
822 $cats_array = array();
823 $cats_array[] = $id_category;
824 $childCategories = Db::getInstance()->executeS('SELECT * FROM ' . _DB_PREFIX_ . 'category WHERE id_parent = ' . $id_category);
825 foreach ($childCategories as $child) {
826 $cats_array[] = $child['id_category'];
827 $childsCats = Comuniti_Scraper::getNestedCategoriesFromParent($child['id_category']);
828 for ($i = 0; $i < count($childsCats); $i++) {
829 $cats_array[] = $childsCats[$i];
830 }
831 }
832 return $cats_array;
833 }
834
835 public function cronDeleteOldScraperPriceDropLogs()
836 {
837 // < get the status of the comuniti cronjob >
838 $cron_check = Db::getInstance()->executeS("SELECT * FROM `" . _DB_PREFIX_ . "semaphore` WHERE `name` = 'comuniti_cron'");
839
840 // < if the cron status is offline | status = 0 execute the cronjob >
841 if ($cron_check['status'] == 0) {
842 // < announce that the cronjob is executing >
843 print "<<< Comuniti Cronjob is running now... >>>";
844
845 // < update the semaphore's status >
846 Db::getInstance()->update('semaphore', array('status' => 1, 'timestamp' => time()), "name = 'comuniti_cron'");
847
848 $cron_check = Db::getInstance()->execute("DELETE FROM `" . _DB_PREFIX_ . "comuniti_scraper_pricedrop_logs` WHERE `timestamp` < '" . date('Y-m-d H:i:s', strtotime("-1 week")) . "'");
849 // < update the semaphore's status >
850 Db::getInstance()->update('semaphore', array('status' => 0),
851 "name = 'comuniti_cron'");
852 } elseif ($cron_check['status'] == 1) { // < the cronjob is still running >
853 // < check if the cronjob is not stucked >
854 if ((time() - $cron_check['timestamp']) >= 180) { // if is stuck since 3 minutes ago
855 // < update the semaphore's status >
856 print "<<< Comuniti Cronjob was stuck, unblocking it... >>>";
857 Db::getInstance()->update('semaphore', array('status' => 0),
858 "name = 'comuniti_cron'");
859 die;
860 } else { // < die the action >
861 print "<<< Comuniti Cronjob is still running! >>>";
862 die;
863 }
864 }
865 }
866
867 public function cronGetProductsWithNoScraper()
868 {
869 // < get the status of the comuniti cronjob >
870 $cron_check = Db::getInstance()->executeS("SELECT * FROM `" . _DB_PREFIX_ . "semaphore` WHERE `name` = 'comuniti_cron'");
871
872 // < if the cron status is offline | status = 0 execute the cronjob >
873 if ($cron_check['status'] == 0) {
874 // < announce that the cronjob is executing >
875 print "<<< Comuniti Cronjob is running now... >>>";
876
877 // < update the semaphore's status >
878 Db::getInstance()->update('semaphore', array('status' => 1, 'timestamp' => time()), "name = 'comuniti_cron'");
879
880 $cron_check = Db::getInstance()->execute("SELECT * FROM `" . _DB_PREFIX_ . "product` WHERE id_product NOT IN (SELECT ps_live_comuniti_scraper.product_id FROM ps_live_comuniti_scraper) AND active=1 AND s_accept_drop=1;");
881 $str_products=[];
882 foreach($cron_check as $item){
883 $str_products[]=$item['id_product'];
884 }
885 $templateVars = array(
886 '{products}' => implode(', ', $str_products)
887 );
888
889 @Mail::Send(
890 2,
891 'products_no_scraper',
892 'Active Products with no Competitors',
893 $templateVars,
894 'info@comuniti.pt',
895 'Comuniti',
896 null,
897 null,
898 null,
899 null,
900 _PS_MAIL_DIR_,
901 true,
902 1
903 );
904
905 // < update the semaphore's status >
906 Db::getInstance()->update('semaphore', array('status' => 0),
907 "name = 'comuniti_cron'");
908 } elseif ($cron_check['status'] == 1) { // < the cronjob is still running >
909 // < check if the cronjob is not stucked >
910 if ((time() - $cron_check['timestamp']) >= 180) { // if is stuck since 3 minutes ago
911 // < update the semaphore's status >
912 print "<<< Comuniti Cronjob was stuck, unblocking it... >>>";
913 Db::getInstance()->update('semaphore', array('status' => 0),
914 "name = 'comuniti_cron'");
915 die;
916 } else { // < die the action >
917 print "<<< Comuniti Cronjob is still running! >>>";
918 die;
919 }
920 }
921 }
922
923 public function cronIndexSearchProducts()
924 {
925 // < get the status of the comuniti cronjob >
926 $cron_check = Db::getInstance()->executeS("SELECT * FROM `" . _DB_PREFIX_ . "semaphore` WHERE `name` = 'comuniti_cron'");
927
928 // < if the cron status is offline | status = 0 execute the cronjob >
929 if ($cron_check['status'] == 0) {
930 // < announce that the cronjob is executing >
931 print "<<< Comuniti Cronjob is running now... >>>";
932
933 // < update the semaphore's status >
934 Db::getInstance()->update('semaphore', array('status' => 1, 'timestamp' => time()), "name = 'comuniti_cron'");
935
936 $cron_check = Db::getInstance()->execute("UPDATE `" . _DB_PREFIX_ . "product` SET indexed=1 WHERE active=1");
937 $cron_check = Db::getInstance()->execute("UPDATE `" . _DB_PREFIX_ . "product_shop` SET indexed=1 WHERE active=1");
938 // < update the semaphore's status >
939 Db::getInstance()->update('semaphore', array('status' => 0),
940 "name = 'comuniti_cron'");
941 } elseif ($cron_check['status'] == 1) { // < the cronjob is still running >
942 // < check if the cronjob is not stucked >
943 if ((time() - $cron_check['timestamp']) >= 180) { // if is stuck since 3 minutes ago
944 // < update the semaphore's status >
945 print "<<< Comuniti Cronjob was stuck, unblocking it... >>>";
946 Db::getInstance()->update('semaphore', array('status' => 0),
947 "name = 'comuniti_cron'");
948 die;
949 } else { // < die the action >
950 print "<<< Comuniti Cronjob is still running! >>>";
951 die;
952 }
953 }
954 }
955}