public function getGroupAndTagInfos()
{
global $user;
$a = array();
if (user_access("view alldata", "churchdb")) {
$a["groups"] = getAllGroups();
} else {
$a["groups"] = churchdb_getMyGroups($user->id, false, true);
}
$a["tags"] = getAllTags();
return $a;
}
/**
*
* @param array $params
* @throws CTException
*/
public function sendEMail($params)
{
global $user;
include_once './' . CHURCHDB . '/churchdb_db.php';
$groups = churchdb_getMyGroups($user->id, true, false);
if (empty($groups[$params["groupid"]])) {
throw new CTException("Group is not allowed!");
}
$ids = churchdb_getAllPeopleIdsFromGroups(array($params["groupid"]));
churchcore_sendEMailToPersonIDs(implode(",", $ids), "[" . getConf('site_name') . "] " . t('message.from.x', "{$user->vorname} {$user->name}"), $params["message"], null, true);
}
/**
* get absent times
* @param string $year
* @return string
*/
function churchservice_getAbsents($year = null)
{
$txt = '';
if (user_access("view", "churchdb")) {
$user = $_SESSION["user"];
include_once CHURCHDB . '/churchdb_db.php';
$groups = churchdb_getMyGroups($user->id, true, true);
$allPersonIds = churchdb_getAllPeopleIdsFromGroups($groups);
if (count($groups) > 0 && count($allPersonIds) > 0) {
$sql = "SELECT p.id p_id, p.name, p.vorname, DATE_FORMAT(a.startdate, '%d.%m.') AS startdate_short,\n DATE_FORMAT(a.startdate, '%d.%m.%Y') AS startdate, DATE_FORMAT(a.enddate, '%d.%m.%Y') AS enddate,\n a.bezeichnung, ar.bezeichnung reason\n FROM {cdb_person} p, {cs_absent} a, {cs_absent_reason} ar\n WHERE a.absent_reason_id=ar.id AND p.id=a.person_id\n AND p.id in (" . db_implode($allPersonIds) . ") ";
if ($year == null) {
$sql .= "AND DATEDIFF(a.startdate,NOW())>=-1 AND DATEDIFF(a.startdate,NOW())<=31";
} else {
$sql .= "AND (DATE_FORMAT(a.startdate, '%Y')={$year} OR DATE_FORMAT(a.enddate, '%Y')={$year})";
}
$sql .= "\n ORDER BY a.startdate";
$db = db_query($sql);
$people = array();
foreach ($db as $a) {
if (!isset($people[$a->p_id])) {
$people[$a->p_id] = array();
}
$people[$a->p_id][] = $a;
}
if (count($people)) {
$txt = '<ul>';
foreach ($people as $p) {
$txt .= '<li>' . $p[0]->vorname . " " . $p[0]->name . ": <p>";
foreach ($p as $abwesend) {
$reason = $abwesend->bezeichnung ? $abwesend->bezeichnung . " ({$abwesend->reason})" : $abwesend->reason;
if ($abwesend->startdate == $abwesend->enddate) {
$txt .= "<small>{$abwesend->startdate} {$reason}</small><br/>";
} else {
$txt .= "<small>{$abwesend->startdate_short} - {$abwesend->enddate} {$reason}</small><br/>";
}
}
}
$txt .= '</ul>';
}
}
}
return $txt;
}
function churchcal_getBirthdays($params)
{
global $user;
$all = isset($params["all"]) && $params["all"] == true;
include_once CHURCHDB . "/churchdb_db.php";
if (!$all) {
$gpids = churchdb_getMyGroups($user->id, true, false);
if ($gpids == null) {
return null;
}
$res = db_query("select p.id, gp.geburtsdatum birthday, concat(p.vorname, ' ', p.name) as name \n from {cdb_person} p, {cdb_gemeindeperson_gruppe} gpg, {cdb_gemeindeperson} gp \n where gpg.gruppe_id in (" . implode(',', $gpids) . ") and gpg.gemeindeperson_id=gp.id and \n gp.person_id=p.id and p.archiv_yn=0 and gp.geburtsdatum is not null");
$arrs = array();
foreach ($res as $a) {
$arrs[$a->id] = $a;
}
return $arrs;
} else {
$persons = churchdb_getAllowedPersonData("archiv_yn=0 and geburtsdatum is not null", "p.id p_id, p.id, gp.id gp_id, concat(p.vorname, ' ',p.name) as name, geburtsdatum birthday");
return $persons;
}
}
function churchdb_getTodos()
{
global $user;
$mygroups = churchdb_getMyGroups($user->id, true, true, false);
$mysupergroups = churchdb_getMyGroups($user->id, true, true, true);
if ($mygroups == null) {
return "";
}
if ($mysupergroups == null) {
$mysupergroups = array(-1);
}
$db = db_query("select p.id, p.vorname, p.name, g.bezeichnung, gpg.status_no, s.bezeichnung status\n from {cdb_person} p, {cdb_gruppe} g, {cdb_gemeindeperson} gp, {cdb_gemeindeperson_gruppe} gpg, {cdb_gruppenteilnehmerstatus} s \n where s.intern_code=gpg.status_no and\n gpg.gemeindeperson_id=gp.id and gp.person_id=p.id and gpg.gruppe_id=g.id and\n ((gpg.gruppe_id in (" . implode(',', $mygroups) . ") and gpg.status_no<-1) \n or (gpg.gruppe_id in (" . implode(',', $mysupergroups) . ") and gpg.status_no=-1))\n order by status");
$arr = array();
if ($db == false) {
return "";
}
foreach ($db as $g) {
if (isset($arr[$g->status_no])) {
$a = $arr[$g->status_no];
} else {
$a = (object) array();
}
if (isset($a->content)) {
$c = $a->content;
} else {
$c = array();
}
$c[] = $g;
$a->content = $c;
$a->status_no = $g->status_no;
$a->status = $g->status;
$arr[$g->status_no] = $a;
}
$txt = "";
$entries = "";
$status = "";
$count = 0;
foreach ($arr as $status) {
$txt .= '<li><p>' . $status->status . ' <label class="pull-right badge badge-' . ($status->status_no == -1 ? "important" : "info") . '">' . count($status->content) . '</label>';
foreach ($status->content as $g) {
$txt .= '<br/><small><a href="?q=churchdb#PersonView/searchEntry:#' . $g->id . '">' . $g->vorname . ' ' . $g->name . '</a>';
$txt .= ' - ' . $g->bezeichnung . '</small>';
}
}
if ($txt != "") {
$txt = '<ul>' . $txt . '</ul>';
}
return $txt;
}
/**
* get birthdays (all or from own groups)
* @param array $params
* @return
*/
function churchcal_getBirthdays($params)
{
global $user;
$all = isset($params["all"]) && $params["all"] == true;
// $all = (bool) getVar("all", false, $params); //TODO: use this, not tested
include_once CHURCHDB . "/churchdb_db.php";
if (!$all) {
$gpids = churchdb_getMyGroups($user->id, true, false);
if (!$gpids) {
return null;
}
$res = db_query("SELECT p.id, gp.geburtsdatum AS birthday, CONCAT(p.vorname, ' ', p.name) AS name\n FROM {cdb_person} p, {cdb_gemeindeperson_gruppe} gpg, {cdb_gemeindeperson} gp\n WHERE gpg.gruppe_id IN (" . db_implode($gpids) . ") AND gpg.gemeindeperson_id=gp.id AND\n gp.person_id=p.id AND p.archiv_yn=0 AND gp.geburtsdatum IS NOT NULL");
$arrs = array();
foreach ($res as $a) {
$arrs[$a->id] = $a;
}
return $arrs;
} else {
//why 2x p.id?
$persons = churchdb_getAllowedPersonData("archiv_yn=0 and geburtsdatum IS NOT NULL", "p.id AS p_id, p.id, gp.id gp_id, CONCAT(p.vorname, ' ',p.name) AS name, geburtsdatum birthday");
return $persons;
}
}
/**
* Holt sich eine Person entweder in den Gruppen in denen ich auch bin oder die Bereiche, wo ich ViewAll habe.
*
* @param string $searchpattern
* @param bool $withMyDepartemtnt, default=false; search also in my department, even if i dont have view all there?
*
* @return array (name => "surname name", id => 123)
*/
function _churchdb_getPersonByName($searchpattern, $withMyDepartment = false)
{
global $user;
// check for view all permisson
$auth = user_access("view alldata", "churchdb");
if ($withMyDepartment) {
if ($auth) {
$auth = array_merge($auth, churchdb_getAllowedDeps());
} else {
$auth = churchdb_getAllowedDeps();
}
}
$data = null;
if ($auth) {
// get matching persons by departement and searchpattern
$res = db_query("SELECT p.*, gp.imageurl FROM {cdb_person} p, {cdb_gemeindeperson} gp, {cdb_bereich_person} bp \n WHERE p.archiv_yn=0 AND bp.person_id=p.id AND gp.person_id=p.id AND bp.bereich_id IN (" . implode(",", $auth) . ") \n AND (UPPER(name) LIKE UPPER('" . $searchpattern . "%') OR UPPER(vorname) LIKE UPPER('" . $searchpattern . "%')\n OR (CONCAT(UPPER(vorname),' ',UPPER(name)) LIKE UPPER('" . $searchpattern . "%') )\n OR (CONCAT(UPPER(spitzname),' ',UPPER(name)) LIKE UPPER('" . $searchpattern . "%') )\n OR (UPPER(email) LIKE UPPER('" . $searchpattern . "%') )\n ) ORDER BY vorname, name");
foreach ($res as $p) {
$data[$p->id]["id"] = $p->id;
if ($p->spitzname) {
$data[$p->id]["name"] = "{$p->vorname} ({$p->spitzname}) {$p->name}";
$data[$p->id]["shortname"] = "{$p->spitzname} {$p->name}";
} else {
$data[$p->id]["name"] = $p->vorname . " " . $p->name;
$data[$p->id]["shortname"] = "{$p->vorname} {$p->name}";
}
$data[$p->id]["imageurl"] = $p->imageurl;
}
}
// get groups i have view permission for
$g_ids = churchdb_getMyGroups($user->id, true, false);
// get matching persons from this groups
if (count($g_ids)) {
$res = db_query("SELECT p.name, p.vorname, p.id, gp.imageurl\n FROM {cdb_gemeindeperson} gp, {cdb_person} p, {cdb_gemeindeperson_gruppe} gpg\n WHERE p.archiv_yn=0 AND gpg.gemeindeperson_id = gp.id AND gp.person_id = p.id\n AND gpg.gruppe_id IN (" . implode(",", $g_ids) . ")\n AND (UPPER(p.vorname) LIKE UPPER('" . $searchpattern . "%') \n OR UPPER(p.vorname) LIKE UPPER('" . $searchpattern . "%')) ORDER BY p.vorname, p.name");
foreach ($res as $p) {
if (!isset($data[$p->id])) {
// if person not already inserted, add them
$arr = array();
// TODO: why not $data[$p->id] here? If not important replace with code below
$arr["id"] = $p->id;
$arr["name"] = $p->vorname . " " . $p->name;
$arr["imageurl"] = $p->imageurl;
$data[] = $arr;
// $data[$p->id]["id"] = $p->id;
// $data[$p->id]["name"] = $p->vorname. " ". $p->name;
// $data[$p->id]["imageurl"] = $p->imageurl;
}
}
}
$arrs["result"] = "ok";
$arrs["data"] = $data;
return $arrs;
}
/**
* Move all Meeting data from a group to another
* @param unknown $params from_g_id, to_g_id, id
*/
public function moveMeetingDataFromGroupPerson($params)
{
global $user;
$dt = new DateTime();
$myGroups = churchdb_getMyGroups($user->id, true, true);
if (user_access("administer groups", "churchdb") || isset($myGroups[$params["from_g_id"]])) {
// Get all data for person and group
$db = db_query("SELECT gt.*, gp.id gp_id FROM {cdb_gruppentreffen} gt, {cdb_gruppentreffen_gemeindeperson} gtgp, cdb_gemeindeperson gp\n WHERE gp.person_id=:p_id AND gtgp.gemeindeperson_id=gp.id AND gtgp.gruppentreffen_id=gt.id", array(":p_id" => $params["id"]));
foreach ($db as $gt) {
// check if new gruppentreffen exists
$to_g = db_query("SELECT * from {cdb_gruppentreffen} WHERE gruppe_id=:gruppe_id AND datumvon=:datumvon", array(":gruppe_id" => $params["to_g_id"], ":datumvon" => $gt->datumvon))->fetch();
if ($to_g == false) {
// copy gruppentreffen
db_query("INSERT INTO {cdb_gruppentreffen} (gruppe_id, datumvon, datumbis, eintragerfolgt_yn,\n ausgefallen_yn, anzahl_gaeste, kommentar, modified_date, modified_pid)\n SELECT :to_g_id as gruppe_id, datumvon, datumbis, eintragerfolgt_yn,\n ausgefallen_yn, anzahl_gaeste, kommentar, '" . $dt->format('Y-m-d H:i:s') . "',\n {$user->id} as modified_pid\n FROM {cdb_gruppentreffen}\n WHERE gruppe_id=:gruppe_id and datumvon=:datumvon", array(":gruppe_id" => $params["from_g_id"], ":datumvon" => $gt->datumvon, ":to_g_id" => $params["to_g_id"]));
// and get id
$to_g = db_query("SELECT * from {cdb_gruppentreffen} WHERE gruppe_id=:gruppe_id AND datumvon=:datumvon", array(":gruppe_id" => $params["to_g_id"], ":datumvon" => $gt->datumvon))->fetch();
}
// now move the meeting to the new gruppentreffen_id
db_query("UPDATE {cdb_gruppentreffen_gemeindeperson} SET gruppentreffen_id=:gruppentreffenneu_id\n WHERE gruppentreffen_id=:gruppentreffen_id AND gemeindeperson_id=:gp_id", array(':gruppentreffen_id' => $gt->id, ':gruppentreffenneu_id' => $to_g->id, ':gp_id' => $gt->gp_id));
}
}
}
/**
* TODO: churchdb_getTodos is not tested (where?)
* explain, when gpg.status_no < -1 / replace status_nos by speaking constants
* @return string
*/
function churchdb_getTodos()
{
global $user;
$mygroups = churchdb_getMyGroups($user->id, true, true, false);
$mysupergroups = churchdb_getMyGroups($user->id, true, true, true);
if (!$mygroups) {
return "";
}
if (!$mysupergroups) {
$mysupergroups = array(-1);
}
$groups = db_query("\n SELECT p.id, p.vorname, p.name, g.bezeichnung, gpg.status_no, s.bezeichnung AS status\n FROM {cdb_person} p, {cdb_gruppe} g, {cdb_gemeindeperson} gp, {cdb_gemeindeperson_gruppe} gpg, {cdb_gruppenteilnehmerstatus} s\n WHERE s.intern_code=gpg.status_no AND gpg.gemeindeperson_id=gp.id AND gp.person_id=p.id AND gpg.gruppe_id=g.id\n AND ((gpg.gruppe_id IN (" . db_implode($mygroups) . ") AND gpg.status_no<-1)\n OR (gpg.gruppe_id IN (" . db_implode($mysupergroups) . ") AND gpg.status_no=-1))\n ORDER BY status");
if (!$groups) {
return "";
}
$arr = array();
foreach ($groups as $g) {
if (!isset($arr[$g->status_no])) {
$arr[$g->status_no] = (object) array();
}
if (!isset($arr[$g->status_no]->content)) {
$arr[$g->status_no]->content = array();
}
// TODO is this the same as the 2 lines above?
// if (!isset($arr[$g->status_no])) $arr[$g->status_no] = (object) array ('content' => array());
$arr[$g->status_no]->content[] = $g;
$arr[$g->status_no]->status_no = $g->status_no;
$arr[$g->status_no]->status = $g->status;
}
$txt = "";
$entries = "";
$status = "";
$count = 0;
foreach ($arr as $status) {
$txt .= "<li><p>{$status->status} <label class='pull-right badge badge-" . ($status->status_no == -1 ? "important" : "info") . "'>" . count($status->content) . "</label>";
foreach ($status->content as $g) {
$txt .= "<br/><small><a href='?q=churchdb#PersonView/searchEntry:#{$g->id}'>{$g->vorname} {$g->name}</a> - {$g->bezeichnung}</small>";
}
}
if ($txt != "") {
$txt = '<ul>' . $txt . '</ul>';
}
return $txt;
}
/**
* get church service authorisation
*
* @return array
*/
function churchservice_getAuthorization()
{
global $auth;
if (!isset($_SESSION["user"]->auth["churchservice"])) {
return null;
}
$auth = $_SESSION["user"]->auth["churchservice"];
$user_pid = $_SESSION["user"]->id;
$res = null;
// TODO: why not $res = array();
$res["user_pid"] = $user_pid;
if (user_access("view", "churchdb")) {
$res["viewchurchdb"] = true;
}
if (user_access("administer persons", "churchcore")) {
$res["administer persons"] = true;
}
if (isset($auth["view"])) {
$res["read"] = true;
}
if (isset($auth["edit events"])) {
$res["read"] = true;
$res["write"] = true;
}
if (isset($auth["view history"])) {
$res["viewhistory"] = true;
}
if (isset($auth["view history"])) {
$res["viewhistory"] = true;
}
if (isset($auth["export data"])) {
$res["export"] = true;
}
if (isset($auth["edit template"])) {
$res["edit template"] = true;
}
if (isset($auth["edit masterdata"])) {
$res["admin"] = true;
}
if (isset($auth["manage absent"])) {
$res["manageabsent"] = true;
}
if (isset($auth["view facts"])) {
$res["viewfacts"] = true;
}
if (isset($auth["export facts"])) {
$res["exportfacts"] = true;
}
if (isset($auth["edit facts"])) {
$res["editfacts"] = true;
$res["viewfacts"] = true;
}
if (isset($auth["view song"])) {
if (isset($auth["view songcategory"])) {
//use this? if (isset($auth["view song"]) && isset($auth["view songcategory"]))
$res["viewsong"] = true;
$res["viewsongcategory"] = $auth["view songcategory"];
}
}
if (isset($auth["edit song"])) {
if (isset($auth["view songcategory"])) {
$res["viewsong"] = true;
$res["editsong"] = true;
$res["viewsongcategory"] = $auth["view songcategory"];
}
}
if (isset($auth["view servicegroup"])) {
$res_view = $auth["view servicegroup"];
} else {
$res_view = array();
}
//check if user is in one of the groups of servicegroup
$arr = churchcore_getTableData("cs_servicegroup", "sortkey");
$myTnGroups = churchdb_getMyGroups($user_pid, true, false);
$myLdGroups = churchdb_getMyGroups($user_pid, true, true);
foreach ($arr as $grp) {
$groups = churchservice_getGroupsOfServiceGroup($grp->id);
if ($grp->viewall_yn == 1 || array_in_array($groups, $myTnGroups)) {
$res_view[$grp->id] = true;
}
}
$res["viewgroup"] = $res_view;
if (isset($auth["edit servicegroup"])) {
$res["editgroup"] = $auth["edit servicegroup"];
// Copy edit permission to view permissions!
// TODO: is there a difference to $res["viewgroup"] = $auth["edit servicegroup"]?
foreach ($auth["edit servicegroup"] as $key => $a) {
$res["viewgroup"][$key] = $a;
}
} else {
$res["editgroup"] = array();
}
// check if user is leader or at least member of a group
// this is important for editing entries
$arr = churchcore_getTableData("cs_service", "sortkey");
$res_member = array();
$res_leader = array();
$res_edit = array();
foreach ($arr as $service) {
$groups = churchservice_getGroupsOfService($service->id);
if (array_in_array($groups, $myLdGroups)) {
$res_member[$service->id] = true;
$res_leader[$service->id] = true;
} else {
if (array_in_array($groups, $myTnGroups)) {
$res_member[$service->id] = true;
}
}
// check edit permission for service group
if (isset($res["editgroup"][$service->servicegroup_id])) {
$res_edit[$service->id] = true;
}
}
$res["memberservice"] = $res_member;
$res["leaderservice"] = $res_leader;
$res["editservice"] = $res_edit;
if (isset($auth["view agenda"])) {
$res["view agenda"] = $auth["view agenda"];
}
if (isset($auth["edit agenda"])) {
$res["edit agenda"] = $auth["edit agenda"];
// copy permissions to view
// TODO: is there a difference to $res["edit_agenda"] = $res["view agenda"]?
foreach ($res["edit agenda"] as $key => $edit) {
$res["view agenda"][$key] = $edit;
}
}
if (isset($auth["edit agenda templates"])) {
$res["edit agenda templates"] = $auth["edit agenda templates"];
}
$auth = $res;
//$auth is global; TODO: why not use $auth all the way rather then the additional $res?
return $res;
}
/**
* is user leader of person?
*
* @param int $leader_id
* @param int $person_id
* @return boolean
*/
function churchdb_isPersonLeaderOfPerson($leader_id, $person_id)
{
$myGroups = churchdb_getMyGroups($leader_id, true, true);
if (count($myGroups) > 0) {
return churchdb_isPersonInGroups($person_id, $myGroups);
}
return false;
}
function churchservice_getAbsents($year = null)
{
$txt = '';
if (user_access("view", "churchdb")) {
$user = $_SESSION["user"];
include_once CHURCHDB . '/churchdb_db.php';
$groups = churchdb_getMyGroups($user->id, true, true);
$allPersonIds = churchdb_getAllPeopleIdsFromGroups($groups);
if (count($groups) > 0 && count($allPersonIds) > 0) {
$sql = "SELECT p.id p_id, p.name, p.vorname, DATE_FORMAT(a.startdate, '%d.%m.') startdate_short, DATE_FORMAT(a.startdate, '%d.%m.%Y') startdate, DATE_FORMAT(a.enddate, '%d.%m.%Y') enddate, a.bezeichnung, ar.bezeichnung reason \n FROM {cdb_person} p, {cs_absent} a, {cs_absent_reason} ar \n where a.absent_reason_id=ar.id and p.id=a.person_id and p.id in (" . implode(",", $allPersonIds) . ") ";
if ($year == null) {
$sql .= "and datediff(a.enddate,now())>=-1 and datediff(a.enddate,now())<=31";
} else {
$sql .= "and (DATE_FORMAT(a.startdate, '%Y')={$year} or DATE_FORMAT(a.enddate, '%Y')={$year})";
}
$sql .= " order by a.startdate";
$db = db_query($sql);
$people = array();
foreach ($db as $a) {
if (isset($people[$a->p_id])) {
$absent = $people[$a->p_id];
} else {
$absent = array();
}
$absent[] = $a;
$people[$a->p_id] = $absent;
}
if (count($people) > 0) {
$txt = '<ul>';
foreach ($people as $p) {
$txt .= '<li>' . $p[0]->vorname . " " . $p[0]->name . ": <p>";
foreach ($p as $abwesend) {
$reason = $abwesend->reason;
if ($abwesend->bezeichnung != null) {
$reason = $abwesend->bezeichnung . " ({$reason})";
}
if ($abwesend->startdate == $abwesend->enddate) {
$txt .= '<small>' . $abwesend->startdate . " {$reason}</small><br/>";
} else {
$txt .= '<small>' . $abwesend->startdate_short . "-" . $abwesend->enddate . " {$reason}</small><br/>";
}
}
}
$txt .= '</ul>';
}
if ($year == null && user_access("view", "churchcal")) {
$txt .= '<p style="line-height:100%" align="right"><a href="?q=churchcal&viewname=yearView">' . t("more") . '</a></p>';
}
}
}
return $txt;
}
/**
* is user leader or mitarbeiter of person in one group?
*
* @param int $leader_id
* @param int $person_id
* @return boolean
*/
function churchdb_isPersonLeaderOfPerson($leader_id, $person_id)
{
$myGroups = churchdb_getMyGroups($leader_id, true, true);
return !count($myGroups) ? false : churchdb_isPersonInGroups($person_id, $myGroups);
}
