/**
* get person data
*
* @param string $cond,
* additional sql where clause
* @param string $fields,
* to set sql columns
*
* @return array with person objects or nothing
*/
function churchdb_getAllowedPersonData($cond = '', $fields = "p.id p_id, gp.id gp_id, name, vorname, spitzname, station_id stn_id, status_id sts_id, email as em, \n if (telefonhandy='',telefonprivat, telefonhandy) as tl, geolat as lat, geolng as lng, archiv_yn")
{
global $user;
$where = $cond ? "and {$cond}" : "";
$allPersons = null;
// Get ALL data about which person is allowed to view which department
$sql_dep = db_query("SELECT person_id, bereich_id FROM {cdb_bereich_person}");
// Get departments, the user is in or has rights for
$allowedAndMyDeps = churchdb_getAllowedDeps();
$departments = array();
// fill $departments[personId][depId]
foreach ($sql_dep as $d) {
if (isset($allowedAndMyDeps[$d->bereich_id])) {
if (!isset($departments[$d->person_id])) {
$departments[$d->person_id] = array();
}
$departments[$d->person_id][$d->bereich_id] = $d->bereich_id;
}
}
// get all data about persons in groups for later matching
$sql_g = "SELECT gg.gemeindeperson_id gp_id, gg.gruppe_id id, gg.status_no leiter, \n DATE_FORMAT(gg.letzteaenderung, '%Y-%m-%d') d, gg.aenderunguser user, \n gg.followup_count_no, gg.followup_add_diff_days, followup_erfolglos_zurueck_gruppen_id, comment\n FROM {cdb_gemeindeperson_gruppe} gg";
$groups = db_query($sql_g);
$arr_groups = array();
foreach ($groups as $group) {
// if no followUp, nothing is needed.
if ($group->followup_count_no == null) {
unset($group->followup_count_no);
}
if ($group->followup_add_diff_days == null) {
unset($group->followup_add_diff_days);
}
if ($group->comment == null) {
unset($group->comment);
}
$arr_groups[$group->gp_id][$group->id] = $group;
}
// get all persons from VIEWALL departments
$allowedDeps = user_access("view alldata", "churchdb");
if ($allowedDeps != null) {
$sql_p = "SELECT {$fields}\n FROM {cdb_person} p, {cdb_gemeindeperson} gp \n WHERE p.id=gp.person_id AND 1=1 " . $where;
// whats the 1=1 for?
$res = db_query($sql_p);
foreach ($res as $p) {
$res = false;
foreach ($allowedDeps as $allowedDep) {
if (isset($departments[$p->p_id][$allowedDep])) {
$res = true;
}
}
if ($res) {
if (isset($departments[$p->p_id])) {
$p->access = $departments[$p->p_id];
}
if (isset($arr_groups[$p->gp_id])) {
$p->groups = $arr_groups[$p->gp_id];
}
$allPersons[$p->p_id] = $p;
}
}
}
// get all persons from groups the user is in or the user is district leader of group
$myGroups = churchdb_getMyGroups($user->id, true);
if (count($myGroups) > 0) {
$sql_g = "SELECT {$fields}\n FROM {cdb_person} p, {cdb_gemeindeperson} gp, {cdb_gemeindeperson_gruppe} gpg \n WHERE p.id=gp.person_id AND gpg.gemeindeperson_id=gp.id \n AND gpg.gruppe_id in (" . implode(",", $myGroups) . ") " . $where;
$res = db_query($sql_g);
foreach ($res as $p) {
if (!isset($allPersons[$p->p_id])) {
if (isset($departments[$p->p_id])) {
$p->access = $departments[$p->p_id];
}
if (isset($arr_groups[$p->gp_id])) {
$p->groups = $arr_groups[$p->gp_id];
}
$allPersons[$p->p_id] = $p;
}
}
}
// inclued user, if not yet
if (!isset($allPersons[$user->id])) {
$p = db_query("SELECT {$fields} FROM {cdb_gemeindeperson} gp, {cdb_person} p \n WHERE gp.person_id=p.id AND p.id=:p_id", array(":p_id" => $user->id), false)->fetch();
if ($p != false) {
if (isset($departments[$p->p_id])) {
$p->access = $departments[$p->p_id];
}
if (isset($arr_groups[$p->gp_id])) {
$p->groups = $arr_groups[$p->gp_id];
}
$allPersons[$user->id] = $p;
}
}
// add district leader
$db = db_query("SELECT * FROM {cdb_person_distrikt}");
foreach ($db as $d) {
if (isset($allPersons[$d->person_id])) {
if (isset($allPersons[$d->person_id]->districts)) {
$districts = $allPersons[$d->person_id]->districts;
} else {
$districts = array();
}
$districts[$d->distrikt_id] = $d;
$allPersons[$d->person_id]->districts = $districts;
}
}
// add group leader
$db = db_query("SELECT * FROM {cdb_person_gruppentyp}");
foreach ($db as $d) {
if (isset($allPersons[$d->person_id])) {
if (isset($allPersons[$d->person_id]->gruppentypen)) {
$gruppentypen = $allPersons[$d->person_id]->gruppentypen;
} else {
$gruppentypen = array();
}
$gruppentypen[$d->gruppentyp_id] = $d;
$allPersons[$d->person_id]->gruppentypen = $gruppentypen;
}
}
return $allPersons;
}
/**
* geth auth for ajax
* @return array with auth data
*/
function churchdb_getAuthForAjax()
{
global $config;
$auth = $_SESSION["user"]->auth["churchdb"];
$allowedDeps = churchdb_getAllowedDeps();
$res["dep"] = churchcore_getTableData("cdb_bereich", "", "id IN (" . implode(",", $allowedDeps) . ")");
if (isset($auth["view comments"])) {
foreach ($auth["view comments"] as $key => $value) {
$res["comment_viewer"][$key] = $value;
}
}
if (isset($auth["view address"])) {
$res["viewaddress"] = true;
}
if (isset($auth["view alldetails"])) {
$res["viewaddress"] = true;
$res["viewalldetails"] = true;
}
if (isset($auth["view statistics"])) {
$res["viewstats"] = true;
}
if (isset($auth["view history"])) {
$res["viewhistory"] = true;
}
if (isset($auth["view tags"])) {
$res["viewtags"] = true;
}
if (isset($auth["edit groups"])) {
$res["editgroups"] = true;
}
if (isset($auth["edit relations"])) {
$res["editrelations"] = true;
}
if (isset($auth["export data"])) {
$res["export"] = true;
}
if (isset($auth["write access"])) {
$res["write"] = true;
}
if (isset($auth["create person"])) {
$res["create person"] = true;
}
if (isset($auth["create person without agreement"])) {
$res["create person without agreement"] = true;
}
if (isset($auth["view archive"])) {
$res["viewarchive"] = true;
}
if (isset($auth["push/pull archive"])) {
$res["push/pull archive"] = true;
}
if (isset($auth["edit masterdata"])) {
$res["admin"] = true;
$res["read"] = true;
$res["write"] = true;
$res["export"] = true;
$res["viewalldata"] = true;
$res["viewalldetails"] = true;
$res["viewhistory"] = true;
$res["viewtags"] = true;
$res["editgroups"] = true;
$res["editrelations"] = true;
$res["viewstats"] = true;
$res["groupstats"] = true;
$res["admingroups"] = true;
$res["write"] = true;
}
if (isset($auth["administer groups"])) {
$res["admingroups"] = true;
$res["editgroups"] = true;
} else {
if (isset($auth["view group"])) {
$res["viewgroups"] = $auth["view group"];
}
}
if (isset($auth["view group statistics"])) {
$res["viewgroupstats"] = true;
}
// TODO: here must be differentiated by department
if (isset($auth["view alldata"])) {
$res["viewalldata"] = true;
}
if (user_access("complex filter", "churchdb")) {
$res["complex filter"] = true;
}
if (user_access("administer persons", "churchcore")) {
$res["adminpersons"] = true;
}
if (isset($auth["edit newsletter"])) {
$res["newsletter"] = $auth["edit newsletter"];
}
if (isset($auth["send sms"]) && $config["churchdb_smspromote_apikey"]) {
$res["sendsms"] = true;
}
if (!empty($config["churchdb_changeownaddress"]) && $config["churchdb_changeownaddress"] == 1) {
$res["changeownaddress"] = true;
}
return $res;
}
/**
* get person data
*
* TODO: check how much of the conditions can be put into sql - db is much quicker then php
*
* @param string $cond; additional sql where clause
* @param string $fields; to set sql columns
*
* @return array with person objects or nothing
*/
function churchdb_getAllowedPersonData($cond = '', $fields = "p.id p_id, gp.id gp_id, name, vorname, spitzname,\n station_id stn_id, status_id sts_id, email AS em, IF (telefonhandy='',telefonprivat, telefonhandy) AS tl,\n geolat AS lat, geolng AS lng, archiv_yn, date(geburtsdatum) geb")
{
global $user;
$where = $cond ? "AND {$cond}" : "";
$allPersons = null;
// Get ALL data about which person is allowed to view which department
$dep = db_query("SELECT person_id, bereich_id FROM {cdb_bereich_person}");
// Get departments, the user is in or has rights for
$allowedAndMyDeps = churchdb_getAllowedDeps();
//this does SELECT person_id, bereich_id FROM {cdb_bereich_person}" WHERE person_id=id
$departments = array();
// fill $departments[personId][depId]
// FIXME: First get all rows and then some rows out of it to test for all rows if they in some rows??? Thats crazy ;-)
foreach ($dep as $d) {
if (isset($allowedAndMyDeps[$d->bereich_id])) {
if (!isset($departments[$d->person_id])) {
$departments[$d->person_id] = array();
}
$departments[$d->person_id][$d->bereich_id] = $d->bereich_id;
}
}
// get all data about persons in groups for later matching
$groups = db_query("SELECT gg.gemeindeperson_id gp_id, gg.gruppe_id id, gg.status_no leiter,\n DATE_FORMAT(gg.letzteaenderung, '%Y-%m-%d') d, gg.aenderunguser user,\n gg.followup_count_no, gg.followup_add_diff_days, followup_erfolglos_zurueck_gruppen_id, comment\n FROM {cdb_gemeindeperson_gruppe} gg");
$arrGroups = array();
foreach ($groups as $group) {
// if no followUp, nothing is needed.
if ($group->followup_count_no == null) {
unset($group->followup_count_no);
}
if ($group->followup_add_diff_days == null) {
unset($group->followup_add_diff_days);
}
if ($group->followup_erfolglos_zurueck_gruppen_id == null) {
unset($group->followup_erfolglos_zurueck_gruppen_id);
}
if ($group->comment == null) {
unset($group->comment);
}
$arrGroups[$group->gp_id][$group->id] = $group;
}
// get all persons from VIEWALL departments
if ($allowedDeps = user_access("view alldata", "churchdb")) {
$res = db_query("SELECT {$fields}\n FROM {cdb_person} p, {cdb_gemeindeperson} gp\n WHERE p.id=gp.person_id " . $where);
foreach ($res as $p) {
$res = false;
// TODO: is this res the same as the db result??? if not rename it?
foreach ($allowedDeps as $allowedDep) {
if (isset($departments[$p->p_id][$allowedDep])) {
$res = true;
}
}
if ($res) {
if (isset($departments[$p->p_id])) {
$p->access = $departments[$p->p_id];
}
if (isset($p->gp_id) && isset($arrGroups[$p->gp_id])) {
$p->groups = $arrGroups[$p->gp_id];
}
$allPersons[$p->p_id] = $p;
}
}
}
// get all persons from groups the user is in or the user is district leader of group
$myGroups = churchdb_getMyGroups($user->id, true);
if (count($myGroups) > 0) {
$res = db_query("\n SELECT {$fields}\n FROM {cdb_person} p, {cdb_gemeindeperson} gp, {cdb_gemeindeperson_gruppe} gpg\n WHERE p.id=gp.person_id AND gpg.gemeindeperson_id=gp.id\n AND gpg.gruppe_id in (" . db_implode($myGroups) . ") " . $where);
foreach ($res as $p) {
if (!isset($allPersons[$p->p_id])) {
if (isset($departments[$p->p_id])) {
$p->access = $departments[$p->p_id];
}
if (isset($arrGroups[$p->gp_id])) {
$p->groups = $arrGroups[$p->gp_id];
}
$allPersons[$p->p_id] = $p;
}
}
}
// include user, if not yet
if (!isset($allPersons[$user->id])) {
$p = db_query("SELECT {$fields}\n FROM {cdb_gemeindeperson} gp, {cdb_person} p\n WHERE gp.person_id=p.id AND p.id=:p_id", array(":p_id" => $user->id), false)->fetch();
if ($p != false) {
if (isset($departments[$p->p_id])) {
$p->access = $departments[$p->p_id];
}
if (isset($arrGroups[$p->gp_id])) {
$p->groups = $arrGroups[$p->gp_id];
}
$allPersons[$user->id] = $p;
}
}
// add district leader
$db = db_query("SELECT * FROM {cdb_person_distrikt}");
foreach ($db as $d) {
if (isset($allPersons[$d->person_id])) {
if (isset($allPersons[$d->person_id]->districts)) {
$districts = $allPersons[$d->person_id]->districts;
} else {
$districts = array();
}
$districts[$d->distrikt_id] = $d;
$allPersons[$d->person_id]->districts = $districts;
}
}
// add group leader
$db = db_query("SELECT * FROM {cdb_person_gruppentyp}");
foreach ($db as $d) {
if (isset($allPersons[$d->person_id])) {
if (isset($allPersons[$d->person_id]->gruppentypen)) {
$gruppentypen = $allPersons[$d->person_id]->gruppentypen;
} else {
$gruppentypen = array();
}
$gruppentypen[$d->gruppentyp_id] = $d;
$allPersons[$d->person_id]->gruppentypen = $gruppentypen;
}
}
return $allPersons;
}
