Help groupFilter('', '', '', '', '', '', '', '', '', '', '', '', '', $filterGroupId); } function groupFilterBySel($filterZipcode, $filterPlace, $filterProvince, $filterMonday, $filterTuesday, $filterWednesday, $filterThursday, $filterFriday, $filterSaturday, $filterSunday, $filterMorning, $filterAfternoon, $filterEvening) { return $this->groupFilter($filterZipcode, $filterPlace, $filterProvince, $filterMonday, $filterTuesday, $filterWednesday, $filterThursday, $filterFriday, $filterSaturday, $filterSunday, $filterMorning, $filterAfternoon, $filterEvening, ''); } function groupFilter($filterZipcode, $filterPlace, $filterProvince, $filterMonday, $filterTuesday, $filterWednesday, $filterThursday, $filterFriday, $filterSaturday, $filterSunday, $filterMorning, $filterAfternoon, $filterEvening, $filterGroupId) { $useAnd = false; $useOr = false; $strQuery = "SELECT GR.Id, GR.NAME, GR.Comment, GR.OnSite, GR.PW FROM Groups AS GR"; if($filterGroupId) { $strQuery = "$strQuery WHERE GR.Id = ". $filterGroupId; } if(!$filterGroupId) { $useAnd = false; $strQuery = "$strQuery INNER JOIN GroupAddresses AS GA "; $strQuery = "$strQuery ON GA.GroupId = GR.Id"; // alleen hoofdadres $strQuery = "$strQuery AND GA.Local = '1' "; $strQuery = "$strQuery INNER JOIN Places AS PL ON PL.Id = GA.PlaceId"; if($filterZipcode || $filterPlace || $filterProvince) { if($filterZipcode) { $strQuery = "$strQuery AND PL.Zipcode like '". $filterZipcode ."' "; } if($filterPlace) { $strQuery = "$strQuery AND PL.City like '". $filterPlace ."' "; } if($filterProvince != 0) { $strQuery = "$strQuery AND EXISTS"; $strQuery = "$strQuery (SELECT * FROM Provinces AS PR WHERE PR.Id = PL.ProvinceId"; $strQuery = "$strQuery AND PR.Id = ". $filterProvince . " "; $strQuery = "$strQuery)"; } } $tsAdded = false; if($filterMonday || $filterTuesday || $filterWednesday || $filterThursday || $filterFriday ||$filterSaturday || $filterSunday) { $tsAdded = true; if($useAnd) { $strQuery = "$strQuery AND EXISTS"; } else { $strQuery = "$strQuery WHERE EXISTS"; } $strQuery = "$strQuery (SELECT * FROM TimeSchedules TS WHERE TS.GroupId = GR.Id"; $strQuery = "$strQuery AND ("; if($filterMonday) { $strQuery = "$strQuery" . "TS.DayOfWeek = 1"; $useOr = true; } if($filterTuesday) { $strQuery = "$strQuery" . ($useOr ? " OR " : "") . "TS.DayOfWeek = 2"; $useOr = true; } if($filterWednesday) { $strQuery = "$strQuery" . ($useOr ? " OR " : "") . "TS.DayOfWeek = 3"; $useOr = true; } if($filterThursday) { $strQuery = "$strQuery" . ($useOr ? " OR " : "") . "TS.DayOfWeek = 4"; $useOr = true; } if($filterFriday) { $strQuery = "$strQuery" . ($useOr ? " OR " : "") . "TS.DayOfWeek = 5"; $useOr = true; } if($filterSaturday) { $strQuery = "$strQuery" . ($useOr ? " OR " : "") . "TS.DayOfWeek = 6"; $useOr = true; } if($filterSunday) { $strQuery = "$strQuery" . ($useOr ? " OR " : "") . "TS.DayOfWeek = 7"; $useOr = true; } $strQuery = "$strQuery)"; } else { $tsAdded = true; if($useAnd) { $strQuery = "$strQuery AND EXISTS"; } else { $strQuery = "$strQuery WHERE EXISTS"; } $strQuery = "$strQuery (SELECT * FROM TimeSchedules TS WHERE TS.GroupId = GR.Id"; $strQuery = "$strQuery AND ("; $strQuery = "$strQuery" . "TS.DayOfWeek = -1"; $strQuery = "$strQuery)"; } $useOr = false; if($filterMorning || $filterAfternoon || $filterEvening) { if($tsAdded) { $strQuery = "$strQuery AND ("; } else { if($useAnd) { $strQuery = "$strQuery AND EXISTS"; } else { $strQuery = "$strQuery WHERE EXISTS"; } $strQuery = "$strQuery (SELECT * FROM TimeSchedules TS WHERE TS.GroupId = GR.Id"; $strQuery = "$strQuery AND ("; } if($filterMorning) { $strQuery = "$strQuery" . ($useOr ? " OR " : "") . "(TS.Hour >= 80000 AND TS.Hour < 120000)"; $useOr = true; } if($filterAfternoon) { $strQuery = "$strQuery" . ($useOr ? " OR " : "") . "(TS.Hour >= 120000 AND TS.Hour < 180000)"; $useOr = true; } if($filterEvening) { $strQuery = "$strQuery" . ($useOr ? " OR " : "") . "(TS.Hour >= 180000 AND TS.Hour < 240000)"; } $strQuery = "$strQuery))"; } else { if($tsAdded) { $strQuery = "$strQuery AND ("; } else { if($useAnd) { $strQuery = "$strQuery AND EXISTS"; } else { $strQuery = "$strQuery WHERE EXISTS"; } $strQuery = "$strQuery (SELECT * FROM TimeSchedules TS WHERE TS.GroupId = GR.Id"; $strQuery = "$strQuery AND ("; } $strQuery = "$strQuery" . "TS.Hour = 0"; $strQuery = "$strQuery))"; } $strQuery = "$strQuery ORDER BY PL.City"; } return $strQuery; } function getDayName($day) { $dayName = ""; switch($day) { case 1 : $dayName = "maandag"; break; case 2 : $dayName = "dinsdag"; break; case 3 : $dayName = "woensdag"; break; case 4 : $dayName = "donderdag"; break; case 5 : $dayName = "vrijdag"; break; case 6 : $dayName = "zaterdag"; break; case 7 : $dayName = "zondag"; break; } return $dayName; } function showContactPersons($db, $groupId) { $groupContacts = mysqli_query($db, "SELECT * FROM GroupContacts WHERE GroupId = '$groupId' AND Local = '1'") or die ("Error: ". mysqli_error()); echo "

"; echo ""; echo ""; echo ""; echo ""; echo ""; while($row = mysqli_fetch_array($groupContacts)) { echo ""; $name = $row['Name']; $tel = $row['Tel']; $email = $row['Email']; echo ""; echo ""; echo ""; echo ""; } echo "
"; echo "Naam"; echo ""; echo "Telefoon/GSM"; echo ""; echo "E-mail"; echo "
"; echo $name; echo ""; echo $tel; echo ""; echo $email; echo "
"; } function showTimeschedules($db, $groupId, $local, $exportToExcel) { $counter = 0; $timeSchedules = mysqli_query($db, "SELECT * FROM TimeSchedules WHERE GroupId = '$groupId' AND Local = '1'") or die ("Error: ". mysqli_error($db)); echo ""; while($row = mysqli_fetch_array($timeSchedules)) { echo ""; $day = $row['DayOfWeek']; $hour = $row['Hour']; $comment = $row['Comment']; echo ""; echo ""; echo ""; echo ""; $counter ++; } if($exportToExcel) { for($i = 1; $i <= $counter; $i ++) { echo ""; echo ""; echo ""; } } echo "
"; echo $this->getDayName($day); echo ""; $pos = strpos($hour, ":"); $newhour = substr_replace($hour, "u", $pos, 1); echo substr($newhour, 0, strLen($newhour) - 3); echo ""; echo $comment; echo "
"; echo ''; echo ""; echo ''; echo ""; echo ''; echo "
"; } function showAddresses($db, $groupId, $excludePrivate, $exportToExcel) { $groupAddresses = mysqli_query($db, "SELECT * FROM GroupAddresses where GroupId = '$groupId' AND Local = '1'") or die ("Error: ".mysqli_error($db)); echo ""; while($row = mysqli_fetch_array($groupAddresses)) { $local = $row['Local']; $Private = $row['Private']; if($excludePrivate == 0) { $Private = 0; } $address = $row['Address']; $addressDescr= $row['AddressDescr']; $placeId = $row['PlaceId']; $places = mysqli_query($db, "SELECT * FROM Places WHERE Id = '$placeId'") or die ("Error: ".mysqli_error($db)); $zipcode = ""; $city = ""; $province = ""; if($row2 = mysqli_fetch_array($places)) { $zipcode = $row2['Zipcode']; $city = $row2['City']; $provinceId = $row2['ProvinceId']; $provinces = mysqli_query($db, "SELECT * FROM Provinces where Id = '$provinceId'") or die ("Error: ".mysqli_error($db)); if($row3 = mysqli_fetch_array($provinces)) { $province = $row3['Name']; } } echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; } echo "
"; echo "Adres lokaal:"; echo ""; echo "Uurregeling vergaderingen:"; echo "
"; if($Private == 1) { echo "Adres van het lokaal op aanvraag.

"; echo $zipcode . " " . $city . "
" . $province; } else { echo ($addressDescr!= "" ? $addressDescr. "
" : "") . $address . "
" . $zipcode . " " . $city . "
" . $province; } echo "
"; $this->showTimeschedules($db, $groupId, $local, $exportToExcel); echo "
"; } function showGroups($strQuery, $excludePrivate, $exportToExcel = 0) { $db = $this->getConnection(); $groupQuery = mysqli_query($db, $strQuery) or die ("Error: ".mysqli_error($db)); while($row = mysqli_fetch_array($groupQuery)) { $groupId = $row[0]; $name = $row[1]; $comment = $row[2]; // toevoegen om groepen te verbergen (bovenaan ook , GR.OnSite, GR.PW ) $onsite = $row[3]; $PW = $row[4]; // er kan ook een voorwaarde volgens PW toegevoegd worden if ($onsite == 0) { echo "
"; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; $this->showAddresses($db, $groupId, $excludePrivate, $exportToExcel); if(!$excludePrivate) { $this->showContactPersons($db, $groupId); } echo "
"; echo "Groep"; if($excludePrivate) { echo " Detail (ledentoegang)"; } echo ""; echo "Vermelding"; echo "
"; echo ($name? "$name" : " "); echo ""; echo ($comment ? "$comment" : " "); echo "
"; echo "
"; echo "
"; } } } function showProvinces($filterProvince) { $db = $this->getConnection(); $groupProvinces = mysqli_query($db, "SELECT * FROM Provinces") or die ("Error: ".mysqli_error($db)); echo ""; } }