1: <?php
  2: namespace Budovy;
  3: 
  4: class PeriodicTaskRepository extends Repository {
  5:     /**
  6:      * Vrati zaznam havarie dle ID
  7:      *
  8:      * @return mixed
  9:      */
 10:     public function findById($id) {
 11:         return $this->getTable()->select('periodictask.*, :periodictask_contact.contact.id AS id_contact,
 12:             months & 0x1 AS m1,
 13:             months & 0x2 AS m2,
 14:             months & 0x4 AS m3,
 15:             months & 0x8 AS m4,
 16:             months & 0x10 AS m5,
 17:             months & 0x20 AS m6,
 18:             months & 0x40 AS m7,
 19:             months & 0x80 AS m8,
 20:             months & 0x100 AS m9,
 21:             months & 0x200 AS m10,
 22:             months & 0x400 AS m11,
 23:             months & 0x800 AS m12')->get($id);
 24:     }
 25: 
 26:     /**
 27:      * Vraci nazvy period
 28:      *
 29:      * @return array
 30:      */
 31:     function getPeriodNames() {
 32:         return array(
 33:             'každý den',
 34:             'každý pracovní den',
 35:             'každý víkendový den',
 36:             'první den v měsíci',
 37:             'první pracovní den v měsíci',
 38:             'první pracovní den v týdnu',
 39:             'první víkendový den v týdnu',
 40:             'poslední pracovní den v týdnu',
 41:             'poslední den v měsíci',
 42:             'poslední pracovní den v měsíci',
 43:             'první víkendový den v měsíci'
 44:         );
 45:     }
 46: 
 47:     /**
 48:      * Vraci nazev periody
 49:      *
 50:      * @param int $period perioda opakovani
 51:      * @return string
 52:      */
 53:     function getPeriodName($period) {
 54:         $periods = $this->getPeriodNames();
 55: 
 56:         if (isset($periods[$period])) {
 57:             return $periods[$period];
 58:         }
 59: 
 60:         return '';
 61:     }
 62: 
 63:     /**
 64:      * Vraci data v poli ID => nazev
 65:      *
 66:      * @return array
 67:      */
 68:     function getPairData() {
 69:         return $this->getTable()->select('id, name')->order('name ASC')->fetchPairs('id', 'name');
 70:     }
 71: 
 72:     /**
 73:      * Vrati selection pro datagrid
 74:      *
 75:      * @param array $filter
 76:      * @param array $order
 77:      * @param int $page
 78:      * @param int $limit
 79:      * @return \Nette\Database\Table\Selection
 80:      */
 81:     function getGridSelection($filter, $order, $page, $limit) {
 82:         $selection = $this->getTable()
 83:                 ->select('periodictask.*, unit.name AS unit, unit.place.name AS place, operation.name AS operation')
 84:                 ->select(':periodictask_contact.contact.id AS id_contact, CONCAT(:periodictask_contact.contact.firstname, \' \', :periodictask_contact.contact.lastname) AS contact')
 85:                 ->select('priority.name AS priority');
 86: 
 87:         if (isset($order[0])) {
 88:             $selection->order(implode(' ', $order));
 89:         }
 90: 
 91:         $selection->page($page, $limit);
 92: 
 93:         return $this->addGridFilter($selection, $filter);
 94:     }
 95: 
 96:     /**
 97:      * Vrati pocet zaznamu pro datagrid
 98:      *
 99:      * @param array $filter
100:      * @return int
101:      */
102:     function getGridCount($filter) {
103:         $selection = $this->getTable()->select('COUNT(*) AS cnt');
104: 
105:         $result = $this->addGridFilter($selection, $filter)->fetch();
106: 
107:         return $result->cnt;
108:     }
109: 
110:     /**
111:      * Omezi selection na zaklade filtru pro datagrid
112:      *
113:      * @param Selection $selection
114:      * @param Array $filter
115:      * @return \Nette\Database\Table\Selection
116:      */
117:     private function addGridFilter(\Nette\Database\Table\Selection $selection, $filter) {
118:         $filters = array();
119: 
120:         foreach ($filter as $k => $v) {
121:             if ($k == 'unit') {
122:                 $filters['id_unit=?'] = $v;
123:             } else if ($k == 'operation') {
124:                 $filters['id_operation=?'] = $v;
125:             } else if ($k == 'place') {
126:                 $filters['unit.id_place=?'] = $v;
127:             } else if ($k == 'priority') {
128:                 $filters['id_priority=?'] = $v;
129:             } else if ($k == 'contact') {
130:                 $filters[':periodictask_contact.contact.id=?'] = $v;
131:             } else {
132:                 $filters['`periodictask`.' . $k . ' LIKE ?'] = "%$v%";
133:             }
134:         }
135: 
136:         return $selection->where($filters);
137:     }
138: 
139:     /**
140:      * Vraci prirazene dokumenty
141:      *
142:      * @param int $id_periodictask ID ukolu
143:      * @return \Nette\Database\Table\Selection
144:      */
145:     function getAssignedDocuments($id_periodictask) {
146:         return $this->getTable()->select(':periodictask_document.document.id')->where('periodictask.id', $id_periodictask)->where(':periodictask_document.document.id IS NOT NULL');
147:     }
148: 
149:     /**
150:      * Priradi dokument k ukolu
151:      *
152:      * @param unknown $id_periodictask ID ukolu
153:      * @param unknown $id_document ID dokumentu
154:      */
155:     function assignDocument($id_periodictask, $id_document) {
156:         $this->getTable()->get($id_periodictask)->related('periodictask_document')->insert(array('id_document' => $id_document));
157:         //$this->connection->table('periodictask_document')->insert(array('id_periodictask' => $id_periodictask, 'id_document' => $id_document));
158:     }
159: 
160:     /**
161:      * Odradi dokument z ukolu
162:      *
163:      * @param int $id_periodictask ID ukolu
164:      * @param int $id_document ID dokumenu
165:      */
166:     function removeAssignedDocument($id_periodictask, $id_document) {
167:         $this->connection->table('periodictask_document')->where(array('id_periodictask' => $id_periodictask, 'id_document' => $id_document))->delete();
168:     }
169: 
170:     /**
171:      * Vrati prirazeny kontakt
172:      *
173:      * @param int $id_periodictask ID ukolu
174:      * @return mixed
175:      */
176:     function getAssignedContact($id_periodictask) {
177:         return $this->getTable()->select(':periodictask_contact.contact.*')->where('periodictask.id', $id_periodictask)->where(':periodictask_contact.contact.id IS NOT NULL')->fetch();
178:     }
179: 
180:     /**
181:      * Vraci prirazeny kontakt
182:      *
183:      * @param id $id_periodictask ID ukolu
184:      * @param id $id_contact ID kontaktu
185:      * @throws PDOException
186:      */
187:     function assignContact($id_periodictask, $id_contact) {
188:         try {
189:             $this->connection->table('periodictask_contact')->insert(array('id_periodictask' => $id_periodictask, 'id_contact' => $id_contact));
190:         } catch (\PDOException $e) {
191:             if ($e->getCode() == '23000') {
192:                 $this->connection->table('periodictask_contact')->where(array('id_periodictask' => $id_periodictask))->update(array('id_contact' => $id_contact));
193:             } else {
194:                 throw $e;
195:             }
196:         }
197:     }
198: 
199:     /**
200:      * Odradi kontakt pro zadany ukol
201:      *
202:      * @param int $id_periodictask
203:      */
204:     function removeAssignedContact($id_periodictask) {
205:         $this->connection->table('periodictask_contact')->where('id_periodictask', $id_periodictask)->delete();
206:     }
207: 
208:     /**
209:      * Pro zadanou periodu otestuj datum, zda je platne
210:      *
211:      * @param string $period Perioda
212:      * @param \DateTime $date Datum
213:      * @throws \UnknownPeriod
214:      * @return boolean
215:      */
216:     function checkPeriod($period, $date) {
217:         $datetime = clone $date;
218: 
219:         /*
220:          *  0 'každý den',
221:         *   1 'každý pracovní den',
222:         *   2 'každý víkendový den',
223:         *   3 'první den v měsíci',
224:         *   4 'první pracovní den v měsíci',
225:         *   5 'první pracovní den v týdnu',
226:         *   6 'první víkendový den v týdnu',
227:         *   7 'poslední pracovní den v týdnu',
228:         *   8 'poslední den v měsíci',
229:         *   9 'poslední pracovní den v měsíci'
230:         *   10 'první víkendový den v měsíci'
231:         */
232: 
233:         switch ($period) {
234:             case 0:
235:                 return true;
236: 
237:             case 1:
238:                 return (int)$datetime->format('N') < 6;
239: 
240:             case 2:
241:                 return (int)$datetime->format('N') > 5;
242: 
243:             case 3:
244:                 return (int)$datetime->format('j') == 1;
245: 
246:             case 4:
247:                 $datetime->modify('+' . (1 - $datetime->format('j')) . ' day');
248: 
249:                 switch ($datetime->format('N')) {
250:                     case 6:
251:                         $datetime->modify('+2 day');
252:                         break;
253: 
254:                     case 7:
255:                         $datetime->modify('+1 day');
256:                         break;
257:                 }
258: 
259:                 return $datetime->diff($date)->days == 0;
260: 
261:                 break;
262: 
263:             case 5:
264:                 return (int)$datetime->format('N') == 1;
265: 
266:             case 6:
267:                 return (int)$datetime->format('N') == 6;
268: 
269:             case 7:
270:                 return (int)$datetime->format('N') == 5;
271: 
272:             case 8:
273:                 $datetime->modify('+' . ($datetime->format('t') - $datetime->format('j')) . ' day');
274: 
275:                 return $datetime->diff($date)->days == 0;
276: 
277:             case 9:
278:                 $datetime->modify('+' . ($datetime->format('t') - $datetime->format('j')) . ' day');
279: 
280:                 switch ($datetime->format('N')) {
281:                     case 6:
282:                         $datetime->modify('-1 day');
283:                         break;
284: 
285:                     case 7:
286:                         $datetime->modify('-2 day');
287:                         break;
288:                 }
289: 
290:                 return $datetime->diff($date)->days == 0;
291: 
292:             case 10:
293:                 $datetime->modify('+' . (1 - $datetime->format('j')) . ' day');
294: 
295:                 switch ($datetime->format('N')) {
296:                     case 1:
297:                     case 2:
298:                     case 3:
299:                     case 4:
300:                     case 5:
301:                         $datetime->modify('+' . (6 - $datetime->format('N')) . ' day');
302:                         break;
303: 
304:                     case 7:
305:                         $datetime->modify('+6 day');
306:                         break;
307:                 }
308: 
309:                 return $datetime->diff($date)->days == 0;
310: 
311:             default:
312:                 throw new \UnknownPeriod("Unknown period: " . $period);
313:         }
314:     }
315: 
316:     /**
317:      * Vraci ukoly pro zadane rozpeti datumu
318:      *
319:      * @param \DateTime $start Datum
320:      * @param \DateTime $end Datum
321:      * @return \Nette\Database\Table\Selection
322:      */
323:     function getTasksByDateRange($start, $end) {
324:         $startdate = clone $start;
325:         $startdate->setDate($startdate->format('Y'), $startdate->format('n'), 1);
326: 
327:         $enddate = clone $end;
328:         $enddate->setDate($enddate->format('Y'), $enddate->format('n'), 1);
329: 
330:         $selection = $this->getTable();
331: 
332:         $month = 0;
333: 
334:         for (; $startdate <= $enddate; $startdate->modify('+1 month')) {
335:             $month += pow(2, $startdate->format('n') - 1);
336:         }
337: 
338:         return $selection->where(array('`months` & ?' => $month));
339:     }
340: 
341:     /**
342:      * Vraci ukoly na zaklade datumu
343:      *
344:      * @param string $start Datum Y-m-d
345:      * @return \Nette\Database\Table\Selection
346:      */
347:     function getTasksStartedAt($start) {
348:         $startdate = new \DateTime($start);
349: 
350:         $periods = array();
351: 
352:         for ($n = 0; $n <= 9; $n++) {
353:             if ($this->checkPeriod($n, $startdate)) {
354:                 $periods[] = $n;
355:             }
356:         }
357: 
358:         return $this->getTable()->where('`months` & ? AND period ?', pow(2, $startdate->format('n') - 1), $periods);
359:     }
360: 
361:     /**
362:      * Vraci ukoly pro zadany kontakt
363:      *
364:      * @param int $id_contact
365:      * @return \Nette\Database\Table\Selection
366:      */
367:     function getTasksByCustomer($id_contact) {
368:         return $this->getTable()->select('DISTINCT `periodictask`.*')->where(':periodictask_contact.id_contact=? OR :periodictask_realization:periodictask_realization_contact.id_contact=?', $id_contact, $id_contact);
369:     }
370: 
371:     /**
372:      * Vraci ukoly pro zadany dokument
373:      *
374:      * @param int $id_document
375:      * @return \Nette\Database\Table\Selection
376:      */
377:     function getTasksByDocument($id_document) {
378:         return $this->getTable()->where(':periodictask_document.id_document=?', $id_document);
379:     }
380: 
381:     /**
382:      * Vraci ukoly s povolenym upozornenim na zaklade aktualniho datumu
383:      *
384:      * @return \Nette\Database\Table\Selection
385:      */
386:     function getTasksNotice() {
387:         return $this->getTable()
388:         ->select('periodictask.*, unit.name AS unit, operation.name AS operation, :periodictask_contact.contact.email')
389:         ->select('CURDATE() + INTERVAL(operation.daysbefore) ? AS `date`', new \Nette\Database\SqlLiteral('day'))
390:         ->where('`months`&POW(2,MONTH(CURDATE() + INTERVAL(operation.daysbefore) ?) - 1)', new \Nette\Database\SqlLiteral('day'));
391:     }
392: }