Help with one report: how to add user pictures and change date format

Help with one report: how to add user pictures and change date format

par Federica Marra,
Nombre de réponses : 3

Hello Juan and everyone,

I am trying to add the user pictures in a sql report I created to get an overiview of all the users who earned one specif badge.

SELECT
    -- User.
    u.firstname, u.lastname, u.city, u.email, u.picture, u.id, d.dateissued, d.dateexpire
FROM
    prefix_badge_issued AS d
JOIN
    prefix_badge AS b ON d.badgeid = b.id
JOIN
    prefix_user AS u ON d.userid = u.id

WHERE b.id = 3


I enabled the template and followed your code found in plugin's doc.

<table width="100%">
<tbody><tr>
<td><img src="http://yourmoodle.com/user/pix.php/id]]/f1.jpg"></td>
<td><h2>[[firstname lastname</h2>
City: city
</td>
<td>Issued: dateissued</td>
</tr>
</tbody></table>
<br>


The report works but I cannot get the users' pictures and badge's issuing and expiration date.



Can you help me?

Thanks in advance

Moyenne des évaluations  -
En réponse à Federica Marra

Re: Help with one report: how to add user pictures and change date format

par Matteo Scaramuccia,
Avatar Core developers Avatar Peer reviewers Avatar Plugin developers

Hi Federica,
first of all you should take care of ..., your template should look like:

<table width="100%">
  <tbody>
    <tr>
      <td><img src="http://yourmoodle.com/user/pix.php/UserId/f1.jpg" alt="Email" /></td>
      <td><h2>FirstName LastName</h2>City: City</td>
      <td>Issued: DateIssued</td>
      <td>Expire: DateExpire</td>
    </tr>
   </tbody>
</table>
<br />

and your SQL query should look like:

SELECT
    -- User.
    u.firstname 'FirstName', u.lastname 'LastName'
    , u.city 'City', u.email 'Email', u.picture, u.id 'UserId'
    -- Badge.
    , FROM_UNIXTIME(bi.dateissued) 'DateIssued', FROM_UNIXTIME(bi.dateexpire) 'DateExpire'
FROM
    prefix_badge_issued AS bi
JOIN
    prefix_badge AS b ON bi.badgeid = b.id
JOIN
    prefix_user AS u ON bi.userid = u.id

Blindly written, I didn't have the chance to test it.

Note: the dates are converted according to the timezone of the SQL connection (MySQL).

@Helen: I'm not able to escape the square brackets in order to not let the Moodle Wiki filter to be activated. Text above in doubled square brackets are auto-linked to the Moodle Wiki.

HTH,
Matteo

Moyenne des évaluations Useful (1)
En réponse à Matteo Scaramuccia

Re: Help with one report: how to add user pictures and change date format

par Federica Marra,

Hi Matteo,

Thanks very much for your help!

I managed to change the date format in the sql and get the format I wanted with:

   -- User.
    u.firstname, u.lastname, u.city, u.email, u.picture, u.id, d.dateexpire,
    DATE_FORMAT(FROM_UNIXTIME(d.dateissued),'%y-%m-%d') AS date

But the template and the user pics are still not working properly.

I tried with your code and changing it a bit as well:

 


The problem with the grey icon is that it appear in the report as well and it is not linked with the real user pics.

I found other discussions in moodle about the user pics. Could you please take a look?

https://moodle.org/mod/forum/discuss.php?d=201445

https://moodle.org/mod/forum/discuss.php?d=319037


Thanks again!

Moyenne des évaluations  -
En réponse à Matteo Scaramuccia

Re: Help with one report: how to add user pictures and change date format

par Federica Marra,

Hi again Matteo!

Actually the  is working! But there are some problems with the pics visualization of the url if I do not add http:"www.mymoodle.bla

to the <img src>

Moyenne des évaluations  -