/** * @param $registry Registry */ public function __construct($registry) { $this->db = $registry->get('db'); $this->config = $registry->get('config'); $length_class_query = $this->db->query("SELECT *\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->db->table("length_classes") . " mc\n\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN " . $this->db->table("length_class_descriptions") . " mcd\n\t\t\t\t\t\t\t\t\t\t\t\t\tON (mc.length_class_id = mcd.length_class_id)\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE mcd.language_id = '" . (int) $this->config->get('storefront_language_id') . "'"); foreach ($length_class_query->rows as $result) { $this->lengths[strtolower($result['unit'])] = array('length_class_id' => $result['length_class_id'], 'unit' => $result['unit'], 'title' => $result['title'], 'value' => $result['value']); } }
/** * @param $registry Registry */ public function __construct($registry) { $this->db = $registry->get('db'); $this->config = $registry->get('config'); $sql = "SELECT *\n\t\t\t\tFROM " . $this->db->table("weight_classes") . " wc\n\t\t\t\tLEFT JOIN " . $this->db->table("weight_class_descriptions") . " wcd\n\t\t\t\t\tON (wc.weight_class_id = wcd.weight_class_id)\n\t\t\t\tWHERE wcd.language_id = '" . (int) $this->config->get('storefront_language_id') . "'"; $weight_class_query = $this->db->query($sql); foreach ($weight_class_query->rows as $result) { $this->weights[strtolower($result['unit'])] = array('weight_class_id' => $result['weight_class_id'], 'title' => $result['title'], 'unit' => $result['unit'], 'value' => $result['value']); } }
/** * @param $registry Registry */ public function __construct($registry) { $this->db = $registry->get('db'); $this->config = $registry->get('config'); $cache = $registry->get('cache'); $language_id = (int) $registry->get('language')->getLanguageID(); $cache_key = 'localization.length_classes.lang_' . $language_id; $cache_data = $cache->pull($cache_key); if ($cache_data !== false) { $this->lengths = $cache_data; } else { $sql = "SELECT *\n\t\t\t\t\tFROM " . $this->db->table("length_classes") . " mc\n\t\t\t\t\tLEFT JOIN " . $this->db->table("length_class_descriptions") . " mcd\n\t\t\t\t\t\tON (mc.length_class_id = mcd.length_class_id)\n\t\t\t\t\tWHERE mcd.language_id = '" . $language_id . "'"; $length_class_query = $this->db->query($sql); foreach ($length_class_query->rows as $result) { $this->lengths[strtolower($result['unit'])] = array('length_class_id' => $result['length_class_id'], 'unit' => $result['unit'], 'title' => $result['title'], 'value' => $result['value']); } $cache->push($cache_key, $this->lengths); } }
/** * @return array */ public function getShortList() { $output = array(); $result = $this->db->query("SELECT UPPER(status) as status, COUNT(msg_id) as count\n\t\t\t\t\t\t\t\t\tFROM " . $this->db->table("messages") . " \n\t\t\t\t\t\t\t\t\tWHERE viewed<'1'\n\t\t\t\t\t\t\t\t\tGROUP BY status"); foreach ($result->rows as $row) { $output['count'][$row['status']] = (int) $row['count']; $total += (int) $row['count']; } $output['total'] = $total; //let last couple of messages for each type $result = $this->db->query("(SELECT msg_id, title, message, status, viewed, date_modified\n\t\t\t\tFROM " . $this->db->table('messages') . "\n\t\t\t\t\tWHERE UPPER(status)='E'\n\t\t\t\t\tORDER BY date_modified DESC\n\t\t\t\t\tLIMIT 0,3)\n\t\t\t\tUNION\n\t\t\t\t\t(SELECT msg_id, title, message, status, viewed, date_modified\n\t\t\t\t\tFROM " . $this->db->table('messages') . "\n\t\t\t\t\tWHERE UPPER(status)='W'\n\t\t\t\t\tORDER BY date_modified DESC\n\t\t\t\t\tLIMIT 0,3)\n\t\t\t\tUNION\n\t\t\t\t\t(SELECT msg_id, title, message, status, viewed, date_modified\n\t\t\t\t\tFROM " . $this->db->table('messages') . "\n\t\t\t\t\tWHERE UPPER(status)='N'\n\t\t\t\t\tORDER BY date_modified DESC\n\t\t\t\t\tLIMIT 0,3)"); $output['shortlist'] = $result->rows; return $output; }
/** * drop dataset with values and columnset * @internal param int $dataset_id * @return boolean */ public function dropDataset() { if (!$this->dataset_id) { return false; } if ($this->columnset) { $this->db->query("DELETE FROM " . $this->db->table("dataset_values") . " WHERE dataset_column_id in (" . implode(", ", array_keys($this->columnset)) . ");"); $this->db->query("DELETE FROM " . $this->db->table("dataset_column_properties") . " WHERE dataset_column_id in (" . implode(", ", array_keys($this->columnset)) . ");"); } $this->db->query("DELETE FROM " . $this->db->table("dataset_properties") . " WHERE dataset_id = " . $this->dataset_id . ";"); $this->db->query("DELETE FROM " . $this->db->table("dataset_definition") . " WHERE dataset_id = " . $this->dataset_id . ";"); $this->db->query("DELETE FROM " . $this->db->table("datasets") . " WHERE dataset_id = " . $this->dataset_id . ";"); $this->dataset_id = 0; $this->columnset = array(); return true; }
/** * Get available tax classes for country ID and zone ID * Storefront use only!!! * @param $country_id * @param $zone_id * @return mixed|null */ public function getTaxes($country_id, $zone_id) { $country_id = (int) $country_id; $zone_id = (int) $zone_id; $language = $this->registry->get('language'); $language_id = $language->getLanguageID(); $cache_name = 'tax_class.' . $country_id . '.' . $zone_id; $results = $this->cache->get($cache_name, $language_id); if (is_null($results)) { //Note: Default language text is picked up if no selected language available $sql = "SELECT tr.tax_class_id,\n\t\t\t\t\t\t\ttr.rate AS rate, tr.rate_prefix AS rate_prefix, \n\t\t\t\t\t\t\ttr.threshold_condition AS threshold_condition, tr.threshold AS threshold,\n\t\t\t\t\t\t\tCOALESCE( td1.title,td2.title) as title,\n\t\t\t\t\t\t\tCOALESCE( NULLIF(trd1.description, ''),\n\t\t\t\t\t\t\t\t\t NULLIF(td1.description, ''),\n\t\t\t\t\t\t\t\t\t NULLIF(trd2.description, ''),\n\t\t\t\t\t\t\t\t\t NULLIF(td2.description, ''),\n\t\t\t\t\t\t\t\t\t COALESCE( td1.title,td2.title)\n\t\t\t\t\t\t\t) as description,\n\t\t\t\t\t\t\ttr.priority\t\n\t\t\t\t\tFROM " . $this->db->table("tax_rates") . " tr\n\t\t\t\t\tLEFT JOIN " . $this->db->table("tax_rate_descriptions") . " trd1 ON \n\t\t\t\t\t\t(tr.tax_rate_id = trd1.tax_rate_id AND trd1.language_id = '" . (int) $language_id . "')\n\t\t\t\t\tLEFT JOIN " . $this->db->table("tax_rate_descriptions") . " trd2 ON \n\t\t\t\t\t\t(tr.tax_rate_id = trd2.tax_rate_id AND trd2.language_id = '" . (int) $default_lang_id . "')\n\t\t\t\t\tLEFT JOIN " . $this->db->table("tax_classes") . " tc ON tc.tax_class_id = tr.tax_class_id\n\t\t\t\t\tLEFT JOIN " . $this->db->table("tax_class_descriptions") . " td1 ON \n\t\t\t\t\t\t(tc.tax_class_id = td1.tax_class_id AND td1.language_id = '" . (int) $language_id . "')\n\t\t\t\t\tLEFT JOIN " . $this->db->table("tax_class_descriptions") . " td2 ON \n\t\t\t\t\t\t(tc.tax_class_id = td2.tax_class_id AND td2.language_id = '" . (int) $default_lang_id . "')\n\t\t\t\t\tWHERE (tr.zone_id = '0' OR tr.zone_id = '" . $zone_id . "')\n\t\t\t\t\t\tAND tr.location_id in (SELECT z2l.location_id\n\t\t\t\t\t\t\t\t\t\t\t FROM " . $this->db->table("zones_to_locations") . " z2l, " . $this->db->table("locations") . " l\n\t\t\t\t\t\t\t\t\t\t\t WHERE z2l.location_id = l.location_id and z2l.zone_id = '" . $zone_id . "')\n\t\t\t\t\tORDER BY tr.priority ASC"; $tax_rate_query = $this->db->query($sql); $results = $tax_rate_query->rows; $this->cache->set($cache_name, $results, $language_id); } return $results; }
/** * @param $filename * @param array $lang_defns * @return bool|void */ protected function _save_to_db($filename, $lang_defns) { if (!$lang_defns) { return false; } $block = str_replace('/', '_', $filename); ADebug::checkpoint('ALanguage ' . $this->language_details['name'] . ' ' . $block . ' saving to database'); $sql = "INSERT INTO " . $this->db->table("language_definitions") . " "; $sql .= "(language_id,block,section,language_key,language_value,date_added) VALUES "; $values = array(); foreach ($lang_defns as $k => $v) { //preventing duplication sql-error by unique index $check_array = array('language_id' => (int) $this->language_details['language_id'], 'block' => $this->db->escape($block), 'section' => $this->is_admin, 'language_key' => $this->db->escape($k), 'language_value' => $this->db->escape($v)); if ($this->_is_definition_in_db($check_array)) { continue; } $values[] = "('" . (int) $this->language_details['language_id'] . "',\n '" . $this->db->escape($block) . "',\n '" . $this->is_admin . "',\n '" . $this->db->escape($k) . "',\n '" . $this->db->escape($v) . "',\n NOW() )"; } if ($values) { $sql = $sql . implode(', ', $values); $this->db->query($sql); } return null; }
/** * @param string $type * @param array $tr_details - amount, order_id, transaction_type, description, comments, creator * @return bool */ private function _record_transaction($type, $tr_details) { if (!$this->isLogged()) { return false; } if (!has_value($tr_details['transaction_type']) || !has_value($tr_details['created_by'])) { return false; } if ($type == 'debit') { $amount = 'debit = ' . (double) $tr_details['amount']; } else { if ($type == 'credit') { $amount = 'credit = ' . (double) $tr_details['amount']; } else { return false; } } $this->db->query("INSERT INTO " . $this->db->table("customer_transactions") . "\n \t SET customer_id \t\t= '" . (int) $this->getId() . "',\n \t \torder_id \t\t\t= '" . (int) $tr_details['order_id'] . "',\n \t transaction_type \t= '" . $this->db->escape($tr_details['transaction_type']) . "',\n \t description \t\t= '" . $this->db->escape($tr_details['description']) . "',\n \t comment \t\t\t= '" . $this->db->escape($tr_details['comment']) . "',\n\t\t\t\t\t\t\t" . $amount . ",\n\t\t\t\t\t\t\tsection\t\t\t\t= '" . ((int) $tr_details['section'] ? (int) $tr_details['section'] : 0) . "',\n \t created_by \t\t\t= '" . (int) $tr_details['created_by'] . "',\n \t date_added = NOW()"); $this->cache->delete('balance.' . (int) $this->getId()); if ($this->db->getLastId()) { return true; } return false; }
/** * @param string $table_name * @param array $table_cfg * @param array $data_row * @param array $parent_vals * @return array */ private function _update_or_insert_fromArray($table_name, $table_cfg, $data_row, $parent_vals) { $return = array(); $where = array(); $cols = array(); //set ids to where from parent they might not be in there $where = $this->_build_id_columns($table_cfg, $parent_vals); foreach ($data_row as $col_name => $col_value) { if ($col_name == 'tables' || $col_name == 'action') { continue; } if (isset($parent_vals[$col_name]) && $parent_vals[$col_name] != '') { //we laready set this above. continue; } // Encrypt column value if encryption is enabled if ($this->dcrypt && $this->dcrypt->active && in_array($table_name, $this->dcrypt->getEcryptedTables()) && in_array($col_name, $this->dcrypt->getEcryptedFields($table_name))) { $encrypted = $this->dcrypt->encrypt_data(array($col_name => $col_value), $table_name); $col_value = $encrypted[$col_name]; } if ($col_name == $table_cfg['id'] || isset($table_cfg['relation_ids']) && in_array($col_name, $table_cfg['relation_ids'])) { $where[] = "`" . $col_name . "` = '" . $this->db->escape($col_value) . "'"; continue; } // Date validation // TODO Add field type to table configuration if ($col_name == 'date_added' || $col_name == 'date_modified') { if ((string) $col_value == '0000-00-00 00:00:00') { $cols[] = "`" . $col_name . "` = '" . $this->db->escape($col_value) . "'"; } else { $cols[] = "`" . $col_name . "` = '" . date('Y-m-d H:i:s', strtotime($col_value)) . "'"; } } else { if ($col_name == 'date_available') { if ((string) $col_value == '0000-00-00') { $cols[] = "`" . $col_name . "` = '" . $this->db->escape($col_value) . "'"; } else { $cols[] = "`" . $col_name . "` = '" . date('Y-m-d', strtotime($col_value)) . "'"; } } else { $cols[] = "`" . $col_name . "` = '" . $this->db->escape($col_value) . "'"; } } } $status = 'insert'; if (empty($cols) && empty($where)) { $this->_status2array('error', "Update or Insert {$table_name}. No Data to update."); return array(); } if (!empty($where)) { $check_sql = "SELECT count(*) AS total FROM `" . $this->db->table($table_name) . "` WHERE " . implode(' AND ', $where); if ($this->db->query($check_sql)->row['total'] == 1) { // We are trying to update table where all columns are keys. We have to skip it. if (empty($cols)) { return array(); } $status = 'update'; } } if ($status == 'update') { if (empty($cols)) { $this->_status2array('error', "Update {$table_name}. No Data to update."); return array(); } $sql = "UPDATE `" . $this->db->table($table_name) . "`"; $sql .= " SET " . implode(', ', $cols); $sql .= " WHERE " . implode(' AND ', $where); } else { $sql = "INSERT INTO `" . $this->db->table($table_name) . "`"; $sql .= " SET "; $set_cols = array_unique(array_merge($where, $cols)); $sql .= implode(', ', $set_cols); } if ($this->run_mode == 'commit') { $this->db->query($sql, TRUE); if ($status == 'insert' && isset($table_cfg['id'])) { //If special case, no new ID. if (!$table_cfg['on_insert_no_id']) { $return[$table_cfg['id']] = $this->db->getLastId(); } } } else { $this->_status2array('sql', $sql); if ($status == 'insert' && isset($table_cfg['id'])) { //If special case, no new ID. if (!$table_cfg['on_insert_no_id']) { $return[$table_cfg['id']] = "new_id"; } //id is present for insert if ($data_row[$table_cfg['id']]) { $return[$table_cfg['id']] = $data_row[$table_cfg['id']]; } } } if (!empty($this->db->error)) { $this->_status2array('error', "{$status} data error in {$table_name}. " . $this->db->error); } else { $this->_status2array($status, "{$status} for table {$table_name} done successfully"); } return $return; }
/** * @param int $layout_id * @return bool */ private function _clear_layouts($layout_id) { $this->db->query("DELETE FROM " . $this->db->table("layouts") . " \n\t\t\tWHERE layout_id = '" . (int) $layout_id . "'"); return true; }