Esempio n. 1
0
/**
 * export data (send header and echo result)
 */
function churchdb__export()
{
    drupal_add_http_header('Content-type', 'application/csv; charset=ISO-8859-1; encoding=ISO-8859-1', true);
    drupal_add_http_header('Content-Disposition', 'attachment; filename="churchdb_export.csv"', true);
    include_once "churchdb_db.php";
    $params = $_GET;
    $template = null;
    $ids = null;
    if (isset($params["template"])) {
        $template = _getExportTemplateByName($params["template"]);
    }
    if (isset($params["ids"])) {
        $ids = $params["ids"];
    }
    $export = _getPersonDataForExport($ids, $template);
    $export = _addGroupRelationDataForExport($export, $template);
    // if filtered by relations, load and export linked persons too
    //FIXME: unsanitized get data inserted in sql query! change to use :var syntax with params!
    foreach ($export as $key => $entry) {
        if (isset($params["rel_part"]) && $params["rel_part"] != null && $params["rel_id"] != null) {
            $id = null;
            if ($params["rel_part"] == "k") {
                $rel = db_query("select * from {cdb_beziehung} where beziehungstyp_id=" . $params["rel_id"] . " and vater_id=" . $key)->fetch();
                if ($rel != false) {
                    $id = $rel->kind_id;
                }
            }
            if ($id == null || $params["rel_part"] == "k") {
                $rel = db_query("select * from {cdb_beziehung} where beziehungstyp_id=" . $params["rel_id"] . " and kind_id=" . $key)->fetch();
                $id = $rel->vater_id;
            }
            // if relation to additional person found
            if ($id != null && !isset($export[$id])) {
                $person = _getPersonDataForExport($id, $template);
                if ($person != null && isset($person[$id])) {
                    foreach ($person[$id] as $key => $value) {
                        $export[$key][$params["rel_part"] . "_" . $key] = $value;
                    }
                }
            }
        }
    }
    // check for relations and aggregate data sets, if parameter agg is specified
    $rels = getAllRelations();
    if ($rels != null) {
        $rel_types = getAllRelationTypes();
        foreach ($rels as $rel) {
            if (isset($params["agg" . $rel->typ_id]) && $params["agg" . $rel->typ_id] == "y" && isset($export[$rel->v_id]) && isset($export[$rel->k_id])) {
                // use the male as first person, if available
                if (!isset($export[$rel->v_id]["anrede2"]) || $export[$rel->v_id]["anrede2"] == "Lieber") {
                    $p1 = $rel->v_id;
                    $p2 = $rel->k_id;
                } else {
                    $p1 = $rel->k_id;
                    $p2 = $rel->v_id;
                }
                // add second to the male
                $export[$p1]["anrede"] = $rel_types[$rel->typ_id]->export_title;
                if (isset($export[$p1]["anrede2"]) && isset($export[$p2]["anrede2"])) {
                    $export[$p1]["anrede2"] = $export[$p2]["anrede2"] . " " . $export[$p2]["vorname"] . ", " . $export[$p1]["anrede2"];
                }
                $export[$p1]["vorname2"] = $export[$p2]["vorname"];
                if (isset($export[$p2]["email"])) {
                    $export[$p1]["email_beziehung"] = $export[$p2]["email"];
                }
                // remove second from export data
                $export[$p2] = null;
            }
        }
    }
    // check if there is a group given to add information about to export data
    if (isset($params["groupid"])) {
        foreach ($export as $k => $key) {
            if ($key != null) {
                $r = db_query("select g.bezeichnung, s.bezeichnung status, DATE_FORMAT(gpg.letzteaenderung, '%d.%m.%Y') letzteaenderung, gpg.comment \n                 from {cdb_gruppe} g, {cdb_gemeindeperson} gp, \n                       {cdb_gemeindeperson_gruppe} gpg, {cdb_gruppenteilnehmerstatus} s  \n                    where gp.id=gpg.gemeindeperson_id and g.id=:gruppe_id \n                           and s.intern_code=status_no\n                         and gpg.gruppe_id=g.id and gp.person_id=:person_id", array(":gruppe_id" => $params["groupid"], ":person_id" => $k))->fetch();
                if ($r != false) {
                    $export[$k]["Gruppe"] = $r->bezeichnung;
                    $export[$k]["Gruppe_Dabeiseit"] = $r->letzteaenderung;
                    $export[$k]["Gruppen_Kommentar"] = $r->comment;
                    $export[$k]["Gruppen_Status"] = $r->status;
                }
            }
        }
    }
    // Get all available columns
    $cols = array();
    foreach ($export as $key => $row) {
        if ($row != null) {
            foreach ($row as $a => $val) {
                if (gettype($val) != "object" && gettype($val) != "array") {
                    $cols[$a] = $a;
                }
            }
        }
    }
    // Add header
    $sql = "select langtext from {cdb_feld} where db_spalte=:db_spalte";
    foreach ($cols as $col) {
        $res = db_query($sql, array(":db_spalte" => $col))->fetch();
        if (!$res) {
            $txt = t($col);
            //TODO: test for actually used DB encoding?
            if (substr($txt, 0, 3) != "***") {
                echo mb_convert_encoding('"' . $txt . '";', 'ISO-8859-1', 'UTF-8');
            } else {
                echo mb_convert_encoding('"' . $col . '";', 'ISO-8859-1', 'UTF-8');
            }
        } else {
            echo mb_convert_encoding('"' . $res->langtext . '";', 'ISO-8859-1', 'UTF-8');
        }
    }
    echo "\n";
    // Sort data
    function sort_export_func($a, $b)
    {
        $sort_a = "";
        $sort_b = "";
        if (isset($a["name"])) {
            $sort_a .= $a["name"];
        }
        if (isset($a["vorname"])) {
            $sort_a .= $a["vorname"];
        }
        if (isset($b["name"])) {
            $sort_b .= $b["name"];
        }
        if (isset($b["vorname"])) {
            $sort_b .= $b["vorname"];
        }
        if (isset($a["id"])) {
            $sort_a .= $a["id"];
        }
        if (isset($b["id"])) {
            $sort_b .= $b["id"];
        }
        if ($sort_a == $sort_b) {
            return 0;
        }
        return $sort_a < $sort_b ? -1 : 1;
    }
    usort($export, "sort_export_func");
    // Add all data rows
    foreach ($export as $row) {
        if ($row != null) {
            foreach ($cols as $col) {
                if (isset($row[$col])) {
                    echo mb_convert_encoding('"' . $row[$col] . '";', 'ISO-8859-1', 'UTF-8');
                } else {
                    echo ";";
                }
            }
            echo "\n";
        }
    }
}
Esempio n. 2
0
/**
 * export data (send header and echo result)
 */
function churchdb__export()
{
    drupal_add_http_header('Content-type', 'application/csv; charset=ISO-8859-1; encoding=ISO-8859-1', true);
    drupal_add_http_header('Content-Disposition', 'attachment; filename="churchdb_export.csv"', true);
    include_once "churchdb_db.php";
    $ids = getVar("ids", null);
    $relPart = getVar("rel_part", null);
    $relId = getVar("rel_id", null);
    $template = _getExportTemplateByName(getVar("template", null));
    $export = _getPersonDataForExport($ids, $template);
    $export = _addGroupRelationDataForExport($export, $template);
    // if filtered by relations, load and export linked persons too
    foreach ($export as $key => $entry) {
        if ($relPart && $relId) {
            $id = null;
            if ($relPart == "k") {
                $rel = db_query("SELECT * FROM {cdb_beziehung}\n                        WHERE beziehungstyp_id=:relId AND vater_id=:key", array(":relId" => $relId, "key" => $key))->fetch();
                if ($rel) {
                    $id = $rel->kind_id;
                }
            }
            if (!$id) {
                $rel = db_query("SELECT * FROM {cdb_beziehung}\n                        WHERE beziehungstyp_id=:relId AND kind_id=:key", array(":relId" => $relId, "key" => $key))->fetch();
                $id = $rel->vater_id;
            }
            // if relation to additional person found
            if ($id && !isset($export[$id])) {
                $person = _getPersonDataForExport($id, $template);
                if ($person && isset($person[$id])) {
                    foreach ($person[$id] as $relKey => $relValue) {
                        $export[$key][$relPart . "_" . $relKey] = $relValue;
                    }
                }
            }
        }
    }
    // check for relations and aggregate data sets, if parameter agg is specified
    $rels = getAllRelations();
    if ($rels != null) {
        $rel_types = getAllRelationTypes();
        foreach ($rels as $rel) {
            if (getVar("agg" . $rel->typ_id) == "y" && isset($export[$rel->v_id]) && isset($export[$rel->k_id])) {
                // use the male as first person, if available
                if (!isset($export[$rel->v_id]["Anrede2"]) || $export[$rel->v_id]["Anrede2"] == t('salutation.man.2')) {
                    $p1 = $rel->v_id;
                    $p2 = $rel->k_id;
                } else {
                    $p1 = $rel->k_id;
                    $p2 = $rel->v_id;
                }
                // add second to the male
                $export[$p1]["Anrede"] = $rel_types[$rel->typ_id]->export_title;
                if (isset($export[$p1]["Anrede2"]) && isset($export[$p2]["Anrede2"])) {
                    $export[$p1]["Anrede2"] = $export[$p2]["Anrede2"] . " " . $export[$p2]["vorname"] . ", " . $export[$p1]["Anrede2"];
                }
                $export[$p1]["Vorname2"] = $export[$p2]["vorname"];
                if (isset($export[$p2]["email"])) {
                    $export[$p1]["email_beziehung"] = $export[$p2]["email"];
                }
                // remove second from export data
                $export[$p2] = null;
            }
        }
    }
    // check if there is a group given to add information about to export data
    if ($groupId = getVar("groupid")) {
        foreach ($export as $k => $key) {
            if ($key != null) {
                $r = db_query("\n           SELECT g.bezeichnung, s.bezeichnung status, DATE_FORMAT(gpg.letzteaenderung, '%d.%m.%Y') letzteaenderung, gpg.comment\n           FROM {cdb_gruppe} g, {cdb_gemeindeperson} gp, {cdb_gemeindeperson_gruppe} gpg, {cdb_gruppenteilnehmerstatus} s\n           WHERE gp.id=gpg.gemeindeperson_id and g.id=:gruppe_id and s.intern_code=status_no and gpg.gruppe_id=g.id and gp.person_id=:person_id", array(":gruppe_id" => $groupId, ":person_id" => $k))->fetch();
                if ($r) {
                    $export[$k]["Gruppe"] = $r->bezeichnung;
                    $export[$k]["Gruppe_Dabeiseit"] = $r->letzteaenderung;
                    $export[$k]["Gruppen_Kommentar"] = $r->comment;
                    $export[$k]["Gruppen_Status"] = $r->status;
                }
            }
        }
    }
    // Get all available columns
    $cols = array();
    foreach ($export as $key => $row) {
        if ($row) {
            foreach ($row as $a => $val) {
                if (!is_object($val) && !is_array($val)) {
                    $cols[$a] = $a;
                }
            }
        }
    }
    // Add header
    $sql = "SELECT langtext from {cdb_feld}\n          WHERE db_spalte=:db_spalte";
    foreach ($cols as $col) {
        $res = db_query($sql, array(":db_spalte" => $col))->fetch();
        if (!$res) {
            $txt = t($col);
            // TODO: test for actually used DB encoding?
            if (substr($txt, 0, 3) != "***") {
                echo mb_convert_encoding('"' . $txt . '";', 'ISO-8859-1', 'UTF-8');
            } else {
                echo mb_convert_encoding('"' . $col . '";', 'ISO-8859-1', 'UTF-8');
            }
        } else {
            echo mb_convert_encoding('"' . $res->langtext . '";', 'ISO-8859-1', 'UTF-8');
        }
    }
    echo "\n";
    // Sort data
    usort($export, "sort_export_func");
    // Add all data rows
    foreach ($export as $row) {
        if ($row) {
            foreach ($cols as $col) {
                if (isset($row[$col])) {
                    echo mb_convert_encoding('"' . $row[$col] . '";', 'ISO-8859-1', 'UTF-8');
                } else {
                    echo ";";
                }
            }
            echo "\n";
        }
    }
}