1: <?php
  2: namespace Budovy;
  3: 
  4: class PeriodicTaskRealizationRepository extends Repository {
  5:     /**
  6:      * Vraci zaznam dle primarniho klice
  7:      *
  8:      * @param int $id ID zaznamu
  9:      * @return mixed
 10:      */
 11:     public function findById($id) {
 12:         return $this->getTable()->select('periodictask_realization.*, :periodictask_realization_contact.contact.id AS id_contact')->get($id);
 13:     }
 14: 
 15:     /**
 16:      * Vrati selection pro datagrid
 17:      *
 18:      * @param array $filter
 19:      * @param array $order
 20:      * @param int $page
 21:      * @param int $limit
 22:      * @return \Nette\Database\Table\Selection
 23:      */
 24:     function getGridSelection($filter, $order, $page, $limit) {
 25:         $selection = $this->getTable()->select('periodictask_realization.*, periodictask.name, :periodictask_realization_contact.contact.id AS id_contact, CONCAT(:periodictask_realization_contact.contact.firstname, \' \', :periodictask_realization_contact.contact.lastname) AS contact');
 26: 
 27:         if (isset($order[0]))
 28:             $selection->order(implode(' ', $order));
 29: 
 30:         $selection->page($page, $limit);
 31: 
 32:         return $this->addGridFilter($selection, $filter);
 33:     }
 34: 
 35:     /**
 36:      * Vrati pocet zaznamu pro datagrid
 37:      *
 38:      * @param array $filter
 39:      * @return int
 40:      */
 41:     function getGridCount($filter) {
 42:         $selection = $this->getTable()->select('COUNT(*) AS cnt');
 43: 
 44:         $result = $this->addGridFilter($selection, $filter)->fetch();
 45: 
 46:         return $result->cnt;
 47:     }
 48: 
 49:     /**
 50:      * Omezi selection na zaklade filtru pro datagrid
 51:      *
 52:      * @param Selection $selection
 53:      * @param Array $filter
 54:      * @return \Nette\Database\Table\Selection
 55:      */
 56:     private function addGridFilter(\Nette\Database\Table\Selection $selection, $filter) {
 57:         $filters = array();
 58: 
 59:         foreach ($filter as $k => $v) {
 60:             if ($k == 'contact')
 61:                 $filters[':periodictask_realization_contact.contact.id=?'] = $v;
 62:             else if ($k == 'name')
 63:                 $filters['id_periodictask=?'] = $v;
 64:             else if ($k == 'date')
 65:                 $filters['`periodictask_realization`.date=?'] = $v;
 66:             else
 67:                 $filters['`periodictask_realization`.' . $k . ' LIKE ?'] = "%$v%";
 68:         }
 69: 
 70:         return $selection->where($filters);
 71:     }
 72: 
 73:     /**
 74:      * Vraci prirazene dokumenty
 75:      *
 76:      * @param int $id_periodictask_realization ID realizaovane ulohy
 77:      * @return \Nette\Database\Table\Selection
 78:      */
 79:     function getAssignedDocuments($id_periodictask_realization) {
 80:         return $this->getTable()->select(':periodictask_realization_document.document.id')->where('periodictask_realization.id', $id_periodictask_realization)->where(':periodictask_realization_document.document.id IS NOT NULL');
 81:     }
 82: 
 83:     /**
 84:      * Priradi dokument realizovane uloze
 85:      *
 86:      * @param int $id_periodictask_realization ID realizaovane ulohy
 87:      * @param int $id_document ID dokumentu
 88:      */
 89:     function assignDocument($id_periodictask_realization, $id_document) {
 90:         $this->connection->table('periodictask_realization_document')->insert(array('id_periodictask_realization' => $id_periodictask_realization, 'id_document' => $id_document));
 91:     }
 92: 
 93:     /**
 94:      * Odradi prirazeny dokument
 95:      *
 96:      * @param int $id_periodictask_realization
 97:      * @param int $id_document
 98:      */
 99:     function removeAssignedDocument($id_periodictask_realization, $id_document) {
100:         $this->connection->table('periodictask_realization_document')->where(array('id_periodictask_realization' => $id_periodictask_realization, 'id_document' => $id_document))->delete();
101:     }
102: 
103:     /**
104:      * Vrati prirazeny kontakt
105:      *
106:      * @param int $id_periodictask_realization ID realizaovane ulohy
107:      * @return mixed
108:      */
109:     function getAssignedContact($id_periodictask_realization) {
110:         return $this->getTable()->select(':periodictask_realization_contact.contact.*')->where(array('periodictask_realization.id' => $id_periodictask_realization))->where(':periodictask_realization_contact.contact.id IS NOT NULL')->fetch();
111:     }
112: 
113:     /**
114:      * Priradi kontakt
115:      *
116:      * @param int $id_periodictask_realization ID realizaovane ulohy
117:      * @param int $id_contact ID kontaktu
118:      * @throws PDOException
119:      */
120:     function assignContact($id_periodictask_realization, $id_contact) {
121:         try {
122:             $this->connection->table('periodictask_realization_contact')->insert(array('id_periodictask_realization' => $id_periodictask_realization, 'id_contact' => $id_contact));
123:         } catch (\PDOException $e) {
124:             if ($e->getCode() == '23000')
125:                 $this->connection->table('periodictask_realization_contact')->where(array('id_periodictask_realization' => $id_periodictask_realization))->update(array('id_contact' => $id_contact));
126:             else
127:                 throw $e;
128:         }
129:     }
130: 
131:     /**
132:      * Odradi prirazeny kontakt
133:      *
134:      * @param int $id_periodictask_realization ID realizaovane ulohy
135:      */
136:     function removeAssignedContact($id_periodictask_realization) {
137:         $this->connection->table('periodictask_realization_contact')->where(array('id_periodictask_realization' => $id_periodictask_realization))->delete();
138:     }
139: 
140:     /**
141:      * Vraci zaznamy o realizaci za jednotlive mesice v zadanem casovem rozmezi
142:      *
143:      * @param string $start Datum Y-m-d
144:      * @param string $end Datum Y-m-d
145:      * @return \Nette\Database\Table\Selection
146:      */
147:     function getPriceStatistic($start, $end, $id_place) {
148:         $selection = $this->getTable()
149:             ->select('SUM(periodictask_realization.price) AS price, MONTH(`date`) AS `month`, YEAR(`date`) AS `year`')
150:             ->where('`date` BETWEEN ? AND ?', $start, $end)
151:             ->group('MONTH(`date`), YEAR(`date`)')
152:             ->order('`year` ASC, `month` ASC');
153: 
154:         if ($id_place > 0)
155:             $selection->where('periodictask.unit.place.id', $id_place);
156: 
157:         return $selection;
158:     }
159: 
160:     /**
161:      * Vraci zaznamy o realizaci dle jednotky v zadanem casovem rozmezi
162:      *
163:      * @param string $start Datum Y-m-d
164:      * @param string $end Datum Y-m-d
165:      * @return \Nette\Database\Table\Selection
166:      */
167:     function getUnitStatistic($start, $end, $id_place) {
168:         $selection = $this->getTable()
169:             ->select('SUM(periodictask_realization.price) AS price, periodictask.unit.name')
170:             ->where('`date` BETWEEN ? AND ?', $start, $end)
171:             ->group('periodictask.id_unit')
172:             ->order('name');
173: 
174:         if ($id_place > 0)
175:             $selection->where('periodictask.unit.place.id', $id_place);
176: 
177:         return $selection;
178:     }
179: 
180:     /**
181:      * Vraci zaznamy o realizaci dle ukonu v zadanem casovem rozmezi
182:      *
183:      * @param string $start Datum Y-m-d
184:      * @param string $end Datum Y-m-d
185:      * @return \Nette\Database\Table\Selection
186:      */
187:     function getOperationStatistic($start, $end, $id_place) {
188:         $selection = $this->getTable()
189:             ->select('SUM(periodictask_realization.price) AS price, periodictask.operation.name')
190:             ->where('`date` BETWEEN ? AND ?', $start, $end)
191:             ->group('periodictask.id_operation')
192:             ->order('name');
193: 
194:         if ($id_place > 0)
195:             $selection->where('periodictask.unit.place.id', $id_place);
196: 
197:         return $selection;
198:     }
199: 
200:     /**
201:      * Vraci zaznamy o realizaci dle kontaktu v zadanem casovem rozmezi
202:      *
203:      * @param string $start Datum Y-m-d
204:      * @param string $end Datum Y-m-d
205:      * @return \Nette\Database\Table\Selection
206:      */
207:     function getContactStatistic($start, $end, $id_place) {
208:         $selection = $this->getTable()
209:             ->select('SUM(periodictask_realization.price) AS price, :periodictask_realization_contact.contact.*')
210:             ->where('`date` BETWEEN ? AND ?', $start, $end)
211:             ->group(':periodictask_realization_contact.id_contact')
212:             ->order(':periodictask_realization_contact.contact.lastname, :periodictask_realization_contact.contact.firstname');
213: 
214:         if ($id_place > 0)
215:             $selection->where('periodictask.unit.place.id', $id_place);
216: 
217:         return $selection;
218:     }
219: 
220:     /**
221:      * Vraci ulohy na zaklade vazby na dokument
222:      *
223:      * @param int $id_document ID dokumentu
224:      * @return \Nette\Database\Table\Selection
225:      */
226:     function getTasksByDocument($id_document) {
227:         return $this->getTable()->select('periodictask.name, periodictask_realization.date, periodictask_realization.id')->where(':periodictask_realization_document.id_document=?', $id_document);
228:     }
229: }