/** * 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 { $product = new Product((int) $this->id_product); if (Validate::isLoadedObject($product)) { $this->reference = $product->reference; $this->ean13 = $product->ean13; $this->upc = $product->upc; } } }
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`'); $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); return parent::renderView(); }
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); }
/** * 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 getLastId() { // build query $query = new DbQuery(); $query->select('id_stock_image'); $query->from('stock_image', 'si'); $query->orderBy('id_stock_image DESC'); 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); }
public function getLastId() { // build query $query = new DbQuery(); $query->select('id_erpip_inventory'); $query->from('erpip_inventory', 'i'); $query->orderBy('id_erpip_inventory DESC'); 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; }
/** * 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 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); }
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 getAssociations() { $query = new DbQuery(); $query->select('name'); $query->from('my_associations'); $res = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query); /*$sql = 'SELECT name FROM my_associations'; $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);*/ return $res; }
/** * 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 deleteByIds($ids) { foreach ($ids as $id) { $query = new DbQuery(); $query->delete('stock_mvt_reason_lang', 'id_stock_mvt_reason = "' . pSQL($id['id_stock_mvt_reason']) . '"'); Db::getInstance(_PS_USE_SQL_SLAVE_)->execute($query); $query = null; $query = new DbQuery(); $query->delete('stock_mvt_reason', 'id_stock_mvt_reason = "' . pSQL($id['id_stock_mvt_reason']) . '"'); Db::getInstance(_PS_USE_SQL_SLAVE_)->execute($query); } return true; }
/** * @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; }
/** * 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; } } } }
/** * Gets the data in common of a given product and potentiat product_attribute * * @since 1.5.0 * @param int $id_product * @param int $id_product_attribute Optional * @return string */ public static function getProductsInfo($id_product, $id_product_attribute = null) { // creates the query object $query = new DbQuery(); // selects different names, if it is a combination if ($id_product_attribute) { $query->select('pa.ean13 AS ean13, pa.reference AS reference'); // adds joins & where clauses for combinations $query->from('product_attribute', 'pa'); $query->where('pa.id_product = \'' . pSQL($id_product) . '\' AND pa.id_product_attribute = ' . pSQL($id_product_attribute)); } else { $query->select('p.ean13 AS ean13, p.reference AS reference'); // or just adds a 'where' clause for a simple product $query->from('product', 'p'); $query->where('p.id_product = \'' . pSQL($id_product) . '\''); } $data = Db::getInstance()->executeS($query); return count($data) == 1 ? $data[0] : false; }
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)); }
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; }
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; }
function getDevices() { $devices_obj = new DbQuery(); $devices_obj->select(' mpn.`id`, mpn.`new_order`, mpn.`new_customer`, mpn.`order_statuses`, mpn.`id_shop`, mpn.`app_connection_id`, mpn.`status`, mpn.`device_unique_id`, md.`account_email`, md.`device_name`, md.`last_activity`, c.`iso_code` AS currency_iso '); $devices_obj->from('mobassistantconnector_push_notifications', 'mpn'); $devices_obj->leftJoin('mobassistantconnector_devices', 'md', 'md.`device_unique_id` = mpn.`device_unique_id`'); $devices_obj->leftJoin('currency', 'c', 'c.`id_currency` = mpn.`currency_code`'); $devices_sql = $devices_obj->build(); $devices = Db::getInstance()->executeS($devices_sql); if (!$devices) { $devices = array(); } $devices = replaceNull($devices); $statuses_db = OrderState::getOrderStates(Configuration::get('PS_LANG_DEFAULT')); $count_statuses = count($statuses_db); $statuses = array(); for ($i = 0; $i < $count_statuses; $i++) { $statuses[$statuses_db[$i]['id_order_state']] = $statuses_db[$i]['name']; } $devices = formDevices($devices, $statuses); return Tools::jsonEncode($devices); }
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; }
/** * * @param int $id * @return InvoiceMap|null */ private static function _get($id) { $sql = new \DbQuery(); $sql->select(implode(', ', self::getColumnNames())); $sql->from(self::TABLE_NAME); $sql->where('id = ' . intval($id)); $res = \Db::getInstance()->executeS($sql); if (count($res) !== 1) { return null; } $invoiceMap = new InvoiceMap(); foreach ($res[0] as $key => $value) { $invoiceMap->{$key} = $value; } return $invoiceMap; }