Symfony Profiler

<?php
//----------------------------------------------------------------------
// src/Services/Common/CounterTools.php
//----------------------------------------------------------------------
namespace App\Services\Common;
use Doctrine\Persistence\ManagerRegistry;
use Symfony\Component\Security\Core\Security;
use App\Entity\Access;
use App\Entity\SocietyGroup;
use App\Services\LogTools;
class CounterTools
{
public function __construct(ManagerRegistry $doctrine, Security $security, LogTools $logTools)
{
$this->em = $doctrine->getManager();
$this->security = $security;
$this->logTools = $logTools;
$this->debug = true;
}
// Plan.io Task #4619
public function countExternalMessagesSms(SocietyGroup $societyGroup, Access $access)
{
$societies = $access->getSocietiesAsString();
$sqlMainQuery = "SELECT COUNT(DISTINCT external_message.id) AS counter
FROM external_message
INNER JOIN society ON external_message.society_id = society.id
INNER JOIN society_group ON society.society_group_id = society_group.id
INNER JOIN external_message_type ON external_message.external_message_type_id = external_message_type.id
INNER JOIN external_message_status ON external_message.external_message_status_id = external_message_status.id
WHERE external_message_type.sms = 1
AND external_message_status.unsolved = 1
AND society_group.id = :sg_id";
// Get data
$sql = "";
$sqlQueries = array();
if (!$this->security->isGranted('list_external_messages_any'))
{
return 0;
}
else
{
if ($this->security->isGranted('list_external_messages'))
{
}
else
{
$sqlMainQuery .= " ";
$sqlMainQuery .= " AND society.id IN ".$societies;
}
$sqlQueries[] = $sqlMainQuery;
}
if (count($sqlQueries) > 0)
{
if (count($sqlQueries) == 1)
{
$sql = $sqlQueries[0];
}
else
{
for ($pos = 0; $pos < count($sqlQueries) - 1; $pos++)
{
$sql .= $sqlQueries[$pos];
$sql .= " UNION ";
}
$sql .= $sqlQueries[$pos];
}
}
if ($sql != "")
{
$conn = $this->em->getConnection();
$stmt = $conn->prepare($sqlMainQuery);
$stmt->bindValue('sg_id', $societyGroup->getId());
$queryResult = $stmt->executeQuery();
$result = $queryResult->fetchAssociative();
if (!empty($result['counter']))
{
return intval($result['counter']);
}
}
return 0;
}
public function countMissions(SocietyGroup $societyGroup, Access $access)
{
// Plan.io Task #3961 : Add Shared Missions
// SQL Explanation for Shared Missions
/*
SELECT COUNT(DISTINCT mission.id) AS counter
FROM mission
INNER JOIN society ON mission.society_owner_id = society.id
=> JOIN on mission.societyOwner
=> This is used for permission : show counter only for the current user's societies
INNER JOIN mission_status ON mission.mission_status_id = mission_status.id
=> The mission_status is the one of the mission
=> These are the statuses in the SocietyGroup that shared the mission (SocietyGroupAuthor)
WHERE mission.society_group_owner_id = :sg_id
=> The missions are selected in the current group, but the JOIN is done on the SocietyGroupOwner id
AND mission.society_group_owner_id <> mission.society_group_author_id
=> Only shared missions
AND mission_status.code IN (
SELECT code FROM mission_status WHERE mission_status.society_group_id = mission.society_group_owner_id AND mission_status.unsolved = 1
)
=> And now the fun part :)
=> We want the statuses from the SocietyGroupOwner, but which are unsolved in the SocietyGroupAuthor
=> Thus we select the code of the status from the SocietyGroupAuthor
=> and look for it in the unsolved codes of the statuses of the SocietyGroupOwner
*/
$nbOwnMissions = 0;
$nbSharedMissions = 0;
$societies = $access->getSocietiesAsString();
$missionSqls = array(
// Own Missions
"SELECT COUNT(DISTINCT mission.id) AS counter
FROM mission
INNER JOIN society ON mission.society_id = society.id
INNER JOIN society_group ON society.society_group_id = society_group.id
INNER JOIN mission_status ON mission.mission_status_id = mission_status.id
WHERE mission_status.unsolved = 1
AND society_group.id = :sg_id",
// Shared Missions
"SELECT COUNT(DISTINCT mission.id) AS counter
FROM mission
INNER JOIN society ON mission.society_owner_id = society.id
INNER JOIN mission_status ON mission.mission_status_id = mission_status.id
WHERE mission.society_group_owner_id = :sg_id
AND mission.society_group_owner_id <> mission.society_group_author_id
AND mission_status.code IN (
SELECT code FROM mission_status WHERE mission_status.society_group_id = mission.society_group_owner_id AND mission_status.unsolved = 1
)",
);
foreach ($missionSqls as $key => $missionSql)
{
// Get data
$sql = "";
$sqlQueries = array();
if (!$this->security->isGranted('list_missions_any'))
{
return 0;
}
else
{
if ($this->security->isGranted('list_missions'))
{
}
else
{
$missionSql .= " ";
$missionSql .= " AND society.id IN ".$societies;
}
$sqlQueries[] = $missionSql;
}
if (count($sqlQueries) > 0)
{
if (count($sqlQueries) == 1)
{
$sql = $sqlQueries[0];
}
else
{
for ($pos = 0; $pos < count($sqlQueries) - 1; $pos++)
{
$sql .= $sqlQueries[$pos];
$sql .= " UNION ";
}
$sql .= $sqlQueries[$pos];
}
}
if ($sql != "")
{
$conn = $this->em->getConnection();
$stmt = $conn->prepare($missionSql);
$stmt->bindValue('sg_id', $societyGroup->getId());
$queryResult = $stmt->executeQuery();
$result = $queryResult->fetchAssociative();
if (!empty($result['counter']))
{
if ($key == 0)
{
$nbOwnMissions = intval($result['counter']);
}
else
{
$nbSharedMissions = intval($result['counter']);
}
}
}
}
// $this->logTools->ploopLog("nbOwnMissions = $nbOwnMissions");
// $this->logTools->ploopLog("nbSharedMissions = $nbSharedMissions");
return $nbOwnMissions + $nbSharedMissions;
}
public function countDemands(SocietyGroup $societyGroup, Access $access)
{
$societies = $access->getSocietiesAsString();
$demandSql = "SELECT COUNT(DISTINCT demand.id) AS counter
FROM demand
INNER JOIN society ON demand.society_id = society.id
INNER JOIN society_group ON society.society_group_id = society_group.id
INNER JOIN demand_status ON demand.demand_status_id = demand_status.id
WHERE demand_status.unsolved = 1
AND society_group.id = :sg_id";
// Get data
$sql = "";
$sqlQueries = array();
if (!$this->security->isGranted('list_demands_any'))
{
return 0;
}
else
{
if ($this->security->isGranted('list_demands'))
{
}
else
{
$demandSql .= " ";
$demandSql .= " AND society.id IN ".$societies;
}
$sqlQueries[] = $demandSql;
}
if (count($sqlQueries) > 0)
{
if (count($sqlQueries) == 1)
{
$sql = $sqlQueries[0];
}
else
{
for ($pos = 0; $pos < count($sqlQueries) - 1; $pos++)
{
$sql .= $sqlQueries[$pos];
$sql .= " UNION ";
}
$sql .= $sqlQueries[$pos];
}
}
if ($sql != "")
{
$conn = $this->em->getConnection();
$stmt = $conn->prepare($demandSql);
$stmt->bindValue('sg_id', $societyGroup->getId());
$queryResult = $stmt->executeQuery();
$result = $queryResult->fetchAssociative();
if (!empty($result['counter']))
{
return intval($result['counter']);
}
}
return 0;
}
public function countIkeaServiceOrders(SocietyGroup $societyGroup, Access $access)
{
$societies = $access->getSocietiesAsString();
$demandSql = "SELECT COUNT(DISTINCT ikea_service_order.id) AS counter
FROM ikea_service_order
INNER JOIN society ON ikea_service_order.society_id = society.id
INNER JOIN society_group ON society.society_group_id = society_group.id
INNER JOIN ikea_service_order_status ON ikea_service_order.service_order_status_id = ikea_service_order_status.id
WHERE ikea_service_order_status.unsolved = 1
AND society_group.id = :sg_id";
// Get data
$sql = "";
$sqlQueries = array();
if (!$this->security->isGranted('list_ikea_service_orders_any'))
{
return 0;
}
else
{
if ($this->security->isGranted('list_ikea_service_orders'))
{
}
else
{
$demandSql .= " ";
$demandSql .= " AND society.id IN ".$societies;
}
$sqlQueries[] = $demandSql;
}
if (count($sqlQueries) > 0)
{
if (count($sqlQueries) == 1)
{
$sql = $sqlQueries[0];
}
else
{
for ($pos = 0 ; $pos < count($sqlQueries) - 1 ; $pos++)
{
$sql .= $sqlQueries[$pos];
$sql .= " UNION ";
}
$sql .= $sqlQueries[$pos];
}
}
if ($sql != "")
{
$conn = $this->em->getConnection();
$stmt = $conn->prepare($demandSql);
$stmt->bindValue('sg_id', $societyGroup->getId());
$queryResult = $stmt->executeQuery();
$result = $queryResult->fetchAssociative();
if (!empty($result['counter']))
{
return intval($result['counter']);
}
}
return 0;
}
public function countNotifications(Access $access)
{
$accessId = $access->getId();
$notificationSql =
"SELECT
COUNT(DISTINCT notification.id) AS counter
FROM
ding_notification as notification
INNER JOIN access ON notification.access_id = access.id
WHERE
access_id = :id_access
and notification.seen = 0";
$conn = $this->em->getConnection();
$stmt = $conn->prepare($notificationSql);
$stmt->bindValue('id_access', $accessId);
$queryResult = $stmt->executeQuery();
$result = $queryResult->fetchAssociative();
$counter = 0;
if (!empty($result['counter']))
{
$counter = intval($result['counter']);
}
return $counter;
}
// Plan.io Task #3865
public function countAdvancedNotifications(Access $access)
{
$societyGroup = $access->getSocietyGroup();
$societies = $access->getSocietiesAsString();
$dingSql = "SELECT COUNT(ding.id) as counter
FROM ding_advanced_notification ding
INNER JOIN society ON society.id = ding.society_id
INNER JOIN society_group ON society_group.id = society.society_group_id
WHERE society_group.id = :sg_id";
// Get data
if (!$this->security->isGranted('list_advanced_notifications_any'))
{
return 0;
}
else
{
if ($this->security->isGranted('list_advanced_notifications'))
{
}
else
{
$dingSql .= " ";
$dingSql .= " AND society.id IN ".$societies;
}
}
$conn = $this->em->getConnection();
$stmt = $conn->prepare($dingSql);
$stmt->bindValue('sg_id', $societyGroup->getId());
$queryResult = $stmt->executeQuery();
$result = $queryResult->fetchAssociative();
if (!empty($result['counter']))
{
return intval($result['counter']);
}
return 0;
}
public function countApplications(SocietyGroup $societyGroup, Access $access)
{
$societies = $access->getSocietiesAsString();
$applicationSql = "SELECT COUNT(application.id) as counter
FROM application
INNER JOIN application_status as status ON status.id = application.status_id
INNER JOIN society ON society.id = application.society_id
INNER JOIN society_group ON society_group.id = society.society_group_id
LEFT JOIN human_resource ON application.human_resource_id = human_resource.id
WHERE status.archived = 0
AND human_resource.id IS NULL
AND society_group.id = :sg_id";
// Get data
$sql = "";
$sqlQueries = array();
if (!$this->security->isGranted('list_applications_any'))
{
return 0;
}
else
{
if ($this->security->isGranted('list_applications'))
{
}
else
{
$applicationSql .= " ";
$applicationSql .= " AND society.id IN ".$societies;
}
$sqlQueries[] = $applicationSql;
}
if (count($sqlQueries) > 0)
{
if (count($sqlQueries) == 1)
{
$sql = $sqlQueries[0];
}
else
{
for ($pos = 0; $pos < count($sqlQueries) - 1; $pos++)
{
$sql .= $sqlQueries[$pos];
$sql .= " UNION ";
}
$sql .= $sqlQueries[$pos];
}
}
if ($sql != "")
{
$conn = $this->em->getConnection();
$stmt = $conn->prepare($applicationSql);
$stmt->bindValue('sg_id', $societyGroup->getId());
$queryResult = $stmt->executeQuery();
$result = $queryResult->fetchAssociative();
if (!empty($result['counter']))
{
return intval($result['counter']);
}
}
return 0;
}
// Task plan.io #3847
public function countCosts(SocietyGroup $societyGroup, Access $access)
{
$societies = $access->getSocietiesAsString();
$costSql = "SELECT COUNT(DISTINCT cost.id) AS nb
FROM cost
INNER JOIN society ON cost.society_id = society.id
INNER JOIN society_group ON society.society_group_id = society_group.id
INNER JOIN cost_status ON cost.status_id = cost_status.id
INNER JOIN access ON cost.access_id = access.id
INNER JOIN access author ON cost.author_access_id = author.id
WHERE cost_status.unsolved = 1
AND cost.parent_id is NULL
AND society_group.id = :sg_id";
if ($this->security->isGranted('list_costs_any'))
{
if ($this->security->isGranted('list_costs'))
{
}
else
{
if ($this->security->isGranted('list_costs_society'))
{
$costSql .= " ";
$costSql .= " AND society.id IN ".$societies;
}
else
{
if ($this->security->isGranted('list_costs_own'))
{
$costSql .= " ";
$costSql .= " AND (access.id = " . $access->getId() . " || author.id = " . $access->getId() . ")";
}
}
}
}
else
{
return 0;
}
if ($costSql != "")
{
$conn = $this->em->getConnection();
$stmt = $conn->prepare($costSql);
$stmt->bindValue('sg_id', $societyGroup->getId());
$queryResult = $stmt->executeQuery();
$result = $queryResult->fetchAssociative();
if (!empty($result['nb']))
{
return intval($result['nb']);
}
}
return 0;
}
}
/*
select mission.id, mission.ref, mission_status.value, mission_status.code
from mission
join mission_status on (mission_status.id = mission.mission_status_id)
where mission.society_group_owner_id=263 and mission.society_group_author_id <> mission.society_group_owner_id
and mission_status.code in (select code from mission_status where mission_status.society_group_id = 263 and mission_status.unsolved = 1)
*/

src/Services/Common/CounterTools.php line 19