/**
* Add an SQL JOIN in query between a table and its associated table in multishop
*
* @param string $table Table name (E.g. product, module, etc.)
* @param string $alias Alias of table
* @param bool $inner_join Use or not INNER JOIN
* @param string $on
* @return string
*/
public static function addSqlAssociation($table, $alias, $inner_join = true, $on = null, $force_not_default = false)
{
$table_alias = $table . '_shop';
if (strpos($table, '.') !== false) {
list($table_alias, $table) = explode('.', $table);
}
$asso_table = Shop::getAssoTable($table);
if ($asso_table === false || $asso_table['type'] != 'shop') {
return;
}
$sql = ($inner_join ? ' INNER' : ' LEFT') . ' JOIN ' . _DB_PREFIX_ . $table . '_shop ' . $table_alias . '
ON (' . $table_alias . '.id_' . $table . ' = ' . $alias . '.id_' . $table;
if ((int) self::$context_id_shop) {
$sql .= ' AND ' . $table_alias . '.id_shop = ' . (int) self::$context_id_shop;
} elseif (Shop::checkIdShopDefault($table) && !$force_not_default) {
$sql .= ' AND ' . $table_alias . '.id_shop = ' . $alias . '.id_shop_default';
} else {
$sql .= ' AND ' . $table_alias . '.id_shop IN (' . implode(', ', Shop::getContextListShopID()) . ')';
}
$sql .= ($on ? ' AND ' . $on : '') . ')';
return $sql;
}
/**
* Create a simple entity with all its data and lang data
* If a methode createEntity$entity exists, use it. Else if $classname is given, use it. Else do a simple insert in database.
*
* @param string $entity
* @param string $identifier
* @param string $classname
* @param array $data
* @param array $data_lang
*/
public function createEntity($entity, $identifier, $classname, array $data, array $data_lang = array())
{
$xml = $this->loadEntity($entity);
if ($classname) {
// Create entity with ObjectModel class
$object = new $classname();
$object->hydrate($data);
if ($data_lang) {
$object->hydrate($data_lang);
}
$object->add(true, isset($xml->fields['null']) ? true : false);
$entity_id = $object->id;
unset($object);
} else {
// Generate primary key manually
$primary = '';
$entity_id = 0;
if (!$xml->fields['primary']) {
$primary = 'id_' . $entity;
} elseif (strpos((string) $xml->fields['primary'], ',') === false) {
$primary = (string) $xml->fields['primary'];
}
unset($xml);
if ($primary) {
$entity_id = $this->generatePrimary($entity, $primary);
$data[$primary] = $entity_id;
}
// Store INSERT queries in order to optimize install with grouped inserts
$this->delayed_inserts[$entity][] = array_map('pSQL', $data);
if ($data_lang) {
$real_data_lang = array();
foreach ($data_lang as $field => $list) {
foreach ($list as $id_lang => $value) {
$real_data_lang[$id_lang][$field] = $value;
}
}
foreach ($real_data_lang as $id_lang => $insert_data_lang) {
$insert_data_lang['id_' . $entity] = $entity_id;
$insert_data_lang['id_lang'] = $id_lang;
$this->delayed_inserts[$entity . '_lang'][] = array_map('pSQL', $insert_data_lang);
}
// Store INSERT queries for _shop associations
$entity_asso = Shop::getAssoTable($entity);
if ($entity_asso !== false && $entity_asso['type'] == 'shop') {
$this->delayed_inserts[$entity . '_shop'][] = array('id_shop' => 1, 'id_' . $entity => $entity_id);
}
}
}
$this->storeId($entity, $identifier, $entity_id);
}
/**
* Get the current objects' list form the database
*
* @param int $id_lang Language used for display
* @param string $orderBy ORDER BY clause
* @param string $_orderWay Order way (ASC, DESC)
* @param int $start Offset in LIMIT clause
* @param int $limit Row count in LIMIT clause
*/
public function getList($id_lang, $orderBy = null, $orderWay = null, $start = 0, $limit = null, $id_lang_shop = false)
{
/* Manage default params values */
if (empty($limit)) {
$limit = !isset($this->context->cookie->{$this->table . '_pagination'}) ? $this->_pagination[1] : ($limit = $this->context->cookie->{$this->table . '_pagination'});
}
if (!Validate::isTableOrIdentifier($this->table)) {
$this->_errors[] = Tools::displayError('Table name is invalid:') . ' "' . $this->table . '"';
}
if (empty($orderBy)) {
$orderBy = $this->context->cookie->__get($this->table . 'Orderby') ? $this->context->cookie->__get($this->table . 'Orderby') : $this->_defaultOrderBy;
}
if (empty($orderWay)) {
$orderWay = $this->context->cookie->__get($this->table . 'Orderway') ? $this->context->cookie->__get($this->table . 'Orderway') : 'ASC';
}
$limit = (int) Tools::getValue('pagination', $limit);
$this->context->cookie->{$this->table . '_pagination'} = $limit;
/* Check params validity */
if (!Validate::isOrderBy($orderBy) || !Validate::isOrderWay($orderWay) || !is_numeric($start) || !is_numeric($limit) || !Validate::isUnsignedId($id_lang)) {
die(Tools::displayError('get list params is not valid'));
}
/* Determine offset from current page */
if ((isset($_POST['submitFilter' . $this->table]) || isset($_POST['submitFilter' . $this->table . '_x']) || isset($_POST['submitFilter' . $this->table . '_y'])) && !empty($_POST['submitFilter' . $this->table]) && is_numeric($_POST['submitFilter' . $this->table])) {
$start = (int) ($_POST['submitFilter' . $this->table] - 1) * $limit;
}
/* Cache */
$this->_lang = (int) $id_lang;
$this->_orderBy = $orderBy;
$this->_orderWay = Tools::strtoupper($orderWay);
/* SQL table : orders, but class name is Order */
$sqlTable = $this->table == 'order' ? 'orders' : $this->table;
// Add SQL shop restriction
$selectShop = $joinShop = $whereShop = '';
if ($this->shopLinkType) {
$selectShop = ', shop.name as shop_name ';
$joinShop = ' LEFT JOIN ' . _DB_PREFIX_ . $this->shopLinkType . ' shop
ON a.id_' . $this->shopLinkType . ' = shop.id_' . $this->shopLinkType;
$whereShop = Shop::addSqlRestriction($this->shopShareDatas, 'a', $this->shopLinkType);
}
$asso = Shop::getAssoTable($this->table);
if ($asso !== false && $asso['type'] == 'shop') {
$filterKey = $asso['type'];
$idenfierShop = Shop::getContextListShopID();
}
$filterShop = '';
if (isset($filterKey)) {
if (!$this->_group) {
$this->_group = 'GROUP BY a.' . pSQL($this->identifier);
} elseif (!preg_match('#(\\s|,)\\s*a\\.`?' . pSQL($this->identifier) . '`?(\\s|,|$)#', $this->_group)) {
$this->_group .= ', a.' . pSQL($this->identifier);
}
if (Shop::isFeatureActive() && Shop::getContext() != Shop::CONTEXT_ALL && !preg_match('#`?' . preg_quote(_DB_PREFIX_ . $this->table . '_' . $filterKey) . '`? *sa#', $this->_join)) {
$filterShop = 'JOIN `' . _DB_PREFIX_ . $this->table . '_' . $filterKey . '` sa ON (sa.' . $this->identifier . ' = a.' . $this->identifier . ' AND sa.id_' . $filterKey . ' IN (' . implode(', ', $idenfierShop) . '))';
}
}
///////////////////////
/* Query in order to get results with all fields */
$sql = 'SELECT SQL_CALC_FOUND_ROWS
' . ($this->_tmpTableFilter ? ' * FROM (SELECT ' : '') . '
' . ($this->lang ? 'b.*, ' : '') . 'a.*' . (isset($this->_select) ? ', ' . $this->_select . ' ' : '') . $selectShop . '
FROM `' . _DB_PREFIX_ . $sqlTable . '` a
' . $filterShop . '
' . ($this->lang ? 'LEFT JOIN `' . _DB_PREFIX_ . $this->table . '_lang` b ON (b.`' . $this->identifier . '` = a.`' . $this->identifier . '` AND b.`id_lang` = ' . (int) $id_lang . ($id_lang_shop ? ' AND b.`id_shop`=' . (int) $id_lang_shop : '') . ')' : '') . '
' . (isset($this->_join) ? $this->_join . ' ' : '') . '
' . $joinShop . '
WHERE 1 ' . (isset($this->_where) ? $this->_where . ' ' : '') . ($this->deleted ? 'AND a.`deleted` = 0 ' : '') . (isset($this->_filter) ? $this->_filter : '') . $whereShop . '
' . (isset($this->_group) ? $this->_group . ' ' : '') . '
' . (isset($this->_filterHaving) || isset($this->_having) ? 'HAVING ' : '') . (isset($this->_filterHaving) ? ltrim($this->_filterHaving, ' AND ') : '') . (isset($this->_having) ? $this->_having . ' ' : '') . '
ORDER BY ' . ($orderBy == $this->identifier ? 'a.' : '') . '`' . pSQL($orderBy) . '` ' . pSQL($orderWay) . ($this->_tmpTableFilter ? ') tmpTable WHERE 1' . $this->_tmpTableFilter : '') . '
LIMIT ' . (int) $start . ',' . (int) $limit;
$this->_list = Db::getInstance()->executeS($sql);
$this->_listTotal = Db::getInstance()->getValue('SELECT FOUND_ROWS() as `' . _DB_PREFIX_ . $this->table . '`');
}
/**
* save Entity Object from XML
*
* @param int $successReturnCode
* @return boolean
*/
protected function saveEntityFromXml($successReturnCode)
{
try {
$xml = new SimpleXMLElement($this->_inputXml);
} catch (Exception $error) {
$this->setError(500, 'XML error : ' . $error->getMessage() . "\n" . 'XML length : ' . strlen($this->_inputXml) . "\n" . 'Original XML : ' . $this->_inputXml, 127);
return;
}
$xmlEntities = $xml->children();
$object = null;
$ids = array();
foreach ($xmlEntities as $entity) {
// To cast in string allow to check null values
if ((string) $entity->id != '') {
$ids[] = (int) $entity->id;
}
}
if ($this->method == 'PUT') {
$ids2 = array();
$ids2 = array_unique($ids);
if (count($ids2) != count($ids)) {
$this->setError(400, 'id is duplicate in request', 89);
return false;
}
if (count($xmlEntities) != count($ids)) {
$this->setError(400, 'id is required when modifying a resource', 90);
return false;
}
} elseif ($this->method == 'POST' && count($ids) > 0) {
$this->setError(400, 'id is forbidden when adding a new resource', 91);
return false;
}
foreach ($xmlEntities as $xmlEntity) {
$attributes = $xmlEntity->children();
if ($this->method == 'POST') {
$object = new $this->resourceConfiguration['retrieveData']['className']();
} elseif ($this->method == 'PUT') {
$object = new $this->resourceConfiguration['retrieveData']['className']((int) $attributes->id);
if (!$object->id) {
$this->setError(404, 'Invalid ID', 92);
return false;
}
}
$this->objects[] = $object;
$i18n = false;
// attributes
foreach ($this->resourceConfiguration['fields'] as $fieldName => $fieldProperties) {
$sqlId = $fieldProperties['sqlId'];
if ($fieldName == 'id') {
$sqlId = $fieldName;
}
if (isset($attributes->{$fieldName}) && isset($fieldProperties['sqlId']) && (!isset($fieldProperties['i18n']) || !$fieldProperties['i18n'])) {
if (isset($fieldProperties['setter'])) {
// if we have to use a specific setter
if (!$fieldProperties['setter']) {
// if it's forbidden to set this field
$this->setError(400, 'parameter "' . $fieldName . '" not writable. Please remove this attribute of this XML', 93);
return false;
} else {
$object->{$fieldProperties}['setter']((string) $attributes->{$fieldName});
}
} elseif (property_exists($object, $sqlId)) {
$object->{$sqlId} = (string) $attributes->{$fieldName};
} else {
$this->setError(400, 'Parameter "' . $fieldName . '" can\'t be set to the object "' . $this->resourceConfiguration['retrieveData']['className'] . '"', 123);
}
} elseif (isset($fieldProperties['required']) && $fieldProperties['required'] && !$fieldProperties['i18n']) {
$this->setError(400, 'parameter "' . $fieldName . '" required', 41);
return false;
} elseif ((!isset($fieldProperties['required']) || !$fieldProperties['required']) && property_exists($object, $sqlId)) {
$object->{$sqlId} = null;
}
if (isset($fieldProperties['i18n']) && $fieldProperties['i18n']) {
$i18n = true;
if (isset($attributes->{$fieldName}, $attributes->{$fieldName}->language)) {
foreach ($attributes->{$fieldName}->language as $lang) {
$object->{$fieldName}[(int) $lang->attributes()->id] = (string) $lang;
}
} else {
$object->{$fieldName} = (string) $attributes->{$fieldName};
}
}
}
// Apply the modifiers if they exist
foreach ($this->resourceConfiguration['fields'] as $fieldName => $fieldProperties) {
if (isset($fieldProperties['modifier']) && isset($fieldProperties['modifier']['modifier']) && $fieldProperties['modifier']['http_method'] & constant('WebserviceRequest::HTTP_' . $this->method)) {
$object->{$fieldProperties['modifier']['modifier']}();
}
}
if (!$this->hasErrors()) {
if ($i18n && ($retValidateFieldsLang = $object->validateFieldsLang(false, true)) !== true) {
$this->setError(400, 'Validation error: "' . $retValidateFieldsLang . '"', 84);
return false;
} elseif (($retValidateFields = $object->validateFields(false, true)) !== true) {
$this->setError(400, 'Validation error: "' . $retValidateFields . '"', 85);
return false;
} else {
// Call alternative method for add/update
$objectMethod = $this->method == 'POST' ? 'add' : 'update';
if (isset($this->resourceConfiguration['objectMethods']) && array_key_exists($objectMethod, $this->resourceConfiguration['objectMethods'])) {
$objectMethod = $this->resourceConfiguration['objectMethods'][$objectMethod];
}
$result = $object->{$objectMethod}();
if ($result) {
if (isset($attributes->associations)) {
foreach ($attributes->associations->children() as $association) {
// associations
if (isset($this->resourceConfiguration['associations'][$association->getName()])) {
$assocItems = $association->children();
$values = array();
foreach ($assocItems as $assocItem) {
$fields = $assocItem->children();
$entry = array();
foreach ($fields as $fieldName => $fieldValue) {
$entry[$fieldName] = (string) $fieldValue;
}
$values[] = $entry;
}
$setter = $this->resourceConfiguration['associations'][$association->getName()]['setter'];
if (!is_null($setter) && $setter && method_exists($object, $setter) && !$object->{$setter}($values)) {
$this->setError(500, 'Error occurred while setting the ' . $association->getName() . ' value', 85);
return false;
}
} elseif ($association->getName() != 'i18n') {
$this->setError(400, 'The association "' . $association->getName() . '" does not exists', 86);
return false;
}
}
}
$assoc = Shop::getAssoTable($this->resourceConfiguration['retrieveData']['table']);
if ($assoc !== false && $assoc['type'] != 'fk_shop') {
// PUT nor POST is destructive, no deletion
$sql = 'INSERT IGNORE INTO `' . bqSQL(_DB_PREFIX_ . $this->resourceConfiguration['retrieveData']['table'] . '_' . $assoc['type']) . '` (id_shop, ' . pSQL($this->resourceConfiguration['fields']['id']['sqlId']) . ') VALUES ';
foreach (self::$shopIDs as $id) {
$sql .= '(' . (int) $id . ',' . (int) $object->id . ')';
if ($id != end(self::$shopIDs)) {
$sql .= ', ';
}
}
Db::getInstance()->execute($sql);
}
} else {
$this->setError(500, 'Unable to save resource', 46);
}
}
}
}
if (!$this->hasErrors()) {
$this->objOutput->setStatus($successReturnCode);
return true;
}
}
/**
* Returns webservice object list.
*
* @param string $sql_join
* @param string $sql_filter
* @param string $sql_sort
* @param string $sql_limit
*
* @return array|null
* @throws PrestaShopDatabaseException
*/
public function getWebserviceObjectList($sql_join, $sql_filter, $sql_sort, $sql_limit)
{
$assoc = Shop::getAssoTable($this->def['table']);
$class_name = WebserviceRequest::$ws_current_classname;
$vars = get_class_vars($class_name);
if ($assoc !== false) {
if ($assoc['type'] !== 'fk_shop') {
$multi_shop_join = ' LEFT JOIN `' . _DB_PREFIX_ . bqSQL($this->def['table']) . '_' . bqSQL($assoc['type']) . '`
AS `multi_shop_' . bqSQL($this->def['table']) . '`
ON (main.`' . bqSQL($this->def['primary']) . '` = `multi_shop_' . bqSQL($this->def['table']) . '`.`' . bqSQL($this->def['primary']) . '`)';
$sql_filter = 'AND `multi_shop_' . bqSQL($this->def['table']) . '`.id_shop = ' . Context::getContext()->shop->id . ' ' . $sql_filter;
$sql_join = $multi_shop_join . ' ' . $sql_join;
} else {
$vars = get_class_vars($class_name);
foreach ($vars['shopIDs'] as $id_shop) {
$or[] = '(main.id_shop = ' . (int) $id_shop . (isset($this->def['fields']['id_shop_group']) ? ' OR (id_shop = 0 AND id_shop_group=' . (int) Shop::getGroupFromShop((int) $id_shop) . ')' : '') . ')';
}
$prepend = '';
if (count($or)) {
$prepend = 'AND (' . implode('OR', $or) . ')';
}
$sql_filter = $prepend . ' ' . $sql_filter;
}
}
$query = '
SELECT DISTINCT main.`' . bqSQL($this->def['primary']) . '` FROM `' . _DB_PREFIX_ . bqSQL($this->def['table']) . '` AS main
' . $sql_join . '
WHERE 1 ' . $sql_filter . '
' . ($sql_sort != '' ? $sql_sort : '') . '
' . ($sql_limit != '' ? $sql_limit : '');
return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
}
