function do_cron()
{
global $config;
ct_log("Cron-Job started.", 2, -1, 'cron');
$btns = churchcore_getModulesSorted(false, false);
foreach ($btns as $key) {
include_once constant(strtoupper($key)) . "/{$key}.php";
if (function_exists($key . "_cron")) {
if (isset($config[$key . "_name"]) && $config[$key . "_name"] != "") {
$arr = call_user_func($key . "_cron");
}
}
}
ct_sendPendingNotifications();
ct_log("Cron-Job finished.", 2, -1, 'cron');
}
/**
* execute cron job for all modules
*/
function do_cron()
{
global $files_dir;
ct_log("Cron-Job started.", 2, -1, 'cron');
//delete temporary files (and sessions) older than one day
$tempDir = $files_dir . '/tmp/';
foreach (array_slice(scandir($tempDir), 2) as $file) {
$path = $tempDir . $file;
if (is_file($path) && filemtime($path) < time() - 3600 * 24) {
unlink($path);
}
}
//launch the cronjobs of the individual CT modules
$modulesSorted = churchcore_getModulesSorted(false, false);
foreach ($modulesSorted as $key) {
include_once constant(strtoupper($key)) . "/{$key}.php";
if (function_exists($key . "_cron") && getConf($key . "_name")) {
call_user_func($key . "_cron");
}
}
ct_sendPendingNotifications();
ct_log("Cron-Job finished.", 2, -1, 'cron');
}
function churchcal_delAddition($params)
{
ct_log("del add", 1);
$db = db_query("select cal_id from {cc_cal_add} where id=:id", array(":id" => $params["id"]))->fetch();
if ($db == false) {
throw new CTException("Manuellen Termin #" + $params["id"] + " nicht gefunden!");
}
if (!churchcal_isAllowedToEditEvent($db->cal_id)) {
throw new CTNoPermission("AllowToEditEvent", "churchcal");
}
$i = new CTInterface();
$i->setParam("id");
db_delete("cc_cal_add")->fields($i->getDBInsertArrayFromParams($params))->condition("id", $params["id"], "=")->execute(false);
}
/**
* logout current user
*/
function logout_current_user()
{
if (isset($_SESSION["sessionid"])) {
db_query("DELETE FROM {cc_session} WHERE session=:id", array(':id' => $_SESSION["sessionid"]));
session_destroy();
}
if (isset($_SESSION["user"])) {
$user = $_SESSION["user"];
if ($user->id > 0) {
ct_log(t('logout.successful') . ": " . $user->email, 2, -1, "login");
}
//TODO use language of admin
unset($_SESSION["user"]);
}
// on logout delete remember me cookie!
setcookie("RememberMe", 0);
createAnonymousUser();
}
function churchcal_updateICalSource($id)
{
// Set modified date to not load this calendar each cron job
//db_query("UPDATE {cc_calcategory} SET modified_date = now() WHERE id = :id", (array(":id"=>$id)));
$cat = db_query("SELECT * FROM {cc_calcategory}\n WHERE id = :id", array(":id" => $id))->fetch();
if (!$cat) {
throw new CTException("No calcategory found");
}
include_once ASSETS . '/ics-parser/class.iCalReader.php';
$ical = new ICal($cat->ical_source_url);
$events = $ical->events();
if (!$events) {
ct_log("iCal Source from {$cat->bezeichnung} could not readed and processed!", 2, "calcategory", $id);
} else {
db_query("DELETE FROM {cc_cal} WHERE category_id = :id", array(":id" => $id));
include_once CHURCHCAL . '/churchcal_db.php';
foreach ($events as $event) {
$data = array();
$data["startdate"] = churchcore_icalToDate($event["DTSTART"]);
if (isset($event["DTEND"])) {
$data["enddate"] = churchcore_icalToDate($event["DTEND"]);
}
$data["bezeichnung"] = utf8_encode(getVar("SUMMARY", "", $event));
$data["category_id"] = $id;
$data["repeat_id"] = 0;
$data["intern_yn"] = 0;
$data["modified_pid"] = -1;
$data["notizen"] = getVar("SUMMARY", "", $event) . " " . getVar("DESCRIPTION", "", $event);
$data["link"] = getVar("URL", "", $event);
$data["ort"] = "";
if ($data["startdate"] != "") {
// Substract one day if it is a whole day date
$sd = new Datetime($data["startdate"]);
if (!isset($data["enddate"])) {
$ed = new Datetime($data["startdate"]);
} else {
$ed = new Datetime($data["enddate"]);
if (isFullDay($sd, $ed)) {
$ed->modify("-1 DAY");
}
}
$data["startdate"] = $sd->format("Y-m-d H:i");
$data["enddate"] = $ed->format("Y-m-d H:i");
churchcal_createEvent($data, null, true);
}
}
ct_log("iCal Source from {$cat->bezeichnung} readed and processed!", 2, $id, "category");
}
}
public function logMasterData($params, $loglevel = 2)
{
ct_log($this->prepareForLog($params), $loglevel, isset($params["id"]) ? $params["id"] : null, CDB_LOG_MASTERDATA);
}
/**
* cron job
*/
function churchdb_cron()
{
global $config;
include_once "churchdb_db.php";
createGroupMeetings();
// delete tags
// get tags used by churchservices
$services = churchcore_getTableData('cs_service', '', 'cdb_tag_ids is not null');
$tag = array();
if ($services != false) {
foreach ($services as $service) {
$arr = explode(',', $service->cdb_tag_ids);
foreach ($arr as $ar) {
if (trim($ar) != '') {
$tag[trim($ar)] = true;
}
}
}
}
$res = db_query("SELECT * FROM {cdb_tag} t LEFT JOIN {cdb_gemeindeperson_tag} gpt ON ( t.id = gpt.tag_id )\n \t\t\t\t\tLEFT JOIN {cdb_gruppe_tag} gt ON ( t.id = gt.tag_id )\n WHERE gpt.tag_id IS NULL AND gt.tag_id IS null");
// delete unused ones
foreach ($res as $id) {
if (!isset($tag[$id->id])) {
//TODO this sort of query is for reusing prepared statements - but probably no importend speed advantage to change it :-)
db_query("delete from {cdb_tag} where id=:id", array(":id" => $id->id));
cdb_log("CRON - Loesche Tag Id:" . $id->id . " " . $id->bezeichnung . ", da nicht verwendet", 2);
}
}
// reset login error count for all persons
//TODO: check time of last login try?
db_query("update {cdb_person} set loginerrorcount=0");
// clean mail archive
db_query("delete FROM {cc_mail_queue}\n WHERE (DATE_ADD( modified_date, INTERVAL 30 DAY ) < NOW( ))\n and send_date is not null\n and error=0");
db_query("delete FROM {cc_mail_queue}\n WHERE (DATE_ADD( modified_date, INTERVAL 14 DAY ) < NOW( ))\n and send_date is not null\n and modified_pid=-1\n and error=0");
db_query("delete FROM {cc_mail_queue}\n WHERE (DATE_ADD( modified_date, INTERVAL 90 DAY ) < NOW( ))");
// Synce MailChimp
if (!empty($config["churchdb_mailchimp_apikey"])) {
include_once ASSETS . "/mailchimp-api-class/inc/MCAPI.class.php";
$api = new MCAPI($config["churchdb_mailchimp_apikey"]);
$list_id = null;
$db = db_query("select * from {cdb_gruppe_mailchimp} order by mailchimp_list_id");
foreach ($db as $lists) {
$list_id = $lists->mailchimp_list_id;
// get all subscribers not beeing in the group anymore
$db_g = db_query("select * from \n (select * from {cdb_gruppe_mailchimp_person} m where \n m.mailchimp_list_id='{$list_id}' and gruppe_id=:g_id) as m \n left join (select gpg.gruppe_id, gp.person_id from {cdb_gemeindeperson_gruppe} gpg, \n {cdb_gemeindeperson} gp where gp.id=gpg.gemeindeperson_id) gp on (gp.gruppe_id=m.gruppe_id and gp.person_id=m.person_id)\n where gp.person_id is null", array(":g_id" => $lists->gruppe_id));
$batch = array();
foreach ($db_g as $p) {
$batch[] = array("EMAIL" => $p->email);
db_query("delete from {cdb_gruppe_mailchimp_person} where \n (email=:email and gruppe_id=:g_id and mailchimp_list_id=:list_id)", array(":email" => $p->email, ":g_id" => $lists->gruppe_id, ":list_id" => $list_id));
}
listBatchUnsubscribe($api, $list_id, $batch, $lists->goodbye_yn == 1, $lists->notifyunsubscribe_yn == 1);
// get persons nto yet subscribed (not in table cdb_gruppe_mailchimp_personen)
$db_g = db_query("select * from (select p.id p_id, p.vorname, p.name, p.email p_email, gpg.gruppe_id g_id from {cdb_gemeindeperson} gp, {cdb_person} p, {cdb_gemeindeperson_gruppe} gpg\n where gp.person_id=p.id and gpg.gemeindeperson_id=gp.id and gpg.status_no>=0 and p.email!='' \n and gpg.gruppe_id={$lists->gruppe_id}) as t \n left join {cdb_gruppe_mailchimp_person} m \n on (m.gruppe_id=t.g_id and m.person_id=t.p_id and m.mailchimp_list_id='{$list_id}')\n where m.gruppe_id is null");
$batch = array();
foreach ($db_g as $p) {
$batch[] = array("EMAIL" => $p->p_email, "FNAME" => $p->vorname, "LNAME" => $p->name);
db_query("insert into {cdb_gruppe_mailchimp_person} (person_id, gruppe_id, mailchimp_list_id, email) \n values (:p_id, :g_id, :list_id, :email)", array(":p_id" => $p->p_id, ":g_id" => $p->g_id, ":list_id" => $list_id, ":email" => $p->p_email));
}
listBatchSubscribe($api, $list_id, $batch, $lists->optin_yn == 1);
}
}
// delete old mails
db_query("delete from {cc_mail_queue} where send_date is not null and datediff(send_date, now())<-60");
// Do Statistics
$db = db_query("select max(date) max, curdate() now from {crp_person}")->fetch();
if ($db->max != $db->now) {
db_query("insert into {crp_person} (\n SELECT curdate(), status_id, station_id, \n sum(case when datediff(erstkontakt,'" . $db->max . "')>=0 then 1 else 0 end), \n count(*) \n FROM {cdb_person} p, {cdb_gemeindeperson} gp\n where p.id=gp.person_id group by status_id, station_id\n )");
db_query("insert into {crp_group} (\n SELECT curdate(), gruppe_id, status_id, station_id, s.id gruppenteilnehmerstatus_id, \n sum(case when datediff(gpg.letzteaenderung,'" . $db->max . "')>=0 then 1 else 0 end), \n count(*) \n from {cdb_gemeindeperson_gruppe} gpg, {cdb_gruppenteilnehmerstatus} s, {cdb_gemeindeperson} gp, {cdb_gruppe} g\n where gpg.gemeindeperson_id=gp.id and gpg.status_no=s.intern_code\n and gpg.gruppe_id=g.id and (g.abschlussdatum is null or datediff(g.abschlussdatum, curdate())>-366)\n group by gruppe_id, status_id, station_id, gruppenteilnehmerstatus_id, s.id\n )");
ct_log('ChurchDB Tagesstatistik wurde erstellt.', 2);
}
}
function login_user($ret, $rember_me = false)
{
global $q, $q_orig;
if (!isset($ret->id)) {
addErrorMessage("Keine Id vorhanden, Fehler beim Login!");
return null;
}
$_SESSION["email"] = $ret->email;
if ($ret->cmsuserid == "") {
$ret->cmsuserid = $ret->vorname . " " . $ret->name . " [" . $ret->id . "]";
db_query("update {cdb_person} set cmsuserid='" . $ret->cmsuserid . "' where id={$ret->id}");
}
if ($ret->loginstr != null) {
db_query("update {cdb_person} set loginstr=null where id={$ret->id}");
}
$ret->auth = getUserAuthorization($ret->id);
$_SESSION["user"] = $ret;
// 6 Tage h�lt der Login
$ablaufDesCookies = time() + 60 * 60 * 24 * 6;
setcookie("RememberMe", $rember_me, $ablaufDesCookies);
$_SESSION["sessionid"] = random_string();
setcookie("CC_SessionId", $_SESSION["sessionid"], $ablaufDesCookies);
$dt = new DateTime();
db_query("update {cdb_person} set lastlogin=now(), loginerrorcount=0 where id=" . $ret->id);
// db_query("delete from {cc_session} where person_id=".$ret->id." AND hostname='".$_SERVER["HTTP_HOST"]."'");
db_query("delete from {cc_session} where datediff(now(), datum)>7");
db_query("insert into {cc_session} (person_id, session, hostname, datum) \n values (" . $ret->id . ", '" . $_SESSION["sessionid"] . "', '" . $_SERVER["HTTP_HOST"] . "', '" . $dt->format('Y-m-d H:i:s') . "')");
if ($ret->email != '') {
// Suche Leute aus der Familie, die die gleiche EMail-Adresse haben.
$res = db_query("select * from {cdb_person} where email=:email and archiv_yn=0", array(":email" => $ret->email));
$family = null;
$count = 0;
foreach ($res as $p) {
if ($p->id != $ret->id) {
$family[$p->id] = $p;
}
$count++;
if ($count > 15) {
break;
}
}
if ($family != null) {
$_SESSION["family"] = $family;
}
}
ct_log("Login succeed: " . $ret->email . " with " . (isset($_SERVER['HTTP_USER_AGENT']) ? $_SERVER['HTTP_USER_AGENT'] : "Unkown Browser!"), 2, -1, "login");
// Wenn es Ummelden war, dann nicht weiterleiten, denn sonst w�re das ja wieder Login.
if ($q != $q_orig) {
header("Location: ?q={$q_orig}");
} else {
if ($q == "login") {
header("Location: ?q=" . variable_get("site_startpage", "home"));
}
}
}
/**
*
* @param array $u userdata
* @param bool $rember_me
* @return NULL
*/
function login_user($u, $rember_me = false, $redirect = true)
{
global $q, $q_orig, $config;
if (empty($u->id)) {
addErrorMessage(t("login.error.no.id.specified"));
return null;
}
$_SESSION["email"] = $u->email;
if (!$u->cmsuserid) {
$u->cmsuserid = "{$u->vorname} {$u->name} [" . $u->id . "]";
db_query("UPDATE {cdb_person}\n SET cmsuserid=:cmsuserid\n WHERE id=:id", array(':cmsuserid' => $u->cmsuserid, ':id' => $u->id));
}
if ($u->loginstr) {
db_query("UPDATE {cdb_person}\n SET loginstr=NULL\n WHERE id=:id", array(':id' => $u->id));
}
$u->auth = getUserAuthorization($u->id);
$_SESSION["user"] = $u;
// TODO: make time configurable
// login is valid for 6 days
$cookieExpireTime = time() + 60 * 60 * 24 * 6;
setcookie("RememberMe", $rember_me, $cookieExpireTime);
$_SESSION["sessionid"] = random_string();
setcookie("CC_SessionId", $_SESSION["sessionid"], $cookieExpireTime);
$dt = new DateTime();
db_query("UPDATE {cdb_person} SET lastlogin=NOW(), loginerrorcount=0 WHERE id=:id", array(':id' => $u->id));
// Get language form user setting, if not available set it from current cookie
$lang = getUserSetting("churchcore", $u->id, "language");
if (!$lang) {
_churchcore_savePidUserSetting("churchcore", $u->id, "language", getConf("language"));
} else {
setcookie("language", $lang, time() + 60 * 60 * 24 * 30);
}
// 30 days
db_query("DELETE FROM {cc_session} WHERE datediff(NOW(), datum)>7");
db_query("INSERT INTO {cc_session} (person_id, session, hostname, datum)\n VALUES (:id, :session, :host, :date)", array(':id' => $u->id, ':session' => $_SESSION["sessionid"], ':host' => $_SERVER["HTTP_HOST"], ':date' => $dt->format('Y-m-d H:i:s')));
if ($u->email) {
// look for family users with the same email
$res = db_query("SELECT * FROM {cdb_person}\n WHERE email=:email AND archiv_yn=0", array(":email" => $u->email));
$family = array();
$count = 0;
foreach ($res as $p) {
if ($p->id != $u->id) {
$family[$p->id] = $p;
}
$count++;
if ($count > 15) {
break;
}
//no family should have more then 15 users
}
if (count($family)) {
$_SESSION["family"] = $family;
}
}
ct_log("Login succeed: {$u->email} with " . getVar('HTTP_USER_AGENT', "Unkown Browser", $_SERVER), 2, -1, "login");
if ($redirect) {
// on switching family login dont forward to login again
if ($q != $q_orig) {
header("Location: " . $_SERVER["REQUEST_URI"]);
} else {
if ($q == "login") {
header("Location: ?q=home");
}
}
}
}
/**
* 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"]));
}
}
/**
* 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 editHtml($form)
{
global $user;
$dt = new DateTime();
db_query("insert into {cc_wiki} (doc_id, text, modified_date, modified_pid) \n values (:doc_id, :text, :date, :pid) ON DUPLICATE KEY UPDATE text=:text, modified_date=:date, modified_pid=:pid", array(":text" => $form->fields["text"]->getValue(), ":doc_id" => $form->fields["doc_id"]->getValue(), ":date" => $dt->format('Y-m-d H:i:s'), ":pid" => $user->id));
ct_log("Aktualisierung Hilfeseite " . $form->fields["doc_id"]->getValue(), 2, "-1", "help");
header("Location: ?q=churchwiki&doc=" . $form->fields["doc_id"]->getValue());
}
/**
* cron job
*/
function churchdb_cron()
{
global $config;
include_once "churchdb_db.php";
createGroupMeetings();
// delete tags
// get tags used by churchservices
$services = churchcore_getTableData('cs_service', '', 'cdb_tag_ids is not null');
$tag = array();
if ($services) {
foreach ($services as $service) {
$arr = explode(',', $service->cdb_tag_ids);
foreach ($arr as $a) {
if (trim($a)) {
$tag[trim($a)] = true;
}
}
}
}
$res = db_query("SELECT * FROM {cdb_tag} t LEFT JOIN {cdb_gemeindeperson_tag} gpt ON ( t.id = gpt.tag_id )\n LEFT JOIN {cdb_gruppe_tag} gt ON ( t.id = gt.tag_id )\n WHERE gpt.tag_id IS NULL AND gt.tag_id IS null");
// delete unused tags
foreach ($res as $id) {
if (!isset($tag[$id->id])) {
// TODO this sort of query is for reusing prepared statements - but probably no important speed advantage to
// change it :-)
db_query("DELETE FROM {cdb_tag}\n WHERE id=:id", array(":id" => $id->id));
cdb_log("CRON - Delete Tag Id: {$id->id} {$id->bezeichnung}, not used", 2);
}
}
// reset login error count for all persons
// TODO: check time of last login try?
db_query("UPDATE {cdb_person} SET loginerrorcount=0");
// clean mail archive
db_query("DELETE FROM {cc_mail_queue}\n WHERE (DATE_ADD( modified_date, INTERVAL 30 DAY ) < NOW( )) AND send_date is NOT NULL AND error=0");
db_query("DELETE FROM {cc_mail_queue}\n WHERE (DATE_ADD( modified_date, INTERVAL 14 DAY ) < NOW( )) AND send_date IS NOT NULL AND modified_pid=-1 AND error=0");
db_query("DELETE FROM {cc_mail_queue}\n WHERE (DATE_ADD( modified_date, INTERVAL 90 DAY ) < NOW( ))");
// Synce MailChimp
if (!empty($config["churchdb_mailchimp_apikey"])) {
include_once ASSETS . "/mailchimp-api-class/inc/MCAPI.class.php";
$api = new MCAPI($config["churchdb_mailchimp_apikey"]);
$list_id = null;
$db = db_query("SELECT * FROM {cdb_gruppe_mailchimp}\n ORDER BY mailchimp_list_id");
foreach ($db as $lists) {
$list_id = $lists->mailchimp_list_id;
// get all subscribers not beeing in the group anymore
$db_group = db_query("\n SELECT *\n FROM (SELECT * FROM {cdb_gruppe_mailchimp_person} m WHERE m.mailchimp_list_id='{$list_id}' AND gruppe_id=:g_id) AS m\n LEFT JOIN (SELECT gpg.gruppe_id, gp.person_id FROM {cdb_gemeindeperson_gruppe} gpg, {cdb_gemeindeperson} gp\n WHERE gp.id=gpg.gemeindeperson_id) gp on (gp.gruppe_id=m.gruppe_id and gp.person_id=m.person_id)\n WHERE gp.person_id is null", array(":g_id" => $lists->gruppe_id));
$batch = array();
foreach ($db_group as $p) {
$batch[] = array("EMAIL" => $p->email);
db_query("DELETE FROM {cdb_gruppe_mailchimp_person}\n WHERE (email=:email AND gruppe_id=:g_id AND mailchimp_list_id=:list_id)", array(":email" => $p->email, ":g_id" => $lists->gruppe_id, ":list_id" => $list_id));
}
listBatchUnsubscribe($api, $list_id, $batch, $lists->goodbye_yn == 1, $lists->notifyunsubscribe_yn == 1);
// get persons not yet subscribed (not in table cdb_gruppe_mailchimp_personen)
$db_groups = db_query("\n SELECT *\n FROM (SELECT p.id AS p_id, p.vorname, p.name, p.email AS p_email, gpg.gruppe_id AS g_id\n FROM {cdb_gemeindeperson} gp, {cdb_person} p, {cdb_gemeindeperson_gruppe} gpg\n WHERE gp.person_id=p.id AND gpg.gemeindeperson_id=gp.id AND gpg.status_no>=0 AND p.email!=''\n AND gpg.gruppe_id={$lists->gruppe_id}) AS t\n LEFT JOIN {cdb_gruppe_mailchimp_person} m\n ON (m.gruppe_id=t.g_id and m.person_id=t.p_id and m.mailchimp_list_id='{$list_id}')\n WHERE m.gruppe_id is null");
$batch = array();
foreach ($db_groups as $p) {
$batch[] = array("EMAIL" => $p->p_email, "FNAME" => $p->vorname, "LNAME" => $p->name);
db_query("INSERT INTO {cdb_gruppe_mailchimp_person} (person_id, gruppe_id, mailchimp_list_id, email)\n VALUES (:p_id, :g_id, :list_id, :email)", array(":p_id" => $p->p_id, ":g_id" => $p->g_id, ":list_id" => $list_id, ":email" => $p->p_email));
}
listBatchSubscribe($api, $list_id, $batch, $lists->optin_yn == 1);
}
}
// delete old mails
db_query("DELETE FROM {cc_mail_queue}\n WHERE send_date IS NOT NULL AND DATEDIFF(send_date, NOW())<-60");
// Do Statistics
$db = db_query("SELECT MAX(date) AS max, CURDATE() AS now\n FROM {crp_person}")->fetch();
// TODO: add $db->max != $db->now to sql query?
if ($db->max != $db->now) {
db_query("INSERT INTO {crp_person} (\n SELECT CURDATE(), status_id, station_id,\n SUM(CASE WHEN DATEDIFF(erstkontakt,'" . $db->max . "')>=0 THEN 1 ELSE 0 END),\n COUNT(*)\n FROM {cdb_person} p, {cdb_gemeindeperson} gp\n WHERE p.id=gp.person_id group by status_id, station_id\n )");
db_query("INSERT into {crp_group} (\n SELECT curdate(), gruppe_id, status_id, station_id, s.id gruppenteilnehmerstatus_id,\n SUM(CASE WHEN DATEDIFF(gpg.letzteaenderung,'" . $db->max . "')>=0 THEN 1 ELSE 0 END),\n COUNT(*)\n FROM {cdb_gemeindeperson_gruppe} gpg, {cdb_gruppenteilnehmerstatus} s, {cdb_gemeindeperson} gp, {cdb_gruppe} g\n WHERE gpg.gemeindeperson_id=gp.id AND gpg.status_no=s.intern_code\n AND gpg.gruppe_id=g.id AND (g.abschlussdatum IS NULL OR DATEDIFF(g.abschlussdatum, curdate())>-366)\n GROUP BY gruppe_id, status_id, station_id, gruppenteilnehmerstatus_id, s.id\n )");
ct_log('ChurchDB Tagesstatistik wurde erstellt.', 2);
}
}
/**
*
* @param string $txt
* @param int $level
* @param int $domainid
* @param string $domaintype
* @param int $schreibzugriff_yn
* @param string $_user
*/
function cdb_log($txt, $level = 3, $domainid = -1, $domaintype = CDB_LOG_PERSON, $schreibzugriff_yn = 0, $_user = null)
{
ct_log($txt, $level, $domainid, $domaintype, $schreibzugriff_yn, $_user);
}
/**
*
* @param array $params
* @param int $loglevel
*/
public function logMasterData($params, $loglevel = 2)
{
ct_log($this->prepareForLog($params), $loglevel, getVar("id", null, $params), CDB_LOG_MASTERDATA);
}
