james
mydetail_papara
PaParazi21!

http://ergast.com/api/f1/2018

http://codular.com/curl-with-php


select *, (Point_1st + Point_2nd + Point_3rd) as total
FROM
(
  select us.Round, us.UserName, b.1stPlace, b.2ndPlace, b.3rdPlace
  , (case when b.1stPlace = us.1stPlace then 25 else 0 END) as Point_1st
  , (case when b.2ndPlace = us.2ndPlace then 18 else 0 END) as Point_2nd
  , (case when b.3rdPlace = us.3rdPlace then 15 else 0 END) as Point_3rd
  from userselection as us
  left JOIN
  (
    select *
    FROM
    (
        SELECT r1.`Season`, r1.`Round`,
        r1.`DriverName` as 1stPlace, r2.`DriverName` as 2ndPlace, r3.`DriverName` as 3rdPlace
        FROM `results` as r1
        left join `results` as r2
        on r2.`Round` = r1.`Round`
        and r2.`Season` = r1.`Season`
        and r2.`Position` = 2
        left join `results` as r3
        on r3.`Round` = r1.`Round`
        and r3.`Season` = r1.`Season`
        and r3.`Position` = 3
        WHERE r1.`Season` = 2017
        and r1.`Round` = 1
        and r1.`Position` = 1
    ) as a
  ) as b 
  on b.`Season` = us.Season
  and b.`Round` = us.Round
  group by us.UserName
) as c






DECLARE @SQL varchar(4000);

​SET @SQL = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when Round = ',
      dn,
      ' then r.Points else 0 end) AS `',
      dn, '`'
    )
  ) INTO @SQL
FROM
(
  SELECT DISTINCT Round dn
  FROM results r
  ORDER BY r.Round
) d;
 
SET @SQL 
  = CONCAT('SELECT r.DriverName, ', @SQL, ' 
            from results r
            where `Season` = 2017
            group by r.`DriverName`;');
 
 
SELECT @SQL;

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;



SET @SQL = NULL;
SELECT
GROUP_CONCAT(DISTINCT
	CONCAT(
	'sum(case when ExptraPointsName = ''',
	dn,
	''' then 1 else 0 end) AS `r',
	dn, '`'
	)
) INTO @SQL
FROM
(
SELECT DISTINCT Name dn
FROM extrapoints r
where `Season` = 2018
and `Enabled`  = 1
ORDER BY r.Name
) d;

SET @SQL 
= CONCAT('SELECT r.UserName, ue.UserName, ', @SQL, '
			from regform r
			left join userextrapoints ue
			 on r.username = ue.UserName
			 and ue.`Season` = 2018
			group by r.`UserName`;');
            
            
PREPARE stmt FROM @SQL;
select @SQL;
DEALLOCATE PREPARE stmt;

===========================
SET @SQL = NULL;
SELECT
GROUP_CONCAT(DISTINCT
	CONCAT(
	'sum(case when round = ',
	dn,
	' then d.PointsTotal else 0 end) AS `r',
	dn, '`'
	)
) INTO @SQL
FROM
(
SELECT DISTINCT Round dn
FROM results r
where `Season` = 2018
ORDER BY r.Round
) d;

SET @SQL 			
= CONCAT('select NAME, username, ', @SQL, '
from
(	
	select  NAME, username, round, Points1, Points2, Points3, (Points1+ Points2+ Points3) as  PointsTotal
	from 
	(
		select NAME, username, a.round, UserPlace1, UserPlace2, UserPlace3, Place1, Place2, Place3,
		case 
		when UserPlace1 = Place1 then 25
		else 0
		end as Points1,
		case when UserPlace2 = Place2 then 18
		else 0
		end as Points2,
		case when UserPlace3 = Place3 then 15
		else 0
		end as Points3
		from 
		(
			select r.NAME, r.username, round, 1stPlace as UserPlace1, 2ndPlace as UserPlace2, 3rdPlace as UserPlace3
			from regform r
			left join userselection us
				on us.UserName = r.username
			where us.Season = 2018
			group by r.NAME, r.username, round
		) as a
		left join 
		(
			SELECT r1.`Season`, r1.`Round`,
		  r1.`DriverName` as Place1, 
		  r2.`DriverName` as Place2, 
		  r3.`DriverName` as Place3
		  FROM `results` as r1
		  left join `results` as r2
		  on r2.`Round` = r1.`Round`
		  and r2.`Season` = r1.`Season`
		  and r2.`Position` = 2
		  left join `results` as r3
		  on r3.`Round` = r1.`Round`
		  and r3.`Season` = r1.`Season`
		  and r3.`Position` = 3
		  WHERE r1.`Season` = 2018
		  and r1.`Position` = 1
		) as B 
		on b.Round = a.round
		order by a.round
	) as c
	order by c.round		
) as d
group by d.NAME, d.username
ORDER by sum(d.PointsTotal) DESC;');

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;";