mirror of https://github.com/sipwise/db-schema.git
You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
41 lines
1.6 KiB
41 lines
1.6 KiB
SET autocommit=0;
|
|
USE provisioning;
|
|
|
|
DROP VIEW `provisioning`.`v_time_sets_ical`;
|
|
CREATE VIEW `v_time_sets_ical` AS
|
|
SELECT s.id, s.name,
|
|
concat (
|
|
"BEGIN:VCALENDAR\n",
|
|
"PRODID:-//Mozilla.org/NONSGML Mozilla Calendar V1.1//EN\n",
|
|
"VERSION:2.0\n\n",
|
|
GROUP_CONCAT(concat(
|
|
"BEGIN:VEVENT\n",
|
|
"UID:","sipwise",p.id,"@sipwise",s.id,"\n",
|
|
"SUMMARY:",s.name," event ",p.id,"\n",
|
|
"DTSTART:",DATE_FORMAT(p.start,'%Y%m%dT%H%i%s'),"\n",
|
|
"DTEND:",DATE_FORMAT(p.end,'%Y%m%dT%H%i%s'),"\n",
|
|
"RRULE:",
|
|
"FREQ=",p.freq,
|
|
IFNULL(CONCAT(";COUNT=", p.`count`),''),
|
|
IFNULL(CONCAT(";UNTIL=", DATE_FORMAT(p.until,'%Y%m%dT%H%i%s')),''),
|
|
IFNULL(CONCAT(";INTERVAL=", p.`interval`),''),
|
|
IFNULL(CONCAT(";BYSECOND=", p.bysecond),''),
|
|
IFNULL(CONCAT(";BYMINUTE=", p.byminute),''),
|
|
IFNULL(CONCAT(";BYHOUR=", p.byhour),''),
|
|
IFNULL(CONCAT(";BYDAY=", p.byday),''),
|
|
IFNULL(CONCAT(";BYMONTHDAY=",p.bymonthday),''),
|
|
IFNULL(CONCAT(";BYYEARDAY=", p.byyearday),''),
|
|
IFNULL(CONCAT(";BYWEEKNO=", p.byweekno),''),
|
|
IFNULL(CONCAT(";BYMONTH=", p.bymonth),''),
|
|
IFNULL(CONCAT(";BYSETPOS=", p.bysetpos),''),
|
|
"\n",
|
|
IFNULL(CONCAT("DESCRIPTION:",p.`comment`,"\n"),''),
|
|
"END:VEVENT\n"
|
|
)SEPARATOR '\n'),
|
|
"END:VCALENDAR\n"
|
|
) AS ical
|
|
FROM provisioning.voip_time_sets s JOIN voip_time_periods p ON s.id = p.time_set_id
|
|
GROUP BY s.id;
|
|
|
|
COMMIT;
|