Author Topic: Routes Traveled/Routes Clinched bug  (Read 3431 times)

0 Members and 1 Guest are viewing this topic.

Offline froggie

  • TM Collaborator
  • Sr. Member
  • *****
  • Posts: 492
  • Last Login:Yesterday at 11:49:56 am
Routes Traveled/Routes Clinched bug
« on: August 01, 2017, 11:01:06 am »
Noticed this bug over the last couple weeks...see attached graphics for my example from Wisconsin.  Was waiting to see if it was a temporary thing before I reported.

According to my personal statistics for Wisconsin, I'm #15 for both routes traveled/driven and routes clinched.  However, when I go down to the user listings and sort by routes traveled, I'm actually #10.

What I've found is that the routes clinched ranking is generally correct (it is in my Wisconsin example), but the routes traveled ranking is matching the routes clinched ranking even when that is not the case.

Offline yakra

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 2245
  • Last Login:October 18, 2019, 03:13:53 pm
Re: Routes Traveled/Routes Clinched bug
« Reply #1 on: August 01, 2017, 02:28:06 pm »
It also appears that the "Active+Preview" half of the "Travelers in Region WI" table cannot be sorted. Don;t know whether this has been noted elsewhere.

Offline froggie

  • TM Collaborator
  • Sr. Member
  • *****
  • Posts: 492
  • Last Login:Yesterday at 11:49:56 am
Re: Routes Traveled/Routes Clinched bug
« Reply #2 on: August 02, 2017, 12:11:27 am »
Also, in case I wasn't clear in my OP, I've noticed this in other regions besides Wisconsin.

Offline Jim

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 1539
  • Last Login:Today at 09:28:59 pm

Offline panda80

  • TM Collaborator
  • Full Member
  • *****
  • Posts: 115
  • Last Login:Today at 03:21:03 pm
Question About Accuracy of Routes Driven Ranking on Region Page
« Reply #4 on: August 07, 2018, 03:56:07 am »
Hi,

I discovered a bug when looking at the statistics for Austria. There I travelled the most routes but I am ranked 2nd (by the the most travelled routes), after cinxx, who travelled on less roads, but clinched more of them.

Offline Jim

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 1539
  • Last Login:Today at 09:28:59 pm
Re: Minor statistics bug
« Reply #5 on: August 07, 2018, 10:44:27 am »

Offline mikeandkristie

  • Jr. Member
  • **
  • Posts: 77
  • Last Login:Yesterday at 04:00:16 am
Question About Accuracy of Routes Driven Ranking on Region Page
« Reply #6 on: April 15, 2019, 06:44:28 pm »
On the Region Page (region.php), I'm concerned that the ranking value that is given in the top overall region statistics box is incorrect.  This is for both the active and active & preview numbers.  I had noticed that I was coming up with different values just when glancing at some of the states where we are higher up the mileage list.  By that I mean that when I scrolled down to the travelers list and counted up who had more traveled routes, I wasn't getting a match.

For example, in North Carolina with 268 routes, I could count 7 folks with more putting us in 8th place.  But the page said 10th. 

http://travelmapping.net/user/region.php?u=mikeandkristie&rg=NC

I dug into it deeper today, pasting the columns into Excel so I could sort them easier and comparing the ranking values.  I looked at NC and VA, our top two states and then started going alphabetically and got through Georgia.  No matches yet, so I figured I would report it.  I can post the spreadsheet I have so far if necessary.  The variances are even wider for states where we don't have as much mileage and are much farther down the list.

I can say that the clinched routes are looking correct.  So, I'd compare how those two different sets of values are being calculated.

Mike

Offline mikeandkristie

  • Jr. Member
  • **
  • Posts: 77
  • Last Login:Yesterday at 04:00:16 am
Re: Question About Accuracy of Routes Driven Ranking on Region Page
« Reply #7 on: April 23, 2019, 12:21:10 pm »
Feel free to move this to another forum if I picked the wrong one.

Offline Jim

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 1539
  • Last Login:Today at 09:28:59 pm
Re: Question About Accuracy of Routes Driven Ranking on Region Page
« Reply #8 on: April 23, 2019, 02:30:02 pm »
You're good posting it here.  There's a GitHub Issue for it (at least I think it's the same): https://github.com/TravelMapping/Web/issues/278

It's been a long time since I dug into the web front end to fix bugs or develop new pages.  We had someone starting on a whole new implementation, but I haven't heard about that in a long time so I don't think it's happening.  Maybe this will come to the top of my list this summer.

Offline mikeandkristie

  • Jr. Member
  • **
  • Posts: 77
  • Last Login:Yesterday at 04:00:16 am
Re: Question About Accuracy of Routes Driven Ranking on Region Page
« Reply #9 on: April 23, 2019, 03:03:16 pm »
Thanks, Jim.  I didn't realize there was an issues list.  Something else to browse through some time.

Mike

Offline mikeandkristie

  • Jr. Member
  • **
  • Posts: 77
  • Last Login:Yesterday at 04:00:16 am
Re: Question About Accuracy of Routes Driven Ranking on Region Page
« Reply #10 on: April 24, 2019, 12:07:00 pm »
I may have figured it out.  I hadn't really looked through all of the stuff on GitHub other than a little bit at highway and user data to track changes.  But, I did find my way to region.php.  And to lib/tmphpfuncs.php, which it calls for tm_fetch_user_row_with_rank among other functions.  I'm a C# guy, not PHP, but I was able to muddle my way through it to trace what it was doing.

It looks like the rankings are done with calls to that function and sending in the result set and what column it wants to check.  I copied all of the calls so I could keep things straight.  The numbers at the end are the line numbers.

$row = tm_fetch_user_row_with_rank($activeRes, 'activePercentage');                 189
$row = tm_fetch_user_row_with_rank($activePreviewRes, 'activePreviewPercentage');   212

$row = tm_fetch_user_row_with_rank($activeDrivenRes, 'clinchedPct');                257
$row = tm_fetch_user_row_with_rank($activeDrivenRes, 'drivenPct');                  264

$row = tm_fetch_user_row_with_rank($activePreviewDrivenRes, 'clinchedPct');         290
$row = tm_fetch_user_row_with_rank($activePreviewDrivenRes, 'drivenPct');           297

Since they all call the same function, how come 4 out of 6 come out correctly?  Here's the while loop in the function. 

    while($row['traveler'] != $tmuser && $row = $res->fetch_assoc()) {
        if ($score != $row[$rankBy])
        {
            $score = $row[$rankBy];
            $rank = $nextRank;
        }
        $nextRank++;
        //error_log("($rank, {$row['traveler']}, {$row[$rankBy]})");
    }

It keeps going where there are still rows and it hasn't hit the current user.  Both the clinched and driven ranks are found via the same recordset ($activeDrivenRes or $activePreviewDrivenRes).   Looking at the ORDER BY in the SQL that it calls for them, they are both sorting on clinchedPct.  That works fine for the clinched ranking.  But, since it is still sending it in sorted by cliched for the driven, it is counting down until it gets to you but the numbers aren't sorted for driven.  I believe that is why the numbers are coming out wrong.  There could also be some issues on how it is counting the ranking when there are ties.  It really comes down to what order you are in the list when you all have the same clinched route numbers.  There is no secondary sort on traveler.

I'm trying to "run" the queries in my head as I don't have your database, but I think you may be able to use the SQL RANK() function to rank everybody in the main query.  Then, you could just grab the rank numbers off of the row for the current user to display like you do for the other values instead of iterating through the results in the tm_fetch_user_row_with_rank function.  I usually have to play with what columns are in the PARTITION BY and ORDER BY a bit to get it to group the way I want it.  I might not have this quite right as I'm just doing the query in my head and on a table with a similar scenario that I have access to.  Since we want all of the users in the region sorted, I don't think it needs any PARTITION BY.  Here's my attempt on one of the queries with the ranks in bold. 

-- $activePreviewDrivenRes (Overall [] Region Statistics - Row 2-3, Right Side)
SELECT traveler,
    COUNT(cr.route) AS driven,
    SUM(cr.clinched) AS clinched,
    ROUND(COUNT(cr.route) / $totalActivePreviewRoutes * 100, 2) as drivenPct,
    ROUND(sum(cr.clinched) / $totalActivePreviewRoutes * 100, 2) as clinchedPct,
    RANK() OVER (ORDER BY COUNT(cr.route)) AS drivenRank,
    RANK() OVER (ORDER BY SUM(cr.clinched)) AS clinchedRank

FROM routes AS r
    LEFT JOIN clinchedRoutes AS cr ON cr.route = r.root
    LEFT JOIN systems ON r.systemName = systems.systemName
WHERE (r.region = '$region' AND (systems.level='preview' OR systems.level='active'))
GROUP BY traveler
ORDER BY clinchedPct DESC;

Mike

Offline yakra

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 2245
  • Last Login:October 18, 2019, 03:13:53 pm
Re: Question About Accuracy of Routes Driven Ranking on Region Page
« Reply #11 on: April 24, 2019, 12:44:54 pm »
I don't speak PHP, and don't know how well it'll work, but in any case, well done! :D

Offline Jim

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 1539
  • Last Login:Today at 09:28:59 pm
Re: Question About Accuracy of Routes Driven Ranking on Region Page
« Reply #12 on: April 24, 2019, 09:14:29 pm »
I'd be very happy to create you an account on the server so you could try things out with the DB!  Email me.

Offline yakra

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 2245
  • Last Login:October 18, 2019, 03:13:53 pm
« Last Edit: July 11, 2019, 01:07:13 pm by yakra »

Offline Jim

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 1539
  • Last Login:Today at 09:28:59 pm
Re: Question About Accuracy of Routes Driven Ranking on Region Page
« Reply #14 on: July 11, 2019, 02:04:08 pm »
I have not investigated it.