Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
0.00% |
0 / 139 |
|
0.00% |
0 / 6 |
CRAP | |
0.00% |
0 / 1 |
| Service | |
0.00% |
0 / 139 |
|
0.00% |
0 / 6 |
650 | |
0.00% |
0 / 1 |
| fetchId | |
0.00% |
0 / 13 |
|
0.00% |
0 / 1 |
42 | |||
| fetchList | |
0.00% |
0 / 31 |
|
0.00% |
0 / 1 |
20 | |||
| fetchFromCsv | |
0.00% |
0 / 26 |
|
0.00% |
0 / 1 |
12 | |||
| fetchListRelated | |
0.00% |
0 / 21 |
|
0.00% |
0 / 1 |
12 | |||
| readSearchResultList | |
0.00% |
0 / 19 |
|
0.00% |
0 / 1 |
12 | |||
| fetchServicesForCompilation | |
0.00% |
0 / 29 |
|
0.00% |
0 / 1 |
42 | |||
| 1 | <?php |
| 2 | |
| 3 | /** |
| 4 | * @package ClientDldb |
| 5 | * @copyright BerlinOnline Stadtportal GmbH & Co. KG |
| 6 | **/ |
| 7 | |
| 8 | namespace BO\Zmsdldb\MySQL; |
| 9 | |
| 10 | use BO\Zmsdldb\MySQL\Entity\Service as Entity; |
| 11 | use BO\Zmsdldb\MySQL\Collection\Services as Collection; |
| 12 | use BO\Zmsdldb\Elastic\Service as Base |
| 13 | ; |
| 14 | use Error; |
| 15 | |
| 16 | /** |
| 17 | * @SuppressWarnings(Coupling) |
| 18 | */ |
| 19 | class Service extends Base |
| 20 | { |
| 21 | /** |
| 22 | * |
| 23 | * @return Entity\Service |
| 24 | */ |
| 25 | public function fetchId($service_id) |
| 26 | { |
| 27 | try { |
| 28 | if ($service_id) { |
| 29 | $sqlArgs = [$this->locale, (int)$service_id]; |
| 30 | $sql = 'SELECT data_json FROM service WHERE locale = ? AND id = ?'; |
| 31 | |
| 32 | $stm = $this->access()->prepare($sql); |
| 33 | $stm->setFetchMode(\PDO::FETCH_CLASS | \PDO::FETCH_PROPS_LATE, '\\BO\\Zmsdldb\\MySQL\\Entity\\Service'); |
| 34 | $stm->execute($sqlArgs); |
| 35 | if (!$stm || ($stm && $stm->rowCount() == 0)) { |
| 36 | return false; |
| 37 | } |
| 38 | $service = $stm->fetch(); |
| 39 | return $service; |
| 40 | } |
| 41 | return false; |
| 42 | } catch (\Exception $e) { |
| 43 | throw $e; |
| 44 | } |
| 45 | } |
| 46 | |
| 47 | /** |
| 48 | * |
| 49 | * @return Collection |
| 50 | */ |
| 51 | public function fetchList($location_csv = false, $mixLanguages = false) |
| 52 | { |
| 53 | #--IF(s2.id, s2.data_json, s.data_json) AS data_json |
| 54 | # COALESCE(s2.data_json, s.data_json) AS data_json |
| 55 | try { |
| 56 | $sqlArgs = [$this->locale]; |
| 57 | $where = []; |
| 58 | $join = []; |
| 59 | $groupBy = ''; |
| 60 | if (false === $mixLanguages) { |
| 61 | $where[] = 's.locale = ?'; |
| 62 | $sql = 'SELECT data_json FROM service AS s '; |
| 63 | } else { |
| 64 | $where[] = "s.locale='de'"; |
| 65 | $sql = "SELECT |
| 66 | COALESCE(s2.data_json, s.data_json) AS data_json |
| 67 | FROM service AS s |
| 68 | LEFT JOIN service AS s2 ON s2.id = s.id AND s2.locale = ? |
| 69 | "; |
| 70 | } |
| 71 | |
| 72 | if (!empty($location_csv)) { |
| 73 | #$sqlArgs[] = $this->locale; |
| 74 | $ids = explode(',', $location_csv); |
| 75 | $questionMarks = array_fill(0, count($ids), '?'); |
| 76 | $join[] = 'LEFT JOIN location_service AS ls ON ls.service_id = s.id';# AND ls.locale = ?'; |
| 77 | |
| 78 | $where[] = "ls.location_id IN (" . implode(', ', $questionMarks) . ")"; |
| 79 | $groupBy = 'GROUP BY s.id'; |
| 80 | array_push($sqlArgs, ...$ids); |
| 81 | } |
| 82 | $sql .= " " . implode(' ', $join); |
| 83 | $sql .= " WHERE " . implode(' AND ', $where); |
| 84 | $sql .= " " . $groupBy; |
| 85 | |
| 86 | #echo '<pre>' . print_r([$sql, $sqlArgs],1) . '</pre>';exit; |
| 87 | |
| 88 | $serviceList = new Collection(); |
| 89 | |
| 90 | $stm = $this->access()->prepare($sql); |
| 91 | $stm->execute($sqlArgs); |
| 92 | $stm->fetchAll(\PDO::FETCH_FUNC, function ($data_json) use ($serviceList) { |
| 93 | $service = new \BO\Zmsdldb\MySQL\Entity\Service(); |
| 94 | $service->offsetSet('data_json', $data_json); |
| 95 | |
| 96 | $serviceList[$service['id']] = $service; |
| 97 | }); |
| 98 | |
| 99 | return $serviceList; |
| 100 | } catch (\Exception $e) { |
| 101 | throw $e; |
| 102 | } |
| 103 | } |
| 104 | |
| 105 | /** |
| 106 | * |
| 107 | * @return Collection\Services |
| 108 | */ |
| 109 | public function fetchFromCsv($service_csv, $mixLanguages = false) |
| 110 | { |
| 111 | try { |
| 112 | $sqlArgs = [$this->locale]; |
| 113 | $where = []; |
| 114 | $join = []; |
| 115 | |
| 116 | if (false === $mixLanguages) { |
| 117 | $where[] = 's.locale = ?'; |
| 118 | $sql = 'SELECT data_json FROM service AS s '; |
| 119 | } else { |
| 120 | $where[] = "s.locale='de'"; |
| 121 | $sql = "SELECT |
| 122 | COALESCE(s2.data_json, s.data_json) AS data_json |
| 123 | FROM service AS s |
| 124 | LEFT JOIN service AS s2 ON s2.id = s.id AND s2.locale = ? |
| 125 | "; |
| 126 | } |
| 127 | |
| 128 | $ids = explode(',', $service_csv); |
| 129 | $questionMarks = array_fill(0, count($ids), '?'); |
| 130 | |
| 131 | $where[] = 's.id IN (' . implode(', ', $questionMarks) . ')'; |
| 132 | array_push($sqlArgs, ...$ids); |
| 133 | |
| 134 | $sql .= " " . implode(' ', $join); |
| 135 | $sql .= " WHERE " . implode(' AND ', $where); |
| 136 | |
| 137 | $serviceList = new Collection(); |
| 138 | |
| 139 | $stm = $this->access()->prepare($sql); |
| 140 | $stm->execute($sqlArgs); |
| 141 | $stm->fetchAll(\PDO::FETCH_FUNC, function ($data_json) use ($serviceList) { |
| 142 | $service = new \BO\Zmsdldb\MySQL\Entity\Service(); |
| 143 | $service->offsetSet('data_json', $data_json); |
| 144 | |
| 145 | $serviceList[$service['id']] = $service; |
| 146 | }); |
| 147 | |
| 148 | return $serviceList; |
| 149 | } catch (\Exception $e) { |
| 150 | throw $e; |
| 151 | } |
| 152 | } |
| 153 | |
| 154 | public function fetchListRelated($service_id) |
| 155 | { |
| 156 | try { |
| 157 | $service = $this->fetchId($service_id); |
| 158 | $serviceList = new Collection(); |
| 159 | if (!$service) { |
| 160 | return $serviceList; |
| 161 | } |
| 162 | $leika = str_split(substr(strval($service['leika']), 0, 11)); |
| 163 | $leika[0] = '_'; |
| 164 | $leika[1] = '_'; |
| 165 | |
| 166 | $leika = implode($leika); |
| 167 | |
| 168 | $sqlArgs = [$this->locale, $service['leika'], $leika . '%']; |
| 169 | |
| 170 | $sql = 'SELECT data_json FROM service WHERE locale = ? AND leika != ? AND leika LIKE ?'; |
| 171 | |
| 172 | $serviceList = new Collection(); |
| 173 | |
| 174 | $stm = $this->access()->prepare($sql); |
| 175 | $stm->execute($sqlArgs); |
| 176 | $stm->fetchAll(\PDO::FETCH_FUNC, function ($data_json) use ($serviceList) { |
| 177 | $service = new \BO\Zmsdldb\MySQL\Entity\Service(); |
| 178 | $service->offsetSet('data_json', $data_json); |
| 179 | |
| 180 | $serviceList[$service['id']] = $service; |
| 181 | }); |
| 182 | |
| 183 | return $serviceList; |
| 184 | } catch (\Exception $e) { |
| 185 | throw $e; |
| 186 | } |
| 187 | } |
| 188 | |
| 189 | public function readSearchResultList($query, $service_csv = null) |
| 190 | { |
| 191 | try { |
| 192 | #$query = '+' . implode(' +', explode(' ', $query)); |
| 193 | $sqlArgs = [$this->locale, $this->locale, $query]; |
| 194 | $sql = "SELECT s.data_json |
| 195 | FROM search AS se |
| 196 | LEFT JOIN service AS s ON s.id = se.object_id AND s.locale = ? |
| 197 | WHERE |
| 198 | se.locale = ? AND MATCH (search_value) AGAINST (? IN BOOLEAN MODE) |
| 199 | AND (search_type IN ('name', 'keywords')) AND entity_type='service' |
| 200 | GROUP BY se.object_id |
| 201 | "; |
| 202 | |
| 203 | if (!empty($service_csv)) { |
| 204 | $ids = explode(',', $service_csv); |
| 205 | $questionMarks = array_fill(0, count($ids), '?'); |
| 206 | $sql .= ' AND se.object_id IN (' . implode(', ', $questionMarks) . ')'; |
| 207 | array_push($sqlArgs, ...$ids); |
| 208 | } |
| 209 | #print_r($sql);exit; |
| 210 | |
| 211 | $serviceList = new Collection(); |
| 212 | |
| 213 | $stm = $this->access()->prepare($sql); |
| 214 | $stm->execute($sqlArgs); |
| 215 | $stm->fetchAll(\PDO::FETCH_FUNC, function ($data_json) use ($serviceList) { |
| 216 | $service = new \BO\Zmsdldb\MySQL\Entity\Service(); |
| 217 | $service->offsetSet('data_json', $data_json); |
| 218 | |
| 219 | $serviceList[$service['id']] = $service; |
| 220 | }); |
| 221 | |
| 222 | return $serviceList; |
| 223 | } catch (\Exception $e) { |
| 224 | throw $e; |
| 225 | } |
| 226 | } |
| 227 | |
| 228 | public function fetchServicesForCompilation($authoritys = [], $locations = [], $services = []) |
| 229 | { |
| 230 | try { |
| 231 | $sqlArgs = [$this->locale]; |
| 232 | |
| 233 | $sql = "SELECT |
| 234 | s.data_json |
| 235 | FROM service AS s |
| 236 | "; |
| 237 | $where = ['s.locale = ?']; |
| 238 | $join = []; |
| 239 | |
| 240 | if (!empty($authoritys)) { |
| 241 | $sqlArgs[] = $this->locale; |
| 242 | $join[] = 'authority_service AS a_s ON a_s.service_id = s.id AND a_s.locale = ?'; |
| 243 | $where[] = 'a_s.authority_id IN (' . implode(',', $authoritys) . ')'; |
| 244 | } |
| 245 | if (!empty($locations)) { |
| 246 | $sqlArgs[] = $this->locale; |
| 247 | $join[] = 'location_service AS ls ON ls.service_id = s.id AND ls.locale = ?'; |
| 248 | $where[] = 'ls.location_id IN (' . implode(',', $locations) . ')'; |
| 249 | } |
| 250 | |
| 251 | if (!empty($services)) { |
| 252 | $where[] = 's.id IN (' . implode(',', $services) . ')'; |
| 253 | } |
| 254 | if (!empty($join)) { |
| 255 | $sql .= ' LEFT JOIN ' . implode(' LEFT JOIN ', $join); |
| 256 | } |
| 257 | $sql .= ' WHERE ' . implode(' AND ', $where); |
| 258 | |
| 259 | $serviceList = new Collection(); |
| 260 | |
| 261 | $stm = $this->access()->prepare($sql); |
| 262 | $stm->execute($sqlArgs); |
| 263 | $stm->fetchAll(\PDO::FETCH_FUNC, function ($data_json) use ($serviceList) { |
| 264 | $service = new \BO\Zmsdldb\MySQL\Entity\Service(); |
| 265 | $service->offsetSet('data_json', $data_json); |
| 266 | |
| 267 | $serviceList[$service['id']] = $service; |
| 268 | }); |
| 269 | |
| 270 | return $serviceList; |
| 271 | } catch (\Exception $e) { |
| 272 | throw $e; |
| 273 | } |
| 274 | } |
| 275 | } |