/**
* 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);
}
