Retrieve courses associated with a deleted Convener

[RobiFag]

Hi all, we have just removed a convener from the list of our organization conveners and all the courses associated to this convener have been deleted, is ti possible to retrieve these courses?

[RobiFag]

Moreover all the certificates issued for the courses that have been deleted seems to be no more available. The unique link that attendees can use to prove that they have completed the course is no more available, the page returns this error "Sorry, no certificate matching this number has been issued." and this is a problem since the certificate has been issued and a credit has been used for it.

[timlinux]

I will check if I can retrieve it from a backup.

[timlinux]

Please provide the course convenor name and your org name. I will only be able to check my backups tomorrow so please stand by.

[timlinux]

@sumandari can you find and fix the underlying bug here please. We should remove support for deleting convenors if there are relations attached to them, and rather let you archive them so they become inactive from your org.

[timlinux]

@RobiFag ok I have retrieved the backup from last night. Can you please confirm that the affected convenor is username: 1-rmarzocchi84-gter-srl ?

I have also checked the certificate pdfs, they are still on the system and have not been deleted, so will be available again once I restore the convenor and the associated certificates.

For future reference, if you file an issue, please provide adequate detail so that we can deal with it efficiently. Thanks!

[rmarzocchi84]

@timlinux I confirm I am the old convener.

[RobiFag]

@timlinux thank you so much, I confirm the convenor is @rmarzocchi84 and the organisation is Gter.

[RobiFag]

@RobiFag ok I have retrieved the backup from last night. Can you please confirm that the affected convenor is username: 1-rmarzocchi84-gter-srl ?

I have also checked the certificate pdfs, they are still on the system and have not been deleted, so will be available again once I restore the convenor and the associated certificates.

For future reference, if you file an issue, please provide adequate detail so that we can deal with it efficiently. Thanks!

Yes sure, sorry for the lack of details but i wasn't sure if it was an issue or a normal behavior of the platform. Thanks again!

[RobiFag]

Hi @timlinux sorry for being insistent, but do you have any news about the restoration of the deleted convenor and its related certificates?
The unique link are still not available.
Thanks a lot!

[timlinux]

insert into certification_courseconvener values (81,'1-rmarzocchi84-gter-srl',22);

insert into certification_course values (266,'QGIS_01 - Utilizzo base di QGIS_2020-10-16-2020-12-18',2020-10-16, 2020-12-18);
insert into certification_course values (332,'QGIS_01 - Utilizzo base di QGIS_2021-02-17-2021-04-21',2021-02-17, 2021-04-21);


insert into certification_attendee values (1924 , 'QGIS-1817',1981,6421,266,true::bool);
insert into certification_attendee values (1925 , 'QGIS-1818',1980,6421,266,true::bool);                       
insert into certification_attendee values (1926 , 'QGIS-1819',1979,6421,266,true::bool);                       
insert into certification_attendee values (1927 , 'QGIS-1820',1978,6421,266,true::bool);                       
insert into certification_attendee values (1928 , 'QGIS-1821',1977,6421,266,true::bool);                       
insert into certification_attendee values (1929 , 'QGIS-1822',1976,6421,266,true::bool);                       
insert into certification_attendee values (1930 , 'QGIS-1823',1975,6421,266,true::bool);                       
insert into certification_attendee values (1931 , 'QGIS-1824',1974,6421,266,true::bool);                       
insert into certification_attendee values (1932 , 'QGIS-1825',1973,6421,266,true::bool);                       
insert into certification_attendee values (1933 , 'QGIS-1826',1972,6421,266,true::bool);
insert into certification_attendee values (2195 , 'QGIS-2088',1322,6421,332,true::bool);                       
insert into certification_attendee values (2196 , 'QGIS-2089',1252,6421,332,true::bool);                       
insert into certification_attendee values (2197 , 'QGIS-2090',1251,6421,332,true::bool);                       
insert into certification_attendee values (2198 , 'QGIS-2091',1250,6421,332,true::bool);                       
insert into certification_attendee values (2199 , 'QGIS-2092',1249,6421,332,true::bool);                       
insert into certification_attendee values (2200 , 'QGIS-2093',1248,6421,332,true::bool);                       
insert into certification_attendee values (2201 , 'QGIS-2094',1247,6421,332,true::bool);                       
insert into certification_attendee values (2202 , 'QGIS-2095',1246,6421,332,true::bool);                       
insert into certification_attendee values (2203 , 'QGIS-2096',1245,6421,332,true::bool);                       
insert into certification_attendee values (2204 , 'QGIS-2097',1244,6421,332,true::bool);                       
insert into certification_attendee values (2205 , 'QGIS-2098',1243,6421,332,true::bool);                       
insert into certification_attendee values (2206 , 'QGIS-2099',1242,6421,332,true::bool);                       
insert into certification_attendee values (2207 , 'QGIS-2100',1241,6421,332,true::bool);                       
insert into certification_attendee values (2208 , 'QGIS-2101',1239,6421,332,true::bool);                       
insert into certification_attendee values (2209 , 'QGIS-2102',1238,6421,332,true::bool);  
insert into certification_attendee values (2198 , 'QGIS-2091',1250,6421,332,true::bool);                       
insert into certification_attendee values (2199 , 'QGIS-2092',1249,6421,332,true::bool);                       
insert into certification_attendee values (2200 , 'QGIS-2093',1248,6421,332,true::bool);                       
insert into certification_attendee values (2201 , 'QGIS-2094',1247,6421,332,true::bool);                       
insert into certification_attendee values (2202 , 'QGIS-2095',1246,6421,332,true::bool);                       
insert into certification_attendee values (2203 , 'QGIS-2096',1245,6421,332,true::bool);                       
insert into certification_attendee values (2204 , 'QGIS-2097',1244,6421,332,true::bool);                       
insert into certification_attendee values (2205 , 'QGIS-2098',1243,6421,332,true::bool);                       
insert into certification_attendee values (2206 , 'QGIS-2099',1242,6421,332,true::bool);                       
insert into certification_attendee values (2207 , 'QGIS-2100',1241,6421,332,true::bool);                       
insert into certification_attendee values (2208 , 'QGIS-2101',1239,6421,332,true::bool);                       
insert into certification_attendee values (2209 , 'QGIS-2102',1238,6421,332,true::bool);                       
insert into certification_attendee values (2210 , 'QGIS-2103',1237,6421,332,true::bool);                       
insert into certification_attendee values (2211 , 'QGIS-2104',1236,6421,332,true::bool);                       
insert into certification_attendee values (2212 , 'QGIS-2105',1234,6421,332,true::bool);                       
insert into certification_attendee values (2213 , 'QGIS-2106',1233,6421,332,true::bool);                       
insert into certification_attendee values (2214 , 'QGIS-2107',1231,6421,332,true::bool);                       
insert into certification_attendee values (2215 , 'QGIS-2108',1230,6421,332,true::bool);                       
insert into certification_attendee values (2216 , 'QGIS-2109',1228,6421,332,true::bool);                       
insert into certification_attendee values (2217 , 'QGIS-2110',1225,6421,332,true::bool);                       
insert into certification_attendee values (2218 , 'QGIS-2111',1224,6421,332,true::bool);                       
insert into certification_attendee values (2219 , 'QGIS-2112',1223,6421,332,true::bool);                       
insert into certification_attendee values (2220 , 'QGIS-2113',1222,6421,332,true::bool);                       
insert into certification_attendee values (2221 , 'QGIS-2114',1221,6421,332,true::bool);
insert into certification_attendee values (2295 , 'QGIS-2188',1253,6421,332,true::bool);                       
insert into certification_attendee values (2296 , 'QGIS-2189',1240,6421,332,true::bool);                       
insert into certification_attendee values (2297 , 'QGIS-2190',1232,6421,332,true::bool); 

[timlinux]

@sumandari so we can take the two dumps I put in the home dir on the server:

kartoza@changelog:~$ ls *.dmp
PG_CHANGELOG_gis.21-November-2021-predelete.dmp  PG_CHANGELOG_gis.22-November-2021-postdelete.dmp

If you pg_restore to restore first the predelete dump and the do pg_dump --column-inserts --data-only gis | dump-pre.sql and then restore the post-delete and do pg_dump --column-inserts --data-only gis | dump-post.sql

Then use your favourite diff tool to compare the two.

I think all the changes are only in certification_courseconvener, certification_course and certification_attendee tables but you may need to look around.

At the end of the process you should have a list on insert statements similar to the listing in my previous comment that we can then just run in PSQL on the production db to restore the missing records. I have confirmed that the certificates themselves are still available on the server so no need to worry about those. Please test locally on your own machine before running on production and make an extra db backup before running your script.

[sumandari]

Hi @RobiFag
My apologies for not getting back to you sooner.
The records have been restored, please kindly check it:
https://changelog.qgis.org/en/qgis/certifyingorganisation/qgis-gter-srl/course/qgis_01-utilizzo-base-di-qgis_2020-10-16-2020-12-18/
https://changelog.qgis.org/en/qgis/certifyingorganisation/qgis-gter-srl/course/qgis_01-utilizzo-base-di-qgis_2021-02-17-2021-04-21/

The fix for the code hasn't been deployed yet, I will post here once it's done so that you can deactivate the course convener.
Please let me know if there's anything else I can do.

Regards,
Sumandari

[RobiFag]

Thank you so much @sumandari and @timlinux! Certificates have been successfully restored and the unique link are now available!!

[sumandari]

@RobiFag New feature for disabling Course Convener has been released in v2.3.0 and deployed on prod. Would you please give it a try? Thank you!

[jvdkwast]

It seems that only admins can delete course conveners. It would be best if the responsible person from the training center can remove course conveners and their courses are archived.

[jvdkwast]

Maybe applicants of an organisation should have a special role. That role should allow to edit organisation info (also during the application process) and remove course conveners. Course conveners should have a normal user role.

[jvdkwast]

Applicants should be informed after approval that they are responsible for updating the organisation (add/remove course conveners, edit info, archive org) when things change.