1<?php 2/** 3 * 2007-2016 PrestaShop 4 * 5 * thirty bees is an extension to the PrestaShop e-commerce software developed by PrestaShop SA 6 * Copyright (C) 2017-2018 thirty bees 7 * 8 * NOTICE OF LICENSE 9 * 10 * This source file is subject to the Open Software License (OSL 3.0) 11 * that is bundled with this package in the file LICENSE.txt. 12 * It is also available through the world-wide-web at this URL: 13 * http://opensource.org/licenses/osl-3.0.php 14 * If you did not receive a copy of the license and are unable to 15 * obtain it through the world-wide-web, please send an email 16 * to license@thirtybees.com so we can send you a copy immediately. 17 * 18 * DISCLAIMER 19 * 20 * Do not edit or add to this file if you wish to upgrade PrestaShop to newer 21 * versions in the future. If you wish to customize PrestaShop for your 22 * needs please refer to https://www.thirtybees.com for more information. 23 * 24 * @author thirty bees <contact@thirtybees.com> 25 * @author PrestaShop SA <contact@prestashop.com> 26 * @copyright 2017-2018 thirty bees 27 * @copyright 2007-2016 PrestaShop SA 28 * @license http://opensource.org/licenses/osl-3.0.php Open Software License (OSL 3.0) 29 * PrestaShop is an internationally registered trademark & property of PrestaShop SA 30 */ 31 32/** 33 * Class CustomerThreadCore 34 * 35 * @since 1.0.0 36 */ 37class CustomerThreadCore extends ObjectModel 38{ 39 // @codingStandardsIgnoreStart 40 /** @var int $id_contact */ 41 public $id_contact; 42 /** @var int $id_customer */ 43 public $id_customer; 44 /** @var int $id_order */ 45 public $id_order; 46 /** @var int $id_product */ 47 public $id_product; 48 /** @var bool $status */ 49 public $status; 50 /** @var string $email */ 51 public $email; 52 /** @var string $token */ 53 public $token; 54 /** @var string $date_add */ 55 public $date_add; 56 /** @var string $date_upd */ 57 public $date_upd; 58 // @codingStandardsIgnoreEnd 59 60 /** 61 * @see ObjectModel::$definition 62 */ 63 public static $definition = [ 64 'table' => 'customer_thread', 65 'primary' => 'id_customer_thread', 66 'fields' => [ 67 'id_lang' => ['type' => self::TYPE_INT, 'validate' => 'isUnsignedId', 'required' => true], 68 'id_contact' => ['type' => self::TYPE_INT, 'validate' => 'isUnsignedId', 'required' => true], 69 'id_shop' => ['type' => self::TYPE_INT, 'validate' => 'isUnsignedId'], 70 'id_customer' => ['type' => self::TYPE_INT, 'validate' => 'isUnsignedId'], 71 'id_order' => ['type' => self::TYPE_INT, 'validate' => 'isUnsignedId'], 72 'id_product' => ['type' => self::TYPE_INT, 'validate' => 'isUnsignedId'], 73 'email' => ['type' => self::TYPE_STRING, 'validate' => 'isEmail', 'size' => 254], 74 'token' => ['type' => self::TYPE_STRING, 'validate' => 'isGenericName', 'required' => true], 75 'status' => ['type' => self::TYPE_STRING], 76 'date_add' => ['type' => self::TYPE_DATE, 'validate' => 'isDate'], 77 'date_upd' => ['type' => self::TYPE_DATE, 'validate' => 'isDate'], 78 ], 79 ]; 80 protected $webserviceParameters = [ 81 'fields' => [ 82 'id_lang' => [ 83 'xlink_resource' => 'languages', 84 ], 85 'id_shop' => [ 86 'xlink_resource' => 'shops', 87 ], 88 'id_customer' => [ 89 'xlink_resource' => 'customers', 90 ], 91 'id_order' => [ 92 'xlink_resource' => 'orders', 93 ], 94 'id_product' => [ 95 'xlink_resource' => 'products', 96 ], 97 ], 98 'associations' => [ 99 'customer_messages' => [ 100 'resource' => 'customer_message', 101 'id' => ['required' => true], 102 ], 103 ], 104 ]; 105 106 /** 107 * @param int $idCustomer 108 * @param int|null $read 109 * @param int|null $idOrder 110 * 111 * @return array|false|mysqli_result|null|PDOStatement|resource 112 * 113 * @throws PrestaShopDatabaseException 114 * @throws PrestaShopException 115 * @since 1.0.0 116 * @version 1.0.0 Initial version 117 */ 118 public static function getCustomerMessages($idCustomer, $read = null, $idOrder = null) 119 { 120 $sql = (new DbQuery()) 121 ->select('*') 122 ->from('customer_thread', 'ct') 123 ->leftJoin('customer_message', 'cm', 'ct.`id_customer_thread` = cm.`id_customer_thread`') 124 ->where('`id_customer` = '.(int) $idCustomer); 125 126 if ($read !== null) { 127 $sql->where('cm.`read` = '.(int) $read); 128 } 129 if ($idOrder !== null) { 130 $sql->where('ct.`id_order` = '.(int) $idOrder); 131 } 132 133 return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql); 134 } 135 136 /** 137 * @param string $email 138 * @param int $idOrder 139 * 140 * @return false|null|string 141 * 142 * @throws PrestaShopException 143 * @since 1.0.0 144 * @version 1.0.0 Initial version 145 */ 146 public static function getIdCustomerThreadByEmailAndIdOrder($email, $idOrder) 147 { 148 return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue( 149 (new DbQuery()) 150 ->select('cm.`id_customer_thread`') 151 ->from('customer_thread', 'cm') 152 ->where('cm.`email` = \''.pSQL($email).'\'') 153 ->where('cm.`id_shop` = '.(int) Context::getContext()->shop->id) 154 ->where('cm.`id_order` = '.(int) $idOrder) 155 ); 156 } 157 158 /** 159 * @return array|false|mysqli_result|null|PDOStatement|resource 160 * 161 * @throws PrestaShopDatabaseException 162 * @throws PrestaShopException 163 * @since 1.0.0 164 * @version 1.0.0 Initial version 165 */ 166 public static function getContacts() 167 { 168 return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS( 169 (new DbQuery()) 170 ->select('cl.*, COUNT(*) as `total`') 171 ->select('(SELECT `id_customer_thread` FROM `'._DB_PREFIX_.'customer_thread` ct2 WHERE status = "open" AND ct.`id_contact` = ct2.`id_contact` '.Shop::addSqlRestriction().' ORDER BY `date_upd` ASC LIMIT 1) AS `id_customer_thread`') 172 ->from('customer_thread', 'ct') 173 ->leftJoin('contact_lang', 'cl', 'cl.`id_contact` = ct.`id_contact` AND cl.`id_lang` = '.(int) Context::getContext()->language->id) 174 ->where('ct.`status` = "open"') 175 ->where('ct.`id_contact` IS NOT NULL') 176 ->where('cl.`id_contact` IS NOT NULL '.Shop::addSqlRestriction()) 177 ->groupBy('ct.`id_contact`') 178 ->having('COUNT(*) > 0') 179 ); 180 } 181 182 /** 183 * @param string|null $where 184 * 185 * @return int 186 * 187 * @since 1.0.0 188 * @version 1.0.0 Initial version 189 * @throws PrestaShopException 190 */ 191 public static function getTotalCustomerThreads($where = null) 192 { 193 return (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue( 194 (new DbQuery()) 195 ->select('COUNT(*)') 196 ->from('customer_thread') 197 ->where(($where ?: '1').' '.Shop::addSqlRestriction()) 198 ); 199 } 200 201 /** 202 * @param int $idCustomerThread 203 * 204 * @return array|false|mysqli_result|null|PDOStatement|resource 205 * 206 * @since 1.0.0 207 * @version 1.0.0 Initial version 208 * @throws PrestaShopException 209 */ 210 public static function getMessageCustomerThreads($idCustomerThread) 211 { 212 return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS( 213 (new DbQuery()) 214 ->select('ct.*, cm.*, cl.name subject, CONCAT(e.firstname, \' \', e.lastname) employee_name') 215 ->select('CONCAT(c.firstname, \' \', c.lastname) customer_name, c.firstname') 216 ->from('customer_thread', 'ct') 217 ->leftJoin('customer_message', 'cm', 'ct.`id_customer_thread` = cm.`id_customer_thread`') 218 ->leftJoin('contact_lang', 'cl', 'cl.`id_contact` = ct.`id_contact` AND cl.`id_lang` = '.(int) Context::getContext()->language->id) 219 ->leftJoin('employee', 'e', 'e.`id_employee` = cm.`id_employee`') 220 ->leftJoin('customer', 'c', '(IFNULL(ct.`id_customer`, ct.`email`) = IFNULL(c.`id_customer`, c.`email`))') 221 ->where('ct.`id_customer_thread` = '.(int) $idCustomerThread) 222 ->orderBy('cm.`date_add` ASC') 223 ); 224 } 225 226 /** 227 * @param int $idCustomerThread 228 * 229 * @return false|null|string 230 * 231 * @since 1.0.0 232 * @version 1.0.0 Initial version 233 * @throws PrestaShopException 234 */ 235 public static function getNextThread($idCustomerThread) 236 { 237 $context = Context::getContext(); 238 239 return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue( 240 (new DbQuery()) 241 ->select('`id_customer_thread`') 242 ->from('customer_thread', 'ct') 243 ->where('ct.status = "open"') 244 ->where('ct.`date_upd` = (SELECT date_add FROM '._DB_PREFIX_.'customer_message WHERE (id_employee IS NULL OR id_employee = 0) AND id_customer_thread = '.(int) $idCustomerThread.' ORDER BY date_add DESC LIMIT 1)') 245 ->where($context->cookie->{'customer_threadFilter_cl!id_contact'} ? 'ct.`id_contact` = '.(int) $context->cookie->{'customer_threadFilter_cl!id_contact'} : '') 246 ->where($context->cookie->{'customer_threadFilter_l!id_lang'} ? 'ct.`id_lang` = '.(int) $context->cookie->{'customer_threadFilter_l!id_lang'} : '') 247 ->orderBy('ct.`date_upd` ASC') 248 ); 249 } 250 251 /** 252 * @return array|false|mysqli_result|null|PDOStatement|resource 253 * 254 * @throws PrestaShopDatabaseException 255 * @throws PrestaShopException 256 * @since 1.0.0 257 * @version 1.0.0 Initial version 258 */ 259 public function getWsCustomerMessages() 260 { 261 return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS( 262 (new DbQuery()) 263 ->select('`id_customer_message` AS `id`') 264 ->from('customer_message') 265 ->where('`id_customer_thread` = '.(int) $this->id) 266 ); 267 } 268 269 /** 270 * @return bool 271 * 272 * @throws PrestaShopDatabaseException 273 * @throws PrestaShopException 274 * @since 1.0.0 275 * @version 1.0.0 Initial version 276 */ 277 public function delete() 278 { 279 if (!Validate::isUnsignedId($this->id)) { 280 return false; 281 } 282 283 $return = true; 284 $result = Db::getInstance()->executeS( 285 (new DbQuery()) 286 ->select('`id_customer_message`') 287 ->from('customer_message') 288 ->where('`id_customer_thread` = '.(int) $this->id) 289 ); 290 291 if (count($result)) { 292 foreach ($result as $res) { 293 $message = new CustomerMessage((int) $res['id_customer_message']); 294 if (!Validate::isLoadedObject($message)) { 295 $return = false; 296 } else { 297 $return &= $message->delete(); 298 } 299 } 300 } 301 $return &= parent::delete(); 302 303 return $return; 304 } 305} 306