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