$v){ if($v != null) $where2[] = "$k = '$v'"; else $where2[] = "$k IS NULL"; } $where = implode(" AND " ,$where2); try{ $db = Zend_Db_Table::getDefaultAdapter(); $select = $this->getDbTable()->select() ->from($this->_dbTable) ->where($where); if(is_array($order)){ foreach ($order as $k => $v) $a[] = "$k $v"; $select->order($a); } $stmt = $select->query(); $result = $stmt->fetchAll(); if(!$array){ $entries = array(); if(count($result) > 0) { foreach ($result as $row) { $entry = new Application_Model_Event($row); $entry->setID($row['eventID']); $entries[] = $entry; } } return $entries; }else{ return $result; } }catch (Zend_Exception $e) { echo "Error message 2: " . $e->getMessage() . "\n"; } } public function setDbTable($dbTable) { if (is_string($dbTable)) { $dbTable = new $dbTable(); } if (!$dbTable instanceof Zend_Db_Table_Abstract) { throw new Exception('Invalid table data gateway provided'); } $this->_dbTable = $dbTable; return $this; } public function getDbTable() { if (null === $this->_dbTable) { $this->setDbTable('Application_Model_DbTable_Event'); } return $this->_dbTable; } public function save(Application_Model_Event $event) { $data = array('eventID'=> $event->getID() ,'category'=> $event->getCategory() ,'title'=> $event->getTitle(), 'pbs_membershipID'=> $event->getPbs_membershipID(),'end'=> $event->getEnd() ,'immediate'=> $event->getImmediate() ,'note'=> $event->getNote() ,'participants'=> $event->getParticipants() ,'pbs_bootosID'=> $event->getPbs_bootosID(),'pbs_configID'=> $event->getPbs_configID(),'pbs_poolID'=> $event->getPbs_poolID(),'repeat'=> $event->getRepeat(),'repeattype'=> $event->getRepeattype(),'repeatend'=> $event->getRepeatend(),'repeatdate'=> $event->getRepeatdate(),'repeatings'=> $event->getRepeatings(),'start'=> $event->getStart(), 'pbs_bootmenuID'=> $event->getPbs_bootmenuID(),'pbs_filterID'=> $event->getPbs_filterID(), 'running' => $event->getRunning(), 'runningtype' => $event->getRunningtype(), 'created' => $event->getCreated(), 'force' => $event->getForce() ); if (null === ($id = $event->getID()) ) { unset($data['eventID']); return $this->getDbTable()->insert($data); } else { $this->getDbTable()->update($data, array('eventID = ?' => $id)); } } public function delete(Application_Model_Event $event) { if (null === ($id = $event->getID()) ) { return; } else { $this->getDbTable()->delete(array('eventID = ?' => $id)); } } public function find($id, Application_Model_Event $event = null) { $result = $this->getDbTable()->find($id); if (0 == count($result)) { return; } $row = $result->current(); if($event == null){ $event = new Application_Model_Event(); } $event ->setID($row->eventID) ->setCategory($row->category) ->setTitle($row->title) ->setPbs_membershipID($row->pbs_membershipID) ->setEnd($row->end) ->setImmediate($row->immediate) ->setNote($row->note) ->setParticipants($row->participants) ->setPbs_bootosID($row->pbs_bootosID) ->setPbs_configID($row->pbs_configID) ->setPbs_poolID($row->pbs_poolID) ->setPbs_filterID($row->pbs_filterID) ->setPbs_bootmenuID($row->pbs_bootmenuID) ->setRepeat($row->repeat) ->setRepeattype($row->repeattype) ->setRepeatend($row->repeatend) ->setRepeatdate($row->repeatdate) ->setRepeatings($row->repeatings) ->setStart($row->start) ->setRunning($row->running) ->setCreated($row->created) ->setRunningtype($row->runningtype) ->setForce($row->force); } public function fetchAll() { $resultSet = $this->getDbTable()->fetchAll(); $entries = array(); foreach ($resultSet as $row) { $entry = new Application_Model_Event(); $entry ->setID($row->eventID) ->setCategory($row->category) ->setTitle($row->title) ->setPbs_membershipID($row->pbs_membershipID) ->setEnd($row->end) ->setImmediate($row->immediate) ->setNote($row->note) ->setParticipants($row->participants) ->setPbs_bootosID($row->pbs_bootosID) ->setPbs_configID($row->pbs_configID) ->setPbs_poolID($row->pbs_poolID) ->setPbs_filterID($row->pbs_filterID) ->setPbs_bootmenuID($row->pbs_bootmenuID) ->setRepeat($row->repeat) ->setRepeattype($row->repeattype) ->setRepeatend($row->repeatend) ->setRepeatdate($row->repeatdate) ->setRepeatings($row->repeatings) ->setStart($row->start) ->setRunning($row->running) ->setCreated($row->created) ->setRunningtype($row->runningtype) ->setForce($row->force); $entries[$row->eventID] = $entry; } return $entries; } public function compare(Application_Model_Event $v1,Application_Model_Event $v2){ $vv1 = $v1->toArray(); $vv2 = $v2->toArray(); return array_diff_assoc($vv1,$vv2); } public function getCategoryBarCount($poolID) { $db = Zend_Db_Table::getDefaultAdapter(); if($poolID != 'all'){ $select = $this->getDbTable()->select() ->setIntegrityCheck(false) ->from(array('pce' => 'poolctrl_event'), array('count' => 'COUNT(*)', 'category' => 'pcec.title')) ->join(array('pcec' => 'poolctrl_eventcategory'), 'pce.category = pcec.eventcategoryID', array()) ->where('pce.pbs_poolID = ?', $poolID) ->group('pce.category') ->order('pce.category ASC');} else { $select = $this->getDbTable()->select() ->setIntegrityCheck(false) ->from(array('pce' => 'poolctrl_event'), array('count' => 'COUNT(*)', 'category' => 'pcec.title')) ->join(array('pcec' => 'poolctrl_eventcategory'), 'pce.category = pcec.eventcategoryID', array()) ->group('pce.category') ->order('pce.category ASC'); } $stmt = $db->query($select); $result = $stmt->fetchAll(PDO::FETCH_NUM); $ret = null; foreach ($result as $r) { $ret[] = array((int)$r[0], $r[1]); } return array($ret); } public function getCategoryPieCount($poolID) { $db = Zend_Db_Table::getDefaultAdapter(); if($poolID != 'all'){ $select = $this->getDbTable()->select() ->setIntegrityCheck(false) ->from(array('pce' => 'poolctrl_event'), array('category' => 'pcec.title', 'count' => 'COUNT(*)')) ->join(array('pcec' => 'poolctrl_eventcategory'), 'pce.category = pcec.eventcategoryID', array()) ->where('pce.pbs_poolID = ?', $poolID) ->group('pce.category') ->order('pce.category ASC');} else { $select = $this->getDbTable()->select() ->setIntegrityCheck(false) ->from(array('pce' => 'poolctrl_event'), array('category' => 'pcec.title', 'count' => 'COUNT(*)')) ->join(array('pcec' => 'poolctrl_eventcategory'), 'pce.category = pcec.eventcategoryID', array()) ->group('pce.category') ->order('pce.category ASC'); } $stmt = $db->query($select); $result = $stmt->fetchAll(PDO::FETCH_NUM); $ret = null; foreach ($result as $r) { $ret[] = array($r[0], (int)$r[1]); } return array($ret); } public function getRunningBarCount($poolID) { $db = Zend_Db_Table::getDefaultAdapter(); if($poolID != 'all'){ $select = $this->getDbTable()->select() ->setIntegrityCheck(false) ->from(array('pce' => 'poolctrl_event'), array('count1' => 'SUM(IF(pce.running=1,1,0))', 'count0' => 'SUM(IF(pce.running=0,1,0))') ) ->join(array('pcec' => 'poolctrl_eventcategory'), 'pce.category = pcec.eventcategoryID', array()) ->where('pce.pbs_poolID = ?', $poolID);} else { $select = $this->getDbTable()->select() ->setIntegrityCheck(false) ->from(array('pce' => 'poolctrl_event'), array('over' => 'SUM(IF(pce.running=1,1,0))', 'not_over' => 'SUM(IF(pce.running=1,0,1))') ) ->join(array('pcec' => 'poolctrl_eventcategory'), 'pce.category = pcec.eventcategoryID', array()); } $stmt = $db->query($select); $result = $stmt->fetchAll(PDO::FETCH_NUM); foreach ($result as $r) { $ret1 = array((int)$r[0],'over'); $ret2 = array((int)$r[1],'not over'); $return[] = array($ret1,$ret2); } return $return; } public function getRunningPieCount($poolID) { $db = Zend_Db_Table::getDefaultAdapter(); if($poolID != 'all'){ $select = $this->getDbTable()->select() ->setIntegrityCheck(false) ->from(array('pce' => 'poolctrl_event'), array('count1' => 'SUM(IF(pce.running=1,1,0))', 'count0' => 'SUM(IF(pce.running=0,1,0))') ) ->join(array('pcec' => 'poolctrl_eventcategory'), 'pce.category = pcec.eventcategoryID', array()) ->where('pce.pbs_poolID = ?', $poolID);} else { $select = $this->getDbTable()->select() ->setIntegrityCheck(false) ->from(array('pce' => 'poolctrl_event'), array('over' => 'SUM(IF(pce.running=1,1,0))', 'not_over' => 'SUM(IF(pce.running=0,1,0))') ) ->join(array('pcec' => 'poolctrl_eventcategory'), 'pce.category = pcec.eventcategoryID', array()); } $stmt = $db->query($select); $result = $stmt->fetchAll(PDO::FETCH_NUM); foreach ($result as $r) { $ret1 = array('over', (int)$r[0]); $ret2 = array('not over', (int)$r[1]); $return[] = array($ret1,$ret2); } return $return; } public function getTypeBarCount($poolID) { $db = Zend_Db_Table::getDefaultAdapter(); if($poolID != 'all'){ $select = $this->getDbTable()->select() ->setIntegrityCheck(false) ->from(array('pce' => 'poolctrl_event'), array('other' => 'SUM(IF(pce.repeat=0 AND pce.immediate=0,1,0))', 'repeat' => 'SUM(IF(pce.repeat=1 AND pce.immediate=0,1,0))', 'immediate' => 'SUM(IF(pce.repeat=0 AND pce.immediate=1,1,0))') ) ->join(array('pcec' => 'poolctrl_eventcategory'), 'pce.category = pcec.eventcategoryID', array()) ->where('pce.pbs_poolID = ?', $poolID);} else { $select = $this->getDbTable()->select() ->setIntegrityCheck(false) ->from(array('pce' => 'poolctrl_event'), array('other' => 'SUM(IF(pce.repeat=0 AND pce.immediate=0,1,0))', 'repeat' => 'SUM(IF(pce.repeat=1 AND pce.immediate=0,1,0))', 'immediate' => 'SUM(IF(pce.repeat=0 AND pce.immediate=1,1,0))') ) ->join(array('pcec' => 'poolctrl_eventcategory'), 'pce.category = pcec.eventcategoryID', array()); } $stmt = $db->query($select); $result = $stmt->fetchAll(PDO::FETCH_NUM); foreach ($result as $r) { $ret1 = array((int)$r[0], 'other'); $ret2 = array((int)$r[1], 'repeat'); $ret3 = array((int)$r[2], 'immediate'); $return[] = array($ret1,$ret2,$ret3); } return $return; } public function getTypePieCount($poolID) { $db = Zend_Db_Table::getDefaultAdapter(); if($poolID != 'all'){ $select = $this->getDbTable()->select() ->setIntegrityCheck(false) ->from(array('pce' => 'poolctrl_event'), array('other' => 'SUM(IF(pce.repeat=0 AND pce.immediate=0,1,0))', 'repeat' => 'SUM(IF(pce.repeat=1 AND pce.immediate=0,1,0))', 'immediate' => 'SUM(IF(pce.repeat=0 AND pce.immediate=1,1,0))') ) ->join(array('pcec' => 'poolctrl_eventcategory'), 'pce.category = pcec.eventcategoryID', array()) ->where('pce.pbs_poolID = ?', $poolID);} else { $select = $this->getDbTable()->select() ->setIntegrityCheck(false) ->from(array('pce' => 'poolctrl_event'), array('other' => 'SUM(IF(pce.repeat=0 AND pce.immediate=0,1,0))', 'repeat' => 'SUM(IF(pce.repeat=1 AND pce.immediate=0,1,0))', 'immediate' => 'SUM(IF(pce.repeat=0 AND pce.immediate=1,1,0))') ) ->join(array('pcec' => 'poolctrl_eventcategory'), 'pce.category = pcec.eventcategoryID', array()); } $stmt = $db->query($select); $result = $stmt->fetchAll(PDO::FETCH_NUM); foreach ($result as $r) { $ret1 = array('other', (int)$r[0]); $ret2 = array('repeat', (int)$r[1]); $ret3 = array('immediate', (int)$r[2]); $return[] = array($ret1,$ret2,$ret3); } return $return; } // Fetch all events with same title as dragged one (fetch repeats) public function getDraggingEvents($eventID) { $db = Zend_Db_Table::getDefaultAdapter(); $select = 'SELECT * FROM poolctrl_event WHERE title = (SELECT title FROM poolctrl_event WHERE eventID = ?) AND poolctrl_event.repeat = 1'; $stmt = $db->query($select, array(''.$eventID)); $return = $stmt->fetchAll(); return $return; } public function getDraggingEvent($eventID) { $event = new Application_Model_Event(); $this->find($eventID, $event); $return = $event->toArray(); return $return; } public function getOverlappingEvents($start, $end, $poolID, Application_Model_Event $event = null, $repeat = false) { $db = Zend_Db_Table::getDefaultAdapter(); $return = array(); if($event != null) { if($repeat) { $select = "SELECT * FROM poolctrl_event WHERE ( ( start >= '" . $start . "' AND start <= '" . $end . "' ) OR ( start <= '" . $start . "' AND end IS NOT NULL AND end >= '" . $start . "' ) ) AND pbs_poolID = '" . $poolID . "' AND ( title <> '" . $event->getTitle() . "' OR ( title = '" . $event->getTitle() . "' AND poolctrl_event.repeat <> 1 ) )"; } else { $select = "SELECT * FROM poolctrl_event WHERE ( ( start >= '" . $start . "' AND start <= '" . $end . "' ) OR ( start <= '" . $start . "' AND end IS NOT NULL AND end >= '" . $start . "' ) ) AND pbs_poolID = '" . $poolID . "' AND eventID <> '" . $event->getID() . "'"; } } else { $select = "SELECT * FROM poolctrl_event WHERE ( ( start >= '" . $start . "' AND start <= '" . $end . "' ) OR ( start <= '" . $start . "' AND end IS NOT NULL AND end >= '" . $start . "' ) ) AND pbs_poolID = '" . $poolID . "'"; } $stmt = $db->query($select); $results = $stmt->fetchAll(); foreach($results as $result) { $event = new Application_Model_Event($result); $event->setID($result['eventID']); $return[] = $event; } return $return; } public function getOtherRepeatEvents($title, $eventIDs) { $db = Zend_Db_Table::getDefaultAdapter(); $return = array(); $idSelect = implode("' AND eventID <> '", $eventIDs); $select = "SELECT * FROM poolctrl_event WHERE title = '". $title . "' AND eventID <> '" . $idSelect . "'"; $stmt = $db->query($select); $results = $stmt->fetchAll(); foreach($results as $result) { $event = new Application_Model_Event($result); $event->setID($result['eventID']); $return[] = $event; } return $return; } }