function doReport($data, $typ = "C") { $tab = array("C" => "customer", "V" => "vendor"); $login = $_SESSION["login"]; $felder = substr($data['felder'], 0, -1); $tmp = suchstr($data, $typ); $where = $tmp["where"]; $tabs = $tmp["tabs"]; if ($typ == "C") { $rechte = "(" . berechtigung("C.") . ")"; } else { $rechte = "true"; } if (!preg_match('/P./', $felder)) { $where = $where == "" ? "" : "and {$where}"; if (preg_match('/shipto/i', $tabs) or preg_match('/S./', $felder)) { $sql = "select {$felder} from " . $tab[$typ] . " " . $typ . " left join shipto S "; $sql .= "on S.trans_id=" . $typ . ".id where (S.module='CT' or S.module is null or S.module='') and {$rechte} {$where} order by " . $typ . ".name"; } else { $sql = "select {$felder} from " . $tab[$typ] . " " . $typ . " where {$rechte} {$where} order by " . $typ . ".name"; } } else { $rechte .= ($rechte ? " and (" : "(") . berechtigung("P.cp_") . ")"; $where = $where == "" ? "" : "and {$where}"; if (preg_match('/shipto/i', $tabs) or preg_match('/S./', $felder)) { $sql = "select {$felder} from " . $tab[$typ] . " " . $typ . " left join shipto S "; $sql .= "on S.trans_id=" . $typ . ".id left join contacts P on " . $typ . ".id=P.cp_cv_id "; $sql .= "where (S.module='CT' or S.module is null or S.module='') and {$rechte} {$where} order by " . $typ . ".name,P.cp_name"; } else { $sql = "select {$felder} from " . $typ . " " . $typ . " left join contacts P "; $sql .= "on " . $typ . ".id=P.cp_cv_id where {$rechte} {$where} order by " . $typ . ".name,P.cp_name"; } } $rc = $_SESSION['db']->getAll($sql); $f = fopen('../tmp/report_' . $login . '.csv', "w"); fputs($f, $felder . "\n"); if ($rc) { foreach ($rc as $row) { $tmp = ""; foreach ($row as $fld) { $tmp .= "{$fld},"; } fputs($f, substr($tmp, 0, -1) . "\n"); } fclose($f); return true; } else { fputs($f, "Keine Treffer.\n"); fclose($f); return false; } }
function suchWDB($wort, $kat) { $rechte = berechtigung(); $sql = "SELECT distinct WK.* as cid from wissencontent WC left join wissencategorie WK on WC.categorie=WK.id where {$rechte} and "; if ($wort != '') { $sql .= " content ilike '%" . trim($wort) . "%' "; } else { if ($kat != '') { $sql .= " categorie = {$kat} "; } else { return false; } } $rs = $_SESSION['db']->getAll($sql); return $rs; }
function suchPerson($muster) { //$pre = ($_SESSION["preon"])?"%":""; //wiederspricht sich mit der Zeile danach $pre = isset($muster["pre"]) ? $_SESSION["pre"] : ""; $fuzzy = $muster["fuzzy"]; $andor = $muster["andor"]; $rechte = berechtigung("cp_"); $joinCustomer = " left join customer K on C.cp_cv_id=K.id"; $joinVendor = " left join vendor V on C.cp_cv_id=V.id"; if ($muster["cp_name"] == "~") { $where = "and upper(cp_name) ~ '^\\[^A-Z\\].*\$'"; } else { $dbf = array("cp_name", "cp_givenname", "cp_gender", "cp_title", "cp_street", "cp_zipcode", "cp_city", "cp_country", "country", "cp_phone1", "cp_fax", "cp_homepage", "cp_email", "cp_notes", "cp_stichwort1", "cp_birthday", "cp_beziehung", "cp_abteilung", "cp_position", "cp_cv_id", "cp_owener"); $keys = array_keys($muster); $anzahl = count($keys); $where = ""; if (isset($muster["customer_name"])) { $whereCustomer = "K.name ilike '{$pre}" . $muster["customer_name"] . "{$fuzzy}'"; $whereVendor = "V.name ilike '{$pre}" . $muster["customer_name"] . "{$fuzzy}'"; } else { $whereCustomer = '1=1'; $whereVendor = '1=1'; } $daten = false; $tbl0 = false; $where = array(); for ($i = 0; $i < $anzahl; $i++) { if (in_array($keys[$i], $dbf) && $muster[$keys[$i]]) { $suchwort = trim($muster[$keys[$i]]); $suchwort = strtr($suchwort, "*?", "%_"); if ($keys[$i] == "cp_birthday") { $d = explode("\\.", $suchwort); $suchwort = $d[2] . "-" . $d[1] . "-" . $d[0]; } if ($keys[$i] == "cp_phone1") { //Telefonnummer in beliebigen Telefonfeld suchen. $tmp = "(cp_phone1 like '" . $pre . $suchwort . "{$fuzzy}' "; $tmp .= "or cp_phone2 like '" . $pre . $suchwort . "{$fuzzy}' "; $tmp .= "or cp_mobile1 like '" . $pre . $suchwort . "{$fuzzy}' "; $tmp .= "or cp_mobile2 like '" . $pre . $suchwort . "{$fuzzy}' "; $tmp .= "or cp_satphone like '" . $pre . $suchwort . "{$fuzzy}')"; $where[] = $tmp; } else { $where[] .= $keys[$i] . " ilike '" . $pre . $suchwort . "{$fuzzy}'"; } } } $where = implode(" {$andor} ", $where); $x = 0; } $felderContact = "C.cp_id, C.cp_cv_id, C.cp_title, C.cp_name, C.cp_givenname, C.cp_fax, C.cp_email, C.cp_gender as cp_gender"; $felderContcatOrCustomerVendor = "COALESCE (C.cp_country, country) as cp_country,COALESCE (C.cp_zipcode, zipcode) as cp_zipcode,\n COALESCE (C.cp_city, city) as cp_city, COALESCE (C.cp_street, street) as cp_street,\n COALESCE (NULLIF (C.cp_phone1, ''), NULLIF (C.cp_mobile1, ''), phone) as cp_phone1"; $rs0 = array(); //leere arrays initialisieren, damit es keinen fehler bei der funktion array_merge gibt if ($muster["customer"]) { //auf checkbox customer mit Titel Kunden prüfen $sql0 = "select {$felderContact}, {$felderContcatOrCustomerVendor}, K.name as name, K.language_id as language_id,\n 'C' as tbl from contacts C{$joinCustomer} where C.cp_cv_id=K.id and ({$whereCustomer} {$andor} {$where}) and {$rechte} order by cp_name"; $rs0 = $_SESSION['db']->getAll($sql0); if (!$rs0) { $rs0 = array(); } } $rs1 = array(); //s.o. if ($muster["vendor"]) { //auf checkbox vendor mit Titel Lieferant prüfen $sql0 = "select {$felderContact}, {$felderContcatOrCustomerVendor}, V.name as name, V.language_id as language_id, 'V' as tbl\n from contacts C{$joinVendor} where C.cp_cv_id=V.id and ({$whereVendor} {$andor} {$where}) and {$rechte} order by cp_name"; $rs1 = $_SESSION['db']->getAll($sql0); if (!$rs1) { $rs1 = array(); } } $rs2 = array(); //s.o. if (isset($muster["deleted"])) { //auf checkbox deleted mit Titel "gelöschte Ansprechpartner (Kunden und Lieferanten)" prüfen // es gibt nicht nur gelöschte Personen, sonder auch Personen ohne Zuordnung zu Firmen, z.B. private Adressen $sql0 = "select {$felderContact}, C.cp_country, C.cp_zipcode, C.cp_city, C.cp_street, C.cp_phone1,\n '' as name,'P' as tbl from contacts C where {$rechte} and (" . $where . ") and C.cp_cv_id is null order by cp_name"; $rs2 = $_SESSION['db']->getAll($sql0); if (!$rs2) { $rs2 = array(); } } return array_merge($rs0, $rs1, $rs2); //alle ergebnisse zusammenziehen und zurückgeben }