1<?php 2/** 3 * Copyright since 2007 PrestaShop SA and Contributors 4 * PrestaShop is an International Registered Trademark & Property of PrestaShop SA 5 * 6 * NOTICE OF LICENSE 7 * 8 * This source file is subject to the Academic Free License 3.0 (AFL-3.0) 9 * that is bundled with this package in the file LICENSE.md. 10 * It is also available through the world-wide-web at this URL: 11 * https://opensource.org/licenses/AFL-3.0 12 * If you did not receive a copy of the license and are unable to 13 * obtain it through the world-wide-web, please send an email 14 * to license@prestashop.com so we can send you a copy immediately. 15 * 16 * DISCLAIMER 17 * 18 * Do not edit or add to this file if you wish to upgrade PrestaShop to newer 19 * versions in the future. If you wish to customize PrestaShop for your 20 * needs please refer to https://devdocs.prestashop.com/ for more information. 21 * 22 * @author PrestaShop SA and Contributors <contact@prestashop.com> 23 * @copyright Since 2007 PrestaShop SA and Contributors 24 * @license https://opensource.org/licenses/AFL-3.0 Academic Free License 3.0 (AFL-3.0) 25 */ 26if (!defined('_PS_VERSION_')) { 27 exit; 28} 29 30class ProductComment extends ObjectModel 31{ 32 /** @var int */ 33 public $id; 34 35 /** @var int */ 36 public $id_product; 37 38 /** @var int */ 39 public $id_customer; 40 41 /** @var int */ 42 public $id_guest; 43 44 /** @var int */ 45 public $customer_name; 46 47 /** @var string */ 48 public $title; 49 50 /** @var string */ 51 public $content; 52 53 /** @var int */ 54 public $grade; 55 56 /** @var bool */ 57 public $validate = false; 58 59 /** @var bool */ 60 public $deleted = false; 61 62 /** @var string Object creation date */ 63 public $date_add; 64 65 /** 66 * @see ObjectModel::$definition 67 */ 68 public static $definition = [ 69 'table' => 'product_comment', 70 'primary' => 'id_product_comment', 71 'fields' => [ 72 'id_product' => ['type' => self::TYPE_INT, 'validate' => 'isUnsignedId', 'required' => true], 73 'id_customer' => ['type' => self::TYPE_INT, 'validate' => 'isUnsignedId', 'required' => true], 74 'id_guest' => ['type' => self::TYPE_INT], 75 'customer_name' => ['type' => self::TYPE_STRING], 76 'title' => ['type' => self::TYPE_STRING], 77 'content' => ['type' => self::TYPE_STRING, 'validate' => 'isMessage', 'size' => 65535, 'required' => true], 78 'grade' => ['type' => self::TYPE_FLOAT, 'validate' => 'isFloat'], 79 'validate' => ['type' => self::TYPE_BOOL, 'validate' => 'isBool'], 80 'deleted' => ['type' => self::TYPE_BOOL], 81 'date_add' => ['type' => self::TYPE_DATE], 82 ], 83 ]; 84 85 /** 86 * Get comments by IdProduct 87 * 88 * @return array|bool 89 */ 90 public static function getByProduct($id_product, $p = 1, $n = null, $id_customer = null) 91 { 92 if (!Validate::isUnsignedId($id_product)) { 93 return false; 94 } 95 $validate = (bool) Configuration::get('PRODUCT_COMMENTS_MODERATE'); 96 $p = (int) $p; 97 $n = (int) $n; 98 if ($p <= 1) { 99 $p = 1; 100 } 101 if ($n != null && $n <= 0) { 102 $n = 5; 103 } 104 105 $cache_id = 'ProductComment::getByProduct_' . (int) $id_product . '-' . (int) $p . '-' . (int) $n . '-' . (int) $id_customer . '-' . (bool) $validate; 106 if (!Cache::isStored($cache_id)) { 107 $result = Db::getInstance((bool) _PS_USE_SQL_SLAVE_)->executeS(' 108 SELECT pc.`id_product_comment`, 109 (SELECT count(*) FROM `' . _DB_PREFIX_ . 'product_comment_usefulness` pcu WHERE pcu.`id_product_comment` = pc.`id_product_comment` AND pcu.`usefulness` = 1) as total_useful, 110 (SELECT count(*) FROM `' . _DB_PREFIX_ . 'product_comment_usefulness` pcu WHERE pcu.`id_product_comment` = pc.`id_product_comment`) as total_advice, ' . 111 ((int) $id_customer ? '(SELECT count(*) FROM `' . _DB_PREFIX_ . 'product_comment_usefulness` pcuc WHERE pcuc.`id_product_comment` = pc.`id_product_comment` AND pcuc.id_customer = ' . (int) $id_customer . ') as customer_advice, ' : '') . 112 ((int) $id_customer ? '(SELECT count(*) FROM `' . _DB_PREFIX_ . 'product_comment_report` pcrc WHERE pcrc.`id_product_comment` = pc.`id_product_comment` AND pcrc.id_customer = ' . (int) $id_customer . ') as customer_report, ' : '') . ' 113 IF(c.id_customer, CONCAT(c.`firstname`, \' \', LEFT(c.`lastname`, 1)), pc.customer_name) customer_name, pc.`content`, pc.`grade`, pc.`date_add`, pc.title 114 FROM `' . _DB_PREFIX_ . 'product_comment` pc 115 LEFT JOIN `' . _DB_PREFIX_ . 'customer` c ON c.`id_customer` = pc.`id_customer` 116 WHERE pc.`id_product` = ' . (int) ($id_product) . ($validate ? ' AND pc.`validate` = 1' : '') . ' 117 ORDER BY pc.`date_add` DESC 118 ' . ($n ? 'LIMIT ' . (int) (($p - 1) * $n) . ', ' . (int) ($n) : '')); 119 Cache::store($cache_id, $result); 120 } 121 122 return Cache::retrieve($cache_id); 123 } 124 125 /** 126 * Return customer's comment 127 * 128 * @return array Comments 129 */ 130 public static function getByCustomer($id_product, $id_customer, $get_last = false, $id_guest = false) 131 { 132 $cache_id = 'ProductComment::getByCustomer_' . (int) $id_product . '-' . (int) $id_customer . '-' . (bool) $get_last . '-' . (int) $id_guest; 133 if (!Cache::isStored($cache_id)) { 134 $results = Db::getInstance()->executeS(' 135 SELECT * 136 FROM `' . _DB_PREFIX_ . 'product_comment` pc 137 WHERE pc.`id_product` = ' . (int) $id_product . ' 138 AND ' . (!$id_guest ? 'pc.`id_customer` = ' . (int) $id_customer : 'pc.`id_guest` = ' . (int) $id_guest) . ' 139 ORDER BY pc.`date_add` DESC ' 140 . ($get_last ? 'LIMIT 1' : '') 141 ); 142 143 if ($get_last && count($results)) { 144 $results = array_shift($results); 145 } 146 147 Cache::store($cache_id, $results); 148 } 149 150 return Cache::retrieve($cache_id); 151 } 152 153 /** 154 * Get Grade By product 155 * 156 * @return array|bool 157 */ 158 public static function getGradeByProduct($id_product, $id_lang) 159 { 160 if (!Validate::isUnsignedId($id_product) || 161 !Validate::isUnsignedId($id_lang)) { 162 return false; 163 } 164 $validate = (bool) Configuration::get('PRODUCT_COMMENTS_MODERATE'); 165 166 return Db::getInstance((bool) _PS_USE_SQL_SLAVE_)->executeS(' 167 SELECT pc.`id_product_comment`, pcg.`grade`, pccl.`name`, pcc.`id_product_comment_criterion` 168 FROM `' . _DB_PREFIX_ . 'product_comment` pc 169 LEFT JOIN `' . _DB_PREFIX_ . 'product_comment_grade` pcg ON (pcg.`id_product_comment` = pc.`id_product_comment`) 170 LEFT JOIN `' . _DB_PREFIX_ . 'product_comment_criterion` pcc ON (pcc.`id_product_comment_criterion` = pcg.`id_product_comment_criterion`) 171 LEFT JOIN `' . _DB_PREFIX_ . 'product_comment_criterion_lang` pccl ON (pccl.`id_product_comment_criterion` = pcg.`id_product_comment_criterion`) 172 WHERE pc.`id_product` = ' . (int) $id_product . ' 173 AND pccl.`id_lang` = ' . (int) $id_lang . 174 ($validate ? ' AND pc.`validate` = 1' : '')); 175 } 176 177 public static function getRatings($id_product) 178 { 179 $validate = Configuration::get('PRODUCT_COMMENTS_MODERATE'); 180 181 $sql = 'SELECT (SUM(pc.`grade`) / COUNT(pc.`grade`)) AS avg, 182 MIN(pc.`grade`) AS min, 183 MAX(pc.`grade`) AS max 184 FROM `' . _DB_PREFIX_ . 'product_comment` pc 185 WHERE pc.`id_product` = ' . (int) $id_product . ' 186 AND pc.`deleted` = 0' . 187 ($validate == '1' ? ' AND pc.`validate` = 1' : ''); 188 189 return Db::getInstance((bool) _PS_USE_SQL_SLAVE_)->getRow($sql); 190 } 191 192 public static function getAverageGrade($id_product) 193 { 194 $validate = Configuration::get('PRODUCT_COMMENTS_MODERATE'); 195 196 return Db::getInstance((bool) _PS_USE_SQL_SLAVE_)->getRow(' 197 SELECT (SUM(pc.`grade`) / COUNT(pc.`grade`)) AS grade 198 FROM `' . _DB_PREFIX_ . 'product_comment` pc 199 WHERE pc.`id_product` = ' . (int) $id_product . ' 200 AND pc.`deleted` = 0' . 201 ($validate == '1' ? ' AND pc.`validate` = 1' : '')); 202 } 203 204 public static function getAveragesByProduct($id_product, $id_lang) 205 { 206 /* Get all grades */ 207 $grades = ProductComment::getGradeByProduct((int) $id_product, (int) $id_lang); 208 $total = ProductComment::getGradedCommentNumber((int) $id_product); 209 if (!count($grades) || !$total) { 210 return []; 211 } 212 213 /* Addition grades for each criterion */ 214 $criterionsGradeTotal = []; 215 $count_grades = count($grades); 216 for ($i = 0; $i < $count_grades; ++$i) { 217 if (array_key_exists($grades[$i]['id_product_comment_criterion'], $criterionsGradeTotal) === false) { 218 $criterionsGradeTotal[$grades[$i]['id_product_comment_criterion']] = (int) ($grades[$i]['grade']); 219 } else { 220 $criterionsGradeTotal[$grades[$i]['id_product_comment_criterion']] += (int) ($grades[$i]['grade']); 221 } 222 } 223 224 /* Finally compute the averages */ 225 $averages = []; 226 foreach ($criterionsGradeTotal as $key => $criterionGradeTotal) { 227 $averages[(int) $key] = $criterionGradeTotal / $total; 228 } 229 230 return $averages; 231 } 232 233 /** 234 * Return number of comments and average grade by products 235 * 236 * @return array|false 237 */ 238 public static function getCommentNumber($id_product) 239 { 240 if (!Validate::isUnsignedId($id_product)) { 241 return false; 242 } 243 $validate = (bool) Configuration::get('PRODUCT_COMMENTS_MODERATE'); 244 $cache_id = 'ProductComment::getCommentNumber_' . (int) $id_product . '-' . $validate; 245 if (!Cache::isStored($cache_id)) { 246 $result = (int) Db::getInstance((bool) _PS_USE_SQL_SLAVE_)->getValue(' 247 SELECT COUNT(`id_product_comment`) AS "nbr" 248 FROM `' . _DB_PREFIX_ . 'product_comment` pc 249 WHERE `id_product` = ' . (int) ($id_product) . ($validate ? ' AND `validate` = 1' : '')); 250 Cache::store($cache_id, $result); 251 } 252 253 return Cache::retrieve($cache_id); 254 } 255 256 /** 257 * Return number of comments and average grade by products 258 * 259 * @return int|bool 260 */ 261 public static function getGradedCommentNumber($id_product) 262 { 263 if (!Validate::isUnsignedId($id_product)) { 264 return false; 265 } 266 $validate = (int) Configuration::get('PRODUCT_COMMENTS_MODERATE'); 267 268 $result = Db::getInstance((bool) _PS_USE_SQL_SLAVE_)->getRow(' 269 SELECT COUNT(pc.`id_product`) AS nbr 270 FROM `' . _DB_PREFIX_ . 'product_comment` pc 271 WHERE `id_product` = ' . (int) ($id_product) . ($validate == '1' ? ' AND `validate` = 1' : '') . ' 272 AND `grade` > 0'); 273 274 return (int) ($result['nbr']); 275 } 276 277 /** 278 * Get comments by Validation 279 * 280 * @return array Comments 281 */ 282 public static function getByValidate($validate = '0', $deleted = false, $p = null, $limit = null, $skip_validate = false) 283 { 284 $sql = ' 285 SELECT pc.`id_product_comment`, pc.`id_product`, IF(c.id_customer, CONCAT(c.`firstname`, \' \', c.`lastname`), pc.customer_name) customer_name, pc.`title`, pc.`content`, pc.`grade`, pc.`date_add`, pl.`name` 286 FROM `' . _DB_PREFIX_ . 'product_comment` pc 287 LEFT JOIN `' . _DB_PREFIX_ . 'customer` c ON (c.`id_customer` = pc.`id_customer`) 288 LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (pl.`id_product` = pc.`id_product` AND pl.`id_lang` = ' . (int) Context::getContext()->language->id . Shop::addSqlRestrictionOnLang('pl') . ')'; 289 290 if (!$skip_validate) { 291 $sql .= ' WHERE pc.`validate` = ' . (int) $validate; 292 } 293 294 $sql .= ' ORDER BY pc.`date_add` DESC'; 295 296 if ($p && $limit) { 297 $offset = ($p - 1) * $limit; 298 $sql .= ' LIMIT ' . (int) $offset . ',' . (int) $limit; 299 } 300 301 return Db::getInstance()->executeS($sql); 302 } 303 304 /** 305 * Get numbers of comments by Validation 306 * 307 * @return int Count of comments 308 */ 309 public static function getCountByValidate($validate = '0', $skip_validate = false) 310 { 311 $sql = ' 312 SELECT COUNT(*) 313 FROM `' . _DB_PREFIX_ . 'product_comment`'; 314 315 if (!$skip_validate) { 316 $sql .= ' WHERE `validate` = ' . (int) $validate; 317 } 318 319 return (int) Db::getInstance()->getValue($sql); 320 } 321 322 /** 323 * Get all comments 324 * 325 * @return array Comments 326 */ 327 public static function getAll() 328 { 329 return Db::getInstance()->executeS(' 330 SELECT pc.`id_product_comment`, pc.`id_product`, IF(c.id_customer, CONCAT(c.`firstname`, \' \', c.`lastname`), pc.customer_name) customer_name, pc.`content`, pc.`grade`, pc.`date_add`, pl.`name` 331 FROM `' . _DB_PREFIX_ . 'product_comment` pc 332 LEFT JOIN `' . _DB_PREFIX_ . 'customer` c ON (c.`id_customer` = pc.`id_customer`) 333 LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (pl.`id_product` = pc.`id_product` AND pl.`id_lang` = ' . (int) Context::getContext()->language->id . Shop::addSqlRestrictionOnLang('pl') . ') 334 ORDER BY pc.`date_add` DESC'); 335 } 336 337 /** 338 * Validate a comment 339 * 340 * @return bool succeed 341 */ 342 public function validate($validate = '1') 343 { 344 if (!Validate::isUnsignedId($this->id)) { 345 return false; 346 } 347 348 $success = (Db::getInstance()->execute(' 349 UPDATE `' . _DB_PREFIX_ . 'product_comment` SET 350 `validate` = ' . (int) $validate . ' 351 WHERE `id_product_comment` = ' . (int) $this->id)); 352 353 Hook::exec('actionObjectProductCommentValidateAfter', ['object' => $this]); 354 355 return $success; 356 } 357 358 /** 359 * Delete a comment, grade and report data 360 * 361 * @return bool succeed 362 */ 363 public function delete() 364 { 365 return parent::delete() 366 && ProductComment::deleteGrades($this->id) 367 && ProductComment::deleteReports($this->id) 368 && ProductComment::deleteUsefulness($this->id); 369 } 370 371 /** 372 * Delete Grades 373 * 374 * @return bool succeed 375 */ 376 public static function deleteGrades($id_product_comment) 377 { 378 if (!Validate::isUnsignedId($id_product_comment)) { 379 return false; 380 } 381 382 return Db::getInstance()->execute(' 383 DELETE FROM `' . _DB_PREFIX_ . 'product_comment_grade` 384 WHERE `id_product_comment` = ' . (int) $id_product_comment); 385 } 386 387 /** 388 * Delete Reports 389 * 390 * @return bool succeed 391 */ 392 public static function deleteReports($id_product_comment) 393 { 394 if (!Validate::isUnsignedId($id_product_comment)) { 395 return false; 396 } 397 398 return Db::getInstance()->execute(' 399 DELETE FROM `' . _DB_PREFIX_ . 'product_comment_report` 400 WHERE `id_product_comment` = ' . (int) $id_product_comment); 401 } 402 403 /** 404 * Delete usefulness 405 * 406 * @return bool succeed 407 */ 408 public static function deleteUsefulness($id_product_comment) 409 { 410 if (!Validate::isUnsignedId($id_product_comment)) { 411 return false; 412 } 413 414 return Db::getInstance()->execute(' 415 DELETE FROM `' . _DB_PREFIX_ . 'product_comment_usefulness` 416 WHERE `id_product_comment` = ' . (int) $id_product_comment); 417 } 418 419 /** 420 * Report comment 421 * 422 * @return bool 423 */ 424 public static function reportComment($id_product_comment, $id_customer) 425 { 426 return Db::getInstance()->execute(' 427 INSERT INTO `' . _DB_PREFIX_ . 'product_comment_report` (`id_product_comment`, `id_customer`) 428 VALUES (' . (int) $id_product_comment . ', ' . (int) $id_customer . ')'); 429 } 430 431 /** 432 * Comment already report 433 * 434 * @return bool 435 */ 436 public static function isAlreadyReport($id_product_comment, $id_customer) 437 { 438 return (bool) Db::getInstance()->getValue(' 439 SELECT COUNT(*) 440 FROM `' . _DB_PREFIX_ . 'product_comment_report` 441 WHERE `id_customer` = ' . (int) $id_customer . ' 442 AND `id_product_comment` = ' . (int) $id_product_comment); 443 } 444 445 /** 446 * Set comment usefulness 447 * 448 * @return bool 449 */ 450 public static function setCommentUsefulness($id_product_comment, $usefulness, $id_customer) 451 { 452 return Db::getInstance()->execute(' 453 INSERT INTO `' . _DB_PREFIX_ . 'product_comment_usefulness` (`id_product_comment`, `usefulness`, `id_customer`) 454 VALUES (' . (int) $id_product_comment . ', ' . (int) $usefulness . ', ' . (int) $id_customer . ')'); 455 } 456 457 /** 458 * Usefulness already set 459 * 460 * @return bool 461 */ 462 public static function isAlreadyUsefulness($id_product_comment, $id_customer) 463 { 464 return (bool) Db::getInstance()->getValue(' 465 SELECT COUNT(*) 466 FROM `' . _DB_PREFIX_ . 'product_comment_usefulness` 467 WHERE `id_customer` = ' . (int) $id_customer . ' 468 AND `id_product_comment` = ' . (int) $id_product_comment); 469 } 470 471 /** 472 * Get reported comments 473 * 474 * @return array Comments 475 */ 476 public static function getReportedComments() 477 { 478 return Db::getInstance((bool) _PS_USE_SQL_SLAVE_)->executeS(' 479 SELECT DISTINCT(pc.`id_product_comment`), pc.`id_product`, IF(c.id_customer, CONCAT(c.`firstname`, \' \', c.`lastname`), pc.customer_name) customer_name, pc.`content`, pc.`grade`, pc.`date_add`, pl.`name`, pc.`title` 480 FROM `' . _DB_PREFIX_ . 'product_comment_report` pcr 481 LEFT JOIN `' . _DB_PREFIX_ . 'product_comment` pc 482 ON pcr.id_product_comment = pc.id_product_comment 483 LEFT JOIN `' . _DB_PREFIX_ . 'customer` c ON (c.`id_customer` = pc.`id_customer`) 484 LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (pl.`id_product` = pc.`id_product` AND pl.`id_lang` = ' . (int) Context::getContext()->language->id . ' AND pl.`id_lang` = ' . (int) Context::getContext()->language->id . Shop::addSqlRestrictionOnLang('pl') . ') 485 ORDER BY pc.`date_add` DESC'); 486 } 487} 488