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