Opportunities by user

[viliando]

Hi everyone first i want to say is "Sorry about my English" i'm trying to make something diferent with dashlets but can't finish it.
I use Opportunities by stage and try to convert to Opportunities by user. Need this for my manager of team to can see opportunities for every user without any filters and searches.

Everything works fine but how to change in legend to show user_names not user_ids.

Changes is in:
/application/Espo/Modules/Crm/Services/Opportunity.php

add new public function "reportByStageTeam"

public function reportByStageTeam($dateFrom, $dateTo)
    {
        $pdo = $this->getEntityManager()->getPDO();

        $options = $this->getMetadata()->get('entityDefs.Opportunity.fields.created_by_id.options');

        $sql = "
            SELECT opportunity.created_by_id AS `stage`, SUM(opportunity.amount * currency.rate) as `amount`
            FROM opportunity
            JOIN currency ON currency.id = opportunity.amount_currency
            WHERE
                opportunity.deleted = 0 AND
                opportunity.userteam = 'team-web' AND
                opportunity.close_date >= ".$pdo->quote($dateFrom)." AND
                opportunity.close_date < ".$pdo->quote($dateTo)." AND
                opportunity.stage <> 'Closed Lost' 
            GROUP BY opportunity.created_by_id
            ORDER BY FIELD(opportunity.created_by_id, '".implode("','", $options)."')
        ";

        $sth = $pdo->prepare($sql);
        $sth->execute();

        $rows = $sth->fetchAll(\PDO::FETCH_ASSOC);

        $result = array();
        foreach ($rows as $row) {
            $result[$row['stage']] = floatval($row['amount']);
        }

        return $result;
    }

edit public function in

/application/Espo/Modules/Crm/Controllers/Opportunity.php

add if $level=='team' to load reportByStageteam

public function actionReportByStage($params, $data, $request)
    {
        $level = $this->getAcl()->getLevel('Opportunity', 'read');
        if (!$level || $level == 'own' || $level == 'no') {
            throw new Forbidden();
        }
        if (!$level || $level == 'team') {


        $dateFrom = $request->get('dateFrom');
        $dateTo = $request->get('dateTo');

        return $this->getService('Opportunity')->reportByStageteam($dateFrom, $dateTo);
        }

        $dateFrom = $request->get('dateFrom');
        $dateTo = $request->get('dateTo');

        return $this->getService('Opportunity')->reportByStage($dateFrom, $dateTo);
    }

And this work but two days i try to show USER_NAMES not USER_IDS and can't do that

[tanya]

Hello
Do you know sql?
in select query opportunity.created_by_id AS `stage` you ask to return id, but want, that system returns you the name... not logically you can join user table to be available users name or execute one more query to load pairs user id and user first and last names and set this value as a key in your resulting array

[viliando]

Thanks
May be need a lot of help to do that. Can you help me please. I realy don't know how to join and use user table.