Directory:~$ /proc/self/root/home/lmsyaran/public_html/j3/modules/mod_consultants/

Upload File

[Home] [System Details] [Kill Me]

<?php

class ModConsultantsHelper
{

    public static function getConsultants($params)
    {


        // select consultans id that do atleast 1 consultation
        $db = JFactory::getDbo();
        $query = $db->getQuery(true);

        $query
            ->select('distinct co.id as consultantid')
            ->from($db->quoteName('#__reservation_session',
's'))
            ->join('INNER',
$db->quoteName('#__reservation_plan', 'p') . '
ON ' . $db->quoteName('s.planid') . ' = ' .
$db->quoteName('p.id'))
            ->join('INNER',
$db->quoteName('#__reservation_consultant', 'co') .
' ON ' . $db->quoteName('p.consultantid') . ' =
' . $db->quoteName('co.id'))
            ->where($db->quoteName('s.pay').'=
1');

        $db->setQuery($query);


        $results = $db->loadColumn();



        // select consultants info that their ids are in $results

        if($results)
        {
            $db = JFactory::getDbo();
            $query = $db->getQuery(true);

            $query
               
->select($db->quoteName(array('a.id','a.alt','a.image',
'b.name','c.title'),array('consultantid','alt','image','consultantname','specialty')))
               
->from($db->quoteName('#__reservation_consultant',
'a'))
                ->join('INNER',
$db->quoteName('#__users', 'b') . ' ON ' .
$db->quoteName('a.userid') . ' = ' .
$db->quoteName('b.id'))
                ->join('INNER',
$db->quoteName('#__categories', 'c') . ' ON
' . $db->quoteName('a.catid') . ' = ' .
$db->quoteName('c.id'))
                ->where($db->quoteName('a.published') .
' = 1 ')
                ->where($db->quoteName('a.id') . ' IN
('.implode(',',$results).') ')
                ->order('RAND() LIMIT 8');

            $db->setQuery($query);


            $consultantInfo = $db->loadObjectList();
            $consultantid=[];

            foreach ($consultantInfo as $item) {
                $consultantid[]= $item->consultantid;
            }


            // select number of sessions performed by consultants
            $db = JFactory::getDbo();
            $query = $db->getQuery(true);

            $query
                ->select('co.id as consultantid ,
COUNT(consultantid) as count')
               
->from($db->quoteName('#__reservation_session',
's'))
                ->join('INNER',
$db->quoteName('#__reservation_plan', 'p') . '
ON ' . $db->quoteName('s.planid') . ' = ' .
$db->quoteName('p.id'))
                ->join('INNER',
$db->quoteName('#__reservation_consultant', 'co') .
' ON ' . $db->quoteName('p.consultantid') . ' =
' . $db->quoteName('co.id'))
                ->where($db->quoteName('s.pay').'=
1')
                ->where($db->quoteName('co.id').' IN
('.implode(',',$consultantid).')')
                ->group('consultantid');



            $db->setQuery($query);


            $sessionCount =
$db->loadAssocList('consultantid');

            $commentInfo= self::commentInfo($consultantid);


            return [$consultantInfo,$sessionCount, $commentInfo];
        }

        return [0,0];

    }

    public static function commentInfo($consultantid)
    {
        $db= JFactory::getDbo();
        $query= $db->getQuery(true)
            ->select('consultantid, count(consultantid) as count,
sum(rate) as rate')
           
->from($db->quoteName('#__reservation_comment','cm'))
            ->where($db->quoteName('cm.consultantid').
'IN ('.implode(',',$consultantid).')')
            ->where($db->quoteName('cm.published'). '=
1')
            ->group('consultantid');

        $db->setQuery($query);
        $result= $db->loadObjectList('consultantid');
        return $result;
    }
}