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

by Federica Marra -
Number of replies: 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

Average of ratings: -
In reply to Federica Marra

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

by Matteo Scaramuccia -
Picture of Core developers Picture of Peer reviewers Picture of 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

Average of ratings: Useful (1)
In reply to Matteo Scaramuccia

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

by 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!

Average of ratings: -
In reply to Matteo Scaramuccia

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

by 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>

Average of ratings: -