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