/** * Return the validations around the users current position. * * @param float $lat User positions latitude. * @param float $lng User positions longitude. * @param integer $limit Amount of validations to return. * @param integer $radius Radius around the user position to look for validations. * * @return string JSON-encoded validations around the users current position */ public function getValidationsByOwnPosition($lat, $lng, $limit, $radius) { $limit = empty($limit) ? 20 : $limit; $radius = empty($radius) ? 5000 : $radius; $userPosition = PostGisSqlHelper::getLatLngGeom($lat, $lng); //aggregation1 : join promotion with promo2error_type $sql = "WITH aggregation1 AS ("; $sql .= "SELECT p.id AS promo_id, "; $sql .= " p.startdate, "; $sql .= " p.enddate, "; $sql .= " p.geom AS promogeom, "; $sql .= " pm.error_type, "; $sql .= " pm.validation_extra_coins AS extra_coins "; $sql .= "FROM kort.promotion p "; $sql .= "INNER JOIN kort.promo2mission pm ON p.id=pm.promo_id "; $sql .= "WHERE p.startdate < now() AND p.enddate > now())"; //aggregation2: get limited validations around the user's position as before $sql .= ", aggregation2 AS ("; $sql .= "select * from ("; $sql .= "select id AS validationid, "; $sql .= " type, "; $sql .= " view_type,"; $sql .= " fix_user_id, "; $sql .= " osm_id, "; $sql .= " osm_type, "; $sql .= " title, "; $sql .= " fixmessage, "; $sql .= " falsepositive, "; $sql .= " question, "; $sql .= " bug_question, "; $sql .= " vote_koin_count, "; $sql .= " latitude, "; $sql .= " longitude, "; $sql .= " upratings, "; $sql .= " downratings, "; $sql .= " required_votes,geom AS validationgeom,"; $sql .= " txt1, "; $sql .= " txt2, "; $sql .= " txt3, "; $sql .= " txt4, "; $sql .= " txt5 "; $sql .= " from kort.validations"; $sql .= " where fix_user_id != " . $_SESSION['user_id'] . " "; $sql .= " AND not exists (select 1 "; $sql .= " from kort.vote v "; $sql .= " where v.fix_id = id "; $sql .= " and v.user_id = " . $_SESSION['user_id'] . ")"; $sql .= " order by " . "geom <-> " . PostGisSqlHelper::getLatLngGeom($lat, $lng); $sql .= " limit " . $limit; $sql .= ") t"; $sql .= " where " . "ST_Distance_Sphere(validationgeom," . $userPosition . ") <= " . $radius . " )"; //aggregation3: join aggregation2 and aggregation1 and check where validation_geom is within promotion_geom. //As result, we get all the validations around the user's position who actualy belongs to a active promotion $sql .= ", aggregation3 AS ("; $sql .= "SELECT ag2.validationid AS validationidtemp, "; $sql .= " ag1.promo_id, "; $sql .= " ag1.extra_coins "; $sql .= "FROM aggregation2 ag2 "; $sql .= "INNER JOIN aggregation1 ag1 ON ag2.type=ag1.error_type "; $sql .= "WHERE ST_WITHIN(ag2.validationgeom, ag1.promogeom))"; //left join the validations around the user (aggregation2) with the subset of the validations //who belongs to a promotion (aggregation3) //=> the fields promo_id and extra_coins is either null or holds the corresponding promotion values $sql .= "SELECT validationid AS id,"; $sql .= " type, "; $sql .= " view_type, "; $sql .= " fix_user_id, "; $sql .= " osm_id, "; $sql .= " osm_type, "; $sql .= " title, "; $sql .= " fixmessage, "; $sql .= " falsepositive, "; $sql .= " question, "; $sql .= " bug_question, "; $sql .= " vote_koin_count, "; $sql .= " latitude, "; $sql .= " longitude, "; $sql .= " upratings, "; $sql .= " downratings, "; $sql .= " required_votes, "; $sql .= " validationgeom AS geom, "; $sql .= " txt1, "; $sql .= " txt2, "; $sql .= " txt3, "; $sql .= " txt4, "; $sql .= " txt5, "; $sql .= " promo_id, "; $sql .= " extra_coins "; $sql .= "FROM aggregation2 ag2 "; $sql .= "LEFT JOIN aggregation3 ag3 ON ag2.validationid=ag3.validationidtemp"; $params = array(); $params['sql'] = $sql; $params['type'] = "SQL"; $position = $this->getDbProxy()->addToTransaction($params); $result = json_decode($this->getDbProxy()->sendTransaction(), true); $validationData = array_map(array($this, "convertBoolean"), $result[$position - 1]); $validationData = array_map(array($this, "translateValidation"), $validationData); return json_encode($validationData); }
/** * Returns missions around the users position. * * @param float $lat Latitude of the user position. * @param float $lng Longitude of the user position. * @param integer $limit Amount of bugs to return. * @param integer $radius Radius around the users position to look for. * * @return string JSON-formatted bugs */ public function getBugsByOwnPosition($lat, $lng, $limit, $radius) { $limit = empty($limit) ? 20 : $limit; $radius = empty($radius) ? 5000 : $radius; $userPosition = PostGisSqlHelper::getLatLngGeom($lat, $lng); //aggregation1 : join promotion with promo2error_type $sql = "WITH aggregation1 AS ("; $sql .= "SELECT p.id AS promo_id, "; $sql .= " p.startdate, "; $sql .= " p.enddate, "; $sql .= " p.geom AS promogeom, "; $sql .= " pm.error_type, "; $sql .= " pm.mission_extra_coins AS extra_coins "; $sql .= "FROM kort.promotion p "; $sql .= "INNER JOIN kort.promo2mission pm ON p.id=pm.promo_id "; $sql .= "WHERE p.startdate < now() AND p.enddate > now())"; //aggregation2: get limited missions around the user's position as before $sql .= ", aggregation2 AS ("; $sql .= "SELECT * FROM ("; $sql .= "SELECT id AS missionid, "; $sql .= " schema, "; $sql .= " type, "; $sql .= " osm_id, "; $sql .= " osm_type, "; $sql .= " title, "; $sql .= " description, "; $sql .= " latitude, "; $sql .= " longitude, "; $sql .= " view_type, "; $sql .= " answer_placeholder, "; $sql .= " fix_koin_count, "; $sql .= " geom AS missiongeom, "; $sql .= " txt1, "; $sql .= " txt2, "; $sql .= " txt3, "; $sql .= " txt4, "; $sql .= " txt5 "; $sql .= "FROM kort.errors"; $sql .= " ORDER BY " . "geom <-> " . PostGisSqlHelper::getLatLngGeom($lat, $lng); $sql .= " LIMIT " . $limit; $sql .= ") t"; $sql .= " WHERE " . "ST_Distance_Sphere(missiongeom," . $userPosition . ") <= " . $radius . " )"; //aggregation3: join aggregation2 and aggregation1 and check where mission_geom is within promotion_geom. //As result, we get all the missions around the user's position who actualy belongs to a active promotion $sql .= ", aggregation3 AS ("; $sql .= "SELECT ag2.missionid AS missionidtemp, "; $sql .= " ag2.schema AS schematemp, "; $sql .= " ag1.promo_id, "; $sql .= " ag1.extra_coins "; $sql .= "FROM aggregation2 ag2 "; $sql .= "INNER JOIN aggregation1 ag1 ON ag2.type=ag1.error_type "; $sql .= "WHERE ST_WITHIN(ag2.missiongeom, ag1.promogeom))"; //left join the missions around the user (aggregation2) with the subset of the missions //who belongs to a promotion (aggregation3) => the fields promo_id and extra_coins is either //null or holds the corresponding promotion values $sql .= "SELECT missionid AS id, "; $sql .= " schema,type, "; $sql .= " osm_id, "; $sql .= " osm_type, "; $sql .= " title, "; $sql .= " description, "; $sql .= " latitude, "; $sql .= " longitude, "; $sql .= " view_type, "; $sql .= " answer_placeholder, "; $sql .= " fix_koin_count,missiongeom AS geom, "; $sql .= " txt1, "; $sql .= " txt2, "; $sql .= " txt3, "; $sql .= " txt4, "; $sql .= " txt5, "; $sql .= " promo_id, "; $sql .= " extra_coins "; $sql .= "FROM aggregation2 ag2 "; $sql .= "LEFT JOIN aggregation3 ag3 ON ((ag2.missionid=ag3.missionidtemp) AND (ag2.schema=ag3.schematemp))"; $params = array(); $params['sql'] = $sql; $params['type'] = "SQL"; $position = $this->getDbProxy()->addToTransaction($params); $result = json_decode($this->getDbProxy()->sendTransaction(), true); $translatedData = array_map(array($this, "translateBug"), $result[$position - 1]); return json_encode($translatedData); }