/**
* Update table rows with the given data, optionally limited by using a filter
*
* Pass an array with a column name (the same as in $bind) and a PDO::PARAM_* constant as value
* as fourth parameter $types to define a different type than string for a particular column.
*
* @param string $table
* @param array $bind
* @param Filter $filter
* @param array $types
*
* @return int The number of affected rows
*/
public function update($table, array $bind, Filter $filter = null, array $types = array())
{
$set = array();
foreach ($bind as $column => $_) {
$set[] = $column . ' = :' . $column;
}
$sql = 'UPDATE ' . $table . ' SET ' . join(', ', $set) . ($filter ? ' WHERE ' . $this->renderFilter($filter) : '');
$statement = $this->dbAdapter->prepare($sql);
foreach ($bind as $column => $value) {
$type = isset($types[$column]) ? $types[$column] : PDO::PARAM_STR;
$statement->bindValue(':' . $column, $value, $type);
}
$statement->execute();
return $statement->rowCount();
}
/**
* Updates the lastEnd values of all entries
*
* @return void
*/
private function _updateLastEnd()
{
if ($this->_debug) {
Phprojekt::getInstance()->getLog()->debug('_updateLastEnd');
}
$this->_db->query('UPDATE calendar2 SET last_end = end WHERE rrule = ""');
$entries = $this->_db->select()->from('calendar2', array('id', 'start', 'end', 'rrule'))->where('rrule != ""')->query()->fetchAll();
$update = $this->_db->prepare('UPDATE calendar2 SET last_end = :last_end WHERE id = :id');
$x = 0;
foreach ($entries as $key => $e) {
if ($this->_debug && ++$x % 100 == 0) {
Phprojekt::getInstance()->getLog()->debug($x);
}
$start = new Datetime($e['start']);
$end = new Datetime($e['end']);
$duration = $start->diff($end);
$helper = new Calendar2_Helper_Rrule($start, $duration, $e['rrule']);
$update->execute(array(':last_end' => $helper->getUpperTimeBoundary()->format('Y-m-d H:i:s'), ':id' => $e['id']));
}
}
/**
* Prepare a statement and return a PDOStatement-like object.
*
* @param string|Zend_Db_Select $sql SQL query
* @return Zend_Db_Statement|PDOStatement
*/
public function prepare($sql)
{
return $this->_adapter->prepare($sql);
}
public static function printQuery(Zend_Db_Adapter_Abstract $database, $query)
{
try {
$s = '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">';
$s .= '<html>';
$s .= '<head>';
$s .= '<meta http-equiv="content-type" content="text/html; charset=utf-8" />';
$s .= '</head>';
$s .= '<body>';
$statement = $database->prepare($query);
$statement->execute();
$result = $statement->fetchAll(Zend_Db::FETCH_ASSOC);
$s .= "<table width=600 border=1>";
$s .= "<tr><th colspan=\"2\" align=\"left\" bgcolor=\"#eeeeee\"><h2>Database - Select Query</h2></td></tr>";
$s .= "<tr><th valign=\"top\" align=\"left\" bgcolor=\"#eeeeee\"><nobr>Database Class</nobr></nobr></td><td>" . get_class($database) . " </td></tr>";
$s .= "<tr><th valign=\"top\" align=\"left\" bgcolor=\"#eeeeee\"><nobr>Statement Class</nobr></td><td>" . get_class($statement) . " </td></tr>";
$s .= "<tr><th valign=\"top\" align=\"left\" bgcolor=\"#eeeeee\"><nobr>Rows</nobr></td><td>" . sizeof($result) . " </td></tr>";
#$s .= "<tr><th valign=\"top\" align=\"left\" bgcolor=\"#eeeeee\"><nobr>Columns</nobr></td><td>".$numCols." </td></tr>";
$s .= "<tr><th valign=\"top\" align=\"left\" bgcolor=\"#eeeeee\"><nobr>Query</nobr></td><td>" . htmlentities($query) . " </td></tr>";
$s .= "</table><br />";
$count = 0;
if (sizeof($result) > 0) {
$s .= "<table border=1>";
foreach ($result as $row) {
if ($count == 0) {
# column index
$colCount = 0;
$s .= "<tr>";
foreach ($row as $k => $v) {
$s .= '<th align="left" bgcolor="#eeeeee">' . $colCount++ . '</th>';
}
$s .= "</tr>";
# column names
$s .= "<tr>";
foreach ($row as $k => $v) {
$s .= '<th align="left" bgcolor="#eeeeee">' . htmlentities($k) . '</th>';
}
$s .= "</tr>";
}
$s .= "<tr>";
foreach ($row as $k => $v) {
$v = mb_substr($v, 0, 100);
$s .= '<td>' . htmlentities($v) . ' </td>';
}
$s .= "</tr>";
$count++;
}
$s .= "</table><br />";
}
# EXPLAIN
$statement = $database->prepare('EXPLAIN ' . $query);
$statement->execute();
$result = $statement->fetchAll(Zend_Db::FETCH_ASSOC);
$count = 0;
if (sizeof($result) > 0) {
$s .= "<table border=1>";
foreach ($result as $row) {
if ($count == 0) {
# column names
$s .= "<tr>";
foreach ($row as $k => $v) {
$s .= '<th align="left" bgcolor="#eeeeee">' . htmlentities($k) . '</th>';
}
$s .= "</tr>";
}
$s .= "<tr>";
foreach ($row as $k => $v) {
$v = mb_substr($v, 0, 100);
$s .= '<td>' . htmlentities($v) . ' </td>';
}
$s .= "</tr>";
$count++;
}
$s .= "</table><br />";
}
$s .= '</body>';
$s .= '</html>';
print $s;
} catch (Exception $exception) {
$s = "<table width=600 border=1>";
$s .= "<tr><th colspan=\"2\" align=\"left\" bgcolor=\"#eeeeee\"><h2>Database - Select Query</h2></td></tr>";
$s .= "<tr><th valign=\"top\" align=\"left\" bgcolor=\"#eeeeee\"><nobr>Database Class</nobr></td><td>" . get_class($database) . " </td></tr>";
#$s .= "<tr><th valign=\"top\" align=\"left\" bgcolor=\"#eeeeee\"><nobr>Statement Class</nobr></td><td>".get_class($result)." </td></tr>";
$s .= "<tr><th valign=\"top\" align=\"left\" bgcolor=\"#eeeeee\"><nobr>Code</nobr></td><td>" . $exception->getCode() . " </td></tr>";
$s .= "<tr><th valign=\"top\" align=\"left\" bgcolor=\"#eeeeee\"><nobr>Error</nobr></td><td>" . $exception->getMessage() . " </td></tr>";
#$s .= "<tr><th valign=\"top\" align=\"left\" bgcolor=\"#eeeeee\"><nobr>Native Code</nobr></td><td>".$this->getNativeErrorCode()." </td></tr>";
#$s .= "<tr><th valign=\"top\" align=\"left\" bgcolor=\"#eeeeee\"><nobr>Native Error</nobr></td><td>".htmlentities($this->getNativeError())." </td></tr>";
$s .= "<tr><th valign=\"top\" align=\"left\" bgcolor=\"#eeeeee\"><nobr>Query</nobr></td><td>" . htmlentities($query) . " </td></tr>";
$s .= "</table><br />";
print $s;
}
}
/**
*
* @param Zend_Db_Adapter_Abstract $db
* @param string $printerName
* @param string $ipServer
* @return boolean
* @throws ZendT_Exception_Error
*/
private function _printDocuments($db, $printerName, $ipServer)
{
$sql = "declare\n message varchar2(500);\n begin\n wsapi.ws_printserver_pkg.print(pprintername => :printername,\n pipserver => :ipserver);\n :erro := 0;\n :message := NULL;\n exception when others then\n :erro := 1;\n :message := sqlerrm;\n end;";
$stmt = $db->prepare($sql);
$stmt->bindParam('printername', $printerName);
$stmt->bindParam('ipserver', $ipServer);
$stmt->bindValue('erro', $erro);
$stmt->bindValue('message', $message);
$stmt->execute();
if ($erro) {
throw new ZendT_Exception_Error($message);
}
return true;
}
