Probably a very simple answer, but I want to extract the time groups. Is this in a handy file somewhere like the trunk definitions are?
I’d like to design something that will a week before each federal (US) holiday send out an email saying “We’ll be closed on ____ day, so if you need to get ahold of us during the day send us an email. It looks like you’ll be open / closed / closing early / etc that day. If this is not correct let us know ASAP”.
Ideally I’d do this with a script so I don’t have to enter the holidays multiple times (Outlook, CRM program, whatever I end up using to generate emails).
mysql -u user -ppassword -D asterisk -e “SELECT * from timegroups_groups”
and
mysql -u user -ppassword -D asterisk -e “SELECT * from timegroups_details”
How you identify your “holiday” groups would be up to you, but then something like:-
mysql -u user -ppassword -D asterisk -e “SELECT description,time FROM timegroups_details LEFT JOIN timegroups_groups ON timegroups_details.timegroupid=timegroups_groups.id WHERE timegroups_groups.description=‘Holiday’”
and it would be trivial to add State wide Public Holidays as pertinent
I suggest you start with
and as James suggests you can add/read them either with the somewhat undocumented FreePBX PHP wrapper or directly with mysql queries/updates into asterisk.timegroups_groups and asterisk.timegroups_details, I’m pretty sure either way would work, of course I prefer my way as to me it is more succinct
And then you would need to identify the earliest occurence of each "holiday’ , subtract 7 days and then send your email, I suggest easier in mysql than otherwise.
When you try to script them all, as I said Easter is a problem, for “Western” Easter in the Gregorian calendar , then add to you asterisk database in mysql a function:-
drop function easter_date;
Delimiter //
CREATE FUNCTION easter_date(year_date DATETIME)
RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
DECLARE a INT;
DECLARE b INT;
DECLARE c INT;
DECLARE d INT;
DECLARE e INT;
DECLARE f INT;
DECLARE g INT;
DECLARE h INT;
DECLARE i INT;
DECLARE k INT;
DECLARE l INT;
DECLARE m INT;
DECLARE n INT;
DECLARE p INT;
DECLARE x INT;
set x = YEAR(year_date);
set a = x % 19;
set b = TRUNCATE(x / 100, 0);
set c = x % 100;
set d = TRUNCATE(b / 4, 0);
set e = b % 4;
set f = TRUNCATE((b + 8) / 25, 0);
set g = TRUNCATE(((b - f) + 1) / 3, 0);
set h = (((((19 * a) + b) - d) - g) + 15) % 30;
set i = TRUNCATE(c / 4, 0);
set k = c % 4;
set l = (32 + 2 * e + 2 * i - h - k) % 7;
set m = TRUNCATE((a + 11 * h + 22 * l) / 451, 0);
set n = TRUNCATE((h + l - 7 * m + 114) / 31, 0);
set p = ((((h + l) - (7 * m)) + 114) % 31) + 1;
return CONCAT(x,"-",n,"-",p);
END
//
DELIMITER ;
will give you a formatted FreePBX timecondition for Easter in 2015, you would need to update that before the first moonrise after nehelim 14 or somesuch, or maybe just before March is easier
