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