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