Goal difference bonus – points for close predictions

Resolved iul
(@iul)

12 years, 1 month ago

Hello,
as I was saying in another topic I opened yesterday, I am planning on using this plugin for rugby matches, and the problem is that that sport is fairly high scoring, so predicting the exact points (“goals”) difference is very unlikely.

I was wondering if it would be possible to add another “Scoring Option” where users would get some points for predicting a point difference within a certain range of the point difference that actually happened. For example if a game ends with a points(goals) difference of 17 and a user predicted a points difference within 5 points of “17” then they would get some scoring points. So, any user that has picked any points difference between 12 and 22 would get some ranking points and those that have picked precisely “17” would get more ranking points.

Also, I would like to know if it’s possible to give the “Goal difference bonus” for games that end up in draws. It’s a bit odd that they’re not given for draws. Why is that? Is it due to some limitations of the plugin or is it just because that’s how you considered it should be? 🙂

Thank you for your time.

https://wordpress.org/plugins/football-pool/

Viewing 12 replies - 1 through 12 (of 12 total)

Plugin Author AntoineH
(@antoineh)

12 years, 1 month ago
Hi,

Setting a tolerance for the goal difference point should be possible, but requires changing the calculation method which has some large SQL statements and also the calc_score() function. So without PHP and SQL knowledge it will be hard to accomplish.

You should change this part in the score calculation method:
```
, IF( m.home_score = p.home_score AND m.away_score = p.away_score, NULL,
	IF(
		m.home_score <> m.away_score AND
		( CAST( m.home_score AS SIGNED ) - CAST( p.home_score AS SIGNED ) )
		=
		( CAST( m.away_score AS SIGNED ) - CAST( p.away_score AS SIGNED ) )
		, 1, NULL
	)
  ) AS goal_diff_bonus
```
The goal difference was added once as a request for another user so there is no real reason why goal difference is not counted for draws; at least not from my perspective. I just made what the wish was. I’ve never used it myself.

p.s. changing the code of the plugin breaks the possibility to upgrade to a newer version later.
Thread Starter iul
(@iul)

12 years, 1 month ago

Again, thank you for your super fast, comprehensive response. My first request seems too complicated for me to implement, but if I could manage to do the second one I guess I could use it as it is, so, could you please tell me how to make the plugin give the “Goal difference bonus” for draws too?

Thanks.
Plugin Author AntoineH
(@antoineh)

12 years, 1 month ago
The following is untested, so it may contain bugs or not do exactly what you want, but it should give you a start.

The calc_score() function in classes/class-football-pool-pool.php contains this line:

if ( ! $full && $home != $away && ( $home - $user_home ) == ( $away - $user_away ) ) {

That would have to change to:

if ( ( $home - $user_home ) == ( $away - $user_away ) ) {

The admin/class-football-pool-admin-score-calculation.php contains this sql part:
```
, IF( m.home_score = p.home_score AND m.away_score = p.away_score, NULL,
	IF(
		m.home_score <> m.away_score AND
		( CAST( m.home_score AS SIGNED ) - CAST( p.home_score AS SIGNED ) )
		=
		( CAST( m.away_score AS SIGNED ) - CAST( p.away_score AS SIGNED ) )
		, 1, NULL
	)
  ) AS goal_diff_bonus
```
change that to:
```
, IF( ( CAST( m.home_score AS SIGNED ) - CAST( p.home_score AS SIGNED ) )
		= ( CAST( m.away_score AS SIGNED ) - CAST( p.away_score AS SIGNED ) )
		, 1, NULL
  ) AS goal_diff_bonus
```
Thread Starter iul
(@iul)

12 years, 1 month ago

Thank you for the solution offered. There is a problem though.

classes/class-football-pool-pool.php doesn’t contain
if ( ! $full && $home != $away && ( $home - $user_home ) == ( $away - $user_away ) ) {
anywhere.
I’ve also searched only for “$away” and it doesn’t even contain that.

Thread Starter iul
(@iul)

12 years, 1 month ago

Sorry, I was wrong. I was looking at classes/class-football-pool.php

Thread Starter iul
(@iul)

12 years, 1 month ago

OK, so I managed to make the changes you suggested. Whenever some score calculations are supposed to be done, even if none of the predictions and results are draws, I get this error:
“Step 1: Something went wrong while (re)calculating the scores. Please check if TRUNCATE/DROP or DELETE rights are available at the database and try again.”

Can you please take a look at the changes made to see what is going wrong?
Thanks.

Plugin Author AntoineH
(@antoineh)

12 years, 1 month ago

Sounds like a copy&paste error. Did you check your error log for the exact error message? See this page for details on setting debug information on.

Thread Starter iul
(@iul)

12 years, 1 month ago

When you say “a copy&paste error” do you mean I didn’t correctly paste the code you gave me?
I’ve managed to activate debugging and here is the error I get:

WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘) AS goal_diff_bonus
, 0 AS ranking
, 1 AS ranking_id
‘ at line 26 for query INSERT INTO pool_wp_rwno_scorehistory
( score_order, type, score_date, source_id, user_id
, score, full, toto, goal_bonus, goal_diff_bonus
, ranking, ranking_id )
SELECT
0, 0 AS score_type, m.play_date AS score_date, m.id AS match_id, u.ID AS user_id
, IF ( p.has_joker = 1, 1, 1 ) AS score
, IF ( m.home_score = p.home_score AND m.away_score = p.away_score, 1, NULL ) AS full
, IF ( m.home_score = p.home_score AND m.away_score = p.away_score, NULL,
IF (
IF ( m.home_score > m.away_score, 1, IF ( m.home_score = m.away_score, 3, 2 ) )
=
IF ( p.home_score > p.away_score, 1, IF ( p.home_score = p.away_score, 3, 2 ) )
, IF ( p.home_score IS NULL OR p.away_score IS NULL, NULL, 1 )
, NULL
)
) AS toto
, IF ( m.home_score = p.home_score,
IF ( m.away_score = p.away_score, 2, 1 ),
IF ( m.away_score = p.away_score, 1, NULL )
) AS goal_bonus
, IF( ( CAST( m.home_score AS SIGNED ) – CAST( p.home_score AS SIGNED ) )
= ( CAST( m.away_score AS SIGNED ) – CAST( p.away_score AS SIGNED ) )
, 1, NULL
)
) AS goal_diff_bonus
, 0 AS ranking
, 1 AS ranking_id
FROM wp_rwno_users u
LEFT OUTER JOIN pool_wp_rwno_matches m ON ( 1 = 1 )
LEFT OUTER JOIN pool_wp_rwno_predictions p
ON ( p.match_id = m.id AND ( p.user_id = u.ID OR p.user_id IS NULL ) )
WHERE m.home_score IS NOT NULL AND m.away_score IS NOT NULL AND u.ID IN ( 1 ) ORDER BY 1, 2, 3, 4 made by do_action(‘wp_ajax_footballpool_calculate_scorehistory’), call_user_func_array, Football_Pool_Admin_Score_Calculation::process

Thank you for all your help.
Plugin Author AntoineH
(@antoineh)

12 years, 1 month ago
Yes, with copy&paste I mean something went wrong while copying in the new SQL fragment.

There is a ) too many in your statement. Total statement should be:
```
INSERT INTO pool_wp_rwno_scorehistory
( score_order, type, score_date, source_id, user_id
, score, full, toto, goal_bonus, goal_diff_bonus
, ranking, ranking_id )
SELECT
0, 0 AS score_type, m.play_date AS score_date, m.id AS match_id, u.ID AS user_id
, IF ( p.has_joker = 1, 1, 1 ) AS score
, IF ( m.home_score = p.home_score AND m.away_score = p.away_score, 1, NULL ) AS full
, IF ( m.home_score = p.home_score AND m.away_score = p.away_score, NULL,
IF (
IF ( m.home_score > m.away_score, 1, IF ( m.home_score = m.away_score, 3, 2 ) )
=
IF ( p.home_score > p.away_score, 1, IF ( p.home_score = p.away_score, 3, 2 ) )
, IF ( p.home_score IS NULL OR p.away_score IS NULL, NULL, 1 )
, NULL
)
) AS toto
, IF ( m.home_score = p.home_score,
IF ( m.away_score = p.away_score, 2, 1 ),
IF ( m.away_score = p.away_score, 1, NULL )
) AS goal_bonus
, IF( ( CAST( m.home_score AS SIGNED ) - CAST( p.home_score AS SIGNED ) )
= ( CAST( m.away_score AS SIGNED ) - CAST( p.away_score AS SIGNED ) )
, 1, NULL
) AS goal_diff_bonus
, 0 AS ranking
, 1 AS ranking_id
FROM wp_rwno_users u
LEFT OUTER JOIN pool_wp_rwno_matches m ON ( 1 = 1 )
LEFT OUTER JOIN pool_wp_rwno_predictions p
ON ( p.match_id = m.id AND ( p.user_id = u.ID OR p.user_id IS NULL ) )
WHERE m.home_score IS NOT NULL AND m.away_score IS NOT NULL AND u.ID IN ( 1 ) ORDER BY 1, 2, 3, 4
```
Thread Starter iul
(@iul)

12 years, 1 month ago

I still get an error. And the statement you gave me now is different than the one already existing in the file:

INSERT INTO {$prefix}scorehistory
( score_order, type, score_date, source_id, user_id
, score, full, toto, goal_bonus, goal_diff_bonus
, ranking, ranking_id )
SELECT
0, %d AS score_type, m.play_date AS score_date, m.id AS match_id, u.ID AS user_id
, IF ( p.has_joker = 1, %d, 1 ) AS score
, IF ( m.home_score = p.home_score AND m.away_score = p.away_score, 1, NULL ) AS full
, IF ( m.home_score = p.home_score AND m.away_score = p.away_score, NULL,
IF (
IF ( m.home_score > m.away_score, 1, IF ( m.home_score = m.away_score, 3, 2 ) )
=
IF ( p.home_score > p.away_score, 1, IF ( p.home_score = p.away_score, 3, 2 ) )
, IF ( p.home_score IS NULL OR p.away_score IS NULL, NULL, 1 )
, NULL
)
) AS toto
, IF ( m.home_score = p.home_score,
IF ( m.away_score = p.away_score, 2, 1 ),
IF ( m.away_score = p.away_score, 1, NULL )
) AS goal_bonus
, IF( ( CAST( m.home_score AS SIGNED ) – CAST( p.home_score AS SIGNED ) )
= ( CAST( m.away_score AS SIGNED ) – CAST( p.away_score AS SIGNED ) )
, 1, NULL
)
) AS goal_diff_bonus
, 0 AS ranking
, %d AS ranking_id
FROM {$wpdb->users} u
LEFT OUTER JOIN {$prefix}matches m ON ( 1 = 1 )
LEFT OUTER JOIN {$prefix}predictions p
ON ( p.match_id = m.id AND ( p.user_id = u.ID OR p.user_id IS NULL ) )
WHERE m.home_score IS NOT NULL AND m.away_score IS NOT NULL AND u.ID IN ( {$user_ids} )

Look at the very last line in the code you gave me and the one the file already contains. It’s quite different.
Plugin Author AntoineH
(@antoineh)

12 years, 1 month ago
I just copied the SQL statement from the error message and made my change in it. The SQL in the plugin contains code that is evaluated during run-time. I assumed you’d be able to see the difference in the goal_diff_bonus part.

Anyway, the problem is this part:
```
, 1, NULL
)
) AS goal_diff_bonus
```
There is one ) too many. It should be:
```
, 1, NULL
) AS goal_diff_bonus
```
Thread Starter iul
(@iul)

12 years, 1 month ago

Thanks. That solved it.

Viewing 12 replies - 1 through 12 (of 12 total)

The topic ‘Goal difference bonus – points for close predictions’ is closed to new replies.