/**
* @covers Zend\Db\Sql\Sql::update
*/
public function testUpdate()
{
$update = $this->sql->update();
$this->assertInstanceOf('Zend\\Db\\Sql\\Update', $update);
$this->assertSame('foo', $update->getRawState('table'));
$this->setExpectedException('Zend\\Db\\Sql\\Exception\\InvalidArgumentException', 'This Sql object is intended to work with only the table "foo" provided at construction time.');
$this->sql->update('bar');
}
public function editarLocal($local, $id)
{
$pais = $local['pais'];
$departamento = $local['departamento'];
$provincia = $local['provincia'];
$distrito = $local['distrito'];
$adapter = $this->tableGateway->getAdapter();
$sql = new Sql($adapter);
$idubigeo = $sql->select()->from('ta_ubigeo')->columns(array('in_id'))->where(array('in_idpais' => $pais, 'in_iddep' => $departamento, 'in_idprov' => $provincia, 'in_iddis' => $distrito));
$selectString0 = $this->tableGateway->getSql()->getSqlStringForSqlObject($idubigeo);
$result = $adapter->query($selectString0, $adapter::QUERY_MODE_EXECUTE);
$convertir = $result->toArray();
$data = array('va_telefono' => $local['va_telefono'], 'va_horario' => $local['va_horario'], 'de_latitud' => $local['de_latitud'], 'de_longitud' => $local['de_longitud'], 'va_rango_precio' => $local['va_rango_precio'], 'va_horario_opcional' => $local['va_horario_opcional'], 'va_direccion' => $local['va_direccion'], 'va_direccion_referencia' => $local['va_direccion_referencia'], 'ta_ubigeo_in_id' => $convertir[0]['in_id'], 'va_dia' => $local['va_dia'], 'va_email' => $local['va_email']);
$idupda = $sql->update('ta_local')->set($data)->where(array('in_id' => $id));
$selectString3 = $this->tableGateway->getSql()->getSqlStringForSqlObject($idupda);
// var_dump($selectString3);exit;
$result3 = $adapter->query($selectString3, $adapter::QUERY_MODE_EXECUTE);
$adapte = $this->tableGateway->getAdapter();
$sq = new Sql($adapte);
$select = $sq->select()->from('ta_local')->join(array('tl' => 'ta_plato_has_ta_local'), 'ta_local.in_id = tl.Ta_local_in_id', array('plato' => 'Ta_plato_in_id'))->where(array('ta_local.in_id' => $id));
$selectString = $sql->getSqlStringForSqlObject($select);
$results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
$plato = $results->toArray();
foreach ($plato as $result) {
$this->estadoRestauranteSolarAction($result['plato'], '');
}
return $result3;
}
public function indexAction()
{
date_default_timezone_set('Asia/Dubai');
$auth = new AuthenticationService();
$container = new Container('username');
$sm = $this->getServiceLocator();
$dba = $sm->get($container->adapter);
$secsql = "SELECT * FROM section";
$secstatement = $dba->query($secsql, array(5));
$section = new ResultSet();
$section->initialize($secstatement);
$teasql = "SELECT * FROM teacher";
$teastatement = $dba->query($teasql, array(5));
$teacher = new ResultSet();
$teacher->initialize($teastatement);
if ($auth->hasIdentity() && $container->type == 0) {
if ($this->getRequest()->getPost('submit-update')) {
$count = (int) $this->getRequest()->getPost('rcount');
for ($i = 1; $i <= $count; $i++) {
$id = $this->getRequest()->getPost('rid' . $i);
$att = $this->getRequest()->getPost('rattendance' . $i);
$com = $this->getRequest()->getPost('rcomment' . $i);
$stup = $this->getRequest()->getPost('status-update' . $i);
if ($stup != 0) {
$data = array('counted' => $att, 'comment' => $com, 'Abs_value' => $stup);
$sql = new Sql($dba);
$update = $sql->update();
$update->table('attendance');
$update->set($data);
$update->where(array('Att_id' => $id));
$statement = $sql->prepareStatementForSqlObject($update);
$statement->execute();
} else {
$id = $this->getRequest()->getPost('rid' . $i);
$sql = new Sql($dba);
$delete = $sql->delete('attendance')->where(array('Att_id' => $id));
$statement = $sql->prepareStatementForSqlObject($delete);
$statement->execute();
}
}
$sday = $this->getRequest()->getPost('sdate-filter');
$eday = $this->getRequest()->getPost('edate-filter');
$syour_date = date("Y-m-d", strtotime($sday));
$eyour_date = date("Y-m-d", strtotime($eday));
return new ViewModel(array('attendance' => $this->getRepport($syour_date, $eyour_date, $this->getRequest()->getPost('stid-filter'), $this->getRequest()->getPost('tea-filter'), $this->getRequest()->getPost('sec-filter'), $this->getRequest()->getPost('p-filter'), $this->getRequest()->getPost('status-filter')), 'sections' => $section, 'teachers' => $teacher, 'message' => "Attendance report updated", 'sday' => $sday, 'eday' => $eday, 'stid' => $this->getRequest()->getPost('stid-filter'), 'tid' => $this->getRequest()->getPost('tea-filter'), 'secid' => $this->getRequest()->getPost('sec-filter'), 'pid' => $this->getRequest()->getPost('p-filter'), 'status' => $this->getRequest()->getPost('status-filter'), 'filter' => "Attendance report for " . $this->getRequest()->getPost('stid-filter') . " from " . date("l jS \\of F Y ", strtotime($sday)) . " to " . date("l jS \\of F Y ", strtotime($eday))));
} else {
if ($this->getRequest()->getPost('submit-date')) {
$sday = $this->getRequest()->getPost('sdate-filter');
$eday = $this->getRequest()->getPost('edate-filter');
$syour_date = date("Y-m-d", strtotime($sday));
$eyour_date = date("Y-m-d", strtotime($eday));
return new ViewModel(array('attendance' => $this->getRepport($syour_date, $eyour_date, $this->getRequest()->getPost('stid-filter'), $this->getRequest()->getPost('tea-filter'), $this->getRequest()->getPost('sec-filter'), $this->getRequest()->getPost('p-filter'), $this->getRequest()->getPost('status-filter')), 'sections' => $section, 'teachers' => $teacher, 'message' => "", 'sday' => $sday, 'eday' => $eday, 'stid' => $this->getRequest()->getPost('stid-filter'), 'tid' => $this->getRequest()->getPost('tea-filter'), 'secid' => $this->getRequest()->getPost('sec-filter'), 'pid' => $this->getRequest()->getPost('p-filter'), 'status' => $this->getRequest()->getPost('status-filter'), 'filter' => "Attendance report for " . $this->getRequest()->getPost('stid-filter') . " from " . date("l jS \\of F Y ", strtotime($sday)) . " to " . date("l jS \\of F Y ", strtotime($eday))));
} else {
return new ViewModel(array('attendance' => $this->getRepport(date('Y-m-d'), date('Y-m-d'), "", 0, 0, 0, 0), 'sections' => $section, 'teachers' => $teacher, 'message' => "", 'sday' => date('m/d/Y'), 'eday' => date('m/d/Y'), 'stid' => "", 'tid' => 0, 'secid' => 0, 'pid' => 0, 'status' => 0, 'filter' => "Attendance report for " . date("l jS \\of F Y ", strtotime(date('m/d/Y')))));
}
}
} else {
return $this->redirect()->toRoute('login', array('controller' => 'index', 'action' => 'login'));
}
}
public function save($model, $overwrite = false)
{
/**
* For each member variable in the model, call its associated getter to
* populate the data
*/
$fields = $model->getFields();
$data = array();
foreach ($fields as $row) {
$data[$row] = $model->{'get' . ucfirst($row)}();
}
if (null === $data['id']) {
/**
* Create the query string used to insert the data
* Either update a record if there is a duplicate or ignore it
*/
$qry = $overwrite ? 'INSERT INTO ' . $this->table : 'INSERT IGNORE INTO ' . $this->table;
array_shift($fields);
$insert_fields = implode($fields, ', ');
$qry .= " ({$insert_fields}) VALUES";
$i = count($fields);
$insert_values = array();
for ($j = 0; $j < $i; $j++) {
$insert_values[] = ":field_{$j}";
}
$insert_values = implode($insert_values, ', ');
$qry .= " ({$insert_values})";
if ($overwrite) {
$qry .= ' ON DUPLICATE KEY UPDATE';
$update_fields = array();
for ($j = 0; $j < $i; $j++) {
$update_fields[] = ' ' . $fields[$j] . " = :field_{$j}";
}
$update_fields = implode($update_fields, ', ');
$qry .= $update_fields;
}
$params = array();
for ($j = 0; $j < $i; $j++) {
$params["field_{$j}"] = $data[$fields[$j]];
}
$this->dbAdapter->query($qry, $params);
} else {
$sql = new Sql($this->dbAdapter);
$update = $sql->update();
$fields = $model->getFields();
array_shift($fields);
$data = array();
foreach ($fields as $row) {
$data[$row] = $model->{'get' . ucfirst($row)}();
}
$update->table($this->table);
$update->set($data);
$update->where(array('id = ' . $model->getId()));
$stmt = $sql->prepareStatementForSqlObject($update);
$stmt->execute();
}
}
public function update($data, $where)
{
$update = $this->sql->update($this->_table);
$update->set($data);
$update->where($where);
$result = $this->_execute($update);
if ($result) {
return true;
}
return false;
}
/**
* Save
*
* @return integer
*/
public function save()
{
$this->initialize();
if ($this->rowExistsInDatabase()) {
// UPDATE
$data = $this->data;
$where = array();
// primary key is always an array even if its a single column
foreach ($this->primaryKeyColumn as $pkColumn) {
$where[$pkColumn] = $this->primaryKeyData[$pkColumn];
if ($data[$pkColumn] == $this->primaryKeyData[$pkColumn]) {
unset($data[$pkColumn]);
}
}
$statement = $this->sql->prepareStatementForSqlObject($this->sql->update()->set($data)->where($where));
$result = $statement->execute();
$rowsAffected = $result->getAffectedRows();
unset($statement, $result);
// cleanup
} else {
// INSERT
$insert = $this->sql->insert();
$insert->values($this->data);
$statement = $this->sql->prepareStatementForSqlObject($insert);
$result = $statement->execute();
if (($primaryKeyValue = $result->getGeneratedValue()) && count($this->primaryKeyColumn) == 1) {
$this->primaryKeyData = array($this->primaryKeyColumn[0] => $primaryKeyValue);
} else {
// make primary key data available so that $where can be complete
$this->processPrimaryKeyData();
}
$rowsAffected = $result->getAffectedRows();
unset($statement, $result);
// cleanup
$where = array();
// primary key is always an array even if its a single column
foreach ($this->primaryKeyColumn as $pkColumn) {
$where[$pkColumn] = $this->primaryKeyData[$pkColumn];
}
}
// refresh data
$statement = $this->sql->prepareStatementForSqlObject($this->sql->select()->where($where));
$result = $statement->execute();
$rowData = $result->current();
unset($statement, $result);
// cleanup
// make sure data and original data are in sync after save
$this->populate($rowData, true);
// return rows affected
return $rowsAffected;
}
public function updateUsuario($id, $data = array())
{
$adapter = $this->tableGateway->getAdapter();
$sql = new Sql($adapter);
$update = $sql->update('ta_usuario', $data, array('in_id' => $id));
$selectString = $sql->getSqlStringForSqlObject($update);
$results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
// var_dump($selectString);exit;
$row = $results->current();
if (!$row) {
throw new \Exception("No existe registro con el parametro {$id}");
}
return $row;
}
public function save()
{
$sql = new Sql($this->adapter, TestRecord::TABLE);
$sqlObject = null;
if ($this->id) {
// update existing
$sqlObject = $sql->update()->set(["title" => $this->title, "created_on" => $this->created_on, "type" => $this->type])->where(["id" => $this->id]);
} else {
// insert new
$sqlObject = $sql->insert()->columns(["title", "created_on", "type"])->values(["title" => $this->title, "created_on" => $this->created_on, "type" => $this->type]);
}
$statement = $sql->prepareStatementForSqlObject($sqlObject);
$results = $statement->execute();
}
/**
* Writes the database back to the database
*/
protected function save()
{
$this->validate();
$zend_db = Database::getConnection();
$sql = new Sql($zend_db, $this->tablename);
if ($this->getId()) {
$update = $sql->update()->set($this->data)->where(array('id' => $this->getId()));
$sql->prepareStatementForSqlObject($update)->execute();
} else {
$insert = $sql->insert()->values($this->data);
$sql->prepareStatementForSqlObject($insert)->execute();
$this->data['id'] = $zend_db->getDriver()->getLastGeneratedValue();
}
}
/**
* Save
*
* @return integer
*/
public function save()
{
if (is_array($this->primaryKeyColumn)) {
// @todo compound primary keys
throw new Exception\RuntimeException('Compound primary keys are currently not supported, but are on the TODO list.');
}
if (isset($this->originalData[$this->primaryKeyColumn])) {
// UPDATE
$where = array($this->primaryKeyColumn => $this->originalData[$this->primaryKeyColumn]);
$data = $this->data;
unset($data[$this->primaryKeyColumn]);
$statement = $this->sql->prepareStatementForSqlObject($this->sql->update()->set($data)->where($where));
$result = $statement->execute();
$rowsAffected = $result->getAffectedRows();
unset($statement, $result); // cleanup
} else {
// INSERT
$insert = $this->sql->insert();
$insert->values($this->data);
$statement = $this->sql->prepareStatementForSqlObject($insert);
$result = $statement->execute();
$primaryKeyValue = $result->getGeneratedValue();
$rowsAffected = $result->getAffectedRows();
unset($statement, $result); // cleanup
$where = array($this->primaryKeyColumn => $primaryKeyValue);
}
// refresh data
$statement = $this->sql->prepareStatementForSqlObject($this->sql->select()->where($where));
$result = $statement->execute();
$rowData = $result->current();
unset($statement, $result); // cleanup
$this->populateOriginalData($rowData);
// return rows affected
return $rowsAffected;
}
public function update($data, $orderProductId)
{
$adapter = $this->adapter;
$sql = new Sql($adapter);
$update = $sql->update();
$update->table('order_products');
$update->set($data);
$update->where(array('order_product_id' => $orderProductId));
$statement = $sql->prepareStatementForSqlObject($update);
try {
$affectedRows = $statement->execute()->getAffectedRows();
} catch (\Exception $e) {
die('Error: ' . $e->getMessage());
}
if (empty($affectedRows)) {
die('Zero rows affected');
}
return $affectedRows;
}
public function _insert(Servicos $servicos)
{
$adapter = $this->ConfAdapter();
$servicos = (array) $servicos;
$sql = new Sql($adapter);
if ($servicos['id'] == 0) {
$insert = $sql->insert('servicos')->values($servicos);
} else {
$insert = $sql->update('servicos')->set($servicos)->where(array('id' => $servicos['id']));
}
$sqlString = $sql->getSqlStringForSqlObject($insert);
$adapter->query($sqlString, Adapter::QUERY_MODE_EXECUTE);
$statement = $adapter->query('select id from servicos order by id desc limit 1');
$stm = $statement->execute();
foreach ($stm as $dados) {
$result = $dados['id'];
}
return $result;
}
/**
* Update data into table
*
* @param array|ArrayObject $data
* @param Where|\Closure|string|array|Predicate\PredicateInterface $predicate
* @param string $combination One of the OP_* constants from Predicate\PredicateSet
* @param boolean $validate_datatypes ensure all datatype are compatible with column definition
*
* @throws Exception\InvalidArgumentException
* @throws Exception\ColumnNotFoundException when $data contains columns that does not exists in table
* @throws Exception\ForeignKeyException when insertion failed because of an invalid foreign key
* @throws Exception\DuplicateEntryException when insertion failed because of an invalid foreign key
* @throws Exception\NotNullException when insertion failed because a column cannot be null
* @throws Exception\RuntimeException when insertion failed for another reason
*
* @return int number of affected rows
*/
public function update($data, $predicate, $combination = Predicate\PredicateSet::OP_AND, $validate_datatypes = false)
{
$prefixed_table = $this->prefixed_table;
if ($data instanceof ArrayObject) {
$d = (array) $data;
} elseif (is_array($data)) {
$d = $data;
} else {
throw new Exception\InvalidArgumentException(__METHOD__ . ": requires data to be array or an ArrayObject");
}
$this->checkDataColumns($d);
if ($validate_datatypes) {
$this->validateDatatypes($d);
}
$update = $this->sql->update($prefixed_table);
$update->set($d);
$update->where($predicate, $combination);
$result = $this->executeStatement($update);
return $result->getAffectedRows();
}
public function refresh($user_id)
{
$adapter = $this->sm->get('Zend\\Db\\Adapter\\Adapter');
$sql = new Sql($adapter);
do {
//Generate new hash
$hash = Application\Model\Util::unique_id();
//Check if hash is taken
$select = $sql->select('tokens_table')->where(array('hash' => $hash));
$selectString = $sql->getSqlStringForSqlObject($select);
$results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE)->toArray()[0];
} while ($results);
//Disable old hashs
$update = $sql->update('tokens_table')->set(array('enabled' => 0))->where(array('user_id' => $user_id));
$selectString = $sql->getSqlStringForSqlObject($update);
$adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
//Create new hash on table
$insert = $sql->insert('tokens_table')->values(array('user_id' => $user_id, 'hash' => $hash, 'enabled' => true));
$selectString = $sql->getSqlStringForSqlObject($insert);
$adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
}
/**
* Updates table rows with specified data based on a WHERE clause.
*
* @param mixed $table The table to update.
* @param array $bind Column-value pairs.
* @param mixed $where UPDATE WHERE clause(s).
* @return int The number of affected rows.
*/
public function update($table, array $bind, $where = '')
{
$sql = new Sql($this);
$update = $sql->update($this->_getTableName($table));
$update->where($where);
$update->set($bind);
$updateString = $sql->getSqlStringForSqlObject($update);
$result = $this->query($updateString);
return $result->count();
}
public function saveItem($arrParam = null, $options = null)
{
if ($options == null) {
if ($this->getItem($arrParam, array('task' => 'get-item')) == false || $arrParam['id'] == 0) {
$this->tableGateway->insert($arrParam);
} else {
$this->tableGateway->update($arrParam, array('id' => $arrParam['id']));
}
}
if ($options['task'] == 'add') {
$this->tableGateway->insert($arrParam);
}
if ($options['task'] == 'edit') {
$this->tableGateway->update($arrParam, array('id' => $arrParam['id']));
}
if ($options['task'] == 'edit-district') {
$data = array('name' => $arrParam['name']);
$sqlObj = new Sql($this->adapter);
$updateObj = $sqlObj->update('city_district');
$updateObj->set($data);
$updateObj->where('id = ' . $arrParam['id']);
$sqlString = $sqlObj->getSqlStringForSqlObject($updateObj);
$this->adapter->query($sqlString)->execute();
}
if ($options['task'] == 'edit-ward') {
$data = array('name' => $arrParam['name']);
$sqlObj = new Sql($this->adapter);
$updateObj = $sqlObj->update('city_district_ward');
$updateObj->set($data);
$updateObj->where('id = ' . $arrParam['id']);
$sqlString = $sqlObj->getSqlStringForSqlObject($updateObj);
$this->adapter->query($sqlString)->execute();
}
if ($options['task'] == 'add-district') {
$data = array('city_id' => $arrParam['id'], 'name' => $arrParam['name']);
$sqlObj = new Sql($this->adapter);
$insertObj = $sqlObj->insert('city_district');
$insertObj->values($data);
$sqlString = $sqlObj->getSqlStringForSqlObject($insertObj);
$this->adapter->query($sqlString)->execute();
}
if ($options['task'] == 'add-ward') {
$data = array('district_id' => $arrParam['id'], 'name' => $arrParam['name']);
$sqlObj = new Sql($this->adapter);
$insertObj = $sqlObj->insert('city_district_ward');
$insertObj->values($data);
$sqlString = $sqlObj->getSqlStringForSqlObject($insertObj);
$this->adapter->query($sqlString)->execute();
}
}
/**
* @param string $queue_name
* @throws \Exception
* @return \PpModuleMailer\Model\Mailer|boolean return \PpModuleMailer\Model\Mailer or false if queue is empty
*/
public function getWaitingFromQueue($queue_name)
{
try {
$connection = $this->tableGateway->adapter->getDriver()->getConnection();
$connection->execute('SET AUTOCOMMIT = 0');
$connection->beginTransaction();
$sql = new Sql($this->tableGateway->adapter);
$select = $sql->select();
$select->from($this->config['table']);
$select->where(array('status' => 'waiting', 'queue_name' => $queue_name));
$select->order('created');
//TODO add LIMIT 1
$resultset = $connection->execute($select->getSqlString($this->tableGateway->adapter->getPlatform()));
$mailer = $resultset->current();
//Queue is empty
if (!$mailer) {
$connection->commit();
$connection->execute('SET AUTOCOMMIT = 1');
return false;
}
$sql = new Sql($this->tableGateway->adapter);
$update = $sql->update($this->config['table']);
$update->set(array('status' => 'processing'));
$update->where(array('id' => $mailer['id'], 'status' => 'waiting'));
$connection->execute($update->getSqlString($this->tableGateway->adapter->getPlatform()));
$connection->commit();
$connection->execute('SET AUTOCOMMIT = 1');
$mailerObj = new Mailer();
$mailerObj->exchangeArray($mailer);
$mailerObj->status = 'processing';
return $mailerObj;
} catch (\Exception $e) {
$connection = $this->tableGateway->adapter->getDriver()->getConnection();
$connection->rollback();
$connection->execute('SET AUTOCOMMIT = 1');
throw new \Exception($e->getMessage());
}
}
/**
* FUNCAO QUE EXECUTA O COMANDO UPDATE NO BANCO DE DADOS
* @return mixed
* @throws \Exception
*/
public function executeUpdateQuery()
{
/*
* VERIFICA SE OS PARAMETROS FROM E UPDATE (CAMPOS) FORAM DEFINIDOS
*/
if (!empty(self::$varSqlFrom) && !empty(self::$varSqlUpdate)) {
/*
* INICIALIZA A QUERY PELO ZEND_DB
*/
$sql = new Sql($this->getAdapter($this->varConfigAdapter));
$update = $sql->update();
/*
* DEFINE O PARAMETRO FROM DA QUERY
*/
foreach (self::$varSqlFrom as $key => $value) {
if (isset($value['alias']) and !empty($value['alias'])) {
$update->table(array($value['alias'] => $value['table']));
} else {
$update->table($value['table']);
}
}
/*
* VERIFICA E DEFINE O PARAMETRO WHERE DA QUERY
*/
if (!empty(self::$varSqlWhere) and count(self::$varSqlWhere) > 0) {
$arrayWhere = array();
foreach (self::$varSqlWhere as $key => $value) {
$arrayWhere[] = self::$varSqlWhere[$key]['where'];
}
$update->where($arrayWhere);
}
/*
* DEFINE OS PARAMETROS DE ATUALIZACAO NA QUERY
*/
$update->set(self::$varSqlUpdate);
/*
* CASO O DEBUG ESTEJA ATIVO IMPRIME A QUERY (COMANDO) NA TELA
*/
$queryString = $sql->getSqlStringForSqlObject($update);
if (self::$varDebug) {
$this->debugQuery($queryString);
} elseif (self::$varExplan) {
$this->explainQuery($queryString);
}
try {
$statement = $sql->prepareStatementForSqlObject($update);
$statement->execute();
$retorno = true;
} catch (\Zend\Db\Exception $exc) {
$this->closeConnection();
$retorno = false;
throw new \Exception('Nao foi possível executar o comando UPDATE no banco de dados!<br /><br />' . $exc->getMessage(), 500);
}
} else {
$this->closeConnection();
$retorno = false;
throw new \Exception('O comando UPDATE nao foi definido corretamente!', 500);
}
$this->closeConnection();
self::freeMemory();
if ($this->varExecuteLog) {
$this->geraLogSistema($queryString, 'update');
}
return $retorno;
}
/**
* Sets all Users with ID's in array to active and returns all inactive users
* @param array $array
* @return array
*/
public function setUsersActive($array)
{
$sql = new Sql($this->dbAdapter);
$update = $sql->update()->table('tbluser');
//Set active to 1 where id IN(array)
$update->set(array('freigeschaltet' => 1))->where->in('id', $array);
$stmt = $sql->prepareStatementForSqlObject($update);
$result = $stmt->execute();
if ($result instanceof ResultInterface && $result->isQueryResult() && $result->getAffectedRows()) {
//Return all inactive Users after update
return $this->getAllInactiveUsers();
}
}
public function insertSocialMedia(SocialMediaEntity $socMedEnt)
{
$select = $this->sql->select();
$select->where(array("username" => $socMedEnt->getUsername(), "social_id" => $socMedEnt->getSocialId()));
$result = $this->hydrateResult(new SocialMediaEntity(), $select);
if ($result->count() == 0) {
$hydrator = new ClassMethods();
$data = $hydrator->extract($socMedEnt);
$action = $this->sql->insert();
$action->values($data);
$statement = $this->sql->prepareStatementForSqlObject($action);
// echo "<hr>"; echo $action->getSqlString($this->dbAdapter->getPlatform()); echo "<hr>asdf";
$statement->execute();
$sql = new Sql($this->dbAdapter);
$sql->setTable('blvd');
$update = $sql->update();
$update->set(array("last_social_media_datetime" => $data['date_created']))->where(array("id" => $socMedEnt->getBlvdId()));
$statement = $sql->prepareStatementForSqlObject($update);
$r = $statement->execute();
echo $action->getSqlString($this->dbAdapter->getPlatform());
} elseif (false) {
$hydrator = new ClassMethods();
$data = $hydrator->extract($socMedEnt);
$update = $this->sql->update();
$update->set(array("date_created" => $data['date_created']))->where(array("username" => $socMedEnt->getUsername(), "social_id" => $socMedEnt->getSocialId()));
$statement = $this->sql->prepareStatementForSqlObject($update);
$r = $statement->execute();
//echo "<hr>"; echo $update->getSqlString($this->dbAdapter->getPlatform()); echo "<hr>asdf";
return $r;
}
return false;
}
public function save($sm, $recid, $record, $where, $original = array(), $lastid = array(), $remove = array(), $centais = array(), $modifier = array())
{
/* $sm - in Controller $this->getServiceLocator();
* $recid - record ID in table
* $record - array of values to save. key: <table_name>-<field_name> value: <value>
* $where - where to save. key: <table_name>-<field_name> value: <key in $record array>
* $original - array of original form values. To check for duplicates where duplicates not alowed. Check performed if value in $original not equal to value in $record.
* key: <table_name_field_name>-<field_name> value: <value>
* $lastid - "prime record on insert" array. key: <where to use table>-<where to use field> value: <source table>-<source field>
* ! in $record's array "source" tables fields must be on top
* $remove - array of fields to delete and insert on edit action. key: <table_name>-<field_name> value: source of field value in form <table_name>-<field_name>
* $centais - array of fields to multiply by fixed values. key: <field_name> value: <fixed value>
* $modifier - if it not empty, 'modifier' and 'modified' fields are added to array of records
*
*/
$adapter = $sm->get('Zend\\Db\\Adapter\\Adapter');
$sql = new Sql($adapter);
$config = $sm->get('Config');
$tables = array();
foreach ($record as $key => $value) {
$parts = explode('-', $key);
if (isset($parts[1])) {
if (is_array($value) && !empty($value)) {
//-- if it 'enum' or 'list' field
if (isset($value[0]['id'])) {
//-- so it's 'enum'
foreach ($value as $ekey => $evalue) {
$tables[$parts[0]][$parts[1]][] = $evalue['id'];
}
} else {
//-- so it's 'list'
$tables[$parts[0]][$parts[1]] = $value['id'];
}
} else {
if (isset($centais[$parts[1]])) {
//--- if in cents array
(int) ($value = $value * $centais[$parts[1]]);
}
$tables[$parts[0]][$parts[1]] = $value;
}
}
}
if (!is_array($recid)) {
$r = array();
foreach ($tables as $key => $value) {
$r[$key] = $recid;
}
$recid = $r;
}
$removes = array();
foreach ($remove as $key => $value) {
$parts = explode('-', $key);
$vparts = explode('-', $value);
if (isset($parts[1]) && isset($vparts[1])) {
$removes[$parts[0]][$parts[1]] = $tables[$vparts[0]][$vparts[1]];
}
}
$lastids = array();
foreach ($lastid as $key => $value) {
$parts = explode('-', $key);
$vparts = explode('-', $value);
if (isset($parts[0]) && isset($vparts[1])) {
$lastids[$vparts[0]][$vparts[1]][$parts[0]] = $parts[1];
}
}
//-- to ensure that 'lastids' table in tables array goes first
if (!empty($lastids)) {
$tmp = array();
foreach ($lastids as $key => $value) {
if (array_key_exists($key, $tables)) {
$tmp[$key] = $tables[$key];
unset($tables[$key]);
}
}
foreach ($tables as $key => $value) {
$tmp[$key] = $value;
}
$tables = $tmp;
}
$wheres_orig = array();
foreach ($original as $key => $value) {
$parts = explode('-', $key);
if (isset($parts[0]) && isset($parts[1])) {
if (is_array($value)) {
//-- if that's a field of type 'list'
$value = $value['id'];
}
$wheres_orig[$parts[0]][$parts[1]] = $value;
}
}
$wheres_new = array();
foreach ($wheres_orig as $table => $array) {
foreach ($array as $key => $value) {
if (isset($tables[$table][$key])) {
$wheres_new[$table][$key] = $tables[$table][$key];
}
}
}
$wheres = array();
foreach ($where as $key => $value) {
$parts = explode('-', $key);
$vparts = explode('-', $value);
if (isset($parts[0]) && isset($parts[1]) && isset($tables[$vparts[0]][$vparts[1]])) {
$wheres[$parts[0]][$parts[1]] = $tables[$vparts[0]][$vparts[1]];
if (!empty($recid[$key])) {
unset($tables[$vparts[0]][$vparts[1]]);
}
}
}
$affectedRows = 0;
$debug_mode = false;
// $debug_mode = true; //-- uncoment if you just want to see queries in console; no writes to db performed
if ($debug_mode) {
echo "recid:\n";
print_r($recid);
echo "tables:\n";
print_r($tables);
echo "wheres:\n";
print_r($wheres);
echo "removes:\n";
print_r($removes);
echo "wheres_orig:\n";
print_r($wheres_orig);
echo "wheres_new:\n";
print_r($wheres_new);
echo "lastids:\n";
print_r($lastids);
// exit;
}
$primeids = array();
foreach ($tables as $table => $set) {
if (!empty($this->table_filter)) {
if (!isset($this->table_filter[$table])) {
continue;
}
}
if (in_array($table, $config['ACL_TABLES'])) {
$table_ident = new TableIdentifier($table, $config['ACL_DB']);
} else {
$table_ident = $table;
}
//-- non standart modifier and modified field names handling
if (!empty($modifier)) {
if (is_array($modifier)) {
if (isset($modifier[$table]['modifier_name'])) {
$set[$modifier[$table]['modifier_name']] = $modifier[$table]['modifier'];
}
if (isset($modifier[$table]['modified_name'])) {
$set[$modifier[$table]['modified_name']] = new Expression('NOW()');
}
} else {
$modifier = $sm->get('Zend\\Authentication\\AuthenticationService')->getIdentity()->id;
$set['modifier'] = $modifier;
$set['modified'] = new Expression('NOW()');
}
}
if (!$recid[$table] || isset($wheres_new[$table])) {
//-- so it is insert or edit with possibly changed required unicue fields
if (isset($wheres_orig[$table]) && $wheres_orig[$table] != $wheres_new[$table]) {
if ($this->duplicated($sm, $table_ident, $wheres_new[$table])) {
end($wheres_new[$table]);
$dataset = array('status' => 'success', 'validate' => array($table . '-' . key($wheres_new[$table]) => _('Record exists')));
return $dataset;
}
}
if ($recid[$table]) {
//-- if that is edit anyway
$dbobj = $sql->update($table_ident);
$dbobj->set($set);
foreach ($wheres[$table] as $where => $value) {
if (isset($wheres_orig[$table][$where])) {
$dbobj->where(array($where => $wheres_orig[$table][$where]));
} else {
$dbobj->where(array($where => $value));
}
}
} else {
//-- if to insert
$dbobj = $sql->insert($table_ident);
if (isset($primeids[$table])) {
$current = current($set);
foreach ($primeids[$table] as $key => $value) {
if (empty($value) && !empty($tables[$table][$key])) {
$value = $tables[$table][$key];
}
if (is_array($current)) {
foreach ($current as $cvalue) {
$set = array(key($set) => $cvalue, $key => $value);
$dbobj->values($set);
$query = $sql->getSqlStringForSqlObject($dbobj);
if (!$debug_mode) {
$result = $adapter->query($query, $adapter::QUERY_MODE_EXECUTE);
} else {
echo "*1* {$query}\n";
}
}
} else {
if (!array_filter($set)) {
continue;
}
$set[$key] = $value;
$dbobj->values($set);
$query = $sql->getSqlStringForSqlObject($dbobj);
if (!$debug_mode) {
$result = $adapter->query($query, $adapter::QUERY_MODE_EXECUTE);
} else {
echo "*2* {$query}\n";
}
}
}
continue;
}
$dbobj->values($set);
}
$query = $sql->getSqlStringForSqlObject($dbobj);
if (!$debug_mode) {
$result = $adapter->query($query, $adapter::QUERY_MODE_EXECUTE);
$affectedRows += $result->count();
} else {
echo "*3* {$query}\n";
}
if (isset($lastids[$table])) {
$lastId = $adapter->getDriver()->getLastGeneratedValue();
foreach ($lastids[$table] as $key => $value) {
foreach ($value as $key => $value) {
$primeids[$key][$value] = $lastId;
}
}
}
if (!$debug_mode) {
$affectedRows += $result->count();
}
} else {
//-- edit
if (isset($removes[$table])) {
//-- if have to delete existing records
$adapter->getDriver()->getConnection()->beginTransaction();
$insert_set = array();
$delete = $sql->delete($table_ident);
foreach ($removes[$table] as $where => $value) {
$insert_set[$where] = $value;
$delete->where("{$where} = '{$value}'");
}
$query = $sql->getSqlStringForSqlObject($delete);
if (!$debug_mode) {
$result = $adapter->query($query, $adapter::QUERY_MODE_EXECUTE);
$affectedRows += $result->count();
} else {
echo "*4* {$query}\n";
}
//-- so that's insert
foreach ($tables[$table] as $key => $set) {
if (empty($set)) {
continue;
}
if (!is_array($set)) {
//-- not an enum type field
$dbobj = $sql->insert($table_ident);
$dbobj->values(array_merge($tables[$table], $insert_set));
$query = $sql->getSqlStringForSqlObject($dbobj);
if (!$debug_mode) {
$result = $adapter->query($query, $adapter::QUERY_MODE_EXECUTE);
} else {
echo "*5* {$query}\n";
}
break;
} else {
foreach ($set as $ivalue) {
$dbobj = $sql->insert($table_ident);
$dbobj->values(array_merge(array($key => $ivalue), $insert_set));
$query = $sql->getSqlStringForSqlObject($dbobj);
if (!$debug_mode) {
$result = $adapter->query($query, $adapter::QUERY_MODE_EXECUTE);
} else {
echo "*6* {$query}\n";
}
}
}
}
$adapter->getDriver()->getConnection()->commit();
continue;
}
$update = $sql->update($table_ident);
$update->set($set);
foreach ($wheres[$table] as $where => $value) {
$update->where(array($where => $value));
}
$query = $sql->getSqlStringForSqlObject($update);
if (!$debug_mode) {
$result = $adapter->query($query, $adapter::QUERY_MODE_EXECUTE);
$affectedRows += $result->count();
} else {
echo "*7* {$query}\n";
}
}
}
$dataset = array('status' => 'success', 'message' => _('Affected rows: ') . $affectedRows);
if (!empty($lastId)) {
$dataset['last_id'] = $lastId;
}
return $dataset;
}
public function saveItem($arrParam = null, $options = null)
{
if ($options['task'] == 'add') {
$this->tableGateway->insert($arrParam);
return $this->tableGateway->lastInsertValue;
}
if ($options['task'] == 'edit') {
$this->tableGateway->update($arrParam, array('id' => $arrParam['id']));
}
if ($options['task'] == 'save-history-transaction') {
$sqlObj = new \Zend\Db\Sql\Sql($this->adapter);
$insertObj = $sqlObj->insert('transaction_history');
$insertObj->values($arrParam);
$sqlString = $sqlObj->getSqlStringForSqlObject($insertObj);
$this->adapter->query($sqlString)->execute();
}
if ($options['task'] == 'update-info-user') {
$sqlObj = new \Zend\Db\Sql\Sql($this->adapter);
$updateObj = $sqlObj->update('users');
$updateObj->set($arrParam);
$updateObj->where('id = ' . $arrParam['id']);
$sqlString = $sqlObj->getSqlStringForSqlObject($updateObj);
$this->adapter->query($sqlString)->execute();
}
if ($options['task'] == 'multi-status') {
if (!empty($arrParam)) {
foreach ($arrParam['id'] as $key => $value) {
if ($arrParam['type'] == 'multi-active') {
$status = 1;
}
if ($arrParam['type'] == 'multi-in-active') {
$status = 0;
}
$data = array('id' => $value, 'status' => $status);
$this->tableGateway->update($data, array('id' => $value));
}
}
}
}
/**
* Set Winner Column to $userID
* @param type $userID
* @param type $matchID
*/
protected function setMatchWinner($userID, $matchID)
{
$sql = new Sql($this->dbAdapter);
$where = new \Zend\Db\Sql\Where();
$where->nest()->equalTo('tblmatch.matchID', $matchID)->unnest();
$update = $sql->update('tblmatch')->where($where);
$update->set(array('Winner' => $userID));
$stmt = $sql->prepareStatementForSqlObject($update);
$stmt->execute();
}
public function updateViews($resourceId)
{
$sql = new Sql($this->getAdapter());
$update = $sql->update();
$update->table('resource');
$update->set(array('views' => new Expression("views + 1")));
$update->where->equalTo('id', $resourceId);
$statement = $sql->prepareStatementForSqlObject($update);
$result = $statement->execute();
return $result->getAffectedRows();
}
/**
* Add db state to migration
*
* @param string|null $module
* @param string| $migration
*/
protected function updateDbState($module, $migration)
{
$db = new Database($this->db, array('blacklist' => $this->options['migrationsSchemaTable']));
$sql = new Sql($this->db);
$update = $sql->update($this->getMigrationsSchemaTable());
$where = new Where();
$where->equalTo('module', $module)->and->equalTo('migration', $migration);
$update->where($where);
$update->set(array('state' => $db->toString()));
$selectString = $sql->getSqlStringForSqlObject($update);
$this->db->query($selectString, Adapter::QUERY_MODE_EXECUTE);
}
/**
* Write session data
*
* @param string $id
* @param string $data
* @return boolean
*/
public function write($id, $data)
{
$saveSession = false;
switch (self::$writeMode) {
case self::WRITE_ALWAYS:
$saveSession = true;
break;
case self::WRITE_IF_DATA:
$saveSession = $data || !empty($this->existingSession['data']);
break;
case self::WRITE_IF_CHANGED:
$saveSession = $data != $this->existingSession['data'];
break;
case self::WRITE_NEVER:
// do nothing
break;
}
// don't save the session data if _REQUEST_ACCESS_TIME (which ZF adds by
// defaut) is the only thing in it. Hopefully this won't be required in
// ZF 2.3. See also ZF issue #4332
if (!empty($data)) {
$dataParts = explode('|', $data);
if (count($dataParts) == 2) {
$realData = unserialize($dataParts[1]);
if (is_array($realData) && count($realData) == 1 && isset($realData['_REQUEST_ACCESS_TIME'])) {
if (self::$writeMode == self::WRITE_IF_DATA) {
$saveSession = false;
}
}
}
}
if ($saveSession) {
$ip = isset($_SERVER['REMOTE_ADDR']) ? $_SERVER['REMOTE_ADDR'] : null;
// FIXME
$lifetime = max(3600, ini_get('session.cookie_lifetime'));
if (!empty($this->existingSession)) {
$existingLifetime = strtotime($this->existingSession['expires']) - strtotime($this->existingSession['lastModified']);
if ($lifetime < $existingLifetime) {
$lifetime = $existingLifetime;
}
}
$expires = date('Y-m-d H:i:s', time() + $lifetime);
$sessionData = array('lastModified' => date('Y-m-d H:i:s'), 'expires' => $expires, 'ip' => new DbExpression('INET_ATON(?)', array($ip)));
if (!empty($this->existingSession)) {
if ($data != $this->existingSession['data']) {
$sessionData['data'] = $data;
}
if ($id == $this->existingSession['sessionID']) {
$sql = new Sql($this->getDb());
$update = $sql->update('sessions');
$update->set($sessionData)->where(array('sessionID' => $this->existingSession['sessionID']));
$statement = $sql->prepareStatementForSqlObject($update);
$statement->execute();
} else {
// create a session with the new sessionID
$sessionData['sessionID'] = $id;
$sql = new Sql($this->getDb());
$insert = $sql->insert('sessions');
$insert->values($sessionData);
$statement = $sql->prepareStatementForSqlObject($insert);
$statement->execute();
// drop the expiry date on the old one - not sure why this isn't required?
}
} else {
$sessionData['sessionID'] = $id;
$sessionData['data'] = $data;
$sql = new Sql($this->getDb());
$insert = $sql->insert('sessions');
$insert->values($sessionData);
$statement = $sql->prepareStatementForSqlObject($insert);
$statement->execute();
}
}
return true;
}
/**
* Store instance data in the database
*
* @throws \Thinkscape\ActiveRecord\Exception\DatabaseException
* @throws \Thinkscape\ActiveRecord\Exception\RuntimeException
* @return void
*/
public function save()
{
$db = $this->getDb();
$sql = new Sql($db);
if (!$this->id) {
// perform an INSERT operation
// Get the data
$updateData = $this->collectUpdateData();
// Prepare and execute INSERT
$insert = $sql->insert($this->getDbTable())->values($updateData);
$db->query($insert->getSqlString($db->getPlatform()), $db::QUERY_MODE_EXECUTE);
// Remember ID
if (!($this->id = $db->getDriver()->getLastGeneratedValue())) {
throw new Exception\DatabaseException(sprintf('Unable to retrieve INSERT id when trying to persist %s', get_class($this)));
}
// Store in registry
Core::storeInstanceInRegistry(get_called_class(), $this->id, $this);
} else {
// perform an UPDATE operation
if (!$this->isLoaded) {
throw new Exception\RuntimeException('Attempt to save() a record that has not been loaded');
}
// Get the data
$updateData = $this->collectUpdateData();
if (!count($updateData)) {
return;
// there is nothing to update
}
// Prepare and execute UPDATE
$insert = $sql->update($this->getDbTable())->set($updateData);
$result = $db->query($insert->getSqlString($db->getPlatform()))->execute();
// Check if successful
if ($result->getAffectedRows() == 0) {
throw new Exception\DatabaseException(sprintf('Unable to update record #%s in table %s (class %s)', $this->id, $this->getDbTable(), get_class($this)));
}
}
}
public function update($user)
{
$adapter = $this->sm->get('Zend\\Db\\Adapter\\Adapter');
$sql = new Sql($adapter, 'users_table');
$filter = array('user_id' => $user->user_id);
$fields = $user->toArray();
unset($fields['user_id']);
unset($fields['email']);
$update = $sql->update()->where($filter)->set($fields);
$sqlString = $sql->getSqlStringForSqlObject($update);
$results = $adapter->query($sqlString, $adapter::QUERY_MODE_EXECUTE);
return $results;
}
public function saveItem($arrParam = null, $options = null)
{
if ($options == null) {
if ($this->getItem($arrParam, array('task' => 'get-item')) == false || $arrParam['id'] == 0) {
$this->tableGateway->insert($arrParam);
} else {
$this->tableGateway->update($arrParam, array('id' => $arrParam['id']));
}
}
if ($options['task'] == 'add') {
$this->tableGateway->insert($arrParam);
}
if ($options['task'] == 'edit') {
$this->tableGateway->update($arrParam, array('id' => $arrParam['id']));
}
if ($options['task'] == 'setPermiss') {
$data = array('permission_id' => $arrParam['permission_id']);
$sqlObj = new Sql($this->adapter);
$updateObj = $sqlObj->update('user_group');
$updateObj->set($data);
$updateObj->where('id = ' . $arrParam['id']);
$sqlString = $sqlObj->getSqlStringForSqlObject($updateObj);
$this->adapter->query($sqlString)->execute();
}
}
public function saveItem($arrParam = null, $options = null)
{
if ($options == null) {
if ($this->getItem($arrParam, array('task' => 'get-item')) == false || $arrParam['id'] == 0) {
$this->tableGateway->insert($arrParam);
} else {
$this->tableGateway->update($arrParam, array('id' => $arrParam['id']));
}
}
if ($options['task'] == 'add') {
$this->tableGateway->insert($arrParam);
}
if ($options['task'] == 'ban') {
//add ban
$data = array('user_id' => $arrParam['user_id'], 'ip' => $arrParam['ip'], 'nguyennhan' => $arrParam['nguyennhan']);
$sqlObj = new Sql($this->adapter);
$insertObj = $sqlObj->insert('user_ban');
$insertObj->values($data);
$sqlString = $sqlObj->getSqlStringForSqlObject($insertObj);
$this->adapter->query($sqlString)->execute();
//update user
$dataUser = array('banned' => $arrParam['banned']);
$sqlObj = new Sql($this->adapter);
$updateObj = $sqlObj->update('users');
$updateObj->set($dataUser);
$updateObj->where('id = ' . $arrParam['user_id']);
$sqlString = $sqlObj->getSqlStringForSqlObject($updateObj);
$this->adapter->query($sqlString)->execute();
}
if ($options['task'] == 'un-ban') {
//update user
$dataUser = array('banned' => 'false');
$sqlObj = new Sql($this->adapter);
$updateObj = $sqlObj->update('users');
$updateObj->set($dataUser);
$updateObj->where('id = ' . $arrParam['id']);
$sqlString = $sqlObj->getSqlStringForSqlObject($updateObj);
$this->adapter->query($sqlString)->execute();
}
if ($options['task'] == 'edit') {
$this->tableGateway->update($arrParam, array('id' => $arrParam['id']));
}
if ($options['task'] == 'multi-status') {
if (!empty($arrParam)) {
foreach ($arrParam['id'] as $key => $value) {
if ($arrParam['type'] == 'multi-active') {
$status = 1;
}
if ($arrParam['type'] == 'multi-in-active') {
$status = 0;
}
$data = array('id' => $value, 'status' => $status);
$this->tableGateway->update($data, array('id' => $value));
}
}
}
}
