How to mass update reminders?

[robinx999]

Hello,
is there a way to mass update a reminder? Maybe set the reminder for a whole calender or the reminders based on title of events? Or maybe someone could give me another hint how to solve my Problem.

The problem is the following, in my city I can download a csv or ics file with the garbage days, for this year it is 54 events (normal, recycle, paper and some special events like christmas trees), the advantage of this calender is that it accounts for all shifts in garbage days because of holidays. And my problem is the following all events are 6 o’clock in the morning of the day they get the garbage, but more or less obvious I want to put out the garbage before that. So is there a simple way to set a reminder 11 hours in advance for all events of a calender (made a garbage calender) or is there another way to set up the reminders which does not involve clicking on every entry with the possibility in missing one.

thx and greetings

[Schmu]

Hi,

You could edit the csv before importing into the calendar. With notepad++ for example do a search and replace (with regex if necessary). I guess you imported the events already and for that case I don’t know a good way in the calendar app. I believe there is no such way except editing one by one. Rather go to the database. I can help you with the query if you need help.

[robinx999]

Hi and thx for the effort,

well yes I imported the events, but it is a separate calender so it could easily be dumped and I could start over.
But I looked inside the csv file there is no reminder at all? can it be easily added? The file is this:

Subject,Start Date,Start Time,End Date,End Time
Abfuhr Rest+Bio Bezirk 6,1/3/2017,06:00,1/3/2017,06:00
Abfuhr Papier Bezirk 6,1/4/2017,06:00,1/4/2017,06:00
Abfuhr Weihnachtsbäume Bezirk 6,1/10/2017,06:00,1/10/2017,06:00
Abfuhr LVP Bezirk 6,1/12/2017,06:00,1/12/2017,06:00
Abfuhr Rest+Bio Bezirk 6,1/17/2017,06:00,1/17/2017,06:00
Abfuhr LVP Bezirk 6,1/26/2017,06:00,1/26/2017,06:00
Abfuhr Rest+Bio Bezirk 6,1/31/2017,06:00,1/31/2017,06:00
Abfuhr Papier Bezirk 6,2/1/2017,06:00,2/1/2017,06:00
Abfuhr LVP Bezirk 6,2/9/2017,06:00,2/9/2017,06:00
Abfuhr Rest+Bio Bezirk 6,2/14/2017,06:00,2/14/2017,06:00
Abfuhr LVP Bezirk 6,2/23/2017,06:00,2/23/2017,06:00
Abfuhr Rest+Bio Bezirk 6,2/28/2017,06:00,2/28/2017,06:00
Abfuhr Papier Bezirk 6,3/1/2017,06:00,3/1/2017,06:00
Abfuhr LVP Bezirk 6,3/9/2017,06:00,3/9/2017,06:00
Abfuhr Garten Frühjahr Bezirk 6,3/14/2017,06:00,3/14/2017,06:00
Abfuhr Rest+Bio Bezirk 6,3/14/2017,06:00,3/14/2017,06:00
Abfuhr LVP Bezirk 6,3/23/2017,06:00,3/23/2017,06:00
Abfuhr Rest+Bio Bezirk 6,3/28/2017,06:00,3/28/2017,06:00
Abfuhr Papier Bezirk 6,3/29/2017,06:00,3/29/2017,06:00
Abfuhr LVP Bezirk 6,4/6/2017,06:00,4/6/2017,06:00
Abfuhr Rest+Bio Bezirk 6,4/11/2017,06:00,4/11/2017,06:00
Abfuhr LVP Bezirk 6,4/21/2017,06:00,4/21/2017,06:00
Abfuhr Rest+Bio Bezirk 6,4/25/2017,06:00,4/25/2017,06:00
Abfuhr Papier Bezirk 6,4/26/2017,06:00,4/26/2017,06:00
Abfuhr LVP Bezirk 6,5/5/2017,06:00,5/5/2017,06:00
Abfuhr Rest+Bio Bezirk 6,5/9/2017,06:00,5/9/2017,06:00
Abfuhr LVP Bezirk 6,5/18/2017,06:00,5/18/2017,06:00
Abfuhr Rest+Bio Bezirk 6,5/23/2017,06:00,5/23/2017,06:00
Abfuhr Papier Bezirk 6,5/24/2017,06:00,5/24/2017,06:00
Abfuhr LVP Bezirk 6,6/1/2017,06:00,6/1/2017,06:00
Abfuhr Rest+Bio Bezirk 6,6/7/2017,06:00,6/7/2017,06:00
Abfuhr LVP Bezirk 6,6/16/2017,06:00,6/16/2017,06:00
Abfuhr Rest+Bio Bezirk 6,6/20/2017,06:00,6/20/2017,06:00
Abfuhr Papier Bezirk 6,6/21/2017,06:00,6/21/2017,06:00
Abfuhr LVP Bezirk 6,6/29/2017,06:00,6/29/2017,06:00
Abfuhr Rest+Bio Bezirk 6,7/4/2017,06:00,7/4/2017,06:00
Abfuhr LVP Bezirk 6,7/13/2017,06:00,7/13/2017,06:00
Abfuhr Rest+Bio Bezirk 6,7/18/2017,06:00,7/18/2017,06:00
Abfuhr Papier Bezirk 6,7/19/2017,06:00,7/19/2017,06:00
Abfuhr LVP Bezirk 6,7/27/2017,06:00,7/27/2017,06:00
Abfuhr Rest+Bio Bezirk 6,8/1/2017,06:00,8/1/2017,06:00
Abfuhr LVP Bezirk 6,8/10/2017,06:00,8/10/2017,06:00
Abfuhr Rest+Bio Bezirk 6,8/15/2017,06:00,8/15/2017,06:00
Abfuhr Papier Bezirk 6,8/16/2017,06:00,8/16/2017,06:00
Abfuhr LVP Bezirk 6,8/24/2017,06:00,8/24/2017,06:00
Abfuhr Rest+Bio Bezirk 6,8/29/2017,06:00,8/29/2017,06:00
Abfuhr LVP Bezirk 6,9/7/2017,06:00,9/7/2017,06:00
Abfuhr Rest+Bio Bezirk 6,9/12/2017,06:00,9/12/2017,06:00
Abfuhr Papier Bezirk 6,9/13/2017,06:00,9/13/2017,06:00
Abfuhr LVP Bezirk 6,9/21/2017,06:00,9/21/2017,06:00
Abfuhr Rest+Bio Bezirk 6,9/26/2017,06:00,9/26/2017,06:00
Abfuhr LVP Bezirk 6,10/6/2017,06:00,10/6/2017,06:00
Abfuhr Rest+Bio Bezirk 6,10/10/2017,06:00,10/10/2017,06:00
Abfuhr Papier Bezirk 6,10/11/2017,06:00,10/11/2017,06:00
Abfuhr LVP Bezirk 6,10/19/2017,06:00,10/19/2017,06:00
Abfuhr Rest+Bio Bezirk 6,10/24/2017,06:00,10/24/2017,06:00
Abfuhr LVP Bezirk 6,11/3/2017,06:00,11/3/2017,06:00
Abfuhr Rest+Bio Bezirk 6,11/7/2017,06:00,11/7/2017,06:00
Abfuhr Papier Bezirk 6,11/8/2017,06:00,11/8/2017,06:00
Abfuhr LVP Bezirk 6,11/16/2017,06:00,11/16/2017,06:00
Abfuhr Garten Herbst Bezirk 611/21/2017,06:00,11/21/2017,06:00
Abfuhr Rest+Bio Bezirk 6,11/21/2017,06:00,11/21/2017,06:00
Abfuhr LVP Bezirk 6,11/30/2017,06:00,11/30/2017,06:00
Abfuhr Rest+Bio Bezirk 6,12/5/2017,06:00,12/5/2017,06:00
Abfuhr Papier Bezirk 6,12/6/2017,06:00,12/6/2017,06:00
Abfuhr LVP Bezirk 6,12/14/2017,06:00,12/14/2017,06:00
Abfuhr Rest+Bio Bezirk 6,12/19/2017,06:00,12/19/2017,06:00
Abfuhr LVP Bezirk 6,12/29/2017,06:00,12/29/2017,06:00

I also looked inside the SQL Database.
DESCRIBE oc_calendarobjects;
±---------------±--------------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±---------------±--------------------±-----±----±--------±---------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| calendardata | longblob | YES | | NULL | |
| uri | varchar(255) | YES | | NULL | |
| calendarid | int(10) unsigned | NO | MUL | NULL | |
| lastmodified | int(10) unsigned | YES | | NULL | |
| etag | varchar(32) | YES | | NULL | |
| size | bigint(20) unsigned | NO | | NULL | |
| componenttype | varchar(8) | YES | | NULL | |
| firstoccurence | bigint(20) unsigned | YES | | NULL | |
| lastoccurence | bigint(20) unsigned | YES | | NULL | |
| uid | varchar(255) | YES | | NULL | |
| classification | int(11) | YES | | 0 | |
±---------------±--------------------±-----±----±--------±---------------+
12 rows in set (0,00 sec)

so there is no field reminder or something similar, it is all stored in calenderdata:

It looks like this without a reminder

select calendardata from oc_calendarobjects WHERE UID=‘AAAAAMZSGK4TrTVFhW7CnPFbX+kHAHQYWBF9CmdNpWQbPJNYgAAACDzvgZwAAHQYWBF9CmdNpWQbPJNYgAAACDzv0AEAAA==’ ;
±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| calendardata |
±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| BEGIN:VCALENDAR
PRODID:-//Nextcloud calendar v1.5.0
VERSION:2.0
CALSCALE:GREGORIAN
BEGIN:VEVENT
CLASS:PUBLIC
CREATED:20161109T123334Z
DTSTAMP:20161109T123616Z
LAST-MODIFIED:20161109T123334Z
PRIORITY:5
SEQUENCE:0
SUMMARY:Abfuhr Rest+Bio Bezirk 6 test
TRANSP:OPAQUE
UID:AAAAAMZSGK4TrTVFhW7CnPFbX+kHAHQYWBF9CmdNpWQbPJNYgAAACDzvgZwAAHQYWBF9Cmd
NpWQbPJNYgAAACDzv0AEAAA==
X-MICROSOFT-CDO-BUSYSTATUS:BUSY
X-MICROSOFT-CDO-IMPORTANCE:1
STATUS:CONFIRMED
DTSTART:20170103T050000Z
DTEND:20170103T050000Z
END:VEVENT
END:VCALENDAR |
±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

and it looks like this with a reminder

select calendardata from oc_calendarobjects WHERE UID=‘AAAAAMZSGK4TrTVFhW7CnPFbX+kHAHQYWBF9CmdNpWQbPJNYgAAACDzvgZwAAHQYWBF9CmdNpWQbPJNYgAAACDzv0AEAAA==’ ;
±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| calendardata |
±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| BEGIN:VCALENDAR
PRODID:-//Nextcloud calendar v1.5.0
VERSION:2.0
CALSCALE:GREGORIAN
BEGIN:VEVENT
CLASS:PUBLIC
CREATED:20161109T123334Z
DTSTAMP:20161109T123616Z
LAST-MODIFIED:20161109T123334Z
PRIORITY:5
SEQUENCE:0
SUMMARY:Abfuhr Rest+Bio Bezirk 6 test
TRANSP:OPAQUE
UID:AAAAAMZSGK4TrTVFhW7CnPFbX+kHAHQYWBF9CmdNpWQbPJNYgAAACDzvgZwAAHQYWBF9Cmd
NpWQbPJNYgAAACDzv0AEAAA==
X-MICROSOFT-CDO-BUSYSTATUS:BUSY
X-MICROSOFT-CDO-IMPORTANCE:1
STATUS:CONFIRMED
DTSTART:20170103T050000Z
DTEND:20170103T050000Z
BEGIN:VALARM
ACTION;X-NC-GROUP-ID=0:DISPLAY
TRIGGER:-PT11H
END:VALARM
END:VEVENT
END:VCALENDAR |
±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

I never updated an SQL column with type longblob. How do I add data there without destroying the rest of the field?

[Schmu]

Hi,

Sorry for the late response, had some things to do and was then analyzing this issue a while.
I didn’t thought this might be that difficult actually. Right now I’m stuck with the import of CSV files. I copied your file, went to the calendar app, selected “Import Calendar”, had to switch to “all files” as this import function usually wants to have ICS files and after selecting the csv file I only see the following message forever: “analyzing the calendar”

I wanted to try further CSV headers that I found described on the Internet:

Subject,Start Date,Start Time,End Date,End Time,Reminder On/Off,Reminder Date,Reminder Time
Abfuhr Rest+Bio Bezirk 6,3/13/2017,06:00,3/13/2017,06:00,TRUE,3/12/2017,13:00

But without import, I can’t test unfortunately. Maybe I search for a way with the database update. Which might indeed be pretty difficult.
How did you import the CSV file? I’m using NC 11.0.2 with latest calendar app version 1.5.1.

[Schmu]

Okay, I think I have an idea, that works.
After you imported the csv file, export it from the calendar app. You should then have a ics file. Open this file in notepad++ or an equivalent editor and do a find and replace:

You search for:
END:VEVENT

and replace it with:
BEGIN:VALARM\r\nACTION;X-NC-GROUP-ID=0:DISPLAY\r\nTRIGGER:-PT11H\r\nEND:VALARM\r\nEND:VEVENT

Make sure you have the check box “find \r and \n” activated.
Replace all and save the changed file. I would then drop the calendar and freshly create the calendar you want.
Afterwards you should be able to import the ics file. I tested it and had the correct reminders then.

Be aware that with this setting you only have popups. If you want to have a different type of notifiers you have the change the line “ACTION:”. NC offers popup, audio, email. For email the action would be:
ACTION:EMAIL

Hope this helps.

[robinx999]

Hi,
wow THX a lot. that seems to work.
Actualy I am not sure if I had realy imported the CSV file to owncloud maybe I just used it in Google in the Past. Maybe I should have just linked my citys site http://www.vue-waltrop.de/abfallkalender/
A popup is all I needed mostly, I just use CalDAV Sync Adapter so I get a notification on my smartphone so that I can get the trash out in time.

I hope in the future there is an easier solution, but if not I saved the sed command to convert a calender, so that I can do it next year december, and so on. But I realy hope for a solution involving the webinterface

greetings

[Schmu]

I agree an easier solution would be great. So a mass modify in the GUI or the possibility to define a default reminder for imported calendars.
Maybe there even is a better solution then mine, but I was just looking for a way to help you asap. I’m glad that worked

[tflidd]

I just marked your solution. If you wish a better integration in the calendar app, please open a feature request on https://github.com/nextcloud/calendar/issues (if it does not already exist).