/**
* get personal newsletter for person p_id
*
* @param
* $p_id
*/
function getPersonalNews($p_id)
{
$user;
// ???
$sql = 'SELECT p.name, p.vorname, gp.id gp_id, p.cmsuserid
FROM {cdb_person} p, {cdb_gemeindeperson} gp
WHERE gp.person_id=p.id and p.id=:id';
$person = db_query($sql, array(":id" => $p_id))->fetch();
if (!$person || !$person->cmsuserid) {
return "";
}
if (!($user = user_load($person->cmsuserid))) {
return "";
}
// new persons in group
$sql_gruppen = 'SELECT gpg.gruppe_id g_id, g.bezeichnung FROM {cdb_gemeindeperson_gruppe} gpg, {cdb_gruppe} g
WHERE g.id=gpg.gruppe_id and gpg.status_no>0 AND ((g.abschlussdatum is null)
OR (datediff(g.abschlussdatum,CURRENT_DATE)>-100 ))
AND gpg.gemeindeperson_id=' . $person->gp_id . '
ORDER BY g.bezeichnung';
$sql_teilnehmer = 'SELECT vorname, name, gpg.letzteaenderung, gpg.aenderunguser, g.bezeichnung gruppe, gt.bezeichnung gruppentyp, p.id p_id
FROM {cdb_person} p, {cdb_gemeindeperson} gp, {cdb_gemeindeperson_gruppe} gpg, {cdb_gruppe} g, {cdb_gruppentyp} gt
WHERE p.id=gp.person_id AND gpg.gemeindeperson_id=gp.id AND gpg.gruppe_id=g.id
AND g.gruppentyp_id=gt.id AND gpg.gruppe_id=:gruppen_id
AND (datediff(gpg.letzteaenderung,CURRENT_DATE)>=-31)';
$curtxt = array();
$resGroups = db_query($sql_gruppen);
foreach ($resGroups as $group) {
$resPart = db_query($sql_teilnehmer, array(":gruppen_id" => $group->g_id));
foreach ($resPart as $p) {
// TODO: translate, optimize sql requests
$curtxt[] = _churchdb_a($p->p_id, "{$p->vorname} {$p->name}") . " ({$p->gruppentyp} {$p->gruppe} seit {$p->letzteaenderung}, eingepflegt von {$p->aenderunguser})";
}
}
$txt = "";
if (count($curtxt)) {
$txt .= "<h3>Neue Personen in Deinen Gruppen</h3>" . implode("<br/>", $curtxt);
}
// please (re?)view following persons
$sql_teilnehmer = 'SELECT p.id, gp.id gp_id, vorname, name FROM {cdb_person} p, {cdb_gemeindeperson} gp, {cdb_gemeindeperson_gruppe} gpg, {cdb_gruppe} g, {cdb_gruppentyp} gt
WHERE p.id=gp.person_id and gpg.gemeindeperson_id=gp.id AND gpg.gruppe_id=g.id AND g.gruppentyp_id=gt.id AND gpg.status_no=0
AND gpg.gruppe_id=:teilnehmer';
// $sql_beziehung_ich="SELECT * FROM {cdb_beziehung} WHERE vater_id=$p_id AND kind_id=%n AND datum+30>CURRENT_DATE";
$sql_beziehung_ich = "SELECT COUNT(*) c FROM {cdb_log} WHERE userid='" . $user->cmsuserid . "' AND domain_id=:person_id AND domain_type='person' AND (datediff(datum,CURRENT_DATE)>-100)";
$sql_beziehung_alle = "SELECT COUNT(*) c FROM {cdb_log} WHERE domain_id=:person_id AND domain_type='person' AND (datediff(datum,CURRENT_DATE)>-100)";
$sql_gruppentreffen = "SELECT COUNT(gt.id) c FROM {cdb_gruppentreffen_gemeindeperson} gtgp, {cdb_gruppentreffen} gt \n WHERE gtgp.gruppentreffen_id=gt.id AND datumbis+30>CURRENT_DATE AND gt.gruppe_id=:g_id AND gtgp.gemeindeperson_id=:gp_id";
$res = db_query($sql_gruppen);
$curtxt = array();
foreach ($res as $arr) {
$res2 = db_query($sql_teilnehmer, array(":teilnehmer" => $arr->g_id));
$txt2 = array();
foreach ($res2 as $p) {
$count_ich = db_query($sql_beziehung_ich, array(":person_id" => $p->id))->fetch();
$count_bez_alle = db_query($sql_beziehung_alle, array(":person_id" => $p->id))->fetch();
$count_gruppentreffen = db_query($sql_gruppentreffen, array(":g_id" => $arr->g_id, ":gp_id" => $p->id))->fetch();
if ($count_ich->c == 0 && $count_bez_alle->c < 3 && $count_gruppentreffen->c == 0) {
$txt2[] = _churchdb_a($p->id, $p->vorname . " " . $p->name) . " ({$count_bez_alle->c}/{$count_gruppentreffen->c})";
}
}
if (count($txt2)) {
$curtxt[] = "<i>In Gruppe {$arr->bezeichnung}</i><br/>" . implode("<br/>", $txt2);
}
}
if (!$curtxt) {
$txt .= "<br/><h3>Folgende Personen bitte einmal anschauen</h3>" . implode("<br/>", $curtxt);
}
// Geburtstage
// TODO: why select in select???
$sql_geb = "SELECT * FROM (\n SELECT person_id, datediff(DATE_ADD(geburtsdatum,INTERVAL (year(curdate())-year(geburtsdatum)) year),curdate()) as diff, name, \n vorname, geburtsdatum, (year(curdate())-year(geburtsdatum) - (RIGHT(CURDATE(),5)<RIGHT(geburtsdatum,5))) as 'alter'\n FROM {cdb_person} p, {cdb_gemeindeperson} gp, {cdb_gemeindeperson_gruppe} gpg\n WHERE p.id=gp.person_id AND geburtsdatum is not null AND gpg.gemeindeperson_id=gp.id\n AND gpg.gruppe_id=:gp_id) \n as t WHERE t.diff>=0 AND t.diff<=31 ORDER BY t.diff";
// ORDER BY t.diff, MONTH(geburtsdatum ), DAYOFMONTH(geburtsdatum ), name, vorname";
$res = db_query($sql_gruppen);
$curtxt = array();
foreach ($res as $arr) {
$res2 = db_query($sql_geb, array(":gp_id" => $arr->g_id));
$txt2 = "";
foreach ($res as $p) {
$txt2 = $txt2 . _churchdb_a($p->person_id, "{$p->vorname} {$p->name}") . " {$p->geburtsdatum} (in {$p->diff} Tagen) <br/>";
}
if ($txt2) {
$curtxt[] = "<i>In Gruppe {$arr->bezeichnung}</i><br/>{$txt2}<br/>";
}
}
if ($curtxt) {
$txt .= "<br/><h3>Geburtstage Deiner Personen in den nächsten 31 Tagen</h3>" . implode("<br/>", $curtxt);
}
$sql_teilnehmer = "SELECT vorname, name, c.text, c.datum, c.userid, p.id p_id FROM {cdb_person} p, {cdb_gemeindeperson} gp, {cdb_gemeindeperson_gruppe} gpg, {cdb_gruppe} g, {cdb_gruppentyp} gt, {cdb_comment} c\n WHERE p.id=gp.person_id AND gpg.gemeindeperson_id=gp.id AND gpg.gruppe_id=g.id AND g.gruppentyp_id=gt.id\n AND gpg.gruppe_id=:g_id AND c.comment_viewer_id=0 AND c.relation_id=p.id AND c.relation_name='person' AND (datediff(c.datum,CURRENT_DATE)>=-31) \n ORDER BY c.datum desc";
$res = db_query($sql_gruppen);
$curtxt = array();
foreach ($res as $arr) {
$res2 = db_query($sql_teilnehmer, array(":g_id" => $arr->g_id));
foreach ($res2 as $p) {
$curtxt[] = _churchdb_a($p->p_id, "{$p->vorname} {$p->name}") . " - \"{$p->text}\" ({$p->datum} von {$p->userid})<br/>";
}
}
if (count($curtxt)) {
$txt .= "<br/><h3>Neue Kommentare zu Deinen Personen</h3>" . implode("<br/>", $curtxt);
}
if ($txt) {
$txt = "<div style=\"margin:3px;padding:5px\"><h2>Persönliche Infos für {$person->vorname} {$person->name}</h2>{$txt}</div>";
}
return $txt;
}
/**
* get personal newsletter for person p_id
*
* @param int $p_id
*/
function getPersonalNews($p_id)
{
$person = db_query('
SELECT p.name, p.vorname, gp.id gp_id, p.cmsuserid
FROM {cdb_person} p, {cdb_gemeindeperson} gp
WHERE gp.person_id=p.id and p.id=:id', array(":id" => $p_id))->fetch();
if (!$person || !$person->cmsuserid) {
return "";
}
if (!($user = user_load($person->cmsuserid))) {
return "";
}
// new persons in group
$sql_gruppen = 'SELECT gpg.gruppe_id g_id, g.bezeichnung FROM {cdb_gemeindeperson_gruppe} gpg, {cdb_gruppe} g
WHERE g.id=gpg.gruppe_id and gpg.status_no>0 AND ((g.abschlussdatum is null)
OR (datediff(g.abschlussdatum,CURRENT_DATE)>-100 ))
AND gpg.gemeindeperson_id=' . $person->gp_id . '
ORDER BY g.bezeichnung';
$sql_teilnehmer = 'SELECT vorname, name, gpg.letzteaenderung, gpg.aenderunguser, g.bezeichnung gruppe, gt.bezeichnung gruppentyp, p.id p_id
FROM {cdb_person} p, {cdb_gemeindeperson} gp, {cdb_gemeindeperson_gruppe} gpg, {cdb_gruppe} g, {cdb_gruppentyp} gt
WHERE p.id=gp.person_id AND gpg.gemeindeperson_id=gp.id AND gpg.gruppe_id=g.id
AND g.gruppentyp_id=gt.id AND gpg.gruppe_id=:gruppen_id
AND (datediff(gpg.letzteaenderung,CURRENT_DATE)>=-31)';
$curtxt = array();
$resGroups = db_query($sql_gruppen);
foreach ($resGroups as $group) {
$resPart = db_query($sql_teilnehmer, array(":gruppen_id" => $group->g_id));
foreach ($resPart as $p) {
// TODO: translate, optimize sql requests
$curtxt[] = _churchdb_a($p->p_id, "{$p->vorname} {$p->name}") . " ({$p->gruppentyp} {$p->gruppe} seit {$p->letzteaenderung}, eingepflegt von {$p->aenderunguser})";
}
}
$txt = "";
if (count($curtxt)) {
$txt .= "<h3>" . t('new.persons.in.your.groups') . "</h3>" . implode("<br/>", $curtxt);
}
// please (re?)view following persons
$sql_teilnehmer = '
SELECT p.id, gp.id gp_id, vorname, name
FROM {cdb_person} p, {cdb_gemeindeperson} gp, {cdb_gemeindeperson_gruppe} gpg, {cdb_gruppe} g, {cdb_gruppentyp} gt
WHERE p.id=gp.person_id and gpg.gemeindeperson_id=gp.id AND gpg.gruppe_id=g.id AND g.gruppentyp_id=gt.id AND gpg.status_no=0
AND gpg.gruppe_id=:teilnehmer';
// $sql_beziehung_ich="SELECT * FROM {cdb_beziehung} WHERE vater_id=$p_id AND kind_id=%n AND datum+30>CURRENT_DATE";
$sql_beziehung_ich = "\n SELECT COUNT(*) c\n FROM {cdb_log}\n WHERE userid='" . $user->cmsuserid . "' AND domain_id=:person_id AND domain_type='person' AND (datediff(datum,CURRENT_DATE)>-100)";
$sql_beziehung_alle = "\n SELECT COUNT(*) c\n FROM {cdb_log}\n WHERE domain_id=:person_id AND domain_type='person' AND (datediff(datum,CURRENT_DATE)>-100)";
$sql_gruppentreffen = "\n SELECT COUNT(gt.id) c\n FROM {cdb_gruppentreffen_gemeindeperson} gtgp, {cdb_gruppentreffen} gt\n WHERE gtgp.gruppentreffen_id=gt.id AND datumbis+30>CURRENT_DATE AND gt.gruppe_id=:g_id AND gtgp.gemeindeperson_id=:gp_id";
$res = db_query($sql_gruppen);
$curtxt = array();
foreach ($res as $arr) {
$res2 = db_query($sql_teilnehmer, array(":teilnehmer" => $arr->g_id));
$txt2 = array();
foreach ($res2 as $p) {
$count_ich = db_query($sql_beziehung_ich, array(":person_id" => $p->id))->fetch();
$count_bez_alle = db_query($sql_beziehung_alle, array(":person_id" => $p->id))->fetch();
$count_gruppentreffen = db_query($sql_gruppentreffen, array(":g_id" => $arr->g_id, ":gp_id" => $p->id))->fetch();
if ($count_ich->c == 0 && $count_bez_alle->c < 3 && $count_gruppentreffen->c == 0) {
$txt2[] = _churchdb_a($p->id, $p->vorname . " " . $p->name) . " ({$count_bez_alle->c}/{$count_gruppentreffen->c})";
}
}
if (count($txt2)) {
$curtxt[] = "<i>" . t('in.group.x', $arr->bezeichnung) . "</i><br/>" . implode("<br/>", $txt2);
}
}
if (!$curtxt) {
$txt .= "<br/><h3>" . t('please.look.at.this.persons') . "</h3>" . implode("<br/>", $curtxt);
}
// Geburtstage
// TODO: why select in select???
$sql_geb = "SELECT * FROM (\n SELECT person_id, DATEDIFF(DATE_ADD(geburtsdatum,INTERVAL (YEAR(CURDATE())-YEAR(geburtsdatum)) year),CURDATE()) AS diff, name,\n vorname, geburtsdatum, (YEAR(CURDATE())-YEAR(geburtsdatum) - (RIGHT(CURDATE(),5)<RIGHT(geburtsdatum,5))) AS 'alter'\n FROM {cdb_person} p, {cdb_gemeindeperson} gp, {cdb_gemeindeperson_gruppe} gpg\n WHERE p.id=gp.person_id AND geburtsdatum is not null AND gpg.gemeindeperson_id=gp.id\n AND gpg.gruppe_id=:gp_id\n ) AS t WHERE t.diff>=0 AND t.diff<=31\n ORDER BY t.diff";
// ORDER BY t.diff, MONTH(geburtsdatum ), DAYOFMONTH(geburtsdatum ), name, vorname";
$res = db_query($sql_gruppen);
$curtxt = array();
foreach ($res as $arr) {
$res2 = db_query($sql_geb, array(":gp_id" => $arr->g_id));
$txt2 = "";
foreach ($res as $p) {
$txt2 = $txt2 . _churchdb_a($p->person_id, "{$p->vorname} {$p->name}") . " {$p->geburtsdatum} (in {$p->diff} Tagen) <br/>";
}
if ($txt2) {
$curtxt[] = "<i>" . t('in.group.x', $arr->bezeichnung) . "</i><br/>{$txt2}<br/>";
}
}
if ($curtxt) {
$txt .= "<br/><h3>" . t('birthdays.of.your.persons.in.next.31.days') . "</h3>" . implode("<br/>", $curtxt);
}
$sql_teilnehmer = "\n SELECT vorname, name, c.text, c.datum, c.userid, p.id p_id FROM {cdb_person} p, {cdb_gemeindeperson} gp,\n {cdb_gemeindeperson_gruppe} gpg, {cdb_gruppe} g, {cdb_gruppentyp} gt, {cdb_comment} c\n WHERE p.id=gp.person_id AND gpg.gemeindeperson_id=gp.id AND gpg.gruppe_id=g.id AND g.gruppentyp_id=gt.id\n AND gpg.gruppe_id=:g_id AND c.comment_viewer_id=0 AND c.relation_id=p.id AND c.relation_name='person'\n AND (datediff(c.datum,CURRENT_DATE)>=-31)\n ORDER BY c.datum DESC";
$res = db_query($sql_gruppen);
$curtxt = array();
foreach ($res as $arr) {
$res2 = db_query($sql_teilnehmer, array(":g_id" => $arr->g_id));
foreach ($res2 as $p) {
$curtxt[] = _churchdb_a($p->p_id, "{$p->vorname} {$p->name}") . " - \"{$p->text}\" ({$p->datum} von {$p->userid})<br/>";
}
}
if (count($curtxt)) {
$txt .= "<br/>\n <h3>" . t('new.comments.to.your.persons') . "</h3>" . implode("<br/>", $curtxt);
}
if ($txt) {
$txt = "\n <div style=\"margin:3px;padding:5px\">\n <h2>" . t('personal.information.for.x', $person->vorname . ' ' . $person->name) . "</h2>\n {$txt}\n </div>" . NL;
}
return $txt;
}
