/**
* save notice
*
* @param $title - string - message title
* @param $message - string - message body
* @param $status - message status ( N - notice, W - warning, E - error )
* @param bool $repetition_group - sign to group repetitions of message based on same title of message
* @void
*/
private function _saveMessage($title, $message, $status, $repetition_group = true)
{
$last_message = $this->getLikeMessage($title);
// if last message equal new - update it's repeated.
// update counter and update message body as last one can be different
if ($last_message['title'] == $title && $repetition_group) {
$this->db->query("UPDATE " . $this->db->table("messages") . " \n\t\t\t\t\t\t\t\tSET `repeated` = `repeated` + 1, \n\t\t\t\t\t\t\t\t\t`viewed`='0', \n\t\t\t\t\t\t\t\t\t`message` = '" . $this->db->escape($message) . "'\n\t\t\t\t\t\t\t\tWHERE msg_id = '" . $last_message['msg_id'] . "'");
} else {
$this->db->query("INSERT INTO " . $this->db->table("messages") . " \n\t\t\t\t\t\t SET `title` = '" . $this->db->escape($title) . "',\n\t\t\t\t\t\t `message` = '" . $this->db->escape($message) . "',\n\t\t\t\t\t\t `status` = '" . $this->db->escape($status) . "',\t\t\t\t\t\t \n\t\t\t\t\t\t `date_added` = NOW()");
$msg_id = $this->db->getLastId();
}
}
/**
* @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;
}
/**
* Function for creating new columns in dataset tables. If key "dataset_column_old_name" presents in array and not empty function updates existing column definition
*
* @param array $new_columnset array ("dataset_column_name"=>"","dataset_column_type"=>"","dataset_column_sort_order"=>"" [, "dataset_column_old_name"=>"",])
* @throws AException
* @return boolean
*/
public function defineColumns($new_columnset = array())
{
if (!$this->dataset_id) {
throw new AException(AC_ERR_LOAD, 'Error: Could not define columns! dataset id is null.');
}
$column_checklist = array('name', 'type');
// if $new_columnset[] contain array key 'old_name' it mean that column must be update (functional for future, for example for upgrading extension dataset)
// write columnset definitions
$existing_column_names = array();
if ($new_columnset) {
if ($this->columnset) {
foreach ($this->columnset as $id => $columns) {
$existing_column_names[$id] = $columns['dataset_column_name'];
}
}
$i = 0;
foreach ($new_columnset as $column_definition) {
// checks
if (!is_array($column_definition)) {
throw new AException(AC_ERR_LOAD, 'Error: Could not write dataset columns! column definition is not array.');
}
//check keys of definition
if (!array_intersect(array_keys($column_definition), $column_checklist)) {
throw new AException(AC_ERR_LOAD, 'Error: Could not write dataset column definition! Definition format error.');
}
// check column type
if (!in_array($column_definition['type'], $this->column_type_checklist)) {
throw new AException(AC_ERR_LOAD, 'Error: Could not update dataset column definition! Column type error. Type: ' . $column_definition['type']);
}
$column_definition['name'] = $this->db->escape($column_definition['name']);
$column_definition['sort_order'] = isset($column_definition['sort_order']) ? (int) $column_definition['sort_order'] : $i;
// insert new column
if (!in_array($column_definition['name'], $existing_column_names) && empty($column_definition['old_name'])) {
unset($column_definition['old_name']);
$sql_query = "INSERT INTO " . $this->db->table("dataset_definition") . " (dataset_id, dataset_column_name, dataset_column_type, dataset_column_sort_order)\n\t \t\t\t\t\t\t\t\t\tVALUES ('" . $this->dataset_id . "',\n\t \t\t\t\t\t\t\t\t\t '" . $column_definition['name'] . "',\n\t \t\t\t\t\t\t\t\t\t '" . $column_definition['type'] . "',\n\t \t\t\t\t\t\t\t\t\t '" . $column_definition['sort_order'] . "' );\n";
$this->db->query($sql_query);
$dataset_column_id = $this->db->getLastId();
//after insert of column need to insert empty values for data consistency
$sql_query = "SELECT DISTINCT dv.row_id\n\t\t\t\t\t\t\t\t FROM " . $this->db->table('dataset_values') . " dv\n\t\t\t\t\t\t\t\t INNER JOIN " . $this->db->table('dataset_definition') . " dd ON dd.dataset_column_id = dv.dataset_column_id\n\t\t\t\t\t\t\t\t WHERE dd.dataset_id = '" . $this->dataset_id . "' AND dv.row_id>0";
$res = $this->db->query($sql_query);
if ($res->num_rows) {
foreach ($res->rows as $r) {
$this->db->query("INSERT INTO " . $this->db->table('dataset_values') . " (dataset_column_id, row_id)\n\t\t\t\t\t\t\t\t\t\t\t\tVALUES ('" . $dataset_column_id . "','" . $r['row_id'] . "')");
}
}
// update new column
} else {
// if old name present - update column definition.
//if column type will change - just change it, old values will not move to another column of dataset_values. User need update it by himself.
if (!empty($column_definition['old_name']) && !empty($column_definition['name'])) {
$column_id = (int) array_search(!isset($column_definition['old_name']) ? $column_definition['name'] : $column_definition['old_name'], $existing_column_names);
$sql_query = "UPDATE " . $this->db->table("dataset_definition") . " ";
$sql_query .= "SET dataset_column_name= '" . $column_definition['name'] . "', ";
$sql_query .= "dataset_column_type= '" . $column_definition['type'] . "', ";
$sql_query .= "dataset_column_sort_order= '" . $column_definition['sort_order'] . "'";
$sql_query .= "WHERE dataset_column_id=" . $column_id . ";";
$this->db->query($sql_query);
}
}
//if new name is empty do nothing
$i++;
}
// reset $this->columnset
$this->_getColumnSet();
}
return true;
}
