示例#1
0
/**
 * 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);
    }
}
 public function GroupMeeting($params)
 {
     $this->logGroup($params, 3);
     if ($params["sub"] == "getList") {
         $res = getGroupMeeting($params["g_id"]);
     } else {
         if ($params["sub"] == "canceled") {
             $res = cancelGroupMeeting($params["gt_id"]);
         } else {
             if ($params["sub"] == "create") {
                 createGroupMeetings();
             } else {
                 if ($params["sub"] == "stats") {
                     $res = getGroupMeetingStats($params["id"]);
                 } else {
                     if ($params["sub"] == "delete") {
                         $res = deleteGroupMeetingStats($params["id"]);
                     } else {
                         if ($params["sub"] == "saveProperties") {
                             $res = savePropertiesGroupMeetingStats($params);
                         } else {
                             if ($params["sub"] == "editCheckin") {
                                 $res = editCheckinGroupMeetingStats($params);
                             } else {
                                 throw new CTException("Error in GroupMeeting, unkown sub.");
                             }
                         }
                     }
                 }
             }
         }
     }
     return $res;
 }
示例#3
0
/**
 * 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);
    }
}