/** * 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); } } } }