/** * Gets reference, ean13 and upc of the current product * Stores it in stock for stock_mvt integrity and history purposes */ protected function getProductInformations() { // if combinations if ((int) $this->id_product_attribute > 0) { $query = new DbQuery(); $query->select('reference, ean13, upc'); $query->from('product_attribute'); $query->where('id_product = ' . (int) $this->id_product); $query->where('id_product_attribute = ' . (int) $this->id_product_attribute); $rows = Db::getInstance()->executeS($query); if (!is_array($rows)) { return; } foreach ($rows as $row) { $this->reference = $row['reference']; $this->ean13 = $row['ean13']; $this->upc = $row['upc']; } } else { // else, simple product $product = new Product((int) $this->id_product); if (Validate::isLoadedObject($product)) { $this->reference = $product->reference; $this->ean13 = $product->ean13; $this->upc = $product->upc; } } }
/** * Load ObjectModel * @param $id * @param $id_lang * @param $entity ObjectModel * @param $entity_defs * @param $id_shop * @param $should_cache_objects * @throws PrestaShopDatabaseException */ public function load($id, $id_lang, $entity, $entity_defs, $id_shop, $should_cache_objects) { // Load object from database if object id is present $cache_id = 'objectmodel_' . $entity_defs['classname'] . '_' . (int) $id . '_' . (int) $id_shop . '_' . (int) $id_lang; if (!$should_cache_objects || !Cache::isStored($cache_id)) { $sql = new DbQuery(); $sql->from($entity_defs['table'], 'a'); $sql->where('a.`' . bqSQL($entity_defs['primary']) . '` = ' . (int) $id); // Get lang informations if ($id_lang && isset($entity_defs['multilang']) && $entity_defs['multilang']) { $sql->leftJoin($entity_defs['table'] . '_lang', 'b', 'a.`' . bqSQL($entity_defs['primary']) . '` = b.`' . bqSQL($entity_defs['primary']) . '` AND b.`id_lang` = ' . (int) $id_lang); if ($id_shop && !empty($entity_defs['multilang_shop'])) { $sql->where('b.`id_shop` = ' . (int) $id_shop); } } // Get shop informations if (Shop::isTableAssociated($entity_defs['table'])) { $sql->leftJoin($entity_defs['table'] . '_shop', 'c', 'a.`' . bqSQL($entity_defs['primary']) . '` = c.`' . bqSQL($entity_defs['primary']) . '` AND c.`id_shop` = ' . (int) $id_shop); } if ($object_datas = Db::getInstance()->getRow($sql)) { if (!$id_lang && isset($entity_defs['multilang']) && $entity_defs['multilang']) { $sql = 'SELECT * FROM `' . bqSQL(_DB_PREFIX_ . $entity_defs['table']) . '_lang` WHERE `' . bqSQL($entity_defs['primary']) . '` = ' . (int) $id . ($id_shop && $entity->isLangMultishop() ? ' AND `id_shop` = ' . (int) $id_shop : ''); if ($object_datas_lang = Db::getInstance()->executeS($sql)) { foreach ($object_datas_lang as $row) { foreach ($row as $key => $value) { if ($key != $entity_defs['primary'] && array_key_exists($key, $entity)) { if (!isset($object_datas[$key]) || !is_array($object_datas[$key])) { $object_datas[$key] = array(); } $object_datas[$key][$row['id_lang']] = $value; } } } } } $entity->id = (int) $id; foreach ($object_datas as $key => $value) { if (array_key_exists($key, $entity)) { $entity->{$key} = $value; } else { unset($object_datas[$key]); } } if ($should_cache_objects) { Cache::store($cache_id, $object_datas); } } } else { $object_datas = Cache::retrieve($cache_id); if ($object_datas) { $entity->id = (int) $id; foreach ($object_datas as $key => $value) { $entity->{$key} = $value; } } } }
private static function getCombinationIdByIdCombination($id_product, $id_combination) { $datas = explode('-', $id_combination); if (empty($datas[1])) { return 0; } $query = new DbQuery(); $query->select('pa.id_product_attribute'); $query->from('product_attribute', 'pa'); $query->where('pa.id_product_attribute = \'' . pSQL($datas[1]) . '\''); $query->where('pa.id_product = ' . (int) $id_product); return Db::getInstance()->getValue($query); }
public function renderView() { $badges_feature = new Collection('badge', $this->context->language->id); $badges_feature->where('type', '=', 'feature'); $badges_feature->orderBy('id_group'); $badges_feature->orderBy('group_position'); $badges_achievement = new Collection('badge', $this->context->language->id); $badges_achievement->where('type', '=', 'achievement'); $badges_achievement->orderBy('id_group'); $badges_achievement->orderBy('group_position'); $badges_international = new Collection('badge', $this->context->language->id); $badges_international->where('type', '=', 'international'); $badges_international->orderBy('id_group'); $badges_international->orderBy('group_position'); $groups = array(); $query = new DbQuery(); $query->select('DISTINCT(b.`id_group`), bl.group_name, b.type'); $query->from('badge', 'b'); $query->join(' LEFT JOIN `' . _DB_PREFIX_ . 'badge_lang` bl ON bl.`id_badge` = b.`id_badge`'); $query->where('bl.id_lang = ' . (int) $this->context->language->id); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query); foreach ($result as $res) { $groups['badges_' . $res['type']][$res['id_group']] = $res['group_name']; } $badges_type = array('badges_feature' => array('name' => $this->l('Features'), 'badges' => $badges_feature), 'badges_achievement' => array('name' => $this->l('Achievements'), 'badges' => $badges_achievement), 'badges_international' => array('name' => $this->l('International'), 'badges' => $badges_international)); $levels = array(1 => $this->l('1. Beginner'), 2 => $this->l('2. Pro'), 3 => $this->l('3. Expert'), 4 => $this->l('4. Wizard'), 5 => $this->l('5. Guru'), 6 => $this->l('6. Legend')); $this->tpl_view_vars = array('badges_type' => $badges_type, 'current_level_percent' => (int) Configuration::get('GF_CURRENT_LEVEL_PERCENT'), 'current_level' => (int) Configuration::get('GF_CURRENT_LEVEL'), 'groups' => $groups, 'levels' => $levels); if (version_compare(_PS_VERSION_, '1.5.6.0', '>')) { $this->base_tpl_view = 'view_bt.tpl'; } return parent::renderView(); }
protected function getQuantitySold($id_product, $id_product_attribute, $coverage) { $query = new DbQuery(); $query->select('SUM(' . PP::sqlQty('product_quantity', 'od') . ')'); $query->from('order_detail', 'od'); $query->leftJoin('orders', 'o', 'od.id_order = o.id_order'); $query->leftJoin('order_history', 'oh', 'o.date_upd = oh.date_add'); $query->leftJoin('order_state', 'os', 'os.id_order_state = oh.id_order_state'); $query->where('od.product_id = ' . (int) $id_product); $query->where('od.product_attribute_id = ' . (int) $id_product_attribute); $query->where('TO_DAYS(NOW()) - TO_DAYS(oh.date_add) <= ' . (int) $coverage); $query->where('o.valid = 1'); $query->where('os.logable = 1 AND os.delivery = 1 AND os.shipped = 1'); $quantity = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($query); return $quantity; }
/** * Returns id_erpip_supply_order_receipt_history for a given id_supply_order_receipt_history * @param int $id_supply_order_receipt_history * @return int $id_erpip_supply_order_receipt_history */ public static function getErpAssociation($id_supply_order_receipt_history) { $query = new DbQuery(); $query->select('id_erpip_supply_order_receipt_history'); $query->from('erpip_supply_order_receipt_history'); $query->where('id_supply_order_receipt_history = ' . (int) $id_supply_order_receipt_history); return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($query); }
/** * Returns id_erpip_supplier for a given id_supplier * @param int $id_supplier * @return int $id_erpip_supplier */ public static function getErpSupplierIdBySupplierId($id_supplier) { $query = new DbQuery(); $query->select('id_erpip_supplier'); $query->from('erpip_supplier'); $query->where('id_supplier = ' . (int) $id_supplier); return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($query); }
public function getStockValue() { $query = new DbQuery(); $query->select('SUM(s.`price_te` * (s.`physical_quantity`+s.`physical_quantity_remainder`)'); $query->from('stock', 's'); $query->where('s.`id_warehouse` = ' . (int) $this->id); return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($query); }
public function getNextBadgeId() { $query = new DbQuery(); $query->select('b.`id_badge`'); $query->from('badge', 'b'); $query->where('b.id_group = \'' . pSQL($this->id_group) . '\' AND b.validated = 0'); $query->orderBy('b.group_position'); return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($query); }
/** * For a given id_stock_mvt_reason, tells if it exists * * @since 1.5.0 * @param int $id_stock_mvt_reason * @return bool */ public static function exists($id_stock_mvt_reason) { $query = new DbQuery(); $query->select('smr.id_stock_mvt_reason'); $query->from('stock_mvt_reason', 'smr'); $query->where('smr.id_stock_mvt_reason = ' . (int) $id_stock_mvt_reason); $query->where('smr.deleted = 0'); return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($query); }
private function getFieldsValues() { $sql = new DbQuery(); $sql->select('*'); $sql->from('expressmailing_fax'); $sql->where('campaign_id = ' . $this->campaign_id); $result = Db::getInstance()->getRow($sql); $this->campaign_api_message_id = $result['campaign_api_message_id']; return true; }
public static function getImageContentIdsByIdImage($id_image) { // build query $query = new DbQuery(); $query->select('id_stock_image_content'); $query->from('stock_image_content', 'sic'); $query->where('id_stock_image=' . (int) $id_image); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query); return $result; }
public static function getByProductId($id_product) { if (!Validate::isUnsignedInt($id_product)) { die(Tools::displayError()); } $sql = new DbQuery(); $sql->select('id_simpleblog_post, id_product'); $sql->from('simpleblog_related_post'); $sql->where('id_product = ' . $id_product); return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql); }
public static function getIdBySlug($slug) { if (!Validate::isLinkRewrite($slug)) { return Tools::displayError('getIdBySlug - invalid slug'); } $sql = new DbQuery(); $sql->select('id_simpleblog_post_type'); $sql->from('simpleblog_post_type', 'sbpt'); $sql->where('slug = \'' . $slug . '\''); return Db::getInstance()->getValue($sql); }
/** * For a given product and warehouse, gets the WarehouseProductLocation corresponding ID * * @param int $id_product * @param int $id_product_attribute * @param int $id_supplier * @return int $id_warehouse_product_location ID of the WarehouseProductLocation */ public static function getIdByProductAndWarehouse($id_product, $id_product_attribute, $id_warehouse) { // build query $query = new DbQuery(); $query->select('wpl.id_warehouse_product_location'); $query->from('warehouse_product_location', 'wpl'); $query->where('wpl.id_product = ' . (int) $id_product . ' AND wpl.id_product_attribute = ' . (int) $id_product_attribute . ' AND wpl.id_warehouse = ' . (int) $id_warehouse); return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($query); }
public static function getAllById($id_simpleblog_post) { if (!Validate::isUnsignedInt($id_simpleblog_post)) { return Tools::displayError('getAllById - invalid ID'); } $sql = new DbQuery(); $sql->select('*'); $sql->from('simpleblog_post_image', 'sbpi'); $sql->where('id_simpleblog_post = ' . (int) $id_simpleblog_post); $sql->orderBy('position ASC'); return Db::getInstance()->executeS($sql); }
/** * Retrieve my comments * @param array $params filters (column) * @return array * @throws Exception */ public function comments($params = array()) { $query = new DbQuery(); $query->select('*')->from('comment')->where('id_user = '******'date_add DESC'); if (isset($params['username'])) { $query->where('username = "******"'); } $results = Db::getInstance()->ExecuteS($query); if (!Validate::isNonEmptyArray($results)) { return array(); } return ObjectModel::hydrateCollection('Comment', $results); }
public static function getIdsByName($name) { $query = new DbQuery(); $tab = array(); $query->select('smr.id_stock_mvt_reason'); $query->from('stock_mvt_reason_lang', 'smr'); $query->where('smr.name = "' . pSQL($name) . '"'); $res = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query); foreach ($res as $row) { $tab[] = $row['id_stock_mvt_reason']; } return $tab; }
/** * @see ObjectModel::delete() */ public function delete() { @unlink(_PS_DOWNLOAD_DIR_ . $this->file); $sql = new DbQuery(); $sql->select('pa.`id_product`'); $sql->from('product_attachment', 'pa'); $sql->where('pa.`id_attachment` = ' . (int) $this->id); $products = Db::getInstance()->executeS($sql); Db::getInstance()->delete('product_attachment', '`id_attachment` = ' . (int) $this->id); foreach ($products as $product) { Product::updateCacheAttachment((int) $product['id_product']); } return parent::delete(); }
private function getFieldsValues() { $sql = new DbQuery(); $sql->select('*'); $sql->from('expressmailing_sms'); $sql->where('campaign_id = ' . $this->campaign_id); $result = Db::getInstance()->getRow($sql); $file_copy = $result['path_to_import']; $preview = array(); $preview = EMTools::getCSVPreview($file_copy); $this->context->smarty->assign('preview', $preview); $this->context->smarty->assign('campaign_id', $this->campaign_id); $this->context->smarty->assign('next_page', 'AdminMarketingSStep2'); $this->context->smarty->assign('prev_page', 'AdminMarketingSStep2'); return true; }
public function getUrlsSharedCart() { if (!$this->getGroup()->share_order) { return false; } $query = new DbQuery(); $query->select('domain'); $query->from('shop_url'); $query->where('active = 1'); $query .= $this->addSqlRestriction(Shop::SHARE_ORDER); $domains = array(); foreach (Db::getInstance()->executeS($query) as $row) { $domains[] = $row['domain']; } return $domains; }
public static function getResponseByQuestion($id_question) { $query = new DbQuery(); $query->from('lgconsultas_respuestas'); $query->where('id_consulta = ' . (int) $id_question); $row = DB::getInstance()->executeS($query); if ($row) { $rows = ObjectModel::hydrateCollection('LGRespuesta', $row); $object = array_pop($rows); } else { $object = new LGRespuesta(); $object->id_consulta = (int) $id_question; $object->id_employee = (int) 0; $object->respuesta = ''; $object->date_add = ''; } return $object; }
public static function getIdsAdviceToUnvalidate() { $ids = array(); $query = new DbQuery(); $query->select('a.`id_advice`'); $query->from('advice', 'a'); $query->join(' LEFT JOIN `' . _DB_PREFIX_ . 'condition_advice` ca ON ca.`id_advice` = a.`id_advice` AND ca.`display` = 0 LEFT JOIN `' . _DB_PREFIX_ . 'condition` c ON c.`id_condition` = ca.`id_condition` AND c.`validated` = 1'); $query->where('a.validated = 1'); $query->groupBy('a.`id_advice`'); $query->having('count(*) = SUM(c.validated)'); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query); foreach ($result as $advice) { $ids[] = $advice['id_advice']; } return $ids; }
public static function getStockImages() { // build query $query = new DbQuery(); $query->select('id_stock_image, name, date_add, type_stock'); $query->from('stock_image', 'si'); $query->where('type_stock=\'' . pSQL(Configuration::get('PS_ADVANCED_STOCK_MANAGEMENT')) . '\''); $query->orderBy('date_add DESC'); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query); // date format $i = 0; foreach ($result as $value) { $date_add_tab = explode(' ', $value['date_add']); $date_add_tab = explode('-', $date_add_tab[0]); $result[$i]['date_add'] = 'Le ' . $date_add_tab[2] . '/' . $date_add_tab[1] . '/' . $date_add_tab[0]; $i++; } return $result; }
public static function getZoneBreadcrumbs($first_id_zone) { if (self::$secure_loop > 50) { return array(); } $query = new DbQuery(); $query->select('name, id_parent'); $query->from('erpip_zone'); $query->where('id_erpip_zone = ' . (int) $first_id_zone); $zone = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($query); if (!empty($zone) && (int) $zone['id_parent'] > 0) { self::$zone_breadcrumbs[] = $zone['name']; self::getZoneBreadcrumbs($zone['id_parent']); } else { if (!empty($zone)) { self::$zone_breadcrumbs[] = $zone['name']; } } return self::$zone_breadcrumbs; }
private function getFieldsValues() { $sql = new DbQuery(); $sql->select('*'); $sql->from('expressmailing_email'); $sql->where('campaign_id = ' . $this->campaign_id); $result = Db::getInstance()->getRow($sql); $this->fields_value['campaign_id'] = $this->campaign_id; if (!empty($result['campaign_sender_email'])) { $this->fields_value['campaign_sender_email'] = $result['campaign_sender_email']; } else { $this->fields_value['campaign_sender_email'] = Configuration::get('PS_SHOP_EMAIL'); } if (!empty($result['campaign_sender_name'])) { $this->fields_value['campaign_sender_name'] = $result['campaign_sender_name']; } else { $this->fields_value['campaign_sender_name'] = Configuration::get('PS_SHOP_NAME'); } return true; }
/** * Acción que permite obtener los datos de un item (producto) para poder * consumir desde la aplicación web de LibreDTE * @author Esteban De La Fuente Rubio, DeLaF (esteban[at]sasco.cl) * @version 2016-01-30 */ public function init() { $item = []; // inicializar controlador parent::init(); // columna que se usará para identificar al producto if (!($column = Tools::getValue('column'))) { $column = 'product_id'; } // recuperar ID del producto if ($product_id = Tools::getValue('product_id')) { if ($column != 'product_id') { $db = Db::getInstance(); $sql = new DbQuery(); $sql->select('id_product'); $sql->from('product'); $sql->where($db->_escape($column) . ' = \'' . $db->_escape($product_id) . '\''); $r = Db::getInstance()->executeS($sql); $product_id = isset($r[0]['id_product']) ? (int) $r[0]['id_product'] : 0; } else { $product_id = (int) $product_id; } } else { $product_id = 0; } // crear objeto con el producto if ($product_id) { $this->product = new Product($product_id, true, $this->context->language->id, $this->context->shop->id); } // si el objeto no existe error if (!Validate::isLoadedObject($this->product) or !$this->product->active) { header('HTTP/1.1 404 Not Found'); header('Status: 404 Not Found'); $item = 'Item solicitado fue encontrado o no está activo'; } else { $item = ['TpoCodigo' => 'INT1', 'VlrCodigo' => substr(Tools::getValue('product_id'), 0, 35), 'NmbItem' => substr($this->product->name, 0, 80), 'DscItem' => substr($this->product->meta_description, 0, 1000), 'IndExe' => $this->product->tax_rate ? 0 : 1, 'UnmdItem' => substr('', 0, 4), 'PrcItem' => round($this->product->price), 'ValorDR' => 0, 'TpoValor' => '$']; } // enviar item como objeto json header('Content-Type: application/json'); die(json_encode($item, JSON_PRETTY_PRINT)); }
protected function getTaxOrderSummary() { $query = new DbQuery(); $query->select(' SUM(price_with_order_discount_te) as base_te, tax_rate, SUM(tax_value_with_order_discount) as total_tax_value '); $query->from('supply_order_detail'); $query->where('id_supply_order = ' . (int) $this->supply_order->id); $query->groupBy('tax_rate'); $results = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query); foreach ($results as &$result) { $result['base_te'] = Tools::ps_round($result['base_te'], 2); $result['tax_rate'] = Tools::ps_round($result['tax_rate'], 2); $result['total_tax_value'] = Tools::ps_round($result['total_tax_value'], 2); } unset($result); // remove reference return $results; }
private function getFieldsValues() { $sql = new DbQuery(); $sql->select('*'); $sql->from('expressmailing_sms'); $sql->where('campaign_id = ' . $this->campaign_id); $result = Db::getInstance()->getRow($sql); $this->fields_value['campaign_id'] = $this->campaign_id; $this->fields_value['campaign_name'] = $result['campaign_name']; $this->fields_value['campaign_date_send'] = $result['campaign_date_send']; $this->context->smarty->assign('campaign_text', $result['campaign_sms_text']); $this->fields_value['week_day_limit_L'] = strpos($result['campaign_week_limit'], 'L') !== false ? 'on' : null; $this->fields_value['week_day_limit_M'] = strpos($result['campaign_week_limit'], 'M') !== false ? 'on' : null; $this->fields_value['week_day_limit_C'] = strpos($result['campaign_week_limit'], 'C') !== false ? 'on' : null; $this->fields_value['week_day_limit_J'] = strpos($result['campaign_week_limit'], 'J') !== false ? 'on' : null; $this->fields_value['week_day_limit_V'] = strpos($result['campaign_week_limit'], 'V') !== false ? 'on' : null; $this->fields_value['week_day_limit_S'] = strpos($result['campaign_week_limit'], 'S') !== false ? 'on' : null; $this->fields_value['week_day_limit_D'] = strpos($result['campaign_week_limit'], 'D') !== false ? 'on' : null; $this->fields_value['schedule_sending'] = $this->generateSlider('schedule_sending', 0, 1440, 10, $result['campaign_start_hour'], $result['campaign_end_hour']); return true; }
public static function getMovementsByIds($ids) { // Reformatting datas if multiple IDs separated by '|' and protection if (strstr($ids, '|')) { $idsList = explode('|', $ids); array_walk($idsList, 'intval'); $ids = implode(',', $idsList); } else { $ids = pSQL($ids); } $query = new DbQuery(); $query->select(' IFNULL(CONCAT(pl.name, \' : \', GROUP_CONCAT(DISTINCT agl.`name`, \' - \', al.name SEPARATOR \', \')),pl.name) as name, IFNULL(pa.reference, s.reference) as reference, sm.physical_quantity, m.name as manufacturer_name '); $query->from('stock_mvt', 'sm'); $query->join(' INNER JOIN `' . _DB_PREFIX_ . 'stock` s ON s.id_stock = sm.id_stock'); $query->join('INNER JOIN ' . _DB_PREFIX_ . 'product p ON s.id_product = p.id_product '); $query->join('LEFT JOIN ' . _DB_PREFIX_ . 'product_attribute pa ON s.id_product_attribute = pa.id_product_attribute '); $query->join(' LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON ( s.id_product = pl.id_product AND pl.id_lang = ' . (int) Context::getContext()->language->id . ' )'); $query->join(' LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute_combination` pac ON (pac.id_product_attribute = s.id_product_attribute)'); $query->join(' LEFT JOIN `' . _DB_PREFIX_ . 'attribute` atr ON (atr.id_attribute = pac.id_attribute)'); $query->join(' LEFT JOIN `' . _DB_PREFIX_ . 'attribute_lang` al ON ( al.id_attribute = pac.id_attribute AND al.id_lang = ' . (int) Context::getContext()->language->id . ' )'); $query->join('LEFT JOIN `' . _DB_PREFIX_ . 'attribute_group_lang` agl ON ( agl.id_attribute_group = atr.id_attribute_group AND agl.id_lang = ' . (int) Context::getContext()->language->id . ' )'); $query->join('LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.id_manufacturer = p.id_manufacturer'); $query->where("sm.id_stock_mvt IN ({$ids})"); $query->groupBy('s.id_product, s.id_product_attribute'); return Db::getInstance()->executeS($query); }