Title: Datagrid filter Result = null
Post by: Konitzer on August 15, 2014, 02:24:27 AM
Hallo Community, i filter a datagrid. When the db query found datas he works fine but when he dosn't find datas he show me an empty datagrid for all the time. Wherever i change the filter input the datagrid ist empty. I must update the site to bekome the normal datagrid. I hope you cann help me. Me View Code: function get_filter(){
var filter = new Array();
filter['rufnummer'] = searchRufnr;
filter['mobile'] = searchMobil;
filter['festnetz'] = searchFest;
filter['anbieter'] = searchAnbieter;
filter['kartennummer'] = searchKarte;
filter['kktnr'] = searchKonto;
filter['nutzer'] = searchNutzer;
filter['kostenstelle'] = searchKost;
filter['termination'] = searchGekuendigt;
filter['vvl'] = searchVVL;
filter['vertrag_date'] = searchDate;
filter['vertrag_date_direction'] = searchDateDirection;
filter['vertrag_date_type'] = searchDateType;
return filter;
}
function reload_datagrid_with_filter(){
filter = get_filter();
load_datagrid(filter);
}
function load_datagrid(filter) {
$('#dg').datagrid({
singleSelect: true,
fit: true,
showHeader:true,
url: '<?php echo base_url(); ?>index.php/ajax/datagrid_vertrag',
queryParams:filter,
onSelect: function(index, row) {
load_detail_datagrid(row.id);
},
});
}
My AJAX-Code: public function datagrid_vertrag($sub=false) {
echo $this->vertrag->overview($sub);
}
My Vertrag-Code: public function overview(){
$kundenid = $this->user->kundenid;
$kostenstellen = $this->user->kostenstellen;
$where = array();
$join = array();
$hilfswhere = null;
$where[] = "v.kundenID = {$kundenid}";
if($kostenstellen != false)
{
for($i = 0; $i < count($kostenstellen); $i++)
{
if($hilfswhere == null)
{
$hilfswhere = "((n.Kostenstelle = '" . $kostenstellen[$i] . "')";
} else {
$hilfswhere = $hilfswhere . " OR (n.Kostenstelle = '" . $kostenstellen[$i] . "')";
}
}
$hilfswhere = $hilfswhere . ")";
$where[] = $hilfswhere;
}
if($_POST["mobile"] == "true"){
$where[] = "ta.Mobilfunk = 1";
}
if($_POST["festnetz"] == "true"){
$where[] = "ta.Festnetz = 1";
}
if($_POST["anbieter"] != null){
$where[] = "t.AnbieterID = '{$_POST["anbieter"]}'";
}
if($_POST["kartennummer"] != null){
$where[] = "ka.Kartennummer LIKE '%{$_POST["kartennummer"]}%'";
}
if($_POST["kktnr"] != null){
$where[] = "kkn.Kundenkontonummer LIKE '%{$_POST["kktnr"]}%'";
}
if($_POST["nutzer"] != null){
$where[] = "n.NutzerBezeichnung LIKE '%{$_POST["nutzer"]}%'";
}
if($_POST["kostenstelle"] != null){
$where[] = "n.Kostenstelle LIKE '%{$_POST["kostenstelle"]}%'";
}
if($_POST["rufnummer"] != null){
$where[] = "(m.Rufnummer LIKE '%{$_POST["rufnummer"]}%' OR f.Rufnummer LIKE '%{$_POST["rufnummer"]}%')";
}
if($_POST["termination"] == "true"){
$where[] = "v.kuendigungsDatum IS NOT NULL";
}
if($_POST["vvl"] == "true"){
$where[] = "
(NOW()
BETWEEN DATE_SUB(vertragsende, INTERVAL IF(rv.vorzeitigeVVL IS NOT NULL, rv.vorzeitigeVVL,0) MONTH)
AND vertragsende OR v.sleeper IS NOT NULL)
";
}
if($_POST["vertrag_date"] != null){
$vertrag_date = $_POST["vertrag_date"]; // 01.12.2013
$d = date_parse_from_format("d.m.Y", $vertrag_date); // 2013-12-01
$vertrag_date = $d["year"]."-".$d["month"]."-".$d["day"];
$vertrag_date_direction = "<="; // from/to (<= >=)
if($_POST["vertrag_date_direction"] == "from"){
$vertrag_date_direction = ">=";
}
$vertrag_date_type = "vertragsende"; // mysql field
if($_POST["vertrag_date_type"] == "begin"){
$vertrag_date_type = "vertragsbegin";
}
$where[] = "{$vertrag_date_type} {$vertrag_date_direction} '{$vertrag_date}'";
}
$query = $this->build_vertrag_sql($where);
// print_r($query);
$datagrid_json = $this->easyui->select($query,$id=false);
return $datagrid_json;
}
public function build_vertrag_sql($where){
if($where){
$where_sql = implode(" AND ",$where);
}
$query = "
SELECT DISTINCT
v.ID AS id,
IF(f.Rufnummer IS NULL,m.Rufnummer,f.Rufnummer) AS rufnummer,
t.Bezeichnung AS tarif,
ab.Bezeichnung AS anbieter,
n.NutzerBezeichnung AS nutzer,
n.Kostenstelle AS kostenstelle,
DATE_FORMAT(v.Vertragsbegin, '%d.%m.%Y') AS vertragsbegin,
DATE_FORMAT(v.Vertragsende, '%d.%m.%Y') AS vertragsende,
IF(v.kuendigungsDatum is null,
IF(v.Status = 1, '<img height=\"16px\" weight=\"16px\" src=\"" . base_url() . "application/views/img/gelb.png\">',
IF((NOW() BETWEEN DATE_SUB(vertragsende, INTERVAL IF(rv.vorzeitigeVVL IS NOT NULL, rv.vorzeitigeVVL,0) MONTH) AND vertragsende OR v.sleeper IS NOT NULL) = 1,
'<img height=\"16px\" weight=\"16px\" src=\"" . base_url() . "application/views/img/gruen.png\">', null))
, '<img height=\"16px\" weight=\"16px\" src=\"" . base_url() . "application/views/img/grau.png\">') AS anzeige
FROM
vertrag v
LEFT JOIN festnetzvertrag f ON f.VertragsID = v.ID
LEFT JOIN mobilfunkvertrag m ON m.VertragsID = v.ID
LEFT JOIN kundennummern knr ON (m.KundennummerID = knr.ID OR f.KundenNRID = knr.ID)
LEFT JOIN kundenkontonummern kkn ON (m.KundenkontonrID = kkn.ID OR f.KundenkontoID = kkn.ID)
LEFT JOIN tarif t ON t.ID = v.TarifID
LEFT JOIN anbieterneu ab ON ab.ID = t.AnbieterID
LEFT JOIN nutzer n ON n.VertragsID = v.ID
LEFT JOIN rv ON rv.ID = v.RVID
LEFT JOIN tarifart ta ON ta.TarifID = t.ID
LEFT JOIN karten ka ON ka.VertragsID = v.ID
WHERE ".$where_sql."
ORDER BY n.Kostenstelle, v.id
";
return $query;
}
|