1: <?php
  2: namespace Budovy;
  3: 
  4: class ScheduledTaskRepository extends Repository {
  5:     /**
  6:      * Vraci data v poli ID => nazev
  7:      *
  8:      * @return array
  9:      */
 10:     function getPairData() {
 11:         return $this->getTable()->select('id, name')->order('name ASC')->fetchPairs('id', 'name');
 12:     }
 13: 
 14:     /**
 15:      * Vraci schvalene ulohy v poli ID => nazev
 16:      *
 17:      * @return array
 18:      */
 19:     function getApprovedPairData() {
 20:         return $this->getTable()->select('id, name')->where(':scheduledtask_approved.id_user IS NOT NULL')->order('name ASC')->fetchPairs('id', 'name');
 21:     }
 22: 
 23:     /**
 24:      * Vraci zaznam dle primarniho klice
 25:      * @param int $id ID zaznamu
 26:      * @return mixed
 27:      */
 28:     public function findById($id) {
 29:         return $this->getTable()->select('scheduledtask.*, :scheduledtask_contact.contact.id AS id_contact')->get($id);
 30:     }
 31: 
 32:     /**
 33:      * Vrati selection pro datagrid
 34:      *
 35:      * @param array $filter
 36:      * @param array $order
 37:      * @param int $page
 38:      * @param int $limit
 39:      * @return \Nette\Database\Table\Selection
 40:      */
 41:     function getGridSelection($filter, $order, $page, $limit) {
 42:         $selection = $this->getTable()
 43:             ->select('scheduledtask.*, unit.name AS unit, unit.place.name AS place, operation.name AS operation, :scheduledtask_contact.contact.id AS id_contact')
 44:             ->select('priority.name AS priority')
 45:             ->select('CONCAT(:scheduledtask_contact.contact.firstname, \' \', :scheduledtask_contact.contact.lastname) AS contact')
 46:             ->select(':scheduledtask_approved.id_user IS NOT NULL AS approved');
 47: 
 48:         if (isset($order[0]))
 49:             $selection->order(implode(' ', $order));
 50: 
 51:         $selection->page($page, $limit);
 52: 
 53:         return $this->addGridFilter($selection, $filter);
 54:     }
 55: 
 56:     /**
 57:      * Vrati pocet zaznamu pro datagrid
 58:      *
 59:      * @param array $filter
 60:      * @return int
 61:      */
 62:     function getGridCount($filter) {
 63:         $selection = $this->getTable()->select('COUNT(*) AS cnt');
 64: 
 65:         $result = $this->addGridFilter($selection, $filter)->fetch();
 66: 
 67:         return $result->cnt;
 68:     }
 69: 
 70:     /**
 71:      * Omezi selection na zaklade filtru pro datagrid
 72:      *
 73:      * @param Selection $selection
 74:      * @param Array $filter
 75:      * @return \Nette\Database\Table\Selection
 76:      */
 77:     private function addGridFilter(\Nette\Database\Table\Selection $selection, $filter) {
 78:         $filters = array();
 79: 
 80:         foreach ($filter as $k => $v) {
 81:             if ($k == 'unit') {
 82:                 $filters['id_unit=?'] = $v;
 83:             } else if ($k == 'operation') {
 84:                 $filters['id_operation=?'] = $v;
 85:             } else if ($k == 'priority') {
 86:                 $filters['id_priority=?'] = $v;
 87:             } else if ($k == 'contact') {
 88:                 $filters[':scheduledtask_contact.contact.id=?'] = $v;
 89:             } else if ($k == 'start') {
 90:                 $filters['`scheduledtask`.start=?'] = $v;
 91:             } else if ($k == 'end') {
 92:                 $filters['`scheduledtask`.end=?'] = $v;
 93:             } else if ($k == 'place') {
 94:                 $filters['unit.id_place=?'] = $v;
 95:             } else {
 96:                 $filters['`scheduledtask`.' . $k . ' LIKE ?'] = "%$v%";
 97:             }
 98:         }
 99: 
100:         return $selection->where($filters);
101:     }
102: 
103:     /**
104:      * Vrati selection pro datagrid
105:      *
106:      * @param array $filter
107:      * @param array $order
108:      * @param int $page
109:      * @param int $limit
110:      * @return \Nette\Database\Table\Selection
111:      */
112:     function getAuthorizeGridSelection($filter, $order, $page, $limit) {
113:         $selection = $this->getTable()->select('scheduledtask.*, :scheduledtask_approved.user.id AS approved, scheduledtask_approved.`date` AS date, CONCAT(:scheduledtask_approved.user.firstname, \' \', :scheduledtask_approved.user.lastname) AS user');
114: 
115:         if (isset($order[0]))
116:             $selection->order(implode(' ', $order));
117: 
118:         $selection->page($page, $limit);
119: 
120:         return $this->addAuthorizeGridFilter($selection, $filter);
121:     }
122: 
123:     /**
124:      * Vrati pocet zaznamu pro datagrid
125:      *
126:      * @param array $filter
127:      * @return int
128:      */
129:     function getAuthorizeGridCount($filter) {
130:         $selection = $this->getTable()->select('COUNT(*) AS cnt');
131: 
132:         $result = $this->addAuthorizeGridFilter($selection, $filter)->fetch();
133: 
134:         return $result->cnt;
135:     }
136: 
137:     /**
138:      * Omezi selection na zaklade filtru pro datagrid
139:      *
140:      * @param Selection $selection
141:      * @param Array $filter
142:      * @return \Nette\Database\Table\Selection
143:      */
144:     private function addAuthorizeGridFilter($selection, $filter) {
145:         $filters = array();
146: 
147:         foreach ($filter as $k => $v) {
148:             if ($k == 'date') {
149:                 if ($v == 1)
150:                     $filters[':scheduledtask_approved.user.id?'] = new \Nette\Database\SqlLiteral('IS NOT NULL');
151:                 else if ($v == 0)
152:                     $filters[':scheduledtask_approved.user.id?'] = new \Nette\Database\SqlLiteral('IS NULL');
153:             } else if ($k == 'user')
154:                 $filters[':scheduledtask_approved.user.id=?'] = $v;
155:             else
156:                 $filters['`scheduledtask`.' . $k . ' LIKE ?'] = "%$v%";
157:         }
158: 
159:         return $selection->where($filters);
160:     }
161: 
162:     /**
163:      * Vraci prirazene dokumenty k ukolu
164:      *
165:      * @param int $id_scheduledtask ID ukolu
166:      * @return \Nette\Database\Table\Selection
167:      */
168:     function getAssignedDocuments($id_scheduledtask) {
169:         return $this->getTable()->select(':scheduledtask_document.document.id')->where('scheduledtask.id', $id_scheduledtask)->where(':scheduledtask_document.document.id IS NOT NULL');
170:     }
171: 
172:     /**
173:      * Priradi dokument k ukolu
174:      *
175:      * @param int $id_scheduledtask ID ukolu
176:      * @param int $id_document ID dokumentu
177:      */
178:     function assignDocument($id_scheduledtask, $id_document) {
179:         $this->connection->table('scheduledtask_document')->insert(array('id_scheduledtask' => $id_scheduledtask, 'id_document' => $id_document));
180:     }
181: 
182:     /**
183:      * Odradi prirazeny dokument k ukolu
184:      *
185:      * @param int $id_scheduledtask ID ukolu
186:      * @param int $id_document ID dokumentu
187:      */
188:     function removeAssignedDocument($id_scheduledtask, $id_document) {
189:         $this->connection->table('scheduledtask_document')->where(array('id_scheduledtask' => $id_scheduledtask, 'id_document' => $id_document))->delete();
190:     }
191: 
192:     /**
193:      * Vrati prirazeny kontakt k ukolu
194:      *
195:      * @param int $id_scheduledtask ID ukolu
196:      * @return mixed
197:      */
198:     function getAssignedContact($id_scheduledtask) {
199:         return $this->getTable()->select(':scheduledtask_contact.contact.*')->where('scheduledtask.id', $id_scheduledtask)->where(':scheduledtask_contact.contact.id IS NOT NULL')->fetch();
200:     }
201: 
202:     /**
203:      * Prirad kontakt k ukolu
204:      *
205:      * @param int $id_scheduledtask ID ukolu
206:      * @param int $id_contact ID kontaktu
207:      * @throws PDOException
208:      */
209:     function assignContact($id_scheduledtask, $id_contact) {
210:         try {
211:             $this->connection->table('scheduledtask_contact')->insert(array('id_scheduledtask' => $id_scheduledtask, 'id_contact' => $id_contact));
212:         } catch (\PDOException $e) {
213:             if ($e->getCode() == '23000')
214:                 $this->connection->table('scheduledtask_contact')->where('id_scheduledtask', $id_scheduledtask)->update(array('id_contact' => $id_contact));
215:             else
216:                 throw $e;
217:         }
218:     }
219: 
220:     /**
221:      * Odradi kontakt z ukolu
222:      *
223:      * @param int $id_scheduledtask
224:      */
225:     function removeAssignedContact($id_scheduledtask) {
226:         $this->connection->table('scheduledtask_contact')->where('id_scheduledtask', $id_scheduledtask)->delete();
227:     }
228: 
229:     /**
230:      * Vytvor schvaleni ukolu
231:      *
232:      * @param int $id_scheduledtask ID ukolu
233:      * @param int $id_user ID uzivatele
234:      */
235:     function approve($id_scheduledtask, $id_user) {
236:         $this->connection->table('scheduledtask_approved')->insert(array('id_scheduledtask' => $id_scheduledtask, 'id_user' => $id_user, 'date' => new \Nette\Database\SqlLiteral('CURDATE()')));
237:     }
238: 
239:     /**
240:      * Vraci informace o schvaleni ukolu
241:      *
242:      * @param int $id_scheduledtask ID ukolu
243:      * @return mixed
244:      */
245:     function getApprovedInfo($id_scheduledtask) {
246:         return $this->connection->table('scheduledtask_approved')->select('scheduledtask_approved.date, user.*')->where(array('id_scheduledtask' => $id_scheduledtask))->fetch();
247:     }
248: 
249:     /**
250:      * Vraci ukoly na zaklade datumu
251:      *
252:      * @param \DateTime $start Datum
253:      * @param \DateTime $end Datum
254:      * @return \Nette\Database\Table\Selection
255:      */
256:     function getTasksByDateRange($start, $end) {
257:         $startDate = $start->format('Y-m-d');
258:         $endDate = $end->format('Y-m-d');
259: 
260:         return $this->getTable()->where('start BETWEEN ? AND ? OR end BETWEEN ? AND ?', $startDate, $endDate, $startDate, $endDate);
261:     }
262: 
263:     /**
264:      * Vraci ukoly na zaklade datumu
265:      *
266:      * @param string $start Datum Y-m-d
267:      * @return \Nette\Database\Table\Selection
268:      */
269:     function getTasksStartedAt($start) {
270:         return $this->getTable()->where('start=?', $start);
271:     }
272: 
273:     /**
274:      * Vraci ukoly na zaklade vazby na kontakt
275:      *
276:      * @param int $id_contact ID kontaktu
277:      * @return \Nette\Database\Table\Selection
278:      */
279:     function getTasksByCustomer($id_contact) {
280:         return $this->getTable()->select('DISTINCT `scheduledtask`.*')->where(':scheduledtask_contact.id_contact=? OR :scheduledtask_realization:scheduledtask_realization_contact.id_contact=?', $id_contact, $id_contact);
281:     }
282: 
283:     /**
284:      * Vraci ukoly na zaklade vazby na dokument
285:      *
286:      * @param int $id_document ID dokumentu
287:      * @return \Nette\Database\Table\Selection
288:      */
289:     function getTasksByDocument($id_document) {
290:         return $this->getTable()->where(':scheduledtask_document.id_document', $id_document);
291:     }
292: 
293:     /**
294:      * Vraci ukoly s povolenym upozornenim na zaklade aktualniho datumu
295:      *
296:      * @return \Nette\Database\Table\Selection
297:      */
298:     function getTasksNotice() {
299:         return $this->getTable()
300:         ->select('scheduledtask.*, unit.name AS unit, operation.name AS operation, :scheduledtask_contact.contact.email')
301:         ->where('`start`=CURDATE() + INTERVAL(operation.daysbefore) ?', new \Nette\Database\SqlLiteral('day'))
302:         ->where(':scheduledtask_approved.id_scheduledtask IS NOT NULL');
303:     }
304: }