From: Raphaël Gertz Date: Sun, 13 Dec 2020 21:00:44 +0000 (+0100) Subject: Rewrite delay names X-Git-Tag: 0.1.7~48 X-Git-Url: https://git.rapsys.eu/airbundle/commitdiff_plain/66a391f05616bdd322e1d06f2d793727367e6c01 Rewrite delay names Use slot afterid Displace findAllPendingApplication before findBestApplicationById Rewrite findAllPendingApplication sql request to avoid unattributed sessions without usable application at some point Rewrite findBestApplicationById with optimal conditions --- diff --git a/Repository/SessionRepository.php b/Repository/SessionRepository.php index 5319207..ec952f9 100644 --- a/Repository/SessionRepository.php +++ b/Repository/SessionRepository.php @@ -17,11 +17,14 @@ class SessionRepository extends \Doctrine\ORM\EntityRepository { ///Set accuweather max number of hourly pages const ACCUWEATHER_HOURLY = 3; - ///Set user with bad behaviour delay - const BAD_DELAY = 3; + ///Set guest delay + const GUEST_DELAY = 2; - ///Set user with good behaviour delay - const GOOD_DELAY = 4; + ///Set regular delay + const REGULAR_DELAY = 3; + + ///Set senior + const SENIOR_DELAY = 4; ///Set glyphs //TODO: document utf-8 codes ? @@ -135,7 +138,7 @@ SQL; ->createNativeQuery($req, $rsm) ->setParameter('lid', $location) ->setParameter('uid', $user) - ->setParameter('gooddelay', self::GOOD_DELAY) + ->setParameter('gooddelay', self::SENIOR_DELAY) ->getOneOrNullResult(); } @@ -163,6 +166,8 @@ SQL; 'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp), 'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp), 'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp), + //Delay + ':afterid' => 4, "\t" => '', "\n" => ' ' ]; @@ -175,9 +180,9 @@ SELECT s.id, s.date, s.begin, - ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = 4, 1, 0) DAY) AS start, + ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start, s.length, - ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = 4, 1, 0) DAY) AS stop, + ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop, s.rainfall, s.rainrisk, s.realfeel, @@ -764,11 +769,11 @@ SQL; } /** - * Find every session pending application + * Find all session pending hourly weather * * @return array The sessions to update */ - public function findAllPendingApplication() { + public function findAllPendingHourlyWeather() { //Get entity manager $em = $this->getEntityManager(); @@ -780,22 +785,22 @@ SQL; //XXX: this allow to make this code table name independent $tables = [ 'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp), + 'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp), + //Accuweather + ':accuhourly' => self::ACCUWEATHER_HOURLY, + //Delay + ':afterid' => 4, "\t" => '', "\n" => ' ' ]; - //Select all sessions without application attributed with diff(start, now) <= if(diff(start, created) <= GOOD_DELAY day, diff(start, created)*3/4, GOOD_DELAY day) - //XXX: consider only unfinished sessions with stop > now - //XXX: consider as grace time first quarter (1/4) between creation and start time when below gooddelay - //XXX: we may remove ADDDATE(start, INTERVAL IF(s.slot_id = 4, 1, 0) DAY) used for after specificity - $req =<< NOW() AND - TIMEDIFF(ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = 4, 1, 0) DAY), NOW()) <= IF(DATEDIFF(ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = 4, 1, 0) DAY), s.created) <= :gooddelay, SEC_TO_TIME(TIME_TO_SEC(TIMEDIFF(ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = 4, 1, 0) DAY), s.created))*3/4), SEC_TO_TIME(:gooddelay*24*3600)) -ORDER BY ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = 4, 1, 0) DAY) ASC, s.created ASC +JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id) +WHERE ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) >= NOW() AND ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) < DATE(ADDDATE(NOW(), INTERVAL :accuhourly DAY)) SQL; //Replace bundle entity name by table name @@ -808,22 +813,36 @@ SQL; $rsm ->addEntityResult('RapsysAirBundle:Session', 's') ->addFieldResult('s', 'id', 'id') + ->addFieldResult('s', 'date', 'date') + ->addFieldResult('s', 'begin', 'begin') + ->addFieldResult('s', 'length', 'length') + ->addFieldResult('s', 'rainfall', 'rainfall') + ->addFieldResult('s', 'rainrisk', 'rainrisk') + ->addFieldResult('s', 'realfeel', 'realfeel') + ->addFieldResult('s', 'realfeelmin', 'realfeelmin') + ->addFieldResult('s', 'realfeelmax', 'realfeelmax') + ->addFieldResult('s', 'temperature', 'temperature') + ->addFieldResult('s', 'temperaturemin', 'temperaturemin') + ->addFieldResult('s', 'temperaturemax', 'temperaturemax') + ->addJoinedEntityResult('RapsysAirBundle:Slot', 'o', 's', 'slot') + ->addFieldResult('o', 'slot_id', 'id') + ->addJoinedEntityResult('RapsysAirBundle:Location', 'l', 's', 'location') + ->addFieldResult('l', 'location_id', 'id') + ->addFieldResult('l', 'zipcode', 'zipcode') ->addIndexBy('s', 'id'); //Send result return $em ->createNativeQuery($req, $rsm) - ->setParameter('limit', PHP_INT_MAX) - ->setParameter('gooddelay', self::GOOD_DELAY) ->getResult(); } /** - * Find all session pending hourly weather + * Find all session pending daily weather * * @return array The sessions to update */ - public function findAllPendingHourlyWeather() { + public function findAllPendingDailyWeather() { //Get entity manager $em = $this->getEntityManager(); @@ -835,21 +854,23 @@ SQL; //XXX: this allow to make this code table name independent $tables = [ 'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp), - 'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp), 'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp), + //Accuweather + ':accudaily' => self::ACCUWEATHER_DAILY, + ':accuhourly' => self::ACCUWEATHER_HOURLY, + //Delay + ':afterid' => 4, "\t" => '', "\n" => ' ' ]; - //Select all sessions starting and stopping in the next 3 days - //XXX: select session starting after now and stopping before date(now)+3d as accuweather only provide hourly data for the next 3 days (INTERVAL 3 DAY) - //XXX: we may remove ADDDATE(start, INTERVAL IF(s.slot_id = 4, 1, 0) DAY) used for after specificity + //Select all sessions stopping after next 3 days + //XXX: select session stopping after or equal date(now)+3d as accuweather only provide hourly data for the next 3 days (INTERVAL 3 DAY) $req = <<= NOW() AND ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = 4, 1, 0) DAY) < DATE(ADDDATE(NOW(), INTERVAL :accuhourly DAY)) +WHERE ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) >= DATE(ADDDATE(NOW(), INTERVAL :accuhourly DAY)) AND ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) < DATE(ADDDATE(NOW(), INTERVAL :accudaily DAY)) SQL; //Replace bundle entity name by table name @@ -875,7 +896,6 @@ SQL; ->addFieldResult('s', 'temperaturemax', 'temperaturemax') ->addJoinedEntityResult('RapsysAirBundle:Slot', 'o', 's', 'slot') ->addFieldResult('o', 'slot_id', 'id') - ->addFieldResult('o', 'title', 'title') ->addJoinedEntityResult('RapsysAirBundle:Location', 'l', 's', 'location') ->addFieldResult('l', 'location_id', 'id') ->addFieldResult('l', 'zipcode', 'zipcode') @@ -884,16 +904,15 @@ SQL; //Send result return $em ->createNativeQuery($req, $rsm) - ->setParameter('accuhourly', self::ACCUWEATHER_HOURLY) ->getResult(); } /** - * Find all session pending daily weather + * Find every session pending application * * @return array The sessions to update */ - public function findAllPendingDailyWeather() { + public function findAllPendingApplication() { //Get entity manager $em = $this->getEntityManager(); @@ -904,24 +923,36 @@ SQL; //Get quoted table names //XXX: this allow to make this code table name independent $tables = [ + 'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp), 'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp), - 'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp), - 'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp), + //Delay + ':regulardelay' => self::REGULAR_DELAY * 24 * 3600, + ':seniordelay' => self::SENIOR_DELAY * 24 * 3600, + //Slot + ':afterid' => 4, "\t" => '', "\n" => ' ' ]; - //Select all sessions stopping after next 3 days - //XXX: select session stopping after or equal date(now)+3d as accuweather only provide hourly data for the next 3 days (INTERVAL 3 DAY) - //XXX: we may remove ADDDATE(start, INTERVAL IF(s.slot_id = 4, 1, 0) DAY) used for after specificity - $req = <<= DATE(ADDDATE(NOW(), INTERVAL :accuhourly DAY)) AND ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = 4, 1, 0) DAY) < DATE(ADDDATE(NOW(), INTERVAL :accudaily DAY)) + //Select all sessions not locked without application or canceled application within attribution period + //XXX: DIFF(start, now) <= IF(DIFF(start, created) <= SENIOR_DELAY in DAY, DIFF(start, created) * 3 / 4, SENIOR_DELAY) + //TODO: remonter les données pour le mail ? + $req =<<addEntityResult('RapsysAirBundle:Session', 's') ->addFieldResult('s', 'id', 'id') - ->addFieldResult('s', 'date', 'date') - ->addFieldResult('s', 'begin', 'begin') - ->addFieldResult('s', 'length', 'length') - ->addFieldResult('s', 'rainfall', 'rainfall') - ->addFieldResult('s', 'rainrisk', 'rainrisk') - ->addFieldResult('s', 'realfeel', 'realfeel') - ->addFieldResult('s', 'realfeelmin', 'realfeelmin') - ->addFieldResult('s', 'realfeelmax', 'realfeelmax') - ->addFieldResult('s', 'temperature', 'temperature') - ->addFieldResult('s', 'temperaturemin', 'temperaturemin') - ->addFieldResult('s', 'temperaturemax', 'temperaturemax') - ->addJoinedEntityResult('RapsysAirBundle:Slot', 'o', 's', 'slot') - ->addFieldResult('o', 'slot_id', 'id') - ->addFieldResult('o', 'title', 'title') - ->addJoinedEntityResult('RapsysAirBundle:Location', 'l', 's', 'location') - ->addFieldResult('l', 'location_id', 'id') - ->addFieldResult('l', 'zipcode', 'zipcode') ->addIndexBy('s', 'id'); //Send result return $em ->createNativeQuery($req, $rsm) - ->setParameter('accudaily', self::ACCUWEATHER_DAILY) - ->setParameter('accuhourly', self::ACCUWEATHER_HOURLY) ->getResult(); } @@ -977,83 +989,154 @@ SQL; //XXX: this allow to make this code table name independent $tables = [ 'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp), + 'RapsysAirBundle:GroupUser' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('groups'), $em->getClassMetadata('RapsysAirBundle:User'), $dp), + 'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp), 'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp), + //XXX: Set limit used to workaround mariadb subselect optimization + ':limit' => PHP_INT_MAX, + //Delay + ':guestdelay' => self::GUEST_DELAY * 24 * 3600, + ':regulardelay' => self::REGULAR_DELAY * 24 * 3600, + ':seniordelay' => self::SENIOR_DELAY * 24 * 3600, + //Group + ':guestid' => 2, + ':regularid' => 3, + ':seniorid' => 4, + //Slot + ':afternoonid' => 2, + ':eveningid' => 3, + ':afterid' => 4, + //XXX: days since last session after which guest regain normal priority + ':guestwait' => 30, + //XXX: session count until considered at regular delay + ':scount' => 5, + //XXX: pn_ratio over which considered at regular delay + ':pnratio' => 1, + //XXX: tr_ratio diff over which considered at regular delay + ':trdiff' => 5, "\t" => '', "\n" => ' ' ]; /** - * Query session applications ranked by score, created and user_id + * Query session applications ranked by location score, global score, created and user_id + * + * @xxx guest (or less) with application on location within 30 day are only considered within guestdelay * - * @xxx User with bad behaviour application are excluded until remaining <= baddelay: - * - with (count(premium = 1)+1)/(count(premium = 0)+1) >= 1 - * - with count(sessions) <= 5 - * - with average(temperature) >= average(temperature other) + 10 + * @xxx regular (or less) premium application on hotspot are only considered within regulardelay * - * @xxx Magic happen on this line: - * WHERE IF(d.pnp_rate >= 1, d.remaining <= SEC_TO_TIME(:baddelay), 1) AND IF(d.s_count <= 5, d.remaining <= SEC_TO_TIME(:baddelay), 1) AND IF(d.tr_rate >= (d.otr_rate + 10), d.remaining <= SEC_TO_TIME(:baddelay), 1) + * @xxx senior (or less) with 5 or less session on location are only considered within seniordelay * - * @todo Limit score on last year only ??? - * AND DATEDIFF(s.date, NOW()) <= 365 + * @xxx senior (or less) with l_pn_ratio >= 1 are only considered within seniordelay * - * TODO: we may add ADDDATE(start, INTERVAL IF(s.slot_id = 4, 1, 0) DAY) used for after specificity + * @xxx senior (or less) with l_tr_ratio >= (o_tr_ratio + 5) are only considered within seniordelay + * + * @xxx only consider session within one year (may be unaccurate by the day with after session) + * + * @xxx rainfall may not be accessible for previous session and other session at d-4 (only at d-2) + * + * @todo ??? feedback the data to inform the rejected users ??? */ $req = << s.date - INTERVAL 1 YEAR) + LEFT JOIN RapsysAirBundle:Application AS a2 ON (a2.id = s2.application_id AND a2.user_id = a.user_id AND (a2.canceled IS NULL OR TIMESTAMPDIFF(DAY, a2.canceled, ADDDATE(ADDTIME(s2.date, s2.begin), INTERVAL IF(s2.slot_id = :afterid, 1, 0) DAY)) < 1)) + WHERE s.id = :sid + GROUP BY a.id + ORDER BY NULL + LIMIT 0, :limit + ) AS b + LEFT JOIN RapsysAirBundle:Session AS s3 ON (s3.id != b.session_id AND s3.application_id IS NOT NULL AND s3.locked IS NULL AND s3.date > b.date - INTERVAL 1 YEAR) + LEFT JOIN RapsysAirBundle:Application AS a3 ON (a3.id = s3.application_id AND a3.user_id = b.user_id AND (a3.canceled IS NULL OR TIMESTAMPDIFF(DAY, a3.canceled, ADDDATE(ADDTIME(s3.date, s3.begin), INTERVAL IF(s3.slot_id = :afterid, 1, 0) DAY)) < 1)) + GROUP BY b.id ORDER BY NULL LIMIT 0, :limit - ) AS b - LEFT JOIN sessions AS s3 ON (s3.id != b.session_id AND s3.application_id IS NOT NULL) - LEFT JOIN applications AS a3 ON (a3.id = s3.application_id AND a3.user_id = b.user_id AND (a3.canceled IS NULL OR TIMESTAMPDIFF(DAY, a3.canceled, ADDTIME(s3.date, s3.begin)) < 1)) - GROUP BY b.id + ) AS c + LEFT JOIN RapsysAirBundle:Session AS s4 ON (s4.id != c.session_id AND s4.location_id = c.location_id AND s4.application_id IS NOT NULL AND s4.locked IS NULL AND s4.date > c.date - INTERVAL 1 YEAR) + LEFT JOIN RapsysAirBundle:Application AS a4 ON (a4.id = s4.application_id AND a4.user_id != c.user_id AND (a4.canceled IS NULL OR TIMESTAMPDIFF(DAY, a4.canceled, ADDDATE(ADDTIME(s4.date, s4.begin), INTERVAL IF(s4.slot_id = :afterid, 1, 0) DAY)) < 1)) + GROUP BY c.id ORDER BY NULL LIMIT 0, :limit - ) AS c - LEFT JOIN sessions AS s4 ON (s4.id != c.session_id AND s4.application_id IS NOT NULL AND s4.temperature IS NOT NULL AND s4.rainfall IS NOT NULL) - LEFT JOIN applications AS a4 ON (a4.id = s4.application_id AND a4.user_id != c.user_id AND (a4.canceled IS NULL OR TIMESTAMPDIFF(DAY, a4.canceled, ADDTIME(s4.date, s4.begin)) < 1)) - GROUP BY c.id - ORDER BY NULL + ) AS d + LEFT JOIN RapsysAirBundle:GroupUser AS gu ON (gu.user_id = d.user_id) + GROUP BY d.id LIMIT 0, :limit -) AS d -WHERE IF(d.pnp_rate >= 1, d.remaining <= SEC_TO_TIME(:baddelay), 1) AND IF(d.s_count <= 5, d.remaining <= SEC_TO_TIME(:baddelay), 1) AND IF(d.tr_rate >= (d.otr_rate + 10), d.remaining <= SEC_TO_TIME(:baddelay), 1) -ORDER BY d.score ASC, d.created ASC, d.user_id ASC +) AS e +WHERE + IF(e.group_id <= :guestid AND e.l_previous <= :guestwait, e.remaining <= :guestdelay, 1) AND + IF(e.group_id <= :regularid AND e.premium = 1 AND e.hotspot = 1, e.remaining <= :regulardelay, 1) AND + IF(e.group_id <= :seniorid AND e.l_count <= :scount, e.remaining <= :regulardelay, 1) AND + IF(e.group_id <= :seniorid AND e.l_pn_ratio >= :pnratio, e.remaining <= :regulardelay, 1) AND + IF(e.group_id <= :seniorid AND e.l_tr_ratio >= (e.o_tr_ratio + :trdiff), e.remaining <= :regulardelay, 1) +ORDER BY e.l_score ASC, e.g_score ASC, e.created ASC, e.user_id ASC SQL; //Replace bundle entity name by table name @@ -1080,8 +1163,6 @@ SQL; $applications = $em ->createNativeQuery($req, $rsm) ->setParameter('sid', $id) - ->setParameter('baddelay', self::BAD_DELAY*24*3600) - ->setParameter('limit', PHP_INT_MAX) //XXX: removed, we update score before returning best candidate //->getOneOrNullResult(Query::HYDRATE_SINGLE_SCALAR); ->getResult();