/**
* inform leader about open event services
* TODO: no idea if this could be improved - lots of sql requests and loops
* @return boolean
*/
function churchservice_inform_leader()
{
global $base_url;
include_once "churchservice_db.php";
// get all group ids from services
$res = db_query("SELECT cdb_gruppen_ids FROM {cs_service}\n WHERE cdb_gruppen_ids!='' AND cdb_gruppen_ids IS NOT NULL");
// TODO: use WHERE cdb_gruppen_ids > '' ?
// WHERE cdb_gruppen_ids>''); // TODO: works too
$arr = array();
foreach ($res as $g) {
$arr[] = $g->cdb_gruppen_ids;
}
if (!count($arr)) {
return false;
}
// get persons being (co)leader of one of this service groups
$res = db_query("SELECT p.id AS person_id, gpg.gruppe_id, p.email, p.vorname, p.name, p.spitzname, p.cmsuserid\n FROM {cdb_person} p, {cdb_gemeindeperson_gruppe} gpg, {cdb_gemeindeperson} gp\n WHERE gpg.gemeindeperson_id = gp.id AND p.id = gp.person_id AND status_no >= 1 AND status_no <= 2\n AND gpg.gruppe_id IN (" . db_implode($arr) . ")");
// Aggregiere nach Person_Id P1[G1,G2,G3],P2[G3]
$persons = array();
foreach ($res as $p) {
$data = churchcore_getUserSettings("churchservice", $p->person_id);
// if person has rights and has not deselected info emails
$auth = getUserAuthorization($p->person_id);
if (isset($auth["churchservice"]["view"]) && (!isset($data["informLeader"]) || $data["informLeader"])) {
if (!isset($data["informLeader"])) {
$data["informLeader"] = 1;
churchcore_saveUserSetting("churchservice", $p->person_id, "informLeader", "1");
}
if (empty($persons[$p->person_id])) {
$persons[$p->person_id] = array("group" => array(), "service" => array(), "person" => $p);
}
$persons[$p->person_id]["group"][] = $p->gruppe_id;
}
}
// who should get an email?
foreach ($persons as $person_id => $p) {
if (!ct_checkUserMail($person_id, "informLeaderService", -1, 6 * 24)) {
$persons[$person_id] = null;
// unset($persons[$person_id])?
}
}
// get matching services
// TODO: nearly the same request as above (additonal bezeichnung, id service_id)
$res = db_query("SELECT cdb_gruppen_ids, bezeichnung, id AS service_id\n FROM {cs_service}\n WHERE cdb_gruppen_ids is not null");
foreach ($res as $d) {
$group_ids = explode(",", $d->cdb_gruppen_ids);
foreach ($persons as $key => $person) {
if ($person) {
foreach ($person["group"] as $person_group) {
if (in_array($person_group, $group_ids)) {
$persons[$key]["service"][] = $d->service_id;
}
}
}
}
}
// get events for each person
// TODO: add DAYS_TO_INFORM_LEADER_ABOUT_OPEN_SERVICES to $conf?
foreach ($persons as $person_id => $person) {
if ($person) {
$res = db_query("SELECT es.id, c.bezeichnung AS event,\n DATE_FORMAT(e.startdate, '%d.%m.%Y %H:%i') AS datum, es.name, s.bezeichnung AS service\n FROM {cs_event} e, {cs_eventservice} es, {cs_service} s, {cc_cal} c\n WHERE e.valid_yn=1 AND c.id=e.cc_cal_id AND es.service_id in (" . db_implode($person["service"]) . ")\n AND es.event_id = e.id AND es.service_id = s.id AND es.valid_yn = 1 AND zugesagt_yn = 0\n AND e.startdate > current_date AND DATEDIFF(e.startdate,CURRENT_DATE) <= " . DAYS_TO_INFORM_LEADER_ABOUT_OPEN_SERVICES . "\n ORDER BY e.startdate");
$openServices = array();
foreach ($res as $s) {
$openServices[] = $s;
}
if (count($openServices)) {
$data = array('moreInfoUrl' => "{$base_url}?q=churchservice", 'settingsUrl' => "{$base_url}?q=churchservice#SettingsView", 'openServices' => $openServices, 'surname' => $person["person"]->vorname, 'nickname' => $person["person"]->spitzname ? $person["person"]->spitzname : $person["person"]->vorname, 'name' => $person["person"]->name);
$lang = getUserLanguage($person["person"]->person_id);
$content = getTemplateContent('email/openServicesLeaderInfo', 'churchservice', $data, null, $lang);
churchservice_send_mail("[" . getConf('site_name') . "] " . t2($lang, 'open.services'), $content, $person["person"]->email);
}
}
}
}
/**
* Delete CS-Event, inform people about deleted event and delete calendar entry
*
* @param $params["id"] id of Event
* @param $params["informDeleteEvent"] 1=inform people. Default=0
* @param $params["deleteCalEntry"] 1=delete Calender entry. Default=0
* @throws CTException if Event or Calender Entry could not be found
* @throws CTNoPermission
*/
function churchservice_deleteEvent($params)
{
global $user;
if (!user_access("edit events", "churchservice")) {
throw new CTNoPermission("edit events", "churchservice");
}
ct_log("[ChurchService] " . t('remove.event'), 2, $params["id"], "service");
$db_event = db_query("SELECT e.*, DATE_FORMAT(e.startdate, '%d.%m.%Y %H:%i') AS date_de\n FROM {cs_event} e\n WHERE id=:event_id", array(":event_id" => $params["id"]))->fetch();
if (!$db_event) {
if ($params["id"]) {
throw new CTException("deleteEvent(" . $params["id"] . "): " . t('x.not.found', t('event')));
} else {
return;
}
}
// Inform people about the deleted event
if (getVar("informDeleteEvent", false, $params)) {
$db_cal = db_query("SELECT * FROM {cc_cal}\n WHERE id=:cal_id AND DATEDIFF(startdate, now())>=0", array(":cal_id" => $db_event->cc_cal_id))->fetch();
if ($db_cal != false) {
$db = db_query("SELECT p.id p_id, p.vorname, p.name, IF(p.spitzname, p.spitzname, p.vorname) AS nickname, p.email FROM {cs_eventservice} es, {cdb_person} p\n WHERE event_id = :event_id AND valid_yn = 1 AND p.id = es.cdb_person_id\n AND es.cdb_person_id IS NOT NULL AND p.email != ''", array(":event_id" => $params["id"]));
foreach ($db as $p) {
$lang = getUserLanguage($p->p_id);
$subject = "[" . getConf('site_name') . "] " . t2($lang, 'cancelation.of.event.date', $db_cal->bezeichnung, $db_event->date_de);
$data = array('person' => $p, 'eventTitle' => $db_cal->bezeichnung, 'eventDate' => $db_event->date_de);
// Deine Dienstanfrage wurde entsprechend entfernt.'
$content = getTemplateContent('email/eventDeleted', 'churchservice', $data, null, $lang);
churchservice_send_mail($subject, $content, $p->email);
}
}
}
if (getVar("deleteCalEntry", 1, $params) == 1) {
db_query("DELETE FROM {cs_eventservice}\n WHERE event_id=:event_id", array(":event_id" => $params["id"]), false);
db_query("DELETE FROM {cs_event}\n WHERE id=:event_id", array(":event_id" => $params["id"]), false);
} else {
db_query("UPDATE {cs_event} SET valid_yn=0\n WHERE id=:id", array(":id" => $params["id"]));
}
}
/**
* update event service
*
* @param array $params
* @throws CTNoPermission
* @return array
*/
function churchservice_updateEventService($params)
{
global $user, $base_url;
$id = $params["id"];
$name = isset($params["name"]) ? $params["name"] : null;
$cdb_person_id = isset($params["cdb_person_id"]) ? $params["cdb_person_id"] : null;
$reason = isset($params["reason"]) ? $params["reason"] : null;
$zugesagt_yn = $params["zugesagt_yn"];
include_once CHURCHSERVICE . "/churchservice_db.php";
$res = array();
if ($name == "null") {
$name = null;
}
if ($cdb_person_id == "null") {
$cdb_person_id = null;
}
// look if event is still valid
$arr = db_query("SELECT * FROM {cs_eventservice} WHERE id=:id", array(":id" => $id))->fetch();
if (!$arr) {
return "Entry not found, id not valid!";
}
if ($arr->valid_yn != 1 && !isset($params["valid_yn"])) {
return "Eintrag konnte nicht angepasst werden, da veraltet. Bitte neu laden!";
}
// check auth
$auth = churchservice_getAuthorization();
// Es ist trotzdem erlaubt, wenn die PersonId eingetragen ist, dann wurde er ja angefragt
if (!isset($auth["editservice"][$arr->service_id]) && !isset($auth["memberservice"][$arr->service_id]) && !churchService_adminOfEvent($arr->event_id) && $arr->cdb_person_id != $user->id) {
throw new CTNoPermission("editservice", "churchservice");
}
// Wenn die neue �nderung vom gleichen User kommt und noch kein Cron gelaufen ist,
// Oder wenn valid_yn valide ist, denn dann soll es upgedates werden!
// brauchen wir kein neuen Insert, sondern machen nur ein Update.
// Denn wahrscheinlich war es vorher nur ein Versehen.
// TODO: translation correct?
// if changing user is the same as last time and cron had not yet run
// or if valid_yn is valide (update wished), we dont need an insert, only an update,
// because the last edit probably was a mistake
$dt = new datetime();
if ($arr->modified_pid == $user->id && $arr->mailsenddate == null || isset($params["valid_yn"])) {
$valid_yn = 1;
if (isset($params["valid_yn"])) {
$valid_yn = $params["valid_yn"];
}
db_update("cs_eventservice")->fields(array("name" => $name, "cdb_person_id" => $cdb_person_id, "valid_yn" => $valid_yn, "zugesagt_yn" => $zugesagt_yn, "reason" => $reason, "mailsenddate" => null, "modified_date" => $dt->format('Y-m-d H:i:s'), "modified_pid" => $user->id))->condition("id", $id, "=")->execute();
$new_id = $id;
} else {
// new entry for edit
$new_id = db_insert("cs_eventservice")->fields(array("event_id" => $arr->event_id, "service_id" => $arr->service_id, "valid_yn" => 1, "counter" => $arr->counter, "name" => $name, "cdb_person_id" => $cdb_person_id, "zugesagt_yn" => $zugesagt_yn, "reason" => $reason, "modified_date" => $dt->format('Y-m-d H:i:s'), "modified_pid" => $user->id))->execute();
//if all ok set existing entry to old
db_update("cs_eventservice")->fields(array("valid_yn" => 0))->condition("id", $id, "=")->execute();
}
include_once CHURCHCORE . "/churchcore_db.php";
$leader = churchcore_getPersonById($arr->modified_pid);
$event = db_query("SELECT e.startdate datum, c.bezeichnung FROM {cs_event} e, {cc_cal} c\n WHERE e.cc_cal_id=c.id and e.id=:event_id", array(":event_id" => $arr->event_id))->fetch();
$service = churchcore_getTableData("cs_service", "", "id=" . $arr->service_id);
if ($event && $service) {
$service = $service[$arr->service_id];
$subject = "[" . readConf('site_name', "ChurchTools") . "] ";
$txt = "";
// confirm
if ($zugesagt_yn == 1) {
$txt .= t("surname.name.has.approved.name.for.service.x.for.date.event", $user->vorname, $user->name, $service->bezeichnung, $event->datum, $event->bezeichnung, $name);
$subject .= t("surname.name.has.approved.a.request", $user->vorname, $user->name);
} else {
if ($name) {
$txt .= t("surname.name.has.proposed.name.for.service.x.for.date.event", $user->vorname, $user->name, $service->bezeichnung, $event->datum, $event->bezeichnung, $name);
$subject .= t("surname.name.has.proposed.someone", $user->vorname, $user->name);
} else {
$txt .= t("surname.name.has.canceled.the.service.x.for.date.event", $user->vorname, $user->name, $service->bezeichnung, $event->datum, $event->bezeichnung);
$subject .= t("surname.name.has.canceled.a.request", $user->vorname, $user->name);
}
}
if ($reason != null) {
$txt .= "<p>Folgendes wurde als Grund angegeben: " . $reason;
}
ct_notify("service", $arr->service_id, $txt);
if ($leader != null) {
// send mail, if someone other then the inquirer himself confirmed or canceled
//TODO: maybe use asker, better to understand for nonenglish programmers
//TODO: use email template
if (!empty($leader->email) && $user != null && $leader->id != $user->id) {
$setting = churchcore_getUserSettings("churchservice", $leader->id);
if (isset($setting["informInquirer"]) && $setting["informInquirer"] == 1) {
$txt = "<h3>Hallo " . $leader->vorname . ",</h3><p>\n " . $txt;
$txt .= '<p><a href="' . $base_url . '?q=churchservice&id=' . $arr->event_id . '" class="btn btn-primary">Event aufrufen</a>';
churchservice_send_mail($subject, $txt, $leader->email);
}
}
if (!isset($setting["informInquirer"])) {
churchcore_saveUserSetting("churchservice", $leader->id, "informInquirer", 0);
}
}
}
$arr = db_query("SELECT es.*, concat(p.vorname,' ',p.name) as modifieduser FROM {cs_eventservice} es, {cdb_person} p \n WHERE p.id=es.modified_pid and es.id=:id", array(":id" => $new_id))->fetch();
$res["eventservice"] = churchservice_extractEventServiceData($arr);
$res["result"] = true;
return $res;
}
/**
* Delete CS-Event and inform people about deleted event and delete calender entry
*
* @param $params["id"] id of Event
* @param $params["informDeleteEvent"] 1=inform people. Default=0
* @param $params["deleteCalEntry"] 1=delete Calender entry. Default=0
* @throws CTException if Event or Calender Entry could not be found
* @throws CTNoPermission
*/
function churchservice_deleteEvent($params, $source = null)
{
global $user;
if ($source == null) {
if (!user_access("edit events", "churchservice")) {
throw new CTNoPermission("edit events", "churchservice");
}
ct_log("[ChurchService] Entferne Event!", 2, $params["id"], "service");
}
$db_event = db_query("select e.*, DATE_FORMAT(e.startdate, '%d.%m.%Y %H:%i') date_de from {cs_event} e where id=:event_id", array(":event_id" => $params["id"]))->fetch();
if (!$db_event) {
if ($params["id"] != null) {
throw new CTException("Event nicht gefunden!");
} else {
return;
}
}
// Inform people about the deleted event
if (isset($params["informDeleteEvent"]) && $params["informDeleteEvent"] == 1) {
$db_cal = db_query("select * from {cc_cal} where id=:cal_id", array(":cal_id" => $db_event->cc_cal_id))->fetch();
if (!$db_cal) {
throw new CTException("Event im Kalender nicht gefunden!");
}
$db = db_query("select p.* from {cs_eventservice} es, {cdb_person} p\n where event_id=:event_id and valid_yn=1 and\n p.id=es.cdb_person_id and es.cdb_person_id is not null and p.email!=''", array(":event_id" => $params["id"]));
foreach ($db as $p) {
$subject = "[" . variable_get('site_name') . "] Absage von " . $db_cal->bezeichnung . " " . $db_event->date_de;
$txt = '<h3>Hallo ' . $p->vorname . "!</h3>";
$txt .= 'Das Event ' . $db_cal->bezeichnung . " am " . $db_event->date_de . ' wurde von <i>' . $user->vorname . ' ' . $user->name . '</i> abgesagt. Deine Dienstanfrage wurde entsprechend entfernt.';
churchservice_send_mail($subject, $txt, $p->email);
}
}
if (!isset($params["deleteCalEntry"]) || $params["deleteCalEntry"] == 1) {
db_query("delete from {cs_eventservice} where event_id=:event_id", array(":event_id" => $params["id"]), false);
db_query("delete from {cs_event} where id=:event_id", array(":event_id" => $params["id"]), false);
db_query("delete from {cc_cal} where id=:id and repeat_id=0", array(":id" => $db_event->cc_cal_id));
} else {
db_query("update {cs_event} set valid_yn=0 where id=:id", array(":id" => $params["id"]));
}
}
function churchservice_inform_leader()
{
global $base_url;
include_once "churchservice_db.php";
// Hole erst mal die Gruppen_Ids, damit ich gleich nicht alle Personen holen mu�
$res = db_query("SELECT cdb_gruppen_ids FROM {cs_service} where cdb_gruppen_ids!='' and cdb_gruppen_ids is not null");
$arr = array();
foreach ($res as $g) {
$arr[] = $g->cdb_gruppen_ids;
}
if (count($arr) == 0) {
return false;
}
// Hole nun die Person/Gruppen wo die Person Leiter oder Co-Leiter ist
$res = db_query("SELECT p.id person_id, gpg.gruppe_id, p.email, p.vorname, p.cmsuserid FROM {cdb_person} p, {cdb_gemeindeperson_gruppe} gpg, {cdb_gemeindeperson} gp\n where gpg.gemeindeperson_id=gp.id and p.id=gp.person_id and status_no>=1 and status_no<=2\n and gpg.gruppe_id in (" . implode(",", $arr) . ")");
// Aggregiere nach Person_Id P1[G1,G2,G3],P2[G3]
$persons = array();
foreach ($res as $p) {
$data = churchcore_getUserSettings("churchservice", $p->person_id);
// Darf er �berhaupt noch, und wenn ja dann schaue ob der Leiter es will.
// (Wenn noch nicht best�tigt, dann wird davon ausgegangen
$auth = getUserAuthorization($p->person_id);
if (isset($auth["churchservice"]["view"]) && (!isset($data["informLeader"]) || $data["informLeader"] == 1)) {
if (!isset($data["informLeader"])) {
$data["informLeader"] = 1;
churchcore_saveUserSetting("churchservice", $p->person_id, "informLeader", "1");
}
if (isset($persons[$p->person_id])) {
$arr = $persons[$p->person_id]["group"];
} else {
$persons[$p->person_id] = array();
$arr = array();
$persons[$p->person_id]["service"] = array();
$persons[$p->person_id]["person"] = $p;
}
$arr[] = $p->gruppe_id;
$persons[$p->person_id]["group"] = $arr;
}
}
// Gehe nun die Personen durch und schaue wer seit einer Zeit keine Mail mehr bekommen hatte.
foreach ($persons as $person_id => $p) {
if (!churchcore_checkUserMail($person_id, "informLeaderService", -1, 6 * 24)) {
$persons[$person_id] = null;
}
}
// Suche nun dazu die passenden Services
$res = db_query("SELECT cdb_gruppen_ids, bezeichnung, id service_id FROM {cs_service} where cdb_gruppen_ids is not null");
foreach ($res as $d) {
$gruppen_ids = explode(",", $d->cdb_gruppen_ids);
foreach ($persons as $key => $person) {
if ($person != null) {
foreach ($person["group"] as $person_group) {
if (in_array($person_group, $gruppen_ids)) {
$persons[$key]["service"][] = $d->service_id;
}
}
}
}
}
// Gehe nun die Personen durch und suche nach Events
foreach ($persons as $person_id => $person) {
if ($person != null) {
$res = db_query("SELECT es.id, c.bezeichnung event, DATE_FORMAT(e.startdate, '%d.%m.%Y %H:%i') datum, es.name, s.bezeichnung service \n FROM {cs_event} e, {cs_eventservice} es, {cs_service} s, {cc_cal} c \n where e.valid_yn=1 and c.id=e.cc_cal_id and es.service_id in (" . implode(",", $person["service"]) . ")\n and es.event_id=e.id and es.service_id=s.id and es.valid_yn=1 and zugesagt_yn=0\n and e.startdate>current_date and datediff(e.startdate,CURRENT_DATE)<=60 order by e.startdate");
$txt = '';
foreach ($res as $es) {
$txt .= "<li>" . $es->datum . " " . $es->event . " - Dienst " . $es->service . ": ";
$txt .= '<font style="color:red">';
if ($es->name == null) {
$txt .= "?";
} else {
$txt .= $es->name . "?";
}
$txt .= '</font>';
}
if ($txt != '') {
$txt = "<h3>Hallo " . $person["person"]->vorname . "!</h3><p>Es sind in den nächsten 60 Tagen noch folgende Dienste offen:<ul>" . $txt . "</ul>";
$txt .= '<p><a href="' . $base_url . '/?q=churchservice" class="btn">' . t("more.information") . '</a> ';
$txt .= '<p><a href="' . $base_url . '/?q=churchservice#SettingsView" class="btn">Benachrichtigung deaktivieren</a>';
churchservice_send_mail("[" . readConf('site_name', 'ChurchTools') . "] Offene Dienste", $txt, $person["person"]->email);
}
}
}
}
