<?php

namespace App\Services;

use App\Core\Database;

class HomepageInsightService
{

public static function organization(
int $orgId
): array
{

$db=
Database::getInstance();

$out=[];

$sql=[

'conferences'=>

"

SELECT
COUNT(*)

FROM conferences

WHERE org_id=?

",

'homepages'=>

"

SELECT
COUNT(
DISTINCT conference_id
)

FROM conference_home_sections

WHERE conference_id IN(

SELECT id
FROM conferences
WHERE org_id=?

)

",

'releases'=>

"

SELECT
COUNT(*)

FROM conference_home_releases

WHERE conference_id IN(

SELECT id
FROM conferences
WHERE org_id=?

)

"

];

foreach(
$sql
as $k=>$q
){

$stmt=
$db
->prepare(
$q
);

$stmt
->execute([

$orgId

]);

$out[
$k
]=
(int)
$stmt
->fetchColumn();

}

$out[
'coverage'
]=

$out[
'conferences'
]

?

round(

100
*
$out[
'homepages'
]
/
$out[
'conferences'
]

)

:

0;

return
$out;

}

}
