/**
* @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;
}
